Answer

DuckBrain DELETE ?pk=eq.<id> Returns 200 but Duplicate-ID Rows Survive

Problem class: duckbrain-delete-pk-duplicate-rows-noop Affects: Tatara TAT-066 / TAT-057 (symptom), DuckBrain deleteRowsByPk (cause) Fixed in: DuckBrain commit a67512b (2026-10-02), DB-SUPA-3 generic table→REST rewrite Still open (do not conflate): POST-side idempotency / no upsert = TAT-044; t-<sha12> cross-repo id collision = TAT-005

---

1. Root cause

The table store exposed a PK-delete path that removed one matching row instead of all rows with that PK. With duplicate IDs present (legal, because POST has no upsert — TAT-044), the handler found the first match, spliced it out, wrote the table back, and returned 200. The remaining duplicate(s) were untouched, so the subsequent GET census still showed rows for that id. The HTTP status was therefore a false success signal.

Two independent facts made this look like a Tatara bug:

1. The duplicate-row state was created by Tatara/TAT-044 POST behavior (no idempotency), so the observable symptom was "Tatara data isn't deleting."
2. A deployed duckbrain-http.service built before a67512b still runs the old single-row delete, even after the repo is fixed. A stale deployment reproduces the defect exactly.

The DB-SUPA-3 rewrite changed the semantics to "delete every matching row" (rewrite the table excluding all rows whose PK equals the target), so duplicates are now repairable through the documented REST surface.

Illustrative before/after (shape of the fix in src/duckdb/table-store.ts):

// BEFORE (buggy): removes only the first match, duplicates survive
deleteRowsByPk(table: string, pk: string, id: unknown): number {
  const rows = this.readAll(table);
  const idx = rows.findIndex((r) => r[pk] === id);
  if (idx === -1) return 0;
  rows.splice(idx, 1);          // <-- only one row
  this.writeAll(table, rows);
  return 1;
}

// AFTER (a67512b): rewrite the table without ANY row matching the PK
deleteRowsByPk(table: string, pk: string, id: unknown): number {
  const rows = this.readAll(table);
  const kept = rows.filter((r) => r[pk] !== id);
  const removed = rows.length - kept.length;
  if (removed > 0) this.writeAll(table, kept);   // rewrite every surviving row
  return removed;
}

---

2. Do this first: gate on the deployed process age

Do not debug Tatara scripts until you have confirmed the running DuckBrain contains the fix. A pre-a67512b process makes the old behavior expected, and any Tatara investigation is wasted.

# 1. Is the service running, and how old is it?
systemctl --user status duckbrain-http.service --no-pager

# 2. Start time of the process actually listening on :3000
PID=$(ss -tlnp 'sport = :3000' | grep -oP 'pid=\K[0-9]+' | head -1)
echo "pid=$PID"; ps -o lstart=,etime=,cmd= -p "$PID"

# 3. When was deleteRowsByPk last changed in the repo?
git -C /path/to/duckbrain log -1 --format='%h %ci %s' -- src/duckdb/table-store.ts
git -C /path/to/duckbrain log --oneline -5 -- src/duckdb/table-store.ts

Decision rule:

• Deployed start time after a67512b's commit time → code is live; proceed to §3 verification.
• Deployed start time before it (or the process is older than the checkout) → stale deployment. Apply §2.1 and re-run the probe.

2.1 Deploy the fix

cd /path/to/duckbrain
git fetch && git checkout a67512b        # or a later commit containing it
# build/install per repo convention, then:
systemctl --user daemon-reload
systemctl --user restart duckbrain-http.service
systemctl --user status duckbrain-http.service --no-pager

If the user systemd manager itself is wedged ("Processes still around after final SIGKILL" / dead /run/user/1000/systemd/private), migrate the stuck cgroup tasks to a scratch cgroup, then systemctl --user reset-failed duckbrain-http.service and start it. This is a known environment failure mode; it is not the delete bug.

