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
1CREATE TABLE `backfill_errors` (
2 `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
3 `backfill_id` integer NOT NULL,
4 `did` text NOT NULL,
5 `collection` text NOT NULL,
6 `error_message` text NOT NULL,
7 `created_at` integer NOT NULL,
8 FOREIGN KEY (`backfill_id`) REFERENCES `backfill_progress`(`id`) ON UPDATE no action ON DELETE no action
9);
10--> statement-breakpoint
11CREATE INDEX `backfill_errors_backfill_id_idx` ON `backfill_errors` (`backfill_id`);--> statement-breakpoint
12CREATE TABLE `backfill_progress` (
13 `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
14 `status` text NOT NULL,
15 `backfill_type` text NOT NULL,
16 `last_processed_did` text,
17 `dids_total` integer DEFAULT 0 NOT NULL,
18 `dids_processed` integer DEFAULT 0 NOT NULL,
19 `records_indexed` integer DEFAULT 0 NOT NULL,
20 `started_at` integer NOT NULL,
21 `completed_at` integer,
22 `error_message` text
23);
24--> statement-breakpoint
25CREATE TABLE `boards` (
26 `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
27 `did` text NOT NULL,
28 `rkey` text NOT NULL,
29 `cid` text NOT NULL,
30 `name` text NOT NULL,
31 `description` text,
32 `slug` text,
33 `sort_order` integer,
34 `category_id` integer,
35 `category_uri` text NOT NULL,
36 `created_at` integer NOT NULL,
37 `indexed_at` integer NOT NULL,
38 FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON UPDATE no action ON DELETE no action
39);
40--> statement-breakpoint
41CREATE UNIQUE INDEX `boards_did_rkey_idx` ON `boards` (`did`,`rkey`);--> statement-breakpoint
42CREATE INDEX `boards_category_id_idx` ON `boards` (`category_id`);--> statement-breakpoint
43CREATE TABLE `categories` (
44 `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
45 `did` text NOT NULL,
46 `rkey` text NOT NULL,
47 `cid` text NOT NULL,
48 `name` text NOT NULL,
49 `description` text,
50 `slug` text,
51 `sort_order` integer,
52 `forum_id` integer,
53 `created_at` integer NOT NULL,
54 `indexed_at` integer NOT NULL,
55 FOREIGN KEY (`forum_id`) REFERENCES `forums`(`id`) ON UPDATE no action ON DELETE no action
56);
57--> statement-breakpoint
58CREATE UNIQUE INDEX `categories_did_rkey_idx` ON `categories` (`did`,`rkey`);--> statement-breakpoint
59CREATE TABLE `firehose_cursor` (
60 `service` text PRIMARY KEY DEFAULT 'jetstream' NOT NULL,
61 `cursor` integer NOT NULL,
62 `updated_at` integer NOT NULL
63);
64--> statement-breakpoint
65CREATE TABLE `forums` (
66 `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
67 `did` text NOT NULL,
68 `rkey` text NOT NULL,
69 `cid` text NOT NULL,
70 `name` text NOT NULL,
71 `description` text,
72 `indexed_at` integer NOT NULL
73);
74--> statement-breakpoint
75CREATE UNIQUE INDEX `forums_did_rkey_idx` ON `forums` (`did`,`rkey`);--> statement-breakpoint
76CREATE TABLE `memberships` (
77 `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
78 `did` text NOT NULL,
79 `rkey` text NOT NULL,
80 `cid` text NOT NULL,
81 `forum_id` integer,
82 `forum_uri` text NOT NULL,
83 `role` text,
84 `role_uri` text,
85 `joined_at` integer,
86 `created_at` integer NOT NULL,
87 `indexed_at` integer NOT NULL,
88 FOREIGN KEY (`did`) REFERENCES `users`(`did`) ON UPDATE no action ON DELETE no action,
89 FOREIGN KEY (`forum_id`) REFERENCES `forums`(`id`) ON UPDATE no action ON DELETE no action
90);
91--> statement-breakpoint
92CREATE UNIQUE INDEX `memberships_did_rkey_idx` ON `memberships` (`did`,`rkey`);--> statement-breakpoint
93CREATE INDEX `memberships_did_idx` ON `memberships` (`did`);--> statement-breakpoint
94CREATE TABLE `mod_actions` (
95 `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
96 `did` text NOT NULL,
97 `rkey` text NOT NULL,
98 `cid` text NOT NULL,
99 `action` text NOT NULL,
100 `subject_did` text,
101 `subject_post_uri` text,
102 `forum_id` integer,
103 `reason` text,
104 `created_by` text NOT NULL,
105 `expires_at` integer,
106 `created_at` integer NOT NULL,
107 `indexed_at` integer NOT NULL,
108 FOREIGN KEY (`forum_id`) REFERENCES `forums`(`id`) ON UPDATE no action ON DELETE no action
109);
110--> statement-breakpoint
111CREATE UNIQUE INDEX `mod_actions_did_rkey_idx` ON `mod_actions` (`did`,`rkey`);--> statement-breakpoint
112CREATE INDEX `mod_actions_subject_did_idx` ON `mod_actions` (`subject_did`);--> statement-breakpoint
113CREATE INDEX `mod_actions_subject_post_uri_idx` ON `mod_actions` (`subject_post_uri`);--> statement-breakpoint
114CREATE TABLE `posts` (
115 `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
116 `did` text NOT NULL,
117 `rkey` text NOT NULL,
118 `cid` text NOT NULL,
119 `title` text,
120 `text` text NOT NULL,
121 `forum_uri` text,
122 `board_uri` text,
123 `board_id` integer,
124 `root_post_id` integer,
125 `parent_post_id` integer,
126 `root_uri` text,
127 `parent_uri` text,
128 `created_at` integer NOT NULL,
129 `indexed_at` integer NOT NULL,
130 `banned_by_mod` integer DEFAULT false NOT NULL,
131 `deleted_by_user` integer DEFAULT false NOT NULL,
132 FOREIGN KEY (`did`) REFERENCES `users`(`did`) ON UPDATE no action ON DELETE no action,
133 FOREIGN KEY (`board_id`) REFERENCES `boards`(`id`) ON UPDATE no action ON DELETE no action,
134 FOREIGN KEY (`root_post_id`) REFERENCES `posts`(`id`) ON UPDATE no action ON DELETE no action,
135 FOREIGN KEY (`parent_post_id`) REFERENCES `posts`(`id`) ON UPDATE no action ON DELETE no action
136);
137--> statement-breakpoint
138CREATE UNIQUE INDEX `posts_did_rkey_idx` ON `posts` (`did`,`rkey`);--> statement-breakpoint
139CREATE INDEX `posts_forum_uri_idx` ON `posts` (`forum_uri`);--> statement-breakpoint
140CREATE INDEX `posts_board_id_idx` ON `posts` (`board_id`);--> statement-breakpoint
141CREATE INDEX `posts_board_uri_idx` ON `posts` (`board_uri`);--> statement-breakpoint
142CREATE INDEX `posts_root_post_id_idx` ON `posts` (`root_post_id`);--> statement-breakpoint
143CREATE TABLE `role_permissions` (
144 `role_id` integer NOT NULL,
145 `permission` text NOT NULL,
146 PRIMARY KEY(`role_id`, `permission`),
147 FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`) ON UPDATE no action ON DELETE cascade
148);
149--> statement-breakpoint
150CREATE TABLE `roles` (
151 `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
152 `did` text NOT NULL,
153 `rkey` text NOT NULL,
154 `cid` text NOT NULL,
155 `name` text NOT NULL,
156 `description` text,
157 `priority` integer NOT NULL,
158 `created_at` integer NOT NULL,
159 `indexed_at` integer NOT NULL
160);
161--> statement-breakpoint
162CREATE UNIQUE INDEX `roles_did_rkey_idx` ON `roles` (`did`,`rkey`);--> statement-breakpoint
163CREATE INDEX `roles_did_idx` ON `roles` (`did`);--> statement-breakpoint
164CREATE INDEX `roles_did_name_idx` ON `roles` (`did`,`name`);--> statement-breakpoint
165CREATE TABLE `users` (
166 `did` text PRIMARY KEY NOT NULL,
167 `handle` text,
168 `indexed_at` integer NOT NULL
169);