Deal Stage History

This page documents what exists (and what does not) for tracking how deals move through pipeline stages over time.

What does not exist

There is no deal_stage_history table. Stage transitions are not individually recorded. This is the most common incorrect assumption made when building deal velocity or time-in-stage reports.

Do not attempt to:

  • Query deal_stage_history
  • Create a deal_stage_history table without explicit instruction
  • Calculate exact time-in-stage from historical data

What does exist

The deals table has two timestamps:

Column Type Notes
created_at timestamptz When the deal was created
updated_at timestamptz Last time any field changed

updated_at changes whenever any deal field is modified — not just stage. It is a poor proxy for time-in-stage but it is the only timestamp available without adding new infrastructure.

The correct approach for velocity reports

For approximate deal velocity (time from created to current state):

SELECT
  d.title,
  d.status,
  ds.name AS current_stage,
  d.value,
  EXTRACT(DAY FROM NOW() - d.created_at)::int AS age_days,
  EXTRACT(DAY FROM NOW() - d.updated_at)::int AS days_since_update
FROM deals d
JOIN deal_stages ds ON ds.id = d.id_stage
WHERE d.id_company = :id_company
  AND d.deleted_at IS NULL
  AND d.status = 'open'
ORDER BY age_days DESC;

This gives deal age (time since creation) and days since last update, which approximates staleness.

Adding true stage history (if required)

If a project spec explicitly requires accurate time-in-stage tracking, create a history table as part of the deliverable:

CREATE TABLE IF NOT EXISTS deal_stage_history (
  id          SERIAL PRIMARY KEY,
  id_company  INT NOT NULL,
  id_deal     INT NOT NULL,
  id_stage    INT NOT NULL,
  stage_name  VARCHAR(100),
  entered_at  TIMESTAMPTZ DEFAULT NOW(),
  exited_at   TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_deal_stage_history_deal
  ON deal_stage_history(id_deal, id_company);

Then update the deal stage-change handler to INSERT a row when a deal moves stages and UPDATE exited_at on the previous row. Time in stage = exited_at - entered_at.

Only build this if the spec explicitly requires it — do not add it speculatively.

Deal Stage History — PushButtonCRM Dev