A community based topic aggregation platform built on atproto
bretton.dev
1-- +goose Up
2CREATE TABLE community_blocks (
3 id SERIAL PRIMARY KEY,
4 user_did TEXT NOT NULL CHECK (user_did ~ '^did:(plc|web):[a-zA-Z0-9._:%-]+$'),
5 community_did TEXT NOT NULL CHECK (community_did ~ '^did:(plc|web):[a-zA-Z0-9._:%-]+$'),
6 blocked_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
7
8 -- AT-Proto metadata (block record lives in user's repo)
9 -- These are required for atProto record verification and federation
10 record_uri TEXT NOT NULL, -- atProto record identifier (at://user_did/social.coves.community.block/rkey)
11 record_cid TEXT NOT NULL, -- Content address (critical for verification)
12
13 UNIQUE(user_did, community_did)
14);
15
16-- Indexes for efficient queries
17-- Note: UNIQUE constraint on (user_did, community_did) already creates an index for those columns
18CREATE INDEX idx_blocks_user ON community_blocks(user_did);
19CREATE INDEX idx_blocks_community ON community_blocks(community_did);
20CREATE INDEX idx_blocks_record_uri ON community_blocks(record_uri); -- For GetBlockByURI (Jetstream DELETE operations)
21CREATE INDEX idx_blocks_blocked_at ON community_blocks(blocked_at);
22
23-- +goose Down
24DROP INDEX IF EXISTS idx_blocks_blocked_at;
25DROP INDEX IF EXISTS idx_blocks_record_uri;
26DROP INDEX IF EXISTS idx_blocks_community;
27DROP INDEX IF EXISTS idx_blocks_user;
28DROP TABLE IF EXISTS community_blocks;