Three queries that scan a whole table, now that statements have a deadline

[Devski]

Goal

The three queries in this codebase that read more rows the longer the product
lives should stop doing that — before the deadlines from #172 turn "slow" into
"stopped".

What is there now

Found 12.09.2026 in the review of #172, which put a ten-second bound on every
statement a web request makes. Each of these is small today and none of them
is bounded by anything but the size of a table:

1. The R360 collector's sweep. src/lib/r360/frame-set.ts:438 selects
   distinct key prefixes out of files with a LIKE and a per-row regex
   substring() — a sequential scan with no LIMIT, over a table that gains
   up to 720 rows per orbit. frame-set.ts:366 has the same shape on
   pending_uploads. The database has no deadlines: a pool that waits for ever and statements nothing cuts off #172 gave the collector a pool of its own without a
   statement bound precisely so this cannot fail silently, but that only buys
   time: the scan still gets slower for ever, on the one core the site shares.
2. verifications has no index on expires_at. src/db/schema.ts:147
   indexes identifier only, and Better Auth deletes expired rows on every
   token lookup — a sequential scan on the sign-in path, which is now bounded
   by a deadline it was not written against.
3. The legacy sha256 branch in src/lib/profile.ts:383. No usable index
   for that predicate (files.sha256 is only indexed as part of
   (user_id, sha256, kind)), so it scans files once per key — inside a
   transaction holding the per-user advisory lock. With lock_timeout now
   at three seconds, a slow probe there makes another writer for the same user
   fail rather than wait. It heals when pnpm db:backfill-file-keys has run
   in an environment, and nothing enforces that it has.

Why it matters

None of these is a bug today; all three are the same shape, and it is the
shape that turns a growing table into a failing feature. The collector one is
the worst of them, because its failure mode is a report that says "nothing
found" — indistinguishable from a clean bucket, which is exactly the silence
#167 is about.

Acceptance criteria

• The collector's sweep is bounded by something other than the table's
  size — a key-prefix column, a window, or a cursor it carries between runs
• A sweep that did not finish reports as unfinished, never as clean
• verifications(expires_at) is indexed (expand-only migration, G6)
• The legacy sha256 lookup either has an index or is retired, and the
  environments that still need the backfill are named
• Each of the three has a number measured before and after, on dev's data

Verification

• EXPLAIN (ANALYZE) on each of the three, recorded in the pull request
• A seeded files table an order of magnitude larger than dev's: the
  collector still finishes, and the sign-in path stays flat

Dependencies

Related: #172 (the bounds that make these matter), #167 (a report nobody can
tell from silence), #156 (the collector's decision to leave a human in the
loop), #49 (the backfill that retires the legacy branch).

Spec: SPEC.md §9 · Size: S · Labels: enhancement