# Module Delete & Restore — Pattern

- **Date:** 2026-08-30
- **Status:** Reference (not a plan — no phases to complete)
- **Worked example:** `docs/2026-08-28-project-delete-restore.md` (projects, shipped 2026-08-29)
- **Applies to:** accounts, stocklists, opportunities, wayleaves — one module at a time

## How to use this

Projects is the only module with delete and restore. The other four will follow, and this
document exists so each one repeats the **decisions** and not the **derivation**.

1. Read the settled decisions below. They are answered; do not re-litigate them per module.
2. Work through *Per-module derivation* and answer each question **for that module**, using
   the commands given. Every answer differs by module — that is the whole point.
3. Write a short plan doc from `docs/plan-template.md` (`docs/YYYY-MM-DD-<module>-delete-restore.md`)
   recording those answers and the phase breakdown. It should be brief, because the
   reasoning lives here and the worked example lives in the projects doc.
4. Build in the phase order below, handing over numbered tests at the end of each phase or
   chunk.

**Do not copy the projects plan document.** Its substance is answers — twelve endpoint
sites, four map layers, "premises are tombstoned by the existing trigger", "wayleaves and
stocklists are spatial so they do not block". Not one of those transfers. Copying it
invites reusing its conclusions instead of redoing the work, which is the failure mode
CLAUDE.md already records for `docs/archive/superpowers`: a divergent design used as a
pattern source cost three rewrites.

## Settled decisions

These were decided for projects and hold for every module. If one genuinely does not fit a
module, that is a design conversation, not a silent deviation.

**1. Soft delete, one boolean.** `<table>.is_deleted boolean NOT NULL DEFAULT false`. The
row and everything hanging off it stay for ever: journal, history, attachments,
materialised child rows. Soft delete exists so a stored id always renders a name.

**2. No `deleted_user` / `deleted_datetime` columns.** "Deleted by / when" is
`modified_user` and `modified_datetime`. The delete is an ordinary UPDATE, so the module's
`*_update_modified_fields()` trigger stamps the timestamp and the endpoint sets the user.
Those values stay the delete's values **only because every write path refuses a deleted
record** — that guard is load-bearing for this reading, not just for data integrity.
History remains the authority if they ever disagree.

**3. Hierarchy-free before delete.** No parent, and nothing naming it as parent. The user
removes the links first. This one rule removes an entire class of problem: a deleted record
can never be a parent or a child, so there is no dangling `parent_*_id`, no restore
ordering, no orphan case, and no lineage view needing an `is_deleted` filter. All four
remaining modules have a `parent_*_id` column, so it applies to all of them.

**4. A `<module>_approve` pseudo-module.** `write` deletes and restores; `read` sees the
archive with Restore disabled — someone auditing what was removed without being able to
act. **Additive**: deleting also needs ordinary write on the record. Enablement is always
checked against the real module:
`requireModuleAccess($pdo, '<module>', '<module>_approve', 'write')`. Endpoints that *read*
must also gate on the real module's read, or a bare approver could enumerate record names
through the archive.

**5. Status settable on delete, and it stands on restore.** The confirm modal carries the
module's status dropdown so a record can be archived *as* Cancelled in one action. Restore
changes visibility and nothing else — the status someone chose is not silently reverted.

**6. The journal entry is the only user-visible record.** Delete and restore each write a
`*_journal` row inside the same transaction. Not through the module's `*_journal_save.php`:
that is a separate HTTP request, so a note written through it survives a rolled-back
delete, and it will itself refuse writes against a deleted record. This matters more than
it looks — `buildAuditLogQuery()` builds the audit panel from `field_type = 'static'` meta
rows plus the journal, and `is_deleted` is never a meta field, so **without the journal row
nothing in the UI shows that a record was ever deleted.**

Three layers, each with a job: the **journal** is the user-visible account and survives
restore; **`modified_user` / `modified_datetime`** answer "deleted by / when" while the
record sits in the archive; **`*_history`** is the machine-level authority.

## Per-module derivation

Answer all nine before writing the plan. Each has a command, because guessing is what went
wrong on projects.

**1. Does the table already have `is_deleted`?**
Wayleave does (migration 032) and needs no column. The other three do not.

**2. Does the module's history function have a no-op suppression list?**
All four do — 19, 10, 13 and 19 columns respectively. **`is_deleted` must be added to it**,
or a delete changes only a column the compare ignores, the function returns early, and the
delete writes **no history row at all**. Nothing errors. This is the sharpest trap in the
whole pattern.
```bash
awk '/FUNCTION public.fn_<module>_history/,/^\$\$;/' db/*.sql | grep -c "IS NOT DISTINCT FROM"
```
Also add it to the INSERT column and VALUES lists, and add the column to the `*_history`
table as **nullable** — pre-migration rows genuinely do not know the value.

