# Premise Lists: Title, Freeholder and Company No.

- **Date:** 2026-10-21
- **Status:** Complete — built and tested 2026-10-21 → 22
- **Status Date:** 2026-10-22
- **Phases:** 3
- **Phases Complete:** 3
- **Notes:** Settled by a grilling session 2026-10-21. One building-keyed view, read by three
  premise lists. The wayleave premise→title misalignment this exposed is its own plan,
  `docs/2026-10-21-wayleave-premise-title-link.md`, and is not fixed here.

## Plan Phases

1. Building freeholder view (migration 117) ✅
2. Project and stocklist premise lists ✅
3. Wayleave Premises tab ✅

## Problem

The Land Registry search and the Strategic Map Analyser both name a **Freeholder**, but no
premise list does. A planner looking at a project's, a stocklist's or a wayleave's premises
cannot see who owns the freehold of the building each premise is in, nor the title to order
the register with. Add **Title**, **Freeholder** and **Company No.** to those three lists.

### Settled decisions

1. **The link is through the building**, the chain Land Registry search and the analyser
   already use: premise → its building (`basedata.abp.cross_reference`) →
   `landregistry.toid_title_map.freehold_title_no` → `landregistry.ccod`. So a premise list
   and an Area Summary cannot disagree about a building's freeholder. Wayleave coverage's
   own premise→title link (`abp.title_no`) is a different feed and is left alone here.
2. **Freeholder is proprietor 1**, per `GLOSSARY.md` — `ccod.proprietor_name_1`, with
   `company_registration_no_1` as Company No. Changing what Freeholder means belongs to all
   its readers at once, not to premise lists first.
3. **UK and overseas companies** both: `ccod` holds CCOD and OCOD rows (`data_source`),
   one row per title (4,404,751 rows, 4,404,751 distinct titles — checked 2026-10-21).
   Overseas companies have no UK registration number, so their Company No. is blank.
4. **One title per building.** `toid_title_map` holds at most one freehold title per TOID
   (25,410,923 buildings, none with more than one — checked 2026-10-21). Each premise
   therefore has at most one Title / Freeholder / Company No.; no multi-value cells.
5. **One row per premise is still enforced by the readers**, not trusted to the data — a
   future load that breaks either 1:1 must not double the rows of a 50k-premise list. See
   Phase 1 for why the guard sits in the readers rather than the view.
6. **Blank means unknown, never "private".** A title with no `ccod` row shows its title
   number with Freeholder and Company No. blank — a known freehold not held by a company.
   A building with no title shows all three blank. Nothing is labelled.
7. **Scope:** the project editor's Premises tab, the stocklist editor's premise list, the
   wayleave editor's Premises tab. Out: distance analysis results, the wayleave approval
   detail modal, the direct-UPRN sidebar, the stocklist search/manage panels, the account
   premise list (dead code), and the four existing copies of the ccod join (logged in
   `docs/improvement-opportunities.md`).
8. **Shown on screen and exported.** Visible by default, grouped together after the
   Building columns (after Address on wayleaves). The project and stocklist CSV/xlsx exports
   are Tabulator `download()` calls, so new columns reach them with no export change. The
   wayleave tab has no export and gains none.

## Build

### Phase 1 — Building freeholder view (migration 117) ✅

`db/117_building_freeholder_view.sql` creates **`landregistry.vw_building_freeholder`** —
one row per building with a freehold title:

```sql
CREATE OR REPLACE VIEW landregistry.vw_building_freeholder AS
SELECT tm.os_topo_toid                                   AS toid,
       tm.freehold_title_no                              AS title_number,
       c.proprietor_name_1                               AS freeholder,
       nullif(trim(c.company_registration_no_1), '')     AS company_no
  FROM landregistry.toid_title_map tm
  LEFT JOIN landregistry.ccod c ON c.title_number = tm.freehold_title_no
 WHERE tm.freehold_title_no IS NOT NULL;
```

- **Keyed on the building, not the premise.** A premise's freeholder is its building's
  (`GLOSSARY.md`), and every consumer already holds the building's TOID — so the view does
  not re-read `basedata.abp`, and the analyser and Land Registry search can be moved onto it
  later on the same key.
