11 The Complete Reference Schema

This is the whole database, written out: sixty-one tables covering tenancy, catalog, partners, inventory, sales, purchasing, fulfillment, finance, and integration ops, each with runnable PostgreSQL, the indexes it needs, and its row-level-security policy. Everything the previous ten chapters argued for — the append-only ledger, tenant isolation, idempotency keys, optimistic concurrency, the outbox — shows up here as concrete DDL you can paste into a migration file.

(DDL means "data definition language": the part of SQL that creates and alters tables, rather than the part that reads and writes rows. A migration file is one numbered file of DDL that you run against the database once and never edit again.)

Keep this chapter open in a second tab; the rest of the book is the reasoning, and this is the answer sheet.

11.0 How to read this chapter

Every table in this chapter is presented the same way, four beats in a row:

1. First, a plain-English paragraph: what the table represents in the real world, and the exact moment a row appears in it.
2. Second, the create table statement with every column, every type, every default, and every constraint — nothing elided, nothing left as "and so on".
3. Third, the indexes, each with a one-line justification, because an index you cannot justify is an index you will pay for on every write for the rest of the product's life.
4. Fourth, the row-level-security (RLS) policy that makes the table safe in a multi-tenant database.

A word on how to use it. Do not read this chapter front to back on your first pass. Read section 11.1 (tenancy), then jump to whichever section matches the feature you are building, and come back.

The schema is large because the domain is large: an apparel ERP has to know that a style comes in colorways and sizes, that a purchase order arrives in a container six weeks late, and that a retailer will pay ninety percent of an invoice and keep the rest as a chargeback — a penalty they deduct themselves, without asking, for something like a late shipment. Every table here exists because leaving it out means someone stores that fact in a spreadsheet instead.

Finally, one promise and one warning. The SQL here is real PostgreSQL and it runs, in the order presented, on PostgreSQL 14 or newer. (Supabase runs PostgreSQL 15 and 17 as of July 2026, so a free Supabase project takes it as written.) The warning: a schema only governs the shape of your data. Constraints stop bad rows. Bad workflows — the right rows written in the wrong order, or never written at all — walk straight past them. Chapter 9's invariant tests and this chapter's invariants section are how you prove the workflows are right.

11.0.1 Naming and typing conventions

Consistency in a schema is worth more than cleverness. When every table follows the same shape, you can guess a column name correctly without opening the file, an ORM (object-relational mapper — the library that turns database rows into objects in your program) can generate types mechanically, and a code reviewer notices the one table that breaks the pattern, which is usually the one with the bug. Here are the rules this schema follows, without exception unless the exception is called out explicitly.

Why UUID primary keys

Every table's primary key is a uuid, defaulted to gen_random_uuid(). A UUID (universally unique identifier) is a 128-bit value, written as 36 characters like 3f2504e0-4f89-41d3-9a0c-0305e82c3301. The flavor gen_random_uuid() produces is version 4, which is a random number, so two machines can each mint one and never collide. Compare that to a bigserial, an auto-incrementing integer. Integers are smaller (8 bytes against 16) and index slightly better, so why pay for the UUID?

Four reasons, all of which come from the rest of this book:

1. One: offline clients. Chapter 7's PowerSync layer lets a warehouse tablet create an order while disconnected. The tablet must be able to mint the row's primary key itself, immediately, with no round trip, and it must not collide with the key some other tablet minted at the same second. A sequence cannot do this; a UUID can.
2. Two: no cross-tenant information leak. Sequential integers tell an observer how many orders exist and let them guess neighboring keys; with RLS on, a guessed key returns nothing, but you have still published your order volume in every URL.
3. Three: safe merges. Import batches, integration back-fills, and staging-to-production data copies can generate keys ahead of time and merge without renumbering.
4. Four: keys are stable across environments, so a bug report that references an ID means the same thing in a database dump as in production.

The cost of random keys

The cost is real. A B-tree is the sorted, tree-shaped structure PostgreSQL uses for nearly every index, and a new entry goes wherever its key sorts to. Random UUIDs therefore land at random places, which spreads writes across the whole index instead of appending neatly to the right-hand edge.

On PostgreSQL 18 and later you can get most of it back with uuidv7(), which prefixes a millisecond timestamp so new keys sort near each other; on older versions you can install an extension or generate v7 in the application. This schema uses gen_random_uuid() because it works everywhere; swapping the default to uuidv7() is a one-line change per table and touches no application code.

Money columns use NUMERIC

Money columns are numeric(18,4): up to 18 total digits, 4 of them after the decimal point. numeric stores numbers in base-10 groups and does exact decimal arithmetic.

real and double precision are binary floating point: they store the nearest representable binary fraction, and 0.1 is not representable in binary any more than 1/3 is in decimal. So select 0.1::float8 + 0.2::float8 = 0.3::float8 returns false in PostgreSQL, exactly as it does in JavaScript.

In an ERP that error compounds: an invoice built from forty lines of float arithmetic will disagree with the customer's remittance by a cent, the payment will not fully apply, the invoice will sit at "partially paid" forever, and someone in accounts receivable will lose an afternoon to it. (A remittance is the note a customer sends with a payment saying which invoices the money is meant to cover.)

Do not use PostgreSQL's money type either. It carries no currency code, and both its formatting and its number of decimal places come from the server's lc_monetary locale setting rather than from you. The manual is blunt about it: "The fractional precision is determined by the database's lc_monetary setting", and loading money data into a server configured differently "might not work". We store the amount in numeric and the currency in a separate three-letter column, because a multi-tenant ERP will sell in USD and buy in CNY in the same transaction.

Why four decimal places, not two

The scale question, why four decimal places and not two, is about intermediate values. A unit price of $12.99 with a 15% discount is exactly $11.0415. In a numeric(18,2) column it becomes $11.04 the moment it is stored, losing $0.0015 per unit; on a 5,000-unit order that is $7.50 of drift on a single line, and a real invoice has forty lines.

So we keep four decimals on unit prices and extended amounts, and round to two only when we print an invoice or post to the general ledger. The rounding rule is PostgreSQL's default for numeric, which the manual describes as rounding "ties away from zero" (2.5 becomes 3, not 2), and we apply it in exactly one place: the invoice total.

timestamptz for instants, DATE for calendar days

There are two different kinds of time in an ERP and confusing them causes real bugs. An instant is a moment on the physical timeline: when the webhook arrived, when the row was written, when the carton was scanned. Instants use timestamptz (timestamp with time zone).

Despite the name, timestamptz does not store a time zone; it stores a UTC instant and converts to the session's time zone on the way out. That is exactly what you want: a warehouse in Los Angeles and a factory in Ho Chi Minh City both write now() and both mean the same physical instant.

A calendar date is a label on a calendar with no time and no zone: the ship date a buyer wrote on their purchase order, an invoice due date, a season start. These use date.

If you store a due date as timestamptz, then "due 2026-03-01" becomes midnight UTC, which is 2026-02-28 16:00 in Los Angeles, and your aging report, the standard finance report that buckets unpaid invoices by how many days late they are, files the invoice in the wrong bucket for eight hours of every day.

The rule: if a human wrote it on a form, it is a date; if a machine observed it, it is a timestamptz. Never use bare timestamp (without time zone) — it is an instant with the zone information thrown away, which is the worst of both.

Enums: PostgreSQL types vs CHECK constraints

A status column needs a closed vocabulary. PostgreSQL offers two ways to enforce one, and they trade off differently.

A native enum type (create type order_status as enum ('draft','submitted',...)) is stored as a 4-byte OID, sorts in declaration order, and shows up in the catalog so code generators and ORMs can read the allowed values automatically.

Its weakness is migration: alter type ... add value can only add values (it has been able to slot a new one before or after an existing one since PostgreSQL 9.1), you cannot rename or remove a value without recreating the type and rewriting every dependent column, and before PostgreSQL 12 add value could not run inside a transaction block at all, so a migration that died halfway left the type changed and the tables not.

Even on 12 and later, the release notes are explicit that the new value cannot be referenced "until after it is committed".

A CHECK constraint on a text column (status text not null check (status in ('draft','submitted'))) stores the literal string, sorts alphabetically (so you add an explicit sort_order or an order by case when you need workflow order), and costs a few extra bytes per row. But changing the vocabulary is a plain alter table ... drop constraint / add constraint ... not valid, fully transactional, and you can add the new constraint as not valid to skip the table scan and validate it later during a quiet window.

Why this schema picks text plus CHECK

This schema uses text + CHECK for every status column, because status vocabularies change constantly in a young ERP (you will add on_hold to orders in month four, guaranteed) and because a text value that appears verbatim in JSON APIs, logs, and CSV exports is easier for a beginner to debug. We keep exactly one native enum, app.tenant_plan, to demonstrate the syntax and because billing plans genuinely never change without a code deploy anyway.

One consequence to plan for: your TypeScript will also have a union type 'draft' | 'submitted' | …, and you must not maintain it by hand. Generate it from the database catalog — select pg_get_constraintdef(oid) from pg_constraint where conname = 'orders_status_chk' gives you the exact list at build time. One source of truth, checked by CI, and the day someone adds a state in a migration without updating the frontend, the build breaks instead of production.

The prelude: extensions, roles, and helper functions

Every migration in this chapter assumes the following file has already run. It installs three extensions (optional add-on packages of types, functions, and index support that ship with PostgreSQL), makes sure the two database roles exist (a role is a login or a group of permissions; Supabase creates these two for you and a local PostgreSQL does not), creates the app schema (a namespace, like a folder for database objects) where our helper functions live, and defines the functions that RLS policies and triggers call.

Two terms show up in the comments below and are worth knowing before you read them. A GUC is a PostgreSQL configuration setting; set local app.tenant_id = '...' sets one for the duration of one transaction, and current_setting() reads it back. A JWT (JSON Web Token) is the small signed token a logged-in user's browser sends with every request; Supabase decodes it and makes its contents readable as request.jwt.claims. Between them they answer the only question RLS ever asks: who is calling? Read the comments carefully — app.current_tenant_id() is the most-called function in the entire system.

-- =====================================================================
-- 0001_prelude.sql   extensions, roles, helper schema, helper functions
-- =====================================================================

-- pgcrypto: gen_random_uuid(). Built in since PG 13, but installing it
-- explicitly keeps the migration portable to older servers.
create extension if not exists pgcrypto;

-- btree_gist: lets a GiST index mix equality columns (uuid, text) with
-- range columns, which is what our "no overlapping price periods"
-- exclusion constraints need.
create extension if not exists btree_gist;

-- pg_trgm: trigram indexes for fuzzy search on style names and SKUs.
-- A trigram is any three-character slice of a word ("har", "arb", "rbo"
-- ...), and indexing those slices is what makes a "contains" search fast.
create extension if not exists pg_trgm;

-- Helper schema. Keeping functions out of `public` means an accidental
-- `drop schema public cascade` in a sandbox does not take the security
-- functions with it, and it keeps the table list readable.
create schema if not exists app;
grant usage on schema app to public;

-- Supabase ships these roles; plain PostgreSQL does not. Create them if
-- missing so this schema runs identically on a laptop and in production.
do $$
begin
  if not exists (select 1 from pg_roles where rolname = 'authenticated') then
    create role authenticated nologin;
  end if;
  if not exists (select 1 from pg_roles where rolname = 'service_role') then
    create role service_role nologin;
  end if;
end
$$;

grant usage on schema public to authenticated, service_role;

-- The one native enum in the schema (see the enum discussion above).
create type app.tenant_plan as enum ('trial', 'standard', 'enterprise');

-- Domains: a named type with a built-in CHECK. Every column declared
-- app.currency_code is guaranteed to be three uppercase letters, and we
-- wrote the rule once instead of sixty times.
create domain app.currency_code as char(3)
  constraint currency_code_iso4217 check (value ~ '^[A-Z]{3}$');

create domain app.country_code as char(2)
  constraint country_code_iso3166 check (value ~ '^[A-Z]{2}$');

-- ---------------------------------------------------------------------
-- Who is asking? Two functions, called by every RLS policy in the file.
-- ---------------------------------------------------------------------

-- Reads the current tenant from either a session GUC (set by our own
-- server-side connection pool: `set local app.tenant_id = '...'`) or from
-- the Supabase JWT claims. Returns NULL when neither is set, and NULL
-- never equals anything, so a policy of `tenant_id = app.current_tenant_id()`
-- returns zero rows for an unauthenticated caller. Fail closed.
create or replace function app.current_tenant_id()
returns uuid
language sql
stable
set search_path = public, pg_temp
as $$
  select coalesce(
           nullif(current_setting('app.tenant_id', true), ''),
           nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'tenant_id'
         )::uuid;
$$;

create or replace function app.current_user_id()
returns uuid
language sql
stable
set search_path = public, pg_temp
as $$
  select coalesce(
           nullif(current_setting('app.user_id', true), ''),
           nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'sub'
         )::uuid;
$$;

-- ---------------------------------------------------------------------
-- Role check for RBAC -- role-based access control, where permissions
-- attach to named roles and people are given roles (chapter 6).
-- SECURITY DEFINER is essential here: this
-- function reads `memberships` and `roles`, which are themselves
-- RLS-protected. If a policy on table X calls a plain function that
-- selects from a table whose policy selects from X, PostgreSQL raises
-- "infinite recursion detected in policy". SECURITY DEFINER runs as the
-- function owner, whose queries skip those policies, breaking the cycle.
-- `set search_path` is mandatory on SECURITY DEFINER functions: without
-- it, a caller can prepend a schema to search_path and hijack the
-- function's table references.
-- ---------------------------------------------------------------------
create or replace function app.has_role(required_roles text[])
returns boolean
language sql
stable
security definer
set search_path = public, pg_temp
as $$
  select exists (
    select 1
    from public.memberships m
    join public.membership_roles mr on mr.membership_id = m.id
    join public.roles r on r.id = mr.role_id
    where m.tenant_id = app.current_tenant_id()
      and m.user_id   = app.current_user_id()
      and m.status    = 'active'
      and r.key       = any (required_roles)
  );
$$;

-- Does the caller share a tenant with this user? Used by the users policy.
create or replace function app.shares_tenant_with(other_user_id uuid)
returns boolean
language sql
stable
security definer
set search_path = public, pg_temp
as $$
  select exists (
    select 1
    from public.memberships m
    where m.user_id = other_user_id
      and m.tenant_id = app.current_tenant_id()
      and m.status in ('invited', 'active')
  );
$$;

-- ---------------------------------------------------------------------
-- Trigger functions
-- ---------------------------------------------------------------------

-- Keeps updated_at honest. Never trust the application to set it: a
-- forgotten assignment in one code path makes every cache-invalidation
-- and sync-since-timestamp query silently wrong.
create or replace function app.touch_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at := now();
  return new;
end;
$$;

-- Hard stop for append-only tables. RLS alone is not enough: the
-- service_role connection your background jobs use can be granted
-- BYPASSRLS, and such a role ignores every policy on every table. A
-- trigger still fires for it. The only ways past this one are to drop
-- the trigger or to set session_replication_role = replica, and both
-- need elevated privileges and both leave a trail.
create or replace function app.deny_mutation()
returns trigger
language plpgsql
as $$
begin
  raise exception 'table %.% is append-only; % is not permitted',
    tg_table_schema, tg_table_name, tg_op
    using errcode = 'restrict_violation';
  return null;
end;
$$;

-- ---------------------------------------------------------------------
-- The standard tenant policy, as a function so we write it once.
-- Calling `select app.enable_tenant_rls('public.orders');` is exactly
-- equivalent to writing:
--
--   alter table public.orders enable row level security;
--   alter table public.orders force  row level security;
--   create policy orders_tenant_isolation on public.orders
--     for all to authenticated
--     using      (tenant_id = app.current_tenant_id())
--     with check (tenant_id = app.current_tenant_id());
--   grant select, insert, update, delete on public.orders to authenticated;
--
-- USING filters rows you can see and modify; WITH CHECK validates rows
-- you write. You need both: USING alone lets a caller INSERT a row into
-- another tenant (they just cannot read it back afterwards).
-- FORCE makes the policy apply to the table's owner too, which is the
-- role your migrations run as.
-- ---------------------------------------------------------------------
create or replace function app.enable_tenant_rls(target regclass)
returns void
language plpgsql
security definer
set search_path = public, pg_temp
as $fn$
declare
  policy_name text;
begin
  select c.relname || '_tenant_isolation'
    into policy_name
    from pg_class c
   where c.oid = target;

  execute format('alter table %s enable row level security', target);
  execute format('alter table %s force row level security', target);
  execute format('drop policy if exists %I on %s', policy_name, target);
  execute format(
    'create policy %I on %s for all to authenticated '
    'using (tenant_id = app.current_tenant_id()) '
    'with check (tenant_id = app.current_tenant_id())',
    policy_name, target);
  execute format(
    'grant select, insert, update, delete on %s to authenticated', target);
end;
$fn$;

In plain English: this file installs three add-ons, creates the two login roles if they are missing, and then defines six small functions:

• Two of them answer "who is calling?" by reading either a setting your own server put on the connection or a claim inside the caller's login token; both return NULL when neither is present, which makes every policy fail closed.
• One answers "does the caller hold any of these roles?".
• Two are trigger functions: one stamps updated_at on every write, the other refuses updates and deletes outright.
• The last, app.enable_tenant_rls, writes the standard tenant policy for a table, so you never type it sixty times.

Almost every table section below ends with a single call to it.

A related subtlety, worth learning now rather than during an outage: PostgreSQL's default foreign-key matching is MATCH SIMPLE, which means that if any column of the key is NULL, the constraint is satisfied without looking anything up.

Since tenant_id is never NULL, this gives us exactly the behavior we want for optional parents — styles.season_id may be NULL and the row is fine, but if it is set, it must point at a season in the same tenant. If you ever want "all or nothing" semantics on a multi-column key with several nullable parts, spell out match full.

11.1 Tenancy and identity

This section is the foundation everything else stands on, and it is the section to get right on day one, because retrofitting tenancy into a live schema means rewriting every table and every query. Eight tables:

• tenants (the businesses using your ERP)
• users (the humans)
• memberships (which humans belong to which businesses)
• roles and membership_roles (what they are allowed to do)
• sessions and api_keys (how they prove who they are)
• and audit_log (what they did)

                          +-----------+
                          |  tenants  |  the root of every tenant-scoped FK
                          +-----------+
                            |   |   |
        +-------------------+   |   +------------------------+
        |                       |                            |
        v                       v                            v
  +-------------+        +-------------+              +-------------+
  | memberships |        |    roles    |              |  api_keys   |
  +-------------+        +-------------+              +-------------+
        |  \                   /                            |
        |   \                 /                             |
        |    v               v                              |
        |  +---------------------+                          |
        |  |  membership_roles   |  (M:N join)              |
        |  +---------------------+                          |
        |                                                   |
        v                                                   v
  +-------------+                                    +-------------+
  |    users    |<--- sessions.user_id ------------- |  audit_log  |
  +-------------+     (a login on one tenant)        +-------------+
   global identity;                                   append-only,
   no tenant_id                                        tenant-scoped

  Read it as: one tenant has many memberships; one user has many
  memberships (one per company they work with); a membership is granted
  many roles through the membership_roles join table.

tenants

One row per business that uses your ERP — one apparel brand, one wholesaler. A row is created exactly once, by the signup flow, and is never deleted in normal operation (a closed account gets status = 'closed' and its data is exported then purged by a deliberate admin job).

This is the only table whose id is the tenant id, so it has no separate tenant_id column; every other tenant-scoped table's tenant_id points here. The slug is the short URL-safe handle (acme-apparel) used in subdomains and file paths, and it is globally unique across all tenants.

base_currency is the currency the tenant's financial reports are expressed in; individual orders may be in other currencies. fiscal_year_start_month matters because apparel and retail businesses commonly run a February–January fiscal year, following the retail 4-5-4 calendar published by the National Retail Federation, whose year starts in late January or early February. Every "year to date" report depends on getting this right.

settings is a JSONB bag for genuinely per-tenant toggles that do not deserve columns (feature flags, default label formats); resist putting anything you need to query or constrain in there.

create table public.tenants (
  id                      uuid primary key default gen_random_uuid(),
  slug                    text not null,
  legal_name              text not null,
  display_name            text not null,
  plan                    app.tenant_plan not null default 'trial',
  status                  text not null default 'active',
  base_currency           app.currency_code not null default 'USD',
  home_country            app.country_code not null default 'US',
  time_zone               text not null default 'UTC',
  fiscal_year_start_month smallint not null default 1,
  settings                jsonb not null default '{}'::jsonb,
  created_at              timestamptz not null default now(),
  updated_at              timestamptz not null default now(),
  closed_at               timestamptz,

  constraint tenants_slug_uk unique (slug),
  constraint tenants_slug_chk
    check (slug ~ '^[a-z0-9][a-z0-9-]{1,38}[a-z0-9]$'),
  constraint tenants_status_chk
    check (status in ('active', 'suspended', 'closed')),
  constraint tenants_fiscal_month_chk
    check (fiscal_year_start_month between 1 and 12),
  constraint tenants_closed_chk
    check ((status = 'closed') = (closed_at is not null))
);

comment on table public.tenants is
  'One row per customer business. tenants.id is the tenant_id used everywhere else.';

-- The slug unique constraint already builds the index we need for
-- subdomain lookup (`where slug = 'acme-apparel'`), so no extra index.

alter table public.tenants enable row level security;
alter table public.tenants force  row level security;

-- Note the shape: this table's own id is the tenant key, so the policy
-- compares `id`, not `tenant_id`.
create policy tenants_self_isolation on public.tenants
  for all to authenticated
  using      (id = app.current_tenant_id())
  with check (id = app.current_tenant_id());

grant select, update on public.tenants to authenticated;

Constraint by constraint:

• tenants_slug_chk prevents slugs that would break URLs or collide with reserved routes (no leading/trailing hyphen, 3–40 characters, lowercase alphanumerics and hyphens only).
• tenants_status_chk is the enum-by-CHECK pattern.
• tenants_closed_chk is a correlation check: it forces closed_at to be set if and only if the status is closed, so you can never end up with a closed tenant with no closure timestamp, or a live tenant that some code path marked as closed a year ago.

Correlation checks like this one cost a line of SQL and rule out a whole class of bug, and you should write one every time two columns are supposed to agree.

users

One row per human being, globally — not per human per tenant. This is the second and last table without a tenant_id, and the reason is that the same person genuinely can work for two of your customers: a freelance production manager, a 3PL operator (3PL means third-party logistics — an outside company that stores and ships your goods for you), a consultant. Give them one identity and two memberships.

A row is created when someone accepts an invitation or self-registers; auth_user_id links to Supabase's auth.users row so the JWT's sub claim resolves to our profile. We keep email unique and lowercase, because case-sensitive emails cause duplicate accounts.

Note there is no password column: authentication lives in Supabase Auth (or your identity provider), and this table stores only profile data. The RLS policy is unusual and worth studying — you may read a user row if it is your own, or if you and that user share a tenant.

create table public.users (
  id            uuid primary key default gen_random_uuid(),
  auth_user_id  uuid,
  email         text not null,
  full_name     text,
  avatar_url    text,
  locale        text not null default 'en-US',
  time_zone     text not null default 'UTC',
  status        text not null default 'active',
  last_seen_at  timestamptz,
  created_at    timestamptz not null default now(),
  updated_at    timestamptz not null default now(),

  constraint users_email_uk unique (email),
  constraint users_auth_user_uk unique (auth_user_id),
  constraint users_email_chk
    check (email = lower(email) and position('@' in email) > 1),
  constraint users_status_chk
    check (status in ('active', 'disabled'))
);

alter table public.users enable row level security;
alter table public.users force  row level security;

-- You can always see yourself.
create policy users_self_select on public.users
  for select to authenticated
  using (id = app.current_user_id());

-- You can see colleagues in the tenant you are currently acting in.
-- app.shares_tenant_with() is SECURITY DEFINER so this policy does not
-- re-enter the memberships policy and recurse.
create policy users_colleague_select on public.users
  for select to authenticated
  using (app.shares_tenant_with(id));

-- You may only edit your own profile.
create policy users_self_update on public.users
  for update to authenticated
  using      (id = app.current_user_id())
  with check (id = app.current_user_id());

grant select, update on public.users to authenticated;

Indexes: the two unique constraints give us lookup by email (the login path) and by auth_user_id (the every-request path, since the JWT carries the auth id). Nothing else is needed; this table stays small.

Multiple select policies on one table are combined with OR — a row is visible if any policy permits it. That is why users_self_select and users_colleague_select are separate policies rather than one policy with an or in it: they read more clearly and you can drop one without rewriting the other. Restrictive policies (as restrictive) combine with AND instead, and are the tool for "and additionally, never in a suspended tenant" rules.

memberships

The join between a user and a tenant: "Dana works at Acme Apparel." A row is created when an owner or admin invites someone (status invited, accepted_at null) and flips to active when the invitation is accepted. Removing someone sets status = 'removed' and removed_at rather than deleting the row, because the audit log and every created_by column still need to resolve that person's name. is_owner is a separate flag rather than a role because ownership has database-level consequences (you cannot remove the last owner) and because it must be checkable without a join in an emergency.

create table public.memberships (
  id           uuid primary key default gen_random_uuid(),
  tenant_id    uuid not null references public.tenants (id) on delete cascade,
  user_id      uuid not null references public.users (id)   on delete cascade,
  status       text not null default 'invited',
  is_owner     boolean not null default false,
  title        text,
  invited_by   uuid references public.users (id) on delete set null,
  invited_at   timestamptz not null default now(),
  accepted_at  timestamptz,
  removed_at   timestamptz,
  created_at   timestamptz not null default now(),
  updated_at   timestamptz not null default now(),

  constraint memberships_tenant_row_uk unique (tenant_id, id),
  constraint memberships_tenant_user_uk unique (tenant_id, user_id),
  constraint memberships_status_chk
    check (status in ('invited', 'active', 'suspended', 'removed')),
  constraint memberships_accepted_chk
    check (status <> 'active' or accepted_at is not null),
  constraint memberships_removed_chk
    check ((status = 'removed') = (removed_at is not null))
);

-- Every request resolves "which tenants can this user act in?", so the
-- user-first index is the hot path. The tenant-first direction is
-- already covered by memberships_tenant_user_uk.
create index memberships_user_idx
  on public.memberships (user_id, status);

select app.enable_tenant_rls('public.memberships');

memberships_tenant_user_uk prevents the same person being added to the same company twice, which is the bug that produces duplicated permissions and doubled invitation emails. memberships_tenant_row_uk is the composite-key target that lets membership_roles reference this row in a tenant-safe way.

roles

