# Project Premises — UPRN-Level Table - **Date:** 2026-08-17 - **Status:** Complete - **Status Date:** 2026-08-28 - **Phases:** 6 - **Phases Complete:** 6 - **Notes:** Materialises the project→UPRN relationship that nine queries currently derive with a live spatial join, and adds the parent/child *visible vs valid* distinction that stops a premise being counted twice in a project hierarchy. Two independent flags on a premise row: hierarchy role (Parent/Child) and overlap (more than one claiming project). Hierarchy duplication is resolved silently; unrelated overlap is counted in both and flagged, never tie-broken — so **nothing here reads a project status and nothing waits on the status-semantics work.** Of the approval scaffolding only `state` is kept, with `pending` and `rejected` already allowed by its CHECK; **the approval flow and its tables are not built here.** Wayleave↔project UPRN matching is also out of scope — a later feature of its own, and this plan is the prerequisite it has been waiting on. ## Built, and what changed against this spec Completed 2026-08-28. Migrations 056-058 applied, all six phases tested on the dev server. Test 39 (new `basedata.abp` rows picked up by the resync) is **deferred to the next genuine ABP sync** rather than run against synthetic rows on a live database (Dave, 2026-08-28). Sizing, measured rather than estimated: 871 projects, 862 of them with premises inside their boundary, ~50k premise rows, 41,832 distinct UPRNs. **7,823 UPRNs - 19% - are claimed by more than one project.** That is the error bar Decision 3 accepts by design, and it is much larger than expected, so the flags will not be a rare sight. The project list went to 529ms. Five places where the build departed from what is written below, all deliberate: 1. **The validity re-flag is an anti-join, not the `NOT EXISTS` expression in Phase 2.** Same rule, but a sublink in a `SET` target list is never pulled up into an anti-join, so it stays a per-row SubPlan re-scanning the recursive `vw_project_ancestors` once per row - for the ~1M rows the seed touches. 2. **Filtered consumers do not join `vw_premise_claimants`.** Migration 048's lesson applies to it exactly: a whole-table aggregate cannot have `WHERE project_id = ?` pushed into it. `vw_project_premises`, `stocklist_load.php` and `stocklist_premises_geom` all ask the same question correlated or via `LATERAL`. The view is still created, and is still right for estate-wide questions, but **nothing currently reads it** - logged in the backlog. 3. **`vw_opportunity_list` takes `DISTINCT` on mdu/sdu/other as well as the total.** The plan names only `total_premises`, but `mdu_premises` is the divisor in `total_mtr_per_mdu_prem` - the money figure the double count distorts - so de-duplicating the total alone would have fixed the total and left the metric exactly as wrong. 4. **`project_load.php` gained a query the plan did not specify** (`data_contested_rivals`). Phase 5 requires the alert to *name* the rival projects and nothing returned them. 5. **The premise chip keys on `is_valid`, not `hierarchy_role`.** Two faults found in testing, both from naming the viewing project's role rather than where the premise is counted: Child/Parent read backwards in a three-deep chain, and `hierarchy_role` is only non-null where an *ancestor* holds the premise, so the top project of a chain went unchipped for premises it counts while the one below it did not. `hierarchy_role` is now read by nothing - logged in the backlog beside `vw_premise_claimants`. 6. **Phase 5 reached three project lists, not one.** The spec named `project_list.js` and said to check `stocklist_list.js` / `opportunity_list.js`. Working to that list rather than to the rule missed the two project lists inside other editors - `account_edit_v2.js` (`#account-project-list-table`) and `stocklist_edit.js` (`#stocklist-project-list-table`) - both of which read `vw_projects_list` and already showed premise counts (Dave, 2026-08-28). All three now carry the split. The two *stocklist* lists were correctly left alone: `vw_stocklists_list` counts stocklist premises through a UPRN equality join and has nothing to do with projects. `opportunity_list.js` was also left alone - its `total_premises` is already `count(DISTINCT uprn)`, which absorbs hierarchy and overlap. **One bug found after sign-off, fixed by migration 059** (Dave, 2026-08-28). The refresh re-flagged `is_valid` for the UPRNs held by *the project being refreshed*. That is right for a geometry change and wrong for a parent change, because a project's parent decides the lineage of its whole subtree. Found building a chain bottom up, which is the ordinary way to do it: three standalone projects, then set 6's parent to 5, then set 5's parent to 4. The second step fires the trigger for 5, so 6's UPRNs are out of scope — and a premise inside 4 and 6 but not 5 was never recomputed. It kept `is_valid = true` at 4 from while 4 and 6 were unrelated, while 6 claimed it too, and two valid claimants read as unrelated overlap. Direct children never showed it, because setting each of their parents re-flagged their own UPRNs. The affected set is now the project **and every descendant** — `vw_project_ancestors` carries each project at depth 0, so `ancestor_id = p_project_id` is exactly that. Note this is not about *moving* a project: the trigger fires on `NULL -> value` too, and that first-time case is the one that bit. The spec has the same hole — Phase 2's "the UPRNs the affected projects already hold" is the project, not its subtree. Also, unrelated to the model: **UDPRN was removed from both premise lists** (Dave, 2026-08-28). It predated this work in all four places, and the column survives in `vw_project_premises` because 058 is applied and `CREATE OR REPLACE VIEW` cannot drop a column - a candidate for the same 059 as the two other now-unread columns. **The dependant breakdown is not a panel, and is not a relationship** (Dave, 2026-08-28). Phase 5 below puts it "near the existing hierarchy panel", and it was built as a card under it on Main Details — listing the same descendant projects the hierarchy panel had just listed, one line lower. It now renders *inside* that panel, via an optional `nodeNote` callback on `renderHierarchyPanel()` (opt-in, so accounts and stocklists are untouched). The number also changed meaning. Phase 4's query returned *premises of mine that this descendant counts*, which is a fact about a pair of projects: it could only ever annotate a descendant, and only where the two boundaries overlapped. So the project being viewed showed nothing, parents showed nothing, and a child whose boundary sat outside its parent's showed nothing either — the counts appeared on some rows and not others for reasons a reader could not see. It is now **each project's own valid-premise count**, a property of the project, so every node the panel draws carries one. The query returns the whole lineage: the project, every ancestor and every descendant. **The header carries valid and contested, but not dependant** (Dave, 2026-08-28). Phase 5 says the summary gains all three; the middle one was built and then removed, because it cannot be labelled honestly in one word. `dependant_premises` counts premises where this boundary and a DESCENDANT'S boundary coincide, so a project whose child sits outside it reads 0 while genuinely having a sub-project that delivers — "sub-projects: 0" is true of the premises and false of the hierarchy. Renaming it does not fix that, and the figure a reader actually wants there — what my descendants deliver, wherever those premises are — is a different number that is not a subset of the total, so it cannot sit in the same bracket. The header reads `Total Premises: N (This Project Premises: N)`, then MDU Premises and Cable Meters, plus the overlap badge — both bracketed figures are boundary-scoped, so the bracket is a genuine subset. What a descendant counts survives where it can be acted on: the premise rows, the project list, and the hierarchy panel's per-project counts. **Phase 5 naming, decided with Dave 2026-08-28** and not as drafted below: `is_valid` reads as **This Project**, its inverse as **Sub-projects**, and contested as **Overlap**. The overlap figure is a button into the Premises tab rather than a statistic in the header line, and the full alert lives on that tab beside the flagged rows, with Main Details carrying one line and a way through. ## Plan Phases 1. ✅ Table — `projects.project_premises` archived and rebuilt (migration 056) 2. ✅ Resolution function, triggers and seed (migration 057) + the resync utility 3. ✅ Read view and the four database views (migration 058) 4. ✅ Endpoint queries — `project_load`, `stocklist_load`, `opportunity_manage`, `opportunity_process` 5. ✅ Surfacing the two flags — hierarchy role and overlap — in the project list, project editor and stocklist editor 6. ✅ Documentation ## Problem **Every premise figure in the application is a live spatial join.** `basedata.abp` is GiST-indexed on `geom`, but the join is `ST_Intersects(projects.geom, abp.geom)` against a national address dataset, and in the worst case — `projects.vw_projects_list` — it runs for **every project at once with no filter at all**, on every project-list page load: ```sql FROM projects.projects p_1 LEFT JOIN basedata.abp ON st_intersects(p_1.geom, abp.geom) ``` Nine queries do this. Four are database views, five are endpoint queries: | Where | What it computes | |---|---| | `projects.vw_projects_list` (039) | premise counts for **every** project, unfiltered | | `opportunity.vw_opportunity_list` (039) | opportunity premise totals via linked projects | | `opportunity.vw_opportunity_projects_output` (025) | per-project premise splits inside an opportunity | | `stocklists.stocklist_premises_geom` (baseline) | premise→project attribution for the map | | `project_load.php:243` | project editor premise list | | `project_load.php:385` | project editor premise counts | | `stocklist_load.php:244` | premise→project on the stocklist premise list | | `stocklist_load.php:380` | projects relevant to a stocklist | | `stocklist_load.php:575` | opportunities relevant to a stocklist | | `opportunity_manage.php:352, :427` | opportunity summary + per-project splits | | `opportunity_process.php:314, :387` | same two, on the prospecting run (live `EventSource`) | ### Double counting in a hierarchy Because containment is computed per project independently, a premise inside a parent and three descendants is counted **four times** by anything that sums across projects, and appears as **four rows** in anything that lists premises with their project: ``` stocklist_load.php:244 left join projects.projects p on st_intersects(p.geom, a.geom) ``` One premise, four rows on the stocklist premise list, today. `opportunity_manage.php:352` is the same fault with money attached: an opportunity linked to both a parent and its child sums that premise twice, so `total_mtr_per_mdu_prem` — metres of route per premise — is understated by however much duplication the link set contains. ### What a parent project needs to say A parent project might deliver network to an estate and **no premises directly**; its children deliver the premises. Two things must both be true: - The parent must **see** those premises, or it looks like a project that delivers nothing and someone challenges it. - The parent must not **count** them, or the portfolio total delivers the same premise several times and someone challenges that instead. So containment is not one fact but two: *visible* (this boundary contains the premise) and *valid* (this project is the lowest-level project that contains it). Both are needed, on the same row. ### Why not derive it Recorded decision, `docs/improvement-opportunities.md:655-672` (2026-07-18): project UPRNs are *always* derived from the boundary geom, but **persisted, not computed per load**. This plan implements that decision, with the visible/valid split and the approval-ready columns added on top. `projects.project_premises (uprn bigint, project_id integer)` already exists in the baseline (`sql/netplanner_schema_20260706.sql:4822`) with no primary key, no index, no foreign key and **no PHP reference anywhere**. It is the table the old UPRN matching abandoned half-populated — and the reason that matching was reverted: > `www/fn/wayleave_projects_load.php:4-7` — *"the original UPRN-based overlap was replaced > because `projects.project_premises` isn't populated for all projects in this deployment. > Boundary-based spatial overlap ... is the interim signal"* Nothing reads it, so it is dropped and recreated rather than migrated around. The name is kept: it is already the domain word ("premise lists"), and keeping it makes the existing backlog references true rather than stale. ## Decisions Taken with Dave, 2026-08-17, during the spec interview. 1. **Store what the trigger can maintain; derive what it cannot.** Only one thing is stored: | Field | Where | Meaning | |---|---|---| | `is_valid` | **stored column** | this project is the lowest level in its own lineage that contains the UPRN — structural, and both inputs (boundary, parent) fire the trigger | | `claimant_count` | **derived** | how many projects validly claim this UPRN. `1` is normal; `> 1` is the red flag | | hierarchy role | **derived, per viewing project** | `Child` (counted here) or `Parent` (counted below) | Nothing about project *status* is read anywhere, which is deliberate — see **Sequencing**. Storing `is_valid` is a choice of read speed over write simplicity, and Phase 2 spells out what it obliges: the trigger fires on `parent_project_id` change as well as `geom`, and a refresh re-flags **other projects'** rows for the affected UPRNs, because a child's boundary edit changes its parent's flags. 2. **Validity resolves within a lineage; unrelated overlap is flagged, not hidden.** A row is valid when no *descendant* project also contains the UPRN. Where several valid rows remain for one UPRN they are all kept and all reported as contested. Symmetric, so it needs no "is this project in a hierarchy" test and it has an answer when two unrelated hierarchies both contain the premise — which the asymmetric rule does not. **The hierarchy is arbitrary in both directions.** Every worked example in this document uses a parent and three children because that is the shape the requirement was written in; nothing in the design is bounded by it. `vw_project_ancestors` recurses to any depth and the rule tests `anc.depth > 0` — *any* strictly-lower descendant — so it holds for a ten-deep chain and for a parent with fifty children equally. **Siblings are the case the shape does not settle** (Dave, 2026-08-18). Read as a nested chain P→C1→C2→C3, only C3 is valid, which is what the original requirement wrote down. Read as three siblings under P, none is a descendant of another, so **all three are valid**, contested against each other, and P invalid. That is deliberate: sibling overlap is just overlap that happens to share a parent, and two build phases both containing a premise is almost certainly a boundary error. Pushing validity up to P instead would make a parent accountable for a premise no child planned to deliver — rejected. 3. **De-duplicate what is intended; flag what is a mistake** (Dave, 2026-08-25). This is the principle that replaced an earlier `is_primary` tie-break, and it is why the two cases get different treatment rather than one rule: - **A parent containing its child is by design.** Resolve it silently — the lowest project in the lineage counts the premise, everything above sees it and does not. - **Two unrelated projects containing the same premise is an error.** Do *not* resolve it. **Both count the premise**, and both are flagged. Picking a winner would hide a boundary or hierarchy mistake behind an arbitrary answer. An earlier draft resolved the second case too, ranking claimants by project status and then by `project_id`. That is gone. It bought a clean estate-wide sum at the price of a dependency on status semantics that do not exist yet, an arbitrary winner the user had no lever to change, and a silently-hidden data error. **Counting in both and flagging loudly is simpler, has no forward dependency, and pushes the fix to a human** — which is the only thing that actually corrects the underlying boundary. The consequence to be honest about: **there is no single column that sums cleanly estate-wide.** `sum(valid_premises)` over-counts by exactly the contested premises, and `sum(contested_premises)` is the size of that error. That is a reported error bar rather than a hidden one, and it goes to zero as overlaps get fixed. 4. **Two flags on a premise row, not one.** They answer different questions and either can appear without the other: - **Hierarchy role** — `Child` or `Parent`, naming *the viewing project's* role for that premise. Only shown where a lineage relationship exists for that UPRN. `Child` means counted here; `Parent` means counted by a descendant. A premise contained by only one project carries no role flag at all. - **Overlap** — raised when `claimant_count > 1`, i.e. more than one project validly claims the premise after hierarchy resolution. One project is fine and unflagged; two is the problem (Dave, 2026-08-25). **Severity depends on the viewing project, not the pair:** a project that sits in a hierarchy shows **amber**, a standalone project shows **red**. Not a judgement about which is more wrong — it marks where the cheapest fix lives. Giving the standalone project a `parent_project_id` resolves the overlap through the hierarchy rule immediately, so red is pointing at the project that can be fixed by setting one field. 5. **Nothing is deleted.** A premise leaving a boundary becomes `state = 'removed'`, a tombstone. Wayleave's reason (`db/042_wayleave_coverage_tables.sql:9-19`) is that a tombstone stops a decided change being re-raised and gives a reversal something to restore. Projects get a second benefit: because projects have exactly **one** source and it is materialised, the future pending queue is `WHERE state = 'pending'` — a table read, with no diff view and no spatial scan. Wayleave cannot do that; it has four sources and must re-resolve each one live (`vw_wayleave_pending`). 6. **`total_premises` keeps its current meaning** (everything inside the boundary), with `valid_premises`, `dependant_premises` and `contested_premises` added alongside. No screen changes number on deploy except the two documented in **Risks**. 7. **Only `state` is kept from the approval scaffolding** (Dave, 2026-08-25). `approval_id`, `decided_user`, `decided_datetime` and the `projects.project_approvals` table all move to the approval plan. `state` stays because the tombstone earns its place today, not because approval will want it: without it, a boundary shrunk past a premise and regrown loses when that premise first arrived, and nothing can answer *why did this project's count drop last month*. Everything else would be an always-NULL column in a live client database. 8. **The refresh runs synchronously inside the boundary save**, in the same transaction as the `UPDATE`, with a resync routine as a backstop for boundaries written by manual SQL. 9. **One shared function** — `projects.refresh_project_premises()` — called by the trigger, by the seed and by the resync, so "what is inside a boundary" and "what is valid" have one definition that cannot drift between three copies. 10. **Out of scope:** the approval flow; wayleave↔project UPRN matching (Dave has plans to rebuild the matching both ways once premise logging exists); the accounts premise list that `account_load.php:217` has commented out. ### Sequencing: this plan has no dependency on the status work An earlier draft of this plan resolved unrelated overlap by ranking claimants on project status, which made it depend on semantics that do not exist yet — statuses today carry no meaning beyond *retired from selection* (`project_status_active`, migration 022) and two dashboard settings (`public.dashboard_config` role `complete_status`, and the flat `public.dashboard_inactive_statuses` list). **Decision 3 removed that dependency rather than deferring it** (Dave, 2026-08-25). Nothing in this plan reads a project status. The richer status model that is still wanted — *active / complete / stopped / inactive*, where *stopped* separates a cancellation from a pause and keeps its reason for reporting — is now an independent piece of work, tracked in `docs/improvement-opportunities.md`, and it belongs in the **Statuses** area of Module Management rather than the Dashboard area, because the meaning belongs to the status. Either can ship first. There is an argument for this one going first anyway: it makes a mis-set status *visible*, and a richer status model with nothing reading it is configuration nobody has a reason to get right. ### `ST_Intersects`, not `ST_Within` Wayleave coverage uses `ST_Within(abp.geom, polygon)`. This plan uses `ST_Intersects` because that is what all nine existing consumers use, and for a *point* the two differ only when the point lies exactly on the boundary ring — `ST_Intersects` counts it, `ST_Within` does not. Matching today's numbers exactly is worth more than cross-module symmetry, because the whole value of this change depends on nobody having to ask why a count moved. Noted in the migration comment so the asymmetry is deliberate on the record. `basedata.abp` gets no filter (no `valid_address_flag`, no classification test) for the same reason: the current queries have none. Rows with `geom IS NULL` — which `sql/abp_sync.sql` can produce when coordinates are missing — can never be inside a boundary and need no special handling. ## Build ### Phase 1 — The table (migration `056_project_premises_table.sql`) Follows 042's pattern: the table only, nothing populated, nothing dropped that anything reads. The migration is inert until 057 runs, so it can be applied without taking the module down — which matters with 600 live projects. **Guards first.** Abort if `projects.project_premises` is missing (it should exist from the baseline) or if `projects.project_premises_migrated_20260825` already exists, which would mean 056 has run before. **Archive, do not drop.** The module is in active client use with ~600 live projects, so `ALTER TABLE projects.project_premises RENAME TO project_premises_migrated_20260825` first, and `RAISE NOTICE` the row count preserved. Nothing functional is lost either way — every premise is *reproduced* from the boundary by 057's seed, not migrated, and no PHP reads the old table — but a dated archive costs nothing and gives a client whose numbers look wrong something to compare against. This is the restore-point pattern the `prospector.*` tables already set (`prospector.*_migrated_20260804`, CLAUDE.md). Drop it in a later migration once the numbers are accepted. **Create `projects.project_premises`:** | Column | Type | Purpose | |---|---|---| | `id` | `bigint` identity | PK | | `project_id` | `integer NOT NULL` | FK → `projects.projects` `ON DELETE CASCADE` | | `uprn` | `bigint NOT NULL` | the premise | | `source` | `varchar(20) NOT NULL DEFAULT 'boundary'` | only value there is; exists so a future second source is an INSERT and not a table rebuild, and so the unique key already has the right shape | | `state` | `varchar(10) NOT NULL DEFAULT 'approved'` | `pending` / `approved` / `rejected` / `removed` | | `is_valid` | `boolean NOT NULL DEFAULT true` | this project is the lowest-level one containing the UPRN — the **only** flag stored, because it is the only one the trigger can maintain | | `proposed_user` | `integer` | who saved the boundary that brought the premise in | | `proposed_datetime` | `timestamp NOT NULL DEFAULT now()` | when the premise first entered this boundary — **survives a removal and return** | | `state_datetime` | `timestamp NOT NULL DEFAULT now()` | when the row last changed state | Constraints and indexes: - `UNIQUE (project_id, uprn, source)` — `source` is `NOT NULL` precisely so this needs no `COALESCE`; a nullable column here would let two identical boundary claims coexist, because NULLs are distinct in a unique constraint (042's lesson, restated). - `CHECK (state IN ('pending','approved','rejected','removed'))` — all four allowed from day one; **this plan only ever writes `approved` and `removed`.** - `CHECK (source IN ('boundary'))` — one value, and widening it later is an explicit migration rather than an accident. - `(project_id, state)` — the hot path: premise list and counts for one project. - `(uprn, state)` — the reverse direction: which projects contain this UPRN. Used by the stocklist queries, by the validity re-flag, **and by `vw_premise_claimants` in Phase 3**, which groups by `uprn`. Comment the table with the model in one paragraph, as 042 does, and comment `state`, `is_valid` and `proposed_datetime` individually. `is_valid`'s comment is the one that earns its keep: say that it is the **only** flag stored, that `claimant_count` and `hierarchy_role` are derived in `projects.vw_project_premises` / `vw_premise_claimants`, and that **counting this table without `is_valid` double counts every hierarchy.** **No `projects.project_approvals` table, and no approval columns beyond `state`** (Dave, 2026-08-25). An earlier draft created the approvals table empty so `approval_id` could be a real foreign key from day one. Deferred instead, for the same reason the status dependency was cut: four always-NULL columns and an empty table would sit in a live client database for months, and the approval plan can add them in its own migration once it knows what a decision actually needs to record. Two things are carried forward so that migration is cheap rather than a rebuild: - **`state`'s CHECK already allows `pending` and `rejected`**, so widening the behaviour needs no constraint change — only new code writing values the column already accepts. - **The lessons stay written down here** rather than being rediscovered: a decision must record its own membership (`uprn_added_list` / `uprn_removed_list`, migration 054's correction); projects need **no `source` / `source_ref`** because they have one source, unlike `wayleave.wayleave_approvals`; and there should be **no boundary snapshot column**, because `projects.projects_history` already captures `geom` on every change (migration 017) and a second copy can disagree with the first. ### Phase 2 — Resolution function, triggers and seed (migration `057_project_premises_refresh.sql`) **`projects.vw_project_ancestors`** — the lineage relation the validity rule needs, so the descendant test is a join and not a recursive CTE per premise: ```sql CREATE VIEW projects.vw_project_ancestors AS WITH RECURSIVE a AS ( SELECT project_id, project_id AS ancestor_id, 0 AS depth, ARRAY[project_id] AS path FROM projects.projects UNION ALL SELECT a.project_id, p.parent_project_id, a.depth + 1, a.path || p.parent_project_id FROM a JOIN projects.projects p ON p.project_id = a.ancestor_id WHERE p.parent_project_id IS NOT NULL AND NOT (p.parent_project_id = ANY (a.path)) ) SELECT project_id, ancestor_id, depth FROM a; ``` The `path` array is a cycle guard, matching the hierarchy CTEs already in `project_load.php:287-305` and `:334-351` — the data can contain a self-parent or A→B→A, and `project_save.php:62-66` only blocks the self-parent case at save time. Row count is projects × average depth, so this view is small. **`projects.refresh_project_premises(p_project_id integer DEFAULT NULL, p_rescan boolean DEFAULT true)`** — `RETURNS TABLE (project_id integer, uprn_added integer, uprn_removed integer)`. `NULL` means every project. `p_rescan = false` skips the spatial scan and only re-flags, which is the cheap path a `parent_project_id`-only change takes. Two steps, in order. There is deliberately **no third pass**: an earlier draft stored an overlap count here, which is gone because contention is a property of a UPRN across every project rather than of one project's rows — it is `claimant_count`, grouped per UPRN in `vw_premise_claimants` and recomputed on read (Decision 1). 1. **Resolve and diff containment** for each project in scope, `source = 'boundary'`: | Situation | Action | |---|---| | inside, no row | `INSERT` `state='approved'`, `proposed_user`, `proposed_datetime = now()` | | inside, `state='removed'` | `UPDATE` back to `'approved'`, `state_datetime = now()` — **`proposed_datetime` untouched**, so "when did this premise first enter this boundary" survives a shrink-and-regrow | | inside, `state='approved'` | leave alone entirely — no timestamp churn on a boundary edit that did not affect it | | not inside, `state <> 'removed'` | `UPDATE state='removed'`, `state_datetime = now()` | Collect every UPRN whose row was inserted or changed state into a local array — that, plus the UPRNs the affected projects already hold, is the set whose validity can have moved. 2. **Re-flag `is_valid`** across **all** projects for the affected UPRN set — not just the project being refreshed. A row is valid when no strictly-lower descendant has a live row for the same UPRN: ```sql is_valid = NOT EXISTS ( SELECT 1 FROM projects.project_premises d JOIN projects.vw_project_ancestors anc ON anc.project_id = d.project_id -- d belongs to a descendant... AND anc.ancestor_id = pp.project_id -- ...of pp's project AND anc.depth > 0 -- strictly below WHERE d.uprn = pp.uprn AND d.state <> 'removed') ``` `anc.depth > 0` is the whole rule and it is depth-agnostic on purpose — it asks *is anything strictly below me claiming this*, not *is my immediate child*. Two consequences an implementer should expect rather than treat as a bug: a ten-deep chain resolves to the deepest project with no extra work, and **siblings all stay valid**, because a sibling is not a descendant. Decision 2 covers why that is wanted. **Do not add a tie-break here** — there is no single winner to pick. Unrelated overlap is counted in both projects and flagged, not resolved (Decision 3). `RAISE NOTICE` per project with the added/removed counts. `RETURNS TABLE` so the resync utility can report per project and the trigger can ignore the result. **Triggers on `projects.projects`:** - `AFTER INSERT` — refresh when `NEW.geom IS NOT NULL`. Covers `project_create_boundary.php:69`, which inserts a project with its geometry in one statement. - `AFTER UPDATE ... WHEN (OLD.geom IS DISTINCT FROM NEW.geom OR OLD.parent_project_id IS DISTINCT FROM NEW.parent_project_id)` — the `geom` half is the recorded decision at `improvement-opportunities.md:666`: project rows are UPDATEd on every ordinary field save and boundaries change rarely, so the scan must only run on real geometry change. **The `parent_project_id` half is new and is what storing `is_valid` obliges** — re-parenting changes a whole lineage's validity with no geometry change at all. That branch calls the function with `p_rescan = false`. - `proposed_user` comes from `NEW.modified_user`, which every write path populates (`project_save.php:100`, `map_boundary_update.php:170` via the field list, `project_create_boundary.php:69`). No dependency on the `app.user_id` GUC that 017's history triggers read. Two properties worth stating in the migration comment: the trigger writes only to `project_premises`, never back to `projects.projects`, so it cannot fire itself; and it coexists with 017's history trigger because they write to different tables and their firing order is irrelevant. **Seed.** `SELECT * FROM projects.refresh_project_premises(NULL);` at the end of the migration. `db/migrate.php:88-92` wraps each migration file in a single transaction, so this is one long transaction — it takes no locks on `basedata.abp` (read only) and writes only to a table nothing else can see yet, so it is safe, just potentially slow. Two consequences to handle: **Sized, and it fits** (Dave, 2026-08-25): ~600 projects against ~36M `basedata.abp` rows. That is 600 bounded GiST probes, not a 36M-row scan — the seed belongs in the migration and needs no batching. Two notes remain: - PDO does not surface `RAISE NOTICE`, so the seed is **silent** under `migrate.php`. Report the total from the function's return instead of relying on notices. - The fallback stays documented in the migration header anyway, because it costs one line and the function is shared: apply 057 with the seed line commented, then run `sql/project_premises_resync.sql` in `psql`, where the notices stream. **`sql/project_premises_resync.sql`** — a thin caller, deliberately in `sql/` and **not** `db/`, because `migrate.php` globs `db/*.sql` and would apply it as a migration. Same reasoning as `sql/util_delete_dynamic_field.sql`. It selects `projects.refresh_project_premises(NULL)`, prints the per-project added/removed table, and defaults to reporting only — a `-- SET` at the top to actually write, matching the dry-run default of the existing utility. **`sql/abp_sync.sql`** gains a step 4 pointing at it. The address refresh introduces new-build premises inside existing boundaries with no boundary change, so nothing else catches them (`improvement-opportunities.md:670-672`). The resync is referenced rather than inlined so the address sync stays a script about addresses. ### Phase 3 — Read view and the four database views (migration `058_project_premises_views.sql`) **`projects.vw_premise_claimants`** — one row per UPRN, answering *who claims this premise*: ```sql CREATE VIEW projects.vw_premise_claimants AS SELECT pp.uprn, count(*) AS claimant_count, -- NULL when contested, ON PURPOSE. There is no tie-break (Decision 3), so a single -- owner does not exist to name; forcing NULL makes the ambiguity impossible for a -- consumer to ignore, where an arbitrary first() would read as an answer. CASE WHEN count(*) = 1 THEN min(pp.project_id) END AS project_id, array_agg(pp.project_id ORDER BY pp.project_id) AS project_ids, string_agg(p.project_name, ', ' ORDER BY pp.project_id) AS project_names FROM projects.project_premises pp JOIN projects.projects p ON p.project_id = pp.project_id WHERE pp.state = 'approved' AND pp.is_valid GROUP BY pp.uprn; ``` `WHERE is_valid` is what makes `claimant_count = 1` the normal case: a premise inside a parent and three nested descendants has four rows in the table and **one** valid claimant here. `claimant_count > 1` therefore means genuine unrelated overlap — siblings, or projects in different trees — which is exactly the red-flag condition. **`projects.vw_project_premises`** — premise identity and flags, the live set only: ```sql CREATE VIEW projects.vw_project_premises AS SELECT pp.project_id, pp.uprn, pp.is_valid, cl.claimant_count, -- The viewing project's role for this premise, NULL where no lineage -- relationship exists for this UPRN (Decision 4). CASE WHEN pp.is_valid AND EXISTS ( SELECT 1 FROM projects.project_premises a JOIN projects.vw_project_ancestors anc ON anc.project_id = pp.project_id AND anc.ancestor_id = a.project_id AND anc.depth > 0 WHERE a.uprn = pp.uprn AND a.state <> 'removed') THEN 'Child' WHEN NOT pp.is_valid THEN 'Parent' END AS hierarchy_role, abp.address, abp.postcode, abp.classification_code, abp.class_description, abp.cross_reference AS toid_reference, abp.udprn, abp.geom FROM projects.project_premises pp JOIN basedata.abp abp ON abp.uprn = pp.uprn LEFT JOIN projects.vw_premise_claimants cl ON cl.uprn = pp.uprn WHERE pp.state = 'approved'; ``` `hierarchy_role` is the pair of Decision 4's first flag. `Parent` falls straight out of `NOT is_valid` — the only way to be invalid is for a descendant to hold the premise. `Child` needs the ancestor probe, because being valid does not by itself mean anything above you contains the premise; a standalone project is valid with no role at all. Two deliberate omissions. **No `state` column** — the view *is* the live set, so a state column on it would only ever read `approved`; anything wanting tombstones reads the table. And **no building-type join**: `project_load.php:249` joins `basedata.toid_building_type_geom` while the list views join `basedata.toid_building_type`. Putting either in the shared view would silently move somebody's MDU/SDU numbers, so each consumer keeps the join it has today. **Consumers read the view, not the table**, wherever `claimant_count` or `hierarchy_role` is needed. Reading `project_premises` directly is correct only for `is_valid` and for tombstones. **The four views.** In every case the GiST spatial join becomes an equality join on `abp_uprn_idx`, and `abp` is retained only where `cross_reference` is needed for the building-type lookup: ```sql -- was LEFT JOIN basedata.abp ON st_intersects(p_1.geom, abp.geom) -- becomes LEFT JOIN projects.project_premises pp ON pp.project_id = p_1.project_id AND pp.state = 'approved' LEFT JOIN basedata.abp ON abp.uprn = pp.uprn ``` 1. **`projects.vw_projects_list`** — `CREATE OR REPLACE VIEW`, not `DROP`+`CREATE`, so dependent grants survive (039's own note, line 18). `count(*)` becomes `count(pp.uprn)` — see **Risks**. New columns: `valid_premises`, `dependant_premises`, `contested_premises`, `valid_mdu_premises`, `valid_sdu_premises`, plus `in_hierarchy` (does this project have a parent or any children) which is what drives amber-vs-red in Phase 5. `bduk_uprn_tracking_v2` joins `pp.uprn` directly and needs no `abp` hop. **No column here sums cleanly estate-wide, and that is the design** (Decision 3). `sum(valid_premises)` over-counts by exactly `sum(contested_premises)`, which is the reported size of the error rather than a hidden one. 2. **`opportunity.vw_opportunity_list`** — same substitution through `opportunity_project_link`. `total_premises` becomes `count(DISTINCT pp.uprn)`, which is the double-counting fix: an opportunity linked to both a parent and its child counts the shared premise once. `valid_premises` added alongside. `DISTINCT uprn` rather than `is_valid` is right here, because an opportunity that links only the parent still genuinely covers premises that are not valid *at* the parent — and `DISTINCT` also absorbs unrelated overlap, so an opportunity total is clean even where the underlying projects are contested. 3. **`opportunity.vw_opportunity_projects_output`** — rows are per project, so it mirrors `vw_projects_list`: `count(pp.uprn)` plus valid and dependant. 4. **`stocklists.stocklist_premises_geom`** — the premise→project attribution becomes `LEFT JOIN projects.vw_premise_claimants cl ON cl.uprn = sp.uprn`, then `LEFT JOIN projects.projects p ON p.project_id = cl.project_id`. **One row per premise, always** — the requirement that started this (Dave, 2026-08-18), met by aggregating rather than tie-breaking. `p.project_name` is NULL when contested, so carry `cl.claimant_count` and `cl.project_names` through for the display in Phase 5. **`style_type` needs its test changed.** Today it keys on `p.project_id >= 1` to mean *in a project*; with `cl.project_id` NULL for contested premises that would style a contested premise as *not in a project*, which is wrong in the one case people are being asked to look at. Use `cl.claimant_count >= 1` instead. `public.vw_projects_list` (baseline line 7587) is a legacy duplicate with no reference in `www/` — leave it, and add it to `docs/unusedfiles.md` in Phase 6 rather than maintaining a second copy. ### Phase 4 — Endpoint queries - **`project_load.php:243`** (premise list) — read from `projects.vw_project_premises` filtered by `project_id` instead of joining `projects.projects` to `abp`. Keep the existing `stocklist_premises`, `bd_m`, `bduk_uk_geom` and `toid_building_type_geom` joins exactly as they are, so only the premise *source* changes. Add `is_valid`, `hierarchy_role` and `claimant_count` to the select list for Phase 5 — the two flags plus the count they key on. - **`project_load.php:385`** (counts) — the `prem` CTE reads the view; add `valid_premises`, `dependant_premises`, `contested_premises`. - **New query in `project_load.php`** — the dependant breakdown, one aggregate rather than a per-row lookup: for premises visible in this project but valid in a descendant, group by the owning descendant and count. That gives the parent *"394 premises delivered by: Estate Phase 1 (380), Estate Phase 2 (14)"*, which is the narrative in the problem statement, for the cost of one grouped query. Deliberately **not** a `valid_project_id` column on the premise list: resolving the owner per row needs a lateral over a premise list that can run to thousands, and the aggregate answers the question people actually ask. If the per-row owner is wanted later, it is a column on the table, not a query change. - **`stocklist_load.php:244`** — join `projects.vw_premise_claimants` on `uprn`, giving **exactly one** row per premise instead of one row per containing project. Select `claimant_count` and `project_names` so a contested row can name both projects. - **`stocklist_load.php:380`** (projects for a stocklist) and **`:575`** (opportunities for a stocklist) — join `stocklist_premises.uprn` → `project_premises.uprn`. `:575` already uses `COUNT(DISTINCT p.project_id)` and stays correct. - **`opportunity_manage.php:352, :427`** and **`opportunity_process.php:314, :387`** — the same substitution as the opportunity views, with `count(DISTINCT pp.uprn)` for totals. `opportunity_process.php` is live (`opportunity_edit.js:2426` drives it over `EventSource`), so both files change; the four queries are near-duplicates of each other and of the two views, which is a backlog note for Phase 6, not a refactor here. Every one of these is a `$q` string in a file with no local runtime, so the check is reading plus `node --check` on any JS touched — and the reminder from CLAUDE.md that **no double quotes may appear inside a SQL string comment**, since that closes the PHP string and is a fatal a balance count cannot see. ### Phase 5 — Surfacing the two flags Without this the numbers exist and nobody sees them, and neither the parent-project visibility nor the overlap correction that motivated the change is actually delivered. **The two flags are independent and either can appear alone** (Decision 4). A premise can be a `Child` with no overlap, contested with no hierarchy, both, or neither. One combined indicator would have to invent an order of precedence between two unrelated facts. - **`www/js/project_edit_v2.js`** — the Premises tab carries both: `hierarchy_role` as a `Parent` / `Child` chip, and an overlap chip where `claimant_count > 1`, **amber when the project is in a hierarchy and red when it is standalone**. The summary tiles gain valid, dependant and contested; the dependant breakdown from Phase 4 renders near the existing hierarchy panel, where a reader is already thinking about children. - **`www/js/project_list.js`** — Valid / Dependant / Contested columns on the Tabulator table, next to the existing premise count, with the contested cell taking the same amber-or-red treatment so the list can be scanned for problems. - **`www/js/stocklist_edit.js`** — the premise list shows one row per premise. Where `claimant_count = 1` it names the project as it always has; where `claimant_count > 1` it shows the count with `project_names` behind it, flagged red. **Red is keyed purely on more than one project** (Dave, 2026-08-25) — a single project is fine and unflagged, whatever its hierarchy; the amber-vs-red split belongs to the project editor, where a viewing project exists to be in a hierarchy or not. - **`www/js/stocklist_list.js` / `opportunity_list.js`** — check whether either surfaces a premise count that now has a valid variant worth showing; add only where the column already exists. **The alert on the project editor is the half that makes correction possible.** A marker on a stocklist premise row is seen by someone who usually cannot fix it; the person who can is whoever owns the boundary. So the project editor states how many of its premises another project also claims, names those projects, and names the two fixes: - **set a parent project**, if one genuinely sits inside the other — this resolves the claim through `is_valid` and the overlap disappears; - **edit a boundary**, if the overlap is a drawing error. Naming the fixes is the point. Red marks the standalone project precisely because the first fix is available there and costs one field. ### Phase 6 — Documentation - **`CLAUDE.md`** — a **Project premises** section next to *Wayleave coverage*, stating: one row per premise per project per source; `state = 'approved'` is the live set and `removed` is a tombstone; the refresh is a trigger on `geom` **or** `parent_project_id`; and — the thing a future reader most needs — **what to count**: > `is_valid` is the only stored flag: this project is the lowest level in its own lineage > containing the UPRN. Several projects can hold it for one UPRN, and that is not a bug — > it means unrelated boundaries overlap. `claimant_count` and `hierarchy_role` are derived > in `projects.vw_project_premises` / `vw_premise_claimants`. **Count `is_valid` per > project; no column sums cleanly estate-wide, and `contested_premises` is the size of the > discrepancy. Counting the raw table double counts every hierarchy.** That is the mirror of wayleave's `count(DISTINCT uprn)` warning, and the same class of mistake. State also that **nothing here reads a project status**, so the status-semantics work is independent of it. - **`docs/features.md`** — the premise model. Note this file already has uncommitted changes in the working tree; merge rather than overwrite. - **`docs/improvement-opportunities.md`**: - Tick **"Canonical project-UPRN set"** (`:655-672`) with a dated note pointing here. - Tick **"`total_premises` counts 1 for an entity with no projects"** (`:288-295`) — fixed as a side effect, and its own closing question ("worth checking whether `vw_projects_list`'s equivalent has the same shape") is answered: it did. - Update the wayleave↔project matching item (`:673-707`) to record that its blocker is gone, and that Dave is rebuilding the matching both ways as its own feature. - New items: the four near-duplicate opportunity premise queries across `opportunity_manage.php`, `opportunity_process.php` and the two views; the accounts premise list still commented out at `account_load.php:217`; and a project *delete* path needing a resync (see **Risks**). - **`docs/unusedfiles.md`** — `public.vw_projects_list`. ## Testing checklist 1. Migration 056 applies cleanly and reports the row count preserved into `project_premises_migrated_20260825`. 2. Migration 056 is inert — the project list, project editor and opportunity editor all load and show the same premise numbers as before. 3. Migration 057 applies; note the seed's runtime for the record. `SELECT count(*) FROM projects.project_premises;` is non-zero and every row is `state='approved'`. 4. Every one of the ~600 live projects with a boundary has rows; any project with none has a null or empty `geom`. 5. Pick a project with a known premise count: its `project_premises` count matches what the editor showed before Phase 3. 6. Standalone project, no parent and no children: every row `is_valid = true`, `claimant_count = 1`, no `hierarchy_role`. 7. Parent with one child whose boundary sits inside it: shared premises are `is_valid = false` at the parent and `true` at the child. 8. A premise inside the parent but *outside* every child stays `is_valid = true` at the parent, with no `hierarchy_role`. 9. Nested chain at least three deep: the premise is valid only at the deepest project, and `claimant_count = 1` — the chain is not an overlap. 10. **Two siblings** under the same parent both containing a premise: both `is_valid = true`, the parent `is_valid = false`, and `claimant_count = 2`. 11. **A third sibling** over the same premise takes `claimant_count` to 3, with none of them invalid. 12. Two unrelated projects with no shared ancestor overlapping a premise: both `is_valid = true`, `claimant_count = 2`. 13. Give one contesting project a `parent_project_id` pointing at the other: `claimant_count` drops to 1 and the overlap flag clears, with no boundary edited. 14. Draw a new boundary on the map: the new project's rows appear immediately, without a page reload or a manual step. 15. Reshape a boundary outward: new premises appear as `approved`; the save does not feel slower. 16. Reshape it back inward, then out over them again: the premises become `state='removed'` then return to `approved`, with `proposed_datetime` unchanged throughout. 17. Save an ordinary field on a project with no boundary change: no rows change and no `state_datetime` moves. 18. Change a project's **status**: nothing in `project_premises` or either view changes at all — the plan reads no status anywhere. 19. Re-parent a project under one containing the same premises, then back out again: the flags flip and flip back, with no boundary edited either time. 20. Run `sql/project_premises_resync.sql` immediately after all of the above: zero added and zero removed for every project. 21. Migration 058 applies; the project list loads and `total_premises` is unchanged for every project that has premises. 22. A project with no premises now shows 0 rather than 1 on the project list — confirm that is wanted (see Risks). 23. The project list is measurably faster to load than before; report the before/after. 24. On every row of `vw_projects_list`: `valid_premises` + `dependant_premises` = `total_premises`, and `contested_premises` ≤ `valid_premises`. 25. `SELECT sum(valid_premises) - sum(contested_premises) FROM projects.vw_projects_list;` is at most `SELECT count(DISTINCT uprn) FROM projects.project_premises WHERE state='approved';` — the overlap accounts for the whole difference and nothing else does. 26. A parent project that delivers nothing itself shows a large total, a small or zero valid, and a large dependant. 27. Opportunity summary: an opportunity linked to both a parent and a child counts the shared premise once where it counted it twice, and `total_mtr_per_mdu_prem` moves in the direction that says the route is more efficient than previously reported. 28. Stocklist premise list, **nested chain**: a premise inside a parent and two nested descendants shows **one** row naming the deepest project, not three. 29. Stocklist premise list, **siblings or unrelated projects**: still **one** row, showing the claimant count with both project names behind it and flagged **red**. 30. Stocklist premise list, single claimant: **no** flag at all, whatever hierarchy the project sits in — red is keyed purely on more than one project. 31. The map layer built on `stocklist_premises_geom` renders exactly one point per premise in every case above, and a contested premise still styles as *in a project* rather than falling through to the no-project style. 32. Project editor Premises tab: `Parent` / `Child` chips appear on the right rows and only where a lineage relationship exists for that UPRN. 33. Project editor Premises tab, contested premise **in a project that has a parent or children**: overlap chip is **amber**. 34. Same premise viewed from the **standalone** contesting project: overlap chip is **red**. 35. Both flags on one row: a premise that is both a `Child` and contested shows two chips, not one merged indicator. 36. The project editor alert names the rival projects and the two fixes (set a parent, edit a boundary). 37. The dependant breakdown lists the child projects and counts, and its total equals `dependant_premises`. 38. Run a prospecting job from the opportunity editor: `opportunity_process.php` streams to completion and its premise figures agree with `opportunity_manage.php` for the same opportunity. 39. Insert a handful of rows into `basedata.abp` inside an existing boundary, run the resync, and confirm they are picked up and reported. 40. Company scoping is unaffected — a non-main-company user sees the same projects and premise counts as before — and the PHP error log is clean, with no warnings from any changed endpoint. ## Risks - **Two numbers change on purpose, and both change from wrong to right.** A project with no premises goes from `total_premises = 1` to `0` (`count(*)` over a LEFT JOIN chain counted the empty row — `improvement-opportunities.md:288-295`). And an opportunity linked to both a parent and its child sees `total_premises` fall, because the shared premise stops being counted twice. Anything comparing against a stored or exported figure from before the deploy will show a difference; test 31 exists to see it happen deliberately rather than in a report. - **`stocklist_premises_geom` returns fewer rows.** Aggregating to one row per UPRN is the fix for today's row multiplication, but it is a row-count change in a view the map renders from. Anything downstream that was accidentally relying on the duplicates — a count without `DISTINCT`, most likely — will move. - **Unrelated overlap is left double counted, on purpose.** `sum(valid_premises)` across the estate exceeds the true premise count by exactly the contested set. This is Decision 3 and it is a reported error bar, not a hidden one — but anyone building a report needs to know it, or they will sum the obvious column and be wrong by however many overlaps exist on the day. `contested_premises` is what tells them, and the number should trend to zero as the flags get acted on. If it does not, that is the signal that the flags are not being seen. - **Storing `is_valid` means a refresh writes other projects' rows.** This is the accepted cost of the stored-flag decision. The failure mode is a *partial* refresh leaving a stale flag somewhere the trigger did not reach, which is silent. The resync test below is the check and the resync is the repair — run it after anything unusual. `claimant_count` and `hierarchy_role` cannot rot this way; they are recomputed on every read. - **This module is in active client use.** ~600 live projects, so the deployment is not a greenfield one: 056 renames the old `project_premises` to a dated archive rather than dropping it, 056 is inert on its own, and 057's seed populates every existing project before any consumer reads the table in 058. Nothing between 056 and 058 changes a number a client can see. - **A project deleted outside the app leaves stale validity flags.** The FK cascades the rows away, but the ancestors' `is_valid` for those UPRNs is then wrong, and there is no trigger to catch it because the rows are already gone. Acceptable because **no delete path exists in the application** — no endpoint, and no `DELETE FROM projects.projects` anywhere in `www/`. Documented as: a manual project delete must be followed by the resync. If a delete feature is ever built, it needs a `BEFORE DELETE` hook to capture the UPRNs first. (`claimant_count` self-corrects here, since the deleted project simply stops appearing in the view — a small argument that deriving was the right call.) - **A very large boundary makes the map save wait.** The synchronous refresh is the right default, but there is no opt-out. If it becomes a real problem the queue option is still open — and the resync already exists as the drain. - **Not built, and not to be assumed:** the approval flow. Every row this plan writes lands `approved` with nothing recording who decided it, because nothing did. That is a gap the approval work closes rather than a state it must preserve: on switch-on it generates one legacy approval per project — `P--1`, ~600 rows — and stamps that project's existing rows with it (Dave, 2026-08-25), so nothing is left unattributed. Those are ordinary, reversible approvals, not a special class: reversing one sends its premises to `pending`, which is the correct meaning. Nothing here needs to change for it — an insert plus one `UPDATE` against rows this plan has already created — and `state` allowing `pending` from day one is what makes the rest of the switch-on code-only.