KArtSell.Aegis/db/migrations/0033_source_approval_contract.sql

-- AEG-X-009 / ADR-DATA-001: append-only source approval boundary.
-- This migration authorizes governance records only. It does not authorize ingestion,
-- recommendation, model activation, client publication, order, or KIS submission.

create schema if not exists governance;

create table if not exists governance.source_approval (
    source_approval_id uuid primary key default gen_random_uuid(),
    source_id text not null,
    source_version text not null,
    domain text not null,
    owner text not null,
    steward text not null,
    license_reference text not null,
    availability_sla text not null,
    freshness_sla text not null,
    timezone text not null,
    calendar_id text not null,
    unit_contract text not null,
    schema_contract_version text not null,
    status text not null,
    content_hash char(64) not null,
    published_at timestamptz,
    revision integer,
    approved_by text not null,
    approved_at timestamptz not null,
    created_at timestamptz not null default now(),
    constraint source_approval_status_valid
        check (status in ('CANDIDATE', 'APPROVED', 'SUSPENDED', 'RETIRED', 'QUARANTINED')),
    constraint source_approval_hash_valid
        check (content_hash ~ '^[0-9A-Fa-f]{64}$'),
    constraint source_approval_approved_requires_publication
        check (status <> 'APPROVED' or (published_at is not null and revision is not null and revision > 0))
);

create unique index if not exists source_approval_identity_idx
    on governance.source_approval (source_id, source_version, revision)
    where revision is not null;

create index if not exists source_approval_status_idx
    on governance.source_approval (status, created_at desc);

create or replace function governance.reject_source_approval_mutation()
returns trigger as $$
begin
    raise exception 'governance.source_approval is append-only; create a correction record';
end;
$$ language plpgsql;

drop trigger if exists source_approval_no_update on governance.source_approval;
create trigger source_approval_no_update
before update or delete on governance.source_approval
for each row execute function governance.reject_source_approval_mutation();