Consent stored as structured records, not boolean flags

GDPR Article 7 requires you to demonstrate that consent was freely given, specific, informed, and unambiguous — a boolean flag proves none of this. Under GDPR Article 30, you must maintain records of processing activities. A single subscribed BOOLEAN column cannot tell a regulator when consent was given, under which version of your privacy policy, from which collection point, or for which communication scope. CCPA §1798.100 similarly requires you to substantiate what personal data you hold and how it was lawfully obtained. When a data protection authority opens an investigation, a boolean flag is not evidence — it is a liability.

Replace the boolean flag with a dedicated consent_records table where every row is an immutable event. Add the migration to your supabase/migrations/ or Prisma schema:

CREATE TABLE consent_records (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  contact_id    UUID NOT NULL REFERENCES contacts(id),
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  source        TEXT NOT NULL, -- 'checkout-form', 'api-import', 'landing-page'
  scope         TEXT NOT NULL, -- 'marketing', 'transactional', 'partner'
  granted       BOOLEAN NOT NULL,
  policy_version TEXT NOT NULL,
  ip_address    TEXT,
  user_agent    TEXT
);

CREATE INDEX idx_consent_records_contact_id ON consent_records(contact_id);

The most recent granted = true record for a (contact_id, scope) pair is the effective consent state. Keep the boolean as a denormalized cache if needed for query speed, but never as the source of truth.

• ID: compliance-consent-engine.consent-storage.structured-records

• Severity: critical

• What to look for: Examine the database schema for how consent is stored. Look for columns like marketing_opt_in BOOLEAN or subscribed BOOLEAN on a contacts/users table. A boolean flag cannot record when consent was given, where it came from, what scope it covered, or what version of the privacy policy was in effect — making it legally indefensible. Look instead for a dedicated consent records table with columns for contact_id, timestamp, source (e.g., "checkout-form", "api", "import"), scope (e.g., "marketing", "transactional"), and policy_version.

• Pass criteria: Consent is stored in a dedicated table (or document collection) where each record includes at minimum: a contact identifier, a timestamp, the consent source, the scope/type of consent, and the policy version agreed to. Boolean flags may exist as a denormalized cache but are not the source of truth. Count all columns on the consent table — at least 5 must be present (contact_id, timestamp, source, scope, policy_version). Report the count even on pass.

• Fail criteria: Consent is stored only as a boolean flag on the contact/user record with no associated timestamp, source, scope, or version. Or consent is tracked only in a third-party system with no local record. A subscribed BOOLEAN column with a created_at timestamp does not count as pass — it must include source and scope.

• Skip (N/A) when: The application does not collect communication consent at all (no email or SMS marketing, no newsletter, no promotional content).

• Cross-reference: The Campaign Orchestration & Sequencing Audit checks that opt-out signals exit contacts from active sequences, which depends on consent records being queryable.

• Detail on fail: Describe the storage pattern found. Example: "Contacts table has subscribed BOOLEAN column only — no timestamp, source, or scope recorded" or "Consent tracked only in Mailchimp with no local consent table — no legal evidence of when/how consent was obtained"

• Remediation: Replace or supplement the boolean flag with a consent records table:

  -- Migration to add structured consent records
  CREATE TABLE consent_records (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    contact_id    UUID NOT NULL REFERENCES contacts(id),
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    source        TEXT NOT NULL, -- 'checkout-form', 'api-import', 'landing-page'
    scope         TEXT NOT NULL, -- 'marketing', 'transactional', 'partner'
    granted       BOOLEAN NOT NULL,
    policy_version TEXT NOT NULL,
    ip_address    TEXT,          -- optional, for web form captures
    user_agent    TEXT           -- optional
  );
  
  CREATE INDEX idx_consent_records_contact_id ON consent_records(contact_id);
  

  The most recent granted = true record for a contact+scope combination is the effective consent state. Never UPDATE or DELETE these rows — see the immutability check below.