-- Run with psql -v ON_ERROR_STOP=1 so a failure stops the migration. Do not pass --single-transaction:
-- CREATE INDEX CONCURRENTLY cannot run inside a transaction block. A failed concurrent build leaves an
-- INVALID index to drop before rerunning. Every statement is idempotent.
--
-- Apply before deploying the application; db.create_all() does not add a column to an
-- existing table. Do not drop anything here while an instance might still run the new code.
--
-- Chat requests are text requests whose worker input is an OpenAI messages array. is_chat tells the
-- two apart at every read site; messages holds the array and is loaded only for an assigned request.
-- operation_key and operation_fingerprint hold the key a native caller claims one chat submission with
-- and the hash of the request it claimed it for; context_adjustments records how the context length
-- was changed to fit the messages, for the chat status. chat_extras holds the request's tools,
-- tool_choice, parallel_tool_calls and response_format and, like messages, is loaded only for an
-- assigned request; chat_required_features lists the worker capabilities and guarantees the request
-- needs, written once when the request is created, so eligibility reads it without loading either JSONB
-- column. chat_conformance holds the conformance mode the request selected; chat_tools_temperature holds
-- the temperature the request forwards with tools, NULL when it forwards none, which the pop query
-- compares with a worker's tool temperature limit. chat_fault_reason and chat_fault_rc hold the message
-- and return code of the last refused reply to a chat request, which its status reports once it faulted.
-- chat_messages_offloaded marks a chat request whose messages were also stored as one object at submit.
-- The workers columns hold the chat declaration a worker sends on each pop: whether it accepts chat
-- jobs and whether it still accepts text jobs, the optional features and guarantees it declares, its chat
-- limits, the model it serves chat jobs with and the transports it accepts images by.
-- nonconformant_replies counts the faulted jobs whose reply broke a strict request's contract, which
-- aborted_jobs does not count.
-- The processing_gens columns hold a chat worker's structured reply (the message with any tool calls,
-- finish reasons and reported token counts), the basis the generation was charged on, the worker's
-- declaration as it stood when the generation was assigned, the transport the request's images
-- were sent to it with, the return code its last reply was refused with, the posted character count
-- a cancelled streamed generation was settled on, and whether a legacy text job's max_length exceeded
-- the worker's lease capacity when it was assigned. A nullable column without
-- a default and a column with a constant default are both metadata-only adds; the lock timeout fails
-- rather than queueing an ACCESS EXCLUSIVE lock behind a long transaction. Rerun on failure.
SET lock_timeout = '5s';
ALTER TABLE waiting_prompts ADD COLUMN IF NOT EXISTS is_chat BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE waiting_prompts ADD COLUMN IF NOT EXISTS messages JSONB;
ALTER TABLE waiting_prompts ADD COLUMN IF NOT EXISTS operation_key UUID;
ALTER TABLE waiting_prompts ADD COLUMN IF NOT EXISTS operation_fingerprint CHAR(64);
ALTER TABLE waiting_prompts ADD COLUMN IF NOT EXISTS context_adjustments JSONB;
ALTER TABLE waiting_prompts ADD COLUMN IF NOT EXISTS chat_extras JSONB;
ALTER TABLE waiting_prompts ADD COLUMN IF NOT EXISTS chat_required_features TEXT[];
ALTER TABLE waiting_prompts ADD COLUMN IF NOT EXISTS chat_conformance VARCHAR NOT NULL DEFAULT 'strict';
ALTER TABLE waiting_prompts ADD COLUMN IF NOT EXISTS chat_tools_temperature DOUBLE PRECISION;
ALTER TABLE waiting_prompts ADD COLUMN IF NOT EXISTS chat_fault_reason VARCHAR(1000);
ALTER TABLE waiting_prompts ADD COLUMN IF NOT EXISTS chat_fault_rc VARCHAR(32);
ALTER TABLE waiting_prompts ADD COLUMN IF NOT EXISTS chat_messages_offloaded BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE workers ADD COLUMN IF NOT EXISTS accepts_chat_jobs BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE workers ADD COLUMN IF NOT EXISTS accepts_prompt_jobs BOOLEAN NOT NULL DEFAULT true;
ALTER TABLE workers ADD COLUMN IF NOT EXISTS chat_features TEXT[];
ALTER TABLE workers ADD COLUMN IF NOT EXISTS chat_max_context_length INTEGER;
ALTER TABLE workers ADD COLUMN IF NOT EXISTS chat_max_length INTEGER;
ALTER TABLE workers ADD COLUMN IF NOT EXISTS chat_tools_max_temperature DOUBLE PRECISION;
ALTER TABLE workers ADD COLUMN IF NOT EXISTS chat_model_id VARCHAR;
ALTER TABLE workers ADD COLUMN IF NOT EXISTS image_transports TEXT[];
ALTER TABLE workers ADD COLUMN IF NOT EXISTS nonconformant_replies INTEGER NOT NULL DEFAULT 0;
ALTER TABLE processing_gens ADD COLUMN IF NOT EXISTS message JSONB;
ALTER TABLE processing_gens ADD COLUMN IF NOT EXISTS finish_reason VARCHAR(32);
ALTER TABLE processing_gens ADD COLUMN IF NOT EXISTS native_finish_reason VARCHAR(32);
ALTER TABLE processing_gens ADD COLUMN IF NOT EXISTS prompt_tokens INTEGER;
ALTER TABLE processing_gens ADD COLUMN IF NOT EXISTS completion_tokens INTEGER;
ALTER TABLE processing_gens ADD COLUMN IF NOT EXISTS reasoning_tokens INTEGER;
ALTER TABLE processing_gens ADD COLUMN IF NOT EXISTS charge_basis VARCHAR(16);
ALTER TABLE processing_gens ADD COLUMN IF NOT EXISTS assigned_model VARCHAR;
ALTER TABLE processing_gens ADD COLUMN IF NOT EXISTS assigned_max_context_length INTEGER;
ALTER TABLE processing_gens ADD COLUMN IF NOT EXISTS assigned_max_length INTEGER;
ALTER TABLE processing_gens ADD COLUMN IF NOT EXISTS image_transport VARCHAR(16);
ALTER TABLE processing_gens ADD COLUMN IF NOT EXISTS chat_refusal_rc VARCHAR(32);
ALTER TABLE processing_gens ADD COLUMN IF NOT EXISTS settled_character_count INTEGER;
ALTER TABLE processing_gens ADD COLUMN IF NOT EXISTS over_capacity_at_assignment BOOLEAN NOT NULL DEFAULT false;
RESET lock_timeout;

-- One operation key per user. Every existing row has no key, so the partial index starts empty, but the
-- build still reads the whole table; building it concurrently keeps request writes flowing meanwhile.
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS ix_waiting_prompts_user_operation_key
  ON waiting_prompts (user_id, operation_key)
  WHERE operation_key IS NOT NULL;

-- The eligibility query and the readers of a worker's blacklist and softprompts look rows up by
-- worker_id. PostgreSQL does not index foreign key columns, so without these each lookup scans the table.
CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_worker_blacklists_worker_id ON worker_blacklists (worker_id);
CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_text_worker_softprompts_worker_id ON text_worker_softprompts (worker_id);
