Jyotisha/frontend/supabase/migrations/20260814020000_rectification_v10_runtime.sql

-- Rectification V10 durable conversation/runtime contract.
-- Additive only: V9 RPC signatures remain available for older runners.

begin;

-- ---------------------------------------------------------------------------
-- 1. Durable run attempts and receipt ownership
-- ---------------------------------------------------------------------------

create table if not exists public.agentic_rectification_run_attempts (
  id uuid primary key default gen_random_uuid(),
  case_id uuid not null references public.agentic_rectification_cases(id) on delete cascade,
  turn_id uuid not null references public.agentic_rectification_turns(id) on delete cascade,
  attempt_number integer not null check (attempt_number > 0),
  status text not null default 'started' check (
    status in ('started', 'completed', 'failed', 'retryable', 'aborted')
  ),
  error_code text,
  usage jsonb not null default '{}'::jsonb check (jsonb_typeof(usage) = 'object'),
  started_at timestamptz not null default pg_catalog.now(),
  completed_at timestamptz,
  updated_at timestamptz not null default pg_catalog.now(),
  unique (turn_id, attempt_number),
  unique (id, case_id, turn_id),
  check (status = 'started' or completed_at is not null)
);

create index if not exists agentic_rectification_run_attempts_turn_idx
  on public.agentic_rectification_run_attempts (case_id, turn_id, attempt_number desc);
create index if not exists agentic_rectification_run_attempts_success_idx
  on public.agentic_rectification_run_attempts (turn_id, completed_at desc)
  where status = 'completed';

alter table public.agentic_rectification_run_phases
  add column if not exists attempt_id uuid references public.agentic_rectification_run_attempts(id) on delete cascade;
alter table public.agentic_rectification_tool_receipts
  add column if not exists attempt_id uuid references public.agentic_rectification_run_attempts(id) on delete cascade;
alter table public.agentic_rectification_turns
  add column if not exists successful_attempt_id uuid references public.agentic_rectification_run_attempts(id) on delete set null;
alter table public.agentic_rectification_turns
  add column if not exists request_id uuid;
create unique index if not exists agentic_rectification_turns_case_request_idx
  on public.agentic_rectification_turns (case_id, request_id)
  where request_id is not null;

-- Keep the storage-layer allowlists aligned with the V10 RPC allowlists. The
-- original V9 CHECK constraints would otherwise reject valid V10 receipts.
alter table public.agentic_rectification_run_phases
  drop constraint if exists agentic_rectification_run_phases_phase_check,
  add constraint agentic_rectification_run_phases_phase_check check (
    phase in (
      'run.started', 'skill.started', 'skill.loaded', 'skill.bound', 'case.loaded',
      'intent.classified', 'evidence.proposed', 'evidence.confirmed',
      'candidates.comparing', 'candidates.updated', 'diagnostics.completed',
      'candidate.accepted', 'birth_time.confirmed', 'answer.composed',
      'billing.settled', 'answer.delta', 'run.completed', 'run.failed'
    )
  );

alter table public.agentic_rectification_tool_receipts
  drop constraint if exists agentic_rectification_tool_receipts_tool_name_check,
  add constraint agentic_rectification_tool_receipts_tool_name_check check (
    tool_name in (
      'rectification-read-case', 'rectification-set-focus',
      'rectification-resolve-focus', 'rectification-record-evidence-batch',
      'rectification-propose-evidence', 'rectification-confirm-evidence',
      'rectification-revise-evidence', 'rectification-compare-candidates',
      'rectification-read-diagnostics', 'rectification-offer-candidates',
      'rectification-accept-candidate', 'rectification-confirm-birth-time',
      'rectification-close-case'
    )
  ),
  drop constraint if exists agentic_rectification_tool_receipts_public_phase_check,
  add constraint agentic_rectification_tool_receipts_public_phase_check check (
    public_phase in (
      'run.started', 'skill.started', 'skill.loaded', 'skill.bound', 'case.loaded',
      'intent.classified', 'evidence.proposed', 'evidence.confirmed',
      'candidates.comparing', 'candidates.updated', 'diagnostics.completed',
      'candidate.accepted', 'birth_time.confirmed', 'answer.composed',
      'billing.settled', 'answer.delta', 'run.completed', 'run.failed'
    )
  );

-- Enforce Case/Turn/Attempt scope below the SECURITY DEFINER RPC layer.
alter table public.agentic_rectification_turns
  drop constraint if exists agentic_rectification_turns_id_case_id_key,
  add constraint agentic_rectification_turns_id_case_id_key unique (id, case_id);
alter table public.agentic_rectification_run_attempts
  drop constraint if exists agentic_rectification_run_attempts_turn_case_fkey,
  add constraint agentic_rectification_run_attempts_turn_case_fkey
    foreign key (turn_id, case_id)
    references public.agentic_rectification_turns(id, case_id) on delete cascade;
alter table public.agentic_rectification_run_phases
  drop constraint if exists agentic_rectification_run_phases_attempt_scope_fkey,
  add constraint agentic_rectification_run_phases_attempt_scope_fkey
    foreign key (attempt_id, case_id, turn_id)
    references public.agentic_rectification_run_attempts(id, case_id, turn_id) on delete cascade;
alter table public.agentic_rectification_tool_receipts
  drop constraint if exists agentic_rectification_tool_receipts_attempt_scope_fkey,
  add constraint agentic_rectification_tool_receipts_attempt_scope_fkey
    foreign key (attempt_id, case_id, turn_id)
    references public.agentic_rectification_run_attempts(id, case_id, turn_id) on delete cascade;

create index if not exists agentic_rectification_run_phases_attempt_idx
  on public.agentic_rectification_run_phases (attempt_id, sequence);
create index if not exists agentic_rectification_tool_receipts_attempt_idx
  on public.agentic_rectification_tool_receipts (attempt_id, started_at);

-- ---------------------------------------------------------------------------
-- 2. ConversationFocus and server-owned CaseConversationSummary
-- ---------------------------------------------------------------------------

create table if not exists public.agentic_rectification_conversation_focuses (
  id uuid primary key default gen_random_uuid(),
  case_id uuid not null references public.agentic_rectification_cases(id) on delete cascade,
  question_id text not null check (length(btrim(question_id)) > 0),
  intent text not null check (length(btrim(intent)) > 0),
  target_evidence_id uuid references public.agentic_rectification_evidence(id) on delete set null,
  target_domain text check (
    target_domain is null or target_domain in (
      'education', 'career', 'relationship', 'relocation', 'finance', 'health', 'family', 'other'
    )
  ),
  target_kind text check (
    target_kind is null or target_kind in (
      'education_start', 'education_completion', 'education_interruption',
      'career_entry', 'career_change', 'promotion', 'career_pressure', 'career_exit',
      'relationship_start', 'relationship_commitment', 'relationship_separation',
      'relocation', 'finance_gain', 'finance_loss',
      'self_health_event', 'family_event', 'other'
    )
  ),
  expected_answer_schema jsonb not null default '{}'::jsonb
    check (jsonb_typeof(expected_answer_schema) = 'object'),
  status text not null default 'active' check (
    status in ('active', 'resolved', 'declined', 'skipped', 'superseded')
  ),
  asked_at timestamptz not null default pg_catalog.now(),
  resolved_at timestamptz,
  created_at timestamptz not null default pg_catalog.now(),
  updated_at timestamptz not null default pg_catalog.now(),
  unique (case_id, question_id),
  check (
    (status = 'active' and resolved_at is null)
    or (status <> 'active' and resolved_at is not null)
  )
);

create unique index if not exists agentic_rectification_one_active_focus_per_case
  on public.agentic_rectification_conversation_focuses (case_id)
  where status = 'active';
create index if not exists agentic_rectification_focus_case_history_idx
  on public.agentic_rectification_conversation_focuses (case_id, asked_at desc);
create index if not exists agentic_rectification_focus_evidence_idx
  on public.agentic_rectification_conversation_focuses (target_evidence_id)
  where target_evidence_id is not null;

create table if not exists public.agentic_rectification_case_conversation_summaries (
  case_id uuid primary key references public.agentic_rectification_cases(id) on delete cascade,
  confirmed_evidence_summary jsonb not null default '[]'::jsonb
    check (jsonb_typeof(confirmed_evidence_summary) = 'array'),
  pending_revisions jsonb not null default '[]'::jsonb
    check (jsonb_typeof(pending_revisions) = 'array'),
  active_focus jsonb,
  declined_skipped_topics jsonb not null default '[]'::jsonb
    check (jsonb_typeof(declined_skipped_topics) = 'array'),
  candidate_divergence_summary jsonb,
  missing_evidence_categories jsonb not null default '[]'::jsonb
    check (jsonb_typeof(missing_evidence_categories) = 'array'),
  last_result_policy jsonb,
  summary_version integer not null default 1 check (summary_version > 0),
  updated_at timestamptz not null default pg_catalog.now(),
  check (active_focus is null or jsonb_typeof(active_focus) = 'object'),
  check (candidate_divergence_summary is null or jsonb_typeof(candidate_divergence_summary) = 'object'),
  check (last_result_policy is null or jsonb_typeof(last_result_policy) = 'object')
);

-- Batch idempotency is per Case and per independently parsed event.
alter table public.agentic_rectification_evidence
  add column if not exists idempotency_key text;
create unique index if not exists agentic_rectification_evidence_idempotency_idx
  on public.agentic_rectification_evidence (case_id, idempotency_key)
  where idempotency_key is not null;

