singularity-forge/docs/adr/0077-spec-runtime-evidence-schema-separation.md

ADR-0077: Spec/Runtime/Evidence Schema Separation (Tier 1.3)

Status: Proposed (implementation in progress for SF v3.0)
Date: 2026-05-07
Stakeholders: SF v3.0 core team, UOK dispatch engine, milestone/slice/task tools

---

Problem Statement

Current state: Milestone, slice, and task data are stored in wide monolithic tables that mix three distinct concerns:

1. Spec data — immutable record of intent (vision, goals, success criteria, proof strategy)
2. Runtime state — current execution state (status, completed_at, blockers, dependencies)
3. Evidence/narrative — what happened during execution (verification results, decisions, descriptive summaries)

Problems this creates:

1. Spec immutability unclear — Spec data (vision, goals, risks) can be updated in place, but should represent intent
2. Re-planning awkwardness — When a milestone is re-planned, old spec data is overwritten or lost to markdown projections; unclear what was originally intended
3. Query complexity — Queries select across many irrelevant columns; indexing and partitioning are hard
4. Evidence chain missing — Verification results and narratives are in the same table as specs, making it impossible to audit "why was this decision made?"
5. Data archaeology disabled — Cannot reconstruct the decision history when a milestone enters an unexpected state
6. Table bloat — As narrative/evidence fields grow, the main runtime table grows unnecessarily

---

Proposed Solution: 3-Table Schema (Per Entity Type)

Normalize milestone, slice, and task data from 1 wide table per entity into 3 focused tables:

Target Schema: 9 Tables Total

For each entity type (milestone, slice, task):

1. Spec Table (immutable record of intent)

Example: milestone_specs

CREATE TABLE milestone_specs (
  id TEXT PRIMARY KEY,             -- matches milestone.id
  vision TEXT NOT NULL DEFAULT '', -- immutable spec
  success_criteria TEXT DEFAULT '', -- JSON array, immutable spec
  key_risks TEXT DEFAULT '',        -- JSON array, immutable spec
  proof_strategy TEXT DEFAULT '',   -- JSON array, immutable spec
  verification_contract TEXT DEFAULT '', -- contract spec
  verification_integration TEXT DEFAULT '',
  verification_operational TEXT DEFAULT '',
  verification_uat TEXT DEFAULT '',
  definition_of_done TEXT DEFAULT '', -- JSON array
  requirement_coverage TEXT DEFAULT '',
  boundary_map_markdown TEXT DEFAULT '',
  vision_meeting_json TEXT DEFAULT '', -- JSON meeting notes
  spec_version INTEGER NOT NULL DEFAULT 1, -- support multi-version specs in future
  created_at TEXT NOT NULL,
  PRIMARY KEY (id)
);

Semantics:

• Write-once; no UPDATE after initial creation
• Represents what the milestone owner intended when planning began
• When a milestone is re-planned, a new spec version is created (spec_version increments)
• Foreign key to milestones(id) ensures referential integrity

2. Runtime Table (current execution state)

Example: milestones (renamed from current — spec removed)

CREATE TABLE milestones (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL DEFAULT '',
  status TEXT NOT NULL DEFAULT 'active', -- active/paused/complete/done/canceled
  depends_on TEXT DEFAULT '[]',          -- JSON array of milestone IDs
  created_at TEXT NOT NULL,
  completed_at TEXT DEFAULT NULL,
  replan_count INTEGER DEFAULT 0,
  PRIMARY KEY (id)
);

Semantics:

• Mutable; represents current state of execution
• Only runtime-relevant columns (status, dependencies, timestamps)
• Foreign key from spec tables (milestone_specs.id → milestones.id)
• Efficient for status queries and state transitions

3. Evidence Table (timestamped audit trail)

Example: milestone_evidence

CREATE TABLE milestone_evidence (
  milestone_id TEXT NOT NULL,
  evidence_type TEXT NOT NULL, -- enum: verification_contract, verification_integration, verification_operational, verification_uat, narrative, decision, incident
  content TEXT NOT NULL,       -- markdown, JSON, or structured content
  recorded_at TEXT NOT NULL,   -- when evidence was recorded
  phase_name TEXT DEFAULT '',  -- which phase/executor created this
  recorded_by TEXT DEFAULT '', -- agent name or "manual"
  evidence_id TEXT NOT NULL DEFAULT (lower(hex(randomblob(16)))),
  PRIMARY KEY (milestone_id, evidence_id),
  FOREIGN KEY (milestone_id) REFERENCES milestones(id)
);

Semantics:

• Append-only; rows are never updated or deleted (unless retention policy triggers archival)
• Timestamped audit trail of decisions, verifications, incidents
• Can be queried chronologically to reconstruct decision history
• Supports data archaeology: "Why did this milestone enter a stuck state?"

---

Applied to All Three Entity Types

Apply the same 3-table pattern to slices and tasks:

• slice_specs, slices, slice_evidence
• task_specs, tasks, task_evidence

Total: 9 new/refactored tables

---

Query Model Changes

Before (Current)

