# Project Delete & Restore

- **Date:** 2026-08-28
- **Status:** Complete
- **Status Date:** 2026-08-29
- **Phases:** 6
- **Phases Complete:** 6
- **Notes:** First delete/restore implementation in GeoLynx. Projects only — the other
  four modules follow the same shape later, module by module. Soft delete: nothing is
  ever removed from the database, so every audit trail and label lookup stays intact.
  **All phases complete, built and confirmed 2026-08-29**; migrations 061 and 062 applied.
  Delete is the gear menu in the project editor header; restore is the Archive on the
  project list. One item is outside this repo: the GeoServer WMS layer 33 (Projects (All))
  needs `is_deleted = false` in its SQL view or a CQL filter — Dave's side.

> **Rolling this out to another module?** Use
> `docs/module-delete-restore-pattern.md`, not this document. This is the worked
> example and the design record for *projects*; its substance is answers that are
> specific to this module. The pattern doc carries the decisions that transfer and the
> checklist for deriving the rest.

## Plan Phases

0. Schema — migration 061 (column, history, premises trigger, list view)
1. Permission — the `projects_approve` pseudo-module
2. Endpoint & write guards — `project_archive.php`, route gate, deleted-record refusals
3. Read surfaces — the `is_deleted = false` sweep
4. UI — delete in the editor, archive modal on the list
5. Documentation

## Problem

A project can be created but never removed. A mistyped boundary, an abandoned trial or a
duplicate stays in every list, every picker, every dashboard count and on the map for
ever, and the only way out is manual SQL against `projects.projects` — which bypasses the
history triggers, the premises refresh and company scoping all at once.

What is wanted is an ordinary delete for the person doing the work, and a way back for
the person who has to clean up afterwards. Deleting is destructive in effect (the project
disappears from the estate) but must not be destructive in fact: journals, history,
attachments, premises tombstones and the autocomplete label maps all have to survive, or
the audit trail acquires holes exactly where something went wrong.

### Design decisions (Dave, 2026-08-28)

These were settled before the plan was written and are recorded here because several of
them are not the obvious choice.

**Soft delete, one boolean, and no second pair of audit columns.**
`projects.projects.is_deleted`, defaulting to false, set true on delete and false on
restore. The same shape `wayleave.wayleaves` has carried since migration 032 — that
column was added for exactly this feature arriving, and its module will use it when its
turn comes.

"Deleted by" and "deleted when" are **not stored**: they are `modified_user` and
`modified_datetime` (Dave, 2026-08-29). The delete is an UPDATE like any other, so
`projects_update_modified_fields()` stamps `modified_datetime = now()` and the endpoint
sets `modified_user` in its own statement, exactly as `project_save.php` and
`map_boundary_update.php` do. Those two values then stay the delete's values for as long
as the project remains deleted, because Phase 2 refuses every write path against a
deleted project — the guards are needed for their own reasons, and this reading is a free
consequence of them. Restore bumps them again, which is correct: the project has left the
archive and the delete details are no longer the interesting fact about it. Every delete
and every restore is in `projects_history` regardless, which is the authority if the two
columns and history ever disagree.

**A project must be hierarchy-free before it can be deleted.** Not "no live children" —
no hierarchy at all: `parent_project_id IS NULL` **and** no other project naming it as
parent. The user unlinks the hierarchy first, which leaves a clean, standalone project in
the archive and a clean one to re-parent on the way back. This rule is what makes the
whole feature simple: a deleted project can never be a parent, can never be a child, so
there is no dangling `parent_project_id`, no restore-ordering rule, no orphan case, and
no lineage query that has to reason about visibility. Every other option considered
(block on live children only, clear the parent on restore, block restore until the parent
returns) buys nothing this does not, and each one leaves a state where a hierarchy fact
points at something nothing else can see.

**Live opportunity links block the delete too.** `opportunity.opportunity_project_link`
rows with `is_deleted = false` must be unlinked first, and the confirm modal names them.
Note what is *not* in this list: the wayleave–project and stocklist–project relationships
are **spatial, resolved live from geometry**, not stored links — filtering `is_deleted`
makes a deleted project vanish from them with nothing to detach. `reports.reports` carries
a `project_id` but nothing reads or writes it today and there is no screen to clear a row
from, so blocking on it could produce a delete that cannot be unblocked without SQL.

**A new pseudo-module, `projects_approve`, labelled "Project Approver".** Modelled on
`wayleaves_approve` exactly: enablement is checked against the real `projects` module,
permission against the pseudo-key, via `requireModuleAccess($pdo, 'projects',
'projects_approve', 'write')`. **write** grants delete and restore; **read** grants
sight of the archive with Restore disabled — someone who needs to audit what was removed
without being able to act. Delete additionally requires ordinary write on the project, so
the approver permission adds a gate, it does not replace one. The name generalises
deliberately: the unbuilt project-premises approval flow can hang off the same key rather
than needing a second pseudo-module.

**Status can be set as part of the delete, and stands on restore.** The confirm modal
carries the project status dropdown so a project can be archived *as* Cancelled in one
action rather than two. Restore changes visibility and nothing else: the project comes
back exactly as it was archived, Cancelled included, and whoever restores it sets a new
status if they want one. Status is a lifecycle fact somebody chose; the archive flag is a
separate axis, and reverting a deliberate choice silently would be the worse surprise.

**The journal entry is the user-visible record of both actions** (Dave, 2026-08-29).
Delete and restore each write a `projects.project_journal` row, whose `user_id` and
`log_datetime` capture who and when — so a project that is deleted and later restored
carries its own account of what happened, readable in the editor by the people who need
it rather than only in the database.

