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.feed.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 -- NO foreign key constraint on voter_did to allow out-of-order indexing from Jetstream
24 -- Vote events may arrive before user events, which is acceptable since:
25 -- 1. Votes are authenticated by the user's PDS (security maintained)
26 -- 2. Orphaned votes from never-indexed users are harmless
27 -- 3. This prevents race conditions in the firehose consumer
28);
29
30-- Indexes for common query patterns
31CREATE INDEX idx_votes_subject ON votes(subject_uri, direction) WHERE deleted_at IS NULL;
32CREATE INDEX idx_votes_voter_subject ON votes(voter_did, subject_uri) WHERE deleted_at IS NULL;
33
34-- Partial unique index: One active vote per user per subject (soft delete aware)
35CREATE UNIQUE INDEX unique_voter_subject_active ON votes(voter_did, subject_uri) WHERE deleted_at IS NULL;
36CREATE INDEX idx_votes_uri ON votes(uri);
37CREATE INDEX idx_votes_voter ON votes(voter_did, created_at DESC);
38
39-- Comment on table
40COMMENT ON TABLE votes IS 'Votes indexed from user repositories via Jetstream firehose consumer';
41COMMENT ON COLUMN votes.uri IS 'AT-URI in format: at://voter_did/social.coves.feed.vote/rkey';
42COMMENT ON COLUMN votes.subject_uri IS 'Strong reference to post/comment being voted on';
43COMMENT ON INDEX unique_voter_subject_active IS 'Ensures one active vote per user per subject (soft delete aware)';
44
45-- +goose Down
46DROP TABLE IF EXISTS votes CASCADE;