=== Report 1: Dormant users holding Owner or Administrator access (written by Felix) === Engineer's note: Grain is one row per (user, role) since a user can hold both Owner and Administrator and role_name is a report column; days_since_last_login is left NULL for 'never' rather than a sentinel number, with never-logged-in users sorted first via last_login_at ASC NULLS FIRST (most stale). total_qualifying_rows is a window-count column showing how many rows matched in total, since more than 50 may qualify. REPORT: Dormant users holding Owner or Administrator access QUESTION: Specifically which accounts currently hold the two most powerful roles (Owner, Administrator) but haven't logged in recently — the concrete list of privileged accounts that look stale? tenant_name | tenant_status | role_name | user_email | user_status | last_login | days_since_last_login | account_age_days | total_qualifying_rows -------------------+---------------+---------------+-------------------------------------+-------------+----------------------------+-----------------------+------------------+---------------------- Paddock & Pine | churned | Administrator | maja.jensen@paddockandpine.dk | active | 2024-05-16 06:13:03.853485 | 599 | 678 | 16 Paddock & Pine | churned | Owner | carl.christensen@paddockandpine.dk | active | 2024-05-30 05:55:25.699322 | 585 | 743 | 16 Northwire Audio | churned | Owner | logan.schmid@northwireaudio.co.uk | active | 2024-10-04 08:15:43.724015 | 458 | 1024 | 16 Petal & Pestle | churned | Owner | amelia.nguyen@petalandpestle.com | active | 2024-10-09 07:25:29.989527 | 453 | 1040 | 16 Northwire Audio | churned | Administrator | zoe.berg@northwireaudio.co.uk | active | 2024-10-10 07:10:47.282732 | 452 | 1022 | 16 Petal & Pestle | churned | Administrator | emily.lewis@petalandpestle.com | active | 2024-10-11 06:04:30.128982 | 451 | 1008 | 16 Saltmarsh Knitwear | churned | Owner | julia.shah@saltmarshknitwear.nl | active | 2024-11-15 06:13:56.366752 | 416 | 1296 | 16 Saltmarsh Knitwear | churned | Administrator | ruben.goossens@saltmarshknitwear.nl | active | 2024-11-21 09:11:08.290205 | 410 | 1271 | 16 Aurum & Ash | churned | Administrator | ethan.watson@aurumandash.co.uk | active | 2024-12-20 10:45:46.808919 | 381 | 559 | 16 Aurum & Ash | churned | Owner | evelyn.miller@aurumandash.co.uk | active | 2024-12-26 09:20:33.907508 | 375 | 574 | 16 Klara Beauty | churned | Owner | julian.wozniak@klarabeauty.de | active | 2025-04-17 07:06:17.917437 | 263 | 503 | 16 Klara Beauty | churned | Administrator | julia.moser@klarabeauty.de | active | 2025-04-18 05:25:53.814361 | 262 | 413 | 16 Marginalia | churned | Administrator | iris.vandijk@marginalia.nl | active | 2025-06-13 06:55:24.418769 | 206 | 313 | 16 Marginalia | churned | Owner | bas.hendriks@marginalia.nl | active | 2025-06-19 06:26:42.339924 | 200 | 378 | 16 Halden Boots | churned | Administrator | ebba.jansson@haldenboots.se | active | 2025-09-22 06:10:12.824838 | 105 | 388 | 16 Halden Boots | churned | Owner | ida.lindqvist@haldenboots.se | active | 2025-10-03 08:05:13.116482 | 94 | 396 | 16 (16 rows) === Report 2: API key hygiene by merchant (written by Viktor) === Engineer's note: Grain is one row per tenant (grouped internally by t.id for correctness even though not printed). "Active keys unused 90+ days" excludes never-used keys (NULL last_used_at doesn't satisfy the < comparison), so the two abandonment buckets are mutually exclusive. Report prints up to 50 of the tenants that have at least one api_keys row; total row count is shown on the printed report per house standard. REPORT: API key hygiene by merchant QUESTION: How many API keys does each merchant have, how many are active, and how many of those active keys look abandoned (never used, or unused for a long time)? tenant_name | tenant_status | total_api_keys | active_api_keys | revoked_or_expired_keys | active_never_used | active_unused_90d | oldest_active_key_age_days | service_accounts_count ------------------------------+---------------+----------------+-----------------+-------------------------+-------------------+-------------------+----------------------------+----------------------- Ampere & Co. | active | 1 | 1 | 0 | 0 | 0 | 1964 | 0 Ampfeld Elektronik | active | 5 | 5 | 0 | 0 | 0 | 2252 | 4 Atelier Nouveau | active | 1 | 1 | 0 | 0 | 0 | 1936 | 1 Aurum & Ash | churned | 1 | 1 | 0 | 0 | 0 | 554 | 1 Ballycorran Industrial Supply | active | 3 | 3 | 0 | 0 | 0 | 2372 | 2 Basecamp Nord | active | 1 | 1 | 0 | 0 | 0 | 862 | 1 Bijoux Lune | active | 1 | 1 | 0 | 0 | 0 | 912 | 1 Brennquell Kaffee | active | 5 | 4 | 1 | 0 | 0 | 2155 | 4 Chocolaterie Mireille | active | 1 | 1 | 0 | 0 | 0 | 452 | 1 Cinder Peak Sports Co. | active | 4 | 4 | 0 | 0 | 0 | 1951 | 3 Coastline Interiors | active | 2 | 2 | 0 | 0 | 0 | 1786 | 1 Copper Lane | active | 1 | 1 | 0 | 0 | 0 | 92 | 1 Courtside Collective | active | 1 | 1 | 0 | 0 | 0 | 520 | 1 Cragsley Outfitters | active | 6 | 6 | 0 | 0 | 0 | 2469 | 4 Dewdrop Studio | churned | 1 | 1 | 0 | 0 | 0 | 456 | 1 Fell & Fjell | active | 1 | 0 | 1 | 0 | 0 | NULL | 0 Fennel & Flint | active | 4 | 4 | 0 | 0 | 0 | 2516 | 3 Fern & Fable | active | 1 | 1 | 0 | 0 | 0 | 2003 | 1 Fjordline Wear | active | 1 | 0 | 1 | 0 | 0 | NULL | 1 Folio & Fern | active | 1 | 1 | 0 | 0 | 0 | 1070 | 1 Glasshouse Grooming | active | 1 | 1 | 0 | 0 | 0 | 190 | 1 Gratlinie Outdoor | active | 3 | 3 | 0 | 0 | 0 | 1693 | 2 Grind Collective | past_due | 3 | 3 | 0 | 0 | 0 | 2743 | 2 Hanse Industriebedarf | active | 1 | 1 | 0 | 0 | 0 | 1056 | 1 Hautnah | active | 1 | 1 | 0 | 0 | 0 | 1346 | 1 Honeycomb Larder | active | 1 | 1 | 0 | 0 | 0 | 2388 | 0 Juniper Apothecary | active | 1 | 1 | 0 | 0 | 0 | 2630 | 0 Kaffeekontor Nord | active | 1 | 1 | 0 | 0 | 0 | 139 | 1 Kettle & Key | active | 1 | 1 | 0 | 0 | 0 | 2057 | 1 Kibble Crate | active | 1 | 0 | 1 | 0 | 0 | NULL | 1 Kibblewick | active | 4 | 4 | 0 | 0 | 0 | 699 | 3 Kontor24 Bürobedarf | active | 3 | 3 | 0 | 0 | 0 | 2106 | 2 Linden & Loom | active | 4 | 3 | 1 | 0 | 0 | 1897 | 3 Lumen Collective | active | 1 | 1 | 0 | 0 | 0 | 1156 | 1 Marrowby & Vane | active | 6 | 6 | 0 | 0 | 0 | 2666 | 4 Mirelle | active | 1 | 1 | 0 | 0 | 0 | 2026 | 1 Noordzee Surfwear | active | 1 | 1 | 0 | 0 | 0 | 1330 | 1 Nordic Bloom Cosmetics | active | 2 | 2 | 0 | 0 | 0 | 1263 | 1 Northstar Watches | active | 1 | 1 | 0 | 0 | 0 | 696 | 1 Oakhurst Tailoring | active | 1 | 1 | 0 | 0 | 0 | 1864 | 0 OfficeHub Nordic | active | 3 | 1 | 2 | 0 | 0 | 2275 | 2 Olive & Oak Pantry | active | 1 | 1 | 0 | 0 | 0 | 68 | 1 Ondine Botanicals | active | 1 | 1 | 0 | 0 | 0 | 591 | 1 Ostwind Mode | active | 1 | 1 | 0 | 0 | 0 | 2259 | 1 Papeterie Grossiste | active | 1 | 1 | 0 | 0 | 0 | 276 | 1 Pedal & Pine | active | 1 | 1 | 0 | 0 | 0 | 210 | 1 Petalcourt Beauty | active | 3 | 3 | 0 | 0 | 0 | 1763 | 2 Pixelhaus | active | 1 | 1 | 0 | 0 | 0 | 119 | 1 Purrfect Pantry | active | 1 | 1 | 0 | 0 | 0 | 1334 | 1 Rosehip Lab | active | 1 | 1 | 0 | 0 | 0 | 258 | 1 (50 rows) === Report 3: Audit log coverage by action type (written by Viktor) === Engineer's note: Tenant denominator is computed live as COUNT(*) FROM tenants rather than hardcoded 120, so the % column will self-correct if the actual tenant count differs; please flag if you want it pinned to exactly 120 instead. REPORT: Audit log coverage by action type QUESTION: Is the audit trail (audit_log) consistently capturing sensitive actions across all merchants and over time, or are some action types thin, recent-only, or concentrated in a handful of tenants — could we credibly reconstruct 'who did what, when' if asked? action_type | total_events | events_last_90_days | distinct_tenants | pct_of_tenants | distinct_actor_users | earliest_occurred_at | most_recent_occurred_at --------------+--------------+---------------------+------------------+----------------+----------------------+----------------------------+--------------------------- login | 55388 | 7573 | 120 | 100.0 | 547 | 2023-12-02 06:12:17.421046 | 2025-12-31 18:11:47.560027 update | 26334 | 1818 | 113 | 94.2 | 364 | 2023-12-02 03:00:48.488426 | 2025-12-31 21:58:38.049239 logout | 16788 | 2314 | 120 | 100.0 | 546 | 2023-12-02 12:05:27.458972 | 2025-12-31 22:19:26.215605 create | 13020 | 1420 | 120 | 100.0 | 147 | 2023-12-02 00:00:00 | 2025-12-31 16:50:59.022726 export | 2289 | 220 | 120 | 100.0 | 124 | 2023-12-02 08:05:05 | 2025-12-02 18:49:32 access_denied | 116 | 15 | 33 | 27.5 | 36 | 2023-12-08 09:17:38.51453 | 2025-12-30 08:03:58.36715 delete | 27 | 1 | 16 | 13.3 | 16 | 2024-01-05 12:34:16.549504 | 2025-11-10 15:18:24.435098 (7 rows)