fix(scorecard): presence_rate formula has 4 methodological flaws

[Walid-peach]

Current formula (mart_deputy_scorecard.sql)

-- numerator: every vote_positions row for this deputy (including nonVotant)
count(*) as votes_cast

-- denominator: global count of ALL scrutins in the DB
select count(*) as total from stg_votes

-- rate
round(votes_cast::numeric / total, 4) as presence_rate

---

Flaw 1 — nonVotant is counted as present (but docs say it isn't)

CLAUDE.md and README.md both state:

nonVotant ≠ abstention: present in chamber but did not vote vs. formally abstained

The intent documented in schemas.py is:

presence_rate: present_votes / total_votes
present_votes: Votes where position is not nonVotant

But votes_cast in the mart is count(*) over all positions including nonVotant. A deputy who shows up but never votes still scores 100%. The API returns a separate present_votes = total_votes_cast - total_nonvotant field, but presence_rate is read directly from the mart unchanged — so the displayed rate contradicts the documented definition.

Fix: change the mart numerator to count(*) filter (where position != 'nonvotant').

---

Flaw 2 — Denominator is global, not per-deputy-eligible

The denominator is a single SELECT COUNT(*) FROM stg_votes — the total scrutins in the database, regardless of when a deputy's mandate started or ended.

Example: a deputy who took their seat 3 months into the legislature is penalized for the first 3 months of scrutins they were ineligible to participate in. Their presence_rate will never reach 100% even if they voted in every single session of their actual mandate.

Fix: join deputies.mandate_started_at and count only votes.voted_at >= deputy.mandate_started_at (and <= mandate_ended_at if applicable).

---

Flaw 3 — Inconsistent definition between mart and RAG SQL router

rag/chain/sql_router.py uses a completely different formula for presence_rate in party-level queries:

-- RAG SQL router (per-deputy denominator)
COUNT(*) FILTER (WHERE position IN ('pour','contre','abstention'))::numeric
/ NULLIF(COUNT(*), 0) as presence_rate

Here the denominator is each deputy's own total positions (not global). This means the AI chat and the scorecard page can show conflicting numbers for the same metric.

Fix: align sql_router.py to use the corrected mart formula once Flaws 1 and 2 are resolved.

---

Flaw 4 — Production data horizon distorts comparisons

Production Supabase holds votes from 2025-07-01 only (free-tier limit). Deputies who were very active before that date but less active after look artificially good — and newcomers who arrived post-cutoff look artificially bad — because the denominator shrinks while per-deputy rows are also truncated.

This isn't a code bug but a known data constraint. It should be disclosed in the UI wherever a presence percentage is shown (e.g. a tooltip: "Calculé sur les votes depuis le 1er juillet 2025").

---

Summary

#	Flaw	Impact	Fix location
1	nonVotant counted as present	Rate overstated for deputies who show up but don't vote	mart_deputy_scorecard.sql numerator
2	Global denominator ignores mandate start date	Rate understated for mid-legislature arrivals	mart_deputy_scorecard.sql denominator
3	RAG SQL router uses a different formula	AI answers contradict the scorecard page	rag/chain/sql_router.py
4	Production data starts 2025-07-01	All rates relative to 11 months, not full legislature	UI tooltip disclosure

---

Proposed corrected formula

deputy_stats as (
    select
        p.deputy_id,
        count(*) filter (where p.position != 'nonvotant')  as votes_present,
        count(*) filter (where p.position = 'pour')        as votes_pour,
        count(*) filter (where p.position = 'contre')      as votes_contre,
        count(*) filter (where p.position = 'abstention')  as votes_abstention,
        count(*) filter (where p.position = 'nonvotant')   as votes_nonvotant
    from positions p
    group by p.deputy_id
),

eligible_votes as (
    select
        d.deputy_id,
        count(v.vote_id) as eligible_total
    from deputies d
    join stg_votes v
      on v.voted_at >= d.mandate_started_at
     and (d.mandate_ended_at is null or v.voted_at <= d.mandate_ended_at)
    group by d.deputy_id
)

-- presence_rate in final:
round(
    coalesce(s.votes_present, 0)::numeric / nullif(e.eligible_total, 0),
    4
) as presence_rate

Files to change (future PR)

• transform/models/marts/mart_deputy_scorecard.sql
• rag/chain/sql_router.py
• frontend/src/app/deputes/[id]/page.tsx (data-horizon tooltip)
• transform/models/marts/schema.yml (dbt test bounds)