98 lines
5.1 KiB
SQL
98 lines
5.1 KiB
SQL
-- v14.0 governed feedback-cycle delta.
|
|
-- This migration formalizes evidence-to-hypothesis-to-independent-validation history.
|
|
-- It does not enable automatic model activation, client publication, automatic order submission or KIS submission.
|
|
|
|
create table if not exists evaluation.model_feedback_cycle (
|
|
cycle_id uuid primary key,
|
|
scope_key text not null,
|
|
base_model_version text not null,
|
|
state text not null check (state in (
|
|
'PLANNED','PREDICTION_FROZEN','OUTCOMES_MATURING','EVALUATED','IMPROVEMENT_PROPOSED',
|
|
'CHALLENGER_PLANNED','INDEPENDENTLY_VALIDATED','PROMOTION_REVIEW_PENDING','BUSINESS_HOLD','CLOSED')),
|
|
revision integer not null check (revision > 0),
|
|
started_at timestamptz not null,
|
|
closed_at timestamptz null,
|
|
last_evidence_hash text not null,
|
|
updated_at timestamptz not null,
|
|
check ((state = 'CLOSED') = (closed_at is not null))
|
|
);
|
|
|
|
create index if not exists ix_model_feedback_cycle_scope_state
|
|
on evaluation.model_feedback_cycle (scope_key, state, updated_at desc);
|
|
|
|
create table if not exists evaluation.model_feedback_transition (
|
|
transition_id uuid primary key,
|
|
cycle_id uuid not null references evaluation.model_feedback_cycle(cycle_id),
|
|
sequence_no integer not null check (sequence_no > 0),
|
|
from_state text not null,
|
|
to_state text not null,
|
|
reason_code text not null,
|
|
evidence_hash text not null,
|
|
actor_id text not null,
|
|
actor_type text not null check (actor_type in ('SYSTEM','OPERATOR','INDEPENDENT_VALIDATOR','INVESTMENT_COMMITTEE')),
|
|
correlation_id text not null,
|
|
occurred_at timestamptz not null,
|
|
content_hash text not null unique,
|
|
unique (cycle_id, sequence_no)
|
|
);
|
|
|
|
create table if not exists governance.model_hypothesis_evidence (
|
|
hypothesis_evidence_id uuid primary key,
|
|
hypothesis_id uuid not null references governance.model_improvement_hypothesis(hypothesis_id),
|
|
side text not null check (side in ('SUPPORTING','COUNTER')),
|
|
classification text not null check (classification in ('SOURCE','ASSUMPTION','UNKNOWN','DECISION_REQUIRED')),
|
|
reference_code text not null,
|
|
statement text not null,
|
|
content_hash text not null,
|
|
created_at timestamptz not null default now(),
|
|
unique (hypothesis_id, side, reference_code, content_hash)
|
|
);
|
|
|
|
create table if not exists governance.model_activation_decision (
|
|
activation_decision_id uuid primary key,
|
|
scope_key text not null,
|
|
candidate_model_version text not null,
|
|
promotion_review_id uuid not null references governance.model_promotion_review(review_id),
|
|
decision text not null check (decision in ('KEEP_CURRENT_CHAMPION','ACTIVATE_FOR_SHADOW','ACTIVATE_FOR_PILOT','REJECT','EXPIRE')),
|
|
effective_at timestamptz null,
|
|
rollback_model_version text not null,
|
|
maker_id text not null,
|
|
checker_id text not null,
|
|
actor_type text not null check (actor_type = 'HUMAN_CHANGE_APPROVAL'),
|
|
change_ticket text not null,
|
|
evidence_bundle_hash text not null,
|
|
content_hash text not null unique,
|
|
decided_at timestamptz not null,
|
|
check (maker_id <> checker_id),
|
|
check (decision not in ('ACTIVATE_FOR_SHADOW','ACTIVATE_FOR_PILOT') or effective_at is not null)
|
|
);
|
|
|
|
-- Immutable evidence. The cycle aggregate itself remains mutable only through optimistic application logic;
|
|
-- every transition is captured here and cannot be edited or deleted.
|
|
drop trigger if exists model_feedback_transition_append_only on evaluation.model_feedback_transition;
|
|
create trigger model_feedback_transition_append_only
|
|
before update or delete on evaluation.model_feedback_transition
|
|
for each row execute function building_blocks.prevent_append_only_change();
|
|
|
|
drop trigger if exists model_hypothesis_evidence_append_only on governance.model_hypothesis_evidence;
|
|
create trigger model_hypothesis_evidence_append_only
|
|
before update or delete on governance.model_hypothesis_evidence
|
|
for each row execute function building_blocks.prevent_append_only_change();
|
|
|
|
drop trigger if exists model_activation_decision_append_only on governance.model_activation_decision;
|
|
create trigger model_activation_decision_append_only
|
|
before update or delete on governance.model_activation_decision
|
|
for each row execute function building_blocks.prevent_append_only_change();
|
|
|
|
comment on table governance.model_activation_decision is
|
|
'Human-only activation decision. Scheduler, Hangfire worker and proposal jobs must not insert into this table.';
|
|
|
|
-- Integrity audit is disabled until cycle aging, ownership and alert contracts are approved.
|
|
insert into evaluation.model_operation_schedule
|
|
(schedule_id, operation_code, operation_name, scope_key, cadence, automation_mode, queue_name,
|
|
schedule_version, enabled, next_due_at, max_lag, primary_owner, secondary_owner,
|
|
calendar_id, due_policy, dependency_json, catch_up_policy, max_catch_up)
|
|
values
|
|
('e4100000-0000-4000-8000-000000000039','J39','FeedbackCycleIntegrityAudit','GLOBAL','DAILY','EVALUATION_ONLY','q-control',1,false,now()+interval '1 day',interval '1 day','Compliance/QA','Quant/SRE','GLOBAL_UTC','FIXED_CADENCE','["FEEDBACK_CYCLE_CONTRACT_APPROVED","AGING_POLICY_APPROVED"]','LATEST_ONLY',1)
|
|
on conflict (operation_code, scope_key, schedule_version) do nothing;
|