seshat/backend/database.postgres.sql

CREATE SCHEMA IF NOT EXISTS seshat;

SET
    search_path TO seshat;

DROP TABLE IF EXISTS api_keys;

DROP TABLE IF EXISTS book_statuses;

DROP TABLE IF EXISTS book_origins;

DROP TABLE IF EXISTS refresh_tokens;

DROP TABLE IF EXISTS series_subscriptions;

DROP TABLE IF EXISTS users;

DROP TABLE IF EXISTS books;

DROP TABLE IF EXISTS series;

CREATE TABLE
    series (
        series_id uuid DEFAULT gen_random_uuid (),
        -- 3rd party id for this series.
        provider_series_id text,
        series_title text NOT NULL,
        media_type text,
        -- 3rd party used to fetch the data for this series.
        provider varchar(12) NOT NULL,
        added_at timestamp default NULL,
        PRIMARY KEY (series_id),
        UNIQUE (provider, provider_series_id)
    );

ALTER TABLE series
ALTER COLUMN added_at
SET DEFAULT now ();

CREATE INDEX series_series_title_idx ON series (series_title);

CREATE TABLE
    books (
        book_id uuid DEFAULT gen_random_uuid (),
        -- 3rd party id for this series if applicable.
        provider_series_id text,
        -- 3rd party id for this book.
        provider_book_id text NOT NULL,
        book_title text NOT NULL,
        book_desc text,
        book_volume integer,
        -- 3rd party used to fetch the data for this book.
        provider varchar(12) NOT NULL,
        published_at timestamp default NULL,
        added_at timestamp default NULL,
        PRIMARY KEY (book_id),
        FOREIGN KEY (provider, provider_series_id) REFERENCES series (provider, provider_series_id) ON DELETE CASCADE,
        UNIQUE NULLS NOT DISTINCT (provider_series_id, provider_book_id, book_volume)
    );

ALTER TABLE books
ALTER COLUMN added_at
SET DEFAULT now ();

-- CREATE INDEX books_series_id_idx ON books (series_id);
CREATE INDEX books_provider_provider_series_id_idx ON books (provider, provider_series_id);

-- CREATE INDEX books_isbn_idx ON books USING HASH (isbn);
CREATE INDEX books_book_title_idx ON books (book_title);

CREATE TABLE
    book_origins (
        book_origin_id uuid DEFAULT gen_random_uuid (),
        book_id uuid NOT NULL,
        origin_type integer,
        origin_value text,
        PRIMARY KEY (book_origin_id),
        FOREIGN KEY (book_id) REFERENCES books (book_id) ON DELETE CASCADE,
        UNIQUE (book_id, origin_type, origin_value)
    );

CREATE INDEX book_origins_book_id_idx ON book_origins (book_id);

CREATE INDEX book_origins_type_value_idx ON book_origins (origin_type, origin_value);

CREATE TABLE
    users (
        user_id uuid DEFAULT gen_random_uuid (),
        user_login varchar(24),
        user_name varchar(64),
        password text NOT NULL,
        salt bigint NOT NULL,
        is_admin boolean NOT NULL,
        joined_at timestamp default NULL,
        PRIMARY KEY (user_id),
        UNIQUE (user_login)
    );

ALTER TABLE users
ALTER COLUMN joined_at
SET DEFAULT now ();

CREATE TABLE
    refresh_tokens (
        user_id uuid NOT NULL,
        refresh_token_hash text NOT NULL,
        exp timestamp NOT NULL,
        PRIMARY KEY (user_id, refresh_token_hash),
        FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE
    );

CREATE TABLE
    book_statuses (
        user_id uuid,
        book_id uuid,
        state smallint,
        added_at timestamp default NULL,
        modified_at timestamp default NULL,
        PRIMARY KEY (user_id, book_id),
        FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE,
        FOREIGN KEY (book_id) REFERENCES books (book_id) ON DELETE CASCADE
    );

ALTER TABLE book_statuses
ALTER COLUMN added_at
SET DEFAULT now ();

CREATE INDEX book_statuses_user_id_login_idx ON users (user_id);

CREATE TABLE
    series_subscriptions (
        user_id uuid,
        provider varchar(12) NOT NULL,
        provider_series_id text,
        added_at timestamp default NULL,
        PRIMARY KEY (user_id, provider, provider_series_id),
        FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE,
        FOREIGN KEY (provider, provider_series_id) REFERENCES series (provider, provider_series_id) ON DELETE CASCADE
    );

ALTER TABLE series_subscriptions
ALTER COLUMN added_at
SET DEFAULT now ();

CREATE TABLE
    api_keys (
        user_id uuid,
        api_key char(64),
        added_at timestamp default NULL,
        PRIMARY KEY (user_id, api_key),
        FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE
    );

ALTER TABLE api_keys
ALTER COLUMN added_at
SET DEFAULT now ();

CREATE INDEX api_keys_api_key_idx ON api_keys (api_key);