=== Report 1: Consent purpose audit: full history and the analytics_cookies anomaly (written by Felix) === Engineer's note: Platform-wide, full history, no date filter, one row per distinct purpose value as requested; this will surface every purpose actually present in consent_records (not just analytics_cookies) and show withdrawn/expired patterns across all of them. REPORT: Consent purpose audit: full history and the analytics_cookies anomaly QUESTION: Across the full history, how many consent purposes do we actually track, and is the analytics_cookies 'always 100% opted in, zero withdrawals' pattern unique or does it appear elsewhere? purpose | total_records | distinct_users | granted_count | withdrawn_count | expired_count | pct_users_ever_withdrawn_or_expired | earliest_occurred_at | latest_occurred_at ------------------+---------------+----------------+---------------+-----------------+---------------+-------------------------------------+----------------------------+--------------------------- analytics_cookies | 7404 | 7404 | 7404 | 0 | 0 | 0.0 | 2019-01-19 10:05:46.410929 | 2025-12-31 22:53:16.158384 marketing_email | 4981 | 4550 | 4550 | 431 | 0 | 9.47 | 2019-01-12 22:13:48.933265 | 2025-12-31 22:53:16.158384 (2 rows) === Report 2: Post-erasure data footprint: what remains in sessions, logins, and access log (written by Bea) === Engineer's note: Grain is one row per completed erasure DSAR with subject_user_id populated (rows lacking a linked subject_user_id, if any, can't be matched to sessions/login_attempts/access_log and are excluded); joins to the three history tables are scoped by tenant_id + user_id for correctness in the multi-tenant schema. "Days since completed" is computed against the fixed reporting date 2026-01-05. REPORT: Post-erasure data footprint: what remains in sessions, logins, and access log QUESTION: After a data subject's erasure request is completed, what login/session and access-log history still remains, and for how long? dsar_public_id | tenant_name | subject_user_id | completed_at_date | days_since_completed | sessions_remaining | most_recent_session_started_at | login_attempts_remaining | most_recent_login_attempt_at | access_log_rows_remaining | most_recent_access_log_at -------------------------------------+---------------------+-----------------+-------------------+----------------------+--------------------+--------------------------------+--------------------------+------------------------------+---------------------------+-------------------------- 0bf5fe16-9c96-4a01-9861-ba58a8c62338 | Grind Collective | 345 | 2020-04-28 | 2078 | 0 | NULL | 0 | NULL | 0 | NULL 4531b08c-8444-4f57-aed1-451f6cbf1fed | Ampfeld Elektronik | 1218 | 2021-02-15 | 1785 | 0 | NULL | 0 | NULL | 0 | NULL ddc3341d-2a13-41d3-b194-d7f512b94132 | Marrowby & Vane | 407 | 2021-04-16 | 1725 | 0 | NULL | 4 | 2025-02-11 14:15:31 | 0 | NULL 74bec8f7-3b6e-4350-8535-caee73c055e1 | Marrowby & Vane | 363 | 2021-10-16 | 1542 | 0 | NULL | 2 | 2025-02-10 20:15:35 | 0 | NULL df4dc623-ddb6-4508-81af-ee136635850b | Cragsley Outfitters | 671 | 2022-02-05 | 1430 | 0 | NULL | 4 | 2025-02-10 23:48:15 | 0 | NULL cfddd972-efd4-4480-9ab3-12a867c46b79 | Coastline Interiors | 1769 | 2022-06-03 | 1312 | 0 | NULL | 0 | NULL | 0 | NULL f9ddeb61-8a65-4ffc-8a99-2d6746cab8ee | Wiesgrund Wohnen | 1735 | 2022-08-26 | 1228 | 0 | NULL | 0 | NULL | 0 | NULL 2554d690-68f1-4e5f-a5bd-aa9ecd805f5b | Midland Safety Wear | 1455 | 2022-09-02 | 1221 | 0 | NULL | 0 | NULL | 0 | NULL 3193004e-c781-41fc-9cd9-c2bfb9f01dbe | Studio Vrij | 1519 | 2023-05-15 | 966 | 1 | 2023-01-08 15:57:58.900303 | 2 | 2025-02-08 16:14:25 | 0 | NULL 861d7a09-3d30-4361-9726-2cef07e08814 | Gratlinie Outdoor | 2849 | 2023-09-01 | 857 | 2 | 2023-06-25 14:10:30.191763 | 2 | 2023-06-25 14:10:30.191763 | 0 | NULL e3271040-65dc-4131-a4a0-6d9f36d6980f | Ampfeld Elektronik | 1195 | 2023-10-26 | 802 | 1 | 2023-01-11 12:37:20.821303 | 1 | 2023-01-11 12:37:20.821303 | 0 | NULL 074e73eb-f889-4b53-a9c6-0fa4b5499b07 | Fennel & Flint | 2556 | 2023-11-22 | 775 | 8 | 2023-10-05 06:40:19.066438 | 8 | 2023-10-05 06:40:19.066438 | 0 | NULL 24d61e33-87e8-44e2-9bde-a63f6a1ddaf5 | Spice Route Box | 3607 | 2024-01-05 | 731 | 5 | 2023-12-14 09:24:47.995101 | 5 | 2023-12-14 09:24:47.995101 | 0 | NULL 806d5c26-623f-4692-a0a4-812bc1f5d0cc | Ampfeld Elektronik | 4197 | 2024-02-17 | 688 | 2 | 2024-02-08 13:24:13.15629 | 2 | 2024-02-08 13:24:13.15629 | 0 | NULL 7f3350f7-03dd-4fdd-a4f1-4c25e3dc35c1 | Marrowby & Vane | 2805 | 2024-02-28 | 677 | 5 | 2024-02-03 14:07:11.626644 | 8 | 2025-02-11 23:30:22 | 0 | NULL 1931ed93-6d67-494d-87b9-618f9047e0f7 | Fell & Fjell | 4617 | 2024-04-03 | 642 | 2 | 2024-02-13 07:19:09.236324 | 2 | 2024-02-13 07:19:09.236324 | 0 | NULL d89368e7-9f81-4832-8e7e-8da777ac3441 | Spice Route Box | 2591 | 2024-04-05 | 640 | 12 | 2024-02-11 18:49:14.759843 | 12 | 2024-02-11 18:49:14.759843 | 0 | NULL 691ec686-457b-4eba-966f-40194fab69fa | Sorella Skin | 5453 | 2024-05-16 | 599 | 1 | 2024-04-12 15:38:01.96489 | 1 | 2024-04-12 15:38:01.96489 | 0 | NULL a3194bf3-bb13-40d3-872d-b28febd38b01 | Ampfeld Elektronik | 4907 | 2024-05-28 | 587 | 4 | 2024-05-08 07:22:46.056385 | 4 | 2024-05-08 07:22:46.056385 | 0 | NULL 9158c9e4-8abc-42b2-9d00-0c14afced0a4 | Kayak Corner | 1792 | 2024-05-31 | 584 | 4 | 2023-03-14 19:14:51.674392 | 4 | 2023-03-14 19:14:51.674392 | 0 | NULL 99add429-adf4-44a4-b51b-e668f52d43aa | Coastline Interiors | 2532 | 2024-06-07 | 577 | 6 | 2023-08-17 12:44:37.742121 | 6 | 2023-08-17 12:44:37.742121 | 0 | NULL 442f76f1-6417-4666-821a-7bb927f1743a | Cragsley Outfitters | 829 | 2024-06-14 | 570 | 0 | NULL | 3 | 2025-02-10 08:31:50 | 0 | NULL a627a640-0eb3-45fe-80c8-fb9ab29758e0 | Little Larch Kids | 6196 | 2024-08-27 | 496 | 0 | NULL | 0 | NULL | 0 | NULL 49a0b5ef-09e3-4e24-8e7f-a481e2b109c5 | Petalcourt Beauty | 3366 | 2024-09-15 | 477 | 10 | 2024-04-17 18:42:45.82317 | 10 | 2024-04-17 18:42:45.82317 | 0 | NULL 4f961286-195f-4e5f-a7bd-6c251c3c3488 | Fennel & Flint | 3458 | 2024-10-17 | 445 | 6 | 2024-10-06 15:45:38.097555 | 6 | 2024-10-06 15:45:38.097555 | 0 | NULL 840a9b91-c6ce-4439-b3a4-4182c4c62a22 | Studio Vrij | 6300 | 2024-11-12 | 419 | 4 | 2024-10-27 15:36:16.821727 | 5 | 2025-02-09 21:07:56 | 0 | NULL 38e27076-1c76-43de-b5d7-c3228e334cd6 | Fennel & Flint | 6376 | 2024-11-14 | 417 | 3 | 2024-10-15 09:17:06.414486 | 3 | 2024-10-15 09:17:06.414486 | 0 | NULL a6d8af68-148a-46e1-9f4d-0c3c16f523b6 | Willow & Wren | 5765 | 2024-11-18 | 413 | 1 | 2024-05-21 13:34:16.950104 | 1 | 2024-05-21 13:34:16.950104 | 0 | NULL 6e8fb6f6-1c2f-4219-aad4-d62e5e4f39f6 | Bijoux Lune | 5736 | 2024-11-22 | 409 | 5 | 2024-10-02 13:24:27.329341 | 5 | 2024-10-02 13:24:27.329341 | 0 | NULL 642c8a31-9dda-44cc-b8b7-95c39e0c5bff | Fennel & Flint | 7406 | 2025-01-04 | 366 | 2 | 2024-12-11 17:11:17.756057 | 2 | 2024-12-11 17:11:17.756057 | 0 | NULL fc8c1dbc-10e7-468b-84d3-618415e5305a | Marrowby & Vane | 412 | 2025-01-10 | 360 | 19 | 2024-08-02 08:51:53.743388 | 21 | 2024-08-02 08:51:53.743388 | 0 | NULL f0676f94-b6b8-42de-8f06-84f200c42a0b | Cragsley Outfitters | 8111 | 2025-01-31 | 339 | 2 | 2025-01-22 15:31:53.891641 | 4 | 2025-02-09 15:04:21 | 0 | NULL cc524f30-5be6-48a5-96b6-870e6d379aea | Marrowby & Vane | 6617 | 2025-03-29 | 282 | 5 | 2024-12-26 14:58:33.513737 | 9 | 2025-02-11 12:06:05 | 0 | NULL e3a5ef8b-e2e0-41b2-953a-838812131698 | Kontor24 Bürobedarf | 8617 | 2025-04-29 | 251 | 4 | 2025-12-02 13:53:08.887219 | 4 | 2025-12-02 13:53:08.887219 | 0 | NULL 80a25dc8-3daa-43f2-b1ee-999985f0f6e5 | Cragsley Outfitters | 8596 | 2025-06-09 | 210 | 2 | 2025-05-20 02:39:41.459052 | 2 | 2025-05-20 02:39:41.459052 | 0 | NULL dc5213a1-e7c7-4882-853b-c8c29593e0da | Paperplane Press | 7512 | 2025-07-09 | 180 | 2 | 2025-01-01 20:06:14.295637 | 4 | 2025-01-01 20:06:14.295637 | 0 | NULL 7be56ee5-ad97-46ea-8777-b3ca09d38a57 | Coastline Interiors | 9683 | 2025-08-29 | 129 | 1 | 2025-07-16 19:42:34.176376 | 1 | 2025-07-16 19:42:34.176376 | 0 | NULL 571b0925-08eb-47d0-a322-4d916b11c31f | Marrowby & Vane | 10182 | 2025-09-29 | 98 | 1 | 2025-09-08 06:43:05.79837 | 1 | 2025-09-08 06:43:05.79837 | 0 | NULL e7a93251-9905-4243-bce4-d289a7b17144 | Marrowby & Vane | 6138 | 2025-11-10 | 56 | 9 | 2025-08-25 11:57:09.491376 | 11 | 2025-08-25 11:57:09.491376 | 0 | NULL (39 rows) === Report 3: Post-withdrawal marketing suppression check (written by Felix) === Engineer's note: Single aggregate row as requested: withdrawn-user population defined by each (tenant_id,user_id)'s latest marketing_email consent_records row being 'withdrawn' (platform-wide, full history through 2025-12-31). A send counts as post-withdrawal when COALESCE(sent_at, created_at) is strictly after the withdrawal's occurred_at; max-days and most-recent-send are computed only over users/sends that actually have a post-withdrawal match. REPORT: Post-withdrawal marketing suppression check QUESTION: When a shopper withdraws marketing_email consent, do we actually stop sending them marketing campaign emails, or does sending continue after withdrawal? withdrawn_users | contacted_after_withdrawal_users | pct_contacted_after_withdrawal | total_post_withdrawal_sends | max_days_withdrawal_to_send | most_recent_post_withdrawal_send ----------------+----------------------------------+--------------------------------+-----------------------------+-----------------------------+--------------------------------- 431 | 0 | 0.0 | 0 | NULL | NULL (1 rows)