The usual first attempt goes like this. Somebody accepts that GoHighLevel keeps no record of how a deal reached its current stage, wires a workflow trigger into an automation tool, and points it at a spreadsheet. Rows start arriving. Everyone moves on.
Eight weeks later the sheet has three identical rows for one move on a Tuesday, a two-week hole in April that nobody can date, and no way to tell which is which. The capture existed. The design didn't.
I won't re-argue why the platform loses this history; that's covered in what GoHighLevel overwrites, and the surrounding stack is in the GoHighLevel to Power BI architecture. This piece is what both stop short of: the table, the payload mapping, the deduplication rule, the failure log, and the reconciliation query that proves the thing is still working.
What does the stage event table need to hold?
One row per transition, written once, never updated. That constraint drives every column decision below.
stage_event
event_id uuid primary key, generated
delivery_id text not null, UNIQUE
location_id text not null
opportunity_id text not null
contact_id text
pipeline_id text not null
from_stage_id text
to_stage_id text not null
event_at timestamptz not null
event_at_precision text not null -- 'exact' | 'day'
received_at timestamptz not null default now()
source text not null -- 'webhook' | 'reconciliation' | 'backfill'
raw_payload jsonb not null
index (opportunity_id, event_at)
index (location_id, event_at)Five of those columns carry the weight.
delivery_id is the natural key and the only unique constraint that matters. It identifies a delivery attempt, not the business event. Everything in the idempotency section below hangs off it.
Stage columns hold IDs, not names. Stage names are configuration, and somebody will rename Discovery to Qualified next spring. Store the identifier here and keep names in a separate stage dimension, so a report covering last March still renders the label that was in use then. Storing the display string instead is the most common mistake I find in a half-built history table, and it isn't repairable after the rename has happened.
from_stage_id is nullable on purpose. You often won't be told it, and the section after next explains what to do about that.
event_at and event_at_precision travel together. Rows from a webhook know the second the move happened. Rows written later by the reconciliation job can only say "sometime in the last 24 hours". Both are legitimate history, but mixing them without a flag turns time-in-stage into a number nobody can qualify, so velocity queries filter on precision and the report states which rows it used.
raw_payload keeps the original body. When a field mapping turns out to be wrong six months in, and it will, you re-derive from stored payloads instead of apologising for a gap.
Which fields come from the webhook, and which do you have to work out yourself?
Less than you'd hope arrives ready to insert.
A stage-change workflow trigger fires with the opportunity as it now stands. Broadly you can expect the opportunity identifier, the contact identifier, the pipeline, the sub-account or location, the current stage, the monetary value, the status, and a timestamp. Map those straight through.
Two of the columns above almost never arrive in usable form.
The previous stage is the first. A payload describing the record's current state has no reason to carry the value it replaced, so you derive it: look up the most recent stage_event for that opportunity_id, take its to_stage_id, and write that as this row's from_stage_id. With no prior row, write null and let it mean "first move we observed". That lookup is why the composite index on (opportunity_id, event_at) belongs in the schema from day one rather than being an optimisation somebody adds later.
The event time is the second. Prefer a timestamp generated by the platform over the moment your endpoint happened to run. A retried delivery arriving forty minutes late should still be stamped with when the deal moved, otherwise a queue backlog quietly rewrites your velocity numbers. If no trustworthy timestamp is in the body, use received_at for event_at and mark the precision accordingly rather than pretending you know.
Treat the field list above as structural rather than a menu you can copy today: verify at build time exactly which fields the GoHighLevel opportunity stage-change workflow webhook sends, whether the payload or headers include a delivery or event identifier suitable for deduplication, whether any previous-stage field exists, and what the retry policy is on a non-2xx response. Read those five specifics off the live docs the week you build, not off this article.
One more habit: take location_id from the payload, never from the endpoint URL. The day somebody clones a workflow into a second sub-account without changing the URL is the day every event gets filed under the wrong client.
How do you stop a retried delivery writing the row twice?
Assume every delivery arrives more than once. Networks time out after the receiver has already committed, senders retry on a slow response, and somebody will replay a batch by hand while debugging. A history table that inflates on retry is worse than none, because it looks fine.
The rule is one line of database behaviour: insert on the delivery identifier, and do nothing on conflict.
insert into stage_event (delivery_id, location_id, opportunity_id, ...)
values ($1, $2, $3, ...)
on conflict (delivery_id) do nothing
returning event_id;No rows returned means it was a duplicate. Log it as such and return 200. That last part gets argued about, so: the sender's question is "did you receive this", and the answer is yes. Return an error instead and you teach the retry loop to keep trying forever.
Three details around it.
Commit before you do anything else. Write the row, then queue the enrichment or the notification. An endpoint that transforms first and writes last loses events every time the transform throws.
Have a fallback key ready. If no usable delivery identifier exists in the payload or headers, hash the fields that define the business event, something like location_id + opportunity_id + to_stage_id + event_at rounded to the second, and make that the unique constraint instead. It's weaker, since a deal that genuinely moved to the same stage twice in one second is now indistinguishable from a duplicate, and that's a trade I'll take.
Keep the endpoint dumb. Validate the shape, resolve the previous stage, insert, respond. Anything needing an outbound API call belongs in a job reading the table afterwards, because every extra dependency in the request path is another way to drop history you can't get back.
What happens to the deliveries you didn't accept?
They go in a log, which is the part people skip and then regret.
webhook_delivery
delivery_id text primary key
location_id text
received_at timestamptz not null default now()
status text not null -- accepted | duplicate | rejected | failed
http_status int
attempt int not null default 1
error text
raw_body text not nullEvery request gets a row here before any parsing happens, which is what makes it useful. rejected covers a body you understood and refused: a failed signature check or an unknown location. failed covers everything that threw. Those failed rows are your dead-letter queue. It needs no separate infrastructure, just a query and someone who looks.
A small scheduled job re-reads failed rows, reparses them against the current mapping, and attempts the insert again. Fixed a bug in the mapping this morning? Replay the failures and the gap closes. Rows still failing after a set number of attempts get flagged and stop being retried, because a poison message retried forever is noise.
Then two alerts, and they matter more than the log does.
One on failures crossing a threshold. And the one that catches real problems, an alert on silence: any location that has sent no events for longer than its normal quiet period. Webhooks fail by not arriving, and a missing delivery generates an error nowhere. A sub-account where somebody detached the trigger looks exactly like a slow week, and it keeps looking like one for months.
How does the reconciliation against the scheduled pull actually work?
The event stream catches every move and fails silently. A scheduled full pull of the opportunity object never misses a day and can't see moves between runs. Running both is what makes the data defensible, an assertion I've made twice elsewhere without showing the query. Here it is.
The pull writes a snapshot table with a compound primary key of (opportunity_id, snapshot_date), carrying the current stage_id, pipeline, status and value, via an upsert -- INSERT ... ON CONFLICT (opportunity_id, snapshot_date) DO UPDATE. That key plus the upsert means a re-run of a failed job overwrites rather than duplicating, so reruns are always safe. A plain INSERT against the same key would just throw a duplicate-key error instead. Tomorrow writes tomorrow's rows.
Reconciliation is then one comparison per opportunity per day, between two figures that should agree. The expected stage comes from the event log: the to_stage_id of the latest stage_event for that opportunity with event_at at or before the snapshot cutoff. The observed stage is whatever today's snapshot row says.
Equal, and both systems saw the same reality. Unequal, and one of three things happened, each with its own repair.
Most often the log missed a move. Yesterday's snapshot says Booked Call, today's says Proposal, and no event exists between them. The repair writes a row into stage_event with source = 'reconciliation', from_stage_id taken from yesterday's snapshot, to_stage_id from today's, and event_at_precision = 'day'. You've recovered the transition and lost its timing, which beats a hole in the table by a wide margin.
Less often the log has a move the snapshot doesn't show. Usually the deal moved after the pull's cutoff, in which case tomorrow resolves it and no repair is needed. If it persists two days running, either the deal moved and moved back, or somebody is writing to the CRM through a path you don't know about. Worth reading either way.
Occasionally the opportunity vanishes from the snapshot entirely. Deleted, moved to another pipeline, or the pull truncated without complaining. That last possibility is why a row count on every pull belongs in the same log.
Every mismatch gets written to an exception table with the opportunity, the date, the two stages, the class, and what the repair did. Nobody reads it daily. It's what you open when a client disputes a number, and saying "the log missed four moves that week, here they are, here's the repair" is the difference between a correction and a credibility problem.
Watch the exception rate rather than the count. A steady trickle of day-precision repairs is the system doing its job. A rate climbing week on week means the webhook path is degrading, and you'd rather find that in a chart than in a meeting.
What does one contested week look like?
An illustration, invented for this article rather than drawn from a client account. The mechanics are the ones I build.
Deal 8812 moves from Booked Call to Proposal at 11:04 on a Thursday. The workflow fires, delivery d-9f21 lands, the endpoint finds the last recorded stage for 8812 and writes from_stage_id = booked_call, to_stage_id = proposal, event_at = 11:04, precision exact. At 11:06 the same delivery arrives again after a sender-side timeout. The conflict clause drops it, webhook_delivery records a duplicate, the endpoint returns 200.
At 15:40 the same deal moves to Negotiation. This time the endpoint is mid-deploy for ninety seconds and the delivery is never retried. Nothing errors anywhere.
At 02:00 the nightly pull writes 8812's snapshot row with stage_id = negotiation. Reconciliation compares that against the log's expected proposal, finds a mismatch of the first class, and writes a repair event from Proposal to Negotiation dated to the snapshot with day precision. The exception table gets a line.
Six weeks later the client asks how long deals sat in Proposal that week. The answer exists. For 8812 it's approximate and flagged as approximate, which is a sentence you can say out loud in a meeting. Without the reconciliation step, that deal never entered Proposal as far as any report is concerned, and the conversion rate you quote is wrong in a direction nobody can see.
What this build does not fix
Backfill, first. Reconciliation recovers moves from the day capture started, never before it. There is no historical stage data in the platform to pull.
Definitions, second. A clean event log built on stages that three account managers read differently gives you a precise measurement of a vague thing. Settle what each stage means before spending a fortnight on plumbing, an argument I make in where native reporting stops being enough.
And scale, third. If you run two clients and report monthly totals, this is more machinery than your problem deserves. An append-only tab fed by a scheduled pull, along the lines of the GHL to Google Sheets workflow, captures the same history less durably for far less effort. The database version earns its cost when reporting accuracy is something a client pays for, the data solutions end of my work, and its ingestion half is written up in exporting GoHighLevel data to Supabase.
The same append-only pattern extends past stages. Tagging contacts with the email variant they received and capturing that tag as an event, rather than an overwritable field, is the same build with a different payload -- covered from the testing side in how to call a real winner in a GoHighLevel A/B test.
Frequently asked questions
What columns should a GoHighLevel stage history table have?
A generated primary key, the delivery identifier as a unique constraint, location, opportunity and contact identifiers, pipeline, the stage moved from and the stage moved to as IDs rather than names, the event timestamp with a precision flag beside it, a received timestamp, a source field, and the raw payload. Append only. Nothing in that table should ever be updated.
How do I avoid duplicate rows from GoHighLevel webhook retries?
Put a unique constraint on the delivery identifier and insert with a do-nothing conflict clause, so a repeat writes nothing and still returns success. If the payload carries no usable delivery ID, hash the fields that define the event and key on that instead. Deduplicating in the reporting query afterwards is no substitute, because by then you can't tell a retry from a real repeated move.
What if my webhook endpoint is down for an hour?
Those events are gone, which is why the scheduled pull exists. The next reconciliation run compares the snapshot against the event log, finds opportunities whose stage changed with no matching event, and writes repair rows dated to the snapshot with reduced precision. You recover which moves happened and lose when, within a day.
How do I know the webhook is still firing?
Not from errors, because a webhook that stops firing produces none. Log every delivery attempt, then alert on silence per sub-account against that account's normal volume, with the reconciliation exception rate as a second signal. A trigger detached during a workflow edit is invisible in every other view you have.
Does the scheduled pull replace the webhook, or the other way round?
Neither, and running only one is the usual mistake. The event stream gives you resolution, including deals that move twice in an afternoon, and fails without telling you. The pull gives you a guarantee and misses whatever happened between runs. They fail in opposite directions, which is what lets each one check the other.
Find out whether this is the build you need
Some agencies who ask me for this schema need three of its columns and a scheduled export. Others have needed all of it since last year.
The agency CRM and reporting diagnostic is a paid, bounded first engagement that settles which. I go through what your account captures today, which of the reports you send couldn't be reproduced if challenged, whether accidental history already exists somewhere, and whether the honest answer is this build, a weekly snapshot, or starting capture now and revisiting in six months. You get the findings whether or not the work continues with me.
See how I work with performance-marketing agencies on CRM and reporting data, or start the conversation.
About the author. I'm Ahmed Abdelkhalek, a Data Automation and Reporting Consultant and the founder of ChromiumData, a founder-led consultancy. I work directly with marketing agencies and operations teams from diagnosis through delivery, mostly at the unglamorous end of reporting: CRM data that won't reconcile, integrations that break quietly a month after launch, dashboards nobody trusts. I hold Microsoft's PL-300 (Power BI Data Analyst Associate) and the AWS Certified Solutions Architect (Associate) certification.