Database Schema

Auto-generated from services/api-hono/src/db/schema.ts. Do not edit manually. Run vp run -F @libris/docs generate:db -- --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

reading_status

Values: unread, reading, finished, paused

Tables

Table	Description
api_keys	API key records (hashed, with labels)
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 (Hardcover)
reading_progress	Current KoSync reading position per document/device
reading_progress_history	Historical reading progress snapshots
reading_aggregate
service_credentials	Stored credentials for OPDS, KoSync, Hardcover
app_settings
upload_registry
hardcover_sync_log	Hardcover book sync tracking per library book

api_keys

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

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	—
series	text	Yes	—
series_index	real	Yes	—
genres	text[]	No	[]
tags	text[]	No	[]
hardcover_book_id	integer	Yes	—
hardcover_edition_id	integer	Yes	—
created_by	uuid	Yes	—	FK → apiKeys.id
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	—
original_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
book_id	uuid	Yes	—	FK → books.id
api_key_id	uuid	No	—	FK → apiKeys.id
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
book_id	uuid	Yes	—	FK → books.id
api_key_id	uuid	Yes	—	FK → apiKeys.id
document	text	No	—
device	text	No	—
progress	text	No	—
percentage	numeric(5,4)	No	0
timestamp	bigint	No	0
created_at	timestamptz	No	now()

reading_aggregate

Column	Type	Nullable	Default	Notes
id	uuid	Yes	random()	PK
api_key_id	uuid	No	—	FK → apiKeys.id
book_id	uuid	Yes	—	FK → books.id
started_at	timestamptz	Yes	—
finished_at	timestamptz	Yes	—
manual_status	enum(reading_status)	Yes	—
manual_started_at	timestamptz	Yes	—
manual_finished_at	timestamptz	Yes	—
manual_paused_at	timestamptz	Yes	—
manual_set_at	timestamptz	Yes	—
external_status	enum(reading_status)	Yes	—
external_status_synced_at	timestamptz	Yes	—
updated_at	timestamptz	No	now()

service_credentials

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

app_settings

Column	Type	Nullable	Default	Notes
key	text	Yes	—	PK
value	jsonb	No	—
updated_at	timestamptz	No	now()

upload_registry

Column	Type	Nullable	Default	Notes
id	uuid	Yes	random()	PK
checksum	text	No	—
api_key_id	uuid	No	—	FK → apiKeys.id
filename	text	No	—
created_at	timestamptz	No	now()

hardcover_sync_log

Column	Type	Nullable	Default	Notes
id	uuid	Yes	random()	PK
book_id	uuid	No	—	FK → books.id
api_key_id	uuid	No	—	FK → apiKeys.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
    apiKeys ||--o{ books : "has many"
    books ||--o{ book_files : "has many"
    books ||--o{ book_metadata_candidates : "has many"
    books ||--o{ reading_progress : "has many"
    apiKeys ||--o{ reading_progress : "has many"
    books ||--o{ reading_progress_history : "has many"
    apiKeys ||--o{ reading_progress_history : "has many"
    apiKeys ||--o{ reading_aggregate : "has many"
    books ||--o{ reading_aggregate : "has many"
    apiKeys ||--o{ service_credentials : "has many"
    apiKeys ||--o{ upload_registry : "has many"
    books ||--o{ hardcover_sync_log : "has many"
    apiKeys ||--o{ hardcover_sync_log : "has many"