-- 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;