Consent records with timestamp, source, method per subscriber

GDPR Art. 7(1) requires that the controller 'be able to demonstrate that the data subject has consented.' TCPA §227 litigation regularly turns on whether the sender can produce a consent record for a specific number. CASL Section 13 requires that senders keep consent records for 3 years after the last commercial message. CWE-778 (Insufficient Logging) captures the technical gap: without a timestamped, source-tagged consent log, you cannot respond to a regulator's demand for proof of consent, a court's discovery request, or a user's GDPR access request. A marketingOptIn: true boolean with no supporting record is legally worthless.

Add a dedicated consent audit table that records every opt-in and opt-out action with full provenance — never rely on a boolean flag alone.

-- Migration: consent audit trail
CREATE TABLE marketing_consent_log (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  subscriber_id    UUID NOT NULL REFERENCES subscribers(id),
  channel          TEXT NOT NULL CHECK (channel IN ('email', 'sms')),
  action           TEXT NOT NULL CHECK (action IN ('opt-in', 'opt-out', 're-opt-in')),
  method           TEXT NOT NULL, -- 'double-opt-in', 'single-opt-in', 'sms-keyword'
  source           TEXT NOT NULL, -- 'signup-form', 'checkout', 'preferences-page'
  language_version TEXT,          -- 'v1-2026-01' — version of consent text shown
  ip_address       INET,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);

Write a row for every opt-in and opt-out action. Retain records for the subscriber relationship duration plus 3 years (CASL minimum). Do not delete consent records when users unsubscribe — the opt-out record is itself evidence.

• ID: email-sms-compliance.consent.consent-records-maintained

• Severity: medium

• What to look for: Enumerate every relevant item. For both email and SMS, regulatory frameworks require that you can demonstrate consent upon request. This means maintaining a record for every subscriber that shows: when they consented, how they consented (which form, which page), what they consented to (the specific consent language version), and optionally the IP address at the point of consent. Check the database schema for a marketing_consent, email_subscriptions, or sms_opt_ins table. Does it have a consented_at timestamp? A consent_source field indicating where consent was given? A reference to the version of consent language shown? Check whether consent records are preserved even if the user later modifies their account — can you reconstruct the original consent for a deleted user (within the retention period)?

• Pass criteria: At least 1 of the following conditions is met. For every marketing email and SMS subscriber, a consent record exists with: timestamp of consent, source/page where consent was given, consent method (double opt-in, single opt-in with disclosure, keyword opt-in), and the version of consent language shown. Records are preserved for the duration of the subscriber relationship plus a reasonable retention period (typically 3-5 years, depending on jurisdiction).

• Fail criteria: No consent records stored. Records exist but lack timestamps. No source tracking — cannot determine where a subscriber consented. Consent records deleted when users unsubscribe.

• Skip (N/A) when: Application sends no marketing email or SMS.

• Detail on fail: Example: "Subscriber table has email and marketingOptIn boolean but no timestamp, source, or consent language version. Cannot demonstrate consent upon regulatory request.".

• Remediation: Add a consent audit table:

  -- Migration: add consent audit trail
  CREATE TABLE marketing_consent_log (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    subscriber_id UUID NOT NULL REFERENCES subscribers(id),
    channel      TEXT NOT NULL CHECK (channel IN ('email', 'sms')),
    action       TEXT NOT NULL CHECK (action IN ('opt-in', 'opt-out', 're-opt-in')),
    method       TEXT NOT NULL, -- 'double-opt-in', 'single-opt-in', 'sms-keyword', 'import'
    source       TEXT NOT NULL, -- 'signup-form', 'checkout', 'preferences-page', etc.
    language_version TEXT,      -- 'v1-2026-01', version identifier for consent text shown
    ip_address   INET,
    user_agent   TEXT,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
  );
  

  // Record every consent action — opt-in and opt-out
  await db.marketingConsentLog.create({
    data: {
      subscriberId: subscriber.id,
      channel: 'email',
      action: 'opt-in',
      method: 'double-opt-in',
      source: 'signup-form',
      languageVersion: 'v1-2026-01',
      ipAddress: req.headers.get('x-forwarded-for') ?? null,
    },
  })