Datastream and CMS API

Health check

Unauthenticated. Runs a single trivial query against the mirror to confirm the pool can reach the database — not a check of sync freshness or any individual site. A 200 means "the API can talk to Postgres," nothing more.

Fields resting on unverified JSON paths

The reconciliation checklist. Each entry is a mapped field whose `_document` path was not observed in the schema dump.

This document

The live spec, generated from this same file — served as YAML so it stays diffable and matches what's committed to the repo. api-fvmgt's combined API docs page (/) fetches this alongside api-funnel's /v1/docs and renders both as one operation list.

Live change stream (SSE)

Server-Sent Events, not a normal request/response call — the connection stays open and pushes an event each time something this key can read changes (bookings, check-ins, roster updates). Read scope only: subscribing tells a caller what changed, it never lets them change anything, so a `raw:read`-only check-in screen and a `booking:write` widget can both listen on the same key. Long-lived by design, so none of the usual response machinery applies here — no envelope, no ETag, no pagination, no Cache-Control. The rate limiter in `authenticate` still runs once at connect time, which is what stops one caller from opening hundreds of these.

Class schedule

Backed by `ds_mbo.class`. Defaults to a single day (today) — an unbounded default over 196k rows is not useful. Use either `date`+`days` (the legacy pair) or `start_date`+`end_date`, not both.

One class

A single scheduled class instance by its MBO `class_id`, joined the same way `/v1/schedule` is (room, staff, description). `class_id`s are per-site sequential and collide across tenants — this key's `SiteScope` is what keeps a lookup from crossing into another studio.

One class type's description

The class name, category, and full HTML description for a class *type* (e.g. "Flow"), keyed by `class_schedule_id` — the same field every `/v1/schedule` row carries. Split out of `/v1/schedule` because the description text repeats verbatim across every instance of a class type and was ~52% of that endpoint's payload by weight; this route is meant to be fetched on demand (e.g. when a user expands a class for details) and cached long by the client, not called once per schedule row.

One class (legacy query-param form)

Identical response to `/v1/schedule/{class_id}` — same lookup, same join, same site scoping — just `class_id` as a query param instead of a path segment. Retained so legacy consumers repoint without a path change; new integrations should use the path form.

Class roster

Backed by `ds_mbo.class_visit`, joined to `ds_mbo.client` on `(client_id, site_id)` — never on id alone, since MBO ids collide across sites. Freshness caveat: as of 2026-08-05, `realtime`-tier sites (currently Flow and Flow Yoga Georgetown) get roster changes pushed via webhook — a booking, cancellation, or membership change typically lands in the mirror within 1–3 minutes. That is **event-driven, not polled**: a class with no new activity since the switch still reflects whatever the last scheduled pass (08:00/18:00/23:00 UTC) wrote, which can be hours old even for a class starting soon. `daily`/`paused` sites still rely solely on the scheduled passes. Confirm a class has a recent `sync_dirty_entity` fetch before trusting it as live.

Book a client into a class

Adds a client to a class. Requires the `booking:write` scope, and is served only where the deployment has writes enabled. **This does not write to Datastream.** The booking is proxied to Mindbody, which is the system of record; `ds_mbo.class_visit` picks it up on the next sync, so a roster read straight after a booking will not show it yet. Datastream still flows one direction. The class is resolved inside the key's site scope first — a class the key cannot read is a `404`, not a `403`, so the endpoint never confirms a class it will not serve. The Mindbody site and staff login used are the ones registered for that class's site. Business rules are Mindbody's: capacity, eligibility, late-booking windows and payment are its call, and its own message comes back verbatim on a `422`. The single precondition checked here is a cancelled class, which is a `409`. Send `test: true` to have Mindbody validate the booking without creating one. The response echoes `test` — a consumer that ignores it will read a dry run as a real booking. A dry run returns `visit_id: 0`, since Mindbody answers with a placeholder visit rather than none. **Mindbody does not dedupe.** The same client booked into the same class twice gets two distinct visits — verified in production, and the mirror cannot be used to pre-check because it syncs three times a day. Send an `Idempotency-Key` and a repeat replays the first response instead of booking again. Without one, a retry or a double-click creates a duplicate.

Remove a client from a class

Cancels a client's place. Requires `booking:write`. Scoped to the class, not a visit id, because Mindbody's `removeclientfromclass` takes `(client_id, class_id)` and has no per-visit form. One call clears **every** visit that client holds in the class, so it is the cleanup for the duplicates `POST` can create. Not a no-op when there is nothing to remove: a second call returns `422` with "No class visit found…". Useful as a way to confirm a client is clear. `late_cancel` defaults off, so the forgiving outcome — credit returned, no penalty — is the one you get by accident.

Mark a booked client signed in (or undo it)

Flips `signed_in` on a visit the client already holds. Requires the `checkin:write` scope — deliberately separate from `booking:write`, since this cannot add, remove, or move anyone's booking, only mark arrival on a booking that already exists. `visit_id` is the roster row's `booking_id`, from a prior `GET /v1/schedule/{class_id}/roster`. It is not independently re-verified against the class — the same trust model `DELETE .../bookings` already uses for a caller-supplied `client_id` — but the Mindbody session used is scoped to the class's site, so a visit id from another site fails there, not here. **Known gap:** unlike book/cancel, this write is not yet reflected by the read-after-write overlay. A roster read immediately after still shows the mirror's stale `signed_in` for anyone but the caller, who should trust this response instead.

