Designing the Multi-Tenant Schema

The Tenant Boundary Is the First Schema Decision

Before thinking about projects or tasks, Fieldwork had to decide what 'multi-tenant' means physically in Postgres. The tempting answer is to postpone that decision: start with ordinary tables, add tenant_id later, and let the application sort it out. That is how systems earn themselves a painful retrofit. In Fieldwork, tenant isolation is not an afterthought layered onto a task API. It is the primary boundary that every query, index, and foreign key has to respect.

I chose a shared database and shared schema, with tenant_id carried explicitly through tenant-owned tables. The rejected alternatives were database-per-tenant and schema-per-tenant. Those models buy stronger physical separation, but at the cost this system does not need yet: migration fan-out, harder reporting across tenants, more operational overhead in local development, and more complex connection management. Fieldwork is meant to teach production trade-offs, not hide them under fleet management work.

The Core Tables and Their Keys

Fieldwork's domain is intentionally plain: users, tenants, workspaces, memberships, projects, and tasks. The important part is not the nouns themselves but how identity and containment flow through them. A workspace belongs to one tenant. A project belongs to one workspace and therefore one tenant. A task belongs to one project, one workspace, and one tenant. That repetition is deliberate. It makes joins safer, indexes more targeted, and data leaks harder to create by accident.

create extension if not exists citext;

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

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

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

create table memberships (
    id uuid primary key,
    tenant_id uuid not null references tenants(id) on delete cascade,
    workspace_id uuid not null references workspaces(id) on delete cascade,
    user_id uuid not null references users(id) on delete cascade,
    role text not null check (role in ('owner', 'admin', 'member', 'viewer')),
    invited_by_user_id uuid references users(id),
    created_at timestamptz not null default now(),
    unique (workspace_id, user_id)
);

create table projects (
    id uuid primary key,
    tenant_id uuid not null references tenants(id) on delete cascade,
    workspace_id uuid not null references workspaces(id) on delete cascade,
    slug text not null,
    name text not null,
    description text not null default '',
    status text not null check (status in ('planned', 'active', 'archived')),
    created_at timestamptz not null default now(),
    archived_at timestamptz,
    unique (workspace_id, slug)
);

create table tasks (
    id uuid primary key,
    tenant_id uuid not null references tenants(id) on delete cascade,
    workspace_id uuid not null references workspaces(id) on delete cascade,
    project_id uuid not null references projects(id) on delete cascade,
    title text not null,
    description text not null default '',
    status text not null check (status in ('todo', 'in_progress', 'done', 'canceled')),
    priority text not null check (priority in ('low', 'medium', 'high', 'urgent')),
    assignee_user_id uuid references users(id),
    created_by_user_id uuid not null references users(id),
    due_at timestamptz,
    position bigint not null default 0,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    completed_at timestamptz
);

The users table is global because a person can belong to many tenants and workspaces. Everything else that represents customer data is tenant-owned. Memberships carry both tenant_id and workspace_id because access checks are workspace-scoped but tenant filtering is still a first-class part of the read path. If you only derive tenant ownership through joins, you make every authorization query more brittle than it needs to be.

Why Some IDs Repeat Across Tables

The most arguable decision in this schema is storing tenant_id and workspace_id directly on tasks instead of deriving them through project_id. Normalization purists will complain that project_id already implies workspace and tenant membership. They are right in the narrow relational sense and wrong in the operational sense this system cares about. Fieldwork will read tasks far more often than it rewrites project ancestry, and nearly every task query is tenant-scoped. Carrying those keys on the row turns that common path into a simple indexed predicate instead of a mandatory join chain.

It also gives us a second safety layer. When transactions update tasks, the code can assert project_id, workspace_id, and tenant_id together. That makes accidental cross-tenant joins easier to spot and cheaper to prevent. The storage cost is trivial compared with the debugging cost of discovering a missing join condition after data has leaked into the wrong response payload.

Constraints and Indexes That Do Real Work

Fieldwork avoids decorative indexes. Every index has to justify itself against a real access pattern. Workspace slugs are looked up within a tenant, so unique(tenant_id, slug) lives on workspaces. Projects are often listed by workspace with active-first sorting, so that informs their index shape. Tasks are filtered by tenant, workspace, project, status, assignee, and cursor order. Those are the access patterns to optimize for, not hypothetical ad-hoc queries we do not plan to serve from the API.

create index idx_memberships_workspace_user
    on memberships (workspace_id, user_id);

create index idx_projects_tenant_workspace_status_created
    on projects (tenant_id, workspace_id, status, created_at desc, id desc);

create index idx_tasks_tenant_workspace_project_created
    on tasks (tenant_id, workspace_id, project_id, created_at desc, id desc);

create index idx_tasks_tenant_assignee_status
    on tasks (tenant_id, assignee_user_id, status, updated_at desc)
    where assignee_user_id is not null;

create index idx_tasks_tenant_status_due
    on tasks (tenant_id, status, due_at asc, id asc)
    where due_at is not null;

I also chose text columns plus CHECK constraints for status, role, priority, and plan instead of PostgreSQL enum types. Enums look tidy until you need to evolve them in a course that teaches migrations and backward-compatible rollout. Text plus CHECK makes versioned schema changes easier to reason about and easier to reverse. That is a better fit for this project than enum purity.

What We Rejected

Three options were rejected deliberately. First, deriving tenant scope only through deep joins, because it makes the most important safety boundary implicit. Second, database-per-tenant, because the operational burden would dominate the lessons long before isolation benefits mattered. Third, an ORM-generated schema, because I want the column names, foreign keys, and indexes to be explicit artifacts we can discuss and evolve with intent. Fieldwork is teaching the data layer, so the data layer should not be hidden behind code generation.

The Decision

• One shared Postgres schema, not a schema-per-tenant or database-per-tenant fleet.
• users are global; customer data tables are tenant-owned.
• tenant_id is explicit on workspaces, memberships, projects, and tasks.
• Text plus CHECK constraints beat Postgres enums for easier evolution.
• Indexes start with tenant scope because nearly every API request does too.

Fieldwork's schema is intentionally concrete because later lessons depend on it. Tenants own workspaces, workspaces own projects, projects own tasks, and memberships connect users to workspaces with roles. The decision was to model tenant scope explicitly, even when that means repeating containment keys, because safety and predictable query shapes matter more here than textbook minimalism.