---
apiVersion: data-spec/v1
id: relax-required-uncreatable-fields-20260419
description: "Relax Required=0 on 13 _System_Field rows where Required=1 contradicts Createable=0 or lacks any UI entry point (short-term bootstrap-guarded patch)"
created: 2026-04-19
applied: 2026-04-19
scope: production-only
status: applied
applied_to:
  - staging (gateway01.us-east-1.prod.aws.tidbcloud.com:4000/nexuscrm, user 2JuXyXHYxHrLAUr.root)
  - prod (gateway01.us-east-1.prod.aws.tidbcloud.com:4000/nexuscrm, user 3C9pXLbjDaTBomw.root)
tables: [_System_Field]
depends_on: []
---

# Relax Required on Uncreatable / UI-Unreachable Fields — Data Spec

## Summary

Cross-referencing staging `_System_Field` against `_System_PageLayout.Config`
on 2026-04-19 found 14 rows where `Required=1` is structurally wrong:

| Kind | Count | Reason |
|------|------:|--------|
| A — `Required=1` + `Createable=0` + not system-managed | 12 | field can never be written by a user-created request, so requiring it on create is impossible to satisfy |
| B — `Required=1` + no DefaultValue + not on any PageLayout | 2 | no UI entry point; forms generated from `describe` would demand a value nothing can fill |

This spec flips `Required=0` for these rows and sets `IsCustomized=1` so the
next backend restart's bootstrap UPSERT does NOT re-compute `Required` back to
`1`. (On bootstrap, `buildFieldMetadataFromColumn` derives `Required =
!col.Nullable && !isSystem` and
`backend/internal/infrastructure/persistence/schema_metadata_persist.go:319-325`
updates `Required` only when `IsCustomized=0 OR NULL`.)

This is the **short-term** fix. A companion code PR extends
`reconcileDiscoveredFieldOverrides` (and adds a sibling
`reconcileSystemFieldOverrides`) so a fresh bootstrap produces the correct
state without needing the `IsCustomized=1` guard. Once that PR is deployed,
this spec becomes redundant but remains harmless (the override UPDATE is
unconditional and idempotent).

### Scope

Applied to staging first, then production after staging verification.

### NOT included in this spec

- `contact.LastName` (Required=1, no default, not on any layout). This is the
  Salesforce compound-Name pattern: UI renders `Name` which the persistence
  layer splits into FirstName + LastName. Whether the describe-driven form
  consumers are actually broken is a **frontend/describe** question being
  investigated separately — no metadata change until that's resolved.

## Changes

### 1. Relax 12 Required-but-Uncreatable fields (Kind A)

```sql
-- 1a) Campaign rollups (7 fields, no DefaultValue, not computed by NexusCRM)
UPDATE _System_Field f
JOIN _System_Object o ON o.Id = f.ObjectId
SET f.Required = 0, f.IsCustomized = 1
WHERE LOWER(o.ApiName) = 'campaign'
  AND f.ApiName IN (
    'AmountAllOpportunities',
    'AmountWonOpportunities',
    'NumberOfContacts',
    'NumberOfConvertedLeads',
    'NumberOfLeads',
    'NumberOfOpportunities',
    'NumberOfWonOpportunities'
  )
  AND f.Required = 1
  AND f.IsDeleted = 0;

-- 1b) Opportunity derived booleans (3 fields, have DefaultValue=False but are
--     computed; Required=1 + Createable=0 means any describe-validated create path breaks)
UPDATE _System_Field f
JOIN _System_Object o ON o.Id = f.ObjectId
SET f.Required = 0, f.IsCustomized = 1
WHERE LOWER(o.ApiName) = 'opportunity'
  AND f.ApiName IN ('HasOpenActivity', 'HasOpportunityLineItem', 'IsClosed')
  AND f.Required = 1
  AND f.IsDeleted = 0;

-- 1c) Order.StatusCode (picklist, default=Draft, Createable=0)
UPDATE _System_Field f
JOIN _System_Object o ON o.Id = f.ObjectId
SET f.Required = 0, f.IsCustomized = 1
WHERE LOWER(o.ApiName) = 'order'
  AND f.ApiName = 'StatusCode'
  AND f.Required = 1
  AND f.IsDeleted = 0;

-- 1d) User.FailedLoginAttempts (counter, default=0, Createable=0)
UPDATE _System_Field f
JOIN _System_Object o ON o.Id = f.ObjectId
SET f.Required = 0, f.IsCustomized = 1
WHERE o.ApiName = 'User'
  AND f.ApiName = 'FailedLoginAttempts'
  AND f.Required = 1
  AND f.IsDeleted = 0;
```

**Idempotency:** all four statements guard with `f.Required = 1` so re-applying
is a no-op (0 rows affected). `IsCustomized` is set alongside so the bootstrap
guard in `schema_metadata_persist.go` preserves these values across restarts.

