Multi Tenancy in Rails: The Decisions That Are Expensive to Reverse

The column that was added on a Tuesday

There is a category of decision in software that does not feel like a decision at the time. You are three weeks into building a SaaS product, you need customers to sign up, and somewhere in the first migration you add a column called company_id to a table. Nobody discusses it. It is obvious.

And then ten years later there are forty people in a room trying to work out how to move one enterprise customer onto their own database, and the answer is that you cannot, not without rewriting half the product, because of a column that was added on a Tuesday afternoon in week three.

I have been in that room. I have also been the person who added the column. This article is about the handful of choices in a multi tenant Rails application that are cheap to make and brutally expensive to unmake, and how to make them on purpose.

I have written it for founders as much as for developers. If you run a SaaS or you are about to build one, you do not need to understand Postgres schemas to understand what each choice costs you in year four. That is the level I am aiming for. Where I get technical, I will say why it matters to the business first.

What a tenant actually is

Let me start with the word, because half the confusion I see comes from people using it loosely.

A tenant is the unit of data isolation. It is the boundary inside which data may be seen, and outside which it must not. When a customer logs into your product, they see their tenant's data and nobody else's. That is the whole idea.

What a tenant is not is the same thing as a customer, a user, a company or a billing account, and the moment you conflate them you have made a decision you will regret.

Consider a few real cases. An agency signs up to your product and manages twelve clients through it. Is the agency the tenant, or is each client? If the agency is the tenant, the clients' data sits in one pool and the agency's staff see everything. If each client is a tenant, the agency's staff need to belong to twelve tenants and switch between them. Both are legitimate. They are different products.

A company has a UK entity and a German entity that must not see each other's data for legal reasons, but one finance director pays for both. Two tenants, one billing account.

A freelancer signs up alone, then hires two people, then gets acquired. One tenant that starts with one user and ends up owned by a different company.

If you have not decided what your tenant is, your code has decided for you, and it has almost certainly decided that tenant equals company equals billing account equals the thing with the subscription. That works right up until the first customer whose organisation is shaped differently, and there is always one.

So the first expensive decision is a definition. Write down what a tenant is in your product. Write down what it is not. Then make sure the data model reflects that and nothing else.

The three ways to keep tenants apart

There are three isolation models and everyone who builds SaaS ends up on one of them. I will describe each honestly, including what goes wrong.

Shared schema with a tenant column. One database, one set of tables, and every row that belongs to a tenant carries a column saying which one. Every query filters on it. This is what ninety percent of SaaS products do and it is what I would choose for almost every product I am asked about.

The good: it is cheap. One database to back up, one to monitor, one migration to run when you change a table. Adding a tenant is inserting a row. Ten thousand tenants cost about the same to operate as ten. Cross tenant reporting, the kind you need for your own analytics and for anything that benchmarks customers against each other, is a query.

The bad: the isolation is a promise your code makes, not a wall the database enforces. Forget the filter once and tenant A sees tenant B. Noisy neighbours are real: one customer running an enormous export slows everyone. Per tenant backup and restore is not a thing the database gives you, you have to build it. And when the enterprise customer asks for their data to live in Frankfurt while everyone else is in London, the honest answer is that it does not.

Schema per tenant. One database, but each tenant gets their own Postgres schema, a namespace containing a full copy of every table. The application switches the search path to the tenant's schema on each request and all the queries look like single tenant code.

The good: the isolation is structural. A query in the wrong schema returns nothing rather than someone else's data. Per tenant restore is possible because you can dump a schema. Developers who have never built multi tenant software find it intuitive.

The bad: migrations. Every schema change has to run once per tenant. At ten tenants that is a loop. At a thousand it is a deployment that takes an hour and fails halfway through, leaving half your customers on the new schema and half on the old. Postgres itself starts to strain with tens of thousands of tables, the system catalogues bloat, connection pooling gets complicated, and tooling that assumes one schema, which is most tooling, breaks in interesting ways. I have watched a team spend a full quarter unwinding this at around eight hundred tenants. The Rails ecosystem had a popular gem for this model and its own maintainers eventually wrote a long post about why they no longer recommend it.

