Library Catalog Data Sync Pipelines

Implementing GDPR Right-to-Erasure in Circulation Data

This page answers one narrow operational question: a patron files a GDPR Article 17 “right to erasure” request, you delete their row from the patron table, the ticket is closed — and weeks later their personal data is still sitting in circulation history, active holds, a derived analytics warehouse, and last night’s backup. A single-table delete does not satisfy erasure when personal data has been denormalized across a sync pipeline. This page sits under Patron Consent and Right-to-Erasure Management, which covers the consent-and-erasure lifecycle end to end, and within the broader Patron Validation & Privacy Data Routing architecture. If you run any ILS whose circulation events are replicated downstream for reporting, the leak below is one you will eventually trigger the first time a patron exercises their rights.

Fan-out erasure from a single request to every downstream store, with verification and a hashed tombstone A single GDPR Article 17 erasure request enters an erasure coordinator. The coordinator fans out to four target stores in parallel: the patron table, which is hard-deleted; the circulation history log, where personal fields are nulled; the active holds queue, which is hard-deleted; and the analytics warehouse, where the patron surrogate is purged. Each target reports completion back to the coordinator, which verifies that no target still contains the patron identifier. The append-only audit log cannot be deleted, so instead of a hard delete it is written a hashed tombstone that records the erasure without retaining any personal data. Only when every target reports done and verification passes is the request marked complete. erasure request Article 17 · patron_id erasure coordinator fan-out · verify patron table hard delete row circulation history null personal fields active holds queue hard delete rows analytics warehouse purge surrogate append-only audit log cannot delete — write hashed tombstone instead verify & complete no target retains the identifier all targets report done

Problem Framing

The symptom is a closed ticket that did not actually close anything. The service desk receives an Article 17 request, an admin runs DELETE FROM patron WHERE patron_id = 44182, the row disappears, and the case is marked resolved. No error fires. The patron is gone from every screen a librarian looks at.

The data is not gone from the pipeline. A single query across the downstream stores shows the “erased” patron is still fully present:

text
$ psql -c "SELECT 'circulation' AS store, count(*) FROM circulation_log WHERE patron_id = 44182
           UNION ALL
           SELECT 'holds', count(*) FROM hold_queue WHERE patron_id = 44182
           UNION ALL
           SELECT 'analytics', count(*) FROM warehouse.fact_loans WHERE patron_key = 44182;"
   store     | count
-------------+-------
 circulation |   612   <-- name, email, checkout timestamps still present
 holds       |     3   <-- pickup branch + hold history still present
 analytics   |   612   <-- replicated rows in the reporting warehouse

The patron table was only the entry point. Every checkout wrote a row into circulation_log that denormalized the patron’s name and email for reporting convenience; the nightly ETL copied those rows into warehouse.fact_loans; and the hold queue kept its own copy of contact details for pickup notices. Deleting the one authoritative row left every derived copy untouched — and under GDPR each of those copies is personal data the controller is still processing.

Root Cause

The failure is that circulation data is denormalized and replicated downstream, and a delete does not propagate. In a normalized schema, a foreign-key ON DELETE CASCADE would clean up child rows, but circulation pipelines deliberately break normalization: they copy the patron’s name, email and pickup branch into each event so that reports and notices do not need a live join back to the patron table. Those copies live in separate systems — an analytics warehouse, a search index, a notifications queue — that have no foreign key to the patron table at all, and often no synchronous link to it. A DELETE on the source is simply never observed by them.

There is a second, sharper tension. One of those downstream stores is an append-only audit log whose whole legal purpose is to be immutable — you cannot delete from it without destroying the integrity guarantee that makes it admissible. GDPR itself recognizes this: Article 17(3) exempts data the controller must retain for a legal obligation. So the naive answer of “delete everywhere” is both incomplete (it misses the replicas) and illegal in one place (it would gut the audit log). Erasure has to be modelled as a coordinated operation with a per-store strategy, not a single statement.

