db.sql

-- stores hits to the ping service
CREATE TABLE pings (
    -- unique id of the ping
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

    -- the unix timestamp of the ping
    timestamp INTEGER NOT NULL,

    -- the ip address of the ping client
    remote_ip TEXT NOT NULL
) STRICT;

-- stores logical user accounts
CREATE TABLE accounts (
    -- unique id of the account, must not be reused
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

    -- the creation timestamp of the account
    created_at INTEGER NOT NULL,

    -- username of the account, must be unique since it is it's public identifier
    username TEXT UNIQUE NOT NULL,

    -- display name of the account, shown in profile
    display_name TEXT NOT NULL,

    -- email of the account, must be unique to prevent account spam
    email TEXT UNIQUE NOT NULL,

    -- bio of the account, optional
    bio TEXT,

    -- display organisation of the account, optional
    organisation TEXT,

    -- website of the account, optional
    website TEXT
) STRICT;

-- identities, used to link accounts to login mehtods
CREATE TABLE identities (
    -- id of the account this identity belongs to
    account INTEGER NOT NULL,

    -- kind of the identity, e.g. "local"
    kind TEXT NOT NULL,

    -- hash of the user password, if kind is "local"
    password TEXT,

    PRIMARY KEY (account, kind),
    FOREIGN KEY (account) REFERENCES accounts(id)
) STRICT;

-- notifications sent to an account in response to an event
CREATE TABLE notifications (
    -- unique id of the notification
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

    -- the account this notification is for
    account INTEGER NOT NULL,

    -- the timestamp of the notification
    timestamp INTEGER NOT NULL,

    -- if the notification is marked done or not, "yes" or "no"
    done TEXT NOT NULL,

    -- the kind of the notification, e.g. "snippet_update", "snippet_comment"
    kind TEXT NOT NULL,

    -- the id of the snippet, if kind is "snippet_*"
    c_snippet INTEGER,

    -- the id of the snippet comment, if kind is "snippet_comment"
    c_snippet_comment INTEGER,

    FOREIGN KEY (account) REFERENCES accounts(id),
    FOREIGN KEY (c_snippet) REFERENCES snippets(id),
    FOREIGN KEY (c_snippet_comment) REFERENCES snippet_comments(id)
) STRICT;

-- stores a snippet and its metadata
CREATE TABLE snippets (
    -- the id of the snippet
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

    -- the name of the snippet
    name TEXT NOT NULL,

    -- the author account of the snippet
    author INTEGER NOT NULL,

    -- the visibility of the snippet, "public", "hidden" or "private"
    visibility TEXT NOT NULL,

    -- the lang of the snippet, optional
    lang TEXT,

    UNIQUE (name, author),
    FOREIGN KEY (author) REFERENCES accounts(id)
) STRICT;

-- stores a version of a snippet
CREATE TABLE snippet_versions (
    -- the snippet this is a version of
    snippet INTEGER NOT NULL,

    -- the version of the snippet
    version INTEGER NOT NULL,

    -- the timestamp of the version
    timestamp INTEGER NOT NULL,

    -- the content of the snippet
    content TEXT NOT NULL,

    PRIMARY KEY (snippet, version),
    FOREIGN KEY (snippet) REFERENCES snippets(id)
) STRICT;

-- a star on a snippet
CREATE TABLE snippet_stars (
    -- the snippet which is starred
    snippet INTEGER NOT NULL,

    -- the account which starred the snippet
    account INTEGER NOT NULL,

    PRIMARY KEY (snippet, account),
    FOREIGN KEY (snippet) REFERENCES snippets(id),
    FOREIGN KEY (account) REFERENCES accounts(id)
) STRICT;

-- a notification subscription to a snippet
CREATE TABLE snippet_subscriptions (
    -- the snippet which is subscribed to
    snippet INTEGER NOT NULL,

    -- the account which is subscribed
    account INTEGER NOT NULL,

    PRIMARY KEY (snippet, account),
    FOREIGN KEY (snippet) REFERENCES snippets(id),
    FOREIGN KEY (account) REFERENCES accounts(id)
) STRICT;

-- a text comment made by a user on a snippet (not a specific revision)
CREATE TABLE snippet_comments (
    -- the id of the comment
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

    -- the snippet the comment is made on
    snippet INTEGER NOT NULL,

    -- the timestamp at which the comment was made
    timestamp INTEGER NOT NULL,

    -- the author account of the comment
    author INTEGER NOT NULL,

    FOREIGN KEY (snippet) REFERENCES snippets(id),
    FOREIGN KEY (author) REFERENCES accounts(id)
) STRICT;

-- stores versioned content of a snippet comment
CREATE TABLE snippet_comment_versions (
    -- the comment this is a version of
    comment INTEGER NOT NULL,

    -- the version of the comment
    version INTEGER NOT NULL,

    -- the timestamp at which the comment was made
    timestamp INTEGER NOT NULL,

    -- the content of the comment
    content TEXT NOT NULL,

    PRIMARY KEY (comment, version),
    FOREIGN KEY (comment) REFERENCES snippet_comments(id)
) STRICT;

-- contains one-time-use password reset tokens used for reset links
CREATE TABLE password_resets (
    -- the secret token for the reset link
    token TEXT PRIMARY KEY NOT NULL,

    -- the account this reset link is for
    account INTEGER NOT NULL,

    -- the timestamp of the reset link
    created_at INTEGER NOT NULL,

    FOREIGN KEY (account) REFERENCES accounts(id)
) STRICT;

-- contains one-time-use invite tokens
CREATE TABLE invite_links (
    -- the secret token for the invite
    token TEXT PRIMARY KEY NOT NULL,

    -- the timestamp of the invite
    created_at INTEGER NOT NULL
) STRICT;

-- stores queued background jobs
CREATE TABLE job_queue (
    -- unique id of the job
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

    -- the arguments of the job
    args TEXT NOT NULL,

    -- timestamp of when the job was scheduled
    scheduled INTEGER NOT NULL,

    -- timestamp of when the job was completed, null if not completed yet
    completed INTEGER,

    -- the state of the job, "queued", "running", "failed" or "completed"
    state TEXT NOT NULL,

    -- the last refresh timestamp of the lock
    active INTEGER,

    -- the output of the job, null if not completed yet
    output TEXT
) STRICT;