Database per tenant. Each tenant gets an entire database, potentially on its own server.

The good: complete isolation, the kind you can point to in a security questionnaire. Data residency is trivial: the German customer's database is in Germany. Per tenant restore, per tenant scaling, per tenant everything. A customer can leave and take their database with them.

The bad: cost and operations. Every database needs connections, monitoring, backups, upgrades. Your infrastructure bill scales with your customer count rather than your usage. Migrations run once per database and now they can fail per database. Cross tenant anything is a data warehouse project. Signing up a customer is provisioning infrastructure, which means either a human or a lot of automation. This model is right for a product with fifty customers paying fifty thousand a year each. It is a slow death for a product with five thousand customers paying fifty a month.

How each model fails at ten, a hundred and ten thousand

It helps to think about this in terms of where each model breaks, because the breaking point is what determines the cost of reversing.

At ten tenants everything works. Shared schema is fine. Schema per tenant is fine and feels safer. Database per tenant is fine and feels enterprise. This is the point at which the decision gets made, and it is the point at which every option looks equally good, which is exactly the problem.

At a hundred tenants the schema per tenant model starts to show its migration cost, but it is still manageable. Database per tenant is now a meaningful infrastructure bill and a real operations burden, and if your tenants pay you thirty euros a month you are losing money on each one. Shared schema has probably had its first tenant leak scare, a query someone forgot to scope, caught in code review or, worse, by a customer.

At ten thousand tenants only shared schema is still standing without a rewrite. Schema per tenant has become a full time job for one person and a source of deployment fear. Database per tenant at this scale is a hosting company, not a SaaS.

Now the reversal costs. Moving from shared schema to database per tenant for one customer is possible if you were disciplined, and I will come back to how. Moving from schema per tenant to shared schema is a migration project that touches every table, every query and every deployment script. Moving from database per tenant to anything else means merging thousands of databases and renumbering every primary key. I have quoted that project. Nobody has ever accepted the quote.

This is why I default to shared schema. Not because it is the safest on day one, it is not, but because it is the only one whose failure modes are fixable in place rather than by starting again.

Where the tenant lives in the code

If you go with shared schema, and you should, the next expensive decision is where the current tenant lives and how queries find it.

The tempting answer is a default scope: tell every model to automatically add the tenant filter to every query. It feels safe. It is the opposite. Default scopes in Rails are famously hard to reason about, they leak into places you did not intend, they make it impossible to write the admin tooling that legitimately needs to see across tenants, and they give you a false sense of security that stops you writing the tests that would actually catch a leak.

What I do instead, and what I would insist on in any build I am responsible for, is three things.

First, the current tenant lives in exactly one place. Rails has a mechanism called current attributes for precisely this: a per request container that is set once, at the edge, when the request comes in and the user is identified, and reset when the request ends. Everything else asks that one place. There is no second way to find out which tenant you are in.

Second, every query that touches tenant data is scoped explicitly, through the tenant. You do not ask for all invoices and filter. You ask the current tenant for its invoices. In Rails terms that is associations, and it means the tenant id is in the query because it structurally cannot not be. Code that does not go through the tenant looks wrong on the page, which is the point: reviewers spot it.

Third, the database enforces it too. Postgres has a feature called row level security which lets you attach a policy to a table saying that rows are only visible when a session variable matches the tenant column. The application sets the variable at the start of each request. If someone writes an unscoped query, the database returns nothing rather than everything. This is the belt and braces layer. It costs a day to set up and it has caught mistakes in production that every other layer missed. I would not run a shared schema SaaS without it any more.

There are gems that package parts of this up. The well known one is acts_as_tenant and it is fine. My view is that the mechanism is small enough to own, and owning it means you understand it, which matters more here than almost anywhere else in the codebase. But if your team prefers the gem, use the gem. The decision that is expensive to reverse is not gem versus hand rolled. It is default scope versus explicit scoping.

