HubSpot and Stripe are the two integrations we install most often. Connecting either one in isolation is straightforward. Connecting them together so you can see booked, billed, collected on a single tile is where the surprises hide.

This article documents the schema we standardised on, the two reconciliation patterns we use, and the failure modes we have learned to plan around.

The core question

Almost every dashboard with HubSpot and Stripe is trying to answer one variant of the same question. What is the difference between what we sold and what we collected, and where did it slip.

That is harder than it sounds because deals do not become invoices, and invoices do not become payments, in any one-to-one way. A single deal can spawn many invoices, a single invoice can be split across many payments, and refunds can travel back across all of them.

The schema

We land both systems into a warehouse with three normalised tables and one denormalised view. Names below use our default vocabulary. Most teams rename them in week one.

-- HubSpot side
deal (id, name, owner_id, stage, amount, expected_close_at, ...)
contact (id, email, company_id, ...)
company (id, name, vat_id, ...)

-- Stripe side
invoice (id, customer_id, total, currency, status, issued_at, ...)
payment (id, invoice_id, amount, status, captured_at, ...)
refund (id, payment_id, amount, reason, refunded_at, ...)

-- Bridge (the interesting part)
deal_to_invoice (deal_id, invoice_id, confidence, matched_at, matched_by)

Everything else is downstream of this. The view layer that powers tiles always joins through deal_to_invoice, never directly between deal and invoice.

How we match deals to invoices

Two patterns, in order of preference.

1. The deterministic match

Both systems share a clean external ID. Usually a custom property on the HubSpot deal that holds the Stripe invoice ID, written when the deal moves to Closed Won. If you have this, it is the only signal we trust. Confidence 1.0.

2. The fuzzy match

No shared ID. We match on three weak signals together: company name (Levenshtein distance under three), invoice total within five percent of deal amount, and time of close within thirty days of invoice issue. All three must agree.

A fuzzy match writes a confidence between 0.6 and 0.95. Anything below 0.6 goes to a manual review queue. The reviewer is usually the salesperson who closed the deal, because they will recognise the customer name instantly.

The reconciliation tile

Once the bridge is populated, the dashboard usually grows a tile titled Sold vs collected. It is a single sentence: This quarter, you sold X, invoiced Y, collected Z. The gap is owed to N customers. Behind it sits a drilldown with the offending invoices.

Almost every finance team we have shipped this for has spotted a leak in week two. Usually a few invoices that never went out, or a refund nobody noticed.

Failure modes we plan around

  • Multi-currency.Stripe stores money in cents in the original currency. HubSpot stores deal amount in the portal's default. We always store a third column: amount in the company's reporting currency, with the FX rate and date frozen at the moment of the match.
  • Deleted contacts. A HubSpot user merging two contacts deletes one. We snapshot the deal-contact link on every sync so a merge does not silently break our join.
  • Stripe metadata drift. Teams add metadata keys on Stripe invoices over time. We pull all metadata into a JSONB column and surface the ones that exceed five percent fill rate as candidates for first-class fields.
  • Webhook gaps. Webhooks miss events. Always back them up with a daily reconciliation pull. Trust the pull, treat the webhook as a freshness hint.

How long this takes us

On a clean account, day two of the seven-day build. On a messy account (multiple Stripe accounts, multiple HubSpot pipelines, historical data older than the schema), it can stretch into day three. We tell you which one you have by the end of the discovery call.