# Each fact once: the normal form and its guard

Handbook 1.2, rules 5.7–5.9; plan SN (`reji/plans/2026-10-30-CORE-schema-normalisation.md`). Every schema is in third
normal form at least, so each fact is stored once and can't drift: a list is rows of a child table, a referenced row's
fact is read through the reference, and nothing depends on part of a key. Not drift, and allowed: a snapshot of the terms
a record was decided under (3.4), a trail's copied name (7.4), and a key copied for isolation, the workspace on a child,
when the database ties it to its parent's.

## The guard

`tests/Feature/Guards/NormalFormGuardTest.php` reads the migrated test database's catalogue, like `TenantGuardTest`. The
file is the same in every Tutelora app and names no table; each app's facts are in `tests/Support/normal-form.php`.

| Rule | Fails on | Passes when |
|---|---|---|
| **tie** (5.8) | A foreign key from a workspace-owned row to a workspace-owned row that doesn't hold the workspace on both sides; a polymorphic link (`x_id` beside `x_type`) on a workspace's row tied to no parent | The key is `(link_id, tenant_id) → parent (id, tenant_id)` (the workspace row itself pairs its `id`); or the polymorphic link is listed with why |
| **array** (5.7) | An array column; a column named `*_ids` or `*_list` | Never: make it rows of a child table |
| **jsonb** (5.5) | A jsonb column with no class; in the seeded demo, a list anywhere in a `label` or `document` | It is classed `label` (words per language), `payload` (a provider's or a form's shape), `document`, `log` or `snapshot`, with why |
| **key** (5.4, 7.4) | A `*_id`, `*_by` or `*_public_id` column with no foreign key | It has one, or is listed: a trail, a log that outlives what it names, a polymorphic link, a key held in config |
| **derived** (5.8) | A column that looks worked out from others: `last_*` (not `last_name`), `*_count`, `total*`, `version`, an `is_x` beside its `x_at`, a `status` or `state` beside its append-only history | It is generated; or it is in the app's registry of values it keeps (one writer, `derived:rebuild`: plan SN D7); or it is listed as not derived, with why |
| **copy** (5.7) | A column with the same name on a child and on the parent its key points at | It is listed as a snapshot, a trail or a fact of its own. Skipped: the workspace as parent (its locale is a level a child overrides), actor links (`*_by`), keys, times, and generic names (`name`, `key`, `status`, `state`, `sort_order`, `description`, `locale`, `time_zone`, `search_*`) |
| **flag** | An `is_x` beside its `x_at` with no check tying them; `is_default`, `is_account_holder` or `is_primary` with no partial unique index | The check or the index exists, or the flag is generated |

What the guard can't see (a fact copied under another name, a value depending on part of a key) the migration's
author checks against 5.7 before committing.

## The data file

`tests/Support/normal-form.php` returns:

- `workspace`: the workspace table and its column on every workspace-owned row (`tenant`, `tenant_id`);
- `framework`: Laravel's tables, skipped (`SchemaGuardTest` reads the same list);
- `history`: the trigger function that makes a table an append-only history (`log_append_only`);
- `registry`: the class listing the derived values the app keeps, or `null`;
- `probe`: what the self-test hangs its planted table off;
- `allowed`: per rule, `table.column => reason` (jsonb: `[class, reason]`), the exceptions that stay;
- `baseline`: `rule:table.column => 'SN-x'`, offences not yet fixed, each with the slice that removes it. Atrium's held
  29 on 30 October 2026 and is empty since plan SN was built; an app porting the guard starts its own here.

The baseline only shrinks: a line that is no longer an offence fails ("take it out"), and so does a reason that no
longer applies. A registry entry (`derived`) is a declaration, not an exception, so it needn't be one the name rules flag.

## Derived values the app keeps

`App\Support\Schema\Derived` lists each value worked out from others that the database can't work out itself, with
its one writer, the query of rows that differ and the update that rebuilds them: a template's version, an import's
counts (while its rows are kept), an e-mail's status. `php artisan derived:rebuild [value]` reports every difference
(non-zero when there is one); `--apply` puts them right. The guard reads the same list (`registry` in the data file).
What the database can work out it does: a generated column (`membership.is_active`), a trigger (`tenant.status` from
its history), a check (`privacy_request_answered_chk`).

## Adding a table

1. A child of a workspace-owned parent takes a composite key: `$table->foreign(['parent_id', 'tenant_id'])
   ->references(['id', 'tenant_id'])->on('parent')`, after `UNIQUE (id, tenant_id)` on the parent. A nullable link
   empties only itself on delete: `ON DELETE SET NULL (parent_id)` (PostgreSQL 15 and later).
2. A list is rows of a child or link table, never an array, a delimited string or ids in jsonb.
3. A jsonb column gets its class and reason in the data file.
4. A value worked out from others is generated, kept by a trigger or a check, or declared in `Derived` with its one writer.
5. Run the guard: it names anything left.

## Porting it to an app

Copy the test unchanged. Write the app's `normal-form.php`: its workspace table and column, its framework tables, its
history trigger. Run the guard once and put every offence it prints into `baseline`, with the slice of the app's own SN
plan that removes it, or into `allowed` with a reason. Each slice of that plan then deletes its lines. See the port
notes in `CHANGELOG.md`.