Skip to content

feat(coderd/database): add paginated organization AI spend user query - #29219

Merged
ibetitsmike merged 3 commits into
mainfrom
mike/ai-spend-query-users
Sep 17, 2026
Merged

ibetitsmike merged 3 commits into
mainfrom
mike/ai-spend-query-users

Conversation

@ibetitsmike

@ibetitsmike ibetitsmike commented Sep 11, 2026 •

Copy link
Copy Markdown
Collaborator

First of a four-PR stack that reworks the AI spend page onto the released organization spend export, GET /api/v2/organizations/{organization}/ai/spend/export, and a sibling per-user endpoint, GET /api/v2/organizations/{organization}/ai/spend/users, instead of the deployment-wide /api/v2/ai-gateway/spend/* endpoints this stack previously proposed. Every PR in the stack was rewritten in place from current main; the head branch name predates the rework. The export's accounting rules are unchanged: token usage by created_at in [period_start, period_end), attributed to the organization through the effective budget group, so the page reconciles with the CSV and with daily budget accounting.

Adds ListOrganizationAISpendUsers, the per-user aggregate behind the users endpoint. It shares the export query's joins and predicates, groups to one row per user with priced cost, a count of unpriced usage, and the sorted distinct providers, clients, and models the user spent through (a missing client is reported as Unknown, as in the sessions list), carries the window totals (count, total_cost_micros, total_unpriced_usage_count) as window aggregates ahead of LIMIT/OFFSET, and orders by cost, then username, then user ID. Because every row carries those totals, dbauthz requires read access to every group member in the organization before running the query, instead of filtering rows by the caller's own membership.

Includes the dbauthz wrapper and test, the dbmetrics and dbmock regeneration, and querier tests for grouping, totals across pages, filters, providers, clients, and models, shifted period windows (including token usage recorded outside the window of the interception that produced it), and organization isolation.

Xum prepared this PR on behalf of @ibetitsmike.

@ibetitsmike
ibetitsmike force-pushed the mike/ai-spend-query-users branch from 2153280 to aeb88bc Compare September 11, 2026 10:01
@ibetitsmike
ibetitsmike marked this pull request as ready for review September 11, 2026 11:31
@chatgpt-codex-connector

chatgpt-codex-connector Bot commented Sep 11, 2026 •

Copy link
Copy Markdown

Codex Review Summary

This comment shows the latest Codex review activity on this pull request.

Review Status Commit Review trigger
📝 Code Review ✅ Completed 2026-09-16T16:58:27.436228Z 29ed31e Manual request
ℹ️ About Codex in GitHub

Your team has set up Codex to review pull requests in this repo. Reviews are triggered when you

  • Open a pull request for review
  • Mark a draft as ready
  • Comment "@codex review" or "@codex security review".

Codex reacts with 👀 while any review is running, comments if it has suggestions, and reacts with 👍 once all reviews finish with no findings.

@chatgpt-codex-connector chatgpt-codex-connector Bot left a comment

Copy link
Copy Markdown

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

💡 Codex Review

Here are some automated review suggestions for this pull request.

Reviewed commit: aeb88bce05

ℹ️ About Codex in GitHub

Your team has set up Codex to review pull requests in this repo. Reviews are triggered when you

  • Open a pull request for review
  • Mark a draft as ready
  • Comment "@codex review".

If Codex has suggestions, it will comment; otherwise it will react with 👍.

Codex can also answer questions or update the PR. Try commenting "@codex address that feedback".

Comment thread coderd/database/queries/aibridge.sql Resolved
@ssncferreira
ssncferreira self-requested a review September 11, 2026 11:55
@ibetitsmike
ibetitsmike force-pushed the mike/ai-spend-query-users branch from aeb88bc to 71dfc0c Compare September 14, 2026 08:03
@ibetitsmike

Copy link
Copy Markdown
Collaborator Author

@codex review

@chatgpt-codex-connector

Copy link
Copy Markdown

Codex Review: Didn't find any major issues. Hooray!

Reviewed commit: 71dfc0c9db

ℹ️ About Codex in GitHub

Your team has set up Codex to review pull requests in this repo. Reviews are triggered when you

  • Open a pull request for review
  • Mark a draft as ready
  • Comment "@codex review".

If Codex has suggestions, it will comment; otherwise it will react with 👍.

Codex can also answer questions or update the PR. Try commenting "@codex address that feedback".

Comment thread coderd/database/queries/aibridge.sql Resolved
Comment thread coderd/database/queries/aibridge.sql Resolved
Comment thread coderd/database/queries/aibridge.sql Resolved
@ibetitsmike
ibetitsmike marked this pull request as draft September 15, 2026 18:48
@ibetitsmike
ibetitsmike removed this pull request from stack #29225 September 15, 2026 18:49
@ibetitsmike
ibetitsmike force-pushed the mike/ai-spend-query-users branch from 71dfc0c to 7345ff5 Compare September 15, 2026 18:50
@ibetitsmike ibetitsmike changed the title feat(coderd/database): add AI spend user query feat(coderd/database): add paginated organization AI spend user query Sep 15, 2026
@ibetitsmike

Copy link
Copy Markdown
Collaborator Author

This PR was reworked in place on 2026-09-15: the stack now extends the released organization spend export (GET /api/v2/organizations/{organization}/ai/spend/export) with filters and an Accept: application/json per-user report instead of adding /api/v2/ai-gateway/spend/* endpoints, and the page shows one organization's total and per-user cost only. The description above reflects the new content; earlier review threads refer to code that no longer exists on this branch. #29222 (session counts) was closed because it has no counterpart.

@codex review

Xum posted this comment on behalf of @ibetitsmike.

@chatgpt-codex-connector chatgpt-codex-connector Bot left a comment

Copy link
Copy Markdown

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

💡 Codex Review

Here are some automated review suggestions for this pull request.

Reviewed commit: 7345ff55b7

ℹ️ About Codex in GitHub

Your team has set up Codex to review pull requests in this repo. Reviews are triggered when you

  • Open a pull request for review
  • Mark a draft as ready
  • Comment "@codex review".

If Codex has suggestions, it will comment; otherwise it will react with 👍.

Codex can also answer questions or update the PR. Try commenting "@codex address that feedback".

Add ListOrganizationAISpendUsers, which aggregates AI Gateway token usage
to one row per user for an organization over a [period_start, period_end)
window, with optional provider_name, model, and client filters, ordered by
cost. Each row carries the organization-wide user count and cost totals
so a paginated caller can report them without a second query.

The query shares its joins and predicates with ExportOrganizationAISpend
so the JSON report and the CSV export account for the same token usage.
@ibetitsmike
ibetitsmike force-pushed the mike/ai-spend-query-users branch from 7345ff5 to ba04efe Compare September 16, 2026 11:16

@ssncferreira ssncferreira left a comment

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

LGTM 👍

Comment thread coderd/database/querier_test.go Resolved
@ibetitsmike
ibetitsmike removed this pull request from stack #29372 September 16, 2026 12:23
@ibetitsmike
ibetitsmike marked this pull request as ready for review September 16, 2026 13:50
Every row of ListOrganizationAISpendUsers carries the count and totals over
the whole organization, so filtering rows by the caller's group membership
still exposed other users' spend. dbauthz now requires read access to every
group member in the organization before running the query.

Also cover shifted period windows and token usage recorded outside the
window of the interception that produced it.
@ibetitsmike

Copy link
Copy Markdown
Collaborator Author

Pushed fixes for the human review round (organization-wide group member authorization in dbauthz, period window tests).

@codex review

Xum posted this comment on behalf of @ibetitsmike.

@chatgpt-codex-connector

Copy link
Copy Markdown

Codex Review: Didn't find any major issues. 🚀

Reviewed commit: 2cf3ea5f88

ℹ️ About Codex in GitHub

Your team has set up Codex to review pull requests in this repo. Reviews are triggered when you

  • Open a pull request for review
  • Mark a draft as ready
  • Comment "@codex review".

If Codex has suggestions, it will comment; otherwise it will react with 👍.

Codex can also answer questions or update the PR. Try commenting "@codex address that feedback".

ListOrganizationAISpendUsers returns the sorted distinct models per user
next to providers and clients, so the spend page can show a Models
column. The fixture gives one user two models under one provider so the
expectations cannot be derived from the providers array.
@ibetitsmike

Copy link
Copy Markdown
Collaborator Author

Pushed the models aggregate requested for the spend page (29ed31e: ListOrganizationAISpendUsers returns each user's sorted distinct models).

@codex review

Xum posted this comment on behalf of @ibetitsmike.

@chatgpt-codex-connector

Copy link
Copy Markdown

Codex Review: Didn't find any major issues. 🚀

Reviewed commit: 29ed31ee4c

ℹ️ About Codex in GitHub

Your team has set up Codex to review pull requests in this repo. Reviews are triggered when you

  • Open a pull request for review
  • Mark a draft as ready
  • Comment "@codex review".

If Codex has suggestions, it will comment; otherwise it will react with 👍.

Codex can also answer questions or update the PR. Try commenting "@codex address that feedback".

@ibetitsmike
ibetitsmike merged commit 2e03073 into main Sep 17, 2026
32 checks passed
@ibetitsmike
ibetitsmike deleted the mike/ai-spend-query-users branch September 17, 2026 04:32
@github-actions github-actions Bot locked and limited conversation to collaborators Sep 17, 2026
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

3 participants