-- ---------------------------------------------------------------------------
-- 3. RLS and grants: service role owns writes; browser receives projections.
-- ---------------------------------------------------------------------------

alter table public.agentic_rectification_run_attempts enable row level security;
alter table public.agentic_rectification_conversation_focuses enable row level security;
alter table public.agentic_rectification_case_conversation_summaries enable row level security;

revoke all on table public.agentic_rectification_run_attempts from public, anon, authenticated, service_role;
revoke all on table public.agentic_rectification_conversation_focuses from public, anon, authenticated, service_role;
revoke all on table public.agentic_rectification_case_conversation_summaries from public, anon, authenticated, service_role;

-- No direct table DML is granted. Runtime access is only through the scoped
-- SECURITY DEFINER RPCs below. Revoke inherited V9 DML on receipt tables too;
-- their legacy and V10 write paths are SECURITY DEFINER functions.
revoke insert, update, delete, truncate
  on table public.agentic_rectification_run_phases,
           public.agentic_rectification_tool_receipts
  from service_role;

-- ---------------------------------------------------------------------------
-- 4. Server summary rebuild. No raw quote, birth snapshot, score or reasoning.
-- ---------------------------------------------------------------------------

create or replace function public.refresh_agentic_rectification_case_conversation_summary(
  p_case_id uuid
)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_confirmed jsonb;
  v_pending jsonb;
  v_active_focus jsonb;
  v_declined_skipped jsonb;
  v_candidate_divergence jsonb;
  v_missing jsonb;
  v_last_policy jsonb;
  v_result public.agentic_rectification_results%rowtype;
  v_summary jsonb;
begin
  if p_case_id is null or not exists (
    select 1 from public.agentic_rectification_cases where id = p_case_id
  ) then
    raise exception 'agentic_rectification_case_not_found' using errcode = 'P0001';
  end if;

  select coalesce(jsonb_agg(jsonb_build_object(
    'evidence_id', e.id,
    'summary', e.summary,
    'event_kind', e.event_kind,
    'domain', e.domain,
    'date_precision', e.date_precision,
    'occurred_from', e.occurred_from,
    'occurred_to', e.occurred_to
  ) order by e.created_at, e.id), '[]'::jsonb)
  into v_confirmed
  from public.agentic_rectification_evidence e
  where e.case_id = p_case_id and e.status = 'confirmed';

  select coalesce(jsonb_agg(jsonb_build_object(
    'evidence_id', e.id,
    'supersedes_evidence_id', e.supersedes_evidence_id,
    'summary', e.summary,
    'event_kind', e.event_kind,
    'domain', e.domain,
    'date_precision', e.date_precision,
    'occurred_from', e.occurred_from,
    'occurred_to', e.occurred_to
  ) order by e.created_at, e.id), '[]'::jsonb)
  into v_pending
  from public.agentic_rectification_evidence e
  where e.case_id = p_case_id
    and e.status in ('draft', 'pending_confirmation');

  select jsonb_build_object(
    'id', f.id,
    'case_id', f.case_id,
    'question_id', f.question_id,
    'intent', f.intent,
    'target_evidence_id', f.target_evidence_id,
    'target_domain', f.target_domain,
    'target_kind', f.target_kind,
    'expected_answer_schema', f.expected_answer_schema,
    'status', f.status,
    'asked_at', f.asked_at,
    'resolved_at', f.resolved_at
  ) into v_active_focus
  from public.agentic_rectification_conversation_focuses f
  where f.case_id = p_case_id and f.status = 'active'
  order by f.asked_at desc, f.id
  limit 1;

  select coalesce(jsonb_agg(jsonb_build_object(
    'focus_id', f.id,
    'intent', f.intent,
    'target_evidence_id', f.target_evidence_id,
    'target_domain', f.target_domain,
    'target_kind', f.target_kind,
    'status', f.status,
    'asked_at', f.asked_at,
    'resolved_at', f.resolved_at
  ) order by f.resolved_at, f.id), '[]'::jsonb)
  into v_declined_skipped
  from public.agentic_rectification_conversation_focuses f
  where f.case_id = p_case_id and f.status in ('declined', 'skipped');

  select * into v_result
  from public.agentic_rectification_results
  where case_id = p_case_id and invalidated_at is null
  order by created_at desc, id desc
  limit 1;

  if v_result.id is null then
    v_candidate_divergence := null;
    v_last_policy := null;
  else
    v_candidate_divergence := jsonb_build_object(
      'result_id', v_result.id,
      'overall_confidence', v_result.overall_confidence,
      'margin_percent', v_result.margin_percent,
      'candidate_count', jsonb_array_length(coalesce(v_result.candidates, '[]'::jsonb)),
      'tied_candidate_count', (
        select count(*)
        from jsonb_array_elements(coalesce(v_result.candidates, '[]'::jsonb)) candidate
        where coalesce((candidate->>'tied_minute_count')::integer, 0) > 1
      )
    );
    v_last_policy := jsonb_build_object(
      'result_id', v_result.id,
      'selection_allowed', v_result.selection_allowed,
      'confirmation_allowed', v_result.confirmation_allowed,
      'representative_time', v_result.representative_time,
      'selected_time', v_result.selected_time,
      'selection_kind', v_result.selection_kind,
      'algorithm_version', v_result.algorithm_version
    );
  end if;

  select coalesce(jsonb_agg(domain order by ordinal), '[]'::jsonb)
  into v_missing
  from unnest(array[
    'education', 'career', 'relationship', 'relocation',
    'finance', 'health', 'family'
  ]::text[]) with ordinality as required(domain, ordinal)
  where not exists (
    select 1
    from public.agentic_rectification_evidence e
    where e.case_id = p_case_id and e.status = 'confirmed' and e.domain = required.domain
  );

  insert into public.agentic_rectification_case_conversation_summaries (
    case_id,
    confirmed_evidence_summary,
    pending_revisions,
    active_focus,
    declined_skipped_topics,
    candidate_divergence_summary,
    missing_evidence_categories,
    last_result_policy,
    summary_version,
    updated_at
  ) values (
    p_case_id,
    v_confirmed,
    v_pending,
    v_active_focus,
    v_declined_skipped,
    v_candidate_divergence,
    v_missing,
    v_last_policy,
    1,
    pg_catalog.now()
  )
  on conflict (case_id) do update set
    confirmed_evidence_summary = excluded.confirmed_evidence_summary,
    pending_revisions = excluded.pending_revisions,
    active_focus = excluded.active_focus,
    declined_skipped_topics = excluded.declined_skipped_topics,
    candidate_divergence_summary = excluded.candidate_divergence_summary,
    missing_evidence_categories = excluded.missing_evidence_categories,
    last_result_policy = excluded.last_result_policy,
    summary_version = excluded.summary_version,
    updated_at = excluded.updated_at;

  v_summary := jsonb_build_object(
    'confirmed_evidence_summary', v_confirmed,
    'pending_revisions', v_pending,
    'active_focus', v_active_focus,
    'declined_skipped_topics', v_declined_skipped,
    'candidate_divergence_summary', v_candidate_divergence,
    'missing_evidence_categories', v_missing,
    'last_result_policy', v_last_policy,
    'summary_version', 1
  );
  return v_summary;
end;
$$;

revoke all on function public.refresh_agentic_rectification_case_conversation_summary(uuid)
  from public, anon, authenticated;
grant execute on function public.refresh_agentic_rectification_case_conversation_summary(uuid)
  to service_role;

create or replace function public.agentic_rectification_refresh_conversation_summary_trigger()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_old_case_id uuid := case when tg_op in ('UPDATE', 'DELETE') then old.case_id else null end;
  v_new_case_id uuid := case when tg_op in ('INSERT', 'UPDATE') then new.case_id else null end;
begin
  if v_old_case_id is not null and exists (
    select 1 from public.agentic_rectification_cases where id = v_old_case_id
  ) then
    perform public.refresh_agentic_rectification_case_conversation_summary(v_old_case_id);
  end if;

  if v_new_case_id is not null
    and v_new_case_id is distinct from v_old_case_id
    and exists (
      select 1 from public.agentic_rectification_cases where id = v_new_case_id
    ) then
    perform public.refresh_agentic_rectification_case_conversation_summary(v_new_case_id);
  end if;

  return case when tg_op = 'DELETE' then old else new end;
end;
$$;

revoke all on function public.agentic_rectification_refresh_conversation_summary_trigger()
  from public, anon, authenticated;

drop trigger if exists agentic_rectification_evidence_refresh_summary
  on public.agentic_rectification_evidence;
create trigger agentic_rectification_evidence_refresh_summary
after insert or update or delete on public.agentic_rectification_evidence
for each row execute function public.agentic_rectification_refresh_conversation_summary_trigger();

drop trigger if exists agentic_rectification_focus_refresh_summary
  on public.agentic_rectification_conversation_focuses;
create trigger agentic_rectification_focus_refresh_summary
after insert or update or delete on public.agentic_rectification_conversation_focuses
for each row execute function public.agentic_rectification_refresh_conversation_summary_trigger();

drop trigger if exists agentic_rectification_result_refresh_summary
  on public.agentic_rectification_results;
create trigger agentic_rectification_result_refresh_summary
after insert or update or delete on public.agentic_rectification_results
for each row execute function public.agentic_rectification_refresh_conversation_summary_trigger();

