KArtSell.Aegis/db/migrations/0022_model_operations_execution_schema.sql

-- DB-CONTRACT-001: model-operation tables required by the approved handlers.
-- Source: existing 0008/0010 contracts; append-only closure for the canonical
-- db/migrations catalog. Outbox/Inbox remain building_blocks-owned.

create schema if not exists model_operations;

create table if not exists model_operations.shadow_run (
    run_id uuid primary key,
    model_id uuid not null,
    window_start date not null,
    window_end date not null,
    status varchar(50) not null default 'Pending',
    metrics_json jsonb,
    phase_analysis_json jsonb,
    cost_analysis_json jsonb,
    false_exit_analysis_json jsonb,
    validation_gates_json jsonb,
    error_message text,
    created_at timestamp not null default current_timestamp,
    published_at timestamp,
    constraint check_window_order check (window_start <= window_end),
    constraint check_status check (status in ('Pending', 'DataBackfill', 'Replay', 'EvaluationComplete', 'Failed'))
);

create index if not exists idx_shadow_run_model_created
    on model_operations.shadow_run (model_id, created_at desc);
create index if not exists idx_shadow_run_status
    on model_operations.shadow_run (status);
create index if not exists idx_shadow_run_published_at
    on model_operations.shadow_run (published_at);

create table if not exists model_operations.approval_queue (
    id uuid primary key default gen_random_uuid(),
    run_id uuid not null unique,
    model_id uuid not null,
    status varchar(32) not null default 'Pending',
    requested_by uuid,
    approved_by uuid,
    approval_reason text,
    rejection_reason text,
    requested_at timestamp not null default current_timestamp,
    approved_at timestamp,
    rejected_at timestamp,
    constraint approval_queue_run_fk foreign key (run_id)
        references model_operations.shadow_run(run_id) on delete restrict,
    constraint approval_queue_status_valid check (status in ('Pending', 'Approved', 'Rejected'))
);

create index if not exists approval_queue_status_idx on model_operations.approval_queue(status);
create index if not exists approval_queue_model_idx on model_operations.approval_queue(model_id, requested_at desc);
create index if not exists approval_queue_requested_idx on model_operations.approval_queue(requested_at desc);

create or replace function model_operations.approval_queue_check()
returns trigger as $$
begin
    if new.status = 'Approved' then
        if new.approved_at is null then new.approved_at := current_timestamp; end if;
        if new.approved_by is null then raise exception 'approved_by must be set when status = Approved'; end if;
    elsif new.status = 'Rejected' then
        if new.rejected_at is null then new.rejected_at := current_timestamp; end if;
        if new.rejection_reason is null then raise exception 'rejection_reason must be set when status = Rejected'; end if;
    end if;
    return new;
end;
$$ language plpgsql;

drop trigger if exists approval_queue_check_trigger on model_operations.approval_queue;
create trigger approval_queue_check_trigger
before insert or update on model_operations.approval_queue
for each row execute function model_operations.approval_queue_check();