Highly ambitious ATProtocol AppView service and sdks
slices.network
1-- AT Protocol Indexer Database Schema
2-- Single table approach for maximum flexibility across arbitrary lexicons
3
4CREATE TABLE IF NOT EXISTS "record" (
5 "uri" TEXT PRIMARY KEY NOT NULL,
6 "cid" TEXT NOT NULL,
7 "did" TEXT NOT NULL,
8 "collection" TEXT NOT NULL,
9 "json" JSONB NOT NULL,
10 "indexedAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
11);
12
13-- Essential indexes for performance
14CREATE INDEX IF NOT EXISTS idx_record_collection ON "record"("collection");
15CREATE INDEX IF NOT EXISTS idx_record_did ON "record"("did");
16CREATE INDEX IF NOT EXISTS idx_record_indexed_at ON "record"("indexedAt");
17CREATE INDEX IF NOT EXISTS idx_record_json_gin ON "record" USING GIN("json");
18
19-- Collection-specific indexes for common queries
20CREATE INDEX IF NOT EXISTS idx_record_collection_did ON "record"("collection", "did");
21CREATE INDEX IF NOT EXISTS idx_record_cid ON "record"("cid");