# Plan: Unified Job Consolidation (sales.jobs canonical)

**Date:** 2026-07-11  
**Status:** Design Document (Ready for Review)  
**Owner Decision:** Harish has rejected the "keep both tables" compromise and mandates a single job concept.

---

## Problem Statement

360LM currently has **two separate job/campaign identities**, creating confusion and architectural defects:

1. **`installation.campaigns`** — operational/execution record (format: `2025-LENOVO-LENOVOIN-CHANDIGARH_BTL-01`)
   - Canonical per ADR-046 (created 2026-06-22)
   - Holds campaign ID used in client-facing presentations and site photos
   - Parent of `installation.tours` (physical tour routes) and `installation.jobs` (counter groupings)

2. **`sales.jobs`** — commercial/billing record (format: `JOB-1778820423725` or manual like `bt-job-a`)
   - Holds revenue pipeline, invoicing, offers, customer links
   - `campaign_id` column is present but **always NULL in dev** (not linked to installation)
   - Parent of invoices, challenges, offers, materials tracking

**Current State (Dev DB):**
- 6 `sales.jobs` rows (all with `campaign_id = NULL`)
- 1 `installation.campaigns` row (`2025-LENOVO-LENOVOIN-CHANDIGARH_BTL-01`)
- 2 `installation.tours` rows (linked to that campaign)
- 0 `installation.jobs` rows
- 0 `installation.counters` rows (would link to installation.jobs)

**Architectural Defects:**
- Two sources of truth for "a job" (operation ≠ billing, but same thing)
- Orphan management risk: ADR-046's two-step create with application-layer rollback can leave `installation.campaigns` orphans if process crashes between steps
- Cross-PWA confusion: Hub/Tour Planner/Installation refer to one concept; Sales/Custodian refer to another
- No unified job costing: `sales.job_profitability_v` rolls up commercial costs but has no visibility into operational/tour execution costs

---

## Proposed Architecture

### Core Decision: `sales.jobs` Becomes Canonical

**`sales.jobs` shall be the single canonical job record** (replacing `installation.campaigns`).

**Rationale:**
1. `sales.jobs` already has all the commercial metadata (customer, status, pipeline stage)
2. It already has FK references from multiple load-bearing tables (`invoices`, `challans`, `offers`, `briefs`, `artifacts`, `job_elements`)
3. `custodian.vouchers.job_id` soft-references it (needs FK added)
4. Single-schema design is simpler than cross-schema two-step create with rollback
5. Can support all operational + commercial data in one record

### Resolving ADR-046 Tension: Pre-Commercial Job Status

**Problem in ADR-046:** Campaign ID is needed for presentations/photos *before* a sales invoice exists. ADR-046 made `installation.campaigns` canonical to satisfy this.

**Solution:** Add a **pre-commercial status** to `sales.jobs`:
- New enum value: `'operational_draft'` (or `'campaign_initiated'`)
- Positioned **before** `'draft'` in the job lifecycle
- Allows jobs to exist and get campaign IDs assigned at operational start, independent of commercial terms
- Campaign ID is generated when the job is created in this status, not deferred
- Satisfies the "photo-proof needs campaign ID early" requirement without a second table

**Revised Job Lifecycle:**
```
operational_draft → draft → offer_sent → confirmed → in_execution → billed → closed
                  (operations can start here)  (commercial terms negotiated here)
```

### Tour Management: Relocate from `installation.campaigns` to `sales.jobs`

**Create `sales.job_tours` table** (child of `sales.jobs`, replaces `installation.tours`):

**Schema:**
```sql
CREATE TABLE sales.job_tours (
  tour_id text NOT NULL PRIMARY KEY,
  job_id text NOT NULL REFERENCES sales.jobs(job_id) ON DELETE CASCADE,
  title text,
  assigned_employees text[] NOT NULL DEFAULT '{}',
  start_date date,
  end_date date,
  status text NOT NULL DEFAULT 'planned',
  notes text,
  created_by text NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now()
);
```

**Columns carried forward:**
- `tour_id`, `title`, `assigned_employees`, `start_date`, `end_date`, `status`, `notes` (identical to current `installation.tours`)
- FK: `job_id` (new, pointing to `sales.jobs` instead of `installation.campaigns`)
- Timestamps + audit (`created_by`, `created_at`, `updated_at`) — same pattern as today

**Not included (dropped):**
- `campaign_id` (redundant; jobs already have an ID)

### Counter Management: Relink to `sales.jobs`

**Current state:**
- `installation.counters` → FK → `installation.jobs` (job instance grouping)
- `installation.jobs` → FK → `installation.campaigns` (campaign link)
- `installation.jobs` → FK → `installation.tours` (tour link) [redundant]

