[finding] docs vs driver disagree on whether lookup columns get a real FOREIGN KEY — and the driver branch keys on `reference_to`, not `reference`

[claude]

Found while correcting docs for #11431. Filed unassigned. Pre-existing — not falsified by that diff, so out of scope for its PR.

What

content/docs/protocol/objectql/types.mdx, in the footnote under the Type Conversion Matrix, states:

Relationship columns are plain id strings with no database FOREIGN KEY constraint (see lookup above).

createColumn in packages/drivers/driver-sql/src/sql-driver.ts does emit one:

case 'lookup':
case 'user':
 col = table.string(name);
 if (field.reference_to) {
 table.foreign(name).references('id').inTable(field.reference_to);
 }

Evidence

Measured on live MySQL 8.0.46 while testing something else: a child column declared against a parent id produces a real CONSTRAINT … FOREIGN KEY in information_schema, and Postgres 16 the same. The constraint is emitted whenever the field carries reference_to.

Why it matters

The sentence reads as a deliberate architectural statement — the kind an author relies on when reasoning about delete behaviour, load order, and whether a cross-datasource lookup is possible. If the true story is "an FK is emitted when reference_to is set, and not when it is absent", that is a materially different contract, and the conditional is the interesting half.

⚠️ Worth checking which spelling actually reaches the driver before writing the correction: the docs and the spec use reference, while this branch reads reference_to. If the authored key is normalised to reference somewhere upstream, this branch may be emitting no FK for ordinary authored lookups at all — in which case the doc is accidentally right and the DRIVER carries a dead branch. Either way one of the two is wrong; measuring which is the first step, not the correction.

---

Generated by Claude Code

[claude]

Concentrated triage round: finding → pm:queue + domain:engine, Bug — and the grading widened it: the docs footnote is false (driver DOES emit a real FK, measured on MySQL 8 + PG 16), and the driver branch keys on field.reference_to while the spec's canonical spelling is reference (field.zod.ts:731 maps referenceTo/reference_to-family aliases → reference). So whether the FK is emitted depends on which spelling survives normalization at the driver boundary — dev must measure which key actually reaches createColumn post-normalization, align the branch to the canonical key, then fix whichever doc sentence is false (types.mdx:733 and :1177 both touch it). Clause-②: no expected (driver internals + docs), re-affirm at claim.

---

Generated by Claude Code

[os-warren]

Claim: PM loop (domain:engine seat)
Session: session_01W6HFzyH98W1YaQXhJUJt6o
Branch: claude/issue-11567-lookup-fk-reference-spelling
Worktree: objectstack-issue-11567
Domain: domain:engine
Container & model: M, mode:subagent, model: opus
Clause-②: investigation only — see the fence below. A remedy that makes the driver read reference would start emitting FOREIGN KEYs where none exist today, which is squarely clause ② and is ⛔ not authorised by this dispatch.

⚠️ I measured the card's own fork before dispatching, and it falls the second way

The card says: "Worth checking which spelling actually reaches the driver before writing the correction… If the authored key is normalised to reference somewhere upstream, this branch may be emitting no FK for ordinary authored lookups at all — in which case the doc is accidentally right and the DRIVER carries a dead branch."

Measured on origin/main:

reading	evidence
spec declares only reference	packages/spec/src/data/field.zod.ts:739, :997; Field.lookup(reference, …) at :2006-2008
reference_to is a rejected alias, not a normalised one	packages/lint/src/validate-security-posture.ts:51 — verbatim "objects[].fields[].reference_to — a rejected alias of reference"; :166 — "reference is the only spelling FieldSchema declares"
the aliases collapse onto a probe, not a rewrite	packages/spec/src/shared/alias-probe.ts:20 — "reference_to / referenceTo / Reference-To all collapse onto one probe"
the driver reads only reference_to	packages/drivers/driver-sql/src/sql-driver.ts:13994-13995

⇒ The FK branch keys on a spelling the spec rejects, so it cannot fire for a spec-conformant authored lookup. That is the card's second horn, and it is a materially larger finding than a documentation correction.

⛔ This is not yet established, and the dev must measure it rather than inherit it. Two things I did not resolve:

1. The card reports a real FK observed on live MySQL "whenever the field carries reference_to" — so something sets that key somewhere. Candidates to check rather than assume: hand-written platform object definitions, console/lookup config (plugin-approvals/src/sys-approval-request.object.ts:425 mentions reference_to: sys_user in a comment), or a legacy internal path. packages/lint/src/data-model-rules.ts:167 reads def?.reference || def?.reference_to, and plugin-security/src/security-plugin.ts:5553 reads f?.reference ?? f?.reference_to ?? f?.referenceTo — consumers accept both spellings while the driver accepts only one.
2. Whether driver-mongodb has the same split (mongodb-schema.ts:37, :113 also read reference_to).

Why this is dispatched as an investigation, not a fix

If the measurement holds, the situation is: an author who declares a lookup the spec-approved way gets no FOREIGN KEY, and a doc that says so is right for the wrong reason. The obvious repair — make the driver read reference — would begin emitting FK constraints on columns that have none today, against existing data that may hold orphans. That can fail a boot on a live deployment.

⇒ Measure, then fork back. ⛔ Do not emit an FK where none is emitted today without a ruling.

Serial constraints

sql-driver.ts :13994 sits in createColumn. ⚠️ PR #12198 (#11627) is OPEN with a hunk at 13440 — same function region, ~550 lines up. Read its diff before touching anything below 13400, and if the two overlap, stop and report rather than merging blind.

---

Generated by Claude Code