# Module Design Document — BTL Installation PWA

> ⚠️ **STALE re: unified job identity (ADR-126 §2, chunk 1A, 2026-08-01).**
> `installation.jobs` no longer has its own independent `job_id` primary key.
> Per ADR-126 (`docs/adr/ADR-126-effective-dated-many-to-many-bridges-and-unified-job-identity.md`),
> `sales.jobs.job_id` is the ONE job identity on the platform — no schema keeps a parallel
> one. `installation.jobs`'s `job_id` PK column was dropped; `sales_job_id` (already an
> FK → `sales.jobs(job_id)`) was made `NOT NULL` and promoted to `PRIMARY KEY`. The table
> was confirmed 0 rows before and after (dormant — Installation PWA is IndexedDB + Apps
> Script and never wrote to Postgres), so this was a pure DDL change, no data migrated.
> All other columns (`client`, `tour`, `counter_count`, `status`, `created_by`,
> `created_at`, `updated_at`, `tour_id`) are unchanged. Pre-migration DDL is archived at
> `docs/adr/migration-backups/installation_jobs_before_1A_20260801.sql`.
> `installation.counters` was separately rebuilt (chunk 1B, same day) as a bridge table
> whose `job_id` column now FKs `sales.jobs(job_id)` directly — it no longer FKs
> `installation.jobs` at all. §4.3/§4.4 code blocks below still show the pre-migration
> shape; treated as historical, not current state, same as the §4.1 note below.
>
> ⚠️ **STALE re: campaigns/tours (ADR-111, 2026-07-11/12).** `installation.campaigns` and
> `installation.tours` (§4.1, §4.3 below) **no longer exist** — dropped after every consumer
> was repointed to `sales.jobs`/`sales.job_tours`, which is now the single canonical job
> record platform-wide. `installation.jobs` (§4.2) and `installation.counters` are retained
> as tactical counter-level detail, but `installation.jobs`'s old `campaign_id` column is
> renamed `sales_job_id` and now FKs to `sales.jobs.job_id` directly (no more `campaigns`
> intermediary). See `docs/plan_job_consolidation_2026-07-11.md` and
> `docs/adr/ADR-111-...md` for the current, verified truth. §4.1/§4.3 schema tables below
> describe the removed tables — kept for historical reference, not current state.

**Status:** Complete (Phase 1) · 2026-07-03 · Reverse-engineered from codebase  
**URL:** `/installation/` (supervisor/field role selection)  
**Auth:** Supervisor: PIN via Apps Script bridge; Field: employee ID selection  
**Reports to:** Sales PWA (`sales.jobs` — canonical, ADR-111), Recce PWA (counter master), Dispatch PWA (assignment)

---

## 1. Why this exists

BTL (Below-The-Line) installation = physical branding installation at retail counters. A supervisor extracts counter details (location, item specs, dimensions, materials) from a PowerPoint deck, reviews/corrects them via AI (Ollama), then assigns work to field installation teams. Field persons check in at counters, capture proof photos, and mark installation status.

This PWA replaces a manual, email-based workflow where installation specs lived in Excel/PPT and had no centralized record of who did what where.

---

## 2. Inspiration vs scope

Reference systems studied:

| Source | What we borrow | What we skip |
|---|---|---|
| **Fieldwork / Capture apps** (Fulcrum, Esri Collector) | Role-based field check-in; location capture; photo proofs | Cloud sync (we use IndexedDB + Apps Script) |
| **Project management** (Asana, Monday.com) | Job → Task hierarchy; status rollup; assignment workflows | Gantt, dependencies, budget tracking |
| **PPTX extraction** (Office-365 automation, Zapier) | Slide text + metadata → structured data; vendor lookup | Full OLE/VBA parsing |
| **Offline-first PWA** (Recap, OneSIS) | Local drafts + sync-on-reconnect; service-worker caching | Peer sync (single source of truth = Apps Script) |

**Installation ≠ Recce.** Recce = field observation of existing branding. Installation = planned/scheduled physical work to install new branding. Installation tracks assignment, proof, and status; Recce tracks photos and audit.

---

## 3. Phases (as built)

