Revision pruning deletes concurrently created revisions, failing saves with a draft_revision_id FK violation

[MA2153]

Description

RevisionRepository.pruneOldRevisions deletes revisions created after it took its keep-list snapshot. Because pruning runs fire-and-forget via after(), a save that lands while a previous save's prune is in flight can have its brand-new draft revision deleted out from under it, and the UPDATE … SET draft_revision_id = … that immediately follows then fails the draft_revision_id → revisions.id foreign key.

The prune is two statements (packages/core/src/database/repositories/revision.ts:175-206):

1. SELECT id FROM revisions WHERE collection = ? AND entry_id = ? ORDER BY created_at DESC, id DESC LIMIT keepCount → keepIds
2. DELETE FROM revisions WHERE collection = ? AND entry_id = ? AND id NOT IN (keepIds) AND NOT EXISTS (SELECT 1 FROM ec_<collection> WHERE live_revision_id = revisions.id OR draft_revision_id = revisions.id)

Any revision inserted between (1) and (2) is absent from keepIds, so step 2 deletes it. This happens even far below the retention limit — with keepCount = 50 and three revisions on the entry, the newly inserted fourth is still deleted, because the delete is "everything outside a stale snapshot" rather than "everything older than the newest N". The NOT EXISTS guard doesn't protect it either: the revision is not yet referenced, since the statement that stages it is the one that subsequently fails.

The interleaving in handleContentUpdate (packages/core/src/emdash-runtime.ts:2919-3001):

save A: after(() => pruneQueuedEntry(...))   // not awaited
save B: revisionRepo.create()                 → INSERT rev3
  prune A: SELECT keepIds                     → [rev1, rev2]        (rev3 not visible / not yet inserted)
  prune A: DELETE … id NOT IN (rev1, rev2)    → rev3 deleted
save B: replaceDraftRevision(…, rev3)         → FK violation

draft_revision_id carries a real foreign key on every content table (schema/registry.ts:1042, col.references("revisions.id")), so where FKs are enforced — Postgres, D1, and Node (database/connection.ts:53 sets PRAGMA foreign_keys = ON) — the second save fails outright and the user gets a 500 on an ordinary content update. Where they aren't enforced the row is written anyway and the entry is left pointing at a revision that no longer exists.

Two saves close together on the same entry is enough; no unusual load required.

Expected: a prune queued for revision R never touches revisions newer than R, and a concurrent save always succeeds.

pruneQueuedEntry already receives the queued revisionId (revision.ts:208-222) but uses it only to clear the queue row — it isn't passed to pruneOldRevisions. Bounding the delete with AND id <= ${queuedRevisionId} looks like the natural fix: IDs are monotonic ULIDs (monotonicFactory(), revision.ts:7), so a prune would then ignore everything created after it was queued, which appears to be what the queue design already intends. Folding the keep-selection into the DELETE as a subquery would narrow the window but is still not atomic against a concurrent insert.

Steps to reproduce

Observed in CI rather than hand-reproduced — it's timing-dependent, so it surfaces intermittently:

1. On an entry in a collection with supports: ["revisions"], issue a content update (this stages a draft revision and schedules a prune via after()).
2. Issue a second content update on the same entry while that prune is still in flight.
3. The second update intermittently fails with a foreign key violation on draft_revision_id.

In the test suite this is packages/core/tests/integration/database/media-usage-runtime-refresh.test.ts > runtime content media usage refresh [postgres] > refreshes draft overlay usage after runtime revision restore, where two back-to-back handleContentUpdate calls (lines 412 and 420) produce exactly this interleaving.

Only the [postgres] variant fails: tests/utils/test-db.ts:56 opens better-sqlite3 without PRAGMA foreign_keys = ON, so on SQLite the same race silently writes the dangling draft_revision_id and the test passes.

The pruning has been fire-and-forget since before #2407 (void pruneOldRevisions(...).catch(() => {})), so the race predates it, but #2407 changed deferred-task lifetime and draining, which plausibly changed the timing enough to make it observable.

Environment

• emdash version: 0.32.0 (main at e0ce3a9)
• Node.js version: 22 (CI)
• Runtime: Node; affects Cloudflare Workers/D1 and Postgres equally, since all three enforce the FK
• OS: Linux (GitHub Actions ubuntu-latest)

Logs / error output

FAIL tests/integration/database/media-usage-runtime-refresh.test.ts > runtime content media usage refresh [postgres] > refreshes draft overlay usage after runtime revision restore
error: insert or update on table "ec_posts" violates foreign key constraint "ec_posts_draft_revision_id_fkey"
 ❯ ContentRepository.replaceDraftRevision src/database/repositories/content.ts:1602:18
 ❯ EmDashRuntime.handleContentUpdate src/emdash-runtime.ts:2942:16
 ❯ tests/integration/database/media-usage-runtime-refresh.test.ts:420:23

Serialized Error: {
  severity: 'ERROR',
  code: '23503',
  detail: 'Key (draft_revision_id)=(01KZRM5CP32RN6BM439K1AHQ1V) is not present in table "revisions".',
  schema: 'test_7_44c8ddbdf70e',
  table: 'ec_posts',
  constraint: 'ec_posts_draft_revision_id_fkey',
}

Full run: https://github.com/emdash-cms/emdash/actions/runs/31501622504/job/93813516901