Designing Multi-Tenant Databases

-- Strategy 1: Shared Tables with tenant_id
-- All tenants share the same tables, separated by a column
-- Simplest to manage, most cost-effective

CREATE TABLE tenants (
    tenant_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    subdomain VARCHAR(50) UNIQUE NOT NULL,
    plan VARCHAR(20) DEFAULT 'free',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE projects (
    project_id SERIAL PRIMARY KEY,
    tenant_id INT NOT NULL REFERENCES tenants(tenant_id),
    name VARCHAR(200)
...

-- Row-Level Security for automatic tenant isolation
-- The database itself prevents cross-tenant access!

-- Enable RLS on tenant tables
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

-- Set tenant context (called at connection start)
-- SET app.current_tenant = '42';

-- Create RLS policies
CREATE POLICY tenant_isolation_projects ON projects
    USING (tenant_id = current_setting('app.current_tenant')::INT);

CREATE POLICY tenant_isolation_tasks O
...

-- Strategy 2: Schema-per-Tenant
-- Each tenant gets their own PostgreSQL schema
-- Better isolation, slightly more complex

-- Create tenant schema
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;

-- Create tables in each schema
CREATE TABLE tenant_acme.projects (
    project_id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tenant_acme.tasks (
    task_id SERIAL PRIMARY KEY,
    project_id INT REFERENCES tenant_acme.project
...