Data Models

Logical to Physical Data Model: Building the Physical ERD

Translate an approved logical model into tables, types, keys, constraints and access policies, and see which decisions belong only at the physical layer.

What the Physical Model Adds

The physical model takes each logical decision and says how the database will enforce it. Nothing in it should be new meaning. Every table, column and constraint traces back to something in the logical model.

Logical model	Physical model
Entity	Table (plural, snake_case)
Business identifier	A `UNIQUE` column, plus a surrogate `uuid` primary key
One-to-many relationship	A foreign key column on the many side, with a chosen `ON DELETE` rule
Associative entity	A table with its own key and a composite `UNIQUE` across the pair
Reference set	A lookup table seeded by migration, or a `CHECK` for a tiny fixed list
Derived attribute	A view or a query, not a column
Rule ("due date is after borrow date")	A `CHECK` constraint
Who may read and write	Row-level security enabled, plus one policy per operation

Surrogate Keys and Business Keys: Keep Both

Business identifiers change. Asset tags get reprinted, and member numbers get merged when two cards turn out to be one person. A surrogate uuid never changes, so foreign keys point at it. The business identifier stays as a UNIQUE column, which means a duplicate is refused by the database, not discovered in a report.

Decisions That Exist Only Here

Types. Use timestamptz for moments, date for calendar days, and citext for emails so that case differences do not create duplicates.
Indexes. Index every foreign key column and every column you filter by. The database does not index foreign keys for you.
Delete behaviour. ON DELETE RESTRICT means a member with loan history cannot be deleted by accident. CASCADE means their child rows go with them. Pick one on purpose, and write down why.
Access. Enable row-level security on every table, and write a separate policy for each operation. An append-only table such as loan_events gets no update or delete policy at all, so history cannot be rewritten through the API.

Physical ERD of the tool library as six tables with row-level security on each: members, tools, reservations, loans, loan_events and the tool_categories reference table. Every column has a type, business identifiers are kept as unique columns beside uuid primary keys, and every relationship is now a foreign key. The takeaway: every table traces to an entity or reference set in the logical model, and a table with nothing behind it is a defect. — 2026-09-24 - provenance: rendered from the committed SVG in public/diagrams; alt re-read against the drawing on the same date

The Migrations Are the Physical Model

Do not maintain a hand-drawn physical ERD beside your migrations. It will drift from them within a week. The migrations are the physical model. When you need a picture, generate it from the live database.

That sounds like the "photograph" warned about at the start of this section, and the difference is the point. A physical diagram claims only to show what exists, so generating it from what exists is honest. The logical model is the one that must be written by hand and approved, because its job is to say what should exist. Then you compare the two.

Each migration should name the entity it builds, in a comment at the top. A migration that cannot name its entity is either building something nobody approved, or it has found a gap in the logical model. Either way, stop and fix the model first.

Key Takeaways

The physical model adds how the data is stored. It adds no new meaning.
Keep a surrogate uuid key and a unique business identifier side by side.
Index foreign keys, choose delete behaviour deliberately, and turn on row-level security with one policy per operation.
Derived values become views, and rules become constraints.
The migrations are the physical model. The approved logical model is the thing they are checked against.

Example

sql

-- Entity: LOAN  (logical model v1, approved)
create table public.loans (
  id           uuid primary key default gen_random_uuid(),
  loan_number  text not null unique,
  member_id    uuid not null references public.members (id) on delete restrict,
  tool_id      uuid not null references public.tools (id)   on delete restrict,
  borrowed_on  date not null,
  due_on       date not null,
  constraint due_after_borrowed check (due_on >= borrowed_on)
);
create index loans_member_id_idx on public.loans (member_id);
create index loans_tool_id_idx   on public.loans (tool_id);

-- Entity: LOAN EVENT  (append-only)
create table public.loan_events (
  id           uuid primary key default gen_random_uuid(),
  loan_id      uuid not null references public.loans (id) on delete restrict,
  seq          integer not null,
  kind         text not null check (kind in ('out', 'renewed', 'returned')),
  occurred_at  timestamptz not null default now(),
  recorded_by  uuid not null references auth.users (id),
  unique (loan_id, seq)                 -- the logical identifier: loan + sequence
);
create index loan_events_loan_idx on public.loan_events (loan_id, occurred_at desc);

-- Row-level security ON at the table level, then one policy per operation.
-- is_staff() is defined in an earlier migration.
alter table public.loans       enable row level security;
alter table public.loan_events enable row level security;

create policy loans_select_staff on public.loans
  for select to authenticated using (public.is_staff());
create policy loans_insert_staff on public.loans
  for insert to authenticated with check (public.is_staff());

create policy loan_events_select_staff on public.loan_events
  for select to authenticated using (public.is_staff());
create policy loan_events_insert_staff on public.loan_events
  for insert to authenticated
  with check (public.is_staff() and recorded_by = auth.uid());
-- Deliberately NO update or delete policy on loan_events: history is append-only.

Example — SQL

-- Entity: LOAN  (logical model v1, approved)
create table public.loans (
  id           uuid primary key default gen_random_uuid(),
  loan_number  text not null unique,
  member_id    uuid not null references public.members (id) on delete restrict,
  tool_id      uuid not null references public.tools (id)   on delete restrict,
  borrowed_on  date not null,
  due_on       date not null,
  constraint due_after_borrowed check (due_on >= borrowed_on)
);
create index loans_member_id_idx on public.loans (member_id);
create index loans_tool_id_idx   on public.loans (tool_id);

-- Entity: LOAN EVENT  (append-only)
create table public.loan_events (
  id           uuid primary key default gen_random_uuid(),
  loan_id      uuid not null references public.loans (id) on delete restrict,
  seq          integer not null,
  kind         text not null check (kind in ('out', 'renewed', 'returned')),
  occurred_at  timestamptz not null default now(),
  recorded_by  uuid not null references auth.users (id),
  unique (loan_id, seq)                 -- the logical identifier: loan + sequence
);
create index loan_events_loan_idx on public.loan_events (loan_id, occurred_at desc);

-- Row-level security ON at the table level, then one policy per operation.
-- is_staff() is defined in an earlier migration.
alter table public.loans       enable row level security;
alter table public.loan_events enable row level security;

create policy loans_select_staff on public.loans
  for select to authenticated using (public.is_staff());
create policy loans_insert_staff on public.loans
  for insert to authenticated with check (public.is_staff());

create policy loan_events_select_staff on public.loan_events
  for select to authenticated using (public.is_staff());
create policy loan_events_insert_staff on public.loan_events
  for insert to authenticated
  with check (public.is_staff() and recorded_by = auth.uid());
-- Deliberately NO update or delete policy on loan_events: history is append-only.

Where You'll See This in the Real World

Supabase, Postgres and most managed databases make row-level security available but leave it off until you turn it on. A table without it is readable by anyone who holds the public key. Reviewing the physical model for "row-level security on, one policy per operation, no update policy on history tables" catches in minutes a class of data leak that otherwise shows up in a security report.