# Tables, columns and data

> Create and change tables in a SupaHost project with Studio or SQL: column types, primary keys, foreign keys, constraints, and making tables safe to reach through the Data API.

Source: https://supahost.app/docs/database/tables · Updated 2026-11-11

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.

```sql
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

| Data | Type | Notes |
|---|---|---|
| Short or long text | `text` | No need for `varchar(n)`; add a `check (char_length(x) <= n)` if you want a limit |
| Whole numbers | `int`, `bigint` | Use `bigint` for anything that might grow past 2 billion |
| Exact decimals | `numeric(p, s)` | Prices, quantities |
| True/false | `boolean` | |
| Points in time | `timestamptz` | Stored in UTC, returned with an offset |
| Calendar dates | `date` | |
| Identifiers | `uuid` | `gen_random_uuid()` is built in |
| Flexible documents | `jsonb` | See [JSON and JSONB columns](/docs/database/json) |
| Lists | `text[]`, `int[]` | Fine for short tag lists; use a join table for anything you query heavily |
| Fixed set of values | `enum` or a `check` constraint | Enums are awkward to remove values from later |

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](/kb/camelcase-identifiers).

## 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:

   ```sql
   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:

   ```sql
   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](/docs/database/row-level-security) and [Securing your API](/docs/api/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](/docs/api/exposed-schemas).

## Reading and writing rows

From your app, with supabase-js:

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

From SQL:

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

More on filtering and joins in [Querying and filtering data](/docs/api/querying).

## Changing tables later

Common changes are quick:

```sql
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](/kb/slow-alter-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](/kb/postgrest-schema-cache).

## Related

- [Indexes](/docs/database/indexes)
- [SQL migrations](/docs/database/migrations)
- [Importing data (CSV, SQL dumps)](/docs/database/import-data)
- [42P01: relation does not exist](/kb/relation-does-not-exist)