107 lines
5.8 KiB
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;
|