# Opportunity Module Alignment — own schema, dynamic forms, dashboard - **Date:** 2026-08-04 - **Status:** All phases built — awaiting testing of the editor's remaining panels - **Status Date:** 2026-08-06 - **Phases:** 12 - **Phases Complete:** 12 built (0 ✅, migrations 024–030 ✅ applied, 6 ✅, 7 ✅, 8 ✅, 9 ✅, 10 ✅ — `.py` and `.qgz` both done, export confirmed working, 11 ✅ docs and backlog). **Still untested by Dave:** the journal and audit panel, attachments upload, cover image, the hierarchy panel, and record-level permissions with a second user. - **Notes:** Moves the opportunity module out of `prospector` into its own `opportunity` schema, rebuilt as a full mirror of the projects module — static + dynamic field meta, status management, history triggers, journal, attachments, hierarchy, admin and dashboard. The existing prospecting tools (project selection, routing, output summary, PDF export) are retained, only re-pointed. Scope decided by interview 2026-08-04; every decision below is Dave's, not an assumption. Phase 0 was run on dev 2026-08-05 and its results are recorded below. **Not in scope:** making Main Details a meta-driven tab (its own backlog item), and any change to the `prospector` network tables, which are project-planning data and stay exactly where they are. **Where it stands (2026-08-05):** all six migrations (024–029) applied to dev and verified. Phase 6 (backend) and Phase 7 (editor) are built but **not yet tested by Dave** — the editor should now render Main Details, the status badge, hierarchy, attachments and journal alongside the prospecting tools. Phases 8–11 remain: list page and route rename, admin/dashboard surfaces, the PDF export, and docs. ## Plan Phases 0. Pre-flight audit — what the dev database actually contains 1. Migration 024 — `opportunity` schema and status table 2. Migration 025 — entity tables, data move, views, rename originals 3. Migration 026 — field meta, value and history tables 4. Migration 027 — section, category and the six static fields 5. Migration 028 — history triggers (entity + EAV) 6. Backend PHP — config maps, new endpoints, re-pointed references 7. Editor frontend — Main Details, status badge, hierarchy, journal, attachments 8. List page and route rename 9. Admin and dashboard surfaces 10. PDF export — Python queries and the QGIS project 11. Docs and backlog ## Problem The opportunity module is the odd one out. Its four tables live in `prospector` — a schema whose other contents are the network planner's spatial data — and the entity table carries seven columns: ``` prospector.opportunity(opportunity_id, opportunity_name, opportunity_created_datetime, opportunity_created_user, opportunity_modified_datetime, opportunity_modified_user, geom, company_id) -- company_id added by migration 007 ``` There is no status, no manager, no parent, no account or stocklist link. There is no field meta table, so the module cannot have dynamic fields, cannot appear in Module Management, and cannot join the dashboard (`docs/improvement-opportunities.md` — "Bring opportunities onto the dashboard" is blocked on exactly this). The editor has no details form at all: `html_body_opportunity_edit.php` renders three hardcoded panels — Project Selection, Routing, Output — and `opportunity_edit.js`'s only call to `get_update_form.php` passes `?type=stocklist`, part of a copy-pasted `stocklistLoad()` that renders into a container the page does not have. Everything else in the module works and is worth keeping: project search and linking, pgRouting-based route calculation over the prospector network, the output summary, and the QGIS PDF export. ### What the module touches outside itself | Consumer | Reference | |---|---| | `global_functions.php:107` | `getItemCompanyId()` table map | | `global_search.php:71-74` | cross-module search | | `dashboard_load.php:625-628` | Recently opened | | `data_get.php:47` | `prospector.vw_opportunity_list` allowlist | | `map_get_v2.php:121` | `'prospector' => 'opportunities'` schema gate | | `file_upload.php:45` | entity map — points at `prospector.project_journal`, which does not exist | | `project_load.php:674-684`, `project_edit_v2.js:3100`, `stocklist_edit.js:2037` | "opportunities containing this project" panel | | `python/opportunity_export_pdf.py` | three queries against `prospector.opportunity*` | | `python/Opportunity_PDF_Export.qgz` | four layers bound to `prospector.vw_opportunity_*` | ## Decisions taken at interview (2026-08-04) | Decision | Choice | |---|---| | Naming | Full mirror — `opportunity.opportunities`, projects-shape audit columns (`created_datetime`, `created_user`, `modified_datetime`, `modified_user`) | | Originals | Renamed `*_migrated_20260804`, not dropped — a missed reference fails loudly rather than reading stale data | | New columns | `parent_opportunity_id`, `opportunity_status_id`, `opportunity_manager`, `account_id`, `stocklist_id`, `cover_image_url` | | Machinery | History tables + triggers, journal + audit panel, file uploads, hierarchy panel | | Editor layout | Main Details **hardcoded**, as projects has it today (`data-order="-2"`); the three existing tool panels get high `data-order` values so they always render last | | Static seed | Mirror migration 012 exactly — six fields, `stocklist_id` stays off-form as it is for projects | | Statuses | `Created` only; admin adds the rest | | Dashboard | Full `dashboardModuleMap()` member — count card, stage bar, flags, dashboard admin area | | List page | View **and** page rebuilt to the projects shape, including the `opportunity_modified_username` join fix | | Routes | List route renamed `?do=opportunity` → `?do=opportunities`; `?do=opportunityedit` unchanged | | `opportunity_manage.php` | Left as the multi-action endpoint, re-pointed only | | Permissions | Record-level tier added — `getItemPermission(..., 'opportunity', ...)` as projects has | | Backfill | `opportunity_manager` = creator. Nothing else | | Link tables | `opportunity_project_link` aligned to the audit shape; `opportunity_routes` moved as-is | | Table DDL | Explicit `CREATE TABLE` statements with their own sequences and starting values — **no `CREATE TABLE … (LIKE …)`**, no shared or inherited sequences | | `geom` | Column carried across, no UI and no derivation (see below) | | `cover_image_url` | Wired up as projects has it — `image_upload.php` extended, editor control added | | Access log | Write moves from `opportunity_manage.php` to `opportunity_load.php`, matching `project_load.php:63` | | Delivery | Migrations first, applied and verified before any PHP/JS lands | | Status ids | `Created` = 5, no sequence (`statusSave()` allocates `MAX(id) + 5`); the projects percentage convention is retired and not carried over | | Projects artefacts | Four are deliberately **not** copied — see below | | Extra FKs | None on `company_id` / `account_id` / `stocklist_id`, mirroring projects | | Orphan rows | The 19 links to non-existent projects are deleted by the copy and the FK added; the full original set survives in the `*_migrated_20260804` tables for restoration | | Migrations | Sequenced by concern | | PDF export | Dave re-points the `.qgz` in QGIS; the `.py` is done here | | Folded-in backlog | Dead `stocklistLoad()` deleted; per-page toasts retired for `glToast()` | Two consequences of the backfill decision, stated so they are not a surprise: existing rows land on `Created` **by construction** (the copy does not name a status column, so the default applies), and the audit panel is empty for historic opportunities until each is next edited. ### Four projects artefacts not copied The catalog dump showed these; all read as accidents, and this is the one chance not to inherit them (Dave, 2026-08-05): | In projects | In opportunity | |---|---| | `project_status` has both `project_status_pkey` (PK) and `project_status_un` (UNIQUE) on the same column — two identical indexes | PK only | | `project_field_sections` has a UNIQUE constraint but no primary key | primary key (FKs still resolve) | | `project_field_values_text_history`'s PK is named `…_unique` while its four siblings are `…_pk` | `…_pk` throughout | | `project_field_sub_category`'s sequence is still named `newtable_field_sub_category_id_seq` | named for its table | Also confirmed by the dump and by grep: `projects.project_file_uploads` is referenced by no PHP or JS at all. Uploads go to the shared `public.file_uploads` and are read through `.vw_file_uploads`. The opportunity module gets the view and no table. ### Why `geom` comes across unused No endpoint writes it. The original intent was to derive an opportunity boundary and publish it as a map layer, **parked for two reasons** (Dave, 2026-08-05) and worth recording so it is not rediscovered the hard way: 1. An opportunity can span a very large area, and a hull round it reads as though it *covers* a great many premises that are in no project within the opportunity. 2. Generating a hull good-looking enough to publish performed poorly. He plans to revisit it. The column therefore comes across as-is — no boundary tool, no derivation, no map layer — so that work starts from a column that already exists and has whatever history it has. ## Delivery sequence Migrations first, applied and verified before any PHP or JS lands (Dave, 2026-08-05), so a DDL mistake surfaces while a follow-up migration is still cheap rather than after eleven phases are built on top of it. 1. Phase 0's catalog dump → migrations 024, 025, 026 written and applied. Verify: row counts, `\d opportunity.*` against `\d projects.*`, sequences owned locally. 2. **One PHP change goes early, out of phase order** — the `opportunityname` autocomplete type in `fn/autocomplete.php` and `resolveAutocompleteLabel()`. Migration 027 seeds a `parent_opportunity_id` field that depends on it, and a seed applied before the code deploys leaves the parent field's autocomplete returning nothing. 3. Migrations 027 and 028 applied and verified. 4. Phases 6–11, the code, in order. An empty `opportunity` schema sitting in front of code that does not use it yet is inert: nothing routes to it, `fieldMetaModuleConfig()` does not name it, and the old tables are already renamed, so the module is simply down between step 1 and phase 6. That window is the cost of this ordering and is worth stating before it starts. ## Build ### Phase 0 — Pre-flight audit ✅ (run on dev 2026-08-05) Nothing is written in this phase. These answer questions the repo cannot — the baseline dump only covers migrations 000–005, so it is not evidence of the live shape of anything. **Volumes**, the baseline for the post-move count check: | Table | Rows | |---|---| | `prospector.opportunity` | 56 | | `prospector.opportunity_access_log` | 435 | | `prospector.opportunity_project_link` | 406 | | `prospector.opportunity_routes` | 12,199 | **Dependents:** none. No view outside `prospector` and no stored function references the four tables, so the five module views are the whole dependency graph. **Map layers:** two rows, and `public.map_layers` splits schema from table (`database_schema` / `database_table`), so only the schema value changes — the view names are already correct. | `layer_id` | `layer_name` | `database_table` | |---|---|---| | 45 | `opportunityRoutes` | `vw_opportunity_routes` | | 46 | `vw_opportunity_network_points` | `vw_opportunity_network_points` | **Record-level permissions:** `users.user_item_permissions` has no CHECK on `item_type` — PK, unique `(user_id, item_type, item_id)` and two FKs to `users.users`, nothing more. So `'opportunity'` needs no migration to become a valid item type. **Referential health:** zero opportunities with an unresolvable creator. `prospector.opportunity_routes` is clean on **both** columns — no route references a missing opportunity or a missing project (checked 2026-08-05, joining through the link table and directly), so both its foreign keys go on with no rows dropped. `opportunity_project_link` has **19 rows whose `project_id` is not in `projects.projects`** — resolved 2026-08-05 with `WHERE project_id NOT IN (SELECT project_id FROM projects.projects)`, which is the precise test; the `LEFT JOIN … IS NULL` pattern used first would have counted NULL `project_id` values the same way, and there are none (0 NULL on either column). All 19 also carry `link_active = false`, i.e. they were already removed from their opportunity: no active link is affected. The same test on the other column (`opportunity_id NOT IN (SELECT opportunity_id FROM prospector.opportunity)`) returns nothing, so the link table's `opportunity_id` FK goes on with no exclusions. **They are deleted by the copy** (Dave, 2026-08-05). A link whose project no longer exists has nothing to link back to, is unreachable from the front end — `vw_opportunity_projects` inner-joins `projects.projects` — and route recalculation is trivial if a project is ever re-added. No route rows are affected: the direct routes → projects check returned zero. **The one thing still outstanding — blocks migrations 024–026.** Explicit DDL means the live column, constraint and index shapes are needed; the baseline predates migrations 009 (autocomplete columns and their unique indexes), 018 (`history_action` on the value-history tables), 021 (`field_options_source`, section icons) and 022 (status PK, order, active, style). ```sql -- a. Columns SELECT table_name, ordinal_position, column_name, data_type, character_maximum_length, numeric_precision, numeric_scale, is_nullable, column_default FROM information_schema.columns WHERE table_schema = 'projects' ORDER BY table_name, ordinal_position; -- b. Constraints SELECT conrelid::regclass AS tbl, conname, pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid IN (SELECT oid FROM pg_class WHERE relnamespace = 'projects'::regnamespace) ORDER BY 1, 2; -- c. Indexes SELECT tablename, indexname, indexdef FROM pg_indexes WHERE schemaname = 'projects' ORDER BY tablename, indexname; ``` Referential health was settled separately (2026-08-05) and needs no re-running: ```sql -- the precise test — NOT IN on a non-nullable column, rather than LEFT JOIN … IS NULL, -- which would also count NULL project_id values as if they were broken references SELECT * FROM prospector.opportunity_project_link WHERE project_id NOT IN (SELECT project_id FROM projects.projects); -- 19 rows, all link_active = false ``` ### Phase 1 — Migration 024: schema and status table ✅ (applied 2026-08-05) ```sql CREATE SCHEMA IF NOT EXISTS opportunity; ``` `opportunity.opportunity_status` is written out as explicit DDL matching the live `projects.project_status` shape — the PK, `_display_order`, `_active` and `_style` columns migration 022 added — with its **own** sequence, its own starting value and its own `OWNED BY`. Nothing is inherited from or shared with the projects objects. Then seed `Created` and record its id; migration 025 uses that id as the `opportunity_status_id` column default, which is what makes it undeletable and undeactivatable via `statusDefaultId()`. **Every table in phases 1–3 follows the same rule** — explicit columns, explicit constraints, explicit indexes, its own sequence starting at 1, and foreign keys named after the module. The projects DDL is the reference for *shape*, never the source of the objects themselves. ### Phase 2 — Migration 025: entity tables, data move, views, rename originals ✅ (applied 2026-08-05) **Tables created**, each as explicit DDL mirroring the shape of its projects equivalent: - `opportunity.opportunities` — the full projects column set: `opportunity_id`, `parent_opportunity_id`, `opportunity_name`, `opportunity_status_id`, `opportunity_manager`, `geom`, `created_datetime`, `created_user`, `modified_datetime`, `modified_user`, `company_id`, `account_id`, `stocklist_id`, `cover_image_url` - `opportunity.opportunity_access_log` — as `projects.project_access_log` - `opportunity.opportunity_journal` — as `projects.project_journal` - `opportunity.opportunity_project_link` — `(id, opportunity_id, project_id, added_user, added_datetime, is_deleted)`, the `wayleave.agreement_stocklists` shape - `opportunity.opportunity_routes` — moved as-is (derived pgRouting output, recalculated wholesale, so attribution buys nothing, and no primary key for the same reason) Plus `opportunity.opportunities_update_modified_fields()` and its `BEFORE UPDATE` trigger, mirroring `projects.projects_update_modified_fields()` — it stamps `modified_datetime = now()` on every update. It belongs with the table rather than with the history triggers in 028, and migration 028's no-op suppression is written knowing this fires first. **Data move**, in one transaction, with the audit columns remapped and `opportunity_manager` backfilled from the creator: ```sql INSERT INTO opportunity.opportunities (opportunity_id, opportunity_name, opportunity_manager, geom, created_datetime, created_user, modified_datetime, modified_user, company_id) SELECT opportunity_id, opportunity_name, opportunity_created_user, geom, opportunity_created_datetime, opportunity_created_user, opportunity_modified_datetime, opportunity_modified_user, company_id FROM prospector.opportunity; ``` `opportunity_status_id` is deliberately unnamed so the `Created` default applies. `link_active` inverts to `is_deleted`, `link_date` becomes `added_datetime`, and `added_user` is NULL for historic links — the old table never recorded who linked. Every sequence is `setval`'d to `max(id)`, then row counts are compared against Phase 0 and the migration raises an exception on any mismatch. **Foreign keys.** `opportunity_routes` is clean on both columns, so all 12,199 rows copy and both its FKs go on unconditionally. `opportunity_project_link` copies 387 of its 406 rows — the 19 whose project no longer exists are excluded by `WHERE project_id IN (SELECT project_id FROM projects.projects)` on the insert, with the excluded count reported via `RAISE NOTICE` — and then takes its FK. Nothing is destroyed: all 406 links and all 12,199 route rows remain in the `*_migrated_20260804` tables and can be restored. The FKs match how every projects child table is already constrained (`FOREIGN KEY (project_id) REFERENCES projects.projects ON UPDATE RESTRICT`), which means a future manual project delete is blocked until its opportunity links go first — exactly as `project_field_values_*` already blocks it. **Map layer rows** (Phase 0): `UPDATE public.map_layers SET database_schema = 'opportunity' WHERE database_schema = 'prospector' AND database_table IN ('vw_opportunity_routes', 'vw_opportunity_network_points')` — keyed on names, not the `layer_id` values 45 and 46, which differ between environments. **Views**, all created in the `opportunity` schema: - `vw_opportunity_list` — **rebuilt**, not copied: the projects-list shape plus status desc/style, manager username, account name, parent name. The `um.username AS opportunity_modified_username` join moves from `created_user` to `modified_user`, closing that backlog item. - `vw_opportunity_projects`, `vw_opportunity_projects_output`, `vw_opportunity_network_points`, `vw_opportunity_routes` — re-pointed, logic unchanged. They keep reading the `prospector` network tables cross-schema; that is correct and intended. - `vw_parent_opportunity_links` — new, mirroring `projects.vw_parent_project_links`. Distance is `NULL::numeric`, as accounts and stocklists do — `renderHierarchyAlerts()` only shows distance for projects. - `vw_file_uploads` — new, mirroring `projects.vw_file_uploads` filtered to `entity = 'opportunity'`. Storage is the shared `public.file_uploads` table, which already has an `opportunity_id` column; **no per-module upload table is needed**. One deliberate difference: it also filters `file_is_deleted = false`, as wayleave's does and projects' does not. Only `wayleave_file_delete.php` ever sets that flag, so the projects omission is latent rather than live — but there is no reason to inherit it. Raised as a backlog item in Phase 11. `vw_opportunity_projects` exposes `added_datetime` where it used to expose `link_date`. The `.qgz` references `link_date` in three places, all of them QGIS per-field config blocks (``) rather than labels or expressions, so they are stale metadata for a field that no longer exists and are ignored — but it is one more thing to glance at when re-pointing that layer. **Then, last:** drop the five `prospector.vw_opportunity_*` views and rename the four tables to `*_migrated_20260804`. Any map-layer rows found by Phase 0 query 3 are updated here in the same migration. ### Phase 3 — Migration 026: field meta, value and history tables ✅ (applied 2026-08-05) Explicit DDL again, with `opportunity_id` as the entity column throughout, own sequences, and FKs to `opportunity.opportunities`: - `opportunity_field_sections`, `opportunity_field_category`, `opportunity_field_sub_category` (created even though the sub-category UI is hidden — `fieldMetaModuleConfig()` names it and `admin_save.php` still has its modes), `opportunity_fields`, `opportunity_field_dropdown_options` - `opportunity_field_values_{text,int,numeric,date,boolean}` and their five `_history` companions - `opportunities_history` — the `projects_history` shape, i.e. every entity column plus `history_id`, `history_action`, `history_datetime`, `history_user` This is the phase the catalog dump matters most for. None of the following are in the baseline, and all of them must be present: the autocomplete columns and their unique index on `field_form_id` (009), `field_options_source` and section icons (021), and `history_action` on the five value-history tables (018). ### Phase 4 — Migration 027: section, category and the six static fields ✅ (applied 2026-08-05) A `main_details` section and one category first (a brand-new module has none, and the 012 seed pattern looks both up by ref/order), then the six static rows mirroring migration 012 exactly: | `field_form_id` | data type | input | autocomplete | helper id | |---|---|---|---|---| | `opportunity_name` | text | input | — | — | | `account_id` | int | input | `accountname` | `account_name` | | `opportunity_manager` | int | input | `usernames` | `opportunity_manager_name` | | `company_id` | int | input | `companyname` | `company_name` | | `parent_opportunity_id` | int | input | `opportunityname` | `parent_opportunity_name` | | `opportunity_status_id` | int | dropdown | — (`field_options_source = 'opportunity_status'`) | — | All six are `field_type = 'static'`, so admin lists them read-only with a System badge and `admin_save.php` refuses to edit, toggle or delete them. Status carries a 6-column spacer so dynamic fields start on a fresh row, as projects does. The migration fails loudly if any form id or helper id is already claimed, mirroring 012's guard. `opportunityname` is a **new** autocomplete type — added to `fn/autocomplete.php` and `resolveAutocompleteLabel()` in Phase 6. The migration is written second but must not be applied before that code deploys, or the parent field renders an autocomplete that returns nothing. ### Phase 5 — Migration 028: history triggers ✅ (applied 2026-08-05) `public.fn_opportunities_history()` on `opportunity.opportunities`, plus one function and trigger per EAV value table, following migrations 017 and 018 exactly: `app.user_id` GUC for actor attribution with the row's `modified_user`/`record_user` as fallback, a malformed setting degrading rather than aborting the write, and no-op suppression (an UPDATE touching only `modified_datetime`/`modified_user` writes no history row; an EAV UPDATE leaving `value` unchanged writes none). Because history is trigger-written, `opportunity_save.php` writes no history itself — which also means the map/geometry save paths and `opportunity_create.php` are captured for free. ### Phase 6 — Backend PHP ✅ (2026-08-05, awaiting testing) **Migration 029 was needed.** 025 dropped `UNIQUE (opportunity_id, project_id)` — the original carried it as `opportunity_project_link_unique_1`, and `opportunityAddOne()`'s `ON CONFLICT` upsert cannot run without it. Root cause: 025's DDL was transcribed from the baseline's `CREATE TABLE` block, which lists columns only — constraints sit in a separate `ALTER TABLE` section hundreds of lines later — and the Phase 0 constraint query covered the `projects` schema, not `prospector`. No data harm: the constraint existed on the source, so no duplicate pair could have been copied. **Step 1 of this phase — the re-pointing sweep — is done** (2026-08-05), which brings the module back up after 025's rename: | File | Change | |---|---| | `opportunity_manage.php` | Tables re-pointed; `link_active = true` → `is_deleted = false` throughout; `link_date` → `added_datetime`; upsert now records `added_user`; removal sets `is_deleted` only, leaving `added_datetime` meaning what it says | | `opportunity_process.php` | Tables re-pointed; link predicates translated | | `opportunity_create.php` | Writes the new table and column names, sets `opportunity_manager`, omits status so the default applies | | `global_search.php`, `project_load.php`, `stocklist_load.php`, `dashboard_load.php` | Tables re-pointed, link predicates translated | | `data_get.php` | Allowlist entry → `opportunity.vw_opportunity_list` | | `map_get_v2.php` | `'opportunity' => 'opportunities'` added; `prospector` mapping untouched | | `global_functions.php` | `getItemCompanyId()` → `opportunity.opportunities` | | `opportunity_list.js` | Source view and the three renamed columns (full rebuild still to come in Phase 8) | | `python/opportunity_export_pdf.py` | Tables re-pointed **and** `get_project_details()` given explicit column names | `stocklist_load.php:576-578` was not in the touchpoint table at the top of this doc — it carries the same "opportunities containing this" panel as `project_load.php`. **The Python export had a positional-access trap.** `get_project_details()` did `select *` and the caller read `project[1]` as the opportunity name, which held only because `opportunity_name` was the second column of `prospector.opportunity`. In `opportunity.opportunities` that position is `parent_opportunity_id`, so every exported PDF would have been titled `None` — no error, just a wrong title. The query now names its columns. **All three opportunity endpoints now use the shared `$pdo`** instead of building their own connection. This was a backlog item, but migration 028 makes it load-bearing: `db.php:25` sets the `app.user_id` GUC that the history triggers read for attribution, and a self-built connection never has it — every write would have been attributed by fallback. **Step 2 — config maps, helpers and new endpoints — is also done** (2026-08-05): | Where | Change | |---|---| | `fieldMetaModuleConfig()` | `opportunities` entry — this one entry is what lights up Module Management, `get_update_form.php` and most of `admin_load.php`/`admin_save.php` | | `dashboardModuleMap()` | `opportunities` entry; `statusModuleConfig()` and `statusDefaultId()` derive from it, so the admin Statuses area needs nothing further | | `getAutocompleteLabelMap()` | `'opportunity'` meta table | | `buildAuditLogQuery()` | `$modules` entry plus `opportunityname` in `$autoMap` and `opportunity_status` in `$statusMap` | | `reservedSystemColumns()` | The fourteen entity columns, so no admin-created field can shadow one | | `get_update_form.php` | `?type=opportunity` — module gate, section/category/field/option queries, and the `opportunity_status` options source | | `file_upload.php` | Entity map points at the real journal | | `image_upload.php` | Entity-aware (see below) | **New endpoints**, all three using the shared `$pdo` and the two-tier permission check: - `opportunity_load.php` — static row, dynamic values, autocomplete labels, audit log, ancestors and descendants (both with cycle-guarding path arrays), file attachments, and the access-log write moved here from `opportunity_manage.php`. Also returns `permission`, so the editor can render read-only rather than offer a form the save endpoint will refuse. - `opportunity_save.php` — the `$staticFields` whitelist plus dynamic routing through the `check*` helpers. The five write blocks are driven from one spec array rather than pasted five times; boolean stays explicit for `normaliseBooleanFieldValue()` and `PDO::PARAM_BOOL`. No history is written — the triggers do it. - `opportunity_journal_save.php` — with a write-permission gate, which `project_journal_save.php` lacks entirely (it checks only that someone is logged in, so any user can post a note to any project). Not repeated here; raised in Phase 11. **The `check*Edit*` helpers are wrappers, not a fourth copy.** `metaFieldLookup()`, `metaFieldValueChanged()` and `metaStaticValueChanged()` in `global_functions.php` take their table names from `fieldMetaModuleConfig()`, and the three `checkOpportunityEdit*()` functions are one line each. The projects/accounts/stocklists copies are deliberately untouched — collapsing four modules' live save paths is its own change, and these are the target shape for it. `metaStaticValueChanged()` also shape-checks the interpolated column name, which the three existing copies do not. **`image_upload.php` is now entity-aware**, driven by a small map (`project` default so the existing project-editor call is unchanged, plus `opportunity`). Three fixes came with it: it sets `modified_user`/`modified_datetime` (the backlog item — it was the one write path to an entity table that omitted them, which would have mis-attributed every history row); it gained a write-permission gate, having previously had none beyond "logged in"; and its `cover_image_url` bind no longer passes `PDO::PARAM_INT` for a string path. `serve_image.php` needs no change — it validates path shape, not a per-entity allowlist. **`global_functions.php`** - `fieldMetaModuleConfig()` — an `opportunities` entry (schema `opportunity`, table `opportunity_fields`, value prefix `opportunity_field_values`, entity table `opportunities`, entity id `opportunity_id`, entity history `opportunities_history`). This one entry is what lights up Module Management, `get_update_form.php` and most of `admin_load.php`/`admin_save.php`. - `dashboardModuleMap()` — an `opportunities` entry. `statusModuleConfig()` and `statusDefaultId()` derive from it, so the admin Statuses area needs nothing further. - `getItemCompanyId()` — `'opportunity' => ['opportunity.opportunities', 'opportunity_id']`. - `resolveAutocompleteLabel()` — new `opportunityname` lookup; `getAutocompleteLabelMap()` — `'opportunity' => 'opportunity.opportunity_fields'` in its meta table map. - New save-side helpers `checkOpportunityEditField()`, `checkOpportunityEditCurrentValue()`, `checkOpportunityEditCurrentValueStatic()`. **These live in `global_functions.php`, defined once** — a helper defined in an endpoint and called from another is a fatal that only shows in the browser. **New endpoints**, mirroring their projects counterparts including the record-level permission tier (`getItemPermission()` + `permissionSatisfies()`, as `project_load.php:47` and `project_save.php:38`): - `opportunity_load.php` — static row from the load view, dynamic values, sections, categories, autocomplete labels, hierarchy, journal, audit log via `buildAuditLogQuery()`, file list from `opportunity.vw_file_uploads` - `opportunity_save.php` — `$staticFields` whitelist of the fourteen entity columns, then the dynamic routing through the `check*` helpers into the five typed value tables, including the numeric/boolean write blocks and `normaliseBooleanFieldValue()` fixed on 2026-08-04 - `opportunity_journal_save.php` **Re-pointed** (schema change only, no logic change): `opportunity_manage.php` (all eight actions, plus `link_active`→`is_deleted` in the add/remove SQL), `opportunity_process.php`, `opportunity_export_pdf.php`, `global_search.php`, `project_load.php`'s opportunities panel, `data_get.php` allowlist, `dashboard_load.php` recents. **`opportunity_create.php`** — writes the new table, sets `opportunity_manager` and lets status default. It also builds a second `new PDO(...)` from the same credentials; switch it to the shared `$pdo` while it is open, since the trigger attribution added in Phase 5 travels on the connection that set `app.user_id`. (Backlog item, but skipping it here would mean creates attributed wrongly from day one.) **Access log** — the insert moves out of `opportunity_manage.php:312` into `opportunity_load.php`, where projects writes it (`project_load.php:63`). Opening the editor logs access once, "Recently opened" means the same thing in every module, and nothing double-logs. **`image_upload.php`** — extended to accept the `opportunity` entity so `cover_image_url` is actually reachable. While in there, add `modified_user = $userID` to its UPDATE: it is the one write path to an entity table that omits it (open backlog item), and adding an unattributed write path to a module that has just gained history triggers would be a poor trade. **`file_upload.php`** — the `opportunity` entity maps to `prospector.project_journal`, a table that does not exist. Point it at `opportunity.opportunity_journal`. This is copy-paste prep rather than a live defect and needs no separate cleanup item: the map is used only for the "User uploaded file: …" journal note (`file_upload.php:295-309`), that insert sits in a `try/catch` which writes to `error_log` and carries on, and the module has no upload UI today. The file itself would always have stored correctly in the shared `public.file_uploads`. **`map_get_v2.php`** — add `'opportunity' => 'opportunities'`. The `prospector` mapping is left alone: its network tables are project-planning data and this work does not touch them. ### Phase 7 — Editor frontend ✅ (2026-08-05, awaiting testing) **The form pipeline went into `js/main.js`, not into a fourth editor copy.** `renderMetaForm()` and `populateMetaForm()` are the sections → categories → fields → options → spacers → populate pipeline that `project_edit_v2.js`, `account_edit_v2.js` and `stocklist_edit.js` each carry privately. The opportunity editor uses the shared pair; the other three are untouched. This is now the implementation the "shared `renderMetaFields()`" backlog item should adopt rather than writing a fifth. Everything learned in the 2026-08-04 fixes is folded in: every input type renders, an unrecognised one degrades to a text input rather than vanishing, checkboxes get their hidden `0` companion, and `fieldValueIsTrue()` handles the four spellings a PostgreSQL boolean arrives as. It also fixes one fragility while passing: `project_edit_v2.js` relies on the template hardcoding `
`, which only holds while that section's id really is 1 — and section ids differ between environments. `renderMetaForm()` appends the container to a hardcoded pane when it is missing, so nothing depends on the number. **`html_body_opportunity_edit.php`** — Main Details pane and nav button (hardcoded, as projects has it, `data-order="-1"`); the three tool buttons given unique ids and `data-order` 1006/1007/1008, so admin-defined sections sort in front of them; new Attachments (1010) and Journal (1011) tabs; a status badge in the header; the cover-image drop zone; and the three per-page toast blocks deleted. **The old nav markup was worse than it looked.** All three buttons carried `id="headingNav-main_details" data-order="-2"` — a copy-paste that gave the page three duplicate ids and, because that id is exactly what suppresses a meta-rendered section button, would have suppressed Main Details three times over once the meta existed. **`opportunity_edit.js`** — the ~1,270-line copied stocklist form pipeline and the dead `stocklistLoad()` / `stocklistSave()` functions are gone, replaced by `opportunityLoad()`, `opportunitySave()` and `opportunityJournalSave()` against the new endpoints, plus `renderHierarchyPanel()`, `initStatusBadge()`, the audit-log Tabulator and `glToast()`. The file went from 2,963 lines to about 1,490. A read-only user now gets the form with the save button hidden and the inputs disabled, driven by the `permission` value `opportunity_load.php` returns. **`routes.php`** — `js/project_edit_v2_fileuploads.js` added to `opportunityedit`. **Verified with `node --check`**, which parses both files cleanly. Worth knowing generally: Node is installed on the editing machine, so JS gets a real syntax check even though PHP cannot. Brace counting is unreliable here anyway — the committed `opportunity_edit.js` was already `+2` skewed by braces inside string literals. **`html_body_opportunity_edit.php`** - A hardcoded Main Details nav button and pane at `data-order="-2"`, matching `html_body_projectedit.php`. The pane holds only the entity-id hidden input; the six statics render from meta. - The three existing buttons currently all carry `id="headingNav-main_details" data-order="-2"` (copy-paste). Give them unique ids and high orders — Project Selection 1006, Routing 1007, Output 1008 — so admin-defined sections sort in front of them, which is what `project_edit_v2.js`'s `data-order` sort does. - Journal (1011) and Attachments (1010) buttons and panes, matching the projects ids. - A status badge in the header bar, styled from the status's style token. - The cover-image control, as the projects editor header has it. - Delete `#loadingToast` / `#successToast` / `#errorToast`. **`opportunity_edit.js`** - Delete the copied `stocklistLoad()` and its `#child-stocklists` render (dead — the container does not exist); the real hierarchy comes from `renderHierarchyAlerts()`. - `opportunityLoad()` posting to `opportunity_load.php`; the field render loop ported from `project_edit_v2.js` **as it stands after the 2026-08-04 fixes** — every input type rendering, defaulting to a text input rather than nothing, the hidden `0` companion before each checkbox, and `fieldValueIsTrue()` on populate. - Save through `opportunity_save.php`; `initAutocompleteFields()` for the four autocomplete pairs; audit-log Tabulator; journal panel. - Replace the local toast functions with `glToast()`. **`routes.php`** — add `js/project_edit_v2_fileuploads.js` to `opportunityedit`. ### What testing caught (2026-08-05/06) — four faults worth recording **1. `public.map_layers` stores the relation three times, and 025 fixed two of them.** `database_schema`, `database_table`, *and* again inside the `url` string (`fn/map_get_v2.php?geotable=prospector.vw_opportunity_routes&…`). The `url` is what the editor fetches; the columns are read separately by `map_layer_manager.php:202-203` on the save path. 025 updated the columns and never looked at the url, so both layers stayed registered, enabled and permanently empty — `map_get_v2.php:76` answered "Invalid table name." with nothing on screen to say so. Fixed by **migration 030**. The Phase 0 audit query selected the two columns whose names matched what was expected instead of the whole row, which is why the third copy was never seen. **2. 025's map-layer UPDATE matched zero rows and reported success.** It was keyed on `database_schema = 'prospector' AND database_table IN (…)`. Every extra condition in a repair statement is another way to match nothing in silence; 030 keys on the table name alone and raises if it changes nothing or leaves anything stale. **Any migration that repairs data should assert its own row count.** **3. A comment inside a double-quoted PHP SQL string took the module down with a parse error.** `opportunity_manage.php:628` had `/* …it means "when this project was added"… */` inside `$q = "…"`; the first inner quote closed the string. HTTP 500, and because the file never compiled, none of its try/catch mattered. Balance counting cannot see this and neither can counting quotes (two inside a string keep the file even). Rule: no double quotes in comments placed inside a PHP string — put the prose on a line above it. **4. The rebuild deleted the editor's map code.** Lines 27–1294 of the old `opportunity_edit.js` were read as one dead unit, but the file's own `// Map Code Start` marker at line 247 divides them: 27–244 was the copied stocklist form pipeline, 245–1294 was the OpenLayers setup, popup overlay and DB layer loading, plus the two init calls (`stocklistPremisesLoad`, `opportunityGetOutput`) that populate the selected-projects list and the output summary. All restored. **Read to the end of a block before deleting it, including the comment markers the original author left.** ### The editor layout — decided at testing, not at interview The panes live in a `col-md-6` beside a `col-md-6` map, which the interview never covered, so the new tabs first shipped at half width. Dave's call (2026-08-05): **the map stays beside Project Selection / Routing / Output, and Main Details / Attachments / Journal are full width**, matching the other editors. `applyOpportunityPaneLayout()` switches the pane column between `col-md-6` and `col-12` and shows or hides `#opportunity-map-col`. Two consequences that are easy to get wrong: - OpenLayers measures its container when it renders, so a map built inside a hidden column has zero size — **and `createLayersAndControls()`'s `focusOnLoad` fit then fits to that**, leaving a nonsense centre and zoom with every feature off-screen. It looks exactly like "the layers aren't loading". `updateSize()` restores the size but does not undo the bad fit, so `fitOpportunityMapToData()` re-fits on first reveal, to the combined extent of every loaded vector layer, with spaced retries because the DB layers arrive asynchronously. - The save button belongs in the header (`#opportunityEditSaveButton`, mirroring `html_body_projectedit.php:28`), hidden on the tool tabs, not at the foot of the form. ### Ordering hazard hit on 2026-08-05: the dashboard broke immediately Adding the `opportunities` entry to `dashboardModuleMap()` in Phase 6 is what makes `dashboard_load.php` *walk* the module — but its five small per-module maps still had three entries each, and those were listed as Phase 9 work. The result was `Undefined array key "opportunities"` at `dashboardNameColumn()`, and because a PHP warning prints into the response body it broke the JSON and took the whole dashboard down. Fixed by populating all five (`dashboardMineSql`, `dashboardNameColumn`, `dashboardHref`, `dashboardModuleRecent`, `dashboardModuleListHref`) and by making `dashboardNameColumn()` return null on an unmapped module instead of warning, so the next module added to the map degrades instead of taking the page with it. **The lesson for the remaining phases:** `dashboardModuleMap()` and `fieldMetaModuleConfig()` are not inert registrations — they switch consumers on. Anything keyed off them has to land in the same change, not a later phase. ### Phase 8 — List page and route rename `opportunity_list.js` gains the projects-list column set and filters (status badge, manager, account, company, modified) against the rebuilt view, and its `data_get.php` source becomes `opportunity.vw_opportunity_list`. The route key `opportunity` becomes `opportunities` in `routes.php`; five references follow it — `html_body_opportunity_edit.php:28`, `nav_main_html.php:27`, `nav_opportunity_edit_html.php:25`, `project_edit_v2.js:3100`, `stocklist_edit.js:2037`. No alias is left behind; an old bookmark 404s, which is the same call made for wayleave. ### Phase 9 — Admin and dashboard surfaces `html_body_admin_fields.php` — `opportunities` into `$allowedModules`, `$moduleLabels` and `$moduleIcons` (`fa-lightbulb`). `admin_fields.js` needs no per-module branching for it: the pass-to-stocklist/project toggles are account/stocklist-specific and the status area is driven by `statusModuleConfig()`, which now returns a config rather than null. `?do=admin`'s module × area matrix picks the module up from `fieldMetaModuleConfig()`. `dashboard_load.php` — five small per-module maps alongside the `dashboardModuleMap()` entry: manager column (`opportunity_manager`), name column (`opportunity_name`), edit URL (`?do=opportunityedit&opportunity=`), access-log tuple, and the card list. The config tables (`dashboard_config`, `dashboard_inactive_statuses`, `dashboard_flag_config`) are keyed by module string with absent rows meaning defaults, so **no migration is needed for the dashboard** — which is why this plan has five migrations, not six. With only `Created` seeded, the stage bar shows one stage and no flag has a date field to fire on until an admin configures one. That is correct behaviour, not a defect. ### Phase 10 — PDF export `python/opportunity_export_pdf.py` — three queries re-pointed (`prospector.opportunity`, `prospector.opportunity_routes`, `prospector.opportunity_project_link`). `python/Opportunity_PDF_Export.qgz` — **Dave, in QGIS, once every new table and view is in place** (agreed 2026-08-05; the export is expected to fail in the interim). Four layers to re-point, all `prospector` → `opportunity`: - `vw_opportunity_projects` - `vw_opportunity_projects_output` - `vw_opportunity_network_points` - `vw_opportunity_routes` Three of them are inside embedded `SELECT` subqueries rather than plain table bindings, so each layer's data source needs opening rather than just its name changing. ### Phase 11 — Docs and backlog `CLAUDE.md` — route table (`opportunities`, `opportunityedit`), the schema list, and the Field Meta System section, which currently names four modules. `docs/improvement-opportunities.md` — tick, with dates: the `vw_opportunity_list` modified-username bug, the dead hierarchy render, the opportunity editor's per-page toasts, `image_upload.php` not setting `modified_user`, and "Bring opportunities onto the dashboard". Raise as new items: - The `prospector` → `opportunities` map gate — the planner's network layers are project data gated behind an opportunities permission. - **`main.js` now has eight near-identical autocomplete binder blocks.** Six of them (`companyname`, `accountname`, `projectname`, `stocklistname`, `opportunityname`, `wayleavename`) differ only in the selector — each already reads its type from `$(this.element).data('autocomplete')`, so one binder with a combined selector would do. Adding a ninth copy is the wrong answer next time. Deliberately not folded into this work: `main.js` loads on every page, so collapsing six live code paths wants to be its own change with its own testing pass, not a rider on a module rebuild. - `opportunity_manage.php` remains a multi-action endpoint, against the one-file-per- endpoint convention. - **`projects` / `accounts` / `stocklists` `vw_file_uploads` do not filter `file_is_deleted`.** Latent today because only `wayleave_file_delete.php` sets the flag, but it becomes live the moment a delete path is added to the shared upload panel. - The four `prospector.*_migrated_20260804` tables can be dropped once the module has been exercised for a while. - **`total_premises` counts 1 for an opportunity with no projects.** In both `vw_opportunity_list` and `vw_opportunity_projects_output` the premises count is `count(*)` over a chain of LEFT JOINs, so an opportunity with no links still produces one row and counts it — while `mdu_premises` / `sdu_premises` / `other_premises` correctly come back 0, because their `CASE` sees a NULL building type. `count(a.uprn)` would be right. Carried across verbatim rather than fixed in the migration: it changes a number people may have been reading, so it wants its own change and its own look. ## Testing checklist Schema and data - [ ] Row counts: 56 opportunities, 435 access-log rows, 12,199 routes, 387 links (406 less the 19 deleted) - [ ] Every opportunity has `Created` status and a manager equal to its creator - [ ] `prospector.*_migrated_20260804` tables hold the **full** original sets — 406 links, 12,199 routes — so anything dropped can be restored - [ ] Nothing reads the renamed tables (server log clean after a day's use) - [ ] `\d opportunity.*` vs `\d projects.*` — same columns, types and indexes per table; every sequence is owned by an `opportunity.*` table and starts from its own value Editor - [ ] Main Details renders all six statics; each saves and reloads - [ ] All four autocompletes resolve stored IDs back to labels on load - [ ] Dynamic fields of each type — text, int, numeric, date, boolean, dropdown, textarea — create in admin, render, save, and reload with the right value - [ ] Unticking a checkbox persists as false - [ ] Status badge shows the right label and style; changing status updates it - [ ] Parent set → hierarchy alert on both parent and child - [ ] Journal note saves; audit log shows static and dynamic changes with the right user - [ ] File upload, list and download; the upload writes a journal note. (No delete — only wayleave has a file-delete path; the shared panel offers none.) - [ ] Cover image uploads, displays, and the upload sets `modified_user` - [ ] Opening the editor writes exactly one access-log row, and Recently opened shows it - [ ] Project selection, routing and output summary all still work end to end - [ ] No `#successToast` markup left; `glToast()` fires on save and on error Permissions - [ ] Read-only module permission: form renders, save refused - [ ] Record-level override grants write to a single opportunity - [ ] Non-main-company user sees only their company's opportunities in list, search and map List, admin, dashboard - [ ] List shows status, manager, account, company; **modified-by shows the modifier** - [ ] `?do=opportunity` is gone and every in-app link goes to `?do=opportunities` - [ ] Module Management → Opportunities: sections, categories, fields, statuses, dashboard - [ ] A status in use cannot be deleted; `Created` can be neither deleted nor deactivated - [ ] Dashboard card, stage bar and Recently opened all show opportunities - [ ] Global search finds opportunities Export - [ ] PDF export produces the same document as before the move ## Risks - **The PDF export is broken between applying migration 025 and re-pointing the `.qgz`.** The QGIS project binds views by name and no migration can reach it. Re-point it in the same session as the migration, or expect a failing export in between. - **The module is down between applying migration 025 and deploying the re-pointed endpoints.** The originals are renamed, so `opportunity_manage.php`, `opportunity_process.php`, `opportunity_create.php` and the list page all error until Phase 6 lands. That is inherent to migrations-first; the shortest version is to apply the migrations and deploy the re-pointed endpoints in the same sitting, leaving only the new editor surfaces to follow. - **Map layer rows are data, not code.** Phase 0 found the two that matter (layers 45 and 46) and migration 025 updates them, but if either is missed the editor map goes blank with no error in the repo to find. - **The new DDL is written from a catalog dump, not from the baseline.** If the dump is incomplete, a missing column surfaces as a runtime SQL error in admin or the editor rather than at migration time. The `\d` sweep in the checklist compares the finished schema against projects table by table. - **Ordering between migration 027 and the Phase 6 code.** The `opportunityname` autocomplete type must exist in PHP before the parent field is seeded, or the field renders an autocomplete that returns nothing. - **Renamed originals are deliberately load-bearing.** Anything still reading `prospector.opportunity` errors rather than returning stale rows. That is the point, but it means a missed reference is a visible break, not a slow drift — check the server log after the first day of use. - **`opportunity_manage.php` keeps its `link_active` semantics inverted into `is_deleted`.** Four of its actions and every view filter on it; an inversion missed in one place shows as links that reappear after removal.