-- AT Protocol Indexer Database Schema
-- Single table approach for maximum flexibility across arbitrary lexicons

CREATE TABLE IF NOT EXISTS "record" (
    "uri" TEXT PRIMARY KEY NOT NULL,
    "cid" TEXT NOT NULL,
    "did" TEXT NOT NULL,
    "collection" TEXT NOT NULL,
    "json" JSONB NOT NULL,
    "indexedAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- Essential indexes for performance
CREATE INDEX IF NOT EXISTS idx_record_collection ON "record"("collection");
CREATE INDEX IF NOT EXISTS idx_record_did ON "record"("did");
CREATE INDEX IF NOT EXISTS idx_record_indexed_at ON "record"("indexedAt");
CREATE INDEX IF NOT EXISTS idx_record_json_gin ON "record" USING GIN("json");

-- Collection-specific indexes for common queries
CREATE INDEX IF NOT EXISTS idx_record_collection_did ON "record"("collection", "did");
CREATE INDEX IF NOT EXISTS idx_record_cid ON "record"("cid");