🎯 Objective

Create a database view that aggregates decision flow data by political party from DOCUMENT_PROPOSAL_DATA, enabling analysis of party-level proposal success rates, decision patterns, and legislative effectiveness.

📋 Background

The current DecisionDataFactoryImpl processes individual proposal decisions, but lacks aggregated party-level intelligence. This view will support:

• Party decision effectiveness tracking
• Coalition alignment on proposals
• Ministry proposal success rates by party
• Temporal decision trends by party

Context from Documentation:

• DATABASE_VIEW_INTELLIGENCE_CATALOG.md - No party decision views exist yet
• DATA_ANALYSIS_INTOP_OSINT.md - Comparative analysis framework needs party decision metrics
• DecisionDataFactoryImpl.java - Processes proposal data

📊 Current State

• ✅ DecisionDataFactoryImpl processes DOCUMENT_PROPOSAL_DATA
• ❌ No database view for party-level decision aggregation
• ❌ No KPIs for party decision effectiveness
• ❌ No temporal trend analysis for party decisions

✅ Acceptance Criteria

• Create view_riksdagen_party_decision_flow database view
• Include metrics: total_proposals, approved_proposals, rejected_proposals, approval_rate, decision_types
• Aggregate by party, decision_type, committee, and time period (year/month)
• Add to new changelog file db-changelog-1.35.xml
• Document in DATABASE_VIEW_INTELLIGENCE_CATALOG.md with sample queries
• Include performance index on party + date columns
• Validation: View returns data for all parliamentary parties

🛠️ Implementation Guidance

Files to Create/Modify:

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

<changeSet id="create_view_riksdagen_party_decision_flow" author="intelligence-operative">
    <createView viewName="view_riksdagen_party_decision_flow" replaceIfExists="true">
        <![CDATA[
        SELECT 
            party,
            committee,
            decision_type,
            DATE_TRUNC('month', decision_date) AS decision_month,
            EXTRACT(YEAR FROM decision_date) AS decision_year,
            COUNT(*) AS total_proposals,
            COUNT(*) FILTER (WHERE decision_outcome = 'approved') AS approved_proposals,
            COUNT(*) FILTER (WHERE decision_outcome = 'rejected') AS rejected_proposals,
            ROUND(100.0 * COUNT(*) FILTER (WHERE decision_outcome = 'approved') / COUNT(*), 2) AS approval_rate
        FROM document_proposal_data dpd
        JOIN document_status_container dsc ON dsc.id = dpd.document_status_id
        JOIN document_data dd ON dd.id = dsc.document_id
        GROUP BY party, committee, decision_type, decision_month, decision_year
        ]]>
    </createView>
</changeSet>

2. DATABASE_VIEW_INTELLIGENCE_CATALOG.md - Add documentation
   • Section: "Decision Flow Views (NEW in v1.35)"
   • Include purpose, key metrics, sample queries, intelligence applications

Approach:

1. Analyze document_proposal_data table schema
2. Join with document_status_container and document_data for party information
3. Create aggregation logic for decision outcomes
4. Test query performance with realistic data volumes
5. Add materialized view option for performance if needed

Sample Query for Testing:

SELECT party, decision_year, 
       total_proposals, approved_proposals, 
       approval_rate
FROM view_riksdagen_party_decision_flow
WHERE decision_year >= 2023
ORDER BY approval_rate DESC;

🤖 Recommended Agent

Agent: @hack23-intelligence-operative
Rationale: This issue requires deep understanding of Swedish parliamentary decision processes, OSINT data analysis methodologies, and political intelligence product development.

For implementation, the Intelligence Operative will:

• Analyze DOCUMENT_PROPOSAL_DATA structure for decision flow patterns
• Design SQL view with political science best practices
• Integrate with existing party intelligence views
• Document intelligence applications for decision analysis
• Ensure data quality and accuracy for political analysis

📚 Related Documentation

• DATABASE_VIEW_INTELLIGENCE_CATALOG.md
• DATA_ANALYSIS_INTOP_OSINT.md
• BUSINESS_PRODUCT_DOCUMENT.md
• Intelligence Operative Agent Profile

🏷️ Labels

feature, database, intelligence, decision-flow, osint

📊 Intelligence Value

⭐⭐⭐⭐⭐ VERY HIGH - Enables party-level decision pattern analysis, coalition dynamics assessment, and legislative effectiveness tracking.