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.

• ID: compliance-consent-engine.consent-storage.indexed-query

• Severity: low

• What to look for: Check the database schema for an index on the contact_id (or email) column of the consent records table. Without this index, pre-send consent checks require a sequential scan, which degrades badly at scale (100k+ contacts). Also check whether the application actually queries consent at send time or only at subscription time.

• Pass criteria: An index exists on consent_records.contact_id (or equivalent). The pre-send path queries consent by contact ID, not by scanning all records. If using composite consent (contact + scope), a composite index on (contact_id, scope) is even better. Count the indexes on the consent table — at least 1 must cover contact_id.

• Fail criteria: No index on contact_id in consent records. Or the application does not query consent at send time at all.

• Skip (N/A) when: No local consent table exists (consent managed entirely by a third-party platform like Mailchimp, which handles its own indexing).

• Detail on fail: "consent_records table has no index on contact_id — sequential scan required at send time" or "ConsentService.check() not called in campaign send path — send happens without runtime consent verification"

• Remediation: Add the index:

  -- Single-column index for simple lookups
  CREATE INDEX idx_consent_records_contact_id ON consent_records(contact_id);
  
  -- Or composite index if you filter by scope at query time
  CREATE INDEX idx_consent_records_contact_scope ON consent_records(contact_id, scope, created_at DESC);