Payment Deduplication

Detect when the same payment appears in multiple processors due to retry logic, reconciliation delays, or multi-gateway routing.

The Problem

Your company processes payments through Stripe, Square, and an internal accounting ledger. When a customer's payment fails on one gateway, it retries on another. When settlement takes time, the same transaction appears in your ledger on a different date. When gateways apply different rounding rules, amounts differ by fractions of a cent.

The result: your finance team reconciles payments manually each month, comparing spreadsheets line by line. A $1,247.50 payment from "Acme Corp" shows up in Stripe on January 15, in Square for $1,247.49 on January 16 (penny rounding plus a one-day settlement lag), and in your internal ledger on January 15. These are the same payment, but exact matching would never find it.

This use case demonstrates how range rules and composite rules handle the real-world data drift that makes payment deduplication difficult.

The Data

Stripe Payments

payment_id	merchant	amount	date	reference	method	status
`pay_3nK8mLq`	Acme Corp	1247.50	2026-01-15	INV-2026-0042	card	settled
`pay_7pR2xYw`	Beta LLC	500.00	2026-01-15	INV-2026-0039	card	settled
`pay_1mN4vBz`	Gamma Inc	89.99	2026-01-16	INV-2026-0045	card	settled
`pay_9aT6hDf`	Delta Co	3200.00	2026-01-17	INV-2026-0048	ach	settled
`pay_5kW3jPc`	Acme Corp	625.00	2026-01-18	INV-2026-0051	card	settled
`pay_2eL8nRs`	Epsilon Ltd	150.75	2026-01-19	INV-2026-0053	card	settled

Square Payments

transaction_id	business_name	total	transaction_date	invoice_ref	payment_type	state
`sq_tx_88214`	Acme Corp	1247.49	2026-01-16	INV-2026-0042	credit	complete
`sq_tx_88301`	Beta LLC	500.00	2026-01-15	INV-2026-0039	credit	complete
`sq_tx_88455`	Gamma Incorporated	90.00	2026-01-17	INV-2026-0045	credit	complete
`sq_tx_88512`	Delta Company	3200.00	2026-01-17	INV-2026-0048	ach	complete
`sq_tx_88623`	Zeta Partners	780.25	2026-01-19	INV-2026-0055	credit	complete
`sq_tx_88701`	Epsilon Ltd	150.75	2026-01-20	INV-2026-0053	credit	complete

Internal Ledger

entry_id	vendor	amount_usd	posted_date	invoice	channel	reconciled
`led_20260115_001`	Acme Corp	1247.50	2026-01-15	INV-2026-0042	stripe	false
`led_20260115_002`	Beta LLC	500.00	2026-01-15	INV-2026-0039	stripe	false
`led_20260116_001`	Gamma Inc	89.99	2026-01-16	INV-2026-0045	stripe	false
`led_20260117_001`	Delta Co	3200.00	2026-01-17	INV-2026-0048	ach	false
`led_20260118_001`	Acme Corp	625.00	2026-01-18	INV-2026-0051	stripe	false
`led_20260119_001`	Epsilon Ltd	150.75	2026-01-19	INV-2026-0053	stripe	false

What Makes This Hard

Challenge	Example	Impact
Penny rounding	Stripe: $1,247.50 vs Square: $1,247.49	Exact amount match fails
Settlement lag	Stripe: Jan 15 vs Square: Jan 16	Exact date match fails
Name variations	"Gamma Inc" vs "Gamma Incorporated"	Exact merchant match fails
Gateway-only payments	Zeta Partners only in Square	Should not be flagged as duplicate
Different schemas	`amount` vs `total` vs `amount_usd`	Fields have different names across sources

The Spec

yaml

api_version: kanoniv/v1
identity_version: "1.0"

entity:
  name: payment
  description: "Payment deduplication across Stripe, Square, and internal ledger"

sources:
  - name: ledger
    adapter: csv
    location: data/internal_ledger.csv
    primary_key: entry_id
    attributes:
      merchant: vendor
      amount: amount_usd
      date: posted_date
      reference: invoice
      method: channel
      reconciled: reconciled

  - name: stripe
    adapter: csv
    location: data/stripe_payments.csv
    primary_key: payment_id
    attributes:
      merchant: merchant
      amount: amount
      date: date
      reference: reference
      method: method
      status: status

  - name: square
    adapter: csv
    location: data/square_payments.csv
    primary_key: transaction_id
    attributes:
      merchant: business_name
      amount: total
      date: transaction_date
      reference: invoice_ref
      method: payment_type
      status: state