Assign a teacher or substitute to one class instance

Sets the `staff_id` on a single scheduled class instance via Mindbody's `updateclass`. Requires the `schedule:write` scope, and is served only where the deployment has writes enabled. This is the "sub" verb: Mindbody itself decides whether the result reads as a substitute by comparing the instance's staff to its recurring schedule's own default — this endpoint does not set that flag directly. To change the schedule's *permanent* teacher instead (the default every future-generated instance inherits), use `PATCH /v1/schedule-definitions/{class_schedule_id}/teacher`. The class is resolved inside the key's site scope first, same as `POST .../bookings` — a class the key cannot read is a `404`. A cancelled class is a `409`. **Does not write to Datastream and is not yet reflected by the read-after-write overlay** — a schedule read immediately after still shows the mirror's stale teacher until the next sync.

Publish a new recurring class

Creates a recurring class schedule via Mindbody's `addclassschedule` — the class type, location, days/times, and its **permanent teacher** in one call. Mindbody generates the individual instances; the mirror picks them up on its next sync. Requires `schedule:write`, and is served only where the deployment has writes enabled. `site_id` is required here (not just a narrowing filter): there is no existing mirror row to read tenancy off before the class exists, so the caller's own site scope is checked directly against it. It also supplies Mindbody's `LocationId`.

Change a recurring schedule's permanent teacher

Sets `staff_id` on the recurring class schedule itself via Mindbody's `updateclassschedule` — the default every future-generated instance inherits, not just one occurrence (see `PATCH /v1/schedule/{class_id}/teacher` for that). Requires `schedule:write`, and is served only where the deployment has writes enabled.

Class roster (legacy query-param form)

Everyone booked into one class — name, booking status, whether they checked in. `class_id` as a query param rather than a path segment; kept for callers that haven't repointed to the equivalent path-form endpoint.

Check out a catalog item for a client

Sells one service (package/drop-in) or retail product to a client via Mindbody's `checkoutshoppingcart`. Requires the `purchase:write` scope, and is served only where the deployment has writes enabled. **This does not write to Datastream.** The cart is proxied to Mindbody, the system of record; the sale reaches `ds_enriched.sales` on the next sync. Datastream still flows one direction. The item is resolved inside the key's site scope first — an item the key cannot read is a `404`, never a `403`. **`test` defaults TRUE**, the opposite of a booking: this endpoint moves money on a client's account, so a real sale must say `test: false` out loud. A dry run has Mindbody validate and price the whole cart (tax included) and returns the totals with `sale_id: null`. Mindbody owns the total, tax included, and refuses a payment off by a cent. Omit `payment_amount` and the endpoint resolves the authoritative total itself (one retry with Mindbody's own calculated figure). An explicit `payment_amount` is never corrected — a mismatch comes back as a `422` carrying Mindbody's message with the real total in it. **No card is charged.** The payment is recorded against a Mindbody custom payment method (e.g. 19 = "Square" on the Flow site), which is a label, not a processor. Whatever collects the actual money does so before calling this. Mindbody does not dedupe sales. Send an `Idempotency-Key` and a repeat replays the first response instead of charging the account again.

Square Web Payments SDK bootstrap values for one Mindbody location

