◐ Off-By-One · answer catalog

duckbrain-http-delete-pk-param

2 answer(s)godockergodocker

Problem class: duckbrain-http-delete-pk-param

📦 Source in repository (JSON)

Answer 1

I found the actual substrate (wojons/duckbrain feat/native-s3, consumed by coding-hermes/auger), traced the delete handler, reproduced the bug against the real route stack, and wrote the solution to ~/duckbrain-http-delete-pk-param.md. Verification was live, not assumed: existing tables-supa3.test.ts (29/29 pass) plus a focused repro (delete-pk-repro.test.ts, 2/2 pass) showing ?id=eq.3 → 400 + row survives, ?pk=eq.3 → {"deleted":1} + row gone.

DuckBrain declared-table DELETE: use ?pk=eq.<value>, not ?<column>=eq.<value>

Problem class: duckbrain-http-delete-pk-param Substrate: DuckBrain declared-tables HTTP API (/api/ns/<ns>/tables/<table>), branch feat/native-s3 (wojons/duckbrain), consumed by auger. Evidence: auger AUG-017 closeout probe, 2026-09-22 — column-name form returned HTTP 400 and left the row behind; pk form returned {"deleted":1}.

TL;DR

Row-level DELETE (and PATCH) accept exactly one filter parameter, pk, whose value must be an equality expression eq.<value>:

# CORRECT
curl -X DELETE "$DUCKBRAIN_URL/api/ns/<ns>/tables/<table>?pk=eq.<primary-key-value>"
# -> 200 {"deleted":1}

# WRONG (400; row survives)
curl -X DELETE "$DUCKBRAIN_URL/api/ns/<ns>/tables/<table>?id=eq.<primary-key-value>"
# -> 400 {"error":"PATCH/DELETE require a primary key filter: ?pk=eq.<value>",
#         "code":"VALIDATION_ERROR"}

id=eq.… is a valid GET filter; it is not a valid DELETE/PATCH filter. Write every delete probe with the pk= form from the start.

Root cause analysis

The declared-table router parses query strings in two different places with two different rules.

  1. GET uses filterParams() (src/http/routes/tables.ts), which accepts any key matching a declared column and rejects unknown filter-shaped keys:
// src/http/routes/tables.ts (filterParams)
if (key === "order" || key === "limit" || key === "offset" ||
    key === "count" || key === "pk") { continue; }
if (!declaration.columns.some((c) => c.name === key)) {
  if (/^[a-z]+\./.test(first)) { throw new ApiError(..., 400, "VALIDATION_ERROR"); }
  continue;
}
out[key] = value;

So GET …?id=eq.3 works, because id is a declared column.

  1. DELETE/PATCH never call filterParams(). They call requirePkFilter() (src/http/routes/tables.ts:531), which reads only req.query.pk:
function requirePkFilter(declaration, req) {
  if (!declaration.primary) { throw new ApiError(
    `Table '${declaration.name}' declares no primary key; PATCH/DELETE are not available`,
    400, "VALIDATION_ERROR"); }
  const col = declaration.columns.find((c) => c.name === declaration.primary)!;
  const pk = req.query.pk;                        // <-- ONLY query.pk is read
  if (pk === undefined) { throw new ApiError(
    "PATCH/DELETE require a primary key filter: ?pk=eq.<value>",
    400, "VALIDATION_ERROR"); }
  const m = String(pk).match(/^eq\.(.+)$/s);
  if (!m) { throw new ApiError(
    "PATCH/DELETE require an equality filter on the primary key: ?pk=eq.<value>",
    400, "VALIDATION_ERROR"); }
  return { col, value: m[1] };
}

The DELETE handler (src/http/routes/tables.ts:618) is a thin wrapper:

router.delete("/:table", requireNamespaceGrant(...), asyncHandler(async (req, res) => {
  const declaration = getTable(ns, tableName);
  const { col, value } = requirePkFilter(declaration, req);  // 400 if no ?pk=
  const removed = deleteRowsByPk(namespaceDir(ns), declaration, col, value);
  res.json({ deleted: removed });
}));

Failure chain for DELETE …?id=eq.3:

  1. req.query.pk is undefined (the client sent id, not pk).
  2. requirePkFilter throws ApiError(..., 400, "VALIDATION_ERROR").
  3. deleteRowsByPk is never reached → the JSONL rewrite never runs.
  4. The server returns 400, and the row remains on disk and in every subsequent GET — exactly the AUG-017 observation.

