Schema sync is additive-only: non-additive metadata changes (required→optional, type, drop, rename) silently diverge from existing DBs; need drift detection + os migrate

[xuyushun441-sys]

Summary

The metadata→DB schema sync is additive-only: it creates new tables and adds new columns, but never alters or drops existing columns. Any non-additive metadata change (required↔optional, type/length, drop field, rename) silently diverges from an existing database — with no warning — and the physical column wins at write time. There is also no migration command to reconcile an existing DB.

This produces confusing, hard-to-diagnose failures: the served metadata says one thing, the write fails saying another.

Concrete repro (the one that surfaced this)

After #2178 made sys_business_unit.organization_id optional (required: false):

1. Existing dev DB created while the field was required: true → column is NOT NULL (PRAGMA table_info → notnull: 1).
2. git pull + pnpm build + restart os dev (code fully fixed).
3. GET /meta/object/sys_business_unit → organization_id required: false ✅ (reads metadata)
4. POST /data/sys_business_unit {name, kind} → HTTP 400 {"error":"organization_id is required","code":"VALIDATION_FAILED"} ❌

DB	organization_id column	create
existing (made under required:true)	NOT NULL (notnull=1)	❌ 400 "organization_id is required"
fresh	nullable (notnull=0)	✅ 201

Rebuilding code cannot fix it — the existing column constraint is never relaxed. A DB reset (or manual table rebuild) is currently the only workaround.

Root cause

• Additive-only sync. SqlDriver.initObjects (packages/plugins/driver-sql/src/sql-driver.ts:1446-1493): table missing → CREATE TABLE with full constraints (required → NOT NULL, see createColumn sql-driver.ts:2282); table exists → ALTER TABLE ADD COLUMN only for new fields. Existing columns are never touched (no nullability/type change, no drop, no rename). Runs at boot via ObjectQLPlugin syncRegisteredSchemas() (packages/objectql/src/plugin.ts:336,361) and on package install.
• Two sources of truth for "required". /meta reports required from the metadata (now false), but create-validation effectively enforces the physical column (NOT NULL). These must converge on metadata as the single source of truth.
• Silent. Nothing detects or reports that metadata and the physical schema have diverged.

Impact

• Dev: any required→optional / type / drop / rename change appears to "not work" after pulling + rebuilding; only a DB reset helps. Easy to mistake for stale code (it is not).
• Prod: non-additive metadata changes cannot be applied at all — no migration path, and auto-DDL would be unsafe anyway.

Current behavior (what syncs vs. not)

Metadata change	Existing DB on next boot
New object / new field / new index	✅ applied (additive)
required: true → false (relax NOT NULL)	❌ not applied
field type / length change	❌ not applied
field removed	❌ column kept (orphan)
field renamed	❌ old kept + new added

Proposed solution

Adopt a Prisma-style dev/prod split, with the metadata as the single source of truth and the physical schema reconciled to it:

• P1 — Drift detection + actionable warning (boot-time). After sync, diff metadata vs. actual columns (nullability, type, orphaned columns) and WARN with a concrete hint, e.g. sys_business_unit.organization_id: metadata optional but column is NOT NULL — run "os migrate". Turns silent divergence into a visible, actionable signal.
• P2 — Dev auto-reconcile, "loosen-only". Automatically apply non-destructive, cannot-fail alters: relax NOT NULL → NULL, widen varchar, add index. required → optional falls in this safe set, so existing dev DBs self-heal on restart. (Dev only / opt-in; never auto-DDL in prod.)
• P3 — os migrate command (required). Deliberate, reviewable migrations for the unsafe/destructive set (drop column, tighten constraint w/ backfill, narrow type, rename):
  • os migrate plan → dry-run diff categorized safe / needs-confirm / destructive;
  • os migrate apply → execute (destructive requires --allow-destructive), with backup/rollback;
  • SQLite: table-rebuild (copy → swap) since it can't alter constraints in place; Postgres: native ALTER.

Also fix the "required" source-of-truth inconsistency so validation follows metadata, not the physical column.

Acceptance criteria

• Boot logs a clear warning for every metadata↔physical schema divergence (P1).
• In dev, a required:true→false change is reflected in an existing DB after restart, no reset needed (P2).
• os migrate plan/apply reconciles non-additive changes with a preview + destructive guardrail (P3).
• Validation derives "required" from metadata, consistent with /meta.
• Works across SQLite + Postgres.

References

• Surfaced by fix(platform-objects): organization_id optional so BU/Team create in single-tenant (ADR-0057) #2178 (sys_business_unit/sys_team organization_id made optional — fix correct, but existing DBs still reject).
• packages/plugins/driver-sql/src/sql-driver.ts:1446-1493 (initObjects), :2282 (createColumn required→NOT NULL)
• packages/objectql/src/plugin.ts:336,361 (syncRegisteredSchemas at boot)
• packages/objectql/src/validation/rule-validator.ts:295, record-validator.ts:127 (the two "required" enforcement paths)