Application id, environment, the SDK script URL, and the Square location that `location_id` (Mindbody's) maps to — a console page needs all of it to render a card form and call `POST /v1/payments/square/charge`. Requires only a valid key — none of this is secret; the Web Payments SDK ships the application id to the browser by design. **`location_id` is required.** One Square merchant account (one application id, one access token) serves every Flow studio, but Mindbody's `location_id` and Square's own location id are different id spaces with no natural correspondence, so the mapping lives in `ds_api.square_location_map` (`src/db/square-locations.ts`), one row per (site, Mindbody location). Tenancy is proven the same way `/v1/purchases` proves it for an item: the location is read inside the key's scope first, a `404` for one it cannot see, never a `403` that confirms it exists. Returns `503` when the account itself isn't configured (`SQUARE_APPLICATION_ID` / `SQUARE_ACCESS_TOKEN`), or when this specific location has no Square location mapped yet.

Charge a card via Square and check out the cart in Mindbody

The endpoint `/v1/purchases` deliberately does not have: this one actually moves money. Requires the `payment:write` scope, kept separate from `purchase:write` because it can debit a card, and is served only where the deployment has writes enabled and Square is configured. Runs three steps, in order, per the square-mbo-payments design (verified against production Mindbody and the Square sandbox, 2026-07-30): 1. A `test: true` Mindbody checkout to learn the authoritative total — tax included — **before Square is touched**. 2. `POST /v2/payments` at Square for exactly that figure, using `source_id` (the Web Payments SDK token from the browser; a card number is never seen server-side). 3. A `test: false` Mindbody checkout at that same figure, recorded against the deployment's Square custom payment method (`SQUARE_MBO_PAYMENT_METHOD_ID`, 19 on the Flow site). **If step 3 fails after step 2 succeeded**, the card has already been charged. This endpoint refunds it automatically, alerts through flow-notify, and returns `422` naming what happened either way — it never leaves a charged-but-unfulfilled payment silent. Mindbody discards any payment reference it is given (`Payments[]. Metadata.Notes` verified stored `NULL`), so the Square payment id and the Mindbody sale id are linked only in this service's own ledger (`ds_api.square_payment`) — not automation for refunds issued in the Mindbody admin panel; that is a separate, unbuilt design. **`Idempotency-Key` is required, not optional** — unlike `/v1/purchases`, where the worst case of a dropped retry is a duplicate sale. Here it is a duplicate real charge.

Square subscription plan variations on this merchant account

Every `SUBSCRIPTION_PLAN_VARIATION` in the Square Catalog, flattened with its parent plan's name. `amount` is **dollars**, not Square's cents, matching every other money field in this API. Not site-filtered, and it cannot be: a Square Catalog belongs to the merchant account, not to a Datastream site. Behind `payment:write` rather than a read scope because it calls Square with the merchant credential — this is not a Datastream read, and `raw:read` keys are minted freely for developers.

Create a Square subscription plan with one variation

Creates a `SUBSCRIPTION_PLAN` and its first variation in one call. **A Square Catalog object cannot be deleted through this API**, only ignored, which is why `Idempotency-Key` is required and why this sits behind `ENABLE_WRITES` like every other upstream state change. Omit `periods` for a plan that renews indefinitely — that is what 18 of Flow's 25 Mindbody contracts actually are (`ContractAutomaticallyRenews`). The 6 that stop after a year are `periods: 12`. Prices are tax inclusive, matching Mindbody's own contract totals.

A client's Square subscriptions, live status joined to the Mindbody link

Square is authoritative for `square_status`; this service's own `ds_api.square_subscription` is authoritative for which Mindbody contract a subscription stands for, because Square's Subscription object has no `reference_id` and no metadata to hold one. A row recorded here but absent at Square comes back with `square_status: null`, and a subscription at Square with no local row comes back with `recorded_status: "not_recorded"`. Neither is dropped — that divergence is exactly what a reconciliation job looks for. `is_winding_down` is the field to read, not `status`: Square keeps a cancelled subscription `ACTIVE` until the period already paid for ends.

Enrol a client on a Square subscription (starts real recurring billing)

Find-or-create the Square Customer, store the card against it, create the subscription — in that order. Square charges the first period immediately unless `start_date` is in the future, so **a 201 means real money has already moved**. **Nothing is written to Mindbody.** The response carries `mbo_entitlement: "none"` rather than letting a caller infer from a 201 that the member can book a class. The per-cycle Mindbody write hangs off the `invoice.payment_made` webhook and is not built: Mindbody's `/sale/purchasecontract` accepts no custom payment method (only `CreditCardInfo`, `StoredCardInfo`, `UseDirectDebit`, `UseAccountCredit`), so there is no way to create a Mindbody contract that Mindbody will not also try to bill, and which write replaces it is an open decision. Send **exactly one** of `source_id` (a fresh Web Payments SDK token to save) or `card_id` (a card already on file for this client; it is checked against the customer's own cards before use). Only credit and debit cards can be stored — Apple Pay, Google Pay, Cash App Pay and ACH cannot, so a token from those flows will be rejected by Square. A client with neither a name nor an email address in Mindbody is refused with `409`: Square silently deactivates such a subscription at the next billing cycle.

Cancel a Square subscription at the end of the paid period

Square's only cancel semantics, and the right ones: the member keeps what they have already paid for. The subscription stays `ACTIVE` with a `canceled_date` until that date passes, so read `is_winding_down`, not `status`. Tenancy comes from this service's own record of the subscription, so a subscription created outside this service — straight in the Square dashboard, say — cannot be cancelled here. That is deliberate: the row is the only thing tying a Square subscription to a Flow site, and cancelling on a caller-supplied id alone would let any `payment:write` key cancel anything on the merchant account. Changes nothing in Mindbody, because nothing was created there.

Square webhook deliveries this service has received

The audit trail behind `POST /webhooks/square`, most recent first — including deliveries whose signature failed to verify, flagged by `signature_verified`, because a forged or misconfigured delivery is exactly what you want a record of. Not site-scoped: a Square webhook envelope carries a merchant id and a Square location id, neither of which maps to a Datastream site without first reading the payload.

Sale headers from the enriched layer (scope enriched:read)

