Multi-Tenant PostgreSQL Schema Design

Why Schema Design Comes First

Multi-tenancy is not a filter added near the end of a query. It is the storage rule that determines what a row means, which indexes are worth building, which foreign keys are safe, and which bugs become possible. In a shared-schema system, every tenant-scoped table must answer the question 'which HOA owns this row?' without relying on application folklore or an optional join path.

Meridian chooses the standard SaaS trade-off for this phase: shared-schema isolation with an explicit tenant_id column on every tenant-owned table. That decision keeps local development, migrations, analytics, and operations tractable while the platform is still young. The price is that every read and write path must treat tenant scope as part of correctness, not merely part of performance.

Shared Schema vs Physical Isolation

A beginner often hears two half-truths: shared schema is 'simple but unsafe,' while database-per-tenant is 'serious production architecture.' Both statements are incomplete. Shared schema can be entirely appropriate when the product needs operational simplicity and the engineering team is willing to make tenant scope explicit everywhere. Database-per-tenant can be appropriate when compliance or noisy-neighbor isolation dominates every other concern. Architecture is a workload decision, not a prestige decision.

For Meridian, the strongest argument for shared schema is that every service will own many relatively small tenant partitions: one HOA's residents, units, announcements, invoices, and payments. Those rows are ideal for composite indexes that begin with tenant_id. The platform benefits more from predictable access patterns and simple migrations than from carrying a fleet of tiny databases before scale or compliance requires it.

The Meridian Aggregate Map

Meridian's domain is split by bounded context, but the tenant rule repeats across all of them. identity-service owns tenants, users, memberships, and role assignment. community-service owns buildings, units, residents, announcements, and complaints. billing-service owns invoices and payments. The service boundary changes; the tenant discipline does not.

create table tenants (
    id uuid primary key,
    slug text not null unique,
    name text not null,
    created_at timestamptz not null default now()
);

create table users (
    id uuid primary key,
    email text not null unique,
    full_name text not null,
    created_at timestamptz not null default now()
);

create table memberships (
    id uuid primary key,
    tenant_id uuid not null references tenants(id) on delete cascade,
    user_id uuid not null references users(id) on delete cascade,
    role text not null,
    created_at timestamptz not null default now(),
    unique (tenant_id, user_id)
);

create table buildings (
    id uuid primary key,
    tenant_id uuid not null references tenants(id) on delete cascade,
    name text not null,
    address_line_1 text not null,
    created_at timestamptz not null default now()
);

create table units (
    id uuid primary key,
    tenant_id uuid not null references tenants(id) on delete cascade,
    building_id uuid not null references buildings(id) on delete cascade,
    unit_number text not null,
    floor_label text,
    created_at timestamptz not null default now(),
    unique (tenant_id, building_id, unit_number)
);

create table residents (
    id uuid primary key,
    tenant_id uuid not null references tenants(id) on delete cascade,
    unit_id uuid not null references units(id) on delete restrict,
    full_name text not null,
    email text,
    status text not null,
    created_at timestamptz not null default now()
);

create table invoices (
    id uuid primary key,
    tenant_id uuid not null references tenants(id) on delete cascade,
    resident_id uuid not null references residents(id) on delete restrict,
    unit_id uuid not null references units(id) on delete restrict,
    amount_cents bigint not null check (amount_cents > 0),
    currency char(3) not null,
    status text not null,
    due_date date not null,
    created_at timestamptz not null default now()
);

create table payments (
    id uuid primary key,
    tenant_id uuid not null references tenants(id) on delete cascade,
    invoice_id uuid not null references invoices(id) on delete restrict,
    resident_id uuid not null references residents(id) on delete restrict,
    amount_cents bigint not null check (amount_cents > 0),
    status text not null,
    paid_at timestamptz,
    created_at timestamptz not null default now()
);

Why repeat tenant_id on units, residents, invoices, and payments when building_id, unit_id, or invoice_id could theoretically imply it? Because production systems are optimized for the common query and defended against the common mistake. The common query begins with tenant scope. The common mistake is forgetting one containment join under deadline pressure. Explicit tenant_id makes both the fast path and the safe path easier.

Constraints and Indexes

A schema is not complete once tables compile. Constraints encode invariants; indexes encode access paths. A senior backend engineer should be able to explain both. If a uniqueness rule matters to the business, it belongs in the database. If a list endpoint will always start with tenant_id and often continue with due_date or created_at, the index should reflect that reality instead of hoping PostgreSQL improvises around a weak design.

create index idx_memberships_tenant_user
    on memberships (tenant_id, user_id);

create index idx_units_tenant_building
    on units (tenant_id, building_id, unit_number);

create index idx_residents_tenant_unit
    on residents (tenant_id, unit_id, status, id);

create index idx_announcements_tenant_created
    on announcements (tenant_id, created_at desc, id desc);

create index idx_complaints_tenant_status_created
    on complaints (tenant_id, status, created_at desc, id desc);

create index idx_invoices_tenant_due_date
    on invoices (tenant_id, due_date asc, id asc);

create index idx_payments_tenant_invoice
    on payments (tenant_id, invoice_id, created_at desc);

-- wrong: invoice lookup by id alone in a shared schema
select id, amount_cents, status
from invoices
where id = $1;

-- right: invoice lookup by tenant boundary and id
select id, amount_cents, status
from invoices
where tenant_id = $1
  and id = $2;

Current Repository Status

• Choose the tenancy model before writing repositories or handlers.
• Put tenant_id on every tenant-scoped table, even when a parent key also implies ownership.
• Design uniqueness constraints and indexes around real tenant-scoped access paths.
• Treat shared-schema isolation as a correctness discipline, not a temporary shortcut.