# SQLite production-guarded release

Use this pattern when a full-stack release owns an additive SQLite migration and the test suite, UAT, and deployed service can otherwise resolve the same default runtime path as production.

## 1. Fail closed before test collection

A test fixture alone is insufficient if application modules can resolve settings or run migrations during import, app construction, or collection.

- Give test/UAT an explicit environment discriminator such as `APP_ENV=test|uat`.
- In the central settings loader, require **both** database and runtime paths to resolve beneath a designated temporary root (normally `/tmp`) whenever that discriminator is active.
- Reject the production runtime directory, production DB, symlink aliases, and every non-temporary path before opening SQLite.
- In the global test bootstrap, inspect any caller-supplied paths first; fail rather than silently overriding an explicitly dangerous path. If paths are absent, install process-unique `/tmp` defaults before importing application code.
- Initialize only that isolated DB in a session fixture.
- A CLI test that changes its runtime directory must also set the corresponding DB path explicitly. A globally isolated DB environment otherwise takes precedence and makes the test seed one DB while the CLI reads another.

Required guard evidence:

1. explicit production DB path is rejected before collection/app startup;
2. explicit production runtime is rejected;
3. `/tmp` DB/runtime succeeds;
4. production schema, integrity, and logical digest or protected checksum are unchanged after the negative test.

## 2. Diagnose a suspected accidental migration

Stop normal tests immediately. Do not continue merely because the migration is additive.

1. Identify active DB writers and open handles.
2. Read schema version and `integrity_check` read-only.
3. Attribute every new table/index/trigger to the exact migration.
4. Count new migration-owned rows, audits, lineages, and dependent canonical records.
5. Compute a deterministic business-data digest excluding only migration metadata and the attributed new empty objects.
6. Compare with the trusted pre-event state or backup.
7. Present the user with the concrete options if a production rollback is needed; execute only the selected option.

For an authorized schema rollback:

- create a consistent SQLite online backup first;
- verify backup SHA-256, schema, and integrity;
- drop only uniquely attributable empty objects in one transaction;
- delete the migration ledger row last;
- prove the business digest is unchanged;
- never repeat an accepted one-time rollback or restore its backup unless separately authorized.

A post-rollback service healthcheck failure does not imply the DB rollback failed. Report transaction result and service result separately. Capture interpreter, CWD, environment, stdout/stderr, traceback, and exit code. Reproduce service startup only against an isolated copy until the correct runtime is known.

## 3. Health checks must touch the database

`Application startup complete` and a shallow `/health` response can pass while the configured DB is missing, inaccessible, or incompatible.

A corrected probe must:

1. create/verify the isolated DB **before** service startup;
2. use the same interpreter/venv intended for deployment;
3. bind loopback on a free temporary port;
4. call `/health`;
5. call at least one existing DB-backed endpoint;
6. verify the isolated DB read-only for schema and integrity;
7. terminate the process cleanly;
8. re-verify production read-only afterward.

## 4. Final-suite interpretation

Run the full suite only after the guard and product candidate stabilize. If the full run exposes a narrow test-harness incompatibility caused by the new isolation contract:

- diagnose the precise path precedence;
- fix only affected tests by making their DB explicit;
- rerun only those invalidated tests when the release contract says post-review fixes use affected gates;
- report the original full-suite result and focused rerun separately. Never rewrite `656 passed, 3 failed` plus `3 passed` as a single uninterrupted `659 passed` run.

Repository-wide lint is not automatically a gate when the repository has frozen legacy violations. Run lint on every semantically changed/new file and state the distinction honestly.

## 5. Copy-first deployment gate

Deploy only the SHA verified identical at local `HEAD`, tracking ref, and remote ref.

### Runtime source-of-truth preflight

Before touching SQLite, resolve the production database and working directories from the actual supervisor/service process and unit metadata, not from the agent terminal's persistent exported environment. Long sessions commonly retain UAT variables, and shell-sourcing a systemd `EnvironmentFile` is not guaranteed to reproduce systemd parsing or unit-level overrides.

- Compare the service process's configured DB path, service `WorkingDirectory`, interpreter, and listener with the intended deployment values without printing secrets.
- Prefer the application's configured connection helper for migration/verification. If a standalone probe is unavoidable, reproduce its `row_factory`, pragmas, adapters, and transaction behavior explicitly.
- Run frontend package-manager/build commands from the actual frontend directory containing `package.json`; repository-root execution is not deployment evidence.
- If a guarded deployment command fails after checkout, stop, inspect the live service state, deployed SHA, schema version, and build artifact separately. A recovery trap may have restarted services with a new source tree and an old frontend build; do not assume the pre-failure version is still live.

