Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Multi-Source Payment Reconciliation for Charter Bookings: Bridging GAS, Stripe, and External Payment Channels

What Was Done

During a development session focused on verifying payment status for a same-day ash scattering charter (April 29, 12–3 PM), we executed a comprehensive payment reconciliation workflow that spanned three distinct data sources: Google Apps Script (GAS) booking sheets, Stripe transaction logs via Lambda, and external business payment channels. The goal was to determine whether the charter guest had completed payment and surface any payment gaps before service delivery.

The session revealed a critical architectural gap: high-value, low-frequency charter events often bypass the centralized Stripe payment system and are collected through external channels (Zelle, cash, personal PayPal), leaving the booking system with incomplete payment visibility. This post documents the reconciliation approach, the tools used, and the architectural lessons learned.

Technical Details: The Multi-Source Query Pattern

The reconciliation process followed this sequence:

  • Step 1: Calendar Source of Truth — Queried the JADA Google Calendar via Lambda to retrieve all events for the target date (April 29). This confirmed the ash scattering event and guest name.
  • Step 2: Booking Sheet Audit — Read all tabs across the JADA booking requests sheet (stored in Google Sheets) to locate real (non-test) entries matching the date and guest profile. The sheet contains multiple tabs: charter bookings, booking requests, funeral/ash scattering, and corporate group bookings. Each tab was read sequentially to avoid false matches on test data.
  • Step 3: Stripe Transaction Query — Called the Lambda function that wraps the Stripe API to retrieve recent payments. This returned two successful transactions for the date range (April 22–24):
    • Jolee Brunton (joleebrunton@mac.com) — $250 — April 22
    • Jennifer Sanderson (jennifer.sanderson@talkiatry.com) — $500 — April 24
  • Step 4: Cross-Reference and Gap Analysis — Matched Stripe payments against booking sheet records. The $250 payment from Jolee Brunton had no corresponding full booking record in GAS; the $500 payment was clearly tied to a May 12 corporate group charter.
  • Step 5: External Channel Assumption — Based on domain knowledge (ash scattering events are often coordinated through Carole's external business, burialsatseasandiego.com), inferred that the April 29 payment was likely collected outside Stripe and not reflected in our payment system.

Infrastructure and Data Flow

The query infrastructure consists of:

  • Google Calendar API — Accessed via AWS Lambda function (endpoint: GetJADACalendarEvents) to retrieve calendar metadata. The function reads credentials from repos.env and returns structured event data (date, time, title, attendees).
  • Google Sheets API — Read access to multiple booking sheet tabs via GAS and Lambda. Sheet structure:
    • Booking Requests Tab — new inquiries and pending confirmations
    • Funeral/Ash Scattering Tab — dedicated record for scattering service bookings
    • Corporate Group Tab — bulk bookings and event charters
    Credentials validated from repos.env.
  • Stripe API via Lambda — Called via wrapper function to retrieve payment records. The Lambda reads Stripe credentials from environment variables and queries the transaction log with filters:
    • created[gte]=1714521600 (April 22, 00:00 UTC)
    • created[lte]=1714780800 (April 24, 23:59 UTC)
    • status=succeeded
  • Google Drive — Spot-checked for any attached payment confirmations or receipts in the ash scattering booking folder. This was a secondary check; no payments were found in Drive.

Key Decisions and Design Patterns

Why Three Data Sources? The JADA charter operation is split between a modern digital booking flow (GAS + Stripe) and legacy external channels (Carole's independent business). A single-source query would miss half the payments. The multi-source pattern ensures we can flag incomplete payments and send alerts before service time.

Why Credentials in repos.env? Centralized credential management in repos.env reduces the risk of hardcoded secrets in Lambda code or sheets macros. Each function (Lambda, GAS) reads from this single source, making rotation and auditing straightforward.

Why Check GAS Tabs Individually? The booking sheets contain both test and real data. Reading all tabs sequentially (rather than assuming a single "source of truth" tab) ensures we capture legitimate bookings that may be recorded in domain-specific tabs (e.g., funeral scattering vs. corporate). This approach also surfaced the fact that no booking record existed for April 29 in GAS, which is itself valuable information.

Partial Payment Detection: The $250 Stripe payment is flagged as ambiguous because ash scattering charters typically cost $800–$1,200+. This partial amount suggests either a deposit (with balance owed) or an unrelated transaction. The decision was to flag this for manual review rather than assume it covers the day's event.

Findings and Reconciliation Gap

The reconciliation revealed a critical limitation: Stripe is not the single source of truth for charter revenue. Events coordinated through Carole's external business (burialsatseasandiego.com) bypass the JADA Stripe account entirely. This creates a visibility gap in revenue reporting and payment verification.

For the April 29 ash scattering:

  • No booking record exists in GAS for April 29.
  • No complete Stripe payment is linked to the event.
  • The $250 Jolee Brunton payment (April 22) is a possible deposit but unconfirmed.
  • Payment was likely collected by Carole via non-Stripe channels.

What's Next

To close this gap, the recommended next steps are:

  • Direct Verification: Contact Carole via text or phone to confirm payment status for April 29. This is faster and more reliable than automated reconciliation for external channels.
  • Process Improvement: Implement a PaymentSourceTag field in the booking sheet that indicates whether payment is Stripe, external, or pending. This allows automated systems to route verification requests appropriately.
  • Lambda Enhancement: Extend the Stripe query function to return metadata about payment intent and booking reference. This makes cross-referencing with GAS easier.
  • External Payment Webhook: If Carole's business integrates with a payment processor (PayPal, Square), consider pulling transaction logs from those services on a daily cadence to create a unified payment ledger in DynamoDB or a Google Sheet.

The session demonstrated that manual inspection and domain knowledge remain essential in hybrid payment systems. Automated reconciliation works well for a single channel; multiple channels require either a unified payment backend or a robust manual override process with clear ownership.

```