Consent queryable by contact ID with an index

A consent table with no index on contact_id requires a sequential scan for every pre-send consent check. At 100,000 contacts with an average consent history of 3 records each, that is 300,000 rows scanned per recipient per campaign — multiplied across batch send volumes, this degrades send throughput and can cause pre-send checks to time out, forcing code paths that skip the check entirely to meet send deadlines. ISO 25010:2011 performance efficiency directly covers this: the system must perform the correct function (consent verification) within the required time under the stated load.

Add the index in a migration. If you filter by both contact_id and scope at query time, a composite index is more efficient than two separate indexes:

-- Composite index covers (contact_id, scope, created_at) lookups
CREATE INDEX idx_consent_records_contact_scope
  ON consent_records(contact_id, scope, created_at DESC);

Verify the index is used by running EXPLAIN ANALYZE on the consent lookup query in your staging database before deploying to production. Add this migration to supabase/migrations/ with a timestamp prefix.