Turning a Single-Tenant App Into Multi-Tenant SaaS Without a Rewrite

You can turn a single-tenant app into multi-tenant SaaS without a rewrite by migrating the data model in place. Add a tenants table and a tenant_id column to every customer-owned table, backfill it in batches, enforce it with constraints, scope every query by the session's tenant, switch on PostgreSQL row-level security, then move customers onto the shared deployment one at a time.

This is the path for a common situation: you built the product for one customer, or you run a copy per customer, and now every new sale means another deployment to patch. The code mostly works. What it lacks is a concept of "whose data is this". This guide is engineering guidance for a web app on PostgreSQL; the SQL is PostgreSQL 18. If you are still deciding whether to pool tenants at all, read multi-tenant vs single-tenant SaaS and database per tenant vs shared schema first. This post assumes you have chosen a shared schema with a tenant column.

Do you really need to rewrite a single-tenant app to make it multi-tenant?

Usually not. A rewrite throws away the part that is hardest to rebuild: years of business rules and edge cases that customers rely on. Multi-tenancy is mostly a data-scoping change. The tables stay, the screens stay, most of the business logic stays. What changes is that every row gets an owner, and every read and write proves it belongs to the caller's owner.

A rewrite starts to make sense only when the app has no automated tests, no clear data layer (SQL scattered through templates, for example), and the team cannot say where data is read. Even then, the more reliable route is to add tests and a data layer first, then migrate. The broader trade-off is in rewrite vs refactor.

What are the phases of a single-tenant to multi-tenant migration?

Six phases, each shippable on its own, so production never waits on a big-bang cutover.

Phase	What changes	Risk if skipped	Typical effort (mid-sized app)
1. Inventory	List every table, cache, file path, job and third-party ID that holds customer data	A table you missed becomes the leak	2 to 5 days
2. Schema	tenants table, nullable tenant_id columns, indexes	None yet; nothing reads it	2 to 4 days
3. Backfill and enforce	Every existing row gets the first tenant's ID; NOT NULL and foreign keys go on	Rows with no owner, invisible once RLS is on	1 to 3 days
4. Scope the code	Tenant resolved from the session; every query, cache key and job carries it	Cross-tenant reads the day a second tenant arrives	1 to 4 weeks
5. Database enforcement	Row-level security policies, a non-owner app role, CI checks	A forgotten WHERE clause returns everyone's rows	3 to 7 days
6. Rollout	Customers migrated one by one from their own copy into the shared deployment	A failed cutover affects everyone at once	1 to 3 days per customer, plus a pilot

The effort column is an engineering estimate for an app with roughly 30 to 80 tables and a working test suite, not a quote. Phase 4 is the long one, because it touches every query.

How do you add tenant_id to a live database without downtime?

Add the column as nullable first. PostgreSQL's ALTER TABLE documentation says that when a column is added with no default, or a non-volatile default, "a rewrite of the table" is not required, so the statement is fast even on large tables.

