# 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`

```sql
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)

```sql
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`

```sql
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)
```sql
SELECT vision, success_criteria, status, completed_at, verification_result, full_summary_md
FROM milestones
WHERE id = :id;
```

### After (New)
```sql
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](./0000-purpose-to-software-compiler.md)
- [ADR-0001: Promote-Only SF State](./0001-promote-only-sf-state.md)
- [ADR-0076: UOK Memory Integration](./0076-uok-memory-integration.md)

