The Retry That Erased the Failure: Attempt History and Log Retention

START WITH THE MECHANISM

Interactive companion and laboratory update: 12 November 2026. The editorial date places the topic on the timeline; this current explanation and its measured evidence retain their actual preparation and execution dates.

THE FULL INVESTIGATION

The Retry That Erased the Failure: Attempt History and Log Retention

Dates and technology context

This is a retrospective editorial timeline. Topic context: 5 March 2026. First publicly available: 12 November 2026. Current text, visuals and laboratory revisions were prepared on 12 November 2026.

The four-observation SQLite fixture, reopen checks, retention queries and timing report are 2026-11-12 work. Preserve the already disclosed overwritten first measurement rather than giving it an earlier date.

A retry succeeds, the operation turns green, and the earlier error disappears from the most convenient view of the system. Nothing necessarily deleted a log file. The application may simply have overwritten one row called status until that row described the latest result and no longer described how the result was reached.

That can be exactly the right behavior for a current-state projection. It can be a serious mistake if the same projection is treated as the incident record. The customer needs to know whether the operation completed. The engineer may also need to know how many attempts occurred, which one failed, and what evidence explains the recovery.

This article implements the distinction in a small SQLite-backed .NET laboratory. One operation has a failed first attempt and a successful second attempt. A latest-state table ends with success and no failure code. A separate event history retains all four observations, including the earlier dependency timeout. Duplicate delivery is ignored only after verifying that the event identity still describes the same content.

The fixture is not a broker, a complete workflow engine or a tamper-resistant audit product. It is an executable demonstration of two different read models and the failure that occurs when one is asked to replace the other. Its result is useful precisely because the boundary is small enough to inspect.

The final state answers a different question

Suppose a user asks whether order-1 completed. A response saying attempt one failed would be misleading if attempt two succeeded. A current-state view should make the terminal result easy to discover. It should not force the user to reconstruct every internal event before learning whether the requested outcome is available.

Now suppose an engineer asks why the same operation took longer than usual. The final success value is insufficient. The engineer needs the unsuccessful attempt, its failure category and the relationship to the later attempt. The absence of that information can turn a recovered transient problem into an unexplained latency anomaly.

Both views are legitimate. The design mistake is giving them the same storage and retention contract without noticing the different questions. A mutable status row is a summary. It may be a useful summary, but it is not a chronological account of the work.

The laboratory therefore writes both views together. Each accepted event enters the history, and the current projection is updated in the same local transaction. That makes the distinction visible in the code instead of relying on a convention that a future maintainer may not know.

This does not mean every diagnostic detail should be retained forever. It means retention and summarization should be deliberate. If a system discards attempt history, it should understand which explanations are no longer possible and whether another evidence source preserves them.

Give operations, attempts and observations separate identities

01 / Watch the mechanism

One operation can have different attempt outcomes

Enlarge diagram

Static view. Play to see how the mechanism unfolds.

The synthetic operation is named order-1. Its first attempt has number 1 and its second has number 2. The observations have their own identities, e1 through e4. These identities serve different purposes.

The operation identity connects work that belongs to one requested outcome. The attempt identity separates executions that can have different results. The event identity lets the receiver recognize a repeated observation without assuming that two similar messages are necessarily the same observation.

The executed event sequence is:

e1  order-1  attempt 1  Started
e2  order-1  attempt 1  Failed     dependency_timeout
e3  order-1  attempt 2  Started
e4  order-1  attempt 2  Succeeded

The fixture uses a bounded failure category instead of a raw exception body. That choice keeps the example inspectable without copying arbitrary payloads into its event store. In a real system, a separate protected diagnostic record may be appropriate for more detail, but its content and retention policy need explicit ownership.

An event identity is not an authorization credential. The code accepts synthetic trusted fixture input and does not implement an authentication service. A caller who can append arbitrary events could tell a false story. Preventing that requires another boundary, not a more elaborate event ID.

The event number assigned by the SQLite table is an ingestion sequence. It is not a timestamp from the original worker, a global ordering across services or proof of causal order. Those distinctions matter when the example is extended beyond its deliberately ordered input.

Preserve history and update a projection atomically

02 / A concrete example

One transaction maintains two different views

Enlarge diagram

The database contains an Events table and a Latest table. Events has a unique event ID, a content hash, operation and attempt fields, status and optional failure code. Latest has one row per operation and stores the most recently accepted observation in this ordered fixture.

The write path begins a transaction, checks for an existing event ID, inserts the history row and updates the projection. If a later statement fails, the transaction is not accepted as a completed write. The intended local invariant is that a newly accepted observation does not update the projection without retaining its history entry.