rules:
  # Invoice reference is the strongest signal. If two payments share
  # the same invoice number, they almost certainly represent the same
  # underlying transaction.
  - name: reference_exact
    type: exact
    field: reference
    weight: 1.0

  # Composite rule: amount must be close AND date must be close AND
  # merchant must match. All three conditions are required to prevent
  # false positives (e.g., two different $500 payments on the same day).
  - name: payment_composite
    type: composite
    operator: and
    children:
      # Amount tolerance of 5% handles penny rounding, currency
      # conversion differences, and minor fee adjustments.
      - name: amount_close
        type: range
        field: amount
        tolerance: 0.05
        weight: 0.5

      # Date tolerance of 2 days handles settlement lag, timezone
      # differences, and batch processing delays.
      - name: date_close
        type: range
        field: date
        tolerance: 2
        weight: 0.4

      # Merchant name fuzzy match catches abbreviations and
      # legal name variations ("Inc" vs "Incorporated").
      - name: merchant_fuzzy
        type: similarity
        field: merchant
        algorithm: jaro_winkler
        threshold: 0.85
        weight: 0.4

blocking:
  strategy: exact
  keys: [reference]

decision:
  scoring: weighted_sum
  thresholds:
    match: 0.9
    review: 0.7

survivorship:
  default: source_priority
  overrides:
    - field: amount
      strategy: aggregate
      function: max
    - field: date
      strategy: aggregate
      function: min

metadata:
  owner: [email protected]
  tags: [payment-dedup, finance, reconciliation]
  description: "Monthly payment deduplication across payment processors"

How the Rules Score Each Group

Group 1: Acme Corp $1,247.50 (INV-2026-0042)

Pair	reference_exact	amount_close	date_close	merchant_fuzzy	Composite	Total	Decision
Stripe / Square	1.0	0.5 (0.01% diff)	0.4 (1 day)	0.4 (exact)	0.9	1.9	Match
Stripe / Ledger	1.0	0.5 (0% diff)	0.4 (0 days)	0.4 (exact)	0.9	1.9	Match
Square / Ledger	1.0	0.5 (0.01% diff)	0.4 (1 day)	0.4 (exact)	0.9	1.9	Match

All three are the same payment. Cluster size: 3 records, 1 unique payment.

Group 2: Beta LLC $500.00 (INV-2026-0039)

Pair	reference_exact	amount_close	date_close	merchant_fuzzy	Composite	Total	Decision
Stripe / Square	1.0	0.5 (0% diff)	0.4 (0 days)	0.4 (exact)	0.9	1.9	Match
Stripe / Ledger	1.0	0.5 (0% diff)	0.4 (0 days)	0.4 (exact)	0.9	1.9	Match

Cluster size: 3 records, 1 unique payment.

Group 3: Gamma $89.99 (INV-2026-0045)

Pair	reference_exact	amount_close	date_close	merchant_fuzzy	Composite	Total	Decision
Stripe / Square	1.0	0.5 (0.01% diff)	0.4 (1 day)	0.4 (0.86 JW)	0.9	1.9	Match
Stripe / Ledger	1.0	0.5 (0% diff)	0.4 (0 days)	0.4 (exact)	0.9	1.9	Match

"Gamma Inc" vs "Gamma Incorporated" scores 0.86 in Jaro-Winkler, above the 0.85 threshold. Cluster size: 3.

Group 4: Delta $3,200.00 (INV-2026-0048)

Pair	reference_exact	amount_close	date_close	merchant_fuzzy	Composite	Total	Decision
Stripe / Square	1.0	0.5 (0% diff)	0.4 (0 days)	0.4 (0.87 JW)	0.9	1.9	Match
Stripe / Ledger	1.0	0.5 (0% diff)	0.4 (0 days)	0.4 (exact)	0.9	1.9	Match

"Delta Co" vs "Delta Company" scores 0.87 in Jaro-Winkler. Cluster size: 3.

Singletons (no duplicates detected):

Payment	Present In	Why No Match
Acme Corp $625.00 (INV-2026-0051)	Stripe + Ledger only	Cluster of 2 (Stripe and Ledger are the same transaction)
Zeta Partners $780.25 (INV-2026-0055)	Square only	Unique to Square, no duplicate exists

The Result

Deduplication Summary

Sources:              3
Total records:        18
Clusters:             6
  - 3-record clusters: 4  (genuine triplicates across all 3 processors)
  - 2-record clusters: 1  (Stripe + Ledger, no Square duplicate)
  - Singletons:        1  (Zeta Partners, Square only)
Unique payments:      6
Duplicates removed:   12
Dedup rate:           66.7% (18 records → 6 unique payments)

Duplicate Groups

Group	Merchant	Amount	Invoice	Records	Sources
1	Acme Corp	$1,247.50	INV-2026-0042	3	Stripe, Square, Ledger
2	Beta LLC	$500.00	INV-2026-0039	3	Stripe, Square, Ledger
3	Gamma Inc	$89.99	INV-2026-0045	3	Stripe, Square, Ledger
4	Delta Co	$3,200.00	INV-2026-0048	3	Stripe, Square, Ledger
5	Acme Corp	$625.00	INV-2026-0051	2	Stripe, Ledger
6	Zeta Partners	$780.25	INV-2026-0055	1	Square