| Phase | Scope | Status |
|---|---|---|
| **Phase 1 (current)** | Intake: PPTX → counter list (4 DB tables) · Supervisor PIN login · Field role (agent assign) · AI slide parser (Ollama + Claude.ai handoff) · Offline-first IndexedDB · Apps Script bridge for photo/data persistence | ✅ LIVE (v1.0.0) |

**Future phases (parked, not in this MDD):**
- Phase 2: Photo proof capture (mobile camera) + before/after mode
- Phase 3: GPS check-in at counter + geofence validation
- Phase 4: Real-time supervisor dashboard (heatmap, status, alerts)

---

## 4. Database schema

### 4.1 `installation.campaigns` — master container for a brand campaign

```sql
campaign_id         TEXT PK                 -- generated: "branding-hp-2026-q1"
year                INTEGER NOT NULL       -- fiscal year
brand               TEXT NOT NULL          -- "HP", "LENOVO", "LIEBHERR"
client              TEXT NOT NULL          -- client name (customer/partner)
campaign_name       TEXT NOT NULL          -- human-readable: "HP Refresh – North India"
phase               INTEGER NOT NULL DEFAULT 1   -- internal phase tracking
status              TEXT NOT NULL DEFAULT 'active'::text
                    CHECK IN ('active','paused','archived')
zones               TEXT[] NOT NULL DEFAULT '{}'::text[]  -- geographic zones for this campaign
created_by          TEXT NOT NULL          -- supervisor employee_id
created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
updated_by          TEXT                   -- triggers set this

-- Client visibility controls (Phase 2 feature, columns added early)
client_archived     BOOLEAN NOT NULL DEFAULT false
client_archived_at  TIMESTAMPTZ            -- when supervisor marked archived for client
client_archived_by  TEXT                   -- which supervisor archived it
client_archive_rule TEXT                   -- rule identifier (e.g. "hide_from_client_q2")
```

**Indexes:**
- `campaign_id` PK
- `(brand, year)` for lookup by brand+year
- `(status)` for active/archived filtering

**Trigger:** `trg_campaigns_upd` — BEFORE UPDATE, sets `updated_at` to now()

### 4.2 `installation.tours` — physical tour route (collection of counters for one agent)

```sql
tour_id             TEXT PK                 -- generated: "tour-hp-2026-delhi-01"
campaign_id         TEXT NOT NULL FK → installation.campaigns(campaign_id)
title               TEXT                    -- "North Delhi Q1 Rollout"
assigned_employees  TEXT[] NOT NULL DEFAULT '{}'::text[]  -- employee IDs assigned
start_date          DATE                    -- tour start date
end_date            DATE                    -- tour end date
status              TEXT NOT NULL DEFAULT 'planned'::text
                    CHECK IN ('planned','in_progress','completed','cancelled')
notes               TEXT                    -- supervisor notes for field team
created_by          TEXT NOT NULL
created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
```

**Indexes:**
- `tour_id` PK
- `(campaign_id)` FK
- `(status, start_date)` for active tours

**Trigger:** `trg_tours_upd` — BEFORE UPDATE, sets `updated_at` to now()

### 4.3 `installation.jobs` — a single job (one campaign instance, may span multiple tours)

**CURRENT (post ADR-126 chunk 1A, 2026-08-01) — verified via `\d installation.jobs`:**

```sql
sales_job_id        TEXT PK NOT NULL        -- FK → sales.jobs(job_id); the unified job identity
tour_id             TEXT FK → sales.job_tours(tour_id)
client              TEXT
tour                TEXT
counter_count       INTEGER NOT NULL DEFAULT 0
status              TEXT NOT NULL DEFAULT 'active'::text
created_by          TEXT NOT NULL DEFAULT 'unknown'::text
created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
```

No independent `job_id` column remains — `sales_job_id` is both the FK to `sales.jobs`
and the table's primary key. Trigger `trg_jobs_upd` unchanged.

**HISTORICAL (pre-migration shape, kept for context — do not use as current truth):**

