Queries · q332 · Data Protection Officer · week 2, night 4
“After an erasure is marked completed, do login attempts and sessions actually stop, or does activity continue afterward — and if attempts occur, do any of them succeed?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
Week 2; shared with Henrik Aalto, Chief Information Security Officer
A data protection authority asked how we handle privacy: how many privacy requests we receive and how fast we complete them, how consent is recorded, and what remains after an account is deleted.
The manager, at the start of week 2
Subject: Week 2 — DPA inquiry changes our priorities
Priyanka,
Good work — this is exactly the kind of digging I wanted. A few things surprised me and now matter more because of the regulator inquiry (which I'm handling jointly with Henrik Aalto, CISO — our response needs to be accurate and defensible, not just reassuring).
What struck me:
So, for this week, please prioritize work that lets us answer the DPA's three questions honestly: volume and speed of privacy requests, how consent is captured and whether it's reliable, and what data persists post-deletion. That's the deliverable Henrik and I need to brief the board audit committee before we respond.
My questions:
If all four can't fit, start with 2 and 4 — those go directly into the regulator response.
Thanks, Ines
Subject: Security angle for the DPA response
Hi Priyanka,
A few things from my side that would strengthen the privacy response:
Happy to compare notes before you send anything to data engineering.
Henrik
The analyst · week 2, night 4, request 1 of 3
Last night's post-erasure report surfaced something I need to pin down on this final night: several "remaining" login_attempts/sessions dates are not just old pre-erasure history lingering — they occur well AFTER the erasure's completed_at (e.g. Marrowby & Vane, Cragsley Outfitters, and Studio Vrij subjects all show login attempts dated 2025-02-08 to 2025-02-11, and one Kontor24 Bürobedarf subject shows a session on 2025-12-02, seven months after its 2025-04-29 completion). The Feb 2025 cluster across multiple unrelated erased accounts lines up exactly with Noah Fischer's (CISO) channel post about a 10x login-failure spike in Feb 2025 that wasn't caught by alerts — this could mean that spike included attempts against accounts that should have already been locked down by erasure, which is squarely relevant to both the regulator response and Henrik's security angle. I want hard evidence (count, success/fail, exact timing) rather than eyeballing. I also realized I've never actually checked whether "completed" erasure touches the core users record at all (status/deleted_at) — all my evidence so far is about downstream history tables, not whether the primary account was deactivated, which is the more fundamental test of whether erasure does anything to account access. This is my last night, so I'm spending all 3 slots closing out Henrik's post-erasure footprint question definitively rather than opening new territory; the other items (1, 2, 3, 4, and Henrik's consent/closure-time asks) are already answered from the prior three nights.
The engineer · 1 dry run on the empty schema, then the submission
WITH erasures AS ( SELECT dsr.id, dsr.public_id, dsr.tenant_id, t.name AS tenant_name, dsr.subject_user_id, dsr.completed_at FROM data_subject_requests dsr JOIN tenants t ON t.id = dsr.tenant_id WHERE dsr.request_type = 'erasure' AND dsr.status = 'completed' ), la AS ( SELECT e.id AS erasure_id, count(*) AS login_attempts_count, count(*) FILTER (WHERE la.succeeded) AS succeeded_count, min(la.occurred_at) AS earliest_attempt, max(la.occurred_at) AS latest_attempt FROM erasures e JOIN login_attempts la ON la.tenant_id = e.tenant_id AND la.user_id = e.subject_user_id AND la.occurred_at > e.completed_at GROUP BY e.id ), se AS ( SELECT e.id AS erasure_id, count(*) AS sessions_count, max(s.started_at) AS latest_session_start FROM erasures e JOIN sessions s ON s.tenant_id = e.tenant_id AND s.user_id = e.subject_user_id AND s.started_at > e.completed_at GROUP BY e.id ) SELECT e.public_id AS dsar_public_id, e.tenant_name, e.subject_user_id, CAST(e.completed_at AS date) AS completed_at, COALESCE(la.login_attempts_count, 0) AS login_attempts_after, COALESCE(la.succeeded_count, 0) AS succeeded_attempts_after, la.earliest_attempt AS earliest_login_attempt_after, la.latest_attempt AS latest_login_attempt_after, COALESCE(se.sessions_count, 0) AS sessions_after, se.latest_session_start AS latest_session_start_after FROM erasures e LEFT JOIN la ON la.erasure_id = e.id LEFT JOIN se ON se.erasure_id = e.id ORDER BY e.completed_at ASC, e.id ASC
“Query compiles with the expected 10 columns and grain of one row per completed erasure request.”
“Submitted. The report returns one row per completed erasure DSAR with post-completion login attempt counts (total and succeeded), earliest/latest attempt timestamps, and post-completion session counts with latest session start — ordered by completed_at ascending as requested.”
As Priyanka Desai saw it, from the small dataset the agents worked on
dsar_public_id | tenant_name | subject_user_id | completed_at | login_attempts_after | succeeded_attempts_after | earliest_login_attempt_after | latest_login_attempt_after | sessions_after | latest_session_start_after -------------------------------------+---------------------+-----------------+--------------+----------------------+--------------------------+------------------------------+----------------------------+----------------+--------------------------- 0bf5fe16-9c96-4a01-9861-ba58a8c62338 | Grind Collective | 345 | 2020-04-28 | 0 | 0 | NULL | NULL | 0 | NULL 4531b08c-8444-4f57-aed1-451f6cbf1fed | Ampfeld Elektronik | 1218 | 2021-02-15 | 0 | 0 | NULL | NULL | 0 | NULL ddc3341d-2a13-41d3-b194-d7f512b94132 | Marrowby & Vane | 407 | 2021-04-16 | 4 | 0 | 2025-02-10 09:24:45 | 2025-02-11 14:15:31 | 0 | NULL 74bec8f7-3b6e-4350-8535-caee73c055e1 | Marrowby & Vane | 363 | 2021-10-16 | 2 | 0 | 2025-02-10 17:23:23 | 2025-02-10 20:15:35 | 0 | NULL df4dc623-ddb6-4508-81af-ee136635850b | Cragsley Outfitters | 671 | 2022-02-05 | 4 | 0 | 2025-02-08 11:09:09 | 2025-02-10 23:48:15 | 0 | NULL cfddd972-efd4-4480-9ab3-12a867c46b79 | Coastline Interiors | 1769 | 2022-06-03 | 0 | 0 | NULL | NULL | 0 | NULL f9ddeb61-8a65-4ffc-8a99-2d6746cab8ee | Wiesgrund Wohnen | 1735 | 2022-08-26 | 0 | 0 | NULL | NULL | 0 | NULL 2554d690-68f1-4e5f-a5bd-aa9ecd805f5b | Midland Safety Wear | 1455 | 2022-09-02 | 0 | 0 | NULL | NULL | 0 | NULL 3193004e-c781-41fc-9cd9-c2bfb9f01dbe | Studio Vrij | 1519 | 2023-05-15 | 1 | 0 | 2025-02-08 16:14:25 | 2025-02-08 16:14:25 | 0 | NULL 861d7a09-3d30-4361-9726-2cef07e08814 | Gratlinie Outdoor | 2849 | 2023-09-01 | 0 | 0 | NULL | NULL | 0 | NULL e3271040-65dc-4131-a4a0-6d9f36d6980f | Ampfeld Elektronik | 1195 | 2023-10-26 | 0 | 0 | NULL | NULL | 0 | NULL 074e73eb-f889-4b53-a9c6-0fa4b5499b07 | Fennel & Flint | 2556 | 2023-11-22 | 0 | 0 | NULL | NULL | 0 | NULL 24d61e33-87e8-44e2-9bde-a63f6a1ddaf5 | Spice Route Box | 3607 | 2024-01-05 | 0 | 0 | NULL | NULL | 0 | NULL 806d5c26-623f-4692-a0a4-812bc1f5d0cc | Ampfeld Elektronik | 4197 | 2024-02-17 | 0 | 0 | NULL | NULL | 0 | NULL 7f3350f7-03dd-4fdd-a4f1-4c25e3dc35c1 | Marrowby & Vane | 2805 | 2024-02-28 | 3 | 0 | 2025-02-09 02:24:11 | 2025-02-11 23:30:22 | 0 | NULL 1931ed93-6d67-494d-87b9-618f9047e0f7 | Fell & Fjell | 4617 | 2024-04-03 | 0 | 0 | NULL | NULL | 0 | NULL d89368e7-9f81-4832-8e7e-8da777ac3441 | Spice Route Box | 2591 | 2024-04-05 | 0 | 0 | NULL | NULL | 0 | NULL 691ec686-457b-4eba-966f-40194fab69fa | Sorella Skin | 5453 | 2024-05-16 | 0 | 0 | NULL | NULL | 0 | NULL a3194bf3-bb13-40d3-872d-b28febd38b01 | Ampfeld Elektronik | 4907 | 2024-05-28 | 0 | 0 | NULL | NULL | 0 | NULL 9158c9e4-8abc-42b2-9d00-0c14afced0a4 | Kayak Corner | 1792 | 2024-05-31 | 0 | 0 | NULL | NULL | 0 | NULL 99add429-adf4-44a4-b51b-e668f52d43aa | Coastline Interiors | 2532 | 2024-06-07 | 0 | 0 | NULL | NULL | 0 | NULL 442f76f1-6417-4666-821a-7bb927f1743a | Cragsley Outfitters | 829 | 2024-06-14 | 3 | 0 | 2025-02-08 00:59:38 | 2025-02-10 08:31:50 | 0 | NULL a627a640-0eb3-45fe-80c8-fb9ab29758e0 | Little Larch Kids | 6196 | 2024-08-27 | 0 | 0 | NULL | NULL | 0 | NULL 49a0b5ef-09e3-4e24-8e7f-a481e2b109c5 | Petalcourt Beauty | 3366 | 2024-09-15 | 0 | 0 | NULL | NULL | 0 | NULL 4f961286-195f-4e5f-a7bd-6c251c3c3488 | Fennel & Flint | 3458 | 2024-10-17 | 0 | 0 | NULL | NULL | 0 | NULL 840a9b91-c6ce-4439-b3a4-4182c4c62a22 | Studio Vrij | 6300 | 2024-11-12 | 1 | 0 | 2025-02-09 21:07:56 | 2025-02-09 21:07:56 | 0 | NULL 38e27076-1c76-43de-b5d7-c3228e334cd6 | Fennel & Flint | 6376 | 2024-11-14 | 0 | 0 | NULL | NULL | 0 | NULL a6d8af68-148a-46e1-9f4d-0c3c16f523b6 | Willow & Wren | 5765 | 2024-11-18 | 0 | 0 | NULL | NULL | 0 | NULL 6e8fb6f6-1c2f-4219-aad4-d62e5e4f39f6 | Bijoux Lune | 5736 | 2024-11-22 | 0 | 0 | NULL | NULL | 0 | NULL 642c8a31-9dda-44cc-b8b7-95c39e0c5bff | Fennel & Flint | 7406 | 2025-01-04 | 0 | 0 | NULL | NULL | 0 | NULL fc8c1dbc-10e7-468b-84d3-618415e5305a | Marrowby & Vane | 412 | 2025-01-10 | 0 | 0 | NULL | NULL | 0 | NULL f0676f94-b6b8-42de-8f06-84f200c42a0b | Cragsley Outfitters | 8111 | 2025-01-31 | 2 | 0 | 2025-02-08 00:41:11 | 2025-02-09 15:04:21 | 0 | NULL cc524f30-5be6-48a5-96b6-870e6d379aea | Marrowby & Vane | 6617 | 2025-03-29 | 0 | 0 | NULL | NULL | 0 | NULL e3a5ef8b-e2e0-41b2-953a-838812131698 | Kontor24 Bürobedarf | 8617 | 2025-04-29 | 3 | 3 | 2025-09-22 06:47:29.162759 | 2025-12-02 13:53:08.887219 | 3 | 2025-12-02 13:53:08.887219 80a25dc8-3daa-43f2-b1ee-999985f0f6e5 | Cragsley Outfitters | 8596 | 2025-06-09 | 0 | 0 | NULL | NULL | 0 | NULL dc5213a1-e7c7-4882-853b-c8c29593e0da | Paperplane Press | 7512 | 2025-07-09 | 0 | 0 | NULL | NULL | 0 | NULL 7be56ee5-ad97-46ea-8777-b3ca09d38a57 | Coastline Interiors | 9683 | 2025-08-29 | 0 | 0 | NULL | NULL | 0 | NULL 571b0925-08eb-47d0-a322-4d916b11c31f | Marrowby & Vane | 10182 | 2025-09-29 | 0 | 0 | NULL | NULL | 0 | NULL e7a93251-9905-4243-bce4-d289a7b17144 | Marrowby & Vane | 6138 | 2025-11-10 | 0 | 0 | NULL | NULL | 0 | NULL (39 rows)
The memo and the queries that followed; links found by title, so inferred
It builds on:
Changed only where the benchmark needs the same result on every system
WITH erasures AS ( SELECT dsr.id, dsr.public_id, dsr.tenant_id, t.name AS tenant_name, dsr.subject_user_id, dsr.completed_at SELECT dsr.id AS id, dsr.public_id AS public_id, dsr.tenant_id AS tenant_id, t.name AS tenant_name, dsr.subject_user_id AS subject_user_id, dsr.completed_at AS completed_at FROM data_subject_requests dsr JOIN tenants t ON t.id = dsr.tenant_id WHERE dsr.request_type = 'erasure' AND dsr.status = 'completed'),la AS ( SELECT e.id AS erasure_id, count(*) AS login_attempts_count, count(*) FILTER (WHERE la.succeeded) AS succeeded_count, min(la.occurred_at) AS earliest_attempt, max(la.occurred_at) AS latest_attempt FROM erasures e JOIN login_attempts la ON la.tenant_id = e.tenant_id AND la.user_id = e.subject_user_id AND la.occurred_at > e.completed_at GROUP BY e.id),se AS ( SELECT e.id AS erasure_id, count(*) AS sessions_count, max(s.started_at) AS latest_session_start FROM erasures e JOIN sessions s ON s.tenant_id = e.tenant_id AND s.user_id = e.subject_user_id AND s.started_at > e.completed_at GROUP BY e.id)SELECT e.public_id AS dsar_public_id, e.tenant_name, e.subject_user_id, CAST(e.completed_at AS date) AS completed_at, e.tenant_name AS tenant_name, e.subject_user_id AS subject_user_id, CAST(e.completed_at AS date) AS completed_on, COALESCE(la.login_attempts_count, 0) AS login_attempts_after, COALESCE(la.succeeded_count, 0) AS succeeded_attempts_after, la.earliest_attempt AS earliest_login_attempt_after, la.latest_attempt AS latest_login_attempt_after, COALESCE(se.sessions_count, 0) AS sessions_after, se.latest_session_start AS latest_session_start_afterFROM erasures eLEFT JOIN la ON la.erasure_id = e.idLEFT JOIN se ON se.erasure_id = e.idORDER BY e.completed_at ASC, e.id ASCORDER BY e.completed_at ASC NULLS LAST, e.id ASC NULLS LAST
WITH erasures AS ( SELECT dsr.id AS id, dsr.public_id AS public_id, dsr.tenant_id AS tenant_id, t.name AS tenant_name, dsr.subject_user_id AS subject_user_id, dsr.completed_at AS completed_at FROM data_subject_requests dsr JOIN tenants t ON t.id = dsr.tenant_id WHERE dsr.request_type = 'erasure' AND dsr.status = 'completed' ), la AS ( SELECT e.id AS erasure_id, count(*) AS login_attempts_count, count(*) FILTER (WHERE la.succeeded) AS succeeded_count, min(la.occurred_at) AS earliest_attempt, max(la.occurred_at) AS latest_attempt FROM erasures e JOIN login_attempts la ON la.tenant_id = e.tenant_id AND la.user_id = e.subject_user_id AND la.occurred_at > e.completed_at GROUP BY e.id ), se AS ( SELECT e.id AS erasure_id, count(*) AS sessions_count, max(s.started_at) AS latest_session_start FROM erasures e JOIN sessions s ON s.tenant_id = e.tenant_id AND s.user_id = e.subject_user_id AND s.started_at > e.completed_at GROUP BY e.id ) SELECT e.public_id AS dsar_public_id, e.tenant_name AS tenant_name, e.subject_user_id AS subject_user_id, CAST(e.completed_at AS date) AS completed_on, COALESCE(la.login_attempts_count, 0) AS login_attempts_after, COALESCE(la.succeeded_count, 0) AS succeeded_attempts_after, la.earliest_attempt AS earliest_login_attempt_after, la.latest_attempt AS latest_login_attempt_after, COALESCE(se.sessions_count, 0) AS sessions_after, se.latest_session_start AS latest_session_start_after FROM erasures e LEFT JOIN la ON la.erasure_id = e.id LEFT JOIN se ON se.erasure_id = e.id ORDER BY e.completed_at ASC NULLS LAST, e.id ASC NULLS LAST
The agents' own records, as the role play wrote them; only the personal data of the person who ran it is redacted
Report request from Priyanka Desai (analyst for the Data Protection Officer): Title: Post-erasure login and session activity: timing and success Question: After an erasure is marked completed, do login attempts and sessions actually stop, or does activity continue afterward — and if attempts occur, do any of them succeed? Grain: one row per completed erasure request (request_type='erasure', status='completed', the same 39 rows as last night) Columns: - dsar public_id - tenant name - subject_user_id - completed_at (date) - login attempts strictly after completed_at (count) - of those, succeeded (count, login_attempts.succeeded = true) - earliest login attempt after completed_at - latest login attempt after completed_at - sessions strictly after completed_at (count, sessions.started_at > completed_at) - latest session start after completed_at Details: Source: data_subject_requests (request_type='erasure', status='completed') left-joined to login_attempts and sessions on tenant_id + user_id = subject_user_id, filtered to occurred_at/started_at strictly greater than the request's completed_at. This isolates genuinely post-erasure activity (unlike last night's report, which counted all remaining rows regardless of date). The succeeded-count column is the critical security signal: failed attempts after erasure suggest stale/targeted credential attempts against a closed account; any succeeded=true row would mean live access continued after erasure. Order by completed_at ascending. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH erasures AS ( SELECT dsr.id, dsr.public_id, dsr.tenant_id, t.name AS tenant_name, dsr.subject_user_id, dsr.completed_at FROM data_subject_requests dsr JOIN tenants t ON t.id = dsr.tenant_id WHERE dsr.request_type = 'erasure' AND dsr.status = 'completed' ), la AS ( SELECT e.id AS erasure_id, count(*) AS login_attempts_count, count(*) FILTER (WHERE la.succeeded) AS succeeded_count, min(la.occurred_at) AS earliest_attempt, max(la.occurred_at) AS latest_attempt FROM erasures e JOIN login_attempts la ON la.tenant_id = e.tenant_id AND la.user_id = e.subject_user_id AND la.occurred_at > e.completed_at GROUP BY e.id ), se AS ( SELECT e.id AS erasure_id, count(*) AS sessions_count, max(s.started_at) AS latest_session_start FROM erasures e JOIN sessions s ON s.tenant_id = e.tenant_id AND s.user_id = e.subject_user_id AND s.started_at > e.completed_at GROUP BY e.id ) SELECT e.public_id AS dsar_public_id, e.tenant_name, e.subject_user_id, CAST(e.completed_at AS date) AS completed_at, COALESCE(la.login_attempts_count, 0) AS login_attempts_after, COALESCE(la.succeeded_count, 0) AS succeeded_attempts_after, la.earliest_attempt AS earliest_login_attempt_after, la.latest_attempt AS latest_login_attempt_after, COALESCE(se.sessions_count, 0) AS sessions_after, se.latest_session_start AS latest_session_start_after FROM erasures e LEFT JOIN la ON la.erasure_id = e.id LEFT JOIN se ON se.erasure_id = e.id ORDER BY e.completed_at ASC, e.id ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: dsar_public_id, tenant_name, subject_user_id, completed_at, login_attempts_after, succeeded_attempts_after, earliest_login_attempt_after, latest_login_attempt_after, sessions_after, latest_session_start_after
submitWITH erasures AS ( SELECT dsr.id, dsr.public_id, dsr.tenant_id, t.name AS tenant_name, dsr.subject_user_id, dsr.completed_at FROM data_subject_requests dsr JOIN tenants t ON t.id = dsr.tenant_id WHERE dsr.request_type = 'erasure' AND dsr.status = 'completed' ), la AS ( SELECT e.id AS erasure_id, count(*) AS login_attempts_count, count(*) FILTER (WHERE la.succeeded) AS succeeded_count, min(la.occurred_at) AS earliest_attempt, max(la.occurred_at) AS latest_attempt FROM erasures e JOIN login_attempts la ON la.tenant_id = e.tenant_id AND la.user_id = e.subject_user_id AND la.occurred_at > e.completed_at GROUP BY e.id ), se AS ( SELECT e.id AS erasure_id, count(*) AS sessions_count, max(s.started_at) AS latest_session_start FROM erasures e JOIN sessions s ON s.tenant_id = e.tenant_id AND s.user_id = e.subject_user_id AND s.started_at > e.completed_at GROUP BY e.id ) SELECT e.public_id AS dsar_public_id, e.tenant_name, e.subject_user_id, CAST(e.completed_at AS date) AS completed_at, COALESCE(la.login_attempts_count, 0) AS login_attempts_after, COALESCE(la.succeeded_count, 0) AS succeeded_attempts_after, la.earliest_attempt AS earliest_login_attempt_after, la.latest_attempt AS latest_login_attempt_after, COALESCE(se.sessions_count, 0) AS sessions_after, se.latest_session_start AS latest_session_start_after FROM erasures e LEFT JOIN la ON la.erasure_id = e.id LEFT JOIN se ON se.erasure_id = e.id ORDER BY e.completed_at ASC, e.id ASC
Submitted to tonight's batch. Your work on this request is done.