# ADR-096: Recce Excel Approval Form — One-Time Token Submission + DataValidation

## Status

Accepted, 2026-07-01.

## Status History

```yaml
status_history:
  - date: 2026-07-01
    status: Proposed
    changed_by: hkl
    reason: |
      Recce PWA currently accepts PPTM approval forms (uploaded by supervisors).
      Expanding to Excel (.xlsm) approval form gives clients an alternative
      submission path: supervisors email a pre-filled approval sheet; clients fill
      Status & Comments columns using DataValidation dropdowns; clients submit
      via embedded VBA macro. Documenting token lifecycle, schema changes, and
      channel tracking before production rollout.
    changed_via: adr-kit (360lm)
  - date: 2026-07-01
    status: Accepted
    changed_by: hkl
    reason: |
      Excel approval form complements PPTM approach: clients can choose the tool
      they prefer (Office desktop app vs. supervisor upload). One-time tokens
      prevent replay attacks and duplicate submissions. Enum refactor
      (approved / approved_with_comments / resubmission_required) makes status
      semantics clearer than the prior (approved / rejected / changes_needed).
      Channel tracking enables audit and future filtering (e.g. show only
      xlsx_direct submissions in dashboard).
    changed_via: adr-kit (360lm)
```

## Context

The Recce PWA currently supports **PPTM approval forms** (ADR-087):
- Supervisor generates PPTM with instructions and pre-filled branding items
- Client opens file in PowerPoint, enters Status (approved/rejected/changes_needed) and Comments in named shapes
- Supervisor parses returned PPTM and logs approvals to DB
- Source tracked in `branding_approvals.source = 'pptm_upload'`

**Problem:** The prior PPTM upload path had a **content-type mismatch issue** when supervisors uploaded `.pptm` files; Excel has a stable, unambiguous MIME type (`application/vnd.openxmlformats-officedocument.spreadsheetml.sheet`), avoiding format negotiation headaches. Additionally, some clients prefer email-based workflows or don't have PowerPoint installed. A second submission path (Excel) addresses both the technical friction and user preference, increasing adoption without forcing clients to learn new tools.