-- Terminal cases cannot retain an active question. Evidence/focus RPCs below
-- also reject all new writes once the Case is terminal.
create or replace function public.agentic_rectification_terminal_focus_guard()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  if new.status in ('confirmed', 'closed', 'abandoned', 'superseded')
    and old.status is distinct from new.status then
    update public.agentic_rectification_conversation_focuses
    set status = 'superseded',
        resolved_at = coalesce(resolved_at, pg_catalog.now()),
        updated_at = pg_catalog.now()
    where case_id = new.id and status = 'active';
  end if;
  return new;
end;
$$;

revoke all on function public.agentic_rectification_terminal_focus_guard()
  from public, anon, authenticated;

drop trigger if exists agentic_rectification_cases_terminal_focus_guard
  on public.agentic_rectification_cases;
create trigger agentic_rectification_cases_terminal_focus_guard
after update of status on public.agentic_rectification_cases
for each row execute function public.agentic_rectification_terminal_focus_guard();

-- Existing cases receive a durable summary before the new dossier is used.
select public.refresh_agentic_rectification_case_conversation_summary(id)
from public.agentic_rectification_cases;

-- ---------------------------------------------------------------------------
-- 5. Focus RPCs. The server, not the model, validates the active target.
-- ---------------------------------------------------------------------------

create or replace function public.set_agentic_rectification_conversation_focus(
  p_user_id uuid,
  p_case_id uuid,
  p_question_id text,
  p_intent text,
  p_target_evidence_id uuid,
  p_target_domain text,
  p_target_kind text,
  p_expected_answer_schema jsonb
)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_case public.agentic_rectification_cases%rowtype;
  v_focus public.agentic_rectification_conversation_focuses%rowtype;
begin
  if p_user_id is null or p_case_id is null
    or length(btrim(coalesce(p_question_id, ''))) = 0
    or length(btrim(coalesce(p_intent, ''))) = 0
    or p_expected_answer_schema is null
    or jsonb_typeof(p_expected_answer_schema) <> 'object' then
    raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
  end if;

  select * into v_case
  from public.agentic_rectification_cases
  where id = p_case_id and user_id = p_user_id
  for update;
  if not found then
    raise exception 'agentic_rectification_case_not_found' using errcode = 'P0001';
  end if;
  if v_case.status in ('confirmed', 'closed', 'abandoned', 'superseded') then
    raise exception 'agentic_rectification_case_terminal' using errcode = 'P0001';
  end if;
  if p_target_evidence_id is not null and not exists (
    select 1 from public.agentic_rectification_evidence
    where id = p_target_evidence_id and case_id = p_case_id
  ) then
    raise exception 'agentic_rectification_evidence_not_found' using errcode = 'P0001';
  end if;

  select * into v_focus
  from public.agentic_rectification_conversation_focuses
  where case_id = p_case_id and question_id = p_question_id;
  if found then
    if v_focus.status = 'active'
      and v_focus.intent = p_intent
      and v_focus.target_evidence_id is not distinct from p_target_evidence_id
      and v_focus.target_domain is not distinct from p_target_domain
      and v_focus.target_kind is not distinct from p_target_kind
      and v_focus.expected_answer_schema = p_expected_answer_schema then
      return jsonb_build_object(
        'focus_id', v_focus.id, 'status', v_focus.status, 'idempotent', true
      );
    end if;
    raise exception 'agentic_rectification_focus_idempotency_conflict' using errcode = 'P0001';
  end if;

  update public.agentic_rectification_conversation_focuses
  set status = 'superseded',
      resolved_at = pg_catalog.now(),
      updated_at = pg_catalog.now()
  where case_id = p_case_id and status = 'active';

  insert into public.agentic_rectification_conversation_focuses (
    case_id, question_id, intent, target_evidence_id, target_domain, target_kind,
    expected_answer_schema, status, asked_at
  ) values (
    p_case_id, p_question_id, p_intent, p_target_evidence_id, p_target_domain, p_target_kind,
    p_expected_answer_schema, 'active', pg_catalog.now()
  ) returning * into v_focus;

  return jsonb_build_object(
    'focus_id', v_focus.id,
    'status', v_focus.status,
    'idempotent', false
  );
end;
$$;

revoke all on function public.set_agentic_rectification_conversation_focus(
  uuid, uuid, text, text, uuid, text, text, jsonb
) from public, anon, authenticated;
grant execute on function public.set_agentic_rectification_conversation_focus(
  uuid, uuid, text, text, uuid, text, text, jsonb
) to service_role;

create or replace function public.resolve_agentic_rectification_conversation_focus(
  p_user_id uuid,
  p_case_id uuid,
  p_focus_id uuid,
  p_status text,
  p_evidence_id uuid
)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_case public.agentic_rectification_cases%rowtype;
  v_focus public.agentic_rectification_conversation_focuses%rowtype;
begin
  if p_user_id is null or p_case_id is null or p_focus_id is null
    or p_status not in ('resolved', 'declined', 'skipped', 'superseded') then
    raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
  end if;

  select * into v_case
  from public.agentic_rectification_cases
  where id = p_case_id and user_id = p_user_id;
  if not found then
    raise exception 'agentic_rectification_case_not_found' using errcode = 'P0001';
  end if;
  if v_case.status in ('confirmed', 'closed', 'abandoned', 'superseded') then
    raise exception 'agentic_rectification_case_terminal' using errcode = 'P0001';
  end if;

  select * into v_focus
  from public.agentic_rectification_conversation_focuses
  where id = p_focus_id and case_id = p_case_id
  for update;
  if not found then
    raise exception 'agentic_rectification_focus_not_found' using errcode = 'P0001';
  end if;
  if p_evidence_id is not null and not exists (
    select 1 from public.agentic_rectification_evidence
    where id = p_evidence_id and case_id = p_case_id
  ) then
    raise exception 'agentic_rectification_evidence_not_found' using errcode = 'P0001';
  end if;
  if v_focus.target_evidence_id is not null
    and p_evidence_id is distinct from v_focus.target_evidence_id then
    raise exception 'agentic_rectification_focus_target_mismatch' using errcode = 'P0001';
  end if;
  if v_focus.status <> 'active' then
    if v_focus.status = p_status
      and (v_focus.target_evidence_id is null or v_focus.target_evidence_id is not distinct from p_evidence_id) then
      return jsonb_build_object(
        'focus_id', v_focus.id, 'status', v_focus.status,
        'evidence_id', v_focus.target_evidence_id, 'idempotent', true
      );
    end if;
    raise exception 'agentic_rectification_focus_not_active' using errcode = 'P0001';
  end if;

  update public.agentic_rectification_conversation_focuses
  set status = p_status,
      target_evidence_id = coalesce(target_evidence_id, p_evidence_id),
      resolved_at = pg_catalog.now(),
      updated_at = pg_catalog.now()
  where id = v_focus.id;

  return jsonb_build_object(
    'focus_id', v_focus.id,
    'status', p_status,
    'evidence_id', coalesce(v_focus.target_evidence_id, p_evidence_id),
    'idempotent', false
  );
end;
$$;

revoke all on function public.resolve_agentic_rectification_conversation_focus(
  uuid, uuid, uuid, text, uuid
) from public, anon, authenticated;
grant execute on function public.resolve_agentic_rectification_conversation_focus(
  uuid, uuid, uuid, text, uuid
) to service_role;

create or replace function public.confirm_agentic_rectification_evidence_v10(
  p_user_id uuid,
  p_case_id uuid,
  p_focus_id uuid,
  p_evidence_id uuid
)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_case public.agentic_rectification_cases%rowtype;
  v_focus public.agentic_rectification_conversation_focuses%rowtype;
  v_result jsonb;
begin
  if p_user_id is null or p_case_id is null or p_focus_id is null or p_evidence_id is null then
    raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
  end if;
  select * into v_case
  from public.agentic_rectification_cases
  where id = p_case_id and user_id = p_user_id
  for update;
  if not found then
    raise exception 'agentic_rectification_case_not_found' using errcode = 'P0001';
  end if;
  if v_case.status in ('confirmed', 'closed', 'abandoned', 'superseded') then
    raise exception 'agentic_rectification_case_terminal' using errcode = 'P0001';
  end if;
  if not exists (
    select 1 from public.agentic_rectification_evidence
    where id = p_evidence_id and case_id = p_case_id
  ) then
    raise exception 'agentic_rectification_evidence_not_found' using errcode = 'P0001';
  end if;

  select * into v_focus
  from public.agentic_rectification_conversation_focuses
  where id = p_focus_id and case_id = p_case_id
  for update;
  if not found then
    raise exception 'agentic_rectification_focus_not_found' using errcode = 'P0001';
  end if;
  if v_focus.target_evidence_id is not null
    and v_focus.target_evidence_id <> p_evidence_id then
    raise exception 'agentic_rectification_focus_target_mismatch' using errcode = 'P0001';
  end if;
  if v_focus.status not in ('active', 'resolved') then
    raise exception 'agentic_rectification_focus_not_active' using errcode = 'P0001';
  end if;
  if v_focus.status = 'resolved'
    and v_focus.target_evidence_id is distinct from p_evidence_id then
    raise exception 'agentic_rectification_focus_target_mismatch' using errcode = 'P0001';
  end if;

  v_result := public.confirm_agentic_rectification_evidence(
    p_user_id, p_case_id, p_evidence_id
  );
  if v_focus.status = 'active' then
    perform public.resolve_agentic_rectification_conversation_focus(
      p_user_id, p_case_id, p_focus_id, 'resolved', p_evidence_id
    );
  end if;
  return v_result || jsonb_build_object('focus_id', p_focus_id);
