count(*) on a document collection never decrements after DELETE — counts drift upward permanently

[mkhairi]

Tested against: origin/main @ 09b8d9de (post-v0.3.0 main head, 2026-07-05)

Severity: Medium — cardinality reads disagree with scans forever after any delete; no error, no recovery statement.

Summary

count(*) on a document collection is served from a materialized per-collection row counter. INSERTs maintain it, but DELETEs never decrement it, so after the first delete the count permanently exceeds the real row set — while a scan of the same collection returns the correct rows. The divergence survives within the session and across sessions.

Repro

CREATE COLLECTION r29 (id TEXT PRIMARY KEY, v TEXT) WITH (engine='document_strict');
INSERT INTO r29 (id, v) VALUES ('a','1');
INSERT INTO r29 (id, v) VALUES ('b','2');
SELECT count(*) FROM r29;     -- 2      <- correct
DELETE FROM r29 WHERE id = 'a';
-- DELETE 1
SELECT count(*) FROM r29;     -- 2      <- WRONG, expected 1
SELECT id FROM r29;           -- b      <- scan is correct (1 row)

Related shape on the same counter: count(*) on a never-written document collection returns zero rows instead of one row containing 0.

Expected

1. count(*) reflects the live row set: DELETE decrements whatever counter backs it (the DELETE path clearly knows it removed a row — it reports DELETE 1).
2. count(*) on an empty collection returns a single row with 0.

Files (best-guess)

• per-collection row-counter maintenance — increment wired into the insert path, no decrement in the delete path
• aggregate planner routes count(*) to the materialized counter instead of falling back to a scan when the counter is uninitialized (the empty-collection zero-rows shape)

Operational context

Row counts back pagination, dashboards, and existence checks everywhere; a counter that only ever grows makes them all quietly wrong after the first delete, and the scan-vs-count disagreement is the kind of inconsistency that gets misdiagnosed as data loss or replication lag.