Payment reconciliation

Store these fields when you create a payment (hosted session or API) to enable webhook matching:

1
CREATE TABLE payments (
2
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
3
  session_id VARCHAR(255) UNIQUE NULL, -- NULL for API-integrated payments
4
  payment_id VARCHAR(255) UNIQUE NULL, -- Set when first webhook arrives
5
  client_customer_ref VARCHAR(255) NULL, -- NULL for non-third-party payments
6
  client_reference_id VARCHAR(100) UNIQUE NOT NULL, -- Required for third-party, recommended for all
7
  payment_type ENUM('HOSTED_SESSION', 'API_THIRD_PARTY', 'API_NON_THIRD_PARTY') NOT NULL,
8
  session_url TEXT NULL, -- Only for hosted sessions
9
  expires_at TIMESTAMP NULL, -- Only for hosted sessions
10
  status VARCHAR(50) DEFAULT 'CREATED',
11
  amount_currency VARCHAR(3) NOT NULL,
12
  amount_value DECIMAL(19, 2) NOT NULL,
13
  purpose_of_transaction VARCHAR(50) NOT NULL,
14
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
15
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
16
  INDEX idx_client_customer_ref (client_customer_ref),
17
  INDEX idx_client_reference_id (client_reference_id),
18
  INDEX idx_payment_id (payment_id),
19
  INDEX idx_session_id (session_id),
20
  INDEX idx_payment_type_status (payment_type, status, created_at)
21
);
22
23
CREATE TABLE payment_items (
24
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
25
  payment_id BIGINT NOT NULL, -- Reference to payments.id
26
  session_id VARCHAR(255) NULL, -- For hosted sessions only
27
  item_name VARCHAR(100) NOT NULL,
28
  item_ref VARCHAR(50),
29
  item_type VARCHAR(50),
30
  amount_currency VARCHAR(3) NOT NULL,
31
  amount_value DECIMAL(19, 2) NOT NULL,
32
  FOREIGN KEY (payment_id) REFERENCES payments(id),
33
  INDEX idx_payment_id (payment_id),
34
  INDEX idx_session_id (session_id),
35
  INDEX idx_item_ref (item_ref)
36
);
37
38
CREATE TABLE webhook_events (
39
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
40
  event_id VARCHAR(255) UNIQUE NOT NULL,
41
  payment_id VARCHAR(255) NOT NULL,
42
  status VARCHAR(50) NOT NULL,
43
  payload JSON NOT NULL,
44
  received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
45
  processed BOOLEAN DEFAULT FALSE,
46
  INDEX idx_payment_id (payment_id),
47
  INDEX idx_event_id (event_id)
48
);

The diagram above shows the FX (different-currency) flow: AWAITING_FUNDS → RECEIVED_FUNDS → FX_COMPLETED → PAYOUT_INITIATED → PAYOUT_CREDITED → PAYMENT_COMPLETED. For same-currency payments (no FX), the first webhook is PROCESSING (not AWAITING_FUNDS), then PROCESSING → PAYOUT_INITIATED → PAYOUT_CREDITED → PAYMENT_COMPLETED (or PROCESSING → CANCELLED).

The reconciliation flow consists of six main stages:

1. Session Creation

• Your system creates a hosted payment session via API (v2)
• Store session_id, client_reference_id, client_customer_ref, and all items immediately
• Each item can have an item_ref for granular reconciliation (invoice numbers, property IDs, booking references, etc.)
• Redirect customer to the session_url to complete payment

2. First Webhook (AWAITING_FUNDS or PROCESSING)

• FX payments: AWAITING_FUNDS — contains payment_id for the first time, along with client_reference_id. Match to your session using client_reference_id, map payment_id to session_id, update status to AWAITING_FUNDS.
• Same-currency payments: PROCESSING — sent immediately after payment creation. Contains payment_id, customer_id, and (when provided) client_reference_id and client_customer_ref. Map payment_id to your record and update status to PROCESSING.

3. Status Updates

• Receive webhooks for RECEIVED_FUNDS, FX_COMPLETED, PAYOUT_INITIATED, PAYOUT_CREDITED
• Update status in your database at each milestone
• Store relevant data (amounts, conversion rates, recipient IDs)

4. Multi-Recipient Logic

• For single-recipient payments, PAYOUT_CREDITED is followed by PAYMENT_COMPLETED
• For multi-recipient payments, track each recipient_id status separately
• PAYMENT_COMPLETED fires only when all recipients are paid