- **It must stay filterable** — plain joins, `toid` from a real column, no aggregation and
  no `DISTINCT ON`. The one-row guard (decision 5) is the obvious thing to put in the view
  and the wrong one: a `DISTINCT ON (toid)` view joined to a 50k-row list is the
  `vw_premise_claimants` trap — Postgres cannot push a join condition through it and would
  compute all 25M buildings. So the guard lives in each reader as a `LATERAL … LIMIT 1`,
  which probes `toid_title_map_os_topo_toid_idx` and `ccod_title_number_idx` once per row.
- Guards: both tables exist, `ccod.company_registration_no_1` exists, and an index leads
  on each hop's join column (`toid_title_map.os_topo_toid`, `ccod.title_number`) — without
  them the readers' lateral is a scan per row.
- Verify block: the view's columns are exactly `toid, title_number, freeholder, company_no`,
  and a filtered read resolves. No data is inserted.
- *Tested 2026-10-21:* applied; returns company and non-company titles as designed; a
  filtered read plans as two index scans. Councils and overseas trusts carry no
  registration number (e.g. Camden, US Trust Company of Delaware), so a blank Company No.
  beside a named Freeholder is normal, not only for OCOD rows.

### Phase 2 — Project and stocklist premise lists ✅

Both endpoints gain the same lateral, beside their existing building join:

```sql
left join lateral (
    select f.title_number, f.freeholder, f.company_no
      from landregistry.vw_building_freeholder f
     where f.toid = <toid column>
     order by f.title_number
     limit 1
) fh on true
```

and select `fh.title_number, fh.freeholder, fh.company_no`.

- `www/fn/project_load.php` q4 (~line 230): `<toid column>` is `abp.toid_reference`.
- `www/fn/stocklist_load.php` q4 (~line 219): `<toid column>` is `a.cross_reference`.

Columns — after Building Premises in both tables, header-filterable like their neighbours:

```js
{title: "Title", field: "title_number", headerSort: true, headerFilter: "input"},
{title: "Freeholder", field: "freeholder", headerSort: true, headerFilter: "input"},
{title: "Company No.", field: "company_no", headerSort: true, headerFilter: "input"},
```

- `www/js/project_edit_v2.js` `premiseColumns` (~line 5175).
- `www/js/stocklist_edit.js` `#stocklist-premise-list` columns (~line 1738).

*Tested 2026-10-22:* all checks pass — columns, blanks, row counts and load time unchanged,
header filters, CSV/xlsx exports on both lists.

### Phase 3 — Wayleave Premises tab ✅

`www/fn/wayleave_premises_load.php` already joins `basedata.abp abp` for the address; add the
same lateral on `abp.cross_reference` and the three columns to the final `SELECT`. Columns
go after Address in `#wl-premises-tabulator` (`www/js/wayleave_edit_v3.js` ~line 733).

The Title shown here is the **building's** freehold title. A premise covered because a
title was linked to the wayleave can show a different title in this column — that is the
misalignment `docs/2026-10-21-wayleave-premise-title-link.md` investigates, and it is
expected until that plan lands.

*Tested 2026-10-22:* columns, blanks, row count and header filters pass. Title-sourced
premises appear only once approved — the tab answers what is covered — so a wayleave whose
title premises are pending shows no `title` Added method; that is the coverage model, not
this column. The SF660048 case raised here is recorded in the wayleave plan's findings.

## Testing checklist

1. Run migration 117; `SELECT * FROM landregistry.vw_building_freeholder LIMIT 5;` returns toid, title_number, freeholder, company_no.
2. Open a project with premises in company-owned buildings: Title, Freeholder and Company No. show after Building Premises.
3. A premise whose building has a title not in CCOD shows the title with Freeholder and Company No. blank.
4. A premise with no building title shows all three blank, with no label.
5. An overseas-company freeholder shows Title and Freeholder with Company No. blank.
6. The project premise row count is unchanged from before the migration.
7. The project Premises tab still loads in about the time it did, on the largest project you have.
8. Header filters on the three new columns filter the project list.
9. Project CSV and xlsx exports include the three columns.
10. Stocklist premise list shows the three columns after Building Premises, row count unchanged.
11. Stocklist CSV and xlsx exports include the three columns.
12. Wayleave Premises tab shows the three columns after Address, row count unchanged.
13. For one premise, the Freeholder matches the Strategic Map Analyser's freeholder for that building's area.

## Risks

- **Load time on large lists.** Three index probes per row on a ~50k-row project list. The
  lateral is the filterable shape; if test 7 regresses, check the plan for a sequential scan
  of `toid_title_map` before anything else.
- **Wayleave Title column can disagree with the linked title** — expected, documented above,
  owned by the wayleave plan.