The fix is to invert the assumption that the patron table is the only place personal data lives. Every store that ever received a copy must be enrolled as an erasure target with its own disposition, exactly the disposition-per-target discipline the parent Patron Consent and Right-to-Erasure Management cluster defines.

Solution

Model erasure as a request that fans out to every enrolled target, applies the correct strategy per store, and verifies each one before the request is considered satisfied. Hard-deletable stores are deleted; the circulation log is anonymized in place by nulling personal columns while keeping the row for referential and statistical integrity; and the append-only audit log — which cannot be deleted — is given a hashed tombstone that proves the erasure occurred without retaining any personal data. The whole operation is idempotent, so a retry after a partial failure converges rather than double-processing.

python
from __future__ import annotations

import hashlib
import hmac
import logging
from dataclasses import dataclass, field
from typing import Protocol

logger = logging.getLogger("gdpr.erasure")


class ErasureError(RuntimeError):
    """A target could not complete erasure; the request stays open for retry."""


@dataclass(frozen=True)
class ErasureRequest:
    patron_id: int
    request_ref: str          # the Article 17 case reference
    tombstone_secret: bytes   # keyed hash so the id cannot be brute-forced


class ErasureTarget(Protocol):
    name: str

    def erase(self, req: ErasureRequest) -> None:
        """Apply this store's erasure strategy. Must be idempotent."""

    def still_present(self, req: ErasureRequest) -> bool:
        """True if any personal data for the patron remains in this store."""


@dataclass
class ErasureCoordinator:
    targets: list[ErasureTarget]
    _completed: set[str] = field(default_factory=set)

    def run(self, req: ErasureRequest) -> None:
        pending = [t for t in self.targets if t.name not in self._completed]
        for target in pending:
            try:
                target.erase(req)
            except Exception as exc:  # noqa: BLE001 - re-raised as ErasureError below
                logger.error(
                    "erasure_target_failed",
                    extra={"target": target.name, "request_ref": req.request_ref},
                )
                raise ErasureError(f"{target.name} failed erasure") from exc
            self._completed.add(target.name)
            logger.info(
                "erasure_target_done",
                extra={"target": target.name, "request_ref": req.request_ref},
            )
        self._verify(req)

    def _verify(self, req: ErasureRequest) -> None:
        leaked = [t.name for t in self.targets if t.still_present(req)]
        if leaked:
            logger.error(
                "erasure_verification_failed",
                extra={"request_ref": req.request_ref, "leaked_targets": leaked},
            )
            raise ErasureError(f"data still present in: {', '.join(leaked)}")
        logger.info(
            "erasure_complete",
            extra={"request_ref": req.request_ref, "targets": len(self.targets)},
        )

Each store implements the ErasureTarget contract with the strategy its schema demands. The hard-delete stores drop rows; the circulation log nulls personal columns; the audit log writes a tombstone instead of deleting.

python
PII_COLUMNS = ("patron_name", "patron_email", "patron_phone")


@dataclass
class CirculationLogTarget:
    """Anonymize in place: keep the loan rows, remove the personal data."""

    name: str = "circulation_log"
    conn: "psycopg.Connection" = None  # injected

    def erase(self, req: ErasureRequest) -> None:
        set_clause = ", ".join(f"{col} = NULL" for col in PII_COLUMNS)
        with self.conn.cursor() as cur:
            cur.execute(
                f"UPDATE circulation_log SET {set_clause} WHERE patron_id = %s",
                (req.patron_id,),
            )
        self.conn.commit()

    def still_present(self, req: ErasureRequest) -> bool:
        cond = " OR ".join(f"{col} IS NOT NULL" for col in PII_COLUMNS)
        with self.conn.cursor() as cur:
            cur.execute(
                f"SELECT 1 FROM circulation_log "
                f"WHERE patron_id = %s AND ({cond}) LIMIT 1",
                (req.patron_id,),
            )
            return cur.fetchone() is not None


