otter-space-sdk/pkg/database/migrations/001_inital_database.sql

-- +migrate Up
CREATE TYPE Rating AS ENUM (
    'safe',
    'questionable',
    'explicit'
    );

CREATE TYPE TagType AS ENUM (
    'general',
    'species',
    'character',
    'artist',
    'lore',
    'meta',
    'invalid',
    'copyright'
    );

CREATE TABLE "Post"
(
    id CHAR(25) PRIMARY KEY,
    rating     Rating,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL NULL
);

CREATE TABLE "Source"
(
    id           CHAR(25) PRIMARY KEY,
    display_name TEXT NULL,
    icon         TEXT NULL,
    domain       TEXT NOT NULL UNIQUE,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at   TIMESTAMP NULL
);

CREATE TABLE "Tag"
(
    name       TEXT PRIMARY KEY,
    tag_type   TagType,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL
);

CREATE TABLE "User"
(
    id TEXT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL
);

CREATE TABLE "PostReference"
(
    post_id          TEXT REFERENCES "Post" (id),
    source_id        TEXT REFERENCES "Source" (id),
    url TEXT NOT NULL,
    full_file_url    TEXT,
    preview_file_url TEXT,
    sample_file_url  TEXT,
    source_post_id   TEXT,
    PRIMARY KEY (post_id, source_id, url)
);

CREATE TABLE "TagAlias"
(
    name       TEXT PRIMARY KEY,
    tag_id     TEXT REFERENCES "Tag" (name),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE "TagGroup"
(
    name       TEXT PRIMARY KEY,
    tag_id     TEXT REFERENCES "Tag" (name),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE "UserFavorites"
(
    user_id TEXT REFERENCES "User" (id),
    post_id TEXT REFERENCES "Post" (id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, post_id)
);

CREATE TABLE "UserSource"
(
    user_id                TEXT REFERENCES "User" (id),
    source_id              TEXT REFERENCES "Source" (id),
    scrape_time_interval   INT,
    account_username       TEXT,
    account_id             TEXT,
    last_scrape_time       TIMESTAMP,
    account_validate       BOOL DEFAULT FALSE,
    account_validation_key CHAR(25),
    PRIMARY KEY (user_id, source_id),
    UNIQUE (source_id, account_username, account_id)
);

CREATE TABLE "post_tags"
(
    post_id  TEXT REFERENCES "Post" (id),
    tag_name TEXT REFERENCES "Tag" (name),
    PRIMARY KEY (post_id, tag_name)
);