# Project Milestones - **Date:** 2026-10-02 - **Status:** Complete - **Status Date:** 2026-10-07 - **Phases:** 7 - **Phases Complete:** 7 - **Notes:** Promoted from the *Project milestones & snapshots* item in `docs/improvement-opportunities.md` (2026-09-09). Design settled in conversation 2026-10-01 → 02. Depends on the planner module update (`docs/2026-09-09-planner-module-update.md`), which has shipped: Releases 1 and 2 are built and tested, migrations 066–110 applied. This is the trigger the assets module needs (`docs/2026-09-02-asset-management-research.md`, D6 resolved as promotion); nothing here builds assets, but the seams are listed at the end. The as-built PDF export is **its own feature**, not a phase here — see *Seams*. ## Plan Phases 0. ✅ Prototype — the Milestones tab and the compare page as `www/test.php` design options (decided 2026-10-05) 1. ✅ Schema — milestone types, headers, payload, capture and lock functions (migration 115, tested 2026-10-05) 2. ✅ Admin — the Milestone Types pane in Planner admin (tested 2026-10-05) 3. ✅ The Milestones tab — list, save, delete, the changed-since badge (tested 2026-10-06) 4. ✅ Locking — an as-built locks the plan; an approver unlocks it (tested 2026-10-06) 5. ✅ Compare — the A-B page over two milestones, or a milestone and live (tested 2026-10-07) 6. ✅ Documentation — CLAUDE.md, the backlog, the assets seams (2026-10-07) ## Problem A project moves through planning and build with nothing marking where it was at any point. The planner holds one state, the current one, so there is nothing to sign off, nothing to build against, and when the build diverges from the plan there is no record of what the plan was. UK telcos require an **as-built**: a record of how the network was actually built, which becomes part of their asset base once the project completes, and which often has to show the **difference** from the plan — a cable diverted around a construction problem, a chamber moved, a route pulled through a different duct. The scenario is telco because that is the one Dave knows; the need is not sector-specific. Downstream, the assets module (research doc D6, option b) promotes planned elements to asset records *at a build-complete milestone*. Without milestones it has no trigger and no defined source set. ### What this build is not - **Not the assets module.** Promotion is designed for, not built. No `promoted_*` columns land here; the assets plan adds them. - **Not an as-built PDF.** `project_export_pdf.php` runs scripts from outside the repo and is already flagged for consolidation; pulling it in here drags that cleanup along. This plan leaves a flat, filterable view that QGIS or GeoServer can read, and stops there. - **Not a change to the planner's save path**, beyond a lock check at the top of the endpoints that write the plan — listed in Phase 4, and the list is the lock. ### What changed since the backlog item was written The backlog item chose "snapshot, do not reconstruct from history" because only cables had history. **That is no longer true.** Migrations 070–110 gave every planner table a history table with INSERT / UPDATE / DELETE triggers — `elements`, `element_routes`, `element_ports`, `element_connections` and all five EAV value tables — and migration 073 seeded a baseline history row for every migrated element. Reconstructing a project as at a timestamp is now technically possible. It is still not what this build does (decision 1), but the history tables earn a role they could not have had in September (decision 2). What history still cannot give: the **registries** as they stood at a time. Categories, types and statuses have no history tables; a row in use is deactivated, never deleted (`admin_save.php:2472`), so an id always resolves, but a renamed type reads with today's name. And `projects.project_premises` has tombstones with timestamps, not history, so a premise count as at a date is approximate. ## Settled design decisions Recorded because they are the reasoning behind the schema, and re-deriving them later is expensive. Dates are when Dave settled them. | # | Decision | Reasoning | |---|---|---| | 1 | **Snapshot the payload at save time; never reconstruct it from history** (2026-10-01) | Reconstruction is the simplest *schema* and the hardest *reads*: five window queries, two through a join because routes and ports history carry no project id, run by every consumer — map layer, diff, promotion. Every column added to `planner.elements` must be added to the reconstruction query, which is exactly the trap 079 fell into with the history function. And a signed-off as-built would be a derived answer that a later bug fix changes retroactively. A snapshot is immutable by construction and costs a few hundred rows per project per milestone | | 2 | **History is used forwards, not backwards** (2026-10-01) | Dave's reconstruct suggestion walked history *back* to a time. The hybrid walks it *forward*: any history row stamped after a milestone's `captured_datetime` means something changed since. One indexed count gives the tab its "changed since #4" badge, and the same rows give a who-changed-what log between two milestones that a pure snapshot diff cannot show. That is all history is asked to do | | 3 | **One payload row per element: columns where the source is fixed, `jsonb` where it is genuinely variable** (2026-10-02) | The payload mirrors `planner.elements`' real columns, plus the captured registry names (decision 5), as columns; dynamic field values, routes and ports are three `jsonb` columns. Dynamic fields are EAV, so an admin adding one creates no column and the values need a key-value map whatever the shape. Routes and ports are ordered lists of variable length per element. Everything else is fixed and stays a column, so QGIS, GeoServer and `psql` read the payload directly and the diff can name a change in the planner's own terms. **A whole-row jsonb was proposed and withdrawn** (Dave's challenge, 2026-10-02): the "schema drift" case for it was overstated — of the four migrations cited, only 099 added a column to `elements`; 091 was on the registry, 093 on routes and 100 a new table, none of which jsonb would have captured either. Drift arrives as a migration, and that migration alters the mirror, the capture function and the diff in one change. Three places, caught by review, not a hazard | | 4 | **Routes, ports and parent go inside the element's jsonb; connections get their own table** (2026-10-01) | The most common build change — a cable rerouted through a different duct — is a change to the cable's route rows, not to the cable. Embedding them means "what changed about this cable" is one comparison covering geometry, attributes and relationships, and the diff is written once. A connection is a join *at* a structure between two ports of two elements, so it has no single owning element and is stored and diffed as its own set | | 5 | **Registry names are captured alongside the ids** (2026-10-01) | The registries have no history (above). Storing `type_name`, `category_name`, `status_name` and `kind_label` in the jsonb beside `type_id` etc. makes a milestone self-describing: a report read three years on shows the type as it was called when the build was signed, and a type rename cannot change what an as-built says | | 6 | **Types in Planner admin, system-assigned numbers, free titles** (2026-10-02) | A hybrid of freeform and defined stages. `planner.milestone_types` is an admin-managed list seeded *Design*, *Issued for Construction*, *As Built*; one flag, `is_as_built`, carries the behaviour (lock and, later, promotion). The user picks a type and gives a free title and notes. The **system assigns `milestone_no` per project**, so milestones are #1, #2, #3 and the number is the identity — which removes the problem Dave raised of a user calling both the plan and the as-built "V2". Planner admin rather than projects Module Management because the flags are about planner data and the pane sits beside the kinds, categories, types and statuses it governs | | 7 | **An as-built that changes nothing is still a save** (2026-10-02) | Dave's challenge: if nothing moved between Plan and As Built, is the as-built just the same elements again? Yes, and that is correct. The two milestones *assert different things* — Plan says "we intend this"; As Built says "this is what stands in the ground, and someone signed it" — and the assets module promotes from the second only. The save **warns** "no changes since #3" and allows it. A few hundred duplicated rows is a cheap price for the as-built being self-contained | | 8 | **More than one as-built per project** (2026-10-01) | Phased builds are real, and changes happen after the first as-built. Each as-built promotes what it captured; promotion is idempotent on `element_id` when the assets plan builds it, so a second as-built updates assets rather than duplicating them. The cheaper alternative in the backlog — one promotion per project with phases as child projects — is not needed | | 9 | **Lock the plan, not the project** (2026-10-01; boundary writers corrected 2026-10-05) | An as-built milestone locks planner geometry and relationships **and the project boundary**: `planner_save.php`, `planner_ports_save.php`, `planner_connections_save.php` refuse, and so does every writer of `projects.projects.geom`. The boundary lives on the project row, not in `planner`, and Dave's point (2026-10-05) is that it is still part of the plan — a built network's footprint is as signed-off as its cables. **The plan first named `map_boundary_update.php`, which has had no callers since July.** The editor's boundary save is the `projectBoundary` branch of `map_feature_save.php`, and `project_save.php` carries `geom` in its static whitelist, so a direct POST can write the boundary though the form never sends it. Phase 4 lists all three. Project fields, status, dates, parent, attachments, the journal and the premises refresh all keep working — a built project still has a lifecycle, and admin on the project is not admin on the plan. Schematic node positions (`sld_x` / `sld_y`, written by `network_topo_save.php`) are presentation, not plan, and stay editable | | 10 | **An approver can unlock; a promoted milestone cannot be unlocked** (2026-10-02) | Unlock is an explicit act with a required reason, journalled, needing `projects_approve` write — the same pseudo-module that deletes and restores projects, so no new permission key. Once the assets module has promoted a milestone its lock is **permanent**: the plan has become assets and changes from there on are asset changes. Written here so the assets plan inherits the rule; the lock check is one SQL function so assets can tighten it without touching the four endpoints. Relocking after an unlock is saving another as-built (decision 7 makes that legal even with no changes) | | 11 | **Compare is element-grained, server-computed, and either side may be live** (2026-10-01) | Added, removed, moved, attributes changed, relationships changed, unchanged — flags, not a single class, because one element can be moved *and* rerouted. "Live" is the capture query run without inserting, so "what has changed since Design #2" and "Design #2 against As Built #4" are the same code and the same screen. Geometry comparison is **exact** (`ST_Equals`) in v1 with the shift distance returned per row, so whether a 2 cm snap nudge should count is a later tuning decision made with real data, not a guess now | | 12 | **Connections diff on their natural key, never on `connection_id`** (2026-10-02) | A splice broken and remade gets a new id but is the same join. The key is (at element, from element + port group + port no, to element + port group + port no); a different `connection_kind` on the same key is *changed*, not removed-and-added | | 13 | **The splicing schedule is captured as a third payload** (2026-10-02) | It is derived from ports and connections, which are in the payload, but regenerating it over milestone tables means a second copy of `vw_splice_schedule`'s logic. Inserting the live view's output at capture is one statement and records exactly what the user saw | | 14 | **The tab lives in the project editor; compare is its own page** (2026-10-01) | A milestone is a lifecycle fact about the project, so list, save, lock and the changed-since badge sit in the left nav beside status, journal and attachments. A-B comparison wants the whole screen — a map and a table — so it is a route of its own, `?do=projectmilestones`, built like `?do=projectplan`, opened from the tab | | 15 | **Milestones are immutable; a non-as-built one can be deleted, an as-built never** (2026-10-01) | Nothing on a milestone is edited after capture. Anyone with project write may delete a Design or IFC milestone, journalled. An as-built is a signed record and stays. Project soft-delete leaves milestones in place | | 16 | **Tables in `planner`, not `projects`** (2026-10-01) | The content is planner data, the assets module will read it, and `map_get_v2.php`'s schema gate (`'planner' => 'planner'`) then covers the milestone view with no change | | 17 | **Milestone layers are served through the existing map endpoint with `filter=`** | `planner.vw_milestone_elements` carries `project_id` so the editors' appended `where={"project_id":N}` still applies, and `filter={"milestone_id":N}` narrows to one milestone — the key migration 082 introduced for exactly this shape. GeoServer is visualisation only and configured per instance (Dave, 2026-10-01), so publishing a milestone layer there is configuration, not code | ## Build ### Phase 0 — Prototype Per Dave's standing preference, design options are shown as an interactive prototype, not described. One `www/test.php` page with a design switcher and the ugly cases, each option annotated Fits / Reads / Cost / Watch. **The Milestones tab.** One design question: the list as cards in capture order or as a Tabulator grid. Scenarios: no milestones yet; one Design milestone; a project locked by an as-built, viewed by someone who cannot unlock; the same viewed by an approver; the changed-since badge at 0, 3 and 340. **The compare page.** Three layouts to choose between: - *One map, overlaid.* A and B drawn on one map, A in one hue and B in another, changed elements emphasised, the diff table beneath or beside. - *Two maps, side by side, synchronised.* A left, B right, one view. Reads well for a diverted route; halves the width of each. - *One map with a swipe.* A under, B over, a draggable divider. Familiar from imagery comparison; poor for a table-driven workflow. Scenarios: no differences; one cable rerouted with **no geometry change** (the case the backlog item says must not read as "no change"); one chamber moved 40 m; an element added and one removed; 300 differences. Palette judged against the basemap, one hue at three weights, per the map-colour rule. The chosen layout is recorded here before Phase 5 starts. **Compare page — decided (Dave, 2026-10-05).** - **Layout 1, one map overlaid.** B drawn in the accent at full weight, A dashed and lighter beneath it, a hairline joining a moved point to where it was, the unchanged network grey under all of it. - **Table beside the map, map on the right**, as the opportunities module. - **Emphasis is on the changed run, not the whole element**, and both `Emphasis` (changed run / whole element) and `Unchanged` (faint / hidden) are **user controls on the built page**. The need came from the prototype: a 20 m diversion on a 1 km feeder is invisible when the whole line carries the emphasis, and hiding unchanged elements did nothing to a changed line. The diff stays element-grained (decision 11); the changed run is one extra geometry per row from the compare endpoint — B's line less a buffer of A's, and the same the other way for the ghost — at a tolerance to be set on real data (0.5 m in the prototype; snapping moves vertices by about that). Where the geometry has not changed (a reroute, a status change) there is no run and the whole element carries the casing. The unchanged run of a changed line is drawn as unchanged, so `Unchanged` → hidden removes it too. - **Side labels top-centre of the map; legend bottom-right.** Top-left covers the zoom buttons, bottom-left the scale bar. - **Map layers as the project editor has them.** The prototype's basemap dropdown was a fixture for judging colour; the page takes the instance's layer stack. **Milestones tab — decided (Dave, 2026-10-05).** - **Cards, newest first.** One card per milestone, number first, the locking as-built tinted with its own Unlock, notes visible. - **The nav badge is the milestone count**, hidden at zero — what the tab holds, as the Wayleaves badge is. The changed-since figure is a chip on the latest card, worded as *edits since*, never as changes or differences. - **One compare row above the list** — A, B, Compare — with A defaulting to the latest milestone and B to the live plan. No per-milestone Compare buttons. - **A locking milestone shows no edits since.** Nothing is edited under a lock, so a scenario that said otherwise was wrong; the one exception is the schematic-drag point under Phase 3's `changed` mode. **Still open before Phase 3**, listed on the prototype page: where the save form sits (above the list, or a modal); where the compare row sits; whether a locked plan refuses a Design or IFC save or only warns that nothing has changed; whether a deleted milestone's number is reused by `max + 1` (the plan's numbers are the identity, so the recommendation is a per-project counter or a soft delete); and the lock banner copy for the non-approver. ### Phase 1 — Schema (migration 115) **Built and tested 2026-10-05 as `db/115_project_milestones.sql`** (fourteen checks, all passed; one test was written with the pre-082 kind names — the values are `conductor` and `conduit`, not `cable` and `duct`, and the payload carries whatever `planner.elements` holds). What the file settles beyond the text below, each stated in its header: - **A deleted milestone is soft-deleted.** `project_milestones.is_deleted`; the views filter it, Phase 3's delete sets it and removes the payload rows. The number is the identity, and `max + 1` over a table that keeps its rows never reuses one — the answer to the number-reuse question raised at Phase 0. An as-built cannot be flagged deleted (CHECK) or deleted (trigger). - **The header and the three payload tables are immutable by trigger.** Only the unlock record and `is_deleted` may change on a header, an unlock cannot be reversed, and the payload refuses every UPDATE. Decision 15, held by the database. - **`milestone_types.is_as_built` is guarded by trigger** once a milestone of the type exists; the Phase 2 admin check is a courtesy on top. - **Counts are written with the header INSERT**, counted before the payload goes in and asserted against the inserted row counts, because the immutability trigger would refuse an UPDATE afterwards. - **`capture_milestone()` locks the project row `FOR UPDATE`** so two captures of one project cannot take the same number. It changes nothing on the row, so no premises trigger fires. - **The splice schedule key includes `port_group`**: `port_no` is only unique within a group. - **The five EAV history tables get `(element_id, record_datetime)` indexes.** They had no element_id index at all, and carry `record_datetime` rather than `history_datetime`. - **`milestone_payload()` is `LANGUAGE sql`**, since in plpgsql the RETURNS TABLE names would be variables colliding with the columns they mirror. The verify block checks the table and the function agree column for column, which is the three-places rule made mechanical for two of the three. - **Connections are captured only between live elements**, so the connection set and the element set of a milestone always refer to each other. **Registry** `planner.milestone_types` — `milestone_type_id`, `type_key` (unique), `type_name`, `is_as_built boolean NOT NULL DEFAULT false`, `display_order`, `is_active`, audit columns. Seeded: `design` *Design*, `ifc` *Issued for Construction*, `as_built` *As Built* (the only one flagged). Protection follows the registry rule: a type with milestones cannot be deleted, only deactivated, and **`is_as_built` cannot change once a milestone of that type exists** — changing it would retroactively change what those milestones lock. **Header** `planner.project_milestones` — `milestone_id`, `project_id` (FK `projects.projects`), `milestone_no` (`UNIQUE (project_id, milestone_no)`, assigned in the capture function as `max + 1` per project), `milestone_type_id`, `is_as_built` **copied from the type at capture** (so the header never depends on the registry for its own semantics), `title varchar(100)`, `notes text`, `captured_datetime` (the history watermark for decision 2), `captured_user`, the project's own state — `project_status_id`, `project_status_name`, `project_geom geometry(Geometry, 27700)`, `premises_count` (`is_valid` count), the `element_count` and `connection_count` — and the unlock record: `unlocked_datetime`, `unlocked_user`, `unlock_reason`. No promotion columns. **Payload** `planner.milestone_elements` — `PRIMARY KEY (milestone_id, element_id)`. The real columns of `planner.elements` as they stand at migration time (`project_id`, `element_kind`, `category_id`, `type_id`, `status_id`, `element_name`, `geom geometry(Geometry, 27700)`, `length_m`, `sld_x`, `sld_y`, `parent_element_id`, `parent_relation`, `parent_position`, `a_end_element_id`, `b_end_element_id`, `ownership`, the audit columns), the captured registry names (`kind_label`, `category_name`, `type_key`, `type_name`, `status_name`), and three `jsonb` columns for what is variable: ``` fields: { "": { "form_id": "...", "label": "...", "value": ... } } routes: [ { "sequence_no", "container_element_id", "container_position", "external_dataset", "external_feature_id", "provenance" } ] ports: [ { "port_group", "port_no", "port_label", "port_state" } ] ``` **When a column is added to `planner.elements`, the same migration adds it here, to `milestone_payload()` and to the compare endpoint's column list** — three places, one change. Soft-deleted elements (`is_deleted`) are **not captured** — they are not part of the plan, and the diff then reports them as removed against an earlier milestone. GiST index on `geom`; btree on `(milestone_id, element_kind)` for the layer filter. `planner.milestone_connections` — `PRIMARY KEY (milestone_id, connection_id)`, with the natural key denormalised: `at_element_id`, `from_element_id`, `from_port_group`, `from_port_no`, `to_element_id`, `to_port_group`, `to_port_no`, `connection_kind`, `notes`. `planner.milestone_splice_schedule` — `milestone_id`, `location_id`, `element_id`, `port_no`, `row jsonb` (the `vw_splice_schedule` row as captured). **Functions** - `planner.milestone_payload(p_project_id)` — set-returning, produces the element rows as above from the live tables. **The one definition** the capture inserts from and the compare endpoint runs for a *live* side, so a milestone and live can never disagree about shape. - `planner.capture_milestone(p_project_id, p_type_id, p_title, p_notes, p_user)` — one transaction: assigns `milestone_no`, inserts the header with the project state, inserts the three payloads from `milestone_payload()`, the live connections and the live splice view, and returns the new `milestone_id`. - `planner.plan_locked_by(p_project_id)` — returns the `milestone_id` of the as-built currently locking the plan (`is_as_built AND unlocked_datetime IS NULL`, latest), or NULL. The single lock definition; the assets plan extends it with the promoted rule. **Views** - `planner.vw_project_milestones` — header joined to type and user names, for the tab. - `planner.vw_milestone_elements` — the payload joined to the header for `milestone_no`, type and `captured_datetime`, `geom` cast `geometry(Geometry, 27700)`. A thin view, since the payload is already flat. **Must stay filterable** — `milestone_id` and `project_id` from real columns, no aggregation. This is the map layer and the QGIS/GeoServer seam. **Indexes on history** for the changed-since count: `elements_history (project_id, history_datetime)` and `element_connections_history (project_id, history_datetime)`, `IF NOT EXISTS`. Routes, ports and EAV history reach a project through `element_id`, which they already index. **Permission:** none in the migration. `planner_approve` is not created; `projects_approve` is reused (decision 10). ### Phase 2 — Admin: the Milestone Types pane A sixth rail item in `?do=admin_planner`, `tab-pmilestones`, after Statuses. List, add, rename, reorder, toggle active, and the *As Built* flag. `admin_load.php` gains `planner_milestone_types_list`; `admin_save.php` gains `planner_milestone_type_save`, `planner_milestone_type_delete`, `planner_milestone_type_toggle_active`, each under `admin_planner` like their siblings. Delete refuses when any milestone uses the type and offers deactivate; the flag control is disabled with the reason once the type is in use. `type_key` follows decision 33 of the planner plan: editable, nothing stores it. **Built and tested 2026-10-05** (ten checks; two fixes during testing — the list mode was missing from `admin_load.php`'s first allowlist, and a disabled button's tooltip never shows, so `deleteButton()` now carries it on a wrapper; the delete confirm became a modal at Dave's request, and the wider `confirm()` sweep is in the backlog). Two departures from the text above: - **No `toggle_active` mode.** The Statuses pane dropped its row toggle on 2026-09-21 in favour of an *Active* checkbox in the modal, written by the save mode, and this pane follows its sibling. Two modes, not three: `planner_milestone_type_save` and `planner_milestone_type_delete`, plus `milestone_types` as a kind in `planner_reorder`. - **The key is not offered for edit**, as the Statuses modal does not offer its own. It is set at creation; nothing stores it, so it could be made editable later without a migration. The *As Built* checkbox is disabled with the reason once the type has milestones, and the save refuses the change with a sentence before the 115 trigger would refuse it with a constraint name. Deleted milestones still count: their header rows remain. ### Phase 3 — The Milestones tab **Endpoints** - `project_milestones_load.php` — two modes, the `project_plan_load.php` shape. `list` returns the milestones, the active types, the lock state (`plan_locked_by` and whether the caller may unlock), and the caller's capabilities (`can_save`, `can_save_as_built`, `can_delete`). `changed` returns the changed-since count for the latest milestone — a `count(DISTINCT element_id)` over a `UNION ALL` of the nine history tables filtered after `captured_datetime` — fired **after render**, the way `wayleave_badge_counts.php` keeps its cost off the editor's load path. **It must not count schematic drags** (found in the prototype, 2026-10-05): decision 9 leaves `sld_x` / `sld_y` editable under a lock, and migration 080's history trigger writes a row for them, so a locked plan would otherwise show edits since the as-built that locks it. Either the count skips `elements_history` rows that differ from their predecessor only in those two columns, or the badge is not shown while the plan is locked. To be settled with the tab's other choices. - `project_milestone_save.php` — `create` (type, title, notes; calls `capture_milestone()`; returns the new row and a `no_changes` flag when the count since the previous milestone is zero, which the client has already warned about before posting), `delete` (refused for as-built), and `unlock` (Phase 4). Every action writes a project journal row through the same insert `project_journal_save.php` uses: *Milestone #4 As Built "Phase 1 handover" saved*. **Gates.** Tab rendered where `projects` is enabled and the caller has `planner` read — a milestone is a view of planner data, and seeing it without editing is the project-manager role, the same reasoning as the Network tab. `create` needs `projects` write plus `planner` read (saving a milestone changes no planner data); an as-built type additionally needs `projects_approve` write; `delete` needs `projects` write. `requireNotDeleted()` on all. **Template and JS.** A nav button in `html_body_projectedit.php` with `data-nav="milestones"` and the badge span, following the Wayleaves tab (the newest, `data-tab-target` shape). Panel: the save form (type, title, notes, Save) above the list, a lock banner when locked, a *Compare* button per row and a *Compare with live* on the latest. The list is the Phase 0 design. Copy follows the UI-copy rule — no mechanics, only what is non-obvious and actionable: *"Nothing has changed since #3. Save anyway?"*, *"Plan locked by As Built #4. An approver can unlock it."* **Built 2026-10-05, awaiting testing.** `project_milestones_load.php`, `project_milestone_save.php`, the nav button, panel and two modals in `html_body_projectedit.php`, a block in `project_edit_v2.js` beside the Wayleaves tab's, styles appended to `project_edit_v2.css`, and `dashboard.css` added to the `projectedit` route for the `.dash-chip` badges. Beyond the text above: - **The changed-since count lives in `global_functions.php`** as `plannerChangedSinceCount()`, because both endpoints call it: the load for the chip, the save for `no_changes`. It skips `elements_history` UPDATE rows whose plan columns match their predecessor's — a fingerprint over every column except `sld_x`, `sld_y` and the audit columns — so a schematic drag under a lock does not read as an edit. That settles the open point from Phase 0 the honest way rather than by hiding the chip while locked. - **The copy says "edits since"**, on a chip on the latest card; the nav badge is the milestone count, hidden at zero (Phase 0 decisions). - **Delete is a soft delete** that removes the payload rows and keeps the header, per migration 115, so the number is never reused. - **The create gate was questioned in testing and kept** (Dave, 2026-10-06): a user with project write, planner read and no approver permission can save a Design or IFC milestone while being unable to edit the plan. That is the project-manager action the plan describes — a milestone is a lifecycle statement about the project, not a planning act — and it is why the tab sits beside status and journal. The consequence to watch in real use is the other way round: a planner with planner write but project read cannot save one. If that turns out to be who presses the button, the gate becomes "project write or planner write", one line in each endpoint and the template. - **A locked plan only warns on a Design or IFC save** — the save-anyway modal, since nothing can have been edited. Whether it should refuse is still an open decision; the endpoint allows it today. - **The compare row is markup only, hidden**, until Phase 5 gives it a page to open. - **Nothing is written for Phase 4 yet**: no Unlock button, no header lock badge, no `unlock` mode. The banner is in place and names the locking milestone. ### Phase 4 — Locking - `requirePlanUnlocked($dbh, $projectId)` in `global_functions.php` — **must** live there, as it is called from several endpoints. Calls `plan_locked_by()`, refuses with the generic `'No Access'`-shaped JSON every other gate emits. - Called after the module gates in `planner_save.php`, `planner_ports_save.php` and `planner_connections_save.php`, and on **every writer of the project boundary** (decision 9, corrected 2026-10-05 — the boundary is on `projects.projects`, not in `planner`, and the first draft named a dead endpoint): - `map_feature_save.php`, in the `projectBoundary` branch — the save the editor actually posts the boundary to (`project_edit_v2.js:5711`, `:7022`). Gate only that branch; the endpoint's other branches write prospector tables unrelated to the plan. - `project_save.php` — `geom` is in `$staticFields`, so a direct POST writes the boundary. Either refuse the save when the POST carries `geom` and the plan is locked, or drop `geom` from the whitelist, since nothing posts it. Decide at build; removing it is the smaller surface. - `map_boundary_update.php` — **no callers since July** (`docs/2026-07-14-Dynamic-Navigation.md`) and no permission gate. Gate it anyway, or retire it in this phase; leaving an ungated boundary writer beside a locked one is the exact hole the lock exists to close. Not in `network_topo_save.php` (decision 9). Confirm `project_auto_route.php` only *returns* a route and writes nothing; if it writes, it gets the check too. **Built 2026-10-06, awaiting testing.** What differs from the text above, and why: - **The editor's read-only state comes from the layers endpoint, not `planner_load.php`.** `planner_load.php` is the per-element attribute load; the thing that already makes a read-only planner user's tools absent is `map_layer_manager.php` forcing `layer_editable` / `allow_create` / `allow_delete` off on planner layers. The editor's layers request now carries the project id, and when `plan_locked_by()` answers, the same three flags go off on the planner layers **and the boundary layer**. The three JS line numbers the plan cited were the schematic's paths, which decision 9 keeps editable, so nothing in the editor flips `plannerAccess.write`. Auto-route's button is created at map init and is disabled from the Milestones response, as it is for a read-only user. - **`project_auto_route.php` writes nothing** — confirmed by reading; no gate. - **`project_save.php` dropped `geom` from its whitelist** rather than gating it; nothing posts it. `map_boundary_update.php`, dead since July and ungated, now has the projects write gate it always lacked plus the lock; retiring it is a separate decision. - **Saving an as-built, and unlocking, reload the page.** The layers were built from the server's answer at map init and the editor cannot cheaply rebuild them in place; a reload brings the tools, the badge, the banner and the cards back right together. - **Unlock is `mode=unlock` on `project_milestone_save.php`**: approver write, a required reason, only the milestone currently locking, journalled as *Milestone #4 As Built "…" unlocked: reason*. The promoted-milestone refusal is a comment pointing at the assets plan, which will put it in `plan_locked_by()` rather than here. - `planner_load.php` returns `plan_locked_by`; `project_edit_v2.js` treats it as `plannerAccess.write === false` through the existing read-only paths (`:664`, `:7459`, `:7510`), so the drawing tools and the Save in the attribute panel disappear by the code that already hides them for read-only users. Header badge beside the status badge. - Unlock: a modal with a required reason, posting `unlock` to `project_milestone_save.php`, gated `requireModuleAccess($pdo, 'projects', 'projects_approve', 'write')`. Writes `unlocked_*` on the milestone and a journal row carrying the reason. Refused when the milestone has been promoted — that column does not exist yet, so the check is a comment pointing at the assets plan until it does. ### Phase 5 — Compare **Endpoint** `project_milestone_compare.php` — `a` and `b`, each a `milestone_id` or `live`; both must belong to `project_id`. Builds two row sets (payload tables for a milestone, `milestone_payload()` for live) and returns per element: the flags `added`, `removed`, `moved` (with `ST_HausdorffDistance` in metres), `attributes`, `relationships`; and which keys differ, so the table can say *status: Planned → Built* and *route: duct 112 → duct 119*. Connections by natural key (decision 12). Splice schedule rows by `(location_id, element_id, port_no)`. Element geometries for changed rows are returned as GeoJSON in 3857 so the page can draw emphasis without a third layer request. Gated `projects` read plus `planner` read. **Route** `projectmilestones` in `routes.php`, layout 2 like `projectplan`: `html_body_project_milestones.php`, `js/project_milestones.js`, `css/project_milestones.css`. Query `project_id`, `a`, `b`. An OpenLayers map with the instance basemap and two vector layers through `map_get_v2.php` — a milestone side as `geotable=planner.vw_milestone_elements&filter={"milestone_id":N}`, a live side as the existing `planner.vw_elements`. A Tabulator table of differences, filtered by flag, row click zooms, *Show unchanged* toggle. Download is Tabulator's own `download('xlsx')` over the SheetJS `index.php` already loads. Side selectors to change A or B without leaving the page. Layout as chosen in Phase 0. **The history log** (decision 2, second half): a third tab on the page listing the history rows between the two captured datetimes — who, when, which element, which table — read from the nine history tables through one `UNION ALL`. Only offered when both sides are milestones. **Built 2026-10-07, awaiting testing.** `project_milestone_compare.php` (`compare` and `history` modes), the `projectmilestones` route, `html_body_project_milestones.php`, `js/project_milestones.js`, `css/project_milestones.css`, and the compare row on the Milestones tab. Beyond the text above: - **The changed run is one more column per row**, as Phase 0 decided: `ST_Difference` of B against a 0.5 m buffer of A, and the reverse for the ghost, returned as GeoJSON in 3857 beside the whole geometries. The page's `Emphasis` control chooses runs or whole element; `Unchanged` faint or hidden. 0.5 m is a constant in the endpoint until real data says otherwise. A moved line that also changed attributes or relationships is drawn whole, since the run would hide the element that is the change. - **Four tabs, not three**: Differences, Connections, Splicing and History. The splice schedule rows are diffed on migration 115's key and reported like connections; the plan listed them in the endpoint and nowhere on the page. - **The unchanged network is the map layer and the emphasis is the response.** The layer request carries `where={"project_id":N}` because `map_get_v2.php`'s company check needs a record key and the view has no `company_id`, with `filter={"milestone_id":N}` narrowing to the milestone — decision 17's shape exactly. - **The basemap is the editor's own**: the `base` layer the `projectedit` page context marks visible, built the way the editor builds it, so the colours are judged against the same ground. - **Download is `download('xlsx')`** with the Detail column flattened to one line per row. - **Sides are changed in place** and written back to the URL, so a comparison can be shared as a link. Found in testing (2026-10-07) and built the same day: `Unchanged` gained **Planner styles**, the registry styles keyed on `type_id`, beside faint and hidden; the map got the editor's own **`#sidebarLayers` and `#mapButtons`** for base and reference layers — the first cut had a panel of its own, rightly rejected as not house style; a line that **moved and changed attributes** shows the changed run at full weight and the dash-dot casing along its whole length, so both facts read; the flag chips filter the **map as well as the table**; the page **fills the viewport** rather than scrolling, sized from what is left under the chrome; the view **fits once per comparison**, never on an Emphasis or Unchanged change; a click on empty map or Escape **clears the halo**; and choosing the same side twice says so. ### Phase 6 — Documentation - CLAUDE.md: a *Project Milestones* section — the snapshot-not-reconstruct rule and why, history used forwards, columns-where-fixed / jsonb-where-variable and the three places a new `elements` column must reach, the lock function as the single definition, the natural-key rule for connections, and the two views' filter rule. - `docs/improvement-opportunities.md`: tick the milestones item, pointing here; update the assets item's dependency note to name the lock-permanent-after-promotion rule and the `plan_locked_by()` seam. - `docs/2026-09-09-planner-module-update.md` *Seams left for assets*: add the milestone payload as the promotion source. - Memory: nothing — everything non-obvious is in this doc and CLAUDE.md. **Done 2026-10-07.** CLAUDE.md has a *Project Milestones* section between *Project Delete & Restore* and *Stocklist Import*, the `projectmilestones` route in its routing table, the pseudo-module paragraph naming the two new uses of `projects_approve`, and this doc's entry rewritten as built. The backlog's milestones item is ticked with the three open questions named, and its assets item carries the lock-permanent-after-promotion rule and the `plan_locked_by()` seam. The planner plan's *Seams left for assets* names the milestone payload as the promotion source. No memory file: nothing here is non-obvious beyond what the two documents record. **`docs/table-dependency-chains.md` started here** (Dave, 2026-10-07), prompted by the three-places rule: one section per table whose change reaches further than the table, the things that must change with it listed in order. Seeded with this build's chains — `planner.elements` has seven readers now, not three — and with the chains CLAUDE.md already carried for `map_layers`, `projects.projects`, `project_premises` and the wayleave coverage views, which CLAUDE.md now points at rather than repeats. **Open after the build**, for whoever picks the next piece up: - The create gate — project write plus planner read — if planners rather than project managers turn out to press the button (Phase 3 note). - Whether a locked plan should refuse a Design or IFC save rather than warn (Phase 3 note). - The 0.5 m changed-run tolerance, once real snapping data has been compared. - `map_boundary_update.php` is gated but dead; retiring it is its own decision. - The History tab shows blank usernames for rows written before actor attribution existed; nothing to fix (Dave, 2026-10-07). ## Seams left for assets - **`planner.milestone_elements` of an `is_as_built` milestone is the promotion source.** Only rows with `attrs->'element'->>'ownership' = 'owned'` promote (planner decision 7). - **Promotion is idempotent on `element_id`**: a second as-built updates the asset linked to that element rather than creating another. - **`plan_locked_by()` gains the promoted rule**: a promoted milestone cannot be unlocked. - **`project_milestones` gains `promoted_datetime` / `promoted_user`** in the assets migration, not here. - **`vw_milestone_elements` is the QGIS / GeoServer read** for an as-built drawing, filterable by `milestone_id`, flat columns for the common attributes. ## Testing checklist 1. Migration 113 applies; `SELECT * FROM planner.milestone_types` shows three rows with only As Built flagged. 2. Admin → Planner → Milestone Types: add a type, rename it, reorder, deactivate; the As Built flag can be set on a type with no milestones. 3. Save a Design milestone on a project with planner elements; it appears as #1 with the caller's name and time, and a journal row is written. 4. `SELECT count(*) FROM planner.milestone_elements WHERE milestone_id = <#1>` equals the project's live element count excluding deleted ones. 5. Save a second milestone with no changes; the warning appears, and saving anyway creates #2. 6. Move an element, then reload the tab; the changed-since badge shows 1. 7. Reroute a cable through a different duct **without moving it**; the badge shows 1. 8. Delete #1 (Design); it goes, journalled. Attempt to delete an as-built; refused. 9. Attempt to change the As Built flag on a type that has a milestone; the control is disabled with the reason. 10. Save an As Built without approver permission; refused. With it; saved, and the plan shows locked in the header and the tab. 11. While locked: the drawing tools are absent, the attribute panel has no Save, a direct POST to `planner_save.php` is refused, as are the ports, connections and boundary saves. 12. While locked: change the project status, add an attachment, write a journal note; all succeed. Drag a schematic node; it saves. 13. Unlock without approver permission; refused. With it and no reason; refused. With a reason; unlocked, journalled with the reason, tools return. 14. Compare #1 against #3 with the moved element and the rerouted cable: two rows, one flagged *moved* with a distance, one flagged *relationships* with the duct ids named, and both emphasised on the map. 15. Compare #3 against live after adding one element and soft-deleting another: one *added*, one *removed*. 16. Break a splice and remake it identically; compare shows no connection change. Change its kind; compare shows one *changed* connection. 17. Download the difference table as xlsx; it carries the applied filter only. 18. Compare a milestone of project A with one of project B by editing the URL; refused. 19. Open the compare page with `planner` read only; it loads. Without `planner` read; refused. 20. Soft-delete a project with milestones, then restore it; the milestones are intact. 21. Publish `planner.vw_milestone_elements` in GeoServer with a CQL filter on `milestone_id`; it renders (configuration only — confirms the seam, not a code test). ## Risks - **Payload volume on large projects.** A few hundred rows per milestone is the expectation; a project of ten thousand elements with weekly milestones is not, but nothing stops it. Measure the first real as-built before deciding whether anything needs doing. - **Exact geometry comparison will over-report.** Snapping moves vertices (planner migration 104), so a save that touches a neighbouring cable can register as *moved* by millimetres. Decision 11 accepts this for v1 and returns the distance so the tolerance is tuned on real data; the table should sort by distance so the noise sinks. - **The lock is only as complete as the endpoint list.** Any new planner write endpoint must call `requirePlanUnlocked()`. Record it in CLAUDE.md beside the module-gate pattern. The list was already wrong once, before a line was built: the plan named the dead boundary endpoint and missed the live one, found by grepping for writers of `projects.projects.geom` rather than by reading the editor (2026-10-05). **Derive the list from the column's writers, not from the JS** — and add `geom` on `projects.projects` to the CLAUDE.md note, since the next person will look in `planner` for the plan and not find the boundary there. - **A column added to `planner.elements` must reach three places** — the payload table, `milestone_payload()` and the compare endpoint's column list — or the milestone silently stops capturing it. One migration, so it is a review item rather than a hazard, but it needs to be in CLAUDE.md where the next planner change will see it. - **History-based counts include changes later reverted.** An element moved and moved back counts as changed since the milestone, while the diff shows nothing. The badge says *activity*, the diff says *difference*; the tab copy must not call the badge a difference.