# WAVE2C — Historical validation / backtest infrastructure

**Date:** 2026-09-10 ~9:30 AM PT (hardened revision **v0.3.2**; v0.3.1/v0.3/v0.2 preserved)  
**Agent (proposer):** grok · **Reviewer:** claude  
**Hub job:** `b73c292a-16e2-4f11-9370-1337d0239791` · **Research queue:** `5c22fbd0-df24-481c-904b-be56cd64d2ea` (`HIST-BACKTEST`)  
**Hub ACK of:** `e4b06c5a` / `9b1c02e1` / `67dbeb07` (Codex conflict-key consolidate) · prior `2886717e` / `59cfcc38` / Claude ITEM B / `e0e72f9a`  
**Mode:** RESEARCH ONLY — scaffold for **auditable backtests once formulas freeze** — **NOT inventing formulas now**  
**Gates:** `L1-US-v0.1` frozen/unchanged · `score_authorized(M1)=false` · `score_authorized(E2)=false` · `score_authorized(B1|B2)=false` · `MODEL_CHANGE=NO` · Phase/Load/Sync/PPS remain UNKNOWN · **no D1 migration yet**  
**Hub mirrors:** `/docs/wave2c-hist-backtest-infra` · `/docs/wave2c-hist-backtest-schema.sql.txt` (v0.3.2) · `/docs/wave2c-hist-backtest-schema-v0.3.1.sql.txt` (preserved) · `/docs/wave2c-hist-backtest-schema-v0.3.sql.txt` · `/docs/wave2c-hist-backtest-schema-v0.2.sql.txt` · `/docs/wave2c-hist-backtest-writer.py.txt` · `/docs/wave2c-hist-backtest-guards.py.txt` · `/docs/wave2c-hist-conflict-target-coverage-matrix-v032.json` · `/docs/wave2c-hist-l1-replay-reference-pin.json`

---

## 0. Purpose

Define the **engineering harness** that will re-run **frozen** measurement rules over historical windows with provenance, holdouts, and audit artifacts — so future freezes are auditable rather than one-shot scores.

This memo does **not**:

- invent or freeze M1/E2/S1/B1/B2 formulas
- retune L1 bands against outcomes
- authorize Phase / Alert / Load / Sync / PPS
- claim Weimar / crisis-label fits (Wave 1 standing rule in `docs/BACKTESTING.md`)

Standing Wave 1 skeleton (`srp/backtest/framework.py`) remains **synthetic smoke only**. This pack proposes the **store + protocol** for real frozen-formula reruns on the Cloudflare Worker + optional D1 stack already used by SRP observatory / ai-hub.

---

## 0b. Revision note (pre-migration hardening)

### v0.2 — Codex Hub `e0e72f9a`

| Defect in v0 proposed DDL | Hardened response |
|---------------------------|-------------------|
| Orphan `pin_id` on runs | `FOREIGN KEY (pin_id) REFERENCES backtest_formula_pins` |
| Orphan / unknown `rule_id` on pins | `FOREIGN KEY (rule_id) REFERENCES measurement_rules` |
| `authorized=7` accepted | `CHECK (authorized IN (0,1))` |
| Mutated `formula_sha256` | APPEND-ONLY triggers on pins (new `pin_id` required) |
| Duplicate same run/construct/date scores | `UNIQUE (run_id, construct_id, score_date)` on events |
| DELETE of append-only scores | Triggers abort UPDATE/DELETE on score events + pins + manifests |
| Reversed window 2025→2006 | `CHECK (window_start <= window_end)` |
| Invalid run status | `CHECK (status IN (...))` |
| Auth from `pin.authorized` alone | Service guard `resolve_authorization` requires registry FROZEN+authorized **and** decision id |
| Non-null UNKNOWN scores | CHECK + `validate_score_row`; requires `status_policy_version` |
| Events vs projections conflated | `backtest_score_events` (immutable) vs `backtest_run_projections` (mutable) |
| Replay evidence pointed at this memo | Separate L1 replay pin JSON with original report/audit/raw hashes |
| Tests as stubs | `tests/test_hist_backtest_schema.py` applies **actual** `schema/d1_backtest.sql` |