This is load-bearing, not decorative. The editor's audit panel is built by
`buildAuditLogQuery()`, whose static-field branch is driven by `field_type = 'static'`
meta rows — and `is_deleted` will never be one of those (a meta row would make it a form
field, which Phase 2 explicitly forbids). So the `projects_history` row records the flag
change at database level but **nothing renders it**: without the journal entry, a
restored project would show no sign anywhere in the UI that it had ever been deleted.
The journal is the layer a user can actually read, and it is the only one.

That gives three layers, each with a job: the **journal** is the user-visible account and
survives restore; **`modified_user` / `modified_datetime`** answer "deleted by / when" for
the archive list while the project sits in it; **`projects_history`** is the machine-level
authority if the other two are ever contradicted.

**A deleted project's premises are tombstoned by the existing trigger.** Not filtered in
the views, not left alone. Covered in full in Phase 0 below.

## Build

### Phase 0 ✅ — Schema (migration `db/061_project_soft_delete.sql`)

*Applied and confirmed 2026-08-29 (tests 1-3).*

**1. The column.** One, not three:

```sql
ALTER TABLE projects.projects
    ADD COLUMN is_deleted boolean NOT NULL DEFAULT false;
```

No `deleted_user` / `deleted_datetime`. The archive list's "deleted by / when" is
`modified_user` / `modified_datetime`, for the reasons set out in the design decisions
above — the delete is an ordinary UPDATE, both columns are already maintained on it, and
nothing may write to a deleted project afterwards to disturb them.

`projects.projects_history` takes `is_deleted` as a **nullable** column, matching how the
history mirror treats every other column and leaving pre-migration rows honest. It
already mirrors `modified_user` and `modified_datetime`, so the audit of who deleted what
and when needs nothing added.

**2. Index.** `CREATE INDEX projects_is_deleted_idx ON projects.projects (is_deleted)
WHERE is_deleted;` — **partial, deliberately**. The predicate that will appear in fifteen
queries is `is_deleted = false`, which matches nearly every row in the table and will
never use an index. The only selective question is "show me the archive", and a partial
index answers exactly that while staying a few pages wide however large the estate grows.
This diverges from `wayleaves_is_deleted_idx` (a full index on the same column, migration
032), and the divergence is the point.

**3. `public.fn_projects_history()` — replace in place. This is not optional and it is
easy to miss.** The function (migration 017) enumerates its columns twice, and both lists
must gain `is_deleted`:

- The **no-op suppression** block at the top compares every column it knows about and
  returns early if none differ. `modified_user` and `modified_datetime` are excluded from
  that compare **on purpose** — they bump on every update, so including them would
  defeat the suppression entirely. That is exactly what makes this step load-bearing: in
  a delete, `is_deleted` is the only changed column the compare could ever see, so
  **without this change a delete would write no history row at all**, and the one
  operation most in need of an audit trail would be the only one silently exempt from it.
- The **INSERT column list and VALUES list** must carry it, or every history row written
  after this migration records `is_deleted` as NULL and the flag is invisible in the
  audit.

`trg_projects_history` keeps pointing at the function; only the body changes.

**4. `projects.vw_projects_list` — add `WHERE p.is_deleted = false`** to the outer query.
Definition is 060's with that one predicate; `CREATE OR REPLACE VIEW`.

Two notes for the migration comment, both about things that need *no* change and would be
wrong to add:

- The `prems` CTE and its inline `cl` claimant subquery are **not** touched. They filter
  `state = 'approved'`, and a deleted project's premises rows are tombstoned to
  `'removed'` by step 5, so a deleted project already contributes nothing to any count.
  Adding an `is_deleted` join inside the CTE would be a second, redundant expression of
  the same fact — and per the note in 060, that CTE sits behind a `LEFT JOIN` where no
  filter can be pushed anyway.
- `projects.vw_project_premises` and `projects.vw_premise_claimants` need no change, for
  the same reason. Say so in the migration, so the next person does not add a join to
  `projects.projects` and reintroduce the cost 059's repair was written to avoid.

**5. The premises refresh — extend the trigger, reuse the branch that already exists.**

`projects.project_premises` is materialised from the boundary. A deleted project keeps
its rows unless something removes them, and those rows would keep counting as claimants:
a deleted project would show up as an overlap on a live one, which is precisely the
phantom-overlap bug class migration 059 exists to fix.

The fix is three small changes — the trigger, the trigger's function, and the refresh
function. The middle one was missed when this plan was first written and found while
building (2026-08-29); without it the other two do nothing:

- **Trigger condition.** `trg_projects_premises_refresh_update`'s `WHEN` clause gains
  `OR OLD.is_deleted IS DISTINCT FROM NEW.is_deleted`, alongside the existing `geom` and
  `parent_project_id` tests. A `WHEN` clause cannot be altered, so the trigger is dropped
  and recreated. The insert trigger is untouched — a project is never created deleted.
- **Trigger function.** `public.fn_project_premises_refresh()` branches on *what* changed:
  INSERT and a `geom` change call `refresh_project_premises(id, true)`, and everything
  else falls to the `ELSE` — the re-parent case — with `p_rescan = false`. A delete
  changes neither `geom` nor `parent_project_id`, so it would land in that `ELSE` and be
  refreshed without a rescan, which re-flags validity and **never touches containment**:
  the deleted project would keep every approved premise row and go on counting as a
  claimant. `is_deleted` therefore joins the `geom` test, so a delete and a restore both
  rescan.
- **Refresh function.** The `FOR v_proj IN` loop selects `p.geom AS geom`; it becomes
  `CASE WHEN p.is_deleted THEN NULL ELSE p.geom END AS geom`. That is the whole change.
  A deleted project then falls into the existing `ELSIF p_rescan` branch — the one
  written for "no boundary, so nothing can be inside it" — which tombstones every
  `source = 'boundary'` row to `state = 'removed'` without a single spatial query. Step 2's
  `is_valid` re-flag then runs over the affected UPRNs exactly as it does for any other
  change, so a parent that was showing a premise as invalid because its now-deleted child
  claimed it goes valid again on the same pass.