**Design constraints:**
- Must prevent duplicate submissions (one token per submission)
- Token must be single-use (can't resubmit the same form twice)
- Token must expire (30 days) to avoid stale forms floating in email
- Must track channel (pptm_upload vs xlsx_direct) for audit
- Must keep status enum consistent between PPTM and Excel paths

## Decision

### 1. Approval Status Enum Refactor

Replace `(approved | rejected | changes_needed)` with clearer semantics:

```sql
approved | approved_with_comments | resubmission_required
```

Mapping:
- Old `approved` → New `approved`
- Old `rejected` → New `resubmission_required`
- Old `changes_needed` → New `approved_with_comments`

Applied to:
- `branding_approvals.approval_status` CHECK constraint
- `pptx_submission_log.channel` now also includes `xlsx_direct`
- Migration `migrate_recce_v18` backfills existing rows (2026-07-01)
- Applied to both dev (`lm360`) and prod (`lm360_prod`)

### 2. One-Time Token Lifecycle

New table: `recce.excel_submission_tokens`

```sql
CREATE TABLE recce.excel_submission_tokens (
  token_id BIGSERIAL PRIMARY KEY,
  sub_id TEXT NOT NULL REFERENCES recce.submissions(sub_id) ON DELETE CASCADE,
  token TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ DEFAULT now(),
  created_by TEXT NOT NULL,
  expires_at TIMESTAMPTZ NOT NULL,
  used_at TIMESTAMPTZ,
  used_by TEXT,
  CONSTRAINT "only_one_use" CHECK (used_at IS NOT NULL OR expires_at > now())
);
```

**Token Generation:**
- Created when supervisor downloads Excel form (via `/slides-proxy/gen-xlsm` request)
- Format: `secrets.token_urlsafe(32)` (Python) — 43 ASCII chars, URL-safe
- Stored in token TEXT UNIQUE column
- `expires_at = now() + 30 days`
- Embedded in Excel file (hidden `_meta` sheet, cell C2)

**Token Validation (submission):**
- Client opens Excel, fills Status/Comments cells, clicks Submit button (VBA macro)
- VBA macro reads token from _meta sheet, sends POST to `/submit-excel` with token + responses
- slides-proxy `_submit_excel_approvals()`:
  1. SELECT token row WHERE token = request.token AND used_at IS NULL AND expires_at > now()
  2. If no row or used_at NOT NULL → return 403 Forbidden (token already used or expired)
  3. If valid → atomically UPDATE used_at = now(), used_by = client_direct
  4. Upsert `branding_approvals` (source = xlsx_direct)
  5. Log to `pptx_submission_log` (channel = xlsx_direct, submitted_by = client_direct)

**Consequence:** Token can only be submitted once. If client clicks Submit twice, second attempt fails. Client must download a fresh form (new token) to resubmit.

### 3. Excel Generation Pipeline

**slides-proxy `_gen_xlsm()` function:**

```python
def _gen_xlsm(sub_id: str) -> bytes:
    """
    Generate one-time Excel approval form for a Recce submission.
    
    Steps:
    1. Load template: /opt/slides-proxy/excel_template_base.xlsm
    2. Keep VBA intact: keep_vba=True (openpyxl)
    3. Generate one-time token: token = secrets.token_urlsafe(32)
    4. Populate _meta sheet:
       - A1: "Submission ID", B1: sub_id
       - A2: "Token", B2: token (masked in UI, read by VBA)
       - A3: "Endpoint", B3: "https://srv1111289.hstgr.cloud/slides-proxy/submit-excel"
       - A4: "Count", B4: len(branding_items)
    5. For each branding (1-indexed):
       - Rename sheet: BA_1, BA_2, BA_3, ...
       - Embed photo: XLImage via openpyxl, anchored at **cell B5** (2×2 inch preview)
       - Add DataValidation dropdown to Status cell (BA_N!C:C):
         options: ["Approved", "Approved with Comments", "Re-submission Required"]
       - Add comment cell input box (BA_N!D:D)
       - Set sheet protection (password: 360dm, allow: formatCells, insertRows, deleteRows)
    6. Hide surplus sheets (if template has 10 BA_ sheets but submission has 5)
    7. Insert token row in excel_submission_tokens table
    8. Return binary blob (bytes)
    """
```

**VBA Macro (SubmitApprovals):**
- Read token from _meta sheet (C2)
- Read Status column (C:C) from all visible BA_* sheets
- Read Comments column (D:D) from all visible BA_* sheets
- Use `WinHttp.WinHttpRequest.5.1` to POST JSON to endpoint: `{sub_id, token, approvals: [{branding_idx, status, comment}, ...]}`
  - Header: `Content-Type: application/json`
  - Header: `Authorization: Bearer <token>`
  - Method: POST
  - URL: endpoint from _meta sheet (C3)
- Wait for 200 OK → show "Submission successful" toast
- On error → show "Token expired or invalid" / "Network error" message from response

**Critical: branding_idx offset (0-based DB, 1-based VBA)**
- Excel sheets are named **1-indexed** for human readability: `BA_1`, `BA_2`, `BA_3`, ...
- VBA sends **1-based** `branding_idx` values (1, 2, 3, ...) in the JSON payload
- Server receives the 1-based index and converts: `stored_branding_idx = int(raw_idx) - 1` before writing to DB
- DB column `branding_approvals.branding_idx` is **0-indexed** (0, 1, 2, ...)
- This prevents off-by-one bugs: VBA logic works with human-friendly 1-based sheet names; DB stores 0-based indexes

### 4. Path-Based Routing in slides-proxy

`do_POST()` method updated:

```python
def do_POST(self):
    path = self.path
    
    # Path-based dispatch BEFORE JSON type check
    if path.endswith('/submit-excel'):
        return self._submit_excel_approvals()
    
    # Existing JSON dispatch
    try:
        body = json.loads(self.rfile.read(...))
        request_type = body.get('type')
        
        if request_type == 'gen-xlsm':
            return self._gen_xlsm(body['subId'])
        elif request_type == 'gen-pptx':
            return self._gen_pptx(body['subId'])
        elif request_type == 'gen-pptm':
            return self._gen_pptm(body['subId'])
        ...
```

**Rationale:** POST bodies from VBA are `application/x-www-form-urlencoded` or raw bytes (not JSON). Path-based dispatch handles both JSON and form-encoded requests cleanly.

### 5. PWA UI Changes (index.html)

**Admin card button:**
```html
<button class="btn btn-xlsm" onclick="downloadRecceXlsm(subId)">
  ⬇ Approval Form (Excel)
</button>
```

- CSS: dark green `#0f7a3c`, width `1.4fr` (same as PPTX button)
- Position: between "PDF" and "PPTM" buttons
- i18n keys: `av_xlsm_btn` (EN: "Approval Form (Excel)"), `tst_xlsm_generating`, `tst_xlsm_ready`

**JavaScript:**
```javascript
async function downloadRecceXlsm(subId) {
  const btn = event.target;
  btn.disabled = true;
  btn.textContent = t('tst_xlsm_generating');
  
  try {
    const resp = await fetch('/slides-proxy/', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({ type: 'gen-xlsm', subId })
    });
    
    const blob = await resp.blob();
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = `${subId}_approval_${yyyymmdd()}.xlsm`;
    a.click();
    
    toast(t('tst_xlsm_ready'));
  } catch (err) {
    toast('Error: ' + err.message);
  } finally {
    btn.disabled = false;
    btn.textContent = t('av_xlsm_btn');
  }
}
```

### 6. Approval Status Display

**branding_approvals UI pills (index.html):**

```css
.approved { background: #10b981; color: white; } /* green */
.approved_with_comments { background: #f59e0b; color: white; } /* amber */
.resubmission_required { background: #ef4444; color: white; } /* red */
```

Enum text (i18n):
- EN: `approved` → "Approved" | `approved_with_comments` → "Approved with Comments" | `resubmission_required` → "Re-submission Required"
- HI: (translation keys: `status_approved`, `status_approved_with_comments`, `status_resubmission_required`)

### 7. Channel Tracking in pptx_submission_log

Extended CHECK constraint:

```sql
ALTER TABLE recce.pptx_submission_log 
  ADD CONSTRAINT channel_check CHECK (channel IN ('pptm_upload', 'xlsx_direct'));
```

**Semantics:**
- `pptm_upload` — supervisor uploaded a returned .pptm file (existing)
- `xlsx_direct` — client submitted Excel form directly via /submit-excel (new)

Used for audit: "Show me all approvals from direct Excel submissions" → filter WHERE channel = 'xlsx_direct'

## Consequences

**Positive:**
- Clients gain choice: PPTM (supervisor-mediated) or Excel (direct submission)
- One-time tokens prevent accidental replay submissions
- 30-day TTL balances usability (supervisor can re-send form anytime) with security
- Enum clarity: `resubmission_required` is immediately obvious vs. old `rejected`
- Channel tracking enables future filtering and audit

**Negative / Watch:**
- VBA macro must be signed or trusted by client (MOTW may block unsigned macros from email)
  - Mitigation: supervisor generates form in-app (not sent via email attachment)
  - If email necessary: recommend client right-click → Properties → Unblock before opening
- Token expiry (30 days) means client can't submit a 2-week-old form
  - Mitigation: supervisor re-generates form on request (new token, fresh token sent via email)
- Excel-only tokens are not extensible to other formats
  - Mitigation: if adding PDF/CSV approval paths later, create separate token tables (pdf_submission_tokens, etc.) or add `submission_format` column to unified table

**Future:**
- Phase 5: Show xlsx_direct submissions in branding_approvals UI (badge: "Excel Direct", timestamp)
- Phase 6: Supervisor fallback — extend upload modal to accept .xlsm (parse C22/C25 status cells, same as PPTM)
- Phase 7: If PPTM approval form becomes less popular, deprecate it and consolidate to Excel-only

## File Locations & Commits

| File | Path | Commit |
|------|------|--------|
| DB migration v18 | `db/migrations/migrate_recce_v18.sql` | `6ea3c57` |
| slides-proxy generator | `opt/slides-proxy/slides_proxy.py` `_gen_xlsm()` | `668cfd5` |
| slides-proxy submit handler | `opt/slides-proxy/slides_proxy.py` `_submit_excel_approvals()` | `668cfd5` |
| Excel template | `/opt/slides-proxy/excel_template_base.xlsm` | (user-created) |
| Recce PWA index.html | `/var/www/360lm/recce/index.html` (button + JS) | `5b7f90a` |
| Recce i18n keys | `/var/www/360lm/recce/index.html` (EN/HI dicts) | `5b7f90a` |
| SW version bump | `recce-v37 → recce-v38` | `5b7f90a` |

## References

- `db/migrations/migrate_recce_v18.sql` — enum + token table
- `/opt/slides-proxy/slides_proxy.py` — `_gen_xlsm()` + `_submit_excel_approvals()`
- `/var/www/360lm/recce/index.html` — UI button + JS handler
- ADR-087 — Recce PPTM approval form (predecessor, complements this decision)
- ADR-095 — BTL Client Deck Contract (similar one-time-use pattern via PowerPoint)