Living document for the Security & RLS layer. Every migration that touches RLS / policies / triggers / views / grants is logged here so the access model can be reconstructed from this file alone.

Last reconciled with the current codebase: 6 October 2026. The current checkout contains migrations through 0055_student_curriculum_access.sql; npm run db:status and npm run db:check-rls remain the authority for a specific database.

• Drizzle (src/db/schema.ts, drizzle/) owns table DDL only - columns, types, foreign keys, indexes, enums.
• Raw SQL (supabase/migrations/) owns everything else - RLS policies, triggers, functions, views, grants, storage buckets and policies.

Never re-define a policy or trigger in Drizzle. Drizzle doesn't track raw SQL files; mixing the two silently drifts.

drizzle-kit push reconciles the DB to src/db/schema.ts. Because every RLS policy + the lesson_notes_safe view live in raw SQL (not schema.ts), push sees them as "drift" and disables RLS + drops every policy + drops the view on ALL tables - silently, and --force skips the confirmation. Functions/triggers survive; RLS/policies/views do not. (This caused a full RLS wipe on 2026-07-01; see 0012.)

npm run db:push is therefore blocked by a guard (scripts/db-push-guard.mjs). Safe schema-change workflow:

1. Update src/db/schema.ts (for Drizzle types only).
2. Write the DDL as a raw-SQL ALTER TABLE in a new supabase/migrations/NNNN_*.sql, and apply it with node scripts/apply-sql.mjs supabase/migrations/NNNN_*.sql.
3. Run npm run db:check-rls - audits every table and fails if any lost RLS. Run this after ANY database change.

If db:push ever runs, treat it as a security incident. Stop application writes, capture the resulting schema and database status, and compare them with the ordered files in supabase/migrations/. Restore from a known-good backup or reapply only the exact affected raw-SQL objects after review; do not blindly replay an old fixed migration range. Then run npm run db:status and npm run db:check-rls before reopening access.

node scripts/apply-sql.mjs supabase/migrations/<file>.sql

Prefers DIRECT_URL (session pooler :5432) over DATABASE_URL (transaction pooler :6543). The transaction pooler is not safe for DDL - no advisory locks, no session-level state, prepared statements unreliable. Migrations must use the session pooler.

The runtime Next.js app uses DATABASE_URL (transaction pooler) for connection scaling. That's intentional and unrelated.

File: supabase/migrations/0001_profile_sync_trigger.sql Status: Applied to live project. Risk: Low. Additive; no existing data modified.

What it does: Adds public.handle_new_auth_user() (security definer) + after insert trigger on auth.users. When a new auth user is created, a matching public.profiles row is inserted automatically. Role + name are read from app_metadata first, falling back to user_metadata.

Idempotency: on conflict (id) do nothing on the insert; drop trigger if exists + create or replace function on definition.

Failure mode: If neither metadata bag has role, function logs a warning and skips the profile insert. Auth signup itself still succeeds.

Reversible by:

drop trigger if exists on_auth_user_created on auth.users;
drop function if exists public.handle_new_auth_user;

File: supabase/migrations/0002_role_to_app_metadata.sql Status: Applied to live project. 20/20 users backfilled. Risk: Low. Only writes to auth.users.raw_app_meta_data; preserves existing keys via || merge.

What it does: Copies role, first_name, last_name from raw_user_meta_data into raw_app_meta_data for every auth user that has them. Uses jsonb_strip_nulls so missing fields don't write literal nulls; || merge preserves Supabase-managed keys (provider, providers).

Why this matters: user_metadata is user-mutable via the Supabase client SDK - a signed-in user could set role='admin' on themselves. app_metadata is server-only.

Reversible by:

update auth.users set raw_app_meta_data =
  raw_app_meta_data - 'role' - 'first_name' - 'last_name';

File: supabase/migrations/0003_lesson_notes_safe_view.sql Status: Applied to live project. Risk: Low. Adds a read-only view; no base-table mutations.

