Something went wrong. Try again.
a tool for shared writing and social publishing
Something went wrong. Try again.
leaflet database.sql
14 kB · 314 lines
SQL
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315-- WARNING: This schema is for context only and is not meant to be run.-- Table order and constraints may not be valid for execution.
CREATE TABLE public.atp_poll_records ( uri text NOT NULL, cid text NOT NULL, record jsonb NOT NULL, created_at timestamp with time zone NOT NULL DEFAULT now(), CONSTRAINT atp_poll_records_pkey PRIMARY KEY (uri));CREATE TABLE public.atp_poll_votes ( uri text NOT NULL, record jsonb NOT NULL, voter_did text NOT NULL, poll_uri text NOT NULL, poll_cid text NOT NULL, indexed_at timestamp with time zone NOT NULL DEFAULT now(), CONSTRAINT atp_poll_votes_pkey PRIMARY KEY (uri), CONSTRAINT atp_poll_votes_poll_uri_fkey FOREIGN KEY (poll_uri) REFERENCES public.atp_poll_records(uri));CREATE TABLE public.bsky_follows ( identity text NOT NULL DEFAULT ''::text, follows text NOT NULL, CONSTRAINT bsky_follows_pkey PRIMARY KEY (identity, follows), CONSTRAINT bsky_follows_follows_fkey FOREIGN KEY (follows) REFERENCES public.identities(atp_did), CONSTRAINT bsky_follows_identity_fkey FOREIGN KEY (identity) REFERENCES public.identities(atp_did));CREATE TABLE public.bsky_posts ( uri text NOT NULL, indexed_at timestamp with time zone NOT NULL DEFAULT now(), post_view jsonb NOT NULL, cid text NOT NULL, CONSTRAINT bsky_posts_pkey PRIMARY KEY (uri));CREATE TABLE public.bsky_profiles ( did text NOT NULL, record jsonb NOT NULL, indexed_at timestamp with time zone NOT NULL DEFAULT now(), handle text, CONSTRAINT bsky_profiles_pkey PRIMARY KEY (did), CONSTRAINT bsky_profiles_did_fkey FOREIGN KEY (did) REFERENCES public.identities(atp_did));CREATE TABLE public.comments_on_documents ( uri text NOT NULL, record jsonb NOT NULL, document text, indexed_at timestamp with time zone NOT NULL DEFAULT now(), profile text, CONSTRAINT comments_on_documents_pkey PRIMARY KEY (uri), CONSTRAINT comments_on_documents_document_fkey FOREIGN KEY (document) REFERENCES public.documents(uri), CONSTRAINT comments_on_documents_profile_fkey FOREIGN KEY (profile) REFERENCES public.bsky_profiles(did));CREATE TABLE public.custom_domain_routes ( id uuid NOT NULL DEFAULT gen_random_uuid(), domain text NOT NULL, route text NOT NULL, edit_permission_token uuid NOT NULL, view_permission_token uuid NOT NULL, created_at timestamp with time zone NOT NULL DEFAULT now(), CONSTRAINT custom_domain_routes_pkey PRIMARY KEY (id), CONSTRAINT custom_domain_routes_edit_permission_token_fkey FOREIGN KEY (edit_permission_token) REFERENCES public.permission_tokens(id), CONSTRAINT custom_domain_routes_view_permission_token_fkey FOREIGN KEY (view_permission_token) REFERENCES public.permission_tokens(id), CONSTRAINT custom_domain_routes_domain_fkey FOREIGN KEY (domain) REFERENCES public.custom_domains(domain));CREATE TABLE public.custom_domains ( domain text NOT NULL, identity text DEFAULT ''::text, confirmed boolean NOT NULL, created_at timestamp with time zone NOT NULL DEFAULT now(), identity_id uuid, CONSTRAINT custom_domains_pkey PRIMARY KEY (domain), CONSTRAINT custom_domains_identity_id_fkey FOREIGN KEY (identity_id) REFERENCES public.identities(id), CONSTRAINT custom_domains_identity_fkey FOREIGN KEY (identity) REFERENCES public.identities(email));CREATE TABLE public.document_mentions_in_bsky ( uri text NOT NULL, link text NOT NULL, document text NOT NULL, indexed_at timestamp with time zone NOT NULL DEFAULT now(), CONSTRAINT document_mentions_in_bsky_pkey PRIMARY KEY (uri, document), CONSTRAINT document_mentions_in_bsky_document_fkey FOREIGN KEY (document) REFERENCES public.documents(uri), CONSTRAINT document_mentions_in_bsky_uri_fkey FOREIGN KEY (uri) REFERENCES public.bsky_posts(uri));CREATE TABLE public.documents ( uri text NOT NULL, data jsonb NOT NULL, indexed_at timestamp with time zone NOT NULL DEFAULT now(), sort_date timestamp with time zone DEFAULT LEAST(COALESCE(parse_iso_timestamp((data ->> 'publishedAt'::text)), indexed_at), indexed_at), bsky_like_count integer NOT NULL DEFAULT 0, CONSTRAINT documents_pkey PRIMARY KEY (uri));CREATE TABLE public.documents_in_publications ( publication text NOT NULL, document text NOT NULL, indexed_at timestamp with time zone NOT NULL DEFAULT now(), CONSTRAINT documents_in_publications_pkey PRIMARY KEY (publication, document), CONSTRAINT documents_in_publications_document_fkey FOREIGN KEY (document) REFERENCES public.documents(uri), CONSTRAINT documents_in_publications_publication_fkey FOREIGN KEY (publication) REFERENCES public.publications(uri));CREATE TABLE public.email_auth_tokens ( id uuid NOT NULL DEFAULT gen_random_uuid(), created_at timestamp with time zone NOT NULL DEFAULT now(), confirmed boolean NOT NULL DEFAULT false, email text, confirmation_code text NOT NULL, identity uuid, CONSTRAINT email_auth_tokens_pkey PRIMARY KEY (id), CONSTRAINT email_auth_tokens_identity_fkey FOREIGN KEY (identity) REFERENCES public.identities(id));CREATE TABLE public.email_subscriptions_to_entity ( id uuid NOT NULL DEFAULT gen_random_uuid(), entity uuid NOT NULL, email text NOT NULL, created_at timestamp with time zone NOT NULL DEFAULT now(), token uuid NOT NULL, confirmed boolean NOT NULL DEFAULT false, confirmation_code text NOT NULL, CONSTRAINT email_subscriptions_to_entity_pkey PRIMARY KEY (id), CONSTRAINT email_subscriptions_to_entity_entity_fkey FOREIGN KEY (entity) REFERENCES public.entities(id), CONSTRAINT email_subscriptions_to_entity_token_fkey FOREIGN KEY (token) REFERENCES public.permission_tokens(id));CREATE TABLE public.entities ( id uuid NOT NULL, created_at timestamp with time zone NOT NULL DEFAULT now(), set uuid NOT NULL, CONSTRAINT entities_pkey PRIMARY KEY (id), CONSTRAINT entities_set_fkey FOREIGN KEY (set) REFERENCES public.entity_sets(id));CREATE TABLE public.entity_sets ( id uuid NOT NULL DEFAULT gen_random_uuid(), created_at timestamp with time zone NOT NULL DEFAULT now(), CONSTRAINT entity_sets_pkey PRIMARY KEY (id));CREATE TABLE public.facts ( id uuid NOT NULL, entity uuid NOT NULL, attribute text NOT NULL, data jsonb NOT NULL, created_at timestamp without time zone NOT NULL DEFAULT now(), updated_at timestamp without time zone, version bigint NOT NULL DEFAULT '0'::bigint, CONSTRAINT facts_pkey PRIMARY KEY (id), CONSTRAINT facts_entity_fkey FOREIGN KEY (entity) REFERENCES public.entities(id));CREATE TABLE public.identities ( id uuid NOT NULL DEFAULT gen_random_uuid(), created_at timestamp with time zone NOT NULL DEFAULT now(), home_page uuid NOT NULL DEFAULT create_identity_homepage(), email text UNIQUE, atp_did text UNIQUE, interface_state jsonb, metadata jsonb, CONSTRAINT identities_pkey PRIMARY KEY (id), CONSTRAINT identities_home_page_fkey FOREIGN KEY (home_page) REFERENCES public.permission_tokens(id));CREATE TABLE public.leaflets_in_publications ( publication text NOT NULL, doc text DEFAULT ''::text, leaflet uuid NOT NULL, description text NOT NULL DEFAULT ''::text, title text NOT NULL DEFAULT ''::text, archived boolean, tags ARRAY DEFAULT ARRAY[]::text[], cover_image text, preferences jsonb, CONSTRAINT leaflets_in_publications_pkey PRIMARY KEY (publication, leaflet), CONSTRAINT leaflets_in_publications_publication_fkey FOREIGN KEY (publication) REFERENCES public.publications(uri), CONSTRAINT leaflets_in_publications_leaflet_fkey FOREIGN KEY (leaflet) REFERENCES public.permission_tokens(id), CONSTRAINT leaflets_in_publications_doc_fkey FOREIGN KEY (doc) REFERENCES public.documents(uri));CREATE TABLE public.leaflets_to_documents ( leaflet uuid NOT NULL, document text NOT NULL, created_at timestamp with time zone NOT NULL DEFAULT now(), title text NOT NULL DEFAULT ''::text, description text NOT NULL DEFAULT ''::text, tags ARRAY DEFAULT ARRAY[]::text[], cover_image text, preferences jsonb, CONSTRAINT leaflets_to_documents_pkey PRIMARY KEY (leaflet, document), CONSTRAINT leaflets_to_documents_document_fkey FOREIGN KEY (document) REFERENCES public.documents(uri), CONSTRAINT leaflets_to_documents_leaflet_fkey FOREIGN KEY (leaflet) REFERENCES public.permission_tokens(id));CREATE TABLE public.notifications ( recipient text NOT NULL, created_at timestamp with time zone NOT NULL DEFAULT now(), read boolean NOT NULL DEFAULT false, data jsonb NOT NULL, id uuid NOT NULL, CONSTRAINT notifications_pkey PRIMARY KEY (id), CONSTRAINT notifications_recipient_fkey FOREIGN KEY (recipient) REFERENCES public.identities(atp_did));CREATE TABLE public.oauth_session_store ( key text NOT NULL, session jsonb NOT NULL, CONSTRAINT oauth_session_store_pkey PRIMARY KEY (key));CREATE TABLE public.oauth_state_store ( key text NOT NULL, state jsonb NOT NULL, CONSTRAINT oauth_state_store_pkey PRIMARY KEY (key));CREATE TABLE public.permission_token_on_homepage ( token uuid NOT NULL, identity uuid NOT NULL, created_at timestamp with time zone NOT NULL DEFAULT now(), archived boolean, CONSTRAINT permission_token_on_homepage_pkey PRIMARY KEY (token, identity), CONSTRAINT permission_token_creator_identity_fkey FOREIGN KEY (identity) REFERENCES public.identities(id), CONSTRAINT permission_token_on_homepage_token_fkey FOREIGN KEY (token) REFERENCES public.permission_tokens(id));CREATE TABLE public.permission_token_rights ( token uuid NOT NULL, entity_set uuid NOT NULL, read boolean NOT NULL DEFAULT false, write boolean NOT NULL DEFAULT false, created_at timestamp with time zone NOT NULL DEFAULT now(), create_token boolean NOT NULL DEFAULT false, change_entity_set boolean NOT NULL DEFAULT false, CONSTRAINT permission_token_rights_pkey PRIMARY KEY (token, entity_set), CONSTRAINT permission_token_rights_entity_set_fkey FOREIGN KEY (entity_set) REFERENCES public.entity_sets(id), CONSTRAINT permission_token_rights_token_fkey FOREIGN KEY (token) REFERENCES public.permission_tokens(id));CREATE TABLE public.permission_tokens ( id uuid NOT NULL DEFAULT gen_random_uuid(), root_entity uuid NOT NULL, blocked_by_admin boolean, CONSTRAINT permission_tokens_pkey PRIMARY KEY (id), CONSTRAINT permission_tokens_root_entity_fkey FOREIGN KEY (root_entity) REFERENCES public.entities(id));CREATE TABLE public.phone_number_auth_tokens ( id uuid NOT NULL DEFAULT gen_random_uuid(), created_at timestamp with time zone NOT NULL DEFAULT now(), confirmed boolean NOT NULL DEFAULT false, confirmation_code text NOT NULL, phone_number text NOT NULL, country_code text NOT NULL, CONSTRAINT phone_number_auth_tokens_pkey PRIMARY KEY (id));CREATE TABLE public.phone_rsvps_to_entity ( created_at timestamp with time zone NOT NULL DEFAULT now(), phone_number text NOT NULL, country_code text NOT NULL, status USER-DEFINED NOT NULL, id uuid NOT NULL DEFAULT gen_random_uuid(), entity uuid NOT NULL, name text NOT NULL DEFAULT ''::text, plus_ones smallint NOT NULL DEFAULT '0'::smallint, CONSTRAINT phone_rsvps_to_entity_pkey PRIMARY KEY (id), CONSTRAINT phone_rsvps_to_entity_entity_fkey FOREIGN KEY (entity) REFERENCES public.entities(id));CREATE TABLE public.poll_votes_on_entity ( id uuid NOT NULL DEFAULT gen_random_uuid(), created_at timestamp with time zone NOT NULL DEFAULT now(), poll_entity uuid NOT NULL, option_entity uuid NOT NULL, voter_token uuid NOT NULL, CONSTRAINT poll_votes_on_entity_pkey PRIMARY KEY (id), CONSTRAINT poll_votes_on_entity_option_entity_fkey FOREIGN KEY (option_entity) REFERENCES public.entities(id), CONSTRAINT poll_votes_on_entity_poll_entity_fkey FOREIGN KEY (poll_entity) REFERENCES public.entities(id));CREATE TABLE public.publication_domains ( publication text NOT NULL, domain text NOT NULL, created_at timestamp with time zone NOT NULL DEFAULT now(), identity text NOT NULL, CONSTRAINT publication_domains_pkey PRIMARY KEY (publication, domain), CONSTRAINT publication_domains_domain_fkey FOREIGN KEY (domain) REFERENCES public.custom_domains(domain), CONSTRAINT publication_domains_publication_fkey FOREIGN KEY (publication) REFERENCES public.publications(uri), CONSTRAINT publication_domains_identity_fkey FOREIGN KEY (identity) REFERENCES public.identities(atp_did));CREATE TABLE public.publication_subscriptions ( publication text NOT NULL, identity text NOT NULL, created_at timestamp with time zone NOT NULL DEFAULT now(), record jsonb NOT NULL, uri text NOT NULL UNIQUE, CONSTRAINT publication_subscriptions_pkey PRIMARY KEY (publication, identity), CONSTRAINT publication_subscriptions_publication_fkey FOREIGN KEY (publication) REFERENCES public.publications(uri), CONSTRAINT publication_subscriptions_identity_fkey FOREIGN KEY (identity) REFERENCES public.identities(atp_did));CREATE TABLE public.publications ( uri text NOT NULL, indexed_at timestamp with time zone NOT NULL DEFAULT now(), name text NOT NULL, identity_did text NOT NULL, record jsonb, CONSTRAINT publications_pkey PRIMARY KEY (uri), CONSTRAINT publications_identity_did_fkey FOREIGN KEY (identity_did) REFERENCES public.identities(atp_did));CREATE TABLE public.recommends_on_documents ( uri text NOT NULL, record jsonb NOT NULL, document text NOT NULL, recommender_did text NOT NULL, indexed_at timestamp with time zone NOT NULL DEFAULT now(), CONSTRAINT recommends_on_documents_pkey PRIMARY KEY (uri), CONSTRAINT recommends_on_documents_document_fkey FOREIGN KEY (document) REFERENCES public.documents(uri), CONSTRAINT recommends_on_documents_recommender_did_fkey FOREIGN KEY (recommender_did) REFERENCES public.identities(atp_did));CREATE TABLE public.replicache_clients ( client_id text NOT NULL, client_group text NOT NULL, last_mutation bigint NOT NULL, CONSTRAINT replicache_clients_pkey PRIMARY KEY (client_id));CREATE TABLE public.subscribers_to_publications ( identity text NOT NULL, publication text NOT NULL, created_at timestamp with time zone NOT NULL DEFAULT now(), CONSTRAINT subscribers_to_publications_pkey PRIMARY KEY (identity, publication), CONSTRAINT subscribers_to_publications_identity_fkey FOREIGN KEY (identity) REFERENCES public.identities(email), CONSTRAINT subscribers_to_publications_publication_fkey FOREIGN KEY (publication) REFERENCES public.publications(uri));