```sql
job_id              TEXT PK                 -- generated: "job-hp-2026-mumbai-01"
campaign_id         TEXT FK → installation.campaigns(campaign_id)
tour_id             TEXT FK → installation.tours(tour_id)
client              TEXT                    -- denormalized for quick filter
tour                TEXT                    -- denormalized tour name
counter_count       INTEGER NOT NULL DEFAULT 0  -- count of counters in this job
status              TEXT NOT NULL DEFAULT 'active'::text
                    CHECK IN ('active','paused','completed','archived')
created_by          TEXT NOT NULL DEFAULT 'unknown'::text
created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
```

**Indexes:**
- `job_id` PK
- `(campaign_id)` FK
- `(tour_id)` FK
- `(status)` for active jobs

**Trigger:** `trg_jobs_upd` — BEFORE UPDATE, sets `updated_at` to now()

### 4.4 `installation.counters` — a single retail counter with installation specs

```sql
counter_id          TEXT PK                 -- generated: "cntr-hp-2026-0001"
job_id              TEXT NOT NULL FK → installation.jobs(job_id) ON DELETE CASCADE
slide_number        INTEGER                 -- slide number in original PPTX (for audit)

-- Location
counter_name        TEXT                    -- "Saini Electronics, Railway Rd"
city                TEXT
state               TEXT

-- Installation spec (extracted from slide or entered manually)
brand               TEXT                    -- brand being installed
item_type           TEXT                    -- "standee", "pole_sign", "inshop", "display_unit"
size                TEXT                    -- "6ft x 3ft", "4m pole", "2.4m x 1.2m"
material            TEXT                    -- "aluminum", "vinyl", "PVC", "steel"
qty                 TEXT                    -- quantity (e.g. "2", "1 set")

-- Installation notes
remarks_prod        TEXT                    -- production/fabrication remarks
remarks_inst        TEXT                    -- installation-specific remarks

-- Status + assignment
status              TEXT NOT NULL DEFAULT 'ready'::text
                    CHECK IN ('ready','assigned','in_progress','completed','held','cancelled')
assigned_to         TEXT                    -- employee_id assigned to install this
assigned_to_name    TEXT                    -- denormalized name

-- Location proof
plus_code           TEXT                    -- Google Plus Code for counter location
google_maps_url     TEXT                    -- pre-generated Google Maps link

-- Audit
created_by          TEXT
created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
```

**Indexes:**
- `counter_id` PK
- `(job_id)` FK
- `(status)` for filtering by status
- `(assigned_to, status)` for field-person's "my assigned work"

**Trigger:** `trg_counters_upd` — BEFORE UPDATE, sets `updated_at` to now()

---

### 4.5 PostgREST grants

All tables: `GRANT SELECT, INSERT, UPDATE, DELETE` to `web_anon` (permits full CRUD from PWA).

No row-level security policies — authorization enforced at PWA layer:
- Supervisors: via PIN login (validated server-side in Apps Script)
- Field persons: via employee_id selection (no auth barrier, trust-based per site policy)

---

## 5. API integration

### 5.1 Apps Script Bridge

The PWA does NOT call PostgreSQL directly. Instead, it posts to a **Google Apps Script deployment** (URL loaded from `/data/config.json` at runtime):

```javascript
async function appsCall(action, payload = {}) {
  const body = JSON.stringify({
    sessionToken: CFG.sessionToken,
    action,
    ...payload
  });
  const r = await fetch(CFG.appsScriptUrl, {
    method: 'POST',
    headers: { 'Content-Type': 'text/plain;charset=utf-8' },
    body
  });
  return r.json();
}
```

**Actions supported:**
- `login` — accept PIN, return sessionToken + supervisorId
- `getPptThumbnail` — slide number → PNG thumbnail (cached in IndexedDB)
- `saveCounters` — bulk-insert counters from extracted array
- `getCountersByJob` — fetch all counters for a given job_id
- `updateCounter` — PATCH one counter
- `assignCounters` — assign multiple counters to an agent

**Why Apps Script?**
- Bridging gap: field agents may be offline; local IndexedDB queues requests
- Photo proxy: Photos are stored in Google Drive (Apps Script can read/write Drive)
- Session validation: Apps Script validates PIN against hardcoded supervisor list (future: connect to Hub)