5. Completion

• PAYMENT_COMPLETED webhook indicates full payment success
• Mark the payment as fully reconciled
• Trigger any post-payment workflows (notifications, accounting updates)

6. Error Handling

• CANCELLED: Payment cancelled before or during processing
• REFUNDED: Funds refunded to customer after receipt
• BOUNCED_BACK: Payout failed and funds returned
• Log errors, notify operations team, handle customer communication

When: Session created, waiting for customer to send funds

Webhook contains:

• payment_id (first time this ID appears)
• client_reference_id (your unique payment reference - use this to match!)
• client_customer_ref (your customer identifier)
• data: (empty)

Action:

1. Match the webhook to your session using client_reference_id
2. Store the payment_id and link it to your session_id
3. Update status to AWAITING_FUNDS
4. Display “Payment initiated, awaiting funds” to customer

1
{
2
  "event_id": "evt_1234567890",
3
  "payment_id": "pay_abcdef123456",
4
  "status": "AWAITING_FUNDS",
5
  "customer_id": "0201001008132685",
6
  "client_customer_ref": "CUST-1001-TXN",
7
  "client_reference_id": "PAY-2025-08-15-001",
8
  "event_timestamp": "2025-12-02T10:30:00Z",
9
  "data": {}
10
}

When: Same-currency payment accepted (no FX); sent immediately after payment creation

Webhook contains:

• payment_id (first time this ID appears for same-currency payments)
• client_reference_id (when provided)
• client_customer_ref (when provided)
• data: (empty)

Action:

1. Match the webhook to your payment record using client_reference_id or payment_id
2. Store or link the payment_id to your record
3. Update status to PROCESSING
4. Expect next webhooks: PAYOUT_INITIATED → PAYOUT_CREDITED → PAYMENT_COMPLETED

1
{
2
  "event_id": "evt_1234567890",
3
  "payment_id": "pay_abcdef123456",
4
  "status": "PROCESSING",
5
  "customer_id": "0201001008132685",
6
  "client_customer_ref": "CUST-1001-TXN",
7
  "client_reference_id": "PAY-2025-08-15-001",
8
  "event_timestamp": "2025-12-02T10:30:00Z",
9
  "data": {}
10
}

When: Customer’s funds received in Redpin wallet

Webhook contains:

• payment_id
• data.amount.currency: Currency of funds received
• data.amount.value: Amount received

Action:

1. Find payment by payment_id in your database
2. Update status to RECEIVED_FUNDS
3. Store amount received for reconciliation
4. Display “Funds received, processing conversion” to customer

1
{
2
  "event_id": "evt_1234567891",
3
  "payment_id": "pay_abcdef123456",
4
  "status": "RECEIVED_FUNDS",
5
  "customer_id": "0201001008132685",
6
  "client_customer_ref": "CUST-1001-TXN",
7
  "client_reference_id": "PAY-2025-08-15-001",
8
  "event_timestamp": "2025-12-02T10:35:00Z",
9
  "data": {
10
    "amount": {
11
      "currency": "GBP",
12
      "value": 1000.00
13
    }
14
  }
15
}

When: Currency conversion completed

Webhook contains:

• payment_id
• data.sell_amount: Source currency and amount
• data.buy_amount: Destination currency and amount
• data.quote_rate: Exchange rate used

Action:

1. Find payment by payment_id
2. Update status to FX_COMPLETED
3. Store conversion details for audit trail
4. Display “Conversion complete, initiating payout” to customer

1
{
2
  "event_id": "evt_1234567892",
3
  "payment_id": "pay_abcdef123456",
4
  "status": "FX_COMPLETED",
5
  "customer_id": "0201001008132685",
6
  "client_customer_ref": "CUST-1001-TXN",
7
  "client_reference_id": "PAY-2025-08-15-001",
8
  "event_timestamp": "2025-12-02T10:36:00Z",
9
  "data": {
10
    "sell_amount": {
11
      "currency": "GBP",
12
      "value": 1000.00
13
    },
14
    "buy_amount": {
15
      "currency": "AED",
16
      "value": 4982.70
17
    },
18
    "quote_rate": 4.9827
19
  }
20
}

When: Transfer to recipient started

Webhook contains:

• payment_id
• data.amount: Amount being sent to recipient
• data.recipient_id: Unique identifier for the recipient

Action:

1. Find payment by payment_id
2. Update status to PAYOUT_INITIATED
3. Track recipient_id (especially important for multi-recipient payments)
4. Display “Payout initiated to recipient” to customer

1
{
2
  "event_id": "evt_1234567893",
3
  "payment_id": "pay_abcdef123456",
4
  "status": "PAYOUT_INITIATED",
5
  "customer_id": "0201001008132685",
6
  "client_customer_ref": "CUST-1001-TXN",
7
  "client_reference_id": "PAY-2025-08-15-001",
8
  "event_timestamp": "2025-12-02T10:40:00Z",
9
  "data": {
10
    "amount": {
11
      "currency": "AED",
12
      "value": 4982.70
13
    },
14
    "recipient_id": "162345"
15
  }
16
}

When: Recipient’s bank account credited

Webhook contains:

• payment_id
• data.amount: Amount credited to recipient
• data.recipient_id: Unique identifier for the recipient

Action:

1. Find payment by payment_id
2. Mark this specific recipient_id as PAID
3. If multiple recipients, check if all are paid
4. If single recipient, wait for PAYMENT_COMPLETED
5. Display “Funds delivered to recipient” to customer

1
{
2
  "event_id": "evt_1234567894",
3
  "payment_id": "pay_abcdef123456",
4
  "status": "PAYOUT_CREDITED",
5
  "customer_id": "0201001008132685",
6
  "client_customer_ref": "CUST-1001-TXN",
7
  "client_reference_id": "PAY-2025-08-15-001",
8
  "event_timestamp": "2025-12-02T10:45:00Z",
9
  "data": {
10
    "amount": {
11
      "currency": "AED",
12
      "value": 4982.70
13
    },
14
    "recipient_id": "162345"
15
  }
16
}

When: All recipients paid, payment fully complete

Webhook contains:

• payment_id
• data.recipient_details: Array of all recipients with amounts and IDs

Action:

1. Find payment by payment_id
2. Update status to PAYMENT_COMPLETED
3. Mark as fully reconciled in your system
4. Display “Payment complete” to customer
5. Trigger post-payment workflows (accounting, notifications)

1
{
2
  "event_id": "evt_1234567895",
3
  "payment_id": "pay_abcdef123456",
4
  "status": "PAYMENT_COMPLETED",
5
  "customer_id": "0201001008132685",
6
  "client_customer_ref": "CUST-1001-TXN",
7
  "client_reference_id": "PAY-2025-08-15-001",
8
  "event_timestamp": "2025-12-02T10:46:00Z",
9
  "data": {
10
    "recipient_details": [
11
      {
12
        "amount": {
13
          "currency": "AED",
14
          "value": 4982.70
15
        },
16
        "recipient_id": "162345"
17
      }
18
    ]
19
  }
20
}

Maintain a separate table to track individual recipient status:

1
CREATE TABLE payment_recipients (
2
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
3
  payment_id VARCHAR(255) NOT NULL,
4
  recipient_id VARCHAR(255) NOT NULL,
5
  amount_currency VARCHAR(3) NOT NULL,
6
  amount_value DECIMAL(19, 2) NOT NULL,
7
  status VARCHAR(50) DEFAULT 'PENDING',
8
  payout_initiated_at TIMESTAMP,
9
  payout_credited_at TIMESTAMP,
10
  UNIQUE KEY (payment_id, recipient_id),
11
  INDEX idx_payment_id (payment_id)
12
);

Example reconciliation logic:

1
async function handlePayoutCredited(webhook: PayoutCreditedEvent) {
2
  // Update recipient status
3
  await db.payment_recipients.update({
4
    where: {
5
      payment_id: webhook.payment_id,
6
      recipient_id: webhook.data.recipient_id
7
    },
8
    data: {
9
      status: 'CREDITED',
10
      payout_credited_at: webhook.event_timestamp
11
    }
12
  });
13
14
  // Check if all recipients for this payment are credited
15
  const allRecipients = await db.payment_recipients.findMany({
16
    where: { payment_id: webhook.payment_id }
17
  });
18
19
  const allCredited = allRecipients.every(r => r.status === 'CREDITED');
20
21
  if (allCredited) {
22
    // Still wait for PAYMENT_COMPLETED webhook for final confirmation
23
    console.log('All recipients credited, awaiting PAYMENT_COMPLETED');
24
  }
25
}

When: Payment cancelled before or during processing

Webhook contains:

• payment_id
• data.reason_description: Reason for cancellation

Action:

1. Update status to CANCELLED
2. Store cancellation reason
3. Process refund logic if applicable
4. Notify customer of cancellation

1
{
2
  "event_id": "evt_1234567896",
3
  "payment_id": "pay_abcdef123456",
4
  "status": "CANCELLED",
5
  "customer_id": "0201001008132685",
6
  "client_customer_ref": "CUST-1001-TXN",
7
  "client_reference_id": "PAY-2025-08-15-001",
8
  "event_timestamp": "2025-12-02T10:50:00Z",
9
  "data": {
10
    "reason_description": "Customer cancelled the payment"
11
  }
12
}

When: Funds refunded to customer after receipt

Webhook contains:

• payment_id
• data.refund_amount: Amount refunded
• data.refund_reason: Reason for refund

Action:

1. Update status to REFUNDED
2. Store refund amount and reason
3. Notify customer of refund
4. Update accounting records

1
{
2
  "event_id": "evt_1234567897",
3
  "payment_id": "pay_abcdef123456",
4
  "status": "REFUNDED",
5
  "customer_id": "0201001008132685",
6
  "client_customer_ref": "CUST-1001-TXN",
7
  "client_reference_id": "PAY-2025-08-15-001",
8
  "event_timestamp": "2025-12-02T11:00:00Z",
9
  "data": {
10
    "refund_amount": {
11
      "currency": "GBP",
12
      "value": 1000.00
13
    },
14
    "refund_reason": "Customer overpaid"
15
  }
16
}

When: Payout failed and funds returned

Webhook contains:

• payment_id
• data.amount: Amount that bounced
• data.recipient_id: Affected recipient
• data.bounce_reason: Why the payout failed

Action:

1. Update status to BOUNCED_BACK
2. Store bounce reason and affected recipient
3. Investigate the cause (invalid account, incorrect details)
4. Contact support if needed
5. Prepare retry with corrected details

1
{
2
  "event_id": "evt_1234567898",
3
  "payment_id": "pay_abcdef123456",
4
  "status": "BOUNCED_BACK",
5
  "customer_id": "0201001008132685",
6
  "client_customer_ref": "CUST-1001-TXN",
7
  "client_reference_id": "PAY-2025-08-15-001",
8
  "event_timestamp": "2025-12-02T11:10:00Z",
9
  "data": {
10
    "amount": {
11
      "currency": "AED",
12
      "value": 4982.70
13
    },
14
    "recipient_id": "162345",
15
    "bounce_reason": "Invalid account number"
16
  }
17
}

When: Webhook arrives but cannot match to any session

Possible causes:

• Session not stored before webhook arrives
• client_customer_ref mismatch
• Database sync lag

Action:

1. Log the unmatched webhook with full payload
2. Alert operations team immediately
3. Investigate root cause (timing issue, data mismatch, system error)
4. If legitimate, manually reconcile the payment

When: Webhooks arrive in unexpected sequence

Why it happens:

• Network delays
• Retry logic
• Processing variations

Action:

1. Use event_timestamp to determine actual event order
2. Handle webhooks idempotently (processing same webhook twice should be safe)
3. Do not assume strict ordering in your logic
4. Update status based on timestamp, not arrival order

When: Same event_id received multiple times

Why it happens:

• Webhook delivery retry mechanism
• Network issues causing redelivery

Action:

1. Check event_id before processing any webhook
2. If event_id already exists in your webhook_events table, skip processing
3. Return 200 OK to acknowledge receipt (prevents further retries)
4. Log duplicate detection for monitoring

1
async function processWebhook(webhook: WebhookPayload) {
2
  // Check for duplicate
3
  const existing = await db.webhook_events.findOne({
4
    where: { event_id: webhook.event_id }
5
  });
6
7
  if (existing) {
8
    console.log(`Duplicate webhook ${webhook.event_id}, skipping`);
9
    return { status: 200, message: 'Already processed' };
10
  }
11
12
  // Process webhook...
13
  await db.webhook_events.create({
14
    event_id: webhook.event_id,
15
    payment_id: webhook.payment_id,
16
    status: webhook.status,
17
    payload: webhook,
18
    processed: true
19
  });
20
}

Setup: Create session with 1 recipient

Expected webhooks:

1. AWAITING_FUNDS
2. RECEIVED_FUNDS
3. FX_COMPLETED
4. PAYOUT_INITIATED
5. PAYOUT_CREDITED
6. PAYMENT_COMPLETED

