# ADR-143 Hold Client and Brand in one master schema, read by every PWA ## Status Accepted, 2026-08-24. ## Status History ```yaml status_history: - date: 2026-08-24 status: Proposed changed_by: hkl reason: RentVeh needed a Client & Brand field; the question "which list?" had no answer - date: 2026-08-24 status: Accepted changed_by: hkl reason: "I am approving the whole alignment" — masters created, recce repointed, RentVeh built on them ``` ## Context RentVeh v6 needed a "Client & Brand" field sourced from existing data. There was no existing data to source it from — there were **eleven** of them. Measured on dev 2026-08-24 (`information_schema.columns` sweep, then value-level counts): | where "brand" lives | rows | actual values | |---|---|---| | `recce.brands.name` | 7 | Philips, Lenovo, HP, KFC, Epson, Liebherr, LIV PURE — **curated, with logos** | | `counters.counter_brand.brand_code` | 31 | `HP`, `LENOVO` — upper-case codes, not names | | `recce.submissions.brand` | 2 351 | free text; overwhelmingly synthetic (`OTHER_*`, `P410_*`) | | `sales.jobs.brand` | 27 | `Acme`, `SpecBrand` — synthetic | | `installation.tour_list.brand` | 27 | mirrors sales.jobs | | `btl.campaigns.brand` | 1 | `TestBrand` | | `production.items.brand` | 1 | `Lenovo` | Client is worse: `sales.jobs.client`, `sales.invoices.client_name`, `sales.offers.client_name`, `dispatch.campaigns.client`, `dispatch.dispatches.client`, `installation.jobs.client`, `production.orders.client`, `recce.submissions.client_name` — **eight free-text columns and no master at all**. `counters.client` looks like one (it has `client_id`, `name`, `brand_codes`) but its 5 182 rows are junk fixtures: the name column is literally `44` repeated. Two forces made now the moment to fix it rather than paper over it: 1. **hkl's rule, stated when asked which source RentVeh should use:** *"there should be only single source of truth for all PWAs even for Client and Brand. If it is not same then align it."* Adding a ninth free-text client column to `rentveh.expenses` would have contradicted the instruction that prompted the field. 2. **Production is empty.** `lm360_prod` holds `recce.brands` 7 rows, `recce.submissions` 4 rows, and **zero** rows in `sales.jobs`, `installation.jobs`, `counters.counter`, `sales.invoices` and `sales.offers`. The big numbers above are dev fixtures. This alignment will never be cheaper than it is today; every month of real data makes it more expensive. ## Decision Create a `master` schema holding **`master.brands`** and **`master.clients`**, expose it through PostgREST on both environments, and make it the only place a client or brand name is defined. - `master.brands` carries **both** identifiers the platform already uses — `brand_code` (upper-case, stable, what `counters` speaks) and `name` (display, what `recce` speaks) — plus `logo_url` and `sort_order` carried over from `recce.brands`, and `is_active` for soft delete. - **`recce.brands` becomes a VIEW over `master.brands`**, preserving its exact column list and integer `id`. Recce's code is not touched: it still `GET`s `/db/brands` and `POST`s new brands with `Accept-Profile: recce`, and INSTEAD OF triggers route those writes into the master. Recce's curated list *is* the master list — the same rows, one storage location. - `master.clients` is seeded **empty**, on purpose. Every candidate source is free text whose only dev values are `Acme` and 25 empty strings; a master seeded from that would make junk look authoritative. It self-seeds the way brands always have: naming a client that is not in the master inserts it, behind a confirm dialog. - New consumers read the master directly with `Accept-Profile: master`. RentVeh v6 is the first. **Decision Maker:** hkl ## Implementation Notes - **Migration:** `/var/www/360lm/migrate_master_v1.sql` — idempotent, applied to **both** `lm360` and `lm360_prod` on 2026-08-24. It seeds `master.brands` from `recce.brands` *preserving ids*, renames the old table to `recce.brands_pre_master_20260824` (kept as a backup, not dropped), then creates the view. - **Trigger functions are `SECURITY DEFINER`** with `SET search_path = master, pg_temp` — `recce.brands_view_insert/update/delete`. Without this they run as `web_anon`, which has no privileges on `master.brands`, and every recce brand write would 42501. See `feedback_pg_trigger_security_definer`. - **Soft delete:** recce's "remove brand" fires `INSTEAD OF DELETE` → `is_active = false`. A row another PWA points at is never physically removed. Re-adding the same name revives that row rather than colliding with the unique index. - **PostgREST:** `master` added to `PGRST_DB_SCHEMAS` on **both** services in `/root/360lm-web/docker-compose.yml` (lines 550 dev, 582 prod; backup `docker-compose.yml.bak-20260824-master`). Both containers were recreated. Forgetting either one makes the picker 404 with no other symptom. - **Key identifiers:** `master.brands`, `master.clients`, `master.brand_code_of(text)`, `recce.brands` (view), `recce.brands_pre_master_20260824` (backup table). - **Consumer side (RentVeh v6, `rentveh/index.html`):** `MASTER_RD` / `MASTER_WR` header constants, `loadMasters()`, `addClientBrand()`, `createMasterClient()`. Values are stored on `rentveh.expenses.client_brand` as text joined by `' · '`, not as a foreign key — see Alternatives. - **Consumers repointed 2026-08-24 (same day, hkl: "yes align production, dispatch, counters and sales too").** Each got the same self-contained loader appended before its closing `` — it fetches the master and, *only on success*, replaces what the page shows. If the master is unreachable the page keeps its previous list, so the change is never worse than not having it. Marker to grep for: `ADR-143: Client and Brand come from the shared`. | PWA | was | now | cache bumped | |---|---|---|---| | `production` `#new-client` | **hardcoded** `` Clients / Brands, `Other` kept | `360lm-prod-v6` → `v7` | | `dispatch` `#n_client` | **hardcoded**, same four names | master, same grouping, `+ Custom` kept | `360lm-dispatch-v11` → `v12` | | `sales` `#f-brand` | free text, no list | `list="f-brand-list"` filled from the master | `360sales-v22` → `v23` | | `counters` brand datalist | **derived** from `allCounters.flatMap(brand_codes)` — a brand nobody had used yet could not be offered | union of `master.brands.brand_code` and the derived codes, so `HP_INK` and friends survive | `counters-v39` → `v40` | Both `production` and `dispatch` were storing **brand names in a field labelled "Client"** — the exact vocabulary collapse this ADR exists to stop. The optgroups make the two visible as different things without renaming the field, which is a product decision, not this one's. Verified live on all four with Playwright: the selects render `["Clients","Brands"]` groups, sales' datalist carries the 7 brand names, `window.MASTER_BRAND_CODES` is populated in counters, and **zero page errors** on any of them. - **`sales.customers` is deliberately NOT merged into `master.clients`.** It is the *billing* entity (GSTIN, PAN, address, aliases) and `master.clients` is the *vocabulary*; they are different things that happen to share a noun. Its 16 dev rows are also not import-ready — they contain `Rr`, `Everything Else`, and near-duplicates (`Marketing vibes` vs `Marketing Vibes Agency`, `Park hospi` vs `Park Hospital Pvt Ltd`). Linking them needs a de-duplication pass and a key; that is follow-on work, not a side effect of this ADR. - 🚨 **A test polluted the master before anyone used it.** `rentveh-v6.spec.js` created `ZZ Spec Client ` to prove the insert path, and `web_anon` has no DELETE on `master.clients` — so its cleanup silently failed and the run added **one junk row every time**, seven before it was noticed in a picker screenshot. Fixed by giving the fixture a **fixed** name and accepting 201-or-409, so it can only ever create one row. The general rule: *a test that writes to a shared master must be idempotent, because it cannot clean up.* - **Find all sites still holding their own list:** `grep -rn "brand" /var/www/360lm --include="index.html" | grep -i "fetch\|/db/"` - **Behaviour change to know about:** re-adding an existing brand name through recce used to return HTTP 409 from the unique index; it now updates the existing row and returns 200. Recce guards against this client-side before POSTing, so it is reachable only in a race. ## Alternatives Considered - **Point RentVeh at `sales.jobs` via the linked tour.** Rejected: authoritative for *that trip*, but it inherits free text from a table with 25 empty-client rows, and it answers nothing for the other seven PWAs. It was the recommendation until hkl asked the broader question. - **Point RentVeh at `recce.brands` and leave client free text.** Rejected by hkl explicitly — it ships the feature but leaves the client side scattered, which is the problem itself. - **Replace `recce.brands` with `master.brands` and edit recce's code.** Rejected: editing a live PWA to ship a different PWA's feature. The view keeps recce byte-identical and was verified by round-tripping a real INSERT and DELETE through `/db/brands` with `Accept-Profile: recce`. - **Foreign keys from every consumer column to the master.** Rejected *for this migration*: `sales.jobs` holds 25 rows whose client is `''`, which no FK would accept. Normalising `''` → `NULL` is a data change with reporting consequences and belongs in its own step. - **Store `client_brand` on the expense as an id array instead of text.** Rejected: RentVeh is an offline-first single-file PWA with an IndexedDB mirror; a joined text column prints directly on the expense sheet and needs no second pull. Brand-level *reporting* should come from the job linkage (`tour_id` → `sales.tour_jobs` → `sales.jobs`), not from this display field. - **Do nothing.** Rejected: it was the status quo that produced eleven disagreeing lists. ## Consequences - Every PWA can now read one list. Brand curation stays where the users already do it (recce's Manage Brands screen), and it now writes to the master. - `rentveh.expenses.total_amount` now includes the new night-charges head. `sales.job_pnl` sums that column for non-draft rentveh rows, so **reported job cost will rise** for jobs with night charges. That is the arithmetic of a requested chargeable head, not a defect. - `production`, `dispatch`, `counters` and `sales` (brand half) now read the master at runtime. `btl` and `installation` have no picker at all — they receive text from elsewhere — and `sales`' client half still runs on `sales.customers`. Every consumer stores **text**, not a foreign key, so alignment is by convention rather than constraint; that is deliberate while `sales.jobs` still holds 25 empty-string clients that no FK would accept. - Counters' datalist is filled by an async fetch appended at the end of the document, so on a cold load the first render can miss the master codes and pick them up on the next one. Brand- code entry there is a rare admin action, so this was accepted rather than restructured. ## Revisit If - **A real client list arrives** (import from Tally, or the first live invoices). The self-seeding path is right for a handful of names typed by field staff; a bulk import needs de-duplication and probably a GSTIN or customer-id key, which `master.clients` does not yet have. - **`sales.jobs` starts carrying production rows.** At that point add the FK — and normalise the empty-string clients first, or the constraint will not build. - **A second brand attribute diverges** (a brand with two codes, or a client owning several brands). The current model is a flat list with no client↔brand relationship; that was correct for a 7-brand business and is the first thing to break if the business is not. - **Recce's brand screen changes shape** — it is the only writer that goes through the view, and the INSTEAD OF triggers encode its current contract (`{name}`-only POST, DELETE by name). ## Does NOT govern - **Which client/brand a given job belongs to.** That is `sales.jobs`, unchanged. - **Counter-level brand assignment** (`counters.counter.brand_codes`) — still counters' own data; this ADR only says where the *vocabulary* comes from. - **The RentVeh expense-sheet feature set** (night charges, multi-entry heads, the print layout). That is product behaviour, recorded in `MDD_rentveh.md` and `migrate_rentveh_v6.sql`. - **Authorisation.** `web_anon` grants are unchanged in shape: SELECT+INSERT on the masters, full CRUD on the recce view. Who may curate brands is still a UI-level question in recce.