12/5: Backend - Supabase setup, data modeling & RLS foundations

Daily Agenda

1 ~ Auth and database modeling

We start in the Supabase database diagram. The first table we model together is users_data.

Supabase already owns the authentication table in the auth schema. That means the user's password, auth provider state, email verification state, JWT identity and login metadata are handled by Supabase Auth. We do not create our own password column and we do not store passwords in public.

Instead, our app creates a public profile row that points back to the authenticated user.

users_data

The important relationship is:

auth.users.id -> public.users_data.id

2 ~ Database design workshop

Now you will continue the database diagram for the rest of the application. We will describe the product behavior we need, and you decide which tables and relationships best support it.

The backend needs to support:

• A daily puzzle that is published for a specific date.
• Groups inside each puzzle, each with a name and difficulty.
• Words that belong to those groups.
• A user starting a puzzle attempt.
• Tracking mistakes, completion time and whether the user solved the puzzle.
• Tracking which groups the user has already solved inside an attempt so we can handle closing and opening the app in the middle of the attempt.

Think about:

• Which table owns which data.
• Where foreign keys should point.
• Which rows should be deleted together.
• Which fields need to be unique.
• Which fields are app state, like mistakes, solved state and timestamps.

3 ~ Local Supabase setup

Next we move from diagramming to a local Supabase project.

Initialize the project

Install the Supabase CLI before starting the local setup.

Create an empty folder for your backend project, open it in your terminal, and initialize Supabase there:

supabase init

Then start the local Supabase stack:

supabase start

This gives us a local API, local database, local Studio and local auth service. We use this so backend changes can be built and tested before they are pushed to the cloud project.

Connect to the cloud project

Create the Supabase project in the cloud dashboard, then connect your local project to it:

supabase login
supabase link

Split the schema into files

Inside supabase, create a schemas folder with these files:

This split keeps the backend readable:

Make sure the Supabase CLI reads those schema files when generating diffs. In supabase/config.toml, set schema_paths under [db.migrations]:

[db.migrations]
enabled = true
schema_paths = ["./schemas/*.sql"]

4 ~ users_data, trigger, RLS and migration

We now implement the first real backend slice.

Extensions

In 000_extensions.sql, enable the extension that updates updated_at for us:

create extension if not exists moddatetime schema extensions;

Table

In 002_tables.sql, create users_data:

create table public.users_data (
  id uuid primary key references auth.users (id) on delete cascade,
  display_name text,
  email text not null unique,
  updated_at timestamp not null default now(),
  created_at timestamp not null default now()
);

The id is both the primary key and a foreign key to auth.users. This gives us a one-to-one relationship between the auth identity and the public profile row.

Function

In 004_functions.sql, create a function that inserts the public profile row after a new auth user is created:

create or replace function public.handle_new_auth_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into users_data (id, email, display_name)
  values (
    new.id,
    new.email,
    new.raw_user_meta_data->>'display_name'
  )
  on conflict (id) do nothing;

  return new;
end;
$$;

security definer is important here because the trigger runs as part of the auth insert flow. The function needs permission to insert into public.users_data even though the user is only being created at that moment.

Triggers

In 005_triggers.sql, connect the function to auth.users:

create trigger on_auth_user_created
after insert on auth.users
for each row
execute function public.handle_new_auth_user();

Also add the timestamp trigger for profile updates:

create trigger handle_updated_at before update on public.users_data
  for each row execute procedure extensions.moddatetime(updated_at);

RLS

In 006_rls.sql, enable RLS and allow authenticated users to work only with their own profile row:

alter table public.users_data enable row level security;

create policy "Users can CRUD themselves"
  on public.users_data
  for all
  to authenticated
  using ((select auth.uid()) = id)
  with check ((select auth.uid()) = id);

RLS is the API security layer. The frontend can use the public anon key, but each request also includes the user's JWT. Supabase evaluates auth.uid() from that JWT and only returns rows the policy allows.

Generate and push the migration

Generate the first migration from the schema files:

supabase db diff -f users_data

Then push it to the linked cloud project:

supabase db push

Test the auth flow

We will test the flow through the API:

5 ~ Tables, migrations and RLS workshop

Now you will implement the rest of the schema from your database diagram.

Create the enums and tables you designed in dbdiagram. Your SQL implementation should support the same product behavior we discussed earlier: daily puzzles, grouped words, user attempts, mistakes, solved state, completion time and solved groups.

Your implementation should cover:

• Correct primary keys.
• Correct foreign keys.
• Cascading deletes where child rows should disappear with the parent.
• Sensible defaults for generated ids and timestamps.
• RLS enabled on every public table.
• Policies that let authenticated users read shared puzzle data.
• Policies that let users read and mutate only their own attempt data.

You should also generate a migration and push it to the remote project when your local schema is ready.

6 ~ Views

Views let us package a useful query shape behind a simple API endpoint. The frontend does not need to manually join five tables if the backend can expose the exact shape it needs.

For the first view, we create a weekly summary of one user's puzzle attempts.

In 003_views.sql:

create or replace view public.user_weekly_attempts
with (security_invoker = true)
as
select
  ud.id as user_id,
  array_agg(
    jsonb_build_object(
      'date', d.day::date,
      'attempted', pa.user_id is not null,
      'completed_at', pa.completed_at,
      'puzzle_id', p.id,
      'solved', pa.solved
    )
    order by d.day
  ) as attempts
from public.users_data ud
cross join generate_series(
  current_date - interval '6 days',
  current_date,
  interval '1 day'
) as d(day)
left join public.puzzles p on p.published_at = d.day::date
left join public.puzzle_attempts pa on pa.puzzle_id = p.id and pa.user_id = ud.id
group by ud.id;

with (security_invoker = true) is important. It makes the view use the permissions and RLS rules of the user calling it instead of bypassing them with the view owner's permissions.

Now you will create the view in your project, generate a migration, push it, and test it through the API.

7 ~ Wrap-up

Today we built the foundation of the backend:

• Modeled the database around Supabase Auth.
• Set up local Supabase development.
• Connected local development to the cloud project.
• Created users_data.
• Added an auth trigger to automatically create profile rows.
• Added RLS for user-owned profile data.
• Started the rest of the application schema.
• Created the first frontend-friendly view.

Next backend session we continue from this foundation and focus on edge functions and expanding the API surface the frontend will use.