Files
Jyotisha/docs/operations/rect-p0-aggregate-20261116.sql

107 lines
5.8 KiB
SQL

-- Rectification P0 aggregate v1, 2026-11-16.
-- Public business schema through staging 20b02e0a. The Supabase migration source
-- and self-hosted PostgreSQL 17 use the same qualified business tables.
-- Run under the owner's approved read-only reporting identity. One result set:
-- metric, adoption_source, contract, source, rule, report_type, count_display.
-- Every returned count below five is "<5". Only fixed vocabulary is emitted.
-- Scope is profiles with an active accepted/confirmed time. "Retired" describes
-- the current stored source. Reports are ready rows on those profiles, not a
-- claim that each report was originally generated under that adoption.
-- There is no profile-bound history for browser Markdown downloads.
-- Legacy standalone synastry rows have no reliable subject binding and are omitted.
begin transaction isolation level repeatable read read only;
with active_subjects as (
select p.id as owner_id, null::uuid as subject_id, p.active_birth_provenance as provenance,
p.active_birth_date, p.active_birth_time
from public.profiles p
where p.active_birth_time is not null and p.birth_time_status in ('accepted', 'confirmed')
union all
select p.user_id, p.id, p.active_birth_provenance, p.active_birth_date, p.active_birth_time
from public.chart_profiles p
where p.role = 'other' and p.active_birth_time is not null
and p.birth_time_status in ('accepted', 'confirmed')
), attributes as (
select a.owner_id, a.subject_id, a.provenance ->> 'contract' as contract,
a.provenance ->> 'source' as source, a.provenance ->> 'rule' as rule,
a.provenance ->> 'retired_reason' as retired_reason,
coalesce(a.provenance ->> 'decided_by', r.detail #>> '{decision,decided_by}') as decided_by,
r.detail #>> '{provenance,contract}' as receipt_contract,
coalesce(r.detail #>> '{decision,rule}', r.rule) as receipt_rule,
a.provenance -> 'rounds' as rounds
from active_subjects a
left join public.chart_choice_records r
on r.id = case when a.provenance ->> 'record_id' ~* '^[0-9a-f]{8}(-[0-9a-f]{4}){3}-[0-9a-f]{12}$'
then (a.provenance ->> 'record_id')::uuid end
and r.user_id = a.owner_id and r.chart_profile_id is not distinct from a.subject_id
and r.saved_date = a.active_birth_date and r.saved_time = a.active_birth_time
), classified as (
select *,
case
when contract = 'segment-v1' or source = 'server_segment_midpoint' then 'segment-v1'
when source = 'user_choice_from_answers'
or (contract = 'chart-choice-v2' and rule = 'chart_answers') then 'old_questionnaire'
when coalesce(receipt_contract, contract) = 'chart-choice-v1'
and coalesce(receipt_rule, rule) = 'evidence' and decided_by = 'event' then 'old_event'
when coalesce(receipt_rule, rule) = 'user_override' then 'explicit_manual_choice'
when contract = 'chart-choice-v2' and jsonb_typeof(rounds) = 'array' and rounds <> '[]'::jsonb
then 'issue_229_chart_side'
when source in ('reported_time_default', 'window_midpoint_default') then 'declared_default'
else 'unknown'
end as category
from attributes
), safe_groups as (
select category,
case when contract in ('segment-v1', 'chart-choice-v1', 'chart-choice-v2', 'agentic-rectification-v1')
then contract when contract is null then 'missing' else 'other' end as contract,
case when source in ('user_choice', 'user_choice_from_answers', 'server_segment_midpoint',
'reported_time_default', 'window_midpoint_default', 'certain')
then source when source is null then 'missing' else 'other' end as source,
case when rule in ('chart_answers', 'evidence', 'user_override', 'reused_record', 'reported_time_default',
'window_midpoint_default', 'majority', 'tie_keeps_reported', 'strong_override',
'reported_kept', 'stopped', 'round_limit', 'no_line_in_range')
then rule when rule is null then 'missing' else 'other' end as rule
from classified
), retired as (
select owner_id, subject_id
from classified
where retired_reason = 'BUG-1890' or category in ('old_questionnaire', 'old_event', 'segment-v1')
), categories(category) as (
values ('old_questionnaire'), ('old_event'), ('segment-v1'), ('explicit_manual_choice'),
('issue_229_chart_side'), ('declared_default'), ('unknown')
), report_types(report_type) as (
values ('personal_full'), ('annual_full'), ('synastry_full')
), scoped_reports as (
select r.report_type
from public.personal_reports r
where r.status = 'ready' and r.report_type in (select report_type from report_types)
and exists (
select 1 from retired s
where s.owner_id = r.user_id
and (s.subject_id is not distinct from r.chart_profile_id
or (r.report_type = 'synastry_full' and r.partner_chart_profile_id = s.subject_id))
)
), counts as (
select 'provenance' as metric, null::text as adoption_source, contract, source, rule,
null::text as report_type, count(*) as n
from safe_groups
group by contract, source, rule
union all
select 'category', c.category, null, null, null, null, count(s.category)
from categories c left join safe_groups s using (category)
group by c.category
union all
select 'active_retired', null, null, null, null, null, count(*) from retired
union all
select 'ready_reports_on_retired', null, null, null, null, t.report_type, count(r.report_type)
from report_types t left join scoped_reports r using (report_type)
group by t.report_type
)
select metric, adoption_source, contract, source, rule, report_type,
case when n < 5 then '<5' else n::text end as count_display
from counts
order by metric, adoption_source, contract, source, rule, report_type;
commit;