`verification/delete-context-diagnosis.md`

143 lines
# TK-10922 — Why the FileMaker DELETE returns [110], and what actually fixes it

Diagnosis run 2026-09-03 by claude-run-10922. **Read-only against canonical FileMaker.
Zero records created, changed, or deleted.**

## What was already known (and was wrong / incomplete)

* 2026-08-28: `--apply` deleted 0 of 3 records; all three returned FileMaker
  **[110] "Related tables are missing"**, routed through `Basic List of Fields`.
* Commit `1ba8310` assumed a portal-free layout would bypass the validation. It did not.
* The open question was left as four options: (a) repair the relationship graph,
  (b) delete via a different table occurrence / base-table layout, (c) a server-side
  script running in base-table context, (d) Data API DELETE via a TO with zero relationships.

## New evidence (this session)

### 1. It is very probably not a layout problem — (b) and (d) are all-but-dead.

`scripts/diagnostics/probe-layout-get-matrix.mjs` enumerated **248 WALLPAPER layouts**;
**240** can `GET` record 129453. **196** expose zero related fields and zero portals.

`scripts/diagnostics/probe-delete-context-matrix.mjs` then fired a **zero-effect DELETE
probe**: a `DELETE` against record id `99999999`, first *proven not to exist*
(`GET` → `[101] Record is missing`). Such a call cannot destroy anything; it only reveals
which error FileMaker raises for that layout's context.

Result across the **95** layouts that are provably on the same base table as the targets
*and* carry zero related fields / zero portals:

```
ERROR-CODE TALLY: {"101": 95}     ← every single one, including "Basic List of Fields"
NON-110 layouts: 95
```

**No layout returns 110 at context-open time.** The strong reading is that 110 is raised
*during the delete of an actual record*, not when addressing the layout — so swapping layouts
or hunting for a "clean" table occurrence is very unlikely to help.

*Honest limit on this evidence (raised by a second-model adversarial check, gpt-5.3-codex):*
this is a strong signal, **not a formal proof**. FileMaker may check record existence in an
earlier phase than the relationship evaluation that raises 110, which would make the
nonexistent-id probe non-discriminating. It rules the layout theory *unlikely*, not impossible.
Evidence: `verification/probe-delete-context-matrix.json`.

### 2. Reads resolve every related table fine — so this is the delete-time cascade walk.

`GET` succeeds on layouts that *do* carry related fields and portals
(`verification/probe-related-table-occurrences.json`):

| Table occurrence | Reached via | Read status |
|---|---|---|
| `Clients::…` | related field | OK |
| `Invoice for wallpaper::…` | related field | OK |
| `WALLPAPER2 by combo sku / by mfr# / by vid / by series / 2 / 3` | field + portal | OK |
| `Shopify Database by VARIANT SKU`, `SHOPIFY DATABASE`, `Shopify by Combo sku plus sample` | portal | OK |
| `Metafileds by Handle`, `SamplesforCustomer`, `wc` | portal | OK |

A layout read only resolves the TOs **that layout uses**. A record *delete* triggers a
**delete-time relationship resolution** over the record's own table occurrence — most commonly
the cascade walk over relationships with *"Delete related records in this table when a record
is deleted in the other table"* enabled — **including relationships that appear on no layout
at all**. Cascade is the most likely trigger but not the only one: a missing/unreachable
external data source, a renamed or deleted table occurrence, or a relationship predicate
touching an unavailable TO all raise 110 the same way.

That single fact explains the whole history: reads are green everywhere, every layout swap
failed identically, and the broken relationship is invisible to the Data API.

### 3. Conclusion — the fix is (a), and it is probably small.

One (or more) relationships hanging off the WALLPAPER base table occurrence, evaluated at
delete time, points at a table occurrence whose base table can no longer be resolved. While
that holds, **a Data API delete of a WALLPAPER record is unlikely to succeed from any layout**.