**New state (per user decision, 2026-07-11): retain installation.jobs/counters as-is, just re-point the parent FK):**
- Keep `installation.counters` as-is (no schema change, already low-level tactical detail)
- Keep `installation.jobs` but **change its campaign-facing FK** (Phase 1, see schema
  migration steps below): drop the FK to `installation.campaigns`, rename the column
  `campaign_id` → `sales_job_id` (installation.jobs already has its own `job_id` pkey,
  so the renamed column can't reuse that name), add FK to `sales.jobs.job_id`.
- The existing FK to `installation.tours` (via `tour_id`) is left untouched in Phase 1
  — whether it's dropped or repointed to `sales.job_tours` is an explicit Phase 5
  decision, made once Tour Planner's migration is underway, not pre-decided here.

**Rationale:** `installation.jobs` + `installation.counters` are **tactical operational detail** (counter-by-counter tracking). They should link to the canonical job, not a separate campaign table. Keeps them but redirects their parent reference.

### Current Dependents: Migration Summary

| Dependent | Current Link | New Link | Change |
|-----------|--------------|----------|--------|
| `sales.invoices` | `job_id` → `sales.jobs` | `job_id` → `sales.jobs` | ✅ No change (already correct) |
| `sales.challans` | `job_id` → `sales.jobs` | `job_id` → `sales.jobs` | ✅ No change |
| `sales.offers` | `job_id` → `sales.jobs` | `job_id` → `sales.jobs` | ✅ No change |
| `sales.job_elements` | `job_id` → `sales.jobs` | `job_id` → `sales.jobs` | ✅ No change |
| `sales.briefs` | `job_id` → `sales.jobs` | `job_id` → `sales.jobs` | ✅ No change |
| `sales.artifacts` | `job_id` → `sales.jobs` | `job_id` → `sales.jobs` | ✅ No change |
| `sales.job_materials_v` (view) | Joins on `job_id` + `campaign_id` | Joins on `job_id` only | ⚠️ View update needed (see Views section below) |
| `sales.job_profitability_v` (view) | Joins to invoices + materials on `job_id` | Joins to invoices + materials + vouchers on `job_id` | ⚠️ View update to add voucher_tour_cost? (optional) |
| `custodian.vouchers` | `job_id` (soft ref, no FK) | `job_id` (hard FK) | ⚠️ Add FK constraint |
| `installation.tours` → `installation.campaigns` | FK → `campaigns.campaign_id` | → `sales.job_tours.job_id` | ✅ Absorbed into new `sales.job_tours` table |
| `installation.jobs` → `installation.campaigns` | FK → `campaigns.campaign_id` | → `sales.jobs.job_id` | ⚠️ Update FK target |
| `installation.jobs` → `installation.tours` | FK → `tours.tour_id` | Keep (but may be redundant) | ℹ️ Phase 5: consider dropping if not needed |
| Hub PWA ("Jobs" screen) | Reads `installation.campaigns` + `tours` | Reads `sales.jobs` + `sales.job_tours` | ⚠️ Query rewrite |
| Tour Planner PWA | Writes `installation.tours` | Writes `sales.job_tours` | ⚠️ Table + profile change |
| Tour-PG PWA | Reads `installation.tours` + campaigns | Reads `sales.job_tours` + jobs | ⚠️ Query rewrite |
| Dispatch PWA | References `installation.jobs` (indirect) | References `installation.jobs` → `sales.jobs` chain | ✅ No direct change if install.jobs links correctly |

---

## Views Affected & Updates Required

### `sales.job_materials_v` — Update Required

**Current:** Joins `sales.jobs` on both `job_id` AND `campaign_id` (as fallback match_type)
```sql
LEFT JOIN stores.issues_v iv ON (
  (iv.job_id IS NOT NULL AND iv.job_id = j.job_id) 
  OR 
  (iv.job_id IS NULL AND iv.campaign_id = j.campaign_id)
)
```

**Change:** Remove campaign_id fallback; join only on job_id
```sql
LEFT JOIN stores.issues_v iv ON (iv.job_id = j.job_id)
```

**Remove the match_type CASE expression** (no longer "via_campaign" path):
```sql
-- OLD:
CASE 
  WHEN iv.job_id = j.job_id THEN 'direct'
  WHEN iv.campaign_id = j.campaign_id THEN 'via_campaign'
  ELSE 'unknown'
END AS match_type

-- NEW:
'direct'::text AS match_type
```

### `sales.job_profitability_v` — Optional Enhancement

**Current:** Rolls up invoice + material + voucher cost on `job_id`.

**No breaking changes needed.** View will continue to work after consolidation because:
- Invoices still FK to `sales.jobs.job_id`
- Materials join on `job_id`
- Vouchers will have hard FK to `sales.jobs.job_id` (Phase 1)

**Optional Phase 5+ enhancement:** Add `tour_execution_cost` column to roll up Tour Planner's operational expenses (if Tour Planner records cost data). This would give full job P&L = commercial + operational. **Deferred to post-consolidation roadmap.**

### `installation.tour_list` View

**Current:** Builds from `installation.campaigns` + `installation.tours`.

**Phase 4:** Will need to repoint to `sales.jobs` + `sales.job_tours` (if this view is still used; may be deprecated if Hub PWA replaces it).

---

## Migration Path: 7 Phases (Sequential, Independently Verifiable)

### Phase 1: Schema Migration (DB-only, no code changes yet)

**Goal:** Create new tables, add constraints, backfill non-nullable columns.

**Steps:**

1. Create `sales.job_tours` table (as schema above)
   - Verify: `\d sales.job_tours` shows all columns correctly

2. Add `'operational_draft'` enum value to `sales.jobs.status` type (if using CHECK constraint, add to list)
   - Current CHECK: `status = ANY (ARRAY['draft'::text, 'offer_sent'::text, ...])`
   - New CHECK: Add `'operational_draft'::text` to start of list
   - Verify: Try inserting a job with `status='operational_draft'` (should succeed)

3. **Add FK: `custodian.vouchers.job_id` → `sales.jobs.job_id`**
   ```sql
   ALTER TABLE custodian.vouchers 
   ADD CONSTRAINT vouchers_job_id_fkey 
   FOREIGN KEY (job_id) REFERENCES sales.jobs(job_id);
   ```
   - Verify: `\d custodian.vouchers` shows new FK
   - **Safety check FIRST:** Verify no orphaned vouchers exist
     ```sql
     SELECT COUNT(*) FROM custodian.vouchers v 
     WHERE v.job_id IS NOT NULL 
       AND NOT EXISTS (SELECT 1 FROM sales.jobs j WHERE j.job_id = v.job_id);
     ```
     Should return 0 (if not, data cleanup needed before FK added)

4. Update `installation.jobs` table:
   ```sql
   -- Drop old FK to installation.campaigns
   ALTER TABLE installation.jobs DROP CONSTRAINT jobs_campaign_id_fkey;
   -- installation.jobs_tour_id_fkey (→ installation.tours) is left untouched here —
   -- its disposition is a Phase 5 decision (see Tour Planner phase), not decided now.

   -- Rename the column so it's unambiguous it now points at sales.jobs.job_id,
   -- not a campaign. NOTE: installation.jobs already has its OWN pkey column
   -- named job_id (its own identity, unrelated) — so the renamed FK column must
   -- use a distinct name, not job_id itself.
   ALTER TABLE installation.jobs
   RENAME COLUMN campaign_id TO sales_job_id;
   ALTER TABLE installation.jobs
   ADD CONSTRAINT jobs_sales_job_id_fkey
   FOREIGN KEY (sales_job_id) REFERENCES sales.jobs(job_id);
   ```
   - **Safety check — run this BEFORE the ALTER statements above:** verify no
     orphaned `installation.jobs` rows (uses the original `campaign_id` column
     name, since this runs prior to the rename)
     ```sql
     SELECT COUNT(*) FROM installation.jobs ij 
     WHERE NOT EXISTS (SELECT 1 FROM installation.campaigns ic WHERE ic.campaign_id = ij.campaign_id);
     ```
   - Verify: `\d installation.jobs` shows old FK dropped, new FK added

5. **Backfill `sales.job_tours` with existing tours:**
   ```sql
   INSERT INTO sales.job_tours (tour_id, job_id, title, assigned_employees, start_date, end_date, status, notes, created_by, created_at, updated_at)
   SELECT 
     t.tour_id,
     '???' AS job_id,  -- DECISION POINT: see below
     t.title,
     t.assigned_employees,
     t.start_date,
     t.end_date,
     t.status,
     t.notes,
     t.created_by,
     t.created_at,
     t.updated_at
   FROM installation.tours t;
   ```
   
   **Decision Point:** The 2 existing tours link to `2025-LENOVO-LENOVOIN-CHANDIGARH_BTL-01` (installation.campaigns). There is NO corresponding `sales.jobs` row with matching campaign_id (all 6 sales.jobs have NULL campaign_id). **Two options:**
   
   **Option A (Recommended):** Create a temporary `sales.jobs` row for the Lenovo campaign:
   - Insert new job with `job_id = 'JOB-2025-LENOVO-LENOVOIN-CHANDIGARH_BTL-01'`, `campaign_id = '2025-LENOVO-LENOVOIN-CHANDIGARH_BTL-01'`, `status='operational_draft'`
   - Backfill tours with this new job_id
   - Then proceed with ADR migration
   
   **Option B:** Create "orphan tours" in `sales.job_tours` with a synthetic job_id (e.g., create a "Lenovo Campaign (Operational)" job row just to hold them)
   
   **Recommendation: Option A** — it's clearer. Creates a "real" sales.jobs row for the existing campaign, keeping data integrity clean.
   
   - Verify: `SELECT COUNT(*) FROM sales.job_tours` returns 2

6. Add index on `sales.job_tours.job_id` for FK lookups:
   ```sql
   CREATE INDEX idx_job_tours_job_id ON sales.job_tours(job_id);
   ```
   - Verify: `\d+ sales.job_tours` shows index

**Verification after Phase 1:**
```bash
docker exec -i postgres psql -U lmadmin -d lm360 << 'EOF'
  -- All tables exist
  \d sales.job_tours
  \d sales.jobs   -- check status CHECK includes operational_draft
  \d custodian.vouchers  -- check job_id FK exists
  \d installation.jobs   -- check new FK exists
  
  -- Data integrity
  SELECT COUNT(*) FROM sales.job_tours; -- should be 2
  SELECT COUNT(*) FROM sales.jobs WHERE status = 'operational_draft'; -- should be 1 or 0 (decision point above)
  
  -- No orphans
  SELECT COUNT(*) FROM custodian.vouchers v WHERE v.job_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sales.jobs WHERE job_id = v.job_id);
  SELECT COUNT(*) FROM sales.job_tours t WHERE NOT EXISTS (SELECT 1 FROM sales.jobs WHERE job_id = t.job_id);
EOF
```

---

### Phase 2 — DONE + verified (2026-07-11)

`migrate_job_consolidation_phase2.sql`, applied to `lm360` dev. `sales.job_materials_v`
simplified to a single `job_id` join (no campaign_id fallback); `installation.tour_list`
dropped and recreated pointing at `sales.jobs`/`sales.job_tours` (`CREATE OR REPLACE` can't
rename a column, had to `DROP VIEW` first). One real bug caught and fixed by empirical
verification, not by review: the first draft hardcoded `match_type = 'direct'` unconditionally,
which mislabeled the 5 jobs with no material issued at all as "direct" — fixed to
`CASE WHEN iv.issue_id IS NOT NULL THEN 'direct' ELSE 'none' END`, matching the original
two-outcome semantics minus only the removed campaign_id branch. Also hit and cleaned up the
known `_migrations` INSERT-outside-transaction bug — a rolled-back attempt still recorded
itself as applied; deleted that row before re-running the corrected migration.
`sales.job_profitability_v` (7 rows), `sales.job_materials_v` (10 rows, now 5 direct/5 none),
`installation.tour_list` (2 rows, correct display_name via the new job) all verified live.

### Phase 2 original plan (for reference; see DONE note above)

**Goal:** Repoint views to new table structure.

1. **Update `sales.job_materials_v`:** Remove campaign_id match_type (as specified in Views section above)
   - Verify: `SELECT * FROM sales.job_materials_v LIMIT 1` returns `match_type = 'direct'` (no 'via_campaign')

2. **Update `installation.tour_list` view** (if it exists and is used):
   - Repoint from `installation.campaigns` + `installation.tours` to `sales.jobs` + `sales.job_tours`
   - If deprecated, mark as DEPRECATED in a comment or consider dropping in Phase 6

3. **Re-generate views that depend on above** (if any):
   - Run `\d+ <view_name>` to find dependent views
   - Verify no breaking changes

**Verification after Phase 2:**
```bash
docker exec -i postgres psql -U lmadmin -d lm360 << 'EOF'
  -- Views still readable, no SQL errors
  SELECT COUNT(*) FROM sales.job_profitability_v;
  SELECT COUNT(*) FROM sales.job_materials_v;
  SELECT COUNT(*) FROM installation.tour_list;
  
  -- Sample query: does a job show its tours?
  SELECT j.job_id, COUNT(t.tour_id) as tour_count 
  FROM sales.jobs j 
  LEFT JOIN sales.job_tours t ON t.job_id = j.job_id 
  GROUP BY j.job_id;
EOF
```

---

### Phase 3 — DONE + verified (2026-07-11)

`sales/index.html`'s `saveJob()` rewritten to a single-step create into `sales.jobs` with
`status: 'operational_draft'` and `zones: _selectedZones` (see the `zones` column note below —
plan gap, fixed live). Removed the now-dead `CFG.installHdr` header object (unused after the
rewrite) and added `operational_draft` to both `STATUS_LABELS` maps and the status-change
modal. Bumped `sales/sw.js` CACHE to `360sales-v20`.

**Schema gap the original plan missed, caught before building:** the job form has a *required*
zone-selection step (`_selectedZones`, "Select at least one zone" validation) whose data was
being written to `installation.campaigns.zones` — a column `sales.jobs` never had. Verified
`zones` is write-only (never read back anywhere in the codebase) but is still a live, required
UI field, so dropping the capture silently would have been a real behavior regression. Added
`sales.jobs.zones text[] NOT NULL DEFAULT '{}'` and backfilled from the 1 existing campaign.
The other `installation.campaigns`-only columns (`client_archived`, `client_archived_at/by`,
`client_archive_rule`) were checked and confirmed genuinely dead — zero PWA code references
them anywhere — so they were NOT carried forward (YAGNI), unlike `zones`.

**Real, pre-existing, unrelated bug found and fixed while live-testing:** `loadZoneChips()`
called `fetch(url, CFG.clientRd)` — passing a flat headers object directly as fetch's second
argument instead of `{ headers: CFG.clientRd }`. Since `Accept-Profile` isn't a valid top-level
`RequestInit` property, it was silently dropped from every request, and PostgREST fell back to
its default schema (`expense`, the first entry in `PGRST_DB_SCHEMAS`) — meaning zone selection
had never worked in this dev environment (returned a Postgres "relation expense.zones does not
exist" error, swallowed into "No zones configured"). This blocked all new job creation since
zone selection is required. Fixed (one-line: wrap in `{ headers: ... }`); unrelated to the job
consolidation itself but found and fixed because it blocked live verification of Phase 3.

Verified end-to-end via a live Playwright run against the real dev UI: created a job through
the actual form (year/brand/campaign/zone), confirmed `sales.jobs` got the row with
`status='operational_draft'`, correct `zones`, and confirmed **zero** row appeared in
`installation.campaigns` for it. Test data cleaned up after.

### Phase 3 original plan (for reference; see DONE note above)

**Goal:** New job creation writes to consolidated schema; edit/read flows continue to work.

**Code Changes:**

1. **New Job Creation** (around line 1692-1744):
   - Remove the two-step create pattern (installation.campaigns step 1 + sales.jobs step 2 + rollback)
   - **Single-step create to sales.jobs:**
     - Generate campaign_id using same format: `buildCampaignIdStr(year, brand, client, campaign, phase)`
     - Use this as the `campaign_id` value in the sales.jobs INSERT
     - No rollback complexity (single transaction)
   
   ```javascript
   // OLD: Step 1 → installation.campaigns, Step 2 → sales.jobs with rollback
   // NEW: Single step to sales.jobs
   
   const campaignId = buildCampaignIdStr(year, brand, client, campaign, phase);
   const salesResp = await fetch(`${CFG.api}/jobs`, {
     method: 'POST',
     headers: CFG.saleHdr,
     body: JSON.stringify({
       job_id:        'JOB-' + Date.now(),
       campaign_id:   campaignId,
       job_name:      campaignId,
       status:        'operational_draft',  // NEW: start in pre-commercial status
       brand, client, campaign_name: campaign,
       year, numeral: phase,
       type, site_address: site, description: desc,
       customer_id: customerId,
       created_by: currentUser.id,
     }),
   });
   if (!salesResp.ok) {
     const err = await salesResp.json().catch(()=>({}));
     throw new Error('Job create failed: ' + (err.message || 'unknown error'));
     // NO rollback logic needed
   }
   ```

2. **Edit/Read Flows:**
   - Existing queries using `sales.jobs` and `sales.campaigns` (soft FK) continue unchanged
   - No query rewrites needed for edit/list/detail views (already use `sales.jobs.job_id`)

3. **Remove references to `installation.campaigns`:**
   - Search codebase for `installation.campaigns` or `CFG.installHdr` in job creation flows
   - Remove any code that POSTs to `/campaigns` endpoint
   - Remove the `CFG.installHdr` (installation profile header) from job creation

4. **Test:**
   - Manual test: create a new job in Sales, verify it appears in sales.jobs with `status='operational_draft'` and `campaign_id` is set
   - Verify no campaign is created in installation.campaigns
   - Verify the job can later transition to `draft` and beyond

**Verification after Phase 3:**
```bash
# Manual: Login to Sales PWA, create a test job "TestJob-2026-01"
# Query: 
docker exec -i postgres psql -U lmadmin -d lm360 << 'EOF'
  SELECT job_id, status, campaign_id FROM sales.jobs WHERE campaign_id ILIKE '%TestJob%';
  -- Should show: | JOB-<timestamp> | operational_draft | <campaign_id>
  
  -- Verify no campaign was created in installation
  SELECT COUNT(*) FROM installation.campaigns WHERE campaign_id ILIKE '%TestJob%';
  -- Should return: 0
EOF
```

---

### Phase 4 — DONE + verified (2026-07-11)

`hub/index.html`'s Jobs screen repointed from `installation.campaigns`/`tours` to
`sales.jobs`/`sales.job_tours` (new `SALE_RD`/`SALE_WR` headers replacing `INST_RD`/`INST_WR`,
`enterJobDetail`/`loadJobDetail` replacing `enterCampaign`/`loadCampaign`, all tour CRUD
repointed to `/job_tours` with `job_id` instead of `campaign_id`). Bumped `hub/sw.js` CACHE to
`360lm-hub-v37`.

**Real design decision made, not silently guessed:** Hub previously had its own
active/on_hold/completed status toggle for campaigns — but `sales.jobs` has a richer 7-state
pipeline (operational_draft→draft→...→closed) that Sales PWA already manages via its own status
modal. Asked Harish directly rather than picking a mapping; confirmed removing Hub's job-status
control (now read-only display, with a note pointing to Sales) while keeping full tour
create/edit/status control in Hub, since Tours has no equivalent screen in Sales. Avoids two
UIs able to write the same status field with different vocabularies.

**Real bug found and fixed during Phase 4 live verification, not caught in Phase 1 review:**
`sales.job_tours` (created in Phase 1) never got PostgREST role grants — a new table under an
already-exposed schema does NOT inherit that schema's existing grants. Hub's tour list came
back "permission denied for table job_tours" until `GRANT SELECT, INSERT, UPDATE, DELETE ON
sales.job_tours TO web_anon, authenticator` was applied (added retroactively into
`migrate_job_consolidation_phase1.sql` for anyone re-running from scratch).

Verified end-to-end via live Playwright runs against the real dev Hub UI: job list renders with
correct status badges for all 7 jobs including the synthetic Lenovo one; opening the Lenovo job
shows both backfilled tours with correct names/dates/assigned employees; created a brand-new
tour through the actual "+ New Tour" modal on `bt-job-a` and confirmed the row landed in
`sales.job_tours` with the right `job_id` and employee assignment. Test tour cleaned up after.

### Phase 4 original plan (for reference; see DONE note above)

**Goal:** Hub's job/campaign/tour management reads/writes new schema.

**Code Changes:**

1. **Identify the "Jobs / Campaigns & Tours" screen** in hub/index.html:
   - Find code that fetches `installation.campaigns` (likely via `GET /campaigns`)
   - Find code that creates/updates campaigns
   - Find code that manages tours (fetches `installation.tours`)

2. **Repoint queries:**
   ```javascript
   // OLD:
   // GET /campaigns?status=eq.active (with installation profile header)
   
   // NEW:
   // GET /jobs?status=in.(operational_draft,draft,offer_sent,confirmed,in_execution,billed) (with sales profile header)
   
   const jobsResp = await fetch(
     `${CFG.api}/jobs?status=in.operational_draft,draft,offer_sent,confirmed,in_execution,billed`,
     { headers: CFG.saleHdr }
   );
   const jobs = await jobsResp.json();
   // Use jobs directly (instead of campaigns)
   ```

3. **Tour management:**
   - Repoint from `GET /tours?campaign_id=eq.xxx` to `GET /job_tours?job_id=eq.xxx`
   - Update headers from `installation` profile to `sales` profile
   - Update POST/PATCH endpoints from `/tours` to `/job_tours`

4. **Campaign status update screen:**
   - If Hub has a "change campaign status" button, repoint to PATCH `/jobs/{job_id}` (not `/campaigns/{campaign_id}`)
   - Statuses are now job statuses, not campaign statuses

5. **Test:**
   - Manual: Open Hub, navigate to Jobs/Tours screen
   - Verify all existing jobs appear (from sales.jobs, including the new Lenovo test job)
   - Verify tours appear under their parent job
   - Verify create/edit/delete flows work

**Verification after Phase 4:**
```bash
# Manual: Open Hub, check Jobs screen shows all 6+ sales.jobs (not campaigns)
# Manual: Open a job, verify tours display
# Manual: Create a new tour under a job, verify it's stored in sales.job_tours
```

---

### Phase 5 — DONE + verified (2026-07-11/12)

`tour-planner/index.html` repointed: `loadCampaigns()` now reads `sales.jobs` (filtered
`status=neq.closed` instead of `installation.campaigns.status=eq.active`), `loadCounters()`
queries `installation.jobs?sales_job_id=eq.<sales job_id>` (repointed from the renamed
column), `saveTours()`/`enterMonitor()`/`loadTourForMonitor()` all repointed from
`/tours`+`campaign_id` to `/job_tours`+`job_id` with `sales` profile headers. Also repointed
`installation.jobs.tour_id`'s FK from `installation.tours` to `sales.job_tours` (the exact
FK disposition Phase 1 explicitly deferred to this phase) — zero rows in `installation.jobs`
at the time, so zero migration risk. Bumped `tour-planner/sw.js` CACHE to `360tourplan-v4`.

**`tour-pg` ("Tour Playground") checked and confirmed NOT a dependent** — it has its own
isolated `tour_pg` schema (`saved_tours` table, a route-planning scratchpad feature)
completely unrelated to `installation.tours`/`sales.job_tours`. The original research pass's
claim that Tour-PG depends on this was a false positive; verified by reading its actual code.
No changes made there.

**Real, pre-existing, severe, unrelated bug found and fixed:** `const API = '/postgrest'`
(line 530) — but Traefik's config only ever routes `/db` to PostgREST anywhere on this host;
no `/postgrest` route exists. This means every single fetch call in Tour Planner — campaign
list, counters, route save, tour monitor — was 404ing against the real backend, despite the
MDD marking all 4 phases "✅ Complete." Confirmed with the user before fixing (bug's blast
radius is the whole PWA, not just the touched code) — fixed to `/db`.

Verified end-to-end via live Playwright runs against the real dev Tour Planner: job list
loads all 7 jobs with correct brand/status; `saveTours()` writes a real tour row to
`sales.job_tours` with correct `job_id`/team/route data (test tour created then deleted);
tour monitor correctly lists the one `planned`-status tour. `loadCounters()`'s
`installation.jobs` query executes without error (returns "No jobs in this campaign",
which is accurate — 0 rows exist in `installation.jobs` in dev — not independently
verifiable further without real counter-grouping data).

### Phase 5 original plan (for reference; see DONE note above)

**Goal:** Tour Planner writes optimized routes to new `sales.job_tours` table instead of `installation.tours`.

**Code Changes:**

1. **Identify tour save flow:**
   - Find code that POSTs/PATCHs to `/tours` endpoint (likely with installation profile)
   - This is where Tour Planner saves its optimized route

2. **Update endpoint and profile:**
   ```javascript
   // OLD:
   // POST /tours with headers: { 'Content-Profile': 'installation' }
   
   // NEW:
   // POST /job_tours with headers: { 'Content-Profile': 'sales' }
   
   const tourResp = await fetch(`${CFG.api}/job_tours`, {
     method: 'POST',
     headers: CFG.saleHdr,  // Changed from CFG.installHdr
     body: JSON.stringify({
       tour_id: 'TOUR-' + ...,
       job_id: jobId,  // Changed from campaign_id
       title: tourName,
       assigned_employees: employeeIds,
       start_date: ...,
       end_date: ...,
       status: 'planned',
       created_by: currentUser.id,
     }),
   });
   ```

3. **Update read flows:**
   - Any query fetching tours to display/edit: change from `GET /tours?campaign_id=eq.xxx` to `GET /job_tours?job_id=eq.xxx`

4. **Test:**
   - Manual: Open Tour Planner, load a job
   - Verify tour list loads (should show existing tours from sales.job_tours)
   - Optimize a route and save it
   - Verify new tour appears in sales.job_tours with correct job_id FK

**Verification after Phase 5:**
```bash
# Manual: Open Tour Planner, create/save an optimized tour
# Query:
docker exec -i postgres psql -U lmadmin -d lm360 << 'EOF'
  SELECT * FROM sales.job_tours WHERE tour_id LIKE 'TOUR-%' ORDER BY created_at DESC LIMIT 1;
  -- Should show new tour with correct job_id
EOF
```

**Note on `tour-pg/` (Tour-PG PWA):** This PWA also reads installation.tours. Update it similarly to read from `sales.job_tours` and link to `sales.jobs` instead of `installation.campaigns`.

---

### Phase 6 — DONE + verified (2026-07-12)

`migrate_job_consolidation_phase6.sql` applied to `lm360` dev. Before dropping, ran a full
dependency sweep beyond what Phases 1-5 already covered — found and fixed **one real
dependent neither research pass caught**: `installation.my_tours()` RPC (called by
`expense/index.html`'s "link expense sheet to a tour" dropdown) still queried
`installation.tours`/`campaigns` directly in its SQL body. Rewritten to query
`sales.job_tours`/`sales.jobs` instead, kept exposed under the same `installation.my_tours`
RPC name so the Expense PWA call site needs zero changes. Also checked `vehicle.trips.tour_id`
and `vrs.requests.tour_id` (flagged by a raw grep) — confirmed these are soft/comment-only
references (no real FK, ADR-106 pattern), 0 rows use them in dev, and the tour_id *values*
were preserved verbatim during the Phase 1 backfill, so nothing becomes unresolvable.

Final pre-drop sweep confirmed zero remaining references: no `pg_proc` function body, no view,
and no FK anywhere in the database still touches `installation.tours`/`campaigns` except their
own internal FK to each other. Took a full `pg_dump` backup immediately before dropping
(`db_backups/backup_2026-07-12_pre_phase6_drop.sql`), then dropped both tables.

Verified post-drop via a full live smoke sweep across every touched PWA in one pass (not just
re-checking the dropped tables): Sales dashboard loads, Hub's Jobs screen lists all 7 jobs,
Tour Planner's campaign list lists all 7 jobs, and Expense's `my_tours` RPC call returns the
correct tour for Rakesh — all against the real dev DB, all after the drop.

Test files `tests/admin.spec.js` and `tests/sales.spec.js` also had stale assertions
referencing the old schema (checking for `installHdr`, `/db/campaigns`, `installation.campaigns
has zones column`) — updated to assert the new single-step `sales.jobs`-only behavior. These
assertions target the **prod** URL and will correctly fail there until this migration is
actually deployed to prod — that's the intended guard rail (project convention: don't deploy
if tests fail), not a bug.

### Phase 6 original plan (for reference; see DONE note above)

**Goal:** Remove orphaned tables now that all consumers have migrated.

**Before proceeding:** Verify all phases 1-5 are complete and tested live. This step is **point-of-no-return** for the old schema (though a backup/snapshot can preserve it for audit).

**Steps:**

1. **Drop old tables (in dependency order):**
   ```sql
   -- First, dependencies of installation.campaigns
   DROP TABLE installation.tours CASCADE;  -- Includes triggers
   DROP TABLE installation.jobs CASCADE;   -- Includes triggers
   
   -- Finally, the root
   DROP TABLE installation.campaigns CASCADE;  -- Includes triggers
   
   -- If installation.counters is now orphaned (no parent job):
   -- Optional: migrate counters to point directly to sales.jobs
   -- Or keep as-is if installation.counters still serves a purpose (sub-division of installation.jobs)
   -- Decision: does "installation.counters" still track individual counter installation specs?
   ```

2. **Drop views referencing old tables:**
   ```sql
   DROP VIEW installation.tour_list CASCADE;
   -- (or if it's been updated to use sales schema, this already doesn't exist)
   ```

3. **Verify no remaining code references old tables:**
   ```bash
   grep -r "installation.campaigns\|installation.tours\|installation.jobs" /var/www/360lm --include="*.html" --include="*.js" --include="*.md"
   # Should return 0 results (except in comments/docs)
   ```

**Verification after Phase 6:**
```bash
docker exec -i postgres psql -U lmadmin -d lm360 << 'EOF'
  \dt installation.*  -- Should show NO campaigns, tours, or jobs tables
  \d installation.counters  -- Should still exist (or be explicitly decided in Phase 6 step 1)
EOF
```

---

### Phase 7: Update ADRs and Documentation

**Goal:** Capture the new design and supersede ADR-046.

1. **ADR-111** (drafted 2026-07-11, Accepted — see `docs/adr/ADR-111-unified-job-record-sales-jobs-canonical.md`):
   - Title: "Unified Job Record in sales.jobs (Supersedes ADR-046)"
   - Context: The two-table design created confusion; operational + commercial data belong together
   - Decision: `sales.jobs` is now canonical; `installation.campaigns` and `installation.tours` are removed
   - Consequences: Single transaction job creation (no two-step rollback); all PWAs link to one schema; tour/counter costing rolls up to sales.job_profitability_v
   - Related: ADR-046 (superseded), ADR-024 (no change — campaign ID still used in presentations, now sourced from sales.jobs), ADR-009 (reduced cross-schema coupling)

2. **Mark ADR-046 as Superseded:**
   - Change Status from "Accepted" to "Superseded" with date and reference to new ADR
   - Keep ADR-046 in history (do not delete)

3. **Update relevant MDDs:**
   - `/installation/MDD_installation.md` — remove/deprecate references to campaigns/tours (now in sales)
   - `/sales/MDD_sales.md` — update to document the new job table structure, job_tours, operational_draft status
   - `/hub/MDD_hub.md` — update "Jobs / Campaigns & Tours" screen spec to use new schema
   - `/tour-planner/MDD_tour_planner.md` — update to write to sales.job_tours

4. **Update project memory:**
   - Edit `dbt_sales.md` to reflect new canonical job creation (single-step, no rollback)
   - Edit `dbt_installation.md` to note that campaigns/tours are now deprecated in favor of sales.jobs/sales.job_tours

---

## Current Data State & Decision Points

### Existing Data to Migrate

**6 `sales.jobs` rows (all with `campaign_id = NULL`):**
| job_id | job_name | status | customer_id |
|--------|----------|--------|-------------|
| `JOB-1778820423725` | 26-Epson-EverythingElse-BTLActivityInd-001 | draft | cust-everything_else-cd745e |
| `JOB-1778744623251` | 26-Epson-EverythingElse-BTLActivityEps-001 | draft | (similar) |
| `bt-job-a` | BT-AsianPaints-DealerMeet-2026-01 | offer_sent | — |
| `bt-job-b` | BT-Verve-BTLBranding-2026-01 | confirmed | — |
| `bt-job-c` | BT-MktVibes-StoreInauguration-2026-01 | in_execution | — |
| `bt-job-d` | BT-AsianPaints-FlexPrint-2026-01 | billed | — |

**Action (Phase 1):** These jobs already have meaningful status. Leave them as-is (they are past the `operational_draft` phase). No campaign_id backfill needed for existing jobs — they were created before consolidation.

**1 `installation.campaigns` row:**
| campaign_id | campaign_name | status |
|-------------|---------------|--------|
| `2025-LENOVO-LENOVOIN-CHANDIGARH_BTL-01` | Chandigarh BTL | active |

**Decision point (Phase 1):** Does this map to an existing `sales.jobs` row? No — all 6 sales.jobs have NULL campaign_id. **Option A:** Create a synthetic `sales.jobs` row to hold the tours. **Option B:** Orphan the tours temporarily (Phase 1, to be reassigned later). **Recommendation: Option A** — keep data clean.

**2 `installation.tours` rows:**
Both link to the Lenovo campaign above. Will be moved to `sales.job_tours` in Phase 1.

### Decision: Job ID Format

**Current:** 
- `sales.jobs` uses `JOB-<timestamp>` or manual IDs (`bt-job-a`)
- `installation.campaigns` uses campaign ID format: `YYYY-BRAND-CLIENT-CAMPAIGN-Pxx`

**New approach:** Keep both. `sales.jobs` now has:
- `job_id` (primary key, can be auto-generated like `JOB-<timestamp>`)
- `campaign_id` (secondary, human-readable campaign identifier, generated early and used for presentations/photos)

This way:
- `campaign_id` serves the "photos need ID early" purpose (as per ADR-046)
- `job_id` is the internal PK
- Both are indexed for quick lookup
- Views and APIs can expose either (job_id for internal ops, campaign_id for external docs)

---

## Risk & Mitigation

| Risk | Likelihood | Impact | Mitigation |
|------|------------|--------|-----------|
| **Old `installation.campaigns` queries still execute, return wrong data** | Medium | High | Phase 6 drops table; Phase 3-5 test all consumer queries; grep for old table refs before drop |
| **Orphaned `installation.counters` after dropping `installation.jobs`** | Low | Medium | Phase 6: verify all counters have a valid installation.jobs parent before dropping; or redesign counters to link to sales.jobs directly |
| **Tour Planner doesn't save routes post-migration** | Low | High | Phase 5 manual test (create and save a route); Playwright spec covers tour save flow |
| **Dispatch PWA breaks (references installation.jobs indirectly)** | Low | Medium | Phase 5+6: verify Dispatch still fetches installation.jobs (now linked to sales.jobs); update queries if needed |
| **Vouchers with NULL job_id fail FK check** | Very Low | Low | Phase 1 safety check confirms no orphans; add FK only after verification |
| **Custom reports/RLS rules rely on old schema** | Low | Medium | Audit all views and RLS policies before Phase 6; update as needed |

**Rollback Strategy:** Each phase can be rolled back independently using version control (git revert) and DB restore (logical backup). Critical: **take a logical backup before Phase 1** so a full revert is always possible.

---

## Decisions — confirmed by Harish, 2026-07-11

All four resolved with the recommended default; no open questions remain on this plan.

1. **Job ID format:** keep both — `job_id` (internal PK, `JOB-<timestamp>` style) +
   `campaign_id` (human-readable, presentation/photo-facing), unchanged from today.
2. **`operational_draft` status:** added — resolves the ADR-046 tension without a
   second table.
3. **Tour ID format:** kept as-is (campaign-derived, e.g.
   `2025-LENOVO-LENOVOIN-CHANDIGARH_BTL-01-T01`), unchanged from today.
4. **`installation.jobs`/`installation.counters`:** retained as-is, just re-pointed
   to `sales.jobs` (see the corrected Phase 1 schema steps above — column renamed
   `campaign_id` → `sales_job_id`, not `job_id`, since `installation.jobs` already
   has its own `job_id` pkey).

<!-- original open-questions text preserved below for reference, now resolved -->

<details><summary>Original open-questions draft (resolved above)</summary>

1. **Job ID format going forward:**
   - Keep `JOB-<timestamp>` + `campaign_id` (recommended)?
   - Or adopt a fully human-readable format for `job_id` itself?
   - *Default recommendation: Keep both (job_id + campaign_id).*

2. **Status: should "operational_draft" exist?**
   - Recommended: Yes, it resolves the ADR-046 tension elegantly
   - Alternative: Start all jobs in regular "draft" status (skip operational_draft)
   - *Default recommendation: Add operational_draft.*

3. **Tour Planner's tour_id format:**
   - Keep current format (e.g., `2025-LENOVO-LENOVOIN-CHANDIGARH_BTL-01-T01`)?
   - Or switch to `TOUR-<timestamp>`?
   - *Default recommendation: Keep current format (human-readable, based on campaign_id; once campaign_id is in sales.jobs, this still works).*

4. **Should `installation.counters` be migrated or retained as-is?**
   - Retained as-is (current recommendation): Keeps counter-level tactical detail separate; `installation.jobs` becomes a grouping layer between `sales.jobs` and `installation.counters`
   - Migrated to `sales` schema: More consistent namespace, but adds to sales schema scope
   - *Default recommendation: Retain; link installation.jobs to sales.jobs instead of installation.campaigns.*

</details>

---

## Implementation Checklist (Surgeon's Pre-Flight)

Before starting **any phase**:
- [ ] Backup dev DB: `pg_dump -U lmadmin lm360 > backup_2026_07_11_pre_consolidation.sql`
- [ ] Create git branch: `git checkout -b consolidate/unified-job-record`
- [ ] Review this plan with Harish (design finalization)

Before **Phase 1** (schema migration):
- [ ] All Playwright tests pass on main
- [ ] Confirm current data state matches doc above (run verification queries)

Before **Phase 6** (drop old schema):
- [ ] All Playwright tests pass on branch (including new job creation, tour management, Hub jobs screen, Tour Planner save)
- [ ] Manual end-to-end: Sales create job → Hub sees it → Tour Planner optimizes → Tours appear
- [ ] Verify no orphaned records (run Phase 1 safety checks again)

Before **git commit** (end of Phase 7):
- [ ] All specs pass
- [ ] Branch is up-to-date with main
- [ ] ADRs are updated
- [ ] MDD files are current
- [ ] Deploy-prod.sh tested on a staging simulation (if applicable)

---

## Deliverables

**This document:** Architecture + phased migration plan (approved by Harish before build starts)

**Next step:** Launch `adr-generator` agent to draft ADR-**NEW** (Unified Job Record), which will cite this plan.

**Build phases:** Parallel to ADR authoring (phases 1-7 proceed in order; ADR review can happen concurrently)

---

## Appendix: SQL Schema Summary (Post-Migration)

```sql
-- NEW: sales.job_tours (replaces installation.tours)
CREATE TABLE sales.job_tours (
  tour_id text NOT NULL PRIMARY KEY,
  job_id text NOT NULL REFERENCES sales.jobs(job_id) ON DELETE CASCADE,
  title text,
  assigned_employees text[] NOT NULL DEFAULT '{}',
  start_date date,
  end_date date,
  status text NOT NULL DEFAULT 'planned',
  notes text,
  created_by text NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now(),
  FOREIGN KEY (job_id) REFERENCES sales.jobs(job_id) ON DELETE CASCADE
);

-- UPDATED: installation.jobs (relinked to sales.jobs)
-- installation.jobs keeps its OWN job_id (unrelated identity, counter-grouping PK).
-- The old campaign_id column is renamed sales_job_id and now points at sales.jobs.
-- Its existing tour_id → installation.tours FK is left as-is here — disposition
-- (drop, or repoint to sales.job_tours) is an explicit Phase 5 decision, not
-- pre-decided in this schema summary.
CREATE TABLE installation.jobs (
  job_id text NOT NULL PRIMARY KEY,
  client text,
  tour text,
  counter_count integer NOT NULL DEFAULT 0,
  status text NOT NULL DEFAULT 'active',
  created_by text NOT NULL DEFAULT 'unknown',
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now(),
  sales_job_id text,  -- renamed from campaign_id; points to sales.jobs.job_id
  tour_id text,       -- unchanged for now; see Phase 5
  FOREIGN KEY (sales_job_id) REFERENCES sales.jobs(job_id),
  FOREIGN KEY (tour_id) REFERENCES installation.tours(tour_id)  -- until Phase 5 decides
);

-- UPDATED: custodian.vouchers (adds FK)
ALTER TABLE custodian.vouchers
  ADD CONSTRAINT vouchers_job_id_fkey
  FOREIGN KEY (job_id) REFERENCES sales.jobs(job_id);

-- REMOVED (in Phase 6):
-- DROP TABLE installation.campaigns CASCADE;
-- DROP TABLE installation.tours CASCADE;
```

---

**Document Status:** Ready for Review  
**Next Action:** Harish approves plan → Launch adr-generator → Execute Phases 1-7