Jesse
1484 lines
56 KiB
PL/PgSQL
1484 lines
56 KiB
PL/PgSQL
-- V9 Agentic Rectification runtime: durable Case / Evidence / Turn / Receipt
|
|
-- state plus safe open/entry-summary/get/close/upgrade RPCs and the one-time
|
|
-- idempotent legacy Agentic backfill.
|
|
--
|
|
-- Business schema only. This migration belongs in frontend/supabase/migrations
|
|
-- and MUST NOT be duplicated into frontend/db/migrations (identity foundation;
|
|
-- see BUG-127 / BUG-144).
|
|
--
|
|
-- Security model (same as the existing agentic_rectification_results):
|
|
-- * all new tables enable RLS and grant table access to service_role only;
|
|
-- * all RPCs are SECURITY DEFINER with search_path = '', granted to
|
|
-- service_role only; the browser never passes userId, birth snapshot,
|
|
-- candidate range or permission decisions -- the server derives them.
|
|
|
|
begin;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 1. Cases
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
create table if not exists public.agentic_rectification_cases (
|
|
id uuid primary key default gen_random_uuid(),
|
|
user_id uuid not null references auth.users(id) on delete cascade,
|
|
session_id uuid not null unique references public.chat_sessions(id) on delete cascade,
|
|
status text not null default 'draft' check (
|
|
status in (
|
|
'draft',
|
|
'collecting_evidence',
|
|
'candidate_ready',
|
|
'candidate_accepted',
|
|
'needs_rebaseline',
|
|
'paused',
|
|
'confirmed',
|
|
'closed',
|
|
'abandoned',
|
|
'superseded'
|
|
)
|
|
),
|
|
skill_name text not null default 'jyotish-birth-time-rectification'
|
|
check (length(btrim(skill_name)) > 0),
|
|
skill_version text not null check (length(btrim(skill_version)) > 0),
|
|
baseline_profile_fingerprint text not null check (length(btrim(baseline_profile_fingerprint)) > 0),
|
|
baseline_birth_snapshot jsonb not null check (jsonb_typeof(baseline_birth_snapshot) = 'object'),
|
|
candidate_range jsonb not null check (jsonb_typeof(candidate_range) = 'object'),
|
|
accepted_time time without time zone,
|
|
confirmed_time time without time zone,
|
|
created_at timestamptz not null default pg_catalog.now(),
|
|
updated_at timestamptz not null default pg_catalog.now(),
|
|
last_activity_at timestamptz not null default pg_catalog.now(),
|
|
completed_at timestamptz,
|
|
closed_reason text check (closed_reason is null or closed_reason in ('completed_by_user', 'abandoned_by_user', 'other')),
|
|
check (status not in ('confirmed', 'closed') or completed_at is not null)
|
|
);
|
|
|
|
-- Resumable statuses: draft, collecting_evidence, candidate_ready,
|
|
-- candidate_accepted, needs_rebaseline, paused.
|
|
-- Terminal statuses: confirmed, closed, abandoned, superseded.
|
|
-- At most ONE resumable case per user. This partial unique index is the
|
|
-- database enforcement point; the service layer must never bypass it.
|
|
create unique index if not exists agentic_rectification_cases_one_resumable_per_user
|
|
on public.agentic_rectification_cases (user_id)
|
|
where status in (
|
|
'draft', 'collecting_evidence', 'candidate_ready', 'candidate_accepted',
|
|
'needs_rebaseline', 'paused'
|
|
);
|
|
|
|
create index if not exists agentic_rectification_cases_user_activity_idx
|
|
on public.agentic_rectification_cases (user_id, last_activity_at desc);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 2. Turns (raw user/assistant text + model version; reasoning is forbidden)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
create table if not exists public.agentic_rectification_turns (
|
|
id uuid primary key default gen_random_uuid(),
|
|
case_id uuid not null references public.agentic_rectification_cases(id) on delete cascade,
|
|
user_message text,
|
|
assistant_message text,
|
|
status text not null check (status in ('pending', 'completed', 'failed', 'retryable')),
|
|
model_name text not null check (length(btrim(model_name)) > 0),
|
|
model_version text,
|
|
created_at timestamptz not null default pg_catalog.now(),
|
|
completed_at timestamptz,
|
|
updated_at timestamptz not null default pg_catalog.now(),
|
|
check (status <> 'completed' or (assistant_message is not null and length(btrim(assistant_message)) > 0))
|
|
);
|
|
|
|
create index if not exists agentic_rectification_turns_case_idx
|
|
on public.agentic_rectification_turns (case_id, created_at);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 3. Evidence (append-only revision lineage, server-owned IDs)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
create table if not exists public.agentic_rectification_evidence (
|
|
id uuid primary key default gen_random_uuid(),
|
|
case_id uuid not null references public.agentic_rectification_cases(id) on delete cascade,
|
|
source_turn_id uuid not null references public.agentic_rectification_turns(id) on delete cascade,
|
|
source_message_id uuid,
|
|
user_quote text not null check (length(btrim(user_quote)) > 0),
|
|
subject text not null check (subject in ('self', 'family', 'other')),
|
|
event_kind text not null check (
|
|
event_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'
|
|
)
|
|
),
|
|
domain text not null check (
|
|
domain in ('education', 'career', 'relationship', 'relocation', 'finance', 'health', 'family', 'other')
|
|
),
|
|
occurred_from date,
|
|
occurred_to date,
|
|
date_precision text not null check (date_precision in ('year', 'month', 'day', 'range', 'unknown')),
|
|
summary text not null check (length(btrim(summary)) > 0),
|
|
status text not null default 'draft' check (
|
|
status in ('draft', 'pending_confirmation', 'confirmed', 'superseded', 'rejected')
|
|
),
|
|
supersedes_evidence_id uuid references public.agentic_rectification_evidence(id) on delete set null,
|
|
created_at timestamptz not null default pg_catalog.now(),
|
|
confirmed_at timestamptz,
|
|
updated_at timestamptz not null default pg_catalog.now(),
|
|
check (status <> 'confirmed' or confirmed_at is not null),
|
|
check (supersedes_evidence_id is null or supersedes_evidence_id <> id)
|
|
);
|
|
|
|
create index if not exists agentic_rectification_evidence_case_idx
|
|
on public.agentic_rectification_evidence (case_id, created_at);
|
|
|
|
create index if not exists agentic_rectification_evidence_lineage_idx
|
|
on public.agentic_rectification_evidence (supersedes_evidence_id)
|
|
where supersedes_evidence_id is not null;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 4. Tool receipts (fingerprints/phase/tool/status only; no payload/reasoning)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
create table if not exists public.agentic_rectification_tool_receipts (
|
|
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,
|
|
tool_name text not null check (
|
|
tool_name in (
|
|
'rectification-read-case',
|
|
'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'
|
|
)
|
|
),
|
|
public_phase text not null check (
|
|
public_phase in (
|
|
'run.started', 'skill.started', 'skill.loaded', 'case.loaded',
|
|
'evidence.proposed', 'evidence.confirmed', 'candidates.comparing', 'candidates.updated',
|
|
'diagnostics.completed', 'candidate.accepted', 'birth_time.confirmed',
|
|
'answer.delta', 'run.completed', 'run.failed'
|
|
)
|
|
),
|
|
started_at timestamptz not null default pg_catalog.now(),
|
|
completed_at timestamptz,
|
|
status text not null check (status in ('started', 'completed', 'failed', 'skipped')),
|
|
input_fingerprint text,
|
|
result_fingerprint text,
|
|
engine_version text,
|
|
safe_error_code text
|
|
);
|
|
|
|
create index if not exists agentic_rectification_tool_receipts_turn_idx
|
|
on public.agentic_rectification_tool_receipts (case_id, turn_id);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 5. Open request idempotency ledger (double-click / multi-tab safe)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
create table if not exists public.agentic_rectification_open_ledger (
|
|
request_id uuid not null,
|
|
user_id uuid not null references auth.users(id) on delete cascade,
|
|
case_id uuid not null references public.agentic_rectification_cases(id) on delete cascade,
|
|
session_id uuid not null references public.chat_sessions(id) on delete cascade,
|
|
intent text not null check (intent in ('homepage', 'session', 'new')),
|
|
created_at timestamptz not null default pg_catalog.now(),
|
|
primary key (user_id, request_id)
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 6. Forward extension of agentic_rectification_results to case_id semantics
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
alter table public.agentic_rectification_results
|
|
add column if not exists case_id uuid
|
|
references public.agentic_rectification_cases(id) on delete set null,
|
|
add column if not exists evidence_ledger_fingerprint text,
|
|
add column if not exists candidate_range_fingerprint text,
|
|
add column if not exists skill_version text;
|
|
|
|
create index if not exists agentic_rectification_results_case_idx
|
|
on public.agentic_rectification_results (case_id)
|
|
where case_id is not null;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 7. Chat session reverse pointer (one-to-one, bidirectional consistency)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
alter table public.chat_sessions
|
|
add column if not exists agentic_rectification_case_id uuid
|
|
references public.agentic_rectification_cases(id) on delete set null;
|
|
|
|
create unique index if not exists chat_sessions_agentic_rectification_case_unique
|
|
on public.chat_sessions (agentic_rectification_case_id)
|
|
where agentic_rectification_case_id is not null;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 8. RLS + grants (service-role only; browser never touches these tables)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
alter table public.agentic_rectification_cases enable row level security;
|
|
alter table public.agentic_rectification_turns enable row level security;
|
|
alter table public.agentic_rectification_evidence enable row level security;
|
|
alter table public.agentic_rectification_tool_receipts enable row level security;
|
|
alter table public.agentic_rectification_open_ledger enable row level security;
|
|
|
|
revoke all on table public.agentic_rectification_cases from public, anon, authenticated, service_role;
|
|
revoke all on table public.agentic_rectification_turns from public, anon, authenticated, service_role;
|
|
revoke all on table public.agentic_rectification_evidence from public, anon, authenticated, service_role;
|
|
revoke all on table public.agentic_rectification_tool_receipts from public, anon, authenticated, service_role;
|
|
revoke all on table public.agentic_rectification_open_ledger from public, anon, authenticated, service_role;
|
|
grant all on table public.agentic_rectification_cases to service_role;
|
|
grant all on table public.agentic_rectification_turns to service_role;
|
|
grant all on table public.agentic_rectification_evidence to service_role;
|
|
grant all on table public.agentic_rectification_tool_receipts to service_role;
|
|
grant all on table public.agentic_rectification_open_ledger to service_role;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 9. Bidirectional Case <-> Session consistency triggers
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
create or replace function public.agentic_rectification_cases_sync_session()
|
|
returns trigger
|
|
language plpgsql
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
begin
|
|
if new.session_id is distinct from old.session_id then
|
|
update public.chat_sessions
|
|
set agentic_rectification_case_id = null
|
|
where agentic_rectification_case_id = old.id;
|
|
end if;
|
|
update public.chat_sessions
|
|
set agentic_rectification_case_id = new.id,
|
|
session_type = 'birth_time_rectification'
|
|
where id = new.session_id;
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.agentic_rectification_cases_sync_session()
|
|
from public, anon, authenticated;
|
|
|
|
drop trigger if exists agentic_rectification_cases_sync_session_trigger
|
|
on public.agentic_rectification_cases;
|
|
create trigger agentic_rectification_cases_sync_session_trigger
|
|
after insert or update of session_id on public.agentic_rectification_cases
|
|
for each row execute function public.agentic_rectification_cases_sync_session();
|
|
|
|
create or replace function public.agentic_rectification_chat_sessions_case_guard()
|
|
returns trigger
|
|
language plpgsql
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
declare
|
|
v_case public.agentic_rectification_cases%rowtype;
|
|
begin
|
|
if new.agentic_rectification_case_id is not null then
|
|
select * into v_case
|
|
from public.agentic_rectification_cases
|
|
where id = new.agentic_rectification_case_id;
|
|
if not found then
|
|
raise exception 'agentic_rectification_case_not_found' using errcode = 'P0001';
|
|
end if;
|
|
if v_case.session_id is distinct from new.id then
|
|
raise exception 'agentic_rectification_case_session_mismatch' using errcode = 'P0001';
|
|
end if;
|
|
if v_case.user_id is distinct from new.user_id then
|
|
raise exception 'agentic_rectification_case_owner_mismatch' using errcode = 'P0001';
|
|
end if;
|
|
end if;
|
|
return new;
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.agentic_rectification_chat_sessions_case_guard()
|
|
from public, anon, authenticated;
|
|
|
|
drop trigger if exists agentic_rectification_chat_sessions_case_guard_trigger
|
|
on public.chat_sessions;
|
|
create trigger agentic_rectification_chat_sessions_case_guard_trigger
|
|
before update of agentic_rectification_case_id on public.chat_sessions
|
|
for each row execute function public.agentic_rectification_chat_sessions_case_guard();
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 10. Shared helpers used by RPCs
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
create or replace function public.agentic_rectification_normalize_quote(p_value text)
|
|
returns text
|
|
language sql
|
|
immutable
|
|
as $$
|
|
select regexp_replace(
|
|
lower(coalesce(p_value, '')),
|
|
'[\s\u3000，。！？、；：“”‘’（）《》·—…]',
|
|
'',
|
|
'g'
|
|
)
|
|
$$;
|
|
|
|
revoke all on function public.agentic_rectification_normalize_quote(text)
|
|
from public, anon, authenticated;
|
|
|
|
create or replace function public.agentic_rectification_resumable_statuses()
|
|
returns text[]
|
|
language sql
|
|
immutable
|
|
as $$
|
|
select array[
|
|
'draft', 'collecting_evidence', 'candidate_ready', 'candidate_accepted',
|
|
'needs_rebaseline', 'paused'
|
|
]::text[]
|
|
$$;
|
|
|
|
revoke all on function public.agentic_rectification_resumable_statuses()
|
|
from public, anon, authenticated;
|
|
|
|
create or replace function public.agentic_rectification_legacy_fingerprint(
|
|
p_user_id uuid,
|
|
p_session_id uuid,
|
|
p_status text
|
|
)
|
|
returns text
|
|
language sql
|
|
immutable
|
|
as $$
|
|
select encode(
|
|
public.digest(
|
|
convert_to(
|
|
p_user_id::text || ':' || p_session_id::text || ':' || coalesce(p_status, ''),
|
|
'utf8'
|
|
),
|
|
'sha256'
|
|
),
|
|
'hex'
|
|
)
|
|
$$;
|
|
|
|
revoke all on function public.agentic_rectification_legacy_fingerprint(uuid, uuid, text)
|
|
from public, anon, authenticated;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 11. Open Case RPC (idempotent, same-user serialized, atomic case+session)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
create or replace function public.open_agentic_rectification_case(
|
|
p_user_id uuid,
|
|
p_request_id uuid,
|
|
p_intent text,
|
|
p_session_id uuid,
|
|
p_skill_name text,
|
|
p_skill_version text,
|
|
p_baseline_profile_fingerprint text,
|
|
p_baseline_birth_snapshot jsonb,
|
|
p_candidate_range jsonb
|
|
)
|
|
returns jsonb
|
|
language plpgsql
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
declare
|
|
v_case public.agentic_rectification_cases%rowtype;
|
|
v_session public.chat_sessions%rowtype;
|
|
v_session_id uuid;
|
|
v_turn_count bigint;
|
|
begin
|
|
if p_user_id is null or p_request_id is null then
|
|
raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
|
|
end if;
|
|
if p_intent not in ('homepage', 'session', 'new') then
|
|
raise exception 'agentic_rectification_invalid_intent' using errcode = 'P0001';
|
|
end if;
|
|
if length(btrim(p_skill_name)) = 0 or length(btrim(p_skill_version)) = 0
|
|
or length(btrim(coalesce(p_baseline_profile_fingerprint, ''))) = 0 then
|
|
raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
|
|
end if;
|
|
|
|
-- Serialize concurrent open/create for the same user (double-click, two
|
|
-- tabs, retries). The advisory lock is released when this transaction ends.
|
|
perform pg_catalog.pg_advisory_xact_lock(
|
|
pg_catalog.hashtext('agentic_rectification_open:' || p_user_id::text)
|
|
);
|
|
|
|
-- Idempotency: an earlier open with the same requestId wins.
|
|
select c.* into v_case
|
|
from public.agentic_rectification_cases c
|
|
join public.agentic_rectification_open_ledger l
|
|
on l.case_id = c.id and l.session_id = c.session_id
|
|
where l.user_id = p_user_id and l.request_id = p_request_id
|
|
limit 1;
|
|
|
|
if found then
|
|
return jsonb_build_object(
|
|
'disposition', case
|
|
when v_case.status = any (public.agentic_rectification_resumable_statuses()) then 'resumed'
|
|
else 'readonly'
|
|
end,
|
|
'case_id', v_case.id,
|
|
'session_id', v_case.session_id,
|
|
'status', v_case.status,
|
|
'should_start_opening', false,
|
|
'skill_version', v_case.skill_version
|
|
);
|
|
end if;
|
|
|
|
if p_intent = 'session' then
|
|
if p_session_id is null then
|
|
raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
|
|
end if;
|
|
select * into v_session
|
|
from public.chat_sessions
|
|
where id = p_session_id and user_id = p_user_id;
|
|
if not found then
|
|
raise exception 'agentic_rectification_session_not_found' using errcode = 'P0001';
|
|
end if;
|
|
if v_session.session_type <> 'birth_time_rectification' then
|
|
raise exception 'agentic_rectification_session_not_rectification' using errcode = 'P0001';
|
|
end if;
|
|
select * into v_case
|
|
from public.agentic_rectification_cases
|
|
where session_id = p_session_id;
|
|
if not found then
|
|
raise exception 'agentic_rectification_case_not_found' using errcode = 'P0001';
|
|
end if;
|
|
return jsonb_build_object(
|
|
'disposition', case
|
|
when v_case.status = any (public.agentic_rectification_resumable_statuses()) then 'resumed'
|
|
else 'readonly'
|
|
end,
|
|
'case_id', v_case.id,
|
|
'session_id', v_case.session_id,
|
|
'status', v_case.status,
|
|
'should_start_opening', false,
|
|
'skill_version', v_case.skill_version
|
|
);
|
|
end if;
|
|
|
|
if p_intent = 'homepage' then
|
|
select * into v_case
|
|
from public.agentic_rectification_cases
|
|
where user_id = p_user_id
|
|
and status = any (public.agentic_rectification_resumable_statuses())
|
|
order by last_activity_at desc, id
|
|
limit 1;
|
|
if found then
|
|
select count(*) into v_turn_count
|
|
from public.agentic_rectification_turns
|
|
where case_id = v_case.id;
|
|
return jsonb_build_object(
|
|
'disposition', 'resumed',
|
|
'case_id', v_case.id,
|
|
'session_id', v_case.session_id,
|
|
'status', v_case.status,
|
|
'should_start_opening', false,
|
|
'skill_version', v_case.skill_version
|
|
);
|
|
end if;
|
|
end if;
|
|
|
|
if p_intent = 'new' then
|
|
select * into v_case
|
|
from public.agentic_rectification_cases
|
|
where user_id = p_user_id
|
|
and status = any (public.agentic_rectification_resumable_statuses())
|
|
order by last_activity_at desc, id
|
|
limit 1;
|
|
if found then
|
|
-- supersedeActive=true is forbidden: restarting with an active case
|
|
-- must surface a safe conflict, never silently abandon.
|
|
raise exception 'agentic_rectification_active_case_conflict' using errcode = 'P0001';
|
|
end if;
|
|
end if;
|
|
|
|
-- Create a new case + a new session atomically. The inner exception block
|
|
-- rolls back both inserts together if a concurrent request already created
|
|
-- a resumable case for this user. Profile snapshot and candidate range are
|
|
-- validated here because only this path persists them.
|
|
begin
|
|
if p_baseline_birth_snapshot is null or jsonb_typeof(p_baseline_birth_snapshot) <> 'object'
|
|
or p_baseline_birth_snapshot ->> 'birth_date' is null
|
|
or p_baseline_birth_snapshot ->> 'latitude' is null
|
|
or p_baseline_birth_snapshot ->> 'longitude' is null
|
|
or p_baseline_birth_snapshot ->> 'timezone_offset' is null
|
|
or length(btrim(coalesce(p_baseline_birth_snapshot ->> 'birth_time_source', ''))) = 0 then
|
|
raise exception 'agentic_rectification_profile_incomplete' using errcode = 'P0001';
|
|
end if;
|
|
if p_candidate_range is null or jsonb_typeof(p_candidate_range) <> 'object'
|
|
or not (p_candidate_range ->> 'start_time') ~ '^([01][0-9]|2[0-3]):[0-5][0-9]$'
|
|
or not (p_candidate_range ->> 'end_time') ~ '^([01][0-9]|2[0-3]):[0-5][0-9]$' then
|
|
raise exception 'agentic_rectification_invalid_range' using errcode = 'P0001';
|
|
end if;
|
|
|
|
insert into public.chat_sessions (user_id, title, theme, session_type, messages)
|
|
values (p_user_id, '生时校正', 'general', 'birth_time_rectification', '[]'::jsonb)
|
|
returning id into v_session_id;
|
|
|
|
insert into public.agentic_rectification_cases (
|
|
user_id, session_id, status, skill_name, skill_version,
|
|
baseline_profile_fingerprint, baseline_birth_snapshot, candidate_range
|
|
) values (
|
|
p_user_id, v_session_id, 'draft', p_skill_name, p_skill_version,
|
|
p_baseline_profile_fingerprint, p_baseline_birth_snapshot, p_candidate_range
|
|
) returning id into v_case.id;
|
|
|
|
insert into public.agentic_rectification_open_ledger (
|
|
request_id, user_id, case_id, session_id, intent
|
|
) values (
|
|
p_request_id, p_user_id, v_case.id, v_session_id, p_intent
|
|
);
|
|
exception when unique_violation then
|
|
select * into v_case
|
|
from public.agentic_rectification_cases
|
|
where user_id = p_user_id
|
|
and status = any (public.agentic_rectification_resumable_statuses())
|
|
order by last_activity_at desc, id
|
|
limit 1;
|
|
if not found then
|
|
raise;
|
|
end if;
|
|
insert into public.agentic_rectification_open_ledger (
|
|
request_id, user_id, case_id, session_id, intent
|
|
) values (
|
|
p_request_id, p_user_id, v_case.id, v_case.session_id, p_intent
|
|
);
|
|
return jsonb_build_object(
|
|
'disposition', 'resumed',
|
|
'case_id', v_case.id,
|
|
'session_id', v_case.session_id,
|
|
'status', v_case.status,
|
|
'should_start_opening', false,
|
|
'skill_version', v_case.skill_version
|
|
);
|
|
end;
|
|
|
|
return jsonb_build_object(
|
|
'disposition', 'created',
|
|
'case_id', v_case.id,
|
|
'session_id', v_session_id,
|
|
'status', 'draft',
|
|
'should_start_opening', true,
|
|
'skill_version', p_skill_version
|
|
);
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.open_agentic_rectification_case(uuid, uuid, text, uuid, text, text, text, jsonb, jsonb)
|
|
from public, anon, authenticated;
|
|
grant execute on function public.open_agentic_rectification_case(uuid, uuid, text, uuid, text, text, text, jsonb, jsonb)
|
|
to service_role;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 12. Entry summary RPC (homepage CTA truth)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
create or replace function public.get_agentic_rectification_entry_summary(
|
|
p_user_id uuid
|
|
)
|
|
returns jsonb
|
|
language plpgsql
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
declare
|
|
v_resumable public.agentic_rectification_cases%rowtype;
|
|
v_terminal public.agentic_rectification_cases%rowtype;
|
|
begin
|
|
if p_user_id is null then
|
|
raise exception 'agentic_rectification_invalid_input' using errcode = 'P0001';
|
|
end if;
|
|
|
|
select * into v_resumable
|
|
from public.agentic_rectification_cases
|
|
where user_id = p_user_id
|
|
and status = any (public.agentic_rectification_resumable_statuses())
|
|
order by last_activity_at desc, id
|
|
limit 1;
|
|
|
|
select * into v_terminal
|
|
from public.agentic_rectification_cases
|
|
where user_id = p_user_id
|
|
and status in ('confirmed', 'closed', 'abandoned', 'superseded')
|
|
order by last_activity_at desc, id
|
|
limit 1;
|
|
|
|
return jsonb_build_object(
|
|
'has_resumable_case', v_resumable.id is not null,
|
|
'has_terminal_case_with_time', v_terminal.id is not null
|
|
and (v_terminal.accepted_time is not null or v_terminal.confirmed_time is not null),
|
|
'latest_resumable', case
|
|
when v_resumable.id is null then null
|
|
else jsonb_build_object(
|
|
'case_id', v_resumable.id,
|
|
'status', v_resumable.status,
|
|
'last_activity_at', v_resumable.last_activity_at
|
|
)
|
|
end,
|
|
'latest_terminal', case
|
|
when v_terminal.id is null then null
|
|
else jsonb_build_object(
|
|
'case_id', v_terminal.id,
|
|
'status', v_terminal.status,
|
|
'has_usable_time', v_terminal.accepted_time is not null or v_terminal.confirmed_time is not null
|
|
)
|
|
end
|
|
);
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.get_agentic_rectification_entry_summary(uuid)
|
|
from public, anon, authenticated;
|
|
grant execute on function public.get_agentic_rectification_entry_summary(uuid)
|
|
to service_role;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 13. Case read RPC (sanitized projection; never the birth snapshot)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
create or replace function public.get_agentic_rectification_case(
|
|
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_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 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_result
|
|
from public.agentic_rectification_results
|
|
where case_id = v_case.id
|
|
and invalidated_at is null
|
|
order by created_at desc
|
|
limit 1;
|
|
|
|
return 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,
|
|
'created_at', v_case.created_at,
|
|
'last_activity_at', v_case.last_activity_at,
|
|
'completed_at', v_case.completed_at,
|
|
'closed_reason', v_case.closed_reason,
|
|
'evidence_count', v_evidence_count,
|
|
'turn_count', v_turn_count,
|
|
'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,
|
|
'created_at', v_result.created_at
|
|
)
|
|
end
|
|
);
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.get_agentic_rectification_case(uuid, uuid)
|
|
from public, anon, authenticated;
|
|
grant execute on function public.get_agentic_rectification_case(uuid, uuid)
|
|
to service_role;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 14. Close Case RPC (terminal; never wraps as engine confirmed)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
create or replace function public.close_agentic_rectification_case(
|
|
p_user_id uuid,
|
|
p_case_id uuid,
|
|
p_reason text
|
|
)
|
|
returns jsonb
|
|
language plpgsql
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
declare
|
|
v_case public.agentic_rectification_cases%rowtype;
|
|
begin
|
|
if p_user_id is null or p_case_id is null
|
|
or coalesce(p_reason, '') not in ('completed_by_user', 'abandoned_by_user', 'other') 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
|
|
if v_case.status <> 'closed' then
|
|
raise exception 'agentic_rectification_case_terminal' using errcode = 'P0001';
|
|
end if;
|
|
return jsonb_build_object(
|
|
'success', true, 'idempotent', true,
|
|
'case_id', v_case.id, 'status', v_case.status
|
|
);
|
|
end if;
|
|
|
|
update public.agentic_rectification_cases
|
|
set status = 'closed',
|
|
closed_reason = p_reason,
|
|
completed_at = pg_catalog.now(),
|
|
last_activity_at = pg_catalog.now(),
|
|
updated_at = pg_catalog.now()
|
|
where id = v_case.id;
|
|
|
|
return jsonb_build_object(
|
|
'success', true, 'idempotent', false,
|
|
'case_id', v_case.id, 'status', 'closed'
|
|
);
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.close_agentic_rectification_case(uuid, uuid, text)
|
|
from public, anon, authenticated;
|
|
grant execute on function public.close_agentic_rectification_case(uuid, uuid, text)
|
|
to service_role;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 15. Upgrade Skill RPC (explicit migration; running cases pin their version)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
create or replace function public.upgrade_agentic_rectification_skill(
|
|
p_user_id uuid,
|
|
p_case_id uuid,
|
|
p_skill_version text
|
|
)
|
|
returns jsonb
|
|
language plpgsql
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
declare
|
|
v_case public.agentic_rectification_cases%rowtype;
|
|
v_previous text;
|
|
begin
|
|
if p_user_id is null or p_case_id is null
|
|
or length(btrim(coalesce(p_skill_version, ''))) = 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;
|
|
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;
|
|
|
|
v_previous := v_case.skill_version;
|
|
update public.agentic_rectification_cases
|
|
set skill_version = p_skill_version,
|
|
updated_at = pg_catalog.now()
|
|
where id = v_case.id;
|
|
|
|
return jsonb_build_object(
|
|
'success', true,
|
|
'case_id', v_case.id,
|
|
'previous_skill_version', v_previous,
|
|
'skill_version', p_skill_version,
|
|
'idempotent', v_previous = p_skill_version
|
|
);
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.upgrade_agentic_rectification_skill(uuid, uuid, text)
|
|
from public, anon, authenticated;
|
|
grant execute on function public.upgrade_agentic_rectification_skill(uuid, uuid, text)
|
|
to service_role;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 16. Turn append RPC (server-owned; no reasoning ever persisted)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
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
|
|
)
|
|
returns jsonb
|
|
language plpgsql
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
declare
|
|
v_case public.agentic_rectification_cases%rowtype;
|
|
v_turn_id uuid;
|
|
begin
|
|
if p_user_id is null or p_case_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;
|
|
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;
|
|
|
|
insert into public.agentic_rectification_turns (
|
|
case_id, user_message, assistant_message, status, model_name, model_version, completed_at
|
|
) values (
|
|
p_case_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 id into v_turn_id;
|
|
|
|
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);
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.append_agentic_rectification_turn(uuid, uuid, text, text, text, text, text)
|
|
from public, anon, authenticated;
|
|
grant execute on function public.append_agentic_rectification_turn(uuid, uuid, text, text, text, text, text)
|
|
to service_role;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 17. Tool receipt insert RPC (fingerprints only)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
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
|
|
)
|
|
returns jsonb
|
|
language plpgsql
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
declare
|
|
v_receipt_id uuid;
|
|
v_turn_count bigint;
|
|
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;
|
|
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 count(*) into v_turn_count
|
|
from public.agentic_rectification_turns
|
|
where id = p_turn_id and case_id = p_case_id;
|
|
if v_turn_count = 0 then
|
|
raise exception 'agentic_rectification_turn_not_found' using errcode = 'P0001';
|
|
end if;
|
|
|
|
insert into public.agentic_rectification_tool_receipts (
|
|
case_id, turn_id, tool_name, public_phase, status,
|
|
input_fingerprint, result_fingerprint, engine_version, safe_error_code,
|
|
completed_at
|
|
) values (
|
|
p_case_id, p_turn_id, p_tool_name, p_public_phase, p_status,
|
|
p_input_fingerprint, p_result_fingerprint, p_engine_version, p_safe_error_code,
|
|
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);
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.insert_agentic_rectification_tool_receipt(uuid, uuid, uuid, text, text, text, text, text, text, text)
|
|
from public, anon, authenticated;
|
|
grant execute on function public.insert_agentic_rectification_tool_receipt(uuid, uuid, uuid, text, text, text, text, text, text, text)
|
|
to service_role;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 18. Evidence proposal / confirm / revise RPCs
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
create or replace function public.propose_agentic_rectification_evidence(
|
|
p_user_id uuid,
|
|
p_case_id uuid,
|
|
p_source_turn_id uuid,
|
|
p_user_quote text,
|
|
p_subject text,
|
|
p_event_kind text,
|
|
p_domain 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_turn public.agentic_rectification_turns%rowtype;
|
|
v_evidence_id uuid;
|
|
v_existing_id uuid;
|
|
begin
|
|
if p_user_id is null or p_case_id is null or p_source_turn_id is null
|
|
or length(btrim(coalesce(p_user_quote, ''))) = 0
|
|
or length(btrim(coalesce(p_summary, ''))) = 0
|
|
or p_subject not in ('self', 'family', 'other')
|
|
or p_date_precision not in ('year', 'month', 'day', 'range', 'unknown') 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_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
|
|
or position(
|
|
public.agentic_rectification_normalize_quote(p_user_quote)
|
|
in public.agentic_rectification_normalize_quote(v_turn.user_message)
|
|
) = 0 then
|
|
raise exception 'agentic_rectification_quote_not_grounded' using errcode = 'P0001';
|
|
end if;
|
|
|
|
-- Idempotency: replaying the same proposal returns the existing draft.
|
|
select id into v_existing_id
|
|
from public.agentic_rectification_evidence
|
|
where case_id = p_case_id
|
|
and source_turn_id = p_source_turn_id
|
|
and user_quote = p_user_quote
|
|
and event_kind = p_event_kind
|
|
and summary = p_summary
|
|
and status in ('draft', 'pending_confirmation')
|
|
limit 1;
|
|
if v_existing_id is not null then
|
|
return jsonb_build_object('evidence_id', v_existing_id, 'idempotent', true);
|
|
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
|
|
) values (
|
|
p_case_id, p_source_turn_id, p_user_quote, p_subject, p_event_kind, p_domain,
|
|
p_occurred_from, p_occurred_to, p_date_precision, p_summary, 'draft'
|
|
) returning id into v_evidence_id;
|
|
|
|
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('evidence_id', v_evidence_id, 'idempotent', false);
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.propose_agentic_rectification_evidence(uuid, uuid, uuid, text, text, text, text, date, date, text, text)
|
|
from public, anon, authenticated;
|
|
grant execute on function public.propose_agentic_rectification_evidence(uuid, uuid, uuid, text, text, text, text, date, date, text, text)
|
|
to service_role;
|
|
|
|
create or replace function public.confirm_agentic_rectification_evidence(
|
|
p_user_id uuid,
|
|
p_case_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_evidence public.agentic_rectification_evidence%rowtype;
|
|
begin
|
|
if p_user_id is null or p_case_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;
|
|
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_evidence
|
|
from public.agentic_rectification_evidence
|
|
where id = p_evidence_id and case_id = p_case_id;
|
|
if not found then
|
|
raise exception 'agentic_rectification_evidence_not_found' using errcode = 'P0001';
|
|
end if;
|
|
if v_evidence.status = 'confirmed' then
|
|
return jsonb_build_object('evidence_id', v_evidence.id, 'status', 'confirmed', 'idempotent', true);
|
|
end if;
|
|
if v_evidence.status not in ('draft', 'pending_confirmation') then
|
|
raise exception 'agentic_rectification_evidence_not_confirmable' using errcode = 'P0001';
|
|
end if;
|
|
|
|
update public.agentic_rectification_evidence
|
|
set status = 'confirmed',
|
|
confirmed_at = pg_catalog.now(),
|
|
updated_at = pg_catalog.now()
|
|
where id = v_evidence.id;
|
|
|
|
return jsonb_build_object('evidence_id', v_evidence.id, 'status', 'confirmed', 'idempotent', false);
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.confirm_agentic_rectification_evidence(uuid, uuid, uuid)
|
|
from public, anon, authenticated;
|
|
grant execute on function public.confirm_agentic_rectification_evidence(uuid, uuid, uuid)
|
|
to service_role;
|
|
|
|
create or replace function public.revise_agentic_rectification_evidence(
|
|
p_user_id uuid,
|
|
p_case_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_target public.agentic_rectification_evidence%rowtype;
|
|
v_new_id uuid;
|
|
begin
|
|
if p_user_id is null or p_case_id is null or p_evidence_id is null
|
|
or length(btrim(coalesce(p_user_quote, ''))) = 0
|
|
or length(btrim(coalesce(p_summary, ''))) = 0
|
|
or p_date_precision not in ('year', 'month', 'day', 'range', 'unknown') 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_target
|
|
from public.agentic_rectification_evidence
|
|
where id = p_evidence_id and case_id = p_case_id;
|
|
if not found then
|
|
raise exception 'agentic_rectification_evidence_not_found' using errcode = 'P0001';
|
|
end if;
|
|
if v_target.status not in ('confirmed', 'pending_confirmation') then
|
|
raise exception 'agentic_rectification_evidence_not_revisable' using errcode = 'P0001';
|
|
end if;
|
|
|
|
-- Append-only lineage: the old row is superseded, never overwritten.
|
|
update public.agentic_rectification_evidence
|
|
set status = 'superseded',
|
|
updated_at = pg_catalog.now()
|
|
where id = v_target.id;
|
|
|
|
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, supersedes_evidence_id
|
|
) values (
|
|
v_target.case_id, v_target.source_turn_id, p_user_quote, v_target.subject,
|
|
v_target.event_kind, v_target.domain,
|
|
p_occurred_from, p_occurred_to, p_date_precision, p_summary,
|
|
'pending_confirmation', v_target.id
|
|
) returning id into v_new_id;
|
|
|
|
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(
|
|
'evidence_id', v_new_id,
|
|
'supersedes_evidence_id', v_target.id,
|
|
'idempotent', false
|
|
);
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.revise_agentic_rectification_evidence(uuid, uuid, uuid, text, date, date, text, text)
|
|
from public, anon, authenticated;
|
|
grant execute on function public.revise_agentic_rectification_evidence(uuid, uuid, uuid, text, date, date, text, text)
|
|
to service_role;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 19. Legacy Agentic backfill (one-time, idempotent)
|
|
--
|
|
-- Mapping rules:
|
|
-- * engine_confirmed result -> confirmed (terminal)
|
|
-- * user_accepted result only -> candidate_accepted (resumable)
|
|
-- * messages present, no selection -> collecting_evidence, or
|
|
-- candidate_ready when results exist
|
|
-- * empty session -> draft for the newest empty per user
|
|
-- only; older empties -> abandoned
|
|
-- * per user, at most one resumable -> latest activity wins; the rest of
|
|
-- the resumable candidates are
|
|
-- superseded (read-only history kept)
|
|
-- * no confirmed evidence is ever generated from chat text
|
|
-- * agentic_rectification_results rows are mapped to the backfilled case_id
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
create or replace function public.backfill_agentic_rectification_legacy_cases()
|
|
returns jsonb
|
|
language plpgsql
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
declare
|
|
v_row record;
|
|
v_case_id uuid;
|
|
v_fingerprint text;
|
|
v_snapshot jsonb;
|
|
v_range jsonb;
|
|
v_profile public.profiles%rowtype;
|
|
v_selected_time time without time zone;
|
|
v_result_range jsonb;
|
|
v_base_birth_date date;
|
|
v_base_lat double precision;
|
|
v_base_lon double precision;
|
|
v_base_tz double precision;
|
|
v_base_source text;
|
|
v_accepted_time time without time zone;
|
|
v_confirmed_time time without time zone;
|
|
v_legacy_sessions integer := 0;
|
|
v_created integer := 0;
|
|
v_mapped_results integer := 0;
|
|
v_confirmed_count integer := 0;
|
|
v_candidate_accepted_count integer := 0;
|
|
v_candidate_ready_count integer := 0;
|
|
v_collecting_count integer := 0;
|
|
v_draft_count integer := 0;
|
|
v_abandoned_count integer := 0;
|
|
v_superseded_count integer := 0;
|
|
begin
|
|
for v_row in
|
|
with candidate as (
|
|
select
|
|
s.id as session_id,
|
|
s.user_id,
|
|
s.updated_at,
|
|
(jsonb_typeof(coalesce(s.messages, '[]'::jsonb)) = 'array'
|
|
and jsonb_array_length(coalesce(s.messages, '[]'::jsonb)) > 0) as has_messages,
|
|
exists (
|
|
select 1 from public.agentic_rectification_results r where r.session_id = s.id
|
|
) as has_results,
|
|
(select r.selection_kind
|
|
from public.agentic_rectification_results r
|
|
where r.session_id = s.id
|
|
and r.selection_kind in ('engine_confirmed', 'user_accepted')
|
|
order by r.created_at desc
|
|
limit 1) as result_kind,
|
|
(select count(*) from public.agentic_rectification_results r where r.session_id = s.id) as result_count
|
|
from public.chat_sessions s
|
|
where s.session_type = 'birth_time_rectification'
|
|
and not exists (
|
|
select 1 from public.agentic_rectification_cases c where c.session_id = s.id
|
|
)
|
|
),
|
|
based as (
|
|
select candidate.*,
|
|
case
|
|
when result_kind = 'engine_confirmed' then 'confirmed'
|
|
when result_kind = 'user_accepted' then 'candidate_accepted'
|
|
when has_messages then case when has_results then 'candidate_ready' else 'collecting_evidence' end
|
|
else 'abandoned'
|
|
end as base_status
|
|
from candidate
|
|
)
|
|
select ranked.*,
|
|
case
|
|
when base_status = any (public.agentic_rectification_resumable_statuses())
|
|
and resumable_rank > 1 then 'superseded'
|
|
when base_status = 'abandoned'
|
|
and resumable_rank = 1 and activity_rank = 1 then 'draft'
|
|
else base_status
|
|
end as final_status
|
|
from (
|
|
select based.*,
|
|
row_number() over (
|
|
partition by user_id
|
|
order by (base_status = any (public.agentic_rectification_resumable_statuses())) desc,
|
|
updated_at desc, session_id
|
|
) as resumable_rank,
|
|
row_number() over (
|
|
partition by user_id order by updated_at desc, session_id
|
|
) as activity_rank
|
|
from based
|
|
) ranked
|
|
order by user_id, updated_at desc, session_id
|
|
loop
|
|
v_legacy_sessions := v_legacy_sessions + 1;
|
|
v_range := null;
|
|
v_accepted_time := null;
|
|
v_confirmed_time := null;
|
|
v_selected_time := null;
|
|
v_result_range := null;
|
|
v_base_birth_date := null;
|
|
v_base_lat := null;
|
|
v_base_lon := null;
|
|
v_base_tz := null;
|
|
v_base_source := null;
|
|
|
|
select r.selected_time, r.candidate_range,
|
|
r.baseline_birth_date, r.baseline_latitude, r.baseline_longitude,
|
|
r.baseline_timezone_offset, r.baseline_birth_time_source
|
|
into v_selected_time, v_result_range,
|
|
v_base_birth_date, v_base_lat, v_base_lon, v_base_tz, v_base_source
|
|
from public.agentic_rectification_results r
|
|
where r.session_id = v_row.session_id
|
|
order by r.created_at desc
|
|
limit 1;
|
|
|
|
if v_result_range is not null and jsonb_typeof(v_result_range) = 'object'
|
|
and (v_result_range ->> 'start_time') ~ '^([01][0-9]|2[0-3]):[0-5][0-9]$'
|
|
and (v_result_range ->> 'end_time') ~ '^([01][0-9]|2[0-3]):[0-5][0-9]$' then
|
|
v_range := v_result_range;
|
|
end if;
|
|
if v_row.final_status = 'confirmed' then
|
|
v_confirmed_time := v_selected_time;
|
|
elsif v_row.final_status = 'candidate_accepted' then
|
|
v_accepted_time := v_selected_time;
|
|
end if;
|
|
|
|
select * into v_profile from public.profiles where id = v_row.user_id;
|
|
|
|
if v_range is null then
|
|
if v_profile.id is not null
|
|
and (v_profile.active_birth_time is not null or v_profile.reported_birth_time is not null) then
|
|
v_range := jsonb_build_object(
|
|
'start_time', coalesce(to_char(v_profile.active_birth_time, 'HH24:MI'), to_char(v_profile.reported_birth_time, 'HH24:MI')),
|
|
'end_time', coalesce(to_char(v_profile.active_birth_time, 'HH24:MI'), to_char(v_profile.reported_birth_time, 'HH24:MI'))
|
|
);
|
|
else
|
|
v_range := jsonb_build_object('start_time', '00:00', 'end_time', '23:59');
|
|
end if;
|
|
end if;
|
|
|
|
v_snapshot := jsonb_build_object(
|
|
'birth_date', to_char(coalesce(v_profile.birth_date, v_base_birth_date), 'YYYY-MM-DD'),
|
|
'latitude', coalesce(v_profile.latitude, v_base_lat),
|
|
'longitude', coalesce(v_profile.longitude, v_base_lon),
|
|
'timezone_offset', coalesce(v_profile.timezone_offset, v_base_tz),
|
|
'birth_time_source', coalesce(v_profile.birth_time_source, v_base_source, 'legacy_import')
|
|
);
|
|
v_fingerprint := public.agentic_rectification_legacy_fingerprint(
|
|
v_row.user_id, v_row.session_id, v_row.final_status
|
|
);
|
|
|
|
insert into public.agentic_rectification_cases (
|
|
user_id, session_id, status, skill_name, skill_version,
|
|
baseline_profile_fingerprint, baseline_birth_snapshot, candidate_range,
|
|
accepted_time, confirmed_time, last_activity_at, completed_at
|
|
) values (
|
|
v_row.user_id, v_row.session_id, v_row.final_status,
|
|
'jyotish-birth-time-rectification', '9.0.0',
|
|
v_fingerprint, v_snapshot, v_range,
|
|
v_accepted_time, v_confirmed_time, v_row.updated_at,
|
|
case when v_row.final_status in ('confirmed', 'closed') then v_row.updated_at else null end
|
|
) returning id into v_case_id;
|
|
|
|
update public.agentic_rectification_results
|
|
set case_id = v_case_id
|
|
where session_id = v_row.session_id and case_id is null;
|
|
v_mapped_results := v_mapped_results + v_row.result_count;
|
|
|
|
v_created := v_created + 1;
|
|
if v_row.final_status = 'confirmed' then
|
|
v_confirmed_count := v_confirmed_count + 1;
|
|
elsif v_row.final_status = 'candidate_accepted' then
|
|
v_candidate_accepted_count := v_candidate_accepted_count + 1;
|
|
elsif v_row.final_status = 'candidate_ready' then
|
|
v_candidate_ready_count := v_candidate_ready_count + 1;
|
|
elsif v_row.final_status = 'collecting_evidence' then
|
|
v_collecting_count := v_collecting_count + 1;
|
|
elsif v_row.final_status = 'draft' then
|
|
v_draft_count := v_draft_count + 1;
|
|
elsif v_row.final_status = 'abandoned' then
|
|
v_abandoned_count := v_abandoned_count + 1;
|
|
elsif v_row.final_status = 'superseded' then
|
|
v_superseded_count := v_superseded_count + 1;
|
|
end if;
|
|
end loop;
|
|
|
|
return jsonb_build_object(
|
|
'legacy_sessions_scanned', v_legacy_sessions,
|
|
'cases_created', v_created,
|
|
'results_mapped', v_mapped_results,
|
|
'confirmed', v_confirmed_count,
|
|
'candidate_accepted', v_candidate_accepted_count,
|
|
'candidate_ready', v_candidate_ready_count,
|
|
'collecting_evidence', v_collecting_count,
|
|
'draft', v_draft_count,
|
|
'abandoned', v_abandoned_count,
|
|
'superseded', v_superseded_count
|
|
);
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.backfill_agentic_rectification_legacy_cases()
|
|
from public, anon, authenticated;
|
|
grant execute on function public.backfill_agentic_rectification_legacy_cases()
|
|
to service_role;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 20. Backfill verification (idempotent; can be re-run anytime)
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
create or replace function public.verify_agentic_rectification_backfill()
|
|
returns jsonb
|
|
language plpgsql
|
|
security definer
|
|
set search_path = ''
|
|
as $$
|
|
declare
|
|
v_total_cases bigint;
|
|
v_resumable bigint;
|
|
v_terminal bigint;
|
|
v_unmapped_results bigint;
|
|
v_legacy_without_case bigint;
|
|
v_orphan_cases bigint;
|
|
v_conflicts bigint;
|
|
begin
|
|
select count(*) into v_total_cases from public.agentic_rectification_cases;
|
|
select count(*) into v_resumable
|
|
from public.agentic_rectification_cases
|
|
where status = any (public.agentic_rectification_resumable_statuses());
|
|
select count(*) into v_terminal
|
|
from public.agentic_rectification_cases
|
|
where status in ('confirmed', 'closed', 'abandoned', 'superseded');
|
|
select count(*) into v_unmapped_results
|
|
from public.agentic_rectification_results
|
|
where case_id is null;
|
|
select count(*) into v_legacy_without_case
|
|
from public.chat_sessions s
|
|
where s.session_type = 'birth_time_rectification'
|
|
and not exists (
|
|
select 1 from public.agentic_rectification_cases c where c.session_id = s.id
|
|
);
|
|
select count(*) into v_orphan_cases
|
|
from public.agentic_rectification_cases c
|
|
left join public.chat_sessions s on s.id = c.session_id
|
|
where s.id is null;
|
|
select count(*) into v_conflicts
|
|
from (
|
|
select user_id
|
|
from public.agentic_rectification_cases
|
|
where status = any (public.agentic_rectification_resumable_statuses())
|
|
group by user_id
|
|
having count(*) > 1
|
|
) conflicts;
|
|
|
|
return jsonb_build_object(
|
|
'total_cases', v_total_cases,
|
|
'resumable_cases', v_resumable,
|
|
'terminal_cases', v_terminal,
|
|
'unmapped_results', v_unmapped_results,
|
|
'legacy_sessions_without_case', v_legacy_without_case,
|
|
'orphan_cases', v_orphan_cases,
|
|
'resumable_conflicts', v_conflicts
|
|
);
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function public.verify_agentic_rectification_backfill()
|
|
from public, anon, authenticated;
|
|
grant execute on function public.verify_agentic_rectification_backfill()
|
|
to service_role;
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
-- 21. Run the one-time backfill inside this migration (apply). The app never
|
|
-- runs bulk legacy migration at startup.
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
do $$
|
|
declare
|
|
v_result jsonb;
|
|
begin
|
|
select public.backfill_agentic_rectification_legacy_cases() into v_result;
|
|
raise notice 'agentic_rectification_v9 backfill: %', v_result;
|
|
end;
|
|
$$;
|
|
|
|
commit;
|