Repository navigation
idempotency_records grows unbounded: full response body stored per successful write, no purge job #259
Description
Found by the #243 spec review (contrarian + architect lenses, verified against source).
The gap
IdempotencyMiddleware stores the full response body of every successful (2xx) write in idempotency_records, keyed by (AccountId, method+path, SHA256(key)). Nothing ever deletes these rows: the Jobs folder contains only DailyEntryLockSweep, DurableJobWorker", and its heartbeat — no purge reads CreatedAt. audit_events` grows the same way, but that is arguably a feature (audit retention is a product decision); cached idempotency responses are not — their usefulness ends when a client retry is no longer plausible (hours, not months).
Every production write grows the table monotonically, with response bodies inline. At farm scale this is slow-burn, but it is unbounded, and the #243 rehearsal will quantify the growth rate (its findings doc records before/after table sizes).
Suggested shape
- A sweep job on the existing
DurableJobWorkerloop deleting records older than a retention window (config, default e.g. 48 h — comfortably beyond any client retry horizon, aligned with how idempotent replays are actually used by the SPA). - Batched deletes (the table may be large by the time this ships).
- Integration test: an old record is purged, a fresh one survives, and a replay after purge re-executes rather than 500s.
- Decide explicitly whether
audit_eventsretention is in scope here or its own product decision (lean: own decision, not this issue).
Refs
src/Cluckwork.Api/Middleware/IdempotencyMiddleware.cs(storage, key scope)src/Cluckwork.Infrastructure/Persistence/IdempotencyRecord.cs(unique index, CreatedAt)src/Cluckwork.Infrastructure/Jobs/(the worker loop to hang the sweep on)- Release rehearsal: day-in-the-life simulation of 10 users (all roles) with container-resource + HTTP-traffic monitoring #243 (quantifies growth), Deployment readiness audit: production go-live scan before first real users (host undecided) #244 (deployment-readiness audit — consumes this)
Activity
mforce commentedon Aug 2, 2026
Measured, not estimated — #243 harness on merged
main(5df5cd7)The open decision was whether the purge sweep needs a
CreatedAtindex. Here are numbers instead of arithmetic.Instrument note (this part matters)
seed --profile simulationwrites zeroidempotency_recordsrows — it invokes handlers directly through DI, with noHttpClientand noIdempotency-Key, andIdempotencyMiddlewareis HTTP-layer. Confirmed empirically: a full 90-day fixture seed left the table at 0 rows. Measuring with the seeder would have produced a confident "no index needed" answer to a different question. All numbers below come from the k6 harness driving real HTTP writes.The run
10 VUs (full cast: Owner/Manager/Sales/3×Worker/4×ReadOnly), 10-minute capacity phase, prod-config container.
Growth
The premise needs correcting
This issue's framing is "full response body stored per successful write", implying bodies dominate. They don't:
ResponseBodyThe table is dominated by row overhead, the three hash columns, and the two existing indexes — not by cached bodies. This app's write endpoints return small bodies (created ids and status), so the "unbounded response-body storage" concern is much weaker than filed. The growth concern is real; the reason stated for it is not.
Does the sweep need an index?
Synthesized the 48h steady state implied by the measured rate — 105,000 rows / 33 MB — with the real column shape, and ran the proposed batched delete both ways. Worst case deliberately: no row is old enough yet, so the
LIMIT 1000never short-circuits and the scan runs to completion.Seq Scan, 105k rowsCreatedAtbtree indexIndex ScanRecommendation: code-only sweep, no index, and do not touch
InitialCreateThe index is 273× faster in relative terms and irrelevant in absolute ones. 17 ms once per sweep interval on a background worker is free; the index would tax every write on the hot idempotency insert path to buy it back. That trade is backwards.
Two honesty caveats on the extrapolation:
tools/simulation/README.md): plaintext local Postgres, uncapped host resources, raised rate limits. Absolute latency here is not production-equivalent — but the scan-vs-index comparison is a same-machine A/B, so the 273× ratio and the 17 ms order of magnitude hold.Blocker cleared along the way
The harness could not boot merged
mainat all — four breakages (#319AllowedHosts, #261/#262 TLS floor, abootstrap.shheredoc executing a comment, and a k6 timezone bug that failed 12.4% of requests during the hours UTC and the farm's date disagree). Fixed in a separate PR with a CI smoke so this cannot rot silently again.feat(api): purge aged idempotency_records on the durable-job worker (#…