A named bundle of permissions, scoped to a tenant so a customer can define "Warehouse Lead" without affecting anyone else. Rows are created twice: five system roles are seeded automatically the moment a tenant is created (see 11.12), and custom roles are created by tenant admins later. is_system marks the seeded ones so the UI can prevent deleting them.

permissions is a text array of permission keys like orders.approve or inventory.adjust; chapter 6 covered how the API layer checks them. An array is the right shape here because the whole set is always read together, never joined against, and rarely more than fifty entries.

create table public.roles (
  id           uuid primary key default gen_random_uuid(),
  tenant_id    uuid not null references public.tenants (id) on delete cascade,
  key          text not null,
  name         text not null,
  description  text,
  is_system    boolean not null default false,
  permissions  text[] not null default '{}',
  created_at   timestamptz not null default now(),
  updated_at   timestamptz not null default now(),

  constraint roles_tenant_row_uk unique (tenant_id, id),
  constraint roles_tenant_key_uk unique (tenant_id, key),
  constraint roles_key_chk check (key ~ '^[a-z][a-z0-9_]{1,30}$'),
  constraint roles_permissions_chk
    check (array_position(permissions, null) is null)
);

-- GIN index so "which roles grant orders.approve?" is an index scan
-- rather than a sequential scan over every role in the database.
create index roles_permissions_gin
  on public.roles using gin (permissions);

select app.enable_tenant_rls('public.roles');

roles_permissions_chk uses array_position(permissions, null) is null to reject arrays containing a NULL element — a NULL permission would silently match nothing and is always a bug in the caller. roles_key_chk keeps keys machine-safe so they can appear in code and config without quoting.

membership_roles

The many-to-many join: this membership has this role. Rows are created when an admin assigns a role and deleted when they unassign it — this is one of the few tables where hard deletes are correct, because the audit log records the change and the row itself carries no history. Note that both foreign keys are composite and include tenant_id, so it is impossible to grant a role from tenant B to a membership in tenant A even with RLS disabled.

create table public.membership_roles (
  id             uuid primary key default gen_random_uuid(),
  tenant_id      uuid not null references public.tenants (id) on delete cascade,
  membership_id  uuid not null,
  role_id        uuid not null,
  granted_by     uuid references public.users (id) on delete set null,
  created_at     timestamptz not null default now(),
  updated_at     timestamptz not null default now(),

  constraint membership_roles_tenant_row_uk unique (tenant_id, id),
  constraint membership_roles_pair_uk
    unique (tenant_id, membership_id, role_id),
  constraint membership_roles_membership_fk
    foreign key (tenant_id, membership_id)
    references public.memberships (tenant_id, id) on delete cascade,
  constraint membership_roles_role_fk
    foreign key (tenant_id, role_id)
    references public.roles (tenant_id, id) on delete restrict
);

-- Reverse lookup: "who has the finance role?" for an admin screen, and
-- to make `on delete restrict` on roles fast.
create index membership_roles_role_idx
  on public.membership_roles (tenant_id, role_id);

select app.enable_tenant_rls('public.membership_roles');

The role FK is restrict, not cascade: deleting a role that people still hold should fail loudly and make the admin reassign those people first, rather than silently stripping permissions from a dozen accounts. The membership FK is cascade, because removing a membership genuinely should remove its role grants.

sessions

One row per active browser login, scoped to the tenant the user selected. A row is created at login (or at tenant switch, which mints a new session) and is either revoked at logout or expires. We never store the session token itself, only token_hash, a SHA-256 of the token as bytea: if the database leaks, the hashes are useless for impersonation. Sessions are one of the few tables you will genuinely want to prune — a daily job deletes rows where expires_at < now() - interval '30 days'.

create table public.sessions (
  id            uuid primary key default gen_random_uuid(),
  tenant_id     uuid not null references public.tenants (id) on delete cascade,
  user_id       uuid not null references public.users (id)   on delete cascade,
  token_hash    bytea not null,
  issued_at     timestamptz not null default now(),
  expires_at    timestamptz not null,
  last_used_at  timestamptz,
  revoked_at    timestamptz,
  revoke_reason text,
  ip_address    inet,
  user_agent    text,
  created_at    timestamptz not null default now(),
  updated_at    timestamptz not null default now(),

  constraint sessions_tenant_row_uk unique (tenant_id, id),
  constraint sessions_token_uk unique (token_hash),
  constraint sessions_expiry_chk check (expires_at > issued_at),
  constraint sessions_token_len_chk check (octet_length(token_hash) = 32)
);

-- "Show me my active sessions" and the logout-everywhere path.
create index sessions_user_active_idx
  on public.sessions (tenant_id, user_id)
  where revoked_at is null;

-- Supports the nightly prune job without scanning the whole table.
create index sessions_expiry_idx on public.sessions (expires_at);

select app.enable_tenant_rls('public.sessions');

sessions_token_len_chk asserts the hash is exactly 32 bytes, which catches the classic bug of storing the raw token (variable length, and a catastrophic leak) instead of its digest. The partial index where revoked_at is null indexes only live sessions, which is a tiny fraction of the table after a year — partial indexes are the standard tool whenever your queries only ever ask about a minority of rows.

api_keys

Machine credentials: a 3PL's integration, a customer's ordering script, your own background workers. Created by an admin in settings; the plaintext key is shown exactly once and never again, because we store only the hash.

prefix is the first few characters of the key, stored in the clear so the UI can display ak_9f2c… next to each entry and so support can identify which key a caller used from a log line. scopes narrows what the key can do, and is checked in the API layer the same way role permissions are. Keys are revoked, never deleted, because the audit log references them.

create table public.api_keys (
  id            uuid primary key default gen_random_uuid(),
  tenant_id     uuid not null references public.tenants (id) on delete cascade,
  name          text not null,
  prefix        text not null,
  key_hash      bytea not null,
  scopes        text[] not null default '{}',
  created_by    uuid references public.users (id) on delete set null,
  last_used_at  timestamptz,
  expires_at    timestamptz,
  revoked_at    timestamptz,
  created_at    timestamptz not null default now(),
  updated_at    timestamptz not null default now(),

  constraint api_keys_tenant_row_uk unique (tenant_id, id),
  constraint api_keys_prefix_uk unique (prefix),
  constraint api_keys_hash_uk unique (key_hash),
  constraint api_keys_prefix_chk check (prefix ~ '^ak_[0-9a-f]{8}$'),
  constraint api_keys_hash_len_chk check (octet_length(key_hash) = 32)
);

-- Authentication looks the key up by prefix, then verifies the hash.
-- api_keys_prefix_uk already provides that index.

create index api_keys_active_idx
  on public.api_keys (tenant_id)
  where revoked_at is null;

select app.enable_tenant_rls('public.api_keys');

In plain English: an API key is stored the way a password should be. The full key is shown to the admin once and then thrown away; the database keeps only a 32-byte hash of it plus a short readable prefix. When a machine calls your API you find the row by prefix, then check the hash. The partial index on live keys keeps the settings screen fast even after years of revoked keys have piled up.

audit_log

Append-only history of who changed what. A row is written by the application (or by a trigger) on every state-changing operation: an order confirmed, a price changed, a user's role granted, an inventory adjustment posted.

This table is the answer to "why does this order say 4,000 units when the buyer swears they ordered 400?", and the first time you need it, it settles an argument that would otherwise be one person's memory against another's.

It carries created_at but no updated_at — an updated_at column on an immutable row is a lie, and the deny-mutation trigger makes updates impossible anyway. The seq column is a monotonically increasing bigint, which gives you a total order to page through even when two events share a microsecond timestamp.

create table public.audit_log (
  id                uuid primary key default gen_random_uuid(),
  tenant_id         uuid not null references public.tenants (id) on delete cascade,
  seq               bigint generated always as identity,
  occurred_at       timestamptz not null default now(),
  actor_kind        text not null default 'user',
  actor_user_id     uuid references public.users (id) on delete set null,
  actor_api_key_id  uuid,
  action            text not null,
  entity_table      text not null,
  entity_id         uuid,
  before_data       jsonb,
  after_data        jsonb,
  request_id        uuid,
  ip_address        inet,
  created_at        timestamptz not null default now(),

  constraint audit_log_tenant_row_uk unique (tenant_id, id),
  constraint audit_log_seq_uk unique (seq),
  constraint audit_log_actor_kind_chk
    check (actor_kind in ('user', 'api_key', 'system', 'integration')),
  constraint audit_log_action_chk check (action ~ '^[a-z_]+\.[a-z_]+$'),
  constraint audit_log_actor_chk
    check (
      (actor_kind = 'user'    and actor_user_id    is not null) or
      (actor_kind = 'api_key' and actor_api_key_id is not null) or
      (actor_kind in ('system', 'integration'))
    ),
  constraint audit_log_api_key_fk
    foreign key (tenant_id, actor_api_key_id)
    references public.api_keys (tenant_id, id) on delete restrict
);

-- The three questions people actually ask of an audit log:
-- 1. "What happened to THIS order?"
create index audit_log_entity_idx
  on public.audit_log (tenant_id, entity_table, entity_id, occurred_at desc);
-- 2. "What did THIS person do?"
create index audit_log_actor_idx
  on public.audit_log (tenant_id, actor_user_id, occurred_at desc);
-- 3. "What happened in the last hour?" (also the paging cursor)
create index audit_log_recent_idx
  on public.audit_log (tenant_id, occurred_at desc);
-- Free-text drill-down into the payload, e.g. after_data @> '{"status":"cancelled"}'
create index audit_log_after_gin
  on public.audit_log using gin (after_data jsonb_path_ops);

alter table public.audit_log enable row level security;
alter table public.audit_log force  row level security;

-- Read your own tenant's history...
create policy audit_log_tenant_select on public.audit_log
  for select to authenticated
  using (tenant_id = app.current_tenant_id());

-- ...and append to it. There is deliberately NO update or delete policy:
-- under RLS, an operation with no permissive policy is denied.
create policy audit_log_tenant_insert on public.audit_log
  for insert to authenticated
  with check (tenant_id = app.current_tenant_id());

grant select, insert on public.audit_log to authenticated;

-- Belt and braces: even a BYPASSRLS superuser session cannot rewrite it
-- without first disabling this trigger, which is itself an audited act.
create trigger audit_log_append_only
  before update or delete on public.audit_log
  for each row execute function app.deny_mutation();

audit_log_actor_chk is a discriminated-union check: it enforces that a row claiming a user actor actually names one, and likewise for API keys, so you can never log "someone changed this" with no idea who.

The jsonb_path_ops GIN index is a smaller, faster variant of the default JSONB index. It drops the key-exists operators (?, ?|, ?&) and keeps containment (@>) plus the two jsonpath match operators, and containment is all we ever run against an audit payload. The manual notes that such an index "is usually much smaller" than the default one over the same data.

11.2 Catalog

The catalog answers "what do we sell?" and in apparel that question has three axes, which is why it takes nine tables instead of one. A style is the design (the Harbor Crew Sweatshirt). A colorway is that design in one color (Harbor Crew in Ecru). A size comes from a size scale (XS–XXL, or 28–40 waist).

The cross product of style × colorway × size is a variant, and the variant is the thing that actually has a SKU, a barcode, a price, and a number in a warehouse. (SKU stands for stock keeping unit: the exact item a picker can hold, count, and put in a box. "Harbor Crew Sweatshirt" is not a SKU; "Harbor Crew, Ecru, Medium" is.)

Everything downstream — every order line, every inventory transaction, every carton — points at a variant. Nothing downstream points at a style.

  +----------+        +--------------+
  | seasons  |        | size_scales  |
  +----------+        +--------------+
       |                     |
       | (optional)          | 1:N
       v                     v
  +----------+          +---------+
  |  styles  |----------|  sizes  |
  +----------+  scale   +---------+
     |     |                  |
 1:N |     +--------+         |
     v              |         |
 +------------+     |         |
 | colorways  |     |         |
 +------------+     |         |
     |              |         |
     +------+   +---+     +---+
            v   v         v
          +------------------+        +-----------+
          |     variants     |---1:N--| barcodes  |
          |   (the SKU row)  |        +-----------+
          +------------------+
                  |
                  | 1:N
                  v
        +-------------------+       +--------------+
        | price_list_items  |--N:1--| price_lists  |
        +-------------------+       +--------------+

  variants carries THREE composite foreign keys, and together they make
  a mismatched SKU impossible: the colorway must belong to the style, the
  size must belong to the style's size scale, and all of it must belong
  to the same tenant. There is no trigger and no application check doing
  this work -- it is pure key design.

seasons

Apparel is organized by season: Spring/Summer 2027, Holiday 2026. A season groups styles for merchandising, drives which linesheet a buyer sees (a linesheet is the catalog page a wholesale buyer orders from), and bounds sell-through reporting (sell-through is the share of what you bought in that has since gone out; 11.10 computes it). Rows are created by a merchandiser at the start of a development cycle, roughly two to four times a year, and are archived rather than deleted once the season is shipped and reported on.

create table public.seasons (
  id           uuid primary key default gen_random_uuid(),
  tenant_id    uuid not null references public.tenants (id) on delete cascade,
  code         text not null,          -- 'SS27', 'HOL26' -- appears on line sheets
  name         text not null,          -- 'Spring/Summer 2027'
  starts_on    date not null,          -- first ship date of the season
  ends_on      date not null,          -- last ship date; bounds sell-through
  is_archived  boolean not null default false,
  created_at   timestamptz not null default now(),
  updated_at   timestamptz not null default now(),

  constraint seasons_tenant_row_uk unique (tenant_id, id),
  constraint seasons_tenant_code_uk unique (tenant_id, code),
  constraint seasons_code_chk check (code ~ '^[A-Z0-9]{2,12}$'),
  constraint seasons_range_chk check (ends_on >= starts_on)
);

-- Season pickers list live seasons in date order; this index serves both
-- the filter and the sort without a sort node in the plan.
create index seasons_active_idx
  on public.seasons (tenant_id, starts_on desc)
  where not is_archived;

select app.enable_tenant_rls('public.seasons');

In plain English: a season has a short code, a display name, and a start and end date, and the range check refuses a season that ends before it begins. The index covers only seasons that have not been archived and stores them newest first, which is exactly what a season dropdown needs.

size_scales

A named, ordered list of sizes. Apparel companies run several: alpha (XS–XXL) for knits, numeric (0–16) for dresses, waist×inseam for denim, one-size for accessories. The scale exists as its own table so that "S comes before M" is stored once, not repeated on every style. Rows are created during onboarding and rarely change afterward.

create table public.size_scales (
  id           uuid primary key default gen_random_uuid(),
  tenant_id    uuid not null references public.tenants (id) on delete cascade,
  code         text not null,      -- 'ALPHA', 'NUM-MISSY', 'ONESIZE'
  name         text not null,      -- 'Alpha XS-XXL'
  description  text,
  is_active    boolean not null default true,
  created_at   timestamptz not null default now(),
  updated_at   timestamptz not null default now(),

  constraint size_scales_tenant_row_uk unique (tenant_id, id),
  constraint size_scales_tenant_code_uk unique (tenant_id, code)
);

select app.enable_tenant_rls('public.size_scales');

In plain English: this table is deliberately tiny. It gives each size run a code and a name so that individual sizes have something to belong to. The sizes themselves, and the order they run in, live in the next table.

sizes

One row per size within a scale, carrying the only thing that is genuinely hard about sizes: their order. sort_order is what makes a size run print as XS, S, M, L, XL rather than alphabetically as L, M, S, XL, XS, and getting it wrong is one of the most common complaints leveled at apparel systems. The extra unique (tenant_id, size_scale_id, id) exists purely so variants can prove, by foreign key, that a size belongs to the right scale.

create table public.sizes (
  id             uuid primary key default gen_random_uuid(),
  tenant_id      uuid not null references public.tenants (id) on delete cascade,
  size_scale_id  uuid not null,
  code           text not null,       -- 'S', '32X34' -- goes in the SKU
  label          text not null,       -- 'Small' -- shown to humans
  sort_order     smallint not null,   -- 0-based position in the size run
  is_active      boolean not null default true,
  created_at     timestamptz not null default now(),
  updated_at     timestamptz not null default now(),

  constraint sizes_tenant_row_uk unique (tenant_id, id),
  -- Wider key: the FK target that lets variants prove scale membership.
  constraint sizes_scale_row_uk unique (tenant_id, size_scale_id, id),
  constraint sizes_scale_code_uk unique (tenant_id, size_scale_id, code),
  -- DEFERRABLE so a drag-and-drop reorder can renumber every size in one
  -- transaction without tripping the constraint half way through: the app
  -- runs `set constraints sizes_scale_order_uk deferred;` first.
  constraint sizes_scale_order_uk unique (tenant_id, size_scale_id, sort_order)
    deferrable initially immediate,
  constraint sizes_sort_chk check (sort_order >= 0),
  constraint sizes_scale_fk
    foreign key (tenant_id, size_scale_id)
    references public.size_scales (tenant_id, id) on delete cascade
);

select app.enable_tenant_rls('public.sizes');

In plain English: each size belongs to one scale, carries a short code for the SKU and a friendly label for people, and has a sort_order that fixes its place in the run. The three unique constraints do three different jobs: no repeated code inside a scale, no two sizes claiming the same position, and a wider key that variants will use later to prove a size really belongs to the scale it claims. The order constraint is deferrable so a drag-and-drop reorder can renumber the whole run inside one transaction.

styles

The design. A row is created when a designer or merchandiser opens a new style in development, long before it can be sold, which is why status starts at development. The style carries everything that is true of the garment regardless of color or size: its name, category, gender, fabric composition, country of origin, and HS code (the customs tariff classification, needed on every commercial invoice for an import). Default prices and cost live here so a new variant can inherit them, but the variant's own values win.

create table public.styles (
  id                       uuid primary key default gen_random_uuid(),
  tenant_id                uuid not null references public.tenants (id) on delete cascade,
  style_number             text not null,   -- 'W1042' -- the human key buyers quote
  name                     text not null,   -- 'Harbor Crew Sweatshirt'
  description              text,
  brand                    text,            -- for houses that run several labels
  category                 text not null default 'uncategorized', -- 'tops'
  subcategory              text,                                  -- 'sweatshirts'
  gender                   text not null default 'unisex',
  season_id                uuid,            -- optional: carryover styles have none
  size_scale_id            uuid not null,   -- which size run this style comes in
  status                   text not null default 'development',
  default_unit_cost        numeric(18,4),   -- FOB factory cost, seeds variants
  default_wholesale_price  numeric(18,4),   -- what a retailer pays
  default_retail_price     numeric(18,4),   -- MSRP (manufacturer's
                                           -- suggested retail price), the
                                           -- number printed on the ticket
  fabric_composition       text,            -- '80% cotton, 20% polyester'
  country_of_origin        app.country_code,
  hs_code                  text,            -- customs tariff code, 6-10 digits
  attributes               jsonb not null default '{}'::jsonb,
  created_by               uuid references public.users (id) on delete set null,
  created_at               timestamptz not null default now(),
  updated_at               timestamptz not null default now(),

  constraint styles_tenant_row_uk unique (tenant_id, id),
  -- Wider key so variants can assert "same style AND same size scale".
  constraint styles_scale_row_uk unique (tenant_id, id, size_scale_id),
  constraint styles_tenant_number_uk unique (tenant_id, style_number),
  constraint styles_number_chk check (style_number ~ '^[A-Z0-9][A-Z0-9._-]{1,31}$'),
  constraint styles_gender_chk
    check (gender in ('mens', 'womens', 'unisex', 'kids', 'infant')),
  constraint styles_status_chk
    check (status in ('development', 'active', 'discontinued', 'archived')),
  constraint styles_hs_code_chk check (hs_code is null or hs_code ~ '^[0-9]{6,10}$'),
  constraint styles_cost_chk check (default_unit_cost is null or default_unit_cost >= 0),
  constraint styles_wholesale_chk
    check (default_wholesale_price is null or default_wholesale_price >= 0),
  constraint styles_retail_chk
    check (default_retail_price is null or default_retail_price >= 0),
  constraint styles_season_fk
    foreign key (tenant_id, season_id)
    references public.seasons (tenant_id, id) on delete restrict,
  constraint styles_size_scale_fk
    foreign key (tenant_id, size_scale_id)
    references public.size_scales (tenant_id, id) on delete restrict
);

-- The merchandising grid: "show me active tops for SS27".
create index styles_browse_idx
  on public.styles (tenant_id, status, season_id, category);
-- Type-ahead search on name. gin_trgm_ops makes ILIKE '%harbor%' indexable.
create index styles_name_trgm
  on public.styles using gin (name gin_trgm_ops);
-- Foreign-key support: makes `on delete restrict` on seasons cheap.
create index styles_season_idx on public.styles (tenant_id, season_id);

select app.enable_tenant_rls('public.styles');

In plain English: everything that is true of a garment regardless of its color or size lives here — name, category, fabric, country of origin, customs code, and the default prices a new SKU inherits. The season is optional, because carryover styles belong to no season; the size scale is required, because every style comes in some size run. The three indexes serve the three ways people find a style: filtering the merchandising grid, typing part of a name, and following the season link.

colorways

One row per color a style is offered in. Created when the design is finalized for a season; discontinued rather than deleted, because last season's Ecru still appears on old orders and in inventory. code is the short color code that becomes part of the SKU, and it is unique per style, not per tenant, because "BLK" means black on every style.

create table public.colorways (
  id          uuid primary key default gen_random_uuid(),
  tenant_id   uuid not null references public.tenants (id) on delete cascade,
  style_id    uuid not null,
  code        text not null,     -- 'ECR' -- the middle segment of the SKU
  name        text not null,     -- 'Ecru'
  hex_swatch  text,              -- '#F2E8DC' for the UI colour chip
  status      text not null default 'active',
  sort_order  smallint not null default 0,
  created_at  timestamptz not null default now(),
  updated_at  timestamptz not null default now(),

  constraint colorways_tenant_row_uk unique (tenant_id, id),
  -- Wider key: proves to variants that this colorway belongs to that style.
  constraint colorways_style_row_uk unique (tenant_id, style_id, id),
  constraint colorways_style_code_uk unique (tenant_id, style_id, code),
  constraint colorways_code_chk check (code ~ '^[A-Z0-9]{2,8}$'),
  constraint colorways_hex_chk check (hex_swatch is null or hex_swatch ~ '^#[0-9A-Fa-f]{6}$'),
  constraint colorways_status_chk check (status in ('active', 'discontinued')),
  constraint colorways_style_fk
    foreign key (tenant_id, style_id)
    references public.styles (tenant_id, id) on delete cascade
);

select app.enable_tenant_rls('public.colorways');

In plain English: one row per color of one style. The code is unique per style rather than per tenant, so every style can have its own BLK. The hex swatch exists only to paint a color chip in the interface. Deleting a style cascades its colorways away with it, since a color with no style has nothing left to describe.

variants

The SKU: one specific style, in one color, in one size. This is the most-referenced table in the database — every order line, ledger entry, carton content, and price points here. A row is created when a style's color and size grid is generated, which in practice means one style produces twenty to sixty variants in one transaction. is_stocked distinguishes items you hold inventory for from made-to-order or drop-ship items; is_sellable lets you keep a variant in the catalog for receiving and returns while blocking new sales.

create table public.variants (
  id                 uuid primary key default gen_random_uuid(),
  tenant_id          uuid not null references public.tenants (id) on delete cascade,
  style_id           uuid not null,
  colorway_id        uuid not null,
  size_scale_id      uuid not null,   -- denormalised from styles, on purpose
  size_id            uuid not null,
  sku                text not null,   -- 'W1042-ECR-M' -- unique per tenant
  status             text not null default 'active',
  unit_cost          numeric(18,4),   -- standard cost, overrides style default
  wholesale_price    numeric(18,4),
  retail_price       numeric(18,4),
  weight_grams       integer,         -- for freight quoting and packing lists
  length_mm          integer,
  width_mm           integer,
  height_mm          integer,
  country_of_origin  app.country_code,
  hs_code            text,
  is_sellable        boolean not null default true,
  is_stocked         boolean not null default true,
  external_ids       jsonb not null default '{}'::jsonb,  -- {"shopify":"4451..."}
  created_at         timestamptz not null default now(),
  updated_at         timestamptz not null default now(),

  constraint variants_tenant_row_uk unique (tenant_id, id),
  constraint variants_tenant_sku_uk unique (tenant_id, sku),
  -- One row per point in the style x colour x size grid.
  constraint variants_grid_uk unique (tenant_id, style_id, colorway_id, size_id),
  constraint variants_sku_chk check (sku ~ '^[A-Z0-9][A-Z0-9._-]{2,63}$'),
  constraint variants_status_chk
    check (status in ('active', 'discontinued', 'archived')),
  constraint variants_cost_chk check (unit_cost is null or unit_cost >= 0),
  constraint variants_wholesale_chk
    check (wholesale_price is null or wholesale_price >= 0),
  constraint variants_retail_chk check (retail_price is null or retail_price >= 0),
  constraint variants_weight_chk check (weight_grams is null or weight_grams > 0),

  -- The three structural guarantees, in key form:
  -- (1) the style exists in this tenant AND uses this size scale
  constraint variants_style_fk
    foreign key (tenant_id, style_id, size_scale_id)
    references public.styles (tenant_id, id, size_scale_id) on delete restrict,
  -- (2) the colorway belongs to that style
  constraint variants_colorway_fk
    foreign key (tenant_id, style_id, colorway_id)
    references public.colorways (tenant_id, style_id, id) on delete restrict,
  -- (3) the size belongs to that size scale
  constraint variants_size_fk
    foreign key (tenant_id, size_scale_id, size_id)
    references public.sizes (tenant_id, size_scale_id, id) on delete restrict
);

-- "Show me the size grid for this style" -- the most common catalog read.
create index variants_style_idx
  on public.variants (tenant_id, style_id, colorway_id);
-- Sellable-only lookups for the order entry screen and the ATS query.
-- ATS means "available to sell": the number a salesperson is allowed to
-- promise a customer for a given week. Section 11.10 builds it in full.
create index variants_sellable_idx
  on public.variants (tenant_id, status)
  where is_sellable;
-- SKU prefix search: `sku like 'W1042%'`. text_pattern_ops makes LIKE with
-- a literal prefix an index range scan even under a non-C collation.
create index variants_sku_prefix_idx
  on public.variants (tenant_id, sku text_pattern_ops);
-- Integration lookups: "which variant is Shopify id 4451...?"
create index variants_external_gin
  on public.variants using gin (external_ids jsonb_path_ops);
-- Support `on delete restrict` from sizes/colorways without a seq scan.
create index variants_size_idx on public.variants (tenant_id, size_id);

select app.enable_tenant_rls('public.variants');