Restore is the same mechanism in reverse and needs no separate code path: `geom` comes
back, the rows revive from `'removed'` to `'approved'` through the existing `ON CONFLICT`
clause, and `proposed_datetime` survives untouched — so "when did this premise first
enter this boundary" reads correctly across an archive and a restore, exactly as it
already does across a shrink and a regrow.

`CREATE OR REPLACE FUNCTION` has no partial form, so the function is repeated in full,
as 059 did.

**No repair pass is needed.** Nothing is deleted at migration time, so there is no
existing state to correct.

Guard block at the head in the house style: fail if `projects.vw_projects_list`,
`projects.refresh_project_premises(integer, boolean)` or `public.fn_projects_history()`
is missing, and fail if `projects.projects` already carries `is_deleted` (the
already-run check).

### Phase 1 ✅ — The `projects_approve` pseudo-module

*Built and confirmed 2026-08-29 (tests 4-6).*

Three files, four sites, no migration — role permissions are assigned through the admin
UI, and `users.role_permissions.module` is free text. `admin_load.php` reads
`users.role_permissions` with no whitelist, so a `projects_approve` row loads back with
no change there.

- **`www/fn/admin_save.php:1502`** — `'projects_approve'` added to `$allowed`, directly
  after `'projects'`.
- **`www/js/admin_users.js:22`** — added to `MODULES`. All three loops over that array
  (`resetRoleModal`, `openEditRoleModal`, `saveRole`) look their radios up by name, so
  position in the array is presentation only.
- **`www/js/admin_users.js:476`** — `projects_approve: 'Project Approver'` in the label
  map that renders the Permissions column of the roles table. Not in the original plan;
  without it the roles list would show the raw key `projects_approve` against any role
  holding it, since the formatter falls back to `labels[p.module] || p.module`.
- **`www/html/html_body_admin_users.php:295`** — a `<tr data-module="projects_approve">`
  row with the three radios, directly under the `projects` row so the pair reads
  together, as the wayleave pair does. Uses the same `fa-check-double` icon as Wayleave
  Approver: the icon marks the row as an approver capability rather than a module, which
  is the thing a reader needs to see.

The comment above the row records what differs from the wayleave one: there, only
`write` is meaningful and `read` exists solely because the matrix is uniform. Here
**both levels mean something** — `write` deletes and restores, `read` sees the archive
with Restore disabled.

Three things it deliberately does **not** get:

- **No `public.app_modules` row.** Enablement is a property of the real module. Every
  gate calls `requireModuleAccess($pdo, 'projects', 'projects_approve', 'write')` —
  enablement checked on `projects`, permission on the pseudo-key. `wayleaves_approve` has
  no row either.
- **No `fn/routes.php` entry.** It gates actions, not a page.
- **No entry in `fieldMetaModuleConfig()` or `dashboardModuleMap()`.** Per CLAUDE.md,
  adding a module there switches five per-module maps on in `dashboard_load.php` and a
  label map in `admin_load.php`'s `moduleOverview()`; a key present in the config and
  missing from those emits a PHP warning into the JSON body and breaks the whole
  response. A pseudo-module has no fields, no statuses and no dashboard.

### Phase 2 ✅ — `www/fn/project_archive.php` and the write guards

*Both chunks built and confirmed 2026-08-29 — 2a the endpoint, 2b the guards.*

**2b as built.** Two helpers in `global_functions.php`: `isItemDeleted()`, built on
`getItemCompanyId()`'s map shape and answering `false` for any entity without the column
(so entity-generic callers need no special case), and `requireNotDeleted()`, which exits
with the same `'No Access'` every other gate emits. The route gate went into
`index.php`'s existing `$routeItemParams` block, **outside** the company branch so it
applies to company 1 too. Refusals added to `project_save.php` (the project itself, plus
a `parent_project_id` naming a deleted project), `project_journal_save.php`,
`project_load.php`, `map_feature_save.php`, `map_boundary_update.php`,
`image_upload.php`, `project_export_pdf.php`, `project_export_spatial.php` and
`project_fibre_allocation.php`.

`project_auto_route.php` is **not** guarded: it takes no project id in any request
parameter, so there is nothing to scope. The plan listed it on the assumption it did.

Two things found doing it:

- **`map_boundary_update.php` did not include `global_functions.php`.** Calling any shared
  helper from it would have been an undefined-function fatal visible only in the browser —
  the exact failure CLAUDE.md warns about. The include was added with the guard.
- **That file also has no permission gate whatsoever** — `loginCheck` and nothing else,
  while it updates `projects.geom` from a POST parameter. Any logged-in user can rewrite
  any project's boundary in any company. Pre-existing and outside this plan's scope;
  logged in `docs/improvement-opportunities.md` as serious.

Two corrections found while building 2a, both now in the code:

- **The endpoint needs two permission gates, not one.** Gating only on `projects_approve`
  would let a user holding the capability but no `projects` permission list every project
  name in their company through the archive. `wayleave_approvals_load.php` sets the
  precedent: gate the read on the **real** module, use the pseudo-key for the action. So
  every mode first passes `requireModuleAccess($dbh, 'projects', 'projects', 'read')`,
  then the `projects_approve` gate on top.
- **`project_save.php` must also refuse a `parent_project_id` pointing at a deleted
  project** — a 2b item. Phase 3 stops the autocomplete offering one, but a direct POST
  could still make a deleted project somebody's parent, which would break the
  hierarchy-free guarantee restore depends on. Nothing else in the plan closes that door.

**The editor already refuses a deleted project, by accident** (found testing 2a,
2026-08-29). `project_load.php:82` reads `projects.vw_projects_list`, which 061 now
filters, so a deleted project returns zero rows; `project_edit_v2.js:3380` treats a
failed load as *denied or record missing* and bounces to
`?do=dashboard&access_denied=1`.

