How I built a production financial integration around QuickBooks and Stripe.
ClearWIP is a construction work-in-progress reporting SaaS built on QuickBooks Online data and billed through Stripe. This page walks through how it handles the problems that break financial integrations in the wild — expiring OAuth connections, duplicate records, unverifiable numbers, and cross-tenant leakage — at the architecture level.
ClearWIP is a product I founded, built, and operate. It is shown here as engineering proof, not as a client engagement.
One workflow, owned end to end
ClearWIP pulls job, invoice, bill, expense, and payment data from QuickBooks Online, layers user-entered estimates on top, and produces deterministic work-in-progress reports designed for contractors to hand to banks and bonding agents.
- The system boundary is explicit: synced QuickBooks records are immutable facts; user-entered assumptions are versioned inputs; calculated results live only in reproducible snapshots — three kinds of state, three different ownership rules.
- The scope is deliberately bounded: no payment collection, no ERP replacement, no side channels into the accounting system. The integration reads, reconciles, and reports; it does not quietly mutate the source of truth.
Why this design: most integration damage starts when one system writes into another without a clear ownership rule. Deciding which system owns which state — before writing sync code — is the difference between an integration and a slow-motion data corruption.
OAuth and token lifecycle
The most common way a QuickBooks integration dies in production is silently: a token expires, a company is switched, and the sync just stops. ClearWIP treats the connection itself as a stateful, audited object.
- Authorization-code flow with PKCE (S256). The OAuth state parameter is cryptographically random, stored server-side only as a SHA-256 hash, bound to the workspace, consumed on first use, and expires in minutes — the callback never trusts query parameters directly.
- Redirect URIs are exact-string matched against an environment allowlist; a callback with any other redirect target is rejected before processing.
- Refresh tokens are envelope-encrypted at rest: a cloud KMS master key wraps a per-workspace key-encryption key, which wraps a unique per-connection AES-256-GCM data key. No plaintext token ever touches disk or logs.
- If the key-management service is unreachable, sync fails closed — the system prefers unavailability over handling tokens without the encryption boundary.
- On a 401 from QuickBooks, the backend refreshes automatically; if refresh fails, the connection is marked as needing reauthorization, an audit event is emitted, and users see a reconnect call to action instead of a silent stall.
- QuickBooks refresh tokens expire after 100 days of inactivity, so the product warns users approaching that window rather than letting the connection die quietly.
- Reconnect and disconnect are modeled as explicit lifecycles: superseded connections keep their history, token revocation is attempted best-effort, and every transition is audited.
Sync and data provenance
Every synced record can answer: where did this come from, which company, which sync run, and who is allowed to change it.
- Synced QuickBooks data lands in eleven read-only fact tables that only the sync worker may write. Application code — and users — can never mutate a synced fact.
- Row identity is scoped by workspace, QuickBooks company (realm), and provider ID, because QuickBooks recycles provider IDs across companies. Reconnecting to a different company can therefore never mix two companies’ records.
- If a workspace reconnects to a different QuickBooks company, the prior company’s data is retained but excluded from every active surface — and restored intact if that company reconnects.
- User-entered WIP assumptions are versioned records that sync must never overwrite; any conflict resolves assumption-wins with an audit record.
- Every sync attempt is recorded as a run with status, entity counts, diagnostics, and error codes — visible to workspace admins on a sync-diagnostics page with a manual sync trigger.
Why this design: identity scoping by provider company is the kind of decision that looks pedantic until a reconnect to the wrong QuickBooks company silently merges two businesses’ financial records. The schema makes that failure structurally impossible instead of relying on application code to remember.
Background processing, retries, and idempotency
Financial sync work runs on a transactional job queue with the invariant that a retry can never create a duplicate financial record.
- Operations that trigger async work (sync, snapshot generation, report export) write a durable intent row, emit the audit event, and enqueue the job in one database transaction — the job exists if and only if the intent committed.
- If the process dies before the worker runs, a stuck-job rescue picks the work up on the next startup; the intent row is still pending, so the worker proceeds normally.
- Sync is idempotent, keyed on the sync run ID; retryable creates and provider-triggering commands require idempotency keys at both the wire and service layers.
- Provider calls are bounded: one HTTP client per provider with per-request deadlines — no unbounded default client anywhere.
- When the provider degrades (repeated 5xx or timeouts), sync halts, the connection is marked failed, users see a provider-error banner, and retries back off exponentially — a temporary provider error is never allowed to masquerade as a data failure.
Deterministic calculation and reconciliation
Numbers that reach a bank or bonding agent must be reproducible. ClearWIP treats every reported figure as something that can be re-derived, verified, and explained.
- The calculation engine is deterministic: the same inputs at the same calculation version must produce byte-identical outputs, enforced by committed test fixtures the engine must reproduce exactly.
- Every snapshot stores its normalized inputs and a SHA-256 input hash; locked financial snapshots are immutable and append-only. Later syncs cannot silently change a locked period.
- An integrity check can re-run the calculation against a locked snapshot’s inputs and compare against the stored outputs — divergence is detectable, not assumed away.
- Any change to the output shape must bump the calculation version in the same change, with a version-gated serializer reproducing every prior version byte-for-byte, pinned by a frozen golden-byte fixture.
- Every calculated result carries an explanation payload recording each formula input and intermediate value, so a user can verify how a number was derived.
- Report exports always identify whether values came from current data or a locked snapshot — the two are never silently mixed.
Why this design: reconciliation is only possible when inputs are captured, normalized, and hashed at calculation time. A system that cannot re-derive its own numbers cannot tell you which of two disagreeing systems is right — which is exactly the situation most integration-repair engagements start from.
Stripe subscriptions and plan enforcement
The billing integration follows the same discipline as the accounting one: idempotent writes, explicit webhook failure classes, and enforcement that never silently drops user work.
- Subscription state is tracked per workspace against Stripe, with plan tiers, a no-card trial, and entitlement records for per-workspace overrides.
- Plan limits are service-enforced; frontend checks are advisory only. Usage limits are soft by design — progressive warning banners start at 80% — and the one hard block fires on the billing action itself, never by silently dropping user work.
- Stripe writes use workspace-derived idempotency keys, so retries are safe.
- Webhook handling distinguishes failure classes: an invalid signature is logged and never retried; a valid-signature handler failure returns 500 so Stripe retries; payment lapses get a grace period before enforcement.
Multi-tenant security boundaries
Financial data for many businesses in one database demands more than a WHERE clause. Tenant isolation is enforced at four layers: middleware, query predicates, database constraints, and row-level security.
- The workspace is the tenant boundary. Every workspace-scoped query carries a verified workspace predicate derived from middleware-checked membership — never from raw request data or a JWT claim.
- Cross-workspace foreign keys are impossible at the database layer via composite tenant keys, and every route slice requires a wrong-workspace test before it ships.
- Postgres row-level security runs underneath as defense in depth: the API connects as a non-bypass role whose connections are scoped to the request’s workspace on acquire and reset on release, so even a query that escaped the application boundary returns zero foreign rows.
- Background workers use a separate database role for legitimate cross-workspace discovery, with explicit per-workspace predicates in loop bodies.
- A membership mismatch returns 404, not 403 — resource existence is not leaked across tenants.
Deployment, monitoring, backups, and recovery
An integration is only as trustworthy as the operational floor underneath it.
- Infrastructure is declarative (Terraform) on Google Cloud, with release pipelines, health-gated rollouts, and a written, tested rollback runbook. Database migrations are forward-only, so schema changes follow expand/contract discipline and code rollbacks stay safe.
- Observability is structured logging plus OpenTelemetry traces and metrics; warning-and-above log records carry request and workspace identifiers, and raw provider messages are never logged.
- Postgres runs with point-in-time recovery (WAL archiving) plus daily encrypted snapshots, restore procedures tested quarterly against explicit RTO/RPO targets.
- Auth, role, provider, billing, snapshot, export, and settings changes emit append-only audit events transactionally with the state change they describe, under a 365-day retention floor with a locked compliance archive.
- Public surfaces are rate-limited per IP and per token, and any outbound URL passes an SSRF validator that rejects private, loopback, and cloud-metadata destinations.
What this proves — and what it does not
Proof should be exactly as strong as it is, and no stronger.
What it proves
- End-to-end design, implementation, and operation of a production financial integration: OAuth lifecycle, sync, provenance, reconciliation, billing, tenancy, and operations as one coherent system.
- The specific failure classes integration repair work deals with — expired tokens, duplicate records, out-of-order state, unverifiable numbers, cross-tenant risk — addressed by design rather than by patching after the fact.
- Judgment about where correctness must be absolute (financial snapshots, tenant boundaries) versus where soft handling is right (usage limits, provider degradation).
What it does not prove
- It is not a client case study. No external client paid for this system, and it is not evidence of a completed repair engagement for someone else’s codebase.
- It does not prove familiarity with your specific stack or provider mix — that is what a fit call establishes.
- It does not claim commercial traction for ClearWIP itself; it is offered here strictly as engineering proof.
Have an integration that no longer adds up?
Tell me what is failing, which systems disagree, and what “fixed” needs to mean. A 15-minute call establishes whether it fits, with a clear yes or no.