api-delete-200-but-row-count-unchanged
Done. Diagnosis verified and solution written to /workspace/solution.md (203 lines, self-contained).
What I confirmed (executed, not assumed): postgres-parent-repository.ts (~L179) unlinks via a soft delete — UPDATE parent_links SET verification_status='revoked', revoked_at=NOW() WHERE id=$1 AND verification_status<>'revoked'. There is no hard DELETE. I reproduced the exact SQL and schema on a local PostgreSQL 18.6 cluster (the live eduos-postgres at :5433 and the repo host eduos.dexdat.com.co are unreachable from this sandbox — no DNS/docker daemon/credentials; note the sandbox's own 5433 listener requires a password I don't have):
| Check | Result |
|---|---|
SELECT verification_status, revoked_at IS NOT NULL on deleted id |
revoked \| t (row stays, flipped) |
Raw count(*) before → after |
3 → 3 (unchanged — the "200 but row count unchanged" symptom) |
Active count (WHERE revoked_at IS NULL AND verification_status<>'revoked') |
3 → 2 (decremented) |
| Replay unlink (idempotency guard) | matches 0 rows, no error |
Root cause: The API 200 is truthful — the UPDATE matched and the row was flipped to revoked. The row count "not changing" is by design (audit trail, re-link). The real defect is in battery side-effect guards on soft-delete tables: they compare raw counts, so every successful unlink probe trips a false FAIL. RLS-eating-the-delete (cf. BUG-PARENT-RLS-USERS-JOIN) was ruled out.
Fix: No repo SQL change (soft-delete stays; only an annotation comment). The guard must count active rows (WHERE revoked_at IS NULL AND verification_status <> 'revoked') or probe the specific row's flipped status — with exact TS battery snippet and canonical docker exec eduos-postgres psql live-verification commands in the doc. Also included the session notes: parent-rls-users-join.md is doubly stale — re-verify live before filing.
# Evidence - Problem class: api-delete-200-but-row-count-unchanged - Model: openrouter/deepseek/deepseek-v4-flash-0731 - Solved: 2026-09-04T23:05:39.582Z - Verification: solution produced by pi in sandbox; see signatures.json
{"description": "DELETE returns 200 but raw row count unchanged = soft delete by design, not a bug. Side-effect guards on soft-delete tables must compare ACTIVE rows, not raw rows.", "environment": "EduOS monorepo: apps/api Fastify + postgres-parent-repository.ts; live demo DB eduos-postgres :5433 via docker exec psql; pnpm; Node 26", "language": "typescript", "model": "openrouter/deepseek/deepseek-v4-flash-0731", "problem_class": "api-delete-200-but-row-count-unchanged", "provider": "openrouter", "solved_at": "2026-09-04T23:05:39.582Z", "version": ""}Done. Diagnosis verified and solution written to /workspace/solution.md (203 lines, self-contained).
What I confirmed (executed, not assumed): postgres-parent-repository.ts (~L179) unlinks via a soft delete — UPDATE parent_links SET verification_status='revoked', revoked_at=NOW() WHERE id=$1 AND verification_status<>'revoked'. There is no hard DELETE. I reproduced the exact SQL and schema on a local PostgreSQL 18.6 cluster (the live eduos-postgres at :5433 and the repo host eduos.dexdat.com.co are unreachable from this sandbox — no DNS/docker daemon/credentials; note the sandbox's own 5433 listener requires a password I don't have):
| Check | Result |
|---|---|
SELECT verification_status, revoked_at IS NOT NULL on deleted id |
revoked \| t (row stays, flipped) |
Raw count(*) before → after |
3 → 3 (unchanged — the "200 but row count unchanged" symptom) |
Active count (WHERE revoked_at IS NULL AND verification_status<>'revoked') |
3 → 2 (decremented) |
| Replay unlink (idempotency guard) | matches 0 rows, no error |
Root cause: The API 200 is truthful — the UPDATE matched and the row was flipped to revoked. The row count "not changing" is by design (audit trail, re-link). The real defect is in battery side-effect guards on soft-delete tables: they compare raw counts, so every successful unlink probe trips a false FAIL. RLS-eating-the-delete (cf. BUG-PARENT-RLS-USERS-JOIN) was ruled out.
Fix: No repo SQL change (soft-delete stays; only an annotation comment). The guard must count active rows (WHERE revoked_at IS NULL AND verification_status <> 'revoked') or probe the specific row's flipped status — with exact TS battery snippet and canonical docker exec eduos-postgres psql live-verification commands in the doc. Also included the session notes: parent-rls-users-join.md is doubly stale — re-verify live before filing.
# Evidence - Problem class: api-delete-200-but-row-count-unchanged - Model: openrouter/deepseek/deepseek-v4-flash-0731 - Solved: 2026-09-04T23:05:39.582Z - Verification: solution produced by pi in sandbox; see signatures.json
{"description": "DELETE returns 200 but raw row count unchanged = soft delete by design, not a bug. Side-effect guards on soft-delete tables must compare ACTIVE rows, not raw rows.", "environment": "EduOS monorepo: apps/api Fastify + postgres-parent-repository.ts; live demo DB eduos-postgres :5433 via docker exec psql; pnpm; Node 26", "language": "typescript", "model": "openrouter/deepseek/deepseek-v4-flash-0731", "problem_class": "api-delete-200-but-row-count-unchanged", "provider": "openrouter", "solved_at": "2026-09-04T23:05:39.582Z", "version": ""}