That is the right outcome reached the wrong way, and it does not make 2b's guards
redundant — it changes what they are for. The bounce currently happens **after** the
editor shell has rendered and the load has round-tripped, and it tells the user they lack
permission when in fact the project was deleted. So 2b still needs:

- the `index.php` route gate, so the refusal happens server-side before any HTML and the
  user never sees a flash of an editor they cannot use;
- an explicit `is_deleted` refusal in `project_load.php` even though the view already
  yields nothing — so the behaviour is deliberate and survives anyone later re-pointing
  that query at `projects.projects`, which is where its sibling queries already read.

**No distinct deleted-record message** (Dave, 2026-08-29), reversing the plan's original
intent. The gate reuses the existing `?do=dashboard&access_denied=1`, whose wording is
deliberately undifferentiated: *"Access denied. That item does not exist or you do not
have permission to view that page."* Telling the user the project was **deleted** would
confirm that it exists to someone who may have no right to know that — a stale or guessed
id becomes an existence oracle. The generic message covers never-existed, no-permission
and deleted with one answer, and anyone entitled to know a project was archived can see it
in the archive. Test 19 is written to that behaviour.

**One new endpoint, four modes** (POST `mode`), following `opportunity_manage.php`'s
mode-dispatch shape and `wayleave_approval_decide.php`'s gating. A module-specific
endpoint is the right home for a module-specific action; this is not something
`project_save.php` should learn to do, because `is_deleted` must never become a saveable
field (see below).

- **`check`** — pre-flight for the confirm modal. Returns the blocker list so the modal
  can render its warnings *before* the user commits to anything.
- **`delete`** — re-runs every check server-side, then acts.
- **`restore`** — clears the flag.
- **`list`** — the archive, for the modal on the list page.

**Gating.** `check`, `delete` and `restore` require
`requireModuleAccess($pdo, 'projects', 'projects_approve', 'write')`; `list` requires the
same at `'read'`. All four then apply the record-level tier exactly as `project_save.php`
does: module write plus a company match, or an item-level grant standing on its own, with
company 1 exempt. Delete and restore additionally require ordinary **write on
`projects`** — approver is an extra gate, not a substitute.

**The blocking checks**, run inside the transaction after `SELECT ... FOR UPDATE` on the
project row so two concurrent deletes cannot race past each other:

1. `parent_project_id IS NOT NULL` → *"This project has a parent: X. Remove the parent
   link on this project first."*
2. `EXISTS (SELECT 1 FROM projects.projects WHERE parent_project_id = :id)` → *"N projects
   have this as their parent: … Remove the parent link on each of them first."* Returns
   their names and ids so the modal can list them. (Wording corrected during testing —
   see the rule under Phase 4.) **No `is_deleted` filter on this check**: a deleted
   project cannot have a parent (rule 1 is enforced on the way in), so every row here is
   necessarily live, and adding the filter would state a weaker rule than the one being
   enforced.
3. `EXISTS (SELECT 1 FROM opportunity.opportunity_project_link WHERE project_id = :id AND
   is_deleted = false)` → *"Linked to N opportunities — unlink these first."* Returns
   opportunity ids and names for the modal.

`check` returns them all so the modal can show every blocker at once rather than making
the user clear them one round-trip at a time.

**The delete itself**, one transaction: set `is_deleted = true` and `modified_user` to
the acting user (`modified_datetime` comes from
`projects_update_modified_fields()`); apply `project_status_id` if the modal sent a
changed one; insert the journal row. The history
trigger fires on the UPDATE and, with Phase 0's function fix, records the flag change.
The premises trigger fires on the same UPDATE and tombstones the boundary rows.

**The journal rows.** `projects.project_journal` is `(project_id, user_id, log_datetime,
log_text)`, so the actor and the time come from the row itself and only the text has to
be written:

- Delete: *"Project deleted."*, and when the modal changed the status, a second sentence
  — *"Status set to Cancelled."* — in the same entry, so the two facts stay together.
- Restore: *"Project restored."*

Both are inserted **here, inside the transaction**, rather than by a second call to
`project_journal_save.php`. That endpoint is a separate HTTP request: a note written
through it would survive a delete that then rolled back, leaving a journal claiming
something that never happened. It also refuses writes against a deleted project as of
this phase, so it could not write the restore note at all.

These entries surface in the editor's audit panel through `buildAuditLogQuery()`'s
journal branch, with no change needed there — the union already includes the journal
table. As set out in the design decisions, this is the **only** place a user can see that
a project was deleted, because `is_deleted` is not a static meta field and the audit
panel's static branch never sees it.

**Restore** is the mirror: `is_deleted = false`, `modified_user` set, a journal row. No
status change, no blockers — a hierarchy-free project has nothing to conflict with. The
premises trigger rebuilds containment.

**Write guards on existing endpoints.** A deleted project must refuse every write, not
merely be hidden. Each of these already resolves a project id and checks permission; the
addition is one predicate at that point:

- `project_save.php` — refuse. **And `is_deleted` must never be added to
  `$staticFields`** (line 69), for the same reason it is absent from
  `wayleave_save.php`'s list: the whitelist is driven by POST keys, and a saveable
  `is_deleted` is a delete with no permission check, no blocker checks and no journal.
- `project_journal_save.php` — refuse.
- `map_feature_save.php` (line 352) and `map_boundary_update.php` (line 201) — both
  `UPDATE projects.projects SET geom`. Refuse; a boundary edit on a deleted project would
  fire the premises trigger and resurrect its rows.
- `image_upload.php` — refuse a cover image against a deleted project.
- `project_export_pdf.php`, `project_export_spatial.php`, `project_auto_route.php`,
  `project_fibre_allocation.php` — read-side, but they take a project id directly; refuse
  for consistency.

