LLM Cost Attribution Per Customer: Postgres Pattern for Margin Math

There are three pieces. A llm_usage_events table that captures one row per LLM call with the fields a rollup needs. A llm_model_rates table that holds per-model pricing with effective dates. A nightly rollup job that joins the two and writes a per-customer-per-day cost row. The pieces are small on their own — most of the value is in the discipline of writing a row for every call and stamping the rate at call time.

Per-call usage events table

Start with the usage table. The schema is intentionally narrow: it captures the dimensions a rollup needs and nothing else. PII does not belong here — the customer_id is enough, and joining out to your customer table at query time is fine.

CREATE TABLE llm_usage_events (
  id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id       text NOT NULL,
  -- Free-form scope: 'chat', 'agent.summarize', 'rag.answer', etc.
  -- Lets you attribute cost not just to a customer but to a feature.
  feature           text NOT NULL,
  provider          text NOT NULL,            -- 'openai' | 'anthropic'
  model             text NOT NULL,            -- exact API model id, e.g. 'gpt-5.4'
  input_tokens      integer NOT NULL DEFAULT 0,
  cached_tokens     integer NOT NULL DEFAULT 0,   -- read from cache
  cache_write_tokens integer NOT NULL DEFAULT 0,  -- Anthropic explicit cache write
  output_tokens     integer NOT NULL DEFAULT 0,
  -- Stamped at call time so historical rows survive provider price changes.
  rate_input_usd_per_mtok        numeric(10, 4) NOT NULL,
  rate_cached_input_usd_per_mtok numeric(10, 4) NOT NULL,
  rate_cache_write_usd_per_mtok  numeric(10, 4) NOT NULL DEFAULT 0,
  rate_output_usd_per_mtok       numeric(10, 4) NOT NULL,
  -- Cost computed at call time (cents to avoid float rounding drift).
  cost_micro_usd    bigint NOT NULL,
  request_id        text,                     -- provider-assigned id when available
  occurred_at       timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX llm_usage_events_customer_day
  ON llm_usage_events (customer_id, date_trunc('day', occurred_at));

CREATE INDEX llm_usage_events_day_model
  ON llm_usage_events (date_trunc('day', occurred_at), provider, model);

Rates table with effective-date ranges

The rates table is small enough to keep in version control, but storing it in Postgres lets you change rates without a deploy. The effective-date range is what makes mid-month rate changes safe — you close out the current row and insert a new one, and historical rows already carry the old rate stamped at call time.

CREATE TABLE llm_model_rates (
  provider                       text NOT NULL,
  model                          text NOT NULL,
  input_usd_per_mtok             numeric(10, 4) NOT NULL,
  cached_input_usd_per_mtok      numeric(10, 4) NOT NULL,
  cache_write_5m_usd_per_mtok    numeric(10, 4) NOT NULL DEFAULT 0,
  cache_write_1h_usd_per_mtok    numeric(10, 4) NOT NULL DEFAULT 0,
  output_usd_per_mtok            numeric(10, 4) NOT NULL,
  effective_from                 timestamptz NOT NULL,
  effective_to                   timestamptz,
  PRIMARY KEY (provider, model, effective_from)
);

-- The current-rate lookup the call-time wrapper uses.
CREATE OR REPLACE VIEW llm_model_rates_current AS
SELECT *
FROM llm_model_rates
WHERE effective_to IS NULL;

Wrapper as the only path to the provider SDKs

The TypeScript wrapper is where attribution becomes a discipline instead of a hope. The wrapper is the only path to the provider SDKs in the application. Direct imports of openai or @anthropic-ai/sdk outside the wrapper are caught in code review. The wrapper requires a customer_id and a feature name on every call, looks up the current rate, runs the request, and writes the usage row before returning.

type LlmCallArgs = {
  customer_id: string;
  feature: string;          // e.g. 'agent.summarize'
  model: string;            // exact model id, e.g. 'claude-opus-4-7'
  messages: AnthropicMessageParam[];
  cache_breakpoint?: boolean;
};

export async function callAnthropic(args: LlmCallArgs): Promise<AnthropicMessage> {
  const rate = await rates.getCurrent("anthropic", args.model);
  if (!rate) throw new Error(`No rate row for anthropic/${args.model}`);

  const response = await anthropic.messages.create({
    model: args.model,
    max_tokens: 4096,
    messages: args.messages,
    metadata: {
      // Opaque per-customer id for provider-side attribution.
      // Hashing keeps PII out, per the Messages API guidance.
      user_id: hmacCustomerId(args.customer_id),
    },
  });

  const usage = response.usage;
  const inputTokens       = usage.input_tokens ?? 0;
  const cachedTokens      = usage.cache_read_input_tokens ?? 0;
  const cacheWriteTokens  = usage.cache_creation_input_tokens ?? 0;
  const outputTokens      = usage.output_tokens ?? 0;

  // Cost is computed in micro-dollars (1e6 micro = 1 USD) to keep
  // integer math through the rollup and avoid float rounding drift.
  const costMicroUsd =
    Math.round((inputTokens       / 1_000_000) * rate.input_usd_per_mtok          * 1_000_000) +
    Math.round((cachedTokens      / 1_000_000) * rate.cached_input_usd_per_mtok   * 1_000_000) +
    Math.round((cacheWriteTokens  / 1_000_000) * rate.cache_write_5m_usd_per_mtok * 1_000_000) +
    Math.round((outputTokens      / 1_000_000) * rate.output_usd_per_mtok         * 1_000_000);

  await db.llmUsageEvents.insert({
    customer_id: args.customer_id,
    feature: args.feature,
    provider: "anthropic",
    model: args.model,
    input_tokens: inputTokens,
    cached_tokens: cachedTokens,
    cache_write_tokens: cacheWriteTokens,
    output_tokens: outputTokens,
    rate_input_usd_per_mtok:        rate.input_usd_per_mtok,
    rate_cached_input_usd_per_mtok: rate.cached_input_usd_per_mtok,
    rate_cache_write_usd_per_mtok:  rate.cache_write_5m_usd_per_mtok,
    rate_output_usd_per_mtok:       rate.output_usd_per_mtok,
    cost_micro_usd: costMicroUsd,
    request_id: response.id,
    occurred_at: new Date(),
  });

  return response;
}

Hashed provider-side identifier

The OpenAI variant is structurally identical. The cost math reads usage.prompt_tokens_details.cached_tokens for the cache-hit count and subtracts it from usage.prompt_tokens to get the fresh-input count. The provider-side attribution field is the safety_identifier parameter (the legacy user field still works on Chat Completions). Hash the customer_id before sending — the safety best-practices guidance is explicit about not sending raw usernames or email addresses.

export async function callOpenAI(args: LlmCallArgs): Promise<OpenAIChatCompletion> {
  const rate = await rates.getCurrent("openai", args.model);
  if (!rate) throw new Error(`No rate row for openai/${args.model}`);

  const response = await openai.chat.completions.create({
    model: args.model,
    messages: args.messages as OpenAIChatMessage[],
    safety_identifier: hmacCustomerId(args.customer_id),
  });

  const usage = response.usage;
  const cachedTokens = usage?.prompt_tokens_details?.cached_tokens ?? 0;
  const inputTokens  = (usage?.prompt_tokens ?? 0) - cachedTokens;
  const outputTokens = usage?.completion_tokens ?? 0;

  const costMicroUsd =
    Math.round((inputTokens  / 1_000_000) * rate.input_usd_per_mtok        * 1_000_000) +
    Math.round((cachedTokens / 1_000_000) * rate.cached_input_usd_per_mtok * 1_000_000) +
    Math.round((outputTokens / 1_000_000) * rate.output_usd_per_mtok       * 1_000_000);

  await db.llmUsageEvents.insert({
    customer_id: args.customer_id,
    feature: args.feature,
    provider: "openai",
    model: args.model,
    input_tokens: inputTokens,
    cached_tokens: cachedTokens,
    cache_write_tokens: 0,
    output_tokens: outputTokens,
    rate_input_usd_per_mtok:        rate.input_usd_per_mtok,
    rate_cached_input_usd_per_mtok: rate.cached_input_usd_per_mtok,
    rate_cache_write_usd_per_mtok:  0,
    rate_output_usd_per_mtok:       rate.output_usd_per_mtok,
    cost_micro_usd: costMicroUsd,
    request_id: response.id,
    occurred_at: new Date(),
  });

  return response;
}

Idempotent nightly rollup

The nightly rollup is the part finance cares about. It writes one row per customer per day with cost broken out by provider so the monthly margin report joins straight from a single table. The rollup is idempotent on (customer_id, day) so re-running for a historical day is safe — useful when you backfill a missed window or recompute after a rate correction.

CREATE TABLE llm_cost_daily (
  customer_id   text NOT NULL,
  day           date NOT NULL,
  provider      text NOT NULL,
  input_tokens  bigint NOT NULL,
  cached_tokens bigint NOT NULL,
  output_tokens bigint NOT NULL,
  cost_usd      numeric(12, 4) NOT NULL,
  PRIMARY KEY (customer_id, day, provider)
);

-- Idempotent UPSERT for any UTC day, given a $1 boundary.
INSERT INTO llm_cost_daily (customer_id, day, provider,
                            input_tokens, cached_tokens, output_tokens, cost_usd)
SELECT
  customer_id,
  date_trunc('day', occurred_at AT TIME ZONE 'UTC')::date AS day,
  provider,
  SUM(input_tokens)::bigint,
  SUM(cached_tokens)::bigint,
  SUM(output_tokens)::bigint,
  (SUM(cost_micro_usd) / 1000000.0)::numeric(12, 4) AS cost_usd
FROM llm_usage_events
WHERE occurred_at >= $1::timestamptz
  AND occurred_at <  ($1::timestamptz + interval '1 day')
GROUP BY customer_id, day, provider
ON CONFLICT (customer_id, day, provider) DO UPDATE
SET input_tokens  = EXCLUDED.input_tokens,
    cached_tokens = EXCLUDED.cached_tokens,
    output_tokens = EXCLUDED.output_tokens,
    cost_usd      = EXCLUDED.cost_usd;

date_trunc() on a timestamp with time zone truncates with respect to a particular zone. The Postgres docs are explicit that the optional time zone argument can pick the zone you bucket on. Use UTC for billing — it removes a class of off-by-one bugs around daylight saving and makes the math obvious in audits. If a customer-facing dashboard needs the customer's local zone, do that conversion at read time, not at rollup time.

Monthly provider reconciliation

The provider-reconciliation step is the second half of trust. Once a month, after the OpenAI and Anthropic invoices arrive, run a check job that sums cost_usd per provider for the invoiced period and compares it to the invoice total. The gap should be small — single-digit percent at most — and it should be explainable by tier discounts, batch-API discounts, or fine-tuning line items the per-call math does not see. A widening gap means an attribution leak: somewhere a call is happening outside the wrapper, or a rate row is stale, or a model id has shifted (the API quietly maps deprecated aliases) and the rates table still carries the old name.