feat: add ai_model_prices table by evgeniy-scherbina · Pull Request #24932 · coder/coder

evgeniy-scherbina · 2026-05-04T16:22:40Z

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, 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:

(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

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.

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.

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.

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:

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

evgeniy-scherbina · 2026-05-05T20:26:23Z

+	// embedded JSON is corrupted, which is a release-time bug worth crashing
+	// on rather than silently disabling cost tracking.
+	//nolint:gocritic // No user request context here, so use a system-restricted context.
+	if err := prices.Load(dbauthz.AsSystemRestricted(ctx), options.Database); err != nil {


Prices will be loaded in both enterprise and AGPL, I think it's fine?

According to internal discussion, it is:
https://codercom.slack.com/archives/C096PFVBZKN/p1778201099153079

…-price-table

Emyrk

Idk how much of my review made it through this github downtime

Emyrk · 2026-05-06T16:51:46Z

Github is having issues. Can the primary key just be (provider,model)? instead of an arbitrary ID?

coder-agents-review

Good foundation for AI Bridge cost controls. The table design, RBAC wiring, and startup integration are well-structured. The test suite is genuinely strong: 5 subtests against real Postgres covering fresh seed, idempotency, overwrite (including NULL replacing non-NULL), orphan preservation, and the full dbauthz auth chain. The generator's missing-provider error path and the self-correction from silent skip to hard error (c825ce7) show good defensive thinking.

4 P2, 7 P3, 2 P4, 3 Nit. The P2s cluster around two themes: validation gaps that let bad data through silently (empty seed, upstream schema drift), and a misleading migration comment that contradicts the seeder's actual null semantics.

Melody walked 20 wire-format pairings end-to-end and found zero mismatches across JSON tags, SQLC params, RBAC actions, TypeScript generated types, and constraint enumerations. That is clean plumbing.

"A function that does nothing also does not delete the orphan." (Bisky, on the LeavesOrphanRowsUntouched test)

Process note: the commit history shows four separate "ci: fix linter" round-trips (emdash, xerrors, nolint, and missing ALTER TYPE). Each would have been caught by make lint or make gen locally. The project rules are explicit about running hooks before pushing. The code is correct now, but the CI cost was avoidable.

🤖 This review was automatically generated with Coder Agents.

coder-agents-review · 2026-05-06T20:04:16Z

+		return nil, xerrors.Errorf("status %d", resp.StatusCode)
+	}
+
+	body, err := io.ReadAll(resp.Body)


P4 [DEREM-12] io.ReadAll(resp.Body) reads the entire upstream response into memory with no size limit. A compromised or misbehaving models.dev could serve an arbitrarily large payload. This is a developer-only build tool, not runtime code, so the blast radius is a developer machine or CI runner. io.LimitReader(resp.Body, 10<<20) is trivial and prevents unbounded reads.

(Kurapika P3, Kite P4)

🤖

Fixed here: e184309

100MiB limit, current file is ~2MiB

coder-agents-review · 2026-05-06T20:04:16Z

 					},
 					rbac.ResourceApiKey.Type:               {policy.ActionRead}, // Validate API keys.
 					rbac.ResourceAibridgeInterception.Type: {policy.ActionCreate, policy.ActionRead, policy.ActionUpdate, policy.ActionDelete},
+					rbac.ResourceAiModelPrice.Type:         {policy.ActionUpdate}, // Required for the startup price seeder.


Note [DEREM-22] subjectAibridged grants ActionUpdate but not ActionRead for ResourceAiModelPrice. Correct for the startup seeder, which only writes. But future cost-computation code will need to read prices, likely under the same AsAIBridged context. The next PR adding cost tracking will need to add ActionRead here.

(Hisoka, Mafuuu)

🤖

I think future reads may happen in user context, not in AsAIBridged context.

ssncferreira

Overall LGTM, but I think we should address some concerns, mainly the individual INSERT ... ON CONFLICT statements in a transaction.

nit: Maybe we could have split this into 2 PRs to facilitate the review process, one for the script and another one for the coder/aibridge related changes

ssncferreira · 2026-05-07T11:53:56Z

