supabase/docs/migrations-and-dev-seed.md

Classroom Copilot Supabase migrations and deterministic dev seed

Status: branch implementation validated on Supabase dev host 192.168.0.94 using an isolated throwaway database.

Consolidated init chain

The Docker Compose db service now mounts a single ordered Classroom Copilot chain:

1. volumes/db/cc/61-gais-reference.sql — GAIS reference table schema and open-data read policies only.
2. volumes/db/cc/62-application-schema.sql — canonical app schema, storage/file metadata tables, class/lesson/CIS tables, indexes, and base RLS.
3. volumes/db/cc/63-academic-calendar.sql — school timetable, academic year/term/week/day, teacher timetable, and teacher slot tables.
4. volumes/db/cc/64-extended-schema.sql — term breaks, academic periods, invitations, taught lessons, and week-cycle slot uniqueness.
5. volumes/db/cc/65-phase-c.sql — Phase C cleanup after taught lessons exist; links lesson deliveries to taught lessons.
6. volumes/db/cc/66-taught-lessons-nullable.sql — nullable taught lesson class_id and teacher slot class FK.
7. volumes/db/cc/67-dev-seed.sql — deterministic, non-sensitive dev fixtures.

The old 61-core-schema.sql through 70-add-directory-support.sql bootstrap files were removed from the active chain because they represented an older ClassConcepts/filesystem schema and stale role vocabulary. The Git history remains the archive.

Deterministic dev seed contents

67-dev-seed.sql creates only fixture data:

• 1 platform admin in admin_profiles.
• 1 school/institute.
• 1 school admin, 2 teachers, 3 students.
• institute memberships for the school admin, teachers, and students.
• 2 classes with class-teacher and class-student rows.
• 1 school timetable, 1 academic year, 1 term, 1 week, 4 academic days, and 16 academic periods.
• 2 teacher timetables, 3 teacher timetable slots, and 3 taught lessons.
• 2 planned lessons and 1 delivered lesson fixture.
• Storage buckets cc.users, cc.public.snapshots, and cc.examboards.
• TLDraw default snapshot paths on the teacher whiteboard rooms; object rows are not pre-created.

Fixture emails use the classroomcopilot.dev domain and are not real users. Do not replace this seed with live student/teacher data.

Validation pattern used on Supabase dev

Do not run schema experiments on production. To validate this branch without mutating the live dev database, create a throwaway database on the Supabase dev Postgres container, clone only the auth and storage schema definitions from dev, apply the ordered chain, check row counts, then drop the throwaway database.

The 2026-05-28 validation used this shape on ubuntu-ct-supabase-dev (192.168.0.94):

DB=cc_mig_validate_<timestamp>
BASE=/tmp/cc-supabase-migration-validate

docker exec supabase-db psql -U postgres -d postgres -v ON_ERROR_STOP=1 -c "DROP DATABASE IF EXISTS $DB WITH (FORCE);"
docker exec supabase-db psql -U postgres -d postgres -v ON_ERROR_STOP=1 -c "CREATE DATABASE $DB;"

# The dev auth schema has a trigger that references public.handle_new_user();
# create a no-op stub before restoring auth/storage schema-only into the throwaway DB.
cat >/tmp/create_dummy.sql <<'SQL'
create or replace function public.handle_new_user()
returns trigger
language plpgsql
as $$
begin
  return new;
end;
$$;
SQL
docker exec -i supabase-db psql -U postgres -d "$DB" -v ON_ERROR_STOP=1 < /tmp/create_dummy.sql

docker exec supabase-db pg_dump -U postgres -d postgres --schema-only --no-owner --no-privileges -n auth -n storage > /tmp/${DB}_auth_storage_schema.sql
docker exec -i supabase-db psql -U postgres -d "$DB" -v ON_ERROR_STOP=1 < /tmp/${DB}_auth_storage_schema.sql

for f in \
  "$BASE"/volumes/db/cc/61-gais-reference.sql \
  "$BASE"/volumes/db/cc/62-application-schema.sql \
  "$BASE"/volumes/db/cc/63-academic-calendar.sql \
  "$BASE"/volumes/db/cc/64-extended-schema.sql \
  "$BASE"/volumes/db/cc/65-phase-c.sql \
  "$BASE"/volumes/db/cc/66-taught-lessons-nullable.sql \
  "$BASE"/volumes/db/cc/67-dev-seed.sql; do
  docker exec -i supabase-db psql -U postgres -d "$DB" -v ON_ERROR_STOP=1 < "$f"
done

# Smoke counts, then cleanup.
docker exec supabase-db psql -U postgres -d "$DB" -Atc "select 'profiles='||count(*) from public.profiles union all select 'institutes='||count(*) from public.institutes union all select 'memberships='||count(*) from public.institute_memberships union all select 'classes='||count(*) from public.classes union all select 'academic_periods='||count(*) from public.academic_periods union all select 'teacher_timetable_slots='||count(*) from public.teacher_timetable_slots union all select 'taught_lessons='||count(*) from public.taught_lessons union all select 'planned_lessons='||count(*) from public.planned_lessons union all select 'lesson_deliveries='||count(*) from public.lesson_deliveries union all select 'buckets='||count(*) from storage.buckets where id like 'cc.%';"
docker exec supabase-db psql -U postgres -d postgres -v ON_ERROR_STOP=1 -c "DROP DATABASE IF EXISTS $DB WITH (FORCE);"

Expected smoke counts from the deterministic seed:

profiles=7
institutes=1
memberships=6
classes=2
academic_periods=16
teacher_timetable_slots=3
taught_lessons=3
planned_lessons=2
lesson_deliveries=1
buckets=3

Production rule

This branch is not a production migration by itself. Before production use:

1. Take a production schema/data backup.
2. Compare live production schema drift against this consolidated chain.
3. Prepare explicit forward migrations for any live-only objects or data transforms.
4. Validate those forward migrations on Supabase dev first.
5. Only apply to production after human approval.