# Consent queryable by contact ID with an index

- **Pattern:** `ab-000778` (`compliance-consent-engine.consent-storage.indexed-query`)
- **Severity:** low
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000778
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000778)

## Why it matters

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.

## Severity rationale

Low because the operational impact (slow sends) is real but the compliance risk is indirect — the consent check still runs, it is just slow enough to become a reliability pressure that leads teams to skip it.

## Remediation

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:

```sql
-- 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.

## Detection

- **ID:** `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:

  ```sql
  -- 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);
  ```

## External references

- iso-25010 performance-efficiency.time-behaviour

Taxons: performance, data-integrity

HTML version: https://auditbuffet.com/patterns/ab-000778