One row per sale with item_count / payment_count / sale_total / payment_total rollups — most reads never need the line tables. `sale_date` is date-only; `transacted_at` is the datetime when known (prefer it for clocks/sorting). Requires at least one narrowing filter. `adjusted=true` returns 400 until sale_adjusted exists (PLAN.md §9): refusing loudly beats silently serving unadjusted numbers. A sale made through `POST /v1/purchases` appears here before the mirror has it, carrying `source: "pending_write"` and counted in `total_count`. It retires the moment the sync carries the same sale id. Such a response is `Cache-Control: no-store`. The same applies to `/v1/sales/{id}`, `/{id}/items` and `/{id}/payments`. Every row also carries `source` as upstream provenance — `"mbo"` today for every synced row (ds_enriched v2's provenance column, see docs/DS-ENRICHED-V2.md). It exists so a future second raw source (e.g. a Momence/Arketa mirror) can land in the same tables without growing a parallel set of prefixed columns; `"pending_write"` is this API's own overlay value layered on top of that column, not a competing source.

Line items for multiple sales in one call (batch)

Batched form of `/v1/sales/{sale_id}/items` — pass `sale_ids` as a comma-separated list (same convention as every other multi-value param) and get every matching line back in one flat list, one `WHERE sale_id IN (...)` query instead of one request per sale. Built for purchase-history style pages that otherwise fire two requests per sale after listing them. Registered ahead of `/v1/sales/{sale_id}` so the literal path `items` is not swallowed by that route's `sale_id` parameter.

Payments for multiple sales in one call (batch)

Batched form of `/v1/sales/{sale_id}/payments` — same `sale_ids` convention and same IN (...) shape as `/v1/sales/items`.

One sale header

The transaction record itself (date, client, location, totals) from the enriched layer — line items and payments are separate reads (`/v1/sales/{sale_id}/items`, `/payments`), joined by this same `sale_id`.

Line items of a sale (revenue earned)

Never sum together with /payments — the same money appears in both. Returns carry a negative `unit_price` AND a negative `item_quantity`: MBO puts the sign in both columns, so the naive `unit_price * item_quantity` flips a refund back to positive. Extended list price is `unit_price * ABS(item_quantity)`. `total_amount` is already signed and net of discount, so a purchase-history total is simply `SUM(total_amount)`; the discount is the gap between that and the extended list price. Verified against MBO's ALL Purchases PDF. Each row carries `source` (`ds_enriched.sale_items.source`, default `"mbo"`) — provenance, same convention as `/v1/sales`.

Payments of a sale (money received)

Never sum together with /items — the same money appears in both. The two method fields read backwards from what the names suggest, and this is MBO's naming carried through verbatim: `payment_type` is the human label MBO prints on its reports ("Square", "Account", "Credit Card", "Comp/Guest"); `payment_method` is the numeric MBO code behind it ("19", "16", "4", "7"). Display `payment_type`; match on `payment_method`. Each row carries `source` (`ds_enriched.sale_payments.source`, default `"mbo"`) — provenance, same convention as `/v1/sales`.

Sell a membership to a client

Buys a contract (membership/autopay) for a client via Mindbody's `purchasecontract`. Requires the `contract:write` scope — deliberately separate from `purchase:write`, because a membership creates a recurring billing obligation rather than a one-off charge. **Mindbody has no dry run for this endpoint**, and it silently ignores unknown fields — verified against production on 2026-08-02: `Test: true` and a deliberately bogus field produced byte-identical responses. So `test` is never forwarded. `test: true` (**the default**) runs a local preflight instead: it reads the contract, checks it is purchasable at the location Mindbody would check against, and returns the terms and the amounts that would be charged, with `validated_locally_only: true`. It cannot tell you whether the card will authorise or whether the studio's own rules allow the sale — only a real purchase does that. A real purchase (`test: false`) additionally requires **`confirm_amount`**, the first payment the caller expects to charge. It must match the catalog or the request is a `409`. There is no dry run and the amount is otherwise absent from the request, so this is what stops a consumer whose catalog has drifted from silently charging the wrong figure. **Payment sources:** `use_account_credit` (draws on the client's Mindbody account balance) or `stored_card` (charges the card on file). Raw card details are refused with a `400` — accepting a PAN would place this service in PCI DSS scope. `use_direct_debit` is refused too: Mindbody answers `Direct debit not enabled` for this site. Only a stale staff token is retried. The purchase itself never is — a retry Mindbody accepted the first time would enrol the client twice. Send an `Idempotency-Key`.

Purchasable services and packages

Backed by `ds_mbo.service`. Hides discontinued packages unless `discontinued=true`. The legacy `location_id` filter is deliberately not implemented — it would require matching inside the `sellAtLocationIds` JSON array, whose element type is unverified, and a filter that quietly matches nothing is worse than an absent one. `location_ids` is returned on every row for client-side filtering. **Not yet populated:** `description`, `is_auto_renewing`.

The contract catalog — memberships and autopays a client can buy

ds_mbo.sale_contract: what is FOR SALE, as opposed to /v1/clients/{client_id}/contracts, which is what a client HOLDS. MBO returns contracts per location, so a contract sold at three studios appears three times, each row carrying its own `location_id`. Pricing is quoted as `first_payment_total`, `recurring_payment_total` and `total_contract_amount`; `contract_items` carries the line items the contract grants. `description` is not included in this list response — it was 69.5KB of a 214KB payload (31%) across 174 rows. Fetch it on demand from `GET /v1/contracts/{contract_id}/description` rather than expecting it inline. Carries `source` as upstream provenance, `"mbo"` today (see docs/DS-ENRICHED-V2.md).

One contract from the catalog

The sellable membership definition itself (terms, pricing, autopay schedule) — not a client's holding of it. For what a specific client actually has, see `/v1/clients/{client_id}/contracts`. Includes `description`, unlike the list response.

One contract's description

The contract name and full HTML description, keyed by `contract_id` — the same field every `/v1/contracts` row carries. Split out of `/v1/contracts` because the description text was ~31% of that endpoint's payload by weight; this route is meant to be fetched on demand (e.g. when a user expands a contract for details) and cached long by the client, not called once per list row.

The retail catalog — merchandise, not class packs

`ds_mbo.product`: water bottles, apparel, cacao, gift cards. Distinct from `/v1/packages`, which is `ds_mbo.service` (class packs and drop-ins). MBO's `/sale/products` was not pulled until 2026-08-01, so this is empty until the sync's next SalesFamily run — an empty list here means "not synced yet", not "no retail". Every field except `product_id` and `site_id` is currently unverified against real rows; see `/status/schema-assumptions`. Carries `source` as upstream provenance, `"mbo"` today (see docs/DS-ENRICHED-V2.md).

One retail product

A single physical/retail item from the catalog (mats, apparel, gift cards) — not a class package or membership contract.

Promo code definitions and their terms

ds_mbo.promo_code — the codes that exist and what they do: `discount_type` ("Percent" or "FlatRate") with `discount_amount`, the `activation_date`/`expiration_date` window, `days_valid`, `max_uses`, and `applicable_items`. Definitions only. Datastream records the discount a sale received (`sale_items.discount_amount`) but not which code produced it, and MBO's sale payload has no promo field — so redemption counts are not available from this API at any endpoint. `current=true` is the practical filter: active AND inside its date window. Most expired codes are still flagged active in MBO, so `active=true` alone over-reports what a student could actually redeem. Open-ended codes carry an expiration of 2099-12-31. Carries `source` as upstream provenance, `"mbo"` today (see docs/DS-ENRICHED-V2.md).

One promo code

Discount code definition — percent/amount off, validity window, applicable services. Redemption is tracked on the sale, not here.

Staff and instructors

Backed by `ds_mbo.staff`. **Not yet populated:** `slug`. The mirror's staff entity does not carry it — the legacy API read it from `mb_flow_staff` on the warehouse. **`bio` is not returned here.** It is a long free-text biography that was ~62% of this endpoint's payload by weight at `limit=1000`; fetch it on demand from `GET /v1/staff/{staff_id}/bio` instead.

One staff member's biography

A teacher's full-text biography, keyed by `staff_id` — the same field every `/v1/staff` row carries. Split out of `/v1/staff` because `bio` was ~62% of that endpoint's payload by weight at `limit=1000`; this route is meant to be fetched on demand (e.g. when a user expands a teacher profile) and cached long by the client, not called once per staff row.

Studio locations

Backed by `ds_mbo.location`. PLAN.md §6 listed `ds_config` as the source, but `ds_config.sites` is the tenant registry and has no addresses; the mirror holds the data matching this shape. Requires `raw:read`. **Not yet populated:** `slug`, `image_url`. `amenities` passes through MBO's array of objects rather than the array of strings the legacy schema advertised.

Bookable rooms at the site

`ds_mbo.resource` — `{room_id, name}`. MBO returns nothing else on `/site/resources`: **no capacity and no location**. Room capacity lives in the Portal's own `cms_rooms_capacity`, and the room↔location pairing is only observable through classes, which carry both. Carries `source` as upstream provenance, `"mbo"` today (see docs/DS-ENRICHED-V2.md).

Enrollments, workshops, and events

Backed by `ds_mbo.enrollment`. **Not yet populated:** `max_capacity`, `total_booked` — the enrollment entity has no counts, and the legacy API aggregated them warehouse-side. `pricing` is not served at all: it came from `cms_pricing` on the Portal, which is another product's schema (CLAUDE.md rule 5).

One event

A single enrollment — a training, retreat, or workshop by MBO `event_id`. An event is a date RANGE, not one class instance: a mentorship can run May → January as one row, so this shows the span rather than pretending it's a single session.

One event (legacy query-param form)

Same event lookup as `/v1/events/{event_id}`, with `event_id` as a query param instead of a path segment. Kept for callers that haven't repointed yet.

Daily revenue by location and category (cash-accounting adjusted)

Replaces ai_sales_snapshot_v2 — specifically dw_flow's vw__dm_sales_adjusted shape, which is what the old Redash dashboards actually read. Sums ds_enriched.sale_items (excluding line items literally named "Tip") and unions a negated slice of ds_enriched.sale_payments for non-cash payment types (Groupon, ClassPass, Debt Write Off, LivingSocial, Comp/Guest, Flow Yoga, Gift Card, Account) — those sales show full retail price on the purchased item but no real cash came in, so the payment leg offsets it back out. Excludes ds_enriched.config_excluded_clients (test/demo accounts). Requires `date_from`/`date_to` in practice — an unbounded call can time out over the full sales history.

Point-in-time active membership counts by type and location

Replaces ai_membership_snapshot_v2's membership-by-type chart (dw_flow.pc_memberByType). For each client, ranks their ds_enriched.client_contracts rows by start_date/agreement_date and keeps only the most recent contract that is active/unexpired/ non-terminated as of `as_of` (default today) — a true point-in-time snapshot, not a raw count of every contract row started in a date range. Excludes config_excluded_clients. `changeInMembership` (new/renewed/cancelled/expired, diffed day-over-day by the old cron against yesterday's snapshot) has no equivalent — there is no daily snapshot history table here to diff against — and is intentionally omitted.

Class attendance and capacity rolled up by class/teacher/day-time

Replaces ai_class_performance_snapshot_v2 + ai_class_detail_snapshot_v2 in one shape, from ds_enriched.classes. "Best"/"worst" rankings (best class times, top candidates for replacement) are this same rollup sorted ascending instead of descending on avgClassAttendance — there is nothing further to compute. Not excluded: config_excluded_clients. classes.total_signed_in is a count at the class-occurrence grain (no client_id column on this table), so a test account's visit can't be excluded here without a per-class fan-out join down to class_visit.

New students by first signed-in visit within a required date window

Replaces the dw_flow first-visit marts (vw_first_visits / the v1 client_first_visits table), which were removed in the v2 enriched rebuild (docs/DS-ENRICHED-V2.md, "Gone in v2"). Computed live from the raw mirror ds_mbo.class_visit: ranks every signed-in visit per client by start time within the requested window, keeps rank 1, groups by the date of that first visit. Excludes config_excluded_clients. `date_from`/`date_to` are REQUIRED — 400 without both. "New student" means *first signed-in visit within the queried window*, not an absolute lifetime-first-visit-ever: a client whose true first visit predates date_from but who visits again inside the window will show up as a "new student" on that later date. An earlier version ranked each client's entire visit history to preserve a true lifetime-first answer and filtered the date window only after ranking; that 503'd in production (class_visit is 897K+ rows) even with a supporting index, because the volume of history scanned per client — not just the sort — was the actual cost. This is a deliberate, documented tradeoff, and matches how comparable "new users in period" reports work elsewhere. `location_id`, if given, narrows the ranking window further — a client's first signed-in visit *at that location, within the date window*.

Promo code catalog and discounted sale line items (unjoined)

Replaces ai_promo_detail_snapshot_v2. Datastream does not tie a sale to the promo code that discounted it — sale_items only carries discount_amount, no code reference, and MBO's sale payload itself has no promo field to mirror. So this returns two independent lists: the promo code catalog (ds_mbo.promo_code) and sale_items rows with a discount in the date window (config_excluded_clients-filtered) — not a fabricated join. A caller who needs "how much did FREEWEEK cost us" cannot get that from Datastream today.

Mindbody API call volume — sync side and write side

Requires `config:read` (ops data, not client data). Two sources, tagged by `source`, one row per (date, site, family|endpoint): - `sync` — itflow_datastream's scheduled and webhook-driven pulls, read from `ds_config.sync_control_execution` (that service's own schema, read-only here). `family` is a sync family (`ClassFamily`, `TransactionFamily`, …); `endpoint` is null. - `write_api` — this service's own writes (bookings, cancels, check-ins, schedule/teacher edits), counted at the single choke point in `mboFetch()` and stored in `ds_api.mbo_call_log`. `endpoint` is the MBO path (e.g. `/class/addclienttoclass`); `family` is null. Defaults to the last 7 days if neither `date_from` nor `date_to` is given — this is a spend dashboard, not a full-history report. **Not covered:** the legacy Java sync service on AWS, which also calls Mindbody and is not instrumented anywhere this API can read. That spend has to be read from Mindbody's own account dashboard, not this endpoint — the response `note` field repeats this so a chart never implies otherwise. **Degrades gracefully** if `ds_api.mbo_call_log` (sql/019) hasn't been applied yet: the response falls back to sync-only rows rather than erroring.

