1CREATE TABLE users (
  2    id TEXT PRIMARY KEY CHECK (length(id) = 36),
  3    username TEXT NOT NULL UNIQUE
  4        CHECK (
  5            length(username) BETWEEN 1 AND 32
  6            AND username = lower(username)
  7            AND substr(username, 1, 1) GLOB '[a-z0-9]'
  8            AND username NOT GLOB '*[^a-z0-9._-]*'
  9        ),
 10    email TEXT NOT NULL UNIQUE
 11        CHECK (length(email) > 0 AND email = lower(email)),
 12    email_verified INTEGER NOT NULL CHECK (email_verified IN (0, 1)),
 13    display_name TEXT NOT NULL
 14        CHECK (length(CAST(display_name AS BLOB)) <= 128),
 15    password_hash BLOB NOT NULL CHECK (length(password_hash) > 0),
 16    disabled INTEGER NOT NULL CHECK (disabled IN (0, 1)),
 17    uid_number INTEGER NOT NULL UNIQUE
 18        CHECK (uid_number BETWEEN 10000 AND 19999),
 19    created_at INTEGER NOT NULL,
 20    last_login_at INTEGER
 21) STRICT;
 22
 23CREATE TABLE groups (
 24    id TEXT PRIMARY KEY CHECK (length(id) = 36),
 25    name TEXT NOT NULL UNIQUE
 26        CHECK (
 27            length(name) BETWEEN 1 AND 32
 28            AND name = lower(name)
 29            AND substr(name, 1, 1) GLOB '[a-z0-9]'
 30            AND name NOT GLOB '*[^a-z0-9_-]*'
 31        ),
 32    description TEXT NOT NULL,
 33    gid_number INTEGER NOT NULL UNIQUE
 34        CHECK (gid_number BETWEEN 20000 AND 2147483647),
 35    created_at INTEGER NOT NULL
 36) STRICT;
 37
 38CREATE TABLE memberships (
 39    user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 40    group_id TEXT NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
 41    PRIMARY KEY (user_id, group_id)
 42) STRICT;
 43
 44CREATE INDEX memberships_group_id_idx ON memberships(group_id, user_id);
 45
 46CREATE TABLE google_links (
 47    user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
 48    google_sub TEXT NOT NULL UNIQUE CHECK (length(google_sub) > 0),
 49    google_email_snapshot TEXT NOT NULL
 50        CHECK (
 51            length(google_email_snapshot) > 0
 52            AND google_email_snapshot = lower(google_email_snapshot)
 53        ),
 54    linked_at INTEGER NOT NULL
 55) STRICT;
 56
 57CREATE TABLE invitations (
 58    id TEXT PRIMARY KEY CHECK (length(id) = 36),
 59    token_hash BLOB NOT NULL UNIQUE CHECK (length(token_hash) > 0),
 60    email TEXT CHECK (email IS NULL OR (length(email) > 0 AND email = lower(email))),
 61    created_by TEXT REFERENCES users(id) ON DELETE SET NULL,
 62    created_at INTEGER NOT NULL,
 63    expires_at INTEGER NOT NULL,
 64    used_by TEXT REFERENCES users(id) ON DELETE SET NULL,
 65    used_at INTEGER,
 66    revoked_at INTEGER,
 67    CHECK (used_at IS NULL OR revoked_at IS NULL)
 68) STRICT;
 69
 70CREATE INDEX invitations_created_at_idx ON invitations(created_at DESC, id);
 71
 72CREATE TABLE audit_log (
 73    id INTEGER PRIMARY KEY AUTOINCREMENT,
 74    actor TEXT NOT NULL CHECK (length(actor) > 0),
 75    action TEXT NOT NULL CHECK (length(action) > 0),
 76    subject TEXT NOT NULL CHECK (length(subject) > 0),
 77    occurred_at INTEGER NOT NULL,
 78    detail TEXT NOT NULL
 79) STRICT;
 80
 81CREATE INDEX audit_log_occurred_at_idx ON audit_log(occurred_at DESC, id DESC);
 82CREATE INDEX audit_log_actor_idx ON audit_log(actor, occurred_at DESC, id DESC);
 83CREATE INDEX audit_log_action_idx ON audit_log(action, occurred_at DESC, id DESC);
 84CREATE INDEX audit_log_subject_idx ON audit_log(subject, occurred_at DESC, id DESC);
 85
 86CREATE TABLE forward_sessions (
 87    id TEXT PRIMARY KEY CHECK (length(id) > 0),
 88    user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 89    app_domain TEXT NOT NULL CHECK (length(app_domain) > 0),
 90    created_at INTEGER NOT NULL,
 91    expires_at INTEGER NOT NULL,
 92    revoked INTEGER NOT NULL CHECK (revoked IN (0, 1))
 93) STRICT;
 94
 95CREATE INDEX forward_sessions_user_id_idx
 96    ON forward_sessions(user_id, created_at DESC, id);
 97
 98CREATE TABLE settings (
 99    id INTEGER PRIMARY KEY CHECK (id = 1),
100    config_commit TEXT CHECK (config_commit IS NULL OR length(config_commit) > 0),
101    userbase_commit TEXT CHECK (userbase_commit IS NULL OR length(userbase_commit) > 0),
102    next_uid_number INTEGER NOT NULL
103        CHECK (next_uid_number BETWEEN 10000 AND 20000),
104    next_gid_number INTEGER NOT NULL
105        CHECK (next_gid_number BETWEEN 20000 AND 2147483648)
106) STRICT;
107
108INSERT INTO settings (
109    id,
110    config_commit,
111    userbase_commit,
112    next_uid_number,
113    next_gid_number
114) VALUES (1, NULL, NULL, 10000, 20000);