A community based topic aggregation platform built on atproto
bretton.dev
1-- +goose Up
2-- Create votes table for AppView indexing
3-- Votes are indexed from the firehose after being written to user repositories
4CREATE TABLE votes (
5 id BIGSERIAL PRIMARY KEY,
6 uri TEXT UNIQUE NOT NULL, -- AT-URI (at://voter_did/social.coves.interaction.vote/rkey)
7 cid TEXT NOT NULL, -- Content ID
8 rkey TEXT NOT NULL, -- Record key (TID)
9 voter_did TEXT NOT NULL, -- User who voted (from AT-URI repo field)
10
11 -- Subject (strong reference to post/comment)
12 subject_uri TEXT NOT NULL, -- AT-URI of voted item
13 subject_cid TEXT NOT NULL, -- CID of voted item (strong reference)
14
15 -- Vote data
16 direction TEXT NOT NULL CHECK (direction IN ('up', 'down')),
17
18 -- Timestamps
19 created_at TIMESTAMPTZ NOT NULL, -- Voter's timestamp from record
20 indexed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- When indexed by AppView
21 deleted_at TIMESTAMPTZ, -- Soft delete (for firehose delete events)
22
23 -- Foreign keys
24 CONSTRAINT fk_voter FOREIGN KEY (voter_did) REFERENCES users(did) ON DELETE CASCADE
25);
26
27-- Indexes for common query patterns
28CREATE INDEX idx_votes_subject ON votes(subject_uri, direction) WHERE deleted_at IS NULL;
29CREATE INDEX idx_votes_voter_subject ON votes(voter_did, subject_uri) WHERE deleted_at IS NULL;
30
31-- Partial unique index: One active vote per user per subject (soft delete aware)
32CREATE UNIQUE INDEX unique_voter_subject_active ON votes(voter_did, subject_uri) WHERE deleted_at IS NULL;
33CREATE INDEX idx_votes_uri ON votes(uri);
34CREATE INDEX idx_votes_voter ON votes(voter_did, created_at DESC);
35
36-- Comment on table
37COMMENT ON TABLE votes IS 'Votes indexed from user repositories via Jetstream firehose consumer';
38COMMENT ON COLUMN votes.uri IS 'AT-URI in format: at://voter_did/social.coves.interaction.vote/rkey';
39COMMENT ON COLUMN votes.subject_uri IS 'Strong reference to post/comment being voted on';
40COMMENT ON INDEX unique_voter_subject_active IS 'Ensures one active vote per user per subject (soft delete aware)';
41
42-- +goose Down
43DROP TABLE IF EXISTS votes CASCADE;