# ADR-127 Counter-to-Job Is an Effective-Dated Many-to-Many Junction; `installation.counters` Is Rebuilt as That Bridge Over the `counters.counter` Master ## Status Accepted, 2026-08-02 (signed off by hkl). Originally Proposed 2026-08-01. ## Status History ```yaml status_history: - date: 2026-08-01 status: Proposed changed_by: hkl (via DL laptop Claude Code session, Opus 4.8, Phase 0 of the Tour-Job build plan) reason: | The frozen Tour-Job architecture decision (2026-08-01) established that a counter/outlet relates to a Job as a true many-to-many WITH a time dimension — a non-exclusive outlet carries several brands (many concurrent jobs) at once, and an exclusive outlet gets a new job each campaign cycle (a time series of jobs). The dormant `installation.counters` table currently models a 1:1 (single `job_id NOT NULL`), which cannot express either case. Because the table is empty (0 rows, no live writer), this is the moment to rebuild it into the correct bridge shape defined by ADR-126, before it ever gets populated. This ADR fixes only the Postgres schema shape + grants — the Installation PWA is IndexedDB + Apps Script today and reads none of these Postgres tables, so there is deliberately no UI build here. changed_via: adr-kit (360lm), authored directly per hkl's explicit instruction + the frozen Tour-Job architecture decision (2026-08-01). - date: 2026-08-01 status: Proposed changed_by: hkl (via DL debt-cleanup plan, chunk D2, executed on Sonnet) reason: | Execution amendment — added `installation.counters.install_status TEXT CHECK (ready, in_transit, installed, delayed, skipped)` as a column distinct from this ADR's own link-lifecycle `status` (active/paused/completed): the install-workflow vocabulary is a different concept from bridge-link lifecycle and must not be forced into the same column. `tour-planner/index.html`'s `updateCounterStatus()` now PATCHes `install_status`, keyed by `(counter_id, job_id)`. No other schema change; `status` semantics from the original decision stand unchanged. - date: 2026-08-02 status: Accepted changed_by: hkl (via DL laptop Claude Code session) reason: | Signed off by hkl. The full Tour-Job build (all 18 chunks of BUILD_PLAN.md) was implemented and Playwright-verified end-to-end on dev (lm360), including the cross-PWA regression (chunk 4C, 7/7). hkl confirmed the built result and moved on to downstream work (ADR-128/130, Job-P&L, ImageBinding); the status header was never flipped at build time and is corrected to Accepted here. Decision text unchanged; per adr-coding-rules.md this ADR is now locked and may only be superseded, not edited. changed_via: adr-kit (360lm), status-only correction per hkl's explicit instruction. ``` ## Context **What a counter's relationship to a job really is (hkl, 2026-08-01):** - Outlets are **non-exclusive / multi-brand**: one physical counter sells for several brands at the same time, i.e. participates in **multiple concurrent jobs**. - Exclusive outlets get a **fresh job each campaign cycle**, i.e. the same counter has a **time series** of jobs, only one (or a few) active at any instant. Either way, "one counter, one job" is wrong. The relationship is many-to-many with a time dimension — precisely the shape ADR-126 defines. **Current dormant shape (to be replaced):** - `installation.counters` — PK `counter_id`, single `job_id NOT NULL REFERENCES installation.jobs`. **Dormant: 0 rows, no live writer.** It hard-codes the 1:1 this ADR removes, and it points at `installation.jobs`'s *independent* job identity, which ADR-126 §2 collapses. - `installation.jobs` — dormant (0 rows); ADR-126 §2 unifies its identity onto `sales.jobs`. **The real, live multiplicity already lives elsewhere.** `counters.counter` (PK `counter_id`, format `cntr--`; `brand_codes text[]`, address, GPS, GSTIN) is the **1208-row outlet master**. `counters.allocation` (31 rows) and `recce.submissions` (1339 rows) already key on `(counter_id, job_id)` and already support a counter serving multiple jobs. This ADR extends that established, live pattern into the (currently wrong) `installation.counters` table — it is not a new idea, it is aligning a dormant table with what the live tables already do. **Why now, and why schema-only.** The table is empty, so rebuilding it is near-zero risk and far cheaper than after it fills. But the Installation PWA does not read these Postgres tables at all (IndexedDB + Apps Script), so there is **no UI to change** — building UI now would be speculative. This ADR lays the correct shape and grants so that whenever Installation migrates onto Postgres, the target is already right. ## Decision ### 1. Rebuild `installation.counters` as a Counter-to-Job bridge (ADR-126 shape) Replace the dormant 1:1 table with an effective-dated many-to-many bridge, per ADR-126 §1: ```sql DROP TABLE IF EXISTS installation.counters; -- dormant, 0 rows CREATE TABLE installation.counters ( id BIGSERIAL PRIMARY KEY, counter_id TEXT NOT NULL REFERENCES counters.counter(counter_id), job_id TEXT NOT NULL REFERENCES sales.jobs(job_id), status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active','paused','completed')), start_date DATE, -- when this counter started serving the job end_date DATE, -- NULL = currently active; populated = historical added_by TEXT NOT NULL, added_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE (counter_id, job_id) -- one active pairing per (counter, job); a new cycle = new job_id ); ``` Key points: - **`counter_id` references the real master `counters.counter`**, not the dormant `installation.jobs` chain. This aligns `installation.counters` with `counters.allocation` and `recce.submissions`, which already key on the master + job. - **`job_id` references `sales.jobs`** (the single identity, ADR-126 §2) — never a per-schema id. - **Concurrency** (multi-brand outlet) is expressed by multiple `active` rows for the same `counter_id` with different `job_id`s. - **History** (exclusive outlet, new job per cycle) is expressed by closing the prior row's `end_date` and inserting the new cycle's row; a re-run is a new `job_id`, so `UNIQUE(counter_id, job_id)` does not obstruct it. Per ADR-126, relax to `UNIQUE(counter_id, job_id, start_date)` only if a genuine same-pair-across-cycles need appears — deferred, not needed for v1. ### 2. Grants (ADR-106) `installation.counters` gets explicit PostgREST read/write grants scoped to the role(s) that will eventually consume it, per ADR-106 (cross-schema access is grant-gated). Exact grant statements are build-time detail (build chunk 1B); no grant is implicit. Because the table now references both `counters.counter` and `sales.jobs` across schema boundaries, the consuming role needs the usual cross-schema read grants ADR-106 governs. ### 3. Explicitly out of scope: no Installation UI build The Installation PWA is IndexedDB + Apps Script and reads **none** of these Postgres tables today. This ADR changes only the Postgres schema + grants. No PWA HTML changes; no `CACHE_VER` bump; no Playwright UI verification (there is no UI touching this). Verification is DB-level only (§Verify in the build plan chunk 1B): `\d installation.counters` matches the shape above, and a synthetic insert of two different jobs for the same counter succeeds (proving M:N), then the test rows are deleted. ## Implementation Notes - Applied in build-plan **chunk 1B** (`[Sonnet]`, mechanical migration authored from this ADR). - Depends on ADR-126 §2 (job-identity unification, chunk 1A) so that `job_id REFERENCES sales.jobs` is the only job reference — chunk 1A lands first or together. - Document the rebuilt shape in `dbt_installation.md` (the installation design notes), including that `installation.counters` is now a bridge over `counters.counter`, not a 1:1 to `installation.jobs`. - The 1208-row `counters.counter` master is **not** modified. `counters.allocation` / `recce.submissions` are **not** modified — they already have the right shape; this ADR only brings the dormant `installation.counters` into line with them. ## Alternatives Considered - **Keep `installation.counters` as 1:1 (single `job_id`) and model multiplicity only in `counters.allocation`.** Rejected. It leaves a dormant table hard-coding a relationship the business does not have (multi-brand outlets, per-cycle re-jobs), guaranteeing a wrong target the day Installation migrates onto Postgres. Fixing it while empty is nearly free; fixing it later means migrating real rows. - **Point the bridge's `counter_id` at `installation.jobs`/its own counter identity instead of the `counters.counter` master.** Rejected. It would create a second counter identity in parallel to the 1208-row master — the same duplicate-identity mistake ADR-126 §2 removes for jobs. The master is `counters.counter`; the bridge references it, matching `counters.allocation` / `recce.submissions`. - **Build the Installation PWA UI onto the new bridge now.** Rejected. Installation is IndexedDB + Apps Script and reads none of these Postgres tables; UI work now is speculative and untestable against a real consumer. Lay the schema now, build UI only when Installation actually migrates. - **A boolean `is_active` instead of `start_date`/`end_date`.** Rejected for the same reason as in ADR-126: a boolean cannot answer point-in-time "which jobs did this counter serve as of date D", which the per-cycle-job case needs. ## Consequences **Positive:** - The dormant `installation.counters` now correctly models both real cases — multi-brand concurrency and per-cycle history — before it is ever populated, at near-zero data risk. - It aligns with the already-live `counters.allocation` / `recce.submissions` `(counter_id, job_id)` pattern and with ADR-126, so the whole platform models counter<->job the same way. - References the single job identity (`sales.jobs`) and the single counter master (`counters.counter`) — no duplicate identities. **Negative / Trade-offs:** - A dormant table is rebuilt with no immediate consumer — effort spent ahead of use. Accepted because doing it while empty is far cheaper than after Installation migrates, and it removes a known-wrong shape from the schema now. - Point-in-time correctness is a query-time convention (the `end_date` filter), not a constraint — same trade-off ADR-126 documents. **Risks and mitigations:** | Risk | Mitigation | |---|---| | The rebuilt table diverges from `counters.allocation`/`recce.submissions` conventions | This ADR explicitly aligns to their `(counter_id, job_id)` keying; review any future change against all three together | | Installation later migrates and a reader forgets the effective-date filter | Document the `end_date IS NULL` active-filter in `dbt_installation.md`; carry ADR-126's call-site convention | | Dropping/recreating the dormant table accidentally hits a non-empty prod copy | Guarded: table is 0 rows on dev; build chunk 1B is dev-only and verifies row count = 0 before DROP; no prod cutover without explicit hkl go-ahead | ## Related Decisions - **ADR-126** — Effective-dated many-to-many bridges + unified job identity. This ADR is a direct consumer of ADR-126 §1 (the bridge shape) and depends on §2 (job-identity unification). - **ADR-111** — Unified Job Record in `sales.jobs`. `job_id` here references `sales.jobs`; ADR-111 is untouched. - **ADR-125** (revised 2026-08-01) — Tour<->Job true many-to-many (`sales.tour_jobs`). The sibling application of ADR-126's bridge pattern to Tours; built in the same plan as this counter junction. - **ADR-053** — Per-Brand Counter Data Lives on `recce.submissions`, Not on `counters.counter`. Context for why brand/job-specific counter data is keyed on `(counter_id, job_id)` tables rather than the master; this ADR's bridge follows the same principle. - **ADR-106** — Cross-Schema Access Is Grant-Gated. Governs `installation.counters`' grants, which now span `counters.*` and `sales.jobs`. - **ADR-009** — Each PWA Owns a Dedicated PostgreSQL Schema. The bridge lives in `installation` but references masters in `counters` and `sales` via ADR-106 grants. ## References - `installation.counters`, `installation.jobs`, `counters.counter`, `counters.allocation`, `recce.submissions` schemas and row counts (live `lm360` dev DB, psql 2026-08-01): `installation.counters`=0, `installation.jobs`=0, `counters.counter`=1208, `counters.allocation`=31, `recce.submissions`=1339 — confirming the installation side is dormant/greenfield and the multiplicity already lives in `counters.*`/`recce.*`. - Frozen Tour-Job architecture decision (hkl, DL session, 2026-08-01): outlets are non-exclusive/multi-brand; exclusive outlets get a new job each cycle. Master build plan (`C:\Users\Lenovo\Documents\tour-job-architecture\BUILD_PLAN.md`, sections 1, 2, and chunk 1B). - Salesforce `CampaignMember` / Kimball bridge-table pattern (via ADR-126) — the industry precedent for this effective-dated junction.