Why every customer gets their own database

Almost every business application you have ever used keeps all of its customers in one database. Your records and your competitor's records sit in the same tables, distinguished by a column. Every query that reads anything must carry a condition limiting it to the right customer.

This works. It is efficient, it is well understood, and thousands of good products run on it. It has one failure mode, and the failure mode is total: a query written without that condition returns somebody else's data to whoever asked. Not a degraded experience, not a slow page. Their customer list, in your browser.

Why that risk never really goes away

The usual answer is discipline: a shared function that adds the condition, a code review that checks for it, a test that catches the obvious cases. All of that helps. None of it changes the shape of the problem, which is that correctness depends on every one of hundreds of queries, written over years by people who have not met, doing the same thing right every time.

Databases have row level security, which moves the condition into the database rather than the application, and is a genuine improvement. It is still a rule applied to a shared store rather than the absence of a shared store.

What we did instead

In Consonas, each organisation has its own database. Not its own row, table, column or schema. Its own SQLite database, inside its own isolated compute object, which only requests for that organisation are routed to.

A query written without a customer condition cannot return the wrong customer's data, because the only data reachable from where it runs belongs to one organisation. Getting it wrong produces a wrong answer to the right person rather than a right answer to the wrong one.

There is a second check behind that. Each object knows which organisation it belongs to and refuses any request addressed to a different one, so a routing mistake produces a refusal instead of a disclosure. That check has never fired in normal operation, which is exactly what you want from a backstop, and it is tested deliberately.

What "its own database" means in practice

The phrase is used loosely enough in this industry to be worth pinning down. There are at least four things people mean by it and they are not equivalent.

A column. One database, one set of tables, a customer identifier on every row. This is the common arrangement and the one described above.

A schema. One database, one set of tables per customer inside it. Better, because a query without a condition fails rather than returning the wrong rows. Still one store, one set of credentials, one blast radius, and a connection that can be pointed at the wrong schema.

A database on a shared server. Better again, and the point at which most products stop. The separation is real but the server is shared, so a misconfiguration at the server level still reaches everybody, and the operational burden of thousands of databases on one machine is what usually pushes teams back to the column.

A database inside its own isolated compute. What Consonas does. Each organisation has a SQLite database living inside a single instance of a compute object that exists only for that organisation. Code running for organisation A is not connected to organisation B's database with the wrong credentials. It has no connection to it at all, and no way to obtain one.

How a request finds the right one

A request arrives naming an organisation in its path, the session is resolved, and that name is looked up to find the organisation it refers to. The caller is then checked for membership of that organisation. Only after that does anything get forwarded, and the object it is forwarded to is identified by a value minted when the organisation was created, which has nothing to do with the name in the path.

It is worth being exact about which part of that does the work, because the flattering version of this paragraph is the wrong one. The organisation does come from the path, and a caller can type any name there they like. What they cannot do is be a member of it. A request edited to name somebody else's organisation resolves that organisation and then fails the membership check, and the answer it gets is the one anybody gets for a name that was never registered, so the path is not a way to find out who else is here either. The signed context that travels on to the object carries the organisation's permanent identifier rather than the name, so nothing downstream is deciding anything from a string the caller chose.

Behind that, each object records which organisation it was created for and refuses any request naming a different one. That check exists for the case where the routing itself is wrong, which is the failure the first mechanism cannot catch, and it turns a routing defect into a refusal instead of a disclosure. It has never fired in normal operation. That is exactly what you want from a backstop and it is the reason we test it deliberately instead of trusting the silence.

Proving it instead of asserting it

A claim about separation that is only ever stated is worth very little. Ours is checked by tests that run on every build, and the ones that matter are the negative ones: a request carrying a valid session for one organisation and naming another is refused; an object asked to serve an organisation it was not created for refuses; a request with no session reaches nothing.

We write those tests the same way round every time. Break the protection first, watch the test fail, then restore the protection and watch it pass. A test that has never been observed to fail is not evidence that the thing it guards is working, and the number of test suites in the world that pass regardless of the code they cover is larger than anybody would like.

What it costs

Being honest about the trade is the point of writing this down.

You cannot query across customers. Not "you should not", you cannot. Any platform number has to be built from aggregates each organisation reports rather than from a query over everything. That is more work, and it also means our own operational reporting cannot accidentally read a customer's data, which we have come to see as a feature.

Every schema change has to happen thousands of times. One migration against one shared database becomes a migration applied to every organisation separately. We do it lazily: an organisation gets its pending changes the next time somebody uses it, recorded in a journal so an interrupted migration resumes rather than restarting.

Dormant customers cost money. In a shared database an unused account is a few rows and costs essentially nothing. Here it is a database, billed while it exists. That has a real consequence for our free plan, which is the subject of another post.

You give up the reflex of fixing data with one statement. Every engineer who has run a system of this kind has at some point corrected a bad release with a single update across every affected row. That option does not exist here. A correction is a job that visits each organisation, which is slower to write, slower to run, and considerably harder to get wrong in a way that reaches someone it should not have.

Migrations, in more detail, because this is where people expect it to fall over

The objection we hear most is that applying a schema change to thousands of separate databases is unworkable. It is the right thing to worry about and it is the part that needed the most design.

Changes are applied lazily rather than in a sweep. When a request arrives for an organisation, the object checks the version recorded in its own database against the version the code expects, and applies anything outstanding before serving, which is exactly when it matters. A nightly job walks the organisations nobody has opened and brings them up as well, a few at a time, so that an account dormant for a year is not a year of migrations waiting to run in front of the person who finally signs in.

Each step is recorded in a journal inside that organisation's database as it completes. An interruption therefore resumes at the next step rather than restarting, and a step that has already run is never run twice.

The discipline this imposes is that a change has to be safe to apply to a database of unknown age. In a shared world you can write a migration that assumes yesterday's shape because there is one database and you know its shape. Here the database might not have been touched in a year. Every change must therefore be additive first, backfilled second, and only later allowed to remove anything, which is a better habit than the one it replaces and it is the habit large shared systems eventually adopt anyway after being burned.

What you get back that a shared database cannot offer

The separation is the reason we did it. Three other things arrived with it and they turned out to matter more than expected.

Recovery is per customer. The platform keeps thirty days of point in time recovery for each object, so one organisation can be put back to one moment without touching anybody else. In a shared database, restoring a customer who made a mistake on Tuesday is a surgical operation on live tables and most companies simply will not do it. One honest qualification, and it is the one that matters. The rewind is now ours as well as the platform's: an operator asks for a moment, a second operator approves it, and the organisation restarts into that moment with the record of who did it written where the rewind cannot reach. What has not happened is a rehearsal. Nobody has run it against the real platform, because the local runtime refuses the two calls it turns on, so the first time it runs will be the first time it runs. The runbook says so, this says so, and we would rather say it than let you assume otherwise.

Deletion is real. Closing an account removes a database rather than marking rows. There is no orphaned row left behind by a foreign key no one remembered, because there is nowhere for it to be left behind in.

Jurisdiction is physical. An organisation created in the European Union has a database that exists in the European Union. There is no shared table holding some customers' data in one place and some in another, and no question about whether an index or a cache spans the boundary.

How the shared column became the default, and what changed underneath it

It is worth being fair to the design we did not choose. The customer column is not laziness, and it was not a failure of care by people who should have known better. It was arithmetic, and for most of the history of business software the arithmetic was overwhelming.

A database used to be a machine. It meant a server in a rack, a licence with a price on it, an operating system someone had to patch, and a person whose job was keeping it alive at three in the morning. Giving every customer their own database therefore meant giving every customer a server. A product with a thousand customers meant a thousand servers, which no company selling software to small organisations could possibly carry. The shared table was not a compromise on safety. For a long time it was the only shape that could be built at all.

Connections made the same point a second time. A database server accepts a limited number of open connections, each one holding memory on the server, which is why every application of that era sits behind a pool. A pool works because it holds a small set of identical connections and hands them out. Point it at a database per customer and the pool stops being a pool: it becomes a directory of connections, most of them idle, all of them occupying something. Even where the storage was affordable, the connection arithmetic was not.

So the industry built compromises in the middle, and they are worth naming because a reader evaluating suppliers will meet all of them. A set of tables per customer inside one database. A customer identifier used to spread rows across a handful of servers, so that no single machine held everyone. A small number of especially nervous customers given their own installation while everyone else shared, which is where the vocabulary of silos, bridges and pools comes from. Each of those moved the boundary. None of them removed the shared store, and each added operational weight in proportion to the number of customers, which is the reason teams that adopt one so often retreat from it.

Three things changed underneath all of that, and they changed recently enough that most products predate them. Storage stopped being sold as a machine and started being sold by the byte, so an empty customer costs what an empty customer is worth rather than what a server costs. Compute became addressable in small pieces, so it is possible to have a unit of running code that exists for one customer and is reached only by that customer's requests. And SQLite, which has been quietly excellent for twenty years, turns out to be exactly the database you want when there is no server to connect to, because it is not a server. Put together, the cost of one more database fell from the cost of a machine to the cost of the bytes inside it.

What did not change is the tooling, and this is the part that catches people. Nearly every framework expects one connection string in one setting. Nearly every migration tool expects one database with one version number, applied once, by a person watching it. Nearly every dashboard people build expects a query that returns every customer's rows at once. The arithmetic that produced the shared column stopped being true, and the defaults built on top of it carried on, because defaults are made of tools and habits rather than of reasoning that anybody revisits.

There is one more reason the review never happens. The failure mode of the shared column is rare and total rather than frequent and small. Designs that fail often get corrected, because the pain arrives in instalments and somebody is eventually paid to stop it. Designs that fail rarely and completely get defended, because every year that passes without an incident reads as evidence, right up until the afternoon it does not. That asymmetry is the honest reason so much good software still keeps everyone in one table: not that anybody decided the risk was acceptable, but that nothing ever forced the decision to be made again.

One organisation's database, from the day it is made to the day it is removed

What follows is invented. There is no such customer, and nothing here is a measurement or a case study. It is a hypothetical two person surveying firm we will call Kestrel, followed from signup to closure, because the argument above is easier to check against a story than against a diagram.

The first minute. Somebody at Kestrel signs up and names the organisation, and chooses where it will live: the European Union or the United States. That name becomes an identifier, the identifier addresses exactly one compute object, and the object is created. The schema is applied inside it. The first entry written into the audit trail is the creation of the organisation itself. At the end of that minute there exists a database whose entire contents, every table, every index, every row, concern one surveying firm, and a region choice that is now a physical fact rather than a setting.

An ordinary Tuesday. A surveyor signs in and opens a client. The request carries a session, the session names the organisation, and the organisation determines which object the request is forwarded to. Inside, the database is beside the code rather than across a network from it. If one of our queries has a condition missing, the surveyor sees the wrong records belonging to Kestrel: too many of their own clients, or their own clients in the wrong order. That is still a defect and we still want to hear about it. It is not a disclosure, and the person best placed to notice it is the person it happened to.

A deployment on Wednesday, while no one at Kestrel is working. Nothing happens to their database at the moment of the release. Their database has not been opened in two days and nothing wakes it. On the Monday, the first sign in of the week finds the version recorded inside that database behind the version the code expects, applies the outstanding steps, and writes each one into the journal as it completes, before the request is served. The first page of Kestrel's week is a little slower than the second. That is the visible cost of applying changes lazily, it is paid one firm at a time, and if a step is wrong it is wrong for Kestrel rather than for everyone at once.

The Tuesday afternoon someone makes a mistake. A large number of records are deleted in error and nobody notices until the following morning. Recovery here restores one organisation to one moment inside the last thirty days. Consider what is not involved in that sentence. No other customer's data moves. No window is announced to anybody else. No one weighs the risk to a thousand other firms against the benefit to this one, which is the calculation that causes shared systems to say no, and it is why the answer in most products is a sympathetic explanation of why the restore cannot be done. Whatever we then have to tell Kestrel about who deleted what, the evidence for it is in the same database as the records, written as it happened.

Eighteen months later, when the firm quietly stops. One of the two surveyors retires and the other goes back to a spreadsheet. Nobody signs in. After six months without a sign in the organisation is treated as dormant, and the administrators hear from us at six, seven and eight months, each time with an offer of a complete export. At twelve months the ordinary deletion process begins, with its own notice and its own grace period. None of that is a countdown we invented to sell an upgrade. It is what happens because holding someone's client list for years after the last person stopped looking at it is harder to defend than deleting it with warning.

The last minute. Closing the account removes a database. Not a flag on a row, not a status column that a report is expected to respect, not a set of rows awaiting a cleanup job that someone wrote in a hurry two years ago. There is no orphaned record left behind by a relationship nobody remembered, because there is nowhere for it to be left. What survives is the export Kestrel took, which is the reason we press people to run one while everything is calm rather than on the day they need it.

Read the story back and notice what is missing from it. At no point did another customer take part. In a shared database, the migration, the restore and the deletion would each have been an operation touching everybody's rows, carried out by a person who, for the length of that operation, was holding a connection that could read every customer in the product. Nothing in the Kestrel story required anybody to hold that.

The awkward cases, where this stops being convenient

A design is easy to like in the abstract. These are the places where ours is inconvenient, and they are worth knowing before you choose it rather than afterwards.

A person who works for two customers. A bookkeeper who does the accounts for two of our customers has two memberships in two organisations, and those organisations share nothing. Settings made in one are settings of that one. Anything they build in one is not visible from the other. This is correct, because the two firms have no relationship and neither would accept the other seeing their arrangements. It is also mildly annoying every day for the bookkeeper, and there is no version of this design where it is not.

The same human being in two customers' records. One person can appear as a contact in many customers' databases, and there is no query anywhere in our systems capable of finding them all. If that person asks to be erased, they have to ask each organisation that holds them, because each organisation is the controller of its own records and we are the processor acting for it, in the sense the Information Commissioner's Office gives those two words. That is the correct answer under data protection law and it is also the answer people find unsatisfying, because they imagine a single button somewhere. There is no such button, we could not build one without building the shared store this whole design exists to avoid, and we would rather explain that than pretend otherwise.

Uniqueness stops at the boundary. Any rule about two records not colliding is a rule inside one database. Nothing can span databases, because there is no place from which to look. The same email address can exist in the records of many different customers and nothing anywhere notices, which is exactly what those customers want, and it does mean that anybody arriving with an instinct for global uniqueness has to leave it at the door.

An outage is total for one customer and nearly invisible in the aggregate. Nobody else can slow Kestrel down, and the same property means that when something is wrong with Kestrel it is wrong completely, with no partial service to fall back on. We say that in the availability clause of the terms rather than claiming only the flattering half of it. Worse, a problem affecting one organisation barely moves a platform wide error rate. A monitoring approach built on aggregate numbers, which is how nearly everybody builds one, will not see a single customer at all. That is a thing to design for deliberately, and it is a genuine tax this shape imposes on the people operating it.

One organisation's work meets in one place. Everything for one customer arrives at one object instead of being spread across machines. A very large import therefore competes with the people trying to use the product while it runs. The compensation is that it competes only with them, and the same import in a shared system competes with strangers who have no idea why their afternoon became slow. We prefer contention that a customer can understand and schedule over contention that is somebody else's fault.

Supporting a customer is slower than it would otherwise be. An engineer here cannot open a console and look at your records to reproduce a problem, because there is no console onto a customer database. Opening one is an action inside the product that needs a stated reason and a second person's approval, and it writes an entry into that customer's own audit trail. That is deliberate, and the cost is real: some problems take longer to diagnose than they would at a company where an engineer can simply look. We think a support process that is slightly slower and always visible is the better arrangement, and we would rather state the cost than describe only the benefit.

A customer who outgrows the shape. Consonas is built for organisations of two to two hundred people, and one database per customer is part of why. An organisation with enormous data volumes, or one that genuinely needs analysis running across separate business units held as separate customers, is asking for something this design is bad at. If that is you, buy something else. A supplier who tells you their architecture suits everybody is telling you they have not found its edges yet.

The questionnaire this design answers oddly

Sooner or later a buyer sends a security questionnaire, and questionnaires are written from the other side of the table by people who have assessed a great many products built on a shared database. The questions therefore assume that design, and several of them have no comfortable box for us. It is worth setting out what we write in them, including where the honest answer is worse than the one the assessor expects.

"Describe the logical controls that separate tenant data." The expected answer names a filter in the application, a policy in the database, or both. Ours is that there is no shared store for a control to be applied to: each organisation has its own database inside its own compute, and code running for one has no connection to another and no way to obtain one. Assessors sometimes read that as evasion, because the question presumes the thing exists and the answer says it does not. The way to make it land is to answer the question underneath it, which is what a mistake produces here: a wrong answer to the right customer rather than the right answer to the wrong one.

"List the individuals with direct database access." The expected answer is a short list of administrators, reviewed quarterly. Ours is that there is no console onto a customer database and no standing route from anybody's machine into one. Access goes through the product, requires a stated reason and a second approver, and appears in the customer's own audit trail where they can read it without asking us. The questionnaire has no field for that, so it goes in the notes, and it is worth putting there because it is one of the few answers where the design is doing the work rather than a policy document.

"How do you prevent one tenant from affecting another's performance?" The expected answer describes limits, quotas and throttles. Ours is that there is nothing shared to contend for. The honest completion of that answer is the half most suppliers leave out: the same isolation means a problem confined to one organisation is total for that organisation, with no degraded mode, and the general availability of the platform tells that customer nothing about their own afternoon.

"Provide your SOC 2 report or ISO 27001 certificate." We hold neither, and there is no audit in progress at the time of writing. Both are meaningful and both cost a great deal for a company our size. If your procurement requires one, this is the point at which we tell you we are not the right supplier yet, rather than at the end of a six week process. No amount of architecture substitutes for a certificate, and any supplier offering you a diagram instead of the audit you asked for is answering a different question.

"Where is the data processed, and can that change?" The region is chosen by you when the organisation is created and fixed from then on, including for us, because the database physically exists where it was made. There are two regions, the European Union and the United States. There is no United Kingdom region, and a buyer in the United Kingdom needs to choose one of the two knowingly instead of discovering it later, which is why we say it in the answer instead of leaving it to be inferred.

"What is your recovery objective, and when did you last test a restore?" The question expects one number covering the whole platform, because in a shared system a restore is one operation affecting everyone. Here recovery is per customer: one organisation returned to one moment within the last thirty days, without a window and without touching anybody else. That is a better answer to the question the assessor meant, and a worse fit for the form they sent, and the reason to write it out in full is that the difference is the whole point.

The general lesson, for anybody on either side of that table, is that a questionnaire encodes an architecture. Half the value of filling one in honestly is discovering which of its questions do not apply to you and being able to say why, and the other half is noticing the questions that apply perfectly and that you would rather they had not asked.

Was it worth it

For us, yes, and the reason is what we are selling. A CRM holds the most sensitive commercial information a small organisation has. We tell buyers their data is separated from everyone else's. With a shared database that sentence means we intend to write every query correctly. With this, it means there is nowhere for the query to go.

We would not recommend it universally. If you are building something where customers genuinely share data, or where cross customer analysis is the product, this is the wrong shape and you will fight it constantly. It suits a product where the boundary between customers is absolute, which is exactly what a CRM is.

The parts of this that we are held to, rather than merely describing, are on the security page and in the sub processor list. What separation means in a trade that routinely holds two populations who must not meet is set out on recruitment and healthcare administration.

The other thing worth saying is that this is not a decision you can defer. Almost everything else in a product can be changed later at some cost. This one cannot. A product built on a shared database with three years of customers in it is not going to become a product with a database per customer, whatever anybody intends, because the migration is the whole system. If you think you might want it, you have to want it at the beginning, and that is the honest reason to write about it now rather than after it has become invisible.

Asked by engineers who read this far