Aziz Khan
← Writing

Two rules you can't fix later in a multi-tenant SaaS

ArchitecturePostgreSQLSaaS

Traveo is a multi-tenant platform for travel agencies: leads, trips, invoices, vendors, payroll. Before writing a single screen, I wrote down two rules. Almost everything else in the codebase can be refactored later. These two can't, because by the time you notice they're broken, the bad data is already in production.

Rule 1: tenancy is enforced in Postgres, not the app

The usual approach is to add WHERE tenant_id = ? to every query. It works until one query forgets it, and then one agency sees another agency's customers. ORMs don't save you here. Drizzle, which I use, doesn't scope queries automatically, and I wouldn't trust one that claimed to.

So the database does it:

  • Every tenant-scoped table has a tenant_id column and row-level security turned on.
  • The app reaches those tables only through a withTenant() helper that sets the tenant for the session.
  • Adding a tenant-scoped table means adding its policy to one rls.sql file. That's the checklist item reviewers look for.

A forgotten WHERE now returns nothing instead of leaking data. Failing closed is the whole point.

Rule 2: money never touches a float

0.1 + 0.2 is not 0.3 in floating point, and an accounting system that's off by a paisa is wrong. Travel bookings also cross currencies, so:

  • numeric(19,4) in Postgres.
  • Decimal strings on the wire, never JSON numbers.
  • decimal.js in code.
  • Every amount carries its currency. Every FX rate carries a timestamp.

A bonus rule: the ledger is append-only

Journal entries are immutable, and that's enforced by Postgres rules, not by convention. Corrections are reversing entries. No module stores a balance; balances are derived by summing journal lines. When numbers look wrong, you can always replay how they got there.

Why decide this on day one

Each of these is cheap at the start and close to impossible later. Moving to row-level security once queries are spread across a codebase means auditing every one of them. Moving money from floats to decimals means migrating historical data you can no longer trust. Write them down before the first table, and make the database enforce them, because the database doesn't get tired at 2am.