# Admin Field Meta Management — Options, Guards and Pickers

- **Date:** 2026-08-02
- **Status:** Complete
- **Status Date:** 2026-08-03
- **Phases:** 7
- **Phases Complete:** 7
  - *Phase 0 ✅ applied and verified on dev 2026-08-02. All three post-migration audit
    queries pass: option ordering backfilled with none left unordered, no blank labels, no
    unexpected inactive rows, no markup left in any `field_section_icon`, and no `text` or
    `inputarea` input types remaining.*
  - *Phase 6 ✅ tested by Dave 2026-08-03 — picker, selection persistence, and the coupled
    editor fix all confirmed across project, account and stocklist editors. Icon set revised
    after first pass: Dave found the original 25 too narrow (no hammer for a Build section),
    so it is now 42 in seven themed groups, every entry verified against the vendored
    FA 5.15.4. **Found while testing:** the "Main Details" tab is hardcoded in all four
    editor templates and uses Bootstrap Icons, so admin cannot style it — logged to the
    backlog, not fixed here.*
  - *Phase 6 detail — 25-icon radio grid replaces the
    free-text class box (`SECTION_ICONS` in `admin_fields.js`, `.section-icon-grid` styles
    in `admin.css`), plus the coupled fix wrapping `field_section_icon` in an `<i>` in
    project/account/stocklist editors. Values outside the standard set are appended to the
    grid rather than silently reset to "no icon".*
  - *Phase 5 ✅ tested by Dave 2026-08-03 — cascade, reset-on-change, `date` auto-select,
    required validation, slug hardening (`My New Field!!  (2)` and a leading digit), static
    and usage-locked cases all pass. Step 10 (legacy flagging on wayleave) was retired as
    unfounded: I picked the module from its renderer's capabilities rather than its data,
    and wayleave holds no invalid pairs. The legacy path is nonetheless demonstrably wired —
    had the combination map failed to load, every field would have flagged.*
  - *Phase 5 detail — Input Type is now a `<select>`
    cascading off Data Type from `fieldTypeCombinations()`; disabled until a data type is
    chosen, reset on change, auto-selected where only one is valid, legacy values preserved
    and flagged. Both columns required client and server; `fieldAdd` and `fieldUpdate`
    validate the pair (update only when either half changes, so legacy fields still open).
    `toSnakeCase` hardened, with `fieldFormIdSlug()` as a server-side fallback.*
  - *Phase 4 ✅ tested by Dave 2026-08-02 — all 10 steps pass, including static option
    editing on wayleave `agreement_type` and `project_type`, orphan option cleanup on
    delete, and no lock leakage from a static row into the next dynamic one. Copy revised
    at Dave's request: banner is now "This is a system field. Only the field name and
    organisation options are available."; the data-type note reads "System Field".*
  - *Phase 4 detail — `fieldUpdate` no longer calls
    `rejectIfStaticField`; static rows take a presentation-only path (identity columns
    rejected on change, `field_input_type` / `field_active` / `field_type` forced to their
    stored values). `fieldToggleActive` and `fieldDelete` still reject static outright.
    Static rows get an edit button plus a padlock in the table, and the modal shows a
    "System field" banner with form id / input type / active disabled.
    **Non-obvious fix required:** the `reservedSystemColumns` guard had to be skipped for
    static rows — a static field's form id **is** an entity column by design, so the check
    would have rejected every system field on sight.*
  - *Phase 3 ✅ tested by Dave 2026-08-02 — data type editable on an unused field, locked
    once a project stored a value, **still locked after the value was cleared** (the history
    check), and delete buttons enabling/disabling to match.*
  - *Phase 3 detail — `fields_list` now returns
    `usage_count` per dynamic field (10 grouped queries per load, fixed regardless of field
    count); delete button disabled with a count tooltip; data-type control unlocked when
    unused; `fieldUpdate` actually writes `field_data_type` (it never did) behind a
    server-side recheck, clearing the option list on a type change; `fieldValueCount` and
    `fieldDelete` both now count the 10 `_history` tables too.*
  - *Phases 1 + 2 ✅ tested by Dave 2026-08-02 — creating a dropdown field with options in
    one pass, editing and reordering options on an existing field, delete correctly
    disabled on in-use options, and the new dropdown rendering on the project form (which
    also clears the `get_update_form.php` risk). **Bug found and fixed during testing:**
    `field_options` was wired into `fieldAdd` but not `fieldUpdate`, so edits returned
    success and silently did nothing.*
  - *Phase 2 detail — options panel in the field modal
    (`html_body_admin_fields.php` markup, `admin_fields.js` buffer/render/reorder/validate,
    payload folded into the existing Save Field button). Reorder is up/down buttons, not
    drag — SortableJS is not vendored and the page's existing drag comes from Tabulator's
    `movableRows`, which is not worth instantiating inside a modal for a handful of rows.
    **Divergence from the plan:** for `field_options_source` fields the panel hides the
    table and shows only the explanatory note, rather than listing the canonical options
    greyed out. Serving those would mean lifting `get_update_form.php`'s `$optionsSources`
    whitelist into `global_functions.php` — right, but it belongs with the Statuses item
    that owns those tables.*
  - *Phase 1 written 2026-08-02 (backend only, no UI yet) — **awaiting testing**. Touches
    `global_functions.php` (shared helpers), `admin_load.php` (`field_options_list`,
    combination map served via `field_lookups`), `admin_save.php` (`field_options_save`,
    options folded into `field_add`, option cleanup on `field_delete`) and
    `get_update_form.php` (labels/ordering/active reach the editors). Depends on migration
    021 having been applied — the new columns are referenced directly.*