SELECT vision, success_criteria, status, completed_at, verification_result, full_summary_md
FROM milestones
WHERE id = :id;

After (New)

SELECT s.vision, s.success_criteria, r.status, r.completed_at, e.content
FROM milestones r
LEFT JOIN milestone_specs s ON r.id = s.id
LEFT JOIN milestone_evidence e ON r.id = e.milestone_id AND e.evidence_type = 'verification_contract'
WHERE r.id = :id
ORDER BY e.recorded_at DESC;

Benefits:

• Each table has only relevant columns
• Indices can be more efficient (e.g., index on milestone_evidence(evidence_type, recorded_at))
• Queries self-document intent (joins explain what's spec vs. runtime vs. evidence)

---

Implementation Phases

Phase 1: Schema Definition (0.5d)

• Define 9 new tables in sf-db.js
• Add CREATE TABLE statements and schema version bump
• Document column types and constraints

Phase 2: Data Migration (1.0d)

• Create migration script that reads current schema
• Populate new *_specs tables from current spec columns
• Populate new *_runtime tables (will rename after migration)
• Populate new *_evidence tables from current narrative/verification columns
• Test migration on real SF project data

Phase 3: Data Layer Updates (1.0d)

• Update insertMilestone(), insertSlice(), insertTask() to write to both spec and runtime tables
• Create insertMilestoneEvidence(), insertSliceEvidence(), insertTaskEvidence() functions
• Update query functions (getMilestone(), getMilestoneSlices(), etc.) to JOIN across new tables
• Update UPDATE functions (upsertMilestonePlanning(), etc.) to write only to spec table

Phase 4: Tool Updates (0.5d)

• Update plan-milestone, plan-slice, plan-task tools to use new insert functions
• Update complete-milestone, complete-slice, complete-task tools to record evidence
• Verify existing workflows (dispatch loop, replan, re-execute) still work

Phase 5: Testing (0.5d)

• Write migration tests (verify data integrity across migration)
• Write query tests (verify new queries return same data as old queries)
• Write immutability tests (verify specs cannot be modified after creation)
• Write evidence chain tests (verify evidence is timestamped and queryable)

---

Data Integrity Rules

1. Spec immutability: No UPDATE on *_specs tables after initial INSERT

   • If a change is needed, INSERT a new spec version and INCREMENT spec_version

2. Runtime-spec linkage: Foreign key constraint ensures runtime.id maps to spec.id

3. Evidence timestamping: All *_evidence rows have recorded_at set at insertion time (cannot be NULL)

4. Retention policy: Evidence is append-only unless retention policy expires rows (future decision)

---

Risk Mitigation

Risk	Mitigation
Migration complexity	Dry-run migration on sample data first; create rollback script
Breaking existing tools	Update all callers of insertMilestone, insertSlice, insertTask systematically
Performance regression	Profile new JOIN queries; add indices on frequently-accessed columns
Over-engineering	Start with milestone tables; defer slice/task until stable

---

Expected Benefits

1. Clear semantics — Spec is intent, runtime is state, evidence is history
2. Auditability — Can reconstruct why a decision was made by reading evidence chain
3. Re-planning clarity — Multiple spec versions can exist for the same milestone ID
4. Query efficiency — Each query only loads columns it needs; better cache locality
5. Data archaeology — Enables forensics tools to trace decision history
6. Future extensibility — Can add spec versioning, evidence retention policies, etc. without schema churn

---

Open Questions

1. Evidence retention: Should old evidence ever be archived or deleted? Or indefinite retention?
2. Spec versioning: Should spec versions be labeled or just incremented (e.g., "v1", "v2.1")?
3. Re-planning linkage: When a milestone is re-planned, should the new spec version reference the old one?
4. Performance trade-off: Are JOINs acceptable, or should we denormalize certain columns for read performance?
5. Phased rollout: Should we migrate all three entity types at once, or start with milestones?

---

Appendix: Detailed Column Mappings

Milestones: Current → New

Current milestones	New milestones (Runtime)	New milestone_specs (Spec)
id	id	id
title	title	—
status	status	—
depends_on	depends_on	—
created_at	created_at	created_at
completed_at	completed_at	—
vision	—	vision
success_criteria	—	success_criteria
key_risks	—	key_risks
proof_strategy	—	proof_strategy
verification_contract	—	verification_contract
verification_integration	—	verification_integration
verification_operational	—	verification_operational
verification_uat	—	verification_uat
definition_of_done	—	definition_of_done
requirement_coverage	—	requirement_coverage
boundary_map_markdown	—	boundary_map_markdown
vision_meeting_json	—	vision_meeting_json

Evidence Table Sources

New milestone_evidence table will be populated from:

• Current verification_result → evidence_type='verification_contract'
• New events created when milestone transitions to complete or done → evidence_type='decision'
• New incidents recorded during re-plan or escalation → evidence_type='incident'

---

References

• ADR-0000: SF Is a Purpose-to-Software Compiler
• ADR-0001: Promote-Only SF State
• ADR-0076: UOK Memory Integration