The places tenants leak that nobody thinks about

Here is the thing about the request cycle: it is the easy part. The tenant comes in with the user, you set it, you scope the queries, done. The leaks happen everywhere that is not a request.

Background jobs. A job is enqueued during a request, with the tenant set. It runs ten minutes later in a worker process where nothing is set. If your job code assumes the tenant is there, it is now running with no tenant, which with proper row level security means it does nothing, and without it means it does everything. Every job needs the tenant serialised into its arguments and restored when it runs. This is the single most common tenant leak I have found in code audits. I wrote about the wider discipline in my piece on background workers, and this is the multi tenant addendum to it.

Caches. You cache a rendered fragment, a computed total, an API response. The cache key is the record id. Record ids are global across tenants in a shared schema, but a fragment cached for tenant A's dashboard is now served to tenant B if the key does not include the tenant. Every cache key needs the tenant in it. Every one.

File storage. Uploads go to object storage with a path. If the path is just the file's id, a signed URL for one file is a signed URL for any file you can guess the id of. Prefix every stored object with the tenant. It also makes per tenant export and erasure a prefix operation rather than a database join.

Search indexes. If you use Elasticsearch, Meilisearch or Postgres full text, the index needs the tenant as a filter that is applied on every search, not as a field the caller is trusted to add. The number of SaaS products where the search endpoint is the one that leaks is not small.

Logs and error reports. Less about leaking to customers, more about your own team. A log line with a customer's data in it and no tenant id is a compliance problem waiting to happen, because you cannot find and delete it when they leave.

Primary keys. If your ids are sequential integers, they leak the size of your business and let a curious customer enumerate. If your URLs contain them, a typo lands someone on another tenant's record, and only your scoping stands between them and seeing it. Use UUIDs, or at least do not expose sequential ids in URLs. This is not strictly a tenancy decision but it is one that is expensive to change later, because every URL in every email you have ever sent contains the old form.

Users who belong to more than one tenant

The second most common expensive mistake, after the default scope, is putting the tenant id on the users table.

It seems obvious. A user works for a company, so a user belongs to a tenant. Except the agency case from earlier: their staff need to be in twelve. The consultant who advises three of your customers and wants one login. The founder who runs two businesses on your product. The customer who gets acquired and now has staff from the parent company needing access. The support engineer on your own team who needs to see into a customer's account with the customer's permission.

Every one of these exists in every SaaS I have worked on, and every product that put tenant id on the user has ended up either building a horrible workaround, usually involving multiple accounts per person, or doing the migration.

The model that survives is: users are global, tenants are global, and there is a membership table in between that says which users belong to which tenants with which role. A user logs in once, sees the tenants they belong to, picks one, and the session carries the current tenant from then on. Invitations become a row in the membership table waiting for a user to claim it. Roles live on the membership, not the user, because the same person can be an admin in one tenant and read only in another.

Single sign on comes later, and this model makes it possible. When the enterprise customer wants their staff to log in through their own identity provider, you attach the identity provider to the tenant, and the membership table already knows who is allowed in. If tenant id were on the user, SSO would mean rebuilding authentication.

This costs about a day more than the naive version on day one. It saves months later.

Configuration, feature flags and the shape of a tenant

Tenants are not identical. One has the enterprise plan, one is on a trial, one has a feature you enabled by hand because they asked nicely and they are a good reference customer.

The expensive mistake here is putting configuration in code. A conditional that checks whether the tenant is the one special customer, then another, then a case statement, and three years later there is a file nobody dares touch called tenant_overrides.

The version that ages well is boring: a settings model per tenant with typed values, and a feature flag system that resolves flags per tenant with sensible defaults. Both can be edited from an admin screen by someone who is not a developer. Both are readable in one place when a customer asks why their account behaves differently. There are good gems for the flags, Flipper being the one I reach for.

The related decision is what a plan is. A plan is not a tenant attribute. A plan is a bundle of limits and flags, and a tenant is on one. If you hard code plan names into the codebase, the day marketing renames the plans is a code change. If plans are data and tenants reference them, it is an admin edit.

The tenant is not the thing that pays

I said at the start that the tenant is not the billing account, and I want to spell out why this matters, because it is a decision that looks like an accounting detail and turns out to be structural.

A billing account is the entity that has a payment method, receives invoices and owes you money. A tenant is the entity whose data is isolated. Usually they are one to one. They are not always, and the cases where they diverge are the customers who pay you the most.

The holding company that pays for five subsidiaries' tenants on one invoice. The agency that pays for their clients' tenants and marks up. The enterprise that wants the tenant to exist before procurement has finished, with billing attached six weeks later. The reseller.

If your subscription record hangs off the tenant, all of these are workarounds. If a tenant has a billing account and a billing account has many tenants, all of these are rows. The migration from the first to the second is not huge in code, but it is huge in data, because every historic invoice has to be reattached, and your accountant has views on that.

Decide it on day one. It is one extra table.

Why the isolation model is also your GDPR strategy

I have been writing about this from the engineering side, so let me switch to the legal side, because they are the same decision seen from two angles.

Under GDPR, and the same applies under the UK version, every one of your customers is a controller and you are their processor. Each of them can ask you, on behalf of their own users, to export everything you hold about a person, or to erase it. Each of them needs a data processing agreement with you that says where their data is and who else touches it. Each of them has the right to leave and take their data.

Every one of those obligations is easy or hard depending on your isolation model.

Export. In a shared schema, exporting a tenant is a query per table filtered by tenant id plus a prefix listing in file storage. If you were disciplined about the tenant column being everywhere, it is a script. If you were not, it is archaeology.

Erasure. Same again, and this is where the log and cache leaks come back to bite. If a customer's data is in a log line without a tenant id, you cannot prove you erased it.

Residency. If the German customer's contract says Frankfurt, a shared schema in London does not satisfy it, and no amount of row level security changes that. This is the one obligation that only the database per tenant model meets natively, which is why the next section exists.

Records of processing. Your records need to say what data you hold for whom and where. A tenant table with a residency column and a clear list of sub processors is that record. A pile of special cases is not.

I went deeper into the AI specific version of this in building GDPR compliant AI features in your SaaS, and everything in that piece assumes you got the tenancy right underneath. You cannot bolt data protection onto a product that does not know whose data is whose.

The moment the enterprise asks for their own database

It will happen. A customer large enough to matter will say, in a security review or a procurement call, that their policy requires their data to be in a dedicated database, or in a specific country, or both. You will want to say yes.

Whether you can say yes cheaply depends entirely on decisions you made years earlier. Here is what has to be true.

The tenant id must never have leaked into places it does not belong. Not into URLs as a routing key. Not into hard coded joins across tenants. Not into assumptions in reporting code that all tenants are in one database. If the whole application only ever reaches tenant data by asking the current tenant, then which database the current tenant lives in is a detail that can vary.

Rails has supported multiple databases natively for some years now, including switching the connection per request. The pattern that works is: most tenants live in the shared database, a tenant record carries an optional pointer to a dedicated database, and the connection switching happens in the same place the current tenant is set. Everything below that line does not know or care.

You do not build this on day one. You build the discipline that makes it possible on day one, and you build the switch the first time a customer is willing to pay for it. I have done this exact move for one customer of a client's product, and it took about three weeks, because the discipline had been there. I have also been asked to do it on a product with default scopes and integer ids in every URL, and the honest quote was a rewrite.

Keep the door open. It costs nothing to keep it open and a fortune to reopen it.

The one test every SaaS needs

I am a test first developer and I have written at length about why in my guide to TDD and BDD in Rails, so I will keep this to the specific point.

Most test suites for multi tenant products test that things work. Very few test that things do not work across the boundary. And that is the test that matters, because a feature that fails is a bug report, while a tenant leak is a data breach with a seventy two hour reporting clock and an entry in your incident log that every future enterprise customer will ask about.

The test is simple to describe. Create two tenants. Create data in both. Log in as a user of tenant A. Try every way you can think of to reach tenant B's data: the list endpoints, the show endpoints with tenant B's ids, the search, the export, the API, the background job with a forged argument, the cached fragment. Assert that every single one returns nothing or a not found. Then do the same in the other direction.

Write it as a Cucumber feature if you want the business to be able to read it, because this is one the business should read. Run it on every commit. Add a case to it every time you add an endpoint. It is tedious. It is the highest value test in the suite.

And then there is the version of the test that runs against the database rather than the application: with row level security in place, connect as the application role with tenant A's session variable set, run a raw select with no filter, and assert you only get tenant A's rows. That test proves that even if the application is wrong, the database is right.

What I chose for GrowCentric, and why

I will use my own product as the worked example, with the caveat that I am going to talk about the reasoning rather than the internals, because the internals are not the point.

GrowCentric is an ecommerce growth platform. Several client shops connect to it, it collects their data, and one of the things it does is benchmark: this is how your conversion rate compares with shops like yours. That last feature is only possible because the platform can look across tenants, in aggregate and anonymised, under a licence the terms of service grant explicitly. It is also, from a data protection point of view, the most sensitive thing the product does, which is why the access terms spell out exactly what is aggregated and why.

That single product requirement made the isolation decision for me. A database per tenant model would have made the benchmark a warehouse project and would have made the product's core feature the hardest thing to build. Schema per tenant would have made it possible but painful. Shared schema with a tenant column, strict explicit scoping, row level security underneath, and a very clear line in the code between "this query is scoped to a tenant" and "this query is the aggregate benchmark and is only reachable from one place", was the model that matched what the product actually is.

I also knew from the first week that users would belong to several tenants, because an agency managing several shops is a customer I want. So users are global with memberships. And I knew that EU clients would ask about residency, so the tenant record carries where the data lives even while every tenant is in the same place, because the day one of them needs to be elsewhere I would rather change a value than a schema.

None of that took longer than the naive version would have. It took thinking about it for an afternoon before the first migration instead of after the first enterprise call.

The list, for the founder in a hurry

If you only read one section, read this one and give it to whoever is building your product.

1. Define what a tenant is in your product, in writing, before the first migration.
2. Shared schema with a tenant column, unless you have a specific reason not to, and "it feels safer" is not a reason.
3. The current tenant lives in one place, set at the edge of each request and each job.
4. Every query goes through the tenant. No default scopes. Ever.
5. Postgres row level security as the layer under the application.
6. Tenant in every cache key, every file path, every search filter, every job argument, every log line.
7. Users are global. Memberships join users to tenants and carry the role.
8. Tenant has a billing account. Billing account has many tenants.
9. Configuration and plans are data with an admin screen, not conditionals in code.
10. UUIDs, or at least no sequential ids in URLs.
11. A residency field on the tenant from day one, even if every value is the same.
12. The tenant A cannot see tenant B test, on every commit, growing with every endpoint.

Every one of those is a day or less at the start. Every one of them is weeks to months if you have to retrofit it with customers on the platform.

The honest closing

I will end where I started. The column added on a Tuesday afternoon is not a mistake because the column is wrong. A tenant column is exactly right. It is a mistake because nobody decided it, so nobody decided the eleven things that go with it, and the product grew around the gaps.

The good news is that all of this is cheap if you do it at the beginning, and I mean cheap: a settings model, a membership table, a session variable, a test. The expensive version is the one where you discover the gaps through a customer. If you are at the beginning, spend the afternoon. If you are past it and something in this article made you wince, it is usually more fixable than it feels, and the order to fix it in is the order of the list above.