**Route gate.** `www/index.php`'s record-level company gate block (the
`$routeItemParams` loop) is the natural home: it already resolves an item type and id for
each edit route, so the deleted check joins it rather than adding a second pass. Add
`isItemDeleted($dbh, $itemType, $itemID)` to `global_functions.php`, built on the same
`$tableMap` shape as `getItemCompanyId()` — `project` and `wayleave` have the column,
every other type returns false. On a hit, redirect to
`index.php?do=dashboard&deleted_record=1`, a distinct flag from `access_denied=1` so the
message can say the record was deleted rather than implying a permissions problem.

`project_load.php` gets the same refusal independently of the route gate. The endpoint is
requestable directly and does not route through `index.php` — the same reasoning
`map_get_v2.php` records for its own gate.

### Phase 3 ✅ — Read surfaces

*Both chunks built and confirmed 2026-08-29 — 3a the map (migration 062 plus the
`map_get_v2.php` predicate), 3b the lists and pickers.*

**3b found a live regression that 061 had already caused.** `opportunity_manage.php`'s
nearby-projects CTE selects `projects.*` **and** `o.is_deleted` from the link table.
Before 061 `projects.*` had no `is_deleted`, so the two later bare `is_deleted = false`
references resolved unambiguously to the link's column. Adding the column to
`projects.projects` gave that CTE two output columns of the same name, making those
references `column reference "is_deleted" is ambiguous` — so the opportunity editor's
nearby-projects panel has been erroring since 061 was applied. Fixed by aliasing the
link's column to `link_is_deleted`, which also says which one is meant. A sweep confirmed
this is the only query in `www/` that star-selects from `projects`, so the collision was
contained to it.

**A write guard belonging to Phase 2, found here.** `opportunityAddOne()` inserted a
project–opportunity link with no check that the project was live. Since
`project_archive.php` refuses to delete a project that has live opportunity links, adding
one afterwards would manufacture exactly the state that check assumes impossible. Guarded
with `requireNotDeleted()`, the same shape as the `parent_project_id` guard. This is what
lets the queries that reach projects *through* a live link skip a filter of their own.

**Three sites in the plan's list needed nothing, and the reasons are worth keeping:**

- **`stocklist_load.php` (all four sites)** — every one reaches `projects.projects`
  through `projects.project_premises` filtered `state = 'approved'`, and a deleted
  project's rows are tombstoned. This is not convention: `project_premises.source` has
  `CHECK (source IN ('boundary'))` and `refresh_project_premises()` is its only writer, so
  every row a deleted project holds is tombstoned. If a second source is ever added that
  CHECK must change, and that is the moment to revisit these four.
- **`opportunity_process.php` (146, 314)** and **`opportunity_manage.php` (151, 430,
  895)** — all reach projects through a live opportunity link, now guarded above.
- **`vw_projects_list` consumers** — filtered inside the view by 061.

**Two dashboard queries also gained the wayleave predicate.** `dashboardModuleRecent()`
and `dashboardRecent()` had no soft-delete filter at all, while `dashboardCompanySql()`'s
`$softDelete` map has listed `wayleaves` since it was written — so the tiles excluded
deleted wayleaves and the recent lists did not. A no-op today (nothing sets that flag
yet), but leaving one branch of the same query filtered and the other not would have been
a trap.

**The map layers were missing from this list, and structurally so** (found by Dave testing
Phase 2, 2026-08-29: a deleted project was still returned by
`map_get_v2.php?geotable=projects.vw_projects_geom`). The set of relations the map serves
is **data, not code** — `public.map_layers` rows carry `database_schema` /
`database_table` and a `url` naming a geotable — so a list built by grepping `www/` could
never have contained them. Nothing in the codebase names `vw_projects_geom` at all. The
authoritative list came from querying that table:

| Layer | Relation | Served by | Fix |
|---|---|---|---|
| 18 Project Boundary | `projects.vw_projects_geom` | `map_get_v2.php` | migration 062 |
| 35 Project Neighbours | `projects.project_nearest_neighbours` | `map_get_v2.php` | migration 062 |
| 33 Projects (All) | `projects.projects` | GeoServer WMS | Dave — GeoServer SQL view or CQL filter |
| 23 Parent/Child Links | `projects.vw_parent_project_links` | GeoServer WMS | none needed |

Layer 23 needs nothing in either place: that view is an INNER JOIN of projects to projects
on `parent_project_id`, and a deleted project has no parent and is nobody's parent, so it
can appear on neither side. The hierarchy-free delete rule paying for itself.

`project_nearest_neighbours` needed **both** sides filtered — a deleted project can appear
as the subject *and* as a live project's neighbour, and the second is the one that would
draw an archived boundary onto a live project's map.

**The filter went in the views, not the endpoint**, because neither view selects
`is_deleted`, so `map_get_v2.php` could not filter on a column that is not there. It is
also the better half: a filtered view is correct for GeoServer and every other reader, not
only for requests arriving through our endpoint. `map_get_v2.php` gained a relation-keyed
predicate for `projects.projects`, the one project relation it serves that does expose the
column.

**Lesson for the remaining modules:** when wayleave's turn comes, query `public.map_layers`
first. `wayleave.wayleaves` carries `is_deleted` and is absent from the endpoint's map on
purpose — nothing sets it yet — and is logged in `improvement-opportunities.md`.

Every query that reads projects needs `is_deleted = false`, with two deliberate
exceptions. Sites confirmed by inspection:

1. **`data_get.php`** — the generic source endpoint behind the project list and several
   map layers. Add the predicate for the `projects.projects` and
   `projects.vw_projects_list` sources next to `$companysql`, in the same block. The
   caller-supplied `where` is ANDed in, so no request can undo it.
