Compact SQLite after a rebuild; prune the few rows that do accumulate

[wiverson]

Revised 2026-08-05 after measuring. The original framing — that expired
OAuth tokens accumulate without bound — was wrong. See the comment below for
what changed. Keeping the issue open, at lower priority, for the part that
holds up.

What is actually true

Access tokens do not accumulate. The refresh grant in
@better-auth/oauth-provider deletes the access token it replaces:

await ctx.context.adapter.deleteMany({
  model: "oauthAccessToken",
  where: [{ field: "refreshId", value: refreshToken.id }],
});

There are three deleteMany sites in the provider: that one, plus two in
revokeRefreshFamily which clear an entire chain when a revoked refresh token is
reused. Steady state is roughly one live access-token row per connector, not one
per refresh.

We already prune the other unbounded thing. auth.ts evicts unused
oauthClient rows older than 24h before enforcing the 50-client cap, and
admin.ts cascades a manual revoke across tokens and consent.

Measured sizes

A synthetic vault matching the production one (2,611 notes, 1.15M words, 8.2 MB
of markdown) indexed to a 14.4 MB database in 2 seconds — about 1.8x the
source, because notes_fts is a content table, so FTS5 keeps a full copy of
title/headings/body alongside the inverted index. That is consistent with the
29 MB observed on the real instance, and it is bounded by vault size rather than
by time.

For contrast, 17,520 token rows — a year of hourly refresh, had they
accumulated — measures 18.6 MB. They do not accumulate, so that number is the
ceiling we are not approaching.

What still grows, and how fast

Never pruned, all small:

• session — one row per sign-in, a few hundred bytes.
• oauthRefreshToken — rows from abandoned grants (a connector removed, a client
  that registered once and never returned).
• oauthAccessToken — only orphans whose refresh chain was abandoned.
• verification — currently zero rows; we use no email flows.

Single-digit MB over years, against a volume measured in gigabytes.

The part worth doing

Compaction, and it is about a spike rather than a trend:

• rebuildIndex on 2,600 notes rewrites the entire FTS5 index. FTS5 leaves
  tombstones on delete and only reclaims them on
  INSERT INTO notes_fts(notes_fts) VALUES('optimize'), which nothing calls.
• SQLite never returns freed pages to the filesystem without VACUUM, so the
  file keeps its high-water mark after any large churn.

Proposed, in priority order:

• PRAGMA optimize plus an FTS5 optimize after rebuildIndex — cheap, and
  targets the one operation that actually churns the file.
• Surface database size and row counts on the Security or Settings tab, so
  growth is observable rather than assumed. This is what would have replaced
  the guesswork in the first version of this issue.
• A low-frequency sweep of expired session / oauthRefreshToken /
  oauthAccessToken / verification rows. Hygiene, not capacity.
• VACUUM only as an operator-triggered action, not a timer.

Interaction with #4

VACUUM rewrites the database and needs roughly its own size in free space to
finish. If it ever runs on a timer, the disk-headroom check in #4 has to gate it
— otherwise the operation meant to reclaim space is the one that fails when
space runs short.

[wiverson]

Rewrote the issue above. Recording what changed and why, since the original
framing drove the priority.

The claim that was wrong: that expired OAuth access tokens accumulate
without bound, one row per refresh. I asserted that without reading the
provider's refresh path. It deletes the access token it replaces on every
refresh, so the steady state is about one live row per connector.

How it surfaced: pushback on whether this was really much data. It was not.
Measuring rather than estimating gave: a vault the size of the production one
indexes to 14.4 MB, and the token accumulation I was worried about is prevented
upstream.

What survives: compaction. rebuildIndex churns the whole FTS5 index,
nothing calls optimize, and the file never shrinks. Still tens of MB, so still
not urgent — but it is a real gap rather than an imagined one, and the
"surface the numbers" item now exists specifically so the next version of this
question can be answered by looking instead of guessing.