KArtSell.Aegis/db/migrations/0015_signal_engine_semantic_versioning.sql

-- v12.3 semantic-versioning and contract-governance delta.
-- Prior migrations remain immutable. Legacy ambiguous weight contexts are blocked, not guessed.

alter table signal_engine.sell_decision_context
    add column if not exists weight_semantics_version smallint;

-- Every row created before this migration is conservatively classified as legacy/ambiguous.
update signal_engine.sell_decision_context
set weight_semantics_version = 1
where weight_semantics_version is null;

alter table signal_engine.sell_decision_context
    alter column weight_semantics_version set default 2,
    alter column weight_semantics_version set not null,
    drop constraint if exists ck_sell_context_weight_semantics_version,
    add constraint ck_sell_context_weight_semantics_version
        check (weight_semantics_version in (1, 2));

comment on column signal_engine.sell_decision_context.weight_semantics_version is
    '1=legacy ambiguous current_portfolio_weight backfill; 2=explicit security and lot weights from approved source. Decision reads require 2.';

alter table signal_engine.signal_decision
    add column if not exists decision_contract_version text,
    add column if not exists policy_trace_schema_version smallint;

update signal_engine.signal_decision
set decision_contract_version = coalesce(decision_contract_version, 'sell-decision.v2'),
    policy_trace_schema_version = coalesce(policy_trace_schema_version, 2)
where decision_contract_version is null
   or policy_trace_schema_version is null;

alter table signal_engine.signal_decision
    alter column decision_contract_version set default 'sell-decision.v2',
    alter column decision_contract_version set not null,
    alter column policy_trace_schema_version set default 2,
    alter column policy_trace_schema_version set not null,
    drop constraint if exists ck_signal_decision_contract_version,
    add constraint ck_signal_decision_contract_version
        check (decision_contract_version = 'sell-decision.v2'),
    drop constraint if exists ck_signal_decision_policy_trace_schema_version,
    add constraint ck_signal_decision_policy_trace_schema_version
        check (policy_trace_schema_version = 2);

create table if not exists signal_engine.policy_contract_definition (
    contract_version text primary key,
    content_hash text not null unique,
    policy_json jsonb not null,
    status text not null check (status in ('PROPOSED', 'APPROVED', 'RETIRED')),
    effective_from timestamptz null,
    approved_by text null,
    approved_at timestamptz null,
    created_at timestamptz not null default now(),
    check (jsonb_typeof(policy_json) = 'object')
);

insert into signal_engine.policy_contract_definition
(contract_version, content_hash, policy_json, status)
values
('sell-policy.v1', 'a269a0331b83c0f6ec108e7587de1d20c798036ff8d0c03d726cd854e73d8480', $$
{
  "changeControl": "MODEL_CHANGE_AND_GOLDEN_OOS_REQUIRED",
  "contractVersion": "sell-policy.v1",
  "decisionContractVersion": "sell-decision.v2",
  "policies": [
    {
      "condition": "approved hard impairment",
      "documentedRange": [
        0.8,
        1.0
      ],
      "mayCrossStrategicCore": true,
      "policyId": "ALG-SELL-001",
      "priority": 1000,
      "reentryEligible": false,
      "requestedSellRatioOfLot": 1.0,
      "terminal": true
    },
    {
      "condition": "capital floor breached",
      "decisionRequired": "Whether portfolio survival bypasses same-direction cooldown",
      "mayCrossStrategicCore": true,
      "policyId": "ALG-SELL-PORT-001",
      "priority": 900,
      "ratioSource": "server PIT risk context",
      "reentryEligible": true,
      "terminal": true
    },
    {
      "condition": {
        "gapBelowFloorAtrGte": 1.5
      },
      "documentedRange": [
        0.3,
        0.5
      ],
      "mayCrossStrategicCore": false,
      "policyId": "ALG-SELL-002",
      "priority": 800,
      "reentryEligible": true,
      "requestedSellRatioOfLot": 0.4,
      "terminal": false
    },
    {
      "condition": {
        "consecutiveCloseBreachesGte": 2
      },
      "documentedRange": [
        0.15,
        0.25
      ],
      "mayCrossStrategicCore": false,
      "policyId": "ALG-SELL-003",
      "priority": 700,
      "reentryEligible": true,
      "requestedSellRatioOfLot": 0.2,
      "terminal": false
    },
    {
      "condition": "approved concentration/liquidity excess ratio > 0",
      "mayCrossStrategicCore": false,
      "policyId": "ALG-SELL-004",
      "priority": 600,
      "ratioSource": "server PIT portfolio risk context",
      "reentryEligible": true,
      "terminal": false
    },
    {
      "condition": "opportunity edge lower bound > 0 and requested ratio > 0",
      "mayCrossStrategicCore": false,
      "policyId": "ALG-SELL-005",
      "priority": 500,
      "reentryEligible": true,
      "requestedRange": [
        0.1,
        0.25
      ],
      "terminal": false
    }
  ],
  "policyTraceSchemaVersion": 2,
  "status": "RESEARCH_CANDIDATE_NOT_PRODUCTION"
}
$$::jsonb, 'PROPOSED')
on conflict (contract_version) do nothing;

drop trigger if exists policy_contract_definition_immutable on signal_engine.policy_contract_definition;
create trigger policy_contract_definition_immutable
before update or delete on signal_engine.policy_contract_definition
for each row execute function signal_engine.prevent_immutable_change();