CREATE TABLE tenants (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name       text NOT NULL,
  slug       text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- The customer the app was built for becomes the first tenant.
INSERT INTO tenants (id, name, slug)
VALUES ('00000000-0000-0000-0000-000000000001', 'Original customer', 'original');

-- Repeat per customer-owned table. Fast: no table rewrite.
ALTER TABLE invoices ADD COLUMN tenant_id uuid;

-- Outside a transaction: builds without blocking writes.
CREATE INDEX CONCURRENTLY invoices_tenant_id_idx ON invoices (tenant_id);

Build the index with CONCURRENTLY. The CREATE INDEX documentation explains that a standard build "locks out writes (but not reads)" until it finishes, while the concurrent form does not, at the cost of taking longer and not running inside a transaction block. Most queries will filter by tenant first, so in practice you often want composite indexes such as (tenant_id, created_at) rather than tenant_id alone.

How do you backfill tenant_id safely?

In batches, so no single statement holds locks on millions of rows. Every existing row belongs to the original customer, so the value is known:

-- Run repeatedly until it reports UPDATE 0.
UPDATE invoices
SET    tenant_id = '00000000-0000-0000-0000-000000000001'
WHERE  id IN (
  SELECT id FROM invoices
  WHERE  tenant_id IS NULL
  LIMIT  5000
);

Before the backfill, deploy code that writes tenant_id on every insert. Otherwise new rows arrive with NULL while you are filling the old ones.

Then enforce it without a long lock. Add a check constraint as NOT VALID, validate it, and only then set NOT NULL:

ALTER TABLE invoices
  ADD CONSTRAINT invoices_tenant_id_not_null CHECK (tenant_id IS NOT NULL) NOT VALID;

ALTER TABLE invoices VALIDATE CONSTRAINT invoices_tenant_id_not_null;

-- Skips the full-table scan because the valid CHECK already proves it.
ALTER TABLE invoices ALTER COLUMN tenant_id SET NOT NULL;
ALTER TABLE invoices DROP CONSTRAINT invoices_tenant_id_not_null;

ALTER TABLE invoices
  ADD CONSTRAINT invoices_tenant_fk FOREIGN KEY (tenant_id) REFERENCES tenants (id) NOT VALID;
ALTER TABLE invoices VALIDATE CONSTRAINT invoices_tenant_fk;

This sequence comes straight from the PostgreSQL docs. With NOT VALID, the constraint "does not scan the table and can be committed immediately"; validation takes only a SHARE UPDATE EXCLUSIVE lock, which does not block normal reads and writes; and SET NOT NULL skips its table scan when "a valid CHECK constraint exists ... which proves no NULL can exist".

Two details catch teams out. Unique constraints usually need the tenant added: an invoice number unique across the whole table becomes unique per tenant, UNIQUE (tenant_id, number). And child tables should carry their own tenant_id rather than inheriting it through a join, so each table can have its own policy.

How do you scope every query by tenant in the application?

Resolve the tenant once, from the verified session, and pass it down. Never read it from the request body, the query string or a client-controlled header.

// One place decides the tenant. Handlers never read it from input.
export async function withTenant<T>(
  session: { tenantId: string },
  work: (db: PoolClient) => Promise<T>,
): Promise<T> {
  const client = await pool.connect()
  try {
    await client.query('BEGIN')
    // Transaction-scoped, so a pooled connection never carries it to the next request.
    await client.query("SELECT set_config('app.tenant_id', $1, true)", [session.tenantId])
    const result = await work(client)
    await client.query('COMMIT')
    return result
  } catch (err) {
    await client.query('ROLLBACK')
    throw err
  } finally {
    client.release()
  }
}

Then work through the inventory from phase 1. The places that leak after a migration are rarely the main CRUD screens; they are the side paths:

• Caches. Every key gets the tenant. A cached "dashboard totals" with no tenant in the key serves one customer's numbers to the next.
• Background jobs. Each job payload carries a tenantId, and the worker opens its transaction with it, instead of running as an admin role over every row.
• File storage. Prefix object keys with the tenant and check the tenant before issuing a signed URL.
• Settings that were global. Branding, email sender, tax rates and feature flags that lived in environment variables become per-tenant rows.
• Users. Decide early whether a user can belong to more than one tenant. If yes, you need a membership table and a tenant switcher; if no, users.tenant_id is enough. Roles move onto that membership, which is covered in SaaS authorization and RBAC design.

When do you turn on row-level security?

After the code sets app.tenant_id on every path, and before the second tenant's data arrives. Row-level security (RLS) makes the database add a tenant condition to every query, so a missed WHERE returns nothing instead of everything.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING      (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid)
  WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid);

Turn it on while there is still only one tenant. Any code path that forgot to set the tenant now returns zero rows, which shows up in your tests and logs as a visible bug instead of a silent leak later. PostgreSQL's row security documentation is clear that superusers and roles with BYPASSRLS always bypass policies, and table owners do too unless the table is forced, so the app must connect as a separate role that owns nothing. The full setup, including pooling pitfalls, is in the PostgreSQL row-level security guide.

