codegen: verify secondary belongs_to FK against tenant on create (diamond shape) — cross-tenant write reference

[Sorcecoder]

Summary

For a tenant-owned entity with two belongs_to references — one to the tenant path (verified via membership) and a second belongs_to to a sibling entity in the same tenant (the "diamond" shape, e.g. Meter belongs_to Org + Meter belongs_to Audit) — the generated create handler verifies only the tenant FK (membership). The secondary belongs_to FK value from the request body is written without verifying it belongs to the caller's tenant.

Result: a caller who is a member of org X can create a Meter in org X that references an Audit belonging to a foreign org Y, by supplying a foreign audit_id in the create body.

Impact / classification

• Write-side data-integrity, not a confidentiality leak. Reads remain tenant-scoped (WHERE org_id = ?), so a cross-org reference does not by itself expose org Y's rows through org X's read paths.
• A tenant boundary is crossed on the write path (a dangling cross-tenant FK reference), which is why it's worth tracking rather than dropping.
• General and pre-existing: applies to any non-tenant secondary belongs_to FK on a tenant-owned entity; not introduced by any specific recent change (surfaced while live-testing the JC0568 refuses a legitimate parent-fk subroute mount when the entity is ALSO directly tenant-owned (Org+parent diamond) #288 diamond).

Why it was not filed as a patch bug

All three Round-17 specialist lenses (security, codegen, runtime QA) reviewed the diamond and judged this below the security bar for a patch:

• security: dismissed as below-bar (write-side integrity, not a read leak),
• codegen: did not flag it as a codegen defect (create/openapi/migration/testgen all otherwise correct),
• runtime QA: live-tested the diamond, filed nothing.

The security lens explicitly asked that it be considered (not dropped) — hence this tracking issue.

Proposed direction (feature-shaped — next feature cycle, not a patch)

On create for a tenant-owned entity, verify each secondary belongs_to FK (i.e. every belongs_to other than the tenant path) resolves to a row in the same tenant before insert. Options to weigh during design:

1. Generated guard: emit a membership-scoped existence check for each secondary FK (SELECT 1 FROM {sibling} WHERE id = ? AND org_id = ?), 404/422 on miss — analogous to how the tenant FK is already membership-verified.
2. DB-level: composite FK / (id, org_id) reference so the database rejects a cross-tenant reference (requires the sibling to carry the tenant column + a composite unique).
3. Loud refusal (JC-class): if a design declares a secondary same-tenant belongs_to that codegen cannot verify, refuse at design-check time rather than emit an unverified write.

This is new validation codegen (a feature), so per the current release-versioning policy it belongs in the next feature release, not a 0.7.x patch.

Acceptance

• A tenant member cannot create a row whose secondary belongs_to FK points at a row in a foreign tenant (403/404/422, not 201).
• Existing single-belongs_to (tenant-only) entities are byte-identical (no regression).
• A generated test proves the cross-tenant secondary-FK create is rejected.