Database schema

The SQLite schema built by the versioned migrations of src/main/db/schema.ts (see MIGRATIONS). Columns marked added later are added to existing databases by addColumnIfMissing() (see the architecture).

achievements

Column	Type	Constraints	Default	Added later
id	TEXT	PRIMARY KEY
unlocked_at	TEXT	NOT NULL

credits

Column	Type	Constraints	Default	Added later
library_id	TEXT	PRIMARY KEY, NOT NULL
person_id	TEXT	PRIMARY KEY, NOT NULL
role	TEXT	PRIMARY KEY, NOT NULL
position	INTEGER	NOT NULL

library

Column	Type	Constraints	Default	Added later
id	TEXT	PRIMARY KEY
path	TEXT	NOT NULL, UNIQUE
title	TEXT	NOT NULL
page_count	INTEGER	NOT NULL
current_page	INTEGER	NOT NULL	0
added_at	TEXT	NOT NULL
last_opened_at	TEXT	NOT NULL
file_count	INTEGER	NOT NULL	0	yes
file_size	INTEGER	NOT NULL	0	yes
rating	INTEGER	NOT NULL	0	yes
tags	TEXT	NOT NULL	'[]'	yes
title_locked	INTEGER	NOT NULL	0	yes
series	TEXT			yes
volume	TEXT			yes
release_date	TEXT			yes
language	TEXT			yes
avg_page_width	INTEGER			yes
avg_page_height	INTEGER			yes
finished_at	TEXT			yes

library_folders

Column	Type	Constraints	Default	Added later
id	TEXT	PRIMARY KEY
path	TEXT	NOT NULL
added_at	TEXT	NOT NULL
watched	INTEGER	NOT NULL	1

people

Column	Type	Constraints	Default	Added later
id	TEXT	PRIMARY KEY
first_name	TEXT	NOT NULL, UNIQUE	''
last_name	TEXT	NOT NULL, UNIQUE
nationality	TEXT

reading_list_items

Column	Type	Constraints	Default	Added later
list_id	TEXT	PRIMARY KEY, NOT NULL
library_id	TEXT	PRIMARY KEY, NOT NULL
position	INTEGER	NOT NULL

reading_lists

Column	Type	Constraints	Default	Added later
id	TEXT	PRIMARY KEY
name	TEXT	NOT NULL
created_at	TEXT	NOT NULL
position	INTEGER	NOT NULL	0	yes
user_id	TEXT	NOT NULL	'default'	yes

reading_sessions

Column	Type	Constraints	Default	Added later
id	INTEGER	PRIMARY KEY
library_id	TEXT	NOT NULL
day	TEXT	NOT NULL
seconds	INTEGER	NOT NULL
user_id	TEXT	NOT NULL	'default'	yes

settings

Column	Type	Constraints	Default	Added later
key	TEXT	PRIMARY KEY
value	TEXT	NOT NULL

user_achievements

Column	Type	Constraints	Default	Added later
user_id	TEXT	PRIMARY KEY, NOT NULL
id	TEXT	PRIMARY KEY, NOT NULL
unlocked_at	TEXT	NOT NULL

user_books

Column	Type	Constraints	Default	Added later
user_id	TEXT	PRIMARY KEY, NOT NULL
library_id	TEXT	PRIMARY KEY, NOT NULL
current_page	INTEGER	NOT NULL	0
rating	INTEGER	NOT NULL	0
tags	TEXT	NOT NULL	'[]'
finished_at	TEXT
last_opened_at	TEXT	NOT NULL

user_settings

Column	Type	Constraints	Default	Added later
user_id	TEXT	PRIMARY KEY, NOT NULL
key	TEXT	PRIMARY KEY, NOT NULL
value	TEXT	NOT NULL

users

Column	Type	Constraints	Default	Added later
id	TEXT	PRIMARY KEY
nickname	TEXT	NOT NULL
avatar	BLOB
avatar_type	TEXT
created_at	TEXT	NOT NULL
last_active_at	TEXT	NOT NULL	''

Indexes

Index	Table	Columns
idx_credits_person_id	credits	person_id
idx_reading_lists_user_id	reading_lists	user_id
idx_reading_sessions_day	reading_sessions	day
idx_reading_sessions_user_day	reading_sessions	user_id, day
idx_user_books_library_id	user_books	library_id