GDPR Consent Management in PostgreSQL: A Complete Implementation Guide

Why Database-Level Consent Tracking

Most consent management happens at the frontend — cookie banners, checkbox forms. But the authoritative record of what the user actually consented to should live where the data lives: in your database. When an auditor asks "show me proof that user-123 consented to newsletter processing," you need a database record, not a frontend log.

Setting Up Consent Management

Define Processing Purposes

First, define what you process data for and the legal basis:

SELECT pgcomply.define_purpose('billing', 'Process payments and invoices', 'contract');
SELECT pgcomply.define_purpose('analytics', 'Product usage analytics', 'legitimate_interest');
SELECT pgcomply.define_purpose('newsletter', 'Email newsletter', 'consent');
SELECT pgcomply.define_purpose('marketing', 'Marketing communications', 'consent');
SELECT pgcomply.define_purpose('profiling', 'User behavior profiling', 'consent');

The legal basis determines whether explicit consent is needed. Processing based on contract or legal_obligation doesn't require opt-in consent.

Record Consent

When a user grants consent:

SELECT pgcomply.grant_consent(
  'user-123',
  'newsletter',
  source := 'signup_form',
  ip_address := '192.168.1.100',
  evidence := 'Checked "Subscribe to newsletter" on /signup at 2026-02-18T10:30:00Z'
);

Parameters captured: subject ID, purpose, source, IP, evidence text, and optional expiration date.

Check Consent in Real-Time

In your application logic:

SELECT pgcomply.has_consent('user-123', 'newsletter');
-- Returns: true/false

Use this before sending any marketing email or processing data for a consent-based purpose.

Handle Withdrawal

SELECT pgcomply.withdraw_consent('user-123', 'newsletter');

The original grant record is preserved (for audit purposes), and a new withdrawal record is created. has_consent() immediately returns false.

Query Consent Status

Full overview per user:

SELECT * FROM pgcomply.consent_status('user-123');

 purpose     | legal_basis         | status   | granted_at          | expires_at
-------------+---------------------+----------+---------------------+-----------
 billing     | contract            | active   | 2026-01-15 10:00:00 | —
 analytics   | legitimate_interest | active   | 2026-01-15 10:00:00 | —
 newsletter  | consent             | withdrawn| 2026-02-18 14:30:00 | —
 marketing   | consent             | active   | 2026-01-15 10:05:00 | 2027-01-15
 profiling   | consent             | expired  | 2025-01-15 10:05:00 | 2026-01-15

Full Audit Trail

SELECT * FROM pgcomply.consent_history('user-123');

Shows every consent event — grants, withdrawals, expirations — with timestamps and evidence. This is what you show the auditor.

Consent Expiration

Set automatic expiration:

SELECT pgcomply.grant_consent(
  'user-123', 'marketing',
  expires_at := NOW() + INTERVAL '1 year'
);

Schedule daily checks for expired consent:

-- Via pg_cron or pgcomply.schedule_jobs()
-- Expired consent is automatically flagged in consent_status()

Integration Pattern

In your application code:

# Before sending marketing email
async def send_newsletter(user_id: str):
    result = await db.fetchval(
        "SELECT pgcomply.has_consent($1, 'newsletter')", user_id
    )
    if not result:
        log.info(f"Skipping {user_id}: no newsletter consent")
        return
    # Send email...

This makes consent enforcement part of your business logic, not an afterthought.

Summary

GDPR consent management belongs in the database where the data lives. pgcomply provides the complete lifecycle: define purposes with legal basis, record grants with evidence, check consent in real-time, handle withdrawals, and maintain an immutable audit trail. No external consent platform needed — it's all SQL.