# Project Premise Counts — materialising the list aggregates - **Date:** 2026-09-29 - **Status:** Planned - **Status Date:** 2026-09-29 - **Phases:** 4 - **Phases Complete:** 0 - **Notes:** The project list takes ~5s, measured, because `projects.vw_projects_list` recomputes eleven aggregates for **every project in the database** on every load, joining `project_premises` to the 41M-row AddressBase table and separately aggregating the whole of `project_premises` by UPRN. The fix is the one migrations 056–059 already made one level down: materialise it. `refresh_project_premises()` already computes the exact set of projects whose counts can have changed, so this is an extra step in an existing function rather than new machinery. Independent of `docs/2026-09-29-project-plan-view.md`; the two share no files. ## Plan Phases 1. The table and its backfill (migration 112) 2. Maintenance — Step 3 in `refresh_project_premises()` 3. The view rewrite 4. Documentation ## Problem `www/js/project_list.js` calls `fn/data_get.php?source=projects.vw_projects_list&orderby=modified_datetime&sort=desc` with no `fields` parameter, so `data_get.php` falls through to `$fields = '*'`. The view opens with a `prems` CTE that, on every load, for every project in the database: - scans every `projects.project_premises` row with `state = 'approved'` - `LEFT JOIN`s each to `basedata.abp` — the national AddressBase table, ~41M rows - then to `basedata.toid_building_type` via `cross_reference` → `toid` - then to `adhoc.bduk_uprn_tracking_v2` - separately aggregates **the entire** `project_premises` table grouped by `uprn`, to get a claimant count - then computes eleven conditional aggregates over the result The view's own comment states the constraint plainly: *"this CTE sits behind a LEFT JOIN so no filter can ever be pushed into it and it is always computed in full"*. The reasoning for why set-based is correct **there** is sound. What does not follow is that the work should happen at read time at all. `LIMIT` cannot rescue it either — the outer `ORDER BY modified_datetime` forces every group to be materialised before anything can be sorted or discarded. ### Why this is the one thing that scales with premises Everything else in the estate is small. Three clients at 40k / 200k / 500k premises a year is ~740k a year; at a typical 200 premises per project that is ~3,700 projects a year, and over five years `project_premises` reaches perhaps 10–12M narrow rows — about 2GB with indexes, which Postgres does not notice. AddressBase, at ~41M rows, is three to four times the entire five-year operational dataset and is present on day one regardless. So premise volume does not stress the hardware. It stresses **this query**, linearly and on every list load. At present data it is 5s. At the volumes above it is a full scan of ~10M premise rows joined to AddressBase, every time anyone opens the list. ### What is not the problem The `(abp.cross_reference)::text = (bt.toid)::text` casts look alarming and are not an issue — that is `pg_dump` rendering a `varchar` comparison, and `varchar`/`text` are binary coercible so the index is still usable. Not worth chasing. ## Root-cause analysis Migrations 056–059 replaced live `ST_Intersects(projects.geom, abp.geom)` resolution with `projects.project_premises`, materialised from the boundary and maintained by a trigger. `docs/2026-08-17-project-premises-uprn-table.md` records why: nine queries were resolving containment live, one of them for every project at once on every project-list load. The aggregates **over** that table were left live. They are the same problem, one level up, and they carry the same fix. The containment work was moved to write time; the counting was not. ## Decisions Settled with Dave, 2026-09-29. 1. **A table, not a materialised view.** `REFRESH MATERIALIZED VIEW` is all-or-nothing — there is no row targeting and no `WHERE`. `CONCURRENTLY` does not help: it requires a `UNIQUE` index, it avoids only the `AccessExclusiveLock` on readers, and internally it builds the whole new result into a temp table and diffs it, so it does strictly **more** total work than a plain refresh. A matview here means recomputing every project because one boundary moved, which relocates the 5s rather than removing it. Matviews remain the right tool for something periodic and estate-wide; not for this. 2. **Its own table, not columns on `projects.projects`.** Two concrete reasons. `fn_projects_history()` fires on every update to that table, so count columns would either have to join its no-op comparison list — and then every recount writes an audit row about a number nobody edited — or be left out of it and churn the trigger for nothing. And CLAUDE.md's warning about adding a column to an entity table (the `opportunity_manage.php` bare-`is_deleted` ambiguity) applies to every star-select of `projects.projects`. A separate table avoids both questions entirely. 3. **Maintained inside `refresh_project_premises()`, not by a new trigger.** That function is already the single writer for `project_premises`, already fires on `geom` and `parent_project_id`, and is already what `sql/project_premises_resync.sql` calls — so the resync and `abp_sync` backstops come free rather than needing their own path. A second trigger would be a second definition of when counts are stale. 4. **The affected-project set already exists.** Step 2 of the function re-flags `is_valid` across **all** projects holding any affected UPRN — its `target` CTE joins `project_premises` to `affected` on `uprn` with no project filter, and its comment says so: *"Not just the project being refreshed... That is the accepted cost of storing is_valid rather than deriving it."* So the set of projects whose counts can have changed is `scope ∪ every project holding an affected UPRN`, which the function is already computing. **`contested_premises` therefore needs no special handling** — the cross-project fan-out it depends on is already there. 5. **Three counts are dropped from the view, not migrated.** `dependant_premises`, `valid_mdu_premises` and `valid_sdu_premises` are computed on every list load and no consumer reads them from the view. Eleven aggregates become eight. `dependant_premises` needs care, because it is not unused — it is *separately* computed. `project_load.php:406` has its own aggregate for the single project the editor has open, which is where the Sub-projects chip comes from. That query is correctly filtered to one project and is untouched by any of this. Dropping the column from `vw_projects_list` removes a duplicate computation of the same idea, not the idea. 6. **No denormalisation of `building_type` or the BDUK flag onto `project_premises`.** An earlier draft proposed it. It is unnecessary once the counts are materialised: the joins move to write time against a bounded project set, which is the whole win, and a second denormalised copy would need its own staleness story against AddressBase for no further gain. `project_premises` stays a pure containment record. 7. **The claimant count is asked correlated, not set-based.** This is the opposite of the choice the current view makes, and correctly so — CLAUDE.md's rule is that a filtered consumer asks the question correlated or via `LATERAL`, and an unfiltered one asks it set-based. The current CTE is unfiltered, so set-based is right there. The recompute **is** filtered, to a bounded project set, so `LATERAL` is right here. ## Build ### Phase 1 — The table and its backfill **Migration `db/112_project_premise_counts.sql`.** `111` is spoken for by `docs/2026-09-29-project-plan-view.md`, which is unbuilt. If that work is shelved this can take the lower number — but only before either is applied. ```sql CREATE TABLE projects.project_premise_counts ( project_id integer PRIMARY KEY REFERENCES projects.projects(project_id) ON DELETE CASCADE, total_premises integer NOT NULL DEFAULT 0, mdu_premises integer NOT NULL DEFAULT 0, smdu_premises integer NOT NULL DEFAULT 0, sdu_premises integer NOT NULL DEFAULT 0, bduk_vouchers integer NOT NULL DEFAULT 0, bduk_vouchers_mdu integer NOT NULL DEFAULT 0, valid_premises integer NOT NULL DEFAULT 0, contested_premises integer NOT NULL DEFAULT 0, computed_datetime timestamptz NOT NULL DEFAULT now() ); ``` `ON DELETE CASCADE` is right here and is not in tension with soft delete: a deleted project keeps its row (`is_deleted`, migration 061) and therefore keeps its counts, which will read zero because the refresh takes the no-boundary branch and tombstones everything. The cascade only fires on a genuine hard delete outside the app, which is exactly when the counts should go too. Guards at the top: `projects.project_premises` exists, `refresh_project_premises(integer, boolean)` exists, and `projects.project_premise_counts` does not. Verify block at the foot asserting the table is populated for every project — the migration must not be able to report OK having backfilled nothing. **The backfill** is the same aggregate the view computes today, written once over all projects, with two differences from the current CTE that are deliberate: - one row per premise from `basedata.abp`, via `LEFT JOIN LATERAL (… LIMIT 1)`. Migration 057 records that `basedata.abp` has no unique index on `uprn`. Where a UPRN appears twice, today's join multiplies that premise's row and inflates every count that touches it. **The backfill may therefore not match the view exactly, and where it differs the new number is the right one.** Confirming that is a Phase 1 test, not a surprise. - the claimant count taken correlated rather than from a whole-table aggregate. ### Phase 2 — Maintenance **`refresh_project_premises()` gains a Step 3**, after the existing `is_valid` re-flag. Nothing about Steps 1 and 2 changes — this is additive, and deliberately so: that function is the single writer for a table nine queries read, and a regression there is far worse than a slow list. ``` Step 3 — recompute counts for every project the refresh could have moved affected = DISTINCT uprn FROM project_premises WHERE p_project_id IS NULL OR project_id = p_project_id touched = DISTINCT project_id FROM project_premises JOIN affected USING (uprn) → INSERT … SELECT the eight counts for `touched` ON CONFLICT (project_id) DO UPDATE ``` `affected` is re-derived rather than carried out of Step 2 — the two are separate statements, and re-deriving it is the same cheap index work against `project_premises_uprn_state_idx (uprn, state)`. Restructuring Step 2 to share the CTE would put the risk in the statement that must not break. `touched` deliberately does **not** filter on `state`. It is a superset — a project whose rows on those UPRNs are all tombstones has counts that did not change — and recomputing a few extra projects is cheaper than reasoning about which ones to skip. Cost, for a typical 200-premise boundary edit: ~200 UPRNs, an index probe each, resolving to a handful of projects, each recounted from `project_premises_project_state_idx`. Milliseconds. Against 5 seconds on every list load today. **The full-rebuild path needs checking.** `refresh_project_premises(NULL)` makes `affected` every UPRN in the table and `touched` every project, which is correct and is what the seed and the resync want — but it adds a full recount to a call that is already the slowest thing in the schema. Time it before and after; if it is unacceptable the counts step can take its own `p_counts boolean DEFAULT true` parameter. **The backstops come free.** `sql/project_premises_resync.sql` calls this function, and is step 4 of `sql/abp_sync.sql` — so an address sync that drops new-builds inside existing boundaries maintains the counts along with the rows. No second code path, which is the whole reason for Decision 3. **Drift detection belongs in the resync report, not a new script.** That script already exists, already runs as a dry run inside a transaction that ends in `ROLLBACK`, and already has "EXPECT ZERO" as its pass condition. Extending its report with a counts comparison puts the check where somebody already looks, rather than adding a survey tool nobody remembers to run. ### Phase 3 — The view rewrite `projects.vw_projects_list`'s `prems` CTE is replaced by a `LEFT JOIN` to `projects.project_premise_counts`. Everything else about the view is untouched, including the `is_deleted` filter migration 061 put inside it and the `top_project_id` column from 060. `LEFT JOIN`, not `JOIN`: a project created in the instant between its row and its counts row must still appear on the list, with zeroes, rather than vanishing. `COALESCE(…, 0)` on each count. Three columns leave the view — `dependant_premises`, `valid_mdu_premises`, `valid_sdu_premises`. `data_get.php` serves `SELECT *`, so consumers receive fewer columns rather than an error, and no consumer reads them from the view. `project_load.php` keeps its own `dependant_premises` aggregate for the open project and is unaffected. Worth one test anyway. **Two consumers, not one.** `project_list.js` is the obvious one; `project_edit_v2.js:4887` also reads the view for `in_hierarchy` through `data_static`. Both need checking after the rewrite. While the view is open, `project_list.js` should pass an explicit `fields` list rather than relying on `SELECT *`. That is independent of the rest and can land with it. ### Phase 4 — Documentation - This plan updated — status, phases complete. - `CLAUDE.md` — a paragraph under **Project Premises** saying the counts are materialised in `projects.project_premise_counts`, maintained by Step 3 of `refresh_project_premises()`, and that `vw_projects_list` reads them rather than computing them. The existing rule about what to count is unchanged and still applies. - `sql/project_premises_resync.sql` — its header gains the counts comparison. - Anything the build turns up that is not this feature goes in `docs/improvement-opportunities.md`. ## Testing checklist Restated in chat at the end of each phase, and stopped on for confirmation. 1. Migration 112 applies cleanly, and re-running it is refused rather than silently doing nothing. 2. After 112, `projects.project_premise_counts` has exactly one row per row in `projects.projects`. 3. The backfilled counts match the current view for every project, or differ only where `basedata.abp` holds a duplicate UPRN — confirm by counting duplicates and checking the differing projects are the ones holding them. 4. A project with no boundary has a counts row reading zero rather than no row at all. 5. A soft-deleted project has a counts row reading zero. 6. Editing a project boundary updates that project's counts within the same transaction. 7. Editing a child's boundary updates its parent's `valid_premises` too, not only its own. 8. Growing a boundary so it overlaps an unrelated project raises `contested_premises` on **both** projects, and the unrelated one was never touched by the edit. 9. Shrinking that boundary back lowers `contested_premises` on both again. 10. Changing a project's `parent_project_id` with no geometry change updates counts for the whole lineage. 11. Setting a parent for the first time does the same — this is the case migration 059 was written for. 12. Deleting a project through the app leaves its counts row present and reading zero. 13. `sql/project_premises_resync.sql` reports zero drift on a database the app has been maintaining. 14. Deliberately corrupting one counts row by hand, then running the resync, reports that row as drifted and corrects it on `COMMIT`. 15. Time `refresh_project_premises(NULL)` before and after Phase 2 and record both numbers. 16. The project list renders the same projects, in the same order, with the same numbers as before the rewrite. 17. The project list is materially faster — record the before and after timings. 18. The project editor still resolves `in_hierarchy` correctly after the view rewrite. 19. The project editor's This Project / Sub-projects / Overlap chips are unchanged — they come from `project_load.php`'s own aggregate, not the view. 20. A non-company-1 user sees the same set of projects on the list as before. 21. A deleted project still does not appear on the list. 22. `sql/abp_sync.sql` followed by the resync picks up new-build premises and updates counts for the projects containing them. 23. A freshly created project appears on the list immediately, with zeroes, rather than being absent. ## Risks - **Phase 2 touches the single writer for `project_premises`.** Nine queries read that table and a regression there is materially worse than a slow list. The step is purely additive, Steps 1 and 2 are not restructured, and Phase 1 lands the table inert so the numbers can be verified before anything depends on them. - **Counts drift silently.** A maintained table's failure mode is wrong numbers that look right, which is worse than slow correct ones. Mitigated by folding the comparison into the resync report, which already exists and already expects zero. - **`basedata.abp` has no unique index on `uprn`.** Today's view inflates counts where a UPRN is duplicated; the backfill will not. This is a correction, but it will present as a mismatch during Phase 1 and needs recognising as such rather than debugging as a fault. - **The full-rebuild path gets slower.** `refresh_project_premises(NULL)` already scans every boundary; it now also recounts every project. Measured in Phase 2, with a `p_counts` parameter as the escape hatch if it is unacceptable. - **`vw_projects_list` has two consumers, and one is not obvious.** The project editor reads it for `in_hierarchy`. Dropping three columns and replacing the CTE is safe for both in principle; test both.