Consent Database Schema: How to Store Consent So You Can Prove It Later

The hardest part of consent is not collecting it. It is proving, six months later, what a specific person saw and chose. Most consent tables only store "accepted" and "at," which proves nothing. Here is a schema design that actually holds up.

The three things a consent record must capture

A defensible consent record answers three questions:

1. What did they see? Not "the cookie banner" in general—the specific version of the notice, the list of purposes, the wording, as it existed at that moment.
2. What did they choose? Per-purpose yes or no, not one lumpen "accepted."
3. When, and by whom? A timestamp, and some way to correlate the decision to a device or user.

If your schema cannot answer all three, it stores the outcome but not the consent.

The two-table design

Keep the version (what they saw) separate from the decision (what they chose). Mixing them is how notices silently change and the old wording disappears.

-- One row per published consent configuration.
CREATE TABLE consent_versions (
  id            BIGSERIAL PRIMARY KEY,
  published_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  snapshot      JSONB NOT NULL,          -- full notice + purpose definitions
  content_hash  TEXT NOT NULL            -- hash of the snapshot, for integrity
);

-- One row per decision. Append-only: never UPDATE, never DELETE.
CREATE TABLE consent_records (
  id            BIGSERIAL PRIMARY KEY,
  version_id    BIGINT NOT NULL REFERENCES consent_versions(id),
  device_id     TEXT,                    -- installation / device correlation, not PII
  purpose_key   TEXT NOT NULL,           -- e.g. 'marketing', 'analytics'
  granted       BOOLEAN NOT NULL,
  recorded_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_consent_records_device
  ON consent_records (device_id, purpose_key);

The version_id foreign key is the whole point. A change to the notice creates a new version row; the old row never changes. Old decisions keep pointing at the exact version the person saw.

The rules that make it hold up

• Append-only. Never UPDATE a consent record. A change of mind is a new row with the new choice. Rewriting history is the fastest way to invalidate your evidence.
• Snapshot the notice. Store the full notice content in the version row. A reference to "the notice" is worthless if the notice is an editable document somewhere else.
• Hash the content. Store a hash of the snapshot so a tampered-with record is detectable. The receipt should also carry the hash of what was shown, so the two can be cross-checked.
• Per-purpose choice. Store purpose_key and granted per row, not one boolean per user. "They clicked accept" means nothing unless you know what the accept covered, and required purposes should never be toggleable off.
• Don't store what you don't need. For the core path you do not need an email or IP address on the receipt—you need a device identifier to correlate choices and a timestamp. Every unnecessary field is data you now have to protect and delete on request.

Withdrawal is a new record, not an edit

When a user withdraws or changes optional purposes, insert new rows:

INSERT INTO consent_records (version_id, device_id, purpose_key, granted, recorded_at)
VALUES
  (7, 'device-a1b2', 'marketing',  FALSE, NOW()),
  (7, 'device-a1b2', 'analytics',  FALSE, NOW());

The history stays intact: earlier rows show consent was given, later rows show it was withdrawn. A reviewer can reconstruct the full timeline instead of trusting the latest state.

Idempotency and duplicates

The SDK and your retry logic may deliver the same decision twice. Add a natural dedup key—for example a client-generated decision ID—and make the insert ignore duplicates:

CREATE UNIQUE INDEX idx_consent_unique_decision
  ON consent_records (device_id, purpose_key, decision_uid);

Without this, a retried POST creates two rows for one decision and your counts drift.

The pattern in Erasure

This is the same shape as Erasure's Accord: an immutable published consent version with a content hash, append-only consent receipts that lock to the version the person saw, per-purpose choice, withdrawal that creates a new receipt, and a device-scoped identifier rather than full subject accounts. A consent.updated webhook tells your backend when choices change so downstream systems can react.

For more on the product side, the consent management hub covers the workflow, and the receipts documentation explains the exact model.