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.