Microsoft.Data.Sqlite transactions group those statements into one unit of work. That is a database-local property. It does not make a remote message acknowledgement atomic with the write or coordinate an external effect. Microsoft.Data.Sqlite transaction documentation.

The projection uses an explicit UPSERT:

INSERT INTO Latest(Operation, Attempt, Status, Code)
VALUES($op, $attempt, $status, $code)
ON CONFLICT(Operation) DO UPDATE SET
    Attempt = excluded.Attempt,
    Status = excluded.Status,
    Code = excluded.Code;

This statement intentionally overwrites the operation's current summary. The history insert does not. SQLite's UPSERT behavior is tied to the declared uniqueness conflict target; it is not a general instruction to ignore every possible data error. SQLite UPSERT documentation.

The separation makes the final result unsurprising. After e4, Latest says attempt two succeeded and its failure code is null. Events still contains e2 with the dependency-timeout category. The summary is useful because it is concise; the history is useful because it has not been compressed into that summary.

A duplicate is not permission to change the past

03 / A concrete example

Duplicate and conflict are different outcomes

Enlarge diagram

A repeated event ID can arrive for an ordinary reason, such as redelivery after an acknowledgement was lost. The receiver should not count that repeat as another failed attempt merely because it observed the same envelope twice.

The fixture computes a SHA-256 over the serialized event representation used by the application. When the ID already exists, the stored hash must match. An identical repeat returns a duplicate result without inserting another history row or updating the projection. The check verifies that the total remains four and the count of failed observations remains one.

If the same event ID arrives with a changed failure code, the code rejects it as an identity conflict. Silently ignoring the new bytes would hide a disagreement; silently replacing the old bytes would rewrite the evidence. Neither behavior is described as an ordinary duplicate.

The hash is not a signature or a guarantee of truth. It establishes equality of the chosen representation within this fixture. Changing serialization rules in a later version requires a compatibility decision. A stable event identity whose canonical representation changes cannot simply inherit the old duplicate semantics without review.

The code also does not claim protection against an administrator who can edit the database file. There is no externally anchored hash chain, access-control service or immutable-storage guarantee. The word history describes how this application writes its records, not an assertion that the records are impossible to alter.

The exact observations after recovery

04 / A concrete example

Success in the projection does not erase history

Enlarge diagram

The correctness run appends all four events and checks the final projection. It then replays e2, verifies that the duplicate did not change counts, submits the conflicting e2 variant and requires rejection. After closing the connection, it reopens the database and checks the exact sequence of event identities and the preserved failure count.

The accepted result is:

View	Observed state
Current operation	Attempt 2, Succeeded, no current failure code
History identities	e1, e2, e3, e4
Failed observations	1
Exact duplicate e2	Ignored without another row
Changed-content e2	Rejected as an identity conflict
After connection reopen	Four events and one failed observation remain

The run reported 12 explicit checks, all passing under SDK 10.0.400 and runtime 10.0.15. It used a real SQLite file and closed all connections before deleting the owned fixture directory. The output was captured before cleanup, so the record of the result survives even though the temporary database does not.

Reopening a connection is not the same as testing process crash recovery or power-loss behavior. Other laboratories in this collection include controlled process exits, but this one does not borrow their evidence as proof of its own durability. It verifies the specific file-backed history and projection behavior described above.

The final success has not erased the failed attempt from the event table. It has erased it only from the deliberately compact current-state view. That is the central distinction an operator needs to understand when choosing which view can answer an incident question.

Ordered input is a real limitation of this projection

The sample's Latest table means last accepted observation in ingestion order. It is not a complete state machine for arbitrary out-of-order messages. A newly identified old failure arriving after a later success could overwrite the summary if it were accepted through this simple path.

The article does not hide that behavior behind the word latest. The fixture's input is ordered, and the duplicate test reuses an existing event ID, which is ignored before projection. That is different from a genuinely late event with a new identity. A production system needs a rule for the latter case.

Possible designs include an explicit event revision, a validated state transition, a projection that compares a trusted attempt sequence, or a recomputation from a defined causal history. Each design needs assumptions about who assigns the ordering information and how conflicting observations are resolved. Timestamps alone can be insufficient if clocks differ or an event is recorded after the underlying effect.

The immutable event set can still help investigate those cases, even when the current projection needs repair. Keeping the source observations separate makes it possible to improve or rebuild the projection without pretending the earlier observations never existed. That is a practical advantage of separating storage roles.