Validation:

• Verify each status transition is stored correctly
• Confirm payment_id mapped to session_id on first webhook
• Check all amounts and conversion rates stored accurately

Setup: Create session with 3 recipients

Expected webhooks:

1. AWAITING_FUNDS
2. RECEIVED_FUNDS
3. FX_COMPLETED
4. PAYOUT_INITIATED (recipient 1)
5. PAYOUT_INITIATED (recipient 2)
6. PAYOUT_INITIATED (recipient 3)
7. PAYOUT_CREDITED (recipient 1)
8. PAYOUT_CREDITED (recipient 2)
9. PAYOUT_CREDITED (recipient 3)
10. PAYMENT_COMPLETED

Validation:

• Verify PAYOUT_CREDITED fires 3 times (once per recipient)
• Confirm each recipient_id tracked separately
• Check PAYMENT_COMPLETED fires only after all recipients paid

Setup: Create session but cancel before completion

Expected webhooks:

1. AWAITING_FUNDS
2. CANCELLED

Validation:

• Verify cancellation reason stored
• Confirm customer notification sent
• Check no further webhooks received after CANCELLED

Setup: Manually send webhook with unknown payment_id

Expected behavior:

• System logs unmatched event
• Operations team receives alert
• No status update to any payment
• 200 OK returned to webhook sender

Validation:

• Confirm unmatched event logged with full payload
• Verify alert triggers correctly
• Check system remains stable

Setup: Process same webhook twice (same event_id)

Expected behavior:

• First delivery: Webhook processed, status updated
• Second delivery: Webhook skipped, no duplicate status update
• Both deliveries: 200 OK returned

Validation:

• Verify event_id deduplication works
• Confirm no duplicate status updates in database
• Check idempotency maintained

Symptoms: Expected webhook never arrives

Likely causes:

• Webhook URL not publicly accessible
• Signature verification failing (webhook rejected by your endpoint)
• Firewall blocking Svix delivery IPs
• Incorrect webhook URL configured

Solutions:

1. Verify webhook URL returns 200 OK when called
2. Check webhook endpoint logs for rejected requests
3. Review signature verification implementation
4. Ensure URL uses HTTPS (HTTP may be blocked)
5. Check firewall rules allow Svix IP ranges
6. Review webhook delivery logs in Redpin dashboard (if available)

Symptoms: AWAITING_FUNDS webhook arrives but cannot find matching session

Likely causes:

• client_reference_id mismatch between session and webhook
• Session not stored before webhook arrives (timing issue)
• Database sync lag in replicated environment

Solutions:

1. Log client_reference_id from webhook and compare to database records
2. Ensure session stored synchronously before redirecting customer
3. Check database replication lag if using read replicas
4. Verify client_reference_id format consistency (trimming, case sensitivity)
5. Add retry logic to handle temporary timing issues

Symptoms: Status has not updated in 24+ hours

Likely causes:

• Customer did not complete session flow (abandoned at hosted page)
• Payment awaiting manual review or compliance check
• Technical issue on Redpin side

Solutions:

1. Check if customer completed session flow (check session expiration)
2. Contact Partner Integrations Team with payment_id and session_id
3. Review webhook logs for missed deliveries
4. Check customer communication (email bounces, failed notifications)
5. Verify payment not stuck in AWAITING_FUNDS due to customer inaction

Symptoms: Received PAYOUT_CREDITED for all recipients but PAYMENT_COMPLETED never arrives

Likely causes:

• This is expected behavior, wait longer (up to 5 minutes)
• Webhook delivery delay or failure
• One recipient payout still pending

Solutions:

1. Wait up to 5 minutes after last PAYOUT_CREDITED webhook
2. Verify all expected recipients received PAYOUT_CREDITED
3. Check webhook delivery logs if still missing after 5 minutes
4. Contact support if issue persists beyond 10 minutes
5. Review recipient count in original session request

Symptoms: Same status processed multiple times, duplicate database entries

Likely causes:

• Not checking event_id for idempotency
• Webhook processing not transactional
• Race condition in concurrent webhook processing

Solutions:

1. Implement event_id deduplication check before processing
2. Use database transactions for webhook processing
3. Add unique constraint on event_id in webhook_events table
4. Implement distributed locks for concurrent webhook processing
5. Return 200 OK even for duplicate events (prevents further retries)