Tenant registry for this key

Backed by `ds_config.sites`, scoped to the key's own sites. Requires `config:read`. An empty response does not mean "no such site": two data-bearing site ids are absent from the registry entirely, so a key can be bound to a site that has data but no registry row.

Look up clients by id, unique id, email, status, or modified_since

Client profile rows: `client_id`, name, `email`, `mobile_phone`, `status` / `active`, `account_balance`, `creation_date`, `home_location_id`, `address`, `address2`, `city`, `state`, `postal_code`, `country`, etc. Requires at least one narrowing filter — an unfiltered call is a full per-site dump and returns 400. `email` resolves through the enriched layer's indexed email column, then serves the fresh mirror row; an email registered since the last enriched refresh will not resolve. Carries `source` as upstream provenance, `"mbo"` today (see docs/DS-ENRICHED-V2.md).

Typeahead search — name, email, phone, or client_id in one `q` param

For a search-as-you-type box, not a list page: capped small (default and effective limit 8) and always ordered by name. `q` matches against name, email, mobile_phone, client_unique_id, and client_id together, so the caller doesn't need to know which field the user typed into. Reads the enriched mirror (real columns, not the raw MBO JSON), so it carries the same freshness caveat as `/v1/clients?email=`: refreshes on the enriched transform's schedule, not live.

