leaflet.pub
a tool for shared writing and social publishing
awarm.space
1import { pgTable, pgEnum, text, jsonb, foreignKey, timestamp, boolean, uuid, index, bigint, unique, uniqueIndex, smallint, primaryKey, integer } from "drizzle-orm/pg-core"
2 import { sql } from "drizzle-orm"
3
4export const aal_level = pgEnum("aal_level", ['aal1', 'aal2', 'aal3'])
5export const code_challenge_method = pgEnum("code_challenge_method", ['s256', 'plain'])
6export const factor_status = pgEnum("factor_status", ['unverified', 'verified'])
7export const factor_type = pgEnum("factor_type", ['totp', 'webauthn', 'phone'])
8export const oauth_authorization_status = pgEnum("oauth_authorization_status", ['pending', 'approved', 'denied', 'expired'])
9export const oauth_client_type = pgEnum("oauth_client_type", ['public', 'confidential'])
10export const oauth_registration_type = pgEnum("oauth_registration_type", ['dynamic', 'manual'])
11export const oauth_response_type = pgEnum("oauth_response_type", ['code'])
12export const one_time_token_type = pgEnum("one_time_token_type", ['confirmation_token', 'reauthentication_token', 'recovery_token', 'email_change_token_new', 'email_change_token_current', 'phone_change_token'])
13export const key_status = pgEnum("key_status", ['default', 'valid', 'invalid', 'expired'])
14export const key_type = pgEnum("key_type", ['aead-ietf', 'aead-det', 'hmacsha512', 'hmacsha256', 'auth', 'shorthash', 'generichash', 'kdf', 'secretbox', 'secretstream', 'stream_xchacha20'])
15export const rsvp_status = pgEnum("rsvp_status", ['GOING', 'NOT_GOING', 'MAYBE'])
16export const action = pgEnum("action", ['INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'ERROR'])
17export const equality_op = pgEnum("equality_op", ['eq', 'neq', 'lt', 'lte', 'gt', 'gte', 'in'])
18export const buckettype = pgEnum("buckettype", ['STANDARD', 'ANALYTICS', 'VECTOR'])
19
20
21export const oauth_state_store = pgTable("oauth_state_store", {
22 key: text("key").primaryKey().notNull(),
23 state: jsonb("state").notNull(),
24});
25
26export const notifications = pgTable("notifications", {
27 recipient: text("recipient").notNull().references(() => identities.atp_did, { onDelete: "cascade", onUpdate: "cascade" } ),
28 created_at: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
29 read: boolean("read").default(false).notNull(),
30 data: jsonb("data").notNull(),
31 id: uuid("id").primaryKey().notNull(),
32});
33
34export const publications = pgTable("publications", {
35 uri: text("uri").primaryKey().notNull(),
36 indexed_at: timestamp("indexed_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
37 name: text("name").notNull(),
38 identity_did: text("identity_did").notNull().references(() => identities.atp_did, { onDelete: "cascade" } ),
39 record: jsonb("record"),
40},
41(table) => {
42 return {
43 identity_did_idx: index("publications_identity_did_idx").on(table.identity_did),
44 }
45});
46
47export const comments_on_documents = pgTable("comments_on_documents", {
48 uri: text("uri").primaryKey().notNull(),
49 record: jsonb("record").notNull(),
50 document: text("document").references(() => documents.uri, { onDelete: "cascade", onUpdate: "cascade" } ),
51 indexed_at: timestamp("indexed_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
52 profile: text("profile").references(() => bsky_profiles.did, { onDelete: "set null", onUpdate: "cascade" } ),
53});
54
55export const entities = pgTable("entities", {
56 id: uuid("id").primaryKey().notNull(),
57 created_at: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
58 set: uuid("set").notNull().references(() => entity_sets.id, { onDelete: "cascade", onUpdate: "cascade" } ),
59},
60(table) => {
61 return {
62 set_idx: index("entities_set_idx").on(table.set),
63 }
64});
65
66export const facts = pgTable("facts", {
67 id: uuid("id").primaryKey().notNull(),
68 entity: uuid("entity").notNull().references(() => entities.id, { onDelete: "cascade", onUpdate: "restrict" } ),
69 attribute: text("attribute").notNull(),
70 data: jsonb("data").notNull(),
71 created_at: timestamp("created_at", { mode: 'string' }).defaultNow().notNull(),
72 updated_at: timestamp("updated_at", { mode: 'string' }),
73 // You can use { mode: "bigint" } if numbers are exceeding js number limitations
74 version: bigint("version", { mode: "number" }).default(0).notNull(),
75},
76(table) => {
77 return {
78 entity_idx: index("facts_entity_idx").on(table.entity),
79 }
80});
81
82export const replicache_clients = pgTable("replicache_clients", {
83 client_id: text("client_id").primaryKey().notNull(),
84 client_group: text("client_group").notNull(),
85 // You can use { mode: "bigint" } if numbers are exceeding js number limitations
86 last_mutation: bigint("last_mutation", { mode: "number" }).notNull(),
87},
88(table) => {
89 return {
90 client_group_idx: index("replicache_clients_client_group_idx").on(table.client_group),
91 }
92});
93
94export const email_auth_tokens = pgTable("email_auth_tokens", {
95 id: uuid("id").defaultRandom().primaryKey().notNull(),
96 created_at: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
97 confirmed: boolean("confirmed").default(false).notNull(),
98 email: text("email"),
99 confirmation_code: text("confirmation_code").notNull(),
100 identity: uuid("identity").references(() => identities.id, { onDelete: "cascade", onUpdate: "cascade" } ),
101});
102
103export const bsky_posts = pgTable("bsky_posts", {
104 uri: text("uri").primaryKey().notNull(),
105 indexed_at: timestamp("indexed_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
106 post_view: jsonb("post_view").notNull(),
107 cid: text("cid").notNull(),
108});
109
110export const bsky_profiles = pgTable("bsky_profiles", {
111 did: text("did").primaryKey().notNull().references(() => identities.atp_did, { onDelete: "cascade" } ),
112 record: jsonb("record").notNull(),
113 indexed_at: timestamp("indexed_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
114 handle: text("handle"),
115});
116
117export const entity_sets = pgTable("entity_sets", {
118 id: uuid("id").defaultRandom().primaryKey().notNull(),
119 created_at: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
120});
121
122export const poll_votes_on_entity = pgTable("poll_votes_on_entity", {
123 id: uuid("id").defaultRandom().primaryKey().notNull(),
124 created_at: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
125 poll_entity: uuid("poll_entity").notNull().references(() => entities.id, { onDelete: "cascade", onUpdate: "cascade" } ),
126 option_entity: uuid("option_entity").notNull().references(() => entities.id, { onDelete: "cascade", onUpdate: "cascade" } ),
127 voter_token: uuid("voter_token").notNull(),
128});
129
130export const permission_tokens = pgTable("permission_tokens", {
131 id: uuid("id").defaultRandom().primaryKey().notNull(),
132 root_entity: uuid("root_entity").notNull().references(() => entities.id, { onDelete: "cascade", onUpdate: "cascade" } ),
133 blocked_by_admin: boolean("blocked_by_admin"),
134});
135
136export const identities = pgTable("identities", {
137 id: uuid("id").defaultRandom().primaryKey().notNull(),
138 created_at: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
139 home_page: uuid("home_page").default(sql`create_identity_homepage()`).notNull().references(() => permission_tokens.id, { onDelete: "cascade" } ),
140 email: text("email"),
141 atp_did: text("atp_did"),
142 interface_state: jsonb("interface_state"),
143 metadata: jsonb("metadata"),
144},
145(table) => {
146 return {
147 identities_email_key: unique("identities_email_key").on(table.email),
148 identities_atp_did_key: unique("identities_atp_did_key").on(table.atp_did),
149 }
150});
151
152export const phone_number_auth_tokens = pgTable("phone_number_auth_tokens", {
153 id: uuid("id").defaultRandom().primaryKey().notNull(),
154 created_at: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
155 confirmed: boolean("confirmed").default(false).notNull(),
156 confirmation_code: text("confirmation_code").notNull(),
157 phone_number: text("phone_number").notNull(),
158 country_code: text("country_code").notNull(),
159});
160
161export const phone_rsvps_to_entity = pgTable("phone_rsvps_to_entity", {
162 created_at: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
163 phone_number: text("phone_number").notNull(),
164 country_code: text("country_code").notNull(),
165 status: rsvp_status("status").notNull(),
166 id: uuid("id").defaultRandom().primaryKey().notNull(),
167 entity: uuid("entity").notNull().references(() => entities.id, { onDelete: "cascade", onUpdate: "cascade" } ),
168 name: text("name").default('').notNull(),
169 plus_ones: smallint("plus_ones").default(0).notNull(),
170},
171(table) => {
172 return {
173 unique_phone_number_entities: uniqueIndex("unique_phone_number_entities").on(table.phone_number, table.entity),
174 }
175});
176
177export const site_standard_publications = pgTable("site_standard_publications", {
178 uri: text("uri").primaryKey().notNull(),
179 data: jsonb("data").notNull(),
180 indexed_at: timestamp("indexed_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
181 identity_did: text("identity_did").notNull().references(() => identities.atp_did, { onDelete: "cascade" } ),
182});
183
184export const custom_domain_routes = pgTable("custom_domain_routes", {
185 id: uuid("id").defaultRandom().primaryKey().notNull(),
186 domain: text("domain").notNull().references(() => custom_domains.domain),
187 route: text("route").notNull(),
188 edit_permission_token: uuid("edit_permission_token").notNull().references(() => permission_tokens.id, { onDelete: "cascade", onUpdate: "cascade" } ),
189 view_permission_token: uuid("view_permission_token").notNull().references(() => permission_tokens.id, { onDelete: "cascade", onUpdate: "cascade" } ),
190 created_at: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
191},
192(table) => {
193 return {
194 edit_permission_token_idx: index("custom_domain_routes_edit_permission_token_idx").on(table.edit_permission_token),
195 custom_domain_routes_domain_route_key: unique("custom_domain_routes_domain_route_key").on(table.domain, table.route),
196 }
197});
198
199export const site_standard_documents = pgTable("site_standard_documents", {
200 uri: text("uri").primaryKey().notNull(),
201 data: jsonb("data").notNull(),
202 indexed_at: timestamp("indexed_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
203 identity_did: text("identity_did").notNull().references(() => identities.atp_did, { onDelete: "cascade" } ),
204});
205
206export const custom_domains = pgTable("custom_domains", {
207 domain: text("domain").primaryKey().notNull(),
208 identity: text("identity").default('').references(() => identities.email, { onDelete: "cascade", onUpdate: "cascade" } ),
209 confirmed: boolean("confirmed").notNull(),
210 created_at: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
211 identity_id: uuid("identity_id").references(() => identities.id, { onDelete: "cascade" } ),
212});
213
214export const email_subscriptions_to_entity = pgTable("email_subscriptions_to_entity", {
215 id: uuid("id").defaultRandom().primaryKey().notNull(),
216 entity: uuid("entity").notNull().references(() => entities.id, { onDelete: "cascade" } ),
217 email: text("email").notNull(),
218 created_at: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
219 token: uuid("token").notNull().references(() => permission_tokens.id, { onDelete: "cascade" } ),
220 confirmed: boolean("confirmed").default(false).notNull(),
221 confirmation_code: text("confirmation_code").notNull(),
222});
223
224export const documents = pgTable("documents", {
225 uri: text("uri").primaryKey().notNull(),
226 data: jsonb("data").notNull(),
227 indexed_at: timestamp("indexed_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
228 bsky_like_count: integer("bsky_like_count").default(0).notNull(),
229});
230
231export const atp_poll_votes = pgTable("atp_poll_votes", {
232 uri: text("uri").primaryKey().notNull(),
233 record: jsonb("record").notNull(),
234 voter_did: text("voter_did").notNull(),
235 poll_uri: text("poll_uri").notNull().references(() => atp_poll_records.uri, { onDelete: "cascade", onUpdate: "cascade" } ),
236 poll_cid: text("poll_cid").notNull(),
237 indexed_at: timestamp("indexed_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
238},
239(table) => {
240 return {
241 poll_uri_idx: index("atp_poll_votes_poll_uri_idx").on(table.poll_uri),
242 voter_did_idx: index("atp_poll_votes_voter_did_idx").on(table.voter_did),
243 }
244});
245
246export const atp_poll_records = pgTable("atp_poll_records", {
247 uri: text("uri").primaryKey().notNull(),
248 cid: text("cid").notNull(),
249 record: jsonb("record").notNull(),
250 created_at: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
251});
252
253export const oauth_session_store = pgTable("oauth_session_store", {
254 key: text("key").primaryKey().notNull(),
255 session: jsonb("session").notNull(),
256});
257
258export const bsky_follows = pgTable("bsky_follows", {
259 identity: text("identity").default('').notNull().references(() => identities.atp_did, { onDelete: "cascade" } ),
260 follows: text("follows").notNull().references(() => identities.atp_did, { onDelete: "cascade" } ),
261},
262(table) => {
263 return {
264 bsky_follows_pkey: primaryKey({ columns: [table.identity, table.follows], name: "bsky_follows_pkey"}),
265 }
266});
267
268export const subscribers_to_publications = pgTable("subscribers_to_publications", {
269 identity: text("identity").notNull().references(() => identities.email, { onUpdate: "cascade" } ),
270 publication: text("publication").notNull().references(() => publications.uri),
271 created_at: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
272},
273(table) => {
274 return {
275 subscribers_to_publications_pkey: primaryKey({ columns: [table.identity, table.publication], name: "subscribers_to_publications_pkey"}),
276 }
277});
278
279export const site_standard_documents_in_publications = pgTable("site_standard_documents_in_publications", {
280 publication: text("publication").notNull().references(() => site_standard_publications.uri, { onDelete: "cascade" } ),
281 document: text("document").notNull().references(() => site_standard_documents.uri, { onDelete: "cascade" } ),
282 indexed_at: timestamp("indexed_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
283},
284(table) => {
285 return {
286 site_standard_documents_in_publications_pkey: primaryKey({ columns: [table.publication, table.document], name: "site_standard_documents_in_publications_pkey"}),
287 }
288});
289
290export const documents_in_publications = pgTable("documents_in_publications", {
291 publication: text("publication").notNull().references(() => publications.uri, { onDelete: "cascade" } ),
292 document: text("document").notNull().references(() => documents.uri, { onDelete: "cascade" } ),
293 indexed_at: timestamp("indexed_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
294},
295(table) => {
296 return {
297 publication_idx: index("documents_in_publications_publication_idx").on(table.publication),
298 documents_in_publications_pkey: primaryKey({ columns: [table.publication, table.document], name: "documents_in_publications_pkey"}),
299 }
300});
301
302export const document_mentions_in_bsky = pgTable("document_mentions_in_bsky", {
303 uri: text("uri").notNull().references(() => bsky_posts.uri, { onDelete: "cascade" } ),
304 link: text("link").notNull(),
305 document: text("document").notNull().references(() => documents.uri, { onDelete: "cascade" } ),
306 indexed_at: timestamp("indexed_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
307},
308(table) => {
309 return {
310 document_mentions_in_bsky_pkey: primaryKey({ columns: [table.uri, table.document], name: "document_mentions_in_bsky_pkey"}),
311 }
312});
313
314export const permission_token_on_homepage = pgTable("permission_token_on_homepage", {
315 token: uuid("token").notNull().references(() => permission_tokens.id, { onDelete: "cascade", onUpdate: "cascade" } ),
316 identity: uuid("identity").notNull().references(() => identities.id, { onDelete: "cascade" } ),
317 created_at: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
318 archived: boolean("archived"),
319},
320(table) => {
321 return {
322 permission_token_creator_pkey: primaryKey({ columns: [table.token, table.identity], name: "permission_token_creator_pkey"}),
323 }
324});
325
326export const publication_domains = pgTable("publication_domains", {
327 publication: text("publication").notNull().references(() => publications.uri, { onDelete: "cascade" } ),
328 domain: text("domain").notNull().references(() => custom_domains.domain, { onDelete: "cascade" } ),
329 created_at: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
330 identity: text("identity").notNull().references(() => identities.atp_did, { onDelete: "cascade", onUpdate: "cascade" } ),
331},
332(table) => {
333 return {
334 publication_idx: index("publication_domains_publication_idx").on(table.publication),
335 publication_domains_pkey: primaryKey({ columns: [table.publication, table.domain], name: "publication_domains_pkey"}),
336 }
337});
338
339export const publication_subscriptions = pgTable("publication_subscriptions", {
340 publication: text("publication").notNull().references(() => publications.uri, { onDelete: "cascade" } ),
341 identity: text("identity").notNull().references(() => identities.atp_did, { onDelete: "cascade" } ),
342 created_at: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
343 record: jsonb("record").notNull(),
344 uri: text("uri").notNull(),
345},
346(table) => {
347 return {
348 publication_idx: index("publication_subscriptions_publication_idx").on(table.publication),
349 publication_subscriptions_pkey: primaryKey({ columns: [table.publication, table.identity], name: "publication_subscriptions_pkey"}),
350 publication_subscriptions_uri_key: unique("publication_subscriptions_uri_key").on(table.uri),
351 }
352});
353
354export const site_standard_subscriptions = pgTable("site_standard_subscriptions", {
355 publication: text("publication").notNull().references(() => site_standard_publications.uri, { onDelete: "cascade" } ),
356 identity: text("identity").notNull().references(() => identities.atp_did, { onDelete: "cascade" } ),
357 created_at: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
358 record: jsonb("record").notNull(),
359 uri: text("uri").notNull(),
360},
361(table) => {
362 return {
363 site_standard_subscriptions_pkey: primaryKey({ columns: [table.publication, table.identity], name: "site_standard_subscriptions_pkey"}),
364 site_standard_subscriptions_uri_key: unique("site_standard_subscriptions_uri_key").on(table.uri),
365 }
366});
367
368export const leaflets_to_documents = pgTable("leaflets_to_documents", {
369 leaflet: uuid("leaflet").notNull().references(() => permission_tokens.id, { onDelete: "cascade", onUpdate: "cascade" } ),
370 document: text("document").notNull().references(() => documents.uri, { onDelete: "cascade", onUpdate: "cascade" } ),
371 created_at: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
372 title: text("title").default('').notNull(),
373 description: text("description").default('').notNull(),
374 tags: text("tags").default('RRAY[').array(),
375 cover_image: text("cover_image"),
376},
377(table) => {
378 return {
379 leaflets_to_documents_pkey: primaryKey({ columns: [table.leaflet, table.document], name: "leaflets_to_documents_pkey"}),
380 }
381});
382
383export const permission_token_rights = pgTable("permission_token_rights", {
384 token: uuid("token").notNull().references(() => permission_tokens.id, { onDelete: "cascade", onUpdate: "cascade" } ),
385 entity_set: uuid("entity_set").notNull().references(() => entity_sets.id, { onDelete: "cascade", onUpdate: "cascade" } ),
386 read: boolean("read").default(false).notNull(),
387 write: boolean("write").default(false).notNull(),
388 created_at: timestamp("created_at", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
389 create_token: boolean("create_token").default(false).notNull(),
390 change_entity_set: boolean("change_entity_set").default(false).notNull(),
391},
392(table) => {
393 return {
394 token_idx: index("permission_token_rights_token_idx").on(table.token),
395 entity_set_idx: index("permission_token_rights_entity_set_idx").on(table.entity_set),
396 permission_token_rights_pkey: primaryKey({ columns: [table.token, table.entity_set], name: "permission_token_rights_pkey"}),
397 }
398});
399
400export const leaflets_in_publications = pgTable("leaflets_in_publications", {
401 publication: text("publication").notNull().references(() => publications.uri, { onDelete: "cascade" } ),
402 doc: text("doc").default('').references(() => documents.uri, { onDelete: "set null" } ),
403 leaflet: uuid("leaflet").notNull().references(() => permission_tokens.id, { onDelete: "cascade", onUpdate: "cascade" } ),
404 description: text("description").default('').notNull(),
405 title: text("title").default('').notNull(),
406 archived: boolean("archived"),
407 tags: text("tags").default('RRAY[').array(),
408 cover_image: text("cover_image"),
409},
410(table) => {
411 return {
412 leaflet_idx: index("leaflets_in_publications_leaflet_idx").on(table.leaflet),
413 publication_idx: index("leaflets_in_publications_publication_idx").on(table.publication),
414 leaflets_in_publications_pkey: primaryKey({ columns: [table.publication, table.leaflet], name: "leaflets_in_publications_pkey"}),
415 }
416});