end;
$$;

revoke all on function public.confirm_agentic_rectification_evidence_v10(uuid, uuid, uuid, uuid)
  from public, anon, authenticated;
grant execute on function public.confirm_agentic_rectification_evidence_v10(uuid, uuid, uuid, uuid)
  to service_role;

create or replace function public.revise_agentic_rectification_evidence_v10(
  p_user_id uuid,
  p_case_id uuid,
  p_focus_id uuid,
  p_evidence_id uuid,
  p_user_quote text,
  p_occurred_from date,
  p_occurred_to date,
  p_date_precision text,
  p_summary text
)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_case public.agentic_rectification_cases%rowtype;
  v_focus public.agentic_rectification_conversation_focuses%rowtype;
  v_existing public.agentic_rectification_evidence%rowtype;
  v_result jsonb;
begin
  if p_user_id is null or p_case_id is null or p_focus_id is null or p_evidence_id is null then
    raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
  end if;
  select * into v_case
  from public.agentic_rectification_cases
  where id = p_case_id and user_id = p_user_id
  for update;
  if not found then
    raise exception 'agentic_rectification_case_not_found' using errcode = 'P0001';
  end if;
  if v_case.status in ('confirmed', 'closed', 'abandoned', 'superseded') then
    raise exception 'agentic_rectification_case_terminal' using errcode = 'P0001';
  end if;

  select * into v_focus
  from public.agentic_rectification_conversation_focuses
  where id = p_focus_id and case_id = p_case_id
  for update;
  if not found then
    raise exception 'agentic_rectification_focus_not_found' using errcode = 'P0001';
  end if;
  if v_focus.target_evidence_id is not null
    and v_focus.target_evidence_id <> p_evidence_id then
    raise exception 'agentic_rectification_focus_target_mismatch' using errcode = 'P0001';
  end if;
  if v_focus.status not in ('active', 'resolved') then
    raise exception 'agentic_rectification_focus_not_active' using errcode = 'P0001';
  end if;

  if v_focus.status = 'resolved' then
    if v_focus.target_evidence_id is distinct from p_evidence_id then
      raise exception 'agentic_rectification_focus_target_mismatch' using errcode = 'P0001';
    end if;
    select * into v_existing
    from public.agentic_rectification_evidence
    where case_id = p_case_id
      and supersedes_evidence_id = p_evidence_id
      and user_quote = p_user_quote
      and occurred_from is not distinct from p_occurred_from
      and occurred_to is not distinct from p_occurred_to
      and date_precision = p_date_precision
      and summary = p_summary
    order by created_at desc, id desc
    limit 1;
    if not found then
      raise exception 'agentic_rectification_focus_idempotency_conflict' using errcode = 'P0001';
    end if;
    return jsonb_build_object(
      'evidence_id', v_existing.id,
      'supersedes_evidence_id', p_evidence_id,
      'focus_id', p_focus_id,
      'idempotent', true
    );
  end if;

  v_result := public.revise_agentic_rectification_evidence(
    p_user_id, p_case_id, p_evidence_id, p_user_quote,
    p_occurred_from, p_occurred_to, p_date_precision, p_summary
  );
  perform public.resolve_agentic_rectification_conversation_focus(
    p_user_id, p_case_id, p_focus_id, 'resolved', p_evidence_id
  );
  return v_result || jsonb_build_object('focus_id', p_focus_id);
end;
$$;

revoke all on function public.revise_agentic_rectification_evidence_v10(
  uuid, uuid, uuid, uuid, text, date, date, text, text
) from public, anon, authenticated;
grant execute on function public.revise_agentic_rectification_evidence_v10(
  uuid, uuid, uuid, uuid, text, date, date, text, text
) to service_role;

-- ---------------------------------------------------------------------------
-- 6. Batch evidence: per-item validation/result and transactional idempotency.
-- ---------------------------------------------------------------------------

create or replace function public.record_agentic_rectification_evidence_batch(
  p_user_id uuid,
  p_case_id uuid,
  p_source_turn_id uuid,
  p_focus_id uuid,
  p_items jsonb
)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_case public.agentic_rectification_cases%rowtype;
  v_turn public.agentic_rectification_turns%rowtype;
  v_focus public.agentic_rectification_conversation_focuses%rowtype;
  v_focus_replay boolean := false;
  v_focus_match_id uuid;
  v_focus_match_count integer := 0;
  v_focus_resolution text := 'not_requested';
  v_item jsonb;
  v_index bigint;
  v_item_key text;
  v_quote text;
  v_subject text;
  v_kind text;
  v_domain text;
  v_precision text;
  v_summary text;
  v_from date;
  v_to date;
  v_status text;
  v_outcome text;
  v_error text;
  v_clarification jsonb;
  v_existing public.agentic_rectification_evidence%rowtype;
  v_evidence_id uuid;
  v_idempotent boolean;
  v_results jsonb := '[]'::jsonb;
  v_accepted integer := 0;
  v_needs integer := 0;
  v_rejected integer := 0;