2. **`map_get_v2.php`** — serves `projects.projects` geometry to the map. Add a
   soft-delete predicate keyed by relation, in the same place as the `$schemaModuleMap`
   gate. Projects only for now; `wayleave.wayleaves` belongs in the same map and is
   logged in `docs/improvement-opportunities.md` rather than folded in here.
3. **`autocomplete.php:103`** — the project lookup behind every `projectname`
   autocomplete, including the parent-project picker. A deleted project must not be
   offerable as a parent, or the hierarchy rule is defeated from the other end.
4. **`global_search.php:40`** — project search.
5. **`dashboard_load.php`** — add `'projects' => true` to `$softDelete` in
   `dashboardCompanySql()` and pass `$module` at the two project-panel callers that
   currently scope by company only (~719, ~777); the access-log join (~643) and the
   recent-items map (~585) need the predicate too. The comment in that function saying
   wayleave is "currently the only entity table with is_deleted" becomes wrong with this
   migration and must be updated in the same change.
6. **`account_load.php:460`** — projects listed under an account.
7. **`stocklist_load.php`** — 258, 261, 399, 595.
8. **`opportunity_manage.php`** — 103, 151, 201, 430, 744, 895 (project pickers and
   linked-project lists).
9. **`opportunity_process.php`** — 146, 314.
10. **`distance_analysis_process.php:473`** — the snapshot copied into the run's temp
    table; without the predicate an analysis silently includes archived projects.
11. **`wayleave_projects_load.php:48`** — the spatial project-overlap list on a wayleave.
12. **`wayleave_badge_counts.php:63`**.

**The two exceptions, which must NOT get the predicate:**

- **`admin_load.php:573`** — resolves an item-permission row's project name for display.
  A permission granted against a since-deleted project must still render its name, or the
  admin screen shows a bare id.
- **`resolveAutocompleteLabel()` / `getAutocompleteLabelMap()` in `global_functions.php`**
  for the `projectname` type — a stored id must always resolve back to a label. This is
  the standing rule recorded in CLAUDE.md: soft delete exists so label joins never break.

**Needs no change, and the migration comment should say why:** `project_load.php` 398–441
(premise counts), 480 (validity counts) and 524 (rival claimants) all read
`projects.project_premises` filtered on `state = 'approved'`, and a deleted project's rows
are tombstoned — so a deleted project drops out of every one of them without a join to
`projects.projects`.

### Phase 4 ✅ — UI

*Both chunks written 2026-08-29. **4a — the editor delete**: confirmed. **4b — the list
archive**: awaiting testing.*

**4b as built.** Route resolved first this time: `projects` renders
`html/html_body_projectlist.php` and `js/project_list.js`, both live. The Archive button
needs `projects_approve` **read**; whether Restore is offered on a row comes from
`can_restore` in the list response, which is **write** — so a reviewer can audit what was
removed without being able to act.

**One modal, two panes, not two stacked modals.** Restore needs a confirmation, and
stacking Bootstrap modals brings backdrop and focus-trap problems for no benefit: swapping
the modal body keeps the context, needs no second instance, and returns to exactly the
list position the user left. The footer swaps with the pane (Close on the list, Back +
Restore on the confirmation).

The restore button carries no row data in its markup — the handler reads the row through
Tabulator's `cellClick`, so there is nothing to escape. On success both tables reload:
the archive it just left and the project list it just rejoined, since leaving either stale
would show the project in two places or in neither.

**4a as built.** Two files, both confirmed live by resolving the route first:
`routes.php` gives `projectedit` a `head_nav` of `html_header_nav_v2.php`, an **empty**
`nav_html`, and `html_body_projectedit.php` — so the editor's header bar and every panel
live in that one body template.

*Corrected mid-build.* The button was first written into
`www/html/nav_projectedit_html.php`, which is **dead** — the route sets `'nav_html' =>
['']`, and nothing in `www/` references the file. It reads convincingly (it contains
`projectEditSaveButton` and an Export dropdown) which is exactly why it misleads. Caught
by Dave: *the editor has no sidebar.* Removing it is logged in
`improvement-opportunities.md`. The lesson is in memory: resolve the route in
`fn/routes.php` before editing any template — CLAUDE.md's **Request Routing** section
documents that lookup, and it is procedure, not background.

**Delete is a gear menu between Save and Export, not a button** (Dave, 2026-08-29). It
was first built as a plain Delete button beside *Exit Project Editor* — the control people
click on the way out of every project, so a mis-click hazard the confirm modal would catch
but should not have to. Moving it one level down, between Save Project and Export Data,
puts it next to nothing clicked often while keeping it in the action group where it stays
discoverable. It is a **menu** rather than a relocated button so later rare or destructive
actions (Duplicate, Transfer) have an obvious home instead of accreting across the header.

The menu is rendered by PHP only for a holder of `projects_approve` write, using
`global $pdo;` — the pattern `nav_global.php` and `nav_admin.php` use, since `load_file()`
includes templates from inside a function. **The wrapper condition hides the whole menu**,
because Delete is currently its only item; adding a second item means changing that
condition to *any item visible*, not just adding an `<li>`, or a user without
`projects_approve` gets an empty gear.

The trigger id stayed `projectDeleteButton`, so the JS needed no change — its handler is
delegated off `document`.

**The save button's `d-none` is not a permission pattern and was not copied.** It is
tab-driven: `project_edit_v2.js:7248` hides it on the map, network, fibre, journal,
premises and attachments panels. Delete is not tab-specific, so it stays visible
throughout.

The modal markup went into `html_body_projectedit.php`
**unconditionally** — the `#uploadModal` precedent in CLAUDE.md is the reason: JS that
constructs a `bootstrap.Modal` against markup a page lacks throws on `backdrop` and takes
the panel with it. Behaviour appended to `project_edit_v2.js`, using the `project` global
from `main.js:18`.

