Stripe — ontology map

What Stripe gives Kula Intelligence, what it cannot give, and how its customers, subscriptions, invoices and charges map onto the sale and payment axes.

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:

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:

-- 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;

-- 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

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;

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