Data Model

The current SQLite schema and the state stored outside it

Alex stores accounts, indexed book metadata, reading progress, collections, and its library change counter in SQLite. Checked-in SQL migrations are the schema source of truth; the web app accesses the database through short-lived watcher-rs db commands.

Entity relationship diagram

Rendering diagram...

Timestamps are Unix seconds.

Tables

`users`

Column	Meaning
`id`	Primary key. Normal users use UUIDs; the seeded desktop admin uses `"1"`.
`email`	Unique login identifier.
`password_hash`	bcrypt password hash.
`display_name`	Name displayed in the UI.
`role`	`admin` or `user`.
`created_at`, `updated_at`	Account timestamps.

pnpm db:seed upserts admin@localhost / admin123 as an admin. Re-running it resets the seeded account fields, including the password. Electron inserts the same account only when it is missing.

`books`

Column	Meaning
`id`	UUID primary key.
`title`, `author`, `description`	Extracted metadata with filename fallback for the title.
`file_type`	`pdf` or `epub`.
`file_path`	Unique absolute path for local books or object key for S3 books.
`file_size`	Bytes.
`file_hash`	Unique SHA-256 content hash.
`cover_path`	Nullable path to a local JPEG cover.
`page_count`	PDF page count; normally null for EPUB.
`added_at`, `updated_at`	Ingestion timestamps.
`source`	`local` or `s3`; defaults to `local`.
`s3_bucket`, `s3_etag`	Bucket identity and polling ETag for S3 rows.

Unique path/key and hash indexes prevent duplicate source entries and duplicate content.

`reading_progress`

Column	Meaning
`id`	UUID primary key.
`user_id`, `book_id`	Reader and book foreign keys.
`current_page`, `total_pages`	PDF position.
`epub_location`	EPUB CFI position.
`percent_complete`	Percentage from 0 through 100.
`status`	`not_started`, `reading`, or `completed`.
`last_read_at`	Last saved position time.

Application code maintains one logical row per user/book, although the current migration does not declare (user_id, book_id) unique. Deleting a book cascades to its progress rows.

`collections`

Column	Meaning
`id`	UUID primary key.
`user_id`	Owner foreign key.
`name`, `description`	Collection metadata. Route validation limits names to 100 characters.
`share_token`	Nullable, unique UUIDv4 public token.
`shared_at`, `created_at`	Sharing and creation timestamps.

Revoking sharing clears the token and timestamp. Re-enabling sharing later generates a new token.

`collection_books`

The (collection_id, book_id) composite primary key represents membership and prevents duplicates. added_at records when the membership was created. Deleting a book cascades to membership rows; collection deletion removes its memberships explicitly before deleting the collection.

`settings`

The key/value table currently stores library_version. Ingestion updates it whenever indexed books change, and the Server-Sent Events endpoint polls it to notify connected clients.

Current indexes and foreign keys

The migrations declare unique indexes for user email, book path, book hash, and collection share token, plus table primary keys. They do not declare separate title, author, progress-status, or foreign-key indexes.

Book foreign keys in reading_progress and collection_books use delete cascade. User foreign keys and collection_books.collection_id use SQLite's default no-action behavior.

Schema management

The schema files are:

0000_wide_expediter.sql — Initial six tables
0001_s3_source_columns.sql — Source, bucket, and ETag columns on books

pnpm db:push creates the schema when needed, repairs unique book indexes on older databases, and applies the S3 columns. Electron performs equivalent startup checks. Alex does not use a runtime ORM or migration-history table.

State outside SQLite

Book files live in LIBRARY_PATH or the configured S3 bucket.
Cover JPEGs live in COVERS_PATH.
Authenticated EPUB scroll restoration is also cached in browser storage as epub-progress:${bookId}.
Public reader progress is browser-only at shared-progress:${token}:${bookId}.
EPUB font size is saved as epub-reader-settings.
Desktop storage, window, secret, and relay settings live in Electron's user-data config.json.