⚠️ **UNVERIFIED:** Apps Script endpoint URL, authentication mechanism, Drive folder structure for photo storage.

### 5.2 AI extraction paths

**Ollama (local Gemma 3.4B):**
- POST `/api/generate` on `http://72.60.97.173:11434` (configured in CFG.ollamaEndpoint)
- Extracts structured data from slide text via prompt-based extraction
- 3-min timeout (CFG.ollamaTimeoutMs = 180000)
- **Fallback:** Claude.ai handoff skill `btl-ppt-extractor` (manual Claude call via `?q=` URL param)

### 5.3 Service Worker caching (v1.0.1)

**Strategy:**
- Cache-first for static assets (HTML, CSS, icons, manifest)
- Network-first for `/version.json` (to detect updates)
- POST requests (Apps Script) never cached — fail closed offline

**CACHE_NAME:** `btl-install-v1.0.1`

---

## 6. Local state (IndexedDB)

**DB Name:** `btl-install-v3` | **Version:** 1

**Stores:**

| Store | KeyPath | Purpose |
|---|---|---|
| `jobs` | `jobId` | Downloaded jobs for offline reference |
| `counters` | `counterId` | Extracted counter rows (before approval) |
| `sessions` | `sessionId` | Supervisor session token + expiry (8 hours) |
| `drafts` | `id` | In-flight edits (correction flow state) |

**Example `sessions` row:**
```javascript
{
  sessionId: 'session',  // always literal key 'session'
  sessionToken: 'abc123xyz',
  supervisorId: 'harish',
  name: 'Harish Lal',
  createdAt: 1719072000000,
  expiresAt: 1719100800000  // +8 hours
}
```

---

## 7. Screens and workflows

### 7.1 Role selection (landing screen)

Two cards: Supervisor (PIN login) | Field Person (agent select).

**State:** `STATE.role = 'supervisor' | 'field'`

### 7.2 Supervisor flow

#### 7.2.1 Intake tab (default)

**Flow:**
1. **Upload PPTX** — drag/drop or file picker
   - Client-side parser (`parsePptx()`) extracts slides via JSZip + DOMParser
   - Returns: `[{ slideIndex, rawText, altTexts, thumbnailUrl }]`

2. **Select AI mode**
   - **Ollama (local):** press "Extract with Ollama" → streams to gemma3:4b
   - **Claude.ai handoff:** press "Handoff to Claude" → opens claude.ai with `?q=` param + PPT attachment
   - **Manual JSON paste:** supervisor pastes corrected JSON directly

3. **Correction flow** (per slide)
   - Shows slide thumbnail + extracted fields: brand, counter_name, city, state, item_type, size, material, qty, remarks_prod, remarks_inst, status
   - Edit fields inline
   - **Accept** → move to next slide
   - **Skip** → mark as hold/unclear, move to next
   - **Status dropdown:** ready | recce_pending | mockup_pending | unclear

4. **Bulk save**
   - All corrected counters → Apps Script `saveCounters` action
   - Creates DB rows, IndexedDB `counters` store
   - Toast: "✓ 24 counters saved"

#### 7.2.2 Mapping tab

- List all counters for the current job
- For each counter: **"📍 Find on Maps"** button → Google Maps search (counter_name + city)
- **"Plus Code"** button → supervisor prompts for Plus Code, saves to DB

#### 7.2.3 Allocation tab

- List all counters
- **Person selector:** dropdown of MANPOWER array
- For each counter: **"→ Assign"** button → assign to selected person
- Updates `assigned_to`, `assigned_to_name`
- Bulk-write to Apps Script

#### 7.2.4 Supervisor session

- **Header:** "BTL Installation" + "SUPERVISOR" badge + Hub link + Logout button
- **Subtitle:** Shows current job name + counter count + last sync timestamp
- **Offline banner:** appears when navigator.onLine = false

### 7.3 Field person flow

#### 7.3.1 Role selection (agent select tab)

- Shows grid of MANPOWER array (8 predefined agents)
- Tap agent → loads their assigned counters into `STATE.fieldAgent = { id, name }`

