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