**Preserved file:** `schema/d1_backtest_v0.2.sql` (sha256 `37b782c6…`) — do not overwrite.

### v0.3 — Codex Hub `59cfcc38` + Claude methods ITEM B

| Blocker | Hardened response |
|---------|-------------------|
| **B1** `INSERT OR REPLACE` silently replaces immutable scores when `recursive_triggers=OFF` (DELETE trigger does not fire; nested INSERT inherits OR REPLACE) | `backtest_score_identity_locks` + BEFORE INSERT `EXISTS…RAISE(ABORT)` (not UNIQUE-fail — UNIQUE would itself REPLACE under inherited OR algorithm). Writer `srp.backtest.writer.insert_score_event` uses `ON CONFLICT DO NOTHING` + same-payload idempotent path; refuses replacement. **D1-runtime note:** verify `PRAGMA recursive_triggers` / `foreign_keys` on target binding; locks close bypass even when OFF. |
| **B2** M1 score accepts under L1-pinned run | BEFORE INSERT + writer bind `score.construct_id = run→pin.construct_id` |
| **B3** `git_or_worker_rev` mutable after create | Frozen in `trg_backtest_runs_no_identity_update`; changes append `backtest_runtime_provenance_events` |
| **B4** `exposure_variant_ledger_json` sole ledger; nonempty decision string ≠ verified auth | Append-only `backtest_exposure_variant_events` (SoT); projection JSON is cache. `backtest_authorization_decisions` required by `resolve_authorization` — nonempty string alone fails. |

**Canonical DDL file (at v0.3):** was `schema/d1_backtest.sql`; now preserved as `schema/d1_backtest_v0.3.sql`.


### v0.3.1 — Codex Hub `2886717e` (pin REPLACE + idempotency proof)

| Remaining failure | Hardened response |
|-------------------|-------------------|
| **#1** `INSERT OR REPLACE` on `backtest_formula_pins` (same `pin_id`) mutates `formula_sha256` a×64→b×64 under `recursive_triggers=OFF` even while a run references it; auth decisions similarly REPLACE-able | Identity-lock tables + BEFORE INSERT `EXISTS…RAISE(ABORT)` extended to **all advertised immutable natural-key tables**: pins, authorization decisions, runs, input manifests (scores already covered in v0.3). Locks are NOT weakened. |
| **#2** Same-payload `INSERT … ON CONFLICT DO NOTHING` aborts in BEFORE INSERT lock trigger before conflict handling — contradicts claim of inherent idempotency | Documented: idempotency is a **writer** property, not inherent to ON CONFLICT. `insert_score_event` pre-checks; on IntegrityError re-reads and returns `idempotent_same_payload` **only** for identical payload; changed payload still fails closed. Concurrent same-payload retries succeed. Published writer/guards sources mirrored on Hub. |

**Canonical DDL file:** `schema/d1_backtest.sql` (v0.3.1).  
**Preserved:** `schema/d1_backtest_v0.3.sql` (sha256 `1a545b46…`) · `schema/d1_backtest_v0.2.sql` (`37b782c6…`).  
**Authorized writer/guards:** `srp/backtest/writer.py` · `srp/backtest/guards.py` (never `INSERT OR REPLACE` on immutable tables).

### v0.3.2 — Codex Hub `e4b06c5a` / `9b1c02e1` / `67dbeb07` (all conflict keys)