In plain English: this row is the SKU. It names one style, one color, and one size, and it repeats the style's size scale so that the three foreign keys underneath can each check one part of the combination. The five indexes cover the five real queries:

• show the size grid for a style
• list only sellable SKUs
• prefix-search a SKU number
• find a SKU by its Shopify id
• and let PostgreSQL check the size link cheaply when somebody tries to delete a size

barcodes

Scannable identifiers for a variant. A UPC is the 12-digit barcode on North American retail packaging; an EAN is its 13-digit international equivalent; an ITF-14 is the bigger barcode printed on a shipping case rather than on the garment.

Usually there is one UPC or EAN per SKU, but a variant genuinely can carry several: your own UPC, a retailer-assigned barcode for a private-label program, and an ITF-14 on the case. Rows are created when a UPC is assigned during product setup, or imported in bulk from a GS1 allocation spreadsheet. (GS1 is the non-profit that issues the number ranges these barcodes are drawn from; you license a prefix from them and assign the rest yourself.)

create table public.barcodes (
  id            uuid primary key default gen_random_uuid(),
  tenant_id     uuid not null references public.tenants (id) on delete cascade,
  variant_id    uuid not null,
  barcode       text not null,      -- digits only; check digit validated in app
  barcode_type  text not null default 'upc_a',
  is_primary    boolean not null default false,
  source        text not null default 'internal',  -- 'internal' | 'customer'
  created_at    timestamptz not null default now(),
  updated_at    timestamptz not null default now(),

  constraint barcodes_tenant_row_uk unique (tenant_id, id),
  constraint barcodes_tenant_code_uk unique (tenant_id, barcode),
  constraint barcodes_digits_chk check (barcode ~ '^[0-9]{8,14}$'),
  constraint barcodes_type_chk
    check (barcode_type in ('upc_a', 'ean_13', 'gtin_14', 'itf_14', 'code_128')),
  constraint barcodes_variant_fk
    foreign key (tenant_id, variant_id)
    references public.variants (tenant_id, id) on delete cascade
);

-- Exactly one primary barcode per variant. A partial unique index is the
-- cleanest way to say "at most one row where this flag is true".
create unique index barcodes_one_primary_uk
  on public.barcodes (tenant_id, variant_id)
  where is_primary;

-- The scan path: barcode in, variant out. Covered by barcodes_tenant_code_uk.
create index barcodes_variant_idx on public.barcodes (tenant_id, variant_id);

select app.enable_tenant_rls('public.barcodes');

In plain English: barcodes hang off a variant, several are allowed, and exactly one may be flagged primary, which the partial unique index enforces without any application code. The digits-only check catches pasted spaces and hyphens. The scan path, barcode in and variant out, is already served by the unique constraint on the barcode itself.

price_lists

A named set of prices in one currency: your standard wholesale list, a EUR list for European accounts, a key-account list with negotiated pricing, an MSRP list. Created during onboarding and each time a new pricing arrangement is agreed. An order stamps its price list at entry so re-pricing a list later never rewrites history.

create table public.price_lists (
  id          uuid primary key default gen_random_uuid(),
  tenant_id   uuid not null references public.tenants (id) on delete cascade,
  code        text not null,        -- 'WHSL-USD-2027'
  name        text not null,
  currency    app.currency_code not null,
  price_kind  text not null default 'wholesale',
  valid_from  date not null default current_date,
  valid_to    date,                 -- null = open ended
  is_default  boolean not null default false,
  created_at  timestamptz not null default now(),
  updated_at  timestamptz not null default now(),

  constraint price_lists_tenant_row_uk unique (tenant_id, id),
  constraint price_lists_tenant_code_uk unique (tenant_id, code),
  constraint price_lists_kind_chk
    check (price_kind in ('wholesale', 'retail', 'msrp', 'cost')),
  constraint price_lists_range_chk
    check (valid_to is null or valid_to >= valid_from)
);

-- At most one default list per (currency, kind) -- the fallback used when a
-- customer has no list of their own.
create unique index price_lists_one_default_uk
  on public.price_lists (tenant_id, currency, price_kind)
  where is_default;

select app.enable_tenant_rls('public.price_lists');

In plain English: a price list is a named container with a currency and a kind. It holds no prices at all; the next table does. The partial unique index allows exactly one default list per currency and kind, and that is the list the system falls back to when a customer has none of their own.

price_list_items

The actual prices. Each row prices either one variant or a whole style (never both — num_nonnulls enforces exactly one), at or above a minimum quantity, over a date range. Rows are created when a price list is built or imported, and superseded by inserting a new row whose range starts where the old one ends. The exclusion constraint is the interesting part: it makes overlapping prices for the same target impossible, so "which price applies today?" always has exactly one answer instead of a race between two rows.

create table public.price_list_items (
  id             uuid primary key default gen_random_uuid(),
  tenant_id      uuid not null references public.tenants (id) on delete cascade,
  price_list_id  uuid not null,
  variant_id     uuid,          -- price one SKU...
  style_id       uuid,          -- ...or every SKU in a style
  min_quantity   integer not null default 1,   -- volume break threshold
  unit_price     numeric(18,4) not null,
  effective      daterange not null default daterange(current_date, null, '[)'),
  created_at     timestamptz not null default now(),
  updated_at     timestamptz not null default now(),

  constraint price_list_items_tenant_row_uk unique (tenant_id, id),
  constraint price_list_items_target_chk
    check (num_nonnulls(variant_id, style_id) = 1),
  constraint price_list_items_qty_chk check (min_quantity > 0),
  constraint price_list_items_price_chk check (unit_price >= 0),
  constraint price_list_items_range_chk check (not isempty(effective)),
  constraint price_list_items_list_fk
    foreign key (tenant_id, price_list_id)
    references public.price_lists (tenant_id, id) on delete cascade,
  constraint price_list_items_variant_fk
    foreign key (tenant_id, variant_id)
    references public.variants (tenant_id, id) on delete cascade,
  constraint price_list_items_style_fk
    foreign key (tenant_id, style_id)
    references public.styles (tenant_id, id) on delete cascade,

  -- No two rows may price the same target, on the same list, at the same
  -- volume break, over overlapping dates. Requires btree_gist for the
  -- uuid/integer equality columns; && is the range-overlap operator.
  constraint price_list_items_no_overlap_excl
    exclude using gist (
      tenant_id with =,
      price_list_id with =,
      (coalesce(variant_id, style_id)) with =,
      min_quantity with =,
      effective with &&
    )
);

-- Price resolution reads every row for a list and a variant at once.
create index price_list_items_lookup_idx
  on public.price_list_items (tenant_id, price_list_id, variant_id);
create index price_list_items_style_idx
  on public.price_list_items (tenant_id, price_list_id, style_id);

select app.enable_tenant_rls('public.price_list_items');

Price resolution order, implemented in the application:

1. variant row on the customer's list
2. style row on the customer's list
3. variant row on the default list
4. style row on the default list
5. variants.wholesale_price
6. styles.default_wholesale_price

Whichever wins is copied onto the order line, because the number you quoted on the day is a historical fact, and re-deriving it next year would give a different answer.

11.3 Partners

Everyone outside your company that you exchange goods, money, or paperwork with. On the sell side: customers (retailers, boutiques, marketplaces), their locations (a chain can have one bill-to and several hundred ship-tos), and the sales reps who own the relationship. On the buy side: suppliers, which in apparel means cut-and-sew factories, fabric mills, trim vendors, and freight forwarders. Payment terms sit in the middle because both sides use them.

One acronym runs through this section and the four after it. EDI is electronic data interchange: a set of fixed-format messages that large retailers and their suppliers exchange instead of email, each identified by a number. The four you will meet in this chapter are:

• the 850 (purchase order — the retailer ordering from you)
• the 856 (ship notice/manifest, universally called the ASN — you telling them what is in the truck, carton by carton)
• the 945 (warehouse shipping advice — your warehouse telling you what it actually shipped)
• and the 810 (invoice)

If you never sell to a department store you may never touch any of them; if you do, they are not optional.

  +----------------+          +-------------+
  | payment_terms  |          | sales_reps  |
  +----------------+          +-------------+
        |      |                     |
        |      +---------+           |
        v                v           v
  +-------------+     +--------------------+
  |  suppliers  |     |     customers      |---+ parent_customer_id
  +-------------+     +--------------------+   | (self reference:
        |                   |         |        |  buying groups,
        | 3PL operator      | 1:N     | 1:N    |  store chains)
        v                   v         v        |
  +--------------+   +--------------------+    |
  |  warehouses  |   | customer_locations |    |
  |  (sec 11.4)  |   |  bill_to / ship_to |    |
  +--------------+   +--------------------+    |
                            ^                  |
                            +------------------+
                     +------------------+
                     |  customer_terms  |  dated overrides of the
                     +------------------+  standard payment terms

  Read it as: payment terms are shared by both sides of the business;
  a customer may point at a parent customer; every address hangs off a
  customer; and a warehouse may be operated by a supplier (your 3PL).

payment_terms

The rules for when money is due: Net 30, 2/10 Net 30 (2% off if paid within 10 days, otherwise the full amount in 30), Due on Receipt. One catalog shared by customers and suppliers. Seeded for every new tenant, extended by finance when a new arrangement is negotiated. These three numbers drive the invoice due date and the aging report.

create table public.payment_terms (
  id                uuid primary key default gen_random_uuid(),
  tenant_id         uuid not null references public.tenants (id) on delete cascade,
  code              text not null,        -- 'NET30', '2-10-NET30'
  name              text not null,        -- '2% 10 days, net 30'
  net_days          smallint not null,    -- due date = invoice date + net_days
  discount_percent  numeric(6,3) not null default 0,  -- early-payment discount
  discount_days     smallint not null default 0,      -- window for that discount
  is_default        boolean not null default false,
  is_active         boolean not null default true,
  created_at        timestamptz not null default now(),
  updated_at        timestamptz not null default now(),

  constraint payment_terms_tenant_row_uk unique (tenant_id, id),
  constraint payment_terms_tenant_code_uk unique (tenant_id, code),
  constraint payment_terms_net_chk check (net_days between 0 and 365),
  constraint payment_terms_discount_chk
    check (discount_percent >= 0 and discount_percent < 100),
  -- You cannot offer a discount window longer than the term itself.
  constraint payment_terms_window_chk
    check (discount_days >= 0 and discount_days <= net_days),
  -- A discount percent with no window (or vice versa) is always a data error.
  constraint payment_terms_pair_chk
    check ((discount_percent = 0) = (discount_days = 0))
);

create unique index payment_terms_one_default_uk
  on public.payment_terms (tenant_id)
  where is_default;

select app.enable_tenant_rls('public.payment_terms');

In plain English: three numbers describe almost any normal payment term. net_days sets the due date, and discount_percent with discount_days describes an early-payment discount. The last two checks block the two ways this table gets filled in wrongly: a discount window longer than the term itself, and a percentage with no window (or a window with no percentage).

sales_reps

The person or agency that owns a customer relationship and earns commission on it. A rep may or may not be a user of your system, independent showroom agents usually are not, so user_id is nullable. Rows are created when a rep is onboarded; deactivated rather than deleted so historical orders keep their attribution.

create table public.sales_reps (
  id               uuid primary key default gen_random_uuid(),
  tenant_id        uuid not null references public.tenants (id) on delete cascade,
  code             text not null,       -- 'NE-01'
  name             text not null,
  email            text,
  phone            text,
  territory        text,                -- 'Northeast US'
  commission_rate  numeric(6,4) not null default 0,  -- 0.0700 = 7% of net sales
  user_id          uuid references public.users (id) on delete set null,
  status           text not null default 'active',
  created_at       timestamptz not null default now(),
  updated_at       timestamptz not null default now(),

  constraint sales_reps_tenant_row_uk unique (tenant_id, id),
  constraint sales_reps_tenant_code_uk unique (tenant_id, code),
  constraint sales_reps_status_chk check (status in ('active', 'inactive')),
  -- A rate is a fraction between 0 and 1, so 7% is stored as 0.0700.
  -- The _rate suffix marks that convention; a _percent column would
  -- hold 7.000 instead. See the naming table in 11.0.1.
  constraint sales_reps_rate_chk check (commission_rate between 0 and 1)
);

select app.enable_tenant_rls('public.sales_reps');

In plain English: a rep can exist without being a user of your system, which is why user_id is nullable — most independent showroom agents never log in. Commission is stored as a fraction between 0 and 1, so a 7% rep is 0.0700. Reps are deactivated rather than deleted, so old orders keep their attribution.

customers

A business you sell to. A row is created when an account is opened — by a salesperson, by an approved wholesale application, or by the Shopify connector the first time an unknown buyer appears. parent_customer_id models buying groups and store chains: two hundred franchise doors under one corporate parent, where credit is checked at the parent and shipped to the children. credit_limit plus the open receivable balance is what the order-entry screen checks before letting a large order through.

create table public.customers (
  id                  uuid primary key default gen_random_uuid(),
  tenant_id           uuid not null references public.tenants (id) on delete cascade,
  code                text not null,      -- 'NORDSTROM' -- stable business key
  name                text not null,      -- trading name, shown in the UI
  legal_name          text,               -- name on the invoice
  customer_type       text not null default 'wholesale',
  status              text not null default 'active',
  parent_customer_id  uuid,               -- buying group / chain parent
  currency            app.currency_code not null default 'USD',
  price_list_id       uuid,               -- null = use the default list
  payment_terms_id    uuid,
  sales_rep_id        uuid,
  credit_limit        numeric(18,4) not null default 0,
  tax_exempt          boolean not null default false,
  tax_registration    text,               -- resale certificate / VAT number
  edi_partner_id      text,               -- ISA qualifier + id for EDI routing
  external_ids        jsonb not null default '{}'::jsonb,
  notes               text,
  created_by          uuid references public.users (id) on delete set null,
  created_at          timestamptz not null default now(),
  updated_at          timestamptz not null default now(),

  constraint customers_tenant_row_uk unique (tenant_id, id),
  constraint customers_tenant_code_uk unique (tenant_id, code),
  constraint customers_type_chk
    check (customer_type in
      ('wholesale', 'retail', 'ecommerce', 'distributor', 'internal')),
  constraint customers_status_chk
    check (status in ('prospect', 'active', 'on_hold', 'inactive')),
  constraint customers_credit_chk check (credit_limit >= 0),
  constraint customers_not_own_parent_chk check (parent_customer_id <> id),
  constraint customers_parent_fk
    foreign key (tenant_id, parent_customer_id)
    references public.customers (tenant_id, id) on delete restrict,
  constraint customers_price_list_fk
    foreign key (tenant_id, price_list_id)
    references public.price_lists (tenant_id, id) on delete restrict,
  constraint customers_terms_fk
    foreign key (tenant_id, payment_terms_id)
    references public.payment_terms (tenant_id, id) on delete restrict,
  constraint customers_rep_fk
    foreign key (tenant_id, sales_rep_id)
    references public.sales_reps (tenant_id, id) on delete restrict
);

-- Customer list screen: active accounts sorted by name.
create index customers_browse_idx
  on public.customers (tenant_id, status, name);
-- Type-ahead on the order entry screen.
create index customers_name_trgm
  on public.customers using gin (name gin_trgm_ops);
-- "All doors under this parent" -- and it makes the parent FK's restrict fast.
create index customers_parent_idx
  on public.customers (tenant_id, parent_customer_id)
  where parent_customer_id is not null;
-- Rep commission reporting and the "my accounts" filter.
create index customers_rep_idx on public.customers (tenant_id, sales_rep_id);
-- Integration matching: Shopify customer id, EDI partner id.
create index customers_external_gin
  on public.customers using gin (external_ids jsonb_path_ops);

select app.enable_tenant_rls('public.customers');

In plain English: this is the account. The optional parent link models chains and buying groups, and the check stops a row becoming its own parent. Four foreign keys hang defaults off the account — price list, payment terms, rep, currency, so order entry can fill most of a new order from the customer alone. The GIN index on external_ids is what lets the Shopify and EDI connectors find an existing account instead of quietly creating a second one.

customer_locations

An address belonging to a customer: where the invoice goes (bill-to) and where the goods go (ship-to). Big retailers have thousands of ship-tos, one per store, each with its own store number that must appear on the carton label. Rows are created with the account, imported in bulk from an EDI 850 or a store list, and deactivated when a door closes.

create table public.customer_locations (
  id                  uuid primary key default gen_random_uuid(),
  tenant_id           uuid not null references public.tenants (id) on delete cascade,
  customer_id         uuid not null,
  code                text not null,     -- store number / 'HQ' / 'DC-02'
  location_type       text not null default 'ship_to',
  name                text not null,
  address_line1       text not null,
  address_line2       text,
  city                text not null,
  region              text,              -- state / province
  postal_code         text,
  country             app.country_code not null,
  contact_name        text,
  phone               text,
  email               text,
  edi_location_code   text,              -- GLN or DUNS+4, printed on labels
  is_default_bill_to  boolean not null default false,
  is_default_ship_to  boolean not null default false,
  is_active           boolean not null default true,
  created_at          timestamptz not null default now(),
  updated_at          timestamptz not null default now(),

  constraint customer_locations_tenant_row_uk unique (tenant_id, id),
  constraint customer_locations_customer_code_uk
    unique (tenant_id, customer_id, code),
  constraint customer_locations_type_chk
    check (location_type in ('bill_to', 'ship_to', 'both')),
  -- A location cannot be the default bill-to unless it can receive invoices.
  constraint customer_locations_bill_flag_chk
    check (not is_default_bill_to or location_type in ('bill_to', 'both')),
  constraint customer_locations_ship_flag_chk
    check (not is_default_ship_to or location_type in ('ship_to', 'both')),
  constraint customer_locations_customer_fk
    foreign key (tenant_id, customer_id)
    references public.customers (tenant_id, id) on delete cascade
);

-- Exactly one default of each kind per customer. This is why customers has
-- no default_ship_to_id column: a partial unique index expresses the rule
-- without creating a circular foreign key between the two tables.
create unique index customer_locations_one_bill_to_uk
  on public.customer_locations (tenant_id, customer_id)
  where is_default_bill_to;
create unique index customer_locations_one_ship_to_uk
  on public.customer_locations (tenant_id, customer_id)
  where is_default_ship_to;

-- Address picker on the order screen.
create index customer_locations_customer_idx
  on public.customer_locations (tenant_id, customer_id)
  where is_active;
-- EDI inbound resolves a ship-to by the partner's own location code.
create index customer_locations_edi_idx
  on public.customer_locations (tenant_id, edi_location_code)
  where edi_location_code is not null;

select app.enable_tenant_rls('public.customer_locations');

In plain English: addresses belong to the customer, each with a code the retailer will recognize. The two partial unique indexes allow exactly one default bill-to and one default ship-to per customer. Expressing the rule on the child avoids a circular foreign key: customers never needs a default_location_id pointing back at a table that already points at it.

customer_terms

A dated override of the standard payment terms for one customer: "Nordstrom gets Net 60 and a 3% discount from January, approved by the CFO." Rows are created when finance negotiates or revises terms, and are never edited — a change means closing the current range and inserting a new row, which gives you a full history of what was promised when. The exclusion constraint guarantees exactly one set of terms is in force on any given day.

create table public.customer_terms (
  id                uuid primary key default gen_random_uuid(),
  tenant_id         uuid not null references public.tenants (id) on delete cascade,
  customer_id       uuid not null,
  payment_terms_id  uuid not null,
  credit_limit      numeric(18,4),      -- null = inherit customers.credit_limit
  discount_percent  numeric(6,3) not null default 0,  -- standing trade discount
  effective         daterange not null default daterange(current_date, null, '[)'),
  approved_by       uuid references public.users (id) on delete set null,
  note              text,
  created_at        timestamptz not null default now(),
  updated_at        timestamptz not null default now(),

  constraint customer_terms_tenant_row_uk unique (tenant_id, id),
  constraint customer_terms_discount_chk
    check (discount_percent >= 0 and discount_percent < 100),
  constraint customer_terms_credit_chk
    check (credit_limit is null or credit_limit >= 0),
  constraint customer_terms_range_chk check (not isempty(effective)),
  constraint customer_terms_customer_fk
    foreign key (tenant_id, customer_id)
    references public.customers (tenant_id, id) on delete cascade,
  constraint customer_terms_terms_fk
    foreign key (tenant_id, payment_terms_id)
    references public.payment_terms (tenant_id, id) on delete restrict,
  -- One set of terms in force per customer per day.
  constraint customer_terms_no_overlap_excl
    exclude using gist (
      tenant_id with =,
      customer_id with =,
      effective with &&
    )
);

-- "What terms apply today?" -- the GiST index from the exclusion constraint
-- already answers `where customer_id = ? and effective @> current_date`.

select app.enable_tenant_rls('public.customer_terms');

In plain English: instead of editing a customer's terms in place, you close the current date range and insert a new row. The exclusion constraint refuses to let two rows for the same customer cover the same day, so "what terms apply today?" always has exactly one answer, and the record of what was promised, when, and by whom survives.

suppliers

Anyone you buy from: the cut-and-sew factory in Vietnam, the mill that sells you jersey, the trim vendor, the freight forwarder, the 3PL that runs your warehouse. Rows are created during vendor onboarding. lead_time_days and min_order_quantity are planning inputs — they are what turns "we need 5,000 units by August" into "place the purchase order by April", and they belong on the supplier because they are properties of the relationship, not of any one order.

create table public.suppliers (
  id                  uuid primary key default gen_random_uuid(),
  tenant_id           uuid not null references public.tenants (id) on delete cascade,
  code                text not null,       -- 'VN-APEX'
  name                text not null,
  legal_name          text,
  supplier_type       text not null default 'factory',
  status              text not null default 'active',
  currency            app.currency_code not null default 'USD',
  payment_terms_id    uuid,
  lead_time_days      smallint not null default 60,  -- PO issue to ex-factory
  min_order_quantity  integer not null default 0,    -- per style, per colour
  incoterm            text,                -- 'FOB', 'DDP': an Incoterm, the
                                           -- standard three-letter code for
                                           -- who pays freight and who carries
                                           -- the risk at each leg
  contact_name        text,
  email               text,
  phone               text,
  address_line1       text,
  city                text,
  region              text,
  postal_code         text,
  country             app.country_code,
  certifications      jsonb not null default '{}'::jsonb,  -- WRAP, GOTS, audits
  external_ids        jsonb not null default '{}'::jsonb,
  created_at          timestamptz not null default now(),
  updated_at          timestamptz not null default now(),

  constraint suppliers_tenant_row_uk unique (tenant_id, id),
  constraint suppliers_tenant_code_uk unique (tenant_id, code),
  constraint suppliers_type_chk
    check (supplier_type in
      ('factory', 'mill', 'trim', 'agent', 'logistics', 'service')),
  constraint suppliers_status_chk
    check (status in ('prospect', 'active', 'on_hold', 'inactive')),
  constraint suppliers_lead_time_chk check (lead_time_days >= 0),
  constraint suppliers_moq_chk check (min_order_quantity >= 0),
  constraint suppliers_incoterm_chk
    check (incoterm is null or incoterm ~ '^[A-Z]{3}$'),
  constraint suppliers_terms_fk
    foreign key (tenant_id, payment_terms_id)
    references public.payment_terms (tenant_id, id) on delete restrict
);

create index suppliers_browse_idx on public.suppliers (tenant_id, status, name);
create index suppliers_name_trgm
  on public.suppliers using gin (name gin_trgm_ops);

select app.enable_tenant_rls('public.suppliers');

In plain English: one table covers factories, mills, trim vendors, freight forwarders, and the 3PL that runs your warehouse, kept apart by supplier_type. lead_time_days and min_order_quantity are what a planner needs to work backward from a delivery date to an order date. certifications is a JSON bag because every retailer asks for a different set of audit documents.

11.4 Inventory

This is the heart of the system and the section chapter 1 was written for. There is exactly one place where stock levels change: inventory_transactions, an append-only ledger of signed quantity movements. Nothing else may write a stock number.

inventory_balances is a cache — a materialized running total that must always equal the sum of the ledger, and that you can rebuild from scratch at any time. Counts, adjustments, and transfers are documents that, when posted, emit ledger rows; they never edit balances directly.

                     +--------------+
                     |  warehouses  |
                     +--------------+
                            |
        +-------------------+-------------------+
        |                                       |
        v                                       v
 +--------------------------+        +----------------------+
 | inventory_transactions   |=====>  | inventory_balances   |
 |  APPEND-ONLY LEDGER      | sum()  |  CACHE (rebuildable) |
 |  variant, warehouse,     |        |  on_hand, allocated, |
 |  signed quantity, ref    |        |  reserved, incoming, |
 +--------------------------+        |  available (derived) |
        ^      ^      ^              +----------------------+
        |      |      |
        |      |      +-------------------------+
        |      |                                |
 +---------------+  +----------------------+  +-------------------+
 | inventory_    |  | inventory_           |  |     transfers     |
 | counts        |  | adjustments          |  +-------------------+
 +---------------+  +----------------------+           |
        |                     |                        |
        v                     v                        v
 +---------------+  +----------------------+  +-------------------+
 | inventory_    |  | inventory_           |  |  transfer_lines   |
 | count_lines   |  | adjustment_lines     |  +-------------------+
 +---------------+  +----------------------+

  Documents post DOWNWARD into the ledger (the arrows into the ledger are
  writes at post time, recorded as ref_type/ref_id). The ledger projects
  UPWARD into balances. Reads of "how much do we have" hit balances; reads
  of "why do we have that" walk the ledger.

warehouses

A physical or logical place stock can sit: your own DC, a 3PL, a retail store, a virtual "in transit" bucket, a quarantine location for damaged goods. Created during setup and when a new facility opens. is_sellable is the flag that decides whether a location's stock counts toward availability — damaged and in-transit locations hold real units that you must not promise to a customer.

create table public.warehouses (
  id             uuid primary key default gen_random_uuid(),
  tenant_id      uuid not null references public.tenants (id) on delete cascade,
  code           text not null,     -- 'DC-LA'
  name           text not null,
  warehouse_type text not null default 'owned',
  supplier_id    uuid,              -- set when a 3PL operates this location
  address_line1  text,
  city           text,
  region         text,
  postal_code    text,
  country        app.country_code,
  time_zone      text not null default 'UTC',   -- for cut-off time logic
  is_sellable    boolean not null default true, -- counts toward ATS?
  is_active      boolean not null default true,
  sort_order     smallint not null default 0,   -- allocation priority
  created_at     timestamptz not null default now(),
  updated_at     timestamptz not null default now(),

  constraint warehouses_tenant_row_uk unique (tenant_id, id),
  constraint warehouses_tenant_code_uk unique (tenant_id, code),
  constraint warehouses_type_chk
    check (warehouse_type in ('owned', '3pl', 'store', 'in_transit', 'virtual')),
  -- A 3PL location without an operator is a data-entry mistake.
  constraint warehouses_3pl_chk
    check (warehouse_type <> '3pl' or supplier_id is not null),
  constraint warehouses_supplier_fk
    foreign key (tenant_id, supplier_id)
    references public.suppliers (tenant_id, id) on delete restrict
);

