RestartiX Platform

English

Română

English

Română

Appearance

Rebuilding staging from scratch ​

Wipe both databases, re-migrate to the current schema, and bring staging back up on the current infrastructure. Written 2026-08-11 for the rebuild that takes staging from 000038 to 000049, but the shape is general — the ordering constraints below are properties of the pipeline, not of this particular rebuild.

There is no production equivalent and there should not be. Production has real patient data; the destructive step here (reset-staging-db.sh) refuses to run unless ENV=staging.

Everything in this document was verified against the live environment rather than inferred from the repository. Where a claim came from a measurement, it says so.

Why the order is what it is ​

Three constraints decide the sequence. None is obvious, and each was found by something failing rather than by reading the code.

Terraform applies before the app deploy. The deploy action fetches describe-task-definition --task-definition <family>, which resolves to the latest ACTIVE revision — so it clones whatever Terraform last registered. Apply first and new env vars reach the service on the next deploy. Deploy first and the service misses them for a whole cycle, because ignore_changes = [task_definition] stops Terraform moving the service itself.

Secrets are populated before any task definition names them. Terraform creates Secrets Manager containers empty, with zero versions, and ECS secret injection is fail-closed: a task launched from a revision naming a versionless secret dies at ResourceInitializationError. The service survives that — it stays pinned by ignore_changes — but the crons do not, because they follow the family's latest revision by design. They stop launching within minutes, and silently.

The database wipe comes last. reset-staging-db.sh runs the migrations runner, which pulls :latest at RunTask time. Reset before deploying and the database is migrated by the old image.

The short form: infra, then apps, then the database.

That short form is specific to a wipe, and inverts where there is data to keep. It works here only because phase 4 destroys the schema, so nothing is running against a half-migrated database. An environment with rows cannot deploy apps before migrating: the new code expects the new schema. Concretely, orgsettings/repository.go selects late_cancellation_hours and noshow_grace_minutes, both added by 000046, so any environment still below that migration answers every organization-settings read with column … does not exist. There the order is infra → push the image → migrate → deploy apps, with the push separated from the deploy precisely so the migrations runner can pull an image whose migrations have not yet reached the running services. Do not carry this document's ordering to an environment that has data.

Before you start ​

  • The app code is already on the staging branch. Pushing deploys nothing — deploy-staging.yml is workflow_dispatch only, with no push trigger. This is deliberate; do not add one.
  • Know your migration targets. Core 000049, telemetry 000002.
  • Have a second terminal free for the SSM tunnel in phase 4.