Golden Records

After survivorship, each cluster produces one canonical payment record. The ledger is the system of record (first in the sources list), with amount taking the max across sources and date taking the min (earliest posting date).

json

[
  {
    "canonical_id": "canon_pay_001",
    "canonical_data": {
      "merchant": "Acme Corp",
      "amount": 1247.50,
      "date": "2026-01-15",
      "reference": "INV-2026-0042",
      "method": "card",
      "reconciled": true
    },
    "cluster": { "size": 3, "sources": ["stripe", "square", "ledger"] }
  },
  {
    "canonical_id": "canon_pay_002",
    "canonical_data": {
      "merchant": "Beta LLC",
      "amount": 500.00,
      "date": "2026-01-15",
      "reference": "INV-2026-0039",
      "method": "card",
      "reconciled": true
    },
    "cluster": { "size": 3, "sources": ["stripe", "square", "ledger"] }
  },
  {
    "canonical_id": "canon_pay_003",
    "canonical_data": {
      "merchant": "Gamma Inc",
      "amount": 90.00,
      "date": "2026-01-16",
      "reference": "INV-2026-0045",
      "method": "card",
      "reconciled": true
    },
    "cluster": { "size": 3, "sources": ["stripe", "square", "ledger"] }
  },
  {
    "canonical_id": "canon_pay_004",
    "canonical_data": {
      "merchant": "Delta Co",
      "amount": 3200.00,
      "date": "2026-01-17",
      "reference": "INV-2026-0048",
      "method": "ach",
      "reconciled": true
    },
    "cluster": { "size": 3, "sources": ["stripe", "square", "ledger"] }
  },
  {
    "canonical_id": "canon_pay_005",
    "canonical_data": {
      "merchant": "Acme Corp",
      "amount": 625.00,
      "date": "2026-01-18",
      "reference": "INV-2026-0051",
      "method": "card",
      "reconciled": true
    },
    "cluster": { "size": 2, "sources": ["stripe", "ledger"] }
  },
  {
    "canonical_id": "canon_pay_006",
    "canonical_data": {
      "merchant": "Zeta Partners",
      "amount": 780.25,
      "date": "2026-01-19",
      "reference": "INV-2026-0055",
      "method": "credit",
      "reconciled": false
    },
    "cluster": { "size": 1, "sources": ["square"] }
  }
]

Key Takeaway

Range rules and composite rules handle real-world data drift. Exact matching would miss the Acme Corp payment entirely because the amount differs by $0.01 and the date differs by one day. Range rules with a 5% tolerance and 2-day window catch these near-matches, while the composite AND operator ensures all three signals (amount, date, merchant) must agree before declaring a duplicate.

The invoice reference serves as a "fast path": when it matches exactly, you know immediately. The composite rule is the "safety net" for cases where references are missing or inconsistent.

Variations to Consider

If payments lack invoice references, rely entirely on the composite rule:

yaml

rules:
  - name: payment_composite
    type: composite
    operator: and
    children:
      - name: amount_close
        type: range
        field: amount
        tolerance: 0.02    # tighter tolerance without reference anchor
        weight: 0.5
      - name: date_close
        type: range
        field: date
        tolerance: 1       # tighter window
        weight: 0.4
      - name: merchant_fuzzy
        type: similarity
        field: merchant
        algorithm: jaro_winkler
        threshold: 0.90    # stricter name match
        weight: 0.4

If you need a wider tolerance for international payments (currency conversion drift):

yaml

- name: amount_close
  type: range
  field: amount
  tolerance: 0.10    # 10% tolerance for FX drift
  weight: 0.5

If you want human review for borderline cases, lower the review threshold:

yaml

decision:
  thresholds:
    match: 0.9
    review: 0.5    # catch more borderline duplicates for manual review

Spec Reference: Rules: Range rule tolerance modes and composite operators
Spec Reference: Sources: Multi-field attribute mapping across schemas
Tutorial: Payment Deduplication: Step-by-step walkthrough with Python SDK

Payment Deduplication ​

The Problem ​

The Data ​

Stripe Payments ​

Square Payments ​

Internal Ledger ​

What Makes This Hard ​

The Spec ​

How the Rules Score Each Group ​

The Result ​

Deduplication Summary ​

Duplicate Groups ​

Golden Records ​

Key Takeaway ​

Variations to Consider ​

Related ​

Payment Deduplication

The Problem

The Data

Stripe Payments

Square Payments

Internal Ledger

What Makes This Hard

The Spec

How the Rules Score Each Group

The Result

Deduplication Summary

Duplicate Groups

Golden Records

Key Takeaway

Variations to Consider

Related