begin
  if p_user_id is null or p_case_id is null or p_source_turn_id is null
    or p_items is null or jsonb_typeof(p_items) <> 'array'
    or jsonb_array_length(p_items) = 0 or jsonb_array_length(p_items) > 12 then
    raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
  end if;

  select * into v_case
  from public.agentic_rectification_cases
  where id = p_case_id and user_id = p_user_id
  for update;
  if not found then
    raise exception 'agentic_rectification_case_not_found' using errcode = 'P0001';
  end if;
  if v_case.status in ('confirmed', 'closed', 'abandoned', 'superseded') then
    raise exception 'agentic_rectification_case_terminal' using errcode = 'P0001';
  end if;

  select * into v_turn
  from public.agentic_rectification_turns
  where id = p_source_turn_id and case_id = p_case_id;
  if not found then
    raise exception 'agentic_rectification_turn_not_found' using errcode = 'P0001';
  end if;
  if v_turn.user_message is null then
    raise exception 'agentic_rectification_quote_not_grounded' using errcode = 'P0001';
  end if;

  if p_focus_id is not null then
    select * into v_focus
    from public.agentic_rectification_conversation_focuses
    where id = p_focus_id and case_id = p_case_id
    for update;
    if not found or v_focus.status not in ('active', 'resolved') then
      raise exception 'agentic_rectification_focus_not_active' using errcode = 'P0001';
    end if;
    v_focus_replay := v_focus.status = 'resolved';
    if not v_focus_replay and v_focus.target_evidence_id is not null then
      raise exception 'agentic_rectification_focus_target_mismatch' using errcode = 'P0001';
    end if;
    v_focus_resolution := case when v_focus_replay then 'idempotent' else 'active' end;
    if v_focus_replay and exists (
      select 1
      from jsonb_array_elements(p_items) item
      where length(btrim(coalesce(item->>'idempotency_key', ''))) = 0
        or not exists (
          select 1 from public.agentic_rectification_evidence e
          where e.case_id = p_case_id
            and e.idempotency_key = btrim(item->>'idempotency_key')
        )
    ) then
      raise exception 'agentic_rectification_focus_not_active' using errcode = 'P0001';
    end if;
  end if;

  for v_item, v_index in
    select value, ordinality
    from jsonb_array_elements(p_items) with ordinality
  loop
    v_item_key := btrim(coalesce(v_item->>'idempotency_key', ''));
    v_quote := btrim(coalesce(v_item->>'quote', ''));
    v_subject := coalesce(v_item->>'subject', '');
    v_kind := coalesce(v_item->>'event_kind', '');
    v_domain := coalesce(v_item->>'domain', '');
    v_precision := coalesce(v_item->>'date_precision', '');
    v_summary := btrim(coalesce(v_item->>'summary', ''));
    v_from := null;
    v_to := null;
    v_error := null;
    v_clarification := '[]'::jsonb;
    v_evidence_id := null;
    v_idempotent := false;

    begin
      if nullif(v_item->>'occurred_from', '') is not null then
        v_from := (v_item->>'occurred_from')::date;
      end if;
      if nullif(v_item->>'occurred_to', '') is not null then
        v_to := (v_item->>'occurred_to')::date;
      end if;
    exception when others then
      v_error := 'invalid_date';
    end;

    if v_error is null and (
      length(v_item_key) = 0 or length(v_item_key) > 160
      or length(v_quote) = 0 or length(v_summary) = 0
      or v_subject not in ('self', 'family', 'other')
      or v_kind not in (
        'education_start', 'education_completion', 'education_interruption',
        'career_entry', 'career_change', 'promotion', 'career_pressure', 'career_exit',
        'relationship_start', 'relationship_commitment', 'relationship_separation',
        'relocation', 'finance_gain', 'finance_loss',
        'self_health_event', 'family_event', 'other'
      )
      or v_domain not in ('education', 'career', 'relationship', 'relocation', 'finance', 'health', 'family', 'other')
      or v_precision not in ('year', 'month', 'day', 'range', 'unknown')
    ) then
      v_error := 'invalid_item';
    end if;

    if v_error is null and position(
      public.agentic_rectification_normalize_quote(v_quote)
      in public.agentic_rectification_normalize_quote(v_turn.user_message)
    ) = 0 then
      v_error := 'quote_not_grounded';
    end if;

    if v_error is null and v_precision = 'range'
      and (v_from is null or v_to is null or v_from > v_to) then
      v_error := 'invalid_range';
    end if;

    if v_error is not null then
      v_outcome := 'rejected';
      v_status := 'rejected';
      v_rejected := v_rejected + 1;
    else
      select * into v_existing
      from public.agentic_rectification_evidence
      where case_id = p_case_id and idempotency_key = v_item_key;

      if found then
        if v_existing.source_turn_id is distinct from p_source_turn_id
          or v_existing.user_quote is distinct from v_quote
          or v_existing.subject is distinct from v_subject
          or v_existing.event_kind is distinct from v_kind
          or v_existing.domain is distinct from v_domain
          or v_existing.occurred_from is distinct from v_from
          or v_existing.occurred_to is distinct from v_to
          or v_existing.date_precision is distinct from v_precision
          or v_existing.summary is distinct from v_summary then
          v_outcome := 'rejected';
          v_status := 'rejected';
          v_error := 'idempotency_conflict';
          v_rejected := v_rejected + 1;
        else
          v_evidence_id := v_existing.id;
          v_idempotent := true;
          if v_existing.status = 'confirmed' then
            v_outcome := 'accepted';
            v_status := 'confirmed';
            v_accepted := v_accepted + 1;
          else
            v_outcome := 'needs_clarification';
            v_status := v_existing.status;
            v_clarification := case
              when v_existing.date_precision = 'unknown' then '["date"]'::jsonb
              else '[]'::jsonb
            end;
            v_needs := v_needs + 1;
          end if;
        end if;
      else
        if v_precision = 'unknown' or v_from is null then
          v_outcome := 'needs_clarification';
          v_status := 'draft';
          v_clarification := '["date"]'::jsonb;
          v_needs := v_needs + 1;
        else
          v_outcome := 'accepted';
          v_status := 'confirmed';
          v_accepted := v_accepted + 1;
        end if;

        insert into public.agentic_rectification_evidence (
          case_id, source_turn_id, user_quote, subject, event_kind, domain,
          occurred_from, occurred_to, date_precision, summary, status,
          confirmed_at, idempotency_key
        ) values (
          p_case_id, p_source_turn_id, v_quote, v_subject, v_kind, v_domain,
          v_from, v_to, v_precision, v_summary, v_status,
          case when v_status = 'confirmed' then pg_catalog.now() else null end,
          v_item_key
        ) returning id into v_evidence_id;
      end if;
    end if;

    if p_focus_id is not null
      and v_outcome = 'accepted'
      and v_evidence_id is not null
      and (v_focus.target_domain is null or v_domain = v_focus.target_domain)
      and (v_focus.target_kind is null or v_kind = v_focus.target_kind) then
      v_focus_match_count := v_focus_match_count + 1;
      if v_focus_match_id is null then
        v_focus_match_id := v_evidence_id;
      end if;
    end if;

    v_results := v_results || jsonb_build_array(jsonb_build_object(
      'index', v_index - 1,
      'idempotency_key', nullif(v_item_key, ''),
      'outcome', v_outcome,
      'evidence_id', v_evidence_id,
      'status', v_status,
      'idempotent', v_idempotent,
      'clarification_fields', v_clarification,
      'error_code', v_error
    ));
  end loop;

  update public.agentic_rectification_cases
  set status = case when status = 'draft' and v_accepted > 0 then 'collecting_evidence' else status end,
      last_activity_at = pg_catalog.now(),
      updated_at = pg_catalog.now()
  where id = p_case_id;

  if p_focus_id is not null then
    if v_focus_replay then
      if v_focus_match_count <> 1
        or v_focus_match_id is distinct from v_focus.target_evidence_id then
        raise exception 'agentic_rectification_focus_idempotency_conflict' using errcode = 'P0001';
      end if;
      v_focus_resolution := 'idempotent';
    elsif v_focus_match_count = 1 then
      perform public.resolve_agentic_rectification_conversation_focus(
        p_user_id, p_case_id, p_focus_id, 'resolved', v_focus_match_id
      );
      v_focus_resolution := 'resolved';
    elsif v_focus_match_count > 1 then
      -- Do not guess which evidence answered the question. Keep the focus active
      -- and make the ambiguity explicit to the caller.
      v_focus_resolution := 'ambiguous';
    else
      v_focus_resolution := 'unmatched';
    end if;
  end if;

  return jsonb_build_object(
    'items', v_results,
    'accepted_count', v_accepted,
    'needs_clarification_count', v_needs,
    'rejected_count', v_rejected,
    'focus_id', p_focus_id,
    'focus_evidence_id', v_focus_match_id,
    'focus_resolution', v_focus_resolution
  );
end;
$$;

revoke all on function public.record_agentic_rectification_evidence_batch(
  uuid, uuid, uuid, uuid, jsonb
) from public, anon, authenticated;
grant execute on function public.record_agentic_rectification_evidence_batch(
  uuid, uuid, uuid, uuid, jsonb
) to service_role;

-- ---------------------------------------------------------------------------
-- 7. Attempt lifecycle and attempt-aware receipt insertions.
-- ---------------------------------------------------------------------------

-- Request-aware turn append. The seven-argument V9 function remains available.
create or replace function public.append_agentic_rectification_turn(
  p_user_id uuid,
  p_case_id uuid,
  p_user_message text,
  p_assistant_message text,
  p_model_name text,
  p_model_version text,
  p_status text,
  p_request_id uuid
)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_case public.agentic_rectification_cases%rowtype;
  v_turn public.agentic_rectification_turns%rowtype;
begin
  if p_user_id is null or p_case_id is null or p_request_id is null
    or length(btrim(coalesce(p_model_name, ''))) = 0
    or p_status not in ('pending', 'completed', 'failed', 'retryable') then
    raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
  end if;
  if p_status = 'completed'
    and (p_assistant_message is null or length(btrim(p_assistant_message)) = 0) then
    raise exception 'agentic_rectification_turn_incomplete' using errcode = 'P0001';
  end if;

  select * into v_case
  from public.agentic_rectification_cases
  where id = p_case_id and user_id = p_user_id
  for update;
  if not found then
    raise exception 'agentic_rectification_case_not_found' using errcode = 'P0001';
  end if;

  select * into v_turn
  from public.agentic_rectification_turns
  where case_id = p_case_id and request_id = p_request_id
  for update;
  if found then
    if v_turn.user_message is distinct from p_user_message
      or v_turn.model_name is distinct from p_model_name
      or v_turn.model_version is distinct from p_model_version then
      raise exception 'agentic_rectification_request_mismatch' using errcode = 'P0001';
    end if;
    return jsonb_build_object(
      'turn_id', v_turn.id,
      'status', v_turn.status,
      'assistant_message', v_turn.assistant_message,
      'successful_attempt_id', v_turn.successful_attempt_id,
      'should_execute', false,
      'already_in_progress', v_turn.status = 'pending',
      'idempotent', true
    );
  end if;

  if v_case.status in ('confirmed', 'closed', 'abandoned', 'superseded') then
    raise exception 'agentic_rectification_case_terminal' using errcode = 'P0001';
  end if;

  insert into public.agentic_rectification_turns (
    case_id, request_id, user_message, assistant_message, status,
    model_name, model_version, completed_at
  ) values (
    p_case_id, p_request_id, p_user_message, p_assistant_message, p_status,
    p_model_name, p_model_version,
    case when p_status = 'completed' then pg_catalog.now() else null end
  ) returning * into v_turn;

  update public.agentic_rectification_cases
  set last_activity_at = pg_catalog.now(),
      updated_at = pg_catalog.now()
  where id = p_case_id;

  return jsonb_build_object(
    'turn_id', v_turn.id,
    'status', v_turn.status,
    'assistant_message', v_turn.assistant_message,
    'successful_attempt_id', v_turn.successful_attempt_id,
    'should_execute', true,
    'already_in_progress', false,
    'idempotent', false
  );
end;
$$;

revoke all on function public.append_agentic_rectification_turn(
  uuid, uuid, text, text, text, text, text, uuid
) from public, anon, authenticated;
grant execute on function public.append_agentic_rectification_turn(
  uuid, uuid, text, text, text, text, text, uuid
) to service_role;

-- V10 request idempotency is mandatory for every runtime Turn write. Keep the
-- V9 overload for migration compatibility, but remove it from service runtime.
revoke execute on function public.append_agentic_rectification_turn(
  uuid, uuid, text, text, text, text, text
) from service_role;

create or replace function public.create_agentic_rectification_run_attempt(
  p_user_id uuid,
  p_case_id uuid,
  p_turn_id uuid,
  p_attempt_number integer
)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_case public.agentic_rectification_cases%rowtype;
  v_turn public.agentic_rectification_turns%rowtype;
  v_attempt public.agentic_rectification_run_attempts%rowtype;
  v_inserted boolean := false;
