Complex integration design
Proposed architecture · client workflow to be documented
A received payment needs to reach the right record.
It started with…
The Stripe payment, Airtable record and Sage customer may use different identifiers. Receiving the same event twice must not create two operations.
The proposed workflow
An orchestration that verifies the event, resolves identifiers, checks business rules, calls Sage through an appropriate adapter and tracks the result in Airtable.
- Stripe
Verified event
Protected receiver and transactional ledger
- Zapier
Coordination
Airtable for tracking, code for complex rules
- Sage
Action through an adapter
Result reference or an exception to handle
Design diagram to adapt to the actual Zap and Sage edition.
What we set out to change
Avoid duplicate processing and make each payment traceable to the intended business outcome.
How it is builtData model, code, controls and team ownership
The user journey
- 01
Verify the Stripe event and retain its key.
- 02
Find the record and prepare a validated business request.
- 03
Execute the authorised Sage action, then record its reference or exception.
Data and hand-off structure
| Entity / table | Key fields | Relationships |
|---|---|---|
| Event ledger | Provider · event ID · state · payload hash | Unique receipt key; not an Airtable find-then-create lock |
| Business reference | Customer · order · currency · legal entity | Map identifiers between systems |
| Operation ledger | Business key · destination reference · outcome | Track attempts independently of the incoming event |
Technical implementation
- Verify the signature against the raw body in the webhook receiver.
- Reserve keys in a transactional store: Airtable find-then-create does not prevent concurrent arrivals.
- After a Sage timeout, reconcile the destination before retrying. Event deduplication alone does not guarantee one remote write.
Engineering decision
Separate event deduplication from business-operation ownership
Proposed design, not reviewed client code. One database transaction stores the verified event, reserves the business operation and creates an outbox message. UNIQUE constraints handle concurrent deliveries; a conflict must not roll back the other transaction’s work. The ledger/outbox adapters shown as contracts still need implementation. Sage execution and reconciliation are separate workers; schema version is metadata, not a reason to book the same payment again.
type VerifiedPayment = { accountId: string; live: boolean; eventId: string; paymentId: string; legalEntityId: string;};type Transaction = { insertEventIfAbsent(key: string): Promise<boolean>; reserveOperationIfAbsent(key: string): Promise<boolean>; enqueueOutbox(message: { key: string; schemaVersion: 1 }): Promise<void>;};type Ledger = { transaction<T>(fn: (tx: Transaction) => Promise<T>): Promise<T>;};export async function acceptPayment(event: VerifiedPayment, ledger: Ledger) { // Caller verified the raw Stripe signature and resolved the tenant mapping. const namespace = [event.accountId, event.live, event.legalEntityId]; const eventKey = JSON.stringify([...namespace, event.eventId]); const operationKey = JSON.stringify([ ...namespace, event.paymentId, "record-payment", ]); return ledger.transaction(async tx => { if (!await tx.insertEventIfAbsent(eventKey)) return "duplicate-event"; if (!await tx.reserveOperationIfAbsent(operationKey)) { return "operation-already-reserved"; } await tx.enqueueOutbox({ key: operationKey, schemaVersion: 1 }); return "queued"; });}// All three adapters share one durable DB transaction + UNIQUE keys.// Acknowledge the webhook only after commit; an outbox worker invokes Zapier.// No Sage write here. Unknown remote outcomes go to reconciliation first.Adapted implementation excerpt
Controls and boundaries
- Keep card data out of Airtable; retain only required references and business information.
- Sensitive decisions and writes require explicit permissions and approval rules.
- The Sage connector and recovery capabilities depend on the chosen edition and plan.
Who can contribute
- Operations: reference data, approved rules and exception handling.
- Builders: documented Zapier steps and mappings within their agreed scope.
- Developers: API contracts, custom connectors, tests and versioning.
What supports this case
Architecture example developed to explain the approach. No client Zap export or run history has yet been reviewed; no production outcome is claimed.