Payout Reconciliation

Purpose

Reconciles Shopify Payments payouts to the order transactions that contributed to them. For each payout, sums gross sales, refunds, adjustments, and fees, and compares the computed net to the payout's reported net amount. Discrepancies are flagged with a delta and the suspected cause. Read-only — no mutations.

Prerequisites

Parameters

Safety

> ℹ️ Read-only skill — no mutations are executed. Reconciliation output is informational; do not treat flagged discrepancies as confirmed errors before reviewing the underlying transactions in the Shopify admin.

Workflow Steps

Inputs: select payouts(first: 100, query: "issued_at:>=''") with id, issuedAt, status, net, gross, summary { chargesGross, chargesFee, refundsGross, refundsFee, adjustmentsGross, adjustmentsFee, retriedPayoutsGross, retriedPayoutsFee }

Expected output: All payouts in the window. If account is null, exit — store does not use Shopify Payments.

```

expected_net = chargesGross - chargesFee

+ adjustmentsGross - adjustmentsFee

+ retriedPayoutsGross - retriedPayoutsFee

```

delta = reported_net - expected_net

Inputs: query: "transactions:'gateway:shopify_payments processed_at:>= processed_at:<='", first: 250

Expected output: Candidate orders that may have contributed to the payout, used for an order-level cross-check on top discrepancies

GraphQL Operations

# shopifyPaymentsAccount:query — validated against api_version 2025-01
query PayoutReconciliation($payoutQuery: String!, $payoutAfter: String) {
  shopifyPaymentsAccount {
    id
    payoutSchedule { interval }
    payouts(first: 100, after: $payoutAfter, query: $payoutQuery) {
      edges {
        node {
          id
          issuedAt
          status
          net { amount currencyCode }
          gross { amount currencyCode }
          summary {
            chargesGross { amount currencyCode }
            chargesFee { amount currencyCode }
            refundsFeeGross { amount currencyCode }
            refundsFee { amount currencyCode }
            adjustmentsGross { amount currencyCode }
            adjustmentsFee { amount currencyCode }
            retriedPayoutsGross { amount currencyCode }
            retriedPayoutsFee { amount currencyCode }
          }
        }
      }
      pageInfo { hasNextPage endCursor }
    }
  }
}

# orders:query — validated against api_version 2025-01
query OrdersFundingPayout($query: String!, $after: String) {
  orders(first: 250, after: $after, query: $query) {
    edges {
      node {
        id
        name
        processedAt
        totalPriceSet { shopMoney { amount currencyCode } }
        totalReceivedSet { shopMoney { amount currencyCode } }
        transactions {
          id
          gateway
          kind
          status
          processedAt
          amountSet { shopMoney { amount currencyCode } }
        }
      }
    }
    pageInfo { hasNextPage endCursor }
  }
}

Session Tracking

Claude MUST emit the following output at each stage. This is mandatory.

On start, emit:

╔══════════════════════════════════════════════╗
║  SKILL: Payout Reconciliation                ║
║  Store: <store domain>                       ║
║  Started: <YYYY-MM-DD HH:MM UTC>             ║
╚══════════════════════════════════════════════╝

After each step, emit:

[N/TOTAL] <QUERY|MUTATION>  <OperationName>
          → Params: <brief summary of key inputs>
          → Result: <count or outcome>

On completion, emit:

For format: human (default):

══════════════════════════════════════════════
PAYOUT RECONCILIATION  (<days_back> days)
  Payouts in window:    <n>
  Reconciled (ok):      <n>
  Discrepancies:        <n>   ⚠️
  Sum of abs deltas:    $<amount>

  Top discrepancies:
    Payout <date>  Reported: $<n>  Expected: $<n>  Δ: $<n>

  Output: payout_reconciliation_<date>.csv
══════════════════════════════════════════════

For format: json, emit:

{
  "skill": "payout-reconciliation",
  "store": "<domain>",
  "period_days": 30,
  "payouts_count": 0,
  "reconciled_count": 0,
  "discrepancy_count": 0,
  "sum_abs_delta": 0,
  "currency": "USD",
  "output_file": "payout_reconciliation_<date>.csv"
}

Output Format

CSV file payout_reconciliation_.csv with columns:

payout_id, issued_at, status, reported_net, expected_net, delta, currency, charges_gross, charges_fee, refunds_gross, refunds_fee, adjustments_gross, verdict

Error Handling

Best Practices