- **Notes:** Cleanup pass over `admin_fields` covering the five gaps raised on 2026-08-02
  plus the backlog item *Presentation-only edits on static (system) fields in admin*.
  The two larger backlog items it touches — **Admin "Statuses" management tab** and
  **Admin fields page: rethink section/category organisation UI** — stay out of scope and
  keep their own entries; both need prototyping (status colour/contrast, drag-drop
  hierarchy) that would stall everything here. Phase 0 is a migration (`db/021`) that Dave
  applies; Phase 6 couples a migration to editor JS changes and must deploy together.
  **Decided 2026-08-02:** data type and input type stay two columns and two dropdowns —
  merging them into one control was considered and rejected.

- **Commit sequence before the next merge to `main`** (Dave, 2026-08-02):
  **1. Admin (this plan) → 2. Fix renderer → 3. Fix saving → merge.**
  This is load-bearing for how the plan is written. Because all three land before `main`,
  **admin states the target rather than today's limitation** — it offers field
  configurations the editors cannot yet render and the save endpoints cannot yet store,
  because commits 2 and 3 close those gaps before anything ships. Gating admin on the
  current state would mean revisiting this page twice more. The corollary: commits 2 and 3
  are not optional follow-ups, they are the rest of this piece of work, and the branch must
  not merge with only commit 1 applied.

## Plan Phases

0. Migration 021 — option ordering/state columns, label backfill, icon normalisation
1. Dropdown options — backend (load, usage counts, transactional save)
2. Dropdown options — inline panel in the field modal
3. Usage-aware field actions — delete guard and data-type unlock
4. Presentation-only editing of static (system) fields
5. Input Type as a constrained dropdown + Form ID hardening
6. Section icon picker (25-icon grid) + editor render fix

## Problem

Six separate defects and gaps in `?do=admin_fields`, all in the same three files
(`www/js/admin_fields.js`, `www/html/html_body_admin_fields.php`, `www/fn/admin_load.php`
+ `www/fn/admin_save.php`).

### 1. Dropdown options cannot be managed at all

