Add duplicate_reviewed column migration
epic-duplicate-activity-detection-foundation-task-004 — Write Supabase migration to add the duplicate_reviewed boolean column (default false, not null) to the activities table. Include index on this column combined with status for efficient queue queries. Ensure migration handles existing rows by backfilling false and includes a corresponding rollback script.
Acceptance Criteria
Technical Requirements
Implementation Notes
PostgreSQL handles backfill of NOT NULL DEFAULT columns efficiently for existing rows — the default is stored as a table-level default and applied lazily without a full table rewrite in PostgreSQL 11+. However, explicitly verify with a null-count query after applying to confirm behaviour in the Supabase PostgreSQL version in use.
Place the index creation after the column addition in the same migration file. In production, index creation should use CONCURRENTLY (requires running outside a transaction — use two separate migration files if necessary: one for the column, one for the index). Add a comment in the migration explaining that this column feeds the coordinator_duplicate_queue view and the deduplication workflow.
Testing Requirements
Run migration on a local Supabase instance seeded with existing activity rows. Verify: (1) `\d activities` shows duplicate_reviewed column with NOT NULL DEFAULT false; (2) SELECT COUNT(*) WHERE duplicate_reviewed IS NULL returns 0; (3) INSERT an activity without specifying duplicate_reviewed — confirm it defaults to false; (4) Run migration a second time — confirm no error (idempotency); (5) Run rollback script — confirm column and index are removed without error; (6) Re-run migration after rollback — confirm clean re-application. Document verification queries in migration comments.
The `check_activity_duplicates` RPC may not meet the 500ms target on production-scale data if the composite index is not applied correctly or if Supabase RLS evaluation adds unexpected overhead, causing the duplicate check to noticeably delay activity submission.
Mitigation & Contingency
Mitigation: Write the RPC with an explicit EXPLAIN ANALYZE in development against a seeded dataset representative of a large chapter (10,000+ activities). Pin the index hint in the RPC body and verify the query plan in Supabase's SQL editor before merging.
Contingency: If the 500ms target cannot be met with the RPC approach, introduce an async post-submit check pattern where the activity is saved first and the duplicate warning is surfaced as a follow-up notification, preserving submission speed at the cost of real-time blocking UX.
RLS policies for the coordinator_duplicate_queue view must correctly scope results to the coordinator's chapters. Incorrect policies could expose duplicate records from other chapters (privacy violation) or hide legitimate duplicates (functional regression).
Mitigation & Contingency
Mitigation: Write explicit integration tests that verify RLS behaviour using at least three distinct coordinator + chapter combinations, including a peer mentor belonging to two chapters. Use Supabase's built-in RLS testing utilities.
Contingency: If RLS proves too complex for the queue view, move the chapter-scoping filter into the DuplicateQueueRepository query layer at the application level, trading database-enforced isolation for application-enforced scoping with full test coverage.
Adding the duplicate_reviewed column to the activities table and the composite index requires a migration against a live table. If the migration locks the table for an extended period, it could disrupt active coordinators submitting activities.
Mitigation & Contingency
Mitigation: Use PostgreSQL's `CREATE INDEX CONCURRENTLY` to avoid table lock. Add the duplicate_reviewed column with a DEFAULT false so no backfill update lock is required. Schedule the migration during a low-traffic window.
Contingency: If concurrent index creation fails or takes too long, fall back to a smaller partial index scoped to the last 90 days of activities, then expand it incrementally.