> For the complete documentation index, see [llms.txt](https://docs.kula.digital/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.kula.digital/whats-in-your-data/stripe.md).

# Stripe — ontology map

**Source id:** `com.stripe` · **Role:** payments and subscriptions — a **satellite** source, not a system of record for the studio floor · **Access:** Stripe REST API, restricted key

Stripe knows about money. It knows nothing about classes, attendance, instructors, or what a member actually did in the studio. A Stripe-only studio has revenue and no operations; a studio with Stripe *and* a booking system has both, joined imperfectly through identity resolution.

The single most important thing to understand about Stripe here is the **two-axis model**.

## The two axes: sale vs payment

| Axis      | Stripe object | Canonical table    | What it means                                                             |
| --------- | ------------- | ------------------ | ------------------------------------------------------------------------- |
| **Sale**  | `invoice`     | `commerce.sale`    | What was sold, and on what terms — authoritative for subscription billing |
| **Money** | `charge`      | `commerce.payment` | Money that actually moved, with fee, net and payout                       |

They are joined through `commerce.sale_payment`, and the invoice's expanded payments are what make that link. **Every** charge becomes a payment, including invoice-backed ones — the invoice owns the sale, the charge owns the money.

Practical consequence: a subscription that bills monthly produces one `commerce.sale` per period and one `commerce.payment` per successful charge. Summing sales and summing payments answer different questions (*billed* vs *collected*), and neither is wrong.

## Coverage at a glance

| Stripe entity   | Canonical home                                 | Grain                     | Window                  |
| --------------- | ---------------------------------------------- | ------------------------- | ----------------------- |
| `products`      | `commerce.product`                             | one per product           | full pull               |
| `prices`        | `commerce.plan`                                | one per price             | full pull               |
| `customers`     | `people.member`                                | one per customer          | full pull               |
| `subscriptions` | `people.member` plan-state stub                | winning subscription only | full pull, `status=all` |
| `invoices`      | `commerce.sale`                                | one per invoice           | **date-windowed**       |
| `charges`       | `commerce.payment` + `commerce.payment_refund` | one per charge / refund   | **date-windowed**       |

`prices` map to `commerce.plan` because Stripe's product+price pair is what the rest of the model calls a plan. Recurring prices carry the billing interval; one-off prices don't.

Subscriptions are pulled with `status=all` deliberately — plan-state correctness needs old-but-active and cancelled subscriptions, which Stripe's default filter hides. Only the customer's **winning** subscription produces a member plan-state stub, keyed on the *customer* id so it merges onto the same `people.member` row.

Charges are expanded to include their balance transaction (fee, net, payout) and each refund's balance transaction (fee returned). That's four levels of expansion — Stripe's maximum.

## What we cannot get

**Anything operational.** No classes, no attendance, no instructors, no schedule, no door access. `bookings.*` is entirely empty for a Stripe-only studio. This is not a limitation to work around — it's what Stripe is.

**Member status history before we started capturing it.** Stripe reports a subscription's *current* status. The cancellation timestamps it does send (`canceled_at`, `cancel_at_period_end`, `current_period_end`) land untyped in `source_extras` and describe one subscription, not the person's history. Kula synthesises the change by detecting it itself, so **from the date capture began** every status, membership-type, plan and suspension change is recorded in `people.member_status_event` — read it with `list_member_status_changes`, or the `status_history` block on `get_member_context`. Two Stripe-specific traps: a subscription set to cancel at period end produces **no** event until the status actually flips, and Stripe's duplicate customer rows each carry their own events, so roll up on `COALESCE(canonical_member_id, id)` before counting people. Each member also carries one `is_baseline = true` origin row recording what we first saw; that is an observation, not a change. Establish the capture start before charting a series:

```sql
SELECT min(detected_at) AS capture_started FROM people.member_status_event;
```

**Real member identity.** A Stripe customer is a payer, not a studio member. The name is a free-text field split into first/last on a best-effort basis, and email is whatever was entered at checkout. Where a studio also runs a booking system, the Stripe `people.member` row is a **duplicate** of the real member and must be joined through `people.identity_link`.

**Revenue before the earliest pulled window.** Invoices and charges are date-windowed. History goes back as far as the backfill has run, no further.

**Disputes and chargebacks**, **payouts as a first-class object**, and **Stripe Billing schedule detail** (upcoming invoices, proration previews) are not mapped. Fee and net *are* available per payment, from the expanded balance transaction.

**Webhook-driven live updates** are not the primary path — the ingestor polls. A studio's Stripe data is as fresh as the last sweep, not real-time.

## Identity and join keys

| Thing    | Stripe id | Canonical                             |
| -------- | --------- | ------------------------------------- |
| Customer | `cus_…`   | `people.member.source_external_id`    |
| Product  | `prod_…`  | `commerce.product.source_external_id` |
| Price    | `price_…` | `commerce.plan.source_external_id`    |
| Invoice  | `in_…`    | `commerce.sale.source_external_id`    |
| Charge   | `ch_…`    | `commerce.payment.source_external_id` |

An invoice line's `item_id` is the **price id**, which is what joins a sale to `commerce.plan`. `is_recurring` on the sale distinguishes subscription billing from one-off purchases.

`provider_transaction_id` on a payment is the Stripe `pi_…` / `ch_…` id. It is PII level 3 and masked in the guarded views — read `commerce.payment_guarded`, not the base table, unless you specifically need the gateway id.

### Attributing payments to real people

Do **not** group `commerce.payment` by `payer_member_id` to get per-member spend. Stripe mints a customer id per checkout flow in many setups, so the same human appears as several payers and the total splits across them.

Use **`commerce.payment_attribution`** instead. It resolves the payer to the canonical, identity-linked member and stitches each payment to the sale it funded. Two rules when querying it:

* `payment_amount` **repeats** on every row of a split payment (one row per funded sale). Never `SUM` it directly — sum `allocated_amount`, or sum over `DISTINCT payment_id`.
* `member_id` being NULL is normal — it means the payer couldn't be matched to a known member. Report unattributed revenue as its own bucket rather than dropping it.

## Counting traps

**Deleted customers don't vanish.** A `customer.deleted` snapshot maps to `status = 'inactive'` with `stripe_deleted` in extras. They still count in a naive member count.

**Failed and uncollectible invoices are still sales.** A `commerce.sale` with no matching payment usually means an invoice that was billed but never paid. That's a real dunning signal, not a data gap.

**One invoice, many charges.** Retries after a failed payment produce multiple charges against one invoice. Counting charges over-counts transactions; counting successful charges is what you usually want.

**Refunds are separate rows.** `commerce.payment_refund` for the money side, `commerce.refund` for the sale side. Net revenue subtracts them; it does not net them into the sale row.

**Currency is real here.** Unlike MBO, Stripe carries currency per charge. Multi-currency studios will have mixed-currency rows — don't sum across currencies.

## Questions this source can and can't answer

**Can answer well**

* Revenue billed vs revenue collected, by period
* MRR, active subscriptions, churned subscriptions
* Fees, net revenue, payout amounts
* Failed payments and dunning exposure
* Refund totals and rate
* Per-member spend — **through `commerce.payment_attribution`**

**Cannot answer from Stripe alone**

* Anything about attendance, classes or instructors *(not in Stripe)*
* Whether a paying customer is actually still coming to the studio
* Who a payer is, beyond the name and email typed at checkout
* Revenue before the backfill window
* Disputes and chargebacks *(not mapped)*

**When both Stripe and a booking system are connected**, the interesting questions live in the join: paying-but-not-attending members, attendance without a matching payment, plan price versus what's actually collected. Those all route through `people.identity_link` and `commerce.payment_attribution`, and all of them carry an unmatched remainder that should be stated rather than hidden.

## Recipes — what works well

### One member's money → `get_member_payments`

The right first call. It combines `commerce.sale` (invoices) with the member's standalone gateway payments not yet linked to a sale, so cash that never produced a sale-side row still shows, with a `record_kind` column discriminating them. Scope rules apply — billing email needs admin, card last-4 needs full scope.

### Per-member spend across gateway duplicates → `commerce.payment_attribution`

Never group `commerce.payment` by `payer_member_id`. Use the view, and mind that `payment_amount` repeats per funded sale:

```sql
-- Lifetime collected, de-duped across gateway customer ids
SELECT sum(payment_amount) FROM (
  SELECT DISTINCT payment_id, payment_amount
  FROM commerce.payment_attribution
  WHERE member_id = $1 AND status = 'succeeded'
) t;
```

```sql
-- How much revenue can't be attributed to a known member?
SELECT count(*) FILTER (WHERE member_id IS NULL) AS unattributed,
       count(*) AS total
FROM commerce.payment_attribution;
```

Report that denominator alongside any per-member revenue analysis.

### Billed vs collected → keep the two axes apart

```sql
SELECT date_trunc('month', s.occurred_at) AS month,
       sum(s.total) AS billed
FROM commerce.sale_guarded s
WHERE s.source = 'com.stripe' GROUP BY 1 ORDER BY 1;
```

```sql
SELECT date_trunc('month', occurred_at) AS month,
       sum(amount) FILTER (WHERE status = 'succeeded') AS collected,
       sum(fee)    FILTER (WHERE status = 'succeeded') AS fees,
       sum(net)    FILTER (WHERE status = 'succeeded') AS net
FROM commerce.payment_guarded
WHERE source = 'com.stripe' GROUP BY 1 ORDER BY 1;
```

The gap between them is dunning exposure, not a discrepancy. A sale with no matching payment is a real signal worth surfacing.

### Retention → `get_retention_curve`

Stripe is the ideal input: each cohort is the month of a member's first successful recurring charge, and retention is billing continuity. Bucket by `signup_month`, `plan`, or `location`.

### Acquisition cost → `get_cac_by_cohort`

Needs a marketing source for spend. Note the cohort denominator is members whose first **attended class** was in that month — so on a Stripe-only org there's no denominator. This tool needs a booking source alongside Stripe.

### Counting real members → `people.member_distinct`

Critical here. `people.member` is one row per source, and Stripe adds a row per customer id. Counting `people.member` on a studio running Stripe plus a booking system materially over-counts.

### Is the data current → `get_system_status`

Stripe is polled, not webhook-driven, so "as of" matters.

### What doesn't work on Stripe alone

Anything operational — `list_at_risk_members`, `get_class_utilisation`, `get_time_slot_detail`, `get_teacher_performance` all need booking data Stripe doesn't have. On a Stripe-only org they return empty, and that is the correct result.

### The interesting questions are in the join

With Stripe *and* a booking system connected, the valuable analyses are cross-source: paying-but-not-attending members, attendance with no matching payment, plan price versus what's actually collected. All route through `people.identity_link` and `commerce.payment_attribution` — and all carry an unmatched remainder that should be stated, not hidden.

## Where this lives in the code

| Concern                                           | Path                                                                                     |
| ------------------------------------------------- | ---------------------------------------------------------------------------------------- |
| Entity catalogue and expansions                   | `services/ingestors/stripe/internal/stripeclient/stripeclient.go`                        |
| Transforms (customers, prices, invoices, charges) | `services/ingestors/stripe/internal/transform/`                                          |
| Payment attribution view                          | `services/intelligence/migrations/postgres/org/200_commerce/008_payment_attribution.sql` |
| Operator-facing connect guide                     | [Stripe connector](/your-data-sources/stripe.md)                                         |