Skip to content

Instantly share code, notes, and snippets.

@xacrimon
Created September 12, 2022 15:43
Show Gist options
  • Save xacrimon/ac9f3f479263cbb8eade00fdc3727139 to your computer and use it in GitHub Desktop.
Save xacrimon/ac9f3f479263cbb8eade00fdc3727139 to your computer and use it in GitHub Desktop.
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment