Something went wrong. Try again.
An ATProto Powered Reputation Network
Something went wrong. Try again.
5.1 kB · 126 lines
SQL
at master
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127-- Keep full DIDs at the boundary. High-volume graph tables store dictionary keys.CREATE TABLE IF NOT EXISTS identities ( id INTEGER PRIMARY KEY, did TEXT NOT NULL UNIQUE);
CREATE TABLE IF NOT EXISTS participants ( did TEXT PRIMARY KEY, threshold REAL NOT NULL DEFAULT 1 CHECK(threshold BETWEEN -2 AND 2), synced_at TEXT NOT NULL DEFAULT '', bootstrap_us INTEGER NOT NULL DEFAULT 0, active INTEGER NOT NULL DEFAULT 1, account_us INTEGER NOT NULL DEFAULT 0);
CREATE TABLE IF NOT EXISTS vouches ( author TEXT NOT NULL REFERENCES participants(did) ON DELETE CASCADE, subject TEXT NOT NULL, tag TEXT NOT NULL CHECK(length(tag) <= 128), score INTEGER NOT NULL CHECK(score BETWEEN -3 AND 2), created_at TEXT NOT NULL, record_key TEXT NOT NULL, comment TEXT NOT NULL DEFAULT '', source TEXT NOT NULL DEFAULT 'computer.ngp.provenance.vouch', PRIMARY KEY(author, subject, tag));
CREATE INDEX IF NOT EXISTS vouches_tag ON vouches(tag);
CREATE UNIQUE INDEX IF NOT EXISTS vouches_key ON vouches(author, record_key);CREATE INDEX IF NOT EXISTS vouches_subject_tag ON vouches(subject, tag, author);
CREATE TABLE IF NOT EXISTS graph_revision ( singleton INTEGER PRIMARY KEY CHECK(singleton = 1), revision INTEGER NOT NULL DEFAULT 0);INSERT OR IGNORE INTO graph_revision(singleton) VALUES (1);
-- Replay defers path invalidation until the transaction contains the complete batch.CREATE TABLE IF NOT EXISTS cache_policy ( singleton INTEGER PRIMARY KEY CHECK(singleton = 1), deferred INTEGER NOT NULL DEFAULT 0 CHECK(deferred IN (0, 1)));INSERT OR IGNORE INTO cache_policy(singleton) VALUES (1);
CREATE TABLE IF NOT EXISTS trust_cache ( cache_key TEXT PRIMARY KEY, status TEXT NOT NULL DEFAULT '', score REAL, expires_us INTEGER NOT NULL);CREATE INDEX IF NOT EXISTS trust_cache_expiry ON trust_cache(expires_us);
CREATE TABLE IF NOT EXISTS trust_cache_paths ( cache_key TEXT NOT NULL REFERENCES trust_cache(cache_key) ON DELETE CASCADE, position INTEGER NOT NULL CHECK(position >= 0), did TEXT NOT NULL, PRIMARY KEY(cache_key, position)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS trust_cache_scores ( cache_key TEXT NOT NULL REFERENCES trust_cache(cache_key) ON DELETE CASCADE, position INTEGER NOT NULL CHECK(position >= 0), did TEXT NOT NULL, assertion REAL, source TEXT NOT NULL, list_uri TEXT NOT NULL, score REAL NOT NULL, comment TEXT NOT NULL, PRIMARY KEY(cache_key, position)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS trust_dependencies ( cache_key TEXT NOT NULL REFERENCES trust_cache(cache_key) ON DELETE CASCADE, author INTEGER NOT NULL REFERENCES identities(id), record_key TEXT NOT NULL, source TEXT NOT NULL DEFAULT 'computer.ngp.provenance.vouch', PRIMARY KEY(cache_key, author, source, record_key));CREATE INDEX IF NOT EXISTS trust_dependencies_vouch ON trust_dependencies(author, source, record_key, cache_key);
-- Record mutations invalidate only the paths that use that exact record.CREATE TRIGGER IF NOT EXISTS trust_vouch_delete BEFORE DELETE ON vouches WHEN (SELECT deferred FROM cache_policy WHERE singleton = 1) = 0 BEGIN DELETE FROM trust_cache WHERE cache_key IN ( SELECT cache_key FROM trust_dependencies WHERE author = (SELECT id FROM identities WHERE did = OLD.author) AND source = OLD.source AND record_key = OLD.record_key );END;CREATE TRIGGER IF NOT EXISTS trust_vouch_update BEFORE UPDATE ON vouches WHEN (SELECT deferred FROM cache_policy WHERE singleton = 1) = 0 BEGIN DELETE FROM trust_cache WHERE cache_key IN ( SELECT cache_key FROM trust_dependencies WHERE author = (SELECT id FROM identities WHERE did = OLD.author) AND source = OLD.source AND record_key = OLD.record_key );END;
-- Revisions prevent calculations from publishing cache entries after concurrent graph mutations.CREATE TRIGGER IF NOT EXISTS graph_vouch_insert AFTER INSERT ON vouches BEGIN UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;END;CREATE TRIGGER IF NOT EXISTS graph_vouch_delete AFTER DELETE ON vouches BEGIN UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;END;CREATE TRIGGER IF NOT EXISTS graph_vouch_update AFTER UPDATE ON vouches BEGIN UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;END;CREATE TRIGGER IF NOT EXISTS graph_account_update AFTER UPDATE OF active ON participants WHEN OLD.active != NEW.active BEGIN UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;END;
CREATE TABLE IF NOT EXISTS record_versions ( author TEXT NOT NULL REFERENCES participants(did) ON DELETE CASCADE, record_key TEXT NOT NULL, time_us INTEGER NOT NULL, PRIMARY KEY(author, record_key));
CREATE TABLE IF NOT EXISTS stream_cursors ( source TEXT PRIMARY KEY, time_us INTEGER NOT NULL);
CREATE TABLE IF NOT EXISTS secrets ( kind TEXT NOT NULL, id TEXT NOT NULL, value BLOB NOT NULL, expires INTEGER NOT NULL, PRIMARY KEY(kind, id));