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 uniqueIndex,
10 index,
11 primaryKey,
12} from "drizzle-orm/pg-core";
13
14// ── forums ──────────────────────────────────────────────
15// Singleton forum metadata record, owned by Forum DID.
16// Key: literal:self (rkey is always "self").
17export const forums = pgTable(
18 "forums",
19 {
20 id: bigserial("id", { mode: "bigint" }).primaryKey(),
21 did: text("did").notNull(),
22 rkey: text("rkey").notNull(),
23 cid: text("cid").notNull(),
24 name: text("name").notNull(),
25 description: text("description"),
26 indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
27 },
28 (table) => [uniqueIndex("forums_did_rkey_idx").on(table.did, table.rkey)]
29);
30
31// ── categories ──────────────────────────────────────────
32// Subforum / category definitions, owned by Forum DID.
33export const categories = pgTable(
34 "categories",
35 {
36 id: bigserial("id", { mode: "bigint" }).primaryKey(),
37 did: text("did").notNull(),
38 rkey: text("rkey").notNull(),
39 cid: text("cid").notNull(),
40 name: text("name").notNull(),
41 description: text("description"),
42 slug: text("slug"),
43 sortOrder: integer("sort_order"),
44 forumId: bigint("forum_id", { mode: "bigint" }).references(() => forums.id),
45 createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
46 indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
47 },
48 (table) => [
49 uniqueIndex("categories_did_rkey_idx").on(table.did, table.rkey),
50 ]
51);
52
53// ── boards ──────────────────────────────────────────────
54// Board (subforum) definitions within categories, owned by Forum DID.
55export const boards = pgTable(
56 "boards",
57 {
58 id: bigserial("id", { mode: "bigint" }).primaryKey(),
59 did: text("did").notNull(),
60 rkey: text("rkey").notNull(),
61 cid: text("cid").notNull(),
62 name: text("name").notNull(),
63 description: text("description"),
64 slug: text("slug"),
65 sortOrder: integer("sort_order"),
66 categoryId: bigint("category_id", { mode: "bigint" }).references(
67 () => categories.id
68 ),
69 categoryUri: text("category_uri").notNull(),
70 createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
71 indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
72 },
73 (table) => [
74 uniqueIndex("boards_did_rkey_idx").on(table.did, table.rkey),
75 index("boards_category_id_idx").on(table.categoryId),
76 ]
77);
78
79// ── users ───────────────────────────────────────────────
80// Known AT Proto identities. Populated when any record
81// from a DID is indexed. DID is the primary key.
82export const users = pgTable("users", {
83 did: text("did").primaryKey(),
84 handle: text("handle"),
85 indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
86});
87
88// ── memberships ─────────────────────────────────────────
89// User membership in a forum. Owned by user DID.
90// `did` is both the record owner and the member.
91export const memberships = pgTable(
92 "memberships",
93 {
94 id: bigserial("id", { mode: "bigint" }).primaryKey(),
95 did: text("did")
96 .notNull()
97 .references(() => users.did),
98 rkey: text("rkey").notNull(),
99 cid: text("cid").notNull(),
100 forumId: bigint("forum_id", { mode: "bigint" }).references(
101 () => forums.id
102 ),
103 forumUri: text("forum_uri").notNull(),
104 role: text("role"),
105 roleUri: text("role_uri"),
106 joinedAt: timestamp("joined_at", { withTimezone: true }),
107 createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
108 indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
109 },
110 (table) => [
111 uniqueIndex("memberships_did_rkey_idx").on(table.did, table.rkey),
112 index("memberships_did_idx").on(table.did),
113 ]
114);
115
116// ── posts ───────────────────────────────────────────────
117// Unified post model. NULL root/parent = thread starter (topic).
118// Non-null root/parent = reply. Mirrors app.bsky.feed.post pattern.
119// Owned by user DID.
120export const posts = pgTable(
121 "posts",
122 {
123 id: bigserial("id", { mode: "bigint" }).primaryKey(),
124 did: text("did")
125 .notNull()
126 .references(() => users.did),
127 rkey: text("rkey").notNull(),
128 cid: text("cid").notNull(),
129 title: text("title"),
130 text: text("text").notNull(),
131 forumUri: text("forum_uri"),
132 boardUri: text("board_uri"),
133 boardId: bigint("board_id", { mode: "bigint" }).references(() => boards.id),
134 rootPostId: bigint("root_post_id", { mode: "bigint" }).references(
135 (): any => posts.id
136 ),
137 parentPostId: bigint("parent_post_id", { mode: "bigint" }).references(
138 (): any => posts.id
139 ),
140 rootUri: text("root_uri"),
141 parentUri: text("parent_uri"),
142 createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
143 indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
144 bannedByMod: boolean("banned_by_mod").notNull().default(false),
145 deletedByUser: boolean("deleted_by_user").notNull().default(false),
146 },
147 (table) => [
148 uniqueIndex("posts_did_rkey_idx").on(table.did, table.rkey),
149 index("posts_forum_uri_idx").on(table.forumUri),
150 index("posts_board_id_idx").on(table.boardId),
151 index("posts_board_uri_idx").on(table.boardUri),
152 index("posts_root_post_id_idx").on(table.rootPostId),
153 ]
154);
155
156// ── mod_actions ─────────────────────────────────────────
157// Moderation actions, owned by Forum DID. Written by AppView
158// on behalf of authorized moderators after role verification.
159export const modActions = pgTable(
160 "mod_actions",
161 {
162 id: bigserial("id", { mode: "bigint" }).primaryKey(),
163 did: text("did").notNull(),
164 rkey: text("rkey").notNull(),
165 cid: text("cid").notNull(),
166 action: text("action").notNull(),
167 subjectDid: text("subject_did"),
168 subjectPostUri: text("subject_post_uri"),
169 forumId: bigint("forum_id", { mode: "bigint" }).references(
170 () => forums.id
171 ),
172 reason: text("reason"),
173 createdBy: text("created_by").notNull(),
174 expiresAt: timestamp("expires_at", { withTimezone: true }),
175 createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
176 indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
177 },
178 (table) => [
179 uniqueIndex("mod_actions_did_rkey_idx").on(table.did, table.rkey),
180 index("mod_actions_subject_did_idx").on(table.subjectDid),
181 index("mod_actions_subject_post_uri_idx").on(table.subjectPostUri),
182 ]
183);
184
185// ── firehose_cursor ─────────────────────────────────────
186// Tracks the last processed event from the Jetstream firehose.
187// Singleton table (service is primary key).
188export const firehoseCursor = pgTable("firehose_cursor", {
189 service: text("service").primaryKey().default("jetstream"),
190 cursor: bigint("cursor", { mode: "bigint" }).notNull(), // time_us value from Jetstream
191 updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(),
192});
193
194// ── roles ───────────────────────────────────────────────
195// Role definitions, owned by Forum DID.
196export const roles = pgTable(
197 "roles",
198 {
199 id: bigserial("id", { mode: "bigint" }).primaryKey(),
200 did: text("did").notNull(),
201 rkey: text("rkey").notNull(),
202 cid: text("cid").notNull(),
203 name: text("name").notNull(),
204 description: text("description"),
205 priority: integer("priority").notNull(),
206 createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
207 indexedAt: timestamp("indexed_at", { withTimezone: true }).notNull(),
208 },
209 (table) => [
210 uniqueIndex("roles_did_rkey_idx").on(table.did, table.rkey),
211 index("roles_did_idx").on(table.did),
212 index("roles_did_name_idx").on(table.did, table.name),
213 ]
214);
215
216// ── role_permissions ─────────────────────────────────────────────────────────
217// Normalized join table replacing the permissions text[] column.
218// Cascade delete ensures permissions are cleaned up when a role is deleted.
219export const rolePermissions = pgTable(
220 "role_permissions",
221 {
222 roleId: bigint("role_id", { mode: "bigint" })
223 .notNull()
224 .references(() => roles.id, { onDelete: "cascade" }),
225 permission: text("permission").notNull(),
226 },
227 (t) => [primaryKey({ columns: [t.roleId, t.permission] })]
228);
229
230// ── backfill_progress ───────────────────────────────────
231// Tracks backfill job state for crash-resilient resume.
232export const backfillProgress = pgTable("backfill_progress", {
233 id: bigserial("id", { mode: "bigint" }).primaryKey(),
234 status: text("status").notNull(), // 'in_progress', 'completed', 'failed'
235 backfillType: text("backfill_type").notNull(), // 'full_sync', 'catch_up'
236 lastProcessedDid: text("last_processed_did"),
237 didsTotal: integer("dids_total").notNull().default(0),
238 didsProcessed: integer("dids_processed").notNull().default(0),
239 recordsIndexed: integer("records_indexed").notNull().default(0),
240 startedAt: timestamp("started_at", { withTimezone: true }).notNull(),
241 completedAt: timestamp("completed_at", { withTimezone: true }),
242 errorMessage: text("error_message"),
243});
244
245// ── backfill_errors ─────────────────────────────────────
246// Per-DID error log for failed backfill syncs.
247export const backfillErrors = pgTable(
248 "backfill_errors",
249 {
250 id: bigserial("id", { mode: "bigint" }).primaryKey(),
251 backfillId: bigint("backfill_id", { mode: "bigint" })
252 .notNull()
253 .references(() => backfillProgress.id),
254 did: text("did").notNull(),
255 collection: text("collection").notNull(),
256 errorMessage: text("error_message").notNull(),
257 createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
258 },
259 (table) => [index("backfill_errors_backfill_id_idx").on(table.backfillId)]
260);