Queries · q311 · Chief Information Security Officer · week 2, night 1
“Is the missing deletion audit trail we found for products a narrow, one-table problem, or does it extend across most of the tables that support soft-deletion?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
The manager, at the start of week 2
Subject: Re: Account security snapshot — good start, three gaps worry me more now
Noah,
This is a strong first week — thank you. The sign-in and access-governance findings give me confidence I can defend in a questionnaire. Three things genuinely surprised me and I want to understand them better before Friday's memo, plus one follow-up.
If we can only chase a couple of these deeply this week, I'd prioritize the deletion-audit scope (#1) and the churn-deactivation scope (#3) — those are the two a due-diligence team is most likely to ask us to prove, one way or the other.
Thanks, Henrik
The analyst · week 2, night 1, request 1 of 3
Henrik asked us to prioritize #1 (how wide is the deletion-audit gap) and #3 (how far does the churn-deactivation problem go) this week, so all 3 slots go there tonight. For #1, last week's 6-table sample (products at 0% coverage, users at 69%, three others with zero soft-deletes ever recorded) isn't enough to tell him whether this is "fix one thing" or "fix deletion logging everywhere" — so tonight I'm running a full census across every table that actually supports soft-deletion, not a hand-picked sample. I'm also addressing a gap in how I framed this last week: orders, invoices and other financial documents don't use deleted_at at all — they're "deleted" via a status change (cancelled/void), so testing audit coverage for the specific things Henrik named (orders, financial documents) needs a different query than soft-delete matching, which is report 2. For #3, last week I only checked role holders; tonight's report sizes the exposure across every access surface on churned tenants (user accounts, sessions, API keys, webhooks) so Henrik can judge whether this is a one-line fix (deactivate Owner/Admin) or a broader deprovisioning gap. I'm deliberately not touching #2 (shopper MFA compensating controls) or #4 (old-key sensitivity) tonight per his stated priority, and will pick those up once #1 and #3 are sized.
The engineer · 1 dry run on the empty schema, then the submission
WITH soft_deleted AS ( SELECT 'brands' AS entity_table, COUNT(*) AS total_soft_deleted, COUNT(DISTINCT tenant_id) AS tenant_count FROM brands WHERE deleted_at IS NOT NULL UNION ALL SELECT 'categories', COUNT(*), COUNT(DISTINCT tenant_id) FROM categories WHERE deleted_at IS NOT NULL UNION ALL SELECT 'products', COUNT(*), COUNT(DISTINCT tenant_id) FROM products WHERE deleted_at IS NOT NULL UNION ALL SELECT 'product_variants', COUNT(*), COUNT(DISTINCT tenant_id) FROM product_variants WHERE deleted_at IS NOT NULL UNION ALL SELECT 'collections', COUNT(*), COUNT(DISTINCT tenant_id) FROM collections WHERE deleted_at IS NOT NULL UNION ALL SELECT 'users', COUNT(*), COUNT(DISTINCT tenant_id) FROM users WHERE deleted_at IS NOT NULL UNION ALL SELECT 'billing_profiles', COUNT(*), COUNT(DISTINCT tenant_id) FROM billing_profiles WHERE deleted_at IS NOT NULL UNION ALL SELECT 'plans', COUNT(*), COUNT(DISTINCT tenant_id) FROM plans WHERE deleted_at IS NOT NULL UNION ALL SELECT 'price_lists', COUNT(*), COUNT(DISTINCT tenant_id) FROM price_lists WHERE deleted_at IS NOT NULL UNION ALL SELECT 'discounts', COUNT(*), COUNT(DISTINCT tenant_id) FROM discounts WHERE deleted_at IS NOT NULL UNION ALL SELECT 'coupons', COUNT(*), COUNT(DISTINCT tenant_id) FROM coupons WHERE deleted_at IS NOT NULL UNION ALL SELECT 'promotions', COUNT(*), COUNT(DISTINCT tenant_id) FROM promotions WHERE deleted_at IS NOT NULL UNION ALL SELECT 'campaigns', COUNT(*), COUNT(DISTINCT tenant_id) FROM campaigns WHERE deleted_at IS NOT NULL UNION ALL SELECT 'email_templates', COUNT(*), COUNT(DISTINCT tenant_id) FROM email_templates WHERE deleted_at IS NOT NULL UNION ALL SELECT 'segments', COUNT(*), COUNT(DISTINCT tenant_id) FROM segments WHERE deleted_at IS NOT NULL UNION ALL SELECT 'landing_pages', COUNT(*), COUNT(DISTINCT tenant_id) FROM landing_pages WHERE deleted_at IS NOT NULL UNION ALL SELECT 'referrals', COUNT(*), COUNT(DISTINCT tenant_id) FROM referrals WHERE deleted_at IS NOT NULL UNION ALL SELECT 'loyalty_programs', COUNT(*), COUNT(DISTINCT tenant_id) FROM loyalty_programs WHERE deleted_at IS NOT NULL UNION ALL SELECT 'ab_tests', COUNT(*), COUNT(DISTINCT tenant_id) FROM ab_tests WHERE deleted_at IS NOT NULL UNION ALL SELECT 'experiments', COUNT(*), COUNT(DISTINCT tenant_id) FROM experiments WHERE deleted_at IS NOT NULL UNION ALL SELECT 'cohorts', COUNT(*), COUNT(DISTINCT tenant_id) FROM cohorts WHERE deleted_at IS NOT NULL UNION ALL SELECT 'funnels', COUNT(*), COUNT(DISTINCT tenant_id) FROM funnels WHERE deleted_at IS NOT NULL UNION ALL SELECT 'contacts', COUNT(*), COUNT(DISTINCT tenant_id) FROM contacts WHERE deleted_at IS NOT NULL UNION ALL SELECT 'accounts', COUNT(*), COUNT(DISTINCT tenant_id) FROM accounts WHERE deleted_at IS NOT NULL UNION ALL SELECT 'knowledge_articles', COUNT(*), COUNT(DISTINCT tenant_id) FROM knowledge_articles WHERE deleted_at IS NOT NULL UNION ALL SELECT 'macros', COUNT(*), COUNT(DISTINCT tenant_id) FROM macros WHERE deleted_at IS NOT NULL UNION ALL SELECT 'files', COUNT(*), COUNT(DISTINCT tenant_id) FROM files WHERE deleted_at IS NOT NULL UNION ALL SELECT 'utm_links', COUNT(*), COUNT(DISTINCT tenant_id) FROM utm_links WHERE deleted_at IS NOT NULL UNION ALL SELECT 'suppliers', COUNT(*), COUNT(DISTINCT tenant_id) FROM suppliers WHERE deleted_at IS NOT NULL UNION ALL SELECT 'warehouses', COUNT(*), COUNT(DISTINCT tenant_id) FROM warehouses WHERE deleted_at IS NOT NULL ), audit_counts AS ( SELECT entity_table, COUNT(*) AS deleted_audit_rows FROM audit_log WHERE action = 'delete' GROUP BY entity_table ) SELECT sd.entity_table, sd.total_soft_deleted, COALESCE(ac.deleted_audit_rows, 0) AS matching_audit_rows, CASE WHEN sd.total_soft_deleted = 0 THEN NULL ELSE ROUND(100.0 * COALESCE(ac.deleted_audit_rows, 0) / sd.total_soft_deleted, 1) END AS coverage_rate_pct, sd.tenant_count AS tenants_with_soft_deleted FROM soft_deleted sd LEFT JOIN audit_counts ac ON ac.entity_table = sd.entity_table ORDER BY sd.total_soft_deleted DESC, sd.entity_table ASC
“Query compiles cleanly with the expected columns. Submitting.”
“Submitted. Summary of what the report will show:
- Grain: one row per soft-deletable entity table (30 rows — the request's list names 30 tables, slightly more than the "29" label, all verified to have deleted_at).
- Columns: entity table, total soft-deleted rows, matching audit_log delete rows, coverage rate %, distinct tenants with soft-deleted rows.
- Logic: per-table COUNT(*) WHERE deleted_at IS NOT NULL via UNION ALL, joined to audit_log counts filtered to action = 'delete' grouped by entity_table, coverage = matches/total rounded to 1 decimal (NULL when total is 0), ordered by volume descending so the CISO can immediately see which high-volume tables lack audit coverage like products did.
I flagged the 29→30 discrepancy in the submit note so Noah isn't surprised by the row count.”
As Noah Fischer saw it, from the small dataset the agents worked on
entity_table | total_soft_deleted | matching_audit_rows | coverage_rate_pct | tenants_with_soft_deleted -------------------+--------------------+---------------------+-------------------+-------------------------- products | 615 | 0 | 0.0 | 91 users | 39 | 27 | 69.2 | 20 ab_tests | 0 | 0 | NULL | 0 accounts | 0 | 0 | NULL | 0 billing_profiles | 0 | 0 | NULL | 0 brands | 0 | 0 | NULL | 0 campaigns | 0 | 0 | NULL | 0 categories | 0 | 0 | NULL | 0 cohorts | 0 | 0 | NULL | 0 collections | 0 | 0 | NULL | 0 contacts | 0 | 0 | NULL | 0 coupons | 0 | 0 | NULL | 0 discounts | 0 | 0 | NULL | 0 email_templates | 0 | 0 | NULL | 0 experiments | 0 | 0 | NULL | 0 files | 0 | 0 | NULL | 0 funnels | 0 | 0 | NULL | 0 knowledge_articles | 0 | 0 | NULL | 0 landing_pages | 0 | 0 | NULL | 0 loyalty_programs | 0 | 0 | NULL | 0 macros | 0 | 0 | NULL | 0 plans | 0 | 0 | NULL | 0 price_lists | 0 | 0 | NULL | 0 product_variants | 0 | 0 | NULL | 0 promotions | 0 | 0 | NULL | 0 referrals | 0 | 0 | NULL | 0 segments | 0 | 0 | NULL | 0 suppliers | 0 | 0 | NULL | 0 utm_links | 0 | 0 | NULL | 0 warehouses | 0 | 0 | NULL | 0 (30 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 soft_deleted AS ( SELECT 'brands' AS entity_table, COUNT(*) AS total_soft_deleted, COUNT(DISTINCT tenant_id) AS tenant_count FROM brands WHERE deleted_at IS NOT NULL UNION ALL SELECT 'categories', COUNT(*), COUNT(DISTINCT tenant_id) FROM categories WHERE deleted_at IS NOT NULL UNION ALL SELECT 'products', COUNT(*), COUNT(DISTINCT tenant_id) FROM products WHERE deleted_at IS NOT NULL UNION ALL SELECT 'product_variants', COUNT(*), COUNT(DISTINCT tenant_id) FROM product_variants WHERE deleted_at IS NOT NULL UNION ALL SELECT 'collections', COUNT(*), COUNT(DISTINCT tenant_id) FROM collections WHERE deleted_at IS NOT NULL UNION ALL SELECT 'users', COUNT(*), COUNT(DISTINCT tenant_id) FROM users WHERE deleted_at IS NOT NULL UNION ALL SELECT 'billing_profiles', COUNT(*), COUNT(DISTINCT tenant_id) FROM billing_profiles WHERE deleted_at IS NOT NULL UNION ALL SELECT 'plans', COUNT(*), COUNT(DISTINCT tenant_id) FROM plans WHERE deleted_at IS NOT NULL UNION ALL SELECT 'price_lists', COUNT(*), COUNT(DISTINCT tenant_id) FROM price_lists WHERE deleted_at IS NOT NULL UNION ALL SELECT 'discounts', COUNT(*), COUNT(DISTINCT tenant_id) FROM discounts WHERE deleted_at IS NOT NULL UNION ALL SELECT 'coupons', COUNT(*), COUNT(DISTINCT tenant_id) FROM coupons WHERE deleted_at IS NOT NULL UNION ALL SELECT 'promotions', COUNT(*), COUNT(DISTINCT tenant_id) FROM promotions WHERE deleted_at IS NOT NULL UNION ALL SELECT 'campaigns', COUNT(*), COUNT(DISTINCT tenant_id) FROM campaigns WHERE deleted_at IS NOT NULL UNION ALL SELECT 'email_templates', COUNT(*), COUNT(DISTINCT tenant_id) FROM email_templates WHERE deleted_at IS NOT NULL UNION ALL SELECT 'segments', COUNT(*), COUNT(DISTINCT tenant_id) FROM segments WHERE deleted_at IS NOT NULL UNION ALL SELECT 'landing_pages', COUNT(*), COUNT(DISTINCT tenant_id) FROM landing_pages WHERE deleted_at IS NOT NULL UNION ALL SELECT 'referrals', COUNT(*), COUNT(DISTINCT tenant_id) FROM referrals WHERE deleted_at IS NOT NULL UNION ALL SELECT 'loyalty_programs', COUNT(*), COUNT(DISTINCT tenant_id) FROM loyalty_programs WHERE deleted_at IS NOT NULL UNION ALL SELECT 'ab_tests', COUNT(*), COUNT(DISTINCT tenant_id) FROM ab_tests WHERE deleted_at IS NOT NULL UNION ALL SELECT 'experiments', COUNT(*), COUNT(DISTINCT tenant_id) FROM experiments WHERE deleted_at IS NOT NULL UNION ALL SELECT 'cohorts', COUNT(*), COUNT(DISTINCT tenant_id) FROM cohorts WHERE deleted_at IS NOT NULL UNION ALL SELECT 'funnels', COUNT(*), COUNT(DISTINCT tenant_id) FROM funnels WHERE deleted_at IS NOT NULL UNION ALL SELECT 'contacts', COUNT(*), COUNT(DISTINCT tenant_id) FROM contacts WHERE deleted_at IS NOT NULL UNION ALL SELECT 'accounts', COUNT(*), COUNT(DISTINCT tenant_id) FROM accounts WHERE deleted_at IS NOT NULL UNION ALL SELECT 'knowledge_articles', COUNT(*), COUNT(DISTINCT tenant_id) FROM knowledge_articles WHERE deleted_at IS NOT NULL UNION ALL SELECT 'macros', COUNT(*), COUNT(DISTINCT tenant_id) FROM macros WHERE deleted_at IS NOT NULL UNION ALL SELECT 'files', COUNT(*), COUNT(DISTINCT tenant_id) FROM files WHERE deleted_at IS NOT NULL UNION ALL SELECT 'utm_links', COUNT(*), COUNT(DISTINCT tenant_id) FROM utm_links WHERE deleted_at IS NOT NULL UNION ALL SELECT 'suppliers', COUNT(*), COUNT(DISTINCT tenant_id) FROM suppliers WHERE deleted_at IS NOT NULL UNION ALL SELECT 'warehouses', COUNT(*), COUNT(DISTINCT tenant_id) FROM warehouses WHERE deleted_at IS NOT NULL),audit_counts AS ( SELECT entity_table, COUNT(*) AS deleted_audit_rows FROM audit_log WHERE action = 'delete' GROUP BY entity_table)SELECT sd.entity_table, sd.total_soft_deleted, sd.entity_table AS entity_table, sd.total_soft_deleted AS total_soft_deleted, COALESCE(ac.deleted_audit_rows, 0) AS matching_audit_rows, CASE WHEN sd.total_soft_deleted = 0 THEN NULL ELSE ROUND(100.0 * COALESCE(ac.deleted_audit_rows, 0) / sd.total_soft_deleted, 1) ELSE 100.0 * COALESCE(ac.deleted_audit_rows, 0) / sd.total_soft_deleted END AS coverage_rate_pct, sd.tenant_count AS tenants_with_soft_deletedFROM soft_deleted sdLEFT JOIN audit_counts ac ON ac.entity_table = sd.entity_tableORDER BY sd.total_soft_deleted DESC, sd.entity_table ASCORDER BY sd.total_soft_deleted DESC NULLS FIRST, sd.entity_table ASC NULLS LAST
WITH soft_deleted AS ( SELECT 'brands' AS entity_table, COUNT(*) AS total_soft_deleted, COUNT(DISTINCT tenant_id) AS tenant_count FROM brands WHERE deleted_at IS NOT NULL UNION ALL SELECT 'categories', COUNT(*), COUNT(DISTINCT tenant_id) FROM categories WHERE deleted_at IS NOT NULL UNION ALL SELECT 'products', COUNT(*), COUNT(DISTINCT tenant_id) FROM products WHERE deleted_at IS NOT NULL UNION ALL SELECT 'product_variants', COUNT(*), COUNT(DISTINCT tenant_id) FROM product_variants WHERE deleted_at IS NOT NULL UNION ALL SELECT 'collections', COUNT(*), COUNT(DISTINCT tenant_id) FROM collections WHERE deleted_at IS NOT NULL UNION ALL SELECT 'users', COUNT(*), COUNT(DISTINCT tenant_id) FROM users WHERE deleted_at IS NOT NULL UNION ALL SELECT 'billing_profiles', COUNT(*), COUNT(DISTINCT tenant_id) FROM billing_profiles WHERE deleted_at IS NOT NULL UNION ALL SELECT 'plans', COUNT(*), COUNT(DISTINCT tenant_id) FROM plans WHERE deleted_at IS NOT NULL UNION ALL SELECT 'price_lists', COUNT(*), COUNT(DISTINCT tenant_id) FROM price_lists WHERE deleted_at IS NOT NULL UNION ALL SELECT 'discounts', COUNT(*), COUNT(DISTINCT tenant_id) FROM discounts WHERE deleted_at IS NOT NULL UNION ALL SELECT 'coupons', COUNT(*), COUNT(DISTINCT tenant_id) FROM coupons WHERE deleted_at IS NOT NULL UNION ALL SELECT 'promotions', COUNT(*), COUNT(DISTINCT tenant_id) FROM promotions WHERE deleted_at IS NOT NULL UNION ALL SELECT 'campaigns', COUNT(*), COUNT(DISTINCT tenant_id) FROM campaigns WHERE deleted_at IS NOT NULL UNION ALL SELECT 'email_templates', COUNT(*), COUNT(DISTINCT tenant_id) FROM email_templates WHERE deleted_at IS NOT NULL UNION ALL SELECT 'segments', COUNT(*), COUNT(DISTINCT tenant_id) FROM segments WHERE deleted_at IS NOT NULL UNION ALL SELECT 'landing_pages', COUNT(*), COUNT(DISTINCT tenant_id) FROM landing_pages WHERE deleted_at IS NOT NULL UNION ALL SELECT 'referrals', COUNT(*), COUNT(DISTINCT tenant_id) FROM referrals WHERE deleted_at IS NOT NULL UNION ALL SELECT 'loyalty_programs', COUNT(*), COUNT(DISTINCT tenant_id) FROM loyalty_programs WHERE deleted_at IS NOT NULL UNION ALL SELECT 'ab_tests', COUNT(*), COUNT(DISTINCT tenant_id) FROM ab_tests WHERE deleted_at IS NOT NULL UNION ALL SELECT 'experiments', COUNT(*), COUNT(DISTINCT tenant_id) FROM experiments WHERE deleted_at IS NOT NULL UNION ALL SELECT 'cohorts', COUNT(*), COUNT(DISTINCT tenant_id) FROM cohorts WHERE deleted_at IS NOT NULL UNION ALL SELECT 'funnels', COUNT(*), COUNT(DISTINCT tenant_id) FROM funnels WHERE deleted_at IS NOT NULL UNION ALL SELECT 'contacts', COUNT(*), COUNT(DISTINCT tenant_id) FROM contacts WHERE deleted_at IS NOT NULL UNION ALL SELECT 'accounts', COUNT(*), COUNT(DISTINCT tenant_id) FROM accounts WHERE deleted_at IS NOT NULL UNION ALL SELECT 'knowledge_articles', COUNT(*), COUNT(DISTINCT tenant_id) FROM knowledge_articles WHERE deleted_at IS NOT NULL UNION ALL SELECT 'macros', COUNT(*), COUNT(DISTINCT tenant_id) FROM macros WHERE deleted_at IS NOT NULL UNION ALL SELECT 'files', COUNT(*), COUNT(DISTINCT tenant_id) FROM files WHERE deleted_at IS NOT NULL UNION ALL SELECT 'utm_links', COUNT(*), COUNT(DISTINCT tenant_id) FROM utm_links WHERE deleted_at IS NOT NULL UNION ALL SELECT 'suppliers', COUNT(*), COUNT(DISTINCT tenant_id) FROM suppliers WHERE deleted_at IS NOT NULL UNION ALL SELECT 'warehouses', COUNT(*), COUNT(DISTINCT tenant_id) FROM warehouses WHERE deleted_at IS NOT NULL ), audit_counts AS ( SELECT entity_table, COUNT(*) AS deleted_audit_rows FROM audit_log WHERE action = 'delete' GROUP BY entity_table ) SELECT sd.entity_table AS entity_table, sd.total_soft_deleted AS total_soft_deleted, COALESCE(ac.deleted_audit_rows, 0) AS matching_audit_rows, CASE WHEN sd.total_soft_deleted = 0 THEN NULL ELSE 100.0 * COALESCE(ac.deleted_audit_rows, 0) / sd.total_soft_deleted END AS coverage_rate_pct, sd.tenant_count AS tenants_with_soft_deleted FROM soft_deleted sd LEFT JOIN audit_counts ac ON ac.entity_table = sd.entity_table ORDER BY sd.total_soft_deleted DESC NULLS FIRST, sd.entity_table 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 Noah Fischer (analyst for the Chief Information Security Officer): Title: Full census of audit-trail deletion coverage, all soft-deletable tables Question: Is the missing deletion audit trail we found for products a narrow, one-table problem, or does it extend across most of the tables that support soft-deletion? Grain: one row per entity table, covering every production table with a deleted_at column Columns: - entity table - total soft-deleted rows (deleted_at is not null, all history 2023-01-01 to 2025-12-31) - matching audit_log rows (action = 'delete' and entity_table = this table name, all history) - coverage rate (%) - distinct tenants with at least one soft-deleted row Details: Source: UNION ALL of per-table counts of rows with deleted_at is not null, for every table in the catalog that has a deleted_at column: brands, categories, products, product_variants, collections, users, billing_profiles, plans, price_lists, discounts, coupons, promotions, campaigns, email_templates, segments, landing_pages, referrals, loyalty_programs, ab_tests, experiments, cohorts, funnels, contacts, accounts, knowledge_articles, macros, files, utm_links, suppliers, warehouses (29 tables). For each, separately count audit_log rows with action = 'delete' and entity_table equal to that table's literal name (ignore entity_schema). coverage_rate = matching / total_soft_deleted, rounded to 1 decimal percent, NULL if total_soft_deleted = 0. Order by total_soft_deleted descending (so tables with real volume are visible first), entity table name as tie-break. Expect ~29 rows, under the 50-row cap. Check your query with the warehouse's dry_run tool, then hand it in with submit.
dry_runWITH soft_deleted AS ( SELECT 'brands' AS entity_table, COUNT(*) AS total_soft_deleted, COUNT(DISTINCT tenant_id) AS tenant_count FROM brands WHERE deleted_at IS NOT NULL UNION ALL SELECT 'categories', COUNT(*), COUNT(DISTINCT tenant_id) FROM categories WHERE deleted_at IS NOT NULL UNION ALL SELECT 'products', COUNT(*), COUNT(DISTINCT tenant_id) FROM products WHERE deleted_at IS NOT NULL UNION ALL SELECT 'product_variants', COUNT(*), COUNT(DISTINCT tenant_id) FROM product_variants WHERE deleted_at IS NOT NULL UNION ALL SELECT 'collections', COUNT(*), COUNT(DISTINCT tenant_id) FROM collections WHERE deleted_at IS NOT NULL UNION ALL SELECT 'users', COUNT(*), COUNT(DISTINCT tenant_id) FROM users WHERE deleted_at IS NOT NULL UNION ALL SELECT 'billing_profiles', COUNT(*), COUNT(DISTINCT tenant_id) FROM billing_profiles WHERE deleted_at IS NOT NULL UNION ALL SELECT 'plans', COUNT(*), COUNT(DISTINCT tenant_id) FROM plans WHERE deleted_at IS NOT NULL UNION ALL SELECT 'price_lists', COUNT(*), COUNT(DISTINCT tenant_id) FROM price_lists WHERE deleted_at IS NOT NULL UNION ALL SELECT 'discounts', COUNT(*), COUNT(DISTINCT tenant_id) FROM discounts WHERE deleted_at IS NOT NULL UNION ALL SELECT 'coupons', COUNT(*), COUNT(DISTINCT tenant_id) FROM coupons WHERE deleted_at IS NOT NULL UNION ALL SELECT 'promotions', COUNT(*), COUNT(DISTINCT tenant_id) FROM promotions WHERE deleted_at IS NOT NULL UNION ALL SELECT 'campaigns', COUNT(*), COUNT(DISTINCT tenant_id) FROM campaigns WHERE deleted_at IS NOT NULL UNION ALL SELECT 'email_templates', COUNT(*), COUNT(DISTINCT tenant_id) FROM email_templates WHERE deleted_at IS NOT NULL UNION ALL SELECT 'segments', COUNT(*), COUNT(DISTINCT tenant_id) FROM segments WHERE deleted_at IS NOT NULL UNION ALL SELECT 'landing_pages', COUNT(*), COUNT(DISTINCT tenant_id) FROM landing_pages WHERE deleted_at IS NOT NULL UNION ALL SELECT 'referrals', COUNT(*), COUNT(DISTINCT tenant_id) FROM referrals WHERE deleted_at IS NOT NULL UNION ALL SELECT 'loyalty_programs', COUNT(*), COUNT(DISTINCT tenant_id) FROM loyalty_programs WHERE deleted_at IS NOT NULL UNION ALL SELECT 'ab_tests', COUNT(*), COUNT(DISTINCT tenant_id) FROM ab_tests WHERE deleted_at IS NOT NULL UNION ALL SELECT 'experiments', COUNT(*), COUNT(DISTINCT tenant_id) FROM experiments WHERE deleted_at IS NOT NULL UNION ALL SELECT 'cohorts', COUNT(*), COUNT(DISTINCT tenant_id) FROM cohorts WHERE deleted_at IS NOT NULL UNION ALL SELECT 'funnels', COUNT(*), COUNT(DISTINCT tenant_id) FROM funnels WHERE deleted_at IS NOT NULL UNION ALL SELECT 'contacts', COUNT(*), COUNT(DISTINCT tenant_id) FROM contacts WHERE deleted_at IS NOT NULL UNION ALL SELECT 'accounts', COUNT(*), COUNT(DISTINCT tenant_id) FROM accounts WHERE deleted_at IS NOT NULL UNION ALL SELECT 'knowledge_articles', COUNT(*), COUNT(DISTINCT tenant_id) FROM knowledge_articles WHERE deleted_at IS NOT NULL UNION ALL SELECT 'macros', COUNT(*), COUNT(DISTINCT tenant_id) FROM macros WHERE deleted_at IS NOT NULL UNION ALL SELECT 'files', COUNT(*), COUNT(DISTINCT tenant_id) FROM files WHERE deleted_at IS NOT NULL UNION ALL SELECT 'utm_links', COUNT(*), COUNT(DISTINCT tenant_id) FROM utm_links WHERE deleted_at IS NOT NULL UNION ALL SELECT 'suppliers', COUNT(*), COUNT(DISTINCT tenant_id) FROM suppliers WHERE deleted_at IS NOT NULL UNION ALL SELECT 'warehouses', COUNT(*), COUNT(DISTINCT tenant_id) FROM warehouses WHERE deleted_at IS NOT NULL ), audit_counts AS ( SELECT entity_table, COUNT(*) AS deleted_audit_rows FROM audit_log WHERE action = 'delete' GROUP BY entity_table ) SELECT sd.entity_table, sd.total_soft_deleted, COALESCE(ac.deleted_audit_rows, 0) AS matching_audit_rows, CASE WHEN sd.total_soft_deleted = 0 THEN NULL ELSE ROUND(100.0 * COALESCE(ac.deleted_audit_rows, 0) / sd.total_soft_deleted, 1) END AS coverage_rate_pct, sd.tenant_count AS tenants_with_soft_deleted FROM soft_deleted sd LEFT JOIN audit_counts ac ON ac.entity_table = sd.entity_table ORDER BY sd.total_soft_deleted DESC, sd.entity_table ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: entity_table, total_soft_deleted, matching_audit_rows, coverage_rate_pct, tenants_with_soft_deleted
submitWITH soft_deleted AS ( SELECT 'brands' AS entity_table, COUNT(*) AS total_soft_deleted, COUNT(DISTINCT tenant_id) AS tenant_count FROM brands WHERE deleted_at IS NOT NULL UNION ALL SELECT 'categories', COUNT(*), COUNT(DISTINCT tenant_id) FROM categories WHERE deleted_at IS NOT NULL UNION ALL SELECT 'products', COUNT(*), COUNT(DISTINCT tenant_id) FROM products WHERE deleted_at IS NOT NULL UNION ALL SELECT 'product_variants', COUNT(*), COUNT(DISTINCT tenant_id) FROM product_variants WHERE deleted_at IS NOT NULL UNION ALL SELECT 'collections', COUNT(*), COUNT(DISTINCT tenant_id) FROM collections WHERE deleted_at IS NOT NULL UNION ALL SELECT 'users', COUNT(*), COUNT(DISTINCT tenant_id) FROM users WHERE deleted_at IS NOT NULL UNION ALL SELECT 'billing_profiles', COUNT(*), COUNT(DISTINCT tenant_id) FROM billing_profiles WHERE deleted_at IS NOT NULL UNION ALL SELECT 'plans', COUNT(*), COUNT(DISTINCT tenant_id) FROM plans WHERE deleted_at IS NOT NULL UNION ALL SELECT 'price_lists', COUNT(*), COUNT(DISTINCT tenant_id) FROM price_lists WHERE deleted_at IS NOT NULL UNION ALL SELECT 'discounts', COUNT(*), COUNT(DISTINCT tenant_id) FROM discounts WHERE deleted_at IS NOT NULL UNION ALL SELECT 'coupons', COUNT(*), COUNT(DISTINCT tenant_id) FROM coupons WHERE deleted_at IS NOT NULL UNION ALL SELECT 'promotions', COUNT(*), COUNT(DISTINCT tenant_id) FROM promotions WHERE deleted_at IS NOT NULL UNION ALL SELECT 'campaigns', COUNT(*), COUNT(DISTINCT tenant_id) FROM campaigns WHERE deleted_at IS NOT NULL UNION ALL SELECT 'email_templates', COUNT(*), COUNT(DISTINCT tenant_id) FROM email_templates WHERE deleted_at IS NOT NULL UNION ALL SELECT 'segments', COUNT(*), COUNT(DISTINCT tenant_id) FROM segments WHERE deleted_at IS NOT NULL UNION ALL SELECT 'landing_pages', COUNT(*), COUNT(DISTINCT tenant_id) FROM landing_pages WHERE deleted_at IS NOT NULL UNION ALL SELECT 'referrals', COUNT(*), COUNT(DISTINCT tenant_id) FROM referrals WHERE deleted_at IS NOT NULL UNION ALL SELECT 'loyalty_programs', COUNT(*), COUNT(DISTINCT tenant_id) FROM loyalty_programs WHERE deleted_at IS NOT NULL UNION ALL SELECT 'ab_tests', COUNT(*), COUNT(DISTINCT tenant_id) FROM ab_tests WHERE deleted_at IS NOT NULL UNION ALL SELECT 'experiments', COUNT(*), COUNT(DISTINCT tenant_id) FROM experiments WHERE deleted_at IS NOT NULL UNION ALL SELECT 'cohorts', COUNT(*), COUNT(DISTINCT tenant_id) FROM cohorts WHERE deleted_at IS NOT NULL UNION ALL SELECT 'funnels', COUNT(*), COUNT(DISTINCT tenant_id) FROM funnels WHERE deleted_at IS NOT NULL UNION ALL SELECT 'contacts', COUNT(*), COUNT(DISTINCT tenant_id) FROM contacts WHERE deleted_at IS NOT NULL UNION ALL SELECT 'accounts', COUNT(*), COUNT(DISTINCT tenant_id) FROM accounts WHERE deleted_at IS NOT NULL UNION ALL SELECT 'knowledge_articles', COUNT(*), COUNT(DISTINCT tenant_id) FROM knowledge_articles WHERE deleted_at IS NOT NULL UNION ALL SELECT 'macros', COUNT(*), COUNT(DISTINCT tenant_id) FROM macros WHERE deleted_at IS NOT NULL UNION ALL SELECT 'files', COUNT(*), COUNT(DISTINCT tenant_id) FROM files WHERE deleted_at IS NOT NULL UNION ALL SELECT 'utm_links', COUNT(*), COUNT(DISTINCT tenant_id) FROM utm_links WHERE deleted_at IS NOT NULL UNION ALL SELECT 'suppliers', COUNT(*), COUNT(DISTINCT tenant_id) FROM suppliers WHERE deleted_at IS NOT NULL UNION ALL SELECT 'warehouses', COUNT(*), COUNT(DISTINCT tenant_id) FROM warehouses WHERE deleted_at IS NOT NULL ), audit_counts AS ( SELECT entity_table, COUNT(*) AS deleted_audit_rows FROM audit_log WHERE action = 'delete' GROUP BY entity_table ) SELECT sd.entity_table, sd.total_soft_deleted, COALESCE(ac.deleted_audit_rows, 0) AS matching_audit_rows, CASE WHEN sd.total_soft_deleted = 0 THEN NULL ELSE ROUND(100.0 * COALESCE(ac.deleted_audit_rows, 0) / sd.total_soft_deleted, 1) END AS coverage_rate_pct, sd.tenant_count AS tenants_with_soft_deleted FROM soft_deleted sd LEFT JOIN audit_counts ac ON ac.entity_table = sd.entity_table ORDER BY sd.total_soft_deleted DESC, sd.entity_table ASC
Submitted to tonight's batch. Your work on this request is done.