110000x · 02 — Alias Implementation (Migration 042R)

File: migrations/postgres/042R_iu_core_operator_aliases.sql Apply channel: workflow_admin via pg_hba local trust socket inside Docker postgres container, DB directus (same channel as 91000x/100000x). Apply command:

ssh contabo
docker exec -i postgres psql -U workflow_admin -d directus -v ON_ERROR_STOP=1 < /tmp/042R_iu_core_operator_aliases.sql

Take pg_dump -Fc -d directus -f /tmp/pre-110000x.dump first.

Full SQL

-- =============================================================================
-- 042R_iu_core_operator_aliases.sql
-- IU Core 110000x: Operator alias surface over MARK→VERIFY-MARK→CUT→VERIFY-CUT
-- pipeline. Wrappers only — no new core logic. G1-G7 fully preserved.
-- =============================================================================

BEGIN;

-- -----------------------------------------------------------------------------
-- 1. fn_iu_op_mark_file — short MARK wrapper
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.fn_iu_op_mark_file(
  p_source_text     text,
  p_source_ref      text,
  p_pieces          jsonb,
  p_actor           text,
  p_source_kind     text DEFAULT 'user',
  p_idempotency_key text DEFAULT NULL,
  p_mark_report_md  text DEFAULT NULL,
  p_workflow_ref    text DEFAULT 'operational-cut-workflow'
) RETURNS jsonb
LANGUAGE plpgsql
AS $fn$
DECLARE
  v_source_hash    text;
  v_source_bytes   bigint;
  v_manifest_digest text;
  v_ik             text;
  v_mark_md        text;
  v_manifest       jsonb;
  v_coverage_proof jsonb;
  v_result         jsonb;
BEGIN
  IF p_source_text IS NULL OR length(p_source_text) = 0 THEN
    RAISE EXCEPTION 'fn_iu_op_mark_file: p_source_text required';
  END IF;
  IF p_source_ref IS NULL OR length(p_source_ref) = 0 THEN
    RAISE EXCEPTION 'fn_iu_op_mark_file: p_source_ref required';
  END IF;
  IF p_pieces IS NULL OR jsonb_typeof(p_pieces) <> 'array'
     OR jsonb_array_length(p_pieces) = 0 THEN
    RAISE EXCEPTION 'fn_iu_op_mark_file: p_pieces must be non-empty jsonb array';
  END IF;
  IF p_workflow_ref <> 'operational-cut-workflow' THEN
    RAISE EXCEPTION 'fn_iu_op_mark_file: unknown workflow_ref %', p_workflow_ref;
  END IF;

  v_source_hash    := md5(p_source_text);
  v_source_bytes   := octet_length(p_source_text);
  v_manifest_digest := md5(v_source_hash || '|'
                           || v_source_bytes::text || '|'
                           || p_pieces::text);
  v_ik := COALESCE(
    p_idempotency_key,
    'op-mark-' || v_source_hash || '-' || v_manifest_digest
  );
  v_mark_md := COALESCE(
    p_mark_report_md,
    format(E'# MARK report (alias)\n\n- source_ref: %s\n- source_hash: %s\n- source_bytes: %s\n- pieces: %s\n- workflow: %s\n- actor: %s\n- manifest_digest: %s\n',
      p_source_ref, v_source_hash, v_source_bytes,
      jsonb_array_length(p_pieces), p_workflow_ref, p_actor,
      v_manifest_digest)
  );

  v_manifest := jsonb_build_object(
    'manifest_version', 'v0.6',
    'source_id',        p_source_ref,
    'source_hash',      v_source_hash,
    'source_bytes',     v_source_bytes,
    'manifest_digest',  v_manifest_digest,
    'pieces',           p_pieces
  );
  v_coverage_proof := jsonb_build_object(
    'covered_bytes', v_source_bytes,
    'piece_count',   jsonb_array_length(p_pieces),
    'workflow_ref',  p_workflow_ref,
    'normalization', 'whitespace_collapse_v1'
  );

  v_result := public.fn_iu_mark_create_manifest(
    p_manifest          := v_manifest,
    p_mark_report_md    := v_mark_md,
    p_coverage_proof    := v_coverage_proof,
    p_determinism_digest:= v_manifest_digest,
    p_source_kind       := p_source_kind,
    p_source_ref        := p_source_ref,
    p_idempotency_key   := v_ik,
    p_actor             := p_actor
  );

  RETURN jsonb_build_object(
    'alias',             'fn_iu_op_mark_file',
    'staging_record_id', v_result->>'staging_record_id',
    'manifest_digest',   v_manifest_digest,
    'source_hash',       v_source_hash,
    'source_bytes',      v_source_bytes,
    'lifecycle_status',  v_result->>'lifecycle_status',
    'destination',       'iu_core.iu_staging_record + iu_staging_payload (No-Vector Staging Zone)',
    'idempotency_key',   v_ik,
    'inner_result',      v_result
  );
END;
$fn$;