create index warehouses_active_idx
  on public.warehouses (tenant_id, sort_order)
  where is_active;

select app.enable_tenant_rls('public.warehouses');

In plain English: a warehouse is anywhere stock can sit, including places that are not buildings — an in-transit bucket, a damaged-goods bucket. is_sellable is the flag that matters: stock in a location where it is false is real stock, but it will never be offered to a customer. The last check refuses a 3PL location that does not say which supplier operates it.

inventory_transactions

The ledger. One row per movement of stock: five units received, three shipped, one written off. A row is created whenever a document posts — a receipt, a shipment, an adjustment, a count, a transfer leg, and it is never updated and never deleted. A mistake is corrected by posting a compensating row, exactly as in double-entry bookkeeping, so the history of what you believed and when stays intact. Quantity is signed: positive adds, negative removes.

Three columns deserve special attention:

• seq is a gap-free-ish monotonic counter that gives the ledger a total order for replay and for the "balance is current as of sequence N" watermark.
• idempotency_key is the chapter 3 pattern made physical: the poster computes a deterministic key ('shipment:' || shipment_line_id), and the unique constraint means a retried webhook, a double-clicked button, or a re-run job inserts the movement exactly once.
• ref_type/ref_id point back at the document that caused the movement, which is what makes every number on the balance screen explainable.

create table public.inventory_transactions (
  id               uuid primary key default gen_random_uuid(),
  tenant_id        uuid not null references public.tenants (id) on delete cascade,
  seq              bigint generated always as identity,
  variant_id       uuid not null,
  warehouse_id     uuid not null,
  quantity         integer not null,       -- SIGNED: +in, -out, never zero
  txn_type         text not null,
  reason_code      text,                   -- 'damaged', 'cycle_count', 'theft'
  unit_cost        numeric(18,4),          -- landed cost per unit, for valuation
  currency         app.currency_code,
  occurred_at      timestamptz not null default now(),  -- when it happened
  posted_at        timestamptz not null default now(),  -- when we recorded it
  ref_type         text,                   -- which document caused this
  ref_id           uuid,
  ref_line_id      uuid,
  idempotency_key  text not null,          -- exactly-once insurance
  actor_user_id    uuid references public.users (id) on delete set null,
  notes            text,
  created_at       timestamptz not null default now(),

  constraint inventory_transactions_tenant_row_uk unique (tenant_id, id),
  constraint inventory_transactions_seq_uk unique (seq),
  -- The one constraint that makes retries safe.
  constraint inventory_transactions_idem_uk unique (tenant_id, idempotency_key),
  constraint inventory_transactions_qty_chk check (quantity <> 0),
  constraint inventory_transactions_type_chk
    check (txn_type in (
      'opening_balance', 'receipt', 'shipment', 'return', 'adjustment',
      'count', 'transfer_out', 'transfer_in', 'scrap', 'sample_out', 'rework')),
  -- Direction must match the type: a 'receipt' can never be negative.
  -- 'adjustment', 'count' and 'rework' may go either way by definition.
  constraint inventory_transactions_sign_chk
    check (
      (txn_type in ('opening_balance','receipt','return','transfer_in')
         and quantity > 0)
      or (txn_type in ('shipment','transfer_out','scrap','sample_out')
         and quantity < 0)
      or txn_type in ('adjustment','count','rework')
    ),
  constraint inventory_transactions_cost_chk
    check (unit_cost is null or unit_cost >= 0),
  -- Receipts must carry a cost or inventory valuation is unknowable.
  constraint inventory_transactions_receipt_cost_chk
    check (txn_type <> 'receipt' or unit_cost is not null),
  constraint inventory_transactions_ref_chk
    check (ref_type is null or ref_type in
      ('receipt','shipment','adjustment','count','transfer','order','import','manual')),
  constraint inventory_transactions_variant_fk
    foreign key (tenant_id, variant_id)
    references public.variants (tenant_id, id) on delete restrict,
  constraint inventory_transactions_warehouse_fk
    foreign key (tenant_id, warehouse_id)
    references public.warehouses (tenant_id, id) on delete restrict
);

-- The balance rebuild and the "history of this SKU here" drill-down.
create index inventory_transactions_position_idx
  on public.inventory_transactions (tenant_id, variant_id, warehouse_id, seq);
-- "What moved in this warehouse today?" and the daily valuation snapshot.
create index inventory_transactions_when_idx
  on public.inventory_transactions (tenant_id, warehouse_id, occurred_at desc);
-- "Show me every movement this shipment caused" -- the audit trail from a
-- document back into the ledger.
create index inventory_transactions_ref_idx
  on public.inventory_transactions (tenant_id, ref_type, ref_id)
  where ref_id is not null;
-- Incremental cache updates read everything above the balance watermark.
create index inventory_transactions_seq_idx
  on public.inventory_transactions (tenant_id, seq);

alter table public.inventory_transactions enable row level security;
alter table public.inventory_transactions force  row level security;

create policy inventory_transactions_select on public.inventory_transactions
  for select to authenticated
  using (tenant_id = app.current_tenant_id());

-- Insert requires the inventory.post permission as well as the tenant match:
-- a read-only sales user must not be able to invent stock.
create policy inventory_transactions_insert on public.inventory_transactions
  for insert to authenticated
  with check (
    tenant_id = app.current_tenant_id()
    and app.has_role(array['owner','admin','warehouse','integration'])
  );

grant select, insert on public.inventory_transactions to authenticated;

-- No update/delete policy exists, and the trigger stops even superusers.
create trigger inventory_transactions_append_only
  before update or delete on public.inventory_transactions
  for each row execute function app.deny_mutation();

In plain English: every row is one movement of one SKU in or out of one warehouse, signed so that adding the rows up gives you the balance. The type has to agree with the sign, a receipt can never be negative, which the sign check enforces.

The idempotency key makes retries safe: post the same movement twice with the same key and the second insert fails on a unique constraint instead of doubling your stock. There is a select policy and an insert policy, no update or delete policy at all, and a trigger that refuses both anyway.

inventory_balances

The cache. One row per (variant, warehouse) that has ever had activity, holding the four numbers every screen wants instantly: what is physically here, what is committed to orders, what is softly held, and what is on the water.

Rows are created on first movement and updated inside the same transaction as the ledger insert. available is a generated column so it can never disagree with its inputs, and version is the optimistic-concurrency counter from chapter 3.

Optimistic concurrency means nobody locks the row: each writer reads a version number, sends it back with the update, and the update only lands if the number still matches. If someone else got there first, it matches nothing and the loser is told to re-read and retry.

create table public.inventory_balances (
  id            uuid primary key default gen_random_uuid(),
  tenant_id     uuid not null references public.tenants (id) on delete cascade,
  variant_id    uuid not null,
  warehouse_id  uuid not null,
  on_hand       integer not null default 0,  -- sum of the ledger, exactly
  allocated     integer not null default 0,  -- hard-committed to order lines
  reserved      integer not null default 0,  -- soft holds, carts, quotes
  incoming      integer not null default 0,  -- open PO units expected here
  -- STORED generated column: computed on write, indexable, always in
  -- step with its inputs. Write STORED explicitly. On PostgreSQL 18 the
  -- default changed to VIRTUAL, which is computed on read and cannot be
  -- indexed -- and we index `available` two lines below.
  available     integer generated always as (on_hand - allocated - reserved) stored,
  last_seq      bigint not null default 0,   -- watermark: the highest ledger
                                             -- seq already folded into this row
  version       integer not null default 1,  -- optimistic concurrency token
  computed_at   timestamptz not null default now(),
  created_at    timestamptz not null default now(),
  updated_at    timestamptz not null default now(),

  constraint inventory_balances_tenant_row_uk unique (tenant_id, id),
  constraint inventory_balances_position_uk
    unique (tenant_id, variant_id, warehouse_id),
  constraint inventory_balances_alloc_chk check (allocated >= 0),
  constraint inventory_balances_reserved_chk check (reserved >= 0),
  constraint inventory_balances_incoming_chk check (incoming >= 0),
  -- You cannot commit more than you physically have.
  constraint inventory_balances_commit_chk
    check (allocated + reserved <= greatest(on_hand, 0)),
  constraint inventory_balances_variant_fk
    foreign key (tenant_id, variant_id)
    references public.variants (tenant_id, id) on delete restrict,
  constraint inventory_balances_warehouse_fk
    foreign key (tenant_id, warehouse_id)
    references public.warehouses (tenant_id, id) on delete restrict
);

-- "Availability for these SKUs across all warehouses" -- the ATS hot path.
create index inventory_balances_variant_idx
  on public.inventory_balances (tenant_id, variant_id);
-- The replenishment screen: what is at or below zero here?
create index inventory_balances_short_idx
  on public.inventory_balances (tenant_id, warehouse_id)
  where available <= 0;

select app.enable_tenant_rls('public.inventory_balances');

In plain English: this is a running total, one row per SKU per warehouse. on_hand is the sum of the ledger, allocated and reserved are what has been promised, and available is computed by the database from the other three so it can never drift away from them. last_seq records how far through the ledger this row has been brought up to date, which is what lets a background job resume instead of recomputing everything from the beginning.

inventory_counts

A physical count document: a full wall-to-wall inventory (the whole building, counted at once, usually with the doors shut) or a cycle count (one aisle or one group of SKUs, counted while the warehouse keeps working). Created when a warehouse manager schedules a count; it moves through counting and review, and posting it emits count-type ledger rows for every line whose counted quantity differs from the system quantity.

create table public.inventory_counts (
  id             uuid primary key default gen_random_uuid(),
  tenant_id      uuid not null references public.tenants (id) on delete cascade,
  warehouse_id   uuid not null,
  count_number   text not null,     -- 'CNT-2027-014'
  count_type     text not null default 'cycle',
  status         text not null default 'draft',
  scheduled_on   date,
  zone           text,              -- aisle / bin range being counted
  started_at     timestamptz,
  posted_at      timestamptz,
  counted_by     uuid references public.users (id) on delete set null,
  approved_by    uuid references public.users (id) on delete set null,
  notes          text,
  created_at     timestamptz not null default now(),
  updated_at     timestamptz not null default now(),

  constraint inventory_counts_tenant_row_uk unique (tenant_id, id),
  constraint inventory_counts_number_uk unique (tenant_id, count_number),
  constraint inventory_counts_type_chk
    check (count_type in ('cycle', 'full', 'spot')),
  constraint inventory_counts_status_chk
    check (status in ('draft','counting','review','posted','cancelled')),
  -- A posted count must record when it posted and who approved it.
  constraint inventory_counts_posted_chk
    check (status <> 'posted' or (posted_at is not null and approved_by is not null)),
  constraint inventory_counts_warehouse_fk
    foreign key (tenant_id, warehouse_id)
    references public.warehouses (tenant_id, id) on delete restrict
);

create index inventory_counts_open_idx
  on public.inventory_counts (tenant_id, warehouse_id, status)
  where status in ('draft','counting','review');

select app.enable_tenant_rls('public.inventory_counts');

In plain English: a count is a document with a life cycle — draft, counting, review, posted. The posted check refuses to let it reach the end without both a timestamp and an approver, so "who signed off on this variance?" always has an answer. The partial index covers only counts still in progress, which is a handful of rows at any moment.

inventory_count_lines

One row per SKU counted. system_quantity is frozen when the line is generated so the variance is measured against what we believed at that moment; counted_quantity is filled by the person with the scanner. The generated variance column is what posts to the ledger.

create table public.inventory_count_lines (
  id                 uuid primary key default gen_random_uuid(),
  tenant_id          uuid not null references public.tenants (id) on delete cascade,
  count_id           uuid not null,
  variant_id         uuid not null,
  system_quantity    integer not null,   -- snapshot at line creation
  counted_quantity   integer,            -- null until someone counts it
  variance           integer generated always as
                       (counted_quantity - system_quantity) stored,
  recount_requested  boolean not null default false,
  bin_location       text,
  counted_at         timestamptz,
  note               text,
  created_at         timestamptz not null default now(),
  updated_at         timestamptz not null default now(),

  constraint inventory_count_lines_tenant_row_uk unique (tenant_id, id),
  constraint inventory_count_lines_uk unique (tenant_id, count_id, variant_id),
  constraint inventory_count_lines_counted_chk
    check (counted_quantity is null or counted_quantity >= 0),
  constraint inventory_count_lines_count_fk
    foreign key (tenant_id, count_id)
    references public.inventory_counts (tenant_id, id) on delete cascade,
  constraint inventory_count_lines_variant_fk
    foreign key (tenant_id, variant_id)
    references public.variants (tenant_id, id) on delete restrict
);

-- The review screen sorts by biggest absolute variance first.
create index inventory_count_lines_variance_idx
  on public.inventory_count_lines (tenant_id, count_id)
  where variance <> 0;

select app.enable_tenant_rls('public.inventory_count_lines');

In plain English: when a line is created, the system's own number is copied into system_quantity and frozen there. The counter fills in counted_quantity, and the database works out the difference. Freezing matters: if you compared against a live balance instead, a shipment leaving during the count would appear as a phantom variance.

inventory_adjustments

A deliberate correction with a reason and an approver: damage, sample pulls, shrinkage, a found pallet. Created by a warehouse user, approved by a supervisor, and posted — at which point it emits adjustment ledger rows. Requiring a reason code on every adjustment is what turns "inventory is always wrong" into a report you can act on.

create table public.inventory_adjustments (
  id                 uuid primary key default gen_random_uuid(),
  tenant_id          uuid not null references public.tenants (id) on delete cascade,
  warehouse_id       uuid not null,
  adjustment_number  text not null,
  reason_code        text not null,   -- 'damage','shrink','found','sample'
  status             text not null default 'draft',
  posted_at          timestamptz,
  created_by         uuid references public.users (id) on delete set null,
  approved_by        uuid references public.users (id) on delete set null,
  notes              text,
  created_at         timestamptz not null default now(),
  updated_at         timestamptz not null default now(),

  constraint inventory_adjustments_tenant_row_uk unique (tenant_id, id),
  constraint inventory_adjustments_number_uk
    unique (tenant_id, adjustment_number),
  constraint inventory_adjustments_status_chk
    check (status in ('draft', 'approved', 'posted', 'cancelled')),
  constraint inventory_adjustments_reason_chk
    check (reason_code in
      ('damage','shrink','found','sample','rework','correction','other')),
  constraint inventory_adjustments_posted_chk
    check (status <> 'posted' or posted_at is not null),
  constraint inventory_adjustments_warehouse_fk
    foreign key (tenant_id, warehouse_id)
    references public.warehouses (tenant_id, id) on delete restrict
);

create index inventory_adjustments_recent_idx
  on public.inventory_adjustments (tenant_id, warehouse_id, created_at desc);

select app.enable_tenant_rls('public.inventory_adjustments');

In plain English: an adjustment is the paperwork around a deliberate correction, and reason_code is compulsory. That one required field is what turns a pile of corrections into a report you can act on — how much you are losing to damage, to shrink, to sampling.

inventory_adjustment_lines

One row per SKU being adjusted, with a signed delta. Positive means found, negative means lost.

create table public.inventory_adjustment_lines (
  id             uuid primary key default gen_random_uuid(),
  tenant_id      uuid not null references public.tenants (id) on delete cascade,
  adjustment_id  uuid not null,
  variant_id     uuid not null,
  quantity_delta integer not null,   -- SIGNED, mirrors the ledger
  unit_cost      numeric(18,4),      -- write-off value, for the profit
                                     -- and loss statement
  note           text,
  created_at     timestamptz not null default now(),
  updated_at     timestamptz not null default now(),

  constraint inventory_adjustment_lines_tenant_row_uk unique (tenant_id, id),
  constraint inventory_adjustment_lines_uk
    unique (tenant_id, adjustment_id, variant_id),
  constraint inventory_adjustment_lines_qty_chk check (quantity_delta <> 0),
  constraint inventory_adjustment_lines_cost_chk
    check (unit_cost is null or unit_cost >= 0),
  constraint inventory_adjustment_lines_adjustment_fk
    foreign key (tenant_id, adjustment_id)
    references public.inventory_adjustments (tenant_id, id) on delete cascade,
  constraint inventory_adjustment_lines_variant_fk
    foreign key (tenant_id, variant_id)
    references public.variants (tenant_id, id) on delete restrict
);

create index inventory_adjustment_lines_variant_idx
  on public.inventory_adjustment_lines (tenant_id, variant_id);

select app.enable_tenant_rls('public.inventory_adjustment_lines');

In plain English: one row per SKU, carrying a signed change that mirrors what will hit the ledger. Zero is rejected, since an adjustment of nothing is a mistake. The unit cost is captured so finance can value the write-off.

transfers

Stock moving between two of your own warehouses. Created when someone requests a transfer; shipping it posts transfer_out rows at the origin, receiving it posts transfer_in rows at the destination. The gap between those two events is real, a truck takes three days, which is why the two legs are separate ledger entries and why many companies route the middle through an in_transit warehouse so the units are never invisible.

create table public.transfers (
  id                  uuid primary key default gen_random_uuid(),
  tenant_id           uuid not null references public.tenants (id) on delete cascade,
  transfer_number     text not null,
  from_warehouse_id   uuid not null,
  to_warehouse_id     uuid not null,
  status              text not null default 'draft',
  requested_on        date not null default current_date,
  expected_arrival_on date,
  shipped_at          timestamptz,
  received_at         timestamptz,
  carrier             text,
  tracking_number     text,
  created_by          uuid references public.users (id) on delete set null,
  notes               text,
  created_at          timestamptz not null default now(),
  updated_at          timestamptz not null default now(),

  constraint transfers_tenant_row_uk unique (tenant_id, id),
  constraint transfers_number_uk unique (tenant_id, transfer_number),
  constraint transfers_status_chk
    check (status in ('draft','in_transit','received','cancelled')),
  -- Transferring to yourself is always a mistake.
  constraint transfers_distinct_chk check (from_warehouse_id <> to_warehouse_id),
  constraint transfers_shipped_chk
    check (status = 'draft' or status = 'cancelled' or shipped_at is not null),
  constraint transfers_received_chk
    check (status <> 'received' or received_at is not null),
  constraint transfers_from_fk
    foreign key (tenant_id, from_warehouse_id)
    references public.warehouses (tenant_id, id) on delete restrict,
  constraint transfers_to_fk
    foreign key (tenant_id, to_warehouse_id)
    references public.warehouses (tenant_id, id) on delete restrict
);

create index transfers_open_idx
  on public.transfers (tenant_id, status, expected_arrival_on)
  where status in ('draft','in_transit');

select app.enable_tenant_rls('public.transfers');

In plain English: a transfer names an origin, a destination, and a status. It cannot run from a warehouse to itself, and it cannot claim to have been received with no received-at time. The two legs — out of the origin, into the destination — are posted separately, because in real life days pass between them.

transfer_lines

One row per SKU on the transfer, tracking shipped and received quantities separately because they routinely differ, and every difference is the start of a shrinkage investigation.

create table public.transfer_lines (
  id                 uuid primary key default gen_random_uuid(),
  tenant_id          uuid not null references public.tenants (id) on delete cascade,
  transfer_id        uuid not null,
  variant_id         uuid not null,
  quantity_requested integer not null,
  quantity_shipped   integer not null default 0,
  quantity_received  integer not null default 0,
  created_at         timestamptz not null default now(),
  updated_at         timestamptz not null default now(),

  constraint transfer_lines_tenant_row_uk unique (tenant_id, id),
  constraint transfer_lines_uk unique (tenant_id, transfer_id, variant_id),
  constraint transfer_lines_requested_chk check (quantity_requested > 0),
  constraint transfer_lines_shipped_chk check (quantity_shipped >= 0),
  -- You cannot receive more than was shipped.
  constraint transfer_lines_received_chk
    check (quantity_received >= 0 and quantity_received <= quantity_shipped),
  constraint transfer_lines_transfer_fk
    foreign key (tenant_id, transfer_id)
    references public.transfers (tenant_id, id) on delete cascade,
  constraint transfer_lines_variant_fk
    foreign key (tenant_id, variant_id)
    references public.variants (tenant_id, id) on delete restrict
);

create index transfer_lines_variant_idx
  on public.transfer_lines (tenant_id, variant_id);

select app.enable_tenant_rls('public.transfer_lines');

In plain English: three quantities per SKU (requested, shipped, received) and a constraint that you cannot receive more than was shipped. Requested and shipped differ when the origin was short. Shipped and received differ when something went missing on the way, and that is the number worth a phone call.

11.5 Sales

Order-to-cash starts here:

• An order is a customer's commitment to buy;
• order lines are the SKUs and quantities;
• order revisions are the immutable history of every change, which matters enormously when a buyer disputes what they agreed to;
• allocations are hard commitments of specific warehouse stock to specific lines;
• and reservations are soft, expiring holds — the difference between "this is yours" and "this is yours for the next twenty minutes".

  +------------+        +------------------+
  | customers  |---1:N--|      orders      |---1:N---+ order_revisions
  +------------+        +------------------+          (append-only log)
                               |
                               | 1:N
                               v
                        +------------------+
                        |   order_lines    |
                        +------------------+
                          |            |
                     1:N  |            |  1:N
                          v            v
                 +---------------+  +---------------+
                 |  allocations  |  | reservations  |
                 | hard, durable |  | soft, expiring|
                 +---------------+  +---------------+
                          |                  |
                          +--------+---------+
                                   v
                        inventory_balances.allocated
                        inventory_balances.reserved
                        (both maintained in the same
                         transaction as the row here)

orders

A customer order. A row is created by a salesperson keying it in, by the Shopify connector, by an inbound EDI 850, by a spreadsheet import, or by a self-serve wholesale portal — five sources, one table, distinguished by source.

In apparel, orders are placed months before they ship, which is why start_ship_on and cancel_after_on exist: a retailer's purchase order says "ship between 1 July and 15 July; if it is not shipped by then, it is canceled". That window is a contractual term, and the retailer will enforce it.

The money columns are stored, not computed on read, because an order total must be reproducible years later even if a price list changed. version supports optimistic concurrency (chapter 3): the UI reads version 7, submits an update where version = 7, and if a rep in another window already saved version 8 the update matches zero rows and the app can re-present the conflict instead of silently overwriting.

create table public.orders (
  id                    uuid primary key default gen_random_uuid(),
  tenant_id             uuid not null references public.tenants (id) on delete cascade,
  order_number          text not null,      -- 'SO-2027-00412'
  customer_id           uuid not null,
  bill_to_location_id   uuid,
  ship_to_location_id   uuid,
  sales_rep_id          uuid,
  price_list_id         uuid,
  payment_terms_id      uuid,
  status                text not null default 'draft',
  order_date            date not null default current_date,
  start_ship_on         date,               -- earliest acceptable ship date
  cancel_after_on       date,               -- retailer cancels after this date
  requested_ship_on     date,
  customer_po_number    text,               -- the buyer's own PO reference
  currency              app.currency_code not null default 'USD',
  subtotal_amount       numeric(18,4) not null default 0,
  discount_amount       numeric(18,4) not null default 0,
  tax_amount            numeric(18,4) not null default 0,
  freight_amount        numeric(18,4) not null default 0,
  total_amount          numeric(18,4) not null default 0,
  source                text not null default 'manual',
  external_id           text,               -- Shopify order id, EDI control no.
  external_updated_at   timestamptz,        -- for last-writer reconciliation
  version               integer not null default 1,  -- optimistic concurrency
  revision              integer not null default 0,  -- customer-visible rev no.
  hold_reason           text,
  notes                 text,
  created_by            uuid references public.users (id) on delete set null,
  submitted_at          timestamptz,
  confirmed_at          timestamptz,
  cancelled_at          timestamptz,
  closed_at             timestamptz,
  created_at            timestamptz not null default now(),
  updated_at            timestamptz not null default now(),

  constraint orders_tenant_row_uk unique (tenant_id, id),
  constraint orders_number_uk unique (tenant_id, order_number),
  constraint orders_status_chk
    check (status in ('draft','submitted','confirmed','allocated',
                      'partially_shipped','shipped','invoiced','closed','cancelled')),
  constraint orders_source_chk
    check (source in ('manual','shopify','edi','import','api','portal')),
  constraint orders_amounts_chk
    check (subtotal_amount >= 0 and discount_amount >= 0
           and tax_amount >= 0 and freight_amount >= 0 and total_amount >= 0),
  -- The ship window has to open before it closes.
  constraint orders_ship_window_chk
    check (cancel_after_on is null or start_ship_on is null
           or cancel_after_on >= start_ship_on),
  constraint orders_cancelled_chk
    check ((status = 'cancelled') = (cancelled_at is not null)),
  constraint orders_version_chk check (version > 0),
  constraint orders_customer_fk
    foreign key (tenant_id, customer_id)
    references public.customers (tenant_id, id) on delete restrict,
  constraint orders_bill_to_fk
    foreign key (tenant_id, bill_to_location_id)
    references public.customer_locations (tenant_id, id) on delete restrict,
  constraint orders_ship_to_fk
    foreign key (tenant_id, ship_to_location_id)
    references public.customer_locations (tenant_id, id) on delete restrict,
  constraint orders_rep_fk
    foreign key (tenant_id, sales_rep_id)
    references public.sales_reps (tenant_id, id) on delete restrict,
  constraint orders_price_list_fk
    foreign key (tenant_id, price_list_id)
    references public.price_lists (tenant_id, id) on delete restrict,
  constraint orders_terms_fk
    foreign key (tenant_id, payment_terms_id)
    references public.payment_terms (tenant_id, id) on delete restrict
);

-- Idempotent integration ingest: the same Shopify order can arrive twice
-- from a webhook and a poll, and the second insert must fail, not duplicate.
create unique index orders_external_uk
  on public.orders (tenant_id, source, external_id)
  where external_id is not null;

-- The order book: open orders by ship window. The partial predicate keeps
-- the index small -- closed orders are the majority after year one.
create index orders_open_idx
  on public.orders (tenant_id, start_ship_on, customer_id)
  where status in ('confirmed','allocated','partially_shipped');
-- Customer account screen.
create index orders_customer_idx
  on public.orders (tenant_id, customer_id, order_date desc);
-- Ops dashboards and status filters.
create index orders_status_idx
  on public.orders (tenant_id, status, order_date desc);