What it does: Creates public.lesson_notes_safe over public.lesson_notes that (a) omits the internal_note column and (b) self-enforces row visibility for student / parent / tutor / admin via a WHERE clause inside the view.

Why a view (not column privileges): Hiding internal_note from students/parents while keeping it visible to tutors/admins is a column-level concern that RLS alone (row-level) can't express. The view route is explicit; new sensitive columns are caught by the explicit column list.

Why security_invoker=false: The view bypasses RLS on the base table so its own WHERE clause is the sole gate. Migration 0004 locks lesson_notes (base table) to tutors + admins only - students and parents have no policy on the base table, so this view is their only path, and internal_note is unreachable for them by construction.

Grants: revoke all from anon and authenticated, then grant SELECT only to authenticated. Supabase's default privileges grant the full DML set to those roles on new views; we have to revoke explicitly.

Supabase Advisor warning: This view is flagged "Security Definer View - CRITICAL". That's a generic check against any security_invoker=false view. In our case the pattern is intentional and documented; the WHERE clause + revoked DML grants ARE the access control. The warning can be dismissed.

File: supabase/migrations/0004_rls_enable_and_policies.sql Status: Applied to live project. Verified via JWT impersonation matrix. Risk: HIGH at apply time - locks every public table behind RLS. Wrapped in BEGIN/COMMIT so partial failures roll back fully.

What it does: Enables RLS on all 14 (now 15, see 0005) public tables and writes policies implementing the PRD access matrix. Defines 10 SECURITY DEFINER helper functions used by policies: is_admin, is_parent_of, is_enrolled_in, is_tutor_of_class, is_parent_of_enrolled_in, is_tutor_of_student, is_tutor_of_lesson, has_homework_assignment, is_parent_of_assignee, is_tutor_of_homework.

Why SECURITY DEFINER helpers (not inline EXISTS): Inline EXISTS subqueries in policies trigger RLS on the queried table. Two policies that reference each other's tables produce infinite recursion in policy (42P17). Helpers run as OWNER, bypassing RLS during the lookup. search_path is pinned (public, auth) on every helper to defeat search-path injection.

Why service_role still works: Supabase grants bypassrls to service_role. All server-side scripts (seed-users.mjs, seed-demo.mjs, rename-users.mjs, seed-availability.mjs) and any Next.js API route using the service role continue to work without policy exceptions. Same for raw psql / DIRECT_URL connections (postgres role bypasses RLS).

Why server-side Drizzle queries are unaffected: src/db/client.ts connects via DATABASE_URL as the postgres role, which also bypasses RLS. Trusted server-side reads/writes through Drizzle do not need policy exceptions. RLS is the defense-in-depth layer for any direct client-side query that goes through the Supabase JS SDK with a JWT.

Reversible by: alter table public.<name> disable row level security; per table.

File: supabase/migrations/0007_homework_grading_lock.sql Status: Applied to live project, verified 2026-05-27 (10/10 checks pass). Risk: Low. Adds a BEFORE UPDATE trigger; no schema or data change.

What it does: Adds a BEFORE UPDATE trigger on public.homework_assignments that silently reverts changes to score, feedback, marked_at, marked_by unless the caller is an admin or the homework's authoring tutor. Closes security-checklist A9 (caveat §2 from migration 0004).

Why this matters: Migration 0004's homework_assignments_student_update RLS policy is row-restricted (own assignment) but not column-restricted. A student calling supabase.from('homework_assignments').update({ score: 100 }).eq(...) via the JS SDK would have their UPDATE accepted at the RLS layer. This trigger enforces column-level rules at the DB layer.

Carve-outs (trusted callers, no restriction):

• current_user not in ('authenticated', 'anon') - postgres role (Drizzle via DATABASE_URL) or service_role.
• public.is_admin() - admin via authenticated JWT.
• public.is_tutor_of_homework(homework_id) - the homework's authoring tutor via authenticated JWT.

Why SECURITY INVOKER (not DEFINER): With SECURITY DEFINER, current_user inside the function is always the function owner (postgres), so the bypass check would always succeed for any caller. SECURITY INVOKER makes current_user reflect the actual calling role. The helpers this function uses (is_admin, is_tutor_of_homework) are themselves SECURITY DEFINER and handle their own table access.

