ORM: soft cascade delete re-scans every already-processed row each batch (quadratic reads, then a stranded CLEARING write outage) #407

Summary

Soft cascade delete re-queries from cursor: null on every batch. Under soft mode the already-processed rows are patched, not removed, so they stay inside the index range and get re-read and discarded by a .filter() on every subsequent batch.

Reads grow as roughly N²/batchSize. Past Convex's per-execution read limit the batch throws, and the aggregate state is left stranded at COUNT_STATUS_CLEARING — which the lifecycle write barrier turns into a total write outage on that table.

This is the only finding in the audit whose failure mode is an outage rather than slowness.

Root cause

packages/kitcn/src/orm/scheduled-mutation-batch.ts:245-251:

// Cascade workers patch/delete rows that are selected by the same indexed
// foreign key columns. Forwarding cursors can skip remaining rows after
// those mutations, so cascade continuation always re-queries from null.
const usesCursorContinuation = false;
const paged = await queryWithIndex().paginate({
  cursor: usesCursorContinuation ? args.cursor : null,
  numItems: args.batchSize,
});

The comment is correct about why — forwarding a cursor across mutations can skip rows. The problem is what soft mode does to that reasoning.

The soft-cascade condition is at :220-222:

workType === 'cascade-delete' &&
...
(args.cascadeMode ?? 'hard') === 'soft';

and the guard that excludes already-processed rows is a .filter() at :237-241:

return indexed.filter((q: any) =>
  q.or(
    q.eq(q.field(DELETION_TIME_FIELD), undefined),
    q.eq(q.field(DELETION_TIME_FIELD), null)
  )
);

Hard mode is fine: processed rows are deleted, so re-querying from null skips them for free.

Soft mode is not: processed rows only get deletionTime set. They remain in the index range, Convex still reads them, and the .filter() rejects them afterwards. Batch k therefore re-reads all (k-1) * batchSize rows it already handled.

cascadeMode resolves to 'soft' whenever the delete mode is soft or scheduled (packages/kitcn/src/orm/delete.ts:578-580):

const cascadeMode: CascadeMode =
  this.cascadeMode ??
  (deleteMode === 'soft' || deleteMode === 'scheduled' ? 'soft' : 'hard');

Impact

• Quadratic document reads on a routine operation. Because it is quadratic, the read ceiling arrives at a table size well below where anyone would expect trouble.
• On hitting the ceiling the mutation dies mid-campaign. If the table declares an aggregateIndex/rankIndex, state stays at COUNT_STATUS_CLEARING, and assertAggregateIndexesWritable (orm/aggregate-index/runtime.ts:3088) is prepended to create/update/delete before-hooks — so every write to that table then fails until the state is repaired by hand.
• Hard-mode cascade is unaffected.

Note on testing this

The wasted reads are consumed by .filter(), so they are invisible to the documents counter in countDocumentReads. A regression test needs the scanned-row counter from #384 — asserting on documents here would pass vacuously.

Suggested fix

Keep the correctness property the comment describes (no cursor skipping across mutations) while dropping the re-scan. Options:

1. Watermark on the indexed column — carry the last processed key and start the next batch's index range above it, rather than replaying from null. Preserves the "don't trust a forwarded cursor" property because the range is recomputed from data, not from an opaque cursor.
2. Narrow the index range instead of filtering — if the soft-delete marker can participate in the index, processed rows leave the range entirely and the scan stops re-reading them.

Option 1 is likelier to fit without a schema change. Either way the acceptance criterion is that total reads across a full cascade campaign scale linearly with row count, asserted at two table sizes.

Environment

• kitcn 0.25.10
• convex 1.44.0
• verified against origin/main at 00f7f121