Before production migration:

1. ensure the deployment worktree is clean;
2. verify a clean rollback-code worktree/revision;
3. prove there are no production DB writers/open handles;
4. create a fresh consistent SQLite backup;
5. verify backup schema, integrity, and checksum;
6. copy that exact backup to at least one disposable probe DB, and use a second independent copy when the contract requires restore/migration corroboration;
7. migrate the probe with the deployment interpreter, exact merged code, and the project's configured connection helper. Do not substitute a bare `sqlite3.connect` if migration code relies on `sqlite3.Row`, pragmas, adapters, or other connection setup—the harness can fail while the migration itself is sound;
8. apply the migration a second time and require the same migration head with no additional schema/data effects;
9. compare pre/post business digests over the original columns, with exclusions fixed before seeing the result;
10. verify new ingestion/audit tables are empty and integrity is `ok`;
11. write a rollback-readiness record that distinguishes code rollback from DB restore.

Then migrate production once and immediately verify schema, integrity, business digest, and zero automatic ingestion/backfill.

### Stable pre/post business digest

Freeze the digest contract **before** migration. Enumerate all pre-existing user tables except migration metadata and SQLite internals, record their original column lists and row counts, and hash only those original columns after migration. This lets additive tables/columns exist without masking a mutation to pre-existing business data.

For tables without a stable universal ordering key, hash each type-tagged encoded row, sort the row hashes, then hash the table name, frozen column list, count and sorted-row digest. Encode `NULL`, text, integers, floats and BLOBs distinctly so representations cannot collide. Keep the table/column manifest owner-only; report only aggregate row counts and digests. Require the same baseline digest and counts on the source before backup, restored backup, first probe migration, second idempotency migration and production immediately after migration. Separately require every new ingestion/import/audit table to remain empty before the authorized live operation.

Do not use the earlier incident rollback script as the normal deployment rollback. For an additive backward-compatible schema, prefer code rollback while leaving new empty objects inert. Restore a DB backup only for a real data rollback and with separate authorization.

## 6. Private Tailscale topology

For a private UI that must keep write operations localhost-gated:

- bind the backend only to `127.0.0.1` with `local_only` write mode;
- bind the frontend only to the host's Tailscale IP, not `0.0.0.0`;
- proxy same-origin `/api` requests from the frontend server to the loopback backend;
- allow the Tailscale DNS/IP in the frontend host allowlist;
- smoke both DNS and IP routes when policy permits.

This lets a remote Tailscale browser use explicit Preview/Confirm while the backend sees only the loopback proxy connection. Verify the real topology with listening sockets and process environments, not assumed scripts.

Smoke without creating production ingestion:

- frontend `/` and the deep link return 200;
- proxied API health returns 200;
- valid read-only preview returns 200;
- intentionally invalid confirm reaches validation (for example 422) but cannot write;
- ingestion batch/item counts remain zero;
- browser page loads the new panel with no console/page errors.

Before a live browser matrix, inspect whether page bootstrap or refresh timers invoke provider/update routes. If they do, temporarily run the browser UAT with write mode disabled, navigate the automation page away after evidence so timers stop, then restore the approved production write mode and re-verify health. A visual test must not silently become a provider refresh or market-data mutation.

For logout/reboot persistence on a Linux user-owned deployment, prefer enabled `systemd --user` units over session-scoped background processes. Verify `loginctl ... Linger=yes`, both units `enabled` and `active`, exact working directories/commit, and actual listeners. User services do not reliably inherit the interactive shell `PATH`; use absolute interpreter paths or declare an explicit minimal `PATH` for Node/Vite. Restart once through systemd and repeat health/listener checks.

## 7. Evidence after service reads

Physical SQLite file SHA-256 can change after WAL/checkpoint activity without logical data changes. Prefer schema, integrity, row counts, and canonical logical digests for post-start evidence. Define digest semantics **before** UAT: retain a strict all-column digest and a material financial digest whose only exclusions are explicitly named operational fields (for example refresh timestamps or alert counters). Never invent exclusions after observing a delta. Compare both, and require unchanged row counts plus empty ingestion/audit targets.

Some legacy GET/bootstrap paths may refresh operational metadata. If the broad business digest changes after browser UAT:

