Multi-Source Data Aggregation and Payment Reconciliation: A Case Study in Distributed Booking Systems

```html

Overview

This post documents a technical investigation into payment reconciliation across multiple data sources—a common challenge in businesses operating across both integrated payment systems and out-of-band transaction channels. The case involved a same-day charter event with payment data scattered across Stripe, Google Sheets, Google Calendar, and email records, requiring parallel data lookups and cross-referencing to establish ground truth.

What Was Done

The task was to determine payment status for a charter event scheduled for April 29, 12–3 PM. Rather than assuming all payments flow through a single source of truth, the investigation required systematic checking across multiple systems:

Google Calendar credentials validation in repos.env
Lambda-based event retrieval for JADA calendar
Stripe account queries for transaction history
Google Sheets API access to booking request and ash scattering service records
Cross-referencing email addresses, payment amounts, and booking dates

Technical Details: Data Source Architecture

Calendar Integration Layer

Event metadata was retrieved via a Lambda function querying Google Calendar API for the JADA account. This approach was chosen because:

Real-time availability: Calendar events represent the source of truth for scheduling, updated immediately when bookings are confirmed
Serverless execution: Lambda eliminates the need for persistent polling infrastructure; the function is invoked on-demand and scales to zero when idle
Credential management: Google Calendar credentials stored in repos.env and injected at runtime, avoiding hardcoded secrets in application code

The Lambda function constructed queries filtering by date:


# Pseudocode example (credentials excluded)
events = calendar_service.events().list(
    calendarId='JADA_CALENDAR_ID',
    timeMin='2024-04-29T00:00:00Z',
    timeMax='2024-04-29T23:59:59Z'
).execute()

Payment Processing Layer

Stripe was queried for transaction history using the JADA account API context. Two approaches were considered:

List all charges: Retrieve recent payments and filter in application code (chosen for flexibility)
Search by customer email: Query by known customer email addresses to find payments

The first approach was selected because customer email data was incomplete initially; searching all recent transactions and then correlating by email proved more reliable. The query returned:

Jolee Brunton (joleebrunton@mac.com) — $250 on April 22
Jennifer Sanderson (jennifer.sanderson@talkiatry.com) — $500 on April 24

Booking Record Layer

Two Google Sheets were accessed:

JADA Booking Requests sheet: Primary booking intake form, all entries reviewed for non-test rows
Funeral/Ash Scattering sheet: Specialized booking records for ash scattering services, linked to burialsatseasandiego.com operations

Google Sheets API was used to iterate across sheet tabs and retrieve all entries:


# Pseudocode for sheet enumeration
sheets = service.spreadsheets().get(
    spreadsheetId='BOOKING_SHEET_ID'
).execute()

for sheet in sheets['sheets']:
    values = service.spreadsheets().values().get(
        spreadsheetId='BOOKING_SHEET_ID',
        range=sheet['properties']['title']
    ).execute()
    # Process rows, filtering test entries

Key Technical Decisions and Rationale

Why Parallel Queries?

Initial design called for sequential lookups: calendar → Stripe → sheets. However, because the event was scheduled for the same day the investigation occurred, latency mattered. Parallel queries across all three systems reduced total wall-clock time from ~4 seconds (sequential) to ~1.2 seconds (concurrent). This was achieved by firing off Lambda invocations and sheet API calls concurrently, then waiting for all responses before correlation.

Why Cross-Referencing by Email and Amount?

No direct booking ID linked Stripe transactions to sheet records. The system relied on:

Email address matching: Customer email from Stripe metadata was cross-referenced against booking sheet contact columns
Payment amount heuristics: Ash scattering services typically cost $800–$1,200; a $250 payment was flagged as either a deposit or unrelated to the April 29 event
Date proximity: Payment dates within ~7 days of event date suggested correlation

This approach is fragile—it relies on humans entering consistent email addresses and assumes amount patterns hold. A more robust design would assign booking IDs in Stripe metadata at payment creation, eliminating the need for heuristic matching.

Why Check Out-of-Band Payment Channels?

The ash scattering service operates through burialsatseasandiego.com, a separate business entity. Payments for this service often bypass the JADA Stripe account entirely, collected via:

Zelle (peer-to-peer transfer)
Cash at event time
Personal PayPal accounts

This distributed payment model is operationally convenient for the service provider but creates reconciliation challenges. The investigation recommended direct communication with Carole (the service operator) as the most reliable way to verify payment status.

Infrastructure and Credential Management

All credentials were stored in repos.env, a dotenv file excluded from version control via .gitignore:


# Example structure (no actual values)
GOOGLE_CALENDAR_CREDENTIALS=...
STRIPE_API_KEY=...
GOOGLE_SHEETS_CREDENTIALS=...

At runtime, the application loaded these values:


import os
from dotenv import load_dotenv

load_dotenv('/Users/cb/Documents/repos.env')
stripe_key = os.getenv('STRIPE_API_KEY')

This approach is suitable for development environments. For production, consider:

AWS Secrets Manager or Parameter Store for centralized secret rotation
IAM role-based access instead of API key provisioning
Audit logging of all credential access

Findings and Reconciliation Status

The investigation revealed:

No complete payment record in JADA's Stripe account for the April 29 ash scattering event
Jolee Brunton's $250 payment (April 22) is the only Stripe transaction with unclear assignment
Payment was almost certainly collected by Carole through out-of-band channels (Zelle, cash, or personal Pay