atproto user agency toolkit for individuals and groups
1/**
2 * SQLite persistence for challenge history and peer reliability.
3 * Pattern follows SyncStorage.
4 */
5
6import type Database from "better-sqlite3";
7import type { StorageChallengeResult } from "./types.js";
8
9export interface ChallengeHistoryRow {
10 challenge_id: string;
11 challenger_did: string;
12 target_did: string;
13 subject_did: string;
14 challenge_type: string;
15 /** SQLite stores booleans as 0/1 integers. */
16 passed: number;
17 verified_at: string;
18 duration_ms: number;
19}
20
21export interface PeerReliabilityRow {
22 peer_did: string;
23 subject_did: string;
24 total_challenges: number;
25 successful_challenges: number;
26 reliability: number;
27 last_challenge_at: string;
28}
29
30export class ChallengeStorage {
31 constructor(private db: Database.Database) {}
32
33 /**
34 * Create challenge tables if they don't exist.
35 */
36 initSchema(): void {
37 this.db.exec(`
38 CREATE TABLE IF NOT EXISTS challenge_history (
39 challenge_id TEXT PRIMARY KEY,
40 challenger_did TEXT NOT NULL,
41 target_did TEXT NOT NULL,
42 subject_did TEXT NOT NULL,
43 challenge_type TEXT NOT NULL,
44 passed INTEGER NOT NULL,
45 verified_at TEXT NOT NULL,
46 duration_ms INTEGER NOT NULL
47 );
48
49 CREATE INDEX IF NOT EXISTS idx_challenge_history_target
50 ON challenge_history (target_did, subject_did);
51
52 CREATE TABLE IF NOT EXISTS peer_reliability (
53 peer_did TEXT NOT NULL,
54 subject_did TEXT NOT NULL,
55 total_challenges INTEGER NOT NULL DEFAULT 0,
56 successful_challenges INTEGER NOT NULL DEFAULT 0,
57 reliability REAL NOT NULL DEFAULT 0.0,
58 last_challenge_at TEXT NOT NULL,
59 PRIMARY KEY (peer_did, subject_did)
60 );
61 `);
62 }
63
64 /**
65 * Record a challenge result and update peer reliability.
66 */
67 recordResult(
68 challengerDid: string,
69 targetDid: string,
70 subjectDid: string,
71 challengeType: string,
72 result: StorageChallengeResult,
73 ): void {
74 const passedInt = result.passed ? 1 : 0;
75
76 this.db
77 .prepare(
78 `INSERT OR REPLACE INTO challenge_history
79 (challenge_id, challenger_did, target_did, subject_did, challenge_type, passed, verified_at, duration_ms)
80 VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
81 )
82 .run(
83 result.challengeId,
84 challengerDid,
85 targetDid,
86 subjectDid,
87 challengeType,
88 passedInt,
89 result.verifiedAt,
90 result.durationMs,
91 );
92
93 this.db
94 .prepare(
95 `INSERT INTO peer_reliability
96 (peer_did, subject_did, total_challenges, successful_challenges, reliability, last_challenge_at)
97 VALUES (?, ?, 1, ?, ?, ?)
98 ON CONFLICT(peer_did, subject_did) DO UPDATE SET
99 total_challenges = peer_reliability.total_challenges + 1,
100 successful_challenges = peer_reliability.successful_challenges + ?,
101 reliability = CAST((peer_reliability.successful_challenges + ?) AS REAL)
102 / (peer_reliability.total_challenges + 1),
103 last_challenge_at = ?`,
104 )
105 .run(
106 targetDid,
107 subjectDid,
108 passedInt,
109 result.passed ? 1.0 : 0.0,
110 result.verifiedAt,
111 passedInt,
112 passedInt,
113 result.verifiedAt,
114 );
115 }
116
117 /**
118 * Get challenge history for a target peer, optionally filtered by subject DID.
119 */
120 getHistory(
121 targetDid: string,
122 subjectDid?: string,
123 limit = 50,
124 ): ChallengeHistoryRow[] {
125 if (subjectDid) {
126 return this.db
127 .prepare(
128 `SELECT * FROM challenge_history
129 WHERE target_did = ? AND subject_did = ?
130 ORDER BY verified_at DESC LIMIT ?`,
131 )
132 .all(targetDid, subjectDid, limit) as ChallengeHistoryRow[];
133 }
134 return this.db
135 .prepare(
136 `SELECT * FROM challenge_history
137 WHERE target_did = ?
138 ORDER BY verified_at DESC LIMIT ?`,
139 )
140 .all(targetDid, limit) as ChallengeHistoryRow[];
141 }
142
143 /**
144 * Get reliability scores for a peer, optionally filtered by subject DID.
145 */
146 getReliability(
147 peerDid: string,
148 subjectDid?: string,
149 ): PeerReliabilityRow[] {
150 if (subjectDid) {
151 const row = this.db
152 .prepare(
153 `SELECT * FROM peer_reliability
154 WHERE peer_did = ? AND subject_did = ?`,
155 )
156 .get(peerDid, subjectDid) as PeerReliabilityRow | undefined;
157 return row ? [row] : [];
158 }
159 return this.db
160 .prepare(`SELECT * FROM peer_reliability WHERE peer_did = ?`)
161 .all(peerDid) as PeerReliabilityRow[];
162 }
163
164 /**
165 * Delete all challenge history and peer reliability data.
166 * Used during full disconnect to wipe the node clean.
167 */
168 purgeAll(): void {
169 this.db.prepare("DELETE FROM challenge_history").run();
170 this.db.prepare("DELETE FROM peer_reliability").run();
171 }
172
173 /**
174 * Get all peer reliability scores, sorted by reliability descending.
175 */
176 getAllReliability(): PeerReliabilityRow[] {
177 return this.db
178 .prepare(
179 `SELECT * FROM peer_reliability ORDER BY reliability DESC`,
180 )
181 .all() as PeerReliabilityRow[];
182 }
183}