Data Model | Alex Docs

Alex uses SQLite as its primary data store. Database access is mediated by the Rust watcher-rs bridge, and the schema supports both local-folder and S3-backed books.

Entity relationship diagram

Rendering diagram...

Tables

users

Stores user accounts and roles.

Column	Type	Description
`id`	TEXT (UUID)	Primary key (admin user has fixed ID `"1"` for desktop auth compatibility)
`email`	TEXT	Unique login identifier
`password_hash`	TEXT	Bcrypt hash
`display_name`	TEXT	Displayed in UI
`role`	TEXT	`admin` or `user`
`created_at`	INTEGER	Unix timestamp
`updated_at`	INTEGER	Unix timestamp

The default admin account (admin@localhost / admin123) is seeded using INSERT...ON CONFLICT DO UPDATE so it is idempotent and safe to re-run on every app launch.

books

Stores metadata for every ingested book.

Column	Type	Description
`id`	TEXT (UUID)	Primary key
`title`	TEXT	Extracted from metadata or filename
`author`	TEXT	Nullable author
`description`	TEXT	Nullable summary/description
`file_type`	TEXT	`pdf` or `epub`
`file_path`	TEXT	Local absolute path (`source='local'`) or S3 object key (`source='s3'`)
`file_size`	INTEGER	Size in bytes
`file_hash`	TEXT	SHA-256 hash (unique)
`cover_path`	TEXT	Cover path, nullable
`page_count`	INTEGER	PDF page count, nullable for EPUB
`added_at`	INTEGER	Unix timestamp
`updated_at`	INTEGER	Unix timestamp
`source`	TEXT	Storage source: `local` or `s3`
`s3_bucket`	TEXT	Bucket name for S3 books, nullable for local
`s3_etag`	TEXT	Last-seen object ETag for S3 diffing

Notes:

source='local' rows are ingested from LIBRARY_PATH.
source='s3' rows are ingested from configured S3 poll mode.
Duplicate content is prevented via unique file_hash.

reading_progress

Per-user reading position for each book.

Column	Type	Description
`id`	TEXT (UUID)	Primary key
`user_id`	TEXT	FK -> `users.id`
`book_id`	TEXT	FK -> `books.id`
`current_page`	INTEGER	PDF page position
`total_pages`	INTEGER	PDF total pages
`epub_location`	TEXT	EPUB CFI location
`percent_complete`	REAL	0.0 to 100.0
`status`	TEXT	`not_started`, `reading`, `completed`
`last_read_at`	INTEGER	Unix timestamp

collections

User-owned groups of books.

Column	Type	Description
`id`	TEXT (UUID)	Primary key
`user_id`	TEXT	FK -> `users.id`
`name`	TEXT	Collection name
`description`	TEXT	Optional description
`share_token`	TEXT	Public token, nullable
`shared_at`	INTEGER	When sharing enabled
`created_at`	INTEGER	Unix timestamp

collection_books

Many-to-many join table between collections and books.

Column	Type	Description
`collection_id`	TEXT	FK -> `collections.id` (composite PK)
`book_id`	TEXT	FK -> `books.id` (composite PK)
`added_at`	INTEGER	Unix timestamp

settings

App-level key-value settings.

Column	Type	Description
`key`	TEXT	Primary key
`value`	TEXT	Setting value
`updated_at`	INTEGER	Unix timestamp

Currently, library_version is used to notify clients of ingestion changes.

Relationships

One-to-many

users -> reading_progress
users -> collections
books -> reading_progress

Many-to-many

books <-> collections via collection_books

Cascade behavior

Deleting a book removes associated reading_progress and collection_books records.
Deleting a user removes their progress and collections.
Deleting a collection removes collection_books links but not the books.

Indexes

Key indexes include:

users.email (unique)
books.file_path (unique)
books.file_hash (unique)
books.title, books.author
reading_progress(user_id, book_id)
reading_progress.status
collections.share_token (unique)
collection_books(collection_id), collection_books(book_id)

Access and schema management

Database operations flow through the Rust bridge:

watcher-rs db query-all
watcher-rs db query-one
watcher-rs db execute

Schema is managed via SQL migration files applied by scripts/db-push.js:

0000_wide_expediter.sql — Initial schema (users, books, reading_progress, collections, collection_books, settings)
0001_s3_source_columns.sql — Adds source, s3_bucket, s3_etag columns to the books table

Schema management commands:

# Apply schema migrations
pnpm db:push

# Seed default admin user (idempotent upsert)
pnpm db:seed

# Reset database (destructive)
rm -f data/library.db data/library.db-shm data/library.db-wal && pnpm db:push && pnpm db:seed