moth11.net
backend for xcvr appview
1CREATE TABLE profiles (
2 did TEXT PRIMARY KEY,
3 display_name TEXT,
4 default_nick TEXT,
5 status TEXT,
6 avatar_cid TEXT,
7 avatar_mime TEXT,
8 color INTEGER CHECK (color BETWEEN 0 and 16777215),
9 indexed_at TIMESTAMPTZ NOT NULL DEFAULT now()
10);
11
12CREATE TABLE profile_records (
13 uri TEXT NOT NULL PRIMARY KEY,
14 profile_did TEXT NOT NULL,
15 FOREIGN KEY (profile_did) REFERENCES profiles(did) ON DELETE CASCADE,
16 cid TEXT NOT NULL,
17 indexed_at TIMESTAMPTZ NOT NULL DEFAULT now()
18);
19
20CREATE TABLE did_handles (
21 handle TEXT PRIMARY KEY,
22 did TEXT NOT NULL UNIQUE,
23 indexed_at TIMESTAMPTZ NOT NULL DEFAULT now()
24);
25
26CREATE TABLE channels (
27 uri TEXT PRIMARY KEY,
28 cid TEXT NOT NULL,
29 did TEXT NOT NULL,
30 host TEXT NOT NULL,
31 title TEXT NOT NULL,
32 topic TEXT,
33 created_at TIMESTAMPTZ NOT NULL,
34 indexed_at TIMESTAMPTZ NOT NULL DEFAULT now()
35);
36
37CREATE TABLE signets (
38 uri TEXT PRIMARY KEY,
39 issuer_did TEXT NOT NULL,
40 author_handle TEXT NOT NULL,
41 channel_uri TEXT NOT NULL,
42 FOREIGN KEY (channel_uri) REFERENCES channels(uri) ON DELETE CASCADE,
43 message_id INTEGER CHECK (message_id BETWEEN 0 AND 4294967295),
44 cid TEXT NOT NULL,
45 started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
46 indexed_at TIMESTAMPTZ NOT NULL DEFAULT now()
47);
48
49CREATE INDEX ON signets (channel_uri, message_id DESC);
50
51CREATE TABLE messages (
52 uri TEXT PRIMARY KEY,
53 did TEXT NOT NULL,
54 signet_uri TEXT NOT NULL,
55 FOREIGN KEY (signet_uri) REFERENCES signets(uri) ON DELETE CASCADE,
56 body TEXT,
57 nick TEXT NOT NULL DEFAULT 'wanderer',
58 color INTEGER CHECK (color BETWEEN 0 AND 16777215),
59 cid TEXT NOT NULL,
60 posted_at TIMESTAMPTZ NOT NULL DEFAULT now(),
61 indexed_at TIMESTAMPTZ NOT NULL DEFAULT now()
62);
63
64CREATE INDEX ON messages (signet_uri);
65
66CREATE TABLE oauthrequests (
67 id SERIAL PRIMARY KEY,
68 authserver_iss TEXT,
69 state TEXT,
70 did TEXT,
71 pds_url TEXT,
72 pkce_verifier TEXT,
73 dpop_auth_server_nonce TEXT,
74 dpop_private_jwk TEXT
75);
76
77CREATE TABLE oauthsessions (
78 id SERIAL PRIMARY KEY,
79 authserver_iss TEXT,
80 state TEXT,
81 did TEXT,
82 pds_url TEXT,
83 pkce_verifier TEXT,
84 dpop_auth_server_nonce TEXT,
85 dpop_private_jwk TEXT,
86 dpop_pds_nonce TEXT,
87 access_token TEXT,
88 refresh_token TEXT,
89 expiration TIMESTAMPTZ
90);