RestartiX Platform

English

Română

English

Română

Appearance

Production reset — what it would cost ​

Status: SCOPING ONLY — and the decision went the other way. NO RESET. Kept because the analysis is what a future "can we just wipe it?" needs, and because §4's backup findings are load-bearing regardless.

Outcome (2026-08-17). No reset. The fold was achieved without one, by folding 000051–000055 into 000043/000044 — migrations production has never applied — rather than into 000008/000009, which it has. §1's benefits were obtained in full except retiring the pre-000039 catch-up scripts, which stay. See consents-and-agreements.md.

Nothing in this document has been done. It was written 2026-08-17 because tidying the 000050–000055 migrations kept running into the same wall, and the wall looked like a decision about production rather than about migrations.

The question that produced it: why can't 000051 and 000053 fold into 000008/000009? The mechanical answer is that production's schema_migrations says 39, so golang-migrate will never re-run 000008, so an edit there reaches every fresh database and never reaches production — the consent_purposes.enforcement column would not arrive late, it would never arrive, and every consent gate would 500 on it after deploy. The only world where the fold is genuinely clean is one where production is wiped and re-migrated from 000001.

So this document scopes that, and it should be decided on its own terms. If a production reset is right, the migration cleanup is a consequence of it. If it is wrong, no amount of migration tidiness makes it right.

The premise turned out to be false, which is the most useful thing here.000008/000009 were never the only possible destination. Anything in 000040–000050 is equally "behind" the change and production has applied none of it, so the fold landed there instead and cost nothing but a staging rebuild. Before scoping a reset to enable a refactor, check whether an unapplied migration can host it.

1. What the reset actually buys ​

Stated first and plainly, because it is smaller than it feels:

  • 000051 and 000053 fold into 000008/000009. 000053's five DELETE statements vanish entirely, because org_service_disclaimer would never have been seeded.
  • The catch-up scripts targeting migrations at or below 000039 stop mattering — 16 files in infra/scripts/, of which the promotion runbook currently sequences five in a specific order, two of which fail silently (empty Family panel; empty per-person reads).
  • The 000040–000055 range becomes contiguous and the promotion becomes "run the migrations", with no ⛔ blocks and no ordering table.
  • Every database in existence would be built by the same files, so "what shape is production actually in" stops being a question that needs a catalog diff to answer.

It unblocks no feature. No patient or clinic sees any difference. The entire benefit is that the schema history reads as designed rather than as patched, and that a class of silent promotion failure disappears. That is a real engineering good and it is worth something — but it is being weighed against live clinical records, so the asymmetry has to be stated rather than felt.

2. What is in production today ​

Production runs schema 000039 and has served real patients continuously since 2026-06-05 — 73 days as of writing. This is asserted independently in foundation.md, june-demo.md and CLAUDE.md. The "production holds a demo tenant only" line in consents-and-agreements.md is a narrower claim about consent-catalog rows and should not be read as a description of the whole database.

Because 000040+ has never been applied there, production has nospecialists, offerings, forms, appointments, calendars, documents or pdf_templates. Nothing from F1–F6 is at risk, because nothing from F1–F6 is there. What is at risk is the pre-F-tier platform:

ClassTablesWhy it matters
Clinical historysession_runs, session_pain_events, session_exercise_eventsThe actual record of what patients did. Irreplaceable — nobody can re-do a session from June.
Prescriptionsprotocols, programs, sessions, program_phases, protocol_pausesPatient-instance copies under three-tier copy-on-derive. A reset detaches every active prescription.
Consent ledgerconsents, consent_purpose_versionsThe evidence that processing was lawful, under 6-year retention. See §5.
Auditaudit_log (+ partitions), audit_ai_provenance, break_glass_sessionsAppend-only by policy, 6-year retention, break-glass "never deleted". See §5.
Identityprincipals, humans, patient_profiles, patients, patient_caregiversThe join between a Clerk account and a person. See §3 — this is the one that bites.
Moneypatient_subscriptions, patient_content_grants, organization_subscriptions, access_offer_orders, access_offer_fulfillmentsPaid access. A reset revokes content people bought.
Comms forensicsnotifications, notification_deliveries, notification_idempotency_keysThe stored row is the forensic record — notify writes no separate audit row. Deleting it deletes the proof of what was sent.
Telemetryseparate database: media_session_metrics, media_buffering_events, media_library_viewsOwn migrations, own reset decision. QoS history only — the least costly to lose.

3. What is NOT in Postgres, and gets orphaned ​

This is the part that makes a reset more than a DROP SCHEMA, and Clerk is the one that actually hurts.

  • Clerk. humans.provider_subject_id is the link to a Clerk user id. Wiping Postgres does not wipe Clerk: every existing account still authenticates successfully and then resolves to no principal. A returning patient gets a valid session against a platform that has never heard of them. There are only two honest ways out — wipe the Clerk production application too (which also destroys their credentials, so every user re-registers), or make first-login re-provision a principal, which is new code written specifically to paper over the reset.
  • The legacy → handoff bridge. Favourable, unusually. The bridge is idempotent and its "link consumed" marker lives in Redis (SET NX), not Postgres — so a reset plus a Redis flush makes every legacy hand-off link claimable again. This is the least-bad path for returning patients and it exists already.
  • Redis must be flushed with the reset, not after it. It holds auth sessions, booking holds, rate-limit counters, JTI reservations and caregiver grant codes, all keyed on ids that would no longer exist. It runs noeviction, so stale keys do not age out on their own.
  • S3 and Bunny Stream. Uploads (avatars, signatures, exercise assets) and video are referenced from the database, never the reverse. Nothing is lost; everything becomes unreferenced, and the storage bill continues. Re-associating them means a manual pass or accepting orphans.