The status dropdown clones its options from `#project_status_id`, the Main Details status
field — `get_update_form.php` has already served those from the `project_status` options
source, so cloning keeps one source of truth and picks up an admin's changes for free. If
that field is absent for any reason the row hides rather than offering an empty select;
the status change is optional and the delete works without it.

**Editor — the delete button** (`www/html/html_body_projectedit.php`, the header bar).

A red outline button appended to the existing btn-group beside *Save Project* and *Export
Project Data*, rendered by PHP only when the user holds `projects_approve` write —
server-side, so there is no flash of a button that then vanishes. The endpoint enforces
regardless; visibility is cosmetic.

**The confirm modal** (`#projectDeleteModal`, markup in
`www/html/html_body_projectedit.php`, behaviour in `www/js/project_edit_v2.js`). Opening
it calls `project_archive.php` with `mode=check` and renders:

- The project name in the title and **on the confirm button** — *"Delete Ashby Ph2"* —
  so what is being deleted is on the thing you click.
- Blockers, if any: parent set, child projects (named, with links), live opportunity
  links (named, with links). With any blocker present the confirm button stays disabled
  and the modal explains what to remove.

  **Wording rule (Dave, 2026-08-29): every instruction says to REMOVE a link, never to
  "re-parent".** The first build told the user to "re-parent" a child project, which
  implies the child must be attached to some other project. A parent link is optional, so
  that is an assumption about how the customer works rather than a statement of what the
  delete requires. Say the one thing that has to happen and nothing beyond it. Each
  blocker also names *where* the link lives, because it is not always on the project being
  deleted: its own parent link is on it, a child's is on the child. The same rule governs
  the endpoint's refusal message.
- A **status dropdown**, defaulted to the project's current status, so the project can be
  archived as Cancelled in the same action. Options come from the status field already
  rendered in Main Details — cloned client-side rather than fetched again, since
  `get_update_form.php` has already served them from the `project_status` options source.
  It carries **no help text**: a note explaining that the status is not reverted on
  restore was written and cut — see the copy rule below.
- One sentence saying it can be restored from the project list archive. That is what
  keeps a single-click confirm honest.

**Copy rule: no mechanics, no obvious consequences** (Dave, 2026-08-29, three rounds of
the same correction). UI copy states what the user gets. Two things get cut every time:
**internal mechanics** — anything that always follows from the action, such as premises
recalculating, map layers refreshing, pickers updating — and **the obvious consequence of
the verb**, since "delete removes it from lists and the map" is what delete means. What
survives is only what the user could not predict *and* can act on: that the action is
reversible and from where, why a control is disabled, what must be cleared first.

What was cut here, all of it written by me and rejected on review:

- Delete footnote — *"The project is removed from lists, pickers and the map, and can no
  longer be opened or edited. Nothing is erased — it can be restored from the Archive on
  the project list."* → *"This can be undone from the Archive on the project list."*
- Status help text — *"Optional. The status you leave here stays with the project and is
  not reverted if it is restored."* → **cut entirely.**
- Archive modal intro — *"Projects that have been deleted. Nothing here is erased…"* →
  **cut entirely**; the modal is titled Archived Projects.
- Restore confirmation — *"Its premises are recalculated from its boundary."* →
  *"This will restore the project and it will be available for editing."*

The last one matters most as a lesson: it was written **after** the first correction and
defended as "the non-obvious part". Being non-obvious is not the test — mechanics stay out
even when they are interesting. It was rejected as *"internal GeoLynx mechanics that always
happen from a user perspective, another pointless comment."*

On success: `window.location = 'index.php?do=map'`.

The page must carry the modal markup — the `#uploadModal` precedent in CLAUDE.md is the
warning here: JS that constructs a Bootstrap modal against markup a page does not have
throws and takes the rest of the panel with it.

**List page — the archive** (`www/html/html_body_projectlist.php`,
`www/js/project_list.js`).

An `[Archive]` button in the header actions beside *Export Data*, rendered by PHP only
when the user holds `projects_approve` read. It opens `#projectArchiveModal`, which
holds its own small Tabulator fed by `project_archive.php?mode=list`: project name,
status, deleted by, deleted date, and a Restore button per row — disabled, with a
tooltip, for a read-only holder. The two audit columns are `modified_user` (resolved to a
name) and `modified_datetime`, labelled *Deleted by* and *Deleted* because on a deleted
project that is what they mean.

Restore opens a small second confirmation naming the project, calls `mode=restore`, then
refreshes the archive table and the main project list so the project reappears without a
page reload.

**Refreshing the main list must not rebuild it** (found in testing, 2026-08-29). The first
build called `projectListLoad()`, which empties `#project-list` and constructs a new
Tabulator — so a restore threw away the user's header filters, sort and page and dropped
them back on a default list. `projectListRefresh()` calls `replaceData()` on the existing
table instead, so all three survive; it falls back to a full load only when there is no
table to replace data into, where nothing could be preserved anyway.

That matters more than a nicety: keeping the list's filters and page position is the whole
reason the archive is a modal rather than a toggle over the same table, so a restore that
reset the list defeated the choice the modal was made for.

### Phase 5 ✅ — Documentation

- **`CLAUDE.md`** — a short **Project Delete & Restore** section after *Project Premises*:
  the hierarchy-free rule, the `projects_approve` pseudo-module and its two levels, the
  fact that deletion tombstones premises through the existing trigger, and the two read
  surfaces that must keep resolving deleted projects. Add `projects_approve` to the
  permission-system notes.
