Lesson 19.419.4 Designing the AP tables

In everyday life. Before a clinic opens, someone decides what a patient record holds: name, date of birth, insurance number, never the patient's diary. They decide which fields may be empty, which must be unique, and that an appointment must name a real patient. Those decisions come before the first patient walks in, because changing them later means changing every record.

What you will learn. How the design of the tables writes the data's rules into the database, and why every table and every key in this chapter starts with the company.

Why it matters. A rule written into the database holds for every row, whichever code writes it. With the company in every key, a Cedar Ridge row can never point at a Brightline row, even by mistake.

One request, start to finish. Ana Morales, Brightline's AP analyst, and the builder decide what a purchase order row must hold. The builder proposes. Ana corrects.

1. A purchase order has a number, a vendor, a date and an amount. Ana: "and the invoice it was raised for, if there is one yet."
2. The amount is a whole number of cents, never a decimal, so no rounding ever creeps in (Chapter 14).
3. The vendor must be a real vendor. Ana: "and it must be one of our vendors. A Cedar Ridge purchase order must never point at a Brightline vendor, even by mistake."

That last sentence decides the shape of every table in the chapter.

What is a schema?

A schema is the design of a database: its tables, their columns, each column's type (text, a whole number, a date, a vector), and the rules each row must follow. Postgres calls those rules constraints: a column that may not be empty (NOT NULL), a value that must be unique, a check on its form, or a pointer that must point at a real row (a foreign key, 19.2). The database refuses any row that breaks one.

Why does every key include the company?

Every table except tenants itself has a tenant_id column holding the company's id, the same id the sign-in server gives it (org_brightline, org_cedar). And every primary key and every foreign key includes it. Here is the invoices table, as the agent wrote it in Lab 19's migration:

CREATE TABLE invoices (
  tenant_id      text   NOT NULL,                  -- whose invoice this is
  invoice_number text   NOT NULL CHECK (invoice_number ~ '^INV-\d{4}$'),
  vendor_id      text   NOT NULL,
  invoice_date   date   NOT NULL,
  due_date       date,                             -- may be empty: some exports leave it out
  terms          text   NOT NULL,
  amount_cents   bigint NOT NULL CHECK (amount_cents >= 0),
  status         text   NOT NULL CHECK (status IN ('open', 'paid')),
  PRIMARY KEY (tenant_id, invoice_number),
  FOREIGN KEY (tenant_id, vendor_id) REFERENCES vendors (tenant_id, vendor_id)
);

In plain words, line by line: text, date and bigint (a large whole number) are the columns' types. NOT NULL means the column may never be empty. CHECK (invoice_number ~ '^INV-\d{4}$') means the number must be INV- followed by exactly four digits (~ tests the text against a pattern). CHECK (amount_cents >= 0) means no negative amounts, and CHECK (status IN ('open', 'paid')) means the status must be one of those two words. REFERENCES vendors (...) names the table and columns the foreign key must match. And the key: an invoice is named by its company and its number together, a composite key (two columns that together name one row). Its vendor is found by its company and its vendor id together. So a Cedar Ridge invoice can only ever point at a Cedar Ridge vendor: the pair (org_cedar, V006) does not exist, because only Brightline has a V006, and the database refuses the row. The same key also lets two companies use the same ids. Brightline and Cedar Ridge both have a vendor V001 and an invoice INV-8303. They are different rows, never confused.

Which tables hold the AP data?

What is an index?

An index is a sorted shortcut the database keeps beside a table, like the index at the back of a book. Without one, finding Cedar Ridge's invoices means reading every row. With one on tenant_id, the database jumps straight to them. A primary key always comes with an index, and because each key here starts with tenant_id, every table already has a shortcut to one company's rows. Part C adds a second, special kind of index, for vectors.

At work. In Lab 19, Task 2, the builder asks Claude Code to try one deliberately wrong row: a Cedar Ridge invoice for vendor V006. The database refuses it with error 23503, a foreign key violation, and Claude Code explains which rule refused it. No code in the connector was involved. The rule lives in the database.

The operating rule: write the data's rules into the schema, where the database enforces them on every row. Put tenant_id in every table that holds a company's rows, and in every key.

Figure 19.10. The AP tables. Every key starts with the company, so every pointer stays inside one company.