🎯 Objective

Create a database view tracking individual politician decision patterns from DOCUMENT_PROPOSAL_DATA, enabling analysis of politician-level proposal success rates, committee work effectiveness, and legislative productivity.

📋 Background

While view_riksdagen_politician_document tracks document authorship, we lack decision outcome intelligence at the politician level. This view complements Issue #7918 (party-level) by providing individual politician decision analytics.

Use Cases:

• Politician scorecard: proposal success rate
• Committee specialist identification
• Ministry proposal support patterns
• Cross-party collaboration on decisions

Context from Documentation:

• DATABASE_VIEW_INTELLIGENCE_CATALOG.md - Politician views (8 existing)
• DATA_ANALYSIS_INTOP_OSINT.md - Pattern recognition framework

📊 Current State

• ✅ Politician document views exist (authorship tracking)
• ❌ No decision outcome tracking per politician
• ❌ No proposal effectiveness metrics
• ❌ No committee decision patterns

✅ Acceptance Criteria

• Create view_riksdagen_politician_decision_pattern database view
• Include metrics: person_id, total_decisions, approved_rate, committee_focus, decision_types
• Aggregate by politician, committee, decision_type, year/month
• Add to db-changelog-1.35.xml (same changelog as Create Party Decision Flow View from DOCUMENT_PROPOSAL_DATA #7918)
• Document in DATABASE_VIEW_INTELLIGENCE_CATALOG.md with examples
• Performance index on person_id + date
• Validation: Returns data for active parliamentarians

🛠️ Implementation Guidance

Files to Modify:

1. service.data.impl/src/main/resources/db-changelog-1.35.xml (append changeSet)

<changeSet id="create_view_riksdagen_politician_decision_pattern" author="intelligence-operative">
    <createView viewName="view_riksdagen_politician_decision_pattern" replaceIfExists="true">
        <![CDATA[
        SELECT 
            pd.person_id,
            pd.first_name,
            pd.last_name,
            pd.party,
            dpd.committee,
            dpd.decision_type,
            DATE_TRUNC('month', dpd.decision_date) AS decision_month,
            EXTRACT(YEAR FROM dpd.decision_date) AS decision_year,
            COUNT(*) AS total_decisions,
            COUNT(*) FILTER (WHERE dpd.decision_outcome = 'approved') AS approved_decisions,
            COUNT(*) FILTER (WHERE dpd.decision_outcome = 'rejected') AS rejected_decisions,
            ROUND(100.0 * COUNT(*) FILTER (WHERE dpd.decision_outcome = 'approved') / COUNT(*), 2) AS approval_rate,
            COUNT(DISTINCT dpd.committee) AS committees_active,
            STRING_AGG(DISTINCT dpd.decision_type, ', ') AS decision_types
        FROM person_data pd
        JOIN document_proposal_data dpd ON dpd.person_id = pd.person_id
        GROUP BY pd.person_id, pd.first_name, pd.last_name, pd.party, 
                 dpd.committee, dpd.decision_type, decision_month, decision_year
        ]]>
    </createView>
</changeSet>

2. DATABASE_VIEW_INTELLIGENCE_CATALOG.md - Add under "Politician Views" section

Sample Query:

-- Top 10 most effective politicians by approval rate (last year)
SELECT first_name, last_name, party, 
       total_decisions, approval_rate,
       committees_active
FROM view_riksdagen_politician_decision_pattern
WHERE decision_year = EXTRACT(YEAR FROM CURRENT_DATE)
  AND total_decisions >= 5
ORDER BY approval_rate DESC, total_decisions DESC
LIMIT 10;

Edge Cases:

• Politicians with no decision data (exclude from results)
• Multiple parties over time (use current party)
• Decision date null values (filter out)

🤖 Recommended Agent

Agent: @hack23-intelligence-operative
Rationale: Requires political science expertise to design meaningful politician-level decision metrics and integrate with existing politician intelligence views.

For implementation, the Intelligence Operative will:

• Design politician decision effectiveness metrics
• Identify key decision pattern indicators
• Integrate with view_riksdagen_politician_summary
• Create intelligence queries for politician scorecards
• Validate against known high-performing politicians

📚 Related Documentation

• Issue Create Party Decision Flow View from DOCUMENT_PROPOSAL_DATA #7918 - Party Decision Flow View (related)
• DATABASE_VIEW_INTELLIGENCE_CATALOG.md - Politician Views section
• DATA_ANALYSIS_INTOP_OSINT.md - Comparative analysis framework

🏷️ Labels

feature, database, intelligence, politician-analysis

📊 Intelligence Value

⭐⭐⭐⭐⭐ VERY HIGH - Enables individual politician effectiveness tracking and committee specialization analysis.