Postgres best practices
Postgres schema design for Supabase apps: rules that prevent bugs
Use `uuid` primary keys, `timestamptz` for every time, `numeric` for money, `not null` and `check` constraints for every rule you can state, and `jsonb` only for truly unstructured data. Keep internal tables out of the exposed `public` schema, and turn on RLS in the same migration that creates each table.
Last updated
Which column types should I use?
- Primary keys: `uuid default gen_random_uuid()`. IDs are not guessable and can be made on the client.
- Times: `timestamptz`, never `timestamp`, so time zones never shift your data.
- Money: `numeric(12,2)` or integer cents, never `float`.
- JSON: `jsonb`, not `json`, and only for data with no fixed shape.
- Status fields: `text` with a `check` constraint, which is easier to change than an enum type.
create table public.invoices (
id uuid primary key default gen_random_uuid(),
tenant_id uuid not null references public.tenants (id) on delete cascade,
amount numeric(12,2) not null check (amount >= 0),
status text not null default 'draft' check (status in ('draft', 'sent', 'paid', 'void')),
created_at timestamptz not null default now()
);
create index invoices_tenant_idx on public.invoices (tenant_id);
alter table public.invoices enable row level security;What belongs in the public schema?
Supabase exposes the `public` schema through its API. Put only the tables your frontend reads there. Audit logs, job queues, webhook payloads and anything only the server touches belong in a separate schema that is not in the API's exposed schemas list.
create schema if not exists internal;
create table internal.webhook_events (
id text primary key,
payload jsonb not null,
received_at timestamptz not null default now()
);Why put constraints in the database?
AI-generated frontends call your tables directly. Any rule that lives only in client code can be skipped by a user with the anon key and curl. `not null`, `check`, `unique` and foreign keys hold no matter who writes the row.
Frequently asked questions
- uuid or bigint identity keys?
- Both work. `uuid` hides row counts and can be made on the client. `bigint generated always as identity` is smaller and faster to index. For tables exposed to the browser, `uuid` avoids guessable IDs.
- Should I use Postgres enums?
- Text with a `check` constraint is easier to change later. Enum types need a migration to add or remove values and cannot easily drop a value.
- How do I stop AI tools from creating unsafe tables?
- Ask for migrations that include `enable row level security` and policies for every new table, then run the RLS check query from the RLS guide after every change.