leaflet.pub
a tool for shared writing and social publishing
awarm.space
1-- WARNING: This schema is for context only and is not meant to be run.
2-- Table order and constraints may not be valid for execution.
3
4CREATE TABLE public.atp_poll_records (
5 uri text NOT NULL,
6 cid text NOT NULL,
7 record jsonb NOT NULL,
8 created_at timestamp with time zone NOT NULL DEFAULT now(),
9 CONSTRAINT atp_poll_records_pkey PRIMARY KEY (uri)
10);
11CREATE TABLE public.atp_poll_votes (
12 uri text NOT NULL,
13 record jsonb NOT NULL,
14 voter_did text NOT NULL,
15 poll_uri text NOT NULL,
16 poll_cid text NOT NULL,
17 indexed_at timestamp with time zone NOT NULL DEFAULT now(),
18 CONSTRAINT atp_poll_votes_pkey PRIMARY KEY (uri),
19 CONSTRAINT atp_poll_votes_poll_uri_fkey FOREIGN KEY (poll_uri) REFERENCES public.atp_poll_records(uri)
20);
21CREATE TABLE public.bsky_follows (
22 identity text NOT NULL DEFAULT ''::text,
23 follows text NOT NULL,
24 CONSTRAINT bsky_follows_pkey PRIMARY KEY (identity, follows),
25 CONSTRAINT bsky_follows_follows_fkey FOREIGN KEY (follows) REFERENCES public.identities(atp_did),
26 CONSTRAINT bsky_follows_identity_fkey FOREIGN KEY (identity) REFERENCES public.identities(atp_did)
27);
28CREATE TABLE public.bsky_posts (
29 uri text NOT NULL,
30 indexed_at timestamp with time zone NOT NULL DEFAULT now(),
31 post_view jsonb NOT NULL,
32 cid text NOT NULL,
33 CONSTRAINT bsky_posts_pkey PRIMARY KEY (uri)
34);
35CREATE TABLE public.bsky_profiles (
36 did text NOT NULL,
37 record jsonb NOT NULL,
38 indexed_at timestamp with time zone NOT NULL DEFAULT now(),
39 handle text,
40 CONSTRAINT bsky_profiles_pkey PRIMARY KEY (did),
41 CONSTRAINT bsky_profiles_did_fkey FOREIGN KEY (did) REFERENCES public.identities(atp_did)
42);
43CREATE TABLE public.comments_on_documents (
44 uri text NOT NULL,
45 record jsonb NOT NULL,
46 document text,
47 indexed_at timestamp with time zone NOT NULL DEFAULT now(),
48 profile text,
49 CONSTRAINT comments_on_documents_pkey PRIMARY KEY (uri),
50 CONSTRAINT comments_on_documents_document_fkey FOREIGN KEY (document) REFERENCES public.documents(uri),
51 CONSTRAINT comments_on_documents_profile_fkey FOREIGN KEY (profile) REFERENCES public.bsky_profiles(did)
52);
53CREATE TABLE public.custom_domain_routes (
54 id uuid NOT NULL DEFAULT gen_random_uuid(),
55 domain text NOT NULL,
56 route text NOT NULL,
57 edit_permission_token uuid NOT NULL,
58 view_permission_token uuid NOT NULL,
59 created_at timestamp with time zone NOT NULL DEFAULT now(),
60 CONSTRAINT custom_domain_routes_pkey PRIMARY KEY (id),
61 CONSTRAINT custom_domain_routes_edit_permission_token_fkey FOREIGN KEY (edit_permission_token) REFERENCES public.permission_tokens(id),
62 CONSTRAINT custom_domain_routes_view_permission_token_fkey FOREIGN KEY (view_permission_token) REFERENCES public.permission_tokens(id),
63 CONSTRAINT custom_domain_routes_domain_fkey FOREIGN KEY (domain) REFERENCES public.custom_domains(domain)
64);
65CREATE TABLE public.custom_domains (
66 domain text NOT NULL,
67 identity text DEFAULT ''::text,
68 confirmed boolean NOT NULL,
69 created_at timestamp with time zone NOT NULL DEFAULT now(),
70 identity_id uuid,
71 CONSTRAINT custom_domains_pkey PRIMARY KEY (domain),
72 CONSTRAINT custom_domains_identity_id_fkey FOREIGN KEY (identity_id) REFERENCES public.identities(id),
73 CONSTRAINT custom_domains_identity_fkey FOREIGN KEY (identity) REFERENCES public.identities(email)
74);
75CREATE TABLE public.document_mentions_in_bsky (
76 uri text NOT NULL,
77 link text NOT NULL,
78 document text NOT NULL,
79 indexed_at timestamp with time zone NOT NULL DEFAULT now(),
80 CONSTRAINT document_mentions_in_bsky_pkey PRIMARY KEY (uri, document),
81 CONSTRAINT document_mentions_in_bsky_document_fkey FOREIGN KEY (document) REFERENCES public.documents(uri),
82 CONSTRAINT document_mentions_in_bsky_uri_fkey FOREIGN KEY (uri) REFERENCES public.bsky_posts(uri)
83);
84CREATE TABLE public.documents (
85 uri text NOT NULL,
86 data jsonb NOT NULL,
87 indexed_at timestamp with time zone NOT NULL DEFAULT now(),
88 sort_date timestamp with time zone DEFAULT LEAST(COALESCE(parse_iso_timestamp((data ->> 'publishedAt'::text)), indexed_at), indexed_at),
89 bsky_like_count integer NOT NULL DEFAULT 0,
90 CONSTRAINT documents_pkey PRIMARY KEY (uri)
91);
92CREATE TABLE public.documents_in_publications (
93 publication text NOT NULL,
94 document text NOT NULL,
95 indexed_at timestamp with time zone NOT NULL DEFAULT now(),
96 CONSTRAINT documents_in_publications_pkey PRIMARY KEY (publication, document),
97 CONSTRAINT documents_in_publications_document_fkey FOREIGN KEY (document) REFERENCES public.documents(uri),
98 CONSTRAINT documents_in_publications_publication_fkey FOREIGN KEY (publication) REFERENCES public.publications(uri)
99);
100CREATE TABLE public.email_auth_tokens (
101 id uuid NOT NULL DEFAULT gen_random_uuid(),
102 created_at timestamp with time zone NOT NULL DEFAULT now(),
103 confirmed boolean NOT NULL DEFAULT false,
104 email text,
105 confirmation_code text NOT NULL,
106 identity uuid,
107 CONSTRAINT email_auth_tokens_pkey PRIMARY KEY (id),
108 CONSTRAINT email_auth_tokens_identity_fkey FOREIGN KEY (identity) REFERENCES public.identities(id)
109);
110CREATE TABLE public.email_subscriptions_to_entity (
111 id uuid NOT NULL DEFAULT gen_random_uuid(),
112 entity uuid NOT NULL,
113 email text NOT NULL,
114 created_at timestamp with time zone NOT NULL DEFAULT now(),
115 token uuid NOT NULL,
116 confirmed boolean NOT NULL DEFAULT false,
117 confirmation_code text NOT NULL,
118 CONSTRAINT email_subscriptions_to_entity_pkey PRIMARY KEY (id),
119 CONSTRAINT email_subscriptions_to_entity_entity_fkey FOREIGN KEY (entity) REFERENCES public.entities(id),
120 CONSTRAINT email_subscriptions_to_entity_token_fkey FOREIGN KEY (token) REFERENCES public.permission_tokens(id)
121);
122CREATE TABLE public.entities (
123 id uuid NOT NULL,
124 created_at timestamp with time zone NOT NULL DEFAULT now(),
125 set uuid NOT NULL,
126 CONSTRAINT entities_pkey PRIMARY KEY (id),
127 CONSTRAINT entities_set_fkey FOREIGN KEY (set) REFERENCES public.entity_sets(id)
128);
129CREATE TABLE public.entity_sets (
130 id uuid NOT NULL DEFAULT gen_random_uuid(),
131 created_at timestamp with time zone NOT NULL DEFAULT now(),
132 CONSTRAINT entity_sets_pkey PRIMARY KEY (id)
133);
134CREATE TABLE public.facts (
135 id uuid NOT NULL,
136 entity uuid NOT NULL,
137 attribute text NOT NULL,
138 data jsonb NOT NULL,
139 created_at timestamp without time zone NOT NULL DEFAULT now(),
140 updated_at timestamp without time zone,
141 version bigint NOT NULL DEFAULT '0'::bigint,
142 CONSTRAINT facts_pkey PRIMARY KEY (id),
143 CONSTRAINT facts_entity_fkey FOREIGN KEY (entity) REFERENCES public.entities(id)
144);
145CREATE TABLE public.identities (
146 id uuid NOT NULL DEFAULT gen_random_uuid(),
147 created_at timestamp with time zone NOT NULL DEFAULT now(),
148 home_page uuid NOT NULL DEFAULT create_identity_homepage(),
149 email text UNIQUE,
150 atp_did text UNIQUE,
151 interface_state jsonb,
152 metadata jsonb,
153 CONSTRAINT identities_pkey PRIMARY KEY (id),
154 CONSTRAINT identities_home_page_fkey FOREIGN KEY (home_page) REFERENCES public.permission_tokens(id)
155);
156CREATE TABLE public.leaflets_in_publications (
157 publication text NOT NULL,
158 doc text DEFAULT ''::text,
159 leaflet uuid NOT NULL,
160 description text NOT NULL DEFAULT ''::text,
161 title text NOT NULL DEFAULT ''::text,
162 archived boolean,
163 tags ARRAY DEFAULT ARRAY[]::text[],
164 cover_image text,
165 preferences jsonb,
166 CONSTRAINT leaflets_in_publications_pkey PRIMARY KEY (publication, leaflet),
167 CONSTRAINT leaflets_in_publications_publication_fkey FOREIGN KEY (publication) REFERENCES public.publications(uri),
168 CONSTRAINT leaflets_in_publications_leaflet_fkey FOREIGN KEY (leaflet) REFERENCES public.permission_tokens(id),
169 CONSTRAINT leaflets_in_publications_doc_fkey FOREIGN KEY (doc) REFERENCES public.documents(uri)
170);
171CREATE TABLE public.leaflets_to_documents (
172 leaflet uuid NOT NULL,
173 document text NOT NULL,
174 created_at timestamp with time zone NOT NULL DEFAULT now(),
175 title text NOT NULL DEFAULT ''::text,
176 description text NOT NULL DEFAULT ''::text,
177 tags ARRAY DEFAULT ARRAY[]::text[],
178 cover_image text,
179 preferences jsonb,
180 CONSTRAINT leaflets_to_documents_pkey PRIMARY KEY (leaflet, document),
181 CONSTRAINT leaflets_to_documents_document_fkey FOREIGN KEY (document) REFERENCES public.documents(uri),
182 CONSTRAINT leaflets_to_documents_leaflet_fkey FOREIGN KEY (leaflet) REFERENCES public.permission_tokens(id)
183);
184CREATE TABLE public.notifications (
185 recipient text NOT NULL,
186 created_at timestamp with time zone NOT NULL DEFAULT now(),
187 read boolean NOT NULL DEFAULT false,
188 data jsonb NOT NULL,
189 id uuid NOT NULL,
190 CONSTRAINT notifications_pkey PRIMARY KEY (id),
191 CONSTRAINT notifications_recipient_fkey FOREIGN KEY (recipient) REFERENCES public.identities(atp_did)
192);
193CREATE TABLE public.oauth_session_store (
194 key text NOT NULL,
195 session jsonb NOT NULL,
196 CONSTRAINT oauth_session_store_pkey PRIMARY KEY (key)
197);
198CREATE TABLE public.oauth_state_store (
199 key text NOT NULL,
200 state jsonb NOT NULL,
201 CONSTRAINT oauth_state_store_pkey PRIMARY KEY (key)
202);
203CREATE TABLE public.permission_token_on_homepage (
204 token uuid NOT NULL,
205 identity uuid NOT NULL,
206 created_at timestamp with time zone NOT NULL DEFAULT now(),
207 archived boolean,
208 CONSTRAINT permission_token_on_homepage_pkey PRIMARY KEY (token, identity),
209 CONSTRAINT permission_token_creator_identity_fkey FOREIGN KEY (identity) REFERENCES public.identities(id),
210 CONSTRAINT permission_token_on_homepage_token_fkey FOREIGN KEY (token) REFERENCES public.permission_tokens(id)
211);
212CREATE TABLE public.permission_token_rights (
213 token uuid NOT NULL,
214 entity_set uuid NOT NULL,
215 read boolean NOT NULL DEFAULT false,
216 write boolean NOT NULL DEFAULT false,
217 created_at timestamp with time zone NOT NULL DEFAULT now(),
218 create_token boolean NOT NULL DEFAULT false,
219 change_entity_set boolean NOT NULL DEFAULT false,
220 CONSTRAINT permission_token_rights_pkey PRIMARY KEY (token, entity_set),
221 CONSTRAINT permission_token_rights_entity_set_fkey FOREIGN KEY (entity_set) REFERENCES public.entity_sets(id),
222 CONSTRAINT permission_token_rights_token_fkey FOREIGN KEY (token) REFERENCES public.permission_tokens(id)
223);
224CREATE TABLE public.permission_tokens (
225 id uuid NOT NULL DEFAULT gen_random_uuid(),
226 root_entity uuid NOT NULL,
227 blocked_by_admin boolean,
228 CONSTRAINT permission_tokens_pkey PRIMARY KEY (id),
229 CONSTRAINT permission_tokens_root_entity_fkey FOREIGN KEY (root_entity) REFERENCES public.entities(id)
230);
231CREATE TABLE public.phone_number_auth_tokens (
232 id uuid NOT NULL DEFAULT gen_random_uuid(),
233 created_at timestamp with time zone NOT NULL DEFAULT now(),
234 confirmed boolean NOT NULL DEFAULT false,
235 confirmation_code text NOT NULL,
236 phone_number text NOT NULL,
237 country_code text NOT NULL,
238 CONSTRAINT phone_number_auth_tokens_pkey PRIMARY KEY (id)
239);
240CREATE TABLE public.phone_rsvps_to_entity (
241 created_at timestamp with time zone NOT NULL DEFAULT now(),
242 phone_number text NOT NULL,
243 country_code text NOT NULL,
244 status USER-DEFINED NOT NULL,
245 id uuid NOT NULL DEFAULT gen_random_uuid(),
246 entity uuid NOT NULL,
247 name text NOT NULL DEFAULT ''::text,
248 plus_ones smallint NOT NULL DEFAULT '0'::smallint,
249 CONSTRAINT phone_rsvps_to_entity_pkey PRIMARY KEY (id),
250 CONSTRAINT phone_rsvps_to_entity_entity_fkey FOREIGN KEY (entity) REFERENCES public.entities(id)
251);
252CREATE TABLE public.poll_votes_on_entity (
253 id uuid NOT NULL DEFAULT gen_random_uuid(),
254 created_at timestamp with time zone NOT NULL DEFAULT now(),
255 poll_entity uuid NOT NULL,
256 option_entity uuid NOT NULL,
257 voter_token uuid NOT NULL,
258 CONSTRAINT poll_votes_on_entity_pkey PRIMARY KEY (id),
259 CONSTRAINT poll_votes_on_entity_option_entity_fkey FOREIGN KEY (option_entity) REFERENCES public.entities(id),
260 CONSTRAINT poll_votes_on_entity_poll_entity_fkey FOREIGN KEY (poll_entity) REFERENCES public.entities(id)
261);
262CREATE TABLE public.publication_domains (
263 publication text NOT NULL,
264 domain text NOT NULL,
265 created_at timestamp with time zone NOT NULL DEFAULT now(),
266 identity text NOT NULL,
267 CONSTRAINT publication_domains_pkey PRIMARY KEY (publication, domain),
268 CONSTRAINT publication_domains_domain_fkey FOREIGN KEY (domain) REFERENCES public.custom_domains(domain),
269 CONSTRAINT publication_domains_publication_fkey FOREIGN KEY (publication) REFERENCES public.publications(uri),
270 CONSTRAINT publication_domains_identity_fkey FOREIGN KEY (identity) REFERENCES public.identities(atp_did)
271);
272CREATE TABLE public.publication_subscriptions (
273 publication text NOT NULL,
274 identity text NOT NULL,
275 created_at timestamp with time zone NOT NULL DEFAULT now(),
276 record jsonb NOT NULL,
277 uri text NOT NULL UNIQUE,
278 CONSTRAINT publication_subscriptions_pkey PRIMARY KEY (publication, identity),
279 CONSTRAINT publication_subscriptions_publication_fkey FOREIGN KEY (publication) REFERENCES public.publications(uri),
280 CONSTRAINT publication_subscriptions_identity_fkey FOREIGN KEY (identity) REFERENCES public.identities(atp_did)
281);
282CREATE TABLE public.publications (
283 uri text NOT NULL,
284 indexed_at timestamp with time zone NOT NULL DEFAULT now(),
285 name text NOT NULL,
286 identity_did text NOT NULL,
287 record jsonb,
288 CONSTRAINT publications_pkey PRIMARY KEY (uri),
289 CONSTRAINT publications_identity_did_fkey FOREIGN KEY (identity_did) REFERENCES public.identities(atp_did)
290);
291CREATE TABLE public.recommends_on_documents (
292 uri text NOT NULL,
293 record jsonb NOT NULL,
294 document text NOT NULL,
295 recommender_did text NOT NULL,
296 indexed_at timestamp with time zone NOT NULL DEFAULT now(),
297 CONSTRAINT recommends_on_documents_pkey PRIMARY KEY (uri),
298 CONSTRAINT recommends_on_documents_document_fkey FOREIGN KEY (document) REFERENCES public.documents(uri),
299 CONSTRAINT recommends_on_documents_recommender_did_fkey FOREIGN KEY (recommender_did) REFERENCES public.identities(atp_did)
300);
301CREATE TABLE public.replicache_clients (
302 client_id text NOT NULL,
303 client_group text NOT NULL,
304 last_mutation bigint NOT NULL,
305 CONSTRAINT replicache_clients_pkey PRIMARY KEY (client_id)
306);
307CREATE TABLE public.subscribers_to_publications (
308 identity text NOT NULL,
309 publication text NOT NULL,
310 created_at timestamp with time zone NOT NULL DEFAULT now(),
311 CONSTRAINT subscribers_to_publications_pkey PRIMARY KEY (identity, publication),
312 CONSTRAINT subscribers_to_publications_identity_fkey FOREIGN KEY (identity) REFERENCES public.identities(email),
313 CONSTRAINT subscribers_to_publications_publication_fkey FOREIGN KEY (publication) REFERENCES public.publications(uri)
314);