Skip to content

Database

Jump to bottom
Luc Garrabos edited this page May 6, 2026 · 1 revision

Database

BookStorage supports two database backends: SQLite (default) and PostgreSQL. The schema is equivalent on both.

Table of contents

Database backends

Backend Driver Configuration
SQLite github.com/mattn/go-sqlite3 (CGO, requires GCC) BOOKSTORAGE_DATABASE=database.db
PostgreSQL github.com/lib/pq BOOKSTORAGE_POSTGRES_URL=postgresql://user:pass@host:5432/db

When BOOKSTORAGE_POSTGRES_URL is set, SQLite is not used. See Configuration for connection string details.

Schema overview

erDiagram
    users ||--o{ works : "owns"
    users ||--o{ sessions : "has"
    users ||--o{ reading_sites : "manages"
    users ||--o{ dismissed_recommendations : "dismisses"
    users ||--o{ csv_import_sessions : "imports"
    catalog ||--o{ works : "linked to"
    reading_sites ||--o{ works : "associated with"
    works ||--o{ works : "parent/child (series)"

    users {
        bigint id PK
        text username UK
        text password
        int validated
        int is_admin
        int is_superadmin
        text display_name
        text email
        text bio
        text avatar_path
        int is_public
        text google_sub UK
        text google_email
    }

    works {
        bigint id PK
        text title
        int chapter
        text link
        text status
        text image_path
        text reading_type
        bigint user_id FK
        int rating
        text notes
        timestamp updated_at
        int is_adult
        bigint catalog_id FK
        int anilist_enrich_opt_out
        bigint parent_work_id FK
        int series_sort
        int notify_new_chapters
        bigint reading_site_id FK
    }

    catalog {
        bigint id PK
        text title
        text reading_type
        text image_url
        text source
        text external_id
        timestamp created_at
    }

    sessions {
        bigint id PK
        bigint user_id FK
        text token_hash UK
        timestamp created_at
        timestamp last_seen_at
        timestamp expires_at
        text ip
        text user_agent
        timestamp revoked_at
    }

    reading_sites {
        bigint id PK
        bigint user_id FK
        text name
        text base_url
        timestamp last_probe_at
        text probe_status
        int probe_http_status
        text probe_detail
    }

    dismissed_recommendations {
        bigint id PK
        bigint user_id FK
        text source
        text external_id
        timestamp created_at
    }

    translation_cache {
        text source_hash PK
        text target_lang PK
        text translated_text
        timestamp created_at
    }

    csv_import_sessions {
        text id PK
        bigint user_id FK
        text raw_csv
        timestamp created_at
    }

    oauth_states {
        text state_hash PK
        text purpose
        bigint user_id
        text next
        bigint expires_at_unix
        text code_verifier
    }

    schema_migrations {
        int version PK
        timestamp applied_at
    }
Loading

Tables

users

Stores user accounts. Key fields:

Column Type Description
id BIGSERIAL Primary key
username TEXT Unique username
password TEXT bcrypt hash (nullable for OAuth-only accounts)
validated INTEGER 0 = pending approval, 1 = approved
is_admin INTEGER Admin flag
is_superadmin INTEGER Super-admin flag
display_name TEXT Display name (profile)
email TEXT Email address (profile)
bio TEXT User biography (profile)
avatar_path TEXT Path to avatar image
is_public INTEGER 1 = library is public, 0 = private
google_sub TEXT Google account subject ID (unique)
google_email TEXT Google account email

works

The main library table, one row per work per user.

Column Type Description
id BIGSERIAL Primary key
title TEXT Work title (required)
chapter INTEGER Current chapter (default 0)
link TEXT URL to reading platform
status TEXT Reading status (e.g. En cours, Termine)
image_path TEXT Cover image path
reading_type TEXT Type (e.g. Manga, Webtoon)
user_id BIGINT FK Owner
rating INTEGER Rating (default 0)
notes TEXT User notes
updated_at TIMESTAMP Last modification
is_adult INTEGER Adult content flag
catalog_id BIGINT FK Linked catalog entry
anilist_enrich_opt_out INTEGER Opt out of AniList enrichment
parent_work_id BIGINT FK Parent work for series grouping
series_sort INTEGER Sort order within series
notify_new_chapters INTEGER Chapter notification preference
reading_site_id BIGINT FK Associated reading site

catalog

External catalog entries (AniList, MangaDex, manual).

Column Type Description
id BIGSERIAL Primary key
title TEXT Catalog title
reading_type TEXT Type
image_url TEXT Cover image URL
source TEXT Source (anilist, mangadex, manual)
external_id TEXT External identifier
created_at TIMESTAMP Creation date

sessions

Active user sessions.

Column Type Description
id BIGSERIAL Primary key
user_id BIGINT FK Session owner
token_hash TEXT SHA-256 hash of session token (unique)
created_at TIMESTAMP Session creation
last_seen_at TIMESTAMP Last activity
expires_at TIMESTAMP Absolute expiry
ip TEXT Client IP
user_agent TEXT Client user agent
revoked_at TIMESTAMP Revocation time (null if active)

reading_sites

Reading platforms configured by users.

Column Type Description
id BIGSERIAL Primary key
user_id BIGINT FK Owner
name TEXT Site name
base_url TEXT Site base URL
last_probe_at TIMESTAMP Last probe time
probe_status TEXT Status (ok, down, unknown)
probe_http_status INTEGER HTTP status from probe
probe_detail TEXT Probe error details

dismissed_recommendations

Recommendations dismissed by users.

Column Type Description
user_id BIGINT FK User
source TEXT Recommendation source
external_id TEXT External ID of dismissed item
Unique constraint (user_id, source, external_id)

translation_cache

Cached translations from LibreTranslate.

Column Type Description
source_hash TEXT Hash of source text (composite PK)
target_lang TEXT Target language (composite PK)
translated_text TEXT Translated text

csv_import_sessions

Temporary storage for multi-step CSV imports.

oauth_states

CSRF protection for OAuth flows. Includes PKCE code_verifier.

schema_migrations

Tracks applied migration versions.

Indexes

The schema includes the following indexes for query performance:

Index Table Columns
idx_works_user_id works user_id
idx_works_user_status works user_id, status
idx_works_user_type works user_id, reading_type
idx_works_user_updated_at works user_id, updated_at
idx_works_user_title works user_id, title
idx_works_catalog_id works catalog_id
idx_works_parent_work_id works parent_work_id
idx_works_reading_site_id works reading_site_id
idx_catalog_source_external_id catalog source, external_id
idx_users_validated_public users validated, is_public
idx_reading_sites_user_id reading_sites user_id
idx_sessions_user_id sessions user_id
idx_sessions_user_revoked sessions user_id, revoked_at
idx_sessions_expires_at sessions expires_at
idx_dismissed_recommendations_user_source dismissed_recommendations user_id, source
idx_csv_import_sessions_user csv_import_sessions user_id
idx_oauth_states_expires oauth_states expires_at_unix

Full-text search

SQLite (FTS5)

When compiled with FTS5 support, SQLite uses a virtual table for full-text search across work titles, notes, and links. The current implementation uses SQL LIKE patterns as a fallback for broad matching.

PostgreSQL (GIN)

PostgreSQL uses a generated tsvector column with a GIN index:

ALTER TABLE works ADD COLUMN works_fts_document tsvector
    GENERATED ALWAYS AS (
        to_tsvector('simple',
            coalesce(title, '') || ' ' || coalesce(notes, '') || ' ' || coalesce(link, '')
        )
    ) STORED;

CREATE INDEX works_fts_gin ON works USING gin (works_fts_document);

The simple text search configuration is used for language-agnostic tokenization.

Migrations

Schema migrations are versioned and tracked in the schema_migrations table. The current latest version is 11 (LatestSchemaMigrationVersion = 11 in internal/database/migrations.go).

Migrations run automatically at startup via database.EnsureSchema(). Each migration is idempotent and handles both SQLite and PostgreSQL.

SQLite to PostgreSQL migration

A built-in migration tool allows moving from SQLite to PostgreSQL without data loss:

  1. Access: Admin panel > PostgreSQL (requires superadmin role)
  2. Test: verify PostgreSQL connectivity
  3. Migrate: transfers all tables, sequences, and data
  4. Finalize: writes BOOKSTORAGE_POSTGRES_URL to .env and switches the active backend

The migration is handled by internal/database/migrate_sqlite_to_pg.go. The process user must have write permission on the .env file. See Troubleshooting for common issues.

Authentication and Security — Next: understand authentication flows and security measures.

BookStorage Wiki

Clone this wiki locally