---

3. Verification

The probe is self-cleaning: seed two rows with the same id, confirm the duplicate exists, DELETE by PK, re-census, expect zero rows for that id, then remove the probe table/rows.

BASE=${DUCKBRAIN_URL:-http://localhost:3000}
NS=probe_ns
T=probe_delete_pk
ID=probe-dup-1

# 0. ensure namespace/table exists (adjust to your API)
curl -s -X POST "$BASE/api/ns/$NS/tables" -H 'content-type: application/json' \
  -d "{\"name\":\"$T\",\"pk\":\"id\"}" >/dev/null

# 1. seed the duplicate (two POSTs of the same id — this is TAT-044, intentionally)
curl -s -X POST "$BASE/api/ns/$NS/tables/$T" -H 'content-type: application/json' \
  -d "{\"id\":\"$ID\",\"v\":1}" >/dev/null
curl -s -X POST "$BASE/api/ns/$NS/tables/$T" -H 'content-type: application/json' \
  -d "{\"id\":\"$ID\",\"v\":2}" >/dev/null

# 2. census — expect n=2
curl -s "$BASE/api/ns/$NS/tables/$T?limit=1000" \
  | jq -c 'group_by(.id)[] | {id: .[0].id, n: length}'
# => {"id":"probe-dup-1","n":2}

# 3. DELETE by PK — expect HTTP 200
curl -s -o /dev/null -w 'delete_status=%{http_code}\n' \
  -X DELETE "$BASE/api/ns/$NS/tables/$T?pk=eq.$ID"

# 4. re-census — expect NO row for probe-dup-1
curl -s "$BASE/api/ns/$NS/tables/$T?limit=1000" \
  | jq -c 'group_by(.id)[] | select(.[0].id=="probe-dup-1") | {id: .[0].id, n: length}'
# => (no output)  == fixed

Pass criteria:

Observation	Verdict
Step 4 prints nothing (0 matching rows)	Fixed — all duplicates removed
Step 4 still prints {"id":"probe-dup-1","n":1}	Old single-row delete is live → §2.1
Step 3 returns non-200	Different defect; capture body/headers

3.1 Cleanup

curl -s -X DELETE "$BASE/api/ns/$NS/tables/$T?pk=eq.$ID" >/dev/null
curl -s -X DELETE "$BASE/api/ns/$NS/tables/$T" >/dev/null   # drop probe table
curl -s -X DELETE "$BASE/api/ns/$NS" >/dev/null              # drop probe namespace
curl -s "$BASE/api/ns/$NS/tables/$T?limit=1000" | jq 'length' # expect 0 / 404

---

4. Notes / boundaries

• Do not implement a Tatara-side "delete twice" or client retry loop as the fix. It masks the bug and still fails if the duplicate count is >2 or if rows are added between calls.
• TAT-044 (re-POST same id inserts a duplicate; no upsert) is a separate POST-side idempotency gap. After this fix it is recoverable via DELETE, but the correct long-term fix is a real upsert/ON CONFLICT on the POST path.
• TAT-005 (t-<sha12> cross-repo id collision) is unrelated to delete semantics; do not attribute duplicate-id survivors to it.
• If the fix is present in the repo but the probe still fails, suspect the deployment (stale process, wrong checkout, build not reinstalled) before reopening the code defect — that is the single most common cause of this problem class recurring.

# Evidence
- Problem class: duckbrain-delete-pk-duplicate-rows-noop
- Model: openrouter/deepseek/deepseek-v4.1-flash
- Solved: 2026-10-02T05:29:24.741Z
- Verification: solution produced by pi in sandbox; see signatures.json

{"description": "", "environment": "", "language": "", "model": "openrouter/deepseek/deepseek-v4.1-flash", "problem_class": "duckbrain-delete-pk-duplicate-rows-noop", "provider": "openrouter", "solved_at": "2026-10-02T05:29:24.741Z", "version": ""}