The Per-Tenant SQLite Graveyard: Why Your SaaS Data Tier Is About to Get a Lot Less Romantic

The Romantic Era: Why Per-Tenant SQLite Was Never Stupid

Let’s be fair to the old architecture. Per-tenant SQLite isn’t a dumb idea, it’s a different idea with different failure modes. When it works, it’s beautiful. One database file per customer. Perfect isolation. No way for Tenant A’s query to accidentally read Tenant B’s emails. The A-R-S-Y project’s dedicated SQLite tenant database factory captures the engineering rigor this approach demands: server-generated immutable tenant IDs, deliberate lock configurations, restrictive file permissions, and absolutely no shared handles across tenants.

The isolation story is genuinely compelling. A bug in your SQL can only nuke one customer’s data. A compromised query can only leak one tenant. It’s the database equivalent of separate bank vaults instead of one big room with a velvet rope.

But the operational reality starts to curdle around the edges. Every tenant database needs:

• Its own backup schedule and restore verification
• Its own migration state tracking
• Its own connection management and lifecycle
• Its own answer to “what happens when this file gets corrupted?”

When you have three tenants, this is manageable. When you’re scaling toward 10,000, you’re no longer running a SaaS, you’re running a database hosting company as a side project.

The Breaking Point: What Actually Forces the Migration

The shift usually isn’t triggered by a philosophical revelation about data architecture. It’s triggered by something concrete, like trying to answer a simple business question.

Consider the finance-budget-tracker migration to Supabase. The motivation is stark: “Today each person has their own packages/backend/data/ledger.db, which a public server can’t rely on.” That’s the whole story in one sentence. You can’t build shared features, cross-tenant reporting, or a proper API layer on top of per-instance files scattered across a filesystem.

The migration pain is equally concrete. The issue enumerates the hidden costs most people don’t anticipate:

• node:sqlite is synchronous, but Postgres clients are asynchronous, every repository, service, and route touching the database becomes async.
• SQLite syntax (AUTOINCREMENT, datetime('now'), COLLATE NOCASE) doesn’t port directly.
• Tests using :memory: SQLite need an entirely new test strategy.
• Dates stored as text need a deliberate decision about whether to adopt Postgres DATE types.

These aren’t architecture debates. They’re hours of tedious work that nobody budgets for.

RLS Is the Easy Part. Everything Else Is Hard.

Here’s the uncomfortable truth: enabling row-level security is the least interesting part of this migration. The shared Postgres design discussion is a masterclass in what actually matters, and it’s almost all infrastructure paranoia.

The plan described there is refreshingly rigorous:

• Tenant ID is the first column of every primary key
• RLS is forced with restrictive policies on every table
• The app roles can’t bypass RLS
• One module sets the tenant context per transaction with set_config(..., true)
• All SQL uses constants (no dynamic query construction)
• A CI check walks the catalog and fails on any table without a policy
• A trigger rejects writes for a different tenant

This is what “defense in depth” looks like when someone has actually thought about the failure modes. And it’s still not enough.

The Leaks You Haven’t Thought About Yet

The comment thread on that plan is worth reading twice, because it identifies the subtle ways tenant isolation breaks even with RLS properly configured.

Views and functions are the classic leak vector. A plain view runs with the owner’s rights and skips RLS entirely unless you explicitly create it with security_invoker = true. Any SECURITY DEFINER function does the same. Your CI catalog check needs to cover views, functions, and materialized views, which have no RLS at all, period.

Indexes and constraints can silently defeat your tenant isolation. Putting tenant_id first in the primary key isn’t enough. Every unique constraint and every index needs the tenant column first, or the planner will happily do a cross-tenant scan and let RLS filter afterward. That’s not just a performance problem, it’s a correctness problem waiting for an optimizer update to bite you.

The @@ operator in Postgres 17 isn’t leakproof. For search queries with full-text search under RLS, the GIN index only helps with the tenant column, the text match runs as a filter on those rows. Fine at small scale, but you need to benchmark it on your biggest tenant before you commit to the pattern.

Delete Semantics: The Compliance Nightmare Nobody Plans For

Here’s where the spiciness quotient kicks in. When your data is sensitive, say, emails belonging to people who aren’t even your users, delete has to be real. Not soft-delete. Not tombstone. Actual, forensic, “this data no longer exists anywhere” deletion.

Per-tenant SQLite made this straightforward: delete the file, maybe a few backups, done. Shared Postgres makes it a distributed systems problem.

One approach from the thread: events go into an append-only table with JSONB, free text goes into a separate content table, and erasing removes the text while keeping the event metadata. Derived tables get rebuilt per tenant from the events. This is elegant, the event log is the source of truth, and tenant data can be reconstructed from it.

