# Build Task: QBO Write Queue Sheet + Apps Script Worker **From:** Ledger **To:** Riv (Slate can assist on Apps Script if needed) **Date opened:** 2026-05-20 **Priority:** P1 — blocking all Afton cleanup execution beyond Step 1 queueing **Status:** OPEN — not started --- ## Why this is needed now The Afton cleanup write queue (`C:\PKA\Team\Riv\j2_qbo_mcp\write_queue.json`) currently has **61 pending sparse Customer updates** from `afton_customer_cleanup.py --apply` (run 2026-05-20T17:41) with no approval/execution path. Going forward, the queue will receive much larger and higher-stakes batches: - 40 duplicate payment voids (Step 4) - 5 reconciliation adjustment JE reversals (~$800K, Step 6) - AR reclassifications, payment applications, ongoing close work A per-write CLI confirmation flow against the raw JSON file is unworkable. The original architecture (per `afton_qbo_cleanup_checkpoint.md` and `project_ledger_qbo_access` memory) calls for a Google Sheet + Apps Script worker. Build it now before more batches stack up. Per [[user-jimmie-adhd]]: visible queues with batch approval are the design pattern — the Sheet IS that queue. --- ## Build specification ### 1. Google Sheet — name: `QBO Write Queue` One tab, schema mirrors `write_queue.json` with worker-writeback columns added: | Col | Field | Source / Behavior | |---|---|---| | A | `queue_id` | from `id` | | B | `client` | from `client` (e.g. `afton`) | | C | `timestamp` | from `timestamp` | | D | `tool` | from `tool_called` | | E | `tier` | from `tier` | | F | `summary` | **Parsed from `payload_json`** — human-readable: `entity_type` + `Id` + what's changing (e.g. `Customer 1027: clear FamilyName, rename "Aaron Elmendorf null" → "Aaron Elmendorf"`) | | G | `payload_json` | full raw JSON for audit | | H | `requested_by` | from `requested_by` | | I | **`Approve`** | EMPTY by default — Jimmie drops `Y` here. Data validation: `Y` / blank / `N` | | J | `status` | Worker-written: `Pending` / `Approved` / `Executed` / `Failed` | | K | `qbo_txn_id` | Worker-written on success | | L | `error` | Worker-written on failure | | M | `executed_at` | Worker-written timestamp on success or failure | **Freeze row 1.** **Conditional formatting:** green row when status=`Executed`, red when `Failed`, yellow when `Approved` (in flight). ### 2. Apps Script worker **Trigger:** time-based, every 1 minute (Apps Script max frequency). Optional: also fire on edit trigger when col I is touched. **Loop:** 1. Read all rows where `Approve=Y` AND `status=Pending` 2. For each, call the corresponding `j2-qbo` MCP tool based on col D (`tool`) + col G (`payload_json`) - Initial tool support: `update_transaction` (covers all 61 pending rows) - Extend to other tools as future batches require 3. On success: write `Executed` + `qbo_txn_id` + `executed_at` 4. On failure: write `Failed` + `error` + `executed_at` — do NOT retry; failed rows need human review **Chairman fence (mandatory, belt + suspenders):** - If `client` ∈ {`looptv`, `vipplay`, `fuzebox`} → worker refuses execution, sets status=`Failed`, error=`Chairman group hard fence — manual only` - The j2-qbo MCP server should already enforce this; the worker enforces it again at the seam **Auth:** Apps Script needs credentials to reach the MCP. Two viable paths: - (a) Apps Script calls the MCP server's HTTP interface directly with a shared token in script properties - (b) Apps Script writes `Approved` status only, and a local Python worker (poll loop on Jimmie's machine) does the actual MCP calls **Recommend (b)** — keeps QBO credentials on the local machine, no cloud exposure of refresh tokens. The Apps Script is just the UI layer; the existing `qbo_client.py` does the work. ### 3. Initial seed On first deploy, populate the Sheet with the **current 61 Pending rows** from `write_queue.json` so Jimmie can start approving immediately. ### 4. Bidirectional sync (Sheet ↔ JSON) - **Sheet is canonical** for Approve state and worker-writeback fields - **JSON is canonical** for new writes (anything Ledger/Riv/scripts enqueue lands in JSON first) - New JSON entries appear in the Sheet within ~1 minute (worker append) - Updates to Approve flow from Sheet → JSON via worker - The `mcp__j2-qbo__list_queue` MCP tool should keep reading from JSON for consistency --- ## Acceptance criteria - [ ] Sheet exists at a known URL, schema matches §1 - [ ] All 61 current `write_queue.json` rows are visible in the Sheet with parsed `summary` field - [ ] Dropping `Y` in col I on Aaron Elmendorf (Id 1027) row triggers a successful sparse update against Afton QBO realm within ~1 minute - [ ] `status` flips to `Executed`, `qbo_txn_id` is populated, `executed_at` is set - [ ] Customer 1027 in QBO now has `FamilyName=""` and `DisplayName="Aaron Elmendorf"` - [ ] Failure path tested: invalid SyncToken → row goes to `Failed` with error message, no retry - [ ] Chairman fence tested: drop a row with `client=vipplay` + `Y`, verify worker refuses --- ## References - `Team Inbox/afton_qbo_cleanup_checkpoint.md` — full cleanup plan, Step 4 of which is this build - `C:\PKA\Atlas\app\j2_qbo_mcp_server.py` — MCP server (already live) - `C:\PKA\Team\Riv\j2_qbo_mcp\queue_backend.py` — queue read/write module - `C:\PKA\Team\Riv\j2_qbo_mcp\write_queue.json` — current queue file (61 Pending rows as of 2026-05-20T17:41) - `C:\PKA\Team\Riv\j2_qbo_mcp\afton_customer_cleanup.py` — script that produced the current Pending batch - Memory: [[project-ledger-qbo-access]], [[user-jimmie-adhd]], [[feedback-jimmie-ship-speed-preference]] --- ## Notes for Riv - Tier graduation tracking can ship later — first cut just needs Pending→Executed flow working. Tier auto-promotion logic is a v2. - Edit trigger on col I would be nice for instant approval feel, but 1-minute polling is fine for v1. - If you go the local-Python-worker route (recommended), it can run as a PM2 process alongside Atlas — coordinate with Atlas for runtime sharing per [[reference-atlas-runtime]]. - Once this ships, the get_payment endpoint for j2-qbo MCP (Step 2 of the cleanup checkpoint) becomes the next unlock for batched payment voids.