Behavior is silent revert, not error: the student's UPDATE statement appears to succeed (no error returned), but the disallowed columns retain their previous values. Optimistic UI in the student portal will not throw - it just won't visibly change the grade. If a future audit shows a student attempted such an UPDATE, the audit log (migration 0006) captures the attempt as an INSERT/UPDATE row.

Reversible by:

drop trigger if exists homework_assignments_grading_lock on public.homework_assignments;
drop function if exists public.enforce_homework_assignment_grading_lock;

File: supabase/migrations/0006_audit_logs.sql Status: Applied to live project, verified 2026-05-27 (8/8 checks pass). Risk: Low. Adds a new table, function, and triggers; does not alter existing data.

What it does: Creates an append-only public.audit_logs table + public.handle_audit_log() trigger function (SECURITY DEFINER) + AFTER INSERT/UPDATE/DELETE triggers on six watched tables: profiles, family_links, classes, enrollments, invoices, announcements. Each row mutation writes an audit entry containing actor_id (from auth.uid()), actor_role (from JWT app_metadata.role), action (INSERT/UPDATE/DELETE), table name, full old + new row state as JSONB, and timestamp.

Why this list of tables: these are the high-stakes operational tables an admin mutates. Tutor-driven high-volume tables (homework, lesson_notes, attendance, progress_topics) are deliberately excluded - auditing every attendance mark would balloon the log without adding security value.

Tamper-resistance:

• RLS enabled on audit_logs; SELECT granted to admins only via audit_logs_admin_read policy.
• No INSERT/UPDATE/DELETE policy granted to any role. The trigger function bypasses RLS because it runs SECURITY DEFINER (owner = postgres).
• authenticated role has SELECT only; anon has no grants.

Caveat - actor identification is partial:

auth.uid() only returns a non-null UUID when the database session has a JWT context. Two types of operations:

• User-context operations (Supabase JS SDK with a user's JWT, or set local request.jwt.claims in server code): actor_id and actor_role are populated.
• Server-context operations (Drizzle's db client connecting as the postgres role, or service_role): both are NULL. The audit row still captures what changed and when, but identifies the actor as "system / server."

For most admin portal mutations today (which go through server-side Drizzle), audit rows will have actor_id = NULL. To get reliable actor capture for admin actions, server actions need to either (a) make the mutation through a Supabase-client session that carries the admin's JWT, or (b) SET LOCAL request.jwt.claims before the Drizzle query. Filed as a follow-up; not a security hole, just a logging gap.

Reversible by:

drop trigger if exists audit_profiles on public.profiles;
drop trigger if exists audit_family_links on public.family_links;
drop trigger if exists audit_classes on public.classes;
drop trigger if exists audit_enrollments on public.enrollments;
drop trigger if exists audit_invoices on public.invoices;
drop trigger if exists audit_announcements on public.announcements;
drop function if exists public.handle_audit_log;
drop table if exists public.audit_logs;

File: supabase/migrations/0005_tutor_availability_rls.sql Status: Applied to live project. Verified. Risk: Low.

What it does: Enables RLS on public.tutor_availability (added to the schema after 0004) and writes policies: tutor_availability_tutor_all (own rows) + tutor_availability_admin_all. No student/parent policy - those portals query via server-side Drizzle which bypasses RLS.

Why it exists: Supabase Advisor flagged it CRITICAL (RLS Disabled in Public) because the table was added after 0004. This is the catch-up.

File: supabase/migrations/0009_subject_topics_rls.sql Status: Pending apply (DB-apply step deferred to controller checkpoint). Risk: Low. Additive - enables RLS on a new table and writes two new policies. No existing rows or policies modified.

What it does: Enables RLS on public.subject_topics (added to the schema in the curriculum-topics feature branch) and writes policies mirroring the existing subjects table pattern: subject_topics_select_authenticated (SELECT for any authenticated user) + subject_topics_admin_all (full DML for admins via public.is_admin()). Revokes all grants from anon; grants SELECT to authenticated.

Why it mirrors subjects: subject_topics is a reference/lookup table (curriculum metadata), not user-scoped data. Any authenticated user needs to read it to populate dropdowns and views; only admins should create/update/delete entries.

Reversible by:

alter table public.subject_topics disable row level security;

File: supabase/migrations/0010_tutor_week_sections_rls.sql Status: Pending apply (DB-apply step deferred to controller checkpoint). Risk: Low. Additive - enables RLS on two new tables, adds a SECURITY DEFINER helper function, and writes six new policies. No existing rows or policies modified.

What it does: Enables RLS on public.tutor_week_sections and public.tutor_week_attachments (both added to the schema in Task 1) and writes policies:

• tutor_week_sections_select_authenticated - SELECT for any authenticated user.
• tutor_week_sections_tutor_all - full DML gated on tutor_id = auth.uid().
• tutor_week_sections_admin_all - full DML for admins via public.is_admin().
• tutor_week_attachments_select_authenticated - SELECT for any authenticated user.
• tutor_week_attachments_tutor_all - full DML gated on public.is_owner_of_tutor_section(section_id).
• tutor_week_attachments_admin_all - full DML for admins via public.is_admin().

Adds SECURITY DEFINER helper public.is_owner_of_tutor_section(p_section_id uuid) returns boolean: resolves whether the current authenticated user owns the parent tutor_week_sections row that an attachment hangs off. Follows the same pattern as existing helpers in 0004 - search_path pinned to public, auth, returns bool only (no data leaked), stable language SQL.

Why a helper for tutor_week_attachments: the attachment table has no direct tutor_id column - ownership is inherited through section_id → tutor_week_sections.tutor_id. An inline EXISTS in the policy would trigger RLS on tutor_week_sections during policy evaluation, risking 42P17 (infinite recursion). The SECURITY DEFINER helper bypasses RLS on tutor_week_sections for that single ownership lookup only.

Revokes all grants from anon; grants SELECT to authenticated for both tables.

Reversible by:

alter table public.tutor_week_sections disable row level security;
alter table public.tutor_week_attachments disable row level security;
drop function if exists public.is_owner_of_tutor_section(uuid);

File: supabase/migrations/0011_tutor_section_read_scope.sql Status: Pending apply (DB-apply step deferred to controller checkpoint). Risk: Low. Replaces two permissive using(true) select policies with scoped ones; write policies from 0010 are unchanged.

What it does: Replaces the 2a broad-read policies (tutor_week_sections_select_authenticated and tutor_week_attachments_select_authenticated, both using(true)) with policies gated by two new SECURITY DEFINER helper functions:

• public.can_read_tutor_section(p_tutor_id uuid, p_subject_week_id uuid) returns boolean - returns true if the caller is the section's tutor, an admin, a student enrolled with that tutor for that subject's week, or a parent of such a student.
• public.can_read_tutor_section_att(p_section_id uuid) returns boolean - resolves the parent tutor_week_sections row and delegates to can_read_tutor_section. Needed because tutor_week_attachments has no direct tutor_id or subject_week_id column; an inline EXISTS would risk 42P17 recursion.

Both helpers follow the existing pattern: language sql stable security definer set search_path = public, auth, return bool only (no data leaked).

Why now (Part 2b): 0010 shipped with using(true) select policies as a deliberate first-pass to unblock the tutor UI. Any authenticated user (including students and parents of unrelated students) could read all tutor sections. 0011 closes that gap.

Note - class_week_overrides dropped in Part 2b: The companion Drizzle schema migration (npm run db:push, controller-gated Step 1 of Task 4) drops the class_week_overrides table entirely. That table was introduced in Task 1 but never populated - no data is lost. It had no RLS policies in 0010 (table was not present in that migration) and no rows in any environment. No SECURITY.md matrix entry existed for it; none is needed post-drop.

Reversible by:

drop function if exists public.can_read_tutor_section(uuid, uuid);
drop function if exists public.can_read_tutor_section_att(uuid);

-- on tutor_week_sections
drop policy if exists tutor_week_sections_select_scoped on public.tutor_week_sections;
create policy tutor_week_sections_select_authenticated on public.tutor_week_sections
  for select to authenticated using (true);

-- on tutor_week_attachments
drop policy if exists tutor_week_attachments_select_scoped on public.tutor_week_attachments;
create policy tutor_week_attachments_select_authenticated on public.tutor_week_attachments
  for select to authenticated using (true);

File: supabase/migrations/0012_uncovered_tables_rls.sql Status: Applied. Risk: Medium at apply time - enables RLS on 8 tables that previously had none. Wrapped in BEGIN/COMMIT. Requires the dev server stopped first (its connections hold locks that block ALTER TABLE … ENABLE RLS).

What it does: Enables RLS + policies on 8 tables that shipped without any committed RLS and had relied only on server-side Drizzle (which bypasses RLS): terms, subject_weeks, student_week_progress, discussion_threads, discussion_replies, dm_threads, dm_messages, dm_reads. Adds one helper public.is_dm_participant(p_thread_id uuid) (security definer, pinned search_path, bool only, bypasses dm_threads RLS to avoid recursion).

• terms, subject_weeks - read all authenticated, write admin (mirrors subjects).
• student_week_progress - student own (read+write), parent of that student (read), admin all.
• discussion_threads / discussion_replies - read all authenticated; author INSERT/UPDATE own; admin all (moderation + soft-delete).
• dm_threads / dm_messages - participants read/write; admin READ (safeguarding oversight, see below); dm_messages INSERT requires sender_id = auth.uid() AND participant.
• dm_reads - own only (read receipts stay private; no admin access).

Why admins can read DMs: students are minors. Under the Victorian Reportable Conduct Scheme the organisation must be able to investigate reported conduct against workers/volunteers, which requires access to the relevant messages; Child Safe Standards Standard 9 balances that against the child's right to privacy. Chosen model (per owner decision): admins can read for investigation/safeguarding, and that access should be accountable. Follow-up owed: (1) log admin DM reads (needs-basis, accountable); (2) a "report conversation" flow so access is report-triggered. Not legal advice - confirm against the org's Child Safety Policy.

Reversible by: alter table public.<t> disable row level security; for each of the 8 tables, plus drop function if exists public.is_dm_participant(uuid);.

File: supabase/migrations/0013_profiles_role_lock.sql Status: Applied 2026-07-02. Verified via JWT impersonation (4/4: self-promote blocked, non-role field updates while role pinned, admin can change, postgres/server bypasses). Risk: Low. Adds a BEFORE UPDATE trigger + function; no schema or data change.

What it does: Closes checklist A8 / Known caveat §1. profiles_update_own (from 0004) lets a user UPDATE any column on their own row, including role. This adds public.enforce_profiles_role_lock() (SECURITY INVOKER, pinned search_path) + a BEFORE UPDATE trigger that silently reverts role to its OLD value unless the caller is an admin (is_admin()) or a trusted server context (current_user not in ('authenticated','anon') → postgres/service_role). Mirrors the silent-revert pattern of 0007.

Why it matters: the authoritative role lives in auth.users.app_metadata (server-only) and every policy reads role from auth.jwt(), so a self-set profiles.role grants no access today. This pins the display column too, so any future code path that trusts profiles.role for authz can't become a privilege-escalation vector.

Reversible by:

drop trigger if exists profiles_role_lock on public.profiles;
drop function if exists public.enforce_profiles_role_lock;

File: supabase/migrations/0014_rate_limits.sql Status: Applied 2026-07-02. Function verified (fixed-window allow-then-deny + window-reset PASS). Risk: Low. New table + function; no existing data touched.

What it does: Backend rate limiting (checklist B3 / C4). Adds public.rate_limits (bucket, identifier, window_started_at, count) and public.check_rate_limit(bucket, identifier, max, window_seconds) returns boolean - an atomic fixed-window counter (upsert with a CASE that resets once the window elapses; all SET expressions see the pre-update row so window/count stay consistent). Postgres-backed so it works across serverless instances.

Access: RLS enabled with an explicit deny-all policy (rate_limits_no_client_access, using(false)) plus revoke all from anon/authenticated - unreachable via the client SDK. EXECUTE on check_rate_limit is revoked from public/anon/authenticated too: because the function increments the counter, a granted client could call it with a victim's identifier to lock them out (broken access control / DoS). Only the owner (postgres role, used by Drizzle) executes it. Consumed server-side by src/lib/rate-limit.ts (login action, DM send, discussion thread/reply).

Reversible by:

drop function if exists public.check_rate_limit(text, text, integer, integer);
drop table if exists public.rate_limits;

File: supabase/migrations/0055_student_curriculum_access.sql Status: Applied to the connected Supabase project on 2026-09-23. Risk: Low. Additive table and index; no existing curriculum or enrolment data is changed.

What it does: Adds one admin-managed exception row per student, subject and historical term. Normal curriculum access is derived from the student's first enrolment date for that subject. A grant permits an earlier term, while the calendar-based future-week lock continues to apply independently.

Access: RLS is enabled with no browser policies and grants are revoked from anon/authenticated. Reads and writes run through role-checked server code; only admin actions can create or remove a grant.

Reversible by:

drop table if exists public.student_curriculum_term_grants;

File: supabase/migrations/0060_class_week_plans.sql Status: Applied to dev and Production on 2026-10-12 (Production right after the 1fb0976 deploy went live). Risk: Medium. Renames tutor_week_sections / tutor_week_attachments to class_week_plans / class_week_plan_attachments, re-keys them by class, drops classes.lesson_plan, and replaces the 0010/0011 policies and helpers. The migration raises instead of dropping content it cannot place.

What it does: Drops the 0010/0011 policies and the is_owner_of_tutor_section, can_read_tutor_section and can_read_tutor_section_att helpers, then adds three SECURITY DEFINER helpers with a pinned search_path that return a boolean or the plan's class id only:

• can_read_class_week_plan(class_id) - admin, the class tutor, a student enrolled in the class, or a parent of one.
• can_write_class_week_plan(class_id) - admin or the class tutor.
• class_of_week_plan(plan_id) - resolves an attachment's class without triggering RLS recursion on class_week_plans.

Policies: class_week_plans_select / class_week_plan_attachments_select (read helper), class_week_plans_write / class_week_plan_attachments_write (write helper). anon has no grants; authenticated has SELECT only, as in 0010. Reads are now scoped to the class, not to every class the tutor teaches in the subject.

Reversible: only before plans diverge per class; see the migration header.

File: supabase/migrations/0061_discussion_board_access.sql Status: Applied to dev and Production on 2026-10-12 (Production right after the 1fb0976 deploy went live). Risk: Low. Only narrows SELECT; the app reads discussions through server-side Drizzle, which bypasses RLS.

What it does: Replaces the 0012/0016 using (true) read policies on discussion_threads, discussion_replies and discussion_attachments, which let any signed-in user (including a parent, a withdrawn student or a student of another subject) read every board through the client SDK. Adds can_read_discussion_subject(subject_id) plus subject_of_discussion_thread / subject_of_discussion_reply (SECURITY DEFINER, pinned search_path, execute revoked from public/anon):

• admin board (subject_id is null): any signed-in student, tutor or admin;
• subject board: admins, tutors of a class in the subject, and students with an active (not withdrawn) enrolment in it.

The app layer matches: canSeeBoard ignores withdrawn enrolments, and each role's thread page 404s a thread opened under another board's URL (threadIsOnBoard). Verified with JWT impersonation: a student withdrawn from a subject loses its threads, keeps the others, and a parent reads none.

Reversible: yes, by recreating the using (true) policies.

File: supabase/migrations/0062_homework_score_percent.sql Status: Applied to dev and Production on 2026-10-12 (Production right after the 1fb0976 deploy went live). Risk: Low. Adds homework_assignments_score_percent (score null or 0-100). No dev or production rows were outside the range.

Reversible: yes, drop the constraint.

File: supabase/migrations/0063_subject_colors.sql Status: Applied to dev and Production on 2026-10-16 (RLS check green), Production just before the 7c775ea push. Risk: Low. Adds nullable subjects.color and subjects_color_palette (null or one of the ten palette keys in src/lib/subject-tone.ts). Existing rows stay null and keep the colour taken from their name. No policy changes; subject writes stay admin-only server actions.

Reversible: yes, drop the constraint and the column.

File: supabase/migrations/0064_subject_names_not_unique.sql Status: Applied to dev and Production on 2026-10-17 (RLS check green on both), Production just before the push of the duplicate-names commit. Risk: Low. Drops the unique constraint on subjects.name (subjects_name_unique), so two subjects can both be called "Mathematics". No data changes. Every lookup, filter and grouping that used the name now uses the subject id; pickers and legends add the year level where two names collide (distinctSubjects in src/lib/subject-label.ts). No policy changes.

Reversible: only while no two subjects share a name: add the constraint back. Once duplicates exist, rename them first.

Read access. "✓" = full row visibility for own data; "limited" = subset of columns or rows; "no" = denied at RLS level.

* homework_assignments UPDATE by student is row-restricted but not column-restricted - see Known caveats. ** student/parent portals read this via server-side Drizzle (bypasses RLS); not a real restriction in practice.

Write access. INSERT/UPDATE/DELETE; service_role bypasses all of this.

*** "own" for class_week_plan_attachments means the attachment's plan belongs to a class the caller tutors (can_write_class_week_plan(class_of_week_plan(plan_id)), migration 0060).

1. Identify the access pattern. Simple column match (x = auth.uid()) goes inline. Cross-table lookups MUST go through a SECURITY DEFINER helper to avoid RLS recursion.
2. Add a helper if needed. Helpers live alongside the existing ones in 0004. Pattern: language sql stable security definer set search_path = public, auth. Body must only return true/false about the caller's relationships - never return data.
3. Drop-and-create the policy in a new migration (supabase/migrations/000N_<topic>.sql). Always include drop policy if exists before create policy so the migration is re-runnable.
4. Wrap in BEGIN/COMMIT if the migration touches more than one policy. Atomic apply prevents a half-locked state.
5. Verify with a JWT impersonation script. Pattern: set local role authenticated; set local request.jwt.claims = '...'; inside a transaction, then test positive (returns expected rows) and negative (returns 0) cases. Also test anon.
6. Apply via scripts/apply-sql.mjs - prefers DIRECT_URL (session pooler) which is required for DDL.
7. Append a migration log entry to this file with status, risk, what-it-does, reversibility.

• Inline cross-table EXISTS in policies - use a SECURITY DEFINER helper instead. Recursion 42P17 is the failure mode.
• SECURITY DEFINER helpers without pinned search_path - search-path injection attack vector.
• SECURITY DEFINER helpers that return data - they bypass RLS, so any data they return leaks. Return bool only.
• Re-defining policies in Drizzle (src/db/schema.ts) - Drizzle owns table DDL; policies/triggers/views/grants live in supabase/migrations/.

The former role-edit and homework-grading caveats are closed by migrations 0013 and 0007 respectively. Auth authorization reads app_metadata.role only.

The Drizzle runtime connection uses a trusted Postgres role and can bypass RLS. Every server loader/action must therefore keep its requireRole/ownership checks. RLS is defense-in-depth for direct Supabase client access, not the primary authorization boundary for server code.

RLS permits admins to read DMs for safeguarding, but the current UI exposes only conversations in which that admin participates. If a non-participant oversight UI is added, it must require a reason/report and create an audit READ record before returning content.

The custom domain and Vercel Production environment-variable names are present, but docs/security-checklist.md intentionally leaves Supabase URL allowlists, Auth custom SMTP, backup/PITR, token settings, legal documents, and real-inbox delivery open until their dashboard or live tests are recorded.