**3. What materialises from this record, and does anything tombstone it automatically?**
Projects got this free: `project_premises` is rebuilt by a trigger, so resolving a deleted
project's geometry to NULL tombstoned every row and the `is_valid` re-flag ran on the same
pass. **No other module has that.** `stocklist_premises` is user-imported, not derived;
wayleave coverage is an approval model with four sources. Decide explicitly: tombstone,
filter in the views, or leave — and if the answer is "filter", every current *and future*
reader has to remember, which is why the trigger was preferred.

**4. What stored references should block a delete?**
```sql
SELECT conrelid::regclass AS referencing_table, conname
  FROM pg_constraint
 WHERE confrelid = '<schema>.<table>'::regclass AND contype = 'f'
 ORDER BY 1;
```
Then judge each: a link the user can see and remove in the UI is a blocker; one with no
screen behind it is not, or you produce a delete nobody can unblock without SQL. **Check
whether an apparent relationship is spatial rather than stored** — the wayleave–project and
stocklist–project relationships resolve live from geometry, so there is nothing to detach
and they must not block.

Whatever blocks must also be **refused at the point of creation**, or the state is
manufacturable after the fact. Projects blocked on live opportunity links, so
`opportunityAddOne()` had to refuse linking a deleted project.

**5. Which map layers serve this module's relations?**
```sql
SELECT layer_id, layer_title, database_schema, database_table, layer_active, url
  FROM public.map_layers
 WHERE database_schema = '<schema>' OR url ILIKE '%<schema>.%'
 ORDER BY layer_active DESC, layer_id;
```
**The map is configured in data, not code — grepping `www/` cannot find these.** On
projects this was missed entirely and surfaced in testing. For each layer decide where the
filter goes: **inside the view** if it is a view (then it is true for GeoServer and every
other reader too), a relation-keyed predicate in `map_get_v2.php` if the relation exposes
the column, or in GeoServer for a WMS layer. Note that a view not selecting `is_deleted`
*cannot* be filtered from the endpoint.

**6. Which code sites read the entity table?**
```bash
grep -rn "<schema>\.<table>" --include=*.php www/fn/
```
Roughly 20–24 sites per module. Sort them into: needs the predicate; **already covered**
because it reaches the record through something filtered (a live link, an approved-state
join); and **must NOT be filtered** — see question 7.

**7. Which reads must keep resolving deleted records?**
At minimum `admin_load.php`'s item-permission label lookup and
`resolveAutocompleteLabel()` / `getAutocompleteLabelMap()` for the module's `*name` type.
A well-meaning sweep that filters everywhere breaks label rendering in exactly the places
soft delete exists to protect. List them in the plan so they are not "fixed" later.

**8. Does any query star-select this table?**
```bash
grep -rn "<table>\.\*\|SELECT [a-z]\.\*" --include=*.php www/fn/
```
**Adding a column can break a query that never mentions it.** On projects,
`opportunity_manage.php` selected `projects.*` alongside another table's `is_deleted`, so
the new column made later bare references ambiguous and the opportunity editor's
nearby-projects panel broke the moment the migration was applied. Check this **before**
applying, not after.

**9. Where do the UI controls go?**
Resolve the route in `fn/routes.php` first and edit only the files it names — never infer a
template from its filename. On projects, `nav_projectedit_html.php` reads exactly like the
editor's chrome and is dead (`'nav_html' => ['']`); a whole button was written into it.

## Build shape

Six phases, in this order. Each is independently testable, and everything before Phase 3 is
invisible to users.

- **Phase 0 — Schema.** One migration: the column, the history-table mirror, a **partial**
  index (`WHERE is_deleted` — the common predicate is `= false`, which no index helps),
  the history function, whatever question 3 decided, and the module's list view. Guard the
  head, verify the foot: assert the history function actually compares the new column, and
  that no row came out flagged.
- **Phase 1 — Permission.** Four sites for the pseudo-module: `admin_save.php`'s
  `$allowed`, `admin_users.js`'s `MODULES`, **its roles-list label map** (missed on
  projects — without it the roles table shows the raw key), and a row in
  `html_body_admin_users.php`. No `app_modules` row, no `routes.php` entry, nothing in
  `fieldMetaModuleConfig()` / `dashboardModuleMap()`.