**Safety:** metadata-only change, no physical DDL, no user data touched. These
fields were never writeable (`Createable=0`); all we change is whether
`describe` callers see them as `nillable=true` (they will, which matches
actual API behavior — you cannot pass these fields on insert anyway).

### 2. Relax User.Password (Kind B)

```sql
-- User.Password: Required=1, Createable=1, no default, not on any PageLayout.
-- In NexusCRM, passwords are set via invite / reset-password flows, not via
-- the metadata-driven user-create form. Any describe-driven form generator
-- that honors Required would demand a field the UI cannot collect.
UPDATE _System_Field f
JOIN _System_Object o ON o.Id = f.ObjectId
SET f.Required = 0, f.IsCustomized = 1
WHERE o.ApiName = 'User'
  AND f.ApiName = 'Password'
  AND f.Required = 1
  AND f.IsDeleted = 0;
```

**Note on the physical column:** `User.Password` stays `NOT NULL` at the DB
level (defined in `backend/internal/bootstrap/system_tables.json`). The actual
invite / login-reset code paths always supply a password hash, so flipping the
logical Required flag to 0 does not open a path that inserts NULL passwords.
The companion code PR does NOT change `system_tables.json`; it only adds a
reconcile UPDATE so fresh bootstraps converge on `Required=0` without
requiring `IsCustomized=1`.

**Idempotency:** `f.Required = 1` guard. No-op if already 0.

## Rollback

```sql
-- Restore Required=1 and clear the IsCustomized guard. This re-opens the
-- original metadata contradiction; only run if a regression is identified.
UPDATE _System_Field f
JOIN _System_Object o ON o.Id = f.ObjectId
SET f.Required = 1, f.IsCustomized = 0
WHERE (
  (LOWER(o.ApiName) = 'campaign' AND f.ApiName IN (
    'AmountAllOpportunities','AmountWonOpportunities','NumberOfContacts',
    'NumberOfConvertedLeads','NumberOfLeads','NumberOfOpportunities',
    'NumberOfWonOpportunities'
  ))
  OR (LOWER(o.ApiName) = 'opportunity' AND f.ApiName IN (
    'HasOpenActivity','HasOpportunityLineItem','IsClosed'
  ))
  OR (LOWER(o.ApiName) = 'order' AND f.ApiName = 'StatusCode')
  OR (o.ApiName = 'User' AND f.ApiName IN ('Password','FailedLoginAttempts'))
)
AND f.IsDeleted = 0;
```

Rolling back restores the pre-spec bug: `describe` emits `nillable=false` for
fields no UI can fill, and any metadata-driven form generator that honors the
flag blocks on them.

## Verification

**Before apply — capture current state (expect 14 rows, all Required=1):**

```sql
SELECT o.ApiName AS object, f.ApiName AS field, f.Required, f.Createable, f.IsCustomized
FROM _System_Field f
JOIN _System_Object o ON o.Id = f.ObjectId
WHERE (
  (LOWER(o.ApiName) = 'campaign' AND f.ApiName IN (
    'AmountAllOpportunities','AmountWonOpportunities','NumberOfContacts',
    'NumberOfConvertedLeads','NumberOfLeads','NumberOfOpportunities',
    'NumberOfWonOpportunities'
  ))
  OR (LOWER(o.ApiName) = 'opportunity' AND f.ApiName IN (
    'HasOpenActivity','HasOpportunityLineItem','IsClosed'
  ))
  OR (LOWER(o.ApiName) = 'order' AND f.ApiName = 'StatusCode')
  OR (o.ApiName = 'User' AND f.ApiName IN ('Password','FailedLoginAttempts'))
)
AND f.IsDeleted = 0
ORDER BY object, field;
```

**After apply — same query should show all 14 rows with `Required=0, IsCustomized=1`.**

**Restart-persistence check** (only possible after the companion code PR
lands, or by deliberately restarting the backend against this data):
1. Note `Required` and `IsCustomized` for each row.
2. Restart the backend (`docker compose restart backend` or equivalent).
3. Re-run the same SELECT. All 14 rows should still show `Required=0,
   IsCustomized=1` — proving the bootstrap UPSERT guard preserved the spec.

**Describe-level check:**
```bash
curl -s -H "Authorization: Bearer $TOKEN" \
  "$BASE/services/data/v63.0/sobjects/Campaign/describe" \
  | python3 -c "import sys,json; d=json.load(sys.stdin); \
    print([(f['name'], f.get('nillable')) for f in d['fields'] \
           if f['name'] in ('AmountAllOpportunities','NumberOfLeads')])"
```
Expected after apply: both fields show `nillable=True` (previously `False`).