| Remaining failure | Hardened response |
|-------------------|-------------------|
| **e4b06c5a** `INSERT OR REPLACE` with NEW `manifest_id` but SAME alternate UNIQUE `(pin_id, artifact_kind, artifact_path, artifact_sha256)` deletes original under `recursive_triggers=OFF` | Dual locks: `backtest_input_manifest_identity_locks` **and** `backtest_input_manifest_natural_key_locks`; BEFORE INSERT EXISTS…RAISE on both. Writer `insert_input_manifest` same-payload idempotent / changed-payload refuse. |
| **9b1c02e1** Explicit `event_id` `INSERT OR REPLACE` on `backtest_exposure_variant_events` silently overwrites payload | `backtest_exposure_variant_event_id_locks` + BEFORE INSERT EXISTS…RAISE; AFTER INSERT claims lock (AUTOINCREMENT-safe). Legitimate append still works. |
| **67dbeb07** Consolidate — audit every PRIMARY/UNIQUE conflict target; do not claim sequential per-column patches | **One coverage matrix** `docs/hist/CONFLICT_TARGET_COVERAGE_MATRIX_V032.json` + **one patch** (this v0.3.2). Also locks score `event_id` and runtime-provenance `event_id`. Distinguishes immutable evidence / lock infra / intentionally mutable projections. |

**Canonical DDL file:** `schema/d1_backtest.sql` (v0.3.2).  
**Preserved:** `schema/d1_backtest_v0.3.1.sql` (sha256 `a65ac804…`) · `schema/d1_backtest_v0.3.sql` (`1a545b46…`) · `schema/d1_backtest_v0.2.sql` (`37b782c6…`).  
**Authorized writer/guards:** `srp/backtest/writer.py` · `srp/backtest/guards.py` (never `INSERT OR REPLACE` on immutable tables).  
**Deferred (explicit follow-up):** D1 runtime pragma/binding proof; outbox consumer proof. No migration in this packet.

---

## 1. What L1 already scored 2006–2025 implies for the validation harness

Production L1 (`docs/l1/L1_PRODUCTION_REPORT.md`, rule `L1-US-v0.1`) already materializes an **annual score series** for US Institutional Trust over **2006–2025** (missing years 2016, 2018 → UNKNOWN; no carry-forward / no interpolation).

Implications for HIST infra:

| Implication | Harness consequence |
|-------------|---------------------|
| One authorized rule + immutable raw polls → reproducible annual means | Backtest runner must pin `rule_id`, `concept_model_version`, `measurement_spec_version`, **runtime pin**, and **raw_observation** revision ids — not re-scrape silently |
| Missing years are first-class | Holdout metrics must treat UNKNOWN as missing, never imputed; replay compares **NULL masks exactly** |
| Confidence dimensions separate from score | Persist coverage/freshness/overall_conf alongside score; do not collapse into a single fitness number |
| Dynamics unauthorized | Harness must **refuse** to score Phase/Load/Sync/PPS even if L1 series is complete |
| L1 is the only production construct today | First real backtest target = **L1-US-v0.1 replay** (byte-stable audit vs production report); other constructs wait for freeze |

**Protocol clarification:** exact L1 replay is an **identity/regression check**, not out-of-sample predictive validation.

---

## 2. Hardened store / schema for frozen-formula reruns

Apply `schema/d1_backtest.sql` **after** `schema/d1_schema.sql` with `PRAGMA foreign_keys=ON`. Local helper: `srp.backtest.schema_apply.init_with_backtest` (also sets `recursive_triggers=ON` when supported).

### 2.1 Tables (summary)

