relay filter/appview bootstrap
gmstn.systems
1CREATE TABLE record (
2 uri TEXT PRIMARY KEY,
3 cid TEXT UNIQUE NOT NULL,
4 collection TEXT NOT NULL,
5 creator_did TEXT NOT NULL,
6 created_at TIMESTAMPTZ NOT NULL,
7 indexed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
8 data JSONB NOT NULL,
9 target_did TEXT,
10 ref_cids TEXT[] NOT NULL DEFAULT '{}'
11);
12
13INSERT INTO record (uri, cid, collection, creator_did, created_at, indexed_at, data, target_did, ref_cids)
14SELECT uri, cid, 'systems.gmstn.development.lattice', creator_did, created_at, indexed_at, data, NULL, '{}'
15FROM lattice;
16
17INSERT INTO record (uri, cid, collection, creator_did, created_at, indexed_at, data, target_did, ref_cids)
18SELECT uri, cid, 'systems.gmstn.development.shard', creator_did, created_at, indexed_at, data, NULL, '{}'
19FROM shard;
20
21INSERT INTO record (uri, cid, collection, creator_did, created_at, indexed_at, data, target_did, ref_cids)
22SELECT uri, cid, 'systems.gmstn.development.channel', creator_did, created_at, indexed_at, COALESCE(data, '{}'), NULL, '{}'
23FROM channel WHERE uri IS NOT NULL;
24
25INSERT INTO record (uri, cid, collection, creator_did, created_at, indexed_at, data, target_did, ref_cids)
26SELECT uri, cid, 'systems.gmstn.development.channel.invite', creator_did, created_at, indexed_at, COALESCE(data, '{}'), recipient_did, ARRAY[channel]
27FROM channel_invite WHERE uri IS NOT NULL;
28
29INSERT INTO record (uri, cid, collection, creator_did, created_at, indexed_at, data, target_did, ref_cids)
30SELECT uri, cid, 'systems.gmstn.development.channel.membership', recipient_did, created_at, indexed_at, data, recipient_did, ARRAY[channel, invite]
31FROM channel_membership;
32
33CREATE INDEX idx_record_collection_creator ON record(collection, creator_did, indexed_at DESC);
34CREATE INDEX idx_record_collection_target ON record(collection, target_did, indexed_at DESC) WHERE target_did IS NOT NULL;
35CREATE INDEX idx_record_cid ON record(cid);
36CREATE INDEX idx_record_ref_cids ON record USING GIN(ref_cids);
37
38DROP TABLE channel_membership;
39DROP TABLE channel_invite;
40DROP TABLE channel;
41DROP TABLE shard;
42DROP TABLE lattice;