Queries · q336 · VP Platform Engineering · week 2, night 1
“Has the ~15% stuck-in-retry rate on sync webhooks been happening all year, or did it start/worsen recently?”
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 1, request 2 of 3
Diego wants the webhook stuck-retry finding properly validated before it goes to leadership, and that's explicitly his #1 and #2 priority this week, so two of tonight's three slots go there: first, a correct tenant-level view (fixing last week's broken join) showing exactly how many merchants have ERP/WMS/Accounting sync webhooks stuck above a real threshold and what revenue/order volume sits behind them, so we can say concentrated-in-big-accounts vs long-tail; second, a full-year monthly trend of that same stuck-rate (webhook_deliveries has clean 12-month coverage, unlike job_runs/health_checks) to tell if this is longstanding or a recent regression. I'm using my third slot on Diego's #3 (carts.remind confidence) since it's a cheap, high-value pull: the actual error_message on the 20 failed runs will tell us in one query whether this was one shared root cause (supports his 'latent risk that could recur' concern being containable) or scattered causes (supports a load/capacity read). I'm deferring Diego's #4 (checking if tickets from the 10 affected tenants actually mention sync/reminders/carts) to night 2 since it needs careful text-search scoping and isn't his top priority this week.
The engineer · 1 dry run on the empty schema, then the submission
SELECT t0.month_start AS month, t0.total_sync_deliveries, ROUND(100.0 * t0.success_count / NULLIF(t0.total_sync_deliveries,0), 2) AS success_rate_pct, ROUND(100.0 * t0.failed_count / NULLIF(t0.total_sync_deliveries,0), 2) AS failed_rate_pct, ROUND(100.0 * t0.pending_retrying_count / NULLIF(t0.total_sync_deliveries,0), 2) AS pending_or_retrying_rate_pct FROM ( SELECT date_trunc('month', wd.created_at) AS month_start, COUNT(*) AS total_sync_deliveries, SUM(CASE WHEN wd.status = 'success' THEN 1 ELSE 0 END) AS success_count, SUM(CASE WHEN wd.status = 'failed' THEN 1 ELSE 0 END) AS failed_count, SUM(CASE WHEN wd.status IN ('pending','retrying') THEN 1 ELSE 0 END) AS pending_retrying_count FROM webhook_deliveries wd JOIN webhooks wh ON wh.id = wd.webhook_id WHERE wh.name IN ('ERP sync','WMS sync','ACCOUNTING sync') AND wd.created_at >= DATE '2025-01-01' AND wd.created_at < DATE '2026-01-01' GROUP BY date_trunc('month', wd.created_at) ) t0 ORDER BY t0.month_start ASC
“Query compiles and returns the expected columns at monthly grain for 2025.”
“Submitted. The report aggregates webhook_deliveries for webhooks named 'ERP sync', 'WMS sync', and 'ACCOUNTING sync' across all tenants, grouped by calendar month of 2025, with success/failed/pending-or-retrying rates as percentages of that month's total sync deliveries.”
As Lucas Moreau saw it, from the small dataset the agents worked on
month | total_sync_deliveries | success_rate_pct | failed_rate_pct | pending_or_retrying_rate_pct --------------------+-----------------------+------------------+-----------------+----------------------------- 2025-01-01 00:00:00 | 4438 | 94.21 | 0.02 | 5.77 2025-02-01 00:00:00 | 3088 | 94.3 | 0.0 | 5.7 2025-03-01 00:00:00 | 3551 | 93.41 | 0.0 | 6.59 2025-04-01 00:00:00 | 3434 | 94.09 | 0.0 | 5.91 2025-05-01 00:00:00 | 3810 | 93.99 | 0.0 | 6.01 2025-06-01 00:00:00 | 3413 | 94.67 | 0.0 | 5.33 2025-07-01 00:00:00 | 3702 | 94.41 | 0.0 | 5.59 2025-08-01 00:00:00 | 3308 | 94.77 | 0.0 | 5.23 2025-09-01 00:00:00 | 3724 | 94.04 | 0.0 | 5.96 2025-10-01 00:00:00 | 4110 | 94.72 | 0.0 | 5.28 2025-11-01 00:00:00 | 5720 | 93.41 | 0.02 | 6.57 2025-12-01 00:00:00 | 7454 | 95.26 | 0.0 | 4.74 (12 rows)
The memo and the queries that followed; links found by title, so inferred
Later queries that build on this report:
It builds on:
Changed only where the benchmark needs the same result on every system
SELECT t0.month_start AS month, t0.total_sync_deliveries, ROUND(100.0 * t0.success_count / NULLIF(t0.total_sync_deliveries,0), 2) AS success_rate_pct, ROUND(100.0 * t0.failed_count / NULLIF(t0.total_sync_deliveries,0), 2) AS failed_rate_pct, ROUND(100.0 * t0.pending_retrying_count / NULLIF(t0.total_sync_deliveries,0), 2) AS pending_or_retrying_rate_pct t0.total_sync_deliveries AS total_sync_deliveries, 100.0 * t0.success_count / NULLIF(t0.total_sync_deliveries,0) AS success_rate_pct, 100.0 * t0.failed_count / NULLIF(t0.total_sync_deliveries,0) AS failed_rate_pct, 100.0 * t0.pending_retrying_count / NULLIF(t0.total_sync_deliveries,0) AS pending_or_retrying_rate_pctFROM ( SELECT date_trunc('month', wd.created_at) AS month_start, COUNT(*) AS total_sync_deliveries, SUM(CASE WHEN wd.status = 'success' THEN 1 ELSE 0 END) AS success_count, SUM(CASE WHEN wd.status = 'failed' THEN 1 ELSE 0 END) AS failed_count, SUM(CASE WHEN wd.status IN ('pending','retrying') THEN 1 ELSE 0 END) AS pending_retrying_count FROM webhook_deliveries wd JOIN webhooks wh ON wh.id = wd.webhook_id WHERE wh.name IN ('ERP sync','WMS sync','ACCOUNTING sync') AND wd.created_at >= DATE '2025-01-01' AND wd.created_at < DATE '2026-01-01' GROUP BY date_trunc('month', wd.created_at)) t0ORDER BY t0.month_start ASCORDER BY t0.month_start ASC NULLS LAST
SELECT t0.month_start AS month, t0.total_sync_deliveries AS total_sync_deliveries, 100.0 * t0.success_count / NULLIF(t0.total_sync_deliveries,0) AS success_rate_pct, 100.0 * t0.failed_count / NULLIF(t0.total_sync_deliveries,0) AS failed_rate_pct, 100.0 * t0.pending_retrying_count / NULLIF(t0.total_sync_deliveries,0) AS pending_or_retrying_rate_pct FROM ( SELECT date_trunc('month', wd.created_at) AS month_start, COUNT(*) AS total_sync_deliveries, SUM(CASE WHEN wd.status = 'success' THEN 1 ELSE 0 END) AS success_count, SUM(CASE WHEN wd.status = 'failed' THEN 1 ELSE 0 END) AS failed_count, SUM(CASE WHEN wd.status IN ('pending','retrying') THEN 1 ELSE 0 END) AS pending_retrying_count FROM webhook_deliveries wd JOIN webhooks wh ON wh.id = wd.webhook_id WHERE wh.name IN ('ERP sync','WMS sync','ACCOUNTING sync') AND wd.created_at >= DATE '2025-01-01' AND wd.created_at < DATE '2026-01-01' GROUP BY date_trunc('month', wd.created_at) ) t0 ORDER BY t0.month_start 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 Stuck-Retry Rate for Sync Webhooks (ERP/WMS/Accounting)
Question: Has the ~15% stuck-in-retry rate on sync webhooks been happening all year, or did it start/worsen recently?
Grain: one row per calendar month of 2025
Columns:
- month
- total_sync_deliveries
- success_rate_pct
- failed_rate_pct
- pending_or_retrying_rate_pct
Details: Table webhook_deliveries joined to webhooks, filtered to webhooks.name in the same sync-integration set as the companion merchant-level request ('ERP sync', 'WMS sync', 'ACCOUNTING sync'). Filter created_at in 2025 (full year - this table has complete 2025 coverage per last week's '2025 Webhook Delivery Reliability Trend'). Group by month of created_at, platform-wide across all affected tenants combined. Rates as shares of total_sync_deliveries for that month, rounded 2dp. Order by month ascending.
Check your query with the warehouse's dry_run tool, then hand it in with submit.dry_runSELECT t0.month_start AS month, t0.total_sync_deliveries, ROUND(100.0 * t0.success_count / NULLIF(t0.total_sync_deliveries,0), 2) AS success_rate_pct, ROUND(100.0 * t0.failed_count / NULLIF(t0.total_sync_deliveries,0), 2) AS failed_rate_pct, ROUND(100.0 * t0.pending_retrying_count / NULLIF(t0.total_sync_deliveries,0), 2) AS pending_or_retrying_rate_pct FROM ( SELECT date_trunc('month', wd.created_at) AS month_start, COUNT(*) AS total_sync_deliveries, SUM(CASE WHEN wd.status = 'success' THEN 1 ELSE 0 END) AS success_count, SUM(CASE WHEN wd.status = 'failed' THEN 1 ELSE 0 END) AS failed_count, SUM(CASE WHEN wd.status IN ('pending','retrying') THEN 1 ELSE 0 END) AS pending_retrying_count FROM webhook_deliveries wd JOIN webhooks wh ON wh.id = wd.webhook_id WHERE wh.name IN ('ERP sync','WMS sync','ACCOUNTING sync') AND wd.created_at >= DATE '2025-01-01' AND wd.created_at < DATE '2026-01-01' GROUP BY date_trunc('month', wd.created_at) ) t0 ORDER BY t0.month_start ASC
STATUS: COMPILES (dry run on the empty schema, no data read) COLUMNS: month, total_sync_deliveries, success_rate_pct, failed_rate_pct, pending_or_retrying_rate_pct
submitSELECT t0.month_start AS month, t0.total_sync_deliveries, ROUND(100.0 * t0.success_count / NULLIF(t0.total_sync_deliveries,0), 2) AS success_rate_pct, ROUND(100.0 * t0.failed_count / NULLIF(t0.total_sync_deliveries,0), 2) AS failed_rate_pct, ROUND(100.0 * t0.pending_retrying_count / NULLIF(t0.total_sync_deliveries,0), 2) AS pending_or_retrying_rate_pct FROM ( SELECT date_trunc('month', wd.created_at) AS month_start, COUNT(*) AS total_sync_deliveries, SUM(CASE WHEN wd.status = 'success' THEN 1 ELSE 0 END) AS success_count, SUM(CASE WHEN wd.status = 'failed' THEN 1 ELSE 0 END) AS failed_count, SUM(CASE WHEN wd.status IN ('pending','retrying') THEN 1 ELSE 0 END) AS pending_retrying_count FROM webhook_deliveries wd JOIN webhooks wh ON wh.id = wd.webhook_id WHERE wh.name IN ('ERP sync','WMS sync','ACCOUNTING sync') AND wd.created_at >= DATE '2025-01-01' AND wd.created_at < DATE '2026-01-01' GROUP BY date_trunc('month', wd.created_at) ) t0 ORDER BY t0.month_start ASC
Submitted to tonight's batch. Your work on this request is done.