select app.enable_tenant_rls('public.orders');

In plain English: the header records who, when, where, on what terms, and how much. The totals are stored rather than recalculated on read, so an order printed in three years matches the one printed today. The unique index on source plus external id is what stops the same Shopify order being imported twice, once by a webhook and once by a poller. The three ordinary indexes serve the order book, the customer account screen, and the ops dashboard.

order_lines

One row per SKU per order. The four quantity columns are the whole game: quantity_ordered never changes without a revision, while allocated, shipped, and canceled move as the order progresses. quantity_allocated means "currently committed and not yet shipped", so it goes down as shipments go out, which is what makes the check constraint below hold at every moment.

create table public.order_lines (
  id                 uuid primary key default gen_random_uuid(),
  tenant_id          uuid not null references public.tenants (id) on delete cascade,
  order_id           uuid not null,
  line_number        integer not null,        -- 1,2,3... stable, shown to buyer
  variant_id         uuid not null,
  quantity_ordered   integer not null,
  quantity_allocated integer not null default 0,
  quantity_shipped   integer not null default 0,
  quantity_cancelled integer not null default 0,
  quantity_invoiced  integer not null default 0,
  unit_price         numeric(18,4) not null,  -- resolved and FROZEN at entry
  discount_percent   numeric(6,3) not null default 0,
  tax_rate           numeric(6,4) not null default 0,
  extended_amount    numeric(18,4) generated always as (
                       round((quantity_ordered - quantity_cancelled)::numeric
                             * unit_price * (1 - discount_percent / 100), 4)
                     ) stored,
  requested_ship_on  date,
  status             text not null default 'open',
  note               text,
  created_at         timestamptz not null default now(),
  updated_at         timestamptz not null default now(),

  constraint order_lines_tenant_row_uk unique (tenant_id, id),
  constraint order_lines_number_uk unique (tenant_id, order_id, line_number),
  -- One line per SKU per order: two lines for the same SKU make every
  -- downstream sum ambiguous and every allocation race unresolvable.
  constraint order_lines_variant_uk unique (tenant_id, order_id, variant_id),
  constraint order_lines_status_chk
    check (status in ('open','allocated','shipped','cancelled','closed')),
  constraint order_lines_qty_ordered_chk check (quantity_ordered > 0),
  constraint order_lines_qty_sign_chk
    check (quantity_allocated >= 0 and quantity_shipped >= 0
           and quantity_cancelled >= 0 and quantity_invoiced >= 0),
  -- The core line invariant: you cannot commit, ship, or cancel more than
  -- was ordered, in any combination.
  constraint order_lines_qty_balance_chk
    check (quantity_allocated + quantity_shipped + quantity_cancelled
           <= quantity_ordered),
  constraint order_lines_invoiced_chk
    check (quantity_invoiced <= quantity_shipped),
  constraint order_lines_price_chk check (unit_price >= 0),
  constraint order_lines_discount_chk
    check (discount_percent >= 0 and discount_percent < 100),
  constraint order_lines_tax_chk check (tax_rate >= 0 and tax_rate < 1),
  constraint order_lines_order_fk
    foreign key (tenant_id, order_id)
    references public.orders (tenant_id, id) on delete cascade,
  constraint order_lines_variant_fk
    foreign key (tenant_id, variant_id)
    references public.variants (tenant_id, id) on delete restrict
);

-- Demand by SKU: the ATS query and the open order book both read this.
create index order_lines_variant_open_idx
  on public.order_lines (tenant_id, variant_id, requested_ship_on)
  where status in ('open','allocated');
-- Loading an order with its lines.
create index order_lines_order_idx on public.order_lines (tenant_id, order_id);

select app.enable_tenant_rls('public.order_lines');

In plain English: one row per SKU on the order, with four quantities that move as the order progresses. The balance check is the one to internalize: allocated plus shipped plus canceled can never exceed ordered, at any instant. unit_price is copied in at order entry and never re-derived, so re-pricing a price list next season cannot rewrite an order somebody already signed. extended_amount is computed by the database from the quantities and the discount, so it can never disagree with them.

order_revisions

An append-only record of every change to an order after submission: quantity increased, ship window moved, line canceled. A row is written by the same transaction that makes the change. When a buyer says "we never agreed to 400 units", this table has the before, the after, who did it, and when.

create table public.order_revisions (
  id             uuid primary key default gen_random_uuid(),
  tenant_id      uuid not null references public.tenants (id) on delete cascade,
  order_id       uuid not null,
  revision       integer not null,     -- 1, 2, 3... matches orders.revision
  change_kind    text not null,
  change_reason  text,
  before_data    jsonb not null,       -- the order + lines before the change
  after_data     jsonb not null,       -- and after
  changed_by     uuid references public.users (id) on delete set null,
  source         text not null default 'manual',
  created_at     timestamptz not null default now(),

  constraint order_revisions_tenant_row_uk unique (tenant_id, id),
  constraint order_revisions_uk unique (tenant_id, order_id, revision),
  constraint order_revisions_revision_chk check (revision > 0),
  constraint order_revisions_kind_chk
    check (change_kind in ('created','quantity','price','dates','address',
                           'lines_added','lines_removed','cancelled','other')),
  constraint order_revisions_order_fk
    foreign key (tenant_id, order_id)
    references public.orders (tenant_id, id) on delete cascade
);

create index order_revisions_order_idx
  on public.order_revisions (tenant_id, order_id, revision desc);

alter table public.order_revisions enable row level security;
alter table public.order_revisions force  row level security;

create policy order_revisions_select on public.order_revisions
  for select to authenticated
  using (tenant_id = app.current_tenant_id());

create policy order_revisions_insert on public.order_revisions
  for insert to authenticated
  with check (tenant_id = app.current_tenant_id());

grant select, insert on public.order_revisions to authenticated;

create trigger order_revisions_append_only
  before update or delete on public.order_revisions
  for each row execute function app.deny_mutation();

In plain English: this is a diary of changes, written in the same transaction that makes each change, holding the whole order before and after as JSON. Nothing may update or delete it: there is no update policy, no delete policy, and a trigger that blocks both regardless. When a buyer disputes what they agreed to, this table is the evidence.

allocations

A hard commitment: these 12 units in DC-LA belong to this order line. Created by the allocation run (nightly, or on demand when an order is confirmed), released when an order is canceled, and consumed when a shipment ships. Each row's existence must be mirrored by inventory_balances.allocated in the same transaction — that pairing is invariant #3 in 11.13.

create table public.allocations (
  id             uuid primary key default gen_random_uuid(),
  tenant_id      uuid not null references public.tenants (id) on delete cascade,
  order_line_id  uuid not null,
  variant_id     uuid not null,      -- denormalised for fast rollups
  warehouse_id   uuid not null,
  quantity       integer not null,
  status         text not null default 'active',
  allocated_at   timestamptz not null default now(),
  released_at    timestamptz,
  shipped_at     timestamptz,
  allocated_by   uuid references public.users (id) on delete set null,
  created_at     timestamptz not null default now(),
  updated_at     timestamptz not null default now(),

  constraint allocations_tenant_row_uk unique (tenant_id, id),
  constraint allocations_qty_chk check (quantity > 0),
  constraint allocations_status_chk
    check (status in ('active','released','shipped')),
  constraint allocations_released_chk
    check ((status = 'released') = (released_at is not null)),
  constraint allocations_shipped_chk
    check ((status = 'shipped') = (shipped_at is not null)),
  constraint allocations_line_fk
    foreign key (tenant_id, order_line_id)
    references public.order_lines (tenant_id, id) on delete cascade,
  constraint allocations_variant_fk
    foreign key (tenant_id, variant_id)
    references public.variants (tenant_id, id) on delete restrict,
  constraint allocations_warehouse_fk
    foreign key (tenant_id, warehouse_id)
    references public.warehouses (tenant_id, id) on delete restrict
);

-- One active allocation per line per warehouse keeps the arithmetic simple:
-- to change a quantity you update the row, you do not add a second one.
create unique index allocations_active_uk
  on public.allocations (tenant_id, order_line_id, warehouse_id)
  where status = 'active';

-- "How much of this SKU is committed here?" -- the balance reconciliation.
create index allocations_position_idx
  on public.allocations (tenant_id, variant_id, warehouse_id)
  where status = 'active';
create index allocations_line_idx on public.allocations (tenant_id, order_line_id);

select app.enable_tenant_rls('public.allocations');

In plain English: one row says "these units of this SKU, in this warehouse, belong to this order line". The partial unique index allows only one active allocation per line per warehouse, so changing a quantity means updating a row rather than adding a second one and hoping everybody remembers to add them up. The two correlation checks force the timestamps to agree with the status.

reservations

A soft, expiring hold. A B2B cart holds stock for 20 minutes while a buyer finishes checkout; a showroom quote holds a size run for 48 hours. Rows are created by the cart or quote flow and are swept by a background job once expires_at passes, which decrements inventory_balances.reserved. Unlike allocations, a reservation may reference no order line at all — the order does not exist yet.

create table public.reservations (
  id             uuid primary key default gen_random_uuid(),
  tenant_id      uuid not null references public.tenants (id) on delete cascade,
  variant_id     uuid not null,
  warehouse_id   uuid not null,
  quantity       integer not null,
  reserved_for   text not null,        -- 'cart','quote','sample','edi_pending'
  reference_id   uuid,                 -- cart id / quote id, app-defined
  order_line_id  uuid,                 -- set once the hold becomes an order
  expires_at     timestamptz not null, -- REQUIRED: holds must die on their own
  released_at    timestamptz,
  status         text not null default 'active',
  created_by     uuid references public.users (id) on delete set null,
  created_at     timestamptz not null default now(),
  updated_at     timestamptz not null default now(),

  constraint reservations_tenant_row_uk unique (tenant_id, id),
  constraint reservations_qty_chk check (quantity > 0),
  constraint reservations_status_chk
    check (status in ('active','converted','released','expired')),
  constraint reservations_for_chk
    check (reserved_for in ('cart','quote','sample','edi_pending','manual')),
  constraint reservations_variant_fk
    foreign key (tenant_id, variant_id)
    references public.variants (tenant_id, id) on delete restrict,
  constraint reservations_warehouse_fk
    foreign key (tenant_id, warehouse_id)
    references public.warehouses (tenant_id, id) on delete restrict,
  constraint reservations_line_fk
    foreign key (tenant_id, order_line_id)
    references public.order_lines (tenant_id, id) on delete cascade
);

-- The expiry sweeper: find live holds that have timed out, cheaply, every
-- minute, forever. Without the partial predicate this scan grows without bound.
create index reservations_expiry_idx
  on public.reservations (expires_at)
  where status = 'active';

create index reservations_position_idx
  on public.reservations (tenant_id, variant_id, warehouse_id)
  where status = 'active';

select app.enable_tenant_rls('public.reservations');

In plain English: a reservation is a hold with an expiry date, and expires_at is compulsory so a forgotten hold cannot lock stock away forever. A sweeper job runs against the expiry index every minute and releases whatever has timed out. Unlike an allocation, a reservation may point at no order line at all, because at that moment the order does not exist.

11.6 Purchasing

