validateRecord SKIP_FIELDS matches by name: required org_id on managed objects silently becomes NULL

[xuyushun441-sys]

Summary

validateRecord skips required-checks for any field literally named organization_id/tenant_id, on the assumption it is the engine-injected tenant-scope column. When an object has a genuine, user-supplied, required field with that name, the required-check is silently bypassed → the value reaches the DB as NULL (a silent integrity failure, not a clean 400).

Repro

POST /api/v1/data/sys_team with body { "name": "x" } (omitting organization_id):

NOT NULL constraint failed: sys_team.organization_id   (SQLITE_CONSTRAINT_NOTNULL)

Expected: 400 VALIDATION_FAILED { field: organization_id, code: required } from the validator — never reaching the driver.

Root cause

packages/objectql/src/validation/record-validator.ts:

const SKIP_FIELDS = new Set([
  'id','created_at','created_by','updated_at','updated_by',
  'organization_id','tenant_id',   // <- skipped BY NAME
]);
...
if (SKIP_FIELDS.has(name)) continue;   // required-check never runs for org_id

This is correct only when organization_id is engine-injected. But:

• sys_team.organization_id is a real required lookup to the parent org (Field.lookup('sys_organization', { required: true })).
• registry.ts:~189 skips system-field injection for managedBy: 'better-auth' tables, so it is not injected either.

Net: the required org is neither validated by the client nor injected by the engine → silent NULL → DB constraint. Any object with a legitimate organization_id/tenant_id field hits this.

Proposed change

Distinguish injected system columns from declared business fields by provenance, not by name. Set a flag at registration (e.g. field.system / field.injected: true) on columns the registry injects, and have validateRecord skip only those — never skip by hardcoded name. A genuinely declared organization_id field then gets normal required/type validation.

Why this is the most insidious of the set

Unlike the unknown-field case (loud error), this is a silent integrity failure — the row either errors at the DB by luck (NOT NULL present) or, worse, writes NULL where the column is nullable. It will recur on any object that legitimately names a field organization_id/tenant_id.

Related

• Pairs with managedBy enforcement — same injection boundary (linked below).
• Error-mapping safety net (branch fix/rest-map-schema-errors) currently maps this NOT NULL → 400, but that is the symptom; this issue is the cause.

Found running cloud LOCAL-E2E-CHECKLIST B7.

[xuyushun441-sys]

Related set — three rungs of "the data API must honor object metadata before hitting the DB", surfaced together by LOCAL-E2E-CHECKLIST B7:

• Data API: reject unknown payload fields (validateRecord ignores keys not in schema) #1590 — reject unknown payload fields (schema integrity)
• managedBy is not enforced: generic CRUD bypasses better-auth on sys_team (data-integrity / security) #1591 — enforce managedBy (who owns writes)
• validateRecord SKIP_FIELDS matches by name: required org_id on managed objects silently becomes NULL #1592 — provenance-aware system-field skip (silent NULL on real organization_id)

Landed as the error-mapping safety net: branch fix/rest-map-schema-errors (maps the symptoms to 4xx; these issues are the causes).