- **Phase 2 — Endpoint and write guards.** `<module>_archive.php` with `check` / `delete` /
  `restore` / `list`. `check` feeds the modal so every blocker shows at once; `delete`
  re-runs the same checks inside its transaction after `SELECT … FOR UPDATE`. Then
  `requireNotDeleted()` on every write path, the `index.php` route gate (**outside** the
  company branch, so it applies to company 1 too), and the module's `*_load.php`.
- **Phase 3 — Read surfaces.** The sweep from questions 5–7. Split it: map first, then
  lists and pickers.
- **Phase 4 — UI.** Editor delete, then list archive. See the UI rules below.
- **Phase 5 — Documentation.** A CLAUDE.md section, backlog items for anything found in
  passing, and **a pass over the plan doc reconciling its spec text with what shipped** —
  see *Keeping the plan honest*.

## UI rules

Learned by getting them wrong on projects. All three are cheaper to follow than to redo.

**Delete goes in a gear menu, not a bare button.** It was first placed beside *Exit Project
Editor* — the control people click on the way out of every record. A confirm modal catches
a mis-click but should not have to. A gear dropdown between Save and Export keeps it
discoverable, next to nothing clicked often, and gives later rare actions a home. Note the
wrapper condition hides the *whole menu* when Delete is its only item; adding a second item
means changing that to "any item visible", or a user without the permission gets an empty
gear.

**Copy rule: no mechanics, no obvious consequences.** State what the user gets. Cut
internal workings (premises recalculating, layers refreshing, pickers updating) and cut
anything implied by the verb ("delete removes it from lists and the map"). Keep only what
the user could not predict *and* can act on: that it is reversible and from where, why a
control is disabled, what must be cleared first. Being non-obvious is **not** the test —
mechanics stay out even when interesting.

**Never assume intent.** Say the action required, not one implying what the user wants
next: *"Remove the parent link on it first"*, never *"re-parent it"* — a parent link is
optional. Name **where** the link lives, since it is not always on the record in front of
you. Use "it"/"its" for a record, not "their".

**Refreshing a list must not rebuild it.** After a restore, use Tabulator's `replaceData()`
on the existing table. Calling the page's full load function empties the container and
constructs a new table, discarding the user's header filters, sort and page. On projects
that defeated the reason the archive is a modal at all.

**Modal markup is unconditional**, even when the button that opens it is permission-gated —
the `#uploadModal` precedent in CLAUDE.md: JS constructing a `bootstrap.Modal` against
absent markup throws and takes the surrounding behaviour with it.

**One modal, two panes** for the archive and its restore confirmation. Stacked Bootstrap
modals bring backdrop and focus-trap problems for nothing; swapping the body keeps context
and returns to the list exactly where the user left it.

## Keeping the plan honest

Plan docs drift during testing. On projects the "as built" notes were kept current while
the spec text above them was not, so a message string rejected in testing survived 280
lines above the rule banning it. At Phase 5, reread the earlier phases against what
actually shipped and correct them — do not only append.

Record decisions **as they are made**, including ones that reverse the plan, with the date
and who decided. Three of the projects UI decisions only exist because they were written
down when they happened.

## Testing

Flat numbered lists, handed over in the message at the end of every phase or chunk, then
stop for confirmation. Say which items are already proven by a migration's own verify
block so only the human-necessary ones are done by hand. Projects ran to 67 items across
six phases; roughly 35 per module is workable.

The phases before the UI have no interface, so they are tested by console `fetch` calls
against the endpoint plus SQL for what the UI cannot show. Give the exact commands — a
wrong mode name or POST key wastes a round trip.

## Per-module starting conditions

What is known today, to save the first hour. Verify before relying on it.

| | `is_deleted` | `parent_*_id` | History no-op compare | Materialised children | Sites reading the table |
|---|---|---|---|---|---|
| **accounts** | needs adding | yes | 19 cols | none | ~20 |
| **stocklists** | needs adding | yes | 10 cols | `stocklist_premises` (imported, not derived) | ~24 |
| **opportunities** | needs adding | yes | 13 cols | routes, estimates | ~24 |
| **wayleaves** | **already there** (032) | yes | 19 cols | coverage — approval model, four sources | ~20 |

All four have a journal table, an access log, an entry in `getItemCompanyId()`'s map and a
`*name` autocomplete type, so those parts of the pattern apply unchanged.

**Wayleave has a head start and a trap.** The column exists and `dashboardCompanySql()`
plus both dashboard recent-item queries already filter it — but nothing sets it, and
`map_get_v2.php`'s soft-delete map covers `projects.projects` only. Its coverage model is
also the least like projects' premises, so question 3 needs real thought there. See
`docs/improvement-opportunities.md`.