Add a CI query that fails the build if any table with a tenant_id column has RLS off or no policy. That is the check that catches the table someone adds next quarter.

How do you roll customers onto the shared deployment?

One at a time, starting with a friendly customer, with a rollback plan for each.

1. Keep the original customer as tenant one. Their data never moves; the shared deployment is their old deployment, now tenant-aware.
2. Import the next customer into a new tenant. Export from their separate copy, remap primary keys to avoid collisions (UUIDs make this easy; integer IDs need an offset or a mapping table), and stamp every row with the new tenant_id.
3. Verify before cutover. Row counts per table, totals that matter (invoice sums, balances), and a login as that customer's admin to check the screens.
4. Switch their domain or subdomain to the shared deployment. Keep their old copy read-only for an agreed window as the rollback.
5. Run the cross-tenant test suite with the new tenant: log in as one, try to read the other's records by ID, and expect 404 every time.

If a cross-tenant read ever does slip through, the containment steps are in users can see another tenant's data.

Why RAITHub for this migration?

Because the risky part is not the SQL, it is finding every path where data moves, and proving each one is scoped. RAITHub has built and tested that kind of isolation on live platforms.

• Database-enforced isolation in production. Sundor Skin, a B2B wholesale platform RAITHub built, runs 146 PostgreSQL tables with row-level security, 88 permission codes, 12 staff roles and 530+ automated tests. It is not a SaaS, but isolating one business buyer from another is the same problem.
• Takeover without a rewrite. This migration is a code-rescue shape of work: map the existing app, add tests around it, then change the data model under it. That is the code rescue service.
• Fixed scope. Phases 1 to 3 are easy to scope precisely, so they can be a fixed-price first step after a free 15-minute technical audit. The code stays yours.

When you don't need us

• You will only ever have a handful of large customers who each want their own environment. Keep single-tenant and automate the deployments instead.
• The app has a small data layer and good tests. A competent in-house developer can follow this guide phase by phase.
• You are starting fresh. Build multi-tenant from day one; the multi-tenant SaaS guide and the SaaS development service cover that.

If your single-tenant app is ready for its second customer, send RAITHub a short description of the app and its database and book the free audit.

Last reviewed: 29 September 2026. SQL checked against the PostgreSQL 18 documentation.

Frequently asked questions

Can I convert a single-tenant app to multi-tenant without a rewrite?

Yes, in most cases. Add a tenants table and a tenant_id column to every customer-owned table, backfill it, enforce it with constraints, scope every query by the session's tenant, add row-level security, then migrate customers one at a time.

How long does it take to make an app multi-tenant?

For an app with roughly 30 to 80 tables and a working test suite, a realistic engineering estimate is 3 to 8 weeks, most of it spent scoping queries, caches and jobs. Apps without tests take longer, because the tests have to come first.

Will adding tenant_id lock my production tables?

Not if you do it in the right order. Adding a nullable column is fast in PostgreSQL, indexes can be built CONCURRENTLY, and constraints added NOT VALID then validated avoid long write locks. Backfill in batches rather than one large UPDATE.

Should I use row-level security or just add WHERE tenant_id to queries?

Both. Scoped queries give clean 404s and readable code; row-level security catches the query someone forgets to scope. Turn RLS on while there is still one tenant, so missed paths show up as empty results rather than leaks.

What about customers who already have their own copy of the app?

Import each one into a new tenant: remap their IDs, stamp every row with the new tenant_id, verify counts and totals, then switch their domain. Keep the old copy read-only for an agreed period as the rollback.

Can a user belong to more than one tenant?

Only if you design for it. That needs a memberships table linking users to tenants with a role on each membership, and a way to switch tenants. Decide this before the migration, because it changes how sessions and roles work.