- **`docs/improvement-opportunities.md`** — four items were logged from this build, three
  of them found by accident while doing something else:
  - **`map_boundary_update.php` has no permission gate at all** (serious) — `loginCheck`
    and nothing else, while it updates `projects.geom` from a POST parameter.
  - **Wayleave soft delete is declared but not wired up** — including the
    `map_get_v2.php` entry for `wayleave.wayleaves`.
  - **The wayleave projects tab's Status column is always empty** — an alias/field-name
    mismatch, unrelated to this work.
  - **Delete the dead `www/html/nav_projectedit_html.php`**.
- **This document** — `Status` / `Phases Complete` updated as phases land, ✅ on each
  completed phase heading.

## Testing checklist

1. Migration 061 applies cleanly on a database at 060, and re-running it fails on the guard.
2. `projects.projects` has `is_deleted`, false on every existing row, and no other new column.
3. Every existing project still appears in the project list, with unchanged premise counts.
4. Admin → Users & Roles shows a **Project Approver** row under Projects with the three radio options.
5. Setting a role to Project Approver **write**, saving, and reopening that role brings the radio back on write; the same for **read**; setting it back to None saves as None and the row is gone from `users.role_permissions`.
6. The roles list Permissions column shows "Project Approver" with its level badge, not the raw key `projects_approve`.
7. A user with `projects` write but no `projects_approve` sees no Delete button in the editor and no Archive button on the list.
8. That user calling `project_archive.php` with `mode=delete` directly is refused.
9. A user with `projects_approve` **read** sees the Archive button, sees the archived projects, and cannot click Restore.
10. Delete on a project with a parent set is blocked, and the modal names the parent.
11. Delete on a project that is a parent is blocked, and the modal names every child.
12. Delete on a project linked to a live opportunity is blocked, and the modal names the opportunities with working links.
13. Clearing all three blockers makes the confirm button live without reopening the modal.
14. Deleting a clean project redirects to the map, and the project is not on it.
15. The deleted project is gone from the project list, and the list count drops by one.
16. It is gone from global search, the parent-project autocomplete, the account's project list, the opportunity project picker and the wayleave overlap list.
17. Dashboard project counts and the recent-items panel drop it.
18. A distance analysis run started after the delete does not include it.
19. Loading `?do=projectedit&project=X` for the deleted project redirects to the dashboard with the standard access-denied message, server-side — no flash of the editor shell first, and the message does not reveal that the project exists.
20. `project_load.php`, `project_save.php` and `project_journal_save.php` posted directly against the deleted id are all refused.
21. A map boundary edit posted directly against the deleted id is refused.
22. `projects.project_premises` shows the deleted project's boundary rows as `state = 'removed'`, none `approved`.
23. A live project that overlapped the deleted one no longer reports the overlap, and its Overlap chip count drops accordingly.
24. A live parent whose premises were counted at a since-deleted child now counts them itself (This Project, not Sub-projects).
25. `projects.projects_history` has a row for the delete, with `is_deleted = true` and the right user — this is the one Phase 0 step that fails silently if missed.
26. `projects.project_journal` has the delete note against the right user and time, and it names the new status when one was set.
27. Setting a status in the delete modal changes `project_status_id`; leaving it alone does not.
28. Admin → Users & Roles still shows the project name against an item-level permission on the deleted project.
29. An autocomplete field elsewhere holding the deleted project's id still renders its name rather than a bare number.
30. The archive modal lists the deleted project with the right name, status, deleted-by and date, and the deleted-by is the user who actually pressed Delete, not whoever last edited the project.
31. Restore asks for confirmation, then returns the project to the main list without a page reload.
32. The restored project is back on the map, in search, and in the pickers.
33. Its premises rows are `approved` again, and `proposed_datetime` is the original value, not the restore time.
34. Its status is still whatever the delete set — restore did not revert it.
35. `modified_user` / `modified_datetime` name the restorer and the restore time afterwards, and history holds both the delete and the restore.
36. Opening the restored project's audit panel shows both the delete and the restore entries, each naming the user who did it and when — this is the only place in the UI either fact appears.
37. The restored project can be re-parented normally, and the premises re-flag follows.
38. A second delete of the same project works, and the archive shows the newer delete details.
39. A user from another company cannot see the deleted project in the archive, and cannot restore it by posting its id.

## Risks

- **The `fn_projects_history()` no-op suppression is the sharp edge of this plan.** A
  delete changes one column that the function does not currently compare, so if the
  suppression list is not extended the delete writes no history row and nothing appears
  broken — the feature works, the audit is silently empty. Test 23 exists for this.
- **The read-surface sweep is wide and easy to leave half-done.** Twelve endpoints plus
  two generic ones, and a missed site does not error — it leaks a deleted project into a
  list. Tests 13–16 cover the visible ones; the rest is care.
- **The archive's "deleted by / when" depends on the write guards holding.** Reading it
  from `modified_user` / `modified_datetime` is correct precisely because nothing may
  write to a deleted project. A write path missed in Phase 2, or a manual UPDATE, would
  not error — it would quietly relabel the delete as whoever touched it last. The damage
  is bounded to that one column pair in the archive list: the journal entry and
  `projects_history` are both immune to a later write, so the record of who deleted what
  survives regardless. Test 28 checks the common case.
- **Two sites must be left alone.** `admin_load.php`'s label lookup and the autocomplete
  label resolvers must keep resolving deleted projects. A well-meaning sweep that adds
  the predicate everywhere breaks label rendering in exactly the places soft delete
  exists to protect.
- **The premises trigger now fires on a non-geometry change.** `is_deleted` joins `geom`
  and `parent_project_id` in the `WHEN` clause. The delete path is cheap (the no-boundary
  branch, no spatial query), but restore re-runs full containment for that project — a
  large boundary will make the restore call take as long as a boundary save does today.
- **The hierarchy rule will feel strict the first time it is met.** Archiving a three-deep
  programme means clearing three parent links first. That is the deliberate trade: the
  cost lands once, at delete time, on the person who can see the hierarchy, instead of
  landing on every lineage query for ever.