Invoice numbers that start at 1 for every customer

Difficulty 4/5Subscribers

An invoicing product serves many companies. Each company issues its own invoices, and accountants have opinions about the numbering.

The rules:

Invoice numbers start at 1 for each tenant and go up from there. Acme's first invoice is number 1, and so is Globex's.
Within a tenant the number is unique. Across tenants it means nothing.
No gaps. An accountant seeing invoice 1, 2 and 4 will ask what happened to 3, and "the database allocated it and we rolled back" is not an answer they accept.
Nobody supplies the number. Inserting an invoice for a tenant is what assigns it.
An invoice belongs to a tenant that exists, and its amount is never negative.

tenants already exists.

The contract

2 relations

The tests reference these names. Everything else is yours, and is what is being assessed: extra tables, extra columns, types, constraints, indexes.

tenants
idslug
invoices
idtenant_idnumberamount_cents

The schema that already exists

This runs before your submission. Do not repeat it, extend it.

prelude.sql

CREATE TABLE tenants (
  id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug TEXT NOT NULL UNIQUE
);

INSERT INTO tenants (slug) VALUES ('acme'), ('globex');

CREATE TABLE ...  (
  id          BIGINT GENERATED ALWAYS AS IDENTITY,
  tenant_id   BIGINT NOT NULL REFERENCES ...,
  ...         TEXT NOT NULL,
  valid_from  TIMESTAMPTZ NOT NULL,
  valid_until TIMESTAMPTZ
);

CREATE UNIQUE INDEX ... ON ... (...)
  WHERE ... IS NULL;

LOCKED

This one is for subscribers

The problem above is the whole problem, and nothing is hidden from it. What a subscription adds is the part that tells you whether your answer holds: a real Postgres runs your schema, a battery of hidden tests decides, and a design review reads what you wrote.

See the plans Or try one of the free ones

Checking your subscription…