eduos-postgres-migration-drift-missing-function
Solution written to:
~/SOLUTION-eduos-postgres-migration-drift-missing-function.md
Note: this environment contains no eduos checkout — only /workspace/problem.json. I therefore could not execute the DB/endpoint probes against a real instance. The document is a complete, directly executable runbook; I did locally validate the one nontrivial shell transform it relies on (the 046 → 062 CREATE OR REPLACE + transaction-control-stripping sed).
Root cause — dev-DB migration drift: migration 046 is committed in the repo but was never applied to localhost:5433; tests are green because the test environment migrates its own DB. Endpoint calls a routine the live DB lacks → Postgres 42883 undefined_function → 500.
Diagnosis (pre/post probes)
- pg_proc + information_schema.routines probe for intervention_student_context(uuid) run against dev vs test DB.
- Migration-ledger diff (comm -23 repo files vs schema_migrations) to establish drift scope.
- JWT-authenticated reproduction of the 500.
Exact fix
- 062_ai_tutor_restore_046_functions.sql: next-numbered, idempotent CREATE OR REPLACE copies generated mechanically from 046 (so bodies can't drift), with a review gate for signatures, volatility, SECURITY, search_path, and GRANT EXECUTE; targeted DROP FUNCTION IF EXISTS only when return type/args changed.
- Applies it via the repo's own client: apps/api/node_modules/pg (script included), plus ledger entry.
- docs/runbooks/database-migration-drift.md entry documenting drift scope.
Verification
- Routine probe diff (must be add-only, identity_args = uuid).
- Executability probe as the API role (catches missing grants → different 500).
- Live GET /ai-tutor/students/me/sessions with a real student JWT → 2xx + valid JSON.
- Fresh-DB double-apply to prove idempotency; test suite on its own DB.
Prevention / rollback
- t636 register-sync pre-check: fail-closed ledger diff + routine-existence/executability assertions before judge runs after board-only commits (same guard that would have caught BUG-DATA-001-REOPEN).
- Additive rollback procedure and a change checklist.
# Evidence - Problem class: eduos-postgres-migration-drift-missing-function - Model: openrouter/deepseek/deepseek-v4.1-flash - Solved: 2026-09-14T07:32:03.185Z - Verification: solution produced by pi in sandbox; see signatures.json
{"description": "Student AI tutor 500: GET /ai-tutor/students/me/sessions threw function intervention_student_context(uuid) does not exist though repo migration 046 defines it. Root cause: dev-DB migration drift - live DB (localhost:5433) never received 046 function definitions while repo tree had them; tests green because test env migrates its own DB. Fix pattern: create NEXT-numbered idempotent migration (062) with CREATE OR REPLACE copies of the missing functions from the source migration, apply to dev DB via apps/api node_modules pg client, document drift scope in docs/runbooks. Verify: information_schema.routines probe pre/post + live endpoint JWT probe. Same drift class as BUG-DATA-001-REOPEN; register-sync pre-check (t636) applies to judge runs after board-only commits.", "environment": "", "language": "", "model": "openrouter/deepseek/deepseek-v4.1-flash", "problem_class": "eduos-postgres-migration-drift-missing-function", "provider": "openrouter", "solved_at": "2026-09-14T07:32:03.185Z", "version": ""}Solution written to:
~/SOLUTION-eduos-postgres-migration-drift-missing-function.md
Note: this environment contains no eduos checkout — only /workspace/problem.json. I therefore could not execute the DB/endpoint probes against a real instance. The document is a complete, directly executable runbook; I did locally validate the one nontrivial shell transform it relies on (the 046 → 062 CREATE OR REPLACE + transaction-control-stripping sed).
Root cause — dev-DB migration drift: migration 046 is committed in the repo but was never applied to localhost:5433; tests are green because the test environment migrates its own DB. Endpoint calls a routine the live DB lacks → Postgres 42883 undefined_function → 500.
Diagnosis (pre/post probes)
- pg_proc + information_schema.routines probe for intervention_student_context(uuid) run against dev vs test DB.
- Migration-ledger diff (comm -23 repo files vs schema_migrations) to establish drift scope.
- JWT-authenticated reproduction of the 500.
Exact fix
- 062_ai_tutor_restore_046_functions.sql: next-numbered, idempotent CREATE OR REPLACE copies generated mechanically from 046 (so bodies can't drift), with a review gate for signatures, volatility, SECURITY, search_path, and GRANT EXECUTE; targeted DROP FUNCTION IF EXISTS only when return type/args changed.
- Applies it via the repo's own client: apps/api/node_modules/pg (script included), plus ledger entry.
- docs/runbooks/database-migration-drift.md entry documenting drift scope.
Verification
- Routine probe diff (must be add-only, identity_args = uuid).
- Executability probe as the API role (catches missing grants → different 500).
- Live GET /ai-tutor/students/me/sessions with a real student JWT → 2xx + valid JSON.
- Fresh-DB double-apply to prove idempotency; test suite on its own DB.
Prevention / rollback
- t636 register-sync pre-check: fail-closed ledger diff + routine-existence/executability assertions before judge runs after board-only commits (same guard that would have caught BUG-DATA-001-REOPEN).
- Additive rollback procedure and a change checklist.
# Evidence - Problem class: eduos-postgres-migration-drift-missing-function - Model: openrouter/deepseek/deepseek-v4.1-flash - Solved: 2026-09-14T07:32:03.185Z - Verification: solution produced by pi in sandbox; see signatures.json
{"description": "Student AI tutor 500: GET /ai-tutor/students/me/sessions threw function intervention_student_context(uuid) does not exist though repo migration 046 defines it. Root cause: dev-DB migration drift - live DB (localhost:5433) never received 046 function definitions while repo tree had them; tests green because test env migrates its own DB. Fix pattern: create NEXT-numbered idempotent migration (062) with CREATE OR REPLACE copies of the missing functions from the source migration, apply to dev DB via apps/api node_modules pg client, document drift scope in docs/runbooks. Verify: information_schema.routines probe pre/post + live endpoint JWT probe. Same drift class as BUG-DATA-001-REOPEN; register-sync pre-check (t636) applies to judge runs after board-only commits.", "environment": "", "language": "", "model": "openrouter/deepseek/deepseek-v4.1-flash", "problem_class": "eduos-postgres-migration-drift-missing-function", "provider": "openrouter", "solved_at": "2026-09-14T07:32:03.185Z", "version": ""}