infra/scripts/*.sql — three kinds, and only one is a no-op ​

The directory mixes three things. "Don't run the scripts" is right for the first kind and wrong for the other two.

Catch-ups — skip them. 000003-grandfather-org-entitlements.sql, 000004-entitlement-cleanup.sql, 000004-entitlement-scope.sql, 000004-limit-doctrine.sql, 000004-tiers-is-public.sql, 000043-document-categories.sql, 000006-sex-codes.sql, 000006-caregiver-staff-visibility.sql, 000012-invite-claims-profile.sql and friends exist only for databases that already passed a migration that was later edited in place. A freshly-reset database runs the corrected migrations and reaches the same end state; every statement is a no-op at best.

000043-document-categories.sql states it in its own header: staging "has never applied 000043, so it will build the new schema on its first run and must NOT have this script applied."

The four newest catch-ups are the ones this rebuild will tempt you with, because their numbers are the numbers you are migrating to: 000046-appointment-file-kind.sql, 000049-appointment-notes.sql, 000049-video-room-participants.sql and 000049-participant-insert-policy.sql. All four are LOCAL ONLY — each says so in its own header — and all four correct in-place edits to 000046 / 000049, which staging has never run. The corrected migrations carry every one of them: appointment_files.kind is in 000046, and appointment_notes, video_room_participants and the REVOKE UPDATE, DELETE, TRUNCATE that leaves the app role its INSERT are all in 000049.

Caregiver grant codes need nothing here. The short-lived, single-use codes a patient reads out to give somebody access to their record live in Redis (caregiver_grant:, 15-minute TTL), not the database — so there is no migration, no catch-up script and no new secret for that feature in any environment. A rebuild wipes them along with everything else in Redis, which costs nothing: an unredeemed code is reissued in one tap. The one thing that matters is maxmemory-policy = noeviction on the parameter group, already set because Redis holds auth sessions; under an eviction policy a code could vanish before its TTL and the redeemer would be told it was invalid while the issuer saw no problem.

APPLIED 2026-08-14 — this paragraph is now history, kept because a rebuild makes it live again. Both scripts below were run against staging and verified (patient_caregivers carries both policies; organization_invites has patient_profile_id, its CHECK, and org_invites_select_claim_subject). A REBUILD wipes that and re-migrates from the corrected migrations, which carry all of it — so after a rebuild, skip them like every other catch-up here.

The situation they were needed for: staging as it stood was not a fresh database, and two catch-ups applied to it. 000006_patient_identity.up.sql gained patient_caregivers_select_org_staff — the RLS policy letting clinic staff read a caregiver link when both people are already their patients (P7 family accounts). Staging is at 000049 and passed version 6 long before that edit, so golang-migrate will never re-run it. Until staging is rebuilt again, run 000006-caregiver-staff-visibility.sql there once:

bash
psql "$STAGING_DIRECT_URL" -f infra/scripts/000006-caregiver-staff-visibility.sql

The same applies to 000012-invite-claims-profile.sql, which adds organization_invites.patient_profile_id for the P7 profile claim — with one difference worth knowing: forgetting THAT one fails loudly (the invite INSERT names a missing column) rather than silently.

Both are idempotent, and the 000006 one grants SELECT only — 000006's REVOKE on the write verbs stands. Rebuild and the opposite applies: the corrected 000006 carries the policy, so the script becomes a no-op like the rest of this list. The symptom of forgetting it is quiet — the Family panel on a patient record renders nothing and the API returns an empty list rather than an error, so it reads as "this patient has no relatives on file".

The same logic covers the tier and entitlement catalogue, which lives in migrations 000004 and 000033. There is no seed binary and no seed step — the migrations carry it.

⛔ HOTFIX-restore-treatment-plans-column.sql — never, on a fresh database. This one is not a catch-up and not a no-op. It is an emergency rollback, written when dropping organization_entitlements.treatment_plans_enabled on production ahead of the deploy took the platform down. It adds a column the migrations no longer create and re-registers current_app_has_org_entitlement accepting a code that no longer exists. Run it here and staging carries a schema no migration can produce — self-consistent, silently different, and invisible until something disagrees with it.

Operational tools — fine, and sometimes wanted. move-org-to-tier.sql and 000004b-drop-treatment-plans-column.sql are not catch-ups. The first puts an org on a base tier with correct snapshots, which is exactly what you want after a rebuild leaves a test org on Free. The second is the deferred half of a removal and only applies to a database that still has the column — a fresh one does not.

Phase 1 — secret containers, then values ​

Two targeted operations, because a single full apply would register a task definition naming a secret that has no version yet.

bash
cd infra/scripts
./staging-apply.sh plan \
  -target=module.network \
  -target=aws_secretsmanager_secret.runtime \
  -target=aws_secretsmanager_secret.video_room_secret \
  -target=aws_secretsmanager_secret_version.video_room_secret \
  -target=random_id.video_room_secret

Review, then re-run with apply.

module.network is required in that target set, and its absence is not a subtle failure. Terraform refuses any target set that splits a pending moved, with Error: Moved resource instances excluded by targeting. Staging carries such a move — the private route table went per-AZ in the network module — so a secrets-only target set fails outright. The moves an environment carries are whatever has drifted since its last apply, so read the plan rather than trusting this list.

Then populate:

bash
./populate-staging-secrets.sh --only daily-bootstrap

A blank DAILY_API_KEY is a valid answer — it disables video, and the script still writes the pair so the ARN resolves. An absent version is what breaks the environment. Note that an empty pair still counts as populated, so supplying a real key later needs --force --only daily-bootstrap.

The pair is written once and is canonical from then on. The script generates DAILY_WEBHOOK_SECRET itself whenever you supply a key (and writes both blank when you don't), so it cannot leave you half a pair. What it can leave you is a second pair: providers.Bootstrap inserts ON CONFLICT … DO NOTHING, and the api gates it on a non-empty DAILY_API_KEY, so the first boot that sees a key writes the provider row and the webhook secret beside it permanently — no later redeploy corrects it. Re-run --force --only daily-bootstrap against an environment whose row already exists and Secrets Manager rotates while the row does not; register with the new value and every delivery then fails verification, silently, recoverable only by a Console credential rotation. In this rebuild that cannot bite — phase 4 destroys the row, so the next boot re-seeds it from whatever phase 1 wrote. It bites when the script is re-run later, on its own.

The other three bootstrap secrets (clerk-bootstrap, email-bootstrap, storage-bootstrap) are already populated. They matter more than usual here: DROP SCHEMA in phase 4 wipes platform_service_providers, and the Cat A bootstrap re-seeds it from exactly these on the next boot.

Phase 2 — the full apply ​

bash
./staging-apply.sh plan     # read it
./staging-apply.sh apply

What it does: creates the small cron task definition, registers an api revision carrying the video env vars, repairs the broken schedules and adds the new ones, creates the scheduler DLQ, and restructures the NAT routes. Measured 2026-08-12: 8 to add, 17 to change, 1 to destroy — the destroy is the api task definition being replaced (revision 7 → 8), not a resource loss.

On 2026-08-12 this apply ended red on cloudflare_managed_headers.restartix_pro, and the resource no longer exists — visitor location headers are now dashboard-managed, with the reasoning in infra/envs/staging/cloudflare.tf. In short: the endpoint refuses both Terraform tokens no matter what permissions are added, the create's write half had already landed, and a tainted resource Terraform can read back would have destroyed and re-created a zone-wide setting that production hosts share. If a future apply here surprises you with it, check the dashboard toggle before believing the plan.

It also scales all seven services back to desired_count = 1 — Terraform wants 1 and a parked staging sits at 0. Expect staging to come up during this phase, and Aurora to wake with it.

A service may fail to come up here with CannotPullContainerError: … not found, and that is expected — phase 3 fixes it. Scaling from zero is a task launch, and ignore_changes = [task_definition] means the service launches from whatever immutable :<sha> CI last pinned. Staging's "keep last 20 tagged" lifecycle expires images on push count, so a service that has not been deployed in 20 pushes holds a pin whose image is gone. Nothing reveals this until something replaces the task, which is exactly what this phase does — it is how staging api broke on 2026-08-10. Phase 3 repairs it without intervention: the deploy action clones Terraform's revision and swaps in a freshly built image, which does not require the service to be running. So read a pull failure here as "phase 3 has not run yet", not as a broken environment.

Two more things happen here that read as failures and are not (both observed on the 2026-08-12 run):

api crashes once, on DNS. The first task boots before pgbouncer has registered in Service Connect and dies at hostname resolving error: lookup pgbouncer.staging.local … no such host. ECS restarts it, the second task connects, and the service reaches 1/1 within a minute or two. Only worry if it is still cycling after that.

The new crons fail immediately, in the api's log group. Terraform creates video-room-sweep, video-usage-reconcile, appointment-noshow-sweep and expire-hard-cap-protocols ENABLED, and they fire against the image the service is still running — which predates their binaries, so the task dies at runc create failed: … "/bin/video-room-sweep": no such file or directory. Phase 3 replaces the image and the error stops. Read it as "the deploy has not run yet".

Only NEW schedules come back enabled. The scheduled-tasks module sets state = "ENABLED" at create and then ignore_changes = [state], deliberately, because staging-toggle.sh owns enable/disable. So an apply against a parked staging leaves the pre-existing schedules DISABLED and switches on only the ones it just created — measured 2026-08-12 as 4 enabled, 8 disabled. Do not read a running staging as a fully armed cron fleet, in either direction.

Measured 2026-08-11, pushes remaining before each staging pin expires: portal 3, clinic and console 8, pgbouncer, telemetry and media 10, api 19. Production no longer consumes this window — it moved to its own restartix-production-<svc> repos the same day — so the countdown is now staging's own deploys only, and phase 3 of this runbook spends one of them.

Phase 3 — deploy the apps ​

GitHub Actions → "Deploy to staging" → Run workflow → "Use workflow from": staging → service: all.

There is no push trigger; this is the only way. It must follow phase 2 so the deploy clones Terraform's revision.

The dropdown offers eight services against phase 2's seven, and the extra one is tv: the TV companion is a static site synced to S3 (apps/tv/, no build step, no ECS service), so it has no desired_count to restore and is unaffected by the database wipe. all covers it.

Set "Use workflow from" to staging. Not master. The picker defaults to the repository's default branch, which is master, and that default is wrong for this workflow — restartix-deploy-staging trusts a branch ref:

repo:RestartiX/restartix-platform:ref:refs/heads/staging

Dispatched from master the OIDC subject is refs/heads/master, no role is assumable, and the run dies at the credentials step before touching AWS. Harmless, but it reads as a broken deploy rather than a wrong dropdown.

The two deploy workflows differ here, which is the whole reason this is easy to get wrong:

workflowdispatch fromtrusted subject
Deploy to stagingstagingref:refs/heads/staging
Deploy to productionmasterenvironment:production

Production's role trusts the GitHub Environment rather than a branch ref, so master is right there and wrong here.

Phase 4 — wipe and re-migrate ​

Terminal 2, left running:

bash
./infra/scripts/staging-tunnel.sh          # local port 15432

That is the hand-rolled aws ssm start-session --document-name AWS-StartPortForwardingSessionToRemoteHost against the NAT instance, with both Terraform outputs resolved for you. Pass a port to override the default.

Local port must not be 5432: localhost resolves IPv6 first, and a local Docker Postgres bound to the IPv6 wildcard shadows the tunnel silently — connections succeed and go to the wrong database.

Terminal 1:

bash
ENV=staging CONNECT_HOST=localhost CONNECT_PORT=15432 ./infra/scripts/reset-staging-db.sh

It drops and recreates public in both databases, re-creates the extensions as master (the app role cannot CREATE EXTENSION on Aurora), re-grants the default privileges the drop resets, runs both migration trees, rolls partitions -ahead=3, and force-redeploys the services so providers.Bootstrap re-seeds what DROP SCHEMA wiped.

The extension list in those scripts is a hard dependency of the migrations, and it silently fell behind. 000045 needs btree_gist for the appointments double-booking EXCLUDE constraint and carries its own CREATE EXTENSION IF NOT EXISTS — which is useless on Aurora, because the migrations runner connects as restartix, and that role has no CREATE EXTENSION privilege by design. The 2026-08-12 rebuild was the first run to migrate past 000044 on Aurora, and it died exactly there:

(details: pq: permission denied to create extension "btree_gist")

schema_migrations was left at 45, dirty, with 135 tables from 000001–000044 in place. Both scripts now pre-create it; on a database that already failed this way, create it by hand as master and re-run the reset from clean:

sql
CREATE EXTENSION IF NOT EXISTS "btree_gist";

The rule this establishes: a migration that adds CREATE EXTENSION must add the same extension to bootstrap-db-roles.sh and reset-staging-db.sh in the same PR. It cannot be caught locally — local Postgres runs restartix as a superuser, so every one of these succeeds on a developer machine and fails only on Aurora, part-way through, leaving a dirty version behind.

Redis is not flushed, and one key class notices. The reset script says so in its own header: P45 cache-aside entries survive the wipe, and new orgs get fresh UUIDs so stale keys mostly cannot collide. The exception is the keys that are not UUID-derived — cache.Platform("resolve", "slug", …), which maps a slug to an organization id. Re-create the test org under the same slug it had before the wipe and hostname resolution serves the dead id for up to the 5-minute TTL, which presents as a clinic that resolves to nothing. Every platform-scoped cache is TTL-bounded (5 minutes, an hour for the pose catalogs), so waiting also works; flush if you would rather not wonder.

bootstrap-db-roles.sh is not needed. Roles are cluster-level and survive a schema drop; the reset script re-grants default privileges itself. Running it anyway rotates every role password and requires a pgbouncer redeploy to reload the userlist — cost with no benefit here.

Phase 5 — verify ​

Keep the phase 4 tunnel open — the first two checks are queries against the database through it.

  • schema_migrations reads 49 and is not dirty; telemetry reads 2
  • the catalogue seeded exactly, not merely non-empty — 3 tiers, 8 entitlements, 8 limit definitions (migrations 000004 / 000033). A count catches a seed that half-ran; "non-empty" passes anyway:
    sql
    SELECT (SELECT count(*) FROM tiers) AS tiers,
       (SELECT count(*) FROM entitlements) AS entitlements,
       (SELECT count(*) FROM limit_definitions) AS limits;
  • platform_service_providers has its rows back — if it is empty, a bootstrap secret was missing
  • one cron task on restartix-staging-api-cron reaches exitCode 0
  • the video webhook still verifies — see below

The video webhook usually survives the rebuild, and the exception is this rebuild ​

The registration lives on the provider account, not in our database, so DROP SCHEMA does not touch it. What the wipe destroys is the platform_service_providers row holding the secret we verify deliveries with — and providers.Bootstrap re-seeds that from restartix/staging/daily-bootstrap, the same secret that seeded it the first time. Same value, so the HMAC still matches and there is nothing to redo.

Re-register when the pair changed, which is exactly what happens if phase 1 put a real key into a previously-blank daily-bootstrap, and when the provider has circuit-broken the endpoint into FAILED (no alarm exists for that, in any environment). Then, from a workstation, reading both values back out rather than retyping either:

bash
SECRET=$(aws secretsmanager get-secret-value \
  --secret-id restartix/staging/daily-bootstrap --query SecretString --output text)
export DAILY_API_KEY=$(echo "$SECRET" | jq -r .DAILY_API_KEY)
export DAILY_WEBHOOK_SECRET=$(echo "$SECRET" | jq -r .DAILY_WEBHOOK_SECRET)

go run ./cmd/video-webhook-register -url https://api-staging.restartix.pro/webhooks/daily

It must run after the API has booted with the secret populated, so the provider row exists to verify against. It is idempotent per URL — it updates staging's own webhook rather than adding a second — and it leaves the other webhook on that account alone: there is one provider account today and it is the legacy system's production one, so each environment receives the other's deliveries and discards them as unknown rooms.

Full treatment, including why a mismatched secret fails silently in both directions: video-infrastructure-plan §4.1 and §7.

There is no superadmin until you make one ​

DROP SCHEMA takes platform_memberships with it, and nothing re-creates the grant — not a migration, not providers.Bootstrap, not the Clerk app, whose users are untouched by the wipe and go on signing in perfectly well into a platform where they are nobody. Until the grant is restored there is no Console access, which means no org onboarding, which means the next section has nothing to be about.

Sign in to Console once first: the auth middleware provisions the principals + humans rows on first sight, before authorization, so the request that 403s is the request that creates the row you are about to grant. Then, through the phase 4 tunnel:

bash
PGPASSWORD="$(./infra/scripts/db-creds.sh staging --password)" \
  psql -h localhost -p 15432 -U postgres -d restartix
sql
SET ROLE restartix;
INSERT INTO platform_memberships (principal_id, role)
SELECT principal_id, 'superadmin' FROM humans WHERE email = 'you@restartix.com';

Verified end-to-end on 2026-08-12: the Console sign-in produced exactly one humans row and zero platform_memberships, and the grant below worked as written.

SET ROLE restartix is the load-bearing line. platform_memberships has RLS enabled and no policies at all — deliberately, so the restricted app pool has zero access, and INSERT is additionally revoked from restartix_app. The only way in is the owner's ownership bypass, and restartix is the owner (the table is not FORCEd). The master role can assume it because it created it.

Granting superadmin also deletes every tenant membership that principal held — a trigger, and the documented invariant: platform operators do not simultaneously act as tenant principals. Grant it to the account you administer with, not to the one you plan to test the Clinic app with.

The first org you create will look broken, and is not ​

DROP SCHEMA leaves zero organizations. Whatever you create next — Console onboarding, POST /v1/organizations — gets its organization_entitlements row from the provisioning trigger, which defaults every regulated flag to FALSE. No tier grants one, so nothing turns them on by itself.

Since 2026-08-11 that has visible consequences, because the gates are now mounted:

  • the Clinic app shows no Content group — no exercise library, sessions, programs or catalog
  • the API answers 403 org_entitlement_disabled on that whole stack and on prescribing
  • video join answers 403 on both the staff and patient routes

This is fail-closed working as designed, not a broken rebuild. Turn on what the test org needs in Console → clinic → Plan → Entitlements, or run 000003-grandfather-org-entitlements.sql, which exists for exactly this and is the one catch-up worth running here — on a rebuild it is not correcting an edited migration, it is standing in for the onboarding step that has not been built yet.

What a green run here does not establish ​

This rebuild migrates an empty schema. DROP SCHEMA precedes it, so every ADD CONSTRAINT in 000040–000049 validates against zero rows and succeeds by construction. That says the DDL is correct; it says nothing about the same statements meeting data.

The distinction only matters when the same migrations are applied to an environment that has rows, which is production. Most of the range is new tables and nullable columns and cannot fail that way. The one worth checking against real data first is 000049, which drops and re-adds chk_psp_capability_provider on platform_service_providers, restricting providers to ses / aws_s3 / clerk / daily,whereby — an existing row outside that set fails the ADD, part-way through a migration, on a live database. 000043's FK from consents.source_form_id to forms(id) is checkable on the same principle, though on an environment that has never had a forms table the column should be entirely NULL.

So treat a green rebuild as evidence the migrations are well-formed, not as a rehearsal of the production run.

Phase 6 — park it ​

bash
./infra/scripts/staging-toggle.sh stop

Do not skip this, and do not assume the apply left things parked. Terraform restores desired_count = 1 on all seven services and creates any new schedule ENABLED, so phase 2 hands back a running staging with a partly armed cron fleet (measured 2026-08-12: four enabled, eight left disabled by the module's ignore_changes = [state]). stop is what makes the state uniform again.

Left running, staging costs roughly $162/mo (about $72 of Fargate, $44 of Aurora held awake by the crons, and the $46 floor). Parked, it is $46/mo — ALB $19.70, Redis $13.13, Secrets Manager $7.45, NAT instance $3.50, and about $2.20 of KMS, EIP, ECR and S3. Measured, not estimated.

stop disables every staging schedule as well as scaling the services down, because EventBridge is independent of ECS: without it the crons keep firing at a stopped environment, and a five-minute cron alone fires exactly on Aurora's SecondsUntilAutoPause = 300 boundary and stops the cluster ever pausing.