Index outbox(token_hash) so token deletes don't scan the outbox #13

Closed
opened 2026-10-15 15:52:22 +00:00 by ben · 3 comments
Owner

outbox.token_hash references login_tokens but has no index. When the hourly auth purge (#8, server/retention.js) deletes a token, SQLite's foreign-key check scans the whole outbox. The same happens on every other token delete. The outbox holds at most 30 days of mail, so the cost is small today.

Scope: a new migration step in server/db.js adding CREATE INDEX outbox_token_hash ON outbox(token_hash), with tests that follow the existing migration pattern (backup, user_version).

Risk: this is a schema migration, so it takes the High review tier and a dry run on a copy of the live database.

`outbox.token_hash` references `login_tokens` but has no index. When the hourly auth purge (#8, `server/retention.js`) deletes a token, SQLite's foreign-key check scans the whole outbox. The same happens on every other token delete. The outbox holds at most 30 days of mail, so the cost is small today. **Scope:** a new migration step in `server/db.js` adding `CREATE INDEX outbox_token_hash ON outbox(token_hash)`, with tests that follow the existing migration pattern (backup, `user_version`). **Risk:** this is a schema migration, so it takes the High review tier and a dry run on a copy of the live database.
Author
Owner

Planned as part of #8 migration v6, in docs/plans/2026-10-15-10-58_audit-retention-hold-client-deletion.md (a0f5627): CREATE INDEX outbox_token_hash ON outbox(token_hash), alongside the other v6 changes. Not implemented yet.

Planned as part of #8 migration v6, in `docs/plans/2026-10-15-10-58_audit-retention-hold-client-deletion.md` (a0f5627): `CREATE INDEX outbox_token_hash ON outbox(token_hash)`, alongside the other v6 changes. Not implemented yet.
Author
Owner

Implemented in 8754704: migration v6 adds CREATE INDEX outbox_token_hash ON outbox(token_hash), alongside audit_at, audit_actor and the legal_holds table.

Verification: the migration tests cover:

  • v5 → v6 with a backup;
  • the index existing afterwards;
  • foreign_key_check and integrity_check passing;
  • existing rows unchanged;
  • a fresh database reaching v6;
  • re-running being a no-op.

The full server suite passes (1351). The High review used codex and deepseek.

Left: the dry run on a copy of the live database, then deploying. This issue closes with #8 once v6 is deployed and verified.

Implemented in 8754704: migration v6 adds `CREATE INDEX outbox_token_hash ON outbox(token_hash)`, alongside `audit_at`, `audit_actor` and the `legal_holds` table. **Verification:** the migration tests cover: - v5 → v6 with a backup; - the index existing afterwards; - `foreign_key_check` and `integrity_check` passing; - existing rows unchanged; - a fresh database reaching v6; - re-running being a no-op. The full server suite passes (1351). The High review used codex and deepseek. **Left:** the dry run on a copy of the live database, then deploying. This issue closes with #8 once v6 is deployed and verified.
Author
Owner

Live at ec6f0cb: migration v6 created outbox_token_hash on the live database, verified after the deploy along with integrity_check and foreign_key_check. The dry run on a copy of the live database passed beforehand; the details are on #8.

Live at ec6f0cb: migration v6 created `outbox_token_hash` on the live database, verified after the deploy along with `integrity_check` and `foreign_key_check`. The dry run on a copy of the live database passed beforehand; the details are on #8.
ben closed this issue 2026-10-16 04:20:32 +00:00
Sign in to join this conversation.
No milestone
No project
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference
ben/career-values-cards#13
No description provided.