59 lines
2.6 KiB
SQL
59 lines
2.6 KiB
SQL
|
-- +goose Up
|
||
|
|
-- Full prompt/response capture for every LLM call, for the platform-admin AI
|
||
|
|
-- inspector (/admin/ai-calls).
|
||
|
|
--
|
||
|
|
-- Worker logs only ever carried a 500-rune head/tail preview and
|
||
|
|
-- processed_products.gpt_response keeps response metadata without the prompt, so
|
||
|
|
-- there was no way to answer "what exactly did we send for this job".
|
||
|
|
--
|
||
|
|
-- Rows are large (a category-formula prompt runs several KB) and high volume (one
|
||
|
|
-- per product per language), so they are strictly short-lived: CleanupExpiredAICalls
|
||
|
|
-- prunes them on the worker's retention tick. This table is a debugging buffer, not
|
||
|
|
-- an audit trail — never build billing or reporting on it.
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS ai_call_logs (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
-- Nullable: platform-level probes (admin AI role test) belong to no tenant.
|
||
|
|
company_id UUID REFERENCES companies(id) ON DELETE CASCADE,
|
||
|
|
-- Who triggered the work (job starter / campaign author). Kept on user delete
|
||
|
|
-- so a tenant's recent calls stay readable; the row expires on its own.
|
||
|
|
user_id UUID REFERENCES users(id) ON DELETE SET NULL,
|
||
|
|
job_id UUID,
|
||
|
|
raw_product_id UUID,
|
||
|
|
-- aiprovider role: processing | categorize | seo_meta | campaign | support | probe.
|
||
|
|
role TEXT NOT NULL DEFAULT 'other',
|
||
|
|
provider_mode TEXT NOT NULL DEFAULT '',
|
||
|
|
model TEXT NOT NULL DEFAULT '',
|
||
|
|
system_prompt TEXT NOT NULL DEFAULT '',
|
||
|
|
user_prompt TEXT NOT NULL DEFAULT '',
|
||
|
|
response_text TEXT NOT NULL DEFAULT '',
|
||
|
|
error TEXT NOT NULL DEFAULT '',
|
||
|
|
finish_reason TEXT NOT NULL DEFAULT '',
|
||
|
|
prompt_tokens INTEGER NOT NULL DEFAULT 0,
|
||
|
|
completion_tokens INTEGER NOT NULL DEFAULT 0,
|
||
|
|
total_tokens INTEGER NOT NULL DEFAULT 0,
|
||
|
|
duration_ms INTEGER NOT NULL DEFAULT 0,
|
||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Primary browse: one company's most recent calls.
|
||
|
|
CREATE INDEX IF NOT EXISTS ai_call_logs_company_created_idx
|
||
|
|
ON ai_call_logs (company_id, created_at DESC);
|
||
|
|
|
||
|
|
-- Drill into a single job from the Processing page.
|
||
|
|
CREATE INDEX IF NOT EXISTS ai_call_logs_job_idx
|
||
|
|
ON ai_call_logs (job_id, created_at DESC)
|
||
|
|
WHERE job_id IS NOT NULL;
|
||
|
|
|
||
|
|
-- Retention sweep scans by age across all tenants.
|
||
|
|
CREATE INDEX IF NOT EXISTS ai_call_logs_created_idx
|
||
|
|
ON ai_call_logs (created_at);
|
||
|
|
|
||
|
|
-- Failures are the rare rows worth finding fast across a whole tenant.
|
||
|
|
CREATE INDEX IF NOT EXISTS ai_call_logs_failed_idx
|
||
|
|
ON ai_call_logs (company_id, created_at DESC)
|
||
|
|
WHERE error <> '';
|
||
|
|
|
||
|
|
-- +goose Down
|
||
|
|
DROP TABLE IF EXISTS ai_call_logs;
|