A pipeline you cannot replay is a spreadsheet

Ask a CRM a simple question: what did the pipeline look like at nine o'clock last Monday. Not what it looks like now with a date filter, which is a different question with a different answer. What was in each stage, at that moment, before the week's moves. Most systems cannot answer, and the ones that cannot are, whatever their price, a spreadsheet with a login screen. The current state is stored; the history that produced it is not, or is kept for a while and then thrown away. When I designed the pipeline model for the CRM at CustomGlide, this was the question I built around, and the design that answers it is older than any CRM.

The Monday replay test

The test is one query. Pick any past instant and ask the system for the pipeline as it was. A system that passes has a transition log as its source of truth: every change of stage, owner, amount or status is a row with a timestamp, and the current state of a deal is derived from its rows. A system that fails has a mutable deal record whose stage column was overwritten, with maybe a history table on the side that was never the source of anything.

The replay query is short, and it is the same query for every report that asks about the past.

SELECT DISTINCT ON (deal_id) deal_id, to_stage AS stage
FROM stage_transitions
WHERE moved_at <= '2026-09-15 09:00+05:30'
ORDER BY deal_id, moved_at DESC;

Group that by stage and you have the pipeline as it stood. Run it for the previous Monday too and you have the week's net movement, exactly, rather than as a reconstruction from whatever the current rows happen to say about their created and modified dates.

Why the current row is not enough

The reason this matters is not archival tidiness. It is that most of the questions a sales team asks are questions about change, and change is not visible in a snapshot. How much of this quarter's forecast was already in the pipeline at the start of the quarter. Which deals moved backwards last month. How long do deals spend in Negotiation before they close, and has that got longer. Whether the manager's Monday view was right, now that the month has closed. Every one of those is a query over transitions, and every one is a guess if the transitions were not kept.

The disputes are worse than the reports. A rep says the deal was in Proposal Sent when the target was set; the manager remembers otherwise; the system shows Negotiation and a modified-date. Without the log, the argument is settled by seniority. With the log, it is settled by a row.

What the vendors keep

The major CRMs do keep history, and how much they keep is instructive, because each of them treats it as a feature with a limit rather than as the source of truth. Salesforce's standard field history tracking retains changes for up to 18 months in the interface and 24 through the API, and the paid Field Audit Trail add-on extends that to as long as ten years. HubSpot's property history is capped by revisions rather than time, at 45 for contact properties and 20 for deal, company and ticket properties, and deleting a property deletes its history. Zoho CRM's audit log is kept for three years and then permanently deleted.

None of that is a criticism of the products. Keeping history as a bounded side table is a reasonable engineering choice for a system whose current state is the product. It is a statement about what the product is. A CRM that stores the state and remembers some of the history is a system of record for the present. A CRM that stores the history and derives the state is a system of record for the business, and the difference shows up the first time someone asks about last Monday.

What "as of" makes possible

Once the log is the source of truth, a class of reports that were previously impossible become ordinary, and they are the reports a sales leader actually wants. Pipeline coverage at the start of a period against bookings at its end, which is the only honest way to judge a forecast. Stage duration distributions computed from the actual entry and exit times of every deal, rather than from a "days in stage" field that resets when someone edits the record. Backflow, the share of departures from a stage that go backwards, which is the subject of another post and is a single query over the same table. A cohort view that follows the deals created in one month through every stage they visited afterwards.

None of these needs a data warehouse or an export. They need the table that a mutable design threw away, kept, and a timestamp parameter on the query. The reporting layer of the CRM shrank when the log arrived, because most of what it had been doing was reconstructing history from clues.

The append-only ledger, without the vocabulary

The pattern has a name in software architecture and a body of writing behind it, and I have avoided the name on purpose, because the name makes it sound like a rewrite. It is not. It is one table, appended to on every change, and one derived view of the latest row per deal. The rest of the application reads the view exactly as it read the old mutable row. The dashboards, the forecasts, the stage reports all become queries over the log with a timestamp parameter that defaults to now.

The cost that people expect, storage, is small. A deal that passes through six stages produces six rows. A pipeline of a hundred thousand deals with a dozen transitions each is a million or so rows of a few columns, which is a small table by any modern database's standard. The growth is linear in activity, and activity is the thing the business wants more of.

What it changed

Three things, in the CRM. Every dashboard gained an "as of" parameter, which by default is now and can be any instant, and the forecast page compares the pipeline as it was at the start of the quarter with the pipeline now, from the same query with two timestamps. The stage report that used to be a nightly job that nobody trusted became a view that is correct at any moment because it is computed from the log at that moment. And the argument about last Monday stopped happening, because the system could answer it.

The test is worth running against any system that calls itself a system of record. Pick a Monday. Ask for nine o'clock. If the answer is a reconstruction, a best guess from modified dates and a side table with a retention limit, the system knows the present and has opinions about the past. If the answer is a query, it remembers. A pipeline you cannot replay was never a pipeline. It was a spreadsheet with a stage column, and the stage column has been overwritten.