malpercio.dev
WIP! A BB-style forum, on the ATmosphere!
We're still working... we'll be back soon when we have something to show off!
node
typescript
hono
htmx
atproto
1import {
2 pgTable,
3 bigserial,
4 text,
5 timestamp,
6 integer,
7 boolean,
8 bigint,
9 jsonb,
10 uniqueIndex,
11 index,
12 primaryKey,
13} from "drizzle-orm/pg-core";
14
15// ── forums ──────────────────────────────────────────────
16// Singleton forum metadata record, owned by Forum DID.
17// Key: literal:self (rkey is always "self").
18export const forums = pgTable(
19 "forums",
20 {
21 id: bigserial("id", { mode: "bigint" }).primaryKey(),
22 did: text("did").notNull(),
23 rkey: text("rkey").notNull(),
24 cid: text("cid").notNull(),
25 name: text("name").notNull(),
26 description: text("description"),
27 indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
28 },
29 (table) => [uniqueIndex("forums_did_rkey_idx").on(table.did, table.rkey)]
30);
31
32// ── categories ──────────────────────────────────────────
33// Subforum / category definitions, owned by Forum DID.
34export const categories = pgTable(
35 "categories",
36 {
37 id: bigserial("id", { mode: "bigint" }).primaryKey(),
38 did: text("did").notNull(),
39 rkey: text("rkey").notNull(),
40 cid: text("cid").notNull(),
41 name: text("name").notNull(),
42 description: text("description"),
43 slug: text("slug"),
44 sortOrder: integer("sort_order"),
45 forumId: bigint("forum_id", { mode: "bigint" }).references(() => forums.id),
46 createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
47 indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
48 },
49 (table) => [
50 uniqueIndex("categories_did_rkey_idx").on(table.did, table.rkey),
51 ]
52);
53
54// ── boards ──────────────────────────────────────────────
55// Board (subforum) definitions within categories, owned by Forum DID.
56export const boards = pgTable(
57 "boards",
58 {
59 id: bigserial("id", { mode: "bigint" }).primaryKey(),
60 did: text("did").notNull(),
61 rkey: text("rkey").notNull(),
62 cid: text("cid").notNull(),
63 name: text("name").notNull(),
64 description: text("description"),
65 slug: text("slug"),
66 sortOrder: integer("sort_order"),
67 categoryId: bigint("category_id", { mode: "bigint" }).references(
68 () => categories.id
69 ),
70 categoryUri: text("category_uri").notNull(),
71 createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
72 indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
73 },
74 (table) => [
75 uniqueIndex("boards_did_rkey_idx").on(table.did, table.rkey),
76 index("boards_category_id_idx").on(table.categoryId),
77 ]
78);
79
80// ── users ───────────────────────────────────────────────
81// Known AT Proto identities. Populated when any record
82// from a DID is indexed. DID is the primary key.
83export const users = pgTable("users", {
84 did: text("did").primaryKey(),
85 handle: text("handle"),
86 indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
87});
88
89// ── memberships ─────────────────────────────────────────
90// User membership in a forum. Owned by user DID.
91// `did` is both the record owner and the member.
92export const memberships = pgTable(
93 "memberships",
94 {
95 id: bigserial("id", { mode: "bigint" }).primaryKey(),
96 did: text("did")
97 .notNull()
98 .references(() => users.did),
99 rkey: text("rkey").notNull(),
100 cid: text("cid").notNull(),
101 forumId: bigint("forum_id", { mode: "bigint" }).references(
102 () => forums.id
103 ),
104 forumUri: text("forum_uri").notNull(),
105 role: text("role"),
106 roleUri: text("role_uri"),
107 joinedAt: timestamp("joined_at", { withTimezone: true }),
108 createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
109 indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
110 },
111 (table) => [
112 uniqueIndex("memberships_did_rkey_idx").on(table.did, table.rkey),
113 index("memberships_did_idx").on(table.did),
114 ]
115);
116
117// ── posts ───────────────────────────────────────────────
118// Unified post model. NULL root/parent = thread starter (topic).
119// Non-null root/parent = reply. Mirrors app.bsky.feed.post pattern.
120// Owned by user DID.
121export const posts = pgTable(
122 "posts",
123 {
124 id: bigserial("id", { mode: "bigint" }).primaryKey(),
125 did: text("did")
126 .notNull()
127 .references(() => users.did),
128 rkey: text("rkey").notNull(),
129 cid: text("cid").notNull(),
130 title: text("title"),
131 text: text("text").notNull(),
132 forumUri: text("forum_uri"),
133 boardUri: text("board_uri"),
134 boardId: bigint("board_id", { mode: "bigint" }).references(() => boards.id),
135 rootPostId: bigint("root_post_id", { mode: "bigint" }).references(
136 (): any => posts.id
137 ),
138 parentPostId: bigint("parent_post_id", { mode: "bigint" }).references(
139 (): any => posts.id
140 ),
141 rootUri: text("root_uri"),
142 parentUri: text("parent_uri"),
143 createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
144 indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
145 bannedByMod: boolean("banned_by_mod").notNull().default(false),
146 deletedByUser: boolean("deleted_by_user").notNull().default(false),
147 },
148 (table) => [
149 uniqueIndex("posts_did_rkey_idx").on(table.did, table.rkey),
150 index("posts_forum_uri_idx").on(table.forumUri),
151 index("posts_board_id_idx").on(table.boardId),
152 index("posts_board_uri_idx").on(table.boardUri),
153 index("posts_root_post_id_idx").on(table.rootPostId),
154 ]
155);
156
157// ── mod_actions ─────────────────────────────────────────
158// Moderation actions, owned by Forum DID. Written by AppView
159// on behalf of authorized moderators after role verification.
160export const modActions = pgTable(
161 "mod_actions",
162 {
163 id: bigserial("id", { mode: "bigint" }).primaryKey(),
164 did: text("did").notNull(),
165 rkey: text("rkey").notNull(),
166 cid: text("cid").notNull(),
167 action: text("action").notNull(),
168 subjectDid: text("subject_did"),
169 subjectPostUri: text("subject_post_uri"),
170 forumId: bigint("forum_id", { mode: "bigint" }).references(
171 () => forums.id
172 ),
173 reason: text("reason"),
174 createdBy: text("created_by").notNull(),
175 expiresAt: timestamp("expires_at", { withTimezone: true }),
176 createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
177 indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
178 },
179 (table) => [
180 uniqueIndex("mod_actions_did_rkey_idx").on(table.did, table.rkey),
181 index("mod_actions_subject_did_idx").on(table.subjectDid),
182 index("mod_actions_subject_post_uri_idx").on(table.subjectPostUri),
183 ]
184);
185
186// ── firehose_cursor ─────────────────────────────────────
187// Tracks the last processed event from the Jetstream firehose.
188// Singleton table (service is primary key).
189export const firehoseCursor = pgTable("firehose_cursor", {
190 service: text("service").primaryKey().default("jetstream"),
191 cursor: bigint("cursor", { mode: "bigint" }).notNull(), // time_us value from Jetstream
192 updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(),
193});
194
195// ── roles ───────────────────────────────────────────────
196// Role definitions, owned by Forum DID.
197export const roles = pgTable(
198 "roles",
199 {
200 id: bigserial("id", { mode: "bigint" }).primaryKey(),
201 did: text("did").notNull(),
202 rkey: text("rkey").notNull(),
203 cid: text("cid").notNull(),
204 name: text("name").notNull(),
205 description: text("description"),
206 priority: integer("priority").notNull(),
207 createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
208 indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
209 },
210 (table) => [
211 uniqueIndex("roles_did_rkey_idx").on(table.did, table.rkey),
212 index("roles_did_idx").on(table.did),
213 index("roles_did_name_idx").on(table.did, table.name),
214 ]
215);
216
217// ── role_permissions ─────────────────────────────────────────────────────────
218// Normalized join table replacing the permissions text[] column.
219// Cascade delete ensures permissions are cleaned up when a role is deleted.
220export const rolePermissions = pgTable(
221 "role_permissions",
222 {
223 roleId: bigint("role_id", { mode: "bigint" })
224 .notNull()
225 .references(() => roles.id, { onDelete: "cascade" }),
226 permission: text("permission").notNull(),
227 },
228 (t) => [primaryKey({ columns: [t.roleId, t.permission] })]
229);
230
231// ── backfill_progress ───────────────────────────────────
232// Tracks backfill job state for crash-resilient resume.
233export const backfillProgress = pgTable("backfill_progress", {
234 id: bigserial("id", { mode: "bigint" }).primaryKey(),
235 status: text("status").notNull(), // 'in_progress', 'completed', 'failed'
236 backfillType: text("backfill_type").notNull(), // 'full_sync', 'catch_up'
237 lastProcessedDid: text("last_processed_did"),
238 didsTotal: integer("dids_total").notNull().default(0),
239 didsProcessed: integer("dids_processed").notNull().default(0),
240 recordsIndexed: integer("records_indexed").notNull().default(0),
241 startedAt: timestamp("started_at", { withTimezone: true }).notNull(),
242 completedAt: timestamp("completed_at", { withTimezone: true }),
243 errorMessage: text("error_message"),
244});
245
246// ── backfill_errors ─────────────────────────────────────
247// Per-DID error log for failed backfill syncs.
248export const backfillErrors = pgTable(
249 "backfill_errors",
250 {
251 id: bigserial("id", { mode: "bigint" }).primaryKey(),
252 backfillId: bigint("backfill_id", { mode: "bigint" })
253 .notNull()
254 .references(() => backfillProgress.id),
255 did: text("did").notNull(),
256 collection: text("collection").notNull(),
257 errorMessage: text("error_message").notNull(),
258 createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
259 },
260 (table) => [index("backfill_errors_backfill_id_idx").on(table.backfillId)]
261);
262
263// ── themes ──────────────────────────────────────────────
264// Theme definitions, owned by Forum DID. Multiple themes per forum.
265// Key: tid (multiple records per repo).
266export const themes = pgTable(
267 "themes",
268 {
269 id: bigserial("id", { mode: "bigint" }).primaryKey(),
270 did: text("did").notNull(),
271 rkey: text("rkey").notNull(),
272 cid: text("cid").notNull(),
273 name: text("name").notNull(),
274 colorScheme: text("color_scheme").notNull(), // "light" | "dark"
275 tokens: jsonb("tokens").notNull(), // design token key-value map
276 cssOverrides: text("css_overrides"),
277 fontUrls: text("font_urls").array(),
278 createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
279 indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
280 },
281 (table) => [uniqueIndex("themes_did_rkey_idx").on(table.did, table.rkey)]
282);
283
284// ── theme_policies ───────────────────────────────────────
285// Singleton theme policy, owned by Forum DID.
286// Key: literal:self (rkey is always "self").
287export const themePolicies = pgTable(
288 "theme_policies",
289 {
290 id: bigserial("id", { mode: "bigint" }).primaryKey(),
291 did: text("did").notNull(),
292 rkey: text("rkey").notNull(),
293 cid: text("cid").notNull(),
294 defaultLightThemeUri: text("default_light_theme_uri").notNull(),
295 defaultDarkThemeUri: text("default_dark_theme_uri").notNull(),
296 allowUserChoice: boolean("allow_user_choice").notNull(),
297 indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
298 },
299 (table) => [
300 uniqueIndex("theme_policies_did_rkey_idx").on(table.did, table.rkey),
301 ]
302);
303
304// ── theme_policy_available_themes ────────────────────────
305// Normalized join table: which themes does the policy expose to users?
306// ON DELETE CASCADE: deleting a policy row removes all its available-theme rows.
307export const themePolicyAvailableThemes = pgTable(
308 "theme_policy_available_themes",
309 {
310 policyId: bigint("policy_id", { mode: "bigint" })
311 .notNull()
312 .references(() => themePolicies.id, { onDelete: "cascade" }),
313 themeUri: text("theme_uri").notNull(),
314 themeCid: text("theme_cid"),
315 },
316 (t) => [primaryKey({ columns: [t.policyId, t.themeUri] })]
317);