1. compare table-level digests;
2. identify changed columns without printing sensitive records;
3. distinguish harmless operational refreshes (timestamps, alert occurrence counters) from financial values, classifications, or ingestion records;
4. report the distinction explicitly.

Any financial-value, classification, canonical-ledger, or ingestion mutation remains a deployment blocker unless it was explicitly authorized.

## 8. Additive sensitive-event history migrations

Use these extra gates when expanding a legacy health, medication, or similarly sensitive event table without reinterpreting old records.

### Legacy preservation and constraint relaxation

- Freeze a typed logical digest over every original column before migration. Preserve explicit IDs, raw values, timestamps, source strings, notes, and row counts; new fields on legacy rows remain `NULL`/unknown.
- Do not parse free-text dose/value fields or infer statuses during migration. Structured validation applies only to newly revision-marked rows.
- A legacy tuple-wide `UNIQUE` constraint may be too coarse for multiple legitimate same-day events. SQLite cannot drop its automatic index directly. If relaxation is required, rebuild the **same table only inside the private candidate copy**, copy every original column and explicit ID, then compare the frozen typed digest before proceeding. Inventory existing indexes/triggers first so unrelated objects are not lost.
- Re-run schema application on the candidate. If trigger definitions evolve while a candidate already exists, explicitly `DROP TRIGGER IF EXISTS` and recreate the named trigger; `CREATE ... IF NOT EXISTS` is not an upgrade mechanism.

### Append-only correction and plan semantics

- Model corrections as new rows pointing to the latest event. A unique partial index on `corrects_event_id` permits a linear correction chain while preventing siblings. Reject missing targets, self-relations, cross-entity targets, and targets that already have a child.
- Make any structured row, and any legacy row once referenced by a correction, immutable against update/delete. This keeps append-only history enforceable even when the origin predates the structured schema.
- Permit exactly one effective consumption of a planned event with a partial unique index over `planned_event_id` for administered/missed structured rows. Include current consumption state in Preview binding and recheck it in the worker transaction.
- Validate correction payloads by effective target status: administered requires explicit actual value/unit; planned requires explicit planned value/unit and no actual value; missed/unknown carries neither. Render planned, actual, and uninterpreted legacy free text as three distinct concepts.
- Derived “next plan” readers use the controlled plan date (explicit schedule as override, otherwise structured occurrence/event date) and exclude plans already consumed by administered/missed children.

### Opaque identity and Preview → Confirm binding

- Never expose raw internal revisions or deterministic unkeyed hashes of rows containing numeric IDs. An attacker can enumerate candidate IDs when the remaining fields are known.
- Store a random private 32-byte database identity key and generate domain-separated HMAC references/tokens for public prescription/event IDs, public context revisions, and the complete normalized Preview payload plus current database context.
- A read-only authenticated server may issue the Preview token; the worker must independently recompute it from the queued normalized payload and current transaction state. Bind the idempotency key too, so a token cannot be replayed under a second action identity. Keep the durable replay boundary in the existing action log.
- Do not persist stable sensitive opaque references in `location.search` or browser history state. Keep selection ephemeral in memory and send it only in the authenticated API request.
- Treat malformed nonempty filters as `400`, never as omitted filters. Sanitizers that return `None` must not accidentally turn a rejected privacy filter into an unfiltered PHI response.

### Trusted status and report projection

- An affirmative active/current status requires the complete structured trust contract: controlled status, source, provenance, and immutable revision. If any part is absent, expose `unknown` and offer no current-plan action.
- Default report projections are dedicated allowlists, not copies of the rich record payload. Exclude notes, lot/injection details, provenance, opaque IDs, and revision tokens unless separately opted in.
- Preserve non-private correction truth in reports: mark superseded originals/intermediate corrections and the latest effective correction. Never present every historical row as simultaneously current.

### Required focused probes

Add counterexamples for: two same-day structured events; duplicate plan consumption under distinct idempotency keys; payload mutation after Preview; prescription/context change after Preview; linear multi-step correction; immutable referenced legacy origin; malformed URL/path/source filters; brute-force ID enumeration against public tokens; missing status provenance; correction dose rules for every target status; consumed and schedule-null next plans; minimal report privacy; private-root `0700` plus artifact `0600`; pairwise-distinct/symlink-aliased paths; corrupt-source cleanup; two migration applications; restore to a separate inode; old-reader parity; `integrity_check=ok`; and zero foreign-key errors.