Database Schema

Auto-generated from services/api/server/db/schema.ts. Do not edit manually. Run pnpm run docs:generate:db to regenerate.

PostgreSQL with Drizzle ORM. Migrations auto-apply on startup via the 0.migrate.ts plugin.

Enums

book_status

Values: inbox, review, organized

Tables

Table	Description
books	Main book records with status, metadata, and search vectors
book_files	Physical file storage (format, checksums, paths)
book_metadata_candidates	Metadata fetched from external sources (Google Books, Hardcover)
reading_progress	Current KoSync reading position per document/device
reading_progress_history	Historical reading progress snapshots
service_credentials	Stored credentials for OPDS, KoSync, Hardcover, Google Books
api_keys	API key records (hashed, with labels)
hardcover_sync_log	Hardcover book sync tracking per library book

books

Column	Type	Nullable	Default	Notes
id	uuid	Yes	random()	PK
status	enum(book_status)	No	inbox
title	text	Yes	—
author	text	Yes	—
isbn_10	text	Yes	—
isbn_13	text	Yes	—
publisher	text	Yes	—
published_year	integer	Yes	—
language	text	Yes	—
description	text	Yes	—
cover_url	text	Yes	—
cover_path	text	Yes	—
page_count	integer	Yes	—
genres	text[]	No	[]
tags	text[]	No	[]
hardcover_book_id	integer	Yes	—
hardcover_edition_id	integer	Yes	—
possible_duplicate_of	uuid	Yes	—
approved_at	timestamptz	Yes	—
created_at	timestamptz	No	now()
updated_at	timestamptz	No	now()
search_vector	tsvector	Yes	—

book_files

Column	Type	Nullable	Default	Notes
id	uuid	Yes	random()	PK
book_id	uuid	No	—	FK → books.id
format	text	No	—
original_name	text	No	—
storage_path	text	Yes	—
inbox_path	text	Yes	—
file_size	bigint	No	0
checksum	text	Yes	—
content_hash	text	Yes	—
created_at	timestamptz	No	now()
updated_at	timestamptz	No	now()

book_metadata_candidates

Column	Type	Nullable	Default	Notes
id	uuid	Yes	random()	PK
book_id	uuid	No	—	FK → books.id
source	text	No	—
raw_response	jsonb	Yes	—
normalized	jsonb	No	{}
confidence	numeric(5,4)	No	0
selected_fields	text[]	No	[]

reading_progress

Column	Type	Nullable	Default	Notes
id	uuid	Yes	random()	PK
document	text	No	—
device	text	No	—
device_id	text	Yes	—
progress	text	No	—
percentage	numeric(5,4)	No	0
timestamp	bigint	No	0
raw_payload	jsonb	Yes	—
updated_at	timestamptz	No	now()

reading_progress_history

Column	Type	Nullable	Default	Notes
id	uuid	Yes	random()	PK
document	text	No	—
device	text	No	—
progress	text	No	—
percentage	numeric(5,4)	No	0
timestamp	bigint	No	0
created_at	timestamptz	No	now()

service_credentials

Column	Type	Nullable	Default	Notes
id	uuid	Yes	random()	PK
service	text	No	—
username	text	No	—
password_hash	text	No	—
created_at	timestamptz	No	now()
updated_at	timestamptz	Yes	—

api_keys

Column	Type	Nullable	Default	Notes
id	uuid	Yes	random()	PK
key_prefix	text	No	—
key_hash	text	No	—
label	text	No	—
created_at	timestamptz	No	now()
last_used_at	timestamptz	Yes	—

hardcover_sync_log

Column	Type	Nullable	Default	Notes
id	uuid	Yes	random()	PK
book_id	uuid	No	—	FK → books.id
hardcover_user_book_id	integer	Yes	—
hardcover_read_id	integer	Yes	—
last_status	text	Yes	—
last_progress	numeric(5,4)	Yes	—
last_rating	numeric(3,1)	Yes	—
last_synced_at	timestamptz	No	—
created_at	timestamptz	No	now()
updated_at	timestamptz	No	now()

Relationships

erDiagram
    books ||--o{ book_files : "has many"
    books ||--o{ book_metadata_candidates : "has many"
    books ||--o{ hardcover_sync_log : "has many"