1. Creating Tables

You can create tables using:

• Supabase dashboard

• SQL editor

• Migrations

Example SQL:

create table users (
  id uuid primary key default gen_random_uuid(),
  email text not null,
  name text,
  created_at timestamp default now()
);

Key concepts:

• Primary key identifies each row

• UUID is preferred for distributed apps

• Default timestamps help auditing

2. Column Types (Common Choices)

Typical Supabase types:

• uuid → IDs

• text → strings

• integer → counts

• boolean → flags

• jsonb → flexible data

• timestamp → dates

Example:

create table products (
  id uuid primary key default gen_random_uuid(),
  title text,
  price integer,
  metadata jsonb,
  created_at timestamp default now()
);

Use jsonb carefully — great for flexible fields but harder to index.

3. Unique Constraints

Unique constraints prevent duplicate data.

Example email:

alter table users
add constraint users_email_unique unique (email);

Use cases:

• email

• username

• slug

• order number

You can also create composite unique:

unique (user_id, product_id)

Useful for carts, favorites, memberships.

4. Indexes (Performance Critical)

Indexes speed up queries.

Create index:

create index idx_orders_user_id on orders(user_id);

When to index:

• frequently filtered columns

• foreign keys

• realtime filters

• search fields

Example realtime chat index:

create index idx_messages_room on messages(room_id);

Without indexes, realtime and dashboards slow down.

5. Table Relationships (Foreign Keys)

Relationships connect tables.

Example:

create table orders (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references users(id),
  status text,
  created_at timestamp default now()
);

This enforces:

• valid user

• relational integrity

• easier joins

Supabase automatically exposes relations in queries.

Example:

supabase.from("orders").select("*, users(*)");

6. Many-to-Many Relationships

Use join tables.

Example:

users
projects
memberships

SQL:

create table memberships (
  user_id uuid references users(id),
  project_id uuid references projects(id),
  role text,
  primary key (user_id, project_id)
);

This enables roles per project.

7. Row Level Security (RLS)

RLS controls who can access rows.

Enable:

alter table orders enable row level security;

Example policy:

Users see only their orders:

create policy "Users can read own orders"
on orders
for select
using (auth.uid() = user_id);

This is essential for security.

8. Insert Policy Example

Allow users to create their own records:

create policy "Users can insert own orders"
on orders
for insert
with check (auth.uid() = user_id);

Now backend logic is enforced at database level.

9. Update Policy Example

create policy "Users update own orders"
on orders
for update
using (auth.uid() = user_id);

Prevents editing other users’ data.

10. Production Schema Patterns

Recommended structure:

• users

• profiles

• orders

• order_items

• notifications

• event tables

Split frequently updated data into smaller tables.

Performance Tips

• index foreign keys

• avoid huge rows

• separate event tables

• use composite unique where needed

• avoid overusing jsonb

Database design directly affects realtime performance.

Common Mistakes

• no indexes

• missing RLS

• using text IDs instead of uuid

• storing everything in one table

• forgetting unique constraints

These cause scaling issues.

Conclusion

Supabase database design combines traditional PostgreSQL best practices with modern realtime apps.

A solid schema gives you:

• better performance

• safer security

• scalable realtime

• cleaner backend logic

Tables, indexes, relationships, and RLS form the foundation of every production Supabase project.