| name | supabase-migration |
| description | Use when changing kasane's Supabase schema — adding or altering a table, column, index, policy or grant, regenerating `src/types/supabase.ts`, or when asked about RLS, row-level security, or whether user data is exposed. |
Supabase Migration
kasane ships its Supabase publishable key in the client bundle (src/api/supabase.ts). That is correct and intended: it is anon-scoped. It also means RLS is the entire security boundary. A table created without policies is either invisible or wide open, and there is no server tier to catch the mistake.
The two data classes
| Class | Tables | Rule |
|---|
| Catalog (public, read-only) | series, arc_mappings, movies, search_aliases, genre_filters | for select using (true); grant select ... to anon, authenticated. No client writes, ever. |
| User data (owner-scoped) | user_progress, user_preferences | for all using (auth.uid() = user_id) with check (auth.uid() = user_id); grant select, insert, update, delete ... to authenticated only. |
A new table joins one of these classes. If it fits neither, that is the design question to resolve before writing SQL.
Every migration
- Look first.
list_tables, and read supabase/migrations/20260715000000_kasane_catalog_and_user_data.sql for the established shape.
- Write the file as
supabase/migrations/<YYYYMMDDHHMMSS>_<slug>.sql. Keep the existing style: lower-case SQL, a comment block per section, bigint generated always as identity primary keys, timestamptz not null default now() for created_at/updated_at.
- Every new table needs four things. Omitting any one is the bug:
alter table public.<t> enable row level security;
create policy "<t> are readable by everyone" on public.<t> for select using (true);
grant select on public.<t> to anon, authenticated;
create index <t>_<col>_idx on public.<t> (<fk or filtered column>);
enable row level security without a policy denies everyone, including the app. A policy without enable row level security does nothing at all. Both failure modes are silent at migration time.
with check on anything writable. using gates which rows are read and updated; with check gates what a row may become. A user-writable policy with only using lets a user reassign a row to another user_id.- Apply via
apply_migration (service role, bypasses RLS), never the app's publishable key.
- Regenerate types, which the data layer reads directly:
bunx supabase gen types typescript --project-id obtgldkascmxbtpnvscn > src/types/supabase.ts
bun run typecheck
- Run the advisors.
get_advisors with the security lint catches missing RLS, exposed views, and mutable search paths. Treat its output as part of the migration, not optional.
- Verify as an anonymous client, not as the service role:
set local role anon;
select * from public.<t> limit 1;
insert into public.<t> ...;
Consumers to update
A catalog column change ripples: src/types/supabase.ts → the row-to-domain converters in src/data/catalog.ts (toArc, toMovie, rowToMapping) → the SeriesMapping types in src/types/index.ts → scripts/audit-mappings.ts. Grep for the column name before assuming the migration is done.
The catalog is fetched once and cached for 7 days, so a removed column keeps arriving from stale client caches. Prefer adding a nullable column over renaming one.
Common mistakes
| Mistake | Consequence |
|---|
enable row level security with no policy | The app silently reads zero rows. |
A policy with no enable row level security | The table is fully open to anon. |
Writable policy with using but no with check | A user can reassign a row to someone else's user_id. |
Granting insert on a catalog table to authenticated | Any signed-up user can rewrite the mappings every client trusts. |
| Editing the existing migration file | It has already run. Add a new timestamped file. |
Forgetting generate_typescript_types | src/types/supabase.ts drifts and typecheck passes on a schema that no longer exists. |
| Dropping or renaming a column in one step | Clients hold a 7-day cached catalog and break. Add nullable, migrate, remove later. |
