Problem
The nightly sweeps that mirror per-vault state into D1 have no orphan prune, so rows for vaults that no longer exist accumulate without bound.
workers/identity/src/snapshots.ts:160 is the only writer of vault_snapshots: a per-vault REPLACE (DELETE FROM vault_snapshots WHERE vault_name = ? then reinsert the current manifest) run once per vault the sweep enumerates.- The sweep enumerates only rows in
vaults. A vault that has been deleted is never enumerated again, so its old mirror rows are never revisited or removed.
vault_usage (src/usage.ts, upsert per (vault_name, day)) has the same shape and the same leak.
This is a direct contributor to the O(fleet) growth behind #224. At the time of the 2026-07-26 staging reclamation there were 1,524 orphan vault_snapshots rows and 1,008 orphan vault_usage rows for vaults that no longer existed.
Fix (one line, per sweep)
At the end of each sweep, drop rows whose vault is gone:
DELETE FROM vault_snapshots WHERE vault_name NOT IN (SELECT name FROM vaults);
DELETE FROM vault_usage WHERE vault_name NOT IN (SELECT name FROM vaults);
Safe by construction — a vault_name absent from vaults cannot belong to a live vault. This keeps both mirror tables bounded to the live fleet going forward. (The historical orphans were already cleared by hand in the 2026-07-26 reclamation.)
Related: #224. Best paired with the hosted vault-delete path (separate issue) so deletes prune their own mirror rows synchronously, with this sweep-time prune as the backstop.
Problem
The nightly sweeps that mirror per-vault state into D1 have no orphan prune, so rows for vaults that no longer exist accumulate without bound.
workers/identity/src/snapshots.ts:160is the only writer ofvault_snapshots: a per-vault REPLACE (DELETE FROM vault_snapshots WHERE vault_name = ?then reinsert the current manifest) run once per vault the sweep enumerates.vaults. A vault that has been deleted is never enumerated again, so its old mirror rows are never revisited or removed.vault_usage(src/usage.ts, upsert per(vault_name, day)) has the same shape and the same leak.This is a direct contributor to the O(fleet) growth behind #224. At the time of the 2026-07-26 staging reclamation there were 1,524 orphan
vault_snapshotsrows and 1,008 orphanvault_usagerows for vaults that no longer existed.Fix (one line, per sweep)
At the end of each sweep, drop rows whose vault is gone:
Safe by construction — a
vault_nameabsent fromvaultscannot belong to a live vault. This keeps both mirror tables bounded to the live fleet going forward. (The historical orphans were already cleared by hand in the 2026-07-26 reclamation.)Related: #224. Best paired with the hosted vault-delete path (separate issue) so deletes prune their own mirror rows synchronously, with this sweep-time prune as the backstop.