Index outbox(token_hash) so token deletes don't scan the outbox #13
Labels
No labels
priority/high
priority/low
priority/medium
type/feature
No milestone
No project
No assignees
1 participant
Notifications
Due date
No due date set.
Dependencies
No dependencies set.
Reference
ben/career-values-cards#13
Loading…
Add table
Add a link
Reference in a new issue
No description provided.
Delete branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
outbox.token_hashreferenceslogin_tokensbut 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.jsaddingCREATE 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.
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.Implemented in
8754704: migration v6 addsCREATE INDEX outbox_token_hash ON outbox(token_hash), alongsideaudit_at,audit_actorand thelegal_holdstable.Verification: the migration tests cover:
foreign_key_checkandintegrity_checkpassing;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.
Live at
ec6f0cb: migration v6 createdoutbox_token_hashon the live database, verified after the deploy along withintegrity_checkandforeign_key_check. The dry run on a copy of the live database passed beforehand; the details are on #8.