agent's database connection failed mid-save-play: updates lost for 200 accounts

TL;DR

A connection drop mid-run loses exactly the work since the last commit, and if the agent batches everything into one giant transaction or commits nothing until the end, one drop loses hundreds of updates. Commit in small batches, retry connection failures by reopening the connection, and read back each batch's writes so the run can prove what persisted.

agent's database connection failed mid-save-play: updates lost for 200 accounts

Use this when

• The run reports failures partway and many account updates are missing
• The database or network had a transient drop during the run
• It is unclear which accounts' updates survived

Not for this skill when

• The database is in a prolonged outage owned by the platform team
• Writes failed on constraint violations, not connectivity

Steps

1. Determine what actually persisted by querying for the run's updates per account. Verify: the true persisted set replaces the run's optimistic report
2. Inspect the write pattern: transaction size, commit points, and connection handling on error. Verify: the loss window (everything since which commit) is identified
3. Restructure writes into small transactional batches with connection reopen and bounded retry on connectivity errors. Verify: a simulated drop in testing loses at most one small batch and the run continues
4. Re-run only the accounts whose updates did not persist, using idempotent upserts. Verify: the full intended set is persisted exactly once
5. Add a post-run verification count (intended versus persisted) to every save-play run report. Verify: persistence is proven per run, not assumed

Variant phrasings

database connection lost mid save play

A related phrasing for the same underlying problem.

updates lost for accounts

A related phrasing for the same underlying problem.

partial persistence in agent run

A related phrasing for the same underlying problem.

Compatibility: Compatibility: agent writers against Postgres, MySQL, or CRM-backed stores with transactional batching, current versions.

Why it happens

Durability in batch work is about commit granularity. One transaction for 200 accounts makes the failure atomic in the worst direction. Small commits plus read-back verification turn a mid-run drop into a resumable inconvenience with a provable outcome.

Edge cases / pitfalls

• Connection pools recycle dead connections; the retry must obtain a fresh connection, not reuse the corpse from the pool.
• Idempotent upserts keyed on account plus play plus run date make re-runs safe by construction.
• Verification queries should run on a separate connection after the run, or they will share the fate they are checking.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_gOjFsiySKRGS0LBhwk3lIQ