begin
  if p_user_id is null or p_case_id is null or p_turn_id is null
    or coalesce(p_attempt_number, 0) <= 0 then
    raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
  end if;

  select * into v_case
  from public.agentic_rectification_cases
  where id = p_case_id and user_id = p_user_id
  for update;
  if not found then
    raise exception 'agentic_rectification_case_not_found' using errcode = 'P0001';
  end if;
  select * into v_turn
  from public.agentic_rectification_turns
  where id = p_turn_id and case_id = p_case_id
  for update;
  if not found then
    raise exception 'agentic_rectification_turn_not_found' using errcode = 'P0001';
  end if;

  -- A terminal Case must not create a new attempt, but a worker recovering
  -- after a confirm/close action still needs the identity of the attempt that
  -- already owned execution so it can finish the durable Turn projection.
  select * into v_attempt
  from public.agentic_rectification_run_attempts
  where turn_id = p_turn_id and attempt_number = p_attempt_number
  for update;
  if found then
    return jsonb_build_object(
      'attempt_id', v_attempt.id,
      'attempt_number', v_attempt.attempt_number,
      'status', v_attempt.status,
      'should_execute', false,
      'already_in_progress', v_attempt.status = 'started',
      'idempotent', true
    );
  end if;

  if v_case.status in ('confirmed', 'closed', 'abandoned', 'superseded') then
    raise exception 'agentic_rectification_case_terminal' using errcode = 'P0001';
  end if;

  insert into public.agentic_rectification_run_attempts (
    case_id, turn_id, attempt_number, status
  ) values (
    p_case_id, p_turn_id, p_attempt_number, 'started'
  )
  on conflict (turn_id, attempt_number) do nothing
  returning * into v_attempt;
  v_inserted := found;

  if not v_inserted then
    select * into v_attempt
    from public.agentic_rectification_run_attempts
    where turn_id = p_turn_id and attempt_number = p_attempt_number
    for update;
  end if;

  return jsonb_build_object(
    'attempt_id', v_attempt.id,
    'attempt_number', v_attempt.attempt_number,
    'status', v_attempt.status,
    'should_execute', v_inserted,
    'already_in_progress', not v_inserted and v_attempt.status = 'started',
    'idempotent', not v_inserted
  );
end;
$$;

revoke all on function public.create_agentic_rectification_run_attempt(uuid, uuid, uuid, integer)
  from public, anon, authenticated;
grant execute on function public.create_agentic_rectification_run_attempt(uuid, uuid, uuid, integer)
  to service_role;

create or replace function public.finalize_agentic_rectification_run_attempt(
  p_user_id uuid,
  p_case_id uuid,
  p_turn_id uuid,
  p_attempt_id uuid,
  p_status text,
  p_error_code text,
  p_usage jsonb
)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_case public.agentic_rectification_cases%rowtype;
  v_attempt public.agentic_rectification_run_attempts%rowtype;
  v_error_code text := nullif(btrim(coalesce(p_error_code, '')), '');
begin
  if p_user_id is null or p_case_id is null or p_turn_id is null or p_attempt_id is null
    or p_status not in ('completed', 'failed', 'retryable', 'aborted')
    or p_usage is null or jsonb_typeof(p_usage) <> 'object' then
    raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
  end if;

  select * into v_case
  from public.agentic_rectification_cases
  where id = p_case_id and user_id = p_user_id
  for update;
  if not found then
    raise exception 'agentic_rectification_case_not_found' using errcode = 'P0001';
  end if;
  select * into v_attempt
  from public.agentic_rectification_run_attempts
  where id = p_attempt_id and case_id = p_case_id and turn_id = p_turn_id
  for update;
  if not found then
    raise exception 'agentic_rectification_attempt_not_found' using errcode = 'P0001';
  end if;
  if v_attempt.status <> 'started' then
    if v_attempt.status = p_status
      and v_attempt.error_code is not distinct from v_error_code
      and v_attempt.usage = p_usage then
      return jsonb_build_object(
        'attempt_id', v_attempt.id, 'status', v_attempt.status, 'idempotent', true
      );
    end if;
    raise exception 'agentic_rectification_attempt_already_finalized' using errcode = 'P0001';
  end if;

  if p_status = 'completed' then
    if not exists (
      select 1 from public.agentic_rectification_run_phases
      where attempt_id = p_attempt_id
        and case_id = p_case_id
        and turn_id = p_turn_id
        and phase = 'billing.settled'
    ) or not exists (
      select 1 from public.agentic_rectification_run_phases
      where attempt_id = p_attempt_id
        and case_id = p_case_id
        and turn_id = p_turn_id
        and phase = 'run.completed'
    ) then
      raise exception 'agentic_rectification_attempt_completion_receipt_missing' using errcode = 'P0001';
    end if;

    if not exists (
      select 1
      from public.agentic_rectification_skill_run_receipts receipt
      where receipt.case_id = p_case_id
        and receipt.turn_id = p_turn_id
        and receipt.request_id = p_attempt_id
        and receipt.run_kind = 'turn'
        and receipt.user_id = p_user_id
        and receipt.skill_name = v_case.skill_name
        and receipt.skill_version = v_case.skill_version
        and receipt.skill_sha256 = v_case.skill_sha256
        and receipt.source_commit is not distinct from v_case.skill_source_commit
    ) then
      raise exception 'agentic_rectification_skill_receipt_missing' using errcode = 'P0001';
    end if;
  end if;

  update public.agentic_rectification_run_attempts
  set status = p_status,
      error_code = v_error_code,
      usage = p_usage,
      completed_at = pg_catalog.now(),
      updated_at = pg_catalog.now()
  where id = p_attempt_id;

  return jsonb_build_object(
    'attempt_id', p_attempt_id, 'status', p_status, 'idempotent', false
  );
end;
$$;

revoke all on function public.finalize_agentic_rectification_run_attempt(
  uuid, uuid, uuid, uuid, text, text, jsonb
) from public, anon, authenticated;
grant execute on function public.finalize_agentic_rectification_run_attempt(
  uuid, uuid, uuid, uuid, text, text, jsonb
) to service_role;

-- New overload. The six-argument V9 function remains untouched.
create or replace function public.insert_agentic_rectification_run_phase(
  p_user_id uuid,
  p_case_id uuid,
  p_turn_id uuid,
  p_phase text,
  p_tool_name text,
  p_sequence integer,
  p_attempt_id uuid
)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_attempt public.agentic_rectification_run_attempts%rowtype;
  v_phase_id uuid;
begin
  if p_user_id is null or p_case_id is null or p_turn_id is null or p_attempt_id is null
    or p_phase not in (
      'run.started', 'skill.started', 'skill.loaded', 'skill.bound', 'case.loaded',
      'intent.classified', 'evidence.proposed', 'evidence.confirmed',
      'candidates.comparing', 'candidates.updated', 'diagnostics.completed',
      'candidate.accepted', 'birth_time.confirmed', 'answer.composed',
      'billing.settled', 'answer.delta', 'run.completed', 'run.failed'
    ) then
    raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
  end if;
  if not exists (
    select 1 from public.agentic_rectification_cases
    where id = p_case_id and user_id = p_user_id
  ) then
    raise exception 'agentic_rectification_case_not_found' using errcode = 'P0001';
  end if;
  if not exists (
    select 1 from public.agentic_rectification_turns
    where id = p_turn_id and case_id = p_case_id
  ) then
    raise exception 'agentic_rectification_turn_not_found' using errcode = 'P0001';
  end if;

  select * into v_attempt
  from public.agentic_rectification_run_attempts
  where id = p_attempt_id and case_id = p_case_id and turn_id = p_turn_id
  for update;
  if not found then
    raise exception 'agentic_rectification_attempt_not_found' using errcode = 'P0001';
  end if;
  if v_attempt.status <> 'started' then
    raise exception 'agentic_rectification_attempt_not_started' using errcode = 'P0001';
  end if;

  insert into public.agentic_rectification_run_phases (
    case_id, turn_id, attempt_id, phase, tool_name, sequence
  ) values (
    p_case_id, p_turn_id, p_attempt_id, p_phase, p_tool_name, coalesce(p_sequence, 0)
  ) returning id into v_phase_id;

  return jsonb_build_object('phase_id', v_phase_id, 'attempt_id', p_attempt_id);
end;
$$;

revoke all on function public.insert_agentic_rectification_run_phase(
  uuid, uuid, uuid, text, text, integer, uuid
) from public, anon, authenticated;
grant execute on function public.insert_agentic_rectification_run_phase(
  uuid, uuid, uuid, text, text, integer, uuid
) to service_role;

-- New overload. The eleven-argument V9 function remains untouched.
create or replace function public.insert_agentic_rectification_tool_receipt(
  p_user_id uuid,
  p_case_id uuid,
  p_turn_id uuid,
  p_tool_name text,
  p_public_phase text,
  p_status text,
  p_input_fingerprint text,
  p_result_fingerprint text,
  p_engine_version text,
  p_safe_error_code text,
  p_executed_methods jsonb,
  p_attempt_id uuid
)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_attempt public.agentic_rectification_run_attempts%rowtype;
  v_receipt_id uuid;
