Files
coder/coderd/database/queries/aicostcontrol.sql
Susana Ferreira 8705d80fe0 feat: add provider, model, and client spend to the AI spend users endpoint (#32330)
## Description

Extends the experimental `GET
/api/experimental/organizations/{organization}/ai/spend/users` endpoint
with the per-user details behind the AI spend overview and the detailed
report. Each user now returns their spend per provider, model, and
client, and whether each has unpriced usage.

## Changes

- Rewrite `ListOrganizationAISpendUsers` with `GROUPING SETS`, returning
one row per user and provider, model, or client:
  - Adds the `user_ids` and `group_id` filters.
  - Pages, counts, and totals users, not their rows.
- Reads the provider display name, icon, and type from `ai_providers`,
including deleted providers. The live provider wins when a name was
reused, like `GetAIProviderFilterOptions`.
- Update the SDK and handler:
- Each user returns `provider_spend`, `model_spend`, and `client_spend`,
most expensive first, each with `has_unpriced_usage`.
  - `totals` returns `has_unpriced_usage`.
- Remove the `providers`, `clients`, and `models` name arrays and
`unpriced_usage_count`.
  - Accept `user_ids` (comma-separated, maximum 100) and `group_id`.
- Return more than 100 `user_ids` on the group members AI spend endpoint
as a validation error, like `/ai/spend/users`.
- Switch the Spend page to the new fields, with no UI changes.
- Update the query and handler tests.

<details>
<summary>Dogfood performance</summary>

- Benchmarked on **dogfood**, comparing the existing query on **main**
with the updated query in **this PR**.
- Tested **1, 7, 15 and 30-day windows**, ending at **November 18, 2026,
00:00 UTC**, exclusive.
- Each query ran **five times per window**, one at a time, with query
order and window order rotated across rounds to reduce ordering bias.

The table shows PostgreSQL execution time in **seconds, average
(min-max)**.

| Days | Main | This PR |
|------|-----:|--------:|
| 1 | 0.288 (0.199-0.501) | 0.223 (0.184-0.275) |
| 7 | 11.413 (4.076-40.003) | 4.107 (3.529-4.828) |
| 15 | 9.961 (8.053-16.917) | 7.253 (6.873-7.736) |
| 30 | 14.979 (12.276-23.177) | 8.499 (7.813-9.169) |

All five runs are included. Main's first seven-day run took **40.003s**
with unusually high read I/O time, which raises its average. Cache and
concurrent load can affect these results.

</details>

Closes AIGOV-734

> [!NOTE]
> Generated by Coder Agents on behalf of @ssncferreira.
2026-11-19 14:59:20 +00:00

911 lines
36 KiB
SQL

-- name: UpsertAIModelPrices :exec
-- Upsert a batch of model prices from a JSON array, all recorded under the
-- given source. Each element must have provider, model, and the four price
-- fields, and null prices are written as SQL NULL.
-- Each source keeps its own row, so the price book and a custom price never
-- overwrite each other. A conflicting row is only rewritten when a price
-- differs, so updated_at records when a price last changed. Prices are
-- nullable and a NULL on either side counts as a difference.
INSERT INTO ai_model_prices (
provider, model, input_price, output_price, cache_read_price, cache_write_price, source
)
SELECT
elem->>'provider',
elem->>'model',
(elem->>'input_price')::bigint,
(elem->>'output_price')::bigint,
(elem->>'cache_read_price')::bigint,
(elem->>'cache_write_price')::bigint,
@source::ai_model_price_source
FROM jsonb_array_elements(@seed::jsonb) AS elem
ON CONFLICT (provider, model, source) DO UPDATE SET
input_price = EXCLUDED.input_price,
output_price = EXCLUDED.output_price,
cache_read_price = EXCLUDED.cache_read_price,
cache_write_price = EXCLUDED.cache_write_price,
updated_at = NOW()
WHERE (
ai_model_prices.input_price,
ai_model_prices.output_price,
ai_model_prices.cache_read_price,
ai_model_prices.cache_write_price
) IS DISTINCT FROM (
EXCLUDED.input_price,
EXCLUDED.output_price,
EXCLUDED.cache_read_price,
EXCLUDED.cache_write_price
);
-- name: GetAIModelPriceForProvider :one
-- Returns the price in effect for the model served by the configured provider.
-- The requested model is preferred over the model reported by the provider,
-- whatever the kind of price. For each model, the lookup order is:
-- 1. The organization's price for the provider.
-- 2. A site-wide custom price.
-- 3. The price book price.
-- A NULL organization_id matches no organization price, so only site-wide
-- prices apply. Soft-deleted organization configs, and those that set no
-- price, are ignored. A matched price is used as a whole: its NULL prices are
-- not filled in from a less specific price. Each branch is a unique index
-- lookup.
WITH prices AS (
SELECT
CASE source WHEN 'custom' THEN 2 ELSE 3 END AS specificity,
model,
input_price,
output_price,
cache_read_price,
cache_write_price
FROM ai_model_prices
WHERE ai_model_prices.provider = @provider_type::text
AND ai_model_prices.model IN (@model::text, @provider_model::text)
UNION ALL
SELECT
1,
model,
input_price,
output_price,
cache_read_price,
cache_write_price
FROM ai_model_configs
WHERE ai_model_configs.organization_id = sqlc.narg('organization_id')::uuid
AND ai_model_configs.provider_id = @provider_id::uuid
AND ai_model_configs.model IN (@model::text, @provider_model::text)
AND ai_model_configs.deleted = FALSE
AND COALESCE(
ai_model_configs.input_price,
ai_model_configs.output_price,
ai_model_configs.cache_read_price,
ai_model_configs.cache_write_price
) IS NOT NULL
)
SELECT
prices.model,
prices.input_price,
prices.output_price,
prices.cache_read_price,
prices.cache_write_price
FROM prices
ORDER BY
CASE WHEN prices.model = @model::text THEN 0 ELSE 1 END ASC,
prices.specificity ASC
LIMIT 1;
-- name: GetOrganizationAIProviderModelConfig :one
-- Returns the organization's live config for one model on one configured
-- provider. Soft-deleted rows are ignored.
SELECT *
FROM ai_model_configs
WHERE organization_id = @organization_id
AND provider_id = @provider_id
AND model = @model
AND deleted = FALSE;
-- name: UpsertOrganizationAIProviderModelConfig :exec
-- Sets the organization's config for one model on one configured provider. A
-- NULL price is unknown. An existing live row is only rewritten when a price
-- differs, so updated_at records when a price last changed. Soft-deleted rows
-- are left untouched, and a new live row is inserted beside them.
INSERT INTO ai_model_configs (
organization_id, provider_id, model, input_price, output_price, cache_read_price, cache_write_price
) VALUES (
@organization_id, @provider_id, @model, @input_price, @output_price, @cache_read_price, @cache_write_price
)
ON CONFLICT (organization_id, provider_id, model) WHERE deleted = FALSE DO UPDATE SET
input_price = EXCLUDED.input_price,
output_price = EXCLUDED.output_price,
cache_read_price = EXCLUDED.cache_read_price,
cache_write_price = EXCLUDED.cache_write_price,
updated_at = NOW()
WHERE (
ai_model_configs.input_price,
ai_model_configs.output_price,
ai_model_configs.cache_read_price,
ai_model_configs.cache_write_price
) IS DISTINCT FROM (
EXCLUDED.input_price,
EXCLUDED.output_price,
EXCLUDED.cache_read_price,
EXCLUDED.cache_write_price
);
-- name: DeleteOrganizationAIProviderModelConfig :execrows
-- Soft deletes the organization's live config for one model on one configured
-- provider. Returns zero rows when there is no live config.
UPDATE ai_model_configs
SET deleted = TRUE,
updated_at = NOW()
WHERE organization_id = @organization_id
AND provider_id = @provider_id
AND model = @model
AND deleted = FALSE;
-- name: GetAIModelPrices :many
-- Returns the price in effect for each model, preferring a custom price over
-- the price book. Filtering by source narrows the rows considered first, so a
-- model carrying both prices reports the one from the named source.
-- The source 'all' reports every row instead. It joins the DISTINCT ON key, so
-- each source forms its own group and nothing collapses. Every other source
-- contributes the same constant, leaving the key as (provider, model).
SELECT DISTINCT ON (
provider,
model,
CASE WHEN @source::text = 'all' THEN source::text ELSE '' END
) *
FROM ai_model_prices
-- Filter by provider
WHERE CASE
WHEN @provider::text != '' THEN
provider = @provider
ELSE true
END
-- Filter by model
AND CASE
WHEN @model::text != '' THEN
model = @model
ELSE true
END
-- Filter by source
AND CASE
WHEN @source::text NOT IN ('', 'all') THEN
source = @source::ai_model_price_source
ELSE true
END
ORDER BY
provider ASC,
model ASC,
CASE WHEN @source::text = 'all' THEN source::text ELSE '' END ASC,
CASE WHEN source = 'custom' THEN 0 ELSE 1 END ASC;
-- name: GetGroupAIBudget :one
SELECT *
FROM group_ai_budgets
WHERE group_id = @group_id;
-- name: UpsertGroupAIBudget :one
INSERT INTO group_ai_budgets (group_id, spend_limit_micros)
VALUES (@group_id, @spend_limit_micros)
ON CONFLICT (group_id) DO UPDATE SET
spend_limit_micros = EXCLUDED.spend_limit_micros,
updated_at = NOW()
RETURNING *;
-- name: DeleteGroupAIBudget :one
DELETE FROM group_ai_budgets WHERE group_id = @group_id RETURNING *;
-- name: GetUserAIBudgetOverride :one
SELECT *
FROM user_ai_budget_overrides
WHERE user_id = @user_id;
-- name: GetUserAIBudgetOverrideWithOrganization :one
-- Returns the user's AI budget override with the organization of its group, for
-- budget resolution. An override whose group is in a deleted organization is
-- never returned. When organization_id is set, returns the override only if its
-- group belongs to that organization.
SELECT override.user_id, override.group_id, override.spend_limit_micros,
groups.organization_id
FROM user_ai_budget_overrides override
JOIN groups ON groups.id = override.group_id
JOIN organizations ON organizations.id = groups.organization_id
WHERE override.user_id = @user_id
AND organizations.deleted = false
AND (sqlc.narg('organization_id')::uuid IS NULL OR groups.organization_id = sqlc.narg('organization_id')::uuid);
-- name: UpsertUserAIBudgetOverride :one
INSERT INTO user_ai_budget_overrides (user_id, group_id, spend_limit_micros)
VALUES (@user_id, @group_id, @spend_limit_micros)
ON CONFLICT (user_id) DO UPDATE SET
group_id = EXCLUDED.group_id,
spend_limit_micros = EXCLUDED.spend_limit_micros,
updated_at = NOW()
RETURNING *;
-- name: DeleteUserAIBudgetOverride :one
DELETE FROM user_ai_budget_overrides WHERE user_id = @user_id RETURNING *;
-- name: GetHighestGroupAIBudgetByUser :one
-- Returns the highest group AI budget across the groups the user belongs to,
-- breaking ties by the earliest organization membership. Implements the
-- "highest" budget policy. group_members_expanded is a UNION of group_members
-- and organization_members, so the implicit "Everyone" group
-- (group_id == organization_id) is included. Returns no rows when the user has
-- no budgeted groups. Callers should treat sql.ErrNoRows as "no group budget".
-- When organization_id is set, only groups in that organization count.
SELECT
budget.group_id,
budget.spend_limit_micros,
member.organization_id
FROM group_ai_budgets budget
JOIN group_members_expanded member ON member.group_id = budget.group_id
JOIN organizations ON organizations.id = member.organization_id
JOIN organization_members
ON organization_members.user_id = member.user_id
AND organization_members.organization_id = member.organization_id
WHERE member.user_id = @user_id
AND organizations.deleted = false
AND (sqlc.narg('organization_id')::uuid IS NULL OR member.organization_id = sqlc.narg('organization_id')::uuid)
ORDER BY
budget.spend_limit_micros DESC, -- highest wins
organization_members.created_at ASC, -- earliest organization membership
budget.group_id ASC -- deterministic tiebreak
LIMIT 1;
-- name: GetUserEveryoneFallbackGroup :one
-- Returns the "Everyone" group (id == organization_id) to attribute a user's
-- spend to when no override or budgeted group applies. Prefers the default org,
-- then the earliest organization membership. Returns no rows when the user has
-- no organization membership. When organization_id is set, returns that
-- organization's Everyone group only if the user is a member of it.
SELECT organizations.id AS group_id
FROM organization_members
JOIN organizations ON organizations.id = organization_members.organization_id
WHERE organization_members.user_id = @user_id
AND organizations.deleted = false
AND (sqlc.narg('organization_id')::uuid IS NULL OR organizations.id = sqlc.narg('organization_id')::uuid)
ORDER BY
organizations.is_default DESC, -- prefer the default org
organization_members.created_at ASC, -- earliest organization membership
organizations.id ASC -- deterministic tiebreak
LIMIT 1;
-- name: IncrementUserAIDailySpend :one
-- Adds cost_micros to the spend for (user_id, effective_group_id, day).
-- The day parameter is normalized to its UTC calendar day before storage.
INSERT INTO ai_user_daily_spend (user_id, effective_group_id, day, spend_micros)
VALUES (@user_id, @effective_group_id, ((@day::timestamptz) AT TIME ZONE 'UTC')::date, @cost_micros)
ON CONFLICT (user_id, effective_group_id, day) DO UPDATE SET
spend_micros = ai_user_daily_spend.spend_micros + EXCLUDED.spend_micros
RETURNING *;
-- name: GetUserAISpendSince :one
-- Total spend for (user_id, effective_group_id) on or after period_start until NOW.
-- The period_start parameter is normalized to its UTC calendar day.
SELECT
@user_id::uuid AS user_id,
@effective_group_id::uuid AS effective_group_id,
((@period_start::timestamptz) AT TIME ZONE 'UTC')::date AS period_start,
COALESCE(SUM(spend_micros), 0)::BIGINT AS spend_micros
FROM ai_user_daily_spend
WHERE user_id = @user_id
AND effective_group_id = @effective_group_id
AND day >= ((@period_start::timestamptz) AT TIME ZONE 'UTC')::date;
-- name: GetOrganizationGroupsAISpend :many
-- Returns AI spend limits and aggregate spend for groups in @group_ids that
-- belong to @organization_id, on or after period_start until NOW.
-- spend_limit_micros is the per-member limit, null when the group has no budget.
-- total_spend_limit_micros is the combined budget of the members attributed to
-- the group, with each member's override replacing their share. System users
-- are excluded. It is null when the group has no budget.
-- The period_start parameter is normalized to its UTC calendar day.
-- TODO(AIGOV-527): unify effective group resolution in a single place.
WITH queried_groups AS (
-- The requested groups that belong to the queried organization.
SELECT groups.id, groups.organization_id
FROM groups
WHERE groups.organization_id = @organization_id
AND groups.id = ANY(@group_ids::uuid[])
),
candidate_users AS (
-- Members of the queried groups. Uses group_members_expanded so the implicit
-- Everyone group counts.
SELECT DISTINCT member.user_id
FROM group_members_expanded member
WHERE member.group_id IN (SELECT id FROM queried_groups)
AND member.user_is_system = false
),
user_highest_group AS (
-- Per user, the highest-limit group they belong to. Uses
-- group_members_expanded so the implicit Everyone group counts.
SELECT DISTINCT ON (member.user_id)
member.user_id,
budget.group_id,
budget.spend_limit_micros
FROM group_ai_budgets budget
JOIN group_members_expanded member ON member.group_id = budget.group_id
JOIN organizations ON organizations.id = member.organization_id
JOIN organization_members
ON organization_members.user_id = member.user_id
AND organization_members.organization_id = member.organization_id
WHERE member.user_id IN (SELECT user_id FROM candidate_users)
AND organizations.deleted = false
ORDER BY member.user_id, budget.spend_limit_micros DESC, organization_members.created_at ASC, budget.group_id ASC
),
effective AS (
-- Effective budget group per user: an override wins over the highest-limit
-- group they belong to. Users with neither are left out, since the group they
-- fall back to has no budget and reports null.
SELECT
candidate_users.user_id,
COALESCE(override.group_id, user_highest_group.group_id) AS effective_group_id,
override.spend_limit_micros AS override_limit_micros
FROM candidate_users
LEFT JOIN user_ai_budget_overrides override ON override.user_id = candidate_users.user_id
LEFT JOIN user_highest_group ON user_highest_group.user_id = candidate_users.user_id
),
group_limits AS (
-- Per attributed group, how many members take the group's own limit and the
-- combined limit of those carrying an override.
SELECT
effective.effective_group_id AS group_id,
count(*) FILTER (WHERE effective.override_limit_micros IS NULL) AS plain_member_count,
COALESCE(SUM(effective.override_limit_micros), 0)::BIGINT AS override_limit_sum
FROM effective
WHERE effective.effective_group_id IS NOT NULL
GROUP BY effective.effective_group_id
),
group_totals AS (
-- Combined limit per budgeted group, counting members with no override at the
-- group's own limit and adding the overrides on top. Unbudgeted groups are
-- absent here, so the join below leaves their total null.
SELECT
queried_groups.id AS group_id,
(budget.spend_limit_micros * COALESCE(group_limits.plain_member_count, 0)
+ COALESCE(group_limits.override_limit_sum, 0))::BIGINT AS total_spend_limit_micros
FROM queried_groups
JOIN group_ai_budgets budget ON budget.group_id = queried_groups.id
LEFT JOIN group_limits ON group_limits.group_id = queried_groups.id
),
group_spend AS (
-- Spend per queried group over the period.
SELECT
spend.effective_group_id AS group_id,
COALESCE(SUM(spend.spend_micros), 0)::BIGINT AS current_spend_micros
FROM ai_user_daily_spend spend
WHERE spend.effective_group_id IN (SELECT id FROM queried_groups)
AND spend.day >= ((@period_start::timestamptz) AT TIME ZONE 'UTC')::date
GROUP BY spend.effective_group_id
)
SELECT
queried_groups.id AS group_id,
queried_groups.organization_id AS organization_id,
budget.spend_limit_micros AS spend_limit_micros,
group_totals.total_spend_limit_micros AS total_spend_limit_micros,
COALESCE(group_spend.current_spend_micros, 0)::BIGINT AS current_spend_micros
FROM queried_groups
LEFT JOIN group_ai_budgets budget ON budget.group_id = queried_groups.id
LEFT JOIN group_totals ON group_totals.group_id = queried_groups.id
LEFT JOIN group_spend ON group_spend.group_id = queried_groups.id
ORDER BY queried_groups.id;
-- name: GetGroupMembersAISpend :many
-- Returns each user's AI spend attributed to the queried group, on or after
-- period_start until NOW. Only current members of the queried group are
-- returned. effective_spend_limit_micros and effective_limit_source describe
-- the user's effective budget when its group belongs to the queried group's
-- organization. The effective group falls back to the Everyone group, and
-- effective_group_id is null only when that group belongs to a different
-- organization than the queried group.
-- The period_start parameter is normalized to its UTC calendar day.
-- TODO(AIGOV-527): unify effective group resolution in a single place.
WITH queried_group AS (
-- The queried group's org, used to detect cross-org effective groups.
SELECT organization_id
FROM groups
WHERE id = @group_id
),
filtered_users AS (
-- Users from @user_ids that are members of the queried group. Uses
-- group_members_expanded so the implicit Everyone group counts.
SELECT DISTINCT user_id
FROM group_members_expanded
WHERE group_id = @group_id
AND user_id = ANY(@user_ids::uuid[])
),
user_highest_group AS (
-- Per user, the highest-limit group they belong to. Uses
-- group_members_expanded so the implicit Everyone group counts.
SELECT DISTINCT ON (member.user_id)
member.user_id,
budget.group_id,
budget.spend_limit_micros
FROM group_ai_budgets budget
JOIN group_members_expanded member ON member.group_id = budget.group_id
JOIN organizations ON organizations.id = member.organization_id
JOIN organization_members
ON organization_members.user_id = member.user_id
AND organization_members.organization_id = member.organization_id
WHERE member.user_id IN (SELECT user_id FROM filtered_users)
AND organizations.deleted = false
ORDER BY member.user_id, budget.spend_limit_micros DESC, organization_members.created_at ASC, budget.group_id ASC
),
user_fallback_group AS (
-- Per user, the Everyone group to fall back to when no override or budgeted
-- group applies. The Everyone group has id == organization_id. Prefers the
-- default org, then the earliest organization membership.
SELECT DISTINCT ON (organization_members.user_id)
organization_members.user_id,
organizations.id AS group_id
FROM organization_members
JOIN organizations ON organizations.id = organization_members.organization_id
WHERE organization_members.user_id IN (SELECT user_id FROM filtered_users)
AND organizations.deleted = false
ORDER BY organization_members.user_id, organizations.is_default DESC, organization_members.created_at ASC, organizations.id ASC
),
effective AS (
-- Effective budget per user: a per-user override wins over the highest-limit
-- group, which wins over the Everyone group fallback.
SELECT
filtered_users.user_id,
COALESCE(override.group_id, user_highest_group.group_id, user_fallback_group.group_id) AS raw_effective_group_id,
COALESCE(override.spend_limit_micros, user_highest_group.spend_limit_micros) AS spend_limit_micros,
(CASE
WHEN override.group_id IS NOT NULL THEN 'user_override'
WHEN user_highest_group.group_id IS NOT NULL THEN 'group'
END)::text AS limit_source
FROM filtered_users
LEFT JOIN user_ai_budget_overrides override ON override.user_id = filtered_users.user_id
LEFT JOIN user_highest_group ON user_highest_group.user_id = filtered_users.user_id
LEFT JOIN user_fallback_group ON user_fallback_group.user_id = filtered_users.user_id
),
visible_effective AS (
-- The effective group and budget only when the group belongs to the queried
-- group's organization.
SELECT
effective.user_id,
effective.raw_effective_group_id AS group_id,
effective.spend_limit_micros,
effective.limit_source
FROM effective
JOIN groups ON groups.id = effective.raw_effective_group_id
CROSS JOIN queried_group
WHERE groups.organization_id = queried_group.organization_id
)
-- Spend is aggregated for the queried group, not the user's effective group.
SELECT
effective.user_id,
queried_group.organization_id,
visible_effective.group_id AS effective_group_id,
visible_effective.spend_limit_micros AS effective_spend_limit_micros,
visible_effective.limit_source AS effective_limit_source,
COALESCE(SUM(spend.spend_micros), 0)::BIGINT AS group_spend_micros
FROM effective
CROSS JOIN queried_group
LEFT JOIN visible_effective
ON visible_effective.user_id = effective.user_id
LEFT JOIN ai_user_daily_spend spend
ON spend.user_id = effective.user_id
AND spend.effective_group_id = @group_id
AND spend.day >= ((@period_start::timestamptz) AT TIME ZONE 'UTC')::date
GROUP BY
effective.user_id,
queried_group.organization_id,
visible_effective.group_id,
visible_effective.spend_limit_micros,
visible_effective.limit_source
ORDER BY effective.user_id;
-- name: GetOverBudgetUsersPerGroup :many
-- Returns, per effective group, the number of users at or over their spend
-- limit since period_start. Only non-system users with an enforceable limit
-- (override or budgeted group) count, and the unlimited Everyone fallback does not.
-- TODO(AIGOV-527): unify effective group resolution in a single place.
WITH budgeted_users AS (
-- Non-system users with an override or membership in a budgeted group.
SELECT override.user_id
FROM user_ai_budget_overrides override
JOIN users ON users.id = override.user_id
WHERE users.is_system = false
UNION
SELECT DISTINCT member.user_id
FROM group_ai_budgets budget
JOIN group_members_expanded member ON member.group_id = budget.group_id
WHERE member.user_is_system = false
),
user_highest_group AS (
-- Per user, their highest-limit group ("highest" budget policy).
SELECT DISTINCT ON (member.user_id)
member.user_id,
budget.group_id,
budget.spend_limit_micros
FROM group_ai_budgets budget
JOIN group_members_expanded member ON member.group_id = budget.group_id
JOIN organizations ON organizations.id = member.organization_id
JOIN organization_members
ON organization_members.user_id = member.user_id
AND organization_members.organization_id = member.organization_id
WHERE member.user_id IN (SELECT user_id FROM budgeted_users)
AND organizations.deleted = false
ORDER BY member.user_id, budget.spend_limit_micros DESC, organization_members.created_at ASC, budget.group_id ASC
),
effective AS (
-- An override wins over the highest-limit group, and users with neither drop.
SELECT
budgeted_users.user_id,
COALESCE(override.group_id, user_highest_group.group_id) AS effective_group_id,
COALESCE(override.spend_limit_micros, user_highest_group.spend_limit_micros) AS spend_limit_micros
FROM budgeted_users
LEFT JOIN user_ai_budget_overrides override ON override.user_id = budgeted_users.user_id
LEFT JOIN user_highest_group ON user_highest_group.user_id = budgeted_users.user_id
WHERE COALESCE(override.group_id, user_highest_group.group_id) IS NOT NULL
),
user_spend AS (
-- Each user's spend against their effective group since period_start.
SELECT
effective.user_id,
effective.effective_group_id,
effective.spend_limit_micros,
COALESCE(SUM(spend.spend_micros), 0)::BIGINT AS current_spend_micros
FROM effective
LEFT JOIN ai_user_daily_spend spend
ON spend.user_id = effective.user_id
AND spend.effective_group_id = effective.effective_group_id
AND spend.day >= ((@period_start::timestamptz) AT TIME ZONE 'UTC')::date
GROUP BY effective.user_id, effective.effective_group_id, effective.spend_limit_micros
)
SELECT
effective_group_id AS group_id,
COUNT(*)::BIGINT AS over_budget_users
FROM user_spend
WHERE current_spend_micros >= spend_limit_micros
GROUP BY effective_group_id
ORDER BY effective_group_id;
-- name: ExportOrganizationAISpend :many
-- Returns per-user, per-group, per-model, per-provider aggregated AI spend for
-- @organization_id over the [period_start, period_end) window. Spend is
-- attributed through the token usage's effective group, and rows are bucketed
-- by the token usage created_at, matching how ai_user_daily_spend is derived.
SELECT
ai.initiator_id AS user_id,
users.username AS username,
tu.effective_group_id AS group_id,
groups.name AS group_name,
groups.organization_id AS organization_id,
organizations.name AS organization_name,
ai.model AS model,
ai.provider AS provider,
ai.provider_name AS provider_name,
COALESCE(SUM(tu.input_tokens), 0)::BIGINT AS input_tokens,
COALESCE(SUM(tu.output_tokens), 0)::BIGINT AS output_tokens,
COALESCE(SUM(tu.cache_read_input_tokens), 0)::BIGINT AS cache_read_tokens,
COALESCE(SUM(tu.cache_write_input_tokens), 0)::BIGINT AS cache_write_tokens,
COALESCE(SUM(tu.cost_micros), 0)::BIGINT AS cost_micros,
COUNT(*) OVER()::BIGINT AS count
FROM aibridge_token_usages tu
JOIN aibridge_interceptions ai ON ai.id = tu.interception_id
JOIN users ON users.id = ai.initiator_id
JOIN groups ON groups.id = tu.effective_group_id
JOIN organizations ON organizations.id = groups.organization_id
WHERE groups.organization_id = @organization_id
AND tu.created_at >= @period_start::timestamptz
AND tu.created_at < @period_end::timestamptz
AND (@user_id::uuid = '00000000-0000-0000-0000-000000000000'::uuid OR ai.initiator_id = @user_id)
AND (@group_id::uuid = '00000000-0000-0000-0000-000000000000'::uuid OR tu.effective_group_id = @group_id)
AND (@provider_name::text = '' OR ai.provider_name = @provider_name)
AND (@model::text = '' OR ai.model = @model)
GROUP BY
ai.initiator_id,
users.username,
tu.effective_group_id,
groups.name,
groups.organization_id,
organizations.name,
ai.model,
ai.provider,
ai.provider_name
ORDER BY ai.initiator_id, tu.effective_group_id, ai.provider, ai.provider_name, ai.model
OFFSET @offset_opt
LIMIT NULLIF(@limit_opt::int, 0);
-- name: ListOrganizationAISpendUsers :many
-- Returns one page of per-user AI spend for @organization_id over the
-- [period_start, period_end) window, most expensive first. Each user has one
-- row per provider, model, and client they spent through, and every row
-- carries the user's spend and the count and totals over every matching user.
-- It must count the same token usage as the CSV export for the same filters.
WITH spend AS (
SELECT
ai.initiator_id AS user_id,
ai.provider_name,
ai.model,
COALESCE(ai.client, 'Unknown') AS client,
GROUPING(ai.provider_name) = 0 AS is_provider,
GROUPING(ai.model) = 0 AS is_model,
GROUPING(COALESCE(ai.client, 'Unknown')) = 0 AS is_client,
COALESCE(SUM(tu.cost_micros), 0)::BIGINT AS cost_micros,
BOOL_OR(tu.cost_micros IS NULL) AS has_unpriced_usage
FROM aibridge_token_usages tu
JOIN aibridge_interceptions ai ON ai.id = tu.interception_id
JOIN groups ON groups.id = tu.effective_group_id
WHERE groups.organization_id = @organization_id
AND tu.created_at >= @period_start::timestamptz
AND tu.created_at < @period_end::timestamptz
AND CASE
WHEN COALESCE(cardinality(@user_ids::uuid[]), 0) > 0
THEN ai.initiator_id = ANY(@user_ids::uuid[])
ELSE true
END
AND (
@group_id::uuid = '00000000-0000-0000-0000-000000000000'::uuid
OR tu.effective_group_id = @group_id
)
AND CASE
WHEN @provider_name::text != ''
THEN ai.provider_name = @provider_name::text
ELSE true
END
AND CASE
WHEN @model::text != '' THEN ai.model = @model::text
ELSE true
END
AND CASE
WHEN @client::text != ''
THEN COALESCE(ai.client, 'Unknown') = @client::text
ELSE true
END
GROUP BY GROUPING SETS (
(ai.initiator_id),
(ai.initiator_id, ai.provider_name),
(ai.initiator_id, ai.model),
(ai.initiator_id, COALESCE(ai.client, 'Unknown'))
)
),
-- The page, count, and totals apply to users, not to their provider, model,
-- and client rows.
user_page AS (
SELECT
spend.user_id,
users.username,
users.name,
users.avatar_url,
spend.cost_micros,
COUNT(*) OVER ()::BIGINT AS count,
COALESCE(SUM(spend.cost_micros) OVER (), 0)::BIGINT AS total_cost_micros,
BOOL_OR(spend.has_unpriced_usage) OVER () AS total_has_unpriced_usage
FROM spend
JOIN users ON users.id = spend.user_id
WHERE NOT (spend.is_provider OR spend.is_model OR spend.is_client)
ORDER BY spend.cost_micros DESC, LOWER(users.username), spend.user_id
LIMIT NULLIF(@limit_opt::int, 0)
OFFSET @offset_opt::int
)
SELECT
user_page.user_id,
user_page.username,
user_page.name,
user_page.avatar_url,
@organization_id::uuid AS organization_id,
user_page.cost_micros,
user_page.count,
user_page.total_cost_micros,
user_page.total_has_unpriced_usage,
(CASE
WHEN spend.is_provider THEN 'provider'
WHEN spend.is_model THEN 'model'
WHEN spend.is_client THEN 'client'
END)::text AS dimension,
(CASE
WHEN spend.is_provider THEN spend.provider_name
WHEN spend.is_model THEN spend.model
WHEN spend.is_client THEN spend.client
END)::text AS dimension_name,
COALESCE(providers.display_name, '')::text AS provider_display_name,
COALESCE(providers.icon, '')::text AS provider_icon,
COALESCE(providers.type::text, '')::text AS provider_type,
spend.cost_micros AS dimension_cost_micros,
spend.has_unpriced_usage AS dimension_has_unpriced_usage
FROM user_page
JOIN spend
ON spend.user_id = user_page.user_id
AND (spend.is_provider OR spend.is_model OR spend.is_client)
-- Provider details include deleted providers, since usage still references
-- them, and prefer the live provider when a name was reused.
LEFT JOIN LATERAL (
SELECT ai_providers.display_name, ai_providers.icon, ai_providers.type
FROM ai_providers
WHERE ai_providers.name = spend.provider_name
ORDER BY ai_providers.deleted ASC, ai_providers.updated_at DESC
LIMIT 1
) AS providers ON spend.is_provider
ORDER BY
user_page.cost_micros DESC,
LOWER(user_page.username),
user_page.user_id,
CASE
WHEN spend.is_provider THEN 1
WHEN spend.is_model THEN 2
WHEN spend.is_client THEN 3
END,
spend.cost_micros DESC,
dimension_name ASC;
-- name: GetOrganizationAISpendPerDay :many
-- Returns the AI spend in @organization_id for every UTC day in the
-- [period_start, period_end) window, including days without spend. Both
-- bounds are normalized to their UTC calendar day, matching
-- ai_user_daily_spend, so a period_end after midnight excludes that day.
SELECT
period_days.day::date AS day,
COALESCE(org_spend.spend_micros, 0)::BIGINT AS spend_micros
FROM generate_series(
((@period_start::timestamptz) AT TIME ZONE 'UTC')::date,
((@period_end::timestamptz) AT TIME ZONE 'UTC')::date - 1,
'1 day'::interval
) AS period_days(day)
LEFT JOIN (
SELECT
spend.day,
SUM(spend.spend_micros) AS spend_micros
FROM ai_user_daily_spend spend
JOIN groups ON groups.id = spend.effective_group_id
WHERE groups.organization_id = @organization_id
AND spend.day >= ((@period_start::timestamptz) AT TIME ZONE 'UTC')::date
AND spend.day < ((@period_end::timestamptz) AT TIME ZONE 'UTC')::date
GROUP BY spend.day
) org_spend ON org_spend.day = period_days.day::date
ORDER BY period_days.day;
-- name: GetOrganizationAISpendTotals :one
-- Returns the number of users with AI spend in @organization_id over the
-- [period_start, period_end) window, their total spend, and the median of
-- their per-user totals. Both bounds are normalized to their UTC calendar day,
-- matching ai_user_daily_spend, so a period_end after midnight excludes that
-- day.
WITH user_spend AS (
SELECT
spend.user_id,
SUM(spend.spend_micros) AS spend_micros
FROM ai_user_daily_spend spend
JOIN groups ON groups.id = spend.effective_group_id
WHERE groups.organization_id = @organization_id
AND spend.day >= ((@period_start::timestamptz) AT TIME ZONE 'UTC')::date
AND spend.day < ((@period_end::timestamptz) AT TIME ZONE 'UTC')::date
GROUP BY spend.user_id
HAVING SUM(spend.spend_micros) > 0
)
SELECT
COUNT(*)::BIGINT AS user_count,
COALESCE(SUM(spend_micros), 0)::BIGINT AS spend_micros,
COALESCE(ROUND(percentile_cont(0.5) WITHIN GROUP (ORDER BY spend_micros::double precision)), 0)::BIGINT AS median_spend_micros
FROM user_spend;
-- name: ListOrganizationAISpendPerUser :many
-- Returns one page of users with AI spend in @organization_id over the
-- [period_start, period_end) window, most spend first, with one row for each
-- group their spend was attributed to. A user's rows are adjacent and ordered
-- by group spend. Both bounds are normalized to their UTC calendar day,
-- matching ai_user_daily_spend, so a period_end after midnight excludes that
-- day. Users match GetOrganizationAISpendTotals.
WITH group_spend AS (
SELECT
spend.user_id,
spend.effective_group_id AS group_id,
SUM(spend.spend_micros)::BIGINT AS spend_micros
FROM ai_user_daily_spend spend
JOIN groups ON groups.id = spend.effective_group_id
WHERE groups.organization_id = @organization_id
AND spend.day >= ((@period_start::timestamptz) AT TIME ZONE 'UTC')::date
AND spend.day < ((@period_end::timestamptz) AT TIME ZONE 'UTC')::date
GROUP BY spend.user_id, spend.effective_group_id
HAVING SUM(spend.spend_micros) > 0
),
user_page AS (
SELECT
group_spend.user_id,
SUM(group_spend.spend_micros)::BIGINT AS spend_micros
FROM group_spend
JOIN users ON users.id = group_spend.user_id
GROUP BY group_spend.user_id, users.username
ORDER BY spend_micros DESC, LOWER(users.username), group_spend.user_id
LIMIT NULLIF(@limit_opt::int, 0)
OFFSET @offset_opt::int
)
SELECT
user_page.user_id,
users.username,
users.name,
users.avatar_url,
user_page.spend_micros,
groups.id AS group_id,
groups.name AS group_name,
groups.display_name AS group_display_name,
group_spend.spend_micros AS group_spend_micros
FROM user_page
JOIN users ON users.id = user_page.user_id
JOIN group_spend ON group_spend.user_id = user_page.user_id
JOIN groups ON groups.id = group_spend.group_id
ORDER BY
user_page.spend_micros DESC,
LOWER(users.username),
user_page.user_id,
group_spend.spend_micros DESC,
LOWER(groups.name),
groups.id;
-- name: GetUnpricedAIModels :many
-- Returns, for each live provider, the requested models with unpriced token
-- usage in the [period_start, period_end) window, most unpriced tokens first.
-- Only models without a price now are returned. A model has a price if it has a
-- site-wide price for the provider's type, or an organization price in the
-- organization of the usage's effective group. Usage priced through the model
-- reported by the provider, or whose cost could not be computed from a found
-- price, is not reported as unpriced. openai-compat providers cannot be priced,
-- so they are excluded. @organization_id limits the usage to one organization,
-- and null includes all.
WITH unpriced_usage AS (
SELECT
groups.organization_id,
interceptions.provider_name,
interceptions.model,
SUM(
token_usages.input_tokens
+ token_usages.output_tokens
+ token_usages.cache_read_input_tokens
+ token_usages.cache_write_input_tokens
)::bigint AS token_count
FROM aibridge_token_usages AS token_usages
JOIN aibridge_interceptions AS interceptions
ON interceptions.id = token_usages.interception_id
LEFT JOIN groups
ON groups.id = token_usages.effective_group_id
WHERE token_usages.created_at >= @period_start::timestamptz
AND token_usages.created_at < @period_end::timestamptz
AND token_usages.cost_micros IS NULL
AND token_usages.priced_model IS NULL
AND (
sqlc.narg('organization_id')::uuid IS NULL
OR groups.organization_id = sqlc.narg('organization_id')::uuid
)
GROUP BY groups.organization_id, interceptions.provider_name, interceptions.model
)
SELECT
providers.name AS provider_name,
providers.display_name AS provider_display_name,
providers.icon AS provider_icon,
providers.type::text AS provider_type,
unpriced_usage.model,
SUM(unpriced_usage.token_count)::bigint AS token_count
FROM unpriced_usage
JOIN ai_providers AS providers
ON providers.name = unpriced_usage.provider_name
AND providers.deleted = false
WHERE providers.type::text = ANY(@priceable_providers::text[])
AND NOT EXISTS (
SELECT 1
FROM ai_model_prices AS prices
WHERE prices.provider = providers.type::text
AND prices.model = unpriced_usage.model
)
AND NOT EXISTS (
SELECT 1
FROM ai_model_configs AS configs
WHERE configs.organization_id = unpriced_usage.organization_id
AND configs.provider_id = providers.id
AND configs.model = unpriced_usage.model
AND configs.deleted = false
AND COALESCE(
configs.input_price,
configs.output_price,
configs.cache_read_price,
configs.cache_write_price
) IS NOT NULL
)
GROUP BY providers.name, providers.display_name, providers.icon, providers.type, unpriced_usage.model
ORDER BY
token_count DESC,
provider_name ASC,
LOWER(unpriced_usage.model) ASC,
unpriced_usage.model ASC;