26 lines
1.2 KiB
SQL
26 lines
1.2 KiB
SQL
|
-- +goose Up
|
||
|
|
-- Distinguish live capture from history reconstructed after the fact.
|
||
|
|
--
|
||
|
|
-- ai_usage_daily rows written by the recorder are exact: they come from the
|
||
|
|
-- provider's usage block at call time. Rows recovered from
|
||
|
|
-- processed_products.gpt_response are best-effort — that JSON has no user
|
||
|
|
-- attribution and only survives for products whose response metadata was kept.
|
||
|
|
--
|
||
|
|
-- Keeping the two apart (and putting source in the primary key) makes the
|
||
|
|
-- backfill idempotent: it deletes and rewrites only its own rows, and can never
|
||
|
|
-- double-count a call the recorder already logged.
|
||
|
|
|
||
|
|
ALTER TABLE ai_usage_daily
|
||
|
|
ADD COLUMN IF NOT EXISTS source TEXT NOT NULL DEFAULT 'live';
|
||
|
|
|
||
|
|
ALTER TABLE ai_usage_daily DROP CONSTRAINT IF EXISTS ai_usage_daily_pkey;
|
||
|
|
ALTER TABLE ai_usage_daily
|
||
|
|
ADD CONSTRAINT ai_usage_daily_pkey PRIMARY KEY (day, company_id, user_id, model, role, source);
|
||
|
|
|
||
|
|
-- +goose Down
|
||
|
|
ALTER TABLE ai_usage_daily DROP CONSTRAINT IF EXISTS ai_usage_daily_pkey;
|
||
|
|
DELETE FROM ai_usage_daily WHERE source <> 'live';
|
||
|
|
ALTER TABLE ai_usage_daily
|
||
|
|
ADD CONSTRAINT ai_usage_daily_pkey PRIMARY KEY (day, company_id, user_id, model, role);
|
||
|
|
ALTER TABLE ai_usage_daily DROP COLUMN IF EXISTS source;
|