#### 7.3.2 Counter tile list

- Per counter: card showing `counter_name`, `city`, `status`, `assigned_to`
- **Tap to open detail** (future: capture photo, GPS check-in)
- **Tab: Agent Select** → return to agent grid

#### 7.3.3 Field session

- **Header:** "BTL Installation" + "FIELD PERSON" badge (yellow accent) + Logout button
- **Subtitle:** Agent name + assigned counter count
- Dark mode by default (`body.field-mode` class)

---

## 8. Authentication & Authorization

### 8.1 Supervisor

- **PIN login screen** (6-digit numeric password)
- Validated against Apps Script `/login` endpoint (⚠️ UNVERIFIED where PIN list lives)
- Returns: `{ sessionToken, supervisorId, name }`
- Token stored in IndexedDB `sessions` store, valid for 8 hours
- On app reload: check session expiry; if valid, skip PIN screen

### 8.2 Field person

- **Self-select from MANPOWER grid** (employee list hardcoded in JS)
- No authentication; trust-based (assumes field person has device)
- Sets `STATE.fieldAgent = { id, name }`

---

## 9. Data flow: PPTX → Counters

```
┌─ Supervisor uploads PPTX ─┐
│
├─ Client-side JSZip parser ──→ Extract slides + text
│
├─ (A) Ollama path: POST /api/generate → gemma3 extraction ──→ JSON array
│ └─ Timeout 180s; on abort, show error toast
│
├─ (B) Claude.ai path: Open URL with ?q= + file attach ──→ User extracts manually ──→ Paste JSON
│
├─ (C) Manual JSON paste: supervisor types/pastes JSON directly
│
└─ Correction flow: per-slide review + edit ──→ Accept/Skip each counter
   │
   └─ Finalize: POST `appsCall('saveCounters', { counters: [...] })`
      │
      └─ Apps Script inserts to installation.counters + returns result
         │
         └─ IndexedDB store('counters').put(...) + toast
```

---

## 10. Roles and permissions

| Action | Supervisor | Field Person |
|---|---|---|
| Upload PPTX, extract via AI | ✓ | ✗ |
| Review + correct slide data | ✓ | ✗ |
| Create/edit counters | ✓ | ✗ |
| Assign counter to agent | ✓ | ✗ |
| Find counter on maps | ✓ | ✓ (view only) |
| View my assigned counters | ✗ | ✓ |
| (Future) Capture proof photo | ✗ | ✓ |
| (Future) GPS check-in | ✗ | ✓ |

---

## 11. Integration touchpoints

| PWA | What changes | Notes |
|---|---|---|
| **Recce** (`/recce/`) | — | Counter master from Installation can be linked in Phase 2; for now, independent |
| **Sales** (`/sales/`) | — | `sales.jobs.campaign_id` FK added in migrate_campaigns_v1.sql (2026-01-01) to link sales jobs to campaigns |
| **Dispatch** (`/dispatch/`) | — | Future: installation jobs can trigger dispatch assignments |
| **Hub** (`/hub/`) | PWA registered with `access_group='installation'` | Users with installation role can open `/installation/` |
| **Tour Planner** (`/tour-planner/`) | ⚠️ **latent bug, found during ADR-126 chunk 1A grep (2026-08-01)** | `tour-planner/index.html` `loadCounters()` (~line 707) does query Postgres directly: `GET {API}/jobs?sales_job_id=eq.{campaign.job_id}` with `Accept-Profile: installation`, then reads `j.job_id` off each result to build the counters filter. `installation.jobs.job_id` no longer exists post-1A. **Not an active break today** — `installation.jobs` has 0 rows, so the response is `[]`, `.map(j => j.job_id)` is a no-op, and the function early-returns before the bad reference is ever evaluated. But if `installation.jobs` is ever populated under the new (post-1A) schema, this two-hop lookup silently fails (`job_id` reads as `undefined`, so the follow-up `counters?job_id=in.(...)` call is malformed). This contradicts the plan's assumption that nothing live reads `installation.jobs` via Postgres — it does, from Tour Planner, not from the Installation PWA itself. The whole two-hop pattern (fetch installation job rows → map to ids → query counters by those ids) is obsolete now that `installation.counters` (chunk 1B) bridges `sales.jobs` directly — this needs a Tour Planner frontend fix, out of scope for chunk 1A, flagged here for follow-up. |

