Production pre-wipe backup — 2026-09-17

Provenance record for the full production capture taken before the planned wipe and rebuild. This file is the thing that makes the dump re-importable later. A pg_dump on its own is bytes; what turns it back into data is knowing which migration chain produced its shape, and that answer is a git SHA that is easy to lose and impossible to guess.

Written while the tunnel was open and every number below was read from the live database, not inferred from the repo.

What was captured

Layer	Artifact	Covers	Restorable against
1 — instance snapshot	prewipe-20260917-1106	Both databases, instance-wide	Old schema, fastest rollback
2 — logical dump	s3://restartix-backups-primary-production/daily/2026-09-17-core-full.pgdump.gz.enc	restartix (core)	Old schema, any Postgres ≥ 17
2 — logical dump	…/daily/2026-09-17-telemetry-full.pgdump.gz.enc	telemetry	Old schema, any Postgres ≥ 17

The bucket has Object Lock COMPLIANCE, 7 years — these objects cannot be deleted by anyone, including account root. The on-demand run wrote a new version of each key rather than replacing the 02:01 daily; both versions survive.

Verified by read-back checksum at capture time, not merely by exit code:

• core — sha256=6526f309ca5885885cc966cd256b3b3447a45393e832946b0c9d8a566fb4aa41, 10,271,360 bytes
• telemetry — sha256=637ed3c775e59b24b9347fdea0b80ef72993976d2bf5e41ed976c8bace4efb3b, 2,936,336 bytes

The provenance that matters

Core schema version	39, not dirty
Telemetry schema version	2
Chain that produces this shape	origin/master @ 7265d277
Instance	restartix-production-postgres, Postgres 17.9, Multi-AZ
Core database size	105 MB

7265d277 is load-bearing. Since that commit, 22 migrations inside the applied block 000001–000039 were edited in place on the staging chain, so the current tree produces a different schema at the same version number. Rebuilding the source shape means checking out that SHA — nothing else reconstructs it.

Restoring

Inside AWS, always — the artifact is a complete copy of patient data, and restoring it on a workstation is a genuine egress event.

aws s3 cp s3://restartix-backups-primary-production/daily/2026-09-17-core-full.pgdump.gz.enc .
sha256sum 2026-09-17-core-full.pgdump.gz.enc        # must match above

# -pbkdf2 -iter 100000 is mandatory; without it openssl falls back to its
# legacy MD5 KDF and fails with `bad decrypt`.
openssl enc -d -aes-256-cbc -pbkdf2 -iter 100000 \
  -pass env:BACKUP_ENCRYPTION_KEY \
  -in 2026-09-17-core-full.pgdump.gz.enc -out backup.pgdump.gz

gunzip backup.pgdump.gz
createdb restartix && pg_restore -d restartix -v backup.pgdump

cmd/restore-drill does this end-to-end on Fargate against a scratch database and compares structure to the live source. Until it has run against this artifact, "we have a backup" is a claim rather than a fact.

Reading the data back in later

Two classes of UUID, and only one of them needs work.

Class 1 — regenerated by a wipe. Rows a migration seeds: exercises, the vocabulary catalogs, tiers, permissions. 000031_exercises_seed inserts exercises without an id column, so the gen_random_uuid() default mints new ids every rebuild. Each row carries a stable natural key, so they stay matchable — exercises.slug, purpose code, and so on.

Class 2 — never regenerated. Rows only the application ever created: patient_profiles, patients, programs, sessions, session_exercises, protocols, session_runs. Nothing re-creates these, so they can be re-inserted with the UUIDs they have in the dump. No collision, no remap.

So a re-import is: restore the dump, build old_exercise_id → slug → new_exercise_id, and rewrite only the FK columns pointing into class 1 — in practice session_exercises.exercise_id and exercise_renders.exercise_id.

consents needs no remapping at all: it references purposes by purpose_code TEXT + purpose_version INTEGER, never by a regenerated uuid. The 8,219 consent rows are portable across any number of rebuilds, which matters because they are the GDPR Art. 7 proof-of-consent record under 6-year retention.

The genuinely hard part is not the ids — it is the schema delta described above. Write that ETL against a restored scratch database with real rows in front of you, not against a schema nobody can query anymore.

Two things the rebuild does not bring back

• exercise_renders + content_files — 48 rows each, all ready.000031_exercises_seed seeds exercises and exercise_tags and neither of these. The media bytes survive in S3/Bunny; only the database pointers die. Re-baking is not an available fix: restartix-production-media sits at desired 0 / running 0 and CPU target-tracking cannot scale it from zero. Re-insert these from the dump, through the class-1 remap above.
• Superadmin. DROP SCHEMA takes platform_memberships with it and no migration re-creates the grant. Sign in to Console once so first-sight provisioning writes the humans row, then grant through the tunnel under SET ROLE restartix.

Census at capture

Read from pg_stat_user_tables; partitioned tables appear per partition, which is what a restore verification should compare against.

Table	Live rows
session_exercises	39821
session_exercise_events_2026_06	21335
audit_log_2026_06	19733
session_exercise_events_2026_07	12961
consents	8219
session_exercise_events_2026_08	7546
audit_log_2026_07	6788
audit_log_2026_08	3539
session_exercise_events_2026_09	3111
sessions	2491
programs	2491
protocols	2490
session_runs	2483
principals	1843
humans	1842
patient_profiles	1628
patients	1628
audit_log_2026_09	1569
pose_landmarks	543
exercise_tags	185
role_permissions	182
session_pairings	65
permissions	54
inbound_webhook_dedup_2026_06	48
session_pain_events_2026_06	48
exercise_renders	48
content_files	48
session_pain_events_2026_07	25
consent_purpose_versions	20
exercise_conditions	18
exercise_body_regions	17
exercise_equipment	16
exercises	16
tier_limits	16
session_pain_events_2026_08	15
exercise_movement_patterns	12
consent_purposes	10
exercise_skill_prerequisites	10
organization_subscription_limits	10
exercise_categories	8
legal_document_templates	8
session_tv_liveness_2026_09	8
entitlements	8
roles	6
tier_entitlements	6
limit_definitions	6
exercise_recovery_phases	5
organization_subscription_entitlements	3
organization_memberships	3
tiers	3
tier_versions	3
session_pain_events_2026_09	3
platform_service_providers	3
organization_legal_documents	3
notification_idempotency_keys	2
organization_invites	2
notifications_2026_06	2
notification_deliveries_2026_06	2
organization_subscriptions	2
integration_services	2
organization_domains	1
platform_memberships	1
organization_entitlements	1
pose_engines	1
patient_tier_entitlements	1
break_glass_sessions	1
patient_tier_versions	1
organization_settings	1
organization_billing	1
patient_tiers	1
organizations	1
schema_migrations	1