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 Distributed Receipt Management

Over the past development session, we built out a complete tenant management portal for a rental property at 3028 51st Street that handles utility billing, receipt management, and rent reconciliation. This article covers the architecture decisions, implementation details, and deployment strategy for a system that needed to balance tenant transparency, landlord accounting, and distributed data storage.

The Problem Statement

Managing a multi-tenant property involves coordinating variable utility costs (electric, gas, water, solar), fixed services (Cox internet), receipt documentation, and rent adjustments. The previous system had no centralized way to:

  • Track which utilities had been paid and by whom
  • Store receipts in a way accessible to both tenants and landlord
  • Calculate monthly rent adjustments based on utility credits
  • Reconcile payments across multiple utility providers
  • Provide a shared view of financial data that stayed in sync

System Architecture Overview

We implemented a three-tier architecture:

  • Frontend: Static HTML portal at /Users/cb/Documents/repos/sites/dangerouscentaur/demos/3028fiftyfirststreet.92105.dangerouscentaur.com/index.html
  • Data Layer: JSON files for receipts and utilities, Google Sheets for collaborative editing and month-to-month history
  • Distribution: S3 + CloudFront for static assets, embedded Google Sheets API for live data

This approach prioritizes simplicity and auditability — all data transformations are explicit, and the source of truth for receipts and utility costs lives in version-controlled JSON files that can be reviewed and audited.

Data Schema Design

We created two primary JSON data files:

data/utilities.json — Monthly utility costs and rent adjustments:

{
  "month": "202605",
  "rent": 3200,
  "rent_credit": 0,
  "utilities": {
    "solar": { "cost": 146.35, "received": false },
    "electric": { "cost": 40.80, "received": false },
    "gas": { "cost": 87.72, "received": false },
    "water": { "cost": 174.87, "received": false },
    "cox": { "cost": 111.07, "received": true }
  }
}

The received flag tracks whether we've obtained the actual bill/receipt for that utility this month. This prevents showing incorrect costs to the tenant before we have documentation. The rent_credit field allows us to offset the tenant's rent based on utility overpayments or credits from previous months.

data/receipts.json — Receipt submission and approval workflow:

{
  "pending": [],
  "approved": [
    {
      "id": "receipt_20260515_electric",
      "utility": "electric",
      "month": "202605",
      "amount": 40.80,
      "date_received": "2026-05-15",
      "image_url": "receipts/202605/electric-bill-may.jpg",
      "approved_date": "2026-05-15"
    }
  ],
  "rejected": []
}

Separating pending/approved/rejected provides a workflow for the landlord to validate receipts before they affect rent calculations.

Google Sheets Integration

Rather than hardcoding utility data, we created a Google Sheet named 3028-51st-Utilities-Tracker with one tab per month. This sheet is embedded in the portal using the Google Sheets API embed URL:

<iframe src="https://docs.google.com/spreadsheets/d/SHEET_ID/embed?gid=MONTH_TAB_ID" ...></iframe>

The sheet contains:

  • Utility name, monthly cost, status (received/pending), and notes
  • Formulas to calculate total utilities and adjusted rent
  • Year-to-date totals and averages
  • Comments for communication between landlord and tenant

The sheet is owned by the property's dedicated Google account (3028fiftyfirststreet92105@gmail.com) but shared with both the tenant and landlord for read/write access. This provides a shared source of truth and audit trail through Google's revision history.

Receipt Storage Architecture

Receipts needed to be accessible to both tenant and landlord, but also archived so old receipts don't clutter the current view. We implemented this with S3 directory structure:

s3://dangerouscentaur-3028-51st/
├── receipts/
│   ├── current/          # Month being tracked
│   │   ├── 202605/
│   │   │   ├── electric-bill-may.jpg
│   │   │   ├── gas-bill-may.jpg
│   │   │   └── water-bill-may.jpg
│   ├── archive/
│   │   ├── 202604/       # Previous months, searchable but not featured
│   │   ├── 202603/
│   │   └── ...

This separation keeps the current month's receipts prominent while maintaining full history. The portal's receipts viewer at receipts/index.html loads the current month's images via JavaScript that reads from data/receipts.json.

Deployment and Infrastructure

All static assets (HTML, JSON, receipts, statement PDFs) are deployed to S3 at s3://dangerouscentaur-3028-51st/ with CloudFront distribution in front for caching and HTTPS. The deployment workflow:

  • Update source files locally at ~/Documents/repos/sites/dangerouscentaur/demos/3028fiftyfirststreet.92105.dangerouscentaur.com/
  • Run deployment script to sync to S3: aws s3 sync . s3://dangerouscentaur-3028-51st/ --exclude '.git/*' --exclude 'scripts/*'
  • Invalidate CloudFront cache: aws cloudfront create-invalidation --distribution-id DIST_ID --paths '/*'
  • Portal updates live within 60 seconds (CloudFront TTL)

HTML files for utility statements (docs/electric-statement.html, docs/gas-statement.html, docs/water-statement.html) are generated from templates and deployed alongside the portal.

Python Automation Scripts

To reduce manual work, we created three Python scripts in scripts/:

create-tenant-sheet.py — Generates a new Google Sheet with monthly tabs via the Sheets API

transfer-sheet-ownership.py — Transfers an existing sheet to the property account, using service account credentials with the drive.file scope

update-sheet-data.py — Reads from local utilities.json, updates