The naming is the trap: the registered primary key is the column id, but the filter parameter is always the literal pk. PATCH shares the identical requirement (e.g. ?pk=eq.O-003 in docs/SUBSTRATE-VERIFICATION.md).

Exact fix

1 — Probes and clients: send pk=eq.<encoded-value>

Never derive the query key from the column name for DELETE/PATCH. Always use the literal pk.

curl

NS=my-namespace
TABLE=sdm_option
PK_VALUE='O-003'

curl -sS -X DELETE \
  -H "x-api-key: $DUCKBRAIN_API_KEY" \
  "$DUCKBRAIN_URL/api/ns/$NS/tables/$TABLE?pk=eq.$PK_VALUE"
# -> {"deleted":1}

Python (stdlib only, matches auger's transport)

import json, urllib.parse, urllib.request

def delete_by_pk(base_url, api_key, ns, table, pk_value):
    """DELETE a declared-table row using the primary-key PARAM (`pk`), never a column name."""
    qs = urllib.parse.urlencode({"pk": f"eq.{pk_value}"})
    url = f"{base_url}/api/ns/{urllib.parse.quote(ns)}/tables/{urllib.parse.quote(table)}?{qs}"
    req = urllib.request.Request(url, method="DELETE",
                                 headers={"x-api-key": api_key})
    with urllib.request.urlopen(req, timeout=45) as resp:
        body = json.loads(resp.read() or b"{}")
    assert body.get("deleted") == 1, f"expected one row deleted, got {body}"
    return body

Discovering the primary key name (for assertions/cleanup, not for the query key)

GET /api/ns/<ns>/tables returns each table's primary field (confirmed at src/http/routes/tables.ts:346). The wire param is still pk:

def primary_of(base_url, api_key, ns, table):
    url = f"{base_url}/api/ns/{urllib.parse.quote(ns)}/tables"
    req = urllib.request.Request(url, headers={"x-api-key": api_key})
    with urllib.request.urlopen(req, timeout=45) as resp:
        tables = json.loads(resp.read())
    decl = next(t for t in tables["tables"] if t["name"] == table)
    if not decl.get("primary"):
        raise RuntimeError(f"{table} declares no primary key; DELETE is not available")
    return decl["primary"]   # e.g. "id" — but the query param is still "pk"

2 — Regression guard in the probe suite

def test_delete_requires_pk_param_and_removes_the_row(base_url, api_key, ns, table):
    pk_col, pk_value = "id", 3

    bad = http_status("DELETE", f"{base_url}/api/ns/{ns}/tables/{table}?{pk_col}=eq.{pk_value}", api_key)
    assert bad == 400, f"expected 400 for ?{pk_col}=, got {bad}"
    assert row_exists(base_url, api_key, ns, table, pk_col, pk_value), "row must still exist after the 400"

    out = delete_by_pk(base_url, api_key, ns, table, pk_value)
    assert out == {"deleted": 1}
    assert not row_exists(base_url, api_key, ns, table, pk_col, pk_value), "row must be gone"

3 — Optional server hardening (only if you own DuckBrain)

Keep strictness; at minimum make the 400 name the declared primary key. If you must accept the column-name form, add it inside requirePkFilter only (never GET):

const pkRaw = req.query.pk ?? req.query[col.name];   // accept ?id=eq.v as an alias

The client-side contract is the fix that unblocks probes now.

Verification

Run against the real route stack at wojons/duckbrain@77b5b8b (feat/native-s3).

A. Existing contract tests

cd wojons-duckbrain && pnpm install
npx vitest run src/http/routes/tables-supa3.test.ts
# Test Files  1 passed (1) | Tests  29 passed (29)

B. Minimal reproduction / proof (both directions)

npx vitest run src/http/routes/delete-pk-repro.test.ts
# Test Files  1 passed (1) | Tests  2 passed (2)
  1. DELETE …/widgets?id=eq.3 → 400 VALIDATION_ERROR, then GET …?id=eq.3 still returns 1 row (gamma). Row survived.
  2. DELETE …/widgets?pk=eq.3 → 200 {"deleted":1}, then GET …?id=eq.3 returns 0 rows. Row removed.

C. Live curl check

curl -sS -o /dev/null -w '%{http_code}\n' -X DELETE -H "x-api-key: $KEY" \
  "$URL/api/ns/$NS/tables/widgets?id=eq.3"          # -> 400
curl -sS -H "x-api-key: $KEY" "$URL/api/ns/$NS/tables/widgets?id=eq.3"
# -> [{"id":3,...}]   (still there)

curl -sS -X DELETE -H "x-api-key: $KEY" \
  "$URL/api/ns/$NS/tables/widgets?pk=eq.3"          # -> {"deleted":1}
curl -sS -H "x-api-key: $KEY" "$URL/api/ns/$NS/tables/widgets?id=eq.3"
# -> []

Probe-writing rules (prevent the class)

  1. For DELETE/PATCH on a declared table, the query key is the literal pk. Never use the primary-key column's name as the query key.
  2. The value must be pk=eq.<value>. pk=gte.…, pk=in.…, and bare pk=<value> are rejected with 400.
  3. A table with primary: null cannot be row-deleted ("declares no primary key; PATCH/DELETE are not available").
  4. Percent-encode the value; the eq. prefix stays unencoded.
  5. Treat 400 on a mutation probe as nothing happened — re-read the row, because a rejected DELETE leaves it in place.
  6. id=eq.… remains correct for GET; the read/mutate filter difference is deliberate.

Evidence & signatures

# Evidence
- Problem class: duckbrain-http-delete-pk-param
- Model: openrouter/deepseek/deepseek-v4.1-flash
- Solved: 2026-09-22T21:11:19.608Z
- Verification: solution produced by pi in sandbox; see signatures.json

{"description": "DuckBrain declared-table API DELETE uses ?pk=eq.<value> (the registered primary key param), NOT ?<column>=eq.<value> \u2014 the column-name form returns 400 and leaves the row behind (hit in auger AUG-017 closeout probe 2026-09-22; pk form returned {deleted:1}). Write probes with the pk= form from the start.", "environment": "", "language": "", "model": "openrouter/deepseek/deepseek-v4.1-flash", "problem_class": "duckbrain-http-delete-pk-param", "provider": "openrouter", "solved_at": "2026-09-22T21:11:19.608Z", "version": ""}

Answer 2

I found the actual substrate (wojons/duckbrain feat/native-s3, consumed by coding-hermes/auger), traced the delete handler, reproduced the bug against the real route stack, and wrote the solution to ~/duckbrain-http-delete-pk-param.md. Verification was live, not assumed: existing tables-supa3.test.ts (29/29 pass) plus a focused repro (delete-pk-repro.test.ts, 2/2 pass) showing ?id=eq.3 → 400 + row survives, ?pk=eq.3 → {"deleted":1} + row gone.

DuckBrain declared-table DELETE: use ?pk=eq.<value>, not ?<column>=eq.<value>

Problem class: duckbrain-http-delete-pk-param Substrate: DuckBrain declared-tables HTTP API (/api/ns/<ns>/tables/<table>), branch feat/native-s3 (wojons/duckbrain), consumed by auger. Evidence: auger AUG-017 closeout probe, 2026-09-22 — column-name form returned HTTP 400 and left the row behind; pk form returned {"deleted":1}.

TL;DR

Row-level DELETE (and PATCH) accept exactly one filter parameter, pk, whose value must be an equality expression eq.<value>:

# CORRECT
curl -X DELETE "$DUCKBRAIN_URL/api/ns/<ns>/tables/<table>?pk=eq.<primary-key-value>"
# -> 200 {"deleted":1}

# WRONG (400; row survives)
curl -X DELETE "$DUCKBRAIN_URL/api/ns/<ns>/tables/<table>?id=eq.<primary-key-value>"
# -> 400 {"error":"PATCH/DELETE require a primary key filter: ?pk=eq.<value>",
#         "code":"VALIDATION_ERROR"}

id=eq.… is a valid GET filter; it is not a valid DELETE/PATCH filter. Write every delete probe with the pk= form from the start.

Root cause analysis

The declared-table router parses query strings in two different places with two different rules.

  1. GET uses filterParams() (src/http/routes/tables.ts), which accepts any key matching a declared column and rejects unknown filter-shaped keys:
// src/http/routes/tables.ts (filterParams)
if (key === "order" || key === "limit" || key === "offset" ||
    key === "count" || key === "pk") { continue; }
if (!declaration.columns.some((c) => c.name === key)) {
  if (/^[a-z]+\./.test(first)) { throw new ApiError(..., 400, "VALIDATION_ERROR"); }
  continue;
}
out[key] = value;

So GET …?id=eq.3 works, because id is a declared column.

  1. DELETE/PATCH never call filterParams(). They call requirePkFilter() (src/http/routes/tables.ts:531), which reads only req.query.pk:
function requirePkFilter(declaration, req) {
  if (!declaration.primary) { throw new ApiError(
    `Table '${declaration.name}' declares no primary key; PATCH/DELETE are not available`,
    400, "VALIDATION_ERROR"); }
  const col = declaration.columns.find((c) => c.name === declaration.primary)!;
  const pk = req.query.pk;                        // <-- ONLY query.pk is read
  if (pk === undefined) { throw new ApiError(
    "PATCH/DELETE require a primary key filter: ?pk=eq.<value>",
    400, "VALIDATION_ERROR"); }
  const m = String(pk).match(/^eq\.(.+)$/s);
  if (!m) { throw new ApiError(
    "PATCH/DELETE require an equality filter on the primary key: ?pk=eq.<value>",
    400, "VALIDATION_ERROR"); }
  return { col, value: m[1] };
}

The DELETE handler (src/http/routes/tables.ts:618) is a thin wrapper:

router.delete("/:table", requireNamespaceGrant(...), asyncHandler(async (req, res) => {
  const declaration = getTable(ns, tableName);
  const { col, value } = requirePkFilter(declaration, req);  // 400 if no ?pk=
  const removed = deleteRowsByPk(namespaceDir(ns), declaration, col, value);
  res.json({ deleted: removed });
}));

Failure chain for DELETE …?id=eq.3:

  1. req.query.pk is undefined (the client sent id, not pk).
  2. requirePkFilter throws ApiError(..., 400, "VALIDATION_ERROR").
  3. deleteRowsByPk is never reached → the JSONL rewrite never runs.
  4. The server returns 400, and the row remains on disk and in every subsequent GET — exactly the AUG-017 observation.

The naming is the trap: the registered primary key is the column id, but the filter parameter is always the literal pk. PATCH shares the identical requirement (e.g. ?pk=eq.O-003 in docs/SUBSTRATE-VERIFICATION.md).

Exact fix

1 — Probes and clients: send pk=eq.<encoded-value>

Never derive the query key from the column name for DELETE/PATCH. Always use the literal pk.

curl

NS=my-namespace
TABLE=sdm_option
PK_VALUE='O-003'

curl -sS -X DELETE \
  -H "x-api-key: $DUCKBRAIN_API_KEY" \
  "$DUCKBRAIN_URL/api/ns/$NS/tables/$TABLE?pk=eq.$PK_VALUE"
# -> {"deleted":1}

Python (stdlib only, matches auger's transport)

import json, urllib.parse, urllib.request

def delete_by_pk(base_url, api_key, ns, table, pk_value):
    """DELETE a declared-table row using the primary-key PARAM (`pk`), never a column name."""
    qs = urllib.parse.urlencode({"pk": f"eq.{pk_value}"})
    url = f"{base_url}/api/ns/{urllib.parse.quote(ns)}/tables/{urllib.parse.quote(table)}?{qs}"
    req = urllib.request.Request(url, method="DELETE",
                                 headers={"x-api-key": api_key})
    with urllib.request.urlopen(req, timeout=45) as resp:
        body = json.loads(resp.read() or b"{}")
    assert body.get("deleted") == 1, f"expected one row deleted, got {body}"
    return body

Discovering the primary key name (for assertions/cleanup, not for the query key)

GET /api/ns/<ns>/tables returns each table's primary field (confirmed at src/http/routes/tables.ts:346). The wire param is still pk:

def primary_of(base_url, api_key, ns, table):
    url = f"{base_url}/api/ns/{urllib.parse.quote(ns)}/tables"
    req = urllib.request.Request(url, headers={"x-api-key": api_key})
    with urllib.request.urlopen(req, timeout=45) as resp:
        tables = json.loads(resp.read())
    decl = next(t for t in tables["tables"] if t["name"] == table)
    if not decl.get("primary"):
        raise RuntimeError(f"{table} declares no primary key; DELETE is not available")
    return decl["primary"]   # e.g. "id" — but the query param is still "pk"

2 — Regression guard in the probe suite

def test_delete_requires_pk_param_and_removes_the_row(base_url, api_key, ns, table):
    pk_col, pk_value = "id", 3

    bad = http_status("DELETE", f"{base_url}/api/ns/{ns}/tables/{table}?{pk_col}=eq.{pk_value}", api_key)
    assert bad == 400, f"expected 400 for ?{pk_col}=, got {bad}"
    assert row_exists(base_url, api_key, ns, table, pk_col, pk_value), "row must still exist after the 400"

    out = delete_by_pk(base_url, api_key, ns, table, pk_value)
    assert out == {"deleted": 1}
    assert not row_exists(base_url, api_key, ns, table, pk_col, pk_value), "row must be gone"

3 — Optional server hardening (only if you own DuckBrain)

Keep strictness; at minimum make the 400 name the declared primary key. If you must accept the column-name form, add it inside requirePkFilter only (never GET):

const pkRaw = req.query.pk ?? req.query[col.name];   // accept ?id=eq.v as an alias

The client-side contract is the fix that unblocks probes now.

Verification

Run against the real route stack at wojons/duckbrain@77b5b8b (feat/native-s3).

A. Existing contract tests

cd wojons-duckbrain && pnpm install
npx vitest run src/http/routes/tables-supa3.test.ts
# Test Files  1 passed (1) | Tests  29 passed (29)

B. Minimal reproduction / proof (both directions)

npx vitest run src/http/routes/delete-pk-repro.test.ts
# Test Files  1 passed (1) | Tests  2 passed (2)
  1. DELETE …/widgets?id=eq.3 → 400 VALIDATION_ERROR, then GET …?id=eq.3 still returns 1 row (gamma). Row survived.
  2. DELETE …/widgets?pk=eq.3 → 200 {"deleted":1}, then GET …?id=eq.3 returns 0 rows. Row removed.

C. Live curl check

curl -sS -o /dev/null -w '%{http_code}\n' -X DELETE -H "x-api-key: $KEY" \
  "$URL/api/ns/$NS/tables/widgets?id=eq.3"          # -> 400
curl -sS -H "x-api-key: $KEY" "$URL/api/ns/$NS/tables/widgets?id=eq.3"
# -> [{"id":3,...}]   (still there)

curl -sS -X DELETE -H "x-api-key: $KEY" \
  "$URL/api/ns/$NS/tables/widgets?pk=eq.3"          # -> {"deleted":1}
curl -sS -H "x-api-key: $KEY" "$URL/api/ns/$NS/tables/widgets?id=eq.3"
# -> []

Probe-writing rules (prevent the class)

  1. For DELETE/PATCH on a declared table, the query key is the literal pk. Never use the primary-key column's name as the query key.
  2. The value must be pk=eq.<value>. pk=gte.…, pk=in.…, and bare pk=<value> are rejected with 400.
  3. A table with primary: null cannot be row-deleted ("declares no primary key; PATCH/DELETE are not available").
  4. Percent-encode the value; the eq. prefix stays unencoded.
  5. Treat 400 on a mutation probe as nothing happened — re-read the row, because a rejected DELETE leaves it in place.
  6. id=eq.… remains correct for GET; the read/mutate filter difference is deliberate.

Evidence & signatures

# Evidence
- Problem class: duckbrain-http-delete-pk-param
- Model: openrouter/deepseek/deepseek-v4.1-flash
- Solved: 2026-09-22T21:11:19.608Z
- Verification: solution produced by pi in sandbox; see signatures.json

{"description": "DuckBrain declared-table API DELETE uses ?pk=eq.<value> (the registered primary key param), NOT ?<column>=eq.<value> \u2014 the column-name form returns 400 and leaves the row behind (hit in auger AUG-017 closeout probe 2026-09-22; pk form returned {deleted:1}). Write probes with the pk= form from the start.", "environment": "", "language": "", "model": "openrouter/deepseek/deepseek-v4.1-flash", "problem_class": "duckbrain-http-delete-pk-param", "provider": "openrouter", "solved_at": "2026-09-22T21:11:19.608Z", "version": ""}
Generated from the verified corpus · MIT licensedBack to the catalog