Duplicate latest=1 version rows under autosave on D1/SQLite — createVersion clears prior latest flags non-transactionally with a strict updated_at< guard

Environment: Payload 3.84.1 · @payloadcms/db-d1-sqlite · Cloudflare Workers (OpenNext) · production Cloudflare D1

Summary

On collections with drafts + autosave, production D1 accumulates multiple latest = 1 rows per parent document in the _<collection>_v table. Once that happens, queryDrafts-backed reads (the admin list, REST ?draft=true) return one row per latest-flagged version row with id = parent — a document with pending edits paints as 2–5 duplicate rows in the admin list, and totalDocs inflates accordingly. We observed 5× duplication of a single document on a live site under an editor's normal draft-then-publish loop.

Mechanism (traced in 3.84.1 dist)

createVersion inserts the new version row and then clears prior latest flags in a separate, non-transactional statement guarded by a strict updated_at < comparison. Two failure paths follow on D1:

1. Same-second ties. Autosave (500 ms interval) routinely writes two version rows within the same second. The strict < guard skips rows whose updated_at equals the new row's — both keep latest = 1.
2. Dropped writes. D1's silent-write class (#15070 family): if the clear statement is dropped, nothing detects it — the insert already succeeded, so the stale flag survives.

Local SQLite (fast, reliable, single-connection) never reproduces this — publish → draft → autosave stays at exactly one latest row locally. Production D1 recreates it under normal editing.

Second manifestation — ghost rows

_<collection>_v.parent_id is ON DELETE SET NULL, so hard-deleting a parent strands its latest-flagged version rows with parent_id = NULL. queryDrafts-backed admin lists then paint a row with id: null — clicking it errors with "document with ID null could not be found", and the list counter includes it. Observed on two collections (a trashed-then-purged doc, and a seed-era hard delete).

Live evidence (one tenant, 2026-07-06)

• 5 orphaned _team_v rows + 27 orphaned _pages_v rows (all parent_id IS NULL, latest = 1) cleaned up by hand.
• One document echoed twice in REST ?draft=true (totalDocs 35 for 34 real docs) and 5× in the admin list against a "1-2 of 2" pagination counter.

Suggested fixes (either closes the accumulation)

• Make the latest-clear part of the same transaction/batch as the insert (D1 batch() executes atomically), or
• make the clear guard non-strict / key it on the newly inserted version row's id rather than updated_at <.

Our mitigation (tenant-side), in case it helps others

An afterChange hook that clears duplicate latest = 1 rows scoped to the saved doc (keep newest by updated_at, id tiebreak) + a nightly sweep deleting parent_id IS NULL orphans and clearing stale flags across all version tables. This prevents recurrence but the root cause is the non-transactional two-statement write in createVersion.

Related: #14766 (D1 bound-parameter behavior), #15070 (D1 silent writes).

Start at the createVersion entry point in @payloadcms/db-d1-sqlite and trace the version insert and latest-flag clearing on D1/SQLite. Reproduce drafts with autosave, including same-second writes and parent deletion, while checking the version rows. Done means each parent has one latest row and deleted parents leave no latest-flagged orphan rows.