bretton.dev
A community based topic aggregation platform built on atproto
1-- +goose Up
2CREATE TABLE user_blocks (
3 id SERIAL PRIMARY KEY,
4 blocker_did TEXT NOT NULL CHECK (blocker_did ~ '^did:(plc|web):[a-zA-Z0-9._:%-]+$'),
5 blocked_did TEXT NOT NULL CHECK (blocked_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 blocker's repo)
9 -- These are required for atProto record verification and federation
10 record_uri TEXT NOT NULL, -- atProto record identifier (at://blocker_did/social.coves.actor.block/rkey)
11 record_cid TEXT NOT NULL, -- Content address (critical for verification)
12
13 UNIQUE(blocker_did, blocked_did)
14);
15
16-- Indexes for efficient queries
17-- Note: UNIQUE constraint on (blocker_did, blocked_did) already covers blocker_did as leading column
18CREATE INDEX idx_user_blocks_blocked ON user_blocks(blocked_did);
19CREATE UNIQUE INDEX idx_user_blocks_record_uri ON user_blocks(record_uri); -- For GetBlockByURI (Jetstream DELETE operations)
20
21-- +goose Down
22DROP INDEX IF EXISTS idx_user_blocks_record_uri;
23DROP INDEX IF EXISTS idx_user_blocks_blocked;
24DROP TABLE IF EXISTS user_blocks;