Block reordering leaves orphaned rows in `_rels` tables

Describe the Bug

When blocks containing relationship or link fields are reordered (dragged to a new position) within a layout/blocks field, Payload creates new _rels rows with the updated path indices but never deletes the old rows at the previous path positions. This causes stale relationship references to accumulate in the _rels table over time.

These orphaned rows:

• Reference block indices that no longer exist (e.g., layout.12 on a page with only 5 blocks)
• Reference block positions now occupied by a different block type that doesn't have the field
• Grow linearly with each reorder operation (the relationship row gets "stamped" at every position the block passes through)

This has practical consequences: any hook or logic that checks for inbound references (e.g., preventing deletion of referenced documents) will find these stale FK rows and incorrectly block the operation.

Root cause analysis

The issue is in packages/drizzle/src/upsertRow/deleteExistingRowsByPath.ts.

When a document is updated, deleteExistingRowsByPath is called to clean up old _rels rows before inserting new ones. However, it only deletes rows whose exact paths match the paths in the incoming data (relationsToInsert + relationshipsToDelete):

// upsertRow/index.ts, line 356
await deleteExistingRowsByPath({
  rows: [...relationsToInsert, ...generalRelationshipDeletes],  // ← only paths being written
  ...
})

deleteExistingRowsByPath collects these paths and deletes with inArray(table[pathColumnName], Array.from(pathsToDelete)) — an exact match.

The problem: When a block with relationships (e.g., CTA) is reordered from position 2 to position 0, and a block without relationships (e.g., Content) now occupies position 2:

1. New rels are prepared with path layout.0.links.0.link.reference
2. deleteExistingRowsByPath deletes existing rows at path layout.0.links.0.link.reference (exact match)
3. Inserts the new row at layout.0.links.0.link.reference
4. The old row at layout.1.links.0.link.reference is never deleted — because no new relationship is being written at that path (the Content block has no links), so it's never in the paths set

The deletion is driven by the new data, not by what was there before. Paths that no longer have relationships are simply never touched.

Relevant files:

• packages/drizzle/src/upsertRow/index.ts (lines 344–374) — orchestrates rels save
• packages/drizzle/src/upsertRow/deleteExistingRowsByPath.ts — the exact-match deletion
• packages/drizzle/src/transform/write/blocks.ts (line 134) — path generation: ${path || ''}${field.name}.${i}.

Proposed fix

When saving a blocks field, delete all existing _rels rows whose path starts with the blocks field prefix, rather than only deleting paths that match the new data.

Option A (targeted): In transformBlocks (blocks.ts), after iterating all block data, push a prefix-based delete for the entire blocks field path:

// At the end of transformBlocks, signal that all old rels under this
// blocks field should be purged
relationshipsToDelete.push({ path: `${path || ''}${field.name}.`, prefix: true })

Then update deleteExistingRowsByPath to support prefix deletions using LIKE:

if (prefixPathsToDelete.size > 0) {
  for (const prefix of prefixPathsToDelete) {
    await adapter.deleteWhere({
      db,
      tableName,
      where: and(
        eq(table[parentColumnName], parentID),
        like(table[pathColumnName], `${prefix}%`),
      ),
    })
  }
}

Option B (simpler): In upsertRow/index.ts, before writing rels, collect all blocks field names from the document's field config, then delete all rels rows matching those prefixes:

// Before relationship insert, purge all rels under blocks fields
for (const blocksFieldPath of blocksFieldPaths) {
  await adapter.deleteWhere({
    db,
    tableName: relationshipsTableName,
    where: and(
      eq(table.parent, insertedRow.id),
      like(table.path, `${blocksFieldPath}%`),
    ),
  })
}

This is safe because the new rels are always fully re-inserted after the delete. The same approach would also fix #15976 (rels not cleaned on version restore).

Additional context

• Related: #15976 (rels not cleaned on version restore — same root cause of stale _rels rows not being deleted)
• The bug is not specific to any block type — it affects any block with a relationship-type field
• Block deletion (not just reordering) also leaves orphans
• The orphaned rows have valid FKs to the referenced documents, so they can interfere with cascade/deletion logic

Link to the code that reproduces this issue

https://github.com/fkkehlet/payload-rels-repro

Reproduction Steps

1. Clone the repo, pnpm install, configure .env with a Postgres database, run pnpm dev
2. Open /admin, create a Category (e.g., "Test Category")
3. Create a Page with this layout:
   • Block 1: Content (body: "first")
   • Block 2: CTA (heading: "Call to action", add a link → reference the Category)
   • Block 3: Content (body: "last")
4. Save the page
5. Query pages_rels to see the current state:

   SELECT id, path, categories_id FROM pages_rels WHERE parent_id = 1 ORDER BY path;
   -- Expected: layout.1.links.0.link.reference → categories_id = 1
   

6. In the admin panel, drag the CTA block from position 2 to position 1 (above the first content block)
7. Save the page
8. Query pages_rels again:

   SELECT id, path, categories_id FROM pages_rels WHERE parent_id = 1 ORDER BY path;
   

Expected: One row at layout.0.links.0.link.reference → categories_id = 1

Actual: Two rows:

• layout.0.links.0.link.reference → categories_id = 1 (new, correct)
• layout.1.links.0.link.reference → categories_id = 1 (old, orphaned — now points to a Content block that has no links field)

Repeating reorders accumulates more orphans. In production we found 46 orphaned rows across 8 pages after normal editing.

Example from production:

 id  | page_id |               path               | pages_id | block_at_position
------+---------+----------------------------------+----------+-------------------
  696 |       4 | layout.9.links.0.link.reference  |       13 | NO_BLOCK (index doesn't exist)
  491 |       3 | layout.3.links.0.link.reference  |       13 | contentgrid (wrong block type)
 2155 |       1 | layout.2.links.0.link.reference  |       77 | content_with_media (wrong block type)

Which area(s) are affected?

db: postgres

Environment Info

Payload:  3.84.1
Next.js:  15.5.12
Node.js:  22.22.0
Database: @payloadcms/db-postgres (Neon)
OS:       Linux (Fedora 42)

Start with packages/drizzle/src/upsertRow/deleteExistingRowsByPath.ts and the relationship-save orchestration in packages/drizzle/src/upsertRow/index.ts, then inspect path generation in packages/drizzle/src/transform/write/blocks.ts. Reproduce the reorder using the linked repository and query the Postgres _rels table. Done means moving or deleting blocks leaves only current relationship rows, with no stale paths after repeated saves.