reconciliation

name	reconciliation
description	Compares every customer present in both Postgres and Stripe and surfaces drift — status mismatches, mrr_cents disagreements, orphans, and refunds in Stripe not reflected in payments. Sub-step inside revenue-recovery.

Reconciliation

The goal is find the cases where Postgres and Stripe disagree, and tell the operator which is right. This is the skill that surfaces the bug nobody noticed — the customer who cancelled in Stripe 9 days ago and is still showing as active MRR in the founder's dashboard.

Stripe is the source of truth for billing state. Postgres is the source of truth for everything operational (CSM relationships, support history). When they disagree on billing, Stripe wins.

Step 1: List both sides

In parallel:

• Postgres: get_customers for every customer with a non-null stripe_customer_id. Capture id, name, status, mrr_cents, plan, stripe_customer_id.
• Stripe: list_customers to enumerate everyone in Stripe. Capture id, email, metadata, current subscription state (call list_subscriptions per customer or in batch if the adapter supports it).

Step 2: Detect each drift class

Run all four checks. Each yields its own findings list — emit them separately so the orchestrator can render them grouped.

A. Status mismatch

For every customer matched on stripe_customer_id, compare:

• Postgres customers.status = 'active' AND Stripe subscription status in ('canceled', 'incomplete_expired')
• Postgres subscriptions.status = 'active' AND Stripe subscription cancelled

Either is a drift. Stripe is correct; Postgres lagged. Note the days-since-cancellation in Stripe — that's the "ghost MRR" duration.

B. Amount mismatch

For each matched customer, compute Stripe's active-subscription MRR (sum of monthly-equivalent amounts) and compare to Postgres customers.mrr_cents. If they differ by more than $1 (rounding tolerance), flag it. This usually means a plan change happened in Stripe (upgrade or downgrade) and Postgres didn't update.

C. Orphans

• Stripe orphans: a stripe_customer_id exists in Stripe but no matching Postgres customer row. These are ghosts — likely from a botched import or a Stripe-direct signup that never got synced.
• Postgres orphans: a Postgres customer with stripe_customer_id != null whose Stripe customer no longer exists (deleted in Stripe). Rare but possible.

Both are findings; orphans are usually a reconciliation write rather than a recovery action.

D. Refund drift

For each refund visible in Stripe (via list_invoices with refunded status, or whatever refund-listing the adapter exposes), check that a matching Postgres payments row exists with status = 'refunded'. If not, the operator's reporting is overstating revenue.

Step 3: Recommend the reconciliation action per finding

Per row:

• A. Status mismatch: reconcile — propose updating Postgres customers.status to match Stripe, set subscriptions.canceled_at and status = 'canceled', recompute mrr_cents. This is a Postgres write only — Stripe is already correct. Risk tier: medium (it's a reporting fix, not a money movement).
• B. Amount mismatch: reconcile — propose updating Postgres mrr_cents and the subscriptions.amount_cents to match Stripe's active subscription. Medium risk.
• C. Stripe orphan: investigate — do not auto-create a Postgres row. The operator needs to decide whether the customer should be in Postgres at all. High risk.
• D. Refund drift: reconcile — propose adding a Postgres payments row with status = 'refunded', matching the Stripe refund amount and timestamp. Medium risk.

Step 4: Compute the reporting impact

Sum the "ghost MRR" — Postgres-says-active-but-Stripe-cancelled customers' mrr_cents. This is the dashboard correction the operator will care about most. Surface as a single headline number.

Output shape

All amounts in cents in structured fields. Comply with the orchestrator's "Output format conventions".

status_mismatches:
  - customer_id: 41
    name: Larkspur Inc
    postgres_status: active
    postgres_mrr_cents: 9900
    stripe_status: canceled
    stripe_canceled_at: 2026-04-27
    days_drift: 9
    revenue_at_risk_cents: 9900
    recommended_action: reconcile (Postgres write)
    risk_tier: medium

amount_mismatches:
  - customer_id: 18
    name: Brightpath Studios
    postgres_mrr_cents: 49900
    stripe_mrr_cents:    9900
    delta_cents:       -40000
    cause: downgraded in Stripe, Postgres not updated
    recommended_action: reconcile (Postgres write)
    risk_tier: medium

orphans:
  stripe_only:
    - stripe_customer_id: cus_abc123
      email: [e-mail]
      created_at: 2026-04-08
      recommended_action: investigate
      risk_tier: high
  postgres_only: []

refund_drift: []

reporting_impact_cents:
  ghost_mrr_cents: 9900            # postgres-active-stripe-cancelled total
  ghost_mrr_count: 1
  unrecorded_downgrade_cents: 40000
  unrecorded_downgrade_count: 1
  total_dashboard_correction_cents: 49900

Pagination

list_customers, list_subscriptions, list_invoices on the Stripe side cap at 100 records per call. Reconciliation must enumerate the full universe on both sides — paginate to completion before computing drift counts. A reconciliation built on the first page only will report a fraction of the actual drift.

For Postgres, prefer get_customers_summary and get_subscriptions_summary for headline counts; use the row-level tools for the per-customer enumeration.

Rules

• Read-only when discovering, write-proposing in output. This skill never executes the reconcile actions itself — the orchestrator does, in Phase 2, after approval.
• All *_cents fields are integer cents. $99 = 9900, not 99. Authoritative section is in the orchestrator skill.
• Stripe is the source of truth. When in doubt, propose the Postgres update, not the reverse.
• Never propose creating a Postgres row from a Stripe orphan automatically. Orphans need investigation, not auto-sync.
• If the reconciliation action is a Postgres-only write (no Stripe state change), label it as such — the orchestrator's risk tiering treats it as medium, not high, even though it's still a write.
• Tolerance for amount mismatches is 100 cents ($1). Below that, treat as rounding.