Create Supabase tables for peer mentor pause status

The UNIQUE constraint on peer_mentor_status.mentor_id means there is exactly one status record per mentor, updated in-place on each transition — the full history lives in peer_mentor_status_log. This is a deliberate design choice that simplifies lookups (no need to query for the latest record) but requires a trigger to write log entries. Consider implementing the audit trigger in this same migration using a PostgreSQL trigger function: CREATE OR REPLACE FUNCTION log_mentor_status_change() RETURNS TRIGGER AS $$ BEGIN INSERT INTO peer_mentor_status_log (mentor_id, chapter_id, previous_status, new_status, changed_by, changed_at) VALUES (OLD.mentor_id, OLD.chapter_id, OLD.status, NEW.status, NEW.activated_by, now()); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_mentor_status_log AFTER UPDATE OF status ON peer_mentor_status FOR EACH ROW EXECUTE FUNCTION log_mentor_status_change(); This ensures the audit log is always in sync regardless of which service performs the update.

After applying the migration to the local Supabase instance: (1) verify all columns on both tables with \d+ peer_mentor_status and \d+ peer_mentor_status_log; (2) insert a row with status='active' and confirm it succeeds; (3) attempt to insert status='unknown_value' and confirm CHECK constraint rejects it; (4) insert two rows with the same mentor_id in peer_mentor_status and confirm UNIQUE constraint rejects the second; (5) run EXPLAIN ANALYZE on the composite index query (chapter_id + status filter) to confirm index scan; (6) insert a log row and attempt to UPDATE it, confirming it is blocked if the append-only policy/rule is already in place. Document results in a test_schema_verification.sql file.