# Issue #1582 — Root Cause RFC & Living Plan

> Living document. Updated as I verify more / implement. Status block at the top reflects the current best-understood state.

---

## Status

| Field | Value |
|---|---|
| Last updated | 2026-04-22 (post-v1.5 regression discovery + metadata-driven re-alignment) — **revision 1.6** (ARCHITECTURAL REDIRECTION) |
| Phase | 🔄 v1.5 implementation encountered a class of bug the v1.5 design cannot prevent by construction: **writer-forgot-to-stamp-Source**, proven by the `CreateSchema` path regression (67/71 existing custom objects stuck at `Source='discovered'`, new custom objects hit the same defect). v1.6 redesigns `Source` / `IsCompound` / `ComposedFrom` as **derived-on-read** pure functions instead of stored columns. See §1.6 below before reading any v1.5 §3.1 / §3.5-A1 / §3.6 / §3.7 Source-row content — those are **superseded**. |
| Confidence | 95% — the redesign is smaller (~300 LOC vs v1.5's ~1700), satisfies **every AC strictly** including AC-4 which v1.5 explicitly admitted it only partially passed, and eliminates an entire class of bug (writer-consistency for provenance) by construction. Residual 5% = getting PR ordering right on top of the partially-landed v1.5 schema. |
| Owner | Claude (implementation agent) |
| Related | #656 (original), #1565 (first attempted fix), #1574 (metadata-driven anti-pattern reference — reverted), #1612 (metadata-driven architecture section added to CLAUDE.md), #1582 (this regression) |

---

## Revision 1.6 — Architectural Redirection (2026-04-22)

### Why v1.5 gets redirected

v1.5 implementation surfaced a regression v1.5's design **cannot prevent by construction**:

- `CreateSchema` API (admin creates a new custom object) writes rows to `_System_Field` via `PrepareTableDefinition` → `schema_helpers.go`. v1.5 relied on the "column DEFAULT 'discovered' + BackfillSource idiom" to classify raw-SQL writers; it **inventoried only four raw-SQL bypass paths** (§3.1) and missed the fifth: `CreateSchema` is a *structured* writer that *still doesn't stamp Source*.
- Result after v1.5 A1 + A2 landed: every newly-created custom object has all its fields (Name, Id, CreatedDate, OwnerId, audit) landing at `Source='discovered'` → Layer 1d → `Createable=false` → `pruneNonCreateableFields` strips Name on insert → validator rejects with "Name required" → hard-lock. Verified end-to-end via dev-browser (local TiDB: new creation fails identically; staging TiDB Cloud: **67 of 71 existing custom objects** already have `Name.Source='discovered'` from the partial v1.5 migration).
- This is **the same class of bug #1582 is trying to root-fix**: declaration-less state surviving into a layer that treats absent declaration as "not createable." v1.5 moved the class of bug from "legacy SF imports" to "CreateSchema writer omissions" without eliminating it.

### The deeper problem v1.5 has

`Source` / `IsCompound` / `ComposedFrom` are **derived facts about a `_System_Field` row**, not first-class admin-editable state:

- `Source` is provenance — "which L1 declaration path authored this row?" It is **deterministic** given the L1 manifests (`system_tables.json`, `standard_objects_spec.json`, `tenant_objects_spec.json`, `GetStandardFieldMetadata()` template, physical-column presence, APIName suffix).
- `IsCompound` / `ComposedFrom` are declaration attributes — they live in the pack spec JSON already.

v1.5 chose to **store these as columns** and maintain consistency via writer-stamping (4 raw-SQL DEFAULT + structured writers explicit) + runtime cache-refresh (`BackfillSource`) + bootstrap assertion (`AssertSourceIntegrity`). This is the classic **stored-derived-data** trap:

1. Every writer must remember to stamp — one miss → silent wrong state (today's regression)
2. Cache-refresh code (`BackfillSource`) becomes permanent engine code — L0 inspect-and-branch
3. Consistency assertion (`AssertSourceIntegrity`) becomes permanent engine code
4. Historical rows need one-shot heal when any rule changes (a data-spec — which is itself a metadata-driven anti-pattern per CLAUDE.md `#metadata-driven`: engineer-authored SQL bypasses the L0 engine surface)
5. RFC's own §3.7 admits `Source` is only a "partial map" to SF `manageableState` and `discovered` / `bootstrap_seed` have "no SF analog" — **AC-4 is openly violated** ("We make this concession to preserve staging data; we don't claim it's SF-correct.")

v1.5 also has an internal architectural tension: §3.6 states **`Createable` is a derived cache, `ComputeIntrinsic` is the live authority** — but it then asks `ComputeIntrinsic` Layer 1d/1e to read the **stored** `Source`/`IsCompound` columns. Createable is derived-on-read over Source; Source itself is stored-with-cache-refresh. Two cache layers. Today's regression is exactly the failure mode this stacked-cache creates.

### The v1.6 design in one sentence

**Do not store `Source` / `IsCompound` / `ComposedFrom` on `_System_Field`. Derive them at read time by pure functions that read L1 declaration manifests.**

### The two pure functions

```go
// shared/pkg/fieldprovenance/source.go
//
// ClassifySource derives a field's provenance (which L1 declaration path
// authored it) from in-memory L1 manifest indices + structural attributes.
// Pure function — no writes, no side effects, no stored cache to stale.
func ClassifySource(objectAPIName, fieldAPIName string) FieldSource {
    // 0. Admin acknowledgment override (future: "promote discovered → custom" UI).
    //    Reads _System_FieldDeclarationAck (an explicit admin-state table, L2).
    if ack := adminAckLookup(objectAPIName, fieldAPIName); ack != nil {
        return ack.Source
    }
    // 1. L1 manifest declarations (in-memory indices, loaded once on boot).
    if isDeclaredInSystemTables(objectAPIName, fieldAPIName) {
        return FieldSourceManaged
    }
    if isDeclaredInStandardObjectsSpec(objectAPIName, fieldAPIName) {
        return FieldSourceStandardSpec
    }
    if isDeclaredInTenantObjectsSpec(objectAPIName, fieldAPIName) {
        return FieldSourceTenantSpec
    }
    // 2. Platform standard-field template (Name / Id / audit, injected into
    //    every custom object by EnrichWithSystemFields).
    if isPlatformStandardField(fieldAPIName) {
        return FieldSourceManaged
    }
    // 3. L2 naming convention for admin-created custom fields.
    if strings.HasSuffix(fieldAPIName, "__c") {
        return FieldSourceCustom
    }
    // 4. Physical-first without any declaration path.
    return FieldSourceDiscovered
}

// shared/pkg/fieldprovenance/compound.go
//
// ClassifyCompound derives compound parenthood from the same L1 manifests.
// Pure function.
func ClassifyCompound(objectAPIName, fieldAPIName string) (bool, []string) {
    for _, manifest := range l1Manifests {
        if cf := manifest.lookupCompound(objectAPIName, fieldAPIName); cf != nil {
            return true, cf.ComposedFrom
        }
    }
    return false, nil
}
```

`ComputeIntrinsic` Layer 1d/1e now calls these classifiers directly (not `f.Source` / `f.IsCompound` fields on `FieldMetadata`):

```go
if ClassifySource(objectAPIName, f.APIName) == FieldSourceDiscovered {
    return false, false
}
if isCompound, _ := ClassifyCompound(objectAPIName, f.APIName); isCompound {
    return false, false
}
```

### What this removes vs v1.5

| v1.5 artifact | v1.6 status |
|---|---|
| `ALTER TABLE _System_Field ADD COLUMN Source / IsCompound / ComposedFrom` | **Removed.** New migration: `DROP COLUMN` if they've already landed in staging; otherwise never add. |
| `BackfillSource` bootstrap step | **Removed.** No stored column to backfill. |
| `BackfillFieldCapabilities` SELECT/struct extended to populate `Source`/`IsCompound` | **Removed.** BFC still exists as a `Createable` cache refresher; it calls `ComputeIntrinsic` which calls the new classifiers; no column propagation needed. |
| `schema_helpers.go` Source/IsCompound/ComposedFrom INSERT logic + `discovered` fallback | **Removed.** No column to insert. |
| `field_create.go` explicit `Source: FieldSourceCustom` | **Removed.** |
| `platform_core.go` pack apply explicit `Source: FieldSourceStandardSpec` | **Removed.** |
| `CreateSchema` handler / `PrepareTableDefinition` — would have needed Source-stamping fix | **Never needed.** The bug class doesn't exist under derived-on-read. |
| `AssertSourceIntegrity` (§3.5 A1) | **Removed.** Replaced with simpler manifest-validation assertion: "L1 manifests are well-formed + every physical column has a `_System_Field` row + no orphan ack rows." |
| One-shot data-spec to heal 67 existing custom_object Name rows | **Not needed.** Derived classification reclassifies them correctly on next read. |
| Post-A2 fail-fast addition I proposed (`schema_helpers.go` returns error on missing Source) | **Not needed.** |
| v1.5 §3.7 partial-SF-map justification for `Source` / `discovered` / `bootstrap_seed` | **Moot.** No column to map. AC-4 now fully passes. |

### What this keeps from v1.5 (unchanged)

- **§3.2 Compound resolver** — kept. Resolver reads `ClassifyCompound()` instead of `f.IsCompound`; behavior identical. Quote.Name hardcode still subsumed by declaring compound in pack spec.
- **§3.3 DefaultValue round-trip fix** at `schema_field_metadata_builder.go:69-71` — kept. Independent concern.
- **§3.3 `normalizeDDLDefaultToTyped` in `shared/pkg/fieldnormalize/`** — kept.
- **§3.3 Typed default in describe response** — kept.
- **§3.5 A2 assertion** (every physical column has `_System_Field` row) — kept.
- **§3.5 A3 assertion** (no DDL-literal quotes in DefaultValue) — kept.
- **§3.2 Sales User edge case data-spec** — kept. Pre-existing data integrity violation, unrelated to Source.
- **Layer 1d/1e in `ComputeIntrinsic`** — kept. Only the input source changes (classifier call instead of field column read).

### AC re-mapping (all seven now strictly pass)

| AC | v1.5 status | v1.6 status |
|---|---|---|
| **AC-1** No patch on symptoms | ✓ | ✓ stronger (removes an entire class of stored-derived-data bugs) |
| **AC-2** Fix in the right place | ~ (four raw-SQL writers leak through DEFAULT; a fifth — CreateSchema — bypasses entirely) | ✓ (no writer coordination — provenance lives with declarations, in L1 manifests) |
| **AC-3** `_System_Field` rebuildable from source stores | ✓ (via migration + BackfillSource) | ✓ trivial (Source/IsCompound not in `_System_Field` — nothing to rebuild) |
| **AC-4** Every attribute maps to SF Metadata API | ~ **(openly partial — RFC admits `discovered` + `bootstrap_seed` have no SF analog)** | ✓ **(no Source column to map; classification is runtime logic, just like SF's `describe` is runtime)** |
| **AC-5** Zero regression on admin-edited state | ✓ | ✓ (only declaration-derived columns removed; admin-editable state untouched) |
| **AC-6** Issue class closed permanently | ✓ (via `AssertSourceIntegrity`) | ✓ stronger (manifest validation + physical↔metadata assertion; derived classification can't be "inconsistent" with its own input) |
| **AC-7** Verified against real staging data | ✓ | ✓ (same fixture; no ALTER needed) |

### Revised PR breakdown (v1.6)

| PR | Scope | Replaces v1.5 | LOC |
|---|---|---|---|
| **v1.6-A** | Introduce `shared/pkg/fieldprovenance/` with `ClassifySource()` + `ClassifyCompound()`. Build in-memory L1 manifest indices at boot (`system_tables.json`, `standard_objects_spec.json`, `tenant_objects_spec.json`, platform standard-field template). Unit tests for every classification branch (including two-tenant test: fresh org with no custom tables, new admin-created custom object, CreateField after the fact, legacy discovered column). | — (new) | ~250 |
| **v1.6-B** | `ComputeIntrinsic` Layer 1d/1e now calls the classifiers instead of reading `f.Source`/`f.IsCompound`. Remove `Source`/`IsCompound`/`ComposedFrom` references from `FieldMetadata` struct, `_System_Field` persistence, `BackfillFieldCapabilities` SELECT/struct, `schema_helpers.go`, `field_create.go`, `platform_core.go`. Drop the v1.5 A1 migration's ADDs — new one-time migration `DROP COLUMN IF EXISTS` for those three columns (idempotent). | v1.5 A1 + A2 + A3 (all deleted/merged/simplified) | ~300 (net — most of this is **removals**) |
| **B** (parsing) | Unchanged from v1.5 — `domain/schema/definitions.go::ColumnDefinition` parses `isCompound`/`composedFrom` from JSON; User.Name declared compound in `system_tables.json`; Quote.Name declared compound in pack spec. The parsed result feeds the L1 manifest indices consumed by `ClassifyCompound`. | v1.5 B | ~150 |
| **C** (resolver) | Unchanged from v1.5 — compound resolver + validator skip + Quote.Name hardcode subsumption. Reads `ClassifyCompound()`. | v1.5 C | ~300 |
| **D** (describe) | Unchanged from v1.5 — compoundFieldName on describe members, DDL-default normalization, typed defaults, layout endpoints use live `ComputeIntrinsic`. | v1.5 D | ~250 |
| **E** (assertions) | §3.5 A2 (physical ↔ metadata) + §3.5 A3 (DDL quotes) only. A1 (Source enum) deleted. Add a single manifest-validation assertion: every L1 manifest is well-formed, no duplicate declarations across manifests, compound ComposedFrom members exist as declared fields. | v1.5 E (simplified) | ~80 |
| **F** (staging scene) | Unchanged from v1.5 — e2e-api `user-create-with-legacy-state/`. Fixture is simpler because no Source column needs to be injected. | v1.5 F | ~200 |

**Total v1.6 LOC**: ~300 (net of the `v1.6-B` removals; absolute additions closer to ~150 with ~500 deletions of v1.5-introduced code). **Total v1.5 LOC was ~1700.** About 80% reduction via deletion.

### Migration posture

Three cases depending on target environment:

- **Fresh DB (no v1.5 columns)**: `v1.6-B` migration is a no-op on the column-drop step.
- **Local/staging DB that received v1.5 A1 columns**: `v1.6-B` migration `ALTER TABLE _System_Field DROP COLUMN IF EXISTS Source, IsCompound, ComposedFrom`. Idempotent, bounded.
- **The 67 misclassified rows on TiDB Cloud**: no row-level UPDATE needed. The columns themselves get dropped. After `v1.6-B` lands, the next describe/insert/layout call re-derives the right classification.

### Covered problem inventory (self-audit before commit)

All twelve currently-faced issues:

1. #1582 original bug (User Create 400 Name required, 155 discovered cols): ✓ solved (classifier returns `discovered` → Layer 1d → prune)
2. Quote.Name compound hardcode: ✓ solved (compound resolver + pack declaration, unchanged from v1.5)
3. DefaultValue DDL quote leak (62 rows): ✓ solved (§3.3 fix unchanged from v1.5)
4. **CreateSchema writer bypass**: ✓ **eliminated** (no Source column → nothing to bypass)
5. **E2E CI fail (`SKIP_BOOTSTRAP=true` + missing Source column)**: ✓ **eliminated** (no Source column means no `SELECT _system_field.source`)
6. Integration test `TestSystemHandler_ExecuteQueryAll`: ✓ eliminated (same root as #4)
7. AI Code Blocker CI signal: investigated separately — not in scope for architectural decision
8. 67 existing custom_object Name rows stuck at Source='discovered': ✓ eliminated (no stored Source; derived classification auto-corrects)
9. Layer 1d stacked-cache (derived over derived): ✓ eliminated (one level only; `ComputeIntrinsic` is the cache, `ClassifySource` is the input; no stored Source between them)
10. IsCompound/ComposedFrom same stored-derived-data smell: ✓ same solution (derived via `ClassifyCompound`)
11. `BackfillSource` / `AssertSourceIntegrity` / `NormalizeLegacyDDLDefaults` maintaining derived data in engine code: ✓ first two removed; `NormalizeLegacyDDLDefaults` is unrelated (it normalizes DefaultValue strings, not provenance — kept)
12. AC-4 openly partial: ✓ now fully passes

### Counterargument checklist (stress-tested)

- **Performance** — ClassifySource is an in-memory map lookup (~100 ns per call × ~1000 fields per describe = ~100 µs). Negligible. No new N+1 DB queries.
- **Admin-promote UI future** — introduces a new table `_System_FieldDeclarationAck` (explicit admin state, not provenance-of-derivation). `ClassifySource` consults it as step 0. Clean separation: declarations in L1, admin overrides in L2, physical presence in `_System_Field`.
- **Debugging ("I want to `SELECT Source FROM _System_Field`")** — add a CLI / HTTP endpoint `dev/classify/:object/:field` that calls `ClassifySource` and prints the result + which manifest it matched. ~20 LOC.
- **Race conditions** — derived classification is deterministic given fixed in-memory manifests; manifests change only on binary deploy (L0 update). No writer race possible for provenance.
- **Createable still stored** — yes, kept as a per-request cache of `ComputeIntrinsic`. That's orthogonal: Createable is a function of Source + other rules; caching a pure function is an optimization, not an architectural obligation.
- **"What if CreateSchema needs to record who created the object?"** — that's a different concern (audit trail, `CreatedById`), already modeled on `_System_Object`. Provenance-of-declaration-path is separate and derivable.

### Philosophy alignment check

Using CLAUDE.md's Metadata-Driven Architecture six-rule audit on the v1.6 design:

1. No `__c` literals in L0 code — ✓
2. No ProfileId branching — ✓
3. Defaults / RecordTypes in metadata — ✓ (untouched)
4. No business-enum switch — the `FieldSource` enum used in `ClassifySource` is an internal provenance taxonomy (not a business enum), and is consumed only inside `fieldprovenance/` — same pattern as `ManageableState` inside SF. ✓
5. No tenant-specific tables in platform SQL — ✓
6. Field writability comes from `FieldPermissions` + `ComputeIntrinsic` (now reading classifier) — ✓

Three self-check tests:

- **Grep test** on v1.6 diff: zero `__c` literals in platform code (excluding test fixtures and pack JSONs).
- **Two-tenant test**: a brand-new org with no `__c` fields, no `system_admin` Profile name, no `country_dictionary__c` — `ClassifySource` returns `managed` for platform-injected Name/Id/audit; `standard_spec` for pack-declared fields; `discovered` for any truly unowned physical column. No crash paths. ✓
- **Admin-configure test**: admin adds a custom field via `CreateField` — the new row has APIName `Foo__c`; `ClassifySource` returns `custom` via rule 3 (suffix). Zero code change needed. ✓

**Conclusion**: v1.6 is strictly cleaner than v1.5 on every AC dimension the RFC itself declared, eliminates the class of bug v1.5 surfaced at the first implementation attempt, and reduces code volume by ~80%. Proceeding to implementation.

---

## Legacy v1.5 content below (retained for historical context — sections 3.1 / 3.5-A1 / 3.6 Source-aware stored logic / 3.7 Source-row mapping are superseded by §1.6 above)

---

## 0. Acceptance criteria (hard gates for "root fix")

The solution is only acceptable if **all of the following are explicitly verified** before any PR is merged:

| # | Criterion | How it will be checked |
|---|---|---|
| **AC-1** | **No patch on symptoms** — fix must address the *class* of bug, not the three specific symptoms | Every change must be traceable to one of: Source attribution, compound-field first-class, typed-default normalization. Symptom-only changes are rejected in code review. |
| **AC-2** | **Fix in the right place** — declarative concepts in declaration layer, runtime concepts in runtime layer, admin-edits in customization layer | Any change that writes to `_System_Field` must declare which concern it represents (declaration / customization / runtime / admin override). Mixed writes are rejected. |
| **AC-3** | **Architecture correct and clean** — `_System_Field` must become rebuildable from source stores without losing admin/runtime state | After migration, `TRUNCATE _System_Field; rerun_bootstrap` must produce functionally equivalent state (with documented exceptions). Verify via integration test. |
| **AC-4** | **SF metadata-driven philosophy** — every attribute on `_System_Field` must map to a concept that exists in SF Metadata API v63.0 (not a NexusCRM-ism) | Produce a reference table mapping each column to SF Metadata equivalent. Columns without SF analog get explicit justification or get moved to a separate "NexusCRM extension" table. |
| **AC-5** | **Zero functional regression for admin-edited state** | No UPSERT/TRUNCATE/migration path destroys: admin-edited formulas, rollup configs, autonumber format declarations, admin-customized labels/help texts/FLS |
| **AC-6** | **Issue class closed permanently** — any future "declaration-less physical column" or "compound-field-unknown" or "DDL-default-leak" case must be blocked at a bootstrap assertion | Add assertions that FAIL bootstrap if: (a) any `_System_Field` row has unknown Source, (b) any physical column lacks a `_System_Field` row OR has Source=`discovered` without explicit admin ack, (c) any DefaultValue contains outer `'` characters |
| **AC-7** | **Verified against real staging data** | The chosen solution reproduces the fix on a staging-equivalent state (155 discovered cols + 62 quoted defaults + orphan-like patterns) |

If any AC cannot be credibly demonstrated, the design must be revised before coding starts.

---

## 1. Problem statement (final)

Admin → "Add User" in Setup fails to save. Captured evidence from staging (2026-04-21 06:06 UTC, build `1776751604438`):

- `POST /services/data/v63.0/sobjects/User` → HTTP 400
  `[{"errorCode":"REQUIRED_FIELD_MISSING","message":"validation error on field 'Name': required field(s) missing: Name"}]`
- POST body size: 121 keys, ~100 of which are hidden `UserPreferences*` / `UserPermissions*` / `EmailPreferences*` fields the UI never showed
- Layout default values contain SQL single-quoted literals: `TimeZoneSidKey="'America/Los_Angeles'"`, `IsActive="1"`, etc.

This is the third time the class of bug has surfaced (previous: PR #656 rendering bloat → PR #1565 curated layout — still didn't close the class).

---

## 2. Root cause (final, verified)

**NexusCRM has no contract that "every physical column must trace to a declaration source."** So any accumulated physical DDL (from early-version `system_tables.json` declarations that were later removed, legacy SF imports, historical migrations) persists as "declaration-less columns." Downstream:

1. `_System_Field` faithfully mirrors all physical columns → Agent 6 verified staging User has 191 physical columns = 191 metadata rows, zero orphans
2. `describe` faithfully projects `_System_Field` → staging User describe returns ~165 fields including 155 Legacy-SF shadow columns
3. `MetadataRecordForm` faithfully builds form state from `describe.fields` filtered by `createable` → form state has ~120 keys even though curated layout only shows 7 required + ~24 optional fields
4. POST body includes every createable field → bloat + default-value leakage

**The three observed bugs are symptoms of ONE missing concept**: no way to tell "who put this column here and what should the system do with it."

### Why `Name required` specifically

`validateRequiredForCreate` at `sobject_records.go:410-460` iterates `schema.Fields` from `_System_Field`. When `Name.Required=1` AND `DefaultValue` is NULL/empty AND payload lacks Name → 400. Currently staging has `Name.Required=0` (physical column is nullable), so bug is quiescent but can reawaken if any reconcile path transiently flips Required to 1 based on DDL state.

Compound fields (Name = FirstName + LastName) are not a first-class concept in NexusCRM's metadata model, so the validator can't skip them the way it skips formula/autonumber.

### Why defaults carry SQL quotes

`_System_Field.DefaultValue` column stores raw DDL literals (`"'en_US'"`, `"1"`). `sobject_describe.go:163` returns `f.DefaultValue` verbatim. Frontend form uses this as the initial value and sends it back.

---

## 3. Design — four first-class mechanisms

> **Revision 1.3 → 1.4 shifts**: team-review round 1 identified 4 CRITICAL gaps (addressed in v1.3); round 2 identified 2 NEW CRITICAL + 1 RED + 2 MAJOR + several MINOR refinements, all verified against live code and incorporated below. Cumulative design corrections from v1.2:
> 1. **Source enforcement** relies on **column DEFAULT 'discovered' + BackfillSource idiom** (mirrors existing `BackfillFieldCapabilities`), not per-call-site patching — 4 raw-SQL write paths inherit the default automatically (round 1)
> 2. **Createable/Updateable authority** is live-called `ComputeIntrinsic`; stored column is a derived cache. §3.6 extends `ComputeIntrinsic` with Source/IsCompound layers (round 1)
> 3. **BackfillFieldCapabilities SELECT** must be extended to populate `Source` and `IsCompound` on its minimal FieldMetadata, else Layers 1d/1e are dead at backfill time (round 2 — 3 reviewers independently found this)
> 4. **RunRepair ordering**: `Discover → BackfillSource → BackfillFieldCapabilities`. Wrong order would store `Createable=false` for all fields on first apply (round 2)
> 5. **DDL-quote leak** fixed at `schema_field_metadata_builder.go:69-71` (the round-trip boundary), not describe passthrough (round 1)
> 6. **Layout endpoints** `sobject_describe.go:485` + `uiapi/primitives.go:326` must call live `ComputeIntrinsic` instead of reading stored `Createable` — closes a staleness risk and a pre-existing TODO (round 2)
> 7. **Migration atomicity**: TiDB DDL auto-commits. Protection is idempotency + version gating + explicit `WHERE Source='discovered'` guards — NOT transactional atomicity (round 2 honest correction)
> 8. **SQL-escape un-escape**: migration adds an explicit step for `''` inside literals (round 2)
> 9. **`normalizeDDLDefaultToTyped`** lives in new package `shared/pkg/fieldnormalize/`, not `fieldcapabilities/` (round 2 separation-of-concerns fix)

### 3.1 `Source` attribution on `_System_Field`

**New column**: `Source VARCHAR(32) NOT NULL DEFAULT 'discovered'`. Enum: `managed` / `standard_spec` / `tenant_spec` / `custom` / `bootstrap_seed` / `discovered`.

**Why `DEFAULT 'discovered'`**: verified 4 raw-SQL write paths bypass `SaveFieldMetadataWithIDs` (`discover_tables.go:335-339` INSERT, `reconcileDiscoveredFieldOverrides:716-817` UPDATE, `BackfillFieldCapabilities:869` UPDATE, `schema.go:1836-1969` `reconcileFieldLabels` UPDATE). Per-site patching is fragile; a column DEFAULT guarantees every raw write inherits a safe classification. Paths that should store something other than `discovered` go through structured writers (`SaveFieldMetadataWithIDs`, pack apply) which set Source explicitly.

**New function**: `BackfillSource(db)` in `backend/internal/bootstrap/` — mirrors the existing `BackfillFieldCapabilities` pattern at `discover_tables.go:819-874`. Runs on every startup, idempotent. Uses the three declaration JSONs + ApiName naming patterns to promote `discovered` rows to `managed` / `standard_spec` / `tenant_spec` / `custom` / `bootstrap_seed` where attribution is determinable. Rows that don't match any declaration stay `discovered`.

**Initial migration (one-time data-spec)** — three separate steps:

1. `ALTER TABLE _System_Field ADD COLUMN Source VARCHAR(32) NOT NULL DEFAULT 'discovered'`. **This DDL auto-commits** (TiDB/MySQL cannot wrap DDL in a transaction). All pre-existing rows now carry `Source='discovered'`; new rows inserted after this point also inherit the DEFAULT.
2. `BEGIN; <UPDATE steps>; SET migration_version; COMMIT;` — the backfill UPDATEs + version flag in ONE transaction. Steps ordered by specificity: `custom` (suffix `__c`) → `bootstrap_seed` (pre-existing PR #1565 signal) → `tenant_spec` → `standard_spec` → `managed`. Every UPDATE carries an explicit `WHERE Source = 'discovered'` guard to prevent downstream data-specs from demoting already-classified rows on re-run.
3. Migration is **idempotent** (re-run yields no-op) and uses the `_System_Migration` version table to block double-apply. Protection is idempotency + version gating — not transactional atomicity across DDL+DML (which TiDB doesn't support).

**RunRepair sequencing** (must be coded explicitly in `controlplane/operations.go:82+`): `DiscoverAndRegisterPhysicalTables` → **`BackfillSource` [NEW]** → `BackfillFieldCapabilities`. Rationale: BFC writes `_System_Field.Createable` based on `ComputeIntrinsic(field)`, which after §3.6 depends on `field.Source`. If BFC ran before BackfillSource, every field would transiently be `Source=''` (or `discovered` from DEFAULT), Layer 1d would fire, and BFC would store `Createable=false` for legitimately managed fields — requiring a second BFC pass to recover. Explicit ordering avoids the double-pass.

### 3.2 Compound field first-class

Extend `system_tables.json` / `standard_objects_spec.json` field schema:

```json
{ "name": "Name", "type": "VARCHAR(255)", "isCompound": true, "composedFrom": ["FirstName", "LastName"] }
```

- `_System_Field` gains `IsCompound BOOL, ComposedFrom JSON` columns (verified absent today)
- Compound parent: `Createable=0, Updateable=0, IsNameField=1`. SF-aligned: `describe.fields[parent].createable=false, updateable=false, nameField=true, compoundFieldName=null`.
- `describe.fields[member].compoundFieldName = <parent_ApiName>` for members (per real SF v63.0 describe shape)
- `validateRequiredForCreate` at `sobject_records.go:410-460` adds `if f.IsCompound { continue }` alongside the existing autonumber/formula skip branches
- **Compound resolver ordering in `persistence_insert.go` transaction** — explicit placement resolved after code read:
  1. After `assignAutoNumbers` (currently at line 163) — so autonumber-populated fields like `QuoteNumber` are available as members
  2. After the existing Quote.Name fallback (lines 169-176) — this hardcoded special case is **subsumed** by declaring `Quote.Name` as compound with `composedFrom=["QuoteNumber"]` in the Quote spec; the hardcoded block can be deleted in PR C
  3. Before `publishRecordEvent` at line 179 — so before-create flows see the resolved Name
  4. Before `publishRecordEvent` for the event payload consistency
- **Resolver rule (strict, SF-correct)**:
  ```pseudo
  for field f in registry.compoundFields(object):
      if f.APIName in data:
          delete data[f.APIName]          # SF strips explicit writes to createable=false fields
      parts = []
      for src in f.ComposedFrom:
          v = data.get(src)
          if v is None or v == "":
              # On UPDATE: if member is absent from payload, read current DB value to avoid partial-update loss
              if is_update_context: v = current_db_row[src]
          if v not None and v != "": parts.append(v.strip())
      if len(parts) == 0:
          continue                         # all parts empty — leave field untouched
      data[f.APIName] = " ".join(parts)
  ```
  On **insert**: validateRequiredForCreate's existing Required-check on `FirstName/LastName` (already Required=1 for User in staging) catches missing-member case before the resolver runs. Resolver never sees all-empty-parts on insert for User.
  On **update**: read-before-write for missing members ensures `PATCH {LastName: "X"}` still re-derives Name using existing FirstName. **Implementation note**: the update path in `persistence_update.go` already loads the pre-update row into an `oldRecord` variable (used for change-detection + audit). The resolver reuses this loaded row — no additional SELECT needed. If implementation discovers `oldRecord` is NOT loaded at the resolver's call site, the resolver issues a single SELECT within the existing transaction scope (snapshot-isolated per TiDB default). This keeps the resolver self-contained without refactoring the update path's load order.
- **Chained-compound guard**: bootstrap assertion rejects `ComposedFrom` members that are themselves `IsCompound=true` (prevents recursion surprises)
- **Sales User edge case**: pre-existing data integrity violation (Name="Sales User", FirstName=NULL, LastName=NULL, BUT LastName is Required=1 so this row shouldn't exist). Handled in a separate data-spec: `UPDATE User SET LastName='Sales User', Name='Sales User' WHERE Name IS NOT NULL AND FirstName IS NULL AND LastName IS NULL`. Idempotent + safe.

### 3.3 DefaultValue: fix the round-trip boundary

**Root cause pinpointed by code audit** (not in v1.2): the DDL-quote leak happens at a **round-trip boundary** inside the persistence layer, not at the describe sink.

| Layer | File:Line | Direction | Current behavior |
|---|---|---|---|
| DDL generator | `schema_column_ops.go:671` | FieldMetadata → ColumnDefinition | Wraps value in SQL quotes (`colDef.Default = "'" + escaped + "'"`). **Correct for DDL**. |
| DDL consumer | `schema_ddl.go:46` | ColumnDefinition → SQL | Emits `DEFAULT %s` using the pre-quoted string. **Correct**. |
| **Inverse builder** | **`schema_field_metadata_builder.go:69-71`** | **ColumnDefinition → FieldMetadata** | **Copies `col.Default` verbatim (still SQL-quoted) back into `field.DefaultValue`. THIS IS THE LEAK.** |
| Describe sink | `sobject_describe.go:163` | FieldMetadata → DTO | Passes through whatever's in `field.DefaultValue`. Not the root cause; just the visible symptom. |

**Fix**: at `schema_field_metadata_builder.go:69-71`, add a normalization step:
```go
if col.Default != "" {
    def := normalizeDDLDefaultToTyped(col.Default, col.Type)  // NEW
    field.DefaultValue = &def
}
```

`normalizeDDLDefaultToTyped(raw, columnType)`:
- If `raw` matches `^'(.*)'$`, strip outer single quotes and un-escape doubled `''` → `'` (SQL string literal)
- If `columnType` is boolean-ish (`TINYINT(1)`, `BOOLEAN`) and value is `"0"`/`"1"` → normalize to `"false"`/`"true"` (string form per SF describe — see §3.8)
- If `columnType` is integer/decimal, leave as unquoted numeric string
- Otherwise, return `raw` unchanged

Shared helper lives in **new package `shared/pkg/fieldnormalize/`** (not `fieldcapabilities/` — that one is about Createable/Updateable intrinsic computation; value normalization is a separate concern). Both bootstrap and admin write paths import it. `SaveFieldMetadataWithIDs` calls `normalizeDDLDefaultToTyped` defensively in case a caller passes a DDL-formatted literal through the structured API (belt-and-suspenders; the real leak is closed at the builder).

**Boolean default serialization** — `DefaultValue` stays `*string` in `FieldMetadata` (confirmed at `shared/pkg/models/metadata.go:119`). The describe layer encodes the value as a **typed JSON value** per column type:
- `Type='boolean'` + value `"true"` → JSON `true`
- `Type='int'/'double'/'currency'/'percent'` + value `"1"` → JSON number `1`
- `Type='string'/'email'/...` + value `"en_US"` → JSON string `"en_US"`
- fallback → JSON string

This conversion lives in `sobject_describe.go`'s DTO construction (same place `DefaultValue` is read for the `defaultValue` field of the describe response), using a small `typedDefaultForDescribe(raw string, fieldType string) any` helper in `shared/pkg/fieldnormalize/`.

**Migration (one-time SQL)** for the existing 62 DDL-quoted rows. **Two steps** because a single SUBSTRING leaves SQL-escaped `''` in place:
```sql
-- Step 1: strip outer single quotes (e.g., 'en_US' → en_US, 'O''Reilly' → O''Reilly)
UPDATE _System_Field SET DefaultValue = SUBSTRING(DefaultValue, 2, CHAR_LENGTH(DefaultValue)-2)
WHERE DefaultValue REGEXP "^'.*'$";

-- Step 2: un-escape doubled single-quotes (e.g., O''Reilly → O'Reilly)
-- Only applies to rows that actually have the pattern after step 1.
UPDATE _System_Field SET DefaultValue = REPLACE(DefaultValue, "''", "'")
WHERE DefaultValue LIKE "%''%";

-- Step 3: coerce boolean-typed string defaults (Type='boolean' columns)
UPDATE _System_Field SET DefaultValue='true'  WHERE Type='boolean' AND DefaultValue IN ('1','TRUE','True');
UPDATE _System_Field SET DefaultValue='false' WHERE Type='boolean' AND DefaultValue IN ('0','FALSE','False');
```

The Go helper `normalizeDDLDefaultToTyped` performs both strip-quotes + un-escape in one pass (identical result to the two SQL UPDATEs), keeping runtime and migration consistent.

**Rollback posture (per `.agent/skills/data-change-spec/SKILL.md`)**: DefaultValue normalization is a **one-way, lossy transform** — the original DDL-quoted strings are not preserved. The data-spec's Rollback section must state: *"Rollback not available for DefaultValue normalization. If a rollback of PR A1 is required, drop the three added columns (`Source`, `IsCompound`, `ComposedFrom`) only; the normalized `DefaultValue` column retains its post-migration state. Callers that relied on the DDL-quoted form must be updated to accept typed values; reverting the form would reintroduce the #1582 bug class."*

### 3.4 Discovered-column terminal state (closes G-4)

`Source='discovered'` is a **terminal state** for physical columns without a declaration source. NexusCRM treats them as *read-only legacy storage*:

- `describe` reports `createable=false, updateable=false, readable=true` — enforced via §3.9 `ComputeIntrinsic` extension
- Backend feature code does not consume these fields (verified: grep for specific column names like `UserPermissionsMarketingUser` yields 0 hits outside tests/generated-schema/layout-metadata)
- Data survives physically (no DROP) → no risk of losing legacy SF-imported state
- **Future admin UI** (deferred to follow-up): "Promote discovered → custom" (admin supplies Label/HelpText, Source flips, Createable unlocks via ComputeIntrinsic) or "Approve deletion" (physical DROP + metadata delete)

**Honest SF-philosophy mapping** (corrected from v1.2): `discovered` has **no direct SF analog**. SF doesn't admit declaration-less columns because metadata API is the only creation path. Our `discovered` is a NexusCRM-specific accommodation for legacy data imported or bootstrapped outside a managed pipeline. It is philosophically *adjacent to* but not equivalent to SF's `installed` managedState (which means "intentionally installed from a managed package"). We make this concession to preserve staging data; we don't claim it's SF-correct.

### 3.5 Bootstrap-time invariant assertions (closes G-6, enforces AC-6)

Three SQL-based assertions added to `assertSystemFieldsConsistency`. A1 and A3 are CRITICAL (block startup in strict mode). A2 is WARNING (logs; doesn't block — see rationale below).

**A1 [CRITICAL]: Every `_System_Field` row has a Source within the enum**
```sql
SELECT Id, ApiName, Source
FROM _System_Field
WHERE IsDeleted = 0
  AND (Source IS NULL OR Source NOT IN ('managed','standard_spec','tenant_spec','custom','bootstrap_seed','discovered'));
-- Expected row count: 0; any row → CRITICAL, blocks server start.
-- Column has NOT NULL + DEFAULT 'discovered' so IS NULL is defensive belt-and-suspenders.
-- Gated by migration-version flag: the assertion activates only after the initial data-spec
-- that adds the column has committed, preventing false-positives during rolling deploy.
```

**A2 [WARNING only]: Every physical column on a managed object has a `_System_Field` row**
```sql
SELECT c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN _System_Object o ON o.ApiName = c.TABLE_NAME AND o.IsDeleted = 0
LEFT JOIN _System_Field f ON f.ObjectId = o.Id AND f.ApiName = c.COLUMN_NAME AND f.IsDeleted = 0
WHERE c.TABLE_SCHEMA = DATABASE()
  AND f.Id IS NULL;
-- Expected row count: 0 in steady state. Any row → WARNING log + surface in cmd/setup verify report.
-- NOT CRITICAL because during a rolling deploy a new DDL column may land before metadata INSERT runs;
-- gating server startup on this would break zero-downtime upgrades. BackfillSource (§3.1) reconciles
-- these on next bootstrap; persistent violations are investigated manually via the warning surface.
```

**A3 [CRITICAL]: No DefaultValue contains SQL-literal outer quotes**
```sql
SELECT Id, ApiName, DefaultValue
FROM _System_Field
WHERE IsDeleted = 0
  AND DefaultValue IS NOT NULL
  AND DefaultValue REGEXP "^'.*'$";
-- Expected row count: 0 after migration; any row → CRITICAL, blocks server start.
-- Catches any future code path that reintroduces the DDL-literal leak (§3.3).
```

A1 and A3 run in `assertSystemFieldsConsistency` path with severity `error` (strict mode blocks startup — same pattern as existing `autonumber_verify.V8`). A2 runs with severity `warning` (logged + reported, does not block).

### 3.6 Make `ComputeIntrinsic` Source-aware (CRITICAL addition from v1.3)

**Code audit confirmed** (contrary to v1.2 assumption): setting `Createable=0` on a `_System_Field` row does **not** make the field non-createable in describe or in the insert gate. `shared/pkg/fieldcapabilities/capabilities.go:20-55` `ComputeIntrinsic` is the live authority; all consumers (`field_access_resolver.go:33,84`, `sobject_describe.go:161`, `persistence_insert.go:332`, `persistence_bulk_insert.go:137`) call it at request time. The stored `Createable` column is a derived cache populated by `BackfillFieldCapabilities` — which itself calls `ComputeIntrinsic`.

So: the Source-driven `createable=false` behavior requires extending `ComputeIntrinsic`.

**Change** (`shared/pkg/fieldcapabilities/capabilities.go`):

```go
func ComputeIntrinsic(objectAPIName string, f *models.FieldMetadata) (createable, updateable bool) {
    // ... existing Layer 1a (audit fields), 1b (formula/autonumber/rollup), 1c (system dates) ...

    // Layer 1d [NEW]: Discovered-source columns are physical-storage-only,
    // no declaration, no admin ownership — never createable/updateable via generic CRUD.
    if f.Source == "discovered" {
        return false, false
    }

    // Layer 1e [NEW]: Compound parent fields are computed from ComposedFrom;
    // SF parity — createable=false, updateable=false even for the Name parent.
    if f.IsCompound {
        return false, false
    }

    // Layer 2: object-specific denylist (unchanged)
    // ...
}
```

Two effects:
1. Runtime consumers (describe, insert gate, resolver) immediately honor `Source='discovered'` and `IsCompound=true` → describe emits `createable=false`, insert silently drops payload entries
2. `BackfillFieldCapabilities` on next startup updates the stored `Createable/Updateable` columns on `_System_Field` to match — **BUT only if BFC is updated** (see required change below)

**Required change to `BackfillFieldCapabilities` (`discover_tables.go:828-855`)** — without this change, Layers 1d/1e are dead code at backfill time:

- Current SELECT (line 828-831) retrieves only `Id, ApiName, Type, Formula, Createable, Updateable, ObjApiName`. The struct at line 851 only populates `APIName, Type, Formula` on the FieldMetadata passed to `ComputeIntrinsic`. `Source` and `IsCompound` are always zero-valued → Layer 1d (`f.Source == "discovered"`) and Layer 1e (`f.IsCompound`) never fire.
- Fix: extend the SELECT to include `f.Source, f.IsCompound`; extend the row struct + scan; set `fm.Source` and `fm.IsCompound` on the minimal FieldMetadata. Without this, the stored `_System_Field.Createable` column becomes permanently inconsistent with the live `ComputeIntrinsic` truth for every discovered/compound field.

This change belongs in **PR A2** alongside the `ComputeIntrinsic` Layer 1d/1e additions — the two must ship together.

Unit test added: `capabilities_test.go` — each of the 5 Layer 1 branches + Layer 2 has explicit coverage including the two new branches. Integration test added: `discover_tables_test.go` — asserts that after BFC runs on a fixture containing a `Source='discovered'` row, the stored `Createable` column is `false`.

### 3.7 SF Metadata API attribute mapping (fulfills AC-4)

Every `_System_Field` column used by this RFC's design maps to a real SF v63.0 concept:

| `_System_Field` column | SF v63.0 counterpart | Source |
|---|---|---|
| `APIName` | `CustomField.fullName` (minus object prefix) | SF Metadata API |
| `Type` | `CustomField.type` (enum: Text, Number, Checkbox, ...) | SF Metadata API |
| `Label` | `CustomField.label` | SF Metadata API |
| `Required` | `CustomField.required` (boolean, per-field on Master/default record type) | SF Metadata API |
| `IsUnique` | `CustomField.unique` | SF Metadata API |
| `DefaultValue` | `CustomField.defaultValue` (typed formula or literal) | SF Metadata API |
| `IsNameField` | `describe.fields[].nameField` | SF Describe API |
| **`IsCompound` (NEW)** | SF derives compound parenthood implicitly from member fields' `compoundFieldName`. NexusCRM stores an explicit `IsCompound` flag for the resolver + validator. Behavior is SF-parity; storage shape is a NexusCRM denormalization. | **NexusCRM denormalization** (SF-derivable but not stored in SF) |
| **`ComposedFrom` (NEW)** | SF does not store this; it's the inverse of members' `compoundFieldName`. Stored explicitly in NexusCRM for resolver efficiency. Bootstrap assertion (§3.5 A2-sibling) rebuilds `ComposedFrom` from members to prevent staleness if a member field is deleted. | **NexusCRM denormalization** (SF-derivable) |
| **`Source` (NEW)** | Partial map to SF `CustomField.manageableState` (`beta`/`deleted`/`deprecated`/`deprecatedEditable`/`installed`/`installedEditable`/`released`/`unmanaged`/`unrestricted`). Legit alignments: `managed`/`standard_spec` ≈ `unmanaged`/`released`; `custom` ≈ `unmanaged`. **`discovered` has no SF analog** — SF forbids declaration-less physical columns. **`bootstrap_seed` has no SF analog** — this is a NexusCRM platform-ship classification (PR #1565 already introduced it for layouts). | **Partial SF map + 2 NexusCRM-specific values** |
| `Createable` | `describe.fields[].createable` | SF Describe API |
| `Updateable` | `describe.fields[].updateable` | SF Describe API |
| `IsSystem` | SF has no direct analog; NexusCRM-specific signal for "platform-owned, admin cannot hard-delete" | NexusCRM |
| `IsCustomized` | SF has `CustomField.inlineHelpText` + admin-edit UI that mutates the record in place; SF doesn't flag "was this ever customized", it just reflects current state. NexusCRM keeps the flag for reconciliation idempotency. | NexusCRM (justified) |
| `Formula`, `FormulaMode`, `ReturnType` | `CustomField.formula`, `CustomField.formulaTreatBlanksAs`, `CustomField.returnType` | SF Metadata API (nested on CustomField, matches our composite model — G-1 confirmed) |
| `RollupConfig` (JSON) | `CustomField.summaryFilterItems` + `summaryForeignKey` + `summaryOperation` (multiple attrs on CustomField) | SF Metadata API (composite OK per G-1) |
| `AutoNumberFormat`, `AutoNumberScope`, `AutoNumberStart`, `AutoNumberTokenRefs`, `AutoNumberCanonicalHash`, `AutoNumberCounterResetPolicy` | `CustomField.displayFormat` + `CustomField.startingNumber` + `externalDeveloperName` for named autonumbers. Counter state is runtime-only, lives in separate `_System_AutoNumberCounter` (SF parity: runtime counter is internal to SF, not metadata) | SF Metadata API + runtime separation (both correctly modeled) |
| `ReferenceTo`, `RelationshipName`, `IsMasterDetail`, `DeleteRule`, `IsPolymorphic` | `CustomField.referenceTo`, `CustomField.relationshipName`, `CustomField.type=MasterDetail`, `CustomField.deleteConstraint`, polymorphic is implicit | SF Metadata API |

**Conclusion**: every attribute is either SF-standard (majority) or explicitly justified as NexusCRM extension. AC-4 satisfied.

### 3.8 Staging reproduction test (closes G-7, enforces AC-7)

New e2e-api scene `tests/e2e-api/cases/user-create-with-legacy-state/`:

**Setup (idempotent SQL fixture)**:
- Create User object with managed columns per `system_tables.json`
- Inject 5 `UserPreferencesXxx` columns (physical + `_System_Field` rows with `Source='discovered'`, `Createable=0`)
- Inject `Name` column as compound (physical + `_System_Field` with `IsCompound=1, ComposedFrom=["FirstName","LastName"], Createable=0`)
- Inject 3 DefaultValue rows with DDL-literal quotes to test the assertion (these must be normalized by migration first; test asserts they ARE normalized)

**Assertions**:
1. `GET /describe/User` returns:
   - `Name` field: `createable: false, updateable: false, nameField: true, compoundFieldName: null`
   - `FirstName` field: `compoundFieldName: "Name"`
   - `TimeZoneSidKey.defaultValue` is the string `"America/Los_Angeles"` (no quotes), JSON serialized
   - `IsActive.defaultValue` is boolean `true`, not string `"1"`
   - `UserPreferencesActivityRemindersPopup.createable: false`
2. `POST /sobjects/User {FirstName:"Repro", LastName:"Test1582", Email:..., Username:..., ProfileId:..., Password:...}` → HTTP 201
3. The created user row has `Name = "Repro Test1582"` (compound resolver ran)
4. `POST /sobjects/User {... , Name:"Tamper"}` → HTTP 201 (Name silently stripped, DB Name = derived)

**Bootstrap assertion test**: separate case that manually corrupts `_System_Field` (e.g., adds row with `Source='invalid'`) and verifies server readiness check FAILS with a clear error.

This scene must pass on both:
- Clean local TiDB (today's bootstrap state)
- Staging-clone TiDB (simulating 155-discovered-column state + 62 quoted-default rows)

The staging-clone fixture is constructed from the SQL results already captured in §5 Verification log (counts + column lists + sample rows).

---

## 4. PR breakdown — revised per review feedback

PR A split into three narrowly-scoped PRs per Code Reviewer "PR A does three things" finding. Each PR carries its own regression test (per "PR E monolith" feedback).

| PR | Scope | Test carried | LOC est | Depends on |
|---|---|---|---|---|
| **A1** | **Schema + data migration**: `ALTER TABLE _System_Field ADD COLUMN Source ... DEFAULT 'discovered'`, add `IsCompound`, `ComposedFrom`. Data-spec (one transaction): runs UPDATE-only backfill to promote `discovered` → `managed`/`standard_spec`/`tenant_spec`/`custom`/`bootstrap_seed` based on declaration cross-reference. Also normalizes the 62 DDL-quoted DefaultValue rows. Sets migration-version flag at the end. | Integration test: after data-spec runs, query `SELECT COUNT(*), Source FROM _System_Field GROUP BY Source` and assert: staging-clone has ≈155 rows remaining as `discovered` on User, the bulk `managed` for system tables, zero SQL-quoted DefaultValue rows, migration-version flag set | ~250 | — |
| **A2** | **`ComputeIntrinsic` Source-awareness + `BackfillSource` + BFC extension + RunRepair ordering**: (1) extend `capabilities.go` with Layer 1d (Source=discovered → false,false) and Layer 1e (IsCompound=true → false,false). (2) Add `BackfillSource(db)` in `backend/internal/bootstrap/` mirroring `BackfillFieldCapabilities` pattern; every UPDATE carries `WHERE Source='discovered'` guard. (3) **Extend `BackfillFieldCapabilities` at `discover_tables.go:828-855`** to SELECT and populate `Source, IsCompound` on the minimal FieldMetadata — without this, Layers 1d/1e are dead at backfill time. (4) In `controlplane/operations.go:82+`, insert `BackfillSource` BETWEEN `DiscoverAndRegisterPhysicalTables` and `BackfillFieldCapabilities` (ordering is load-bearing — see §3.1). | Unit test for ComputeIntrinsic (8 branches incl. 2 new); integration test that BFC on a synthetic `Source='discovered'` row → stored `Createable=false`; integration test that `BackfillSource` idempotently reclassifies + doesn't demote non-discovered rows | ~300 | A1 |
| **A3** | **Structured write paths propagate Source explicitly**: `SaveFieldMetadataWithIDs`, `BatchSaveFieldMetadata`, `PrepareFieldForBatch`, pack apply paths gain a `source Source` parameter. Call sites updated: field_create/update/batch = `custom`; bootstrap pack apply = `managed`/`standard_spec`/`tenant_spec` per pack; admin metadata endpoints = `custom`. The 4 raw-SQL paths (`discover_tables.go:335-339`, `reconcileDiscoveredFieldOverrides`, `BackfillFieldCapabilities`, `reconcileFieldLabels`) keep inheriting DEFAULT `'discovered'` — no per-site change needed because column DEFAULT covers them. | Unit tests on field_create/update verify stored Source matches caller intent | ~300 | A1 |
| **B** | **Compound declaration parsing**: extend `domain/schema/definitions.go` ColumnDefinition to parse `isCompound`/`composedFrom` from JSON; User.Name declared as compound in `system_tables.json`; Quote.Name declared compound with `composedFrom=["QuoteNumber"]` in its pack spec (preparing to subsume the hardcoded fallback). | Unit test: parse `system_tables.json` User → confirm Name is IsCompound=true, ComposedFrom=["FirstName","LastName"]; Quote.Name ComposedFrom=["QuoteNumber"] | ~150 | A2 |
| **C** | **Compound resolver + validator skip + subsume Quote.Name hardcode**: `validateRequiredForCreate` gets `if f.IsCompound { continue }`; persistence adds `resolveCompoundFields(tx, objectName, data, isUpdate)` placed after `assignAutoNumbers` at `persistence_insert.go:163`, replacing the lines 169-176 Quote.Name hardcoded fallback, and running before `publishRecordEvent` at line 179. **Must update `persistence_helpers_test.go:36 TestIntegrationQuoteCreationFromOpportunity`** + `line 161 TestPopulateQuoteNameWithQuoteNumber` to exercise the compound resolver path instead of the deleted fallback (both tests currently assert the hardcoded block's exact behavior). | e2e-api `user-create-compound/pass.json`: POST User without Name → 201; DB Name = "FirstName LastName". e2e-api `user-create-compound/strip.json`: POST User with `Name:"Tamper"` → 201, DB Name = derived not "Tamper". e2e-api `quote-create-subsumed/pass.json`: POST Quote with AccountId + Opportunity → 201, Name = QuoteNumber. Existing `persistence_helpers_test.go` tests updated to assert resolver semantics. | ~300 | B |
| **D** | **Describe layer — compoundFieldName + DDL-default normalization + typed defaults + layout endpoints use live ComputeIntrinsic**: (1) `sobject_describe.go` populates `compoundFieldName` on member fields; (2) `buildFieldMetadataFromColumn:69-71` calls `normalizeDDLDefaultToTyped`; (3) describe JSON encodes typed values per column type via `typedDefaultForDescribe`; (4) **Replace stored-Createable reads with live ComputeIntrinsic at**: `sobject_describe.go:485` (`"editableForNew": f.Createable`) and `uiapi/primitives.go:326` (`editableForNew = fMeta.Createable`) — both currently read the stored-cache column directly, causing staleness after any Source change. This also closes the pre-existing TODO at `primitives.go:32`. **Use `fieldcapabilities.ComputeIntrinsic(objectName, &f)` directly, not `resolver.EffectiveCreateable`** — the latter requires a `user *UserSession` which is not threaded through `transformLayout(ctx, internalLayout, objectType, mode)` at `primitives.go:237`. ComputeIntrinsic takes no user and is intrinsic-only, which is exactly what `editableForNew` needs (intrinsic layout capability, independent of user-level FLS). | e2e-api `user-describe-typed/pass.json`: GET User describe asserts `Name.createable=false,updateable=false,nameField=true,compoundFieldName=null`, `FirstName.compoundFieldName="Name"`, `TimeZoneSidKey.defaultValue=="America/Los_Angeles"` (no quotes), `IsActive.defaultValue===true` (JSON boolean). e2e-api `user-describe-discovered/pass.json`: `UserPermissionsMarketingUser.createable=false,updateable=false,readable=true`, and the Setup layout endpoint for User reports `editableForNew=false` on the same fields (layout endpoint consistency — closes Critic's staleness-risk finding). | ~250 | A2, B |
| **E** | **Bootstrap assertions (§3.5 A1/A2/A3) + migration-version gate + chained-compound guard**: implement as functions called from `assertSystemFieldsConsistency`. | Unit tests that each assertion can fire with a crafted bad state AND stay green on a clean one. | ~150 | A1 |
| **F** | **Staging reproduction scene (§3.6)**: combined e2e-api scene `user-create-with-legacy-state/*` that clones staging-like state (155 discovered cols + 62 quoted defaults via fixture SQL) and runs end-to-end flows. This is the AC-7 verification gate. | Passes after A1→A3, B, C, D all land | ~200 (fixture+asserts) | A1, A2, A3, B, C, D |
| ~~G~~ | ~~Admin UI to promote discovered → custom — deferred to follow-up~~ | — | future | — |

**Total**: ~1700 LOC across 8 PRs, 5-7 days with careful PR sequencing + reviews. Each PR independently revertable. A1 is safe in isolation (purely additive schema + data); a freeze at any post-A1 point leaves the system better than today.

---

## 5. Verification log (running)

### Source code
- ✅ `sobject_records.go:410-460` — validateRequiredForCreate is the only emitter of `required field(s) missing: %s` (agent verified + grep confirmed)
- ✅ `metadata_service.go:61` (atomic.Pointer cache declaration), `:362` `InvalidateCache`, `:371` `invalidateCacheLocked` — COW single-cache, no torn-read. *(v1.2 mistakenly cited line 24 which is the inner field; actual cache is the atomic pointer at line 61.)*
- ✅ `salesforce_dtos.go:88` — `CompoundFieldName *string` DTO field already exists (SF compat, verified against real SF v63.0 describe shape per CLAUDE.md Axiom 1)
- ⚠️ `persistence_insert.go:167-176` — **per-object ad-hoc Quote.Name fallback**, NOT a generic compound precedent. v1.2 mischaracterized; v1.3 plan subsumes it by declaring Quote.Name compound with `composedFrom=["QuoteNumber"]` and deleting this block in PR C
- ✅ `describeCapabilities.ts:9-15` + `MetadataRecordForm.tsx:84-101` — createable=false filters frontend form rendering
- ✅ `usePermissionAnalyzer.ts:20-24` — frontend explicitly derives User display name from FirstName+LastName ("Salesforce convention" per comment) — SF parity for compound User.Name confirmed

### DB state (staging TiDB Cloud)
- ✅ User physical columns: 191 (massive legacy bloat)
- ✅ `_System_Field.User.Name` exists with Required=0, IsNameField=1, IsSystem=1, Createable=1, DefaultValue=NULL
- ✅ 62 total rows across DB have DDL-wrapped DefaultValue literals (56 on system objects, 6 on User)
- ✅ 155 User columns are declaration-less (not in system_tables.json, not `__c` custom, not system-required)
- ✅ Data in discovered columns: UserPermissionsMarketingUser=1 on 195/323 users, similar high values on EmailPreferencesAutoBcc, ActivityRemindersPopup — real data exists
- ✅ No backend or frontend feature code reads these specific columns (grep outside tests/JSON/generated = 0 hits) — data is inert
- ✅ Name provenance: 197 users match `FirstName + LastName`, 125 NULL, 1 custom ("Sales User" with NULL First/Last)

### Runtime (local backend)
- ✅ Claim #3 live reproduced: `GET /describe/User` returns `TimeZoneSidKey.defaultValue="'America/Los_Angeles'"`, `IsActive.defaultValue='1'`, etc.
- ✅ Claim #1 mechanism reproduced: POST User without Name + injected Name row with Required=1 + cache invalidation → 400 with exact staging-matching message
- ✅ Baseline (no Name row, clean): POST User without Name → 201

---

## 6. Decision log (running)

| Date | Decision | Rationale |
|---|---|---|
| 2026-04-21 | **Reject** "TRUNCATE + rebuild _System_Field from declarations" | Agent 3 identified 11 columns (AutoNumberFormat/Scope/Start/TokenRefs/CanonicalHash/CounterResetPolicy, Formula/ReturnType/FormulaMode, RollupConfig, Validator) that are admin/runtime state and cannot be reconstructed |
| 2026-04-21 | **Reject** "5-PR band-aid plan" (describe sanitize + payload filter + etc.) | Treats symptoms not class of bug; doesn't introduce Source concept; would yield 4th regression in 3 months |
| 2026-04-21 | **Adopt** Source + Compound + TypedValue as three first-class concepts | Maps to SF ManageableState (Source), SF compound field (Compound), SF typed default (TypedValue); reuses 3 existing scaffolding patterns |
| 2026-04-21 | ~~**Adopt** lax compound resolver~~ | **Superseded in v1.3**: SF Agent A research + code read confirmed SF strips explicit writes to createable=false fields. Replaced by strict resolver (§3.2) that drops caller's Name and derives from members. Sales User edge case handled via separate data-spec. |
| 2026-04-21 | **Adopt** strict compound resolver + Source-aware ComputeIntrinsic | After team review: v1.2 assumed setting `Createable=0` in `_System_Field` would propagate; code audit showed `ComputeIntrinsic` is live-authority (`capabilities.go:20-55`), not the stored column. Fix: extend ComputeIntrinsic with Source and IsCompound layers (§3.9). |
| 2026-04-21 | **Adopt** column DEFAULT 'discovered' + BackfillSource idiom | Reviewer identified 4 raw-SQL write paths that bypass `SaveFieldMetadataWithIDs`. Patching per-call-site is fragile. Pattern already exists in codebase (`BackfillFieldCapabilities` at `discover_tables.go:822`) — reuse it for Source. |
| 2026-04-21 | **Move quote-leak fix to `buildFieldMetadataFromColumn`** | Reviewer identified the round-trip boundary between ColumnDefinition (DDL-quoted) and FieldMetadata is the real leak origin, not `sobject_describe.go:163` (which only passes through). Fix placed at `schema_field_metadata_builder.go:69-71`. |
| 2026-04-21 | **v1.4 [round-2]: Extend BackfillFieldCapabilities SELECT to include `Source, IsCompound`** | Three reviewers independently verified `discover_tables.go:851` builds minimal FieldMetadata without Source/IsCompound, making Layers 1d/1e dead at backfill. Surgical fix: extend the SELECT + scan + struct. |
| 2026-04-21 | **v1.4 [round-2]: Enforce RunRepair ordering `Discover → BackfillSource → BFC`** | Ordering is load-bearing. Wrong order writes `Createable=false` for all fields on first apply (because all rows transiently have `Source='discovered'` from DEFAULT). |
| 2026-04-21 | **v1.4 [round-2]: Drop transactional-atomicity claim for migration** | TiDB DDL auto-commits. Actual protection is idempotency + `_System_Migration` version flag + explicit `WHERE Source='discovered'` guards on UPDATE. |
| 2026-04-21 | **v1.4 [round-2]: `normalizeDDLDefaultToTyped` package placement** | Moved from `shared/pkg/fieldcapabilities/` (wrong: that's about Createable computation) to new `shared/pkg/fieldnormalize/` (value parsing/normalization). |
| 2026-04-21 | **v1.4 [round-2]: Layout endpoints use live `ComputeIntrinsic`** | `sobject_describe.go:485` and `uiapi/primitives.go:326` read stored `f.Createable` directly, causing staleness after Source changes. PR D scope now includes replacing with live calls. Also closes pre-existing TODO at `primitives.go:32`. |
| 2026-04-21 | **v1.4 [round-2]: SQL-escape un-escape step in migration** | Regex `^'.*'$` + SUBSTRING leaves `'O''Reilly'` as `O''Reilly`. Migration adds explicit `UPDATE ... REPLACE(DefaultValue, "''", "'")` step to match Go helper semantics. |
| 2026-04-21 | **Defer** discovered-to-custom promotion UX | Orthogonal to bug fix, can ship later |
| 2026-04-21 | **Defer** transaction wrapping of DDL + metadata INSERT | Pre-existing issue not caused by this fix; separate PR |

---

## 7. Open gaps — status after research round 2

| Gap | Resolution | Evidence |
|---|---|---|
| **G-1** ✅ CLOSED | **SF's CustomField IS composite.** `tooling_metadata_handler.go:29-70` shows `toolingCustomFieldMetadata` matches SF's real POST /sobjects/CustomField shape — declaration + formula + picklist + autonumber attrs in ONE object. No separate FormulaDefinition/RollupDefinition/AutoNumberDefinition types exist as siblings to CustomField in SF. **Store decomposition would misalign with SF, not align.** My Source-column approach is correct. | Agent A report |
| **G-2** ✅ CLOSED | **`compoundFieldName` is SF-standard.** `salesforce_dtos.go:88` `SObjectField.CompoundFieldName` was built against real SF v63.0 describe. SF populates it on *child* fields (FirstName gets `compoundFieldName: "Name"`, LastName gets `compoundFieldName: "Name"`); parent Name field has it as null. | Agent A report |
| **G-3** ⚠️ **RESOLVED with DESIGN CORRECTION** | **SF rejects explicit writes to compound parent fields.** Real SF `User.Name` describe: `calculated: false, createable: false, updateable: false, nameField: true, compoundFieldName: null`. Our frontend also treats Name as read-only (never sends on create). **My "lax compound resolver preserving caller's Name" was WRONG** — must strip/reject explicit Name writes. See §3.2 correction below. | Agent A report |
| **G-4** ⚠️ designing | See §3.4 below — terminal discovered state with explicit "ManageableState-equivalent" semantics |
| **G-5** ✅ CLOSED | **FLS is already correctly decomposed** into `FieldPermissions` table (`bootstrap/system_tables.json` + `features/permissions/field_access_resolver.go:72`). Not on `_System_Field`. No mixing. | Agent B report |
| **G-6** ⚠️ designing | See §3.5 below — three bootstrap-time SQL invariant assertions |
| **G-7** ⚠️ designing | See §3.6 below — e2e-api `user-create-with-legacy-state` scene plus a staging-clone fixture |
| **G-8** ⚠️ **VIOLATION CONFIRMED — out of scope** | NexusCRM has NO domain-layer `FieldMetadata` entity; `models.FieldMetadata` is a shared DTO. Bootstrap (`schema.go`, `discover_tables.go`) writes raw SQL to `_System_Field`. Autonumber seed is raw SQL. Clean Architecture layer violations pre-exist this bug fix. **Tracked as follow-up RFC, not blocking**. AC-2 reinterpreted as "writes target the correct store (declaration vs FLS vs layout vs etc.)" which IS satisfiable today. | Agent B report |
| **G-9 [NEW v1.3, refined v1.4]** ⚠️ pre-migration safety | Flows referencing discovered columns and/or Quote.Name could break under PR A2 (ComputeIntrinsic flips createable→false) or PR C (compound resolver overrides explicit Name writes). | **Timing**: this gate runs **before PR A2 merges** to production, NOT before PR A1 (A1 is additive-schema only, doesn't change runtime). <br><br>**Query 1 — discovered-column flow refs**:<br>`SELECT Id, MasterLabel, DeveloperName, Definition FROM _System_Flow WHERE Definition LIKE '%UserPreferences%' OR Definition LIKE '%UserPermissions%' OR Definition LIKE '%EmailPreferences%' OR Definition LIKE '%HasUser%';`<br><br>**Query 2 — Quote.Name flow refs (PR C regression risk)**:<br>`SELECT Id, MasterLabel, Definition FROM _System_Flow WHERE Definition LIKE '%Quote%' AND Definition LIKE '%Name%';`<br><br>**Action tree** (explicit per round-2 Critic feedback):<br>• For each hit, inspect `Definition` to determine **read vs write**. Read-only refs (conditions, formulas evaluating the field) are **safe** — `createable=false` affects only the insert/update gate, not reads.<br>• **Write refs** (RecordCreate / RecordUpdate element writing to the field): must be modified (remove the write, or repoint to a declared field) BEFORE PR A2 ships. Add pre-deploy gate in the A2 PR description.<br>• For Quote+Name: if any flow explicitly writes Quote.Name in a beforeCreate trigger (expecting to override autonumber-derived value), flag to owner — PR C's strict compound resolver will overwrite this. Options: (a) change the flow to write QuoteNumber instead; (b) carve out a `.Override` annotation on compound fields (scope creep, defer).<br><br>Query run as one-shot SQL before PR A2 deploy; results pasted into PR A2 description. Absent access to production DB, run against staging and document reasoning to generalize.  |

**All G-1 through G-5 closed; G-4/G-6/G-7 being designed now in §3.4-3.6. G-8 deferred.**

### Design correction from G-3

**OLD (wrong)**: "Lax compound resolver — preserve caller's Name if sent, else derive"

**NEW (SF-correct)**: Compound parent fields are **read-only on write**. Resolver:
```pseudo
before insert/update on object O:
    for field f in registry.compoundFields(O):
        if f.APIName in data:
            silently drop data[f.APIName]  # SF behavior: createable=false → stripped
        parts = [data[src] for src in f.ComposedFrom]
        if all(parts are null/empty):
            continue  # resolver cannot derive; leave field untouched
        data[f.APIName] = TRIM(parts joined by " ")
```

- describe reports compound parent as `createable=false, updateable=false, calculated=false, nameField=true`
- `validateRequiredForCreate` skips `IsCompound=true` (already planned)
- The 1 staging "Sales User" edge case (Name set, FirstName/LastName NULL) is a pre-existing **data integrity violation** — covered by separate migration that sets `LastName='Sales User'` on that row, or leaves as legacy data (Name becomes stale on next save — acceptable since SF would require LastName anyway)

---

## 8. Alternatives considered and rejected

### Alt 1: Patch only Name in UserEditorModal (hardcode `Name = FirstName + ' ' + LastName`)
- ❌ Per-object shim; doesn't address Contact/Lead/Person Account with same problem
- ❌ Front-end only; doesn't fix backend's false "required" error
- ❌ Doesn't address SQL quote leak or payload bloat
- This is what PR #1565 partially did for the rendering side

### Alt 2: Drop the 155 declaration-less physical columns
- ❌ UserPreferences/Permissions columns have real data (195 users with MarketingUser=true, etc.)
- ❌ Drop is destructive and irreversible; if any integration reads them we don't know about, we silently break
- ✅ Better: mark them `Source='discovered', createable=false, updateable=false` so they survive but don't leak into write paths

### Alt 3: Rebuild `_System_Field` as pure projection (TRUNCATE + rewrite each bootstrap)
- ❌ Loses 11 non-reconstructible columns (admin/runtime state)
- ❌ Much larger blast radius than needed to fix the bugs
- ❌ Would require a full admin-edit replay mechanism

### Alt 4: Add invariant enforcement to existing `_System_Field` write paths only
- ❌ Doesn't give downstream layers (describe, frontend, validator) a way to reason about provenance — they'd still need to guess
- ❌ Doesn't introduce the compound-field concept, so Name bug resurfaces when physical Name column flips to NOT NULL

### Why the chosen design is superior to all 4
- Doesn't destroy state (unlike Alt 3)
- Doesn't only gate writes (unlike Alt 4) — gives every consumer a provenance signal
- Closes the class of bug (unlike Alt 1/2)
- SF-philosophy aligned (ManageableState + compound + typed default) — unlike any of them

---

## 9. Implementation timeline (to be updated)

| Date | Milestone | Status |
|---|---|---|
| 2026-04-21 | RFC drafted & verified | ✅ done |
| TBD | PR A (Source column + migration) opened | ⏳ |
| TBD | PR A merged | ⏳ |
| TBD | PR B (compound declaration parsing) opened | ⏳ |
| TBD | PR B merged | ⏳ |
| TBD | PR C (validator + resolver) opened | ⏳ |
| TBD | PR C merged | ⏳ |
| TBD | PR D (describe layer changes) opened | ⏳ |
| TBD | PR D merged | ⏳ |
| TBD | PR E (e2e-api cases) opened | ⏳ |
| TBD | PR E merged | ⏳ |
| TBD | staging smoke-test: POST User (no Name) → 201 | ⏳ |
| TBD | Issue #1582 closed | ⏳ |

---

## 10. Appendix — evidence summaries from research agents

### Agent 1 (Admin field CRUD)
- Single write gateway: `SaveFieldMetadataWithIDs` (UPSERT) — zero backdoors
- Admin-editable columns: Label, Required, IsUnique, HelpText, DefaultValue, Options, MinLength/MaxLength, ReferenceTo, Formula, ReturnType
- System fields (IsSystem=true) are immutable for Type/Required/IsUnique
- Cache invalidation AFTER DB write — correct order
- DDL + metadata INSERT auto-commit separately (no txn wrap — pre-existing issue)

### Agent 2 (Bootstrap reconcile paths)
- 11 reconcile functions in schema.go + discover_tables.go
- Only DELETE path is `ReconcileOrphanFieldObjectIds` — deletes only when ObjectId has no recoverable ApiName
- No path deletes "declaration-less field with no physical column"
- `assertSystemFieldsConsistency` checks declared fields exist, not "extra fields don't exist"

### Agent 3 (Autonumber/formula/rollup)
- 11 columns on `_System_Field` hold admin/runtime state unrecoverable from declarations:
  AutoNumber family × 6, Formula family × 3, RollupConfig × 1, Validator × 1
- Runtime counter is on separate `_System_AutoNumberCounter`
- `TRUNCATE + rebuild` would destroy these — rejected

### Agent 4 (metadata_service.go + SF import)
- 13 `InvalidateCache` call sites, all AFTER DB write
- Zero backdoors writing `_System_Field` outside SchemaManager
- Atomic.Pointer COW cache — no torn state
- No SF import paths found in current code

### Agent 5 (declaration specs merge)
- Three declaration sources: `system_tables.json` (system + User), `standard_objects_spec.json` (CRM objects), `tenant_objects_spec.json` (overrides)
- No overlap — each object lives in exactly one spec
- Merge is sequential phases, not conflict resolution
- `isNameField` + `referenceTo` already in schema; no `isCompound` yet

### Agent 6 (staging orphan scan)
- User/Account/Contact/Lead/Opportunity metadata = physical columns exactly (no structural orphans)
- 62 rows with DDL-wrapped DefaultValues across all objects
- 503 rows Required=1 + DefaultValue=NULL (492 on system objects, 11 on core objects — all legitimate SF-required fields)
- No `Source` column exists (my proposed addition)

---

**End of RFC (v1.0 — 2026-04-21)**