Procure-to-pay, the mirror image of section 11.5. You issue a purchase order (PO) to a factory; months later an inbound shipment (an ASN — Advance Ship Notice, the supplier's "it is on the water" message) tells you what is coming and when; when the container is unloaded you post a receipt, and only the receipt writes to the ledger.

  +-------------+        +-------------------+
  |  suppliers  |---1:N--|  purchase_orders  |
  +-------------+        +-------------------+
                                  |
                                  | 1:N
                                  v
                          +-----------------+
                          |    po_lines     |<-----------+
                          +-----------------+            |
                                  ^                      |
                                  |                      |
  +----------------------+        |            +------------------+
  |  inbound_shipments   |        |            |  receipt_lines   |
  |   (ASN / container)  |        |            +------------------+
  +----------------------+        |                      ^
             |                    |                      |
             | 1:N                |                      | 1:N
             v                    |                      |
  +--------------------------+    |            +------------------+
  | inbound_shipment_lines   |----+            |    receipts      |
  +--------------------------+                 +------------------+
                                                        |
                                                        v
                                        inventory_transactions (+receipt)

  Every level is optional going down but anchored going up: a receipt can
  exist without an ASN (a walk-in delivery), and a receipt line can exist
  without a PO line (an unexpected overship) -- but a receipt line always
  names a variant, a warehouse, and a quantity, because those three facts
  are what the ledger needs.

purchase_orders

Your commitment to buy from a supplier. A row is created when planning approves a buy, and it is the document the factory works from. expected_ship_on versus expected_arrival_on is the difference between ex-factory (the day the goods leave the factory gate) and landed (the day they are unloaded at your warehouse, customs cleared). For ocean freight out of Asia that gap is measured in weeks rather than days, and it swings widely with the lane, the port, holidays and customs clearance, which is exactly why both dates are stored and neither is calculated from the other.

create table public.purchase_orders (
  id                      uuid primary key default gen_random_uuid(),
  tenant_id               uuid not null references public.tenants (id) on delete cascade,
  po_number               text not null,     -- 'PO-2027-0088'
  supplier_id             uuid not null,
  destination_warehouse_id uuid not null,    -- where the goods will land
  status                  text not null default 'draft',
  currency                app.currency_code not null default 'USD',
  incoterm                text,              -- 'FOB Ho Chi Minh'
  order_date              date not null default current_date,
  expected_ship_on        date,              -- ex-factory date
  expected_arrival_on     date,              -- landed at the warehouse
  subtotal_amount         numeric(18,4) not null default 0,
  freight_amount          numeric(18,4) not null default 0,
  duty_amount             numeric(18,4) not null default 0,
  total_amount            numeric(18,4) not null default 0,
  payment_terms_id        uuid,
  approved_by             uuid references public.users (id) on delete set null,
  approved_at             timestamptz,
  closed_at               timestamptz,
  notes                   text,
  created_by              uuid references public.users (id) on delete set null,
  created_at              timestamptz not null default now(),
  updated_at              timestamptz not null default now(),

  constraint purchase_orders_tenant_row_uk unique (tenant_id, id),
  constraint purchase_orders_number_uk unique (tenant_id, po_number),
  constraint purchase_orders_status_chk
    check (status in ('draft','issued','acknowledged','in_production','shipped',
                      'partially_received','received','closed','cancelled')),
  constraint purchase_orders_amounts_chk
    check (subtotal_amount >= 0 and freight_amount >= 0
           and duty_amount >= 0 and total_amount >= 0),
  constraint purchase_orders_dates_chk
    check (expected_arrival_on is null or expected_ship_on is null
           or expected_arrival_on >= expected_ship_on),
  -- Nothing may be issued to a supplier without a recorded approval.
  constraint purchase_orders_approval_chk
    check (status in ('draft','cancelled') or approved_at is not null),
  constraint purchase_orders_supplier_fk
    foreign key (tenant_id, supplier_id)
    references public.suppliers (tenant_id, id) on delete restrict,
  constraint purchase_orders_warehouse_fk
    foreign key (tenant_id, destination_warehouse_id)
    references public.warehouses (tenant_id, id) on delete restrict,
  constraint purchase_orders_terms_fk
    foreign key (tenant_id, payment_terms_id)
    references public.payment_terms (tenant_id, id) on delete restrict
);

-- The inbound pipeline view: what is arriving, when.
create index purchase_orders_open_idx
  on public.purchase_orders (tenant_id, expected_arrival_on, supplier_id)
  where status in ('issued','acknowledged','in_production','shipped','partially_received');
create index purchase_orders_supplier_idx
  on public.purchase_orders (tenant_id, supplier_id, order_date desc);

select app.enable_tenant_rls('public.purchase_orders');

In plain English: the header carries the supplier, the destination warehouse, the currency, the two dates, and the costs. The approval check refuses to let a PO leave draft without a recorded approval, so nothing reaches a factory that nobody signed off. The partial index covers only POs still in flight, which is what the inbound pipeline screen shows.

po_lines

One row per SKU on the PO, with its own expected dates because factories routinely split a PO across two shipments. quantity_received accumulates across every receipt against the line, which is what drives the partially_received status.

create table public.po_lines (
  id                  uuid primary key default gen_random_uuid(),
  tenant_id           uuid not null references public.tenants (id) on delete cascade,
  purchase_order_id   uuid not null,
  line_number         integer not null,
  variant_id          uuid not null,
  quantity_ordered    integer not null,
  quantity_received   integer not null default 0,
  quantity_cancelled  integer not null default 0,
  unit_cost           numeric(18,4) not null,   -- FOB cost, frozen at issue
  extended_cost       numeric(18,4) generated always as
                        (round((quantity_ordered - quantity_cancelled)::numeric
                               * unit_cost, 4)) stored,
  expected_ship_on    date,
  expected_arrival_on date,
  status              text not null default 'open',
  created_at          timestamptz not null default now(),
  updated_at          timestamptz not null default now(),

  constraint po_lines_tenant_row_uk unique (tenant_id, id),
  constraint po_lines_number_uk unique (tenant_id, purchase_order_id, line_number),
  constraint po_lines_variant_uk unique (tenant_id, purchase_order_id, variant_id),
  constraint po_lines_status_chk
    check (status in ('open','partially_received','received','cancelled','closed')),
  constraint po_lines_qty_chk
    check (quantity_ordered > 0 and quantity_received >= 0
           and quantity_cancelled >= 0),
  constraint po_lines_cost_chk check (unit_cost >= 0),
  -- Factories routinely ship a little over or under the ordered quantity,
  -- and the tolerance is negotiated per PO, so we do NOT cap received at
  -- ordered. We do cap what may be cancelled.
  constraint po_lines_cancel_chk
    check (quantity_cancelled <= quantity_ordered),
  constraint po_lines_po_fk
    foreign key (tenant_id, purchase_order_id)
    references public.purchase_orders (tenant_id, id) on delete cascade,
  constraint po_lines_variant_fk
    foreign key (tenant_id, variant_id)
    references public.variants (tenant_id, id) on delete restrict
);

-- Incoming supply by SKU: the second half of the ATS query.
create index po_lines_variant_open_idx
  on public.po_lines (tenant_id, variant_id, expected_arrival_on)
  where status in ('open','partially_received');
create index po_lines_po_idx on public.po_lines (tenant_id, purchase_order_id);

select app.enable_tenant_rls('public.po_lines');

In plain English: one row per SKU, with its own dates because factories split a PO across shipments. Received is deliberately not capped at ordered, since a factory shipping slightly over is normal. Canceled is capped, because you cannot cancel more than you asked for. extended_cost is computed by the database.

inbound_shipments

A container, air freight consignment, or parcel on its way to you — created from a supplier's ASN, an EDI 856, or by hand when a factory emails a packing list. It may consolidate several POs, which is why the PO link lives on the lines, not the header.

create table public.inbound_shipments (
  id                 uuid primary key default gen_random_uuid(),
  tenant_id          uuid not null references public.tenants (id) on delete cascade,
  asn_number         text not null,      -- supplier's shipment reference
  supplier_id        uuid not null,
  destination_warehouse_id uuid not null,
  status             text not null default 'expected',
  carrier            text,
  container_number   text,               -- 'MSCU1234567'
  bill_of_lading     text,
  vessel_name        text,
  etd_on             date,               -- estimated time of departure
  eta_on             date,               -- estimated time of arrival
  arrived_at         timestamptz,
  carton_count       integer,
  total_weight_kg    numeric(12,3),
  customs_entry_number text,
  external_id        text,               -- EDI 856 control number
  created_at         timestamptz not null default now(),
  updated_at         timestamptz not null default now(),

  constraint inbound_shipments_tenant_row_uk unique (tenant_id, id),
  constraint inbound_shipments_asn_uk unique (tenant_id, supplier_id, asn_number),
  constraint inbound_shipments_status_chk
    check (status in ('expected','in_transit','arrived','received','cancelled')),
  constraint inbound_shipments_dates_chk
    check (eta_on is null or etd_on is null or eta_on >= etd_on),
  constraint inbound_shipments_cartons_chk
    check (carton_count is null or carton_count > 0),
  constraint inbound_shipments_supplier_fk
    foreign key (tenant_id, supplier_id)
    references public.suppliers (tenant_id, id) on delete restrict,
  constraint inbound_shipments_warehouse_fk
    foreign key (tenant_id, destination_warehouse_id)
    references public.warehouses (tenant_id, id) on delete restrict
);

-- The "what is on the water" board, sorted by arrival.
create index inbound_shipments_eta_idx
  on public.inbound_shipments (tenant_id, eta_on)
  where status in ('expected','in_transit');

select app.enable_tenant_rls('public.inbound_shipments');

In plain English: this is the container, with the vessel, the bill of lading, the dates, and the customs entry number. It is created from the supplier's ASN or typed in from an emailed packing list. One shipment can carry goods from several POs, which is why the link to a PO lives on the lines below rather than up here.

inbound_shipment_lines

What the supplier says is in the container, per SKU, linked back to the PO line it satisfies. The gap between this and the matching receipt line is the shortage claim.

create table public.inbound_shipment_lines (
  id                  uuid primary key default gen_random_uuid(),
  tenant_id           uuid not null references public.tenants (id) on delete cascade,
  inbound_shipment_id uuid not null,
  po_line_id          uuid,             -- null for unexpected goods
  variant_id          uuid not null,
  quantity_expected   integer not null,
  carton_count        integer,
  created_at          timestamptz not null default now(),
  updated_at          timestamptz not null default now(),

  constraint inbound_shipment_lines_tenant_row_uk unique (tenant_id, id),
  constraint inbound_shipment_lines_uk
    unique (tenant_id, inbound_shipment_id, variant_id, po_line_id),
  constraint inbound_shipment_lines_qty_chk check (quantity_expected > 0),
  constraint inbound_shipment_lines_shipment_fk
    foreign key (tenant_id, inbound_shipment_id)
    references public.inbound_shipments (tenant_id, id) on delete cascade,
  constraint inbound_shipment_lines_po_line_fk
    foreign key (tenant_id, po_line_id)
    references public.po_lines (tenant_id, id) on delete restrict,
  constraint inbound_shipment_lines_variant_fk
    foreign key (tenant_id, variant_id)
    references public.variants (tenant_id, id) on delete restrict
);

create index inbound_shipment_lines_variant_idx
  on public.inbound_shipment_lines (tenant_id, variant_id);

select app.enable_tenant_rls('public.inbound_shipment_lines');

In plain English: what the supplier claims is in the box, per SKU, with an optional pointer at the PO line it satisfies. The pointer is optional because containers do turn up with goods nobody expected. Comparing these numbers against the receipt lines later is how a shortage claim gets built.

receipts

The document that says "we physically took these goods into this warehouse at this time". Created when the container is unloaded and counted. Posting a receipt is the only way goods enter the ledger with type receipt, and the idempotency_key makes a double-tap on the Post button harmless.

create table public.receipts (
  id                  uuid primary key default gen_random_uuid(),
  tenant_id           uuid not null references public.tenants (id) on delete cascade,
  receipt_number      text not null,
  warehouse_id        uuid not null,
  supplier_id         uuid,
  purchase_order_id   uuid,
  inbound_shipment_id uuid,
  status              text not null default 'draft',
  received_at         timestamptz not null default now(),
  posted_at           timestamptz,
  received_by         uuid references public.users (id) on delete set null,
  idempotency_key     text not null,
  notes               text,
  created_at          timestamptz not null default now(),
  updated_at          timestamptz not null default now(),

  constraint receipts_tenant_row_uk unique (tenant_id, id),
  constraint receipts_number_uk unique (tenant_id, receipt_number),
  constraint receipts_idem_uk unique (tenant_id, idempotency_key),
  constraint receipts_status_chk check (status in ('draft','posted','cancelled')),
  constraint receipts_posted_chk
    check ((status = 'posted') = (posted_at is not null)),
  constraint receipts_warehouse_fk
    foreign key (tenant_id, warehouse_id)
    references public.warehouses (tenant_id, id) on delete restrict,
  constraint receipts_supplier_fk
    foreign key (tenant_id, supplier_id)
    references public.suppliers (tenant_id, id) on delete restrict,
  constraint receipts_po_fk
    foreign key (tenant_id, purchase_order_id)
    references public.purchase_orders (tenant_id, id) on delete restrict,
  constraint receipts_inbound_fk
    foreign key (tenant_id, inbound_shipment_id)
    references public.inbound_shipments (tenant_id, id) on delete restrict
);

create index receipts_warehouse_idx
  on public.receipts (tenant_id, warehouse_id, received_at desc);
create index receipts_po_idx
  on public.receipts (tenant_id, purchase_order_id)
  where purchase_order_id is not null;

select app.enable_tenant_rls('public.receipts');

In plain English: the receipt is the moment stock becomes yours and appears in the ledger. It can hang off a PO, an inbound shipment, both, or neither — a supplier who walks a box in has none. The idempotency key means a double-tapped Post button posts exactly once.

receipt_lines

What was actually in the box, split three ways: good units, damaged units, and rejected units. Only quantity_received becomes sellable stock; damaged units post to a quarantine warehouse, and rejected units post nowhere and become a supplier claim.

create table public.receipt_lines (
  id                 uuid primary key default gen_random_uuid(),
  tenant_id          uuid not null references public.tenants (id) on delete cascade,
  receipt_id         uuid not null,
  po_line_id         uuid,
  variant_id         uuid not null,
  quantity_received  integer not null,
  quantity_damaged   integer not null default 0,
  quantity_rejected  integer not null default 0,
  unit_cost          numeric(18,4) not null,  -- landed cost for valuation
  lot_code           text,
  bin_location       text,
  created_at         timestamptz not null default now(),
  updated_at         timestamptz not null default now(),

  constraint receipt_lines_tenant_row_uk unique (tenant_id, id),
  constraint receipt_lines_qty_chk
    check (quantity_received >= 0 and quantity_damaged >= 0
           and quantity_rejected >= 0),
  -- A line that records nothing at all is a data-entry slip.
  constraint receipt_lines_nonzero_chk
    check (quantity_received + quantity_damaged + quantity_rejected > 0),
  constraint receipt_lines_cost_chk check (unit_cost >= 0),
  constraint receipt_lines_receipt_fk
    foreign key (tenant_id, receipt_id)
    references public.receipts (tenant_id, id) on delete cascade,
  constraint receipt_lines_po_line_fk
    foreign key (tenant_id, po_line_id)
    references public.po_lines (tenant_id, id) on delete restrict,
  constraint receipt_lines_variant_fk
    foreign key (tenant_id, variant_id)
    references public.variants (tenant_id, id) on delete restrict
);

create index receipt_lines_receipt_idx on public.receipt_lines (tenant_id, receipt_id);
-- Rolls quantity_received back up to the PO line for status calculation.
create index receipt_lines_po_line_idx
  on public.receipt_lines (tenant_id, po_line_id)
  where po_line_id is not null;

select app.enable_tenant_rls('public.receipt_lines');

In plain English: what was really in the box, split three ways. Good units become sellable stock. Damaged units are still yours, so they post to a quarantine warehouse where is_sellable is false. Rejected units are refused at the door, post nowhere at all, and become a claim against the supplier. The non-zero check refuses a line that records none of the three.

11.7 Fulfillment

Goods leaving the building:

• A shipment is one physical dispatch from one warehouse to one address;
• shipment lines say which order lines it satisfies and in what quantity;
• cartons are the boxes, each with an SSCC label a retailer's dock scans;
• trackings are the carrier's parcel numbers and their scan events.

One order can produce many shipments (a split shipment), and one shipment can never span two orders in this schema — a deliberate simplification that makes invoicing sane.

  +----------+       +-------------+       +--------------------+
  |  orders  |--1:N--|  shipments  |--1:N--| shipment_trackings |
  +----------+       +-------------+       +--------------------+
       |                    |
       |                    | 1:N
       | 1:N                v
       v             +-------------+
 +--------------+    |   cartons   |
 | order_lines  |    +-------------+
 +--------------+           |
       |                    | carton_id (nullable)
       | 1:N                |
       v                    v
 +----------------------------------+
 |         shipment_lines           |---> inventory_transactions (-shipment)
 +----------------------------------+

  A shipment line points UP at the order line it fulfils and SIDEWAYS at
  the carton it was packed in. Both links matter: the first drives billing
  and back-order arithmetic, the second drives the packing list and the
  retailer's compliance rules.

shipments

One dispatch. Created when a pick is released, and moved through picking and packing to shipped — the moment that posts negative ledger rows, decrements allocations, and (usually) triggers invoicing.

create table public.shipments (
  id                   uuid primary key default gen_random_uuid(),
  tenant_id            uuid not null references public.tenants (id) on delete cascade,
  shipment_number      text not null,
  order_id             uuid not null,
  warehouse_id         uuid not null,
  ship_to_location_id  uuid,
  status               text not null default 'draft',
  carrier              text,              -- 'UPS'
  service_level        text,              -- 'Ground'
  ship_date            date,
  shipped_at           timestamptz,
  delivered_at         timestamptz,
  freight_amount       numeric(18,4) not null default 0,
  freight_terms        text,              -- 'prepaid' | 'collect' | 'third_party'
  bol_number           text,
  packing_slip_url     text,
  external_id          text,              -- 3PL or EDI 945 reference
  idempotency_key      text not null,
  created_by           uuid references public.users (id) on delete set null,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now(),

  constraint shipments_tenant_row_uk unique (tenant_id, id),
  constraint shipments_number_uk unique (tenant_id, shipment_number),
  constraint shipments_idem_uk unique (tenant_id, idempotency_key),
  constraint shipments_status_chk
    check (status in ('draft','picking','packed','shipped','delivered','cancelled')),
  constraint shipments_freight_terms_chk
    check (freight_terms is null
           or freight_terms in ('prepaid','collect','third_party')),
  constraint shipments_freight_chk check (freight_amount >= 0),
  -- Shipped means shipped: the timestamp is not optional.
  constraint shipments_shipped_chk
    check (status not in ('shipped','delivered') or shipped_at is not null),
  constraint shipments_delivered_chk
    check (status <> 'delivered' or delivered_at is not null),
  constraint shipments_order_fk
    foreign key (tenant_id, order_id)
    references public.orders (tenant_id, id) on delete restrict,
  constraint shipments_warehouse_fk
    foreign key (tenant_id, warehouse_id)
    references public.warehouses (tenant_id, id) on delete restrict,
  constraint shipments_location_fk
    foreign key (tenant_id, ship_to_location_id)
    references public.customer_locations (tenant_id, id) on delete restrict
);

create index shipments_order_idx on public.shipments (tenant_id, order_id);
-- The warehouse work queue.
create index shipments_open_idx
  on public.shipments (tenant_id, warehouse_id, status)
  where status in ('draft','picking','packed');
-- Shipping-activity reporting and the invoice-generation sweep.
create index shipments_shipped_idx
  on public.shipments (tenant_id, ship_date desc)
  where status in ('shipped','delivered');

select app.enable_tenant_rls('public.shipments');

In plain English: one shipment is one dispatch, from one warehouse, against one order. The status walks from draft through picking and packed to shipped, and shipped is the moment negative ledger rows get written. The checks refuse a shipped row with no shipped-at time and a delivered row with no delivered-at time. The idempotency key protects the posting step.

cartons

A physical box. Retail compliance programs require one SSCC-18 (Serial Shipping Container Code) label per carton and an EDI 856 that describes exactly what is in each one; getting this wrong is what generates chargebacks. Created during packing.

create table public.cartons (
  id             uuid primary key default gen_random_uuid(),
  tenant_id      uuid not null references public.tenants (id) on delete cascade,
  shipment_id    uuid not null,
  carton_number  integer not null,       -- 1..n within the shipment
  sscc           text,                   -- 18-digit GS1 pallet/carton code
  weight_kg      numeric(10,3),
  length_cm      numeric(10,2),
  width_cm       numeric(10,2),
  height_cm      numeric(10,2),
  tracking_number text,
  created_at     timestamptz not null default now(),
  updated_at     timestamptz not null default now(),

  constraint cartons_tenant_row_uk unique (tenant_id, id),
  constraint cartons_number_uk unique (tenant_id, shipment_id, carton_number),
  constraint cartons_sscc_uk unique (tenant_id, sscc),
  constraint cartons_sscc_chk check (sscc is null or sscc ~ '^[0-9]{18}$'),
  constraint cartons_number_positive_chk check (carton_number > 0),
  constraint cartons_weight_chk check (weight_kg is null or weight_kg > 0),
  constraint cartons_shipment_fk
    foreign key (tenant_id, shipment_id)
    references public.shipments (tenant_id, id) on delete cascade
);

create index cartons_shipment_idx on public.cartons (tenant_id, shipment_id);

select app.enable_tenant_rls('public.cartons');

In plain English: one row per box. The SSCC is the 18-digit serial number on the carton label that the retailer's dock scans, it must be unique across the tenant, and the check enforces the digit count. Weight and dimensions are here because the carrier's rate and the retailer's compliance rules both depend on them.

shipment_lines

The unit of truth for "what actually went out". One row per (order line, carton) pair, so a line split across three cartons is three rows, which is exactly what an EDI 856 pack structure needs. Posting the shipment turns each row into one negative ledger entry.

create table public.shipment_lines (
  id              uuid primary key default gen_random_uuid(),
  tenant_id       uuid not null references public.tenants (id) on delete cascade,
  shipment_id     uuid not null,
  order_line_id   uuid not null,
  variant_id      uuid not null,      -- must match the order line's variant
  carton_id       uuid,               -- null until packed
  quantity        integer not null,
  created_at      timestamptz not null default now(),
  updated_at      timestamptz not null default now(),

  constraint shipment_lines_tenant_row_uk unique (tenant_id, id),
  constraint shipment_lines_qty_chk check (quantity > 0),
  constraint shipment_lines_shipment_fk
    foreign key (tenant_id, shipment_id)
    references public.shipments (tenant_id, id) on delete cascade,
  constraint shipment_lines_order_line_fk
    foreign key (tenant_id, order_line_id)
    references public.order_lines (tenant_id, id) on delete restrict,
  constraint shipment_lines_variant_fk
    foreign key (tenant_id, variant_id)
    references public.variants (tenant_id, id) on delete restrict,
  -- RESTRICT, not SET NULL: see the note below.
  constraint shipment_lines_carton_fk
    foreign key (tenant_id, carton_id)
    references public.cartons (tenant_id, id) on delete restrict
);

create index shipment_lines_shipment_idx
  on public.shipment_lines (tenant_id, shipment_id);
-- Rolls shipped quantity back up to the order line.
create index shipment_lines_order_line_idx
  on public.shipment_lines (tenant_id, order_line_id);
create index shipment_lines_carton_idx
  on public.shipment_lines (tenant_id, carton_id)
  where carton_id is not null;

select app.enable_tenant_rls('public.shipment_lines');

The carton link is the one place in the schema where on delete set null is tempting — "the carton was scrapped, so unpack the line", and it is exactly the trap from the earlier warning. Because the key is (tenant_id, carton_id), SET NULL would try to null tenant_id too, and the delete would fail with a baffling not-null violation.

So the constraint is restrict: to remove a carton you must first unpack its lines with an explicit update shipment_lines set carton_id = null, which is honest work that leaves an audit trail. Cartons still disappear automatically when their shipment does, by cascade from cartons_shipment_fk.

shipment_trackings

Carrier tracking numbers and their scan history. One shipment can have many (one per parcel), and the events array grows as webhooks arrive from the carrier.

create table public.shipment_trackings (
  id               uuid primary key default gen_random_uuid(),
  tenant_id        uuid not null references public.tenants (id) on delete cascade,
  shipment_id      uuid not null,
  carrier          text not null,
  tracking_number  text not null,
  status           text not null default 'pending',
  events           jsonb not null default '[]'::jsonb,  -- raw carrier scans
  last_event_at    timestamptz,
  delivered_at     timestamptz,
  tracking_url     text,
  created_at       timestamptz not null default now(),
  updated_at       timestamptz not null default now(),

  constraint shipment_trackings_tenant_row_uk unique (tenant_id, id),
  constraint shipment_trackings_number_uk
    unique (tenant_id, carrier, tracking_number),
  constraint shipment_trackings_status_chk
    check (status in ('pending','in_transit','out_for_delivery',
                      'delivered','exception','returned')),
  constraint shipment_trackings_events_chk
    check (jsonb_typeof(events) = 'array'),
  constraint shipment_trackings_shipment_fk
    foreign key (tenant_id, shipment_id)
    references public.shipments (tenant_id, id) on delete cascade
);

create index shipment_trackings_shipment_idx
  on public.shipment_trackings (tenant_id, shipment_id);
-- The polling job: which parcels are still moving?
create index shipment_trackings_open_idx
  on public.shipment_trackings (tenant_id, status, last_event_at)
  where status in ('pending','in_transit','out_for_delivery','exception');

select app.enable_tenant_rls('public.shipment_trackings');

In plain English: one row per parcel number, with the carrier's raw scan events kept in a JSON array exactly as they arrived. The check makes sure that column really holds an array and not an object somebody wrote by mistake. The partial index feeds the polling job with only the parcels that are still moving.

11.8 Financial

Where order-to-cash ends:

• An invoice is a demand for payment;
• a payment is money that arrived;
• payment applications connect the two, many-to-many, because a retailer pays fourteen invoices with a single bank transfer (an ACH, the standard US bank-to-bank electronic payment);
• credit memos are money you owe back;
• and deductions (chargebacks) are money the customer took without asking — the defining feature of wholesale apparel and the reason your receivable never matches their remittance.

  +-------------+                          +------------+
  |  shipments  |---1:1 (usually)--->      |  invoices  |
  +-------------+                          +------------+
                                            |    ^    ^
                                       1:N  |    |    |
                                            v    |    |
                                   +---------------+  |
                                   | invoice_lines |  |
                                   +---------------+  |
                                                      |
  +------------+      +----------------------+        |
  |  payments  |--1:N-| payment_applications |--------+
  +------------+      +----------------------+        |
        ^                                             |
        |                +----------------+           |
        +----------------|   deductions   |-----------+
        (taken from a    | (chargebacks)  |
         payment)        +----------------+
                         +----------------+
                         |  credit_memos  |-----------+
                         +----------------+
                          (applied to invoices too)

  Read the arrows into `invoices` as "things that reduce the balance".
  balance_due = total - amount_paid - amount_credited, and every one of
  those reducers is a row somewhere you can point at.

invoices

A demand for payment, normally generated automatically the moment a shipment ships. It is immutable once open — corrections happen through credit memos, never by editing the invoice, because the customer already has the PDF and their accounts-payable system already keyed it.

create table public.invoices (
  id                uuid primary key default gen_random_uuid(),
  tenant_id         uuid not null references public.tenants (id) on delete cascade,
  invoice_number    text not null,
  customer_id       uuid not null,
  order_id          uuid,
  shipment_id       uuid,
  status            text not null default 'draft',
  issue_date        date not null default current_date,
  due_date          date not null,          -- issue_date + terms.net_days
  payment_terms_id  uuid,
  currency          app.currency_code not null default 'USD',
  subtotal_amount   numeric(18,4) not null default 0,
  discount_amount   numeric(18,4) not null default 0,
  tax_amount        numeric(18,4) not null default 0,
  freight_amount    numeric(18,4) not null default 0,
  total_amount      numeric(18,4) not null default 0,
  amount_paid       numeric(18,4) not null default 0,   -- sum of applications
  amount_credited   numeric(18,4) not null default 0,   -- memos + deductions
  balance_due       numeric(18,4) generated always as
                      (total_amount - amount_paid - amount_credited) stored,
  external_id       text,                   -- QuickBooks / NetSuite id
  synced_at         timestamptz,
  posted_at         timestamptz,
  voided_at         timestamptz,
  pdf_url           text,
  version           integer not null default 1,
  created_at        timestamptz not null default now(),
  updated_at        timestamptz not null default now(),

  constraint invoices_tenant_row_uk unique (tenant_id, id),
  constraint invoices_number_uk unique (tenant_id, invoice_number),
  constraint invoices_status_chk
    check (status in ('draft','open','partially_paid','paid','void','written_off')),
  constraint invoices_amounts_chk
    check (subtotal_amount >= 0 and discount_amount >= 0 and tax_amount >= 0
           and freight_amount >= 0 and total_amount >= 0
           and amount_paid >= 0 and amount_credited >= 0),
  -- You cannot collect more than you billed.
  constraint invoices_overpay_chk
    check (amount_paid + amount_credited <= total_amount),
  constraint invoices_due_chk check (due_date >= issue_date),
  constraint invoices_void_chk check ((status = 'void') = (voided_at is not null)),
  constraint invoices_customer_fk
    foreign key (tenant_id, customer_id)
    references public.customers (tenant_id, id) on delete restrict,
  constraint invoices_order_fk
    foreign key (tenant_id, order_id)
    references public.orders (tenant_id, id) on delete restrict,
  constraint invoices_shipment_fk
    foreign key (tenant_id, shipment_id)
    references public.shipments (tenant_id, id) on delete restrict,
  constraint invoices_terms_fk
    foreign key (tenant_id, payment_terms_id)
    references public.payment_terms (tenant_id, id) on delete restrict
);

-- One invoice per shipment; the partial predicate allows many NULLs
-- (manual invoices) while blocking a duplicate for the same shipment.
create unique index invoices_shipment_uk
  on public.invoices (tenant_id, shipment_id)
  where shipment_id is not null;

-- The aging report: unpaid invoices by due date. balance_due is a stored
-- generated column, so it can be indexed and used in a partial predicate.
create index invoices_open_idx
  on public.invoices (tenant_id, due_date, customer_id)
  where status in ('open','partially_paid');
create index invoices_customer_idx
  on public.invoices (tenant_id, customer_id, issue_date desc);
create index invoices_sync_idx
  on public.invoices (tenant_id, synced_at)
  where external_id is null;

select app.enable_tenant_rls('public.invoices');

In plain English: the header carries the money and the dates, and balance_due is computed by the database as total less paid less credited. Because it is a stored generated column it can be indexed, which is what makes the aging report quick. The partial unique index allows one invoice per shipment while still allowing any number of manual invoices that reference no shipment at all.

invoice_lines

What is being billed. Usually one line per shipped order line, plus lines for freight and surcharges that have no order line at all, which is why order_line_id and variant_id are both nullable.

create table public.invoice_lines (
  id               uuid primary key default gen_random_uuid(),
  tenant_id        uuid not null references public.tenants (id) on delete cascade,
  invoice_id       uuid not null,
  line_number      integer not null,
  order_line_id    uuid,
  variant_id       uuid,
  description      text not null,       -- printed on the PDF
  quantity         numeric(14,3) not null,   -- numeric: freight lines use 1.000
  unit_price       numeric(18,4) not null,
  discount_percent numeric(6,3) not null default 0,
  tax_rate         numeric(6,4) not null default 0,
  extended_amount  numeric(18,4) generated always as
                     (round(quantity * unit_price
                            * (1 - discount_percent / 100), 4)) stored,
  gl_account_code  text,                -- maps to the accounting system
  created_at       timestamptz not null default now(),
  updated_at       timestamptz not null default now(),

  constraint invoice_lines_tenant_row_uk unique (tenant_id, id),
  constraint invoice_lines_number_uk unique (tenant_id, invoice_id, line_number),
  constraint invoice_lines_qty_chk check (quantity <> 0),
  constraint invoice_lines_price_chk check (unit_price >= 0),
  constraint invoice_lines_discount_chk
    check (discount_percent >= 0 and discount_percent < 100),
  constraint invoice_lines_invoice_fk
    foreign key (tenant_id, invoice_id)
    references public.invoices (tenant_id, id) on delete cascade,
  constraint invoice_lines_order_line_fk
    foreign key (tenant_id, order_line_id)
    references public.order_lines (tenant_id, id) on delete restrict,
  constraint invoice_lines_variant_fk
    foreign key (tenant_id, variant_id)
    references public.variants (tenant_id, id) on delete restrict
);

create index invoice_lines_invoice_idx on public.invoice_lines (tenant_id, invoice_id);
-- Revenue by SKU reporting.
create index invoice_lines_variant_idx
  on public.invoice_lines (tenant_id, variant_id)
  where variant_id is not null;

select app.enable_tenant_rls('public.invoice_lines');

In plain English: usually one line per shipped order line, plus lines for freight and surcharges that correspond to nothing on the order, which is why the order-line and variant links are both optional. Quantity is numeric rather than integer here so that a freight line can be a quantity of 1.000 and a part-month service charge can be a fraction.

payments

Money received, recorded before anyone decides which invoices it covers. A row is created when a check is keyed, an ACH file is imported, or a card charge settles; unapplied_amount is what is still floating.

create table public.payments (
  id                uuid primary key default gen_random_uuid(),
  tenant_id         uuid not null references public.tenants (id) on delete cascade,
  payment_number    text not null,
  customer_id       uuid not null,
  method            text not null,
  reference         text,                 -- cheque number, ACH trace, charge id
  received_on       date not null default current_date,
  currency          app.currency_code not null default 'USD',
  amount            numeric(18,4) not null,
  amount_applied    numeric(18,4) not null default 0,
  unapplied_amount  numeric(18,4) generated always as
                      (amount - amount_applied) stored,
  status            text not null default 'unapplied',
  external_id       text,
  deposited_on      date,
  notes             text,
  created_by        uuid references public.users (id) on delete set null,
  created_at        timestamptz not null default now(),
  updated_at        timestamptz not null default now(),

  constraint payments_tenant_row_uk unique (tenant_id, id),
  constraint payments_number_uk unique (tenant_id, payment_number),
  constraint payments_method_chk
    check (method in ('check','ach','wire','card','cash','credit','other')),
  constraint payments_status_chk
    check (status in ('unapplied','partially_applied','applied','void')),
  constraint payments_amount_chk check (amount > 0),
  -- You cannot apply more money than arrived.
  constraint payments_applied_chk
    check (amount_applied >= 0 and amount_applied <= amount),
  constraint payments_customer_fk
    foreign key (tenant_id, customer_id)
    references public.customers (tenant_id, id) on delete restrict
);

-- The cash-application work queue: money that still needs a home.
create index payments_unapplied_idx
  on public.payments (tenant_id, customer_id)
  where status in ('unapplied','partially_applied');
create index payments_received_idx
  on public.payments (tenant_id, received_on desc);

select app.enable_tenant_rls('public.payments');

In plain English: money is recorded when it arrives, before anybody works out which invoices it covers. unapplied_amount is computed by the database and is the number the cash-application queue sorts by. The applied check refuses to let more be spread across invoices than actually arrived.

payment_applications

One row per "this much of that payment pays this invoice". The many-to-many join that makes cash application auditable. Reversals are recorded as new rows pointing at the row they reverse, never by deletion.

create table public.payment_applications (
  id             uuid primary key default gen_random_uuid(),
  tenant_id      uuid not null references public.tenants (id) on delete cascade,
  payment_id     uuid not null,
  invoice_id     uuid not null,
  amount         numeric(18,4) not null,   -- may be negative for a reversal
  applied_at     timestamptz not null default now(),
  reverses_id    uuid,                     -- points at the row being undone
  applied_by     uuid references public.users (id) on delete set null,
  note           text,
  created_at     timestamptz not null default now(),

  constraint payment_applications_tenant_row_uk unique (tenant_id, id),
  constraint payment_applications_amount_chk check (amount <> 0),
  constraint payment_applications_payment_fk
    foreign key (tenant_id, payment_id)
    references public.payments (tenant_id, id) on delete restrict,
  constraint payment_applications_invoice_fk
    foreign key (tenant_id, invoice_id)
    references public.invoices (tenant_id, id) on delete restrict,
  constraint payment_applications_reverses_fk
    foreign key (tenant_id, reverses_id)
    references public.payment_applications (tenant_id, id) on delete restrict
);

-- A reversal may only be recorded once per original row.
create unique index payment_applications_reverses_uk
  on public.payment_applications (tenant_id, reverses_id)
  where reverses_id is not null;

create index payment_applications_payment_idx
  on public.payment_applications (tenant_id, payment_id);
create index payment_applications_invoice_idx
  on public.payment_applications (tenant_id, invoice_id);

select app.enable_tenant_rls('public.payment_applications');

In plain English: each row applies part of one payment to one invoice, so one check can settle fourteen invoices and one invoice can be settled by three checks. A mistake is undone by inserting a negative row that points at the row it reverses, never by deleting anything, and the partial unique index makes sure a given row is reversed at most once.

credit_memos

Money you owe the customer back: a return, a pricing error, a negotiated allowance. Created by finance, and applied to invoices exactly like a payment is.

create table public.credit_memos (
  id                 uuid primary key default gen_random_uuid(),
  tenant_id          uuid not null references public.tenants (id) on delete cascade,
  credit_memo_number text not null,
  customer_id        uuid not null,
  invoice_id         uuid,              -- the invoice being credited, if any
  reason_code        text not null,
  status             text not null default 'draft',
  issue_date         date not null default current_date,
  currency           app.currency_code not null default 'USD',
  amount             numeric(18,4) not null,
  amount_applied     numeric(18,4) not null default 0,
  external_id        text,
  approved_by        uuid references public.users (id) on delete set null,
  notes              text,
  created_at         timestamptz not null default now(),
  updated_at         timestamptz not null default now(),

  constraint credit_memos_tenant_row_uk unique (tenant_id, id),
  constraint credit_memos_number_uk unique (tenant_id, credit_memo_number),
  constraint credit_memos_status_chk
    check (status in ('draft','open','applied','void')),
  constraint credit_memos_reason_chk
    check (reason_code in ('return','pricing','damage','allowance',
                           'shortage','goodwill','other')),
  constraint credit_memos_amount_chk check (amount > 0),
  constraint credit_memos_applied_chk
    check (amount_applied >= 0 and amount_applied <= amount),
  constraint credit_memos_customer_fk
    foreign key (tenant_id, customer_id)
    references public.customers (tenant_id, id) on delete restrict,
  constraint credit_memos_invoice_fk
    foreign key (tenant_id, invoice_id)
    references public.invoices (tenant_id, id) on delete restrict
);

create index credit_memos_open_idx
  on public.credit_memos (tenant_id, customer_id)
  where status in ('draft','open');

select app.enable_tenant_rls('public.credit_memos');

In plain English: a credit memo is money you owe back, and it is applied to invoices much as a payment is. It always names a customer and optionally names the invoice that caused it. The reason code is what makes "why are we issuing so many credits?" an answerable question.

deductions

A chargeback: the retailer paid $9,140 on a $10,000 invoice and deducted $860 for a late shipment, a missing ASN, or a non-compliant carton label. A row is created during cash application when the remittance does not match, and it is then disputed, approved, or written off.

Tracking these individually, each with a reason code, is how a brand finds out how much of its own money is walking back out the door and which repeated mistake is sending it. (A routing guide is the retailer's rulebook for how to label, pack, and ship to them; most of these codes mean "you broke the routing guide".)

create table public.deductions (
  id                 uuid primary key default gen_random_uuid(),
  tenant_id          uuid not null references public.tenants (id) on delete cascade,
  deduction_number   text not null,
  customer_id        uuid not null,
  invoice_id         uuid,
  payment_id         uuid,              -- the payment it was taken from
  reason_code        text not null,
  customer_reason    text,              -- the code THEY used, verbatim
  status             text not null default 'open',
  deducted_on        date not null default current_date,
  amount             numeric(18,4) not null,
  amount_recovered   numeric(18,4) not null default 0,
  disputed_at        timestamptz,
  resolved_at        timestamptz,
  evidence           jsonb not null default '{}'::jsonb,  -- proof-of-delivery
                                        -- scans, ASN ids, photos of the carton
  assigned_to        uuid references public.users (id) on delete set null,
  notes              text,
  created_at         timestamptz not null default now(),
  updated_at         timestamptz not null default now(),

  constraint deductions_tenant_row_uk unique (tenant_id, id),
  constraint deductions_number_uk unique (tenant_id, deduction_number),
  constraint deductions_status_chk
    check (status in ('open','disputed','approved','recovered','written_off')),
  constraint deductions_reason_chk
    check (reason_code in ('late_shipment','shortage','damage','pricing',
                           'label_compliance','asn_compliance','co_op',
                           'markdown','unauthorized','other')),
  constraint deductions_amount_chk check (amount > 0),
  constraint deductions_recovered_chk
    check (amount_recovered >= 0 and amount_recovered <= amount),
  constraint deductions_resolved_chk
    check (status not in ('recovered','written_off') or resolved_at is not null),
  constraint deductions_customer_fk
    foreign key (tenant_id, customer_id)
    references public.customers (tenant_id, id) on delete restrict,
  constraint deductions_invoice_fk
    foreign key (tenant_id, invoice_id)
    references public.invoices (tenant_id, id) on delete restrict,
  constraint deductions_payment_fk
    foreign key (tenant_id, payment_id)
    references public.payments (tenant_id, id) on delete restrict
);

-- The dispute work queue and the "why are we losing money" report.
create index deductions_open_idx
  on public.deductions (tenant_id, status, deducted_on desc)
  where status in ('open','disputed');
create index deductions_reason_idx
  on public.deductions (tenant_id, reason_code, deducted_on desc);

select app.enable_tenant_rls('public.deductions');

In plain English: a deduction is money the customer simply kept. Your reason code and their reason code are stored separately, because the two rarely agree and the dispute is usually about exactly that gap. The evidence column holds the proof you will need to argue it back. The two indexes drive the dispute work queue and the report showing which repeated mistake costs you the most.

11.9 Integration and ops

The plumbing from chapters 5 and 10:

• Connections hold credentials;
• webhook_events is the inbox (raw, verified, deduplicated);
• the outbox is the outbox pattern — domain events written in the same transaction as the data change, published afterward by a worker, which is how you get at-least-once delivery without two-phase commit;
• sync_state holds cursors;
• reconciliation_runs records the periodic "do we still agree with Shopify?" sweep;
• import_batches/import_rows are the spreadsheet pipeline;
• and jobs is the domain-level view of background work.

   external world                    your database
  ------------------      +-------------------------------+
   Shopify / EDI  --webhook--> webhook_events  (inbox)     |
   QuickBooks                      |  dedup on            |
                                   |  external_event_id   |
                                   v                      |
                             +-----------+                |
                             |   jobs    |--> domain tables|
                             +-----------+     (in a txn)  |
                                   ^                |      |
                                   |                v      |
   Shopify <--publisher-------  outbox   <--- same txn ----+
                                   ^
                                   |
  +---------------------+   +--------------+   +----------------------+
  | integration_        |   |  sync_state  |   | reconciliation_runs  |
  | connections         |---|  (cursors)   |   | (drift detection)    |
  +---------------------+   +--------------+   +----------------------+

  +------------------+        +---------------+
  |  import_batches  |---1:N--|  import_rows  |
  +------------------+        +---------------+

  The rule: inbound data lands raw in webhook_events FIRST and is
  processed second. Outbound events are written to outbox INSIDE the
  transaction that changed the data, and published later. Neither path
  ever calls an external API while holding a database transaction open.

integration_connections

One row per external system a tenant has connected. A row is created by an OAuth callback (OAuth is the "log in with Shopify" handshake that hands your app a token instead of the user's password) or by someone pasting credentials into a settings form. Note there is no secret in this table, only a pointer to where the secret is kept.

create table public.integration_connections (
  id                   uuid primary key default gen_random_uuid(),
  tenant_id            uuid not null references public.tenants (id) on delete cascade,
  provider             text not null,
  name                 text not null,        -- 'US Shopify store'
  external_account_id  text,                 -- shop domain, realm id, ISA id
  status               text not null default 'connected',
  credentials_ref      text not null,        -- key in the secret manager/vault
  config               jsonb not null default '{}'::jsonb,
  scopes               text[] not null default '{}',
  connected_at         timestamptz not null default now(),
  last_success_at      timestamptz,
  last_error_at        timestamptz,
  last_error           text,
  disabled_at          timestamptz,
  created_by           uuid references public.users (id) on delete set null,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now(),

  constraint integration_connections_tenant_row_uk unique (tenant_id, id),
  constraint integration_connections_account_uk
    unique (tenant_id, provider, external_account_id),
  constraint integration_connections_provider_chk
    check (provider in ('shopify','quickbooks','netsuite','edi','sftp',
                        'shipstation','stripe','custom')),
  constraint integration_connections_status_chk
    check (status in ('connected','error','disabled','pending')),
  -- A credentials_ref that looks like a secret is a leak: block the
  -- obvious shapes at the database boundary.
  constraint integration_connections_ref_chk
    check (credentials_ref !~ '^(sk_|shpat_|Bearer )')
);

create index integration_connections_provider_idx
  on public.integration_connections (tenant_id, provider)
  where status = 'connected';

select app.enable_tenant_rls('public.integration_connections');

In plain English: a connection records that a tenant has linked an external system, what it is called, and where its credentials are kept — a reference to a vault entry, never the secret itself. The last check is a cheap safety net: if somebody pastes a live Shopify or Stripe token into that reference field, the database refuses the row.

webhook_events

The inbox. Every inbound webhook is written here before it is processed, so a crash mid-processing loses nothing and a replayed delivery is recognized. The unique key on (provider, external_event_id) is the deduplication that makes at-least-once delivery safe.

create table public.webhook_events (
  id                  uuid primary key default gen_random_uuid(),
  tenant_id           uuid not null references public.tenants (id) on delete cascade,
  connection_id       uuid,
  provider            text not null,
  topic               text not null,        -- 'orders/create'
  external_event_id   text not null,        -- provider's delivery id
  payload             jsonb not null,       -- exactly as received
  headers             jsonb not null default '{}'::jsonb,
  signature_verified  boolean not null default false,
  status              text not null default 'received',
  attempts            integer not null default 0,
  received_at         timestamptz not null default now(),
  processed_at        timestamptz,
  last_error          text,
  created_at          timestamptz not null default now(),
  updated_at          timestamptz not null default now(),

  constraint webhook_events_tenant_row_uk unique (tenant_id, id),
  -- The deduplication key. A replayed delivery hits this and is ignored.
  constraint webhook_events_external_uk
    unique (tenant_id, provider, external_event_id),
  constraint webhook_events_status_chk
    check (status in ('received','processing','processed','failed','ignored')),
  constraint webhook_events_attempts_chk check (attempts >= 0),
  constraint webhook_events_processed_chk
    check (status <> 'processed' or processed_at is not null),
  constraint webhook_events_connection_fk
    foreign key (tenant_id, connection_id)
    references public.integration_connections (tenant_id, id) on delete cascade
);

-- The processing queue: oldest unprocessed first.
create index webhook_events_pending_idx
  on public.webhook_events (tenant_id, received_at)
  where status in ('received','processing');
-- The failure triage screen.
create index webhook_events_failed_idx
  on public.webhook_events (tenant_id, provider, received_at desc)
  where status = 'failed';

select app.enable_tenant_rls('public.webhook_events');

In plain English: an inbound webhook is written down before it is acted on, so a crash halfway through processing loses nothing. The unique key on the provider's own event id means a delivery that arrives twice is recognized and ignored the second time, which is what makes it safe to accept at-least-once delivery from Shopify.

outbox

Domain events waiting to go out. A row is inserted in the same transaction as the change it describes — if the order commits, the event is guaranteed to exist; if it rolls back, so does the event. A publisher process then claims rows, calls the external API, and marks them published. This is the pattern that removes "we saved the order but the Shopify update failed" from your life.

create table public.outbox (
  id              uuid primary key default gen_random_uuid(),
  tenant_id       uuid not null references public.tenants (id) on delete cascade,
  seq             bigint generated always as identity,
  aggregate_type  text not null,        -- 'order','shipment','variant'
  aggregate_id    uuid not null,
  event_type      text not null,        -- 'order.confirmed'
  payload         jsonb not null,
  destination     text,                 -- null = all interested subscribers
  status          text not null default 'pending',
  available_at    timestamptz not null default now(),  -- backoff target
  published_at    timestamptz,
  attempts        integer not null default 0,
  max_attempts    integer not null default 10,
  last_error      text,
  created_at      timestamptz not null default now(),
  updated_at      timestamptz not null default now(),

  constraint outbox_tenant_row_uk unique (tenant_id, id),
  constraint outbox_seq_uk unique (seq),
  constraint outbox_status_chk
    check (status in ('pending','publishing','published','failed','dead')),
  constraint outbox_event_chk check (event_type ~ '^[a-z_]+\.[a-z_]+$'),
  constraint outbox_attempts_chk
    check (attempts >= 0 and max_attempts > 0),
  constraint outbox_published_chk
    check ((status = 'published') = (published_at is not null))
);

-- The publisher's claim query:
--   select ... where status = 'pending' and available_at <= now()
--   order by seq for update skip locked limit 100;
-- The partial index keeps that scan proportional to the backlog, not to
-- the millions of already-published rows.
create index outbox_ready_idx
  on public.outbox (available_at, seq)
  where status in ('pending','failed');

create index outbox_aggregate_idx
  on public.outbox (tenant_id, aggregate_type, aggregate_id, seq);

select app.enable_tenant_rls('public.outbox');

In plain English: when you change something the outside world needs to hear about, you insert a row here in the same transaction as the change. If the transaction rolls back, the event vanishes with it, so you can never announce something that did not happen.

A separate publisher then claims pending rows in sequence order, calls the external API, and marks them published; the claim query uses for update skip locked so several publishers can run at once without fighting over the same rows.

sync_state

One row per (connection, resource) holding the cursor for incremental sync: "we have pulled Shopify orders updated up to 2027-03-14T09:12:04Z". Created the first time a resource syncs, updated after every successful page.

create table public.sync_state (
  id                  uuid primary key default gen_random_uuid(),
  tenant_id           uuid not null references public.tenants (id) on delete cascade,
  connection_id       uuid not null,
  resource            text not null,        -- 'orders','products','inventory'
  direction           text not null default 'inbound',
  cursor_value        text,                 -- opaque provider cursor
  watermark_at        timestamptz,          -- highest updated_at we have seen
  last_run_at         timestamptz,
  last_success_at     timestamptz,
  last_full_sync_at   timestamptz,
  records_synced      bigint not null default 0,
  status              text not null default 'idle',
  last_error          text,
  stats               jsonb not null default '{}'::jsonb,
  created_at          timestamptz not null default now(),
  updated_at          timestamptz not null default now(),

  constraint sync_state_tenant_row_uk unique (tenant_id, id),
  constraint sync_state_uk
    unique (tenant_id, connection_id, resource, direction),
  constraint sync_state_direction_chk
    check (direction in ('inbound','outbound')),
  constraint sync_state_status_chk
    check (status in ('idle','running','error','paused')),
  constraint sync_state_connection_fk
    foreign key (tenant_id, connection_id)
    references public.integration_connections (tenant_id, id) on delete cascade
);

select app.enable_tenant_rls('public.sync_state');

In plain English: one row per connection, per resource, per direction, remembering where the last sync got to. The cursor is whatever opaque string the provider handed back; the watermark is the highest updated-at timestamp you have seen. Between them, the next run can ask only for what changed.

reconciliation_runs

The record of a drift check: "at 03:00 we compared 4,812 Shopify inventory levels with ours and found 3 disagreements, 3 of which we repaired". Chapter 5 argued that every integration needs this; without it, silent divergence is discovered by a customer.

create table public.reconciliation_runs (
  id                   uuid primary key default gen_random_uuid(),
  tenant_id            uuid not null references public.tenants (id) on delete cascade,
  connection_id        uuid not null,
  resource             text not null,
  status               text not null default 'running',
  window_start         timestamptz not null,
  window_end           timestamptz not null,
  started_at           timestamptz not null default now(),
  finished_at          timestamptz,
  records_checked      integer not null default 0,
  discrepancies_found  integer not null default 0,
  auto_repaired        integer not null default 0,
  report               jsonb not null default '{}'::jsonb,  -- the diffs
  last_error           text,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now(),

  constraint reconciliation_runs_tenant_row_uk unique (tenant_id, id),
  constraint reconciliation_runs_status_chk
    check (status in ('running','completed','failed','cancelled')),
  constraint reconciliation_runs_window_chk check (window_end > window_start),
  constraint reconciliation_runs_counts_chk
    check (records_checked >= 0 and discrepancies_found >= 0
           and auto_repaired >= 0 and auto_repaired <= discrepancies_found),
  constraint reconciliation_runs_finished_chk
    check (status = 'running' or finished_at is not null),
  constraint reconciliation_runs_connection_fk
    foreign key (tenant_id, connection_id)
    references public.integration_connections (tenant_id, id) on delete cascade
);

create index reconciliation_runs_recent_idx
  on public.reconciliation_runs (tenant_id, connection_id, started_at desc);

select app.enable_tenant_rls('public.reconciliation_runs');

In plain English: this is the record of a drift check rather than the check itself. It stores the window compared, how many records were examined, how many disagreed, how many were repaired automatically, and the diffs. The counts check refuses a run claiming to have repaired more records than it found broken.

import_batches

One uploaded spreadsheet. Created when a file lands, then validated as a whole before a single row is committed — the chapter 8 rule: validate everything, import all or nothing, and never leave a half-imported file behind.

create table public.import_batches (
  id               uuid primary key default gen_random_uuid(),
  tenant_id        uuid not null references public.tenants (id) on delete cascade,
  entity           text not null,        -- what the file contains
  source_filename  text not null,
  file_url         text,                 -- object storage path of the original
  file_hash        text,                 -- sha256 hex; detects a re-upload
  status           text not null default 'uploaded',
  column_mapping   jsonb not null default '{}'::jsonb,  -- header to field name
  options          jsonb not null default '{}'::jsonb,  -- upsert? dry run?
  total_rows       integer not null default 0,
  valid_rows       integer not null default 0,
  error_rows       integer not null default 0,
  imported_rows    integer not null default 0,
  uploaded_by      uuid references public.users (id) on delete set null,
  started_at       timestamptz,
  finished_at      timestamptz,
  last_error       text,
  idempotency_key  text not null,
  created_at       timestamptz not null default now(),
  updated_at       timestamptz not null default now(),

  constraint import_batches_tenant_row_uk unique (tenant_id, id),
  constraint import_batches_idem_uk unique (tenant_id, idempotency_key),
  constraint import_batches_entity_chk
    check (entity in ('styles','variants','customers','customer_locations',
                      'orders','inventory','purchase_orders','prices','barcodes')),
  constraint import_batches_status_chk
    check (status in ('uploaded','validating','validated','importing',
                      'completed','failed','cancelled')),
  constraint import_batches_counts_chk
    check (total_rows >= 0 and valid_rows >= 0 and error_rows >= 0
           and imported_rows >= 0
           and valid_rows + error_rows <= total_rows
           and imported_rows <= valid_rows),
  constraint import_batches_hash_chk
    check (file_hash is null or file_hash ~ '^[0-9a-f]{64}$')
);

create index import_batches_recent_idx
  on public.import_batches (tenant_id, created_at desc);
create index import_batches_active_idx
  on public.import_batches (tenant_id, status)
  where status in ('uploaded','validating','validated','importing');

select app.enable_tenant_rls('public.import_batches');

In plain English: one row per uploaded file. The file hash catches somebody re-uploading the same spreadsheet twice. The counts constraint keeps the arithmetic honest: valid plus error rows cannot exceed the total, and imported rows cannot exceed the valid ones. column_mapping records how the spreadsheet's headers were matched to real fields, which is what makes a failed import reproducible.

import_rows

One row per spreadsheet row, keeping the raw values forever. That is what lets the error report say "row 412, column Ship Date: '31/02/2027' is not a date" instead of "import failed", and what lets a user fix twelve cells and re-run only those rows.

create table public.import_rows (
  id           uuid primary key default gen_random_uuid(),
  tenant_id    uuid not null references public.tenants (id) on delete cascade,
  batch_id     uuid not null,
  row_number   integer not null,      -- 1-based, matches the spreadsheet
  raw_data     jsonb not null,        -- the cells exactly as uploaded
  normalized   jsonb,                 -- after parsing and mapping
  status       text not null default 'pending',
  errors       jsonb not null default '[]'::jsonb,  -- [{field, code, message}]
  entity_id    uuid,                  -- the row we created or updated
  processed_at timestamptz,
  created_at   timestamptz not null default now(),
  updated_at   timestamptz not null default now(),

  constraint import_rows_tenant_row_uk unique (tenant_id, id),
  constraint import_rows_uk unique (tenant_id, batch_id, row_number),
  constraint import_rows_status_chk
    check (status in ('pending','valid','invalid','imported','skipped','failed')),
  constraint import_rows_number_chk check (row_number > 0),
  constraint import_rows_errors_chk check (jsonb_typeof(errors) = 'array'),
  -- An invalid row must say why. Silent rejection is the worst outcome.
  constraint import_rows_invalid_chk
    check (status <> 'invalid' or jsonb_array_length(errors) > 0),
  constraint import_rows_batch_fk
    foreign key (tenant_id, batch_id)
    references public.import_batches (tenant_id, id) on delete cascade
);

create index import_rows_batch_idx
  on public.import_rows (tenant_id, batch_id, row_number);
-- The error report screen reads only the bad rows.
create index import_rows_errors_idx
  on public.import_rows (tenant_id, batch_id)
  where status in ('invalid','failed');

select app.enable_tenant_rls('public.import_rows');

In plain English: every spreadsheet row is kept exactly as it was uploaded, next to the parsed version and any errors. That is what lets the error report name the row, the column, and the offending value, and lets a user correct twelve cells and re-run only those rows. A row marked invalid must carry at least one error, so nothing is ever rejected without a stated reason.

jobs

The domain-level record of background work: an allocation run, a nightly rebuild of ATS (available-to-sell), a bulk price update. pg-boss (the PostgreSQL job-queue library this book uses) keeps its own pgboss schema and does the real work of locking, retrying, and scheduling. This table is the tenant-visible, RLS-protected mirror your users see in an "activity" panel, keyed to the pg-boss job id.

create table public.jobs (
  id             uuid primary key default gen_random_uuid(),
  tenant_id      uuid not null references public.tenants (id) on delete cascade,
  queue_name     text not null,        -- pg-boss queue this went to
  job_type       text not null,        -- 'allocation.run'
  pgboss_job_id  uuid,                 -- the row in pgboss.job
  payload        jsonb not null default '{}'::jsonb,
  status         text not null default 'queued',
  priority       smallint not null default 0,
  run_at         timestamptz not null default now(),
  started_at     timestamptz,
  completed_at   timestamptz,
  attempts       integer not null default 0,
  max_attempts   integer not null default 3,
  progress       integer not null default 0,   -- 0..100 for the UI
  result         jsonb,
  last_error     text,
  singleton_key  text,                 -- at most one live job per key
  requested_by   uuid references public.users (id) on delete set null,
  created_at     timestamptz not null default now(),
  updated_at     timestamptz not null default now(),

  constraint jobs_tenant_row_uk unique (tenant_id, id),
  constraint jobs_status_chk
    check (status in ('queued','running','completed','failed','cancelled')),
  constraint jobs_attempts_chk
    check (attempts >= 0 and max_attempts > 0),
  constraint jobs_progress_chk check (progress between 0 and 100),
  constraint jobs_completed_chk
    check (status not in ('completed','failed') or completed_at is not null)
);

-- "Do not start a second allocation run while one is live." A partial
-- unique index expresses that with no application locking at all.
create unique index jobs_singleton_uk
  on public.jobs (tenant_id, singleton_key)
  where singleton_key is not null and status in ('queued','running');

create index jobs_recent_idx on public.jobs (tenant_id, created_at desc);
create index jobs_active_idx
  on public.jobs (tenant_id, status, run_at)
  where status in ('queued','running');

select app.enable_tenant_rls('public.jobs');

In plain English: this table mirrors background work so that users can watch it. The singleton index is the useful trick: give a job a singleton_key and the database itself refuses to queue a second one while the first is alive, with no application locking anywhere.

One last migration: attach the updated_at trigger everywhere

Run this block once, at the end of your migrations. It finds every table in public that has an updated_at column and no touch trigger, and attaches one, so the append-only tables (which have no updated_at) are skipped automatically, and the day you add table sixty-two you cannot forget.

do $$
declare
  t record;
begin
  for t in
    select c.relname
      from pg_class c
      join pg_namespace n on n.oid = c.relnamespace
      join pg_attribute a on a.attrelid = c.oid
     where n.nspname = 'public'
       and c.relkind = 'r'
       and a.attname = 'updated_at'
       and not a.attisdropped
       and not exists (
         select 1 from pg_trigger tg
          where tg.tgrelid = c.oid
            and tg.tgname = c.relname || '_touch_updated_at')
  loop
    execute format(
      'create trigger %I before update on public.%I '
      'for each row execute function app.touch_updated_at()',
      t.relname || '_touch_updated_at', t.relname);
  end loop;
end
$$;

In plain English: the block asks the system catalog for every ordinary table in public that has an updated_at column and does not already have a touch trigger, and creates one for each. Append-only tables have no updated_at column at all, so they are skipped without you having to list them. Run it as the last migration, and run it again after you add a table.

11.10 The derived queries

Six queries answer most of the questions an ERP is asked. Keep them in one file, test them against a seeded fixture, and wrap each in a view or a function once it stabilizes. Every one is tenant-scoped through app.current_tenant_id(), so RLS and the query agree.

1. Current on-hand per SKU per warehouse (straight from the ledger)

This is the definition of on-hand. Everything else is a cache of it.

select
    v.sku,                                   -- the SKU, for humans
    w.code                as warehouse_code,
    sum(t.quantity)::integer as on_hand,     -- signed sum IS the balance
    max(t.seq)            as last_seq,       -- watermark: cache is valid to here
    max(t.occurred_at)    as last_movement_at
from public.inventory_transactions t
-- Join on BOTH tenant_id and id. Always. It uses the composite index and it
-- makes an accidental cross-tenant join impossible to write by mistake.
join public.variants   v on v.tenant_id = t.tenant_id and v.id = t.variant_id
join public.warehouses w on w.tenant_id = t.tenant_id and w.id = t.warehouse_id
where t.tenant_id = app.current_tenant_id()
  -- Post-dated movements exist (a receipt keyed for tomorrow); exclude them
  -- so "on hand" means "on hand now".
  and t.occurred_at <= now()
group by v.sku, w.code
-- Positions that netted to zero are noise on a stock report.
having sum(t.quantity) <> 0
order by v.sku, w.code;

The same aggregate, written as an upsert (one statement that inserts a row, or updates it in place if it is already there), is the cache rebuild. Run it after a restore, after a bulk import, or whenever the drift check in 11.13 fires.

Note the one deliberate difference from the report above: the rebuild sums every ledger row, with no occurred_at <= now() filter. The cache holds the all-time sum, which is what invariant #2 in 11.13 compares against; the report filters post-dated rows because a human asking "what is on hand?" means "right now".

insert into public.inventory_balances
      (tenant_id, variant_id, warehouse_id, on_hand, last_seq, computed_at)
select t.tenant_id, t.variant_id, t.warehouse_id,
       sum(t.quantity)::integer, max(t.seq), now()
from public.inventory_transactions t
where t.tenant_id = app.current_tenant_id()
group by t.tenant_id, t.variant_id, t.warehouse_id
-- The unique constraint on (tenant_id, variant_id, warehouse_id) is what
-- lets ON CONFLICT find the existing row and update it in place.
on conflict (tenant_id, variant_id, warehouse_id) do update
   set on_hand     = excluded.on_hand,
       last_seq    = excluded.last_seq,
       computed_at = excluded.computed_at,
       -- Bump the optimistic-concurrency token so any in-flight update
       -- holding the old version loses, as it should.
       version     = inventory_balances.version + 1;

In plain English: read the whole ledger, total it by SKU and warehouse, and write those totals into the cache. Where a row for that position already exists, on conflict updates it in place rather than failing, and bumps the version number, so any half-finished update still holding the old version loses the race and has to re-read, which is what you want after a rebuild.

2. The full ATS query (available to sell, by week)

ATS answers the only question a salesperson actually has: if I promise these units for that week, can we deliver? It is opening availability, plus incoming supply, minus committed demand, accumulated forward week by week.

A negative number in week 9 means you are oversold in week 9, and everything after it is oversold too, which is why the running total, not the per-week number, is the answer.

with params as (
    select app.current_tenant_id()                as tenant_id,
           date_trunc('week', current_date)::date as week0,   -- Monday of this week
           26                                     as week_count -- half-year horizon
),
weeks as (
    -- One row per week in the horizon: week 0 through week 26, so 27 rows.
    -- Supply or demand dated beyond the last bucket falls outside the grid
    -- and will not appear at all; raise week_count if you sell that far out.
    -- generate_series needs timestamps, so cast in and cast back to date.
    select generate_series(p.week0::timestamp,
                           (p.week0 + p.week_count * 7)::timestamp,
                           interval '7 days')::date as week_start
    from params p
),
on_hand as (
    -- Opening position: available (= on_hand - allocated - reserved) summed
    -- over SELLABLE warehouses only. Damaged and in-transit stock is real
    -- but must never be promised.
    select b.variant_id, sum(b.available)::integer as qty
    from public.inventory_balances b
    join public.warehouses w
      on w.tenant_id = b.tenant_id and w.id = b.warehouse_id
    cross join params p
    where b.tenant_id = p.tenant_id
      and w.is_sellable
      and w.is_active
    group by b.variant_id
),
supply as (
    -- Open purchase-order units, bucketed into the week they are expected
    -- to LAND. greatest(..., week0) drags overdue POs into week 0 rather
    -- than letting them fall off the front of the horizon and vanish.
    select pol.variant_id,
           greatest(
             date_trunc('week',
               coalesce(pol.expected_arrival_on, po.expected_arrival_on,
                        current_date))::date,
             p.week0) as week_start,
           sum(pol.quantity_ordered - pol.quantity_received
               - pol.quantity_cancelled)::integer as qty
    from public.po_lines pol
    join public.purchase_orders po
      on po.tenant_id = pol.tenant_id and po.id = pol.purchase_order_id
    cross join params p
    where pol.tenant_id = p.tenant_id
      and pol.status in ('open', 'partially_received')
      and po.status  in ('issued', 'acknowledged', 'in_production',
                         'shipped', 'partially_received')
      and pol.quantity_ordered > pol.quantity_received + pol.quantity_cancelled
    group by 1, 2
),
demand as (
    -- Open order units, bucketed into the week they are due to ship.
    -- Subtract quantity_allocated as well as shipped and cancelled. The
    -- opening position above already used `available`, which has allocated
    -- units taken out of it; counting them again here would subtract the
    -- same units twice and show you oversold when you are not.
    select ol.variant_id,
           greatest(
             date_trunc('week',
               coalesce(ol.requested_ship_on, o.start_ship_on, o.order_date))::date,
             p.week0) as week_start,
           sum(ol.quantity_ordered - ol.quantity_shipped
               - ol.quantity_cancelled - ol.quantity_allocated)::integer as qty
    from public.order_lines ol
    join public.orders o on o.tenant_id = ol.tenant_id and o.id = ol.order_id
    cross join params p
    where ol.tenant_id = p.tenant_id
      and ol.status in ('open', 'allocated')
      and o.status  in ('confirmed', 'allocated', 'partially_shipped')
      and ol.quantity_ordered > ol.quantity_shipped + ol.quantity_cancelled
                              + ol.quantity_allocated
    group by 1, 2
),
grid as (
    -- Every sellable SKU crossed with every week: without this, weeks with
    -- no activity disappear and the running total skips them.
    select v.id as variant_id, v.sku, wk.week_start
    from public.variants v
    cross join weeks wk
    cross join params p
    where v.tenant_id = p.tenant_id
      and v.is_sellable
      and v.status = 'active'
)
select g.sku,
       g.week_start,
       coalesce(oh.qty, 0) as opening_available,
       coalesce(s.qty, 0)  as incoming,
       coalesce(d.qty, 0)  as demand,
       -- The answer: opening position plus the running net of every week up
       -- to and including this one.
       coalesce(oh.qty, 0)
         + sum(coalesce(s.qty, 0) - coalesce(d.qty, 0))
             over (partition by g.variant_id
                   order by g.week_start
                   rows between unbounded preceding and current row)
         as available_to_sell
from grid g
left join on_hand oh on oh.variant_id = g.variant_id
left join supply  s  on s.variant_id  = g.variant_id and s.week_start = g.week_start
left join demand  d  on d.variant_id  = g.variant_id and d.week_start = g.week_start
order by g.sku, g.week_start;

In plain English, read it one block at a time:

• params fixes the tenant and the first week.
• weeks makes one row per week in the horizon.
• on_hand is today's sellable opening position.
• supply is what is arriving, bucketed into the week it lands.
• demand is what has been promised but not yet allocated, bucketed into the week it is due.
• grid crosses every sellable SKU with every week so that a quiet week cannot vanish from the middle of the series.

The final select then adds the running total of supply minus demand to the opening position. One output row reads: "in the week starting on this date, this SKU has this many units still safe to promise".

3. Open order book

select o.order_number,
       c.name              as customer,
       o.status,
       o.start_ship_on,
       o.cancel_after_on,
       -- Units still owed = ordered, less what already went, less what died.
       sum(ol.quantity_ordered - ol.quantity_shipped
           - ol.quantity_cancelled)                       as units_open,
       -- Value of those units at the frozen line price, after line discount.
       round(sum((ol.quantity_ordered - ol.quantity_shipped
                  - ol.quantity_cancelled)::numeric
                 * ol.unit_price * (1 - ol.discount_percent / 100)), 2)
                                                          as value_open,
       -- Past the cancel date is an at-risk order: the retailer may refuse it.
       (o.cancel_after_on is not null
        and o.cancel_after_on < current_date)             as past_cancel_date
from public.orders o
join public.order_lines ol on ol.tenant_id = o.tenant_id and ol.order_id = o.id
join public.customers   c  on c.tenant_id = o.tenant_id and c.id = o.customer_id
where o.tenant_id = app.current_tenant_id()
  and o.status in ('confirmed', 'allocated', 'partially_shipped')
  and ol.status in ('open', 'allocated')
group by o.id, o.order_number, c.name, o.status,
         o.start_ship_on, o.cancel_after_on
-- A fully shipped order can still be 'partially_shipped' for a moment;
-- HAVING removes the rows with nothing left to do.
having sum(ol.quantity_ordered - ol.quantity_shipped - ol.quantity_cancelled) > 0
order by o.start_ship_on nulls last, value_open desc;

Grouping by o.id lets you select every other orders column without listing it, because id is the primary key and PostgreSQL knows the rest is functionally dependent on it. That behavior is part of the SQL standard, and it keeps long group by lists short and honest.

4. Aging receivables

select c.code,
       c.name,
       sum(i.balance_due)                                    as total_due,
       -- FILTER is the clean way to write conditional aggregates: one pass,
       -- no CASE soup, and NULLs do not sneak into the sum.
       sum(i.balance_due) filter (where i.due_date >= current_date)
                                                             as bucket_current,
       sum(i.balance_due) filter (where current_date - i.due_date between 1 and 30)
                                                             as bucket_1_30,
       sum(i.balance_due) filter (where current_date - i.due_date between 31 and 60)
                                                             as bucket_31_60,
       sum(i.balance_due) filter (where current_date - i.due_date between 61 and 90)
                                                             as bucket_61_90,
       sum(i.balance_due) filter (where current_date - i.due_date > 90)
                                                             as bucket_90_plus,
       -- Chargebacks in dispute are not really "receivable"; show them apart.
       coalesce((select sum(d.amount - d.amount_recovered)
                 from public.deductions d
                 where d.tenant_id = c.tenant_id
                   and d.customer_id = c.id
                   and d.status in ('open', 'disputed')), 0)  as open_deductions
from public.invoices i
join public.customers c on c.tenant_id = i.tenant_id and c.id = i.customer_id
where i.tenant_id = app.current_tenant_id()
  and i.status in ('open', 'partially_paid')
  -- balance_due is a STORED generated column, so this predicate is indexable
  -- and always agrees with total - paid - credited.
  and i.balance_due > 0
group by c.tenant_id, c.id, c.code, c.name
order by total_due desc;

In plain English: this groups unpaid invoices by customer and spreads them across the standard age buckets. filter lets each sum() apply its own condition during a single pass over the rows, which is both faster and easier to read than a stack of case expressions. The subquery at the end reports open chargebacks separately, because money the customer has already helped themselves to is not really collectible receivable and should not be added to the total you chase.

5. Sell-through by style

Sell-through is the retail metric that decides whether you reorder: of everything you brought in, what fraction has gone out? The thresholds people act on vary a lot by channel, price point, and how far into the season you are, so treat any single published number with suspicion. The rough wholesale rule of thumb is that a style sitting well under half sold at mid-season is heading for markdown, and one nearly sold out at mid-season means you under-bought.

with movement as (
    -- One pass over the ledger, split by direction with FILTER.
    select v.style_id,
           sum(t.quantity) filter (where t.txn_type in ('receipt', 'opening_balance'))
                                                        as units_received,
           -- Shipments are negative in the ledger; negate to get units out.
           -sum(t.quantity) filter (where t.txn_type = 'shipment')
                                                        as units_shipped,
           -sum(t.quantity * coalesce(t.unit_cost, 0))
             filter (where t.txn_type = 'shipment')      as cost_of_goods_sold
    from public.inventory_transactions t
    join public.variants v on v.tenant_id = t.tenant_id and v.id = t.variant_id
    where t.tenant_id = app.current_tenant_id()
      and t.occurred_at >= current_date - interval '365 days'
    group by v.style_id
),
on_hand as (
    select v.style_id, sum(b.on_hand)::integer as units_on_hand
    from public.inventory_balances b
    join public.variants v on v.tenant_id = b.tenant_id and v.id = b.variant_id
    where b.tenant_id = app.current_tenant_id()
    group by v.style_id
)
select st.style_number,
       st.name,
       se.code                              as season,
       coalesce(m.units_received, 0)        as received,
       coalesce(m.units_shipped, 0)         as shipped,
       coalesce(oh.units_on_hand, 0)        as on_hand,
       -- nullif() guards the division: a style with no activity yields NULL,
       -- not a division-by-zero error.
       round(100.0 * coalesce(m.units_shipped, 0)
             / nullif(coalesce(m.units_shipped, 0)
                      + coalesce(oh.units_on_hand, 0), 0), 1)
                                            as sell_through_percent,
       round(coalesce(m.cost_of_goods_sold, 0), 2) as cogs
from public.styles st
left join movement m  on m.style_id  = st.id
left join on_hand  oh on oh.style_id = st.id
left join public.seasons se on se.tenant_id = st.tenant_id and se.id = st.season_id
where st.tenant_id = app.current_tenant_id()
  and st.status = 'active'
order by sell_through_percent desc nulls last;

In plain English: the first block totals what came in and what went out over the last year in a single pass over the ledger, using filter to split the pass by transaction type, and negates the shipment figures because shipments are stored as negative quantities. The second block totals what is still on hand right now.

The final select divides units shipped by units shipped plus units on hand, with nullif() guarding the division so that a style with no activity yields NULL instead of an error. Keep one mismatch in mind when you read the result: shipments are measured over a year, while on-hand is measured this instant.

6. Inventory valuation

What is the stock worth? This uses weighted-average cost derived from every positive, cost-bearing ledger row — the same number your accountant wants for the balance sheet.

with average_cost as (
    -- Weighted average: total money in, divided by total units in. Only
    -- inbound rows (quantity > 0) with a cost participate; shipments do not
    -- change what a unit cost you.
    select t.variant_id,
           sum(t.quantity * t.unit_cost) / nullif(sum(t.quantity), 0)
             as avg_unit_cost
    from public.inventory_transactions t
    where t.tenant_id = app.current_tenant_id()
      and t.quantity > 0
      and t.unit_cost is not null
    group by t.variant_id
)
select w.code                       as warehouse,
       st.style_number,
       v.sku,
       b.on_hand,
       -- Fallback chain: measured average, then the variant's standard cost,
       -- then the style default, then zero. Never NULL: a NULL in a
       -- valuation report silently drops the row from the total.
       round(coalesce(ac.avg_unit_cost, v.unit_cost,
                      st.default_unit_cost, 0), 4)          as unit_cost,
       round(b.on_hand * coalesce(ac.avg_unit_cost, v.unit_cost,
                                  st.default_unit_cost, 0), 2)
                                                            as extended_value
from public.inventory_balances b
join public.variants   v  on v.tenant_id = b.tenant_id and v.id = b.variant_id
join public.styles     st on st.tenant_id = v.tenant_id and st.id = v.style_id
join public.warehouses w  on w.tenant_id = b.tenant_id and w.id = b.warehouse_id
left join average_cost ac on ac.variant_id = b.variant_id
where b.tenant_id = app.current_tenant_id()
  and b.on_hand > 0
order by w.code, st.style_number, v.sku;

In plain English: the first block works out what each unit cost you on average, by dividing total money in by total units in across every inbound ledger row that carried a cost. The main query multiplies that average by what is on hand.

The coalesce chain falls back to the variant's standard cost, then the style default, then zero, because a NULL unit cost would make the whole extended value NULL, and that row would then drop silently out of the total and understate your inventory.

11.11 Every state machine in the system

A status column without a written transition table becomes a status column where anything can become anything. Enforce these in one place in the application (a transition map plus a guard function), log every move to audit_log, and add the pathological transitions to your property tests from chapter 9.

Two rules that cut across every machine

Both rules are easy to state and easy to break under deadline pressure. First, terminal states are terminal: closed, cancelled, void, and delivered have no outgoing edges, and you undo one by creating a new document — a return, a credit memo, a fresh order. Editing the old row is always the wrong move.

Second, the state that posts to the ledger is irreversible — shipments.shipped and receipts.posted, because the ledger is append-only and a compensating movement is a different, visible act.

11.12 Migration ordering and tenant seeding

Tables must be created after everything they reference. Run the waves below in order. Inside a wave, order matters only where one table points at another table in the same wave — the listing already puts those in a workable sequence and calls the awkward ones out (colorways before variants, cartons before shipment_lines, api_keys before audit_log). Each wave is a separate migration file, so a failure is easy to locate.

  wave 0  0001_prelude          extensions, roles, app schema, functions
  wave 1  0002_tenancy          tenants, users
  wave 2  0003_identity         memberships, roles, membership_roles,
                                sessions, api_keys, audit_log
  wave 3  0004_catalog_base     seasons, size_scales, sizes
  wave 4  0005_catalog          styles  (needs seasons + size_scales)
  wave 5  0006_catalog_variants colorways, then variants (needs styles,
                                colorways, sizes), then barcodes
  wave 6  0007_pricing          price_lists, price_list_items
  wave 7  0008_partners         payment_terms, sales_reps, then
                                customers, customer_locations,
                                customer_terms, suppliers
  wave 8  0009_warehouses       warehouses            (needs suppliers)
  wave 9  0010_ledger           inventory_transactions, inventory_balances
  wave 10 0011_inventory_docs   inventory_counts(+lines),
                                inventory_adjustments(+lines),
                                transfers(+lines)
  wave 11 0012_sales            orders, order_lines, order_revisions,
                                allocations, reservations
  wave 12 0013_purchasing       purchase_orders, po_lines,
                                inbound_shipments(+lines),
                                receipts, receipt_lines
  wave 13 0014_fulfillment      shipments, cartons, shipment_lines,
                                shipment_trackings
                                (cartons BEFORE shipment_lines)
  wave 14 0015_financial        invoices, invoice_lines, payments,
                                payment_applications, credit_memos,
                                deductions
  wave 15 0016_ops              integration_connections,
                                webhook_events, outbox, sync_state,
                                reconciliation_runs, import_batches,
                                import_rows, jobs
  wave 16 0017_triggers         the updated_at loop, append-only triggers
  wave 17 0018_seed_functions   app.seed_tenant()

  The only cycle in the whole schema is customers / customer_locations,
  and the design dodges it: "default ship-to" is a partial unique index
  on the child rather than a column on the parent. If you ever do need a
  true cycle, create both tables first and add the second foreign key
  afterwards with:
    alter table ... add constraint ... foreign key ... deferrable

Seeding a new tenant

A brand-new tenant is not usable until it has been seeded. Everything below must exist before a user can key their first order, and all of it should live in one security definer function called by the signup flow, inside one transaction, so a half-seeded tenant is impossible.

-- Called once, inside the signup transaction, right after the tenants insert.
create or replace function app.seed_tenant(p_tenant_id uuid, p_owner_user_id uuid)
returns void
language plpgsql
security definer
set search_path = public, pg_temp
as $$
declare
  v_membership_id uuid;
  v_scale_id      uuid;
begin
  -- FORCE ROW LEVEL SECURITY applies to the table owner too, and this
  -- function runs as the owner. Without this line app.current_tenant_id()
  -- returns NULL, the tenant policy's WITH CHECK fails, and every insert
  -- below dies with "new row violates row-level security policy". Set the
  -- setting first, always. One more prerequisite: the policies are granted
  -- TO authenticated, so the role that owns this function must be a member
  -- of authenticated, or no policy applies to it and everything is denied.
  perform set_config('app.tenant_id', p_tenant_id::text, true);

  -- 1. Five system roles. Without these, nobody can do anything.
  insert into public.roles (tenant_id, key, name, is_system, permissions) values
    (p_tenant_id, 'owner',     'Owner',     true, array['*']),
    (p_tenant_id, 'admin',     'Admin',     true,
       array['orders.*','catalog.*','inventory.*','partners.*','settings.*']),
    (p_tenant_id, 'sales',     'Sales',     true,
       array['orders.read','orders.write','catalog.read','partners.read']),
    (p_tenant_id, 'warehouse', 'Warehouse', true,
       array['inventory.*','shipments.*','receipts.*','catalog.read']),
    (p_tenant_id, 'finance',   'Finance',   true,
       array['invoices.*','payments.*','deductions.*','orders.read']);

  -- 2. The owner's membership, granted the owner role.
  insert into public.memberships
         (tenant_id, user_id, status, is_owner, accepted_at)
  values (p_tenant_id, p_owner_user_id, 'active', true, now())
  returning id into v_membership_id;

  insert into public.membership_roles (tenant_id, membership_id, role_id)
  select p_tenant_id, v_membership_id, r.id
  from public.roles r
  where r.tenant_id = p_tenant_id and r.key = 'owner';

  -- 3. Payment terms. Every customer and supplier needs one.
  insert into public.payment_terms
         (tenant_id, code, name, net_days, discount_percent, discount_days, is_default)
  values (p_tenant_id, 'NET30',      'Net 30',              30, 0,   0, true),
         (p_tenant_id, 'NET60',      'Net 60',              60, 0,   0, false),
         (p_tenant_id, '2-10-NET30', '2% 10 days, net 30',  30, 2.0, 10, false),
         (p_tenant_id, 'COD',        'Due on receipt',       0, 0,   0, false);

  -- 4. A default size scale with a real alpha run, so a style can be created
  --    on day one without a setup detour.
  insert into public.size_scales (tenant_id, code, name)
  values (p_tenant_id, 'ALPHA', 'Alpha XS-XXL')
  returning id into v_scale_id;

  insert into public.sizes (tenant_id, size_scale_id, code, label, sort_order)
  values (p_tenant_id, v_scale_id, 'XS',  'Extra Small', 0),
         (p_tenant_id, v_scale_id, 'S',   'Small',       1),
         (p_tenant_id, v_scale_id, 'M',   'Medium',      2),
         (p_tenant_id, v_scale_id, 'L',   'Large',       3),
         (p_tenant_id, v_scale_id, 'XL',  'Extra Large', 4),
         (p_tenant_id, v_scale_id, 'XXL', '2X Large',    5);

  -- 5. A default wholesale price list in the tenant's base currency.
  insert into public.price_lists
         (tenant_id, code, name, currency, price_kind, is_default)
  select p_tenant_id, 'WHSL-' || t.base_currency, 'Standard Wholesale',
         t.base_currency, 'wholesale', true
  from public.tenants t
  where t.id = p_tenant_id;

  -- 6. One warehouse, plus the two non-sellable buckets every brand needs.
  insert into public.warehouses
         (tenant_id, code, name, warehouse_type, is_sellable, sort_order)
  values (p_tenant_id, 'MAIN',      'Main Warehouse', 'owned',      true,  0),
         (p_tenant_id, 'DAMAGED',   'Damaged Goods',  'virtual',    false, 90),
         (p_tenant_id, 'IN-TRANSIT','In Transit',     'in_transit', false, 91);

  -- 7. The tenant's own audit trail starts here.
  insert into public.audit_log
         (tenant_id, actor_kind, actor_user_id, action, entity_table, entity_id)
  values (p_tenant_id, 'system', p_owner_user_id, 'tenant.seeded',
          'tenants', p_tenant_id);
end;
$$;

Three things are deliberately left out of the seed: customers, styles, and integrations. A new tenant should land in an empty but working state. Demo data that looks real is the fastest route to a customer invoicing a fictional retailer.

11.13 Invariants

An invariant is a statement that must be true after every transaction, forever. Constraints enforce the ones the database can see inside a single row or key; the rest are enforced by code and verified by the queries below. Chapter 9's job runs all of them nightly (and in CI against generated data).

Nine invariants are listed below, and the eleven queries that follow check the ones a query can check — some invariants need more than one query, and two of them need none, because a key already guarantees them. Each query is written so that zero rows returned means healthy, and any row is an incident with its own evidence attached.

1. Every row belongs to exactly one tenant, and every reference stays inside it. Enforced structurally by the composite (tenant_id, id) foreign keys — no runtime check needed.
2. The ledger is the truth and the cache agrees with it. For every (variant, warehouse): inventory_balances.on_hand = sum(inventory_transactions.quantity).
3. Committed stock is backed by physical stock. allocated + reserved <= on_hand, and allocated equals the sum of active allocation rows.
4. Nothing ships that was not ordered. Each order line's quantity_shipped equals the sum of its shipment lines on shipped shipments, and never exceeds quantity_ordered.
5. Documents and the ledger agree. A posted receipt's lines sum to the receipt-type ledger rows that reference it; likewise a shipped shipment.
6. Invoice arithmetic closes. total = subtotal - discount + tax + freight, subtotal = sum(lines), and amount_paid = sum(payment_applications).
7. Cash is never double-applied. For every payment, the sum of its applications equals amount_applied and never exceeds amount.
8. Idempotency keys are unique per tenant on ledger rows, receipts, shipments, and import batches — enforced by unique constraints, so a retry can never double-post.
9. Every state is reachable by a legal transition from the table in 11.11, and every transition is in audit_log.

-- ===================================================================
-- Invariant checks. All eleven queries MUST return zero rows.
-- Run nightly per tenant; alert on any row, with the row as the evidence.
-- ===================================================================

-- #2  Cache drift: the balance disagrees with the ledger.
select b.tenant_id, b.variant_id, b.warehouse_id,
       b.on_hand as cached, coalesce(l.qty, 0) as ledger
from public.inventory_balances b
left join (
    select tenant_id, variant_id, warehouse_id, sum(quantity)::integer as qty
    from public.inventory_transactions
    group by tenant_id, variant_id, warehouse_id
) l on l.tenant_id   = b.tenant_id
   and l.variant_id  = b.variant_id
   and l.warehouse_id = b.warehouse_id
where b.on_hand <> coalesce(l.qty, 0);

-- #3  Allocation drift: the cached commitment disagrees with the rows.
select b.tenant_id, b.variant_id, b.warehouse_id,
       b.allocated as cached, coalesce(a.qty, 0) as actual
from public.inventory_balances b
left join (
    select tenant_id, variant_id, warehouse_id, sum(quantity)::integer as qty
    from public.allocations
    where status = 'active'
    group by tenant_id, variant_id, warehouse_id
) a on a.tenant_id    = b.tenant_id
   and a.variant_id   = b.variant_id
   and a.warehouse_id = b.warehouse_id
where b.allocated <> coalesce(a.qty, 0);

-- #4  Shipped quantity on the line disagrees with what actually shipped.
select ol.tenant_id, ol.id as order_line_id,
       ol.quantity_shipped as line_says, coalesce(sl.qty, 0) as shipments_say
from public.order_lines ol
left join (
    select sl.tenant_id, sl.order_line_id, sum(sl.quantity)::integer as qty
    from public.shipment_lines sl
    join public.shipments s
      on s.tenant_id = sl.tenant_id and s.id = sl.shipment_id
    where s.status in ('shipped', 'delivered')
    group by sl.tenant_id, sl.order_line_id
) sl on sl.tenant_id = ol.tenant_id and sl.order_line_id = ol.id
where ol.quantity_shipped <> coalesce(sl.qty, 0);

-- #4b A shipment line whose variant does not match its order line's variant.
--     (Structurally possible, operationally catastrophic: wrong goods billed.)
select sl.tenant_id, sl.id as shipment_line_id,
       sl.variant_id as shipped, ol.variant_id as ordered
from public.shipment_lines sl
join public.order_lines ol
  on ol.tenant_id = sl.tenant_id and ol.id = sl.order_line_id
where sl.variant_id <> ol.variant_id;

-- #5  A posted receipt whose lines do not match the ledger rows it created.
--     Good units and damaged units both post (damaged ones into the
--     quarantine warehouse), so both count here. Rejected units post
--     nowhere -- they never entered the building -- so they do not.
select r.tenant_id, r.id as receipt_id,
       coalesce(rl.qty, 0) as receipt_says, coalesce(t.qty, 0) as ledger_says
from public.receipts r
left join (
    select tenant_id, receipt_id,
           sum(quantity_received + quantity_damaged)::integer as qty
    from public.receipt_lines group by tenant_id, receipt_id
) rl on rl.tenant_id = r.tenant_id and rl.receipt_id = r.id
left join (
    select tenant_id, ref_id, sum(quantity)::integer as qty
    from public.inventory_transactions
    where ref_type = 'receipt'
    group by tenant_id, ref_id
) t on t.tenant_id = r.tenant_id and t.ref_id = r.id
where r.status = 'posted'
  and coalesce(rl.qty, 0) <> coalesce(t.qty, 0);

-- #6  Invoice header arithmetic does not close against its lines.
select i.tenant_id, i.id as invoice_id, i.subtotal_amount, coalesce(l.total, 0)
from public.invoices i
left join (
    select tenant_id, invoice_id, sum(extended_amount) as total
    from public.invoice_lines group by tenant_id, invoice_id
) l on l.tenant_id = i.tenant_id and l.invoice_id = i.id
where i.status <> 'draft'
  -- Allow one cent of rounding across many lines; anything more is a bug.
  and abs(i.subtotal_amount - coalesce(l.total, 0)) > 0.01;

-- #6b The invoice header does not add up on its own terms.
--     total = subtotal - discount + tax + freight. Exact numerics, so an
--     exact comparison is safe here; no tolerance is needed.
select i.tenant_id, i.id as invoice_id, i.total_amount,
       i.subtotal_amount - i.discount_amount + i.tax_amount
         + i.freight_amount as computed_total
from public.invoices i
where i.status <> 'draft'
  and i.total_amount <> i.subtotal_amount - i.discount_amount
                       + i.tax_amount + i.freight_amount;

-- #6c Cash applied to the invoice disagrees with the application rows.
select i.tenant_id, i.id as invoice_id,
       i.amount_paid as invoice_says, coalesce(pa.total, 0) as applications_say
from public.invoices i
left join (
    select tenant_id, invoice_id, sum(amount) as total
    from public.payment_applications group by tenant_id, invoice_id
) pa on pa.tenant_id = i.tenant_id and pa.invoice_id = i.id
where abs(i.amount_paid - coalesce(pa.total, 0)) > 0.005;

-- #7  Payment applied more than it received, or its cache disagrees.
select p.tenant_id, p.id as payment_id, p.amount, p.amount_applied,
       coalesce(pa.total, 0) as applications_say
from public.payments p
left join (
    select tenant_id, payment_id, sum(amount) as total
    from public.payment_applications group by tenant_id, payment_id
) pa on pa.tenant_id = p.tenant_id and pa.payment_id = p.id
where abs(p.amount_applied - coalesce(pa.total, 0)) > 0.005
   or p.amount_applied > p.amount;

-- #3b Oversold: a sellable position promising more than it holds.
select b.tenant_id, b.variant_id, b.warehouse_id,
       b.on_hand, b.allocated, b.reserved, b.available
from public.inventory_balances b
join public.warehouses w
  on w.tenant_id = b.tenant_id and w.id = b.warehouse_id
where w.is_sellable
  and b.available < 0;

-- #9  Terminal states that were reopened (an illegal transition happened).
select o.tenant_id, o.id as order_id, o.status, o.cancelled_at, o.closed_at
from public.orders o
where (o.cancelled_at is not null and o.status <> 'cancelled')
   or (o.closed_at    is not null and o.status not in ('closed', 'cancelled'));

In plain English, each query asks the same shape of question: compute the number from the source of truth, compare it with the number somebody cached or maintained by hand, and return the rows where the two disagree. A healthy database returns nothing at all, so the alert rule is simply "any row is a page".

The two money checks allow half a cent of tolerance because sums of many rounded lines drift by tiny amounts; the invoice header check needs no tolerance, because those four columns are exact numerics that must add up exactly.

Field notes & further reading

• PostgreSQL manual — Row Security Policies. The authoritative description of USING vs WITH CHECK, permissive vs restrictive policies, and the FORCE flag. Read the whole page before writing your first policy.
• PostgreSQL manual — Numeric Types. The section on arbitrary-precision numbers explains exactly why numeric is exact and double precision is not.
• PostgreSQL manual — Constraints. Covers check, unique, foreign-key MATCH modes, and exclusion constraints in one place.
• PostgreSQL manual — Generated Columns. The restriction list, including the rule that one generated column may not reference another, and the difference between stored and virtual — PostgreSQL 18 made virtual the default, so every generated column in this chapter says stored out loud.
• PostgreSQL manual — Partial Indexes. The technique behind almost every index in this chapter.
• Supabase — Row Level Security. The Supabase helper functions auth.uid() and auth.jwt(), and the performance advice to wrap a function call in (select …) inside a policy so PostgreSQL evaluates it once per statement instead of once per row.
• Use The Index, Luke! — Markus Winand's free book on index design. Read the chapters on concatenated indexes and on order by before you add your next index.