Commit Graph
30 Commits
Author SHA1 Message Date
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
Susana Ferreira d611c60a9a feat: scope unpriced AI models to an organization and its prices (#31752)
## Description

Scopes `GetUnpricedAIModels` to an optional organization and checks
organization prices, so the unpriced models endpoint can list what an
organization still needs to price. Usage is attributed to an
organization through its token usage's effective group, the same as
spend.

## Changes

- Optional `organization_id`: when set, only that organization's usage
counts, and when null (the notification), all usage counts.
- A model still holds a price if it has a site-wide price for the
provider type, or an organization price (`ai_model_configs`, live and
with a price) for the provider in the usage's organization.
- dbauthz reads `ResourceAiModelPrice`, scoped to the organization when
one is given, like `GetAIModelPriceForProvider`.
- Table-driven query tests run every case for all organizations, org A
and org B.

<details>
<summary>Cases</summary>

Model X is on `anthropic-prod`. Y is the model the provider reports
serving for X. "Notif" is the deployment-wide notification, "UI A/B" is
the endpoint for org A/B.

| # | Prices | Usage | Notif before | Notif after | UI A | UI B |
|---|---|---|---|---|---|---|
| 1 | No price for X anywhere | A uses X, unpriced | ✅ | ✅ | ✅ | ❌ (B
didn't use X) |
| 2 | Site-wide price for X | A and B use X, priced | ❌ | ❌ | ❌ | ❌ |
| 3 | A priced X before the usage | Only A uses X, priced | ❌ | ❌ | ❌ |
❌ |
| 4 | A priced X after the usage | Only A uses X, unpriced when recorded
| ✅ (wrong) | ❌ | ❌ | ❌ |
| 5 | A priced X, B has none | A uses X (priced), B uses X (unpriced) |
✅ (because of B) | ✅ (because of B) | ❌ | ✅ |
| 6a | No price for X or Y when recorded, then Y priced site-wide | Only
A uses X, unpriced when recorded | ✅ | ✅ | ✅ | ❌ |
| 6b | Same as 6a | A and B use X, unpriced when recorded | ✅ | ✅ | ✅ |
✅ |
| 7a | No price for X, but a site-wide price for Y | A uses X, priced
through Y | ❌ | ❌ | ❌ | ❌ |
| 7b | No price for X, A priced Y, and B has none | A and B use X: A
priced through Y, B unpriced | ✅ (because of B) | ✅ (because of B) | ❌ |
✅ |

</details>

Note: pagination will be added in a follow-up.

Related to AIGOV-730

> [!NOTE]
> Generated by Coder Agents on behalf of @ssncferreira.
2026-11-13 13:21:32 +00:00
Susana Ferreira 8c5921db5e fix: count unpriced AI model usage by when it was recorded (#31751)
## Description

Updates the unpriced AI models query behind the weekly owner
notification to count token usage by when it was recorded
(`token_usages.created_at`) in a `[period_start, period_end)` window,
consistent with the other AI spend queries. It also returns one row per
provider name and model instead of per provider type, so the unpriced
models endpoint later in this stack can reuse it. The notification
payload keeps the same format.

## Changes

- Rename `GetUnpricedAIModelsSince` to `GetUnpricedAIModels`. It used
`interceptions.started_at` with no end.
- Treat usage as unpriced only when no price was found (`cost_micros IS
NULL AND priced_model IS NULL`), so usage whose price was found but
whose cost could not be computed is not reported.
- Return one row per live provider name and model, with the provider's
display name, icon and type.
- The notification passes the last 7 days and merges rows by provider
type, since site-wide prices are keyed by type, so its payload keeps the
same shape.
- Table-driven query tests and a notification test for providers sharing
a type.

Related to AIGOV-730

> [!NOTE]
> Generated by Coder Agents on behalf of @ssncferreira.
2026-11-13 13:21:32 +00:00
Susana Ferreira 7db53f3b47 feat: add organization AI spend overview queries (#31711)
## Description

Adds 3 database queries for the organization AI spend overview. They
read the daily spend aggregate (`ai_user_daily_spend`), scoped to the
organization through each spend row's effective group, over a
`[period_start, period_end)` range of UTC days.

1. `GetOrganizationAISpendTotals`: the number of users with spend, the
total spend, and the median of the users' total spend.
2. `ListOrganizationAISpendPerUser`: paginated users, most spend first,
with one row for each group their spend was attributed to.
3. `GetOrganizationAISpendPerDay`: the spend for every UTC day in the
range, including days without spend.

Note: Only users with spend above zero are included, so users with only
unpriced usage are not listed.

## Changes

- dbauthz checks matching `/ai/spend/users` (read on organization group
members).
- Querier and dbauthz tests.

Related to AIGOV-728

> [!NOTE]
> Generated by Coder Agents on behalf of @ssncferreira.
2026-11-13 10:02:58 +00:00
Sas Swart eb38759fda feat: limit effective group resolution to an organization (#31119)
AI budget resolution searches all of a user's groups across every
organization. Whichever group wins determines the organization. #31120
needs to resolve inside a known organization, and it needs to know which
organization site-wide resolution picked.

Add an `organizationID uuid.NullUUID` argument to
`budget.ResolveUserAIBudget` and `budget.ResolveUserEffectiveGroup`.
When it is set, count only that organization's override, group budgets
and Everyone group. An organization that is unknown, deleted or that the
user is not a member of resolves to nothing. When it is null, resolve
exactly as before. Return the winning group's organization in
`budget.EffectiveGroup.OrganizationID`.

Read the override through a new resolution-only query,
`GetUserAIBudgetOverrideWithOrganization`, so that the CRUD query
`GetUserAIBudgetOverride` and the admin endpoints stay untouched. Add a
nullable `organization_id` filter to `GetHighestGroupAIBudgetByUser` and
`GetUserEveryoneFallbackGroup`.

Every caller passes null, so behaviour is unchanged. There is no
migration and no proto change.

Scoping to an organization can lift a limit: a user capped in org B has
no limit in org A if A has no budgets. AIGOV-711 intends this, and it
takes effect in #31120.

Part of AIGOV-715.

---

*This PR was generated by Coder Agents on behalf of @SasSwart.*
2026-10-29 12:14:34 +02:00
TJ 4e4a56b665 fix: show configured provider icons and names in AI settings (#31033)
Admin-configured provider icons were ignored on the AI Settings Models
page (model rows, Add model menu, provider filter, model form header and
provider select) and in Agents compaction settings. These views derived
the icon only from the provider type, so a provider like DeepSeek on an
OpenAI-compatible type showed a generic icon even though the Providers
page and chat model selector showed the configured one. They now pass
the configured icon to `ProviderIcon`, which falls back to the type
icon.

The Spend page showed provider types (OpenAI) instead of configured
providers (DeepSeek) because `ListOrganizationAISpendUsers` aggregated
`ai.provider` while its filter matched `ai.provider_name`. It now
aggregates `provider_name`, and the page resolves names to display names
and icons from the AI Gateway providers list, which it shares with the
provider filter through one query. `OrganizationAISpendUser.providers`
now holds provider names; records from before configured names existed
have the type as their name, so they render as before.

AI Gateway Sessions views have the same type-versus-name issue and are
left for a separate fix.

**Note:** the Spend page resolves provider display names and icons from
`/aibridge/providers`, which requires deployment-wide read access
(`viewAnyAIBridgeInterception`). Owners and Auditors see the custom
display name and icon; Template Admins, User Admins, Org Admins, Org
Auditors and Org User Admins see the provider name rendered as a type
without the icon. Revisiting that endpoint's permissions is tracked in
AIGOV-726.

Refs CODAGT-1421

<details>
<summary>Decision log</summary>

- Root cause: callers rendered `<ProviderIcon provider={type} />`
without the descriptor's `icon`; nothing was stale.
- Spend page provider metadata is fetched only for viewers who can
already use the provider filter (`viewAnyAIBridgeInterception`); other
viewers see names formatted like provider types.
- Extended `ProvidersBadge` with an optional `providersByName` map
instead of adding a Spend-specific badge, so the Sessions fix can reuse
it.
- Removed `providerTypeByIDFromUserConfigs`, which became unused once
compaction settings used `providerInfoByIDFromUserConfigs`.
- Visual coverage is in Storybook stories (ModelRow, ModelForm,
UserCompactionThresholdSettings, SpendPage) per the repo's FE1 rule.

</details>

---

_Generated by Coder Agents on behalf of @tracyjohnsonux._
2026-10-26 13:26:38 +00:00
Sas Swart 837c2e1af1 feat(coderd): price token usage with organization prices (#30719)
Prices token usage by the most specific match: the organization's price
for the configured provider, then a site-wide custom price, then the
price book. The requested model is still preferred over the model the
provider reports.

- `GetAIModelPriceForProvider` replaces
`GetAIModelPriceByProviderModel`, which is removed. It is one `UNION
ALL` of primary-key lookups, ordered by requested model first, then by
specificity. dbauthz checks `ResourceAiModelPrice`, scoped to the org
when one is given.
- `coderd/aibridge/prices` tests read seeded prices through
`GetAIModelPrices`.

Interceptions don't carry an organization yet, so
`resolveTokenUsageCost` passes none and only site-wide prices apply at
runtime for now (AIGOV-716). Org matching is covered by database tests
with explicit org IDs. For the same reason, the unpriced-models report
still counts only site-wide prices.

**Used later:** prices set through the API in #30720 take effect here
once interceptions carry an organization.

---

*This PR was generated by Coder Agents on behalf of @SasSwart.*
2026-10-22 15:13:24 +00:00
Sas Swart da78c88100 feat(coderd): add queries for organization AI model prices (#30718)
Adds the store queries for organization AI model prices in
`ai_model_configs`, with authorization. They cover basic CRUD on one
price, keyed by `(organization_id, provider_id, model)`.

- `GetOrganizationAIProviderModelPrice` reads one price.
- `UpsertOrganizationAIProviderModelPrice` sets one price, with typed
params. It rewrites an existing row only when a price differs, so
`updated_at` records the last change.
- `DeleteOrganizationAIProviderModelPrice` soft deletes one price. Reads
and upserts only match live rows, so setting a price after a delete
inserts a new live row.

All three authorize against `ResourceAiModelPrice.InOrg(org)`. The new
`AiModelPriceInOrg` case in `TestRolePermissions` pins that only owners
and org admins pass. A TODO in dbauthz notes that this should become a
resource for AI Governance model config.

Each query has its own test. The upsert tests cover that identical
prices leave `updated_at` unchanged, that changed prices advance it, and
that an empty model is rejected.

**Used later:** #30719 seeds its lookup tests with the upsert. #30720
serves all three through the org price API.

---

*This PR was generated by Coder Agents on behalf of @SasSwart.*
2026-10-22 13:58:42 +00:00
Susana Ferreira 34db9ca638 feat: price AI Gateway token usage by provider model (#30090)
## Description

When the requested model has no price, price AI Gateway token usage by
the model the provider reports instead of leaving it unpriced. This
covers providers that resolve an alias to a specific model, such as
`codex-auto-review` served as `gpt-5.6-luna`.

Stacked on #30086, which records the provider model.

Closes https://linear.app/codercom/issue/AIGOV-650

## Changes

- Look up the price for the requested model first, then the provider
model, preferring a custom price over the price book for each model.
- Store the provider model and the model used for pricing
(`priced_model`) on each token usage.
- Add a `provider_model` label to `unpriced_token_usage_records_total`.
- Document the lookup order and the new metric label.

> [!NOTE]
> Generated by Claude Code on behalf of @ssncferreira.
2026-09-29 17:47:28 +01:00
Michael Suchacz 2872e88827 perf(coderd/database): aggregate AI spend before joining user profiles (#29737)
The organization spend query joined user profile fields onto every usage
row before aggregating. On dogfood's September window, this sorted 1.23
million rows and wrote about 1.5 GiB of temporary data per request.
Aggregate by user first, then join profiles and apply ordering and
pagination.

A counterbalanced production-data benchmark (six runs each, AB/BA order,
September 1 through September 22 15:00 UTC cutoff, 1,233,784 usage rows)
measured median database execution time of 6.08 s before and 3.12 s
after this change. Ranges were 5.74 to 8.32 s and 2.52 to 4.06 s,
respectively; median per-pair speedup was 2.10x. Median temporary writes
fell from 1,610 MiB to 329 MiB. Both queries had zero shared-buffer
reads in every run. These are EXPLAIN ANALYZE timings on a busy
database, not API/page-load latency. A same-snapshot, unlimited-page
comparison found zero differing rows in either direction, including row
order, across all 88 users.

Refs #28964. This is a one-layer performance stack; further optimization
experiments are not included here. No schema, API, or frontend changes.

<details>
<summary>Delivery record</summary>

Head: `41a179a17464bb54bbc9b3886e9c3ea6b7bda7ef`. SQLC generation,
targeted database/HTTP/export/authorization tests, and the full
pre-commit hook passed before publication and again before requesting
Codex review. The existing query test now covers usage across two groups
in one organization.

Remote browser UAT: not run. Production SQL evidence covers a prototype
verified identical to the committed query; this head is not deployed.
Frontend checks were not run because no frontend or response type
changed. Local pre-push checks were skipped as requested.

Codex:
[OK](https://github.com/coder/coder/pull/29737#issuecomment-5780120712)
at 2026-09-22 16:27:06 UTC for `41a179a174`, after the
[request](https://github.com/coder/coder/pull/29737#issuecomment-5780086039)
at 16:24:34 UTC. No findings or unresolved threads. Completed
independent review passes: 1/6. CI: all current-head check runs passed
or were skipped, including the required gate. The non-required `Pixel /
Review` status remains pending. Codex reports its current-head review
completed; one trigger, no retriggers, no code changes requested.

</details>

> Xum opened and manages this PR on behalf of @ibetitsmike.
2026-09-22 21:58:18 +02:00
Susana Ferreira dadbbe39ff feat: add filters and pagination to the AI spend database query (#29412)
## Description

Extend the existing organization AI spend export query with filtering
and pagination.

## Changes

- Add user, effective group, provider name, and model filters.
- Return the total matching-row count before pagination.
- Require organization-wide group-member read access.
- Keep existing CSV exports unpaginated.

> [!NOTE]
> Generated by Coder Agents on behalf of @ssncferreira.
2026-09-18 15:26:24 +01:00
Michael Suchacz 2e03073938 feat(coderd/database): add paginated organization AI spend user query (#29219)
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.
2026-09-17 04:32:15 +00:00
Susana Ferreira 45fb985ce6 fix(coderd/database): exclude system users from AI budgets (#28698)
## Description

Group AI budget totals counted system users, including the prebuilds
user, even though they are excluded from the displayed member count.

The budget queries now exclude system users when calculating group
totals and over-budget user counts.

## Changes

- Exclude system users from group AI budget totals.
- Exclude system users from over-budget user counts.
- Add database regression coverage.

Closes https://linear.app/codercom/issue/AIGOV-595

> [!NOTE]
> Generated by Coder Agents on behalf of @ssncferreira.
2026-09-09 09:29:57 +01:00
Yevhenii Shcherbina 0bc2858b6e feat: report unpriced AI models to owners (#28419)
Closes
[AIGOV-568](https://linear.app/codercom/issue/AIGOV-568/notify-admins-about-unpriced-ai-models).

AI Bridge records token usage for a model with no price at a NULL cost,
so its spend is neither reported nor enforced against any budget. Until
now that only reached admins through an info log and a Prometheus
metric.

Owners now receive a weekly report listing models used without a price
in the past week. The report links to documentation explaining how Coder
calculates spend from the default price book and how owners can
configure missing prices with `coder exp ai-model-prices`.

## Screenshots

<img width="726" height="531" alt="image"
src="https://github.com/user-attachments/assets/168ae378-60cb-4c96-8f60-36c6edc0b268"
/>
<img width="470" height="417" alt="image"
src="https://github.com/user-attachments/assets/7dc71a1e-0c57-44f1-86f7-df800603c865"
/>



## Design

**Derived, not tracked.** The unpriced set is computed at report time
from interceptions that recorded token usage, joined against the current
`ai_model_prices` table. Requests that produced no token usage are
excluded.

**No new loop or table.** `reportUnpricedAIModels` is a sibling of
`reportFailedWorkspaceBuilds` in the existing report generator, reusing
its ticker and `notification_report_generator_logs`. Each report runs in
its own transaction under a separate advisory lock, so failures and lock
contention do not couple otherwise independent reports. The weekly
frequency is enforced by the persisted timestamp rather than by the
ticker, which restarts with the process and runs on a different phase in
each replica.

## Behaviour

| Situation | Outcome |
|---|---|
| Model used without a price | Listed in the next weekly report |
| Price set | Disappears from the next report |
| Still unpriced and still in use | Reported again each week |
| Model stops being used | Drops out of the report |
| Nothing unpriced | No notification; window still advances |
| More than 100 unpriced models | Top 100 by usage, with the total
reported alongside |
| openai-compat models | Excluded, they cannot be priced |
| Many replicas | Exactly one report per week |
| First ever run | Reports models used in the preceding week immediately
|

## Design choices

1. **openai-compat** is excluded because it cannot be priced and would
produce permanent, unactionable notifications.
2. **Token volume** orders the list but is not shown. The 100-model cap
therefore prioritizes models responsible for the most unpriced usage.
3. **Placement** remains in the generic report generator rather than
`aibridgedserver`. This keeps one periodic-report lifecycle at the cost
of coupling the notifications package to an AI cost-control query.
4. **No license gate.** The reporter runs even when AI Gateway is
disabled. With no recorded token usage the query is empty and no
notification is sent.

---

Created by Coder Agents on behalf of @evgeniy-scherbina.
2026-08-27 21:04:13 +00:00
Susana Ferreira 276d0a8586 fix: expose effective member AI budgets (#28410)
## Description

The group members page could not distinguish an unlimited Everyone
fallback from a configured budget on the Everyone group when viewing
another group, so it incorrectly displayed `Unlimited` and `Everyone
(not allocated)` despite the configured Everyone budget.

The member spend response now includes `effective_budget` as the
canonical budget that currently applies to the user. The existing
`group_budget` field remains for backward compatibility and is
deprecated in favor of `effective_budget`.

## Changes

- Add `effective_budget` to group member AI spend responses.
- Deprecate `group_budget` in favor of `effective_budget`.
- Display budgeted Everyone as the governing group instead of
`Unlimited` and `Everyone (not allocated)`.
- Add database, API, conversion, and Storybook coverage.

Closes https://linear.app/codercom/issue/AIGOV-588

> [!NOTE]
> Generated by Coder Agents on behalf of @ssncferreira.
2026-08-26 16:17:07 +01:00
Susana Ferreira 50ecd6959f feat: add a source filter to the AI model price list (#28329)
## Description

With prices from the price book and prices set through the API both in the table, `list` showed only the price in effect and gave no way to tell where it came from. There was also no way to see the price book's entry for a model that had been overridden, since the listing resolves to the custom price.

This adds a `source` column to the output and a `--source` filter for narrowing to one source, or to every row with `all`.

Note: `--source` selects rows rather than models. A model carrying both a price book price and a custom one appears under either value, so the two filtered listings do not sum to the unfiltered one. `--source all` reports both rows at once.

## Changes

- Add `Source` to `codersdk.AIModelPrice` and `AIModelPricesFilter`, and map it in `db2sdk`, which previously dropped it.
- Filter `GetAIModelPrices` by source before resolution, so the price book's row for an overridden model stays reachable.
- Accept `all` as a source, which reports every price a model holds instead of resolving to one.
- Accept a `source` query parameter on the list endpoint, rejected with a 400 when outside the enum.
- Add `--source` to the list command and a `source` column to its output.
- Document the column and the filter, and lead the price section with `coder exp ai-model-prices list`.
- Distinguish a deployment's default prices from the price book that ships with each release.

Closes https://linear.app/codercom/issue/AIGOV-593

> [!NOTE]
> Initially generated by Claude Opus 5, modified and reviewed by @ssncferreira
2026-08-20 11:56:58 +01:00
Susana Ferreira 9390e51422 feat: distinguish default and custom AI model prices (#28204)
## Description

Prices in `ai_model_prices` come from two places: the price book embedded in each Coder release, and prices set through the experimental CLI (https://github.com/coder/coder/pull/27926). Until now, they were indistinguishable, so the startup seeder re-applied the book over every row, and a price someone set would silently revert on the next restart.

This adds a `source` column with values `default` and `custom`, and keeps a row for each. Readers resolve a model to its custom price when one exists and fall back to the book, including the cost path that prices an interception.

Keeping both rows means the price book stays current underneath an override, so a future delete can restore it.

Note: the endpoint still only accepts models the price book does not cover. Overriding a price the book already carries is handled by the follow-up issue https://linear.app/codercom/issue/AIGOV-594.

## Changes

- Add a migration for the `ai_model_price_source` enum and a `source` column, backfilled to `default` and then `NOT NULL`. `source` joins the primary key, so a model can hold one row per source.
- Take a `source` parameter on `UpsertAIModelPrices`. Each source keeps its own row, so the price book and a custom price never overwrite each other.
- Resolve to the custom price in `GetAIModelPriceByProviderModel` and `GetAIModelPrices`, so both the interception cost snapshot and the list endpoint use it.
- Seed the price book as `default`, and write prices set through the endpoint as `custom`.

Closes https://linear.app/codercom/issue/AIGOV-592

> [!NOTE]
> Initially generated by Claude Opus 5, modified and reviewed by @ssncferreira
2026-08-20 11:36:58 +01:00
Susana Ferreira 2d9b6eda8f feat: add experimental CLI to price unpriced AI models (#27926)
## Description

AI Gateway computes the cost of an interception from `ai_model_prices`,
which is seeded on every server start from a price book embedded in the
binary. A model the price book does not cover records a NULL cost, so
its spend is invisible to cost reporting and is not enforced against
budgets. The only fix was to wait for a Coder release that added the
model.

This adds an experimental CLI, backed by an experimental HTTP endpoint,
for pricing those models. Models the price book already covers are
rejected, because the seeder re-applies the book on every start and
would overwrite an operator price. Support for custom pricing will be
handled in
https://linear.app/codercom/issue/AIGOV-589/extend-experimental-cli-command-to-set-custom-ai-model-prices.

## Commands

```
coder exp ai-model-prices list [--provider] [--model]
coder exp ai-model-prices update [file|-] [--provider] [--model] [--input-price] [--output-price] [--cache-read-price] [--cache-write-price] [--yes]
```

## Changes

- Add `GET` and `POST /api/experimental/ai/model-prices`, gated behind
the AI Bridge entitlement and the existing `ai_model_price` RBAC
resource.
- Add a `GetAIModelPrices` query with optional `provider` and `model`
filters applied in SQL.
- Validate the whole request before writing anything, so one bad entry
cannot leave the table half updated, and report every problem at once.
- Reject prices for models the embedded price book already covers,
through a new `prices.IsDefaultPriced`.
- Add the `coder exp ai-model-prices` command with `list` and `update`.
`update` accepts a JSON document or the single-model flags and prints a
plan, asking to confirm unless the document is piped in or `--yes` is
passed.
- Consolidate the supported provider list into
`coderd/aibridge/prices/providers` so the price generator and the server
share one definition.
- Add `codersdk` types and client methods for both endpoints, and bound
the request body at 1 MiB.
- Document the command in the AI Gateway cost controls page.

Closes
https://linear.app/codercom/issue/AIGOV-567/experimental-cli-command-to-set-prices-for-unpriced-ai-models

> [!NOTE]
> Initially generated by Claude Opus 5, modified and reviewed by
@ssncferreira
2026-08-13 15:00:36 +01:00
Susana Ferreira 5efa7abe7d fix: only write AI model prices that changed (#27923)
Previously, the AI Gateway price seeder rewrote every row of
`ai_model_prices` on each server start, because `ON CONFLICT` fires on a
key conflict rather than on a value difference. `updated_at` therefore
recorded when the server last restarted rather than when a price last
changed.

Guard the `DO UPDATE` branch so a conflicting row is only rewritten when
one of its four prices differs. The comparison uses `IS DISTINCT FROM`
rather than `<>` because the price columns are nullable, and `<>` yields
NULL when either side is NULL, which would skip the update and leave a
stale price in place.

Related to
https://linear.app/codercom/issue/AIGOV-567/experimental-cli-command-to-set-prices-for-unpriced-ai-models

> [!NOTE]
> Initially generated by Claude Opus 5, modified and reviewed by
@ssncferreira
2026-08-10 16:33:48 +01:00
Susana Ferreira 0b4095085e fix: report combined member limit in group AI spend (#27589)
## Problem

The organization groups page showed each group's AI budget as the
group's per-member limit, so the total it displayed was effectively
group members × group budget. That ignores per-user budget overrides
charged to the group, so a group where one member has an override
reported a limit that doesn't match what its members can actually spend.

## Changes

- Add `total_spend_limit_micros` to the organization groups AI spend
payload, the combined budget of the members attributed to the group,
with each member's override replacing their share.
- Return `null` for the total when the group has no budget, since its
members spend without a cap.
- Both the organization groups and single group spend endpoints report
the new field, as they share the same query.
- Use the total as the denominator on the groups page AI budget column.

Depends on #27568
2026-07-29 09:16:37 +01:00
Susana Ferreira c3895ff9c0 feat: add CSV export for AI spend data (#27491)
## Description

Adds `GET /api/v2/organizations/{organization}/ai/spend/export`,
returning `text/csv` with per-user, per-group, per-model, per-provider
aggregated AI spend. The data is built from the raw AI Gateway token
usage tables rather than the `ai_user_daily_spend` rollup, but stays
consistent with it: spend is attributed through the token usage's
effective group and bucketed by the token usage `created_at`, the same
values the daily rollup derives from.

The period defaults to the current UTC month, narrowed to the configured
AI Gateway retention window when the month begins before retained data
does. Explicit `period_start`/`period_end` params must be provided
together, are interpreted as UTC, and may span at most 31 days. Unlike
the default period, an explicit period that begins before the retention
window is rejected rather than narrowed. Every row echoes the applied
bounds, so a narrowed window is visible in the export.

The endpoint requires organization-level admin permissions.

## Changes

- Add the `ExportOrganizationAISpend` query aggregating
`aibridge_token_usages` joined to `aibridge_interceptions`, scoped to
the organization via the effective group, resolving the username, group
name, and organization name alongside their IDs.
- Add the `exportOrganizationAISpend` handler and route, gated by the
`aigateway-cost-control` experiment and the `AIBridge` feature,
returning the CSV in a single response.
- Add the `ExportOrganizationAISpend` codersdk client method.
- Require organization-wide `ResourceGroupMember` read, since the export
aggregates every user in the organization. The per-row filter stays in
`dbauthz` as defence in depth.
- Escape leading formula characters in the free-text columns, so a model
or provider name recorded from an intercepted request cannot be
evaluated when the CSV is opened in a spreadsheet.
- Add an index on `aibridge_token_usages (effective_group_id,
created_at)`, which the period and group predicates otherwise cannot
use.

Closes
https://linear.app/codercom/issue/AIGOV-293/add-csv-export-for-ai-spend-data

> [!NOTE]
> Generated by Coder Agents on behalf of @ssncferreira
2026-07-28 10:58:38 +01:00
Susana Ferreira c351280a37 feat: add Prometheus metrics for AI Governance cost control (#27490)
## Description

Adds Prometheus metrics for AI budget cost control, emitted by the
aibridged server under the `cost_control` subsystem (full names are
prefixed `coder_ai_gateway_`).

- `blocked_requests_total` (counter) — labels: `group_id`
- `blocked_users` (gauge) — labels: `group_id`
- `unpriced_requests_total` (counter) — labels: `provider`, `model`
- `enforcement_duration_seconds` (histogram) — labels: `outcome`

## Changes

- Add `GetOverBudgetUsersPerGroup` query (plus dbauthz/dbmetrics/dbmock
wiring) to count over-budget users per effective group.
- Add a background collector that refreshes the `blocked_users` gauge on
an interval, started only when Prometheus is enabled.
- Wire `Metrics` through the aibridged server, coderd API,
`cli/server.go`, and the enterprise AI gateway handler; recording is
nil-safe when metrics are unset.

Closes
https://linear.app/codercom/issue/AIGOV-296/add-prometheus-metrics-for-cost-control

> [!NOTE]
> Initially generated by Claude Opus 4.7, modified and reviewed by
@ssncferreira
2026-07-28 09:22:58 +01:00
Susana Ferreira c23f2c0223 feat: fall back to the Everyone group for AI spend attribution (#27364)
## Description

Previously, a user with no per-user override and no membership in a budgeted group had no effective group, so their AI spend was attributed nowhere and was, therefore, untracked. This change falls back to the organization's Everyone group when no override or group budget applies.

Since every user in an organization is implicitly a member of that org's Everyone group, spend is now attributed and tracked for any user with organization membership. A user with no organization membership resolves to no group, so their daily spend is not incremented and a warning is logged.

The fallback is unlimited, so enforcement is unaffected: only override and group budgets can block requests. For users in multiple organizations, an existing budget on any Everyone group is still chosen by the "highest" policy; when none is budgeted, the fallback prefers the default org, then orders by organization name.

## Changes

- Add `ResolveUserEffectiveGroup` and the `GetUserEveryoneFallbackGroup` query: resolve override → group budget → Everyone group fallback.
- Attribute token-usage spend and the user AI spend endpoint via the fallback, so unbudgeted users resolve to their Everyone group instead of null.
- Update `GetGroupMembersAISpend` to surface the Everyone fallback as the effective group.
- Update `GetHighestGroupAIBudgetByUser` to break ties by organization name then group name, keeping multi-org resolution deterministic and consistent with the fallback.
- For multi-org users with no budget anywhere, the fallback picks the Everyone group deterministically: prefer the default org, then order by organization name.

Closes https://linear.app/codercom/issue/AIGOV-509/fall-back-to-the-everyone-group-for-spend-attribution

> [!NOTE]
> Initially generated by Claude Opus 4.7, modified and reviewed by @ssncferreira
2026-07-23 09:26:25 +01:00
Susana Ferreira a9fdf87a2f feat: add GET /groups/{group}/members/ai/spend (#27130)
## Description

Adds `GET /api/v2/groups/{group}/members/ai/spend?user_ids=...` (also available org-scoped at `/api/v2/organizations/{org}/groups/{groupName}/members/ai/spend`) to return per-member AI spend attributed to a group, along with each member's effective budget group and the applied spend limit when the queried group is their effective budget source.

In the UI, this endpoint is used alongside the existing `/api/v2/groups/{group}/members` endpoint. AI spend data is kept separate from that endpoint so that:

- Different concepts stay on different endpoints: identity (group members) vs. cost control (spend). Cost control is an additional feature layered on top of groups/orgs.
- Callers that don't need spend information don't pay for its computation.

UI flow:

1. Request `/api/v2/groups/{group}/members` → returns the group's members.
2. Request `/api/v2/groups/{group}/members/ai/spend?user_ids=...` with the IDs from step 1.

**Note:** Only current members of the queried group are returned. `spend_limit_micros` and `limit_source` are populated only when the queried group is the member's effective budget source (its own limit or a user override). `effective_group_id` is null when the member's budget resolves to a group in another organization, since an organization is treated as a tenant boundary.

<img width="2880" height="1904" alt="image" src="https://github.com/user-attachments/assets/33ed395d-d1a3-4b46-bb04-c8d3f41c8886" />

## Changes

- Add `codersdk.GroupMembersAISpend` and `GroupMemberAISpend` types, reusing the shared `AISpendPeriodWindow`.
- Add `GetGroupMembersAISpend` SQL query with a dbauthz per-row filter that mirrors `GET /api/v2/groups/{group}/members`.
- Add handler and routes under `/groups/{group}/members/ai/spend` (and the org-scoped alias) with a required `user_ids` query param (cap 100). Callers with more than 100 members are expected to batch across multiple requests.
- Add codersdk client method.
- Tests: dbauthz, raw SQL, endpoint, and role-access.

Closes https://linear.app/codercom/issue/AIGOV-471/backend-group-members-endpoint-with-members-spend

> [!NOTE]
> Initially generated by Claude Opus 4.7, modified and reviewed by @ssncferreira
2026-07-20 13:04:37 +01:00
Susana Ferreira 2adc8f5272 feat: add GET /organizations/{org}/groups/ai/spend (#27123)
## Description

Adds `GET /api/v2/organizations/{org}/groups/ai/spend?group_ids=...` to return per-group AI spend and configured limits for a set of groups in an organization.

In the UI, this endpoint is used alongside the existing `/api/v2/organizations/{org}/groups` endpoint. AI spend data is kept separate from that endpoint so that:

- Different concepts stay on different endpoints: identity (groups) vs. cost control (spend). Cost control is an additional feature layered on top of groups/orgs.
- Callers that don't need spend information don't pay for its computation.

UI flow:

1. Request `/api/v2/organizations/{org}/groups` → returns the organization's groups.
2. Request `/api/v2/organizations/{org}/groups/ai/spend?group_ids=...` with the IDs from step 1.

The groups endpoint from 1) is currently not paginated, but if pagination is added later, this design keeps the two responses in sync. This spend endpoint intentionally takes `group_ids` rather than paginating on its own, since it depends on the group set from step 1. Pagination could be added in the future, especially for Cost Control-focused pages.

<img width="2880" height="1460" alt="image" src="https://github.com/user-attachments/assets/ea83b74d-6a4f-45a6-af2f-1024e019da07" />

## Changes

- Add `codersdk.OrganizationGroupsAISpend` and `OrganizationGroupAISpend` types, plus a shared `AISpendPeriodWindow` embedded in the spend response.
- Add `GetOrganizationGroupsAISpend` SQL query with a dbauthz per-row filter that mirrors `GET /organizations/{org}/groups`.
- Add handler and route under `/organizations/{organization}/groups/ai/spend` with a required `group_ids` query param (cap 100). Callers with more than 100 groups are expected to batch across multiple requests.
- Add codersdk client method.
- Tests: dbauthz, raw SQL, endpoint, and role-access.

Closes https://linear.app/codercom/issue/AIGOV-466/backend-organization-groups-endpoint-with-groups-spend

> [!NOTE]
> Initially generated by Claude Opus 4.7, modified and reviewed by @ssncferreira
2026-07-20 12:54:52 +01:00
Susana Ferreira fcdd029d74 feat: add ai_user_daily_spend table and queries (#26562)
## Description

Adds the spend tracking table and queries needed by [AIGOV-427](https://linear.app/codercom/issue/AIGOV-427/add-post-response-spend-accumulation) (post-response accumulation) and [AIGOV-428](https://linear.app/codercom/issue/AIGOV-428/add-pre-request-budget-enforcement) (pre-request enforcement).

## Changes

- Add `ai_user_daily_spend` table to aggregate per-user, per-effective-group AI spend by UTC day.
- Add `UpsertUserAIDailySpend` and `GetUserAISpendSince` queries.

Closes https://linear.app/codercom/issue/AIGOV-426/add-daily-spend-table-and-queries

> [!NOTE]
> Initially generated by Claude Opus 4.7, modified and reviewed by @ssncferreira
2026-07-02 16:29:25 +01:00
Yevhenii Shcherbina 1cada0649c feat(coderd): resolve effective user AI budget (#26142)
Closes
https://linear.app/codercom/issue/AIGOV-287/add-effective-group-resolution

Implements the effective AI budget resolution from the AI Governance
cost-controls RFC: for a given user, a `user_ai_budget_overrides` row
wins if present, otherwise the deployment budget policy (`highest`)
picks the largest group budget across the user's groups, ties broken
alphabetically.

For now, I keep the logic under `coderd/aibridge/budget`, but that may
change during the implementation of budget enforcement.
2026-06-10 14:01:20 +00:00
Yevhenii Shcherbina 1a91d31793 feat: add user AI budget override endpoints (#25439)
Implements https://linear.app/codercom/issue/AIGOV-285
Follow the structure established in
https://github.com/coder/coder/pull/25203

## Summary

Adds the `user_ai_budget_overrides` table and CRUD API at
`/api/v2/users/{user}/ai/budget`. An override sets a custom per-user
spend cap that supersedes group-budget resolution, attributing spend to
a specific group.

## Schema

```sql
CREATE TABLE user_ai_budget_overrides (
    user_id            UUID        PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    group_id           UUID        NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    spend_limit_micros BIGINT      NOT NULL CHECK (spend_limit_micros >= 0),
    created_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at         TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
```

## Membership lifecycle

The membership invariant — a user must be a member of the attributed
group, including when that group is "Everyone" — would naturally be
expressed as a composite FK on `(user_id, group_id) →
group_members_expanded(user_id, group_id)`. PostgreSQL doesn't allow
foreign keys to reference views, so enforcement is split across two
mechanisms:

- **Write-time check.** A CHECK constraint on the table
(`user_ai_budget_overrides_must_be_group_member`) calls a `STABLE`
function `is_group_member(user_id, group_id)` that queries
`group_members_expanded`. The view surfaces both regular group
memberships and the implicit "Everyone" group memberships from
`organization_members`. Any INSERT or UPDATE that violates the predicate
is rejected with a Postgres `check_violation`, which the handler maps to
a 400. `is_group_member` is defined as a general predicate, reusable by
any future table that needs the same check.

- **Cascade on removal.** Two `BEFORE DELETE` triggers handle membership
loss:
- `trigger_delete_user_ai_budget_overrides_on_group_member_delete` on
`group_members` — covers regular group removals (admin action, OIDC
sync).
- `trigger_delete_user_ai_budget_overrides_on_org_member_delete` on
`organization_members` — covers the "Everyone" group, whose membership
lives in `organization_members`.

The single-column FKs on `users(id)` and `groups(id)` remain to cascade
on user or group deletion (those paths don't pass through
`group_members`).

## Authorization

The dbauthz layer gates each operation against the `User` and (for
writes) `Group` resources:

| Operation | User resource  | Group resource |
|-----------|----------------|----------------|
| `GET`     | `ActionRead`   | —              |
| `PUT`     | `ActionUpdate` | `ActionUpdate` |
| `DELETE`  | `ActionUpdate` | `ActionUpdate` |

For `DELETE`, the dbauthz layer fetches the existing override first to
learn the attributed `group_id`, then runs both checks.

### Role matrix

| Role         | GET | PUT | DELETE |
|--------------|-----|-----|--------|
| Owner        | ✅   | ✅   | ✅      |
| UserAdmin    | ✅   | ✅   | ✅      |
| OrgAdmin     | ✅   | ❌   | ❌      |
| OrgUserAdmin | ✅   | ❌   | ❌      |

Internal discussion:
https://codercom.slack.com/archives/C096PFVBZKN/p1779392747885359

## Audit logs
Audit logs will be addressed in a follow-up PR.
2026-05-29 10:08:25 -04:00
Yevhenii Shcherbina 238968cfa0 feat: add per-group AI budget table and endpoints (#25203)
Closes
https://linear.app/codercom/issue/AIGOV-284/add-group-budgets-table-and-crud-api

## Summary

Adds the `group_ai_budgets` table and the following endpoints:

- `GET /api/v2/groups/{group}/ai/budget`
- `PUT /api/v2/groups/{group}/ai/budget`
- `DELETE /api/v2/groups/{group}/ai/budget`

Each group may have at most one budget row. If no row exists, no budget
is enforced.

### Feature gate
  
Added `RequireFeatureMW(FeatureAIBridge)` on the `/ai/budget` sub-route.

## RBAC

Authorization reuses `rbac.ResourceGroup` with the existing
`.InOrganization(...).WithID(...)` scoping model.

The `dbauthz` wrappers load the parent `groups` row and authorize
against it.

No new resource type is introduced. As a result, anyone with
`group:update` permissions (Owner, OrgAdmin, or UserAdmin within the
organization) can manage AI budgets for that group.

## Read access for group members

`database.Group.RBACObject()` grants `policy.ActionRead` to all members
of the group through the group ACL:

```go
func (g Group) RBACObject() rbac.Object {
	return rbac.ResourceGroup.WithID(g.ID).
		InOrg(g.OrganizationID).
		// Group members can read the group.
		WithGroupACL(map[string][]policy.Action{
			g.ID.String(): {
				policy.ActionRead,
			},
		})
}
```

Because the `GET` endpoint authorizes against the same loaded `Group`
object, any group member can call:

```text
GET /api/v2/groups/{group}/ai/budget
```

`PUT` and `DELETE` remain admin-only. The group ACL grants only
`ActionRead`, so write operations continue to require role-based
`group:update` permissions.

## Alternative considered

A dedicated `rbac.ResourceGroupAiBudget` resource would allow budget
management to be separated from general group administration.

We decided not to add that complexity for now.
2026-05-14 15:54:37 -04:00
Yevhenii Shcherbina 4124d1137d feat: add ai_model_prices table (#24932)
# Summary

Implements
https://linear.app/codercom/issue/AIGOV-282/add-ai-model-price-table-and-seed-generator

This PR lays the groundwork for AI Bridge cost controls (per the AI
Governance RFC). It adds the foundation needed for future cost tracking:
a place to store per-model token prices, a way to keep those prices in
sync with upstream pricing data, and a startup mechanism that ensures
every deployment has prices loaded before AI Bridge starts processing
requests.

The price data comes from [models.dev](https://models.dev/), a
community-maintained catalogue of AI provider pricing. A generator
script fetches the latest prices, filters to Anthropic and OpenAI for
now, and produces a seed file checked into the repository.

On every server startup the seed is applied to the database, so new
releases automatically pick up any price corrections that landed since
the previous one. Existing rows are overwritten with the latest prices;
rows for models no longer in the seed are left untouched.

# Batching the AI model price seed: three approaches

Context: at server startup we seed the `ai_model_prices` table from an
embedded JSON price book (~70 rows today, will grow as we add providers,
potentially 4000+).

Each row is:

```text
(provider, model, input_price, output_price, cache_read_price, cache_write_price)
```

Any of the four price columns can be:

- `NULL` → “price unknown for this dimension”
- explicit `0` → “free”

The batch must be an UPSERT so re-running is idempotent and existing
rows pick up new prices.

We considered three implementations.

---

## Approach 1 — Per-row UPSERT in a Go loop

```go
for _, row := range rows {
    if err := db.UpsertAIModelPrice(ctx, database.UpsertAIModelPriceParams{
        Provider:   row.Provider,
        Model:      row.Model,
        InputPrice: nullInt64(row.InputPrice),
        // ...
    }); err != nil {
        return err
    }
}
```

### Pros

- Trivial.
- NULL handling falls out naturally from `sql.NullInt64`.

### Cons

- `N` round-trips per seed.
- With ~70 rows that means ~70 statement executions on every startup,
even inside a transaction.
- Doesn't scale gracefully as the price book grows, potentially 4000+.

---

## Approach 2 — `UNNEST` with parallel arrays

Pass each column as a separate Go slice. Postgres unnests them in
parallel into a virtual table, then `INSERT ... SELECT`.

```sql
INSERT INTO ai_model_prices (
    provider,
    model,
    input_price,
    output_price,
    cache_read_price,
    cache_write_price
)
SELECT
    UNNEST(@providers::text[]),
    UNNEST(@models::text[]),
    NULLIF(UNNEST(@input_prices::bigint[]), -1),
    NULLIF(UNNEST(@output_prices::bigint[]), -1),
    NULLIF(UNNEST(@cache_read_prices::bigint[]), -1),
    NULLIF(UNNEST(@cache_write_prices::bigint[]), -1)
ON CONFLICT (provider, model) 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();
```

Go side: flatten rows into six parallel slices.

Use a sentinel (`-1`) for “missing”, since `lib/pq` can't encode `NULL`
into a `bigint[]` element.

```go
providers := make([]string, len(rows))
models    := make([]string, len(rows))
inputs    := make([]int64,  len(rows))
outputs   := make([]int64,  len(rows))
cacheR    := make([]int64,  len(rows))
cacheW    := make([]int64,  len(rows))

for i, r := range rows {
    providers[i] = r.Provider
    models[i]    = r.Model

    inputs[i] = -1
    if r.InputPrice != nil {
        inputs[i] = *r.InputPrice
    }

    outputs[i] = -1
    if r.OutputPrice != nil {
        outputs[i] = *r.OutputPrice
    }

    cacheR[i] = -1
    if r.CacheReadPrice != nil {
        cacheR[i] = *r.CacheReadPrice
    }

    cacheW[i] = -1
    if r.CacheWritePrice != nil {
        cacheW[i] = *r.CacheWritePrice
    }
}

return db.UpsertAIModelPrices(ctx, database.UpsertAIModelPricesParams{
    Providers:        providers,
    Models:           models,
    InputPrices:      inputs,
    OutputPrices:     outputs,
    CacheReadPrices:  cacheR,
    CacheWritePrices: cacheW,
})
```

### Pros

- Single round-trip.

### Cons

- The generated `sqlc` params become plain `[]int64`, which can't
represent `NULL`.

---

## Approach 3 — `jsonb_array_elements` over a single `@seed::jsonb`
(chosen)

Pass the raw seed JSON as one parameter; let Postgres expand and parse
it.

```sql
INSERT INTO ai_model_prices (
    provider,
    model,
    input_price,
    output_price,
    cache_read_price,
    cache_write_price
)
SELECT
    elem->>'provider',
    elem->>'model',
    (elem->>'input_price')::bigint,
    (elem->>'output_price')::bigint,
    (elem->>'cache_read_price')::bigint,
    (elem->>'cache_write_price')::bigint
FROM jsonb_array_elements(@seed::jsonb) AS elem
ON CONFLICT (provider, model) 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();
```

Go side reduces to:

```go
return db.UpsertAIModelPrices(ctx, seedJSON)
```

### Pros

- Single round-trip.
- NULLs fall out naturally:
  - `(elem->>'cache_write_price')::bigint` becomes `NULL`
  - no sentinels
- The seed is already JSON:
- Existing precedent:
  - `jsonb_array_elements` is already used elsewhere in the codebase

### Cons

- Less type-safe at the SQL boundary than `UNNEST`
- Slightly less standard than `UNNEST`
- Readers need familiarity with:
  - `jsonb_array_elements`
  - `->>` extraction syntax
- Postgres pays JSON parse cost
  - negligible at our scale

---

---

# Decision

We picked Approach 3.

It collapses the round-trips like `UNNEST` does, but without:

- nullable-array workarounds
- sentinel values
2026-05-08 16:45:14 -04:00