# Integration spec: billing_to_crm (fictional example)

Acme Analytics is fictional. This is the redesign of the agency's Stripe to HubSpot billing flow, written from [integration-spec-template.md](integration-spec-template.md). Its inventory row and field map are in [example-acme-redesign-inventory.csv](example-acme-redesign-inventory.csv) and [example-acme-redesign-field-map.csv](example-acme-redesign-field-map.csv). Status stays draft until the approvers sign.

## Header

- Flow ID: billing_to_crm, version 2, status draft
- Mode: Design (replaces the agency's version 1 after the review)
- Tier: 1, because the billing fields start renewal tasks for customer success with a response target
- Business owner: Head of Customer Success. Technical owner: RevOps engineer. On-call: RevOps on-call rota
- Date: 2026-11-01. Approvers: Head of Customer Success, RevOps lead, finance systems lead

## Decision

- Sentence: Stripe subscription state becomes a billing summary on the HubSpot company so that customer success can open renewal and payment-risk work on the right account.
- Who acts on it: customer success managers, through the HubSpot workflow "Renewal task 60 days out" and the past-due view.
- If the data is stale or wrong: a renewal call is missed, a past-due account is treated as healthy, or a churned account gets renewal outreach.

## Method

- Chosen: custom (receiver, queue and worker on a managed runtime), pattern card "Billing state to CRM account summary".
- Simpler methods and the clause each failed:
  - Native connector: cannot aggregate several subscriptions per account into one summary or reject a stale version (MP01).
  - iPaaS: cannot serialise writes per account or compare an integration revision before writing; the version check would need code steps the operations team cannot maintain (MP02).
  - Event pipeline: the destination needs current state, not events (MP03).
  - Warehouse plus reverse ETL: a 15-minute target is below what load, model build and sync interval allow here (MP04).

## Systems and objects

- Source: Stripe live mode, subscriptions and customers; API version pinned at build and recorded in the deploy log. Sandbox for tests.
- Destination: HubSpot Professional, companies; API paths dated 2026-09; private app `acme-billing-sync`.
- Environments: Stripe sandbox and the HubSpot test account for tests; production only after the pilot read-back passes.

## Trigger

- What starts a run: a Stripe event destination subscribed to customer.subscription.created, customer.subscription.updated and customer.subscription.deleted, plus invoice.payment_failed and invoice.paid. The worker refetches current state on every event, so it does not depend on the event payload; check at build time which of these types the account offers as thin events, which Stripe recommends for new integrations.
- What does not start a run: HubSpot changes, including the worker's own writes. The worker never subscribes to HubSpot company changes.

## Identity

- Match key: `acme_account_id` from Stripe customer metadata, stored in the HubSpot company property `acme_account_id`, created as a unique value property (hasUniqueValue true; HubSpot allows at most ten per object).
- Crosswalk: warehouse table `crosswalk_billing_accounts` with stripe_customer_id, acme_account_id, hubspot_company_id, status (matched, ambiguous, unmatched, merged, deleted), first_seen, last_verified.
- Two candidate matches: no write; exception to customer success operations with both company IDs.
- No match: no company is created (this flow does not own company creation); exception to customer success operations.
- Normalisation: `acme_account_id` is seven-digit text with leading zeros, compared exactly; never cast to a number or lowercased.
- Merges and deletes: when two HubSpot companies merge, the crosswalk moves to the survivor before the next write. A Stripe customer deletion marks the crosswalk row deleted and stops writes for it.

## Field ownership

- Field map: [example-acme-redesign-field-map.csv](example-acme-redesign-field-map.csv). Stripe owns every billing field; people do not edit them (decision of 2026-06-02).
- Conflict rule: version. `billing_state_revision` is integration-owned and increments when the reconciled summary changes; a queued write whose revision is at or below the stored one is dropped.
- Writes carry only the seven owned fields and are skipped when nothing changed, which also prevents loops.
- Renewal date: billing owns `next_renewal_date` once decision D-1 is confirmed, and the HubSpot Salesforce connector mapping for it becomes one-way into Salesforce (CR-3). Until then the flow keeps writing, the review's FO01 finding stays open, and the field map lists the connector under `other_writers`, so this spec's lint shows FO02 WARN: the connector does not compare `billing_state_revision`.

## Values

- `billing_status`: worst state across active subscriptions in the order past_due, active, trialing, canceled, none. Unknown Stripe statuses go to the exception queue, never to a default.
- `mrr_amount`: recurring prices normalised to a month, excluding tax and one-time charges, in the account's currency in `mrr_currency`. Accounts with subscriptions in two currencies go to the exception queue.
- `next_renewal_date`: earliest upcoming current_period_end across active subscriptions, as the UTC calendar date, sent as YYYY-MM-DD (HubSpot date properties take YYYY-MM-DD or epoch milliseconds at midnight UTC).
- `billing_observed_at`: the Stripe event created time used for the summary; stored apart from the time the worker wrote it.

## Removals

- No active subscription left: `billing_status` none, `active_subscription_count` 0, `mrr_amount` 0, and `next_renewal_date` cleared by an explicit clear. A failed Stripe fetch never clears anything.
- Unsubscribe, audience exit: not applicable to this flow.
- Delete or erasure: the crosswalk row is marked deleted; the privacy owner's erasure process removes the company fields.
- Minimum-population guard: a run that would clear billing fields on more than 10% of companies or more than 1,000 companies stops for approval.

## Freshness

- Target: 15 minutes from the Stripe event to the HubSpot update.
- Measured as: Stripe event created time to receipt (sender delay), receipt to worker completion (processing), and the HubSpot response time (acceptance), each logged per event.
- Late events: an older event is stored for history; the worker always refetches the account's current subscriptions, so it cannot restore an obsolete state.

## Idempotency

- Delivery key: Stripe event ID, stored with a unique constraint in the receipt table.
- Business effect key: billing_to_crm + acme_account_id + billing_state_revision. A second Event object for the same change refetches the same state, computes the same revision and is skipped as a no-op; the worker never drops events by data.object.id plus type.
- Ledger: Postgres table `billing_effects` with a unique constraint on the effect key; retention 35 days, longer than Stripe's 30-day CLI resend window.
- Claim: insert the effect row before the HubSpot write; a unique-constraint violation means the effect is already claimed.
- Unknown outcome: after a timeout, the worker reads the company by `acme_account_id` and compares the revision before writing again.

## Ordering

- Rule: refetch the account's subscriptions from Stripe on every event, then compare revisions before writing.
- Serialisation: queue partitioned by `acme_account_id`, so one account is processed by one worker at a time.

## Retries and errors

- Error classes: transient (5xx, timeouts, connection resets), quota (HubSpot 429, read policyName from the body; Stripe 429 with Stripe-Rate-Limited-Reason), permanent (400, 404, validation errors), unknown outcome (timeout after send), hidden failure (per-record errors inside a 2xx batch response during backfill).
- Backoff: exponential with full jitter, base 1 s, factor 2, cap 60 s, at most 6 attempts, then the exception queue.
- Retry layers: worker 6; Stripe SDK automatic retries set to 0 and the HubSpot client's retries off, so the product of nested attempts is 6. A HubSpot 429 with the daily policy pauses the worker until the reset.
- Retry-After: read in seconds when present (RFC 9110); otherwise wait for the next 10-second window.
- Exception queue: table `billing_exceptions`, owner customer success operations, re-drive at 15 minutes, 1 hour and 6 hours, reviewed daily.

## Receivers

- Signature: Stripe-Signature v1 only, verified with Stripe's library on the raw body; tolerance the library default of 5 minutes (never 0); secrets rolled with Stripe's overlap window.
- Tenant check: the event's Stripe account must be Acme's live account; anything else is rejected and logged without payload.
- Durable receipt, then 2xx. Target acknowledgement p99 0.5 s. Stripe's webhook page read on 2026-11-01 gives no timeout figure, so the lint WARN on WH01 stays until the sandbox measurement is recorded.
- Unknown event types: acknowledged and stored, not processed.
- Malformed payloads: stored and quarantined in `billing_exceptions`.

## Rate budget

- Initial backfill, 48,000 companies by batch upsert on `acme_account_id` plus one batch read-back per batch:
  - `python3 scripts/size_sync.py --records 48000 --batch 100 --readback-calls-per-batch 1 --preset hubspot-private-pro --remaining 600000 --reserve 0.2 --other-calls 50000`
  - 1,056 calls, about 79 seconds at 13.30/s, 0.17% of 625,000; available 425,000; PASS RL01, PASS RL02.
- Normal day, about 600 events written one company at a time:
  - `python3 scripts/size_sync.py --records 600 --batch 1 --preset hubspot-private-pro --remaining 600000 --other-calls 50000`
  - 660 calls, 0.11% of the daily allowance; PASS.
- Replay after three days down (Stripe's live retry window), 1,800 events re-driven in batches of 100:
  - `python3 scripts/size_sync.py --records 1800 --batch 100 --readback-calls-per-batch 1 --preset hubspot-private-pro --remaining 600000 --other-calls 50000`
  - 40 calls; PASS.
- Preset hubspot-private-pro, checked 2026-11-01, source https://developers.hubspot.com/docs/developer-tooling/platform/usage-guidelines; the daily limit resets at midnight in the account time zone (America/New_York).
- Stripe reads: one subscription list per event (about 18,000 a month) sits far inside Stripe's read allocation; the backfill lists subscriptions 100 per page.

## Backfill and recovery

- Pilot: 100 accounts, including a multi-subscription account, a trial, a past-due account, a canceled account, a mixed-currency account, an account with no HubSpot match and one with two matches. Listed in `pilot_billing_accounts.csv`.
- Batches: 100 companies per batch upsert; checkpoint after each batch is durably written and read back.
- Holds: the HubSpot workflow "Renewal task 60 days out" is turned off for the backfill window and turned back on after the read-back passes; no renewal tasks may be created for historical states.
- Recovery sources: Stripe resend (Dashboard up to 15 days, CLI up to 30 days; a resend does not cancel automatic retries), Stripe list endpoints for current state, and the receipt table.

## Alerts and runbook

- Oldest unprocessed event older than 15 minutes.
- Any exception (tier 1).
- Event volume under 50% or over 200% of the median of the same weekday over the previous 4 weeks, when that median is 20 or more.
- HubSpot daily usage above 80% of 625,000.
- Stripe event destination disabled, or delivery failures in the Stripe Workbench.
- Credentials expiring within 14 days.
- Daily read-back drift above 0.5% of companies, or any billing_status mismatch.
- Runbook: `runbooks/billing_to_crm.md`, written from [runbook-template.md](runbook-template.md).

## Access

- Integration identities: Stripe restricted key for the runtime with read access to customers and subscriptions (not the agent key used for investigation); HubSpot private app `acme-billing-sync` with company read and write scopes.
- Secret location: the runtime's secret manager; rotation every 90 days, owner RevOps engineer.
- Versions: HubSpot 2026-09 paths; Stripe API version pinned at build; both on the deprecation calendar.

## Privacy and consent

- Personal data: none mapped; the flow writes account-level billing fields only.
- Logs and alerts: event IDs, account IDs and counts only, no payloads.
- Retention: receipts and ledger rows 35 days; exceptions until resolved.

## Tests

- Test plan: every core case plus signature_invalid, unknown_event_type, malformed_payload, partial_batch_failure, removal, backfill_no_outreach, replay_no_duplicate, credential_revoked and loop_check, and the manual permission_check on the private app's scopes, each with evidence at source, transport, destination and downstream automation.
- Read-back: `reconcile_readback.py` on the pilot with `--baseline`; exit code 0, with every owned field listed under Owned fields compared, is required before the full backfill.

## Open decisions

- D-1: who owns the renewal date. Option A: billing owns `next_renewal_date` and the connector maps it one-way into Salesforce. Option B: Salesforce owns a separate contracted renewal field and billing keeps the period end. Owner: RevOps lead, needed by 2026-11-08.
- D-2: whether accounts with two currencies get one summary per currency. Owner: finance systems lead.
- Owed before go-live: sandbox runs of every test case with evidence, and the pilot read-back. This spec is a design, so none has run yet. Owner: RevOps engineer, before the full backfill.

## Approval

- Head of Customer Success: pending
- RevOps lead: pending
- Finance systems lead: pending