---
apiVersion: data-spec/v1
id: system-tables-missing-date-columns-20260409
description: "Add CreatedDate/LastModifiedDate to 16 managed system tables (JSON + metadata backfill)"
issue: "#474"
pr: "#709"
created: 2026-04-09
scope: bootstrap
status: pending
tables:
  - _System_ConnectedApp
  - _System_LoginHistory
  - _System_Migration
  - _System_MutingPermissionSetPermission
  - _System_OAuthCode
  - _System_PageLayoutHistory
  - _System_SetupAuditTrail
  - _System_SystemLog
  - Dashboard
  - DashboardComponent
  - Notification
  - PermissionSetCustomPermission
  - PermissionSetGroupAssignment
  - PermissionSetGroupComponent
  - ProfileCustomPermission
  - QueueSobject
  - _System_Field
depends_on: []
---

# Managed System Tables — Missing Date Column Backfill

## Summary

16 tables declared in `backend/internal/bootstrap/system_tables.json` were
missing `CreatedDate` and/or `LastModifiedDate` column definitions. This
caused `assertSystemFieldsConsistency` to report 18 `SystemFields`
assertion violations during bootstrap (2 tables missing both, 1 missing
only CreatedDate, 13 missing only LastModifiedDate).

The JSON patch landed in commit `5b6ce77a` as part of PR #709. This spec
records the corresponding data change for environments that were
bootstrapped before the commit — they will pick up the fix automatically
on next backend restart via the bootstrap reconcile path (see Changes
sections 2 and 3 below).

Root cause: the JSON entries were authored incompletely — different authors
at different times added tables without the full set of required system
fields. Bootstrap is literal-pass-through of JSON → DDL → metadata
registration; there is no auto-injection of system fields anywhere in the
pipeline. See `assertions_metadata.go` investigation notes under issue #474.

This spec patches `system_tables.json` in the code commit. Existing
environments pick up the change automatically on next bootstrap run via:

1. **`reconcileSystemTableColumns`** (schema.go:204) — additive-only
   `ALTER TABLE ADD COLUMN` for physical columns newly declared in JSON.
2. **`reconcileSystemFieldMetadata`** (schema.go:345) — idempotent insert
   into `_System_Field` for metadata rows newly declared in JSON.

Both reconcile steps run on every boot and are safe to re-run. No manual
SQL is required for existing environments — a simple backend restart
applies the fix.

## Physical vs Metadata State (verified 2026-04-09)

| Table | JSON before | Physical before | Metadata before | After next bootstrap |
|-------|:---:|:---:|:---:|:---:|
| `_System_ConnectedApp` | ✗ ✗ | ✗ ✗ | ✗ ✗ | ✓ ✓ (ALTER + INSERT) |
| `_System_Migration` | ✗ ✗ | ✗ ✗ | ✗ ✗ | ✓ ✓ (ALTER + INSERT) |
| `_System_SystemLog` | ✗ ✗ | ✗ ✗ | ✗ ✗ | ✓ ✓ (ALTER + INSERT) |
| `Dashboard` | ✗ ✗ | ✓ ✓ | ✗ ✗ | ✓ ✓ (INSERT only) |
| `DashboardComponent` | ✗ ✗ | ✓ ✓ | ✗ ✗ | ✓ ✓ (INSERT only) |
| `Notification` | ✗ ✓ | ✗ ✓ | ✗ ✓ | ✓ ✓ (ALTER + INSERT) |
| `_System_LoginHistory` | ✓ ✗ | ✓ ✗ | ✓ ✗ | ✓ ✓ (ALTER + INSERT) |
| `_System_MutingPermissionSetPermission` | ✓ ✗ | ✓ ✗ | ✓ ✗ | ✓ ✓ (ALTER + INSERT) |
| `_System_OAuthCode` | ✓ ✗ | ✓ ✗ | ✓ ✗ | ✓ ✓ (ALTER + INSERT) |
| `_System_PageLayoutHistory` | ✓ ✗ | ✓ ✗ | ✓ ✗ | ✓ ✓ (ALTER + INSERT) |
| `_System_SetupAuditTrail` | ✓ ✗ | ✓ ✗ | ✓ ✗ | ✓ ✓ (ALTER + INSERT) |
| `PermissionSetCustomPermission` | ✓ ✗ | ✓ ✗ | ✓ ✗ | ✓ ✓ (ALTER + INSERT) |
| `PermissionSetGroupAssignment` | ✓ ✗ | ✓ ✗ | ✓ ✗ | ✓ ✓ (ALTER + INSERT) |
| `PermissionSetGroupComponent` | ✓ ✗ | ✓ ✗ | ✓ ✗ | ✓ ✓ (ALTER + INSERT) |
| `ProfileCustomPermission` | ✓ ✗ | ✓ ✗ | ✓ ✗ | ✓ ✓ (ALTER + INSERT) |
| `QueueSobject` | ✓ ✗ | ✓ ✗ | ✓ ✗ | ✓ ✓ (ALTER + INSERT) |

Column definition added (canonical Salesforce-compatible shape):

```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"}
```

## Changes

### 1. JSON patch (code commit)

