SPRINT-7 Registry 500 Findings — organizations Schema vs Code¶
Date: 2026-08-30
Gate: Pre-T2-3 (Ryan requested before secret rotation)
Scope: /api/public/registry/v2 (and v1) 500 on organizations.certification_status
Executive verdict¶
| Question | Answer |
|---|---|
| Migration regression? | No. Restore preserved hosted schema exactly on organizations. |
| Pre-existing prod bug? | Yes. Live Railway last1.app returns 500 on the same endpoints today. |
| Restore fidelity gap? | No on organizations — hosted and R940 column lists are identical (59 columns, zero diff). |
| Safe to proceed T2-3? | Yes for migration integrity. Registry fix is a separate app bug (not a T2-3 blocker unless Ryan wants it fixed first). |
1. Live endpoint test (A/B)¶
| Endpoint | Railway (https://last1.app) |
R940 (http://192.168.1.104:5000) |
|---|---|---|
GET /api/public/registry/v2 |
500 {"message":"Failed to fetch registry"} |
500 (same body) |
GET /api/public/registry (v1) |
500 (same) | (not re-tested; same code path) |
Conclusion: Railway already 500s. R940 did not introduce this; it reproduces production behavior against the same logical schema.
2. organizations certification columns — hosted vs R940¶
Queried live hosted tcthzakppndkykatoefx (session pooler) and R940 last1app-db (192.168.1.103).
Certification-related columns (both environments — identical)¶
| column_name | udt_name |
|---|---|
certification_applied_at |
timestamptz |
certification_confidence_score |
float4 |
certification_expires_at |
timestamptz |
certification_level |
certification_level |
certification_notes |
text |
certification_reason |
text |
certification_state |
certification_state |
Presence checks¶
| Column | Hosted | R940 |
|---|---|---|
certification_status |
absent | absent |
certification_state |
present | present |
Full table parity¶
| Metric | Hosted | R940 |
|---|---|---|
organizations column count |
59 | 59 |
| Column-name diff | none |
Conclusion: Gate B restore did not drop, rename, or drift organizations relative to hosted. Warriors In Need and all other org rows sit on the same column layout in both places.
3. Root cause (code, not restore)¶
server/registryService.ts primary select (REGISTRY_SELECT_V1) includes certification_status, which does not exist in either database:
17:25:/tmp/sprint7-audit/last1.app/server/registryService.ts
const REGISTRY_SELECT_V1 = `
id, name, slug,
certification_status, certification_level,
certification_state, certification_confidence_score,
...
`;
column organizations.certification_status does not exist
hint: Perhaps you meant to reference the column "organizations.certification_state".
Fallback gap: fetchRegistryOrganizations() retries with REGISTRY_SELECT_FALLBACK only when the error mentions certification_state, certification_confidence_score, or irs_ — not when certification_status is missing. The fallback select also lists certification_status, so it would fail anyway.
Drizzle schema (shared/schema.ts) defines both enums/columns (certification_status and certification_state), but the live hosted DB only materialized the certification_state migration path. Deployed app code still queries the legacy column name in registry paths.
This is application/schema migration debt on production Railway, unchanged by R940 restore.
4. Warriors In Need implication¶
Gate B confirmed Warriors In Need org row intact on R940 (organizations + last1_organizations). This registry bug does not indicate org data loss or org schema drift. It blocks public registry API responses until code is updated to use certification_state (or a compat view/column is added). Internal org-scoped queries that already use certification_state (e.g. validation engine) are unaffected.
5. Recommended fix (separate from T2-3)¶
Not executed in this check — report only:
- Update
REGISTRY_SELECT_V1/REGISTRY_SELECT_FALLBACKto selectcertification_state(and map to APIcertificationStatusin enrich layer), or - Add DB compat:
certification_statusgenerated column / view mapping fromcertification_state(heavier; prefer code fix).
Extend fallback trigger to include certification_status missing errors as a safety net.
Fix and test on R940 first, then deploy to Railway before or at T-5 cutover so public registry works on sovereign path.
STOP¶
Findings complete. T2-3 (secret rotation) may proceed from a restore-integrity standpoint. Registry fix is tracked as pre-existing prod bug, not migration regression.