begin
  if p_user_id is null or p_case_id is null or p_turn_id is null or p_attempt_id is null
    or p_tool_name not in (
      'rectification-read-case', 'rectification-set-focus',
      'rectification-resolve-focus', 'rectification-record-evidence-batch',
      'rectification-propose-evidence', 'rectification-confirm-evidence',
      'rectification-revise-evidence', 'rectification-compare-candidates',
      'rectification-read-diagnostics', 'rectification-offer-candidates',
      'rectification-accept-candidate', 'rectification-confirm-birth-time',
      'rectification-close-case'
    )
    or p_public_phase not in (
      'run.started', 'skill.started', 'skill.loaded', 'skill.bound', 'case.loaded',
      'intent.classified', 'evidence.proposed', 'evidence.confirmed',
      'candidates.comparing', 'candidates.updated', 'diagnostics.completed',
      'candidate.accepted', 'birth_time.confirmed', 'answer.composed',
      'billing.settled', 'answer.delta', 'run.completed', 'run.failed'
    )
    or p_status not in ('started', 'completed', 'failed', 'skipped')
    or p_executed_methods is null or jsonb_typeof(p_executed_methods) <> 'array'
    or exists (
      select 1 from jsonb_array_elements_text(p_executed_methods) method(value)
      where method.value not in (
        'd1-rashi', 'd2-hora', 'd4-chaturthamsha', 'd9-navamsa',
        'd10-dashamsa', 'd11-labhamsha', 'd24-chaturvimshamsha',
        'd30-trimshamsha', 'vimshottari-dasha', 'narayana-dasha',
        'gochara', 'ashtakavarga', 'shadbala', 'arudha-pada',
        'functional-benefic-malefic'
      )
    ) then
    raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
  end if;
  if not exists (
    select 1 from public.agentic_rectification_cases
    where id = p_case_id and user_id = p_user_id
  ) then
    raise exception 'agentic_rectification_case_not_found' using errcode = 'P0001';
  end if;
  if not exists (
    select 1 from public.agentic_rectification_turns
    where id = p_turn_id and case_id = p_case_id
  ) then
    raise exception 'agentic_rectification_turn_not_found' using errcode = 'P0001';
  end if;

  select * into v_attempt
  from public.agentic_rectification_run_attempts
  where id = p_attempt_id and case_id = p_case_id and turn_id = p_turn_id
  for update;
  if not found then
    raise exception 'agentic_rectification_attempt_not_found' using errcode = 'P0001';
  end if;
  if v_attempt.status <> 'started' then
    raise exception 'agentic_rectification_attempt_not_started' using errcode = 'P0001';
  end if;

  insert into public.agentic_rectification_tool_receipts (
    case_id, turn_id, attempt_id, tool_name, public_phase, status,
    input_fingerprint, result_fingerprint, engine_version, safe_error_code,
    executed_methods, completed_at
  ) values (
    p_case_id, p_turn_id, p_attempt_id, p_tool_name, p_public_phase, p_status,
    p_input_fingerprint, p_result_fingerprint, p_engine_version, p_safe_error_code,
    p_executed_methods,
    case when p_status = 'completed' then pg_catalog.now() else null end
  ) returning id into v_receipt_id;

  return jsonb_build_object('receipt_id', v_receipt_id, 'attempt_id', p_attempt_id);
end;
$$;

revoke all on function public.insert_agentic_rectification_tool_receipt(
  uuid, uuid, uuid, text, text, text, text, text, text, text, jsonb, uuid
) from public, anon, authenticated;
grant execute on function public.insert_agentic_rectification_tool_receipt(
  uuid, uuid, uuid, text, text, text, text, text, text, text, jsonb, uuid
) to service_role;

-- Attempt-aware finalization. The five-argument V9 function remains available.
create or replace function public.finalize_agentic_rectification_turn(
  p_user_id uuid,
  p_case_id uuid,
  p_turn_id uuid,
  p_status text,
  p_assistant_message text,
  p_successful_attempt_id uuid
)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_turn public.agentic_rectification_turns%rowtype;
  v_attempt public.agentic_rectification_run_attempts%rowtype;
begin
  if p_user_id is null or p_case_id is null or p_turn_id is null
    or p_status not in ('completed', 'failed', 'retryable') then
    raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
  end if;
  if not exists (
    select 1 from public.agentic_rectification_cases
    where id = p_case_id and user_id = p_user_id
  ) then
    raise exception 'agentic_rectification_case_not_found' using errcode = 'P0001';
  end if;

  select * into v_turn
  from public.agentic_rectification_turns
  where id = p_turn_id and case_id = p_case_id
  for update;
  if not found then
    raise exception 'agentic_rectification_turn_not_found' using errcode = 'P0001';
  end if;

  if v_turn.status = 'completed' then
    if p_status = 'completed'
      and v_turn.successful_attempt_id is not distinct from p_successful_attempt_id
      and v_turn.assistant_message is not distinct from p_assistant_message then
      return jsonb_build_object(
        'turn_id', p_turn_id,
        'status', v_turn.status,
        'successful_attempt_id', v_turn.successful_attempt_id,
        'idempotent', true
      );
    end if;
    raise exception 'agentic_rectification_turn_already_completed' using errcode = 'P0001';
  end if;

  if p_status = 'completed' then
    if p_successful_attempt_id is null
      or p_assistant_message is null
      or length(btrim(p_assistant_message)) = 0 then
      raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
    end if;
    select * into v_attempt
    from public.agentic_rectification_run_attempts
    where id = p_successful_attempt_id
      and case_id = p_case_id
      and turn_id = p_turn_id
      and status = 'completed';
    if not found then
      raise exception 'agentic_rectification_attempt_not_successful' using errcode = 'P0001';
    end if;
  elsif p_successful_attempt_id is not null or p_assistant_message is not null then
    raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
  end if;

  update public.agentic_rectification_turns
  set status = p_status,
      assistant_message = case when p_status = 'completed' then p_assistant_message else null end,
      successful_attempt_id = case when p_status = 'completed' then p_successful_attempt_id else null end,
      completed_at = case when p_status = 'completed' then pg_catalog.now() else null end,
      updated_at = pg_catalog.now()
  where id = p_turn_id and status <> 'completed';

  return jsonb_build_object(
    'turn_id', p_turn_id,
    'status', p_status,
    'successful_attempt_id', case when p_status = 'completed' then p_successful_attempt_id else null end,
    'idempotent', false
  );
end;
$$;

revoke all on function public.finalize_agentic_rectification_turn(
  uuid, uuid, uuid, text, text, uuid
) from public, anon, authenticated;

-- Legacy finalizers cannot prove latest-attempt ownership. Retain their
-- definitions for additive migration compatibility, but make the V10 runtime
-- use only the attempt-aware overload below.
revoke execute on function public.finalize_agentic_rectification_turn(
  uuid, uuid, uuid, text, text
) from service_role;
revoke execute on function public.finalize_agentic_rectification_turn(
  uuid, uuid, uuid, text, text, uuid
) from service_role;

-- Attempt-aware monotonic finalization used by the V10 runner.
create or replace function public.finalize_agentic_rectification_turn(
  p_user_id uuid,
  p_case_id uuid,
  p_turn_id uuid,
  p_attempt_id uuid,
  p_status text,
  p_assistant_message text,
  p_successful_attempt_id uuid
)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_turn public.agentic_rectification_turns%rowtype;
  v_attempt public.agentic_rectification_run_attempts%rowtype;
  v_latest_attempt_id uuid;
begin
  if p_user_id is null or p_case_id is null or p_turn_id is null or p_attempt_id is null
    or p_status not in ('completed', 'failed', 'retryable') then
    raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
  end if;
  if not exists (
    select 1 from public.agentic_rectification_cases
    where id = p_case_id and user_id = p_user_id
  ) then
    raise exception 'agentic_rectification_case_not_found' using errcode = 'P0001';
  end if;

  select * into v_turn
  from public.agentic_rectification_turns
  where id = p_turn_id and case_id = p_case_id
  for update;
  if not found then
    raise exception 'agentic_rectification_turn_not_found' using errcode = 'P0001';
  end if;

  if v_turn.status = 'completed' then
    if p_status = 'completed'
      and p_attempt_id = p_successful_attempt_id
      and v_turn.successful_attempt_id = p_successful_attempt_id
      and v_turn.assistant_message is not distinct from p_assistant_message then
      return jsonb_build_object(
        'turn_id', p_turn_id,
        'status', v_turn.status,
        'successful_attempt_id', v_turn.successful_attempt_id,
        'idempotent', true
      );
    end if;
    raise exception 'agentic_rectification_turn_already_completed' using errcode = 'P0001';
  end if;

  select * into v_attempt
  from public.agentic_rectification_run_attempts
  where id = p_attempt_id and case_id = p_case_id and turn_id = p_turn_id
  for update;
  if not found then
    raise exception 'agentic_rectification_attempt_not_found' using errcode = 'P0001';
  end if;

  select id into v_latest_attempt_id
  from public.agentic_rectification_run_attempts
  where case_id = p_case_id and turn_id = p_turn_id
  order by attempt_number desc
  limit 1;
  if v_latest_attempt_id is distinct from p_attempt_id then
    raise exception 'agentic_rectification_attempt_superseded' using errcode = 'P0001';
  end if;

  if p_status = 'completed' then
    if p_successful_attempt_id is distinct from p_attempt_id
      or p_assistant_message is null
      or length(btrim(p_assistant_message)) = 0
      or v_attempt.status <> 'completed' then
      raise exception 'agentic_rectification_attempt_not_successful' using errcode = 'P0001';
    end if;
  else
    if p_successful_attempt_id is not null or p_assistant_message is not null
      or v_attempt.status <> p_status then
      raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
    end if;
    if v_turn.status = p_status then
      return jsonb_build_object(
        'turn_id', p_turn_id,
        'status', v_turn.status,
        'successful_attempt_id', null,
        'idempotent', true
      );
    end if;
  end if;

  update public.agentic_rectification_turns
  set status = p_status,
      assistant_message = case when p_status = 'completed' then p_assistant_message else null end,
      successful_attempt_id = case when p_status = 'completed' then p_successful_attempt_id else null end,
      completed_at = case when p_status = 'completed' then pg_catalog.now() else null end,
      updated_at = pg_catalog.now()
  where id = p_turn_id and status <> 'completed';

  return jsonb_build_object(
    'turn_id', p_turn_id,
    'status', p_status,
    'successful_attempt_id', case when p_status = 'completed' then p_successful_attempt_id else null end,
    'idempotent', false
  );