The 16 table entries in `backend/internal/bootstrap/system_tables.json` are
modified to include the two missing date columns (one or both per table,
per the matrix above). This is a code change, not a SQL change — applied
by merging the PR.

### 2. Physical column backfill (automatic on next bootstrap)

`reconcileSystemTableColumns` compares `system_tables.json` against
`information_schema.columns` and issues:

```sql
ALTER TABLE `<TableName>` ADD COLUMN `CreatedDate` DATETIME NOT NULL
  DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE `<TableName>` ADD COLUMN `LastModifiedDate` DATETIME NOT NULL
  DEFAULT CURRENT_TIMESTAMP;
```

...for every table that still lacks the column. Only the 10 tables whose
physical column is also missing will receive ALTER TABLE statements; the
other 6 (Dashboard, DashboardComponent, and any already in sync) are no-ops.

**Idempotency**: `reconcileSystemTableColumns` guards on
`information_schema.columns` — it only ALTERs when the column does not
exist. Safe to run on every boot.

**Data impact**: `DEFAULT CURRENT_TIMESTAMP` fills existing rows with the
time of ALTER. For append-only audit tables (`_System_LoginHistory`,
`_System_SetupAuditTrail`, etc.) `LastModifiedDate` will equal the ALTER
timestamp for all historical rows — this is acceptable because these rows
never get updated, so the value is inherently meaningless beyond "existed
as of this ALTER". Normal runtime writes will populate correctly going
forward.

### 3. Metadata backfill (automatic on next bootstrap)

`reconcileSystemFieldMetadata` inserts missing rows into `_System_Field`
for every JSON-declared column that does not yet have metadata. Effectively:

```sql
-- Executed once per missing (object, field) pair; function bodies in
-- schema.go:345 handle the loop and deduplication.
INSERT INTO _System_Field
  (Id, ObjectId, ApiName, Label, PhysicalName, Type, Required, IsUnique,
   IsSystem, IsNameField, Createable, Updateable, Indexed)
SELECT
  <deterministic_id>,   -- schema.GenerateFieldID(obj, field)
  o.Id,
  'CreatedDate',        -- or LastModifiedDate
  'Created Date',       -- or Last Modified Date
  'CreatedDate',
  'datetime',
  1, 0, 1, 0, 0, 0, 0
FROM _System_Object o
WHERE o.ApiName = '<TableName>'
  AND NOT EXISTS (
    SELECT 1 FROM _System_Field f
    WHERE f.ObjectId = o.Id AND f.ApiName = 'CreatedDate'
  );
```

**Idempotency**: `NOT EXISTS` guard + deterministic primary key from
`schema.GenerateFieldID()` ensures re-running is a no-op.

## Rollback

Rolling back is done by reverting the JSON change in the PR commit. The
physical columns added by `reconcileSystemTableColumns` can remain — they
are additive and do not break anything. If you want to remove them
explicitly (not recommended; the data is harmless):

```sql
-- For each table that received a new CreatedDate column:
ALTER TABLE `<TableName>` DROP COLUMN `CreatedDate`;

-- For each table that received a new LastModifiedDate column:
ALTER TABLE `<TableName>` DROP COLUMN `LastModifiedDate`;
```

And for metadata rollback:

```sql
-- Delete the metadata rows inserted by reconcileSystemFieldMetadata.
DELETE f FROM _System_Field f
JOIN _System_Object o ON o.Id = f.ObjectId
WHERE o.ApiName IN (<the 16 table names>)
  AND f.ApiName IN ('CreatedDate', 'LastModifiedDate')
  AND f.IsSystem = 1;
```

**Warning**: the metadata DELETE is destructive if field data has been
customized since the INSERT — it uses `ApiName` + `IsSystem=1` as the
match key. If unsure, target by `Id` instead using the deterministic IDs
returned by `schema.GenerateFieldID(<object>, 'CreatedDate')`.

## Verification

After the next bootstrap run, confirm the violations are gone:

```sql
-- Should return 0 rows (all 16 tables now have both fields in metadata)
WITH required_fields AS (SELECT 'CreatedDate' AS f UNION ALL SELECT 'LastModifiedDate')
SELECT o.ApiName, r.f AS missing_field
FROM _System_Object o
CROSS JOIN required_fields r
WHERE o.ApiName IN (
  '_System_ConnectedApp','_System_LoginHistory','_System_Migration',
  '_System_MutingPermissionSetPermission','_System_OAuthCode',
  '_System_PageLayoutHistory','_System_SetupAuditTrail','_System_SystemLog',
  'Dashboard','DashboardComponent','Notification',
  'PermissionSetCustomPermission','PermissionSetGroupAssignment',
  'PermissionSetGroupComponent','ProfileCustomPermission','QueueSobject'
)
AND NOT EXISTS (
  SELECT 1 FROM _System_Field f
  WHERE f.ObjectId = o.Id AND f.ApiName = r.f AND f.IsDeleted = 0
);
```

And the bootstrap assertion should report 0 `SystemFields` violations —
along with the companion assertion code changes in `assertions.go`
(whitelist-based check against `GetManagedTableDefinitions()`) this
completely eliminates the `SystemFields` category from strict-mode failures.