## Description
Paginates the experimental `GET
/api/experimental/organizations/{organization}/ai/model-prices/unpriced`
endpoint from #31753. The list is keyed by the requested model, which
clients set, so it has no upper bound.
## Changes
- `GetUnpricedAIModels` takes `limit_opt` and `offset_opt`, and each row
carries the `count` of every model. A zero limit returns every model, so
the unpriced models notification is unchanged.
- The endpoint reads `limit` and `offset` with
`parseOrganizationAISpendPage` (default 10, maximum 100), as the other
AI spend endpoints do.
- `count` is now the number of models over every page.
- `ExperimentalClient.OrganizationUnpricedAIModels` takes an
`OrganizationAISpendPage`.
- Add query and handler tests for pagination.
Related to AIGOV-730
> [!NOTE]
> Generated by Coder Agents on behalf of @ssncferreira.
915 lines
36 KiB
SQL
915 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. count is the number of models before the limit, and a
|
|
-- @limit_opt of zero returns every model.
|
|
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,
|
|
COUNT(*) OVER ()::BIGINT AS 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
|
|
LIMIT NULLIF(@limit_opt::int, 0)
|
|
OFFSET @offset_opt::int;
|