A community based topic aggregation platform built on atproto
bretton.dev
1-- +goose Up
2-- Create comments table for AppView indexing
3-- Comments are indexed from the firehose after being written to user repositories
4CREATE TABLE comments (
5 id BIGSERIAL PRIMARY KEY,
6 uri TEXT UNIQUE NOT NULL, -- AT-URI (at://commenter_did/social.coves.feed.comment/rkey)
7 cid TEXT NOT NULL, -- Content ID
8 rkey TEXT NOT NULL, -- Record key (TID)
9 commenter_did TEXT NOT NULL, -- User who commented (from AT-URI repo field)
10
11 -- Threading structure (reply references)
12 root_uri TEXT NOT NULL, -- Strong reference to original post (at://...)
13 root_cid TEXT NOT NULL, -- CID of root post (version pinning)
14 parent_uri TEXT NOT NULL, -- Strong reference to immediate parent (post or comment)
15 parent_cid TEXT NOT NULL, -- CID of parent (version pinning)
16
17 -- Content (content is required per lexicon, others optional)
18 content TEXT NOT NULL, -- Comment text (max 3000 graphemes, 30000 bytes)
19 content_facets JSONB, -- Rich text facets (social.coves.richtext.facet)
20 embed JSONB, -- Embedded content (images, quoted posts)
21 content_labels JSONB, -- Self-applied labels (com.atproto.label.defs#selfLabels)
22 langs TEXT[], -- Languages (ISO 639-1, max 3)
23
24 -- Timestamps
25 created_at TIMESTAMPTZ NOT NULL, -- Commenter's timestamp from record
26 indexed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- When indexed by AppView
27 deleted_at TIMESTAMPTZ, -- Soft delete (for firehose delete events)
28
29 -- Stats (denormalized for performance)
30 upvote_count INT NOT NULL DEFAULT 0, -- Comments can be voted on (per vote lexicon)
31 downvote_count INT NOT NULL DEFAULT 0,
32 score INT NOT NULL DEFAULT 0, -- upvote_count - downvote_count (for sorting)
33 reply_count INT NOT NULL DEFAULT 0 -- Number of direct replies to this comment
34
35 -- NO foreign key constraint on commenter_did to allow out-of-order indexing from Jetstream
36 -- Comment events may arrive before user events, which is acceptable since:
37 -- 1. Comments are authenticated by the user's PDS (security maintained)
38 -- 2. Orphaned comments from never-indexed users are harmless
39 -- 3. This prevents race conditions in the firehose consumer
40);
41
42-- Indexes for threading queries (most important for comment UX)
43CREATE INDEX idx_comments_root ON comments(root_uri, created_at DESC) WHERE deleted_at IS NULL;
44CREATE INDEX idx_comments_parent ON comments(parent_uri, created_at DESC) WHERE deleted_at IS NULL;
45CREATE INDEX idx_comments_parent_score ON comments(parent_uri, score DESC, created_at DESC) WHERE deleted_at IS NULL;
46
47-- Indexes for user queries
48CREATE INDEX idx_comments_commenter ON comments(commenter_did, created_at DESC);
49CREATE INDEX idx_comments_uri ON comments(uri);
50
51-- Index for vote targeting (when votes target comments)
52CREATE INDEX idx_comments_uri_active ON comments(uri) WHERE deleted_at IS NULL;
53
54-- Comment on table
55COMMENT ON TABLE comments IS 'Comments indexed from user repositories via Jetstream firehose consumer';
56COMMENT ON COLUMN comments.uri IS 'AT-URI in format: at://commenter_did/social.coves.feed.comment/rkey';
57COMMENT ON COLUMN comments.root_uri IS 'Strong reference to the original post that started the thread';
58COMMENT ON COLUMN comments.parent_uri IS 'Strong reference to immediate parent (post or comment)';
59COMMENT ON COLUMN comments.score IS 'Computed as upvote_count - downvote_count for ranking replies';
60COMMENT ON COLUMN comments.content_labels IS 'Self-applied labels per com.atproto.label.defs#selfLabels (JSONB: {"values":[{"val":"nsfw","neg":false}]})';
61
62-- +goose Down
63DROP TABLE IF EXISTS comments CASCADE;