One database per customer

The usual way to build multi-tenant software is a company_id column on every table and a WHERE company_id = ? on every query. It works right up until the day someone forgets the WHERE clause and one customer sees another customer's assets. For an inventory product that companies trust with their whole stock list, that is not a bug I wanted to be one missed filter away from.

So on the asset platform, every tenant gets their own SQLite database file. Not a schema, not a row prefix. A separate file on disk.

What that actually looks like

The system database holds one small table: the list of tenants and where their data lives. Everything else, the assets, the audits, the readers, the users, lives in a per-tenant file that nothing else can open.

function tenantDb(slug: string) {
  const row = system.prepare(
    "select db_path from tenants where slug = ? and status = 'active'"
  ).get(slug);
  if (!row) throw new Error("unknown tenant");
  return new Database(row.db_path); // its own file, its own connection
}

A request comes in, the middleware resolves the tenant from the login (or the subdomain), and from that point every query runs against a connection that physically cannot reach another company's rows. There is no filter to forget.

The parts I didn't expect to like

Backups became trivial. "Export this customer's data" is a file copy. When a sales demo needs a clean slate, I delete one file and reseed it, and no other tenant notices. Onboarding a company is a row in the system table plus a fresh file, which is why adding a customer is a config change and not a deploy.

It also made the branded-app story honest. Each customer's handheld app and each customer's report PDFs read from a database that is theirs alone, so white-labelling is skin over a real boundary, not a cosmetic filter.

The trade-offs, because there always are some

You give up cross-tenant queries. "How many assets across all customers" now means opening every file and summing, which is fine at my scale (tens of tenants) and would not be at thousands. Migrations run once per file instead of once, so the migrator loops over tenants and applies each in order. And SQLite means one writer at a time per tenant, which for an internal asset tool is a non-issue and for a high-write SaaS would push me to Postgres schemas instead.

For this product the isolation was worth more than the convenience. A breach in one tenant leaks one file. That is the sentence I wanted to be able to say out loud.