DB Schema: Two Meanings, Six Tables, and 271 Lines of Postgres

A database schema is the declared structure of a database: the tables, the columns and their types, the keys that connect them, and the constraints that decide which rows are allowed to exist. It is written in SQL, versioned in your repository, and applied by migrations — not drawn in a diagram tool and hoped for.

That is the textbook answer. The useful one needs a real schema to point at, so this post reads one: the commerce schema behind this storefront, 6 tables and 271 lines of SQL across 8 migration files in supabase/migrations/. Every snippet below is copied from that directory, including the two migrations that exist because the first version of the schema was wrong.

First: "schema" means two different things in Postgres

This is the single biggest source of confusion in the question what are schemas in a database, and almost nothing that ranks for it says so plainly.

Both are correct. Postgres uses the second sense in its own DDL, which is why the first line of our first migration reads like this:

-- supabase/migrations/0001_commerce_init.sql
create extension if not exists citext with schema extensions;

That installs the case-insensitive text type into a namespace called extensions rather than into public. The reason is security, not tidiness: anything in public is reachable by default name resolution, so extension functions living there widen the surface a search_path attack can reach. Putting them in their own namespace means every reference has to be explicit — which is why the email column two dozen lines later is typed extensions.citext, not citext.

So when someone asks for "the schema", ask which one they mean. In practice they want the structure; Postgres will keep meaning the namespace.

A real schema, read top to bottom

Six tables, each with one job:

Here is the whole orders table, unedited:

create table public.orders (
  id uuid primary key default gen_random_uuid(),
  ls_order_id text not null unique,
  ls_customer_id text,
  email extensions.citext not null,
  tier text not null check (tier in ('t1','t2','t3')),
  total_cents int,
  currency text,
  status text not null default 'paid' check (status in ('paid','refunded')),
  user_id uuid references auth.users(id) on delete set null,
  raw jsonb,
  created_at timestamptz not null default now()
);
create index orders_user_id_idx on public.orders (user_id);

Eleven columns, and almost every design decision in the system is visible in them:

• ls_order_id text not null unique — the webhook that creates this row can fire twice. The unique constraint is what makes the second delivery a no-op instead of a duplicate order. Idempotency is a schema feature here, not application code.
• total_cents int — money as integer minor units. Never float; 19.99 is not representable in binary floating point and the rounding error compounds.
• email extensions.citext — Alice@Example.com and alice@example.com are the same buyer. Enforcing that in the column type means no call site can forget to lowercase.
• check (status in ('paid','refunded')) — the set of legal states, declared once. A typo in application code becomes a failed write rather than a row nobody's queries match.
• on delete set null — deleting a user must not delete their order history. Contrast profiles, where the same relationship is on delete cascade, because a profile without a user is meaningless.

The constraints are the interesting part. A column list tells you what can be stored; the constraints tell you what the business actually believes. That is the difference between a data schema (the shape) and a database schema that is doing its job.

Rows are one thing; who can read them is another

Structure is only half of what ships. The other half is the access rule attached to each table:

alter table public.orders enable row level security;
create policy "own orders" on public.orders
  for select using ((select auth.uid()) = user_id);

Six tables, six policies, and all six are for select only. There is deliberately no insert, update or delete policy for any client role — every write goes through server code holding the service-role key, which bypasses row-level security entirely. A browser can read its own rows and cannot write any.

Worth knowing: RLS is bypassed by the service-role key, but CHECK constraints are not. They apply to every writer including your own backend, which makes them the last honest line of defence in the schema.

Schemas change. Ours changed four times, and here is why

The part that tutorials skip. A schema is not a thing you design once — these four migrations each exist because reality arrived.

1. A denylist that should have been an allowlist

Migration 0001 validated display names by listing what was banned:

display_name text check (
  display_name is null
  or (char_length(display_name) between 1 and 40 and display_name !~ '[<>]')
)

That is a denylist, and denylists are wrong by construction — you are betting you thought of every bad character. 0002 inverts it:

-- supabase/migrations/0002_display_name_allowlist.sql
alter table public.profiles drop constraint if exists profiles_display_name_check;
alter table public.profiles add constraint profiles_display_name_check
  check (display_name is null or display_name ~ '^[[:alnum:] _''.-]{1,40}$');

The migration comment records what was verified before it shipped: POSIX [[:alnum:]] is Unicode-aware in a UTF-8 database, so José, 山田太郎 and Mary-Jane O'Brien all pass, while <, & and " are rejected. An allowlist that quietly breaks non-Latin names is a worse bug than the one it fixes, so that check was a precondition, not an afterthought.

2. Cascade was the wrong deletion behaviour

download_events originally cascaded: delete the user, delete their download history. Then the privacy policy and the abuse-trail requirement collided — the audit record has to survive a self-delete, anonymised. That is a schema change, not a code change:

-- supabase/migrations/0005_retain_anonymized_download_audit.sql
alter table public.download_events alter column user_id drop not null;
alter table public.download_events drop constraint download_events_user_id_fkey;
alter table public.download_events add constraint download_events_user_id_fkey
  foreign key (user_id) references auth.users(id) on delete set null;