@dataclass
class AuditLogTarget:
    """Append-only: cannot delete. Write a hashed tombstone instead."""

    name: str = "audit_log"
    conn: "psycopg.Connection" = None  # injected

    def _tombstone(self, req: ErasureRequest) -> str:
        # Keyed hash: proves "this patron was erased" without storing the id.
        return hmac.new(
            req.tombstone_secret,
            f"patron:{req.patron_id}".encode(),
            hashlib.sha256,
        ).hexdigest()

    def erase(self, req: ErasureRequest) -> None:
        digest = self._tombstone(req)
        with self.conn.cursor() as cur:
            # Redact PII on historical entries; append one tombstone marker.
            cur.execute(
                "UPDATE audit_log SET patron_name = NULL, patron_email = NULL "
                "WHERE patron_id = %s",
                (req.patron_id,),
            )
            cur.execute(
                "INSERT INTO audit_log (event, patron_hash, request_ref) "
                "VALUES ('erasure_tombstone', %s, %s) "
                "ON CONFLICT (patron_hash) DO NOTHING",  # idempotent
                (digest, req.request_ref),
            )
        self.conn.commit()

    def still_present(self, req: ErasureRequest) -> bool:
        with self.conn.cursor() as cur:
            cur.execute(
                "SELECT 1 FROM audit_log "
                "WHERE patron_id = %s "
                "AND (patron_name IS NOT NULL OR patron_email IS NOT NULL) LIMIT 1",
                (req.patron_id,),
            )
            return cur.fetchone() is not None

The behavioural change is that erasure is now a whole-pipeline operation. The coordinator only reports erasure_complete when every enrolled target has both applied its strategy and passed the still_present check — so a forgotten warehouse copy surfaces as a verification failure rather than a silent leak. The audit log keeps its immutability guarantee: instead of deleting rows, it redacts the personal columns and appends a keyed-hash tombstone that lets a future auditor confirm the erasure happened without the row ever revealing which patron it was. Because erase uses NULL-idempotent updates and an ON CONFLICT DO NOTHING insert, re-running the request after a crash converges to the same state.

Compliance or Privacy Impact

Erasure is a different obligation from retention expiry, and conflating them is the most common way libraries get this wrong.

The operation narrows the personal-data surface rather than widening it, but it shifts risk onto the completeness of the target list. A store that receives circulation copies but is not enrolled as an ErasureTarget is a silent leak with a closed ticket in front of it, so enrolling a new downstream consumer must include registering it here.

Verification

Confirm the erasure is complete with a test that seeds a patron across every store, runs the coordinator, and asserts that no store still reports the identifier while the audit tombstone exists.

python
def test_erasure_fans_out_and_verifies(coordinator, req, stores) -> None:
    # Seed personal data in every enrolled target.
    seed_patron(stores, patron_id=req.patron_id)

    coordinator.run(req)

    # No target retains any personal data for the patron.
    for target in coordinator.targets:
        assert not target.still_present(req), f"{target.name} still has PII"

    # The append-only log kept a tombstone, not the identity.
    tombstone = fetch_audit_tombstone(stores.audit, req.request_ref)
    assert tombstone.event == "erasure_tombstone"
    assert tombstone.patron_hash != str(req.patron_id)  # opaque, not the id
    assert len(tombstone.patron_hash) == 64             # sha256 hexdigest


def test_erasure_is_idempotent(coordinator, req, stores) -> None:
    seed_patron(stores, patron_id=req.patron_id)
    coordinator.run(req)
    coordinator.run(req)  # a retry must not raise or duplicate the tombstone

    assert count_audit_tombstones(stores.audit, req.request_ref) == 1

For a running system, add a standing reconciliation query — the same shape as the diagnostic that exposed the leak — that periodically checks recently-erased request_refs against every store and alerts if any identifier reappears. A nonzero count after an ETL run is your early signal that a downstream job re-imported personal data from a stale source and the patron must be re-erased. Track the age of the oldest open erasure request against your backup rotation window; if it exceeds that window, a restore has silently resurrected erased data.