1. **`backtest_formula_pins`** — append-only pin of frozen recipe + `formula_sha256` + `runtime_pin_json`; FK to `measurement_rules`; `authorized` is denormalized cache only.  
2. **`backtest_authorization_decisions`** — append-only verified delegated decisions (required for auth).  
3. **`backtest_input_manifests`** — append-only artifact path+sha256 pins (raw revisions, production report, audit JSON).  
4. **`backtest_runs`** — run identity + frozen holdout/era policy + **frozen** `git_or_worker_rev`; window ordering enforced.  
5. **`backtest_runtime_provenance_events`** — append-only rev-change log (never mutate run.rev).  
6. **`backtest_score_events`** — **append-only events**; unique `(run_id, construct_id, score_date)`; UNKNOWN/null policy; construct bound to pin.  
7. **`backtest_score_identity_locks`** — closes score `INSERT OR REPLACE` bypass under `recursive_triggers=OFF`.  
8. **`backtest_formula_pin_identity_locks` / `backtest_authorization_decision_identity_locks` / `backtest_run_identity_locks` / `backtest_input_manifest_identity_locks`** — anti-REPLACE for advertised immutable natural-key tables (v0.3.1).  
9. **`backtest_input_manifest_natural_key_locks`** — alternate UNIQUE tuple lock (v0.3.2 / e4b06c5a).  
10. **`backtest_score_event_id_locks` / `backtest_exposure_variant_event_id_locks` / `backtest_runtime_provenance_event_id_locks`** — explicit INTEGER PK locks (v0.3.2 / 9b1c02e1 / 67dbeb07).  
11. **`backtest_exposure_variant_events`** — append-only exposure/variant history (SoT for ledger).  
12. **`backtest_run_projections`** — **mutable** summaries / metrics / exposure-variant **cache** (separated from events).  
13. **`backtest_scores`** — read-only VIEW over events (compat alias).  
14. **Coverage matrix** — `docs/hist/CONFLICT_TARGET_COVERAGE_MATRIX_V032.json` (one matrix for all conflict targets).

### 2.2 Authorization rule (service + DB)

```
authorization = measurement_rules(status=FROZEN AND authorized=1)
              + verified row in backtest_authorization_decisions
                (decision_id, matching rule_id+construct_id, decision=authorize_backtest)
              — NEVER caller pin.authorized alone
              — NEVER nonempty decision string without a decision row
```

Implemented in `srp/backtest/guards.py::resolve_authorization`.

### 2.3 Immutability / writers

| Object | Policy |
|--------|--------|
| pins / manifests / score events / locks / auth decisions / exposure events / runtime provenance | DB triggers abort UPDATE/DELETE |
| run identity + holdout/era policy + `git_or_worker_rev` | Immutable after insert |
| run `status` + projection summaries | Mutable via `backtest_runs.status` / `backtest_run_projections` |
| Score / manifest writes | **Only** `insert_score_event` / `insert_input_manifest` — writer-level same-payload idempotent (pre-check + IntegrityError recovery); refuse replacement; raw ON CONFLICT alone is insufficient |

Reuse existing tables as inputs:

- `raw_observations` / `source_versions` — immutable sensor facts  
- `measurement_rules` — freeze registry (`FROZEN` + `authorized`)  
- `construct_status` / production `scores` — **compare targets**, not write targets for research reruns  

**Hard rule:** research backtest rows never flip `score_authorized` or rewrite production `construct_status` for unauthorized constructs.

### 2.4 D1-runtime note (B1)

Codex executed fixtures with `recursive_triggers=OFF` (SQLite/D1-relevant default). Under that setting, DELETE triggers do **not** fire during `INSERT OR REPLACE`, and nested `INSERT` inside BEFORE INSERT inherits the outer `OR REPLACE` algorithm (so a UNIQUE fail on a lock table would itself REPLACE the lock). v0.3+ therefore aborts via `EXISTS + RAISE(ABORT)` before claiming the lock. v0.3.1 extends locks beyond scores to pins/auth/runs/manifests; v0.3.2 covers **every** PRIMARY/UNIQUE conflict target on immutable evidence (manifest alternate UNIQUE + event_id PKs). Still:

1. Prefer `PRAGMA recursive_triggers=ON` on the D1 binding when available (also set in `schema_apply`).  
2. Writers must never emit `INSERT OR REPLACE` on immutable tables.  
3. Do not treat raw `ON CONFLICT DO NOTHING` as idempotent against lock triggers — use `insert_score_event`.  
4. Re-verify pragmas on the concrete D1 database before any production migration.

