Chapter 8 · Foundations

The data model on day one

Five tables, every column justified, and the four choices in them that are expensive to change later: key type, the tenant slug, soft deletion and where the Connect account id lives.

7 min read

The schema that Chapter 7 called the first week's output, written out with the reasoning attached. Four of these decisions are costly to reverse and are marked.

The tenant

create table tenants (
  id                  uuid primary key default gen_random_uuid(),
  slug                citext not null unique,
  name                text not null,
  connect_account_id  text unique,
  connect_status      text not null default 'pending'
                        check (connect_status in ('pending','restricted','active','rejected')),
  created_at          timestamptz not null default now(),
  deleted_at          timestamptz
);

id as uuid, not a sequence. ⚠️ Expensive to reverse. A tenant id appears in URLs, in webhook metadata, in support tickets and in the payment processor's records. Sequential integers leak your customer count to anyone who reads one, and they make a guessed id a plausible attack — see Chapter 44. Changing the key type after launch means rewriting every foreign key and every stored reference in an external system.

slug as citext. The human-readable identifier (/t/acme/...). citext makes uniqueness case-insensitive in the database rather than in whichever code path happens to remember. Without it, Acme and acme are two tenants, and the one that signs up second takes a URL the first one's users will reach by accident.

connect_status as a checked enum, separate from the account id. The account id existing does not mean the tenant can take money. A tenant can have an account that is restricted because verification documents were rejected. Collapsing these two facts into one nullable column is how a product ends up offering a checkout button to a vendor who cannot be paid — Section 5.

deleted_at rather than a hard delete. ⚠️ Expensive to reverse. Soft deletion is right for a tenant specifically, because deletion is a process — payouts in flight, an invoice not yet settled, a legal retention period — and a deleted tenant must stop working immediately while its rows survive briefly. Chapter 148 covers turning this into real erasure, which is the part most products never build.

Identity and membership

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

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

-- Every tenant must keep at least one owner. Enforced, not assumed:
-- a UI that prevents removing the last owner is bypassed by the API.
create unique index memberships_one_owner_per_tenant
  on memberships (tenant_id) where role = 'owner';

That partial unique index is doing something subtle and worth reading twice: it permits exactly one owner per tenant. Whether you want exactly one or at least one is a product decision — at-least-one needs a trigger or a deferred constraint instead — but the point is that "a tenant always has an owner" must live in the database. Enforced only in the UI, it survives until the first API client.

users.email is globally unique and carries no tenant. This is the decision from Chapter 7, restated because it is the one that gets undone under pressure. One person, one login, many memberships.

The tenant-scoped tables

create table products (
  id          uuid primary key default gen_random_uuid(),
  tenant_id   uuid not null references tenants(id) on delete cascade,
  name        text not null,
  price_cents integer not null check (price_cents >= 0),
  created_at  timestamptz not null default now(),

  -- Unique per tenant, not globally. Two vendors may both sell a "Pro" plan.
  unique (tenant_id, name)
);

create table customers (
  id         uuid primary key default gen_random_uuid(),
  tenant_id  uuid not null references tenants(id) on delete cascade,
  email      citext not null,
  name       text not null,
  created_at timestamptz not null default now(),

  -- Also per tenant. The same business can be a customer of two vendors,
  -- and those are two separate rows with two separate relationships.
  unique (tenant_id, email)
);

create index products_tenant_name_idx  on products  (tenant_id, name);
create index customers_tenant_email_idx on customers (tenant_id, email);

unique (tenant_id, name) instead of unique (name) is the pattern that recurs through the entire schema. Every uniqueness constraint in a multi-tenant system is scoped unless you can state why it is not. A globally unique product name is a bug that presents as "we can't add this plan" and takes a surprising amount of time to diagnose.

The shape, as a picture

The four expensive decisions, listed

Decision	Chosen	Cost of changing later
Key type	`uuid`	Rewrite every foreign key and every id stored in an external system
Tenant slug casing	`citext` unique	Two tenants already own the same name in different cases
Tenant deletion	`deleted_at`	Hard deletes already lost rows a retention period required
Where Connect state lives	On `tenants`, split id from status	Every call site that checked "has an account" must learn the difference

What is deliberately absent

No subscriptions, seats, invoices or entitlements yet. They arrive in Sections 7 and 8 with the billing model they belong to. Adding them now would mean guessing at the seat model before the proration rules are understood, and a schema written against a guess is worse than one written late.

A day-one schema should contain every table the first real transaction touches and nothing it does not. Five tables is enough to onboard a vendor, publish a plan, register a customer and take a payment.

Key points

uuid keys, because a tenant id travels into URLs, webhooks and support tickets, and a sequential one leaks your customer count.
citext for the tenant slug and for every email, so case-insensitive uniqueness is a database guarantee rather than a code habit.
Split the Connect account id from its verification status. An account existing is not permission to take money.
deleted_at on tenants, because deletion is a process with payouts and retention inside it.
Keep at least one owner per tenant with a database constraint. UI enforcement lasts until the first API client.
Scope every uniqueness constraint to the tenant unless you can say why not. unique (tenant_id, name), not unique (name).
Index on (tenant_id, column), leading with the tenant.
Omit the billing tables until the billing model is understood. A schema written against a guess costs more than one written late.

Next: The decisions that are expensive to reverse — the full list, gathered in one place before any of them is made by accident.