A community based topic aggregation platform built on atproto
bretton.dev
1-- +goose Up
2-- Create posts table for AppView indexing
3-- Posts are indexed from the firehose after being written to community repositories
4CREATE TABLE posts (
5 id BIGSERIAL PRIMARY KEY,
6 uri TEXT UNIQUE NOT NULL, -- AT-URI (at://community_did/social.coves.post.record/rkey)
7 cid TEXT NOT NULL, -- Content ID
8 rkey TEXT NOT NULL, -- Record key (TID)
9 author_did TEXT NOT NULL, -- Author's DID (from record metadata)
10 community_did TEXT NOT NULL, -- Community DID (from AT-URI repo field)
11
12 -- Content (all nullable per lexicon)
13 title TEXT, -- Post title
14 content TEXT, -- Post content/body
15 content_facets JSONB, -- Rich text facets (app.bsky.richtext.facet)
16 embed JSONB, -- Embedded content (images, video, external, record)
17 content_labels TEXT[], -- Self-applied labels (nsfw, spoiler, violence)
18
19 -- Timestamps
20 created_at TIMESTAMPTZ NOT NULL, -- Author's timestamp from record
21 edited_at TIMESTAMPTZ, -- Last edit timestamp (future)
22 indexed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- When indexed by AppView
23 deleted_at TIMESTAMPTZ, -- Soft delete (for firehose delete events)
24
25 -- Stats (denormalized for performance)
26 upvote_count INT NOT NULL DEFAULT 0,
27 downvote_count INT NOT NULL DEFAULT 0,
28 score INT NOT NULL DEFAULT 0, -- upvote_count - downvote_count (for sorting)
29 comment_count INT NOT NULL DEFAULT 0,
30
31 -- Foreign keys
32 CONSTRAINT fk_author FOREIGN KEY (author_did) REFERENCES users(did) ON DELETE CASCADE,
33 CONSTRAINT fk_community FOREIGN KEY (community_did) REFERENCES communities(did) ON DELETE CASCADE
34);
35
36-- Indexes for common query patterns
37CREATE INDEX idx_posts_community_created ON posts(community_did, created_at DESC) WHERE deleted_at IS NULL;
38CREATE INDEX idx_posts_community_score ON posts(community_did, score DESC, created_at DESC) WHERE deleted_at IS NULL;
39CREATE INDEX idx_posts_author ON posts(author_did, created_at DESC);
40CREATE INDEX idx_posts_uri ON posts(uri);
41
42-- Index for full-text search on content (future)
43-- CREATE INDEX idx_posts_content_search ON posts USING gin(to_tsvector('english', content)) WHERE deleted_at IS NULL;
44
45-- Comment on table
46COMMENT ON TABLE posts IS 'Posts indexed from community repositories via Jetstream firehose consumer';
47COMMENT ON COLUMN posts.uri IS 'AT-URI in format: at://community_did/social.coves.post.record/rkey';
48COMMENT ON COLUMN posts.score IS 'Computed as upvote_count - downvote_count for ranking algorithms';
49
50-- +goose Down
51DROP TABLE IF EXISTS posts CASCADE;