not null had to go first: you cannot set a column to null on delete while the column forbids null. Ordering like that is why migrations are files in sequence rather than a desired-state diff.

3. The pricing model changed, so the allowlist did

When the licence tiers were restructured, one CHECK carried the change:

-- supabase/migrations/0006_pricing_v2_slot_kinds.sql
alter table public.entitlement_slots
  drop constraint if exists entitlement_slots_kind_check;
alter table public.entitlement_slots
  add constraint entitlement_slots_kind_check
  check (kind in ('template_single', 'all_templates', 'all_access'));

Nothing else needed to move, because the function that inserts slots takes the kinds as an array parameter and is indifferent to their values. A schema that localises a policy change to one constraint is a schema that was factored correctly.

4. A new feature brought two tables and a partial index

Self-service refunds needed a record of successful deliveries, kept separate from the rate-limit ledger so that control stayed untouched, plus a request table with a lifecycle. The interesting line is the index:

-- supabase/migrations/0008_refund_requests.sql
create unique index refund_requests_one_pending
  on public.refund_requests (order_id) where status = 'pending';

A partial unique index — unique only across the rows matching the where clause. One pending request per order, any number of resolved ones. The alternative is a select then an insert in application code, which two concurrent clicks defeat. This cannot be defeated: the second insert fails at the database.

The bug a schema fixed: check-then-act

The download endpoint capped downloads per user per hour. Version one counted with a function, then inserted an audit row — two round trips:

create or replace function public.downloads_in_last_hour(uid uuid)
returns int language sql stable security invoker
set search_path = public, pg_temp as $$
  select count(*)::int from public.download_events
  where user_id = uid and created_at > now() - interval '1 hour';
$$;

That is a time-of-check/time-of-use race (CWE-367). Fire N parallel requests, all N read the same count, all N pass the < limit test, all N insert. The cap is advisory.

The fix, 0007, moves the count and the insert into one call and serialises them per user:

-- supabase/migrations/0007_atomic_download_rate_limit.sql
perform pg_advisory_xact_lock(hashtextextended(p_user_id::text, 0));

select count(*)::int into recent
from public.download_events
where user_id = p_user_id
  and created_at > now() - interval '1 hour';

if recent >= p_limit then
  return -1;  -- over limit — record nothing
end if;

insert into public.download_events
  (user_id, product_slug, framework, version, ip, user_agent)
values
  (p_user_id, p_product_slug, p_framework, p_version, p_ip, p_user_agent);

return recent + 1;

The lock is transaction-scoped, so it releases on commit, and it is keyed on a hash of the user id, so two different users never contend. The migration also records the assumption the fix depends on: correctness relies on READ COMMITTED isolation — the Postgres and Supabase default — because the select takes its snapshot after the previous lock holder committed. Under REPEATABLE READ the snapshot could freeze earlier and the second caller could read a stale count.

Writing that assumption into the migration is part of the schema too. The next person to change the isolation level gets a warning instead of a mystery.

All four functions in this schema carry set search_path = public, pg_temp, and all four have EXECUTE revoked from anon and authenticated — the two SECURITY DEFINER ones especially, since those run with the definer's privileges and an unpinned search_path is how that gets turned against you.

Schema, migration, model: the vocabulary

Mistakes and how they show up

Frequently asked questions

What is a database schema, in one sentence? The declared structure of a database — its tables, columns, data types, keys and constraints — expressed in SQL and applied through versioned migrations.

What is the difference between a database and a schema? A database is the whole store. A schema, in Postgres's own vocabulary, is a namespace within it that groups tables and functions — public is the default, and auth and extensions sit beside it. In casual speech "schema" instead means the design of everything in the database.

Do I need a schema diagram? A diagram is a reading aid, never the source of truth. The SQL in your migrations folder is the schema; a diagram generated from it is useful, and a diagram maintained by hand alongside it will be wrong within a month.

Where should validation live — the schema or the app? Both, and for different reasons. The app gives a good error message; the schema makes the bad row impossible. The display-name rule above exists in application-level validation and as a CHECK, because CHECK constraints still apply to writes made with the service-role key that bypasses row-level security.

How big should a schema be before it needs migrations? From the first table. These 271 lines are trivially small, and the value of the 8 files is not size — it is that four of them record why the structure changed, which no snapshot of the current state can tell you.

Templates in this post

ASoc Vault is a fintech/neobank landing template — unified balances, instant transfers, multi-currency FX and a three-tier pricing table — the kind of product whose backing schema has exactly the money-as-integers and state-machine constraints above. ASoc Vox markets an AI voiceover engine with a generate/clone/download workflow and tiered plans, and ASoc Weave an AI website builder with a Starter/Growth/Scale table and a six-app integrations grid — both entitlement models that live or die on a correct kind allowlist.

Browse the full sets: Next.js landing page templates, Tailwind landing page templates.