A community based topic aggregation platform built on atproto
bretton.dev
1-- +goose Up
2-- Enable pg_trgm extension for fuzzy text search
3CREATE EXTENSION IF NOT EXISTS pg_trgm;
4
5-- Communities table: stores community metadata indexed from firehose
6CREATE TABLE communities (
7 id SERIAL PRIMARY KEY,
8 did TEXT UNIQUE NOT NULL, -- Community DID (did:coves:xxx or did:plc:xxx)
9 handle TEXT UNIQUE NOT NULL, -- Scoped handle (!gaming@coves.social)
10 name TEXT NOT NULL, -- Short community name (local part of handle)
11 display_name TEXT, -- Display name for UI
12 description TEXT, -- Community description
13 description_facets JSONB, -- Rich text annotations
14 avatar_cid TEXT, -- CID of avatar image blob
15 banner_cid TEXT, -- CID of banner image blob
16
17 -- Ownership & hosting (V2: community owns its own repo)
18 owner_did TEXT NOT NULL, -- V1: instance DID, V2: same as did (self-owned)
19 created_by_did TEXT NOT NULL, -- DID of user who created community
20 hosted_by_did TEXT NOT NULL, -- DID of hosting instance
21
22 -- V2: PDS Account Credentials (community has its own PDS account)
23 pds_email TEXT, -- System email for community PDS account
24 pds_password_hash TEXT, -- bcrypt hash for re-authentication
25 pds_access_token TEXT, -- JWT for API calls (expires)
26 pds_refresh_token TEXT, -- For refreshing sessions
27 pds_url TEXT DEFAULT 'http://localhost:2583', -- PDS hosting this community's repo
28
29 -- Visibility & federation
30 visibility TEXT NOT NULL DEFAULT 'public' CHECK (visibility IN ('public', 'unlisted', 'private')),
31 allow_external_discovery BOOLEAN NOT NULL DEFAULT true,
32
33 -- Moderation
34 moderation_type TEXT CHECK (moderation_type IN ('moderator', 'sortition')),
35 content_warnings TEXT[], -- Array of content warning types
36
37 -- Statistics (cached counts)
38 member_count INTEGER DEFAULT 0,
39 subscriber_count INTEGER DEFAULT 0,
40 post_count INTEGER DEFAULT 0,
41
42 -- Federation metadata (for future cross-platform support)
43 federated_from TEXT, -- 'lemmy', 'coves', etc.
44 federated_id TEXT, -- Original ID on federated platform
45
46 -- Timestamps
47 created_at TIMESTAMP WITH TIME ZONE NOT NULL,
48 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
49
50 -- AT-Proto metadata
51 record_uri TEXT, -- AT-URI of the community profile record
52 record_cid TEXT -- CID of the community profile record
53);
54
55-- Indexes for efficient queries
56CREATE INDEX idx_communities_handle ON communities(handle);
57CREATE INDEX idx_communities_visibility ON communities(visibility);
58CREATE INDEX idx_communities_hosted_by ON communities(hosted_by_did);
59CREATE INDEX idx_communities_created_by ON communities(created_by_did);
60CREATE INDEX idx_communities_created_at ON communities(created_at);
61CREATE INDEX idx_communities_name_trgm ON communities USING gin(name gin_trgm_ops); -- For fuzzy search
62CREATE INDEX idx_communities_description_trgm ON communities USING gin(description gin_trgm_ops);
63CREATE INDEX idx_communities_pds_email ON communities(pds_email); -- V2: For credential lookups
64
65-- Security comments for V2 credentials
66COMMENT ON COLUMN communities.pds_password_hash IS 'V2: bcrypt hash - NEVER return in API responses';
67COMMENT ON COLUMN communities.pds_access_token IS 'V2: JWT - rotate frequently, NEVER log';
68COMMENT ON COLUMN communities.pds_refresh_token IS 'V2: Refresh token - NEVER log or expose in APIs';
69
70-- Subscriptions table: lightweight feed following
71CREATE TABLE community_subscriptions (
72 id SERIAL PRIMARY KEY,
73 user_did TEXT NOT NULL,
74 community_did TEXT NOT NULL REFERENCES communities(did) ON DELETE CASCADE,
75 subscribed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
76
77 -- AT-Proto metadata (subscription is a record in user's repo)
78 record_uri TEXT, -- AT-URI of the subscription record
79 record_cid TEXT, -- CID of the subscription record
80
81 UNIQUE(user_did, community_did)
82);
83
84-- Indexes for subscription queries
85CREATE INDEX idx_subscriptions_user ON community_subscriptions(user_did);
86CREATE INDEX idx_subscriptions_community ON community_subscriptions(community_did);
87CREATE INDEX idx_subscriptions_user_community ON community_subscriptions(user_did, community_did); -- Composite index for GetSubscription
88CREATE INDEX idx_subscriptions_subscribed_at ON community_subscriptions(subscribed_at);
89
90-- Memberships table: active participation & reputation tracking
91CREATE TABLE community_memberships (
92 id SERIAL PRIMARY KEY,
93 user_did TEXT NOT NULL,
94 community_did TEXT NOT NULL REFERENCES communities(did) ON DELETE CASCADE,
95
96 -- Reputation system
97 reputation_score INTEGER DEFAULT 0, -- Gained through participation
98 contribution_count INTEGER DEFAULT 0, -- Total posts + comments + actions
99
100 -- Activity tracking
101 joined_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
102 last_active_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
103
104 -- Moderation status
105 is_banned BOOLEAN DEFAULT false,
106 is_moderator BOOLEAN DEFAULT false,
107
108 UNIQUE(user_did, community_did)
109);
110
111-- Indexes for membership queries
112CREATE INDEX idx_memberships_user ON community_memberships(user_did);
113CREATE INDEX idx_memberships_community ON community_memberships(community_did);
114CREATE INDEX idx_memberships_reputation ON community_memberships(community_did, reputation_score DESC);
115CREATE INDEX idx_memberships_last_active ON community_memberships(last_active_at);
116
117-- Community moderation actions table (V2 feature, schema prepared)
118CREATE TABLE community_moderation (
119 id SERIAL PRIMARY KEY,
120 community_did TEXT NOT NULL REFERENCES communities(did) ON DELETE CASCADE,
121 action TEXT NOT NULL CHECK (action IN ('delist', 'quarantine', 'remove')),
122 reason TEXT,
123 instance_did TEXT NOT NULL, -- Which instance took this action
124 broadcast BOOLEAN DEFAULT false, -- Share moderation signal with network?
125 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
126 expires_at TIMESTAMP WITH TIME ZONE -- Optional: temporary moderation
127);
128
129-- Indexes for moderation queries
130CREATE INDEX idx_moderation_community ON community_moderation(community_did);
131CREATE INDEX idx_moderation_instance ON community_moderation(instance_did);
132CREATE INDEX idx_moderation_action ON community_moderation(action);
133CREATE INDEX idx_moderation_created_at ON community_moderation(created_at);
134
135-- +goose Down
136-- Drop security comments
137COMMENT ON COLUMN communities.pds_refresh_token IS NULL;
138COMMENT ON COLUMN communities.pds_access_token IS NULL;
139COMMENT ON COLUMN communities.pds_password_hash IS NULL;
140
141DROP INDEX IF EXISTS idx_communities_pds_email;
142DROP INDEX IF EXISTS idx_moderation_created_at;
143DROP INDEX IF EXISTS idx_moderation_action;
144DROP INDEX IF EXISTS idx_moderation_instance;
145DROP INDEX IF EXISTS idx_moderation_community;
146DROP TABLE IF EXISTS community_moderation;
147
148DROP INDEX IF EXISTS idx_memberships_last_active;
149DROP INDEX IF EXISTS idx_memberships_reputation;
150DROP INDEX IF EXISTS idx_memberships_community;
151DROP INDEX IF EXISTS idx_memberships_user;
152DROP TABLE IF EXISTS community_memberships;
153
154DROP INDEX IF EXISTS idx_subscriptions_subscribed_at;
155DROP INDEX IF EXISTS idx_subscriptions_user_community;
156DROP INDEX IF EXISTS idx_subscriptions_community;
157DROP INDEX IF EXISTS idx_subscriptions_user;
158DROP TABLE IF EXISTS community_subscriptions;
159
160DROP INDEX IF EXISTS idx_communities_description_trgm;
161DROP INDEX IF EXISTS idx_communities_name_trgm;
162DROP INDEX IF EXISTS idx_communities_created_at;
163DROP INDEX IF EXISTS idx_communities_created_by;
164DROP INDEX IF EXISTS idx_communities_hosted_by;
165DROP INDEX IF EXISTS idx_communities_visibility;
166DROP INDEX IF EXISTS idx_communities_handle;
167DROP TABLE IF EXISTS communities;