A field can be given the `dropdown` input type, but nothing in admin can add, reorder or
disable its options. The four `*_field_dropdown_options` tables are only ever written by
migrations (`db/015` seeded wayleave's `agreement_type` list) or by hand.

The tables are also missing the columns the feature needs:

```
projects.project_field_dropdown_options
  dropdown_id bigint, field_id integer, dropdown_value varchar, dropdown_name varchar
```

No `display_order`, no active flag. Migration 011 deliberately *removed* both from
wayleave's table to match the projects shape, so all four now need them added back under
the prefixed naming convention.

`dropdown_name` is dead data in three of the four modules. `get_update_form.php:63-75`
selects the row as-is and `project_edit_v2.js:294` renders
`option.dropdown_label || option.dropdown_value` — `dropdown_label` is only ever set for
options-source rows (the status tables) and for wayleave, which aliases
`dropdown_name AS dropdown_label` at line 85. So for projects/accounts/stocklists the
**stored value string is what the user sees**, and renaming an option would rewrite the
identity of every value already stored against it.

### 2. Delete is offered on fields that have been used

The guard exists — `initiateDelete` (`admin_fields.js:431`) calls `field_value_count` and
swaps the dialog for a Deactivate offer, and `fieldDelete` (`admin_save.php:493-511`)
re-checks server-side. Two gaps:

- The trash button is live on every dynamic row, so the rule is only discovered by
  clicking it.
- Both checks count the five live `*_field_values_*` tables only. Migration 018 now
  populates the `_history` tables, so a field whose values were entered and later cleared
  reads as unused — deleting it orphans its history rows and breaks the audit trail.

### 3. "Cannot change once values exist" always shows

`openEditModal` (`admin_fields.js:300-304`) unconditionally hides
`#wrap-data-type-select` and shows the disabled `#wrap-data-type-static` input with that
message. It never consults usage, so a brand-new field with zero values is locked exactly
like a heavily-used one. `fieldUpdate` compounds it: the UPDATE at `admin_save.php:423-436`
does not list `field_data_type` at all, so even a permitted change could not be written.

### 4. Form ID auto-populate

Already implemented and working — `admin_fields.js:962-967` derives the form id from the
field name via `toSnakeCase` in add mode, and stops as soon as the user types in the Form
ID box themselves. Confirmed with Dave on 2026-08-02. Demoted from a build item to
verification plus a small hardening pass on the transform.

### 5. Section icon is a raw class string with contradictory render paths

`f-section_icon` is a free-text box captioned "FontAwesome class string", but the two
consumers disagree about the format:

- Admin's table formatter builds `<i class="{value}">` (`admin_fields.js:519`) — expects a
  bare class string.
- All three editors inject the value as **raw HTML** into a template literal —
  `${section.field_section_icon}` at `project_edit_v2.js:102`, `account_edit_v2.js:91`,
  `stocklist_edit.js:106` — expects a full `<i …></i>` tag.

Dave confirmed the column holds full tags, so the admin list is currently rendering
`<i class="fas fa-home"></i>` as an empty icon plus visible markup text.

### 6. Input Type offers values that render nothing

The datalist at `html_body_admin_fields.php:156-168` suggests `text, textarea, number,
date, datetime, select, multiselect, checkbox, radio, email, file`. The project, account
and stocklist editors switch on `field_input_type` and handle only `dropdown`, `input`,
`password2`, `submit2`, with `default: input = ''` (`project_edit_v2.js:214-241`).

The precise rule — the switch has an `else` on it that is easy to miss. Autocomplete pairs
(`field_autocomplete_type` + `field_autocomplete_helper_id`) are built by
`buildAutocompleteField` and **never consult `field_input_type` at all**
(`project_edit_v2.js:209-214`). So a field renders nothing only when it is *not* an
autocomplete pair, is active, and its input type is outside `dropdown` / `input`.

**Confirmed live on 2026-08-02.** Querying `projects.project_fields` for input types outside
that pair returned exactly one row — `test` / `test` / `text` / `text`, active, dynamic, no
autocomplete type. It is invisible on the project form today. Housekeeping: it has no
stored values, so it can simply be deleted.

Two combinations work by accident rather than design, and both must keep working:

- `dropdown` → `<select>`, filled by the options loop.
- `input` + data type `date` → `<input type="date">`, i.e. the browser's **native** date
  picker. No datepicker library is loaded anywhere in the app (no flatpickr, no
  bootstrap-datepicker), so this works purely because `input` passes `field_data_type`
  straight through as the HTML `type`. The same passthrough is what makes `int`, `numeric`
  and `boolean` render as plain text boxes.

Wayleave diverges again: `wayleave_edit_v2.js:370-387` renders a `<select>` whenever the
field has options and otherwise handles `textarea/date/checkbox/number` with a text
default — notably its `default` is a text input, not nothing, so a bad input type there is
cosmetic rather than invisible.

**Decided 2026-08-02 — data type and input type stay two columns and two dropdowns.**
Merging them into one "field kind" control was considered and rejected: the split is real
(storage routing vs widget), five save endpoints and the `check*` helpers depend on
`field_data_type`, and a merged menu could only offer entries the editors actually render
— which is two. Admin is constrained to what works today (Phase 5); bringing the editors
up to a wider set is a separate backlog item, actioned **after** this plan is confirmed
complete.

## Root-cause analysis

Items 2 and 3 share one root cause: **field usage is computed on demand, at one call site,
for one purpose.** `field_value_count` is a per-field POST fired only from the delete
flow. Nothing else can ask "has this field been used?", so the edit modal guesses (and
always guesses "yes"), and the row cannot render a usage-aware button.

The fix is to make usage a property of the field row, resolved once per table load and
returned by `fields_list`, with the existing single-field endpoint kept as the server-side
re-check before a destructive write. Phase 3 does this and both defects fall out of it.

Item 1's `dropdown_name` problem has the same shape as the status-options decision
recorded in `db/009`: option **identity** (the stored value) and option **presentation**
(the label) must be separable, or every rename is a data migration.

## Build

### Phase 0 — Migration 021: option ordering/state, label backfill, icon normalisation

New `db/021_field_options_and_icons.sql`, covering all four modules
(`projects.project_field_dropdown_options`, `accounts.account_field_dropdown_options`,
`stocklists.stocklist_field_dropdown_options`, `wayleave.agreement_field_dropdown_options`
and the four `*_field_sections` tables).

**Dropdown option tables**, each:

- `ADD COLUMN IF NOT EXISTS dropdown_display_order integer` — prefixed to match the
  `field_section_display_order` / `field_category_display_order` convention, not the bare
  `display_order` that 011 dropped.
- `ADD COLUMN IF NOT EXISTS dropdown_active boolean NOT NULL DEFAULT true`.
- Backfill order: `row_number() OVER (PARTITION BY field_id ORDER BY dropdown_id)` — the
  current implicit order (`ORDER BY 1` in `get_update_form.php`) preserved exactly.
- Backfill labels: `UPDATE … SET dropdown_name = dropdown_value WHERE dropdown_name IS
  NULL OR btrim(dropdown_name) = ''` — gives projects/accounts/stocklists a real label
  column so Phase 1 can start serving it. Wayleave's are already populated and are left
  alone.
- Index on `(field_id, dropdown_display_order)`.

**Section icon normalisation**, each `*_field_sections` table — strip the tag wrapper down
to the class string, leaving already-bare values untouched:

```sql
UPDATE projects.project_field_sections
   SET field_section_icon = substring(field_section_icon from 'class="([^"]*)"')
 WHERE field_section_icon LIKE '<%'
   AND field_section_icon ~ 'class="[^"]*"';
```

Column is `varchar(50)`; stripping only shortens, so no width change is needed.

**Input type normalisation**, the four `*_fields` tables — Phase 5 makes `field_input_type`
required and validated against the combination map, so any row outside it would fail the
first time someone opened it:

- `field_input_type = 'text'` → `'input'` (the one known non-conforming row, the `test`
  field in projects).
- Backfill anything NULL or empty from its data type's first mapped widget — pending the
  audit query in Phase 5, which must be run before this migration is finalised. Projects is
  known clean; accounts, stocklists and wayleave are unaudited.

Per the house rule the migration is immutable once applied — Dave applies it with
`php db/migrate.php --apply` on the dev server and reports the result.

### Phase 1 — Dropdown options backend

**`www/fn/admin_load.php`**

Extend `$moduleConfig` with the entity table, its id column and its history table
(`projects.projects` / `project_id` / `projects.projects_history`, and the equivalents for
accounts, stocklists and `wayleave.agreements`). Static fields store into the entity
column, so per-option usage needs this.

New mode `field_options_list` (add to `$allowedModes` and `$modePermissions` → `admin_fields`,
read):

- Returns the field's options ordered by `dropdown_display_order, dropdown_id`, each with
  `usage_count`, plus the field's `field_type`, `field_data_type` and
  `field_options_source` so the client can decide what to render.
- Usage is **one grouped query per source**, not one per option:
  - Dynamic field → `SELECT value, COUNT(*) FROM {schema}.{prefix}_{datatype}
    WHERE field_id = :fid GROUP BY value`, unioned with the same against
    `…_{datatype}_history`. Only the field's own data-type table is touched.
  - Static field → the same shape against `{entity table}.{field_form_id}` and its
    `_history` counterpart.
- The static column name comes from the database, but it is still interpolated into SQL,
  so validate it before use: `preg_match('/^[a-z0-9_]+$/', $col)` **and** confirm it exists
  in `information_schema.columns` for that table. Fail closed.
- Counts are matched to options by string comparison on the value, mirroring how the
  editors store them.

**`www/fn/admin_save.php`**

New mode `field_options_save` (allowlist + `admin_fields` write permission), taking
`field_id` and `options` as a JSON array of
`{dropdown_id | null, dropdown_value, dropdown_name, dropdown_active, dropdown_display_order}`.
One transaction:

1. Reject outright if the field has a non-empty `field_options_source` — those options
   belong to the canonical status tables and are managed elsewhere.
2. Recompute usage server-side (never trust the client's counts).
3. Rows with `dropdown_id = null` → INSERT.
4. Rows with an id → UPDATE label, active flag and order. **Reject any change to
   `dropdown_value` on an option with `usage_count > 0`** — the stored EAV/column data *is*
   that string. Label changes are always allowed; that is the whole point of Phase 0's
   backfill.
5. Options absent from the payload → DELETE if unused, reject the whole save with a named
   error if used ("Cannot delete option 'MDU': 12 records use it. Set it inactive
   instead.").
6. Reject duplicate `dropdown_value` within one field.

Also extend `fieldDelete` to clear the field's option rows inside its existing transaction
— today deleting a field leaves orphaned option rows behind.

`fieldAdd` gains an optional `field_options` parameter handled the same way, inserted
after the field row in the same transaction (see Phase 2).

**`www/fn/get_update_form.php`** — align the three older modules to wayleave by selecting
`dropdown_name AS dropdown_label`, filtering `dropdown_active = true`, and ordering by
`dropdown_display_order`. This is the change that makes admin-set labels and ordering
appear in the editors. `project_edit_v2.js:294`'s `dropdown_label || dropdown_value`
fallback keeps any unbackfilled row working.

### Phase 2 — Dropdown options panel in the field modal

Per Dave's choice, the manager lives **inline in the Add/Edit Field modal**, not in a tab.

**`www/html/html_body_admin_fields.php`** — new `#wrap-field-options` block after the
Options section, hidden by default:

```
Dropdown Options
┌──┬────────────┬──────────────────┬────────┬───┐
│⣿ │ Value      │ Label            │ Active │ 🗑 │
│⣿ │ MDU        │ Multi-Dwelling   │  [✓]   │ 🗑 │   ← 12 uses: value locked, bin disabled
│⣿ │ SDU        │ Single Dwelling  │  [✓]   │ 🗑 │
└──┴────────────┴──────────────────┴────────┴───┘
[+ Add option]
```

- Shown only when Input Type is `dropdown` (Phase 5 makes that a reliable signal).
- Reorder with up/down buttons on each row, not drag — SortableJS is not vendored, the
  existing drag support comes from Tabulator's `movableRows`, and pulling a whole
  Tabulator instance into a modal for a handful of rows is not worth it. Display order is
  renumbered from the DOM on save. *(A future drag implementation can reuse the same
  payload.)*
- Rows carry `usage_count`. When > 0: value input `readonly` with a tooltip, bin button
  disabled with a tooltip giving the count, active toggle still live.
- When `field_options_source` is set: the list renders read-only from the served options
  with a note — "Managed in the Statuses area" — and Add/reorder/delete are hidden.
- **Add mode** — the field has no id yet, so rows are buffered client-side and posted as a
  `field_options` JSON string alongside `field_add`; the server inserts them after the
  field row in the same transaction. One Save button, one trip.
- **Edit mode** — `field_options_list` fires when the modal opens; the same
  `field_options` payload rides along with `field_update`. `resetFieldModal` clears the
  buffer so a cancelled add cannot leak rows into the next open.

Client-side validation before save: no blank values, no duplicate values within the field.

### Phase 3 — Usage-aware delete guard and data-type unlock

**`fields_list` returns usage.** Ten grouped queries per module load — for each of the five
types, `SELECT field_id, COUNT(*) … GROUP BY field_id` against the value table and its
`_history` counterpart — merged in PHP into a `usage_count` on each row. Fixed query count
regardless of field count. Only dynamic fields need it (static fields can neither be
deleted nor retyped), so static rows return `null`.

**Delete button** (`buildFieldColumns`, `admin_fields.js:193-214`) — when
`usage_count > 0`, render the trash button `disabled` with
`title="Cannot delete: N stored values. Deactivate instead."`. Enabled and unchanged at
zero. Per Dave's choice the button stays visible so the rule is discoverable. Keep
`initiateDelete`'s confirmation dialog for the zero case.

**Data-type lock** (`openEditModal`, `admin_fields.js:300-304`) — branch on
`usage_count`: zero shows the editable `#wrap-data-type-select` preselected to the current
type; above zero keeps today's disabled input and message, with the count added
("Cannot change — 12 values stored"). This is the exact bug Dave reported.

**Server side** — `fieldUpdate` must actually write `field_data_type`, so add it to the
UPDATE at `admin_save.php:423-436`, guarded by a fresh usage recheck (reject if > 0, and
reject a type not in the five-value whitelist). Changing type also invalidates the option
list, so clear the field's dropdown options when the type changes, and say so in the
modal.

**Extend both existing guards to history**: `fieldValueCount` (`admin_load.php:150-155`)
and `fieldDelete`'s re-check (`admin_save.php:493-498`) each gain the five `_history`
tables — ten subqueries instead of five.

### Phase 4 — Presentation-only editing of static (system) fields

Closes the backlog item of the same name.

**Editable on static rows:** `field_name` (label), `field_section`, `field_category`,
`field_sub_category`, `field_display_order`, `field_spacer_after`, `field_required`, and
the dropdown option list from Phase 2 (Dave: full editing).

**Locked, per Dave — "these are what validate back to the actual table":**
`field_form_id` and `field_data_type`. Also stays locked: `field_input_type` (switching a
status field from `dropdown` to `input` would let free text into an integer column),
`field_type`, `field_active` (deactivating a system field hides a real column from its
editor with no way back through the UI), and the autocomplete metadata (already set-once
for every field).

**`admin_save.php`** — `fieldUpdate` currently calls `rejectIfStaticField` and stops
(line 381). Replace with a static-aware branch:

- Static rows: build the UPDATE from the presentation columns only; take the locked
  columns from the existing row and ignore the POST values entirely, except
  `field_form_id` and `field_data_type` which **error** if the client sends a different
  value, matching how autocomplete mismatches are already handled at lines 396-402.
- `field_toggle_active` and `field_delete` keep rejecting static outright — no change.

**`admin_fields.js`** — static rows get the pencil button back (lines 197-205 currently
render a padlock instead); the toggle and trash stay absent. `openEditModal` sets
`disabled` on the locked inputs and shows a "System field — storage is defined by database
migrations" banner. `collectModalData` omits the locked keys for static rows.

### Phase 5 — Input Type as a constrained dropdown, and Form ID hardening

Both controls stay as separate dropdowns writing separate columns, kept cleanly orthogonal:
`field_data_type` is **purely storage** (which EAV table, how the value is parsed),
`field_input_type` is **purely presentation** (which control renders). Input Type therefore
uses literal widget names rather than shapes the renderer has to derive — deriving would
put presentation back into both columns, which is the confusion that prompted the merge
question in the first place.

**The valid-combination map is the single source of truth.** One declarative structure,
extensible by adding entries, defined **once** in `www/fn/global_functions.php` — both
`admin_load.php` and `admin_save.php` already `require_once` it (lines 8-10 of each), which
is what keeps a helper used by two endpoints from becoming the fatal-in-the-browser hazard
called out in CLAUDE.md:

```php
function fieldTypeCombinations()
{
    return [
        'text'    => ['input', 'textarea', 'dropdown'],
        'int'     => ['number', 'dropdown'],
        'numeric' => ['number', 'dropdown'],
        'boolean' => ['checkbox', 'dropdown'],
        'date'    => ['date'],
    ];
}
```

Served to the client by the existing `field_lookups` mode (the page already calls
`loadLookups()` on init), so the JS never carries its own copy to drift out of sync.
`admin_save.php` validates every add and update against the same function — a crafted POST
must not be able to store a pair the UI cannot produce.

**Interaction, per Dave's spec (2026-08-02):**

1. **Data Type must be chosen first.** Input Type renders `disabled` with a
   `— select a data type first —` placeholder until Data Type has a value.
2. **Input Type is populated from the map** for the chosen data type, then enabled.
3. **Changing Data Type resets Input Type**, so an invalid pair left over from the previous
   data type can never be submitted.
4. **Both are required** — client-side `is-invalid` marking plus a server-side reject, so
   neither column can reach the database empty.

Three consequences to build around:

- **The cascade must run on modal open, not only on user change.** On edit, Data Type may
  be locked (Phase 3) but Input Type still needs populating from it. The reset in step 3
  fires only on a genuine user change — running it on the initial populate would blank the
  field's stored input type just for opening the modal.
- **Auto-select when the map offers exactly one option** — `date` has only `date`. Leaving
  the user to pick from a list of one is pointless friction.
- **Legacy pairs must survive being opened.** If a stored input type is not in the map for
  its data type, prepend it as the selected option labelled `(legacy)` and leave it alone
  unless the user actively changes Data Type. Otherwise opening a field to fix its label
  silently rewrites its input type. Server-side, `collectFieldParams` accepts a value from
  the map **or** the field's existing value.

**Vocabulary note:** `input, textarea, dropdown, number, checkbox, date` is almost exactly
the union of what the data already holds — projects has `input` and `dropdown`, wayleave has
`textarea`, `date`, `checkbox` and `number`. The only non-conforming row found is the `test`
field's `text`, normalised to `input` in Phase 0.

**Query to run before building this phase** — `field_input_type` becomes required, so any
existing NULL or empty rows would fail validation the first time someone opens them.
Projects is known clean (Dave's `select distinct`, 2026-08-02); the other three are not:

```sql
SELECT 'accounts' AS module, field_form_id, field_data_type, field_input_type, field_active
FROM accounts.account_fields   WHERE field_input_type IS NULL OR btrim(field_input_type) = ''
UNION ALL SELECT 'stocklists', field_form_id, field_data_type, field_input_type, field_active
FROM stocklists.stocklist_fields WHERE field_input_type IS NULL OR btrim(field_input_type) = ''
UNION ALL SELECT 'wayleave', field_form_id, field_data_type, field_input_type, field_active
FROM wayleave.agreement_fields WHERE field_input_type IS NULL OR btrim(field_input_type) = '';
```

Anything returned gets a backfill in migration 021 rather than being left to fail at edit
time.

**Data Type keeps all five values, including `numeric` and `boolean`** (decided
2026-08-02). Those two were discarded on save — the project/account/stocklist endpoints
collected them and had no INSERT — but gating them in admin would have meant revisiting this
page a third time. The value tables already existed. Admin stated the target; the save
endpoints caught up before anything reached `main`.

> **Commit 3 done 2026-08-04, awaiting testing.** The six missing upsert blocks are in.
> Boolean turned out to be broken in three places rather than one: no write block, a
> collection `case "bool"` that never matched the stored `boolean`, and a change-check that
> cast both sides with `(bool)` — where `(bool)'f'` is `true`, so a stored false compared
> equal to a ticked box and the change was dropped. All three are fixed, with
> `normaliseBooleanFieldValue()` in `global_functions.php` now used on the write path and on
> both sides of the comparison. Full write-up in `improvement-opportunities.md`.
>
> One thing deliberately left: **unticking a checkbox still cannot be saved**, because an
> unchecked checkbox posts nothing and the save loop only sees posted keys. It is latent —
> the renderer has no `checkbox` case yet, so boolean fields can only be dropdowns — and the
> fix (a hidden `0` before the checkbox) belongs with commit 2, which adds that rendering.

**Form ID** — verified working; no rebuild. Two hardening changes while the file is open:

- `toSnakeCase` (`admin_fields.js:29-31`) currently lowercases, converts whitespace runs to
  `_` and strips everything non-`[a-z0-9_]`. Add: collapse repeated underscores, trim
  leading/trailing underscores, and prefix `f_` if the result starts with a digit (a form
  id starting with a digit is a valid HTML id but breaks bare CSS selectors, and the
  editors build selectors as `'#' + field_form_id`).
- `fieldAdd` derives the same slug server-side when the POSTed form id is blank, so a
  client-side failure cannot create a field with an empty form id.

**Backlog entries** (added, not fixed here):

- Project/account/stocklist editors render only `dropdown` and `input`, and `input` emits
  `type="${field_data_type}"` — so `int`, `numeric` and `boolean` produce invalid HTML
  input types that fall back to text. No real number, date-picker or checkbox support in
  three of four modules.
- Wayleave's renderer (options-driven select, `textarea/date/checkbox/number`, sensible
  text default) is the better design and is what the projects renderer should grow into;
  flag it against the existing wayleave-alignment item so the direction is not lost.

### Phase 6 — Section icon picker, and the editor render fix

**Picker** — replace `#f-section_icon`'s text input with a 5×5 radio grid: each cell a
`<label>` wrapping a hidden radio and a large `<i>`, with the selected cell outlined via
`:checked + …`. Grid only, no free-text fallback (Dave's choice), which is what guarantees
the column only ever holds a known-good class string. Include a "no icon" cell so an icon
can be cleared.

Proposed 25, all verified present in Font Awesome Free **5.15.4** solid — the vendored
copy at `www/css/all.css`; FA6-only names would render as blank boxes:

| Group | Icons |
|---|---|
| General | `fa-info-circle` `fa-file-alt` `fa-clipboard-check` `fa-tasks` `fa-calendar-alt` |
| Location | `fa-map-marked-alt` `fa-map-marker-alt` `fa-route` `fa-road` `fa-draw-polygon` |
| Property | `fa-building` `fa-home` `fa-city` `fa-warehouse` `fa-door-open` |
| Network | `fa-project-diagram` `fa-network-wired` `fa-broadcast-tower` `fa-plug` `fa-sitemap` |
| Commercial / Legal / People | `fa-pound-sign` `fa-file-invoice` `fa-handshake` `fa-file-signature` `fa-user-tie` |

Stored as the full class string (`fas fa-map-marked-alt`), matching what Phase 0
normalises existing rows to and what the admin table formatter already expects.

**Coupled editor change — must deploy with the migration.** Once the column holds bare
class strings, the three editors' raw injection prints the class text instead of an icon.
Change each to wrap it, guarding empties:

```js
// project_edit_v2.js:102, account_edit_v2.js:91, stocklist_edit.js:106
${section.field_section_icon ? `<i class="${section.field_section_icon} me-1"></i>` : ''}
```

Wayleave's editor does not render section icons, so it needs no change.

The admin table formatter (`admin_fields.js:516-521`) already assumes a class string and
becomes correct for free.

## Testing checklist

Phase 0 — migration ✅ *(2026-08-02)*

- [X] `php db/migrate.php --apply` runs clean on dev; `public.schema_migrations` shows 021
- [X] Existing dropdown options kept their original order after backfill
- [X] `dropdown_name` populated for every projects/accounts/stocklists option
- [X] Section icons in all four `*_field_sections` tables are bare class strings
- [X] No `text` or `inputarea` input types remain
- [ ] *Outstanding, app-side:* all four editor forms load and their dropdowns are populated
      in the same order as before — this exercises the `get_update_form.php` change from
      Phase 1, which is the one that could break live forms

Phases 1–2 — dropdown options *(core confirmed 2026-08-02)*

- [X] Add a dropdown field with three options in one pass (Add mode); options appear in the editor
- [X] Edit and reorder options on an existing field; changes persist
- [X] Deleting a used option is refused (button disabled)
- [ ] Reorder options in admin; the editor's `<select>` order matches
- [ ] Set an option inactive; it disappears from the editor but records still holding it show their label
- [ ] Rename an option's **label** with values stored against it — saves, and existing records still resolve
- [ ] Renaming an option's **value** with values stored against it is refused
- [ ] Duplicate values within one field are refused
- [ ] A field with `field_options_source` (e.g. wayleave Wayleave Team) shows the "managed elsewhere" note and no table
- [ ] Cancelling an Add leaves no orphaned option rows, and the next Add opens empty
- [ ] Static field `agreement_type` (wayleave) — options fully editable, usage counted from the
      entity column. **Blocked until Phase 4:** static rows show a padlock instead of an edit
      button, so the modal cannot be opened for them yet.

Phase 3 — usage guards ✅ *(2026-08-02)*

- [X] Brand-new field: Data Type is an editable dropdown, and changing it saves
- [X] Field with stored values: Data Type locked with the count in the message
- [X] Field whose values were entered then cleared: still locked, delete still refused (history check)
- [X] Delete button disabled with a count tooltip on used fields, live on unused ones
- [ ] Deleting a field also removes its dropdown option rows
- [ ] Fields tab still loads promptly with usage counts added

Phase 4 — static fields ✅ *(2026-08-02)*

- [X] Static row opens in the edit modal; label, section, category, order, spacer, required all save
- [X] Form ID and Data Type are visibly disabled and cannot be saved to a new value
- [X] Static fields still cannot be deactivated or deleted
- [X] A repositioned static field lands in the right place on its editor page
- [X] Static field options fully editable (`agreement_type`, `project_type`), usage counted
      from the entity column
- [X] Deleting a field removes its dropdown option rows; no orphans left
- [X] Locks do not leak from a static row into the next dynamic field opened

Phase 5 — input type and form id

- [ ] Input Type is disabled until Data Type is chosen
- [ ] Each data type offers exactly its mapped input types; `date` auto-selects `date`
- [ ] Changing Data Type resets Input Type rather than leaving a stale invalid value
- [ ] Saving with either left empty is refused, client and server
- [ ] Opening an existing field populates Input Type from its locked Data Type without blanking it
- [ ] A wayleave field with `textarea` keeps that value after editing an unrelated attribute
- [ ] A POST carrying an invalid pair (e.g. `date` + `textarea`) is rejected server-side
- [ ] Typing "My New Field" fills Form ID with `my_new_field`; typing in Form ID stops the sync
- [ ] "My New Field!! (2)" produces a clean slug with no doubled or trailing underscores

Phase 6 — icons ✅ *(2026-08-03)*

- [X] Icon grid renders all 42 icons, none blank (verified against the vendored FA 5.15.4)
- [X] Selecting an icon and saving shows it in the admin table and on the editor's section tab
- [X] Clearing an icon leaves the section tab rendering its name with no stray markup
- [X] Project, account and stocklist editor pages show section icons correctly after the migration
- [X] Keyboard navigation of the grid works with a visible focus ring
- [n/a] "Main Details" does not respond — it is hardcoded in the editor templates, not
      meta-rendered. Backlogged as its own item; out of scope here.

## Residual verification

The plan is complete and every phase was signed off, but these edge cases were never
explicitly exercised. None block the commit; worth a spot-check before the branch merges.

- [ ] Option **label** renamed while records hold its value — saves, records still resolve
- [ ] Option **value** renamed while records hold it — refused with a count
- [ ] Duplicate option values within one field — refused
- [ ] Option set inactive — disappears from the editor select, existing records unaffected
- [ ] A `field_options_source` field shows the "managed elsewhere" note and no table
- [ ] Cancelling an Add leaves no orphaned option rows and the next Add opens empty

## Risks

- **Phase 6 is a coupled deploy.** Migration 021 strips the `<i>` wrappers, so the three
  editor files must go up in the same `git pull` or every section tab shows raw class text
  instead of an icon. Client deploys are a plain pull with no per-deploy manual step, so
  the two changes must land in the same commit.
- **`get_update_form.php` now filters on `dropdown_active`.** If Phase 0's default lands
  wrong on any row, options vanish from live editor forms. The `NOT NULL DEFAULT true`
  covers it, but this is the change with the widest live blast radius in the plan.
- **Per-option usage matching is by string.** An option whose value was edited outside the
  UI, or whitespace differences between stored and configured values, will read as unused.
  Acceptable — the failure mode is permitting a delete that should have been blocked, and
  the field-level guard still protects the field itself.
- **Static option editing crosses the static/dynamic boundary.** Phase 1 interpolates
  `field_form_id` as a column name for static usage counts. The regex plus
  `information_schema` check is what keeps that safe; it must not be dropped for brevity.
- **No local runtime.** Nothing here can be executed on the editing machine — PHP is
  verified by reading, and any helper added to `global_functions.php` must be defined once
  and included by every caller. Errors surface only in the browser and the dev server log.