Bookings — upcoming, or a window of visit history

Defaults to start_time >= now. `include_past=true` lifts the floor; `date_from`/`date_to` set an explicit window instead and suppress the default. Joined to class/description/staff/location for display names. For visit history, prefer a window: results are ordered by start time ASC and capped at limit=1000, so `include_past=true` on a long-standing member returns their OLDEST page of visits, not their most recent. Carries `source` as upstream provenance, `"mbo"` today (see docs/DS-ENRICHED-V2.md).

Active passes with remaining balance ("My Passes & Credits")

Current services with an unexpired window; `remaining_count` / `total_count` answer "7 of 10 classes left, expires June 15". Subset of `/services` useful for a profile "active credits" strip.

All service/pass records for a client (raw, incl. expired)

Every client_service row: packs, unlimited periods, intro offers. `is_current` splits active vs past; includes `service_name`, `remaining_count`, `total_count`, `active_date`, `expiration_date`, `program_name`. Reads from `ds_enriched.client_services` (cut over 2026-08-04); carries `source` as upstream provenance, `"mbo"` today (see docs/DS-ENRICHED-V2.md).

Contracts / autopay memberships for a client

What the client **holds** (not the sellable catalog at `/v1/contracts`). Fields include `contract_name`, `autopay_status`, `agreement_date`, `start_date`, `end_date`. Use autopay_status / end_date to separate active memberships from past ones. Reads from `ds_enriched.client_contracts` (cut over 2026-08-04; memberships are merged into this table upstream); carries `source` as upstream provenance, `"mbo"` today (see docs/DS-ENRICHED-V2.md).

