Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Building a Tenant Payment Logging System: Email-to-Payment Automation with AWS Lambda and Google Apps Script

We recently built a complete payment logging workflow for a property management tenant portal. The system allows property managers to forward bank payment notifications (Zelle transfers) directly to a Lambda-powered email handler, which automatically logs the payment and notifies the tenant hub. Here's how we architected it.

The Problem

Previously, manual payment logging required the property manager to type payment information into chat or a form—error-prone and unscalable. We needed a system where forwarding a Zelle payment email would automatically:

  • Parse the payment amount and sender
  • Log it to a persistent receipts registry
  • Update the tenant hub in real-time
  • All within the dangerouscentaur.com domain (not externally visible)

Architecture Overview

The system comprises four layers:

  • Email Ingestion: Amazon SES receiving Zelle forwarded emails at a dedicated payments@dangerouscentaur.com alias
  • Message Processing: Lambda function that parses email content and extracts payment data
  • Command Dispatch: Google Apps Script detecting payment patterns and calling the logging endpoint
  • Data Storage: S3-backed receipts JSON file served to the tenant hub

Infrastructure Setup

SES Domain Verification

We initiated SES domain verification for dangerouscentaur.com via the AWS Console, requesting DKIM tokens to add to the domain's DNS records. This ensures emails sent from payments@dangerouscentaur.com pass SPF and DKIM validation, preventing deliverability issues.

Commands run:

aws ses verify-domain-identity --domain dangerouscentaur.com --region us-east-1
aws ses verify-domain-dkim --domain dangerouscentaur.com --region us-east-1

The DKIM tokens were then added as CNAME records in the domain's DNS provider.

Lambda Functions

Two Lambda functions power the payment system:

  • lambda-email-parser (/scripts/lambda-email-parser/lambda_function.py): Receives raw email from SES, parses headers and body, extracts payment metadata
  • lambda-receipt-action (/scripts/lambda-receipt-action/lambda_function.py): Accepts structured payment data via HTTP POST from the Apps Script handler, validates against an ADMIN_TOKEN, and writes to S3

Both are deployed with Function URLs enabled (no API Gateway), allowing direct HTTPS invocation with query parameters or request bodies.

S3 and CloudFront

The tenant hub is hosted on S3 behind CloudFront (distribution ID: E2X4A1B2C3D4E5F6G, example). The hub's index.html at /Users/cb/Documents/repos/sites/dangerouscentaur/demos/3028fiftyfirststreet.92105.dangerouscentaur.com/index.html is deployed to S3 at a path like s3://dangerouscentaur-tenant-hubs/3028-51st/index.html.

When payments are logged, we invalidate CloudFront cache patterns to ensure the hub immediately reflects updated receipt data:

aws cloudfront create-invalidation \
  --distribution-id E2X4A1B2C3D4E5F6G \
  --paths "/*" \
  --region us-east-1

The Payment Logging Flow

Step 1: Email Ingestion

The property manager receives a Zelle payment notification email from their bank. They forward it to payments@dangerouscentaur.com.

SES receives the email and triggers a Lambda invocation (configured via SES receipt rule). The lambda-email-parser function extracts:

  • Sender email/name (to identify the tenant)
  • Amount (via regex on common Zelle patterns: "You sent $X.XX")
  • Timestamp (from email headers)
  • Reference note (if included in forward subject/body)

Step 2: Parsing and Publishing

The parser Lambda publishes a structured message. This could go to SQS, SNS, or directly to the next step. In this implementation, we chose direct invocation of the receipt-action Lambda via the Python boto3 client:

import boto3
import json

lambda_client = boto3.client('lambda')

response = lambda_client.invoke(
    FunctionName='lambda-receipt-action',
    InvocationType='RequestResponse',
    Payload=json.dumps({
        'action': 'log_rent_payment',
        'tenant_id': '3028',
        'amount': 2500.00,
        'method': 'zelle',
        'timestamp': '2024-01-15T14:30:00Z',
        'admin_token': os.environ['ADMIN_TOKEN']
    })
)

Step 3: Receipt Action Handler

The lambda-receipt-action Lambda validates the request:

  • Checks that admin_token matches the environment variable ADMIN_TOKEN (set via Lambda configuration)
  • Reads the current receipts.json from S3
  • Appends the new payment entry
  • Writes the updated JSON back to S3
  • Returns a 200 response with the logged entry

The receipt JSON structure is:

{
  "receipts": [
    {
      "id": "zelle_20240115_143000",
      "tenant_id": "3028",
      "amount": 2500.00,
      "method": "zelle",
      "timestamp": "2024-01-15T14:30:00Z",
      "logged_at": "2024-01-15T14:32:15Z",
      "note": "Security deposit"
    }
  ]
}

Step 4: Tenant Hub Updates

The hub's JavaScript (embedded in index.html) polls the receipts endpoint on load. The receipts section displays all logged payments with date, amount, and method. After S3 is updated and CloudFront is invalidated, the next hub page load fetches the fresh data.

Google Apps Script Integration

We enhanced the existing WarmLeadResponder.gs file to include a command handler for payment logging. When a Google Sheet receives a forwarded Zelle email (via email import), the Apps Script detects the payment pattern and calls the Lambda receipt-action endpoint:

function onPaymentEmailReceived(subject, body) {
  const adminToken = PropertiesService.getScriptProperties().getProperty('ADMIN_TOKEN');
  const lambdaUrl = PropertiesService.getScriptProperties().