Tables, columns and data

Tables in a SupaHost project are ordinary Postgres tables. Anything you create in the public schema can be read and written through the Data API, subject to grants and Row Level Security, so designing a table and securing it go together.

You can create tables two ways:

• Studio's table editor: point and click. Good for exploring.
• SQL: in Studio's SQL editor, through the MCP server's apply_migration, or over the SSH tunnel on Business. Better for anything you want to repeat on another project or a branch.

A worked example: an invoicing tool

Here is a small schema for an invoice app: customers, invoices and invoice lines.

create table public.customers (
  id bigint generated always as identity primary key,
  owner_id uuid not null default auth.uid() references auth.users (id) on delete cascade,
  name text not null,
  email text,
  created_at timestamptz not null default now()
);

create type public.invoice_status as enum ('draft', 'sent', 'paid', 'void');

create table public.invoices (
  id uuid primary key default gen_random_uuid(),
  owner_id uuid not null default auth.uid() references auth.users (id) on delete cascade,
  customer_id bigint not null references public.customers (id) on delete restrict,
  number text not null,
  status public.invoice_status not null default 'draft',
  due_on date,
  created_at timestamptz not null default now(),
  unique (owner_id, number)
);

create table public.invoice_lines (
  id bigint generated always as identity primary key,
  invoice_id uuid not null references public.invoices (id) on delete cascade,
  description text not null,
  quantity numeric(10, 2) not null check (quantity > 0),
  unit_price_cents bigint not null check (unit_price_cents >= 0)
);

create index on public.invoices (customer_id);
create index on public.invoice_lines (invoice_id);

alter table public.customers enable row level security;
alter table public.invoices enable row level security;
alter table public.invoice_lines enable row level security;

A few choices worth explaining:

• Primary keys. bigint generated always as identity is compact and fast. uuid default gen_random_uuid() is better when ids appear in URLs or are created on the client. Either works with the Data API.
• Ownership column. owner_id ... default auth.uid() fills in the signed-in user automatically on insert, which makes RLS policies simple.
• Foreign keys let the Data API join tables for you (select('*, customers(name)')). Postgres doesn't index the referencing column automatically, so add an index on it yourself.
• Money is stored as integer cents (or numeric), never float.

Choosing column types

Keep table and column names lowercase snake_case. Postgres folds unquoted names to lowercase, so createdAt would need quoting everywhere. See camelCase table or column names not working.

Making a table reachable, safely

Two things decide whether a request through the Data API can touch a table:

1. Grants. In the standard Supabase image, new tables in public are granted to anon, authenticated and service_role automatically. You can check what a table has:

   select grantee, string_agg(privilege_type, ', ') as privileges
   from information_schema.role_table_grants
   where table_schema = 'public' and table_name = 'invoices'
   group by grantee;
   

2. Row Level Security. With RLS enabled and no policies, anon and authenticated see nothing. Add policies for exactly what each role may do:

   create policy "Owners manage their invoices"
     on public.invoices for all
     to authenticated
     using ((select auth.uid()) = owner_id)
     with check ((select auth.uid()) = owner_id);
   

Enable RLS on every table in public, even ones you think nobody calls. The MCP server's get_advisors tool flags tables with RLS disabled or with RLS on but no policies. See Row Level Security and Securing your API.

Tables that should never be reachable from the API belong in a schema of their own (for example private), since only a fixed set of schemas is exposed. See Exposed schemas.

Reading and writing rows

From your app, with supabase-js:

const { data: invoice, error } = await supabase
  .from('invoices')
  .insert({ customer_id: 12, number: 'INV-0042', due_on: '2026-12-01' })
  .select()
  .single()

From SQL:

update public.invoices
set status = 'sent'
where number = 'INV-0042';

More on filtering and joins in Querying and filtering data.

Changing tables later

Common changes are quick:

alter table public.customers add column phone text;
alter table public.customers alter column email set not null;
alter table public.invoices rename column due_on to due_date;

Some changes rewrite the whole table or hold a lock while they check every row, which can take a long time on big tables. Adding a not null column with a volatile default, changing a column's type, or adding a foreign key to a large table are the usual suspects. See ALTER TABLE takes forever on a large table.

After a schema change the Data API reloads its schema cache automatically. If a new table or column doesn't show up, run notify pgrst, 'reload schema'; and see PostgREST not seeing new tables.

Related

• Indexes
• SQL migrations
• Importing data (CSV, SQL dumps)
• 42P01: relation does not exist