-- -----------------------------------------------------------------------------
-- 2. fn_iu_op_verify_mark — short VERIFY-MARK wrapper (dry-run or approve)
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.fn_iu_op_verify_mark(
  p_staging_record_id uuid,
  p_approve           boolean DEFAULT false,
  p_approval_doc_id   text DEFAULT NULL,
  p_approver          text DEFAULT NULL,
  p_actor             text DEFAULT NULL
) RETURNS jsonb
LANGUAGE plpgsql
AS $fn$
DECLARE
  v_actor  text;
  v_result jsonb;
BEGIN
  v_actor := COALESCE(p_actor, p_approver, 'operator');
  IF p_approve THEN
    IF p_approval_doc_id IS NULL OR length(p_approval_doc_id) = 0 THEN
      RAISE EXCEPTION 'fn_iu_op_verify_mark: p_approval_doc_id required when p_approve=true';
    END IF;
    IF p_approver IS NULL OR length(p_approver) = 0 THEN
      RAISE EXCEPTION 'fn_iu_op_verify_mark: p_approver required when p_approve=true';
    END IF;
    v_result := public.fn_iu_verify_mark(
      p_staging_record_id,
      true,
      p_approval_doc_id,
      p_approver,
      v_actor
    );
  ELSE
    v_result := public.fn_iu_verify_mark(
      p_staging_record_id,
      false,
      NULL,
      NULL,
      v_actor
    );
  END IF;

  RETURN jsonb_build_object(
    'alias',             'fn_iu_op_verify_mark',
    'staging_record_id', p_staging_record_id,
    'approve',           p_approve,
    'verdict',           v_result->>'verdict',
    'inner_result',      v_result
  );
END;
$fn$;

-- -----------------------------------------------------------------------------
-- 3. fn_iu_op_cut — short CUT wrapper (resolves source_hash from staging)
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.fn_iu_op_cut(
  p_staging_record_id uuid,
  p_apply             boolean,
  p_actor             text,
  p_open_composer     boolean DEFAULT false
) RETURNS jsonb
LANGUAGE plpgsql
AS $fn$
DECLARE
  v_source_hash text;
  v_pre_composer boolean;
  v_result jsonb;
BEGIN
  -- Resolve source_hash from the staging record's cut_manifest payload.
  SELECT (p.payload_json->>'source_hash') INTO v_source_hash
  FROM   iu_core.iu_staging_payload p
  WHERE  p.staging_record_id = p_staging_record_id
    AND  p.part_name = 'cut_manifest'
  LIMIT 1;

  IF v_source_hash IS NULL THEN
    -- Try nested manifest path (alternative shape).
    SELECT (p.payload_json->'manifest'->>'source_hash') INTO v_source_hash
    FROM   iu_core.iu_staging_payload p
    WHERE  p.staging_record_id = p_staging_record_id
      AND  p.part_name = 'cut_manifest'
    LIMIT 1;
  END IF;

  IF v_source_hash IS NULL THEN
    RAISE EXCEPTION 'fn_iu_op_cut: cannot resolve source_hash from staging % cut_manifest payload', p_staging_record_id;
  END IF;

  SELECT (value='true') INTO v_pre_composer
  FROM dot_config WHERE key='iu_core.composer_enabled';
  v_pre_composer := COALESCE(v_pre_composer, false);

  IF p_open_composer AND NOT v_pre_composer THEN
    UPDATE dot_config SET value='true', updated_at=now()
    WHERE key='iu_core.composer_enabled';
  END IF;

  v_result := public.fn_iu_cut_from_manifest(
    p_staging_record_id,
    p_apply,
    v_source_hash,
    p_actor
  );

  RETURN jsonb_build_object(
    'alias',                   'fn_iu_op_cut',
    'staging_record_id',       p_staging_record_id,
    'apply',                   p_apply,
    'source_hash_resolved',    v_source_hash,
    'composer_pre',            v_pre_composer,
    'composer_opened_by_alias', (p_open_composer AND NOT v_pre_composer),
    'applied',                 v_result->>'applied',
    'pieces_created_count',    v_result->>'pieces_created_count',
    'run_id',                  v_result->>'run_id',
    'refusal_code',            v_result->>'refusal_code',
    'inner_result',            v_result
  );
END;
$fn$;

-- -----------------------------------------------------------------------------
-- 4. fn_iu_op_verify_cut — short VERIFY-CUT wrapper
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.fn_iu_op_verify_cut(
  p_run_id uuid,
  p_actor  text DEFAULT 'operator'
) RETURNS jsonb
LANGUAGE plpgsql
AS $fn$
DECLARE
  v_result jsonb;
BEGIN
  v_result := public.fn_iu_verify_cut_result(p_run_id, p_actor);
  RETURN jsonb_build_object(
    'alias',        'fn_iu_op_verify_cut',
    'run_id',       p_run_id,
    'verdict',      v_result->>'verdict',
    'inner_result', v_result
  );
END;
$fn$;

-- -----------------------------------------------------------------------------
-- 5. fn_iu_op_cleanup_dry_run — read-only cleanup probe
-- -----------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.fn_iu_op_cleanup_dry_run(
  p_older_than_days integer DEFAULT 15,
  p_actor           text    DEFAULT 'operator'
) RETURNS jsonb
LANGUAGE plpgsql
AS $fn$
DECLARE
  v_total   integer;
  v_actions jsonb;
