Something went wrong. Try again.
An ATProto Powered Reputation Network
Something went wrong. Try again.
3.4 kB · 67 lines
SQL
at master
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768-- Identity columns contain dictionary keys. Views restore full DIDs at the storage boundary.CREATE TABLE IF NOT EXISTS soft_accounts ( did INTEGER PRIMARY KEY REFERENCES identities(id), active INTEGER NOT NULL DEFAULT 1, account_us INTEGER NOT NULL DEFAULT 0, bootstrap_us INTEGER NOT NULL DEFAULT 0);
CREATE TABLE IF NOT EXISTS soft_vouches ( author INTEGER NOT NULL REFERENCES soft_accounts(did) ON DELETE CASCADE, subject INTEGER NOT NULL REFERENCES identities(id), source TEXT NOT NULL CHECK(source IN ('app.bsky.graph.follow', 'app.bsky.graph.block')), record_key TEXT NOT NULL, score REAL NOT NULL CHECK( (source = 'app.bsky.graph.follow' AND score = 0.5) OR (source = 'app.bsky.graph.block' AND score = -0.5) ), created_at TEXT NOT NULL, PRIMARY KEY(author, source, record_key)) WITHOUT ROWID;
-- One block overrides follows. Duplicate records never multiply a pair's score.CREATE VIEW IF NOT EXISTS soft_edges ASSELECT author.did AS author, subject.did AS subject, s.source, s.record_key, s.score, s.created_atFROM soft_vouches s JOIN soft_accounts a ON a.did = s.authorJOIN identities author ON author.id = s.authorJOIN identities subject ON subject.id = s.subjectWHERE a.active = 1AND NOT EXISTS (SELECT 1 FROM participants p WHERE p.did = subject.did AND p.active = 0)AND NOT EXISTS (SELECT 1 FROM soft_accounts p WHERE p.did = s.subject AND p.active = 0)AND NOT EXISTS ( SELECT 1 FROM soft_vouches preferred WHERE preferred.author = s.author AND preferred.subject = s.subject AND ((preferred.source = 'app.bsky.graph.block' AND s.source = 'app.bsky.graph.follow') OR (preferred.source = s.source AND preferred.record_key < s.record_key)));
CREATE TABLE IF NOT EXISTS soft_versions ( author INTEGER NOT NULL REFERENCES soft_accounts(did) ON DELETE CASCADE, source TEXT NOT NULL, record_key TEXT NOT NULL, time_us INTEGER NOT NULL, PRIMARY KEY(author, source, record_key)) WITHOUT ROWID;
CREATE TRIGGER IF NOT EXISTS trust_soft_delete BEFORE DELETE ON soft_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 = OLD.author AND source = OLD.source AND record_key = OLD.record_key );END;CREATE TRIGGER IF NOT EXISTS trust_soft_update BEFORE UPDATE ON soft_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 = OLD.author AND source = OLD.source AND record_key = OLD.record_key );END;CREATE TRIGGER IF NOT EXISTS graph_soft_insert AFTER INSERT ON soft_vouches BEGIN UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;END;CREATE TRIGGER IF NOT EXISTS graph_soft_delete AFTER DELETE ON soft_vouches BEGIN UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;END;CREATE TRIGGER IF NOT EXISTS graph_soft_update AFTER UPDATE ON soft_vouches BEGIN UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;END;CREATE TRIGGER IF NOT EXISTS graph_soft_account AFTER UPDATE OF active ON soft_accounts WHEN OLD.active != NEW.active BEGIN UPDATE graph_revision SET revision = revision + 1 WHERE singleton = 1;END;