PostgreSQL Schema Architect

Schema for multi-tenant invoicing: tenant_id on every table, composite keys for tenant scoping, and monthly range partitioning on invoices.

CREATE TABLE tenant (
  tenant_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL
);

CREATE TABLE customer (
  tenant_id   bigint NOT NULL REFERENCES tenant,
  customer_id bigint GENERATED ALWAYS AS IDENTITY,
  name        text NOT NULL,
  PRIMARY KEY (tenant_id, customer_id)
);

CREATE TABLE invoice (
  tenant_id    bigint NOT NULL,
  invoice_id   bigint GENERATED ALWAYS AS IDENTITY,
  customer_id  bigint NOT NULL,
  issued_on    date NOT NULL,
  due_on       date NOT NULL CHECK (due_on >= issued_on),
  status       text NOT NULL CHECK (status IN ('open','paid','void')),
  total_cents  bigint NOT NULL CHECK (total_cents >= 0),
  PRIMARY KEY (tenant_id, invoice_id, issued_on),
  FOREIGN KEY (tenant_id, customer_id) REFERENCES customer
) PARTITION BY RANGE (issued_on);

CREATE INDEX invoice_unpaid_due_idx
  ON invoice (tenant_id, due_on) WHERE status = 'open';

Design notes

• invoice_line and payment follow the same pattern, amounts in integer cents.
• The partial index serves the unpaid list by due date.
• Monthly partitions on issued_on keep revenue reports to few partitions.
• Naming: singular snake_case tables, <table>_id keys.

Next steps

• Create partitions ahead of time with a scheduled job or pg_partman.
• Add row-level security on tenant_id for one shared DB role.