> ## Documentation Index > Fetch the complete documentation index at: https://docs.useautumn.com/llms.txt > Use this file to discover all available pages before exploring further. # Rebuild a customer or entity > One query each that returns the same JSON as POST /v1/customers.get and POST /v1/entities.get at API version 2.6, built from the v2.6 tables. Each query below returns one row with one JSON column, in the shape the API returns at version 2.6 without any `expand`. Replace `cus_123` (and `user_1` for an entity) with your ids, and `` and `` (or `` and ``) with the names Autumn sent you. [Rebuild a customer](https://docs.useautumn.com/documentation/lakehouse/v2-6/querying.md#rebuild-a-customer) lists which table each part comes from. The result is as current as the tables. Balances were computed at `v2_6_balances.as_of`. ## Customer `POST /v1/customers.get`. Subscriptions and purchases are the customer-level live plans (`active`, `past_due` and `scheduled`, with `past_due` shown as `active`), in `position` order. Balances and flags are the customer's view (`scope = 'customer'`), and each breakdown is in `position` order, the order Autumn deducts in. > [tab] ClickHouse > > ```sql theme={null} > WITH > c AS ( > SELECT * > FROM ``.`.v2_6_customers` > WHERE env = 'live' AND customer_id = 'cus_123' > ), > subs AS ( > SELECT s.internal_customer_id AS icid, > arraySort(x -> x.1, groupArray((s.position, formatRowNoNewline('JSONEachRow', > s.id AS id, s.plan_id AS plan_id, s.auto_enable AS auto_enable, s.add_on AS add_on, > if(s.status = 'past_due', 'active', s.status) AS status, s.past_due AS past_due, > s.canceled_at AS canceled_at, s.expires_at AS expires_at, s.trial_ends_at AS trial_ends_at, > s.started_at AS started_at, s.current_period_start AS current_period_start, > s.current_period_end AS current_period_end, s.quantity AS quantity, s.scope AS scope > )))) AS rows > FROM ``.`.v2_6_subscriptions` AS s > INNER JOIN c ON c.org_id = s.org_id AND c.env = s.env AND c.internal_customer_id = s.internal_customer_id > WHERE coalesce(s.internal_entity_id, '') = '' > AND coalesce(s.customer_license_id, '') = '' > AND s.status IN ('active', 'past_due', 'scheduled') > GROUP BY icid > ), > purch AS ( > SELECT p.internal_customer_id AS icid, > arraySort(x -> x.1, groupArray((p.position, formatRowNoNewline('JSONEachRow', > p.plan_id AS plan_id, p.expires_at AS expires_at, p.started_at AS started_at, > p.quantity AS quantity, p.scope AS scope > )))) AS rows > FROM ``.`.v2_6_purchases` AS p > INNER JOIN c ON c.org_id = p.org_id AND c.env = p.env AND c.internal_customer_id = p.internal_customer_id > WHERE coalesce(p.internal_entity_id, '') = '' > AND p.status IN ('active', 'past_due', 'scheduled') > GROUP BY icid > ), > lic AS ( > SELECT l.internal_customer_id AS icid, > arraySort(x -> x.1, groupArray((concat(ifNull(l.parent_plan_id, ''), ' ', ifNull(l.license_plan_id, '')), formatRowNoNewline('JSONEachRow', > l.license_plan_id AS license_plan_id, l.parent_plan_id AS parent_plan_id, > l.license_plan_name AS license_plan_name, l.granted AS granted, l.usage AS usage, > l.remaining AS remaining, l.paid_quantity AS paid_quantity > )))) AS rows > FROM ``.`.v2_6_licenses` AS l > INNER JOIN c ON c.org_id = l.org_id AND c.env = l.env AND c.internal_customer_id = l.internal_customer_id > GROUP BY icid > ), > b AS ( > SELECT b.* > FROM ``.`.v2_6_balances` AS b > INNER JOIN c ON c.org_id = b.org_id AND c.env = b.env AND c.internal_customer_id = b.internal_customer_id > WHERE b.scope = 'customer' > ), > bd AS ( > SELECT d.balance_id AS balance_id, > arraySort(x -> x.1, groupArray((d.position, concat( > left(formatRowNoNewline('JSONEachRow', > d.id AS id, d.plan_id AS plan_id, d.included_grant AS included_grant, > d.prepaid_grant AS prepaid_grant, d.remaining AS remaining, d.usage AS usage, > d.unlimited AS unlimited, d.expires_at AS expires_at, d.source AS source > ), -1), > ',"reset":', if(d.reset_interval IS NULL, 'null', formatRowNoNewline('JSONEachRow', > d.reset_interval AS interval, d.reset_interval_count AS interval_count, > d.reset_resets_at AS resets_at)), > ',"price":', if(d.price_billing_method IS NULL AND d.price_amount IS NULL AND d.price_tiers IS NULL, 'null', concat( > left(formatRowNoNewline('JSONEachRow', > d.price_amount AS amount, d.price_tier_behavior AS tier_behavior, > d.price_billing_units AS billing_units, d.price_billing_method AS billing_method, > d.price_max_purchase AS max_purchase), -1), > ',"tiers":', ifNull(d.price_tiers, 'null'), '}')), > ',"allocation":', if(d.allocation_amount IS NULL, 'null', > formatRowNoNewline('JSONEachRow', d.allocation_amount AS amount)), > '}' > )))) AS rows > FROM ``.`.v2_6_breakdowns` AS d > INNER JOIN b ON b.org_id = d.org_id AND b.env = d.env AND b.balance_id = d.balance_id > GROUP BY balance_id > ), > ro AS ( > SELECT r.balance_id AS balance_id, > arraySort(x -> x.1, groupArray(((ifNull(r.expires_at, 0), r.rollover_id), formatRowNoNewline('JSONEachRow', > r.granted AS granted, r.balance AS balance, r.expires_at AS expires_at > )))) AS rows > FROM ``.`.v2_6_rollovers` AS r > INNER JOIN b ON b.org_id = r.org_id AND b.env = r.env AND b.balance_id = r.balance_id > GROUP BY balance_id > ), > bal AS ( > SELECT b.internal_customer_id AS icid, > groupArray(concat(toJSONString(b.feature_id), ':', > left(formatRowNoNewline('JSONEachRow', > b.feature_id AS feature_id, b.granted AS granted, b.remaining AS remaining, > b.usage AS usage, b.unlimited AS unlimited, b.overage_allowed AS overage_allowed, > b.max_purchase AS max_purchase, b.next_reset_at AS next_reset_at, > b.allocated AS allocated, b.unallocated AS unallocated > ), -1), > ',"breakdown":[', arrayStringConcat(arrayMap(x -> x.2, bd.rows), ','), ']', > ',"rollovers":[', arrayStringConcat(arrayMap(x -> x.2, ro.rows), ','), ']', > '}' > )) AS entries > FROM b > LEFT JOIN bd ON bd.balance_id = b.balance_id > LEFT JOIN ro ON ro.balance_id = b.balance_id > GROUP BY icid > ), > fl AS ( > SELECT f.internal_customer_id AS icid, > groupArray(concat(toJSONString(f.feature_id), ':', formatRowNoNewline('JSONEachRow', > f.id AS id, f.plan_id AS plan_id, f.expires_at AS expires_at, f.feature_id AS feature_id > ))) AS entries > FROM ``.`.v2_6_flags` AS f > INNER JOIN c ON c.org_id = f.org_id AND c.env = f.env AND c.internal_customer_id = f.internal_customer_id > WHERE f.scope = 'customer' > GROUP BY icid > ) > SELECT concat( > left(formatRowNoNewline('JSONEachRow', > c.customer_id AS id, c.name AS name, c.email AS email, c.created_at AS created_at, > c.fingerprint AS fingerprint, c.stripe_id AS stripe_id, c.env AS env, > ifNull(c.send_email_receipts, false) AS send_email_receipts > ), -1), > ',"metadata":', ifNull(c.metadata, '{}'), > ',"billing_controls":', ifNull(c.billing_controls, '{}'), > ',"subscriptions":[', arrayStringConcat(arrayMap(x -> x.2, subs.rows), ','), ']', > ',"purchases":[', arrayStringConcat(arrayMap(x -> x.2, purch.rows), ','), ']', > ',"licenses":[', arrayStringConcat(arrayMap(x -> x.2, lic.rows), ','), ']', > ',"balances":{', arrayStringConcat(bal.entries, ','), '}', > ',"flags":{', arrayStringConcat(fl.entries, ','), '}', > ',"config":', ifNull(c.config, 'null'), > ',"processors":', ifNull(c.processors, 'null'), > '}' > ) AS customer > FROM c > LEFT JOIN subs ON subs.icid = c.internal_customer_id > LEFT JOIN purch ON purch.icid = c.internal_customer_id > LEFT JOIN lic ON lic.icid = c.internal_customer_id > LEFT JOIN bal ON bal.icid = c.internal_customer_id > LEFT JOIN fl ON fl.icid = c.internal_customer_id > SETTINGS output_format_json_quote_64bit_integers = 0; > ``` > [tab] BigQuery > > ```sql theme={null} > WITH > c AS ( > SELECT * > FROM `..v2_6_customers` > WHERE env = 'live' AND customer_id = 'cus_123' > ), > subs AS ( > SELECT s.internal_customer_id, > TO_JSON(ARRAY_AGG(STRUCT( > s.id AS id, s.plan_id AS plan_id, s.auto_enable AS auto_enable, s.add_on AS add_on, > IF(s.status = 'past_due', 'active', s.status) AS status, s.past_due AS past_due, > s.canceled_at AS canceled_at, s.expires_at AS expires_at, s.trial_ends_at AS trial_ends_at, > s.started_at AS started_at, s.current_period_start AS current_period_start, > s.current_period_end AS current_period_end, s.quantity AS quantity, s.scope AS scope > ) ORDER BY s.position)) AS j > FROM `..v2_6_subscriptions` AS s > JOIN c ON c.org_id = s.org_id AND c.env = s.env AND c.internal_customer_id = s.internal_customer_id > WHERE s.internal_entity_id IS NULL > AND s.customer_license_id IS NULL > AND s.status IN ('active', 'past_due', 'scheduled') > GROUP BY s.internal_customer_id > ), > purch AS ( > SELECT p.internal_customer_id, > TO_JSON(ARRAY_AGG(STRUCT( > p.plan_id AS plan_id, p.expires_at AS expires_at, p.started_at AS started_at, > p.quantity AS quantity, p.scope AS scope > ) ORDER BY p.position)) AS j > FROM `..v2_6_purchases` AS p > JOIN c ON c.org_id = p.org_id AND c.env = p.env AND c.internal_customer_id = p.internal_customer_id > WHERE p.internal_entity_id IS NULL > AND p.status IN ('active', 'past_due', 'scheduled') > GROUP BY p.internal_customer_id > ), > lic AS ( > SELECT l.internal_customer_id, > TO_JSON(ARRAY_AGG(STRUCT( > l.license_plan_id AS license_plan_id, l.parent_plan_id AS parent_plan_id, > l.license_plan_name AS license_plan_name, l.granted AS granted, l.usage AS usage, > l.remaining AS remaining, l.paid_quantity AS paid_quantity > ) ORDER BY l.parent_plan_id, l.license_plan_id)) AS j > FROM `..v2_6_licenses` AS l > JOIN c ON c.org_id = l.org_id AND c.env = l.env AND c.internal_customer_id = l.internal_customer_id > GROUP BY l.internal_customer_id > ), > b AS ( > SELECT b.* > FROM `..v2_6_balances` AS b > JOIN c ON c.org_id = b.org_id AND c.env = b.env AND c.internal_customer_id = b.internal_customer_id > WHERE b.scope = 'customer' > ), > bd AS ( > SELECT d.balance_id, > TO_JSON(ARRAY_AGG(STRUCT( > d.id AS id, d.plan_id AS plan_id, d.included_grant AS included_grant, > d.prepaid_grant AS prepaid_grant, d.remaining AS remaining, d.usage AS usage, > d.unlimited AS unlimited, > IF(d.reset_interval IS NULL, NULL, STRUCT( > d.reset_interval AS `interval`, d.reset_interval_count AS interval_count, > d.reset_resets_at AS resets_at)) AS reset, > IF(d.price_billing_method IS NULL AND d.price_amount IS NULL AND d.price_tiers IS NULL, NULL, STRUCT( > d.price_amount AS amount, PARSE_JSON(d.price_tiers) AS tiers, > d.price_tier_behavior AS tier_behavior, d.price_billing_units AS billing_units, > d.price_billing_method AS billing_method, d.price_max_purchase AS max_purchase)) AS price, > d.expires_at AS expires_at, d.source AS source, > IF(d.allocation_amount IS NULL, NULL, STRUCT(d.allocation_amount AS amount)) AS allocation > ) ORDER BY d.position)) AS j > FROM `..v2_6_breakdowns` AS d > JOIN b ON b.org_id = d.org_id AND b.env = d.env AND b.balance_id = d.balance_id > GROUP BY d.balance_id > ), > ro AS ( > SELECT r.balance_id, > TO_JSON(ARRAY_AGG(STRUCT( > r.granted AS granted, r.balance AS balance, r.expires_at AS expires_at > ) ORDER BY r.expires_at, r.rollover_id)) AS j > FROM `..v2_6_rollovers` AS r > JOIN b ON b.org_id = r.org_id AND b.env = r.env AND b.balance_id = r.balance_id > GROUP BY r.balance_id > ), > bal AS ( > SELECT b.internal_customer_id, > JSON_OBJECT(ARRAY_AGG(b.feature_id), ARRAY_AGG(TO_JSON(STRUCT( > b.feature_id AS feature_id, b.granted AS granted, b.remaining AS remaining, > b.usage AS usage, b.unlimited AS unlimited, b.overage_allowed AS overage_allowed, > b.max_purchase AS max_purchase, b.next_reset_at AS next_reset_at, > COALESCE(bd.j, JSON '[]') AS breakdown, COALESCE(ro.j, JSON '[]') AS rollovers, > b.allocated AS allocated, b.unallocated AS unallocated > )))) AS j > FROM b > LEFT JOIN bd ON bd.balance_id = b.balance_id > LEFT JOIN ro ON ro.balance_id = b.balance_id > GROUP BY b.internal_customer_id > ), > fl AS ( > SELECT f.internal_customer_id, > JSON_OBJECT(ARRAY_AGG(f.feature_id), ARRAY_AGG(TO_JSON(STRUCT( > f.id AS id, f.plan_id AS plan_id, f.expires_at AS expires_at, f.feature_id AS feature_id > )))) AS j > FROM `..v2_6_flags` AS f > JOIN c ON c.org_id = f.org_id AND c.env = f.env AND c.internal_customer_id = f.internal_customer_id > WHERE f.scope = 'customer' > GROUP BY f.internal_customer_id > ) > SELECT TO_JSON(STRUCT( > c.customer_id AS id, c.name AS name, c.email AS email, c.created_at AS created_at, > c.fingerprint AS fingerprint, c.stripe_id AS stripe_id, c.env AS env, > COALESCE(PARSE_JSON(c.metadata), JSON '{}') AS metadata, > IFNULL(c.send_email_receipts, FALSE) AS send_email_receipts, > COALESCE(PARSE_JSON(c.billing_controls), JSON '{}') AS billing_controls, > COALESCE(subs.j, JSON '[]') AS subscriptions, > COALESCE(purch.j, JSON '[]') AS purchases, > COALESCE(lic.j, JSON '[]') AS licenses, > COALESCE(bal.j, JSON '{}') AS balances, > COALESCE(fl.j, JSON '{}') AS flags, > PARSE_JSON(c.config) AS config, > PARSE_JSON(c.processors) AS processors > )) AS customer > FROM c > LEFT JOIN subs ON subs.internal_customer_id = c.internal_customer_id > LEFT JOIN purch ON purch.internal_customer_id = c.internal_customer_id > LEFT JOIN lic ON lic.internal_customer_id = c.internal_customer_id > LEFT JOIN bal ON bal.internal_customer_id = c.internal_customer_id > LEFT JOIN fl ON fl.internal_customer_id = c.internal_customer_id; > ``` In ClickHouse, `output_format_json_quote_64bit_integers = 0` keeps epoch-ms timestamps as numbers instead of strings. ### Example response A customer on a custom version of Team that is canceled and moves to Pro at the end of the period, with a past-due add-on, a credit pack, credits shared between two seats by an allocation, a pool of messages fed by the seats, an unlimited feature and a flag. A custom plan looks like any other subscription here; `v2_6_subscriptions.is_custom` says which ones are custom. ```json theme={null} { "id": "cus_123", "name": "Example Co", "email": "billing@example.com", "created_at": 1768435200000, "fingerprint": null, "stripe_id": "cus_Sx1", "env": "live", "metadata": { "region": "eu" }, "send_email_receipts": true, "billing_controls": { "balance_allocations": [ { "feature_id": "credits", "interval": "month", "allocations": [ { "entity_id": "user_1", "amount": 300 }, { "entity_id": "user_2", "amount": 200 } ] } ], "spend_limits": [ { "feature_id": "credits", "enabled": true, "overage_limit": 50, "source": "customer" } ], "usage_alerts": [ { "feature_id": "credits", "enabled": true, "threshold": 80, "threshold_type": "usage_percentage", "basis": "balance", "source": "plan" } ], "overage_allowed": [ { "feature_id": "credits", "enabled": true, "source": "plan" } ] }, "subscriptions": [ { "id": "cp_02", "plan_id": "pro", "auto_enable": false, "add_on": false, "status": "scheduled", "past_due": false, "canceled_at": null, "expires_at": null, "trial_ends_at": null, "started_at": 1796083200000, "current_period_start": null, "current_period_end": null, "quantity": 1, "scope": "customer" }, { "id": "cp_01", "plan_id": "team", "auto_enable": false, "add_on": false, "status": "active", "past_due": false, "canceled_at": 1793698200000, "expires_at": 1796083200000, "trial_ends_at": null, "started_at": 1785542400000, "current_period_start": 1793491200000, "current_period_end": 1796083200000, "quantity": 1, "scope": "customer" }, { "id": "cp_03", "plan_id": "extra_storage", "auto_enable": false, "add_on": true, "status": "active", "past_due": true, "canceled_at": null, "expires_at": null, "trial_ends_at": null, "started_at": 1788998400000, "current_period_start": 1791590400000, "current_period_end": 1794268800000, "quantity": 1, "scope": "customer" } ], "purchases": [ { "plan_id": "credit_pack", "expires_at": null, "started_at": 1792065600000, "quantity": 2, "scope": "customer" } ], "licenses": [ { "license_plan_id": "member", "parent_plan_id": "team", "license_plan_name": "Member", "granted": 5, "usage": 2, "remaining": 3, "paid_quantity": 0 } ], "balances": { "credits": { "feature_id": "credits", "granted": 2200, "remaining": 1800, "usage": 400, "unlimited": false, "overage_allowed": true, "max_purchase": null, "next_reset_at": 1796083200000, "breakdown": [ { "id": "ce_01", "plan_id": "team", "included_grant": 1000, "prepaid_grant": 0, "remaining": 650, "usage": 350, "unlimited": false, "reset": { "interval": "month", "interval_count": null, "resets_at": 1796083200000 }, "price": { "amount": 0.01, "tiers": null, "tier_behavior": null, "billing_units": 1, "billing_method": "usage_based", "max_purchase": null }, "expires_at": null, "source": "customer", "allocation": null }, { "id": "ce_02", "plan_id": "credit_pack", "included_grant": 0, "prepaid_grant": 1000, "remaining": 1000, "usage": 0, "unlimited": false, "reset": { "interval": "one_off", "interval_count": null, "resets_at": null }, "price": { "amount": 10, "tiers": null, "tier_behavior": null, "billing_units": 500, "billing_method": "prepaid", "max_purchase": null }, "expires_at": null, "source": "customer", "allocation": null } ], "rollovers": [ { "granted": 200, "balance": 150, "expires_at": 1796083200000 } ], "allocated": 500, "unallocated": 500 }, "messages": { "feature_id": "messages", "granted": 200, "remaining": 120, "usage": 80, "unlimited": false, "overage_allowed": false, "max_purchase": null, "next_reset_at": 1796083200000, "breakdown": [ { "id": "ce_03", "plan_id": null, "included_grant": 200, "prepaid_grant": 0, "remaining": 120, "usage": 80, "unlimited": false, "reset": { "interval": "month", "interval_count": null, "resets_at": 1796083200000 }, "price": null, "expires_at": null, "source": "customer", "allocation": null } ], "rollovers": [], "allocated": null, "unallocated": null }, "support_tickets": { "feature_id": "support_tickets", "granted": 0, "remaining": 0, "usage": 12, "unlimited": true, "overage_allowed": false, "max_purchase": null, "next_reset_at": null, "breakdown": [ { "id": "ce_04", "plan_id": "team", "included_grant": 0, "prepaid_grant": 0, "remaining": 0, "usage": 12, "unlimited": true, "reset": null, "price": null, "expires_at": null, "source": "customer", "allocation": null } ], "rollovers": [], "allocated": null, "unallocated": null } }, "flags": { "sso": { "id": "ce_05", "plan_id": "team", "expires_at": null, "feature_id": "sso" } }, "config": { "disable_pooled_balance": false }, "processors": { "stripe": { "id": "cus_Sx1" } } } ``` ## Entity `POST /v1/entities.get`. The entity sees its own plans (including the seat it holds) and the customer-level plans, own plans first. Balances and flags are the entity's view (`scope = 'entity'`): shared grants appear with the entity's allocation applied, and grants the entity owns have `source: "entity"`. > [tab] ClickHouse > > ```sql theme={null} > WITH > e AS ( > SELECT e.* > FROM ``.`.v2_6_entities` AS e > INNER JOIN ``.`.v2_6_customers` AS c > ON c.org_id = e.org_id AND c.env = e.env AND c.internal_customer_id = e.internal_customer_id > WHERE c.env = 'live' AND c.customer_id = 'cus_123' AND e.entity_id = 'user_1' > ), > subs AS ( > SELECT > arraySort(x -> (x.1, x.2), groupArray((if(s.internal_entity_id = e.internal_entity_id, 0, 1), s.position, formatRowNoNewline('JSONEachRow', > s.id AS id, s.plan_id AS plan_id, s.auto_enable AS auto_enable, s.add_on AS add_on, > if(s.status = 'past_due', 'active', s.status) AS status, s.past_due AS past_due, > s.canceled_at AS canceled_at, s.expires_at AS expires_at, s.trial_ends_at AS trial_ends_at, > s.started_at AS started_at, s.current_period_start AS current_period_start, > s.current_period_end AS current_period_end, s.quantity AS quantity, s.scope AS scope > )))) AS rows > FROM ``.`.v2_6_subscriptions` AS s > INNER JOIN e ON e.org_id = s.org_id AND e.env = s.env AND e.internal_customer_id = s.internal_customer_id > WHERE (s.internal_entity_id = e.internal_entity_id > OR (coalesce(s.internal_entity_id, '') = '' AND coalesce(s.customer_license_id, '') = '')) > AND s.status IN ('active', 'past_due', 'scheduled') > ), > purch AS ( > SELECT > arraySort(x -> (x.1, x.2), groupArray((if(p.internal_entity_id = e.internal_entity_id, 0, 1), p.position, formatRowNoNewline('JSONEachRow', > p.plan_id AS plan_id, p.expires_at AS expires_at, p.started_at AS started_at, > p.quantity AS quantity, p.scope AS scope > )))) AS rows > FROM ``.`.v2_6_purchases` AS p > INNER JOIN e ON e.org_id = p.org_id AND e.env = p.env AND e.internal_customer_id = p.internal_customer_id > WHERE (p.internal_entity_id = e.internal_entity_id OR coalesce(p.internal_entity_id, '') = '') > AND p.status IN ('active', 'past_due', 'scheduled') > ), > b AS ( > SELECT b.* > FROM ``.`.v2_6_balances` AS b > INNER JOIN e ON e.org_id = b.org_id AND e.env = b.env AND e.internal_entity_id = b.internal_entity_id > WHERE b.scope = 'entity' > ), > bd AS ( > SELECT d.balance_id AS balance_id, > arraySort(x -> x.1, groupArray((d.position, concat( > left(formatRowNoNewline('JSONEachRow', > d.id AS id, d.plan_id AS plan_id, d.included_grant AS included_grant, > d.prepaid_grant AS prepaid_grant, d.remaining AS remaining, d.usage AS usage, > d.unlimited AS unlimited, d.expires_at AS expires_at, d.source AS source > ), -1), > ',"reset":', if(d.reset_interval IS NULL, 'null', formatRowNoNewline('JSONEachRow', > d.reset_interval AS interval, d.reset_interval_count AS interval_count, > d.reset_resets_at AS resets_at)), > ',"price":', if(d.price_billing_method IS NULL AND d.price_amount IS NULL AND d.price_tiers IS NULL, 'null', concat( > left(formatRowNoNewline('JSONEachRow', > d.price_amount AS amount, d.price_tier_behavior AS tier_behavior, > d.price_billing_units AS billing_units, d.price_billing_method AS billing_method, > d.price_max_purchase AS max_purchase), -1), > ',"tiers":', ifNull(d.price_tiers, 'null'), '}')), > ',"allocation":', if(d.allocation_amount IS NULL, 'null', > formatRowNoNewline('JSONEachRow', d.allocation_amount AS amount)), > '}' > )))) AS rows > FROM ``.`.v2_6_breakdowns` AS d > INNER JOIN b ON b.org_id = d.org_id AND b.env = d.env AND b.balance_id = d.balance_id > GROUP BY balance_id > ), > ro AS ( > SELECT r.balance_id AS balance_id, > arraySort(x -> x.1, groupArray(((ifNull(r.expires_at, 0), r.rollover_id), formatRowNoNewline('JSONEachRow', > r.granted AS granted, r.balance AS balance, r.expires_at AS expires_at > )))) AS rows > FROM ``.`.v2_6_rollovers` AS r > INNER JOIN b ON b.org_id = r.org_id AND b.env = r.env AND b.balance_id = r.balance_id > GROUP BY balance_id > ), > bal AS ( > SELECT > groupArray(concat(toJSONString(b.feature_id), ':', > left(formatRowNoNewline('JSONEachRow', > b.feature_id AS feature_id, b.granted AS granted, b.remaining AS remaining, > b.usage AS usage, b.unlimited AS unlimited, b.overage_allowed AS overage_allowed, > b.max_purchase AS max_purchase, b.next_reset_at AS next_reset_at, > b.allocated AS allocated, b.unallocated AS unallocated > ), -1), > ',"breakdown":[', arrayStringConcat(arrayMap(x -> x.2, bd.rows), ','), ']', > ',"rollovers":[', arrayStringConcat(arrayMap(x -> x.2, ro.rows), ','), ']', > '}' > )) AS entries > FROM b > LEFT JOIN bd ON bd.balance_id = b.balance_id > LEFT JOIN ro ON ro.balance_id = b.balance_id > ), > fl AS ( > SELECT > groupArray(concat(toJSONString(f.feature_id), ':', formatRowNoNewline('JSONEachRow', > f.id AS id, f.plan_id AS plan_id, f.expires_at AS expires_at, f.feature_id AS feature_id > ))) AS entries > FROM ``.`.v2_6_flags` AS f > INNER JOIN e ON e.org_id = f.org_id AND e.env = f.env AND e.internal_entity_id = f.internal_entity_id > WHERE f.scope = 'entity' > ) > SELECT concat( > left(formatRowNoNewline('JSONEachRow', > e.entity_id AS id, e.name AS name, e.customer_id AS customer_id, > e.feature_id AS feature_id, e.created_at AS created_at, e.env AS env > ), -1), > ',"billing_controls":', ifNull(e.billing_controls, '{}'), > ',"subscriptions":[', arrayStringConcat(arrayMap(x -> x.3, subs.rows), ','), ']', > ',"purchases":[', arrayStringConcat(arrayMap(x -> x.3, purch.rows), ','), ']', > ',"balances":{', arrayStringConcat(bal.entries, ','), '}', > ',"flags":{', arrayStringConcat(fl.entries, ','), '}', > '}' > ) AS entity > FROM e, subs, purch, bal, fl > SETTINGS output_format_json_quote_64bit_integers = 0; > ``` > [tab] BigQuery > > ```sql theme={null} > WITH > e AS ( > SELECT e.* > FROM `..v2_6_entities` AS e > JOIN `..v2_6_customers` AS c > ON c.org_id = e.org_id AND c.env = e.env AND c.internal_customer_id = e.internal_customer_id > WHERE c.env = 'live' AND c.customer_id = 'cus_123' AND e.entity_id = 'user_1' > ), > subs AS ( > SELECT e.internal_entity_id, > TO_JSON(ARRAY_AGG(STRUCT( > s.id AS id, s.plan_id AS plan_id, s.auto_enable AS auto_enable, s.add_on AS add_on, > IF(s.status = 'past_due', 'active', s.status) AS status, s.past_due AS past_due, > s.canceled_at AS canceled_at, s.expires_at AS expires_at, s.trial_ends_at AS trial_ends_at, > s.started_at AS started_at, s.current_period_start AS current_period_start, > s.current_period_end AS current_period_end, s.quantity AS quantity, s.scope AS scope > ) ORDER BY IF(s.internal_entity_id = e.internal_entity_id, 0, 1), s.position)) AS j > FROM `..v2_6_subscriptions` AS s > JOIN e ON e.org_id = s.org_id AND e.env = s.env AND e.internal_customer_id = s.internal_customer_id > WHERE (s.internal_entity_id = e.internal_entity_id > OR (s.internal_entity_id IS NULL AND s.customer_license_id IS NULL)) > AND s.status IN ('active', 'past_due', 'scheduled') > GROUP BY e.internal_entity_id > ), > purch AS ( > SELECT e.internal_entity_id, > TO_JSON(ARRAY_AGG(STRUCT( > p.plan_id AS plan_id, p.expires_at AS expires_at, p.started_at AS started_at, > p.quantity AS quantity, p.scope AS scope > ) ORDER BY IF(p.internal_entity_id = e.internal_entity_id, 0, 1), p.position)) AS j > FROM `..v2_6_purchases` AS p > JOIN e ON e.org_id = p.org_id AND e.env = p.env AND e.internal_customer_id = p.internal_customer_id > WHERE (p.internal_entity_id = e.internal_entity_id OR p.internal_entity_id IS NULL) > AND p.status IN ('active', 'past_due', 'scheduled') > GROUP BY e.internal_entity_id > ), > b AS ( > SELECT b.* > FROM `..v2_6_balances` AS b > JOIN e ON e.org_id = b.org_id AND e.env = b.env AND e.internal_entity_id = b.internal_entity_id > WHERE b.scope = 'entity' > ), > bd AS ( > SELECT d.balance_id, > TO_JSON(ARRAY_AGG(STRUCT( > d.id AS id, d.plan_id AS plan_id, d.included_grant AS included_grant, > d.prepaid_grant AS prepaid_grant, d.remaining AS remaining, d.usage AS usage, > d.unlimited AS unlimited, > IF(d.reset_interval IS NULL, NULL, STRUCT( > d.reset_interval AS `interval`, d.reset_interval_count AS interval_count, > d.reset_resets_at AS resets_at)) AS reset, > IF(d.price_billing_method IS NULL AND d.price_amount IS NULL AND d.price_tiers IS NULL, NULL, STRUCT( > d.price_amount AS amount, PARSE_JSON(d.price_tiers) AS tiers, > d.price_tier_behavior AS tier_behavior, d.price_billing_units AS billing_units, > d.price_billing_method AS billing_method, d.price_max_purchase AS max_purchase)) AS price, > d.expires_at AS expires_at, d.source AS source, > IF(d.allocation_amount IS NULL, NULL, STRUCT(d.allocation_amount AS amount)) AS allocation > ) ORDER BY d.position)) AS j > FROM `..v2_6_breakdowns` AS d > JOIN b ON b.org_id = d.org_id AND b.env = d.env AND b.balance_id = d.balance_id > GROUP BY d.balance_id > ), > ro AS ( > SELECT r.balance_id, > TO_JSON(ARRAY_AGG(STRUCT( > r.granted AS granted, r.balance AS balance, r.expires_at AS expires_at > ) ORDER BY r.expires_at, r.rollover_id)) AS j > FROM `..v2_6_rollovers` AS r > JOIN b ON b.org_id = r.org_id AND b.env = r.env AND b.balance_id = r.balance_id > GROUP BY r.balance_id > ), > bal AS ( > SELECT b.internal_entity_id, > JSON_OBJECT(ARRAY_AGG(b.feature_id), ARRAY_AGG(TO_JSON(STRUCT( > b.feature_id AS feature_id, b.granted AS granted, b.remaining AS remaining, > b.usage AS usage, b.unlimited AS unlimited, b.overage_allowed AS overage_allowed, > b.max_purchase AS max_purchase, b.next_reset_at AS next_reset_at, > COALESCE(bd.j, JSON '[]') AS breakdown, COALESCE(ro.j, JSON '[]') AS rollovers, > b.allocated AS allocated, b.unallocated AS unallocated > )))) AS j > FROM b > LEFT JOIN bd ON bd.balance_id = b.balance_id > LEFT JOIN ro ON ro.balance_id = b.balance_id > GROUP BY b.internal_entity_id > ), > fl AS ( > SELECT f.internal_entity_id, > JSON_OBJECT(ARRAY_AGG(f.feature_id), ARRAY_AGG(TO_JSON(STRUCT( > f.id AS id, f.plan_id AS plan_id, f.expires_at AS expires_at, f.feature_id AS feature_id > )))) AS j > FROM `..v2_6_flags` AS f > JOIN e ON e.org_id = f.org_id AND e.env = f.env AND e.internal_entity_id = f.internal_entity_id > WHERE f.scope = 'entity' > GROUP BY f.internal_entity_id > ) > SELECT TO_JSON(STRUCT( > e.entity_id AS id, e.name AS name, e.customer_id AS customer_id, e.feature_id AS feature_id, > e.created_at AS created_at, e.env AS env, > COALESCE(PARSE_JSON(e.billing_controls), JSON '{}') AS billing_controls, > COALESCE(subs.j, JSON '[]') AS subscriptions, > COALESCE(purch.j, JSON '[]') AS purchases, > COALESCE(bal.j, JSON '{}') AS balances, > COALESCE(fl.j, JSON '{}') AS flags > )) AS entity > FROM e > LEFT JOIN subs ON subs.internal_entity_id = e.internal_entity_id > LEFT JOIN purch ON purch.internal_entity_id = e.internal_entity_id > LEFT JOIN bal ON bal.internal_entity_id = e.internal_entity_id > LEFT JOIN fl ON fl.internal_entity_id = e.internal_entity_id; > ``` ### Example response Seat `user_1` of the customer above. Its credits show its 300 allocation plus the 500 no allocation holds, and `projects` comes from its own Member seat. ```json theme={null} { "id": "user_1", "name": "Ada", "customer_id": "cus_123", "feature_id": "seats", "created_at": 1785664800000, "env": "live", "billing_controls": { "spend_limits": [ { "feature_id": "credits", "enabled": true, "overage_limit": 20 } ] }, "subscriptions": [ { "id": "cp_04", "plan_id": "member", "auto_enable": false, "add_on": false, "status": "active", "past_due": false, "canceled_at": 1793698200000, "expires_at": null, "trial_ends_at": null, "started_at": 1785664800000, "current_period_start": 1793491200000, "current_period_end": 1796083200000, "quantity": 1, "scope": "entity" }, { "id": "cp_02", "plan_id": "pro", "auto_enable": false, "add_on": false, "status": "scheduled", "past_due": false, "canceled_at": null, "expires_at": null, "trial_ends_at": null, "started_at": 1796083200000, "current_period_start": null, "current_period_end": null, "quantity": 1, "scope": "customer" }, { "id": "cp_01", "plan_id": "team", "auto_enable": false, "add_on": false, "status": "active", "past_due": false, "canceled_at": 1793698200000, "expires_at": 1796083200000, "trial_ends_at": null, "started_at": 1785542400000, "current_period_start": 1793491200000, "current_period_end": 1796083200000, "quantity": 1, "scope": "customer" }, { "id": "cp_03", "plan_id": "extra_storage", "auto_enable": false, "add_on": true, "status": "active", "past_due": true, "canceled_at": null, "expires_at": null, "trial_ends_at": null, "started_at": 1788998400000, "current_period_start": 1791590400000, "current_period_end": 1794268800000, "quantity": 1, "scope": "customer" } ], "purchases": [ { "plan_id": "credit_pack", "expires_at": null, "started_at": 1792065600000, "quantity": 2, "scope": "customer" } ], "balances": { "credits": { "feature_id": "credits", "granted": 2000, "remaining": 1750, "usage": 250, "unlimited": false, "overage_allowed": true, "max_purchase": null, "next_reset_at": 1796083200000, "breakdown": [ { "id": "ce_01", "plan_id": "team", "included_grant": 800, "prepaid_grant": 0, "remaining": 600, "usage": 200, "unlimited": false, "reset": { "interval": "month", "interval_count": null, "resets_at": 1796083200000 }, "price": { "amount": 0.01, "tiers": null, "tier_behavior": null, "billing_units": 1, "billing_method": "usage_based", "max_purchase": null }, "expires_at": null, "source": "customer", "allocation": { "amount": 300 } }, { "id": "ce_02", "plan_id": "credit_pack", "included_grant": 0, "prepaid_grant": 1000, "remaining": 1000, "usage": 0, "unlimited": false, "reset": { "interval": "one_off", "interval_count": null, "resets_at": null }, "price": { "amount": 10, "tiers": null, "tier_behavior": null, "billing_units": 500, "billing_method": "prepaid", "max_purchase": null }, "expires_at": null, "source": "customer", "allocation": null } ], "rollovers": [ { "granted": 200, "balance": 150, "expires_at": 1796083200000 } ], "allocated": 300, "unallocated": 500 }, "messages": { "feature_id": "messages", "granted": 200, "remaining": 120, "usage": 80, "unlimited": false, "overage_allowed": false, "max_purchase": null, "next_reset_at": 1796083200000, "breakdown": [ { "id": "ce_03", "plan_id": null, "included_grant": 200, "prepaid_grant": 0, "remaining": 120, "usage": 80, "unlimited": false, "reset": { "interval": "month", "interval_count": null, "resets_at": 1796083200000 }, "price": null, "expires_at": null, "source": "customer", "allocation": null } ], "rollovers": [], "allocated": null, "unallocated": null }, "projects": { "feature_id": "projects", "granted": 5, "remaining": 3, "usage": 2, "unlimited": false, "overage_allowed": false, "max_purchase": null, "next_reset_at": null, "breakdown": [ { "id": "ce_06", "plan_id": "member", "included_grant": 5, "prepaid_grant": 0, "remaining": 3, "usage": 2, "unlimited": false, "reset": null, "price": null, "expires_at": null, "source": "entity", "allocation": null } ], "rollovers": [], "allocated": null, "unallocated": null }, "support_tickets": { "feature_id": "support_tickets", "granted": 0, "remaining": 0, "usage": 12, "unlimited": true, "overage_allowed": false, "max_purchase": null, "next_reset_at": null, "breakdown": [ { "id": "ce_04", "plan_id": "team", "included_grant": 0, "prepaid_grant": 0, "remaining": 0, "usage": 12, "unlimited": true, "reset": null, "price": null, "expires_at": null, "source": "customer", "allocation": null } ], "rollovers": [], "allocated": null, "unallocated": null } }, "flags": { "sso": { "id": "ce_05", "plan_id": "team", "expires_at": null, "feature_id": "sso" } } } ``` ## Invoices `expand: ["invoices"]` adds the customer's newest 10 invoices: > [tab] ClickHouse > > ```sql theme={null} > SELECT concat('[', arrayStringConcat(arrayMap(x -> x.3, arraySlice( > arrayReverseSort(x -> (x.1, x.2), groupArray((i.created_at, i.invoice_id, concat( > left(formatRowNoNewline('JSONEachRow', > i.invoice_id AS id, i.processor_id AS stripe_id, i.processor_type AS processor_type, > i.status AS status, i.total AS total, i.amount_paid AS amount_paid, > i.refunded_amount AS refunded_amount, i.currency AS currency, > i.created_at AS created_at, i.hosted_invoice_url AS hosted_invoice_url > ), -1), > ',"plan_ids":', ifNull(i.plan_ids, '[]'), '}' > )))), 1, 10)), ','), ']') AS invoices > FROM ``.`.v2_6_invoices` AS i > INNER JOIN ``.`.v2_6_customers` AS c > ON c.org_id = i.org_id AND c.env = i.env AND c.internal_customer_id = i.internal_customer_id > WHERE c.env = 'live' AND c.customer_id = 'cus_123' > SETTINGS output_format_json_quote_64bit_integers = 0; > ``` > [tab] BigQuery > > ```sql theme={null} > SELECT IF(COUNT(*) = 0, JSON '[]', TO_JSON(ARRAY_AGG(STRUCT( > i.invoice_id AS id, PARSE_JSON(IFNULL(i.plan_ids, '[]')) AS plan_ids, i.processor_id AS stripe_id, > i.processor_type AS processor_type, i.status AS status, i.total AS total, > i.amount_paid AS amount_paid, i.refunded_amount AS refunded_amount, i.currency AS currency, > i.created_at AS created_at, i.hosted_invoice_url AS hosted_invoice_url > ) ORDER BY i.created_at DESC, i.invoice_id DESC LIMIT 10))) AS invoices > FROM `..v2_6_invoices` AS i > JOIN `..v2_6_customers` AS c > ON c.org_id = i.org_id AND c.env = i.env AND c.internal_customer_id = i.internal_customer_id > WHERE c.env = 'live' AND c.customer_id = 'cus_123'; > ``` ## Differences from the API * The API leaves out some fields when they are empty or unset, such as `rollovers`, `allocation`, `allocated`, `unallocated`, `reset.interval_count`, `config` and `processors`. These queries return them as `null` or `[]`. * `billing_controls` is returned as stored. The API can add defaults to it, such as a usage alert's `basis` and `name`. * `invoices[].hosted_invoice_url` is the processor's link. The API returns an Autumn link that redirects to it. * Key order can differ between engines and from the API. * Other expands are not built here: `subscriptions.plan` and `purchases.plan` ([Custom plans](https://docs.useautumn.com/documentation/lakehouse/v2-6/schema.md#custom-plans) shows how to read a customer's plan items), `balances.feature` and `flags.feature` (`v2_6_features`), `breakdown[].contributions` (`v2_6_pooled_balance_contributions`), and `entities`, `trials_used` and `referrals` ([Querying](https://docs.useautumn.com/documentation/lakehouse/v2-6/querying.md#rebuild-a-customer)). `rewards.discounts`, `invoice_previews`, `payment_method` and `billing_details` are not in the lakehouse. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.