Queries · q338 · VP Platform Engineering · week 2, night 2
“For the 4 tenants that account for ~91% of all stuck sync-webhook deliveries (Brennquell Kaffee, Cragsley Outfitters, Ampfeld Elektronik, Marrowby & Vane), has their stuck rate been stable all year or is it new/worsening?”
Fictional people at a fictional company, each played by a language-model agent. Who they are and what they are told
Week 1; shared with Samuel Whitcombe, Head of Merchant Success
Leadership wants a review of platform reliability in 2025: failed and slow background jobs, health check failures, rate limiting, webhook delivery problems, and which merchants felt them.
The manager, at the start of week 2
Subject: Re: 2025 Platform Reliability Review — good start, a few things I need tightened up
Lucas,
Thanks — this is useful and more honest than I expected on the limits of what we can see. A few things stood out and need more work before Friday's memo goes to leadership.
First, the retention gap is itself a finding, not just a caveat. We're flying with 90 days of job history and 60 days of health-check history — that means we genuinely don't know how 2025 looked before October. I want that stated plainly and separately in the memo as a reliability/observability gap in its own right, not buried as a footnote under the carts.remind story.
Second, the ERP/WMS/Accounting webhook stuck-retry pattern is, to me, the most important and actionable thing in here — more than the one-week carts.remind incident. I want that properly validated and sized, not eyeballed, before we say anything about it to leadership.
Questions for this week:
On rate limiting: agreed, drop it for this cycle — don't spend a slot trying to force an answer out of config data alone. Just flag it as a 2026 instrumentation ask, as you already have.
Priority for your remaining slots: #1 and #2 above. The webhook/sync issue is the one I can take into a fix-it conversation with engineering next week if the numbers hold up — I'd rather have that nailed down than more polish on the carts.remind narrative.
Diego
The analyst · week 2, night 2, request 1 of 3
Night 1 results reshape the story in an important way. Report 1 shows the stuck-retry pattern touches 56 tenants (not a handful) at wildly different % rates, but many of the worst percentages (e.g. 50%, 38%) are tiny-volume tenants (1-2 stuck deliveries) - a small-sample artifact, not a real signal. When I compute absolute stuck-delivery counts (rate x volume) by hand from that report, ~91% of all stuck deliveries sit with just 4 tenants: Brennquell Kaffee (~1,574 stuck), Cragsley Outfitters (~700), Ampfeld Elektronik (~161), and Marrowby & Vane (~88) - and these are the same 4 of our 10 carts.remind-affected tenants with real order volume. That's the concentration story Diego wants, and I don't need another slot to prove it, it's derivable from what we have. Report 2 shows the platform-wide stuck rate has been flat at ~5.2-6.6% every month all year (not the ~15% I quoted last week, which was a selection-biased view of only the worst 20 endpoints) - so no recent regression at the platform level. Report 3 is a clean, high-confidence answer on carts.remind: all 20 failures are the identical error ('503 Service Unavailable: storefront API') in two one-hour-apart batches on Nov 28, across the same 10 tenants both times - this reads as a brief outage of one shared upstream dependency, not a tenant-specific or load issue, and not really a 'config/feature flag' story as I speculated last week. Tonight I'll: (1) check whether the stuck rate for those same 4 high-impact tenants has itself been stable all year or is recent/worsening (refines Diego's #2 to the account level that matters for the fix-it conversation); (2) pull internal health checks around the Nov 28 14:00-16:00 window to see if monitoring caught the storefront-API blip in real time (closes out #3 with hard evidence); (3) do a first lightweight look at ticket subject lines from the 10 affected tenants in the weeks before Nov 24 to give Diego his requested 'rough read' on whether support was an early warning signal (#4).
The engineer · 2 dry runs on the empty schema, then the submission
SELECT t.name AS tenant_name, to_char(date_trunc('month', wd.created_at), 'YYYY-MM') AS month, COUNT(*) AS total_sync_deliveries, ROUND(100.0 * COUNT(*) FILTER (WHERE wd.status IN ('pending','retrying')) / COUNT(*), 2) AS pending_or_retrying_rate_pct FROM webhook_deliveries wd JOIN webhooks w ON w.id = wd.webhook_id JOIN tenants t ON t.id = wd.tenant_id WHERE t.name IN ('Brennquell Kaffee','Cragsley Outfitters','Ampfeld Elektronik','Marrowby & Vane') AND wd.created_at >= DATE '2025-01-01' AND wd.created_at < DATE '2026-01-01' AND (w.name ILIKE '%ERP%sync%' OR w.name ILIKE '%WMS%sync%' OR w.name ILIKE '%ACCOUNTING%sync%') GROUP BY t.name, date_trunc('month', wd.created_at) ORDER BY t.name ASC, date_trunc('month', wd.created_at) ASC
SELECT t.name AS tenant_name, to_char(date_trunc('month', wd.created_at), 'YYYY-MM') AS month, CAST(date_trunc('month', wd.created_at) AS date) AS month, COUNT(*) AS total_sync_deliveries, ROUND(100.0 * COUNT(*) FILTER (WHERE wd.status IN ('pending','retrying')) / COUNT(*), 2) AS pending_or_retrying_rate_pctFROM webhook_deliveries wdJOIN webhooks w ON w.id = wd.webhook_idJOIN tenants t ON t.id = wd.tenant_idWHERE t.name IN ('Brennquell Kaffee','Cragsley Outfitters','Ampfeld Elektronik','Marrowby & Vane') AND wd.created_at >= DATE '2025-01-01' AND wd.created_at < DATE '2026-01-01' AND (w.name ILIKE '%ERP%sync%' OR w.name ILIKE '%WMS%sync%' OR w.name ILIKE '%ACCOUNTING%sync%')GROUP BY t.name, date_trunc('month', wd.created_at)ORDER BY t.name ASC, date_trunc('month', wd.created_at) ASC
“Query compiles cleanly with the expected columns. Submitting.”
“Submitted. The report covers the 4 named tenants, one row per tenant-month for 2025, with total sync-webhook deliveries and the pending/retrying ("stuck") rate rounded to 2 decimal places, ordered by tenant then month — ready to show whether the stuck rate is stable, new, or worsening across the year.”
As Lucas Moreau saw it, from the small dataset the agents worked on
tenant_name | month | total_sync_deliveries | pending_or_retrying_rate_pct --------------------+------------+-----------------------+----------------------------- Ampfeld Elektronik | 2025-01-01 | 185 | 11.35 Ampfeld Elektronik | 2025-02-01 | 97 | 11.34 Ampfeld Elektronik | 2025-03-01 | 60 | 3.33 Ampfeld Elektronik | 2025-04-01 | 80 | 10.0 Ampfeld Elektronik | 2025-05-01 | 106 | 11.32 Ampfeld Elektronik | 2025-06-01 | 87 | 4.6 Ampfeld Elektronik | 2025-07-01 | 98 | 10.2 Ampfeld Elektronik | 2025-08-01 | 78 | 7.69 Ampfeld Elektronik | 2025-09-01 | 117 | 17.09 Ampfeld Elektronik | 2025-10-01 | 106 | 7.55 Ampfeld Elektronik | 2025-11-01 | 235 | 11.06 Ampfeld Elektronik | 2025-12-01 | 334 | 9.88 Brennquell Kaffee | 2025-01-01 | 1309 | 11.69 Brennquell Kaffee | 2025-02-01 | 1086 | 10.41 Brennquell Kaffee | 2025-03-01 | 1275 | 13.73 Brennquell Kaffee | 2025-04-01 | 1139 | 11.41 Brennquell Kaffee | 2025-05-01 | 1104 | 12.5 Brennquell Kaffee | 2025-06-01 | 1032 | 10.27 Brennquell Kaffee | 2025-07-01 | 1090 | 10.64 Brennquell Kaffee | 2025-08-01 | 1068 | 9.93 Brennquell Kaffee | 2025-09-01 | 1139 | 10.97 Brennquell Kaffee | 2025-10-01 | 1175 | 9.7 Brennquell Kaffee | 2025-11-01 | 1186 | 11.72 Brennquell Kaffee | 2025-12-01 | 1546 | 10.22 Cragsley Outfitters | 2025-01-01 | 1177 | 3.91 Cragsley Outfitters | 2025-02-01 | 705 | 5.11 Cragsley Outfitters | 2025-03-01 | 858 | 4.78 Cragsley Outfitters | 2025-04-01 | 900 | 4.56 Cragsley Outfitters | 2025-05-01 | 1045 | 5.17 Cragsley Outfitters | 2025-06-01 | 942 | 4.14 Cragsley Outfitters | 2025-07-01 | 1088 | 5.06 Cragsley Outfitters | 2025-08-01 | 816 | 4.9 Cragsley Outfitters | 2025-09-01 | 999 | 4.8 Cragsley Outfitters | 2025-10-01 | 1171 | 5.12 Cragsley Outfitters | 2025-11-01 | 1861 | 6.5 Cragsley Outfitters | 2025-12-01 | 2542 | 4.68 Marrowby & Vane | 2025-01-01 | 708 | 1.27 Marrowby & Vane | 2025-02-01 | 362 | 0.83 Marrowby & Vane | 2025-03-01 | 433 | 0.23 Marrowby & Vane | 2025-04-01 | 389 | 1.29 Marrowby & Vane | 2025-05-01 | 430 | 0.93 Marrowby & Vane | 2025-06-01 | 396 | 2.02 Marrowby & Vane | 2025-07-01 | 450 | 0.89 Marrowby & Vane | 2025-08-01 | 386 | 1.3 Marrowby & Vane | 2025-09-01 | 468 | 0.21 Marrowby & Vane | 2025-10-01 | 507 | 1.58 Marrowby & Vane | 2025-11-01 | 823 | 4.37 Marrowby & Vane | 2025-12-01 | 1073 | 0.37 (48 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
SELECT t.name AS tenant_name, CAST(date_trunc('month', wd.created_at) AS date) AS month, COUNT(*) AS total_sync_deliveries, ROUND(100.0 * COUNT(*) FILTER (WHERE wd.status IN ('pending','retrying')) / COUNT(*), 2) AS pending_or_retrying_rate_pct 100.0 * COUNT(*) FILTER (WHERE wd.status IN ('pending','retrying')) / COUNT(*) AS pending_or_retrying_rate_pctFROM webhook_deliveries wdJOIN webhooks w ON w.id = wd.webhook_idJOIN tenants t ON t.id = wd.tenant_idWHERE t.name IN ('Brennquell Kaffee','Cragsley Outfitters','Ampfeld Elektronik','Marrowby & Vane') AND wd.created_at >= DATE '2025-01-01' AND wd.created_at < DATE '2026-01-01' AND (w.name ILIKE '%ERP%sync%' OR w.name ILIKE '%WMS%sync%' OR w.name ILIKE '%ACCOUNTING%sync%')GROUP BY t.name, date_trunc('month', wd.created_at)ORDER BY t.name ASC, date_trunc('month', wd.created_at) ASCORDER BY t.name ASC NULLS LAST, date_trunc('month', wd.created_at) ASC NULLS LAST
SELECT t.name AS tenant_name, CAST(date_trunc('month', wd.created_at) AS date) AS month, COUNT(*) AS total_sync_deliveries, 100.0 * COUNT(*) FILTER (WHERE wd.status IN ('pending','retrying')) / COUNT(*) AS pending_or_retrying_rate_pct FROM webhook_deliveries wd JOIN webhooks w ON w.id = wd.webhook_id JOIN tenants t ON t.id = wd.tenant_id WHERE t.name IN ('Brennquell Kaffee','Cragsley Outfitters','Ampfeld Elektronik','Marrowby & Vane') AND wd.created_at >= DATE '2025-01-01' AND wd.created_at < DATE '2026-01-01' AND (w.name ILIKE '%ERP%sync%' OR w.name ILIKE '%WMS%sync%' OR w.name ILIKE '%ACCOUNTING%sync%') GROUP BY t.name, date_trunc('month', wd.created_at) ORDER BY t.name ASC NULLS LAST, date_trunc('month', wd.created_at) 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 Lucas Moreau (analyst for the VP Platform Engineering):
Title: 2025 Monthly Sync-Webhook Stuck Rate for the 4 Highest-Impact Tenants
Question: For the 4 tenants that account for ~91% of all stuck sync-webhook deliveries (Brennquell Kaffee, Cragsley Outfitters, Ampfeld Elektronik, Marrowby & Vane), has their stuck rate been stable all year or is it new/worsening?
Grain: one row per tenant per calendar month of 2025
Columns:
- tenant_name
- month
- total_sync_deliveries
- pending_or_retrying_rate_pct
Details: Table webhook_deliveries joined to webhooks (name in 'ERP sync','WMS sync','ACCOUNTING sync' and close variants, same matching as the prior sync-webhook report) and tenants, restricted to these 4 tenants: Brennquell Kaffee, Cragsley Outfitters, Ampfeld Elektronik, Marrowby & Vane. Filter created_at in 2025. Group by tenant and month of created_at. pending_or_retrying_rate_pct = share of that tenant-month's sync deliveries with status in ('pending','retrying'), rounded 2dp. Order by tenant_name ascending, month ascending.
Check your query with the warehouse's dry_run tool, then hand it in with submit.dry_runSELECT t.name AS tenant_name, to_char(date_trunc('month', wd.created_at), 'YYYY-MM') AS month, COUNT(*) AS total_sync_deliveries, ROUND(100.0 * COUNT(*) FILTER (WHERE wd.status IN ('pending','retrying')) / COUNT(*), 2) AS pending_or_retrying_rate_pct FROM webhook_deliveries wd JOIN webhooks w ON w.id = wd.webhook_id JOIN tenants t ON t.id = wd.tenant_id WHERE t.name IN ('Brennquell Kaffee','Cragsley Outfitters','Ampfeld Elektronik','Marrowby & Vane') AND wd.created_at >= DATE '2025-01-01' AND wd.created_at < DATE '2026-01-01' AND (w.name ILIKE '%ERP%sync%' OR w.name ILIKE '%WMS%sync%' OR w.name ILIKE '%ACCOUNTING%sync%') GROUP BY t.name, date_trunc('month', wd.created_at) ORDER BY t.name ASC, date_trunc('month', wd.created_at) ASC
STATUS: REJECTED - does not compile: Catalog Error: Scalar Function with name to_char does not exist!
dry_runSELECT t.name AS tenant_name, CAST(date_trunc('month', wd.created_at) AS date) AS month, COUNT(*) AS total_sync_deliveries, ROUND(100.0 * COUNT(*) FILTER (WHERE wd.status IN ('pending','retrying')) / COUNT(*), 2) AS pending_or_retrying_rate_pct FROM webhook_deliveries wd JOIN webhooks w ON w.id = wd.webhook_id JOIN tenants t ON t.id = wd.tenant_id WHERE t.name IN ('Brennquell Kaffee','Cragsley Outfitters','Ampfeld Elektronik','Marrowby & Vane') AND wd.created_at >= DATE '2025-01-01' AND wd.created_at < DATE '2026-01-01' AND (w.name ILIKE '%ERP%sync%' OR w.name ILIKE '%WMS%sync%' OR w.name ILIKE '%ACCOUNTING%sync%') GROUP BY t.name, date_trunc('month', wd.created_at) ORDER BY t.name ASC, date_trunc('month', wd.created_at) ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: tenant_name, month, total_sync_deliveries, pending_or_retrying_rate_pct
submitSELECT t.name AS tenant_name, CAST(date_trunc('month', wd.created_at) AS date) AS month, COUNT(*) AS total_sync_deliveries, ROUND(100.0 * COUNT(*) FILTER (WHERE wd.status IN ('pending','retrying')) / COUNT(*), 2) AS pending_or_retrying_rate_pct FROM webhook_deliveries wd JOIN webhooks w ON w.id = wd.webhook_id JOIN tenants t ON t.id = wd.tenant_id WHERE t.name IN ('Brennquell Kaffee','Cragsley Outfitters','Ampfeld Elektronik','Marrowby & Vane') AND wd.created_at >= DATE '2025-01-01' AND wd.created_at < DATE '2026-01-01' AND (w.name ILIKE '%ERP%sync%' OR w.name ILIKE '%WMS%sync%' OR w.name ILIKE '%ACCOUNTING%sync%') GROUP BY t.name, date_trunc('month', wd.created_at) ORDER BY t.name ASC, date_trunc('month', wd.created_at) ASC
Submitted to tonight's batch. Your work on this request is done.