---

## 12. Migration (code-verified, /var/www/360lm/installation/)

**migrate_campaigns_v1.sql** (lines 1–21):
- Adds `zones`, `client_archived`, `client_archived_at`, `client_archived_by`, `client_archive_rule` to `installation.campaigns` (Phase 2 feature flags, added early)
- Adds `campaign_id` FK to `sales.jobs` (cross-PWA link)
- Creates index on `sales.jobs(campaign_id)` WHERE campaign_id IS NOT NULL
- Emits NOTIFY to PostgREST to reload schema

---

## 13. Configuration

**Hardcoded in index.html, lines 806–819:**

```javascript
const CFG = {
  configUrl: '/data/config.json',              // Apps Script URL loaded at runtime
  appsScriptUrl: '',                           // populated from config.json
  sessionToken: '',                            // populated after login
  version: '2.0.0',
  ollamaEndpoint: 'http://72.60.97.173:11434/api/generate',  // Ollama local endpoint
  ollamaModel: 'gemma3:4b',
  ollamaTimeoutMs: 180000,                     // 3 min
  claudeAiUrl: 'https://claude.ai/new',        // Claude.ai handoff
  skillHint: 'btl-ppt-extractor',              // skill for handoff
  fuzzyThreshold: 0.75,                        // (for future use)
  maxImageKB: 800,                             // image size cap for future photo feature
  sessionExpiryHours: 8
};
```

**Manpower (hardcoded, lines 822–831):**
```javascript
const MANPOWER = [
  { id:'mukesh',     name:'Mukesh',     role:'Installation' },
  { id:'sachin',     name:'Sachin',     role:'Installation' },
  // ... 6 more
];
```

---

## 14. Acceptance checklist

- [x] 4 DB tables created + indexed (campaigns, tours, jobs, counters)
- [x] PostgREST grants (SELECT, INSERT, UPDATE, DELETE to web_anon)
- [x] Supervisor PIN login (Apps Script bridge)
- [x] PPTX upload → client-side parse → slide extraction
- [x] 3 AI extraction paths: Ollama, Claude.ai handoff, manual JSON paste
- [x] Correction flow: per-slide review + edit + save
- [x] IndexedDB local state (jobs, counters, sessions, drafts)
- [x] Service Worker cache (static-first, network-first for version.json)
- [x] Field person: agent select + counter list (no photo capture yet)
- [x] Offline support (online banner, local queue)
- [x] safe-bottom.css loaded (screen respect mobile nav)
- [x] Hub registration (PWA accessible from hub)

---

## 15. Known limitations & future phases

| Item | Status |
|---|---|
| Photo proof capture | 🔲 Phase 2 — mobile camera integration |
| GPS geofence validation | 🔲 Phase 2 — location check-in at counter |
| Real-time supervisor dashboard | 🔲 Phase 3 — heatmap + status alerts |
| Tally/QuickBooks export | 🔲 Future — cost tracking for installations |
| Audit log (full history) | 🔲 Future — via pg_audit or separate table |
| Unified Job ID across PWAs | ⚠️ Parked — depends on core.job_registry ADR |

---

## 16. Glossary

| Term | Meaning |
|---|---|
| **Campaign** | A brand's planned installation push (e.g., "HP North India Q1") |
| **Tour** | A physical route/assignment (collection of counters) for a field team |
| **Job** | One job instance (unique DB row for a campaign/tour combo) |
| **Counter** | A single retail outlet where branding will be installed |
| **BTL** | Below-The-Line marketing (in-store, outdoor, experiential, non-digital-media) |
| **Proof** | Photo/GPS/Plus Code evidence that work was done |
| **Slide correction** | Manual review + fix of AI-extracted counter data before DB save |

---

## 16a. ADRs governing this module

