The data model

This page is the map of the data: what is stored, how it relates and why. For the exact detail of each field, the code is the authority: crates/domain/src/schema.rs lists the fields of each entity, and spec/operation.schema.json lists the same for whoever implements the format. The names are in the glossary. Each data change travels as an operation.

Principles

• The ids are UUIDs, generated by the app, and they are stored as text. There are no auto-incrementing ids: two devices can create rows offline without clashing. (UUID v7s start with the date and sort by the moment of creation.) One exception: a mark’s id is derived from its person and its work (a UUID v5), so two devices that mark the same work for the same person write the same row.
• Everything belongs to a library. Each catalog row belongs to a library, and its identity is the pair (library, id): the same id in two libraries is two different rows, and references between rows are always resolved within the same library. Why: so that a library never overwrites or references another library’s data, even if someone invents ids on purpose.
• A SQLite database on each device (with STRICT tables, which check the types) and PostgreSQL on the server. The same model works in both.
• Dates as ISO 8601 text (2026-09-24 for days); ISBN normalised to thirteen digits without hyphens.
• On delete, the deletion wins. A required reference between rows deletes in cascade whatever hangs from it; an optional one is left empty. It is explained in The sync engine.

The catalog

works ──< work_contributors >── people
  │  │
  │  ├──< work_tags >── tags
  │  ├──< work_genres            (the app's closed list)
  │  ├──< series_entries >── series
  │  ├──< work_relations         (one work requires another)
  │  └──< marks >── members      (each person's own)
  │
  └──< editions ──< copies >── members
          │   └── publishers
          └── 1:1 ── <kind>_details

──< means “one to many”. The main pieces:

Entity	What it is	Main fields
work	The thing in the abstract: “Dune”, without saying which edition	kind (book, comic, boardgame, film, tv_series, videogame, music, other), title
edition	A concrete publication of a work	work_id, title, subtitle, publisher_id, published_year, language, cover, barcode, notes
copy	What is at home	edition_id, owner_id, format, quantity, location, acquired_on, platform (video games), seasons (series)
person	Someone who contributed to a work	name
work_contributor	Who took part in a work, in what role and in what order	work_id, person_id, role (author, writer, illustrator, designer, director, artist, studio), position
publisher	The publisher or the label	name
tag and work_tag	Tags: free words that each library creates for itself, and their link to works	name; work_id, tag_id
work_genre	A genre of a work, from the app’s closed list per kind (genre.rs)	work_id, genre
series and series_entry	A saga (with order) or a line of products (without it), and the works it contains	name, ordered; series_id, work_id, position
work_relation	One work requires another (an expansion and its base game)	work_id, related_work_id, relation (requires)
mark	What a person notes about a work	member_id, work_id, status, status_on, rating, favorite, wished

The format of a copy is a single list for all kinds (hardcover, paperback, pocket, ebook, pdf, audiobook, single_issue, box, dvd, bluray, uhd, physical, digital, vinyl, cd, cassette, other). The interface only offers the ones for each kind. Why one single list: so that a copy can be validated without looking at its work.

Why the work and the edition are kept apart

A book can have many editions (a 1990 hardcover, a 2010 paperback, a translation), and each one can have several copies at home. Separating the three layers avoids repeating data and allows questions that a single table cannot answer:

• The work is what interests you as a reader or player: what you read, rate and want (the marks hang from the work, not from the edition) and what you group into series.
• The edition is what is identified (by ISBN or barcode), what has the cover, the publisher and the year.
• The copy is what you have: format, owner, where it is. The format goes here, not in the edition, because what matters is which format you have it in.

It also makes one rule of adding cheap: if the ISBN is already in the library, the edition is not duplicated, only a copy is added. And a work with no copies (because you do not have it yet) is the “Missing” list.

Details by kind

What is specific to each kind lives in a <kind>_details table with one row per edition (table inheritance, D08: what is common in editions, what is specific apart, with normal columns):

Table	Fields
book_details	isbn13, google_books_id, open_library_id
comic_details	number
boardgame_details	players_min, players_max, duration_minutes, min_age
film_details	duration_minutes
tv_series_details	seasons

Music, video games and “Other” have no table: the artist is a contributor (artist), the label is the publisher, and the platform of a video game goes on the copy. Why columns and not a JSON field: they can be queried, validated and sorted in SQLite and PostgreSQL without tricks. Adding a kind with its own data takes a migration, and that is accepted because it happens very rarely.

Sharing and permissions

Entity	What it is	Main fields
library	The unit that is shared, synchronised and has permissions. Its id is the library’s	name, cloud_library_id, cloud_server, cloud_device_id, founding_nonce
member	A person in the library: owner of copies and, with roles, of permissions. It is not an account, although it may be linked to one	name, role (admin, editor, reader), can_share, account_id, access_withdrawn
device	A device that writes to the library, with the public key with which it signs	public_key, name, member_id (whose it is), vouched_key, trusted_through
revocation	A device whose access was withdrawn	device_id, device_name
change_request	How a request was resolved. Its id is the one of the requested operation	status (accepted, rejected, withdrawn), note

• A member owns copies and may have a role; an account (user) exists only on the server. They are linked with account_id, a random id of the account that only the cloud writes.
• The device belongs to a member through member_id, but only if its vouched_key matches its key. trusted_through is the fence (see security).
• Who may change what is not in the tables but in the rules of the specification.

The log and what makes it converge

Three tables, which are not catalog but what allows it to be synchronised:

Table	What it stores
operations	The log of all operations, own and received: id, library, clock mark, device, intent, the changes in JSON, schema version, signature, and its state (applied or request)
field_versions	For each field of each row, the mark of the last operation that wrote it
tombstones	The tombstones: the deleted rows, so that a deletion cannot undo itself

There is also search_index (the search text, which ops::record maintains) and row_authors (which device created each row, so that an editor only deletes what their person created; it is recalculated from the log).

What is not synced: the local state

Each device also stores things that are its own and do not travel: the paired devices and their secrets, which libraries go to each one (library_peers), the pending exit notices (leave_notices), the libraries it left or from which it was removed (forgotten_libraries), and its settings and its identity (local_state, which includes the seed of the signing key).

On the server

The catalog of each cloud library lives in PostgreSQL, with the same entities. The important differences:

• The primary key belongs to the server. Each row carries a key (a UUID v7 that never leaves the server) in addition to the app’s id, and the pair (cloud library, id) is unique. References between rows use the key. Why: the server never trusts a client id to look something up or decide anything.
• The cloud library has its own id (cloud_library_id, which the server creates), different from the library’s local id.
• A row cannot point to another library: triggers reject it in the database.
• The per-field versions live in the row itself (a JSON column), instead of in a separate table.
• Booleans and integers are BOOLEAN and BIGINT; there are no CHECKs for roles or states, because they are rules of spec/.

Each entity is a Doctrine class in server/src/Entity/Catalog/. How to add a field, and the six places that must be touched together, is in How to contribute.

Files outside the database

• covers/: the covers, named <sha256>.<extension>. Why name them by fingerprint: two devices that download the same cover generate the same name, and when syncing it is enough to ask for the files missing by name; besides, the operations do not carry images.
• Backups: a .zip with the catalog of each library, its operation history and the covers, which by default is stored encrypted with a password (a .kosmotheka file). They never carry the signing key, the session or the pairing secrets.

When the app is updated

Before applying a migration to a database that already has data, the app leaves a copy next to it, with the version it comes from (for example library.sqlite3.v8.bak). If an update goes wrong, close the app, delete library.sqlite3 and rename the copy: the previous version opens it as it was.