Sync payments into the Program Monthly Reconciliation Google Sheet

[maebeale]

Context

Staff hand-maintain a monthly reconciliation Google Sheet (Program Monthly Reconciliation List) that tracks, per program, who owes what and what's been received. Today it's fully manual. This enhancement keeps it in sync with the app so staff stop transcribing payments by hand.

The sheet's structure (from the uploaded August 2026 sample):

• Rows are grouped into sections with a hand-typed header in column A and blank spacer rows: AWBW Membership Fees, On-Demand Training, Misc Refunds, Continuing Education Hours, Misc Payments, and one per Facilitator Training (TAC262 July 2026 Facilitator Training, TAC262 November 2026 Facilitator Training).
• Header columns: A spacer · B Name (contact person) · C Agency (organization) · D Requested (expected $) · E Received (paid $) · F Payment For (program code, e.g. TAC262, or membership year) · G Payment Method (CC, Check #101066) · H Payment Received (date, or a manual "Deadline …" while unpaid) · I Notes (free text).
• Rows exist before payment: a registrant appears with Requested filled and Received blank, then Received / Payment Method / date fill in when money arrives.

Decisions (confirmed with product owner)

• Trigger: both — automatic on create/update and a manual "Sync to sheet" button.
• Scope: all payments (cash, check, Stripe).
• Layout: write directly into this sectioned layout — insert/update the right row under the right section, preserving manual headers, Notes, and deadline text.
• Behavior: mirror the sheet as it works today — include expected/unpaid rows (Requested filled, Received blank) that get completed when the payment lands.

Reframe: the row unit is a registration/invoice, not a raw Payment

Each data row = one payer's obligation to one program (an EventRegistration, ContinuingEducationRegistration, or MembershipInvoice — the allocatable), where:

• Requested (D) = the obligation's cost.
• Received (E) = sum of Allocation amounts posted to it.
• Payment Method (G) / Payment Received (H) = derived from the Payment(s) behind those allocations (CC for Stripe, Check #<n> for check, Cash for cash; date from the payment).

So a payment event updates the Received/Method/date of an existing (or new) row; a new registration creates the Requested row.

Approach

Section routing — program → sheet section

Map each allocatable to its section header text (Facilitator Training event → that training's header; Membership → AWBW Membership Fees; CE → Continuing Education Hours; refund → Misc Refunds; unmapped → Misc Payments). A GoogleSheets::SectionRouter derives the section from Event attributes (code + session label) with a small config fallback for the fixed sections.

Row identity / upsert in a hand-edited sheet (core risk)

The sheet has no ID column and staff edit it by hand, so reliable row matching is the hard part.

• Add a hidden key column holding the allocatable GID; match on that column only — never on visible cells — so re-syncs are idempotent and never clobber manual edits.
• Best-effort adopt pre-existing manual rows once via (section + Name + Agency); otherwise append within the section.
• Never overwrite app-not-owned cells: I (Notes) always manual; H only set to Received M/D when paid, leaving manual "Deadline …" text untouched while unpaid.

Components

• Row builder (decorator): reconciliation_row on the allocatable's decorator (or a shared ReconciliationRow PORO) → ordered cells B..H, money via dollars_from_cents, sentence-case labels.
• Service: app/services/google_sheets/reconciliation_sheet.rb (PORO) — service-account auth (Google::Auth::ServiceAccountCredentials + Google::Apis::SheetsV4::SheetsService), #upsert(allocatable) that reads the tab, resolves/ensures the section, and updates the keyed row or InsertDimensions a new row under the right header (value_input_option: "USER_ENTERED"). No-op guard when ENV/credentials absent.
• Job: app/jobs/sync_reconciliation_row_job.rb — perform(allocatable_gid) on Solid Queue (inline in dev); tolerates deletion.
• Automatic triggers: after_commit on Allocation (covers Received/Method/date — all payment paths funnel through allocations: manual PaymentsController#create, PayChargeExtensions#create_external_processor_payment, and the app/webhooks/ processors) and on EventRegistration / ContinuingEducationRegistration / MembershipInvoice (creates/updates the Requested row before any payment).
• Manual button + backfill: POST /payments/:id/sync_to_sheet (admin-only via policy, honoring the return_to/eyebrow convention) + a button on the payment/registration admin view; a payments:sync_reconciliation_sheet rake task for the one-time backfill.
• Config (ENV, matching Stripe/AWS): GOOGLE_SHEETS_CREDENTIALS_JSON, PAYMENTS_SHEET_ID (1aQ6…rO6Q), PAYMENTS_SHEET_TAB in .env.sample; integration no-ops until set (safe to merge before credentials land).
• Gems: add google-apis-sheets_v4 + googleauth; update AGENTS.md.

Prerequisite (product/ops, out of code scope)

Create a Google Cloud project, enable the Google Sheets API, create a service account + JSON key, and share the spreadsheet as Editor with the service-account email. The integration stays inert until the ENV vars are provided.

Key risks / open items

• Row matching in a hand-edited sheet — hidden-key column + "only touch owned cells" is the mitigation; validate against a copy of the real sheet before enabling production writes.
• Section/program mapping (esp. Facilitator Training headers with session labels) — confirm the Event code/label source during build.
• H column dual meaning (Deadline vs Received) — app only writes Received M/D, never the deadline.

Verification

• Without ENV set: creating a payment no-ops cleanly (log line, no error).
• With a test service account + a copy of this sheet: creating a registration adds a Requested row under the right section; posting a cash/check/Stripe payment fills Received/Method/date on the same row (no duplicate, Notes untouched); editing updates it.
• Manual button pushes/updates on demand; non-admin gets 403.
• Backfill rake task seeds header + hidden key + all current rows once.
• ai/test_extra green before PR.

Tests

Service (stubbed Sheets client), section router, row/decorator, model after_commit callbacks, job, request (sync_to_sheet authorization), policy.