Database

Every table, function, policy and trigger in an IsotopeAI Supabase project. The figures below were read from a live schema, not inferred from the migration files.

Check your own schema #

Run this in the Supabase SQL editor to compare your project against the reference figures above:

select
  (select count(*) from information_schema.tables
     where table_schema = 'public' and table_type = 'BASE TABLE')      as tables,
  (select count(*) from pg_proc p join pg_namespace n on n.oid = p.pronamespace
     where n.nspname = 'public' and p.prokind = 'f')                   as functions,
  (select count(*) from pg_policies where schemaname = 'public')        as policies,
  (select count(*) from pg_trigger t join pg_class c on c.oid = t.tgrelid
     join pg_namespace n on n.oid = c.relnamespace
     where n.nspname = 'public' and not t.tgisinternal)                as triggers,
  (select count(*) from pg_indexes where schemaname = 'public')         as indexes;

The repository also ships a drift checker that fails if the install schema falls behind the live database:

npm run schema:drift

Users and profile #

public.users is the hub. It mirrors auth.users by primary key and carries the application-level fields. Everything else hangs off it.

Study data #

The widest tables in the schema. focus_sessions has 41 columns because a session records subject, task, interruptions, timing mode and derived totals in one row.

Stats and gamification #

These are the tables the leaderboard reads. Both stats tables carry a public SELECT policy plus own-row write policies — that combination is what makes a leaderboard possible without exposing write access.

Groups #

Avoiding infinite RLS recursion #

A membership policy that queries group_members directly re-triggers the policy on group_members, and Postgres aborts with an infinite-recursion error. Every membership check therefore goes through a SECURITY DEFINER helper, which runs with the owner's rights and bypasses the policy it would otherwise re-enter:

-- correct: definer function breaks the cycle
create or replace function public._is_group_member(gid uuid, uid uuid)
returns boolean language sql stable security definer
set search_path = public as $$
  select exists (
    select 1 from public.group_members
    where group_id = gid and user_id = uid
  );
$$;

-- wrong: this recurses and fails at query time
-- using (exists (select 1 from group_members where ...))

npm run security:verify asserts that the anon key cannot trigger recursion on any policy.

Community #

Sync and system #

The sync quartet #

Six tables carry the same four columns, and the sync engine depends on all of them:

version

Monotonic counter. Higher wins when content hashes differ.

content_hash

Stable hash of the row payload. Equal hashes mean no write is needed.

last_synced_at

When this row last reconciled with the cloud.

deleted_at

Tombstone. A soft delete must replicate; a hard delete cannot.

device_id

Which device produced the write, used to break ties.

They exist on users, user_profiles, user_settings, notifications, study_sessions_log and daily_user_stats.

Triggers #

15 triggers. Fourteen live in public, and six of those fire on public.users so a new account is never missing a satellite row — the alternative is an empty leaderboard or a non-functional community tab, caused by a row that was never created.

The fifteenth is the one that matters most, and it is easy to lose: it lives on auth.users, not in public.

It was also absent from the schema dump, because both dump tools exclude the auth schema — correct for tables, wrong for a trigger attached to one. So a restored project passed every check and could not accept a signup. Detect it with:

select count(*) from auth.users u
 where not exists (select 1 from public.users p where p.id = u.id);
-- non-zero => the trigger is missing; supabase/022 restores it and backfills

Or run ./supabase.sh check, which asserts it unconditionally.

Functions #

80 CREATE FUNCTION statements covering 72 distinct names — eight are overloads sharing a name with a different signature. 30 of them are the community_* RPC surface the frontend calls directly through PostgREST.

Pinned search paths #

Every SECURITY DEFINER function pins its search_path. Without that, a caller who can create objects in an earlier schema could shadow a table name and have the function operate on their object with the definer's privileges:

-- find any definer function missing a pinned search_path
select p.proname, pg_get_function_identity_arguments(p.oid) as args
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public' and p.prosecdef and p.proconfig is null;
-- expected: zero rows

To fix one without touching its body:

alter function public.my_function(uuid, integer) set search_path = public;

RLS model #

Three patterns cover almost every table:

(select auth.uid()) rather than a bare auth.uid() is deliberate: the subquery form is evaluated once per statement instead of once per row, which matters on large tables.

Indexes #

66 declared indexes, 124 in total. Both numbers are correct and the difference is not a discrepancy: a PRIMARY KEY or UNIQUE constraint creates an index of its own, so pg_indexes reports 124 while isotope-complete.sql contains 66 explicit CREATE INDEX statements. If you are checking a project against the schema file, 66 is the figure to compare; if you are reading pg_indexes, expect 124.

The ones that matter most for perceived speed:

• group_members(group_id, user_id) — covers the membership subquery used by every gated policy.
• groups(slug) — group lookup by URL.
• daily_user_stats(user_id, date) — leaderboard range scans.
• A GIN index on groups.fts — full-text discovery search.

groups.fts is a generated column, so the search vector cannot drift from the source text:

fts tsvector generated always as (
  to_tsvector('english',
    coalesce(name, '') || ' ' || coalesce(description, '') || ' ' || coalesce(category, ''))
) stored

Schema files #