Mobile support to come.
..20260117042027_create_users.down.sql20260117042027_create_users.up.sql20260117043157_create_organizations.down.sql20260117043157_create_organizations.up.sql20260118222626_create_repositories.down.sql20260118222626_create_repositories.up.sql20260125182548_create_questions.down.sql20260125182548_create_questions.up.sql20260126215900_create_votes.down.sql20260126215900_create_votes.up.sql20260201050312_create_oauth.down.sql20260201050312_create_oauth.up.sql20260206190315_create_commits.down.sql20260206190315_create_commits.up.sql20260207120000_disable_auth_user_trigger.down.sql20260207120000_disable_auth_user_trigger.up.sql
-- Create questions table
CREATE TABLE IF NOT EXISTS questions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
number INTEGER NOT NULL,
author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
repository_id UUID NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
title VARCHAR(255) NOT NULL,
body TEXT NOT NULL,
upvote INTEGER NOT NULL DEFAULT 0,
impression INTEGER NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
UNIQUE(repository_id, number)
);
-- Create indexes for questions
CREATE INDEX idx_questions_author_id ON questions(author_id);
CREATE INDEX idx_questions_repository_id ON questions(repository_id);
CREATE INDEX idx_questions_created_at ON questions(created_at DESC);
-- Create answers table
CREATE TABLE IF NOT EXISTS answers (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
question_id UUID NOT NULL REFERENCES questions(id) ON DELETE CASCADE,
author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
body TEXT NOT NULL,
upvote INTEGER NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Create indexes for answers
CREATE INDEX idx_answers_question_id ON answers(question_id);
CREATE INDEX idx_answers_author_id ON answers(author_id);
CREATE INDEX idx_answers_created_at ON answers(created_at DESC);
-- Create comments table (can be on questions or answers via parent_id)
CREATE TABLE IF NOT EXISTS comments (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
parent_id UUID NOT NULL,
author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
body TEXT NOT NULL,
upvote INTEGER NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Create indexes for comments
CREATE INDEX idx_comments_parent_id ON comments(parent_id);
CREATE INDEX idx_comments_author_id ON comments(author_id);
CREATE INDEX idx_comments_created_at ON comments(created_at DESC);