Category: SaaS Architecture / PostgreSQL / Cloud Storage
Read Time: ~5 mins
The Problem: The Hidden Cost of Chatty SaaS Data
When building multi-tenant SaaS applications, modern developer platforms like Supabase are a dream. You get managed PostgreSQL, Row Level Security, instant REST APIs, and auth out of the box. For small-to-medium businesses—like gym owners and fitness club managers using my Universal Gym Membership SaaS Platform—the 500 MB database quota on Supabase's free tier provides more than enough room for member profiles, subscription tiers, and plan billing rules.
Until you add communication and payment telemetry.
To turn the app into an all-in-one operations engine, I integrated two high-volume features:
1. Automated WhatsApp Campaign Engine: Using the Meta WhatsApp Cloud API for automated renewal reminders, payment receipts, and direct customer support messaging.
2. Dynamic UPI & Cashfree Transactions: Real-time billing webhooks generating itemized receipts for every recurring payment.
Here’s the catch: a busy club with a few hundred members generates thousands of chat interactions and payment records every single month. At ~1.4 KB per WhatsApp message and ~1.0 KB per transaction, plus the default 18.5 MB that PostgreSQL allocates for internal system catalogs and write-ahead logs (WAL), disk usage compounds fast.
Within 8 to 12 months, a single active tenant can easily threaten the 500 MB free quota.
The Dilemma: Why Conventional Fixes Fall Flat
When a tenant's database begins filling up, the standard engineering playbook suggests two options:
- Option A: Upgrade to Paid Tiers ($25/mo per tenant): Passing this cost to small local gym owners ($300/year just for database disk space) destroys the affordability and unit economics of a micro-SaaS.
- Option B: Dump Historical Logs to AWS S3 / Glacier: While technically sound, it completely isolates the business owner. A gym manager who needs to look up a payment dispute from 9 months ago cannot easily query Parquet files in an S3 bucket without opening a developer support ticket.
I needed a solution that was 100% free, perpetually scalable, and directly readable by non-technical business owners.
The Architecture: Google Sheets as a "Human-Readable Cold Data Lake"
In my SaaS architecture, Google Sheets was already serving as a client-side member roster connector. Gym managers love spreadsheets because they own the data, know how to filter rows, and can open it on their phones anytime.
So I asked myself: Why not treat Google Sheets as an infinite, structured cold-storage tier?
Recent operational data (the "hot tier") lives in PostgreSQL for sub-millisecond API response times and live dashboard filtering. But historical records (the "cold tier") can be archived to dedicated tabs in Google Sheets: `Chats_Archive` and `Transactions_Archive`.
The real engineering challenge wasn't just exporting data—it was building an automated, safe 50% Chronological Midpoint Engine that gym owners could trigger with complete confidence.
Engineering the 50% Chronological Midpoint Engine
Step 1: Real-Time Capacity Metering
Before asking owners to archive data, they need complete visibility into their database health. In the company settings panel, I built a real-time storage widget that calculates:
`Total Storage = Base System Catalog (18.5 MB) + (WhatsApp Messages × 1.4 KB) + (Transactions × 1.0 KB) + Customer Directory`
This renders as a live progress gauge with dynamic health states:
- Emerald (<70%): Healthy operational state.
- Amber (70%–85%): Archival recommended.
- Rose (>85%): Critical threshold approaching quota limits.
Step 2: Finding the Chronological Midpoint
Most simple cleanup scripts just take an arbitrary number like "delete the oldest 1,000 rows". But that approach is dangerous: it might delete yesterday's conversation if a tenant had a huge marketing blast, or delete nothing useful if volume was low.
Instead, my engine computes the chronological midpoint:
`Midpoint Timestamp = Oldest Record Timestamp + (Total History Span / 2)`
If a gym has 12 months of accumulated chats, the engine targets exactly the older 6 months. If they have 4 months of history, it archives the oldest 2 months.
To guard against edge cases—such as massive message bursts concentrated in a single weekend where timestamps cluster tightly—I implemented an offset fallback:
```typescript
const offsetMidpoint = Math.floor(totalCount / 2);
```
This guarantees that exactly 50% of the historical volume is accurately identified regardless of timestamp distribution.
Step 3: Two-Phase Batch Archival & Safe Purging
Pushing thousands of records between Supabase, Next.js edge functions, and Google Apps Script requires strict safety guardrails:
1. Batch Ingestion: The older 50% of records are sent to Google Apps Script via `archive_chats_batch` or `archive_transactions_batch`.
2. Verified Receipt: The edge handler waits for an explicit HTTP 200 acknowledgment with verified row counts from the Google Sheets API.
3. Chunked Deletion (Guarding Against URI Overflow): Once receipt is verified, the engine deletes the archived records from Supabase in batches of 100 IDs. Attempting to pass hundreds of UUIDs in a single REST `DELETE /messages?id=in.(...)` query string triggers HTTP 414 (`URI Too Long`) errors. Batching into chunks of 100 ensures bulletproof deletion across all network proxies.
The result? The database instantly reclaims 30% to 45% of its disk footprint, dropping back down into the safe emerald zone—with zero lost data.
Two-Way Restoration: Cold Data That Isn't Frozen Forever
Archiving is only half the battle. What happens when an inactive member returns to the gym 8 months later, or a disputed transaction needs to be audited?
I built a dedicated restoration bridge (`/api/data/restore-member`):
- With one click, an archived member row in Google Sheets is pulled back into active Supabase storage.
- Concurrently, the engine dispatches an in-place status update to the `Archived_Members` sheet tab, tagging the entry as `"Restored to App"`.
- The tenant retains full relational continuity without manual re-entry.
Key Takeaways for SaaS Engineers
1. Tiered Storage Doesn't Need Enterprise Complexity: You don't always need multi-region S3 lifecycle policies to build a tiered storage architecture. Combining low-latency relational SQL for hot data with client-accessible spreadsheets for cold data solves database limits at zero infrastructure cost.
2. Chunk Your REST Purges: Never assume your batch delete queries will fit into a standard URL query string. Always chunk batch operations into safe batches of 100 to prevent proxy truncation and HTTP 414 failures.
3. Transparency Builds Trust: Giving non-technical users a visual storage meter and a 1-click "Reclaim 50% Space" button transforms back-end database maintenance into an empowering feature that clients genuinely appreciate.
Interested in multi-tenant SaaS architectures, edge computing, or full-stack Next.js builds? Feel free to [reach out](https://adilrahman.cc) to talk shop or discuss a custom project.