- **ADR-009:** each PWA owns dedicated schema (implemented: installation.campaigns/tours/jobs/counters — though see the note below on who actually writes them)
- **ADR-011/012:** PIN auth + hub SSO (⚠️ partial — Installation uses a dual-role model: supervisor PIN login is standard, but field-person selection is a hardcoded MANPOWER array, not the standard hub-session flow; nav link to `/hub/` is a plain button, correctly not a gate)
- **ADR-081:** safe-bottom.css (implemented — verified in `releases/v1.0.0/index.html`)
- **ADR-106:** cross-schema access is grant-gated by convention — **notable case**: Installation does not use PostgREST at all for its own operation (bridges via Google Apps Script), yet its `installation` schema tables are directly read AND written by `sales`, `tour-planner`, and `hub` via PostgREST (ratified per ADR-106 §4 — no competing Installation-owned RPC exists to bypass, since Installation's own writes go through a different channel entirely). Worth a future decision on whether `installation` schema should get its own RPC layer if this pattern keeps growing.

---

## Verification

**File paths per claim:**

| Claim | File | Line(s) | Evidence |
|---|---|---|---|
| 4 DB tables exist | `/var/www/360lm/installation/migrate_campaigns_v1.sql` | alter table commands | SQL ALTER TABLE statements |
| SW v1.0.1 deployed | `/var/www/360lm/installation/releases/v1.0.0/service-worker.js` | 9–10 | `const VERSION = '1.0.1'` |
| IndexedDB stores: jobs, counters, sessions, drafts | `/var/www/360lm/installation/releases/v1.0.0/index.html` | 844–851 | `db.createObjectStore('jobs'|'counters'|'sessions'|'drafts')` |
| Supervisor role selection flow | `/var/www/360lm/installation/releases/v1.0.0/index.html` | 996–1010 | `APP.selectRole()` + `APP.verifyPin()` |
| Field agent grid (MANPOWER array) | `/var/www/360lm/installation/releases/v1.0.0/index.html` | 822–831 | `const MANPOWER = [...]` (8 agents) |
| Ollama endpoint config | `/var/www/360lm/installation/releases/v1.0.0/index.html` | 811 | `ollamaEndpoint: 'http://72.60.97.173:11434/api/generate'` |
| Apps Script bridge (appsCall) | `/var/www/360lm/installation/releases/v1.0.0/index.html` | 887–900 | `async function appsCall(action, payload = {})` |
| PPTX parser (JSZip + DOMParser) | `/var/www/360lm/installation/releases/v1.0.0/index.html` | 905–937 | `async function parsePptx(file)` |
| PostgREST grants to web_anon | `docker compose exec -T postgres psql` | — | SELECT, INSERT, UPDATE, DELETE all granted to `web_anon` on installation.* tables |
| Campaigns table schema + triggers | `docker compose exec -T postgres psql \d installation.campaigns` | — | 15 columns, FK refs, trigger `trg_campaigns_upd` |
| Jobs table schema | `docker compose exec -T postgres psql \d installation.jobs` | — | 10 columns, FKs to campaigns + tours, trigger `trg_jobs_upd` |
| Counters table schema | `docker compose exec -T postgres psql \d installation.counters` | — | 21 columns, FK to jobs, status enum, trigger `trg_counters_upd` |
| Tours table schema | `docker compose exec -T postgres psql \d installation.tours` | — | 11 columns, FK to campaigns, assigned_employees array |

**⚠️ UNVERIFIED items:**

1. Apps Script URL, endpoint authentication, PIN validation logic (depends on `/data/config.json` loading at runtime)
2. Google Drive folder structure for photo storage (referred to in appsCall but not seen in code)
3. Ollama container accessibility from VPS (IP 72.60.97.173 — need to verify connectivity)
4. Claude.ai handoff URL generation (`buildHandoffUrl()`) — not used in live flow, manual Claude.ai invocation expected
5. PostgREST connection string / PGRST_DB_SCHEMAS includes `installation` (assumed based on schema existing, not verified in compose)

---

**END of MDD — Installation PWA is feature-complete for Phase 1 (intake + mapping + allocation). Photo capture deferred to Phase 2.**