Skip to content

RealOutcomes Directory 500 Diagnostic

Date: 2026-08-29
Status: Root cause identified · fix applied · awaiting Ryan review
Endpoint: GET /api/directory?limit=1 on VM 116 (realoutcomes-app)

Symptom

Soak probe during NAMING-2 reported:

Check HTTP
/ 200
/api/auth/user 401
/api/directory?limit=1 500

App log:

Error fetching directory: error: (ENOTFOUND) tenant/user postgres.cpocwrygxlqhzvmlrpjv not found

cpocwrygxlqhzvmlrpjv = deleted hosted Supabase project (teardown S3-8, 2026-08-28).

Historical baseline (pre-regression)

SPRINT-3 S3-6 (2026-08-28): GET /api/directory?limit=1 → 200, total 1,987,840 rows.
Directory was working on R940 through Friday cutover.

Reproduction (during incident)

prod  /api/directory?limit=1  → 500
LAN   /api/directory?limit=1  → 500  (192.168.1.99:5000)
prod  /                         → 200
prod  /api/auth/user            → 401

Root cause

NAMING-2 BWS migration introduced a bad DATABASE_URL in REALOUTCOMES_DB_PROD_*, then .env was re-rendered from BWS and the container was restarted without recreating it.

1. Wrong value copied into {PRODUCT}_DB_PROD_*

After NAMING-2 key copy, BWS held:

Key Value (host)
REALOUTCOMES_DB_PROD_DATABASE_URL aws-1-us-east-1.pooler.supabase.com (hosted, deleted)
REALOUTCOMES_DB_PROD_SUPABASE_URL https://cpocwrygxlqhzvmlrpjv.supabase.co (hosted, deleted)
REALOUTCOMES_DB_PROD_SUPABASE_POOLER_URL 192.168.1.98:6543 (correct LAN)

The BWS copy preserved a stale Railway/hosted DATABASE_URL in the old REALOUTCOMES_R940_PROD_DATABASE_URL key instead of the LAN stack value that T3-2 push-r940-stack-bws.py had written. S3-6 worked because the running container still had the correct env from an earlier --env-file create; disk .env and BWS were out of sync.

2. Re-render + docker restart does not reload env

During NAMING-2 fix-up, /opt/realoutcomes/.env was re-rendered from BWS (pulling the bad hosted DATABASE_URL). docker restart does not re-read --env-file; env is fixed at docker run time. The running container continued to expose the old hosted DATABASE_URL until recreated.

3. Not caused by VM hostname rename

Guest hostname change (realoutcomes-r940 → realoutcomes-app) did not affect DB connectivity. IPs unchanged (192.168.1.99 app, 192.168.1.98 db). Cloudflared origin uses IP.

Fix applied (2026-08-29)

  1. push-db-stack-bws.py — refreshed all 9 stack keys from live realoutcomes-db /opt/supabase/docker/.env:
  2. REALOUTCOMES_DB_PROD_DATABASE_URL → 192.168.1.98:5432/postgres
  3. REALOUTCOMES_DB_PROD_SUPABASE_URL → http://192.168.1.98:8000
  4. Re-render /opt/realoutcomes/.env from REALOUTCOMES_APP_PROD_* + REALOUTCOMES_DB_PROD_*.
  5. Recreate container with --env-file /opt/realoutcomes/.env (image tag still realoutcomes-r940:prod on VM; rename to realoutcomes-app:prod is cosmetic follow-up).

Post-fix verification

Check Result
LAN /api/directory?limit=1 200
prod /api/directory?limit=1 200
Directory total 1,990,402 rows
Soak probe OK

Follow-ups for Ryan (not executed)

  1. Deploy script guard: After any BWS re-render, use docker rm -f + docker run --env-file (or deploy-realoutcomes-app.sh), never rely on docker restart for env changes.
  2. BWS migration rule: After {PRODUCT}_R940_* → {PRODUCT}_DB_* copy, always run push-db-stack-bws.py from live stack VM before re-rendering app env.
  3. Image tag on VM 116: Retag realoutcomes-r940:prod → realoutcomes-app:prod when convenient.
  4. DATABASE_URL format: Current URL uses postgres.your-tenant-id@192.168.1.98:5432 (Supabase default tenant placeholder). Works today; consider documenting direct postgres@ URL for clarity.

STOP

Onset T-3 sub-audit not started per Ryan directive until this report is reviewed.