Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Building a Multi-Tenant Utility Portal with Embedded Google Sheets and Automated Receipt Management

Over the past development session, we built out a comprehensive tenant portal for a San Diego rental property (3028 51st Street, 92105) that handles utility billing reconciliation, receipt management, and rent credit tracking. This post covers the architectural decisions, infrastructure setup, and implementation details for what became a surprisingly complex system for managing shared utilities across multiple tenants.

What Was Built

The core deliverable is a static web portal deployed to S3 with CloudFront distribution, accessible at 3028fiftyfirststreet.92105.dangerouscentaur.com. The system tracks:

  • Monthly utility bills (electric, gas, water, solar, internet) paid by the landlord
  • Tenant rent payments and credits for utility overages
  • Receipt uploads and approval workflows
  • Historical billing data spanning multiple months
  • A shared Google Sheet embedded directly in the portal for real-time data visibility

The architecture combines static HTML/JSON on S3 with server-side Python scripts for Google Sheets integration and data transformation.

Data Structure and JSON Schema

The portal uses a JSON-first approach stored in /data/:

utilities.json — Monthly utility costs and rent adjustments:

{
  "rent": 3200,
  "rent_credit": 0,
  "pending_bills": ["electric", "gas", "water"],
  "utilities": [
    {"name": "Solar", "cost": 146.35, "status": "fixed"},
    {"name": "Cox Internet", "cost": 111.07, "status": "fixed"},
    {"name": "Electric", "cost": 40.80, "status": "pending"},
    {"name": "Gas", "cost": 87.72, "status": "pending"},
    {"name": "Water", "cost": 174.87, "status": "pending"}
  ],
  "total_utilities": 560.81
}

This structure explicitly marks which utilities are awaiting actual bill data. The tenant sees total rent due, but understands that utility adjustments are provisional.

receipts.json — Three-queue receipt management system:

{
  "pending": [],
  "approved": [],
  "rejected": [],
  "payments": []
}

The three-queue pattern allows for receipt auditing: landlord uploads receipts through the portal, they queue in pending, get reviewed and moved to approved, then are associated with payment records. This prevents accidental duplicate submissions and provides an audit trail.

bills.json — Historical billing archive:

{
  "2026-03": {
    "period": "March 2026",
    "utilities": [...],
    "total": 560.81,
    "source": "sdge_statement"
  }
}

Monthly snapshots allow the tenant to review past billing without cluttering the current month view.

Google Sheets Integration Strategy

The portal embeds a Google Sheet created via service account credentials. Rather than querying the sheet API on every page load, we use a hybrid approach:

  • The sheet is the source of truth for detailed monthly breakdowns and running totals
  • Each month gets its own tab in the sheet
  • An embedded iFrame displays the sheet directly in the portal (read-only for the tenant)
  • Python scripts (create-tenant-sheet.py, update-sheet-data.py) sync JSON data into the sheet for backup and reconciliation

The sheet ownership was transferred to the property account (3028fiftyfirststreet92105@gmail.com) rather than remaining under the service account. This ensures the landlord retains ownership if the service account credentials are rotated or archived.

Sheet creation happens via the Google Sheets API using service account credentials stored in shared/secrets/financial_sheets_sa.json. The embed URL pattern is:

https://docs.google.com/spreadsheets/d/{SHEET_ID}/edit?usp=sharing

This is placed in the portal's main dashboard to give both parties instant visibility into the running totals.

Receipt Management and Storage Architecture

Receipt images are stored in two locations with different access patterns:

Active receipts — /receipts/ served directly from S3:

  • Current month's receipt images go in /receipts/202605/
  • CloudFront caches these with a short TTL for frequent access
  • The receipts viewer page (/receipts/index.html) lists and displays images dynamically from receipts.json

Archive receipts — /archive/receipts/ for historical access:

  • Once a billing cycle closes, images move to /archive/receipts/202603/, /archive/receipts/202604/, etc.
  • Separate CloudFront cache policy with longer TTL (lower cost for infrequent access)
  • Still indexed in receipts.json so tenants can retrieve historical documents

This tiered approach balances cost (old receipts cached longer, fewer revalidations) with usability (current receipts always fresh, historical access still available).

Utility Statement Pages

Three static documents were generated from the May 2026 bill images:

  • /docs/electric-statement.html
  • /docs/gas-statement.html
  • /docs/water-statement.html

These are static HTML renders of the utility bills, allowing the tenant to reference them without depending on image loading reliability. They're served from S3 with cache headers for long-term storage (these don't change once generated).

Deployment Pipeline

Changes are deployed to S3 via command-line tools (AWS CLI). Key buckets:

  • dangerouscentaur-demos — Primary static site bucket
  • CloudFront distribution: routed through Route53 DNS record for 3028fiftyfirststreet.92105.dangerouscentaur.com

Deployment workflow:

# Sync HTML files (invalidates CloudFront)
aws s3 cp index.html s3://dangerouscentaur-demos/demos/3028fiftyfirststreet.92105.dangerouscentaur.com/ --cache-control "max-age=3600"

# Sync JSON data (shorter TTL for dynamic content)
aws s3 cp data/utilities.json s3://dangerouscentaur-demos/demos/3028fiftyfirststreet.92105.dangerouscentaur.com/data/ --cache-control "max-age=300"

# Sync receipt images (longer TTL, infrequently updated)
aws s3 cp receipts/ s3://dangerouscentaur-demos/demos/3028fiftyfirststreet.92105.dangerouscentaur.com/receipts/ --recursive --cache-control "max-age=86400"