+		// Spot-check a row where the seed has a NULL price (OpenAI does not
+		// publish a cache_write_price). The column should land as SQL NULL.
+		gpt, err := db.GetAIModelPriceByProviderModel(ctx, database.GetAIModelPriceByProviderModelParams{


I think this is an important check and should have its own test and not be coupled to a fresh database test.

SeedsFreshDatabase subtest covers two important cases:

model with all prices set (e.g. Anthropic)

model with some prices are null (e.g. OpenAI)

Do you want separate subtest for null case?

ssncferreira · 2026-05-07T12:13:05Z

 	},
+	"ai_model_price": {
+		Actions: map[Action]ActionDefinition{
+			ActionRead:   "read AI model prices",


Shouldn't we also add this action to the aibridge daemon? 🤔

The aibridge daemon doesn’t need read access for now since it only seeds the initial prices and doesn’t read them. It’s unclear whether it will need this in the future - read access might instead happen in the context of user/admin queries?

GetAIModelPriceByProviderModel (read method) is currently only used in tests.

But I can still add it if you think it make sense—I don’t think it hurts, especially since we’ve already granted write permissions to the aibridge daemon.

ssncferreira · 2026-05-07T12:38:37Z

+	if err := write(os.Stdout, rows); err != nil {
+		return err
+	}
+	_, _ = fmt.Fprintf(os.Stderr, "aibridgepricesgen: wrote %d prices for %d provider(s)\n", len(rows), len(supportedProviders))


nit: consider using log.Printf for timestamp and consistent prefix

I think timestamps aren't useful for a one-shot build tool. aibridgepricesgen runs in seconds, prints two lines, and exits.

Also I see that _, _ = fmt.Fprintf is heavily used in scripts folder.

ssncferreira · 2026-05-07T12:42:44Z

+    "provider": "openai",
+    "model": "chatgpt-image-latest",
+    "input_price": null,
+    "output_price": null,
+    "cache_read_price": null,
+    "cache_write_price": null


I'm wondering if we should have an extra layer of filtering, because this for instance, is an image model I believe. Maybe a good filter will be to discard models where all prices are null, wdyt? 🤔

It's already done as part of other comment. But it only discarded 4 out of 75 models.

pawbana · 2026-05-08T10:58:40Z

+
+`prices.json` in this directory is generated by `make gen/aibridge-prices` and
+embedded into the Coder binary at build time. Do not edit it manually; the
+next regeneration will overwrite any changes.


Will there be a way for operator to update / overwrite the prices manually? If so will manual override have priority over default (and won't be over written)?

Will there be a way for operator to update / overwrite the prices manually?

yes, but in Phase 2, IIRC it's out of scope for current RFC

If so will manual override have priority over default

yes

That said, I haven’t thought much about the implementation details yet. One approach could be to track whether the admin manually updated the prices, and if so, ignore prices.Seed() afterward?

pawbana · 2026-05-08T11:13:15Z

+type seedRow struct {
+	Provider        string `json:"provider"`
+	Model           string `json:"model"`
+	InputPrice      *int64 `json:"input_price"`
+	OutputPrice     *int64 `json:"output_price"`
+	CacheReadPrice  *int64 `json:"cache_read_price"`
+	CacheWritePrice *int64 `json:"cache_write_price"`
+}


I'm a avid supporter of single source of truth. Comments never work, linter rule (if modified then modify other place) would be ok as a backup. IMO some pricerow package in scripts directory that exports priceRow struct would be worth it.

pawbana · 2026-05-08T11:19:52Z

+		require.NoError(t, err)
+		require.Equal(t, int64(2_500_000), gpt.InputPrice.Int64)
+		require.Equal(t, int64(10_000_000), gpt.OutputPrice.Int64)
+		require.Equal(t, int64(1_250_000), gpt.CacheReadPrice.Int64)


require.Empty/Zero for missing prices?

Do you mean check require.Zero(t, got.CacheWritePrice.Int64) on top of require.False(t, got.CacheWritePrice.Valid)? If so - I did it.

pawbana · 2026-05-08T11:30:58Z

+    output_price      BIGINT      CHECK (output_price >= 0),
+    cache_read_price  BIGINT      CHECK (cache_read_price >= 0),
+    cache_write_price BIGINT      CHECK (cache_write_price >= 0),
+    created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),


is creation time needed?

It was in RFC, so I kept it. It shows when for 1st time specific provider/model pair was registered, maybe useful, considering upstream data may change over time.

pawbana · 2026-05-08T11:35:43Z

+	(elem->>'output_price')::bigint,
+	(elem->>'cache_read_price')::bigint,
+	(elem->>'cache_write_price')::bigint
+FROM jsonb_array_elements(@seed::jsonb) AS elem


Why ::jsonb type is used? Maybe it is me but wouldn't normal fields be clearer / easier? Seeder already unmarshals json?

Discussed offline. ::jsonb seems reasonable middle ground between performance and convenience (due to lacking batching support in postgresql driver).

It's a big discussion, I updated PR description, see Batching the AI model price seed: three approaches section

pawbana · 2026-05-08T11:38:56Z

@@ -0,0 +1,209 @@
+// aibridgepricesgen fetches model pricing from models.dev and writes a JSON
+// seed file consumable by the AI Bridge cost-control loader. Output is sorted


AI Bridge -> AI Gateway

Do you want to rename everything:

package name

make target

etc.
?

pawbana · 2026-05-08T11:48:45Z

 	return q.db.UpdateWorkspacesTTLByTemplateID(ctx, arg)
 }

+func (q *querier) UpsertAIModelPrices(ctx context.Context, seed json.RawMessage) error {


Mentioned already in coderd/database/queries/aimodelprices.sql but why seed is json.RawMessage type? I feel it is much more error prone then using eg. []priceRow type.

same see Batching the AI model price seed: three approaches section

pawbana · 2026-05-08T11:52:37Z

+	if len(rows) == 0 {
+		return xerrors.New("price seed is empty")
+	}
+	return db.UpsertAIModelPrices(ctx, data)


maybe db.UpsertAIModelPrices(ctx, data) -> db.UpsertAIModelPrices(ctx, rows) (https://github.com/coder/coder/pull/24932/changes#r3208437056)

same see Batching the AI model price seed: three approaches section

…rimary key

…control.sql

…-price-table

github-actions Bot assigned evgeniy-scherbina May 4, 2026

evgeniy-scherbina commented May 4, 2026

View reviewed changes

Comment thread coderd/database/migrations/000489_ai_model_prices.up.sql

feat(coderd/database): add ai_model_prices table

f272062

evgeniy-scherbina force-pushed the yevhenii/cost-control-price-table branch from 4c529cb to f272062 Compare May 4, 2026 17:59

evgeniy-scherbina commented May 4, 2026

View reviewed changes

Comment thread scripts/aibridgepricesgen/main.go Outdated

evgeniy-scherbina commented May 5, 2026

View reviewed changes

Comment thread scripts/aibridgepricesgen/main.go

evgeniy-scherbina force-pushed the yevhenii/cost-control-price-table branch 5 times, most recently from b70d9b2 to 2e8db6b Compare May 5, 2026 16:37

feat: generate AI Bridge price seed from models.dev

b9fc9b7

evgeniy-scherbina force-pushed the yevhenii/cost-control-price-table branch 2 times, most recently from e041202 to c778bc9 Compare May 5, 2026 19:49

feat(coderd): load AI Bridge price seed at startup

d169366

evgeniy-scherbina force-pushed the yevhenii/cost-control-price-table branch from c778bc9 to d169366 Compare May 5, 2026 20:00

evgeniy-scherbina commented May 5, 2026

View reviewed changes

evgeniy-scherbina force-pushed the yevhenii/cost-control-price-table branch from 78b3169 to 01143f2 Compare May 6, 2026 12:59

evgeniy-scherbina commented May 6, 2026

View reviewed changes

Comment thread coderd/database/dbauthz/dbauthz.go

feat: gate ai_model_price queries with RBAC

3d8ef2a

evgeniy-scherbina force-pushed the yevhenii/cost-control-price-table branch from 01143f2 to 3d8ef2a Compare May 6, 2026 14:08

evgeniy-scherbina marked this pull request as ready for review May 6, 2026 14:11

evgeniy-scherbina requested a review from Emyrk as a code owner May 6, 2026 14:11

evgeniy-scherbina added 2 commits May 6, 2026 14:52

Merge remote-tracking branch 'origin/main' into yevhenii/cost-control…

d2f352f

…-price-table

fix: migration numbers

d1963ec

evgeniy-scherbina changed the title ~~feat(coderd/database): add ai_model_prices table~~ feat: add ai_model_prices table May 6, 2026

evgeniy-scherbina added 2 commits May 6, 2026 15:59

test: fix tests

358cef5

ci: fix linter

554a37e

Emyrk reviewed May 6, 2026

View reviewed changes

Comment thread coderd/database/dbauthz/dbauthz.go

evgeniy-scherbina commented May 6, 2026

View reviewed changes

Comment thread coderd/database/migrations/000489_ai_model_prices.up.sql

coder-agents-review Bot suggested changes May 6, 2026

View reviewed changes

evgeniy-scherbina requested a review from ssncferreira May 6, 2026 20:45

evgeniy-scherbina added 2 commits May 6, 2026 21:08

fix: CR's fixes

e17749c

refactor(coderd/aibridge/prices): inject synthetic seed in loader tests

6c97d23

ssncferreira reviewed May 7, 2026

View reviewed changes

evgeniy-scherbina added 12 commits May 7, 2026 14:54

fix(scripts/aibridgepricesgen): fail when no row has pricing

ebdcdf3

fix(coderd): log price-seed failures instead of fatal

3d64627

fix: drop unpriced models from AI Bridge price seed

32e645a

fix: drop unpriced models from AI Bridge price seed

3ba49da

refactor(coderd): rename prices.Load to prices.Seed

9676d01

docs: add minor comment

9ca9f04

docs: note JSON-tag coupling between price seeder and generator

474c555

fix(scripts/aibridgepricesgen): fail on empty provider models

c263ebb

perf(coderd): batch ai_model_prices upsert into single query

532dd44

docs: note SQL upsert in price seed JSON contract

65e1d03

docs: add README for generated AI Bridge price seed

4a994b2

fix: minor fix

5f008df

pawbana reviewed May 8, 2026

View reviewed changes

test: minor improvement

6298770

evgeniy-scherbina requested a review from dannykopping May 8, 2026 18:23

evgeniy-scherbina added 2 commits May 8, 2026 19:16

refactor(coderd/database): use (provider, model) as ai_model_prices p…

ef89e79

…rimary key

refactor(coderd/database/queries): rename aimodelprices.sql to aicost…

cc85ac0

…control.sql

Emyrk approved these changes May 8, 2026

View reviewed changes

evgeniy-scherbina added 2 commits May 8, 2026 20:17

refactor(scripts/aibridgepricesgen): cap upstream body read at 100 MiB

e184309

Merge remote-tracking branch 'origin/main' into yevhenii/cost-control…

2594553

…-price-table

evgeniy-scherbina merged commit 4124d11 into main May 8, 2026
48 of 52 checks passed

evgeniy-scherbina deleted the yevhenii/cost-control-price-table branch May 8, 2026 20:45

github-actions Bot locked and limited conversation to collaborators May 8, 2026

		@@ -0,0 +1,209 @@
		// aibridgepricesgen fetches model pricing from models.dev and writes a JSON
		// seed file consumable by the AI Bridge cost-control loader. Output is sorted

Uh oh!

Conversation

evgeniy-scherbina commented May 4, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Summary

Batching the AI model price seed: three approaches

Approach 1 — Per-row UPSERT in a Go loop

Pros

Cons

Approach 2 — UNNEST with parallel arrays

Pros

Cons

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

Pros

Cons

Decision

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Emyrk left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Emyrk commented May 6, 2026

Uh oh!

Uh oh!

coder-agents-review Bot left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

coder-agents-review Bot May 6, 2026

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

coder-agents-review Bot May 6, 2026

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

ssncferreira left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

evgeniy-scherbina May 8, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

evgeniy-scherbina commented May 4, 2026 •

edited

Loading

Approach 2 — `UNNEST` with parallel arrays

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

evgeniy-scherbina May 8, 2026 •

edited

Loading

evgeniy-scherbina May 8, 2026 •

edited

Loading

evgeniy-scherbina May 8, 2026 •

edited

Loading

evgeniy-scherbina May 8, 2026 •

edited

Loading