1  -- ACDC Forge Cache Schema
 2  -- This is a READ-ONLY cache of on-chain state
 3  -- NOT authoritative - rebuilt from chain on sync
 4  
 5  -- Governors (Tech and Code)
 6  CREATE TABLE IF NOT EXISTS cached_governors (
 7      address TEXT PRIMARY KEY,
 8      chain TEXT NOT NULL,              -- 'alpha' or 'delta'
 9      display_name TEXT,                -- ENS or paycode
10      nominating_governor TEXT,         -- For Alpha tech govs
11      stake_amount INTEGER,             -- For Delta code govs (microcredits)
12      missed_votes INTEGER DEFAULT 0,
13      last_vote_at INTEGER,
14      status TEXT DEFAULT 'active',     -- 'active', 'inactive', 'deregistered'
15      last_synced_block INTEGER
16  );
17  
18  -- Pull Requests
19  CREATE TABLE IF NOT EXISTS cached_prs (
20      pr_hash TEXT PRIMARY KEY,
21      repo_id TEXT NOT NULL,
22      submitter TEXT NOT NULL,          -- Coder who submitted
23      sponsor TEXT,                     -- Governor who sponsored
24      sponsored_at INTEGER,
25      vote_deadline INTEGER,
26      yes_votes INTEGER DEFAULT 0,
27      no_votes INTEGER DEFAULT 0,
28      status TEXT DEFAULT 'draft',      -- 'draft', 'voting', 'passed', 'failed'
29      behavior_flags INTEGER DEFAULT 0,
30      chain TEXT NOT NULL,              -- 'alpha' or 'delta'
31      last_synced_block INTEGER
32  );
33  
34  -- Individual Votes
35  CREATE TABLE IF NOT EXISTS cached_votes (
36      id INTEGER PRIMARY KEY AUTOINCREMENT,
37      pr_hash TEXT NOT NULL,
38      voter TEXT NOT NULL,
39      vote_type TEXT NOT NULL,          -- 'yes', 'no', 'abstain'
40      flags TEXT,                       -- JSON: ['security', 'political', 'economic']
41      cast_at INTEGER,
42      chain TEXT NOT NULL,
43      last_synced_block INTEGER,
44      UNIQUE(pr_hash, voter, chain)
45  );
46  
47  -- Code Checkpoints
48  CREATE TABLE IF NOT EXISTS cached_checkpoints (
49      commit_hash TEXT PRIMARY KEY,
50      radicle_oid TEXT,
51      approver_count INTEGER DEFAULT 0,
52      finalized_at INTEGER,
53      chain TEXT NOT NULL,
54      last_synced_block INTEGER
55  );
56  
57  -- Contributor Reputation
58  CREATE TABLE IF NOT EXISTS cached_contributors (
59      address TEXT PRIMARY KEY,
60      prs_submitted INTEGER DEFAULT 0,
61      prs_merged INTEGER DEFAULT 0,
62      prs_rejected INTEGER DEFAULT 0,
63      behavior_flags INTEGER DEFAULT 0,
64      reward_points INTEGER DEFAULT 0,
65      chain TEXT NOT NULL,
66      last_synced_block INTEGER
67  );
68  
69  -- Sync State (track indexer progress)
70  CREATE TABLE IF NOT EXISTS sync_state (
71      chain TEXT PRIMARY KEY,
72      last_block INTEGER DEFAULT 0,
73      last_sync_at INTEGER,
74      status TEXT DEFAULT 'idle'        -- 'idle', 'syncing', 'error'
75  );
76  
77  -- Indexes for common queries
78  CREATE INDEX IF NOT EXISTS idx_prs_status ON cached_prs(status);
79  CREATE INDEX IF NOT EXISTS idx_prs_repo ON cached_prs(repo_id);
80  CREATE INDEX IF NOT EXISTS idx_votes_pr ON cached_votes(pr_hash);
81  CREATE INDEX IF NOT EXISTS idx_governors_chain ON cached_governors(chain);
82  CREATE INDEX IF NOT EXISTS idx_contributors_chain ON cached_contributors(chain);