Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

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

Over the past development session, we built out a complete tenant-facing utility portal for a San Diego rental property (3028 51st Street) with integrated receipt management, utility tracking, and rent credit accounting. This article breaks down the architecture, infrastructure decisions, and technical implementation.

Project Overview

The core challenge: a property manager needs to track shared utility costs (electric, gas, water, solar, internet), apply credits to tenant rent based on consumption patterns, manage receipt documentation, and provide tenants read-only visibility into their billing. The solution spans static HTML, JSON data stores, Python automation scripts, Google Sheets embedding, and AWS S3/CloudFront delivery.

File Structure and Data Architecture

The portal lives at /Users/cb/Documents/repos/sites/dangerouscentaur/demos/3028fiftyfirststreet.92105.dangerouscentaur.com/ with this structure:


3028fiftyfirststreet.92105.dangerouscentaur.com/
├── index.html                    # Main tenant portal
├── receipts/
│   └── index.html                # Receipt viewer/upload interface
├── docs/
│   ├── electric-statement.html
│   ├── gas-statement.html
│   └── water-statement.html
├── data/
│   ├── utilities.json            # Current month rates and credits
│   ├── bills.json                # Historical billing data
│   └── receipts.json             # Receipt queue and payment records
├── scripts/
│   ├── create-tenant-sheet.py    # Google Sheets provisioning
│   ├── update-sheet-data.py      # Monthly data sync to Sheets
│   └── transfer-sheet-ownership.py  # Ownership management
└── receipts/
    └── [monthly folders with receipt images]

We use JSON as the source of truth rather than a traditional database. This keeps the system stateless and deployable to static hosting (S3) while maintaining simplicity for a single property. The trade-off: limited concurrent update safety, but acceptable given low write frequency.

Data Models

utilities.json tracks current month rates and tenant credits:

{
  "month": "2026-05",
  "rent_base": 3200,
  "rent_credit": 0,
  "pending_bills": {
    "electric": true,
    "gas": true,
    "water": true
  },
  "utilities": {
    "solar": 146.35,
    "electric": 40.80,
    "gas": 87.72,
    "water": 174.87,
    "cox": 111.07
  }
}

The pending_bills object flags which utilities haven't arrived yet. The portal shows these as "Pending" rather than zero, improving transparency. rent_credit is calculated from utility highlights (portions of the bill the tenant is responsible for) and deducted from their monthly rent.

receipts.json implements a simple three-queue system:

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

Tenants upload receipt images through the web form, which adds entries to the pending queue. The property manager reviews and moves them to approved/rejected. The payments array tracks rent payments received, reconciled against submitted receipts.

Google Sheets Integration

We created a shared Google Sheet via create-tenant-sheet.py, provisioned with a service account that has edit permissions and transferred ownership to the tenant-facing Gmail account (3028fiftyfirststreet92105@gmail.com).

Why a Sheet instead of embedding JSON directly? Sheets provide:

  • Audit trail: Google Sheets tracks all edits with timestamps and user attribution
  • Monthly tabs: Easy to create new tabs (202605, 202606, etc.) without refactoring the data schema
  • Formula support: Rent credit calculations, utility totals, and payment reconciliation can use formulas that auto-update
  • Tenant visibility without write access: Embed the Sheet read-only; tenant can view but cannot modify
  • External comment capability: Tenant and manager can leave comments for coordination

The embed URL is programmatically generated and injected into index.html via a template variable. Monthly data sync (via update-sheet-data.py) pushes JSON values to named ranges in the current month's tab.

Receipt Management Architecture

Receipt images are organized by month in the local directory:

/receipts/202605/electric/IMG_4516.jpg

These are synced to S3 at deployment and served through CloudFront. The receipt viewer (receipts/index.html) is a single-page app that:

  • Reads data/receipts.json to populate the upload form and queue displays
  • Displays thumbnail galleries of uploaded receipts grouped by utility type and month
  • Provides a simple approval workflow for the property manager (approve/reject buttons)
  • Tracks which receipts are "stale" (older than 90 days) and archives them to an /archive/ S3 prefix while keeping them accessible

The key decision: store receipt images in S3, reference them by path in receipts.json, rather than embedding as base64 blobs. This keeps JSON files lean, simplifies versioning, and makes it trivial to delete old receipts without data migration.

Infrastructure and Deployment

The portal is deployed to S3 bucket dangerouscentaur-s3-demo under the path demos/3028fiftyfirststreet.92105.dangerouscentaur.com/. This is served via CloudFront distribution with TLS and custom domain routing through Route53.

Deployment workflow (from the session logs):


# Upload HTML, JSON, receipt images, and statement PDFs to S3
aws s3 sync . s3://dangerouscentaur-s3-demo/demos/3028fiftyfirststreet.92105.dangerouscentaur.com/ \
  --exclude ".git/*" \
  --exclude "scripts/*" \
  --include "*.html" \
  --include "data/*.json" \
  --include "receipts/**" \
  --include "docs/**"

# Invalidate CloudFront cache to force refresh
aws cloudfront create-invalidation \
  --distribution-id [DIST_ID] \
  --paths "/*"

The scripts/ directory (containing Python provisioning tools) is not deployed to S3, since these are operational scripts run locally only.

Key Decisions and Trade-offs

JSON over Database: We chose JSON files over a database (DynamoDB, RDS) for simplicity and because the property has one tenant and low write frequency. The entire state is version-controllable and deployable as static files. The downside: no native locking, so concurrent edits could conflict. Mitigation: establish a convention that only the property manager edits data files; tenants only upload via the web form.

Shared Google Sheet as Single Source of Truth for Monthly Data: Rather than syn