143 lines
6.2 KiB
SQL
143 lines
6.2 KiB
SQL
-- v12.0 integrated hardening delta. Prior migrations remain immutable.
|
|
|
|
-- Rename ambiguous columns while preserving existing data.
|
|
do $$
|
|
begin
|
|
if exists (
|
|
select 1 from information_schema.columns
|
|
where table_schema = 'signal_engine'
|
|
and table_name = 'signal_decision'
|
|
and column_name = 'lot_id'
|
|
) and not exists (
|
|
select 1 from information_schema.columns
|
|
where table_schema = 'signal_engine'
|
|
and table_name = 'signal_decision'
|
|
and column_name = 'position_lot_id'
|
|
) then
|
|
alter table signal_engine.signal_decision
|
|
rename column lot_id to position_lot_id;
|
|
end if;
|
|
|
|
if exists (
|
|
select 1 from information_schema.columns
|
|
where table_schema = 'signal_engine'
|
|
and table_name = 'signal_decision'
|
|
and column_name = 'sell_fraction'
|
|
) and not exists (
|
|
select 1 from information_schema.columns
|
|
where table_schema = 'signal_engine'
|
|
and table_name = 'signal_decision'
|
|
and column_name = 'sell_ratio_of_lot'
|
|
) then
|
|
alter table signal_engine.signal_decision
|
|
rename column sell_fraction to sell_ratio_of_lot;
|
|
end if;
|
|
end;
|
|
$$;
|
|
|
|
alter table signal_engine.signal_decision
|
|
add column if not exists target_portfolio_weight_after numeric(12,8),
|
|
add column if not exists dataset_id text,
|
|
add column if not exists model_version text,
|
|
add column if not exists config_version text,
|
|
add column if not exists code_sha text,
|
|
add column if not exists idempotency_key text,
|
|
add column if not exists correlation_id text;
|
|
|
|
update signal_engine.signal_decision d
|
|
set dataset_id = coalesce(d.dataset_id, e.dataset_id),
|
|
model_version = coalesce(d.model_version, e.model_version),
|
|
config_version = coalesce(d.config_version, e.config_version),
|
|
code_sha = coalesce(d.code_sha, 'LEGACY_UNKNOWN'),
|
|
target_portfolio_weight_after = coalesce(d.target_portfolio_weight_after, 0),
|
|
idempotency_key = coalesce(d.idempotency_key, 'legacy:' || d.decision_id::text),
|
|
correlation_id = coalesce(d.correlation_id, 'legacy:' || d.decision_id::text)
|
|
from signal_engine.evidence_snapshot e
|
|
where e.evidence_id = d.evidence_id;
|
|
|
|
alter table signal_engine.signal_decision
|
|
alter column target_portfolio_weight_after set not null,
|
|
alter column dataset_id set not null,
|
|
alter column model_version set not null,
|
|
alter column config_version set not null,
|
|
alter column code_sha set not null,
|
|
alter column idempotency_key set not null,
|
|
alter column correlation_id set not null;
|
|
|
|
alter table signal_engine.signal_decision
|
|
drop constraint if exists ck_signal_decision_sell_ratio_of_lot,
|
|
add constraint ck_signal_decision_sell_ratio_of_lot
|
|
check (sell_ratio_of_lot between 0 and 1),
|
|
drop constraint if exists ck_signal_decision_target_weight,
|
|
add constraint ck_signal_decision_target_weight
|
|
check (target_portfolio_weight_after between 0 and 1);
|
|
|
|
create unique index if not exists ux_signal_decision_idempotency
|
|
on signal_engine.signal_decision (idempotency_key);
|
|
|
|
create index if not exists ix_signal_decision_lot_created
|
|
on signal_engine.signal_decision (position_lot_id, created_at desc);
|
|
|
|
-- Rebuildable, versioned read model. It is populated only by approved upstream slices.
|
|
create table if not exists signal_engine.sell_decision_context (
|
|
context_id uuid primary key,
|
|
position_lot_id uuid not null,
|
|
cycle_id uuid not null,
|
|
evidence_id text not null references signal_engine.evidence_snapshot(evidence_id),
|
|
dataset_id text not null,
|
|
model_version text not null,
|
|
config_version text not null,
|
|
code_sha text not null,
|
|
as_of timestamptz not null,
|
|
published_at_cutoff timestamptz not null,
|
|
current_portfolio_weight numeric(12,8) not null
|
|
check (current_portfolio_weight between 0 and 1),
|
|
strategic_core_floor_weight numeric(12,8) not null
|
|
check (strategic_core_floor_weight between 0 and current_portfolio_weight),
|
|
hard_impairment_approved boolean not null,
|
|
capital_floor_breached boolean not null,
|
|
survival_sell_ratio_of_lot numeric(12,8) not null
|
|
check (survival_sell_ratio_of_lot between 0 and 1),
|
|
gap_below_floor_atr numeric(18,8) not null check (gap_below_floor_atr >= 0),
|
|
consecutive_close_breaches integer not null check (consecutive_close_breaches >= 0),
|
|
cooldown_satisfied boolean not null,
|
|
concentration_sell_ratio_of_lot numeric(12,8) not null
|
|
check (concentration_sell_ratio_of_lot between 0 and 1),
|
|
opportunity_edge_lower_bound numeric(18,8) not null,
|
|
opportunity_sell_ratio_of_lot numeric(12,8) not null
|
|
check (opportunity_sell_ratio_of_lot between 0 and 1),
|
|
quality_status text not null check (quality_status in ('PASS', 'WARN', 'QUARANTINED')),
|
|
source_watermark text not null,
|
|
projection_version integer not null,
|
|
content_hash text not null unique,
|
|
created_at timestamptz not null default now(),
|
|
unique (position_lot_id, as_of, projection_version)
|
|
);
|
|
|
|
create index if not exists ix_sell_decision_context_lookup
|
|
on signal_engine.sell_decision_context (position_lot_id, as_of desc)
|
|
where quality_status = 'PASS';
|
|
|
|
drop trigger if exists sell_decision_context_immutable on signal_engine.sell_decision_context;
|
|
create trigger sell_decision_context_immutable
|
|
before update or delete on signal_engine.sell_decision_context
|
|
for each row execute function signal_engine.prevent_immutable_change();
|
|
|
|
-- Audit rows are append-only. Corrections are represented as new events.
|
|
create table if not exists signal_engine.decision_audit_event (
|
|
audit_event_id uuid primary key,
|
|
decision_id uuid not null references signal_engine.signal_decision(decision_id),
|
|
event_type text not null,
|
|
actor_id text not null,
|
|
correlation_id text not null,
|
|
payload_json jsonb not null,
|
|
payload_hash text not null,
|
|
occurred_at timestamptz not null,
|
|
unique (decision_id, event_type, payload_hash)
|
|
);
|
|
|
|
drop trigger if exists decision_audit_event_immutable on signal_engine.decision_audit_event;
|
|
create trigger decision_audit_event_immutable
|
|
before update or delete on signal_engine.decision_audit_event
|
|
for each row execute function signal_engine.prevent_immutable_change();
|