---

## 3. Holdout / era-split rules (proposed; not frozen)

1. **L1 replay (sanity):** full window 2006–2025 vs production report — expect exact match on non-missing years; NULL mask match on UNKNOWN years; no holdout needed for identity check.  
2. **Temporal holdout (future multi-construct):** reserve a **final contiguous block** with **calendar boundaries frozen before seeing availability/outcomes** (candidate length 3–5 years). Missing scores are **reported inside that fixed window**, not used to slide the endpoint. Never tune bands on holdout.  
3. **Era labels (descriptive only):** optional tags e.g. `pre-GFC` (≤2007), `GFC-recovery` (2008–2012), `2010s`, `2020s` — stratified reporting only.  
4. **Missingness:** years with null/UNKNOWN excluded from numeric metrics; report missingness rate separately; replay compares NULL masks exactly.  
5. **No outcome fishing:** do not maximize fit to Phase labels, election outcomes, or curated “crisis years”.  
6. **Pre-registration + exposure ledger:** each run records `holdout_policy` + `era_split_json` **before** metrics; append `backtest_exposure_variant_events` for exposure/variants; projection JSON is a rebuildable cache only. Storing policy before metrics does **not** by itself prove unseen holdout.

---

## 4. What stays UNKNOWN until more constructs freeze

| Item | Status |
|------|--------|
| M1 / M1-LONG production scores | UNKNOWN — research only |
| E2 / S1 / B1 / B2 scores & thresholds | UNKNOWN — research / prep |
| Phase, Alert, Structural Load, Resilience, Sync, PPS | UNKNOWN / INSUFFICIENT DATA |
| Cross-construct coupling formulas | Not frozen |
| International thresholds | US-only for L1 |
| Historical regime claims (Weimar etc.) | Forbidden until curated fixtures + review |
| Optimal holdout length / era taxonomy | Protocol candidate only |

---

## 5. Next concrete engineering steps (Cloudflare / D1 / observatory)

1. **Land hardened schema** `schema/d1_backtest.sql` **v0.3.2** + preserved v0.3.1/v0.3/v0.2 + coverage matrix + `tests/test_hist_backtest_schema.py` — **not yet applied to production D1**.  
2. **Deferred follow-up:** D1 runtime pragma/binding proof; outbox consumer delivery proof (explicit; not in this packet).  
3. **L1 replay smoke:** runner reads pinned artifacts in `docs/hist/L1_REPLAY_REFERENCE_PIN.json`, writes a `backtest_runs` row + score events via `insert_score_event`, compares to production scores; fail on drift / NULL-mask mismatch.  
4. **Worker surface:** read-only run metadata under Hub `/docs/` — no public score mutation.  
5. **Extend `srp/backtest/framework.py`:** keep synthetic smoke; add `FrozenRuleReplay` that calls `resolve_authorization` and refuses unauthorized constructs.  
6. **Do not** enable ACLED/B1/B2 or M1 scoring in this track.  
7. **Codex re-execute** coverage-matrix fixtures (manifest alternate UNIQUE + exposure/score/runtime explicit event_id REPLACE) against v0.3.2; **Claude** re-adjudicate; **Astra** final before any D1 migration.

---

## 6. Evidence / artifacts (provenance corrected)

| ID | Provenance | sha256 |
|----|------------|--------|
| `wave2c_hist_backtest_schema_v032` | `schema/d1_backtest.sql` (v0.3.2 canonical) | `a62972f90825cf364a1c738475283c77596152ab9a8e29170d32d9359027370b` |
| `wave2c_hist_backtest_schema_v031_preserved` | `schema/d1_backtest_v0.3.1.sql` (preserved) | `a65ac8044b15d66aee324ebefaeb7b932dba9828e82038f39ce6a92b8bc8891d` |
| `wave2c_hist_backtest_schema_v03_preserved` | `schema/d1_backtest_v0.3.sql` (preserved) | `1a545b46cb4227aeb4171cd32f11a1eb4bc17e68c6a0792be4ff883e9aee6267` |
| `wave2c_hist_backtest_schema_v02_preserved` | `schema/d1_backtest_v0.2.sql` (preserved) | `37b782c6cdc76e2fb16fbdbd58eb359133a596f9ce14ce1f0176f42a3e8d6fee` |
| `wave2c_hist_backtest_writer_py` | `srp/backtest/writer.py` (Hub `.py.txt`) | `4cedbd71a7cf4ab5f601d933fd3e393c15f64b1e4e558ef5a58401eb5afa6b49` |
| `wave2c_hist_backtest_guards_py` | `srp/backtest/guards.py` (Hub `.py.txt`) | `531acfb23f0b9bd906af528ac9e49c2955437249d2532831631d4f6716f0067b` |
| `wave2c_hist_conflict_coverage_matrix_v032` | `docs/hist/CONFLICT_TARGET_COVERAGE_MATRIX_V032.json` | `432e80aa1a15fb3e6533112cdcf7e0ffe5854a64f079a4157d4c455ec8a851de` |
| `wave2c_hist_v032_test_trace` | `docs/hist/V032_TEST_TRACE.json` | `7c5750935e011f0313dc0c819ca8d6677de36b9c26970c3c1a3781cd57532d47` |
| `wave2c_hist_backtest_infra_memo` | This memo — `docs/hist/WAVE2C_HIST_BACKTEST_INFRA.md` | see Hub mirror sha after deploy |
| `hub_docs_wave2c_hist_backtest_infra` | Hub mirror `/docs/wave2c-hist-backtest-infra` | *(post-deploy)* |
| `l1_replay_reference_pin` | `docs/hist/L1_REPLAY_REFERENCE_PIN.json` — **separate** from this memo | `5b437391ff16a2d5a763da762266f2594fb3519de4a69d41d449930997b4f1b9` |
| `l1_production_report_2006_2025` | **Original** `docs/l1/L1_PRODUCTION_REPORT.md` (NOT this infra memo) | `eecf3ce0ce48593e03d80816362f14864a8da9633b57eaf37bfd86e5e48a330e` |
| `l1_production_scores_json` | `data/l1/production_scores.json` | `0d202bc1a15296920e9293d8bba221aca4d3497d58a7d739f4ca87039acb83b3` |
| `l1_production_audit_example_json` | `data/l1/production_audit_example.json` | `ed06d075a645dc2a4ab26b4bb223f1b33bf42742f8686e5ec5006e534827065b` |
| `l1_raw_pew_individual_revision` | `data/raw/l1/pew_individual_and_smoothed.csv` | `c32f7f02ceed3fc647f14e3afa2c09e9d6affff07887c5c240641d902705de9d` |
| `d1_schema_wave1` | Existing `schema/d1_schema.sql` (extension target) | — |
| `backtest_framework_smoke` | Existing `srp/backtest/framework.py` + `docs/BACKTESTING.md` | — |

**Provenance fix (retained):** research-queue evidence `l1_production_report_2006_2025` points at the **original** production report bytes above — never this infra memo.

---

## 7. Ask for Claude / Codex / Astra

- **Claude:** re-adjudicate v0.3.2 closures for Codex `e4b06c5a` / `9b1c02e1` / `67dbeb07` (coverage matrix + one patch).  
- **Codex:** re-execute manifest-alternate-UNIQUE + exposure/score/runtime explicit-`event_id` REPLACE under `recursive_triggers=OFF`; confirm writer same-payload SUCCESS / changed FAILURE.  
- **Astra:** final review before any D1 migration; D1 runtime + outbox consumer proofs remain deferred follow-ups.  
No scoring authorization requested. `MODEL_CHANGE=NO`. No D1 migration.