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
18 owner_did TEXT NOT NULL, -- DID of community owner (instance in V1)
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 -- Visibility & federation
23 visibility TEXT NOT NULL DEFAULT 'public' CHECK (visibility IN ('public', 'unlisted', 'private')),
24 allow_external_discovery BOOLEAN NOT NULL DEFAULT true,
25
26 -- Moderation
27 moderation_type TEXT CHECK (moderation_type IN ('moderator', 'sortition')),
28 content_warnings TEXT[], -- Array of content warning types
29
30 -- Statistics (cached counts)
31 member_count INTEGER DEFAULT 0,
32 subscriber_count INTEGER DEFAULT 0,
33 post_count INTEGER DEFAULT 0,
34
35 -- Federation metadata (for future cross-platform support)
36 federated_from TEXT, -- 'lemmy', 'coves', etc.
37 federated_id TEXT, -- Original ID on federated platform
38
39 -- Timestamps
40 created_at TIMESTAMP WITH TIME ZONE NOT NULL,
41 updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
42
43 -- AT-Proto metadata
44 record_uri TEXT, -- AT-URI of the community profile record
45 record_cid TEXT -- CID of the community profile record
46);
47
48-- Indexes for efficient queries
49CREATE INDEX idx_communities_handle ON communities(handle);
50CREATE INDEX idx_communities_visibility ON communities(visibility);
51CREATE INDEX idx_communities_hosted_by ON communities(hosted_by_did);
52CREATE INDEX idx_communities_created_by ON communities(created_by_did);
53CREATE INDEX idx_communities_created_at ON communities(created_at);
54CREATE INDEX idx_communities_name_trgm ON communities USING gin(name gin_trgm_ops); -- For fuzzy search
55CREATE INDEX idx_communities_description_trgm ON communities USING gin(description gin_trgm_ops);
56
57-- Subscriptions table: lightweight feed following
58CREATE TABLE community_subscriptions (
59 id SERIAL PRIMARY KEY,
60 user_did TEXT NOT NULL,
61 community_did TEXT NOT NULL REFERENCES communities(did) ON DELETE CASCADE,
62 subscribed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
63
64 -- AT-Proto metadata (subscription is a record in user's repo)
65 record_uri TEXT, -- AT-URI of the subscription record
66 record_cid TEXT, -- CID of the subscription record
67
68 UNIQUE(user_did, community_did)
69);
70
71-- Indexes for subscription queries
72CREATE INDEX idx_subscriptions_user ON community_subscriptions(user_did);
73CREATE INDEX idx_subscriptions_community ON community_subscriptions(community_did);
74CREATE INDEX idx_subscriptions_user_community ON community_subscriptions(user_did, community_did); -- Composite index for GetSubscription
75CREATE INDEX idx_subscriptions_subscribed_at ON community_subscriptions(subscribed_at);
76
77-- Memberships table: active participation & reputation tracking
78CREATE TABLE community_memberships (
79 id SERIAL PRIMARY KEY,
80 user_did TEXT NOT NULL,
81 community_did TEXT NOT NULL REFERENCES communities(did) ON DELETE CASCADE,
82
83 -- Reputation system
84 reputation_score INTEGER DEFAULT 0, -- Gained through participation
85 contribution_count INTEGER DEFAULT 0, -- Total posts + comments + actions
86
87 -- Activity tracking
88 joined_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
89 last_active_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
90
91 -- Moderation status
92 is_banned BOOLEAN DEFAULT false,
93 is_moderator BOOLEAN DEFAULT false,
94
95 UNIQUE(user_did, community_did)
96);
97
98-- Indexes for membership queries
99CREATE INDEX idx_memberships_user ON community_memberships(user_did);
100CREATE INDEX idx_memberships_community ON community_memberships(community_did);
101CREATE INDEX idx_memberships_reputation ON community_memberships(community_did, reputation_score DESC);
102CREATE INDEX idx_memberships_last_active ON community_memberships(last_active_at);
103
104-- Community moderation actions table (V2 feature, schema prepared)
105CREATE TABLE community_moderation (
106 id SERIAL PRIMARY KEY,
107 community_did TEXT NOT NULL REFERENCES communities(did) ON DELETE CASCADE,
108 action TEXT NOT NULL CHECK (action IN ('delist', 'quarantine', 'remove')),
109 reason TEXT,
110 instance_did TEXT NOT NULL, -- Which instance took this action
111 broadcast BOOLEAN DEFAULT false, -- Share moderation signal with network?
112 created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
113 expires_at TIMESTAMP WITH TIME ZONE -- Optional: temporary moderation
114);
115
116-- Indexes for moderation queries
117CREATE INDEX idx_moderation_community ON community_moderation(community_did);
118CREATE INDEX idx_moderation_instance ON community_moderation(instance_did);
119CREATE INDEX idx_moderation_action ON community_moderation(action);
120CREATE INDEX idx_moderation_created_at ON community_moderation(created_at);
121
122-- +goose Down
123DROP INDEX IF EXISTS idx_moderation_created_at;
124DROP INDEX IF EXISTS idx_moderation_action;
125DROP INDEX IF EXISTS idx_moderation_instance;
126DROP INDEX IF EXISTS idx_moderation_community;
127DROP TABLE IF EXISTS community_moderation;
128
129DROP INDEX IF EXISTS idx_memberships_last_active;
130DROP INDEX IF EXISTS idx_memberships_reputation;
131DROP INDEX IF EXISTS idx_memberships_community;
132DROP INDEX IF EXISTS idx_memberships_user;
133DROP TABLE IF EXISTS community_memberships;
134
135DROP INDEX IF EXISTS idx_subscriptions_subscribed_at;
136DROP INDEX IF EXISTS idx_subscriptions_user_community;
137DROP INDEX IF EXISTS idx_subscriptions_community;
138DROP INDEX IF EXISTS idx_subscriptions_user;
139DROP TABLE IF EXISTS community_subscriptions;
140
141DROP INDEX IF EXISTS idx_communities_description_trgm;
142DROP INDEX IF EXISTS idx_communities_name_trgm;
143DROP INDEX IF EXISTS idx_communities_created_at;
144DROP INDEX IF EXISTS idx_communities_created_by;
145DROP INDEX IF EXISTS idx_communities_hosted_by;
146DROP INDEX IF EXISTS idx_communities_visibility;
147DROP INDEX IF EXISTS idx_communities_handle;
148DROP TABLE IF EXISTS communities;