end;
$$;

revoke all on function public.finalize_agentic_rectification_turn(
  uuid, uuid, uuid, uuid, text, text, uuid
) from public, anon, authenticated;
grant execute on function public.finalize_agentic_rectification_turn(
  uuid, uuid, uuid, uuid, text, text, uuid
) to service_role;

-- ---------------------------------------------------------------------------
-- 8. Attempt-filtered turn receipt and bounded dossier with durable summary.
-- ---------------------------------------------------------------------------

create or replace function public.get_agentic_rectification_turn_receipt(
  p_user_id uuid,
  p_case_id uuid,
  p_turn_id uuid
)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_turn public.agentic_rectification_turns%rowtype;
  v_case public.agentic_rectification_cases%rowtype;
  v_attempt_id uuid;
  v_phases jsonb;
  v_tools jsonb;
  v_methods jsonb;
  v_engine_version text;
begin
  if p_user_id is null or p_case_id is null or p_turn_id is null then
    raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
  end if;
  select * into v_case
  from public.agentic_rectification_cases
  where id = p_case_id and user_id = p_user_id;
  if not found then
    raise exception 'agentic_rectification_case_not_found' using errcode = 'P0001';
  end if;
  select * into v_turn
  from public.agentic_rectification_turns
  where id = p_turn_id and case_id = p_case_id;
  if not found then
    raise exception 'agentic_rectification_turn_not_found' using errcode = 'P0001';
  end if;

  v_attempt_id := v_turn.successful_attempt_id;
  if v_attempt_id is null then
    select id into v_attempt_id
    from public.agentic_rectification_run_attempts
    where turn_id = p_turn_id and status = 'completed'
    order by completed_at desc, attempt_number desc
    limit 1;
  end if;

  select coalesce(jsonb_agg(
    jsonb_build_object('phase', rp.phase, 'tool', rp.tool_name)
    order by rp.sequence, rp.created_at, rp.id
  ), '[]'::jsonb) into v_phases
  from public.agentic_rectification_run_phases rp
  where rp.turn_id = p_turn_id
    and (
      (v_attempt_id is not null and rp.attempt_id = v_attempt_id)
      or (v_attempt_id is null and rp.attempt_id is null)
    );

  select coalesce(jsonb_agg(tool_name order by tool_name), '[]'::jsonb) into v_tools
  from (
    select distinct tr.tool_name
    from public.agentic_rectification_tool_receipts tr
    where tr.turn_id = p_turn_id and tr.status = 'completed'
      and (
        (v_attempt_id is not null and tr.attempt_id = v_attempt_id)
        or (v_attempt_id is null and tr.attempt_id is null)
      )
  ) tools;

  select coalesce(jsonb_agg(method order by method), '[]'::jsonb) into v_methods
  from (
    select distinct jsonb_array_elements_text(tr.executed_methods) as method
    from public.agentic_rectification_tool_receipts tr
    where tr.turn_id = p_turn_id and tr.status = 'completed'
      and (
        (v_attempt_id is not null and tr.attempt_id = v_attempt_id)
        or (v_attempt_id is null and tr.attempt_id is null)
      )
  ) methods;

  select max(tr.engine_version) into v_engine_version
  from public.agentic_rectification_tool_receipts tr
  where tr.turn_id = p_turn_id and tr.engine_version is not null
    and (
      (v_attempt_id is not null and tr.attempt_id = v_attempt_id)
      or (v_attempt_id is null and tr.attempt_id is null)
    );

  return jsonb_build_object(
    'turn_id', v_turn.id,
    'attempt_id', v_attempt_id,
    'status', v_turn.status,
    'skill_name', v_case.skill_name,
    'skill_version', v_case.skill_version,
    'engine_version', v_engine_version,
    'phases', v_phases,
    'tools', v_tools,
    'methods', v_methods,
    'started_at', v_turn.created_at,
    'completed_at', v_turn.completed_at
  );
end;
$$;

revoke all on function public.get_agentic_rectification_turn_receipt(uuid, uuid, uuid)
  from public, anon, authenticated;
grant execute on function public.get_agentic_rectification_turn_receipt(uuid, uuid, uuid)
  to service_role;

create or replace function public.get_agentic_rectification_case_dossier(
  p_user_id uuid,
  p_case_id uuid
)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_case public.agentic_rectification_cases%rowtype;
  v_turns jsonb;
  v_evidence jsonb;
  v_summary public.agentic_rectification_case_conversation_summaries%rowtype;
  v_result public.agentic_rectification_results%rowtype;
  v_evidence_count bigint;
  v_turn_count bigint;
begin
  if p_user_id is null or p_case_id is null then
    raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
  end if;
  select * into v_case
  from public.agentic_rectification_cases
  where id = p_case_id and user_id = p_user_id;
  if not found then
    raise exception 'agentic_rectification_case_not_found' using errcode = 'P0001';
  end if;

  select coalesce(jsonb_agg(jsonb_build_object(
    'id', recent.id,
    'role', message.role,
    'text', message.text,
    'status', recent.status,
    'created_at', recent.created_at,
    'completed_at', recent.completed_at
  ) order by recent.created_at, recent.id, message.ordinal), '[]'::jsonb)
  into v_turns
  from (
    select t.*
    from public.agentic_rectification_turns t
    where t.case_id = v_case.id
    order by t.created_at desc, t.id desc
    limit 50
  ) recent
  cross join lateral (
    values
      (1, 'user'::text, recent.user_message),
      (2, 'assistant'::text, recent.assistant_message)
  ) as message(ordinal, role, text)
  where message.text is not null;

  select coalesce(jsonb_agg(jsonb_build_object(
    'id', e.id,
    'source_turn_id', e.source_turn_id,
    'subject', e.subject,
    'event_kind', e.event_kind,
    'domain', e.domain,
    'occurred_from', e.occurred_from,
    'occurred_to', e.occurred_to,
    'date_precision', e.date_precision,
    'summary', e.summary,
    'status', e.status,
    'supersedes_evidence_id', e.supersedes_evidence_id,
    'created_at', e.created_at
  ) order by e.created_at, e.id), '[]'::jsonb)
  into v_evidence
  from public.agentic_rectification_evidence e
  where e.case_id = v_case.id;

  select count(*) into v_evidence_count
  from public.agentic_rectification_evidence where case_id = v_case.id;
  select count(*) into v_turn_count
  from public.agentic_rectification_turns where case_id = v_case.id;

  select * into v_summary
  from public.agentic_rectification_case_conversation_summaries
  where case_id = v_case.id;
  if not found then
    perform public.refresh_agentic_rectification_case_conversation_summary(v_case.id);
    select * into v_summary
    from public.agentic_rectification_case_conversation_summaries
    where case_id = v_case.id;
  end if;

  select * into v_result
  from public.agentic_rectification_results
  where case_id = v_case.id and invalidated_at is null
  order by created_at desc, id desc
  limit 1;

  return jsonb_build_object(
    'case', jsonb_build_object(
      'case_id', v_case.id,
      'session_id', v_case.session_id,
      'status', v_case.status,
      'skill_name', v_case.skill_name,
      'skill_version', v_case.skill_version,
      'candidate_range', v_case.candidate_range,
      'accepted_time', v_case.accepted_time,
      'confirmed_time', v_case.confirmed_time,
      'completed_at', v_case.completed_at,
      'closed_reason', v_case.closed_reason,
      'last_activity_at', v_case.last_activity_at,
      'evidence_count', v_evidence_count,
      'turn_count', v_turn_count
    ),
    'turns', v_turns,
    'evidence', v_evidence,
    'conversation_summary', jsonb_build_object(
      'confirmed_evidence_summary', v_summary.confirmed_evidence_summary,
      'pending_revisions', v_summary.pending_revisions,
      'active_focus', v_summary.active_focus,
      'declined_skipped_topics', v_summary.declined_skipped_topics,
      'candidate_divergence_summary', v_summary.candidate_divergence_summary,
      'missing_evidence_categories', v_summary.missing_evidence_categories,
      'last_result_policy', v_summary.last_result_policy,
      'summary_version', v_summary.summary_version,
      'updated_at', v_summary.updated_at
    ),
    'latest_result', case when v_result.id is null then null else jsonb_build_object(
      'result_id', v_result.id,
      'candidates', v_result.candidates,
      'overall_confidence', v_result.overall_confidence,
      'selection_allowed', v_result.selection_allowed,
      'confirmation_allowed', v_result.confirmation_allowed,
      'representative_time', v_result.representative_time,
      'selected_time', v_result.selected_time,
      'selection_kind', v_result.selection_kind,
      'evidence_ledger_fingerprint', v_result.evidence_ledger_fingerprint,
      'candidate_range_fingerprint', v_result.candidate_range_fingerprint,
      'skill_version', v_result.skill_version,
      'algorithm_version', v_result.algorithm_version,
      'created_at', v_result.created_at,
      'invalidated_at', v_result.invalidated_at
    ) end
  );
end;
$$;

revoke all on function public.get_agentic_rectification_case_dossier(uuid, uuid)
  from public, anon, authenticated;
grant execute on function public.get_agentic_rectification_case_dossier(uuid, uuid)
  to service_role;

commit;