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 Reconciliation Workflows

Over the past development session, we constructed a comprehensive tenant management portal for a residential property at 3028 51st Street, San Diego (92105). The system needed to handle utility cost allocation, rent credit tracking, receipt management, and real-time data reconciliation across multiple data sources. This post details the architecture, infrastructure decisions, and implementation patterns we used to make it production-ready.

The Problem Statement

The property owner was manually tracking:

  • Monthly utility bills (electric, gas, water, solar, internet) that needed to be split with tenants
  • Receipt images and payment records scattered across multiple folders
  • Rent pricing that fluctuated based on utility credits and pending bill data
  • No centralized, auditable record system for either party

The solution required a web portal where tenants could view their rent, utility charges, payment history, and receipt documentation—while the property manager maintained a backend reconciliation system for billing corrections.

Architecture Overview

We deployed a static site hosted on S3 with CloudFront distribution, backed by JSON data files and a Google Sheet for collaborative reconciliation:

  • Frontend: Static HTML/CSS/JavaScript at `/Users/cb/Documents/repos/sites/dangerouscentaur/demos/3028fiftyfirststreet.92105.dangerouscentaur.com/`
  • Data Layer: JSON files (`data/utilities.json`, `data/receipts.json`, `data/bills.json`) for tenant-visible data
  • Reconciliation: Google Sheet embedded on the portal with monthly tabs for collaborative expense tracking
  • Storage: S3 bucket for static assets, CloudFront for caching, Route53 DNS pointing to the distribution
  • Receipt Archival: Organized directory structure (`receipts/202605/`, `receipts/archive/`) with both web-accessible and backup copies

Data Model Design

utilities.json tracks monthly charges and credits:

{
  "rent": 3200.00,
  "rent_credit": 0.00,
  "utilities": {
    "solar": 146.35,
    "electric": 40.80,
    "gas": 87.72,
    "water": 174.87,
    "cox_internet": 111.07
  },
  "pending_bills": {
    "electric": true,
    "gas": true,
    "water": true
  },
  "total_monthly": 3760.81
}

The pending_bills object flags utilities awaiting receipt data, preventing premature billing. The rent_credit field allows retroactive adjustments when actual bills arrive lower than estimates.

receipts.json manages the approval workflow:

{
  "pending": [],
  "approved": [
    {
      "id": "receipt_202605_electric",
      "utility": "electric",
      "month": "202605",
      "amount": 45.32,
      "image_url": "receipts/202605/electric.jpg",
      "uploaded_date": "2026-05-15",
      "approved_date": "2026-05-15"
    }
  ],
  "rejected": [],
  "payments": []
}

bills.json maintains historical records back to March 2026, providing audit trail and trend analysis for both parties.

Receipt Management and Archival Strategy

Receipts follow a three-tier storage model:

  • Current Month: `/receipts/202605/` — active receipt images, web-accessible via CloudFront
  • Approved Archive: `/receipts/archive/202605/` — long-term storage, organized by month
  • Local Backup: `/Users/cb/Documents/repos/3028-51st-property/receipts/` — original images for local revision control

This structure ensures:

  • Tenant can access current-month receipts immediately after approval
  • Historical receipts remain organized and discoverable (YYYY-MM format)
  • Property manager retains local git history for disputes or audits
  • CloudFront caching only applies to approved receipts, preventing accidental exposure

Google Sheets Integration

Rather than hardcoding monthly calculations, we embedded a collaborative Google Sheet directly into the portal. The sheet:

  • Has a separate tab for each billing month (May 2026, June 2026, etc.)
  • Contains line items for each utility, rent, and credits
  • Allows the property manager to adjust charges in real-time as receipts arrive
  • Provides tenant read-only access for transparency
  • Can be extended to include payment reconciliation columns

We created the sheet using a Python service account with Google Sheets API credentials, then transferred ownership to the property's dedicated email (3028fiftyfirststreet92105@gmail.com) to ensure continuity. The embed URL was generated and inserted into the portal's index.html:

<iframe src="https://docs.google.com/spreadsheets/embed?key=SHEET_ID&range=A1:G50"></iframe>

Deployment Pipeline

Files are deployed to S3 using the AWS CLI, organized by content type:

  • HTML Pages: `s3://dangerouscentaur-demos/3028-51st/index.html`, `/receipts/index.html`, `/docs/electric-statement.html`, etc.
  • JSON Data: `s3://dangerouscentaur-demos/3028-51st/data/utilities.json`, `/data/receipts.json`
  • Receipt Images: `s3://dangerouscentaur-demos/3028-51st/receipts/202605/*.jpg`

CloudFront invalidation is triggered after each deployment to bust cache on updated data files:

aws s3 cp /local/path/data/utilities.json s3://dangerouscentaur-demos/3028-51st/data/
aws cloudfront create-invalidation --distribution-id DIST_ID --paths "/data/*"

Reconciliation Workflow

The system handles three scenarios:

  • Expected Bills Arrive On Time: Receipt uploaded → approved in Google Sheet → utilities.json updated → JSON deployed → tenant portal refreshes
  • Bills Arrive Lower Than Estimated: rent_credit field in utilities.json set to difference → applied to next month's rent display
  • Bills Still Pending: pending_bills.electric: true` flag prevents final billing until receipt is received

Python scripts automate the final steps:

  • create-tenant-sheet.py — generates monthly Google Sheet tabs with formula templates
  • update-sheet-data.py — syncs approved receipts from JSON to the sheet
  • transfer-sheet-ownership.py