Queries · q134 · VP Platform Engineering · week 1, night 4
“Which individual webhook endpoints are chronically troublesome - low success rates or deliveries stuck retrying rather than resolving?”
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 1
Subject: 2025 platform reliability review — what I need by Friday
Lucas,
Leadership wants a year-in-review on platform reliability for 2025, and I'm leading it jointly with Samuel Whitcombe (Merchant Success). The deliverable is a memo that tells leadership how reliable the platform actually was last year, where it hurt merchants most, and what that implies for our 2026 investment priorities (reliability engineering headcount, which integrations/carriers to fix or replace, whether we need stricter rate limiting or SLAs). Samuel's side will map our findings to specific merchant relationships and churn risk, so where possible, surface things in a way that can be tied back to individual merchants rather than only platform-wide averages.
Given you only get a handful of report slots a night, here's what matters most to me, roughly in priority order:
If you have to cut scope to fit your nightly slots, protect #1 and #2 — those are what the memo absolutely must answer. The rest can wait or be addressed at a higher level if time runs short.
Let me know if anything above is ambiguous before you spend a slot on it.
Diego
Subject: Platform reliability review — what I need from my side
Hi Lucas,
As you pull together the 2025 reliability picture, here's what matters from the merchant-health side:
Happy to flag specific merchant names if that helps scope the analysis.
Best, Samuel
The analyst · week 1, night 4, request 2 of 3
Night 3 gave two important, slightly conflicting signals I need to resolve on this final night. First, Report 1 shows the Nov carts.remind incident did hit real top-tier accounts - Cragsley Outfitters, our #1 tenant by 2025 revenue, is among the 10 affected, alongside #4, #6, #7 and #8 - alongside four zero-revenue/zero-order tenants, so it's a mixed bag, not purely long-tail. Second, Report 2's ticket spike for the 10 affected tenants actually starts climbing weeks before the Nov 24 failure and peaks a week after, which could mean real merchant pain preceded our systems detecting it (bad for the 'are we flying blind' question) - but it could also just be that these are naturally higher-ticket (bigger) merchants, or a seasonal Black-Friday/holiday ticket bump everyone gets. I need a baseline comparison to tell those apart before I write that into the memo as a finding. With my last 3 slots I'll: (1) settle the ticket-spike-vs-baseline question properly using an Aug-Sep baseline for affected vs other tenants, so I can honestly say cause vs. coincidence; (2) finally give Diego a first cut at webhook reliability by individual endpoint, since Report 3 showed a flat ~5-6% of deliveries each month never resolve to success or hard failure (stuck retrying) and he explicitly wants to know which integrations are chronically troublesome; (3) check whether that same webhook stuck-rate clusters on the 10 affected tenants vs everyone else, which is exactly Samuel's cross-dimension clustering question for his top-tier accounts.
The engineer · 1 dry run on the empty schema, then the submission
select w.name as webhook_name, t.name as tenant_name, count(*) as total_deliveries_2025, round(100.0 * sum(case when wd.status = 'success' then 1 else 0 end) / count(*), 2) as success_rate_pct, round(100.0 * sum(case when wd.status = 'failed' then 1 else 0 end) / count(*), 2) as failed_rate_pct, round(100.0 * sum(case when wd.status in ('pending','retrying') then 1 else 0 end) / count(*), 2) as pending_or_retrying_rate_pct, round(avg(wd.duration_ms)) as avg_duration_ms from webhook_deliveries wd join webhooks w on w.id = wd.webhook_id join tenants t on t.id = w.tenant_id where wd.created_at >= date '2025-01-01' and wd.created_at < date '2026-01-01' group by w.id, w.name, t.name having count(*) >= 20 order by pending_or_retrying_rate_pct desc, failed_rate_pct desc, webhook_name asc limit 20
“Query compiles and matches the requested grain and columns.”
“Submitted. The report returns the 20 webhook endpoints with the highest stuck-in-retry rates (tie-broken by failure rate) among those with at least 20 deliveries in 2025, with success/failed/pending-or-retrying rates and average delivery duration per endpoint.”
As Lucas Moreau saw it, from the small dataset the agents worked on
webhook_name | tenant_name | total_deliveries_2025 | success_rate_pct | failed_rate_pct | pending_or_retrying_rate_pct | avg_duration_ms ----------------+-----------------------+-----------------------+------------------+-----------------+------------------------------+---------------- ERP sync | Hautnah | 120 | 80.0 | 0.0 | 20.0 | 2655.0 ERP sync | Lumen Collective | 89 | 82.02 | 0.0 | 17.98 | 5116.0 ERP sync | Hanse Industriebedarf | 97 | 83.51 | 0.0 | 16.49 | 3833.0 ERP sync | Seilschaft | 25 | 84.0 | 0.0 | 16.0 | 4053.0 WMS sync | Linden & Loom | 299 | 84.28 | 0.0 | 15.72 | 2713.0 ERP sync | Thistlecombe | 140 | 84.29 | 0.0 | 15.71 | 3203.0 ACCOUNTING sync | Brennquell Kaffee | 4961 | 84.52 | 0.0 | 15.48 | 2763.0 WMS sync | Ampfeld Elektronik | 512 | 84.57 | 0.0 | 15.43 | 2803.0 WMS sync | Brennquell Kaffee | 4875 | 84.62 | 0.04 | 15.34 | 2744.0 WMS sync | Cragsley Outfitters | 4073 | 85.51 | 0.0 | 14.49 | 2642.0 ERP sync | Folio & Fern | 58 | 86.21 | 0.0 | 13.79 | 2957.0 ERP sync | Wohnglück | 197 | 86.29 | 0.0 | 13.71 | 2357.0 ERP sync | Ampfeld Elektronik | 581 | 86.57 | 0.0 | 13.43 | 2174.0 ERP sync | Basecamp Nord | 49 | 95.92 | 0.0 | 4.08 | 1074.0 ERP sync | Purrfect Pantry | 81 | 96.3 | 0.0 | 3.7 | 1162.0 WMS sync | Wiesgrund Wohnen | 295 | 96.95 | 0.0 | 3.05 | 943.0 ERP sync | Kettle & Key | 68 | 97.06 | 0.0 | 2.94 | 1363.0 ERP sync | Velo Noord | 39 | 97.44 | 0.0 | 2.56 | 1176.0 WMS sync | Marrowby & Vane | 1713 | 97.55 | 0.0 | 2.45 | 762.0 ERP sync | Noordzee Surfwear | 88 | 97.73 | 0.0 | 2.27 | 816.0 (20 rows)
The memo and the queries that followed; links found by title, so inferred
Lucas Moreau's memo to Diego Morales at the end of the week:
6. Webhook deliveries are stable overall but a chronic ~15% "never resolves" rate sits in ERP/WMS/Accounting sync integrations. Platform-wide success rate held 93–95% all 12 months, no trend. Source: "2025 Webhook Delivery Reliability Trend." But the worst 20 individual endpoints are dominated by ERP/WMS/Accounting sync webhooks stuck in pending/retrying 13–20% of the time, at real volume (e.g., Brennquell Kaffee's accounting sync: ~770 stuck deliveries). Several of these tenants (Brennquell Kaffee, Cragsley Outfitters, Ampfeld Elektronik, Marrowby & Vane) also appear in the carts.remind-affected list. Source: "2025 Webhook Delivery Reliability by Endpoint." This looks like a bug in our own retry logic for sync job classes, not a third-party provider problem.
Later queries that build on this report:
It builds on:
Changed only where the benchmark needs the same result on every system
select w.name as webhook_name, t.name as tenant_name, count(*) as total_deliveries_2025, round(100.0 * sum(case when wd.status = 'success' then 1 else 0 end) / count(*), 2) as success_rate_pct, round(100.0 * sum(case when wd.status = 'failed' then 1 else 0 end) / count(*), 2) as failed_rate_pct, round(100.0 * sum(case when wd.status in ('pending','retrying') then 1 else 0 end) / count(*), 2) as pending_or_retrying_rate_pct, round(avg(wd.duration_ms)) as avg_duration_ms 100.0 * sum(case when wd.status = 'success' then 1 else 0 end) / count(*) as success_rate_pct, 100.0 * sum(case when wd.status = 'failed' then 1 else 0 end) / count(*) as failed_rate_pct, 100.0 * sum(case when wd.status in ('pending','retrying') then 1 else 0 end) / count(*) as pending_or_retrying_rate_pct, avg(cast(wd.duration_ms as numeric(18,6))) as avg_duration_msfrom webhook_deliveries wdjoin webhooks w on w.id = wd.webhook_idjoin tenants t on t.id = w.tenant_idwhere wd.created_at >= date '2025-01-01' and wd.created_at < date '2026-01-01'group by w.id, w.name, t.namehaving count(*) >= 20order by pending_or_retrying_rate_pct desc, failed_rate_pct desc, webhook_name asclimit 20order by pending_or_retrying_rate_pct desc NULLS FIRST, failed_rate_pct desc NULLS FIRST, webhook_name asc NULLS LAST, w.id asc NULLS LAST{{fetch_first(20)}}
select w.name as webhook_name, t.name as tenant_name, count(*) as total_deliveries_2025, 100.0 * sum(case when wd.status = 'success' then 1 else 0 end) / count(*) as success_rate_pct, 100.0 * sum(case when wd.status = 'failed' then 1 else 0 end) / count(*) as failed_rate_pct, 100.0 * sum(case when wd.status in ('pending','retrying') then 1 else 0 end) / count(*) as pending_or_retrying_rate_pct, avg(cast(wd.duration_ms as numeric(18,6))) as avg_duration_ms from webhook_deliveries wd join webhooks w on w.id = wd.webhook_id join tenants t on t.id = w.tenant_id where wd.created_at >= date '2025-01-01' and wd.created_at < date '2026-01-01' group by w.id, w.name, t.name having count(*) >= 20 order by pending_or_retrying_rate_pct desc NULLS FIRST, failed_rate_pct desc NULLS FIRST, webhook_name asc NULLS LAST, w.id asc NULLS LAST FETCH FIRST 20 ROWS ONLY
select w.name as webhook_name, t.name as tenant_name, count(*) as total_deliveries_2025, 100.0 * sum(case when wd.status = 'success' then 1 else 0 end) / count(*) as success_rate_pct, 100.0 * sum(case when wd.status = 'failed' then 1 else 0 end) / count(*) as failed_rate_pct, 100.0 * sum(case when wd.status in ('pending','retrying') then 1 else 0 end) / count(*) as pending_or_retrying_rate_pct, avg(cast(wd.duration_ms as numeric(18,6))) as avg_duration_ms from webhook_deliveries wd join webhooks w on w.id = wd.webhook_id join tenants t on t.id = w.tenant_id where wd.created_at >= date '2025-01-01' and wd.created_at < date '2026-01-01' group by w.id, w.name, t.name having count(*) >= 20 order by pending_or_retrying_rate_pct desc NULLS FIRST, failed_rate_pct desc NULLS FIRST, webhook_name asc NULLS LAST, w.id asc NULLS LAST LIMIT 20
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 Webhook Delivery Reliability by Endpoint
Question: Which individual webhook endpoints are chronically troublesome - low success rates or deliveries stuck retrying rather than resolving?
Grain: one row per webhook (endpoint)
Columns:
- webhook_name
- tenant_name
- total_deliveries_2025
- success_rate_pct
- failed_rate_pct
- pending_or_retrying_rate_pct
- avg_duration_ms
Details: Table webhook_deliveries joined to webhooks on webhook_id (for webhook name and target_url/tenant) and tenants for tenant_name. Filter created_at in 2025. success_rate_pct = status='success' count / total * 100; failed_rate_pct = status='failed' count / total * 100; pending_or_retrying_rate_pct = status in ('pending','retrying') count / total * 100 (deliveries that never resolved), all rounded 2dp. avg_duration_ms over non-null duration_ms, rounded to whole number. Only include webhooks with at least 20 total_deliveries_2025 to avoid noise from near-empty endpoints. Order by pending_or_retrying_rate_pct descending, then failed_rate_pct descending, tie-break webhook_name ascending. Limit to 20 rows (the worst endpoints).
Check your query with the warehouse's dry_run tool, then hand it in with submit.dry_runselect w.name as webhook_name, t.name as tenant_name, count(*) as total_deliveries_2025, round(100.0 * sum(case when wd.status = 'success' then 1 else 0 end) / count(*), 2) as success_rate_pct, round(100.0 * sum(case when wd.status = 'failed' then 1 else 0 end) / count(*), 2) as failed_rate_pct, round(100.0 * sum(case when wd.status in ('pending','retrying') then 1 else 0 end) / count(*), 2) as pending_or_retrying_rate_pct, round(avg(wd.duration_ms)) as avg_duration_ms from webhook_deliveries wd join webhooks w on w.id = wd.webhook_id join tenants t on t.id = w.tenant_id where wd.created_at >= date '2025-01-01' and wd.created_at < date '2026-01-01' group by w.id, w.name, t.name having count(*) >= 20 order by pending_or_retrying_rate_pct desc, failed_rate_pct desc, webhook_name asc limit 20
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: webhook_name, tenant_name, total_deliveries_2025, success_rate_pct, failed_rate_pct, pending_or_retrying_rate_pct, avg_duration_ms
submitselect w.name as webhook_name, t.name as tenant_name, count(*) as total_deliveries_2025, round(100.0 * sum(case when wd.status = 'success' then 1 else 0 end) / count(*), 2) as success_rate_pct, round(100.0 * sum(case when wd.status = 'failed' then 1 else 0 end) / count(*), 2) as failed_rate_pct, round(100.0 * sum(case when wd.status in ('pending','retrying') then 1 else 0 end) / count(*), 2) as pending_or_retrying_rate_pct, round(avg(wd.duration_ms)) as avg_duration_ms from webhook_deliveries wd join webhooks w on w.id = wd.webhook_id join tenants t on t.id = w.tenant_id where wd.created_at >= date '2025-01-01' and wd.created_at < date '2026-01-01' group by w.id, w.name, t.name having count(*) >= 20 order by pending_or_retrying_rate_pct desc, failed_rate_pct desc, webhook_name asc limit 20
Submitted to tonight's batch. Your work on this request is done.