Consent log user agreement history on website

Implementation of Consent Log (User Consent Journal) on Website

Consent Log — immutable database storing evidence of obtaining user consent for personal data processing. Per GDPR, regulators can demand proof that consent was obtained lawfully.

GDPR Requirements for Consent Log

• When consent was given (timestamp)
• Who gave consent (user or anonymous identifier)
• What exactly was consented to (specific categories)
• Version of document user agreed with
• Method of obtaining consent (banner, checkbox, API)
• IP address (for jurisdiction binding)

Database Schema

CREATE TABLE consent_events (
    id BIGSERIAL PRIMARY KEY,
    -- Identification
    user_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
    anonymous_id UUID,          -- for unauthenticated
    session_id VARCHAR(100),

    -- Consent data
    event_type VARCHAR(20) NOT NULL,  -- 'granted', 'denied', 'withdrawn', 'updated'
    categories JSONB NOT NULL,        -- {"analytics": true, "marketing": false, ...}
    document_version VARCHAR(20),     -- Privacy Policy version
    method VARCHAR(30),               -- 'banner', 'settings_page', 'api', 'import'

    -- Context
    ip_address INET,
    user_agent TEXT,
    country_code CHAR(2),
    language_code CHAR(5),

    -- Audit
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    -- Prevent row updates (immutable audit log)
    updated_at TIMESTAMPTZ,
    CONSTRAINT no_updates CHECK (updated_at IS NULL)
);

-- Indexes for fast search
CREATE INDEX idx_consent_user ON consent_events(user_id) WHERE user_id IS NOT NULL;
CREATE INDEX idx_consent_anon ON consent_events(anonymous_id) WHERE anonymous_id IS NOT NULL;
CREATE INDEX idx_consent_date ON consent_events(created_at);
CREATE INDEX idx_consent_type ON consent_events(event_type);

Logging Consents

import uuid
from datetime import datetime
import hashlib

class ConsentLogger:
    def __init__(self, db, geoip):
        self.db = db
        self.geoip = geoip

    def log(self, request, categories: dict, event_type: str,
            user_id=None, document_version='v2024-03'):
        # Determine anonymous identifier
        anonymous_id = self._get_or_create_anonymous_id(request)
        country = self.geoip.country(request.remote_addr)

        self.db.execute("""
            INSERT INTO consent_events
            (user_id, anonymous_id, session_id, event_type, categories,
             document_version, method, ip_address, user_agent, country_code, created_at)
            VALUES (%s, %s, %s, %s, %s::jsonb, %s, %s, %s, %s, %s, %s)
        """, (
            user_id,
            anonymous_id,
            request.session.get('id'),
            event_type,
            json.dumps(categories),
            document_version,
            'banner',
            request.remote_addr,
            request.user_agent.string[:500],
            country,
            datetime.utcnow()
        ))

    def _get_or_create_anonymous_id(self, request):
        cookie_id = request.cookies.get('consent_id')
        if cookie_id:
            return cookie_id
        return str(uuid.uuid4())

    def get_user_consent_history(self, user_id: int):
        return self.db.query("""
            SELECT event_type, categories, document_version, created_at, ip_address
            FROM consent_events
            WHERE user_id = %s
            ORDER BY created_at DESC
        """, (user_id,))

    def get_current_consent(self, user_id: int) -> dict:
        """Current user consent"""
        latest = self.db.query_one("""
            SELECT categories FROM consent_events
            WHERE user_id = %s AND event_type IN ('granted', 'updated')
            ORDER BY created_at DESC
            LIMIT 1
        """, (user_id,))
        return latest['categories'] if latest else {}

API for User: View and Manage

@app.route('/api/my/consent', methods=['GET'])
@login_required
def get_my_consent():
    """User current consent"""
    current = consent_logger.get_current_consent(current_user.id)
    history = consent_logger.get_user_consent_history(current_user.id)

    return jsonify({
        'current': current,
        'history': [{
            'event': r['event_type'],
            'categories': r['categories'],
            'version': r['document_version'],
            'date': r['created_at'].isoformat(),
        } for r in history[:10]]
    })

@app.route('/api/my/consent', methods=['DELETE'])
@login_required
def withdraw_consent():
    """Withdraw marketing processing consent"""
    consent_logger.log(
        request,
        categories={'analytics': False, 'marketing': False, 'preferences': False},
        event_type='withdrawn',
        user_id=current_user.id
    )
    # Remove from marketing systems
    revoke_from_mailchimp(current_user.email)
    revoke_from_facebook_custom_audience(current_user.email)

    return jsonify({'status': 'withdrawn'})