Rebuild a customer or entity

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 <catalog> and <namespace> (or <project> and <dataset>) with the names Autumn sent you. 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.

ClickHouse
BigQuery

WITH
  c AS (
    SELECT *
    FROM `<catalog>`.`<namespace>.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 `<catalog>`.`<namespace>.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 `<catalog>`.`<namespace>.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 `<catalog>`.`<namespace>.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 `<catalog>`.`<namespace>.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 `<catalog>`.`<namespace>.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 `<catalog>`.`<namespace>.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 `<catalog>`.`<namespace>.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;

WITH
  c AS (
    SELECT *
    FROM `<project>.<dataset>.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 `<project>.<dataset>.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 `<project>.<dataset>.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 `<project>.<dataset>.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 `<project>.<dataset>.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 `<project>.<dataset>.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 `<project>.<dataset>.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 `<project>.<dataset>.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.

{
  "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".

ClickHouse
BigQuery

WITH
  e AS (
    SELECT e.*
    FROM `<catalog>`.`<namespace>.v2_6_entities` AS e
    INNER JOIN `<catalog>`.`<namespace>.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 `<catalog>`.`<namespace>.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 `<catalog>`.`<namespace>.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 `<catalog>`.`<namespace>.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 `<catalog>`.`<namespace>.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 `<catalog>`.`<namespace>.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 `<catalog>`.`<namespace>.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;

WITH
  e AS (
    SELECT e.*
    FROM `<project>.<dataset>.v2_6_entities` AS e
    JOIN `<project>.<dataset>.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 `<project>.<dataset>.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 `<project>.<dataset>.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 `<project>.<dataset>.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 `<project>.<dataset>.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 `<project>.<dataset>.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 `<project>.<dataset>.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.

{
  "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:

ClickHouse
BigQuery

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 `<catalog>`.`<namespace>.v2_6_invoices` AS i
INNER JOIN `<catalog>`.`<namespace>.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;

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 `<project>.<dataset>.v2_6_invoices` AS i
JOIN `<project>.<dataset>.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 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). rewards.discounts, invoice_previews, payment_method and billing_details are not in the lakehouse.

Lakehouse

Guides

Rebuild a customer or entity

Customer

Example response

Entity

Example response

Invoices

Differences from the API

​Customer

​Example response

​Entity

​Example response

​Invoices

​Differences from the API

Customer

Example response

Entity

Example response

Invoices

Differences from the API