BEGIN
  IF p_older_than_days < 1 THEN
    RAISE EXCEPTION 'fn_iu_op_cleanup_dry_run: p_older_than_days must be >= 1';
  END IF;

  WITH r AS (
    SELECT * FROM public.fn_iu_staging_cleanup(false, p_actor)
  )
  SELECT count(*),
         COALESCE(jsonb_agg(jsonb_build_object(
           'staging_record_id', staging_record_id,
           'staging_kind',      staging_kind,
           'lifecycle_before',  lifecycle_before,
           'lifecycle_after',   lifecycle_after,
           'action',            action
         )), '[]'::jsonb)
  INTO v_total, v_actions
  FROM r;

  RETURN jsonb_build_object(
    'alias',            'fn_iu_op_cleanup_dry_run',
    'apply',            false,
    'older_than_days',  p_older_than_days,
    'eligible_count',   v_total,
    'actions',          v_actions
  );
END;
$fn$;

-- -----------------------------------------------------------------------------
-- 6. DOT catalog registration (5 operator-alias entries)
-- -----------------------------------------------------------------------------
INSERT INTO public.dot_iu_command_catalog (
  command_name, category, mutating, reversible, target_functions
) VALUES
  ('dot_iu_operator_mark_file', 'piece', true, true,
   ARRAY['fn_iu_op_mark_file','fn_iu_mark_create_manifest']),
  ('dot_iu_operator_verify_mark', 'health', false, false,
   ARRAY['fn_iu_op_verify_mark','fn_iu_verify_mark']),
  ('dot_iu_operator_cut_staging', 'piece', true, true,
   ARRAY['fn_iu_op_cut','fn_iu_cut_from_manifest']),
  ('dot_iu_operator_verify_cut', 'health', false, false,
   ARRAY['fn_iu_op_verify_cut','fn_iu_verify_cut_result']),
  ('dot_iu_operator_cleanup_staging_dry_run', 'read', false, false,
   ARRAY['fn_iu_op_cleanup_dry_run','fn_iu_staging_cleanup'])
ON CONFLICT (command_name) DO NOTHING;

COMMIT;

Post-apply expected delta

Surface	Pre	Post	Δ
public schema functions	502	507	+5 (the 5 fn_iu_op_*)
dot_iu_command_catalog rows	36	41	+5 (operator alias entries)
All other surfaces	—	—	unchanged

pg_dump expected delta ≈ +25–35 KB for the 5 fn bodies + 5 DOT rows.

Rollback package

BEGIN;
DROP FUNCTION IF EXISTS public.fn_iu_op_cleanup_dry_run(integer,text);
DROP FUNCTION IF EXISTS public.fn_iu_op_verify_cut(uuid,text);
DROP FUNCTION IF EXISTS public.fn_iu_op_cut(uuid,boolean,text,boolean);
DROP FUNCTION IF EXISTS public.fn_iu_op_verify_mark(uuid,boolean,text,text,text);
DROP FUNCTION IF EXISTS public.fn_iu_op_mark_file(text,text,jsonb,text,text,text,text,text);
DELETE FROM public.dot_iu_command_catalog
WHERE command_name IN (
  'dot_iu_operator_mark_file',
  'dot_iu_operator_verify_mark',
  'dot_iu_operator_cut_staging',
  'dot_iu_operator_verify_cut',
  'dot_iu_operator_cleanup_staging_dry_run'
);
COMMIT;

Core pipeline functions (fn_iu_mark_create_manifest, fn_iu_verify_mark, fn_iu_cut_from_manifest, fn_iu_verify_cut_result, fn_iu_staging_cleanup) are untouched by 042R — rolling back the aliases does not affect them.

Mechanical drift patches applied vs. naive author intent

1. dot_iu_command_catalog.target_functions ARRAY NOT NULL — passed explicit ARRAY[…] list per command.
2. dot_iu_command_catalog.category CHECK is {collection,piece,lifecycle,read,health} — cleanup_dry_run mapped to read (it's a read-only probe, not lifecycle).
3. fn_iu_op_cut reads payload_json->>'source_hash' from the staging payload's cut_manifest part (per fn_iu_mark_create_manifest shape). Falls back to payload_json->'manifest'->>'source_hash' for alternative shapes.
4. iu_staging_payload_kind_chk vocab is {json,text,blob_ref} — fn_iu_mark_create_manifest writes parts with this vocab; aliases don't touch staging payload schema directly.
5. iu_staging_record.source_kind CHECK vocab is {agent,user,system,import} — fn_iu_op_mark_file default is user; agent-driven flows pass agent.
6. fn_iu_verify_mark requires positional args (p_staging_record_id, p_apply, p_approval_doc_id, p_approver, p_actor) — alias passes all 5 in either branch (dry-run with NULL for doc/approver).

No semantic drift; all six are mechanical schema-adapter adjustments.