# System Fields Mandatory Contract

Status: draft
Scope class: bootstrap invariant
Purpose: document the `assertSystemFieldsConsistency` check enforced by
`cmd/setup apply` and the canonical column shape every managed table must
declare so the assertion passes
Date: 2026-05-07

## 1. Purpose

Every managed table declared in `backend/internal/bootstrap/system_tables.json`
(or returned by `bootstrap.GetManagedTableDefinitions`) is required to expose
the system temporal fields `CreatedDate` and `LastModifiedDate` in metadata.
The check is enforced by `assertSystemFieldsConsistency` in
`backend/internal/bootstrap/assertions.go:107` and runs at the end of
`cmd/setup apply`. Strict mode causes any violation to fail the bootstrap.

This document captures the contract so future contributors do not silently
break it by inventing alternative column shapes (e.g. `CreatedAt`,
`InsertedAt`, `Timestamp`) on a new system table.

## 2. The check

### 2.1 Entry point

```
backend/internal/bootstrap/assertions.go:107  assertSystemFieldsConsistency
```

For each managed table:

1. read its rows from `_System_Field` joined to `_System_Object`
2. ensure every name returned by `constants.GetSystemFieldNames()` is present

If any required name is missing, append a `SystemFields` violation. In strict
mode (the bootstrap default for `cmd/setup apply`), at least one
`SeverityError` violation aborts with:

```
verify: assertions: internal error: assertion failures in strict mode:
N critical violation(s) and 0 warning(s)
```

### 2.2 Source of truth for the required field set

```
shared/pkg/constants/tables.go:56  GetSystemFieldNames
```

Currently:

```go
func GetSystemFieldNames() []string {
    return []string{
        FieldCreatedDate,      // "CreatedDate"
        FieldLastModifiedDate, // "LastModifiedDate"
    }
}
```

To extend the list, edit this function. Do not duplicate the list at the
callsite in `assertions.go` — there is exactly one source of truth.

### 2.3 What is and is not checked

The whitelist used by the assertion is built from
`bootstrap.GetManagedTableDefinitions()`, which loads
`backend/internal/bootstrap/system_tables.json`. Anything outside that
whitelist is exempt by design:

- Discovered tables registered by `DiscoverAndRegisterPhysicalTables`
  (their `_System_Object.Id` carries the `disc_` prefix).
- Salesforce-imported legacy tables with UUID-style `_System_Object.Id`
  values (e.g. `addresshistory`, `quotehistory`, `userhistory`,
  `orgemailaddresssecurity`). Many of these legitimately lack one or both
  temporal columns by Salesforce design (`UserHistory` has no
  `LastModifiedDate`).
- Stale managed registrations whose ApiName has since been removed from
  `system_tables.json` (e.g. `_Dev_Session`).

Rationale: `system_tables.json` is the single source of truth for tables
NexusCRM owns. Tables outside it have schemas defined externally and cannot
be coerced to the platform convention without breaking imports.

## 3. The mandatory column shape

Every entry added to `backend/internal/bootstrap/system_tables.json` must
declare both columns in the canonical form:

```json
{
  "name": "CreatedDate",
  "label": "Created Date",
  "type": "DATETIME",
  "nullable": false,
  "default": "CURRENT_TIMESTAMP"
},
{
  "name": "LastModifiedDate",
  "label": "Last Modified Date",
  "type": "DATETIME",
  "nullable": false,
  "default": "CURRENT_TIMESTAMP"
}
```

Examples in current tree: `system_tables.json` lines 178-189 (`_System_Object`),
467-478 (`_System_Field`), 637-654 (`_System_Profile`).

### 3.1 Why both columns, even on append-only tables

Append-only audit tables (e.g. `_System_FlowAuditLog`,
`_System_RequestCapture`) only capture an insertion timestamp at the
domain level. The platform still requires `LastModifiedDate` for two
reasons:

1. **Uniformity**: every UI surface, retention runner, and metadata
   introspection path can assume both fields exist on any managed table.
   Per-table exemptions would push branchy code into every consumer.
2. **No code cost**: `DEFAULT CURRENT_TIMESTAMP` populates the column at
   insert with no writer changes. There is intentionally no UPDATE path —
   append-only semantics are preserved by simply never issuing UPDATE
   statements against the table.

In other words: declare both, write `CreatedDate`, ignore
`LastModifiedDate`. They will remain equal for the row's lifetime and that
is the documented behavior.

### 3.2 Forbidden alternatives

Do not invent alternative timestamp column names on managed tables:

- `CreatedAt`, `InsertedAt`, `Timestamp`, `RecordedAt` — none of these
  satisfy `GetSystemFieldNames()`.
- A single timestamp column instead of two is also a violation; the
  assertion checks both names independently.

If you have a strong reason a managed table genuinely cannot have
`CreatedDate` / `LastModifiedDate` (e.g. external data shape we cannot
modify), the correct path is to remove it from the managed whitelist and
register it as discovered or legacy — not to weaken the assertion.

## 4. Author checklist

Before merging a PR that adds an entry to `system_tables.json`:

1. The new table declares both `CreatedDate` and `LastModifiedDate`
   columns in the canonical shape (§3).
2. Any index that referenced a non-canonical name (e.g. `idx_created_at`
   on `CreatedAt`) is renamed and re-pointed to `CreatedDate`.
3. `cmd/setup apply` runs to completion against a clean L0 bootstrap and
   prints `✅ All assertions passed`.
4. No code path issues `UPDATE` against `LastModifiedDate` on
   append-only tables.

## 5. Motivating incident

PR #265 (`[NEEDS-HUMAN] feat(automation): Pillar 5 audit log —
_System_FlowAuditLog + emit hooks`) declared `_System_FlowAuditLog` with a
single column named `CreatedAt`, omitting `CreatedDate` and
`LastModifiedDate`. After merge, every clean bootstrap rebased onto `main`
failed strict-mode assertion:

```
[error] SystemFields: Table '_System_FlowAuditLog' missing system fields
in metadata: CreatedDate, LastModifiedDate
```

This blocked the event-handler `pr_fixer` pipeline (which has no recovery
path) and forced `pr_review` into a `SKIP_BOOTSTRAP=true` workaround.
Issue #277 patched the table to the canonical shape and produced this
document so the same regression class does not recur.