codegirl007
d3ce29031f
CI / test (pull_request) Successful in 8m23s
Render reply trees with permission-aware inline controls, and move hunt, profile, voting, submission, and hiding flows to post storage.
89 lines
2.6 KiB
SQL
89 lines
2.6 KiB
SQL
CREATE TABLE IF NOT EXISTS users (
|
|
id TEXT PRIMARY KEY,
|
|
username TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('user', 'admin')),
|
|
email TEXT NOT NULL DEFAULT '',
|
|
avatar_url TEXT NOT NULL DEFAULT '',
|
|
state TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS users_email_lower_uidx
|
|
ON users (lower(email))
|
|
WHERE email <> '';
|
|
|
|
CREATE TABLE IF NOT EXISTS questions (
|
|
id TEXT PRIMARY KEY,
|
|
author_id TEXT NOT NULL REFERENCES users(id),
|
|
title TEXT NOT NULL,
|
|
body TEXT NOT NULL,
|
|
city TEXT NOT NULL DEFAULT '',
|
|
hunt_date TEXT NOT NULL,
|
|
hidden INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_questions_hunt_date ON questions(hunt_date, hidden);
|
|
|
|
CREATE TABLE IF NOT EXISTS votes (
|
|
user_id TEXT NOT NULL REFERENCES users(id),
|
|
question_id TEXT NOT NULL REFERENCES questions(id) ON DELETE CASCADE,
|
|
value INTEGER NOT NULL CHECK (value IN (-1, 1)),
|
|
PRIMARY KEY (user_id, question_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS answers (
|
|
question_id TEXT PRIMARY KEY REFERENCES questions(id) ON DELETE CASCADE,
|
|
author_id TEXT NOT NULL REFERENCES users(id),
|
|
body TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS posts (
|
|
id TEXT PRIMARY KEY,
|
|
parent_id TEXT REFERENCES posts(id) ON DELETE CASCADE,
|
|
author_id TEXT NOT NULL REFERENCES users(id),
|
|
title TEXT NOT NULL DEFAULT '',
|
|
body TEXT NOT NULL,
|
|
city TEXT NOT NULL DEFAULT '',
|
|
post_date TEXT NOT NULL DEFAULT '',
|
|
post_state TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
CONSTRAINT posts_shape_check CHECK (
|
|
(parent_id IS NULL AND title <> '' AND post_date <> '')
|
|
OR
|
|
(parent_id IS NOT NULL AND title = '' AND city = '' AND post_date = '')
|
|
)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_posts_parent_created
|
|
ON posts(parent_id, created_at, id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_posts_author_created
|
|
ON posts(author_id, created_at DESC, id DESC);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_posts_root_date
|
|
ON posts(post_date, post_state)
|
|
WHERE parent_id IS NULL;
|
|
|
|
CREATE TABLE IF NOT EXISTS post_votes (
|
|
user_id TEXT NOT NULL REFERENCES users(id),
|
|
post_id TEXT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
|
|
value INTEGER NOT NULL CHECK (value IN (-1, 1)),
|
|
PRIMARY KEY (user_id, post_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_post_votes_post_id
|
|
ON post_votes(post_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
token TEXT PRIMARY KEY,
|
|
data BYTEA NOT NULL,
|
|
expiry TIMESTAMPTZ NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS sessions_expiry_idx ON sessions (expiry);
|