The test server exposes the catalog of a made-up online bookshop, the Aurora Bookshop (Libreria Aurora ). Titles, authors, customers and reviews are generated by a deterministic program (api/app/seed.py): no real data, and every sandbox always starts from the same numbers. The content is in Italian (titles, names, cities, category names); table and column names are in English.
authors ──< books >── categories ──┐ (parent_id: una categoria
1 N N 1 │ può avere una categoria madre)
│ └──────────────────────────────┘
book_tags >── tags (N-N: un libro ha più tag, un tag più libri)
customers ──< orders ──< order_items >── books
1 N 1 N N 1 (N-N ordini↔libri, con quantità e prezzo)
└──< reviews >── books (un cliente recensisce più libri)
──< reads “one to many”: an author has many books; each book has one author (the FK books.author_id).
Counts of the public dataset (in a sandbox they change with your writes). The information on keys and constraints is what GET /api/v1/schema returns.
Column
Type
Notes
id
INTEGER
PK
name
TEXT
not null
country
TEXT
2-letter ISO code (IT, ES, NO, …)
born_year
INTEGER
bio
TEXT
Column
Type
Notes
id
INTEGER
PK
name
TEXT
slug
TEXT
UNIQUE; used in URLs (/categories/gialli/books)
parent_id
INTEGER
FK → categories.id (2-level hierarchy: Narrativa (fiction) → Gialli (crime), Fantascienza (sci-fi), Storici (historical); Saggistica (non-fiction) → Storia (history), Scienza (science), Informatica (computing))
Column
Type
Notes
id
INTEGER
PK
title
TEXT
not null; also indexed in the full-text table books_fts
isbn
TEXT
UNIQUE
author_id
INTEGER
FK → authors.id , index idx_books_author
category_id
INTEGER
FK → categories.id , index idx_books_category
price
REAL
CHECK(price >= 0), index idx_books_price
pages, published_year
INTEGER
language
TEXT
it (most), en, es
in_stock
INTEGER
0/1, exposed as a boolean
attributes
TEXT (JSON)
`{“format”: “brossura
created_at
TEXT
ISO 8601 UTC (2024-08-12T00:00:00Z)
tags(id PK, name UNIQUE); book_tags(book_id FK → books.id, tag_id FK → tags.id) with a composite primary key (book_id, tag_id) and ON DELETE CASCADE on both FKs. Tags: bestseller, novità (new), premio (award), classico (classic), illustrato (illustrated), serie (series), esordio (debut), tascabile (pocket), audiolibro (audiobook), tradotto (translated).
Column
Type
Notes
id
INTEGER
PK
name
TEXT
email
TEXT
UNIQUE, example.com domain
city
TEXT
ten Italian cities
created_at
TEXT
ISO 8601
Column
Type
Notes
id
INTEGER
PK
customer_id
INTEGER
FK → customers.id , indexed
status
TEXT
CHECK in pending, paid, shipped, delivered, cancelled; transitions: pending → paid → shipped → delivered; cancelled from pending or paid
shipping_address
TEXT (JSON)
{"street", "city", "zip", "country"}
total
REAL
sum of quantity × unit_price of the lines
created_at
TEXT
ISO 8601
Column
Type
Notes
id
INTEGER
PK
order_id
INTEGER
FK → orders.id , ON DELETE CASCADE
book_id
INTEGER
FK → books.id (a book that appears here cannot be deleted: 409 book_in_orders)
quantity
INTEGER
CHECK(quantity > 0)
unit_price
REAL
price at the time of the order (copied from books.price)
Column
Type
Notes
id
INTEGER
PK
book_id
INTEGER
FK → books.id , ON DELETE CASCADE, indexed
customer_id
INTEGER
FK → customers.id
rating
INTEGER
CHECK(rating BETWEEN 1 AND 5)
comment
TEXT
created_at
TEXT
ISO 8601
The book_stats view (book_id, reviews_count, avg_rating): reviews and average per book; it is what adds reviews_count and avg_rating to the /books responses.
The full-text table books_fts (FTS5 on title and description): it powers GET /search.
Prices: min €7.13, average €19.90, max €34.49.
Categories with the most books: Saggistica (14), Ragazzi (children, 12), Cucina (cooking, 9).
Author with the most books: Matteo Rinaldi (8).
Orders by status: delivered 48, paid 26, cancelled 18, shipped 16, pending 12.
Cities with the highest revenue: Torino (€1,715.02), Padova, Firenze.
GET /api/v1/schema — schema with PKs, FKs and counts
▶ Run Copy as curl
Response headers
GET /api/v1/categories?tree=true — the category tree
▶ Run Copy as curl
Response headers