M24: [Major] Catalog schema hardening — missing scan_id index, CHECK constraints, ON DELETE; idx_files_state revisit

[curtyo18]

Summary

Four schema-level gaps in packages/engine/src/catalog/migrations/0001_initial.sql (and follow-up migrations). Bundled because one migration file can address all of them.

A. Missing index on files.scan_id

files.scan_id has a FOREIGN KEY but no index. markMissing in files-repo.ts runs WHERE drive_id = ? AND scan_id != ? AND state = 'indexed' AND (path LIKE ... OR ...) against a table that scales to millions of rows. SQLite walks every row matching drive_id (via the UNIQUE(drive_id, path) index prefix) and re-filters by scan_id. On a re-scan of a large drive, this becomes the dominant cost. Add CREATE INDEX idx_files_scan_id ON files (scan_id).

B. CHECK constraints on batches.status and operations.status missing

scans.status, files.state, files.date_source have CHECK constraints (0001_initial.sql:23, 47, 77, 95) enumerating valid values. batches.status and operations.status — both written via the OperationStatus type — don't. Mixed enforcement gives a false sense of safety; a TypeScript-side typo can land an invalid status string in those columns.

C. ON DELETE clauses missing on several FKs

• operations.batch_id — no ON DELETE
• operations.file_id — no ON DELETE
• quarantine.batch_id — no ON DELETE

Defaults to NO ACTION, which means a future purge of an old batch will fail until orphans are cleared manually. Pick ON DELETE CASCADE (or SET NULL for file_id) so the intent is explicit.

D. Discussion needed: idx_files_state may be wasteful

Reviewer flagged idx_files_state ON files (state) (line 55) as indexing a 5-value enum where ~all rows are 'indexed', so the planner will almost never use it and it just slows writes.

HOWEVER: spec §4.1 explicitly lists (state) as an index. So the reviewer's suggestion overrides a documented design choice. Two valid resolutions:

1. Keep as-is — leave the index, trust the spec's intent; revisit when actual measurements show it's hot enough to matter.
2. Make partial — CREATE INDEX idx_files_state ON files (state) WHERE state != 'indexed' — preserves the index's utility (queries for state='missing' or state='moved' use it) while massively reducing write cost.
3. Drop entirely + update spec — if no query uses it, remove + revise spec §4.1 to list only useful indexes.

Pick one based on what queries currently filter by state. Migration cost is low (DROP + optionally CREATE PARTIAL).

Background

From the 2026-05-17 multi-agent full-repo review.

Acceptance criteria

• New migration 0006_schema_hardening.sql (or similar) that:
  • Creates idx_files_scan_id ON files (scan_id) (A)
  • Adds CHECK constraints on batches.status and operations.status enumerating values from OperationStatus in shared/types.ts. SQLite ALTER TABLE doesn't add CHECK directly — use the table-rebuild dance (existing pattern in migration 0004). (B)
  • Adds ON DELETE CASCADE to the three FKs above via the same rebuild dance. (C)
• Migration is idempotent on retry (the rebuild dance preserves existing data).
• Test added: markMissing query plan uses idx_files_scan_id (via EXPLAIN QUERY PLAN).
• Test added: inserting a bogus operations.status = 'banana' is rejected by the CHECK constraint.
• Test added: deleting a batch cascades to its operations (or sets file_id NULL, depending on chosen policy).
• (D) — idx_files_state decision recorded in the PR; if dropped or made partial, the spec §4.1 line is updated in the same commit.

Files affected (likely)

• packages/engine/src/catalog/migrations/0006_*.sql (new)
• packages/engine/src/catalog/migrate.test.ts — migration smoke test
• packages/engine/src/catalog/files-repo.test.ts — query plan assertion
• docs/superpowers/specs/2026-04-25-file-organizer-design.md — §4.1 index list (only if D drops/changes the state index)

Discussion needed

D is the open one. Before writing the migration, decide which of the three options for idx_files_state to pursue. Lean toward Option 2 (partial index) — it's a small change and pays off the moment any query filters by state != 'indexed'.

Out of scope

• Larger schema redesign (M1 already addressed atomicity / safety).
• Other index decisions (e.g., (sha256) already covered).

References

• Spec §4.1 — index list and schema
• Code: packages/engine/src/catalog/migrations/0001_initial.sql
• Related closed: M1: [Major] Catalog SQL safety and atomicity (4 fixes) #21 (M1) — catalog SQL safety/atomicity