bretton.dev
A community based topic aggregation platform built on atproto
1-- +goose Up
2CREATE TABLE community_suggestions (
3 id BIGSERIAL PRIMARY KEY,
4 title TEXT NOT NULL,
5 description TEXT NOT NULL,
6 submitter_did TEXT NOT NULL,
7 status TEXT NOT NULL DEFAULT 'open',
8 vote_count INTEGER NOT NULL DEFAULT 0,
9 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
10 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
11 CONSTRAINT valid_suggestion_status CHECK (status IN ('open', 'under_review', 'approved', 'declined')),
12 CONSTRAINT title_not_empty CHECK (LENGTH(TRIM(title)) > 0),
13 CONSTRAINT title_max_length CHECK (LENGTH(title) <= 200),
14 CONSTRAINT description_max_length CHECK (LENGTH(description) <= 5000),
15 CONSTRAINT description_not_empty CHECK (LENGTH(TRIM(description)) > 0)
16);
17
18CREATE TABLE suggestion_votes (
19 id BIGSERIAL PRIMARY KEY,
20 suggestion_id BIGINT NOT NULL REFERENCES community_suggestions(id) ON DELETE CASCADE,
21 voter_did TEXT NOT NULL,
22 value SMALLINT NOT NULL,
23 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
24 CONSTRAINT valid_vote_value CHECK (value IN (1, -1)),
25 CONSTRAINT unique_suggestion_voter UNIQUE (suggestion_id, voter_did)
26);
27
28-- Indexes
29CREATE INDEX idx_suggestions_status ON community_suggestions(status);
30CREATE INDEX idx_suggestions_created_at ON community_suggestions(created_at DESC);
31CREATE INDEX idx_suggestions_vote_count ON community_suggestions(vote_count DESC);
32CREATE INDEX idx_suggestions_submitter ON community_suggestions(submitter_did);
33CREATE INDEX idx_suggestion_votes_suggestion ON suggestion_votes(suggestion_id);
34CREATE INDEX idx_suggestion_votes_voter ON suggestion_votes(voter_did);
35
36-- +goose Down
37DROP TABLE IF EXISTS suggestion_votes;
38DROP TABLE IF EXISTS community_suggestions;