46 lines
3.3 KiB
SQL
46 lines
3.3 KiB
SQL
-- Normalize payloads without changing existing task-envelope pagination/ids.
|
|
-- Run via the deployment migration flow, never against production from a laptop.
|
|
CREATE TABLE IF NOT EXISTS eddy_conversation_records (
|
|
id text PRIMARY KEY, owner text NOT NULL, event text NOT NULL,
|
|
payload_bytes bigint NOT NULL, received_at bigint NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS eddy_conversation_records_received_at ON eddy_conversation_records(received_at);
|
|
ALTER TABLE eddy_conversation_events ADD COLUMN IF NOT EXISTS record_id text;
|
|
ALTER TABLE eddy_conversation_events ADD COLUMN IF NOT EXISTS ingestion_bytes bigint NOT NULL DEFAULT 0;
|
|
ALTER TABLE eddy_conversation_events ALTER COLUMN event DROP NOT NULL;
|
|
INSERT INTO eddy_conversation_records (id, owner, event, payload_bytes, received_at)
|
|
SELECT DISTINCT ON (owner, machine, source_id, event::jsonb->>'id')
|
|
encode(sha256(convert_to(owner || E'\n' || machine || E'\n' || source_id || E'\n' || (event::jsonb->>'id'), 'UTF8')), 'hex'),
|
|
owner, event, octet_length(event), received_at
|
|
FROM eddy_conversation_events WHERE event IS NOT NULL
|
|
ORDER BY owner, machine, source_id, event::jsonb->>'id', received_at
|
|
ON CONFLICT DO NOTHING;
|
|
UPDATE eddy_conversation_events SET record_id = encode(sha256(convert_to(
|
|
owner || E'\n' || machine || E'\n' || source_id || E'\n' || (event::jsonb->>'id'), 'UTF8')), 'hex')
|
|
WHERE record_id IS NULL AND event IS NOT NULL;
|
|
UPDATE eddy_conversation_events SET ingestion_bytes = octet_length(jsonb_build_object(
|
|
'id', id, 'owner', owner, 'machine', machine, 'taskId', task_id, 'taskTitle', task_title,
|
|
'sourceId', source_id, 'provider', provider, 'receivedAt', received_at, 'recordId', record_id)::text), event = NULL
|
|
WHERE record_id IS NOT NULL AND event IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS eddy_conversations_record_id ON eddy_conversation_events(record_id);
|
|
CREATE INDEX IF NOT EXISTS eddy_conversations_received_at ON eddy_conversation_events(received_at);
|
|
ALTER TABLE eddy_usage_events ADD COLUMN IF NOT EXISTS budget_owner text;
|
|
ALTER TABLE eddy_usage_events ADD COLUMN IF NOT EXISTS ingestion_bytes bigint NOT NULL DEFAULT 0;
|
|
UPDATE eddy_usage_events SET ingestion_bytes = octet_length(row_to_json(eddy_usage_events)::text) WHERE ingestion_bytes = 0;
|
|
CREATE TABLE IF NOT EXISTS eddy_ingestion_budget (
|
|
key text PRIMARY KEY, bytes bigint NOT NULL DEFAULT 0, rows bigint NOT NULL DEFAULT 0,
|
|
day bigint NOT NULL DEFAULT 0, day_bytes bigint NOT NULL DEFAULT 0, day_rows bigint NOT NULL DEFAULT 0,
|
|
tokens double precision, token_at bigint NOT NULL DEFAULT 0
|
|
);
|
|
-- Seed existing retained data; migration retries never reset live budgets.
|
|
WITH entries AS (
|
|
SELECT owner, payload_bytes AS bytes FROM eddy_conversation_records
|
|
UNION ALL SELECT owner, ingestion_bytes AS bytes FROM eddy_conversation_events
|
|
), totals AS (
|
|
SELECT 'conversation:global' AS key, coalesce(sum(bytes),0) AS bytes, count(*) AS rows FROM entries
|
|
UNION ALL SELECT 'conversation:owner:' || encode(sha256(convert_to(lower(owner),'UTF8')),'hex'), sum(bytes), count(*) FROM entries GROUP BY owner
|
|
)
|
|
INSERT INTO eddy_ingestion_budget (key, bytes, rows) SELECT key, bytes, rows FROM totals ON CONFLICT DO NOTHING;
|
|
INSERT INTO eddy_ingestion_budget (key, bytes, rows)
|
|
SELECT 'usage:global', coalesce(sum(ingestion_bytes),0), count(*) FROM eddy_usage_events ON CONFLICT DO NOTHING;
|