Consent records stored with timestamp, policy version, and mechanism

GDPR Art. 7(2) requires that consent be demonstrable — if challenged by a regulator or in litigation, you must prove when the user consented, to which policy version, and via which mechanism. A current-state boolean in a user_settings table provides none of this. GDPR Art. 5(2) (accountability principle) and SOC 2 CC2.3 both require documented audit trails. Without immutable consent records, every withdrawal or complaint triggers a 'our word against theirs' dispute that regulators resolve in the user's favor.

Create an append-only user_consent_audit table and write a new row on every consent change — never update existing rows. Include policy_version so consent granted under an old policy is distinguishable from consent under a revised one.

CREATE TABLE user_consent_audit (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  processing_type VARCHAR(50) NOT NULL,
  consent_given BOOLEAN NOT NULL,
  consented_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  policy_version VARCHAR(20) NOT NULL,
  collection_mechanism VARCHAR(50) NOT NULL, -- 'signup-form', 'settings-ui', 'api'
  ip_address INET
);
-- No UPDATE or DELETE grants on this table for the application role

Query the most recent record per (user_id, processing_type) to determine current consent state — never rely on a separate boolean cache.

• ID: community-privacy-controls.data-rights.consent-records

• Severity: high

• What to look for: Enumerate every relevant item. Examine the database schema for a consent audit log table. Check that consent decisions are logged with: timestamp, policy version, consent mechanism (UI flow, email, API), and processing type. Verify consent records cannot be modified retroactively.

• Pass criteria: At least 1 of the following conditions is met. Consent decisions are immutable audit log entries containing: userId, processing type, consentGiven (true/false), timestamp, policy version, and collection mechanism. Records are retrievable for user verification.

• Fail criteria: No consent log exists. Consent stored only as current state (no history). No policy version tracking. Timestamps or mechanism not recorded.

• Skip (N/A) when: Never — consent records are a compliance requirement.

• Detail on fail: Describe what's missing. Example: "Consent stored only as current boolean in user settings table. No timestamp, policy version, or history available." or "No audit log for consent changes; cannot prove when user consented or to what policy version."

• Remediation: Create immutable consent audit log:

  CREATE TABLE user_consent_audit (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    processing_type VARCHAR(50) NOT NULL,
    consent_given BOOLEAN NOT NULL,
    consented_at TIMESTAMP NOT NULL,
    policy_version VARCHAR(20) NOT NULL,
    collection_mechanism VARCHAR(50) NOT NULL,
    ip_address INET,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );
  
  -- Immutable: no update trigger
  

  Record every consent change:

  async function updateUserConsent(userId: string, processingType: string, consentGiven: boolean) {
    await db.userConsentAudit.create({
      data: {
        userId,
        processingType,
        consentGiven,
        consentedAt: new Date(),
        policyVersion: '1.0',
        collectionMechanism: 'settings-ui',
        ipAddress: req.ip,
      }
    });
  }