perspt_store/
schema.rs

1//! DuckDB Schema Initialization
2//!
3//! Creates the required tables for SRBN session persistence.
4
5use anyhow::Result;
6use duckdb::Connection;
7
8/// Add a column to a table, ignoring errors if it already exists (backward-compatible migration).
9fn add_column_if_not_exists(conn: &Connection, table: &str, column: &str, col_type: &str) {
10    let sql = format!("ALTER TABLE {} ADD COLUMN {} {}", table, column, col_type);
11    let _ = conn.execute(&sql, []);
12}
13
14/// Initialize the DuckDB schema for SRBN persistence
15pub fn init_schema(conn: &Connection) -> Result<()> {
16    // Sessions table - top-level session tracking
17    conn.execute(
18        r#"
19        CREATE TABLE IF NOT EXISTS sessions (
20            session_id VARCHAR PRIMARY KEY,
21            task TEXT NOT NULL,
22            working_dir TEXT NOT NULL,
23            merkle_root BLOB,
24            detected_toolchain VARCHAR,
25            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
26            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
27            status VARCHAR DEFAULT 'active'
28        )
29        "#,
30        [],
31    )?;
32
33    // Create sequences for auto-incrementing IDs (DuckDB doesn't auto-increment INTEGER PRIMARY KEY)
34    conn.execute(
35        "CREATE SEQUENCE IF NOT EXISTS seq_node_states_id START 1",
36        [],
37    )?;
38    conn.execute(
39        "CREATE SEQUENCE IF NOT EXISTS seq_energy_history_id START 1",
40        [],
41    )?;
42    conn.execute(
43        "CREATE SEQUENCE IF NOT EXISTS seq_llm_requests_id START 1",
44        [],
45    )?;
46
47    // Node states table - per-node state tracking
48    conn.execute(
49        r#"
50        CREATE TABLE IF NOT EXISTS node_states (
51            id INTEGER PRIMARY KEY DEFAULT nextval('seq_node_states_id'),
52            node_id VARCHAR NOT NULL,
53            session_id VARCHAR NOT NULL,
54            state VARCHAR NOT NULL,
55            v_total REAL DEFAULT 0.0,
56            merkle_hash BLOB,
57            attempt_count INTEGER DEFAULT 0,
58            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
59            FOREIGN KEY (session_id) REFERENCES sessions(session_id)
60        )
61        "#,
62        [],
63    )?;
64
65    // Energy history table - tracks V(x) over time for convergence analysis
66    conn.execute(
67        r#"
68        CREATE TABLE IF NOT EXISTS energy_history (
69            id INTEGER PRIMARY KEY DEFAULT nextval('seq_energy_history_id'),
70            node_id VARCHAR NOT NULL,
71            session_id VARCHAR NOT NULL,
72            timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
73            v_syn REAL DEFAULT 0.0,
74            v_str REAL DEFAULT 0.0,
75            v_log REAL DEFAULT 0.0,
76            v_boot REAL DEFAULT 0.0,
77            v_sheaf REAL DEFAULT 0.0,
78            v_total REAL DEFAULT 0.0,
79            FOREIGN KEY (session_id) REFERENCES sessions(session_id)
80        )
81        "#,
82        [],
83    )?;
84
85    // Index for fast session lookup
86    conn.execute(
87        "CREATE INDEX IF NOT EXISTS idx_node_states_session ON node_states(session_id)",
88        [],
89    )?;
90
91    conn.execute(
92        "CREATE INDEX IF NOT EXISTS idx_energy_history_session ON energy_history(session_id)",
93        [],
94    )?;
95
96    // LLM requests table - stores all LLM interactions for debugging and analysis
97    conn.execute(
98        r#"
99        CREATE TABLE IF NOT EXISTS llm_requests (
100            id INTEGER PRIMARY KEY DEFAULT nextval('seq_llm_requests_id'),
101            session_id VARCHAR NOT NULL,
102            node_id VARCHAR,
103            model VARCHAR NOT NULL,
104            prompt TEXT NOT NULL,
105            response TEXT NOT NULL,
106            tokens_in INTEGER DEFAULT 0,
107            tokens_out INTEGER DEFAULT 0,
108            latency_ms INTEGER DEFAULT 0,
109            timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
110            FOREIGN KEY (session_id) REFERENCES sessions(session_id)
111        )
112        "#,
113        [],
114    )?;
115
116    conn.execute(
117        "CREATE INDEX IF NOT EXISTS idx_llm_requests_session ON llm_requests(session_id)",
118        [],
119    )?;
120
121    // PSP-5 Phase 3: Sequences for context provenance tables
122    conn.execute(
123        "CREATE SEQUENCE IF NOT EXISTS seq_structural_digests_id START 1",
124        [],
125    )?;
126    conn.execute(
127        "CREATE SEQUENCE IF NOT EXISTS seq_context_provenance_id START 1",
128        [],
129    )?;
130
131    // PSP-5 Phase 3: Structural digests - hashes of compile-critical artifacts
132    conn.execute(
133        r#"
134        CREATE TABLE IF NOT EXISTS structural_digests (
135            id INTEGER PRIMARY KEY DEFAULT nextval('seq_structural_digests_id'),
136            digest_id VARCHAR NOT NULL,
137            session_id VARCHAR NOT NULL,
138            node_id VARCHAR NOT NULL,
139            source_path VARCHAR NOT NULL,
140            artifact_kind VARCHAR NOT NULL,
141            hash BLOB NOT NULL,
142            version INTEGER DEFAULT 1,
143            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
144            FOREIGN KEY (session_id) REFERENCES sessions(session_id)
145        )
146        "#,
147        [],
148    )?;
149
150    conn.execute(
151        "CREATE INDEX IF NOT EXISTS idx_structural_digests_session ON structural_digests(session_id)",
152        [],
153    )?;
154
155    // PSP-5 Phase 3: Context provenance - audit trail of what context was used per node
156    conn.execute(
157        r#"
158        CREATE TABLE IF NOT EXISTS context_provenance (
159            id INTEGER PRIMARY KEY DEFAULT nextval('seq_context_provenance_id'),
160            session_id VARCHAR NOT NULL,
161            node_id VARCHAR NOT NULL,
162            context_package_id VARCHAR NOT NULL,
163            structural_hashes TEXT,
164            summary_hashes TEXT,
165            dependency_hashes TEXT,
166            included_file_count INTEGER DEFAULT 0,
167            total_bytes INTEGER DEFAULT 0,
168            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
169            FOREIGN KEY (session_id) REFERENCES sessions(session_id)
170        )
171        "#,
172        [],
173    )?;
174
175    conn.execute(
176        "CREATE INDEX IF NOT EXISTS idx_context_provenance_session ON context_provenance(session_id)",
177        [],
178    )?;
179
180    // =========================================================================
181    // PSP-5 Phase 5: Escalation evidence and rewrite lineage
182    // =========================================================================
183
184    conn.execute(
185        "CREATE SEQUENCE IF NOT EXISTS seq_escalation_reports_id START 1",
186        [],
187    )?;
188    conn.execute(
189        "CREATE SEQUENCE IF NOT EXISTS seq_rewrite_records_id START 1",
190        [],
191    )?;
192    conn.execute(
193        "CREATE SEQUENCE IF NOT EXISTS seq_sheaf_validations_id START 1",
194        [],
195    )?;
196
197    // Escalation reports — one row per classified non-convergence event
198    conn.execute(
199        r#"
200        CREATE TABLE IF NOT EXISTS escalation_reports (
201            id INTEGER PRIMARY KEY DEFAULT nextval('seq_escalation_reports_id'),
202            session_id VARCHAR NOT NULL,
203            node_id VARCHAR NOT NULL,
204            category VARCHAR NOT NULL,
205            action VARCHAR NOT NULL,
206            energy_snapshot TEXT,
207            stage_outcomes TEXT,
208            evidence TEXT,
209            affected_node_ids TEXT,
210            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
211            FOREIGN KEY (session_id) REFERENCES sessions(session_id)
212        )
213        "#,
214        [],
215    )?;
216    conn.execute(
217        "CREATE INDEX IF NOT EXISTS idx_escalation_reports_session ON escalation_reports(session_id)",
218        [],
219    )?;
220
221    // Rewrite records — one row per local graph rewrite applied
222    conn.execute(
223        r#"
224        CREATE TABLE IF NOT EXISTS rewrite_records (
225            id INTEGER PRIMARY KEY DEFAULT nextval('seq_rewrite_records_id'),
226            session_id VARCHAR NOT NULL,
227            node_id VARCHAR NOT NULL,
228            action VARCHAR NOT NULL,
229            category VARCHAR NOT NULL,
230            requeued_nodes TEXT,
231            inserted_nodes TEXT,
232            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
233            FOREIGN KEY (session_id) REFERENCES sessions(session_id)
234        )
235        "#,
236        [],
237    )?;
238    conn.execute(
239        "CREATE INDEX IF NOT EXISTS idx_rewrite_records_session ON rewrite_records(session_id)",
240        [],
241    )?;
242
243    // Sheaf validation results — one row per validator pass per node
244    conn.execute(
245        r#"
246        CREATE TABLE IF NOT EXISTS sheaf_validations (
247            id INTEGER PRIMARY KEY DEFAULT nextval('seq_sheaf_validations_id'),
248            session_id VARCHAR NOT NULL,
249            node_id VARCHAR NOT NULL,
250            validator_class VARCHAR NOT NULL,
251            plugin_source VARCHAR,
252            passed BOOLEAN NOT NULL,
253            evidence_summary TEXT,
254            affected_files TEXT,
255            v_sheaf_contribution REAL DEFAULT 0.0,
256            requeue_targets TEXT,
257            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
258            FOREIGN KEY (session_id) REFERENCES sessions(session_id)
259        )
260        "#,
261        [],
262    )?;
263    conn.execute(
264        "CREATE INDEX IF NOT EXISTS idx_sheaf_validations_session ON sheaf_validations(session_id)",
265        [],
266    )?;
267
268    // =========================================================================
269    // PSP-5 Phase 6: Provisional branch ledger and interface-sealed speculation
270    // =========================================================================
271
272    conn.execute(
273        "CREATE SEQUENCE IF NOT EXISTS seq_provisional_branches_id START 1",
274        [],
275    )?;
276    conn.execute(
277        "CREATE SEQUENCE IF NOT EXISTS seq_branch_lineage_id START 1",
278        [],
279    )?;
280    conn.execute(
281        "CREATE SEQUENCE IF NOT EXISTS seq_interface_seals_id START 1",
282        [],
283    )?;
284    conn.execute(
285        "CREATE SEQUENCE IF NOT EXISTS seq_branch_flushes_id START 1",
286        [],
287    )?;
288
289    // Provisional branches — speculative child work stored separately from committed state
290    conn.execute(
291        r#"
292        CREATE TABLE IF NOT EXISTS provisional_branches (
293            id INTEGER PRIMARY KEY DEFAULT nextval('seq_provisional_branches_id'),
294            branch_id VARCHAR NOT NULL,
295            session_id VARCHAR NOT NULL,
296            node_id VARCHAR NOT NULL,
297            parent_node_id VARCHAR NOT NULL,
298            state VARCHAR NOT NULL DEFAULT 'active',
299            parent_seal_hash BLOB,
300            sandbox_dir VARCHAR,
301            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
302            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
303            FOREIGN KEY (session_id) REFERENCES sessions(session_id)
304        )
305        "#,
306        [],
307    )?;
308    conn.execute(
309        "CREATE INDEX IF NOT EXISTS idx_provisional_branches_session ON provisional_branches(session_id)",
310        [],
311    )?;
312    conn.execute(
313        "CREATE INDEX IF NOT EXISTS idx_provisional_branches_parent ON provisional_branches(parent_node_id)",
314        [],
315    )?;
316
317    // Branch lineage — parent→child dependency edges for flush propagation
318    conn.execute(
319        r#"
320        CREATE TABLE IF NOT EXISTS branch_lineage (
321            id INTEGER PRIMARY KEY DEFAULT nextval('seq_branch_lineage_id'),
322            lineage_id VARCHAR NOT NULL,
323            parent_branch_id VARCHAR NOT NULL,
324            child_branch_id VARCHAR NOT NULL,
325            depends_on_seal BOOLEAN NOT NULL DEFAULT true,
326            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
327        )
328        "#,
329        [],
330    )?;
331    conn.execute(
332        "CREATE INDEX IF NOT EXISTS idx_branch_lineage_parent ON branch_lineage(parent_branch_id)",
333        [],
334    )?;
335
336    // Interface seals — immutable seal records for dependency gating
337    conn.execute(
338        r#"
339        CREATE TABLE IF NOT EXISTS interface_seals (
340            id INTEGER PRIMARY KEY DEFAULT nextval('seq_interface_seals_id'),
341            seal_id VARCHAR NOT NULL,
342            session_id VARCHAR NOT NULL,
343            node_id VARCHAR NOT NULL,
344            sealed_path VARCHAR NOT NULL,
345            artifact_kind VARCHAR NOT NULL,
346            seal_hash BLOB NOT NULL,
347            version INTEGER DEFAULT 1,
348            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
349            FOREIGN KEY (session_id) REFERENCES sessions(session_id)
350        )
351        "#,
352        [],
353    )?;
354    conn.execute(
355        "CREATE INDEX IF NOT EXISTS idx_interface_seals_session ON interface_seals(session_id)",
356        [],
357    )?;
358    conn.execute(
359        "CREATE INDEX IF NOT EXISTS idx_interface_seals_node ON interface_seals(node_id)",
360        [],
361    )?;
362
363    // Branch flushes — records of flush decisions for audit and resume
364    conn.execute(
365        r#"
366        CREATE TABLE IF NOT EXISTS branch_flushes (
367            id INTEGER PRIMARY KEY DEFAULT nextval('seq_branch_flushes_id'),
368            flush_id VARCHAR NOT NULL,
369            session_id VARCHAR NOT NULL,
370            parent_node_id VARCHAR NOT NULL,
371            flushed_branch_ids TEXT NOT NULL,
372            requeue_node_ids TEXT NOT NULL,
373            reason TEXT NOT NULL,
374            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
375            FOREIGN KEY (session_id) REFERENCES sessions(session_id)
376        )
377        "#,
378        [],
379    )?;
380    conn.execute(
381        "CREATE INDEX IF NOT EXISTS idx_branch_flushes_session ON branch_flushes(session_id)",
382        [],
383    )?;
384
385    // =========================================================================
386    // PSP-5 Phase 8: Ledger-backed node commits and resume correctness
387    // =========================================================================
388
389    // Extend node_states with Phase 8 columns for richer node snapshots.
390    // Uses ADD COLUMN to migrate existing databases; errors are silently
391    // ignored when the column already exists.
392    add_column_if_not_exists(conn, "node_states", "node_class", "VARCHAR");
393    add_column_if_not_exists(conn, "node_states", "owner_plugin", "VARCHAR");
394    add_column_if_not_exists(conn, "node_states", "goal", "TEXT");
395    add_column_if_not_exists(conn, "node_states", "parent_id", "VARCHAR");
396    add_column_if_not_exists(conn, "node_states", "children", "TEXT");
397    add_column_if_not_exists(conn, "node_states", "last_error_type", "VARCHAR");
398    add_column_if_not_exists(conn, "node_states", "committed_at", "VARCHAR");
399
400    // Task graph edges for deterministic DAG reconstruction on resume
401    conn.execute(
402        "CREATE SEQUENCE IF NOT EXISTS seq_task_graph_edges_id START 1",
403        [],
404    )?;
405
406    conn.execute(
407        r#"
408        CREATE TABLE IF NOT EXISTS task_graph_edges (
409            id INTEGER PRIMARY KEY DEFAULT nextval('seq_task_graph_edges_id'),
410            session_id VARCHAR NOT NULL,
411            parent_node_id VARCHAR NOT NULL,
412            child_node_id VARCHAR NOT NULL,
413            edge_type VARCHAR NOT NULL DEFAULT 'dependency',
414            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
415            FOREIGN KEY (session_id) REFERENCES sessions(session_id)
416        )
417        "#,
418        [],
419    )?;
420    conn.execute(
421        "CREATE INDEX IF NOT EXISTS idx_task_graph_edges_session ON task_graph_edges(session_id)",
422        [],
423    )?;
424    conn.execute(
425        "CREATE INDEX IF NOT EXISTS idx_task_graph_edges_parent ON task_graph_edges(parent_node_id)",
426        [],
427    )?;
428
429    // Review outcomes for explicit approval/rejection tracking
430    conn.execute(
431        "CREATE SEQUENCE IF NOT EXISTS seq_review_outcomes_id START 1",
432        [],
433    )?;
434
435    conn.execute(
436        r#"
437        CREATE TABLE IF NOT EXISTS review_outcomes (
438            id INTEGER PRIMARY KEY DEFAULT nextval('seq_review_outcomes_id'),
439            session_id VARCHAR NOT NULL,
440            node_id VARCHAR NOT NULL,
441            outcome VARCHAR NOT NULL,
442            reviewer_note TEXT,
443            energy_at_review DOUBLE,
444            degraded BOOLEAN,
445            escalation_category VARCHAR,
446            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
447            FOREIGN KEY (session_id) REFERENCES sessions(session_id)
448        )
449        "#,
450        [],
451    )?;
452    conn.execute(
453        "CREATE INDEX IF NOT EXISTS idx_review_outcomes_session ON review_outcomes(session_id)",
454        [],
455    )?;
456    conn.execute(
457        "CREATE INDEX IF NOT EXISTS idx_review_outcomes_node ON review_outcomes(node_id)",
458        [],
459    )?;
460
461    // Verification result snapshots for resume and status display
462    conn.execute(
463        "CREATE SEQUENCE IF NOT EXISTS seq_verification_results_id START 1",
464        [],
465    )?;
466
467    conn.execute(
468        r#"
469        CREATE TABLE IF NOT EXISTS verification_results (
470            id INTEGER PRIMARY KEY DEFAULT nextval('seq_verification_results_id'),
471            session_id VARCHAR NOT NULL,
472            node_id VARCHAR NOT NULL,
473            result_json TEXT NOT NULL,
474            syntax_ok BOOLEAN NOT NULL DEFAULT false,
475            build_ok BOOLEAN NOT NULL DEFAULT false,
476            tests_ok BOOLEAN NOT NULL DEFAULT false,
477            lint_ok BOOLEAN NOT NULL DEFAULT false,
478            diagnostics_count INTEGER DEFAULT 0,
479            tests_passed INTEGER DEFAULT 0,
480            tests_failed INTEGER DEFAULT 0,
481            degraded BOOLEAN NOT NULL DEFAULT false,
482            degraded_reason VARCHAR,
483            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
484            FOREIGN KEY (session_id) REFERENCES sessions(session_id)
485        )
486        "#,
487        [],
488    )?;
489    conn.execute(
490        "CREATE INDEX IF NOT EXISTS idx_verification_results_session ON verification_results(session_id)",
491        [],
492    )?;
493    conn.execute(
494        "CREATE INDEX IF NOT EXISTS idx_verification_results_node ON verification_results(node_id)",
495        [],
496    )?;
497
498    // Artifact bundle snapshots for resume and diff review
499    conn.execute(
500        "CREATE SEQUENCE IF NOT EXISTS seq_artifact_bundles_id START 1",
501        [],
502    )?;
503
504    conn.execute(
505        r#"
506        CREATE TABLE IF NOT EXISTS artifact_bundles (
507            id INTEGER PRIMARY KEY DEFAULT nextval('seq_artifact_bundles_id'),
508            session_id VARCHAR NOT NULL,
509            node_id VARCHAR NOT NULL,
510            bundle_json TEXT NOT NULL,
511            artifact_count INTEGER DEFAULT 0,
512            command_count INTEGER DEFAULT 0,
513            touched_files TEXT,
514            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
515            FOREIGN KEY (session_id) REFERENCES sessions(session_id)
516        )
517        "#,
518        [],
519    )?;
520    conn.execute(
521        "CREATE INDEX IF NOT EXISTS idx_artifact_bundles_session ON artifact_bundles(session_id)",
522        [],
523    )?;
524    conn.execute(
525        "CREATE INDEX IF NOT EXISTS idx_artifact_bundles_node ON artifact_bundles(node_id)",
526        [],
527    )?;
528
529    // =========================================================================
530    // Plan Revision, Feature Charter, and Repair Footprint Tables
531    // =========================================================================
532
533    // Feature charters - scope constraints for sessions
534    conn.execute(
535        r#"
536        CREATE TABLE IF NOT EXISTS feature_charters (
537            charter_id VARCHAR PRIMARY KEY,
538            session_id VARCHAR NOT NULL,
539            scope_description TEXT NOT NULL,
540            max_modules INTEGER,
541            max_files INTEGER,
542            max_revisions INTEGER,
543            language_constraint VARCHAR,
544            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
545            FOREIGN KEY (session_id) REFERENCES sessions(session_id)
546        )
547        "#,
548        [],
549    )?;
550    conn.execute(
551        "CREATE INDEX IF NOT EXISTS idx_feature_charters_session ON feature_charters(session_id)",
552        [],
553    )?;
554
555    // Plan revisions - track plan evolution within a session
556    conn.execute(
557        r#"
558        CREATE TABLE IF NOT EXISTS plan_revisions (
559            revision_id VARCHAR PRIMARY KEY,
560            session_id VARCHAR NOT NULL,
561            sequence INTEGER NOT NULL,
562            plan_json TEXT NOT NULL,
563            reason VARCHAR NOT NULL,
564            supersedes VARCHAR,
565            status VARCHAR NOT NULL DEFAULT 'active',
566            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
567            FOREIGN KEY (session_id) REFERENCES sessions(session_id)
568        )
569        "#,
570        [],
571    )?;
572    conn.execute(
573        "CREATE INDEX IF NOT EXISTS idx_plan_revisions_session ON plan_revisions(session_id)",
574        [],
575    )?;
576    conn.execute(
577        "CREATE INDEX IF NOT EXISTS idx_plan_revisions_status ON plan_revisions(status)",
578        [],
579    )?;
580
581    // Repair footprints - bounded repair units during correction
582    conn.execute(
583        "CREATE SEQUENCE IF NOT EXISTS seq_repair_footprints_id START 1",
584        [],
585    )?;
586    conn.execute(
587        r#"
588        CREATE TABLE IF NOT EXISTS repair_footprints (
589            id INTEGER PRIMARY KEY DEFAULT nextval('seq_repair_footprints_id'),
590            footprint_id VARCHAR NOT NULL UNIQUE,
591            session_id VARCHAR NOT NULL,
592            node_id VARCHAR NOT NULL,
593            revision_id VARCHAR NOT NULL,
594            attempt INTEGER NOT NULL,
595            affected_files TEXT NOT NULL,
596            bundle_json TEXT NOT NULL,
597            diagnosis TEXT NOT NULL,
598            resolved BOOLEAN NOT NULL DEFAULT false,
599            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
600            FOREIGN KEY (session_id) REFERENCES sessions(session_id)
601        )
602        "#,
603        [],
604    )?;
605    conn.execute(
606        "CREATE INDEX IF NOT EXISTS idx_repair_footprints_session ON repair_footprints(session_id)",
607        [],
608    )?;
609    conn.execute(
610        "CREATE INDEX IF NOT EXISTS idx_repair_footprints_node ON repair_footprints(node_id)",
611        [],
612    )?;
613
614    // Budget envelopes - session-level budget tracking
615    conn.execute(
616        r#"
617        CREATE TABLE IF NOT EXISTS budget_envelopes (
618            session_id VARCHAR PRIMARY KEY,
619            max_steps INTEGER,
620            steps_used INTEGER NOT NULL DEFAULT 0,
621            max_revisions INTEGER,
622            revisions_used INTEGER NOT NULL DEFAULT 0,
623            max_cost_usd DOUBLE,
624            cost_used_usd DOUBLE NOT NULL DEFAULT 0.0,
625            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
626            FOREIGN KEY (session_id) REFERENCES sessions(session_id)
627        )
628        "#,
629        [],
630    )?;
631
632    // =========================================================================
633    // PSP-7: SRBN step records and correction attempt telemetry
634    // =========================================================================
635
636    conn.execute(
637        "CREATE SEQUENCE IF NOT EXISTS seq_srbn_step_records_id START 1",
638        [],
639    )?;
640    conn.execute(
641        "CREATE SEQUENCE IF NOT EXISTS seq_correction_attempts_id START 1",
642        [],
643    )?;
644
645    // SRBN step records — one row per orchestration step transition per node.
646    conn.execute(
647        r#"
648        CREATE TABLE IF NOT EXISTS srbn_step_records (
649            id INTEGER PRIMARY KEY DEFAULT nextval('seq_srbn_step_records_id'),
650            session_id VARCHAR NOT NULL,
651            node_id VARCHAR NOT NULL,
652            step VARCHAR NOT NULL,
653            outcome VARCHAR NOT NULL,
654            energy_json TEXT,
655            parse_state VARCHAR,
656            retry_classification VARCHAR,
657            attempt_count INTEGER DEFAULT 0,
658            duration_ms INTEGER DEFAULT 0,
659            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
660            FOREIGN KEY (session_id) REFERENCES sessions(session_id)
661        )
662        "#,
663        [],
664    )?;
665    conn.execute(
666        "CREATE INDEX IF NOT EXISTS idx_srbn_step_records_session ON srbn_step_records(session_id)",
667        [],
668    )?;
669    conn.execute(
670        "CREATE INDEX IF NOT EXISTS idx_srbn_step_records_node ON srbn_step_records(session_id, node_id)",
671        [],
672    )?;
673
674    // Correction attempts — one row per correction round-trip within convergence.
675    conn.execute(
676        r#"
677        CREATE TABLE IF NOT EXISTS correction_attempts (
678            id INTEGER PRIMARY KEY DEFAULT nextval('seq_correction_attempts_id'),
679            session_id VARCHAR NOT NULL,
680            node_id VARCHAR NOT NULL,
681            attempt INTEGER NOT NULL,
682            parse_state VARCHAR NOT NULL,
683            retry_classification VARCHAR,
684            response_fingerprint VARCHAR NOT NULL,
685            response_length INTEGER NOT NULL,
686            energy_json TEXT,
687            accepted BOOLEAN NOT NULL,
688            rejection_reason TEXT,
689            created_at BIGINT NOT NULL,
690            FOREIGN KEY (session_id) REFERENCES sessions(session_id)
691        )
692        "#,
693        [],
694    )?;
695    conn.execute(
696        "CREATE INDEX IF NOT EXISTS idx_correction_attempts_session ON correction_attempts(session_id)",
697        [],
698    )?;
699    conn.execute(
700        "CREATE INDEX IF NOT EXISTS idx_correction_attempts_node ON correction_attempts(session_id, node_id)",
701        [],
702    )?;
703
704    log::info!("DuckDB schema initialized successfully");
705    Ok(())
706}
perspt_store/schema.rs

perspt_store/
schema.rs