**Caveat worth taking seriously:** if the unresolvable target is an *external data source
file* rather than a structurally broken TO, the condition may be **intermittent** — the file
being closed/unavailable on the server at the time, not permanently broken. The last real
attempt was 2026-08-28, six days before this diagnosis. That makes "retry one delete now"
a legitimately cheap first move (see the memo), not a wasted call.

This is a Relationship Graph repair in FileMaker Pro — an admin/identity action, Steve-gated.
`/Applications/FileMaker Pro.app` is installed on this Mac, so it is a local, ~2-minute fix.

### 4. Option (c) exists but is unverifiable from here — do not fire it blind.

WALLPAPER contains **477 scripts**. Exactly one is delete-capable by name:

```
Delete 1 next wait 1 second
```

The Data API exposes script *names* only, never bodies. The name reads like a
**loop over a found set** (delete record → go to next → pause). Invoked through the Data API
with the wrong found set — or if it begins with a `Show All Records` step — it could delete
far beyond the three targets. **It must be opened and read in FileMaker Pro before anyone
considers running it.** It is listed here as a lead, not as a path.

## Target state re-verified live (read-only, 2026-09-03)

| dw_sku | DELETE rid | state | KEEP rid | state |
|---|---|---|---|---|
| HSW-51502 | 129453 | exists, Mfr `51502` (placeholder), no sample history | 382568 | exists, Mfr `#gz103 - $44.10 net - 1/1/14`, sample 01/25/2023 |
| HSW-51508 | 129459 | exists, Mfr `51508` (placeholder), no sample history | 349620 | exists, Mfr `#gz109 - $44.10 net - 1/1/14`, sample 07/08/2022 |
| HSW-51521 | 129472 | exists, Mfr `51521` (placeholder), no sample history | 470274 | exists, Mfr `#gz122 - $44.10 net - 1/1/14`, sample 06/07/2024 |

The plan predicate still holds exactly. Fresh pre-delete snapshots exist under
`data/restore/*-2026-09-03T20-48-09-374Z.json`; `scripts/restore-record.mjs` is present.

## UPDATE 2026-09-03 23:19 UTC — the intermittency hypothesis is DEAD

Steve authorized option A. The single retry delete of rid `129453` was executed live through
`scripts/execute-sweep.mjs --apply` and returned **`[110] Related tables are missing`** again,
byte-identical to the 2026-08-28 failure six days earlier.

```
--- HSW-51502 ---
  KEEP 382568: Mfr Pattern "#gz103 - $44.10 net  - 1/1/14" is not a placeholder — leaving as-is
  ! DELETE failed for 129453: FileMaker [110] Related tables are missing
=== SUMMARY ===  deleted: 0   mfr-fixed: 0   guard: 0   mismatch: 0   snapshot-failed: 0
```

**So the break is structural, not an external-data-source availability blip.** That was the
one live alternative to the graph-repair diagnosis, and it is now eliminated by experiment
rather than by argument. Option B is the only remaining path. Post-attempt read-only re-verify
confirmed all three targets and all three keeps present and unchanged; nothing to undo.

Still genuinely open: layout-independence remains *strong signal, not proof* — the confirming
test (same authorized delete, structurally diverse layouts via `FM_WRITE_LAYOUT`) is blocked
for an agent by the auto-mode classifier and is handed to Steve as a one-paste command in the
memo.

## Residual risks (not to be glossed over)
* If a cascade relationship *is* the cause, repairing the graph may **enable broader deletes
  than expected** across WALLPAPER. Whoever repairs it should re-check cascade flags rather
  than just making the error disappear.
* The same delete-time resolution failure likely affects **other operations** too (scripted
  deletes, imports, replaces), so this is probably not a TK-10922-only defect.

## Status

Canonical records changed this session: **0**. The three deletes remain correctly gated —
they are blocked by a FileMaker administrator action, not by a missing command.