relay filter/appview bootstrap
gmstn.systems
1CREATE TABLE IF NOT EXISTS account (
2 did TEXT PRIMARY KEY,
3 handle TEXT NOT NULL,
4 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
5);
6
7CREATE TABLE IF NOT EXISTS lattice (
8 uri TEXT PRIMARY KEY,
9 cid TEXT UNIQUE NOT NULL,
10 creator_did TEXT NOT NULL REFERENCES account(did),
11 description TEXT,
12 created_at TIMESTAMPTZ NOT NULL,
13 indexed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
14 data JSONB NOT NULL
15);
16
17CREATE INDEX IF NOT EXISTS idx_lattice_creator_indexed ON lattice(creator_did, indexed_at DESC);
18
19CREATE TABLE IF NOT EXISTS shard (
20 uri TEXT PRIMARY KEY,
21 cid TEXT UNIQUE NOT NULL,
22 creator_did TEXT NOT NULL REFERENCES account(did),
23 description TEXT,
24 created_at TIMESTAMPTZ NOT NULL,
25 indexed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
26 data JSONB NOT NULL
27);
28
29CREATE INDEX IF NOT EXISTS idx_shard_creator_indexed ON shard(creator_did, indexed_at DESC);
30
31CREATE TABLE IF NOT EXISTS channel (
32 cid TEXT PRIMARY KEY,
33 uri TEXT UNIQUE,
34 creator_did TEXT REFERENCES account(did),
35 name TEXT,
36 topic TEXT,
37 created_at TIMESTAMPTZ,
38 indexed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
39 data JSONB
40);
41
42CREATE INDEX IF NOT EXISTS idx_channel_creator_indexed ON channel(creator_did, indexed_at DESC);
43
44CREATE TABLE IF NOT EXISTS channel_invite (
45 cid TEXT PRIMARY KEY,
46 uri TEXT UNIQUE,
47 creator_did TEXT REFERENCES account(did),
48 channel TEXT NOT NULL REFERENCES channel(cid),
49 recipient_did TEXT REFERENCES account(did),
50 created_at TIMESTAMPTZ,
51 indexed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
52 data JSONB
53);
54
55CREATE INDEX IF NOT EXISTS idx_invite_recipient_indexed ON channel_invite(recipient_did, indexed_at DESC);
56
57CREATE TABLE IF NOT EXISTS channel_membership (
58 cid TEXT PRIMARY KEY,
59 uri TEXT UNIQUE NOT NULL,
60 channel TEXT NOT NULL REFERENCES channel(cid),
61 invite TEXT NOT NULL REFERENCES channel_invite(cid),
62 recipient_did TEXT REFERENCES account(did),
63 state TEXT NOT NULL,
64 created_at TIMESTAMPTZ NOT NULL,
65 indexed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
66 updated_at TIMESTAMPTZ,
67 data JSONB NOT NULL
68);
69
70CREATE INDEX IF NOT EXISTS idx_membership_recipient_indexed ON channel_membership(recipient_did, indexed_at DESC);