4. Reversibility — the strongest fact in the file ​

A reset would not destroy the record, provided the dumps are intact.

Since 2026-08-02 the daily backup cron writes a full logical pg_dump of both the core and telemetry databases into an S3 bucket configured with Object Lock in COMPLIANCE mode, 7-year retention (storage-backups/main.tf), encrypted under its own KMS envelope key, with prevent_destroy on the bucket. COMPLIANCE mode means those objects cannot be deleted or overwritten inside the window even by the account root.

And they demonstrably restore. The first drill failed — which is what produced migration 000039 — and the drill has run green since (cmd/restore-drill + infra/scripts/restore-drill.sh). Production can restore from its own logical backup; that was verified, not assumed.

Two caveats that decide how the reset would have to be sequenced:

  • RDS automated backups retain 7 days (backup_retention_period = 7 in envs/production/main.tf). Point-in-time recovery reaches back one week and no further. The nightly dumps are what cover June and July — and they cover it fully, because a pg_dump is a complete copy, not an increment.
  • Take a manual snapshot immediately before, and verify a restore of the most recent dump into a scratch database before dropping anything. The drill exists precisely so this is a rehearsed step rather than a hopeful one.

5. The compliance read ​

Not a blocker, but it must be a decision rather than a side effect.

  • audit_log is append-only with a 6-year retention policy, and break-glass entries are in the never-deleted class. Deliberately dropping it is not the same act as a retention purge. It stays recoverable in the Object-Locked dumps for 7 years, which is the answer — but the answer has to be recorded, because "we can restore it" and "we retained it" are only the same statement if somebody wrote down where it went.
  • The consent ledger is the lawfulness evidence. If a patient is still being processed after the reset, the platform must be able to show consent was given. Restorable-from-backup is a defensible position; silently gone is not.
  • Object Lock COMPLIANCE cuts both ways. Those dumps cannot be edited or deleted for 7 years, so a GDPR erasure request cannot reach them. That is already true today and is not caused by the reset, but a reset makes the backups the only copy of the erased person's record, which sharpens it.
  • Clinic notification. The clinic is the data controller. Destroying and restoring their patients' records is a processing event they are entitled to know about, whatever the outcome.

6. The counts that should decide this ​

Everything above is structural. The actual decision is a volume question, and nobody has looked. There are no production credentials on this machine, so the custodian runs this; it is read-only.

sql
-- Production, read-only. Answers "how much real history exists".
SELECT 'orgs'              AS what, count(*) FROM organizations WHERE deleted_at IS NULL
UNION ALL SELECT 'patients (live)',       count(*) FROM patients WHERE deleted_at IS NULL
UNION ALL SELECT 'patient_profiles',      count(*) FROM patient_profiles
UNION ALL SELECT 'humans w/ Clerk id',    count(*) FROM humans WHERE provider_subject_id IS NOT NULL
UNION ALL SELECT 'session_runs',          count(*) FROM session_runs
UNION ALL SELECT 'session_runs completed',count(*) FROM session_runs WHERE completed
UNION ALL SELECT 'runs in last 14d',      count(*) FROM session_runs WHERE started_at > now() - interval '14 days'
UNION ALL SELECT 'active protocols',      count(*) FROM protocols WHERE status = 'active'
UNION ALL SELECT 'consents (live)',       count(*) FROM consents WHERE withdrawn_at IS NULL
UNION ALL SELECT 'paid subs (active)',    count(*) FROM patient_subscriptions WHERE status = 'active'
UNION ALL SELECT 'content grants',        count(*) FROM patient_content_grants
UNION ALL SELECT 'audit_log rows',        count(*) FROM audit_log
UNION ALL SELECT 'break_glass sessions',  count(*) FROM break_glass_sessions
UNION ALL SELECT 'notifications sent',    count(*) FROM notifications;

-- The one that matters most: is anyone still USING it?
SELECT date_trunc('week', started_at) AS week, count(*) AS runs,
       count(DISTINCT patient_id) AS patients
  FROM session_runs
 WHERE started_at > now() - interval '10 weeks'
 GROUP BY 1 ORDER BY 1;

How to read it. If the second query shows runs still happening this week from patients who are not the team, production is a live clinical service and the reset is off the table — it would be taking care away from people to tidy a file layout. If it flatlined after the June demo and the live-patient count is a handful of known accounts, the reset is a reasonable pre-launch housekeeping act, and the only real work left is the Clerk orphaning in §3.

7. The cheaper option that gets most of the benefit ​

Worth pricing before deciding, because it costs nothing and needs no permission:

Consolidate the catch-up scripts into one idempotent script. The promotion's actual pain is not that catch-up scripts exist — it is that five of them must run in a specific order and two fail silently. A single guarded, idempotent script that applies all pre-000039 corrections and verifies each one afterwards removes the ordering trap and both silent failures, without touching a row of patient data.

What it does not buy is the fold: 000051 and 000053 stay as forward migrations. But the fold was always the cosmetic half of this. The ordering trap was the dangerous half, and it can be fixed on its own.

8. What is needed to move ​

  1. Run the queries in §6 and paste the results here. Nothing else should be decided before that.
  2. Then one of:
    • No reset — take §7 instead, delete 000054, keep 000050–000055.
    • Reset — decided explicitly, in its own words, not inferred from a remark about migrations. It needs a written sequence covering the manual snapshot, the verified restore rehearsal, the Clerk decision from §3, the Redis flush, the telemetry database, and the clinic notification.

The reset itself is governed by the standing rule that production is never destroyed without in-the-moment confirmation, and by the rule that promotion to production is the custodian's alone. Neither is waived by this document existing.

9. Related ​