# Metadata Reconciliation

How NexusCRM keeps database metadata (field labels, page layouts) in sync with code across deployments.

## Problem

NexusCRM stores metadata (field labels, page layouts) as rows in `_System_*` tables. When code improves the label algorithm or layout generation, existing databases keep the old values because bootstrap only inserts new rows, never updates existing ones.

This means: **code deploys, but data doesn't follow.**

## Solution

Two mechanisms, each with a different SSoT (Single Source of Truth):

| Metadata | SSoT | Mechanism |
|----------|------|-----------|
| Field labels | **Code** (`system_tables.json` + `fieldNameToLabel()`) | `reconcileFieldLabels` updates labels where `IsCustomized=0` |
| Page layouts | **Live database schema** | `generateSmartLayout` creates multi-section layouts from ALL fields in the object's schema |

Both use `IsCustomized` to protect admin customizations.

## Field Labels

### How it works

`reconcileFieldLabels()` runs as Phase 6 of `InitializeSchema` on every server start:

1. Reads desired labels from `system_tables.json` (351 explicit label definitions)
2. For discovered table fields where `Label == ApiName`, applies `fieldNameToLabel()` (camelCase split, `Id` suffix strip)
3. Only updates rows where `IsCustomized = 0`
4. Uses batch `CASE WHEN` SQL for performance (~5s)

### How to change a field label

Edit `system_tables.json`:

```jsonc
{
    "name": "AccountId",
    "label": "Account",          // ← change this
    "type": "VARCHAR(255)"
}
```

Next deploy: bootstrap detects `Label != desired` and updates it. No manual SQL needed.

## Page Layouts

### How it works

Page layouts are NOT defined in code. They are generated from the **live database schema** by `generateSmartLayout()` in `metadata_layouts.go`.

When a user accesses a record detail page:

```
GetLayout("Account")
  → Tier 1: Profile-assigned layout (DB)
  → Tier 2: Default layout for object (DB)
  → Tier 2b: Version check — if LayoutVersion < currentLayoutVersion
              AND IsCustomized=0 → delete stale layout, fall through
  → Tier 3: generateSmartLayout() → reads ALL fields from live schema
              → classifies into 5 sections → persists to DB
```

### Smart section classification

| Section | Rule |
|---------|------|
| **Information** | Name field, OwnerId, required fields, lookup/reference fields, core fields (Status, Type, Phone, Email, etc.) |
| **Address Information** | Fields matching Billing*, Shipping*, Mailing*, Street, City, State, PostalCode, Country |
| **Description** | TextArea / RichTextArea type fields |
| **Additional Details** | Everything else |
| **System Information** | CreatedById, CreatedDate, LastModifiedById, LastModifiedDate |

Only truly invisible fields are excluded: `Id`, `IsDeleted`, `SystemModstamp`. **OwnerId and audit fields ARE included.**

### Layout versioning

Every auto-generated layout stores a `LayoutVersion` integer. When the algorithm improves:

1. Developer bumps `currentLayoutVersion` constant in `metadata_layouts.go`
2. Next time a user accesses any object, `GetLayout` detects `LayoutVersion < currentLayoutVersion`
3. If `IsCustomized = 0`: deletes the stale layout, regenerates from live schema
4. If `IsCustomized = 1`: preserves the admin's layout untouched

**No manual SQL, no data-spec, no CI/CD step needed.** Algorithm improvements propagate automatically.

### Why layouts are NOT hardcoded in JSON

Earlier versions used a `standard_layouts.json` with hardcoded field lists. This was removed because:

- Hardcoded field lists can't know about customer-specific custom fields (200+ imported from Salesforce)
- Any field not listed in the JSON was invisible to users
- Required maintaining a separate JSON file for each object

The current approach reads ALL fields from the live schema, so **no field is ever hidden**.

## IsCustomized Protection

When an admin edits metadata through the UI or API:

| Action | Sets `IsCustomized` | Code path |
|--------|-------------------|-----------|
| Edit field label/type via Setup | `1` on `_System_Field` | `SaveFieldMetadataWithIDs()` |
| Edit page layout via Studio | `1` on `_System_PageLayout` | `HandleSaveLayout()` → `MarkLayoutCustomized()` |

Once `IsCustomized = 1`, neither `reconcileFieldLabels` nor `LayoutVersion` upgrades will touch that row.

**How to reset a customization (give control back to code):**

```sql
-- Reset a field label
UPDATE _System_Field SET IsCustomized = 0 WHERE ObjectId = '<obj_id>' AND ApiName = 'MyField';

-- Reset a page layout (will regenerate on next access)
UPDATE _System_PageLayout SET IsCustomized = 0, LayoutVersion = 0 WHERE ObjectApiName = 'Quote';
```

## Bootstrap Execution Order

```
InitializeSchema(db)
  ├── Phase 0: reconcileSystemTableColumns    — adds missing columns (incl. IsCustomized, LayoutVersion)
  ├── Phase 1: create/register system tables
  ├── Phase 2: register system object metadata
  ├── Phase 3: reconcileSystemFieldMetadata   — adds missing field rows
  ├── Phase 4: ensurePluralLabelsAndNameFields
  ├── Phase 5: ensureStandardAutoNumbers
  └── Phase 6: reconcileFieldLabels           — updates stale labels (IsCustomized=0 only)

CleanupHardcodedLayouts(db)                   — one-time removal of std_layout_* rows

[Server starts accepting requests]

GetLayout (on first access per object)        — Tier 3 generateSmartLayout if needed
```

## Performance

| Operation | First run | Warm restart |
|-----------|-----------|-------------|
| Field label reconciliation | ~5s (345 labels fixed) | ~5s (0 updates) |
| Layout generation | ~100ms per object (on first access) | 0ms (cached in DB) |
| Layout version upgrade | ~100ms per object (when version bumped) | 0ms (already current) |

## Troubleshooting

**Q: I changed a label in `system_tables.json` but it didn't update on staging.**
A: Check if the field has `IsCustomized = 1`. If so, an admin edited it previously. Reset with `UPDATE _System_Field SET IsCustomized = 0 WHERE ...`.

**Q: I improved the layout algorithm but existing layouts didn't change.**
A: Bump `currentLayoutVersion` in `metadata_layouts.go`. Non-customized layouts will regenerate on next access.

**Q: A field doesn't appear in the layout even though it exists in the schema.**
A: Check if it's marked `IsSystem = true` AND matches `isLayoutInvisibleField()` (only Id, IsDeleted, SystemModstamp are hidden). OwnerId and audit fields should appear. If a field is genuinely missing, it may be a classification bug in `generateSmartLayout`.

**Q: How do I see what labels were updated on last restart?**
A: Check server logs for `Field label reconciliation complete: X system + Y algorithmic labels updated`.

**Q: How do I force a layout to regenerate?**
A: `UPDATE _System_PageLayout SET IsCustomized = 0, LayoutVersion = 0 WHERE ObjectApiName = 'MyObject'`. Next access triggers Tier 2b → delete → Tier 3 regeneration.