Payment transaction history for a client

Card charges for the client: `amount`, `status`, `transaction_time`, `card_type`, `cc_last_four`, `is_settled`. Card expiration month/year are deliberately NOT mapped anywhere in this service and can never appear. Reads from `ds_mbo.client_transaction`; carries `source` as upstream provenance, `"mbo"` today (see docs/DS-ENRICHED-V2.md).

Liveness and database connectivity

Unauthenticated. Confirms the pool can run a query against cms-pg-prd — not a check of any individual sync job's freshness (see the per-schema sync_state tables for that).

This OpenAPI document (JSON)

Served as JSON (datastream-api's equivalent serves YAML) — newapi's combined API docs page (/) fetches both and renders one operation list across both products.

The tenant this key belongs to, with its sites and origins

Reflexive by design — a key resolves to exactly one tenant, so this is not a directory. Returns tenant, sites, origins, and the calling key's scopes.

Find contacts

Free-text `q` dispatches on shape: UUID → contact_id, all digits → MBO client id, contains `@` → email, otherwise name substring. Without `q`, at least one of `email`, `external_id`, `marketable`, or `modified_since` is required — an unfiltered scan of ~100k+ contacts is rejected. `external_id` resolves through `contact_identity` and requires `id_type`, because ids from different sources collide.

A single contact

The contact record identity resolution settled on — marketable status, lifecycle stage, matched-by info. Everything downstream (the profile endpoint's HubSpot/tracking/consent sections) keys off this one contact_id, not the raw MBO client id or email.

Every external id resolving to this contact

The crosswalk: every mbo_client → hubspot_contact → tracking_visitor → email identifier this contact absorbed during resolution, each with its source and first-seen date. When a resolution looks wrong — two people merged, or a match that shouldn't have happened — this is the table that explains why, since resolution order is mbo_client → hubspot_contact → tracking_visitor → email (email is a last resort; 27,096 contacts share an address with another contact).

This person's page views

Matched via: 1. `tracking_visitor` identities (cookie stitch), and/or 2. `(site_id, client_id)` on the visit matching an `mbo_client` identity (HubSpot page-visit backfill and identified tracker hits). **Single-source by default.** `source` defaults to `blended`, which serves each instant from exactly one feed: HubSpot page-view history before the tenant's first tracker hit, the tracker from there on. HubSpot rows at or after that cutover are validation data (see `/v1/visits/reconciliation`) and are left out, so a total can never double-count the handoff. `source=all` returns the raw union, `source=hubspot` both HubSpot feeds, and a single source key just that feed. Newest first.

Full profile for one person

Everything this platform holds on one contact in one response: contact row, identities, consent, tracking summary (by source + recent pages), HubSpot properties (human-written fields when matched), and optional live Datastream operational data when configured.

Marketing/web activity feed for one contact

Chronological Funnel events for a person timeline (newest first): | kind | Source | |---|---| | `page_view` | `funnel_tracking.page_visit` (`status` / `meta.source`: `tracker`, `hubspot_backfill`, …) | | `form_submission` | HubSpot form submissions by contact email | | `chat` | Conversations threads (web chat channel) | | `sms_thread` | Conversations SMS threads | | `sms_campaign` | Outbound CRM marketing SMS | | `email_sent` | Marketing email SENT (HubSpot `/email/public/v1/events`) | | `email_open` / `email_click` / `email_bounce` | Same events API | **Email delivery is not its own row.** HubSpot `DELIVERED` is folded into the matching `email_sent` as `status: "Delivered"` (or `"Bounced"`) via `email_campaign_id`. A DELIVERED with no SENT in the window still emits one `email_sent` with status Delivered. Studio class visits and purchases stay on Datastream (`/v1/clients/…/enrollments`, `/v1/sales`). Linked via MBO client → contact, then HubSpot contact ids (identity or email match on crm_object, including merged vids). Email events match on contact email (`recipient`). Engagement rows are loaded by cron into `funnel_hubspot.email_event` (not live HubSpot at request time).

What each website-history feed covers, and where they hand off

Website history arrives from two feeds — HubSpot's page-view export (`hubspot_backfill`, `hubspot_import`) and our own cookie (`tracker`) — and HubSpot is being cancelled. This reports each feed's first and last hit, the `blend_cutover` a blended read uses, the `overlap` window where a tracker-vs-HubSpot comparison is possible, and any `gap` neither feed covered. Read this before summing anything. Measured for tenant `flow` on 2026-08-04: HubSpot 2025-04-14 → 2026-01-02 (601,978 rows) then a 382-row trickle to 2026-04-01, tracker 2026-04-02 → now (787k rows and counting) — contiguous, with no overlap yet.

Which students each feed identified, tracker versus HubSpot

The validation pass to run while HubSpot is still paid for — and it compares **students, not page-view volume**. Volume cannot settle this: HubSpot only recorded views for contacts it knew, the tracker also sees anonymous traffic, bots and crawlers hit the site constantly, and the front-desk kiosk concentrates many people onto a few identities (measured July 2026: 29 `client_id`s absorbed 9,295 mbocheckin.com views). Each bucket therefore counts distinct Mindbody `client_id`s: `both_students`, `tracker_only_students`, and the one that matters, `hubspot_only_students` — people HubSpot tied to a visit and we did not. `coverage_pct` is both / hubspot_students; `missed_pct` its complement. Measured over the full Apr–Aug 2026 overlap: coverage 85–86% every month, so ~600 students a month are attributed by HubSpot and not by us. Of July's 618, 253 arrived with `utm_medium=email` — HubSpot knew them from an email click, which our cookie cannot see unless they then log in or book. `verdict`: `no_overlap` (no bucket has both feeds — untested, not passing), `match`, `drift`, or `not_assessed` for `metrics=visits`. Drift is one-sided on purpose: identifying more people than HubSpot is not drift.

Inbox threads (newest first)

HubSpot Conversations threads (chat, email, SMS channel, etc.). Use `channel_id` / `channel_ids` for multi-select. Full-text `q` searches subject and message bodies.

One conversation thread

HubSpot Conversations metadata for one thread — status (open/closed), channel (live chat = 1000, email = 1002, SMS = 1009, etc.), assigned agent. Message bodies are the separate `/messages` sub-resource.

Messages in a thread (chronological)

Every message in the thread, oldest first — type=MESSAGE only (system/bot events are filtered out). An empty text_body usually means an attachment, not a blank message.

Outbound marketing SMS (CRM Communications)

Default is marketing/campaign sends (`source=CRM`, `view=sends`). - `view=blasts` — group similar copy into blasts - `source=CONVERSATIONS` — legacy list of channel-1009 threads (prefer `/v1/conversations` for two-way SMS)

One blast + recipient sends

A "blast" is not a HubSpot object — it's this API's own grouping of individual CRM-logged sends that share the same message template (keyed by an md5 of the template text, taken from the list view's blast_id). Returns the shared copy plus every recipient send that matched it.

One outbound marketing SMS by HubSpot object id

A single individual CRM-logged text (CRM Communications API, channel_type=SMS) — the "Sends" view's detail, not a Marketing SMS campaign asset (see `/v1/hubspot/sms-campaigns` for those).

Legacy conversation-style SMS thread detail

Predates the Conversations integration — kept only for callers still pointed at it. Prefer `/v1/conversations/{thread_id}/messages` for channel 1009 (SMS); it's the same underlying thread, read through the current, actively-maintained path.

HubSpot lists / segments (export)

Deliberately not `/v1/segments` (reserved for platform-owned rules). Per list: `hubspot_size` (what HubSpot reported) vs `member_count` (rows exported). They diverge when a list was recalculating mid-export.

One list + flattened filters

`filters` are leaves with `group_label` / `group_operator` for AND/OR nesting. `filter_branch` is the raw HubSpot tree.

Members of a list

Stored as HubSpot record ids; names/emails/MBO joined at read time. Null `contact_id` = not yet crosswalked into the spine.

HubSpot form definitions

The forms themselves — name, field list, submission/view counts — not the individual responses people typed in. See the `/submissions` sub-resource for those.

One form (fields + metadata)

One form's full field list (label, name, type, required/hidden) alongside its submission and view counts.

Submissions for a form

Every response to one form, newest first — the field values a visitor actually typed, plus which contact (if any) it resolved to.

One form submission

A single response by its HubSpot conversion_id — full field-by-field answers for that one submission.

Marketing email campaigns

From `funnel_hubspot.asset` kind `marketing_email` (campaign definitions + rollup stats). Per-recipient send/open/click history is **not** listed here — use `GET /v1/contacts/{id}/activity` (`email_*` kinds from `funnel_hubspot.email_event`, filled by the HubSpot email-events sync). Default console filter is `published=true` (batch + automated + AB), not only `state=PUBLISHED`.

One marketing email + HTML body preview

Includes performance stats and `body_html` reconstructed from HubSpot content widgets (module HTML, images, buttons, styleSettings).

HubSpot CTAs (static UI export)

Not available on a public API we can cron. Loaded from a one-shot parse of the HubSpot CTAs listing (`scripts/load-hubspot-ctas.ts`).

One CTA

One HubSpot call-to-action's full record — type, image, views, submissions, submission rate. Sourced from a one-shot UI export (`funnel_hubspot.asset` kind=cta), not a live API sync — CTAs aren't reachable with our current private-app scopes.

HubSpot Marketing SMS campaigns

Real Marketing SMS campaign assets — name, delivered count, click rate, publish/send date, created-by — a different HubSpot object from `/v1/sms` (which covers individual CRM-logged texts). Not reachable via a live API sync: our private-app token lacks the Marketing SMS scope, so this is a one-shot UI export landed in `funnel_hubspot.asset` (kind=marketing_sms), same pattern as CTAs.

One Marketing SMS campaign

Full record for one campaign, including the raw exported document.

CloudTalk call history

Every call (incoming/outgoing, answered/missed/voicemail) synced from CloudTalk's own REST API into `funnel_cloudtalk.call`. "Missed" is derived at sync time, not read live: CloudTalk's IVR auto-answers every call, so `answered_at` is set even when no human picked up — the real signal is talking_time = 0 on an incoming call. `status` here is already resolved to one of answered / missed / voicemail.

One call

Full detail for one call — both parties' numbers, studio line, agent, duration, waiting time, recording link — including the raw synced document.

Stream a call's recording audio

Proxies CloudTalk's undocumented `/calls/recording/{id}.json` endpoint (the dashboard's own `recording_link` 301s to a cookie-gated SPA page, not raw audio) and streams the `audio/wav` bytes back directly.