But erasures need to be logged outside the database and replayed after any restore. Your backup retention covers 30 days plus PITR, which means deleted data technically exists in your backups for 30 days. That’s a compliance answer you need to have ready before a lawyer asks.

And what about logical replicas? A subscriber with the whole table gets the DELETE like any other change, but filtered publications, paused subscribers, lagging consumers, and warehouse snapshots will not get the erasure. You need a documented list of every place tenant data lands, replicas, warehouses, exports, log storage, backups, and an erasure is only done when each one is covered.

The “Just Add a WHERE Clause” Anti-Pattern

There’s a school of thought, articulated by at least one commenter with the confidence of someone who’s never been woken up at 3 AM by a breach, that this entire RLS setup is overthinking. “Just add a customer UUID column and filter.”

The rebuttal is brutally practical: that approach works until one developer forgets a WHERE clause in a report query, and suddenly you’re explaining a cross-tenant data leak to regulators. The defense-in-depth described in this plan, RLS plus CI checks plus cross-tenant write triggers, isn’t paranoia. It’s the actual difference between “secure by default” and “secure until someone makes a mistake”.

The real-world failure modes are documented. Look at tl;dv’s Firestore incident, a single collection storing meeting records for 2 million users with zero tenant isolation. That’s what happens when “we’ll just filter by tenant ID in the application” meets the reality of evolving codebases and human fallibility. The catastrophic tenant isolation failure isn’t a cautionary tale, it’s a prophecy that came true.

Partitioning: The Debate You Should Have on Day One

Should you partition the events table by tenant from day one, or is a composite index fine at 10,000 tenants?

The honest answer: it depends on your tenant size distribution. If you have a handful of massive tenants and thousands of tiny ones, partition by tenant gives you predictable query performance and easier per-tenant operations like backup and restore. If all your tenants are roughly the same size, a composite (tenant_id, created_at) index will serve you fine until it doesn’t, and you’ll know when that happens because your EXPLAIN plans will start showing sequential scans on the big tenants.

Start with the composite index, benchmark it on your largest tenant, and have a partition migration script ready. You’ll know within a month whether you need it.

The Backup Problem Nobody Bills For

Daily backups for 30 days plus PITR sounds reasonable until you need to restore a single tenant. With managed Postgres, the admin isn’t a superuser, so restoring one tenant means PITR to a side system and reinserting that tenant’s rows. That’s not a backup operation, that’s a mini-ETL pipeline you need to build, test, and document.

The cutover strategy from the thread is smart: one tenant at a time, minutes of downtime, no dual-write, row-by-row comparison, old copy kept for 30 days. It’s the kind of boring, methodical approach that doesn’t get you promoted but does prevent you from losing a customer’s data during the migration.

The Authorization Layer You’re About to Need

Here’s the uncomfortable implication hiding under all of this: RLS handles tenant-level isolation, but not user-level authorization within a tenant. Your RBAC implementation is about to become the weak link.

The SaaS authorization trap is real: flat role-based access control starts simple and ends as a combinatorial nightmare when users span multiple workspaces and resource hierarchies. RLS doesn’t solve this, it just ensures that when your RBAC logic has a bug, the damage is contained to one tenant instead of leaking across all of them.

The Verdict: It’s Worth It, But Not for the Reasons You Think

The migration from per-tenant SQLite to shared Postgres with RLS isn’t a scalability play, not really. At 10,000 tenants, a well-run per-tenant SQLite system could probably handle the load. The real reasons are operational:

• One backup system instead of thousands
• One migration pipeline instead of a fleet
• Cross-tenant analytics without a crawl job
• A single, testable security model instead of a filesystem convention

The core trade-offs in PostgreSQL-based multi-tenant design are about choosing your failure modes deliberately. Per-tenant SQLite fails loudly on operations but quietly on security, nothing leaks cross-tenant by design, but you can’t reason about your data as a whole. Shared Postgres fails loudly on security but quietly on operations, one misconfigured view can leak everything, but you can actually run the business on your data.

There’s no right answer. There’s only the answer that matches your team’s ability to enforce invariants. If you can commit to the paranoia, RLS everywhere, CI checks on the catalog, cross-tenant write triggers, erasure logging, documented data placement, shared Postgres is the better long-term home for a growing SaaS.

If you can’t commit to that, per-tenant SQLite’s physical isolation is doing more security work than your process ever will.

Just don’t fall into the trap of thinking “one shared database” means “one shared security model.” It doesn’t. It means you’ve moved your security boundary from the filesystem to the query planner, and the query planner doesn’t care about your compliance obligations.