It is not a reason to accept every event uncritically. Invalid identities, unauthorized producers and contradictory state transitions still need policy. The current code demonstrates one narrow integrity check and does not claim to implement the entire policy needed by a distributed event platform.

Record attempt outcomes without inflating operation failures

The example has one failed attempt and one successful operation. A metric called operations_failed should not be incremented merely because e2 exists if that metric is intended to describe terminal operation outcomes. A metric called failed_attempts can count e2 correctly without contradicting the final success.

The distinction also affects cost and latency interpretation. Two attempts consumed work, even though the customer received one successful result. A current-state view can tell the customer that the operation completed, while an operational report can show that completion required recovery.

The agent cost ledger applies a related distinction to attempts, usage observations and accepted tasks. This history fixture does not measure model usage or pricing. Its contribution is the preservation of execution observations that another accounting or diagnostic component may need.

The labels should remain specific. A recorded failure category is evidence that the producer reported that outcome. It is not an independent proof of the dependency's internal cause. A diagnostic investigation may need a trace, a remote request identifier or another observation to decide why the failure occurred.

This is another reason to resist one generic success flag. A system can correctly represent the final operation while preserving evidence that its dependency is unstable. Removing the latter because retries usually recover can delay recognition of an emerging problem.

The measured cost is the history path, not the retry itself

05 / A concrete example

The measured path is local event persistence

Enlarge diagram

A small local measurement writes five batches of 100 synthetic operations. Each operation contributes the four-event sequence, so a sample performs 400 serial event transactions. The accepted run retains 2000 events and 500 failed-attempt observations before the fixture is removed.

The measured interval includes event hashing, the history insert, the projection UPSERT and the local transaction commit. It does not include a real dependency call, a broker, a network retry, a collector or a retention sweep. The input records and operation IDs are generated in the loop, so their construction is part of the measured path.

Sample	Operations	Event transactions	Elapsed interval	Managed allocation
1	100	400	560.664 ms	2321048 B
2	100	400	522.834 ms	2169632 B
3	100	400	511.575 ms	2052960 B
4	100	400	520.109 ms	2040480 B
5	100	400	560.767 ms	2040480 B

An orchestration retry repeated an earlier successful measurement. The table uses the later complete saved report, not the faster run. The earlier run’s observed summary is preserved in earlier-measurement-note.json, with a median of 478.453 ms; its full raw JSON was overwritten. That incomplete provenance is disclosed rather than presented as another complete report. The two runs should not be combined into a stronger statistical claim.

The values describe the later sequential run against a local SQLite file with DELETE journaling and FULL synchronous behavior selected by the fixture. They do not establish how a concurrent event service scales, how a remote disk behaves, or how a different batching policy changes throughput. The first sample also contains more first-use activity than later samples may encounter.

The current-thread managed-allocation observation is not the database file size or native SQLite memory. It includes allocations made by the managed path inside the interval. A design review concerned with retained heap or storage growth would need another observation boundary and should not substitute this counter for it.

The experiment does not compare the history design with a projection-only implementation. It therefore does not quantify the extra cost of keeping history. Its purpose is to give the implemented path an actual execution record and a reproducible scope. A future comparison can ask whether a different transaction batch or retention strategy preserves the same required evidence at a different cost.

Retention is a separate policy with irreversible consequences

The fixture retains all accepted observations until the laboratory ends. It does not implement automatic retention, compaction or archival. That absence is intentional because those mechanisms need another contract: what evidence must remain available, to whom, and for which decisions?

A production retention policy can reasonably remove detailed diagnostic records after a period while preserving durable business outcomes. It can also aggregate attempt counts or archive older events. The important point is to identify what an investigator can no longer reconstruct after that transformation.

Deleting successful-attempt records may seem harmless until they are needed to distinguish a recovered operation from a permanently failed one. Deleting failure details may reduce sensitivity or storage, but it can also remove the information that explains an intermittent incident. Retention decisions should therefore be made with the operational questions in view, not only the current row count.

The event store should not become an indiscriminate copy of every payload. This example uses a bounded failure code and synthetic identifiers. A real system must decide which fields are necessary and what access controls and retention rules apply. More history is not automatically better evidence if it is unstructured, excessive or unavailable to the people who need to interpret it.

The distinction between durable business evidence and diagnostic detail should remain explicit. A sampled trace can be useful for investigation while an operation receipt must remain complete. The event history in this article is an application-owned fixture; it does not claim to replace a compliance audit system or a complete business ledger.

Reproduce the explanation, then test the next boundary

Download the executable history laboratory and raw results. The project pins its SDK, runtime and SQLite dependencies. The check mode requires a new owned fixture directory, writes the event sequence, validates duplicates and conflicts, reopens the database and removes the fixture after the result has been reported.

The measure mode also requires a new directory. It writes the bounded set of 2000 events, validates aggregate counts after each sample and closes the database before cleanup. Do not point the example at an existing application database or reuse a directory that contains unrelated work.

A useful next test would introduce an explicit ordering policy and verify a genuinely late event. Another could rebuild the projection from preserved observations and compare it with an independently expected state. A third could evaluate a bounded retention rule against a defined set of incident questions. These are proposed extensions, not results already established by the simple history table.

The practical lesson is that recovery and explanation are different products of the system. A retry can restore the requested operation while leaving evidence that the first attempt failed. The system should be able to report both without forcing one to overwrite the other.

When the operation turns green, the earlier failure may no longer be the customer's immediate concern. It can still be the most useful clue for the engineer responsible for the next incident. Preserve the view needed by each question, and make the transition between those views a deliberate part of the design.

Do not let a convenient query redefine the event

The simplest history query in this companion counts rows whose status is Failed. That is correct for the fixture because each failed attempt produces exactly one such observation and its duplicate has the same event identity. A different producer could emit several failure-related events for one attempt, such as dependency timeout, retry scheduled and attempt abandoned. Counting all of them as failed attempts would overstate the result.

A production metric therefore needs a definition beyond a column name. Is the unit an operation, an attempt, an observation or a category occurrence? Which event establishes the transition being counted? Can that transition be observed more than once, and how is the repeat recognized? These questions determine whether a query is meaningful.

The same care applies to success. An event saying that a handler returned is not necessarily a receipt proving that an external effect committed. The laboratory's Succeeded value is a trusted synthetic observation. It does not independently inspect a remote system. If the operation involves a real external effect, the event should be linked to the evidence that establishes that effect or explicitly retain an unknown outcome.

This is why event history and business authority should not be confused. History preserves what the system accepted as observations. It can support an explanation and a later reconciliation, but the application still needs a rule for which evidence determines the official result. In the small fixture, the producer and ordering are controlled. In a distributed system, those conditions need implementation and tests.

A retention change should have a query-level acceptance test

Imagine a proposed optimization that keeps only the newest event per operation. It would preserve the answer to the customer-facing completion question in the demonstrated sequence. It would fail the diagnostic question asking whether an earlier attempt timed out. A retention review can express both questions before approving the transformation.

That creates a concrete acceptance method. Capture a representative set of operational questions and their expected answers for a controlled fixture. Apply the proposed retention or compaction rule to a separate copy. Check which answers remain possible, which become approximate and which are intentionally unavailable. The decision can then acknowledge the loss instead of discovering it during an incident.

No such compaction experiment was executed in this companion. The latest-state table acts as a small example of information loss through summarization, while the event table preserves the full selected sequence. A production retention strategy should have its own evidence and should not inherit a claim of correctness merely because the underlying database transaction works.

A useful design may retain different levels of detail for different periods. It may preserve durable operation receipts while reducing transient diagnostic payloads. It may move older events to a slower store. Those are legitimate choices if the intended questions, access rules and recovery procedures remain clear. They are not interchangeable implementations of retaining everything.

The discipline is to state the explanation being promised. If a service promises that an investigator can reconstruct every attempt for a defined period, the storage and projection design must support that promise. If it promises only aggregate counts after that period, the interface and documentation should say so. A compact result is trustworthy when its omissions are understood.

The SQLite transaction is a useful boundary because both stored views live in the same database. That convenience should not be generalized to a deployment in which the history is sent to a remote log backend and the current state is stored elsewhere. Two independently acknowledged writes can diverge, and a retry can reach one destination more than once. Such a system needs a deliberate delivery and reconciliation design. This companion does not exercise that network boundary.

The code also uses one application-owned connection and sequential writes. It is not a concurrent ingestion service. A broader implementation must decide how multiple writers coordinate, how long they may wait and what a caller should do when a transaction cannot begin. The accepted sample is not evidence that contention is absent, only that contention was outside the selected path.

These limitations are not reasons to discard the result. They locate the result. A reader can see exactly how the event identity, transaction and projection fit together, then decide which additional boundary their own application introduces. The next test should correspond to that boundary rather than repeating the same local fixture with a larger and more impressive event count.

Source code and execution evidence

Download the complete lab, source record and raw execution results. This package supports the specific execution claims in this article. It is not a production certification.