# ADR-129 Per-Job Attributable P&L via a `sales.job_pnl` View — Even-Split Tour-Cost Allocation, Agency-Margin Headline ## Status 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, Job-P&L build plan chunk P0) reason: | With the Tour<->Job many-to-many work landed (ADR-125/126: tour-borne costs now reach jobs through tours via the sales.tour_jobs bridge; invoices already FK jobs directly), the platform is ready for its first per-job profit-and-loss surface — the stated end-goal of ADR-125. A read-only investigation of dev lm360 (the JOB_PNL_OPTIONS.md decision paper, 2026-08-01) mapped every revenue and cost stream against its job linkage and found the revenue side ready but the cost side almost entirely unlinked (~0% of recorded cost is job-attributed today; exactly one cost row across six streams carries a job key). The one real product decision — how to split a shared tour's cost across the multiple jobs that tour served — was put to hkl with worked numbers through three allocation options (JOB_PNL_OPTIONS.md sections 2-3). This ADR records the resulting choices: a single SQL view sales.job_pnl, even-split allocation stored per-tour, an agency-margin headline, and an explicitly attributable-only v1. All five open questions were pre-defaulted in the plan (JOB_PNL_PLAN.md section 2); the plan doc and options paper are the Q&A record. Status is Proposed, not Accepted — v1 has not yet been built. changed_via: adr-kit (360lm), authored directly on DL per hkl's Job-P&L plan session; the JOB_PNL_PLAN.md build plan and JOB_PNL_OPTIONS.md decision paper are the decision record. ``` ## Context **Where money links to jobs after the Tour<->Job work (dev lm360, re-verified 2026-08-01).** - **Revenue is ready.** `sales.invoices` carries a direct `job_id` FK and is generated in-platform by the Sales PWA. 3 invoices exist across 3 of 7 jobs; grand_total sums to `1,631,586` (`taxable_value` `1,382,700`, `agency_amount` `125,700`). Each invoice also carries an inline `po_number`/`po_amount` — the PO *reference* rides on the invoice even though there is no PO *table* (ADR-125's boundary). Reading revenue per job is a plain SELECT. - **Cost is almost entirely unlinked.** Six cost streams carry a job or tour column, but across all of them exactly **one** row is actually job-attributed today: a single `custodian.vouchers` row on `bt-job-a` (`gross_amount` `10,000`, settled). The linkage state: | Stream | Path to job | Linked today | |---|---|---| | `custodian.vouchers` | direct `job_id` | 1 row (`bt-job-a`, gross 10,000) — the only linked cost on the platform | | `rentveh.expenses` | `tour_id` -> `sales.tour_jobs` | 0 (incl. the real "KFC, McD - Ambala/YN/Karnal" 6,263.37 row, `tour_id` NULL) | | `tourexp.expenses` | `tour_id` -> `sales.tour_jobs` | 0 (new PWA, 0 rows) | | `expense.sheets` (`tour_id` column exists) | `tour_id` -> tour -> job | 0 | | `production.orders` | `tour_id` | 0 (demo row) | | `vehicle.trips` | `tour_id` | 0 (demo row) | | `finance.transactions` (17 rows, `366,691`), HR salary, creditcard, pass-through print | no job path at all | — | - **No single job has both a revenue and a cost row today.** The two live tours (`...CHANDIGARH_BTL-01-T01/T02`) both link, via `sales.tour_jobs`, to the *same* Lenovo job, which has no invoice; the three invoices are on `bt-job-b/c/d`, which have no tours; the one job-linked cost is on `bt-job-a`, which has no invoice. **There is not one complete real P&L row in the system.** Consequence: a P&L built now shows near-complete revenue against a near-empty cost side, so every job looks artificially profitable unless the tool is explicit about coverage. The even-split tour-cost path is therefore fully built but **0-populated** in v1 — it will only exercise real multi-job data once capture-wiring fills the tour columns. **The bridge shapes this design.** `sales.job_tours` is the **tour master** (PK `tour_id`; its old per-tour `job_id` was dropped in ADR-125 chunk 2E). `sales.tour_jobs` is the **many-to-many bridge** (`id`, `tour_id`, `job_id`, `status`, `start_date`, `end_date`), where `end_date IS NULL` means "this tour is currently linked to this job" (ADR-126 semantics). This distinction is why the allocation *method* is a property of the tour master, while allocation *divides* across the bridge links — see Decision 3. ## Decision ### 1. One SQL view `sales.job_pnl`, one row per job Build a single read-only view, `sales.job_pnl`, with one row per job in `sales.jobs`, composed of three CTEs (revenue, direct cost, tour-borne cost) joined on `job_id`. View-first is deliberate (see Alternatives): it makes the allocation rule and the coverage gap explicit and testable in SQL before any UI, and any later screen becomes a thin read layer over it, not a rewrite. ### 2. Revenue CTE exposes all three bases; headline aliased to `agency_amount` The revenue CTE sums `grand_total`, `taxable_value`, and `agency_amount` per job from `sales.invoices` and exposes **all three** columns. The view's headline `revenue` is aliased to **`agency_amount`** — the BTL agency's real margin line. Rationale: the captured *cost* side is the agency's own operating spend (vehicle, allowance, vendor voucher) and deliberately excludes the pass-through print cost billed through on invoices; measuring that cost against `grand_total` (gross billed) would show a fake fat profit on every job. `agency_amount` is the only revenue basis that is commensurable with the captured cost. Exposing all three lets hkl re-alias the headline later with no schema change. ### 3. Tour-borne cost allocated per the tour's stored `allocation_method`; even split in v1 Allocation intent is stored on the tour, not hardcoded in the query. Add: ```sql ALTER TABLE sales.job_tours ADD COLUMN allocation_method TEXT NOT NULL DEFAULT 'even' CHECK (allocation_method IN ('even','counters','revenue')); ``` `allocation_method` lives on `sales.job_tours` (the tour master) because how a tour's cost is split is a property of the tour, not of any one link. **Only `'even'` is implemented in v1**; the CHECK reserves `'counters'` and `'revenue'` so B/C can be adopted later without restating history — a live view with `/N` hardcoded would silently rewrite every past job's P&L the day the default changed; storing the method per-tour prevents that. The tour-cost CTE, per tour, sums each of the six streams' linked rows, then for `'even'` allocates `stream_total / N` to each linked job, where **N = COUNT of that tour's currently-active bridge links** (`sales.tour_jobs WHERE end_date IS NULL`). Using the active-link filter is safe specifically because debt-fix **D1** (2026-08-01) corrected the bridge's link-lifecycle semantics: before D1 the chunk-2A backfill had conflated tour lifecycle with link lifecycle on the same columns, so ADR-125 Execution Finding #5 recorded that chunk 2B read `tour_jobs` *unfiltered* to avoid hiding completed tours; D1 reset the backfilled rows to `status='active'`/`end_date=NULL`, fixed the hub and rentveh writers to always write `end_date:null`, and re-enabled `end_date=is.null` filtering. On today's data both live links are `end_date IS NULL`, so the filter is also trivially correct now; D1 is what keeps it correct once links begin to close. ### 4. v1 is explicitly ATTRIBUTABLE-ONLY The view covers only the six job/tour-keyed streams above. `finance.transactions` (the 366,691 ledger), HR salary (ADR-034), creditcard, and pass-through print cost are **excluded** from v1 and carry no job/tour key today. The view's own SQL header comment must say so verbatim — this is an "attributable P&L", not a "true bottom line" — and carry `-- See ADR-129`. Bringing the no-path streams in is deferred (see Related / out-of-scope, Decision 6) and happens only on hkl's explicit ask, with its own scoping. ### 5. Capture-wiring is in scope; the platform commits to filling the columns The columns exist; this decision commits the platform to actually populating them in daily use, so the attributable coverage grows from ~0% over time. In scope for the plan: persist `expense.sheets.tour_id` on save (Expense PWA), add an optional job picker writing `custodian.vouchers.job_id` (Custodian PWA), and persist `tour_id` on `production.orders` / `vehicle.trips` where a tour context exists at creation (or report that none does). RentVeh and Tour Expense already capture `tour_id`. The view reads whatever is linked, so it is correct from day one and simply reports more coverage as capture-wiring lands. ### 6. Visibility TM-only; view grant-gated per ADR-128 When a UI layer is eventually built, per-job P&L is **TM-only** (the Harish/Pramod pattern, matching Sales/invoice access). The view itself is grant-gated: grant `SELECT` on `sales.job_pnl` to `web_anon`, mirroring the `\dp` of a sibling `sales` view, per the shared `web_anon`/`authenticator` role model (ADR-128 — there are no per-PWA Postgres roles). `NOTIFY pgrst, 'reload schema'` after the DDL. ## Implementation Notes - Applied across build-plan chunks: P1 (the `allocation_method` DDL, one txn + reload), P2 (the capture-wiring, one agent per PWA, disjoint files), P3 (the `sales.job_pnl` view + grants). This ADR (P0) gates them. - Every DDL that PostgREST serves needs `NOTIFY pgrst, 'reload schema';` before the new shape is visible over REST, or POSTs/GETs silently 404 (ADR-125 Execution Finding #2). - P3 verification builds a synthetic full P&L row (one job with 1 invoice + 1 direct voucher + 1 two-job tour expense, asserting the 50% even split), then tears down to baseline counts, and asserts the real current data reproduces the verified totals (grand_total 1,631,586 across 3 jobs; the single 10,000 voucher on `bt-job-a`). - Everything is dev (`lm360`) only; no prod movement is proposed. ## Alternatives Considered - **Option B — allocate tour cost weighted by counters touched per job.** Rejected for v1 as non-computable. `installation.counters` = 0 rows, and all 31 `counters.allocation` rows have `job_id = NULL`, so there is no per-tour, per-job serviced-counter set to weight by; only `recce.submissions` is job-keyed, and that is a recce footprint per job, not which counters a tour serviced. B is the *fairest* rule once that data is live (Installation migrates onto `installation.counters`, ADR-127), which is why the `allocation_method` CHECK reserves it. - **Option C — allocate tour cost weighted by each job's revenue share.** Rejected. It is **circular and self-masking** — using revenue to allocate cost, then subtracting that cost from revenue to judge profit, flattens the very per-job signal the P&L exists to surface. It is also non-computable for the 4 of 7 jobs with no invoice yet, and unknowable at cost time when a job's revenue arrives after the tour. Reserved in the CHECK only for a pure shared-overhead pool one has already decided not to analyze per-job. - **Build the mini-PWA first / instead of the view.** Rejected. View-first, per the ADR-044 mini-PWA-family reasoning: the view is the smallest testable money surface, it makes the allocation rule and coverage gap explicit and assertable in SQL before any pixels, and it becomes the single data source a later TM-only read-only screen renders. A PWA built first would bake the allocation logic into UI code and be far harder to verify. - **Wait for 100% cost coverage before shipping any P&L.** Rejected. Attributable-only with honest labeling beats nothing: it surfaces the real revenue side and whatever cost is linked today, makes the coverage gap visible, and grows as capture-wiring (Decision 5) fills the columns. Blocking on full coverage would defer the tool indefinitely, since the largest ledger has no job path at all. ## Consequences **Positive:** - One queryable source of truth for per-job profitability, testable in SQL, with the allocation rule and the revenue basis made explicit rather than buried in UI code. - Storing `allocation_method` per-tour lets B/C arrive later without restating any closed job's P&L — changing the default never touches history. - The agency-margin headline reports a number that is commensurable with the captured cost, so v1 does not manufacture fake profit from pass-through print billing. - Correct from day one against whatever is linked; coverage simply grows as capture-wiring lands. **Negative / Trade-offs:** - An attributable-only P&L can be **misread as a true bottom line**, especially while cost coverage is near-zero. Mitigation: the mandatory exclusions comment in the view SQL header, and the same labeling carried into any future UI. - Even-split **misallocates when a multi-job tour is lopsided** (a 5-outlet + 1-outlet trip still splits 50/50). Mitigation: the reserved `'counters'`/`'revenue'` methods and the per-tour `allocation_method` column let a fairer rule be adopted per-tour once its data is trustworthy. - Attributable coverage **depends on field discipline** — the view is only as good as how faithfully `tour_id`/`job_id` get captured. Mitigation: pickers wired into the natural entry flows (Decision 5) rather than a separate reconciliation step. - The even-split path is **unexercised against real multi-job data** in v1 (both live tours serve one Lenovo job; N=1 today, so allocation is trivially 100%). Mitigation: P3's synthetic two-job test asserts the 50% split; real exercise follows capture-wiring. **Risks and mitigations:** | Risk | Mitigation | |---|---| | The view's number is quoted as the company's real per-job profit | Exclusions comment in the view SQL (`-- See ADR-129`); "attributable P&L, not true bottom line" labeling in any UI; TM-only visibility limits the audience | | Changing the `even` default later silently restates historical P&L | Method stored per-tour on `sales.job_tours`, computed from each tour's own value; the default only affects tours created after the change | | `end_date IS NULL` allocation divides by a wrong link count if link lifecycle drifts again | D1 fixed the backfill and both writers and re-enabled filtering; carry ADR-126's `end_date IS NULL` call-site convention into the view and any future relink/unlink UI | | A new `sales.job_tours` consumer breaks on the added NOT NULL DEFAULT column | Column is defaulted (`'even'`), so inserts omitting it succeed; PostgREST clients select explicit columns — P1 greps `job_tours` consumers to confirm before shipping | | Capture-wiring never happens, leaving the view perpetually near-empty | Capture-wiring is in-scope build work (Decision 5, plan chunks P2), not a hoped-for follow-on; pickers ride the existing entry flows | ## Related Decisions - **ADR-111** — Unified Job Record in `sales.jobs`. `job_pnl` is one row per `sales.jobs` job; both revenue and cost paths resolve to that single job identity. - **ADR-125** (revised 2026-08-01) + **ADR-126** — Tour<->Job true many-to-many via `sales.tour_jobs` over the effective-dated bridge pattern. This ADR consumes that bridge for the tour-cost path and depends on ADR-125 Execution Finding #5 + debt-fix D1 for the `end_date IS NULL` filter's safety. - **ADR-128** — Shared `web_anon`/`authenticator` role model. `sales.job_pnl`'s SELECT grant follows the sibling-mirror convention there; there are no per-PWA roles to grant to. - **ADR-044** — Finance mini-PWA family (not a monolith). Governs the deferred read-only TM-only P&L screen, if built, as a thin layer over this view. - **ADR-036** — Custodian payees are a shared cross-PWA master. Context for the direct `custodian.vouchers.job_id` cost path and its optional job picker (capture-wiring, Decision 5). - **ADR-034** — HR salary cross-writes `finance.transactions`. Context for why salary is one of the no-job-path streams excluded from v1 (Decision 4). **Out of scope (noted, not solved here):** PO tracking — per ADR-125's own boundary there is no PO table; `po_number`/`po_amount` ride inline on invoices, which is enough for the revenue side. Bringing `finance.transactions`, HR salary, creditcard, and pass-through print into a true-bottom- line P&L, and the read-only mini-PWA screen, are deferred to a later hkl-gated phase with its own scoping. ## References - `JOB_PNL_PLAN.md` (DL, `C:\Users\Lenovo\Documents\tour-job-architecture\`, 2026-08-01) — the build plan: section 0 verified data facts, section 1 target design, section 2 the five pre-defaulted decisions, section 3 chunk P3 view semantics. - `JOB_PNL_OPTIONS.md` (DL, same folder, 2026-08-01) — the decision paper: current-state inventory (section 1), the three allocation options with worked examples and why A won (sections 2-3), the view-first deliverable-shape reasoning (section 4), and the five hkl questions with defaults (section 6). - `DEBT_CLEANUP_PLAN.md` + `BUILD_PLAN.md` (DL, same folder) — chunk D1 "Fix `tour_jobs` link-lifecycle semantics", PAID 2026-08-01, which makes the `end_date IS NULL` filter safe. - Live `lm360` dev DB inspection (psql, 2026-08-01, re-verified for this ADR): `sales.invoices` = 3 rows, `grand_total` 1,631,586 / `taxable_value` 1,382,700 / `agency_amount` 125,700 (jobs `bt-job-b` 1,002,056, `bt-job-c` 415,360, `bt-job-d` 214,170); `custodian.vouchers` = 5 rows, 1 job-linked (`bt-job-a`, `gross_amount` 10,000, `net_payable` 8,000, settled); `sales.tour_jobs` = 2 bridge rows, both `status='active'`/`end_date IS NULL`, both linking the same Lenovo job; `sales.job_tours` = 2 tour-master rows; `finance.transactions` = 17 rows, 366,691.