# Append-only event-history SQLite migrations

Use this reference when extending a legacy SQLite event log with typed plans, outcomes, omissions, corrections, opaque public references, and Preview → Confirm writes while preserving historical rows without reinterpretation.

## 1. Freeze the legacy projection

Before migration, record a deterministic digest over the exact legacy columns and ordering. Compare the same projection after migration and after restore. Preserve:

- primary IDs;
- row counts;
- raw values and free-text doses;
- timestamps;
- source and notes;
- NULLs and unknown statuses.

Validate new columns, indexes, triggers, and generated secrets separately. Never let added defaults or bookkeeping tables contaminate the old-projection digest.

## 2. Rebuild without losing schema objects

SQLite table rebuilds silently drop attached indexes and triggers. Before dropping the old table:

1. Read explicit indexes and triggers from `sqlite_master` for that table (`sql IS NOT NULL`).
2. Exclude only objects classified before execution as obsolete, plus objects owned by the new migration and intentionally replaced.
3. For an obsolete uniqueness constraint represented by an automatic index (`sql IS NULL`), identify it by `PRAGMA index_list` plus exact `PRAGMA index_info` columns; do not attempt to recreate it.
4. Copy rows by explicit column list and stable primary-key order.
5. Rename the replacement table.
6. Recreate every preserved unmanaged object from its stored SQL.
7. Compare normalized stored definitions before and after.

A row/value snapshot alone cannot detect lost schema objects.

## 3. Replace managed objects, do not trust names

`CREATE TRIGGER IF NOT EXISTS` and `CREATE INDEX IF NOT EXISTS` can retain stale same-name definitions on partially upgraded databases. For every migration-owned object:

- drop and recreate it transactionally on every application; or
- compare its complete normalized SQL against a versioned expected digest.

`assert_schema()` must verify definitions, not just object names. Regression-test by installing a permissive stale trigger with the correct name, applying the migration twice, and proving the current validation behavior plus no data drift.

## 4. Model corrections as an effective event chain

Corrections are immutable events pointing to the direct prior event. Enforce:

- no self-reference;
- no cycle;
- same subject/medication identity;
- at most one direct successor;
- immutable referenced origins;
- linear repeat corrections.

Read models need one shared latest-effective resolver. The effective status of a correction is its controlled target status; follow successors until the latest event. Apply that resolver everywhere: history, reports, next-plan selection, action capability, and Preview context.

For planned-event consumption:

- a plan corrected to `administered` or `missed` is consumed;
- a plan whose latest effective state is `planned` remains open;
- a linked administration/omission corrected back to `planned` no longer consumes the plan;
- legacy synonyms such as `scheduled`/`geplant` may be displayed as planned only under an explicit compatibility map, never by broad heuristic classification.

Do not filter candidate plans with a raw SQL `EXISTS` on original rows and then apply `LIMIT`; resolve effective states first, then limit the visible unconsumed result.

## 5. Keep public capabilities opaque

- Public object references and revision/capability tokens use domain-separated HMAC under a private database- or runtime-held key.
- Never expose deterministic unkeyed revisions derived from numeric internal IDs; a client can enumerate small IDs and reverse the namespace.
- The server computes Preview from the complete normalized payload plus current prescription, plan-consumption, and correction-chain context.
- Confirm recomputes and constant-time compares the token; any payload or database drift fails closed.
- Durable action identity and replay remain at one existing idempotency/audit boundary; Preview must not create a competing write identity.
- Keep stable opaque medical/event references out of visible URLs and browser history state when they can correlate sensitive records.
- Reject unsafe filter input explicitly. A sanitizer returning `None` must produce a 4xx, not silently disable the filter.

## 6. Separate statuses and dose semantics

A trusted active record requires the complete structured contract: controlled status, controlled source/provenance, and a structured revision. Missing evidence projects to `unknown`; it must not enable new actions.

For new typed events:

- `administered` requires actual dose value and unit;
- `planned` carries planned dose fields only;
- `missed` does not assert planned or actual dose values;
- a correction validates dose fields against its `corrected_target_status`.

Legacy dose text remains separately labelled as uninterpreted. Never fall it back under an affirmative “actual dose” label.

Reports must identify superseded originals and intermediate corrections, or emit only the latest effective representation. Hiding correction lineage causes contradictory clinical summaries.

## 7. Release evidence

Required copy-first evidence:

1. private `0700` evidence root and `0600` files;
2. current online backup plus SHA-256;
3. migration on a disposable copy;
4. second application proving idempotence;
5. frozen legacy projection digest equality;
6. complete expected row/ID/raw-value comparison;
7. `PRAGMA integrity_check = ok`;
8. zero `PRAGMA foreign_key_check` rows;
9. independent restore with hash and logical equality;
10. old and new readers against source/candidate/restore;
11. focused counterexample tests, then the complete suite;
12. independent database, security/privacy, and domain-safety reviews against the exact frozen tree.

Any post-review fix invalidates an already-running or completed “final” suite as release authorization. Preserve the result as regression evidence, but rerun the required gates against the fixed candidate. Do not commit, push, cut over, or deploy while any independent verdict remains blocked.