consent-record-keeping

{
    "version": "KI-CR-v1.1.0",
    "jurisdiction": "EU/GDPR",
    "consentTimestamp": "2026-03-14T10:30:00Z",
    "collectionMethod": "web form with unticked checkboxes",
    "consentReceiptID": "cr_7f3a9b2e-41d8-4c76-b5e3-9a8d1c2f4e60",
    "publicKey": "cloudvault-saas-public-key-fingerprint",
    "language": "en",
    "piiPrincipalId": "usr_7f3a9b2e-41d8-4c76-b5e3-9a8d1c2f4e60",
    "piiControllers": [
        {
            "piiController": "CloudVault SaaS Inc.",
            "onBehalf": false,
            "contact": "42 Innovation Drive, Dublin, D02 YX88, Ireland",
            "phone": "+353-1-555-0142",
            "email": "privacy@cloudvault-saas.eu",
            "piiControllerUrl": "https://cloudvault-saas.eu"
        }
    ],
    "policyUrl": "https://cloudvault-saas.eu/privacy-policy",
    "services": [
        {
            "service": "CloudVault Cloud Storage",
            "purposes": [
                {
                    "purpose": "Service Improvement Analytics",
                    "purposeCategory": ["core function"],
                    "consentType": "explicit",
                    "piiCategory": ["file_metadata", "access_frequency", "storage_patterns"],
                    "primaryPurpose": true,
                    "termination": "https://app.cloudvault-saas.eu/settings/privacy",
                    "thirdPartyDisclosure": false
                }
            ]
        }
    ],
    "sensitive": false,
    "spiCat": []
}

-- Consent records table (append-only, never UPDATE or DELETE)
CREATE TABLE consent_records (
    consent_id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    subject_id          UUID NOT NULL,
    purpose_id          VARCHAR(128) NOT NULL,
    purpose_description TEXT NOT NULL,
    decision            VARCHAR(16) NOT NULL CHECK (decision IN ('granted', 'not_granted', 'withdrawn')),
    mechanism           VARCHAR(64) NOT NULL,
    consent_text_version CHAR(64) NOT NULL,
    timestamp_utc       TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    ip_address          INET,
    user_agent          TEXT,
    session_id          UUID,
    source              VARCHAR(64) NOT NULL,
    controller_name     VARCHAR(256) NOT NULL DEFAULT 'CloudVault SaaS Inc.',
    record_hash         CHAR(64) NOT NULL,
    previous_hash       CHAR(64),
    created_at          TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- Index for efficient lookups per subject
CREATE INDEX idx_consent_records_subject ON consent_records(subject_id, purpose_id, timestamp_utc DESC);

-- Consent text versions (immutable)
CREATE TABLE consent_text_versions (
    version_id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    purpose_id   VARCHAR(128) NOT NULL,
    consent_text TEXT NOT NULL,
    text_hash    CHAR(64) NOT NULL UNIQUE,
    approved_by  VARCHAR(256) NOT NULL,
    effective_from TIMESTAMP WITH TIME ZONE NOT NULL,
    effective_until TIMESTAMP WITH TIME ZONE,
    created_at   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

-- Prevent modifications to consent records
REVOKE UPDATE, DELETE ON consent_records FROM app_user;
REVOKE UPDATE, DELETE ON consent_text_versions FROM app_user;

SELECT DISTINCT ON (purpose_id)
    purpose_id, decision, timestamp_utc, consent_text_version, mechanism, source
FROM consent_records
WHERE subject_id = 'usr_7f3a9b2e-41d8-4c76-b5e3-9a8d1c2f4e60'
  AND timestamp_utc <= '2026-02-15T00:00:00Z'
ORDER BY purpose_id, timestamp_utc DESC;

SELECT consent_id, record_hash, previous_hash,
    CASE WHEN LAG(record_hash) OVER (ORDER BY created_at) = previous_hash
         THEN 'VALID' ELSE 'BROKEN'
    END AS chain_status
FROM consent_records
WHERE subject_id = 'usr_7f3a9b2e-41d8-4c76-b5e3-9a8d1c2f4e60'
ORDER BY created_at;