ZZZ Digital Lab

Google Sheets CRM repair: a synthetic portfolio demo

Synthetic data only. Self-initiated demonstration; no client engagement or independent human certification is claimed.

I use AI coding tools; every change ships with automated tests and a written handoff.

This project demonstrates a bounded repair to an existing Apps Script CRM: accept form leads safely, preserve the client's manual columns, and explain failures. It uses CSV fixtures and a small in-repo Node mock. No Google account or network is required.

Before and after

Reproduce locally

Requires Node 20+; no npm packages or installation needed. Run from this folder:

node tests/run.cjs
node demo.cjs

The tests include deliberately broken baseline behavior; PASS for a before: case means the defect was reproduced. The generated evidence is in evidence/tests.json, evidence/demo.json, evidence/after-leads.csv and evidence/structured-log.jsonl.

Before / after

Failure Broken baseline Repair and automated proof
Duplicate leads Every row is appended on every execution Normalize email, stable submission IDs, scan existing output; repeated execution and cursor reset tested
Timezone and dates Host parser guesses ambiguous dates Explicit ISO offsets or yyyy-MM-dd HH:mm:ss in Asia/Singapore; ambiguous and impossible dates rejected
Trigger double-fire No shared lock Script lock held through read/write/checkpoint; competing and nested executions tested
Quota and time limits One write per row, unlimited work Batch writes, row/time/read limits, cursor-based continuation; simulated quota failure and elapsed budget tested
Unsafe writes Rewrites A:H, erasing notes/formulas Schema checks and owned A:F append only; notes and formula preserved; formula-like contact text escaped
No diagnostics Partial changes with no explanation Structured code/row/count logs, no contact values, default dry-run preview
Write/checkpoint interruption No recovery strategy Checkpoint after successful write; crash after write is recovered using output deduplication

The main fixture contains 8 submissions and 1 existing lead. The repair adds 3 leads, skips 2 duplicates and rejects 3 invalid rows. Final lead count: 4. A direct replay adds 0. Read the rejection log before accepting the batch.

Structure and contract

Email validation is intentionally conservative: the first character must be a letter, digit or underscore; formula-capable prefixes are refused. Broader address syntax requires an agreed rule.

Business rules for this sample: one new lead per normalized email; existing lead details are not overwritten; the first valid submission wins. Conflicting submission IDs are rejected. Existing duplicate/invalid CRM records cause a fail-closed diagnostic, not automatic deletion. Columns G onward are client-owned. This is an append-only inbox; resetting or reordering it requires an agreed cursor reset and replay review.

Limits and review

17 tests were recorded locally. Google Apps Script itself was not run or deployed. The mock does not prove Google quota behavior, real multi-process locking, permissions, actual trigger delivery or platform atomicity. It emulates only the APIs exercised by the tests and supports local date parsing for UTC and Asia/Singapore. The real script uses Google's Utilities service.

The 5,000-row default input/output caps avoid pretending this small design is an unlimited migration tool. A single service call can still overrun the cooperative time budget. A quota exception stops the run, logs a code, releases the lock and leaves the last committed cursor for a later run; it does not grant more quota or automatically install a retry trigger. Existing duplicate cleanup, lead updates, cross-spreadsheet synchronization, emails and external APIs need separate scope. Script locks only coordinate this script; unrelated writers require an agreed maintenance window. Rejected rows need human review; corrected rows must be replayed after an approved cursor reset.

Review covered schema ownership, row mapping, write ordering, cursor replay, injection handling and failure paths. It was performed in the same engineering session using AI coding tools. Client acceptance should use a copy of the spreadsheet and the actual runtime before production.

Replay and recovery evidence

License: MIT. No secrets, customer files or account access are included.

Regenerate portfolio images

After generating the evidence, optionally run python -B make_images.py. This requires locally installed Pillow; the PDF project also uses PyMuPDF for source-page thumbnails and openpyxl for stored-workbook previews. No font or image asset is downloaded. The PNGs are 1000x750 and derived from the recorded outputs. Native app screenshots are not claimed.