Why the schema comes first

A screen is a view of data. If the data is shaped wrongly, every screen, report and integration built on it carries the mistake, and fixing it later means rebuilding all of them. Settling the tables first, before any screen, is the cheapest point to get it right. It also gives every AI agent the same fixed ground to build on: an agent can propose a screen in minutes, but it must not invent a new place to keep the data each time.

Edgar Codd's relational model, published in 1970, is still the basis: data kept in tables of rows, each fact stored once, tables joined by keys ^[1]. Postgres, the database inside Supabase, is a relational database built on that model ^[2].

Read the intent for tables

Take the stamping plant's intent from Session 2 and underline three kinds of words. Nouns you keep a list of (presses, parts, lots) become reference tables. Moments that happen many times (a first-piece result, an hourly gauge reading) become event tables, one row per moment. The decision itself (hold or release) becomes a table too, so that who decided, when and why is never lost.

Use the names manufacturing already agrees on

ISA-95 (IEC 62264) is the standard language between business systems and the plant floor. It gives names for the equipment hierarchy (enterprise, site, area, work centre such as a line, work unit such as a cell or press) and for the things that move: material lots, personnel, equipment, and the results of work ^[3]. Using its words for your tables means your data lines up with the ERP, MES and any future system that also speaks it. The OPC Foundation publishes ISA-95's object model for machines to use, which is a good reference when a name is in doubt ^[4].

Let the table refuse bad data

Postgres can refuse a bad row before it is stored: the wrong type, a missing value, a value outside its range, a reference to something that does not exist, or a duplicate ^[1]. These rules live with the table, so they hold whatever writes the row. Row-level security, from Session 2B, is the last layer: the right row, but for the wrong person.

Types that match the plant

• Times are timestamptz (timestamp with time zone), so a night shift that crosses midnight, or the change to daylight saving time, never scrambles the order of events ^[2].
• Measurements are numeric with the unit in the column name (value_mm), not floating point, so 12.030 is stored as exactly 12.030 ^[3].
• Counts are integer. Money, when it appears, is whole cents in an integer, converted only for display.
• Codes people type, such as press codes, have a check on their format, so 'Press 4', 'press4' and 'P-04' cannot all exist at once.

History you can trust: append-only

Inspections and holds are records of what happened. If they can be edited, nobody can trust them, and in regulated industries they would fail an audit: the US FDA's rule for electronic records, 21 CFR Part 11, requires audit trails that keep earlier values when a record changes ^[4]. The simple design is to never change them at all. A correction is a new row that points at the one it corrects, and a trigger refuses any update or delete ^[5].

-- Refuse any change to a record of what happened.
create function public.append_only() returns trigger language plpgsql as $$
begin
  raise exception '% is append-only: add a correcting row instead', tg_table_name;
end $$;

create trigger inspections_append_only before update or delete on public.inspections
  for each row execute function public.append_only();
create trigger holds_append_only before update or delete on public.holds
  for each row execute function public.append_only();

Current state is a view over history

The quality lead needs to know, right now, which lots are on hold. That is the latest hold or release row for each lot. A view is a saved query that looks like a table, so screens read it like any other ^[6]. On Supabase, create views with security_invoker = true so the reader's row-level security applies through the view as well ^[7].

-- Each lot's current status: its most recent hold or release.
create view public.lot_status with (security_invoker = true) as
select distinct on (lot_id) lot_id, action as status, reason, decided_by, decided_at
from public.holds
order by lot_id, decided_at desc;

Why a file, not clicks

Tables made by clicking in a dashboard exist in one database and nowhere else. A migration is a SQL file that makes the change, named with a timestamp so the files run in order. With the migrations in the repository, any database (a developer's laptop, a CI run, a second plant) can be rebuilt to exactly the same shape, and every change has a pull request, a reviewer and a date ^[1].

Make the file

The Supabase command-line tool is free, and creates the file with the right name in the right folder ^[2]. Your AI coding agent can run it and write the SQL; you read the result.

npx supabase init                                   # once: creates the supabase/ folder
npx supabase migration new create_reference_tables # makes supabase/migrations/<timestamp>_create_reference_tables.sql
# ...the agent writes the SQL into that file, in a pull request...
npx supabase login                                  # once, after the merge, on your own machine: opens the browser
npx supabase link                                   # once: choose your company's project from the list
npx supabase db push                                # applies new migrations to the live project

login opens your browser to sign in to Supabase; link lists your projects so you pick the company's. When a command asks for the database password, type it from the company password manager. It is never written in a file or pasted into a chat.

-- supabase/migrations/20261005091500_create_lots_inspections.sql (the stamping plant, excerpt)
create table public.characteristics (
  id        bigint generated always as identity primary key,
  part_id   bigint not null references public.parts(id),
  name      text not null,                               -- 'flange height'
  lower_mm  numeric(8,3) not null,
  upper_mm  numeric(8,3) not null,
  check (lower_mm < upper_mm)
);
create table public.lots (
  id           bigint generated always as identity primary key,
  site_id      bigint not null references public.sites(id),
  part_id      bigint not null references public.parts(id),
  equipment_id bigint not null references public.equipment(id),
  lot_code     text not null unique check (lot_code ~ '^L-[0-9]{4,}$'),
  started_at   timestamptz not null
);
create table public.inspections (
  id                bigint generated always as identity primary key,
  site_id           bigint not null references public.sites(id),     -- kept here so RLS is one simple rule
  lot_id            bigint not null references public.lots(id),
  characteristic_id bigint not null references public.characteristics(id),
  kind              text not null check (kind in ('first_piece', 'hourly')),
  value_mm          numeric(8,3) not null,
  recorded_by       uuid not null references auth.users(id),
  recorded_at       timestamptz not null default now(),
  corrects_id       bigint references public.inspections(id),
  correction_reason text,
  check ((corrects_id is null) = (correction_reason is null))      -- a correction always says why
);

Read it before it merges

An agent writes a schema quickly and plausibly. These questions catch most of what goes wrong:

1. Does every table serve the intent? A table nothing in docs/INTENT.md needs is scope creep.
2. Does every table have a primary key, and every reference a foreign key?
3. Are times timestamptz, measurements numeric with the unit in the name, and money whole cents?
4. Does every value people choose from a list have a check?
5. Are event and decision tables append-only, with the trigger?
6. Is row-level security turned on, with policies from the matrix (Session 2B)? Does the no-RLS query still return nothing?
7. Does any statement drop or rewrite something that already holds data? If so, stop and ask why.

CI builds the database from nothing

The strongest check is the simplest: on every pull request, CI starts an empty Postgres, applies every migration in order and runs the tests. If a migration only worked because of something clicked into the live dashboard, it fails here, before it can merge. GitHub Actions can start a throwaway Postgres for each run at no cost within the free minutes ^[3].