Lesson 19.719.7 Three ways to keep data apart

In everyday life. Chapter 18 compared tenancy to housing: an apartment building, a row of townhouses, a separate house. Storage works the same way. A self-storage company can rent you a locker in a shared hall, a room of your own in its building, or a whole unit on another site. All three keep your boxes apart. They differ in how thick the walls are, what they cost, and how much work it is to look after a thousand of them.

What you will learn. Three ways to keep many companies' data apart, what each costs, and why Brightline starts with shared tables.

Why it matters. The model you choose decides what stops a forgotten filter in the code: the database itself, or a connection that must be pointed at the right place every time.

One request, start to finish. Two requests reach the business database in the same second. In the first model below, both read the same invoices table. In the second, each reads its own company's copy of the table. In the third, each goes to a different database. In all three, Luis must get Cedar Ridge's invoices and never Brightline's. What differs is who makes sure of it.

What are the three ways?

Two words first, both used in the table below. Compute is the machine time that runs a database's queries, billed separately from the storage that holds its rows. The catalog is the database's own list of every table, column and rule it holds. It grows with every table, so thousands of copies of the same tables make it large and slow.

There are three ways to keep many companies' data in a relational database. Each is a data tenancy model, the data side of Chapter 18's tenancy models:

How do they pair with sign-in?

Chapter 18's first model, one shared connector, pairs naturally with shared tables: one connector, one database, the company from the token on every request. A company that moves to its own deployment (Chapter 18's third model) usually gets its own database too. Whatever the model, the rule from 18.10 does not change: the company comes from the token, and only from the token.

When do you move a company?

Start every company in shared tables. Move one to its own schema or database for a reason you can write down: a contract that requires its data in one country, a size that would slow the others, or its own backup schedule.

Which model does Brightline use?

Brightline uses shared tables with row-level security. Its connector serves two small companies, Brightline itself and Cedar Ridge, and expects more of the same size. Shared tables cost least and need one migration. Their guard is the database itself: with row-level security forced on every table (19.8), a query that forgets its filter still sees only its own company's rows. The other two models need no filter at all, and depend instead on the connection being pointed at the right schema or database, every time.

Whose job is it to keep companies apart?

The patterns belong to Postgres, the job belongs to you. All three models are long-established Postgres patterns, and any Postgres host can run them. A hosting service can make one company its own database quickly. It cannot decide which company a request is for, set that company for the transaction, or check that every table is forced. Correct isolation is always the application's job, whichever host it runs on. 19.8 to 19.12 are how Brightline does that job.

At work. The large hospital chain from Chapter 18 asks for its own database, kept in one region. The builder writes in docs/data-spec.md: "Model 1 for every company. Model 3 by contract: its own database and its own deployment of the connector, the same migrations, run once more." Cedar Ridge shares tables with Brightline.

The operating rule: name your data tenancy model before you build. Start with shared tables and row-level security. Move a company out only for a reason you can write down.

Figure 19.13. Three data tenancy models. Brightline uses the first, and can move a company to the third.