Description

Fixes 4 systemic root causes that left 32 advanced analytical views returning zero/NULL data despite underlying tables having millions of rows. Applied 22 new Liquibase changesets (1.76–1.78), verified application startup, and regenerated full_schema.sql via pg_dump.

Root causes:

Example fix (vote case sensitivity):

-- Before (matches 0 of 3.7M rows):
WHEN vote_data.vote::text = 'Ja' THEN 1
-- After:
WHEN UPPER(vote_data.vote::text) = 'JA' THEN 1

Changesets applied:

• 1.76 (11): Materialized sub-views for decision intelligence timeout, coalition evolution fix, voting anomaly detection, party transition history rebuild, risk summary rebel calculation
• 1.77 (4): Party transition history using assignment_data org_codes, defector/switcher analysis, anomaly pattern Cartesian join elimination
• 1.78 (7): UPPER() vote normalization, risk summary re-apply + rule_group mapping, party performance metrics (status/type/violation filters), effectiveness trends, 3 CASCADE-dropped view recreations

Verified: Application starts (Jetty port 28443, healthcheck OK). Schema regenerated with pg_dump per maintenance guide.

Type of Change

Primary Changes

• 🐛 Bug Fix

Political Analysis

• 📊 Political Data Analysis
  • Party Analysis
  • Riksdagen Integration
• 📈 Analytics & Metrics
  • Performance Metrics
  • Risk Assessment
  • Decision Analysis
  • Transparency Metrics

Technical Changes

• 🏗️ Infrastructure
  • Database Changes
  • Performance Optimization

Impact Analysis

Political Analysis Impact

• Impact on data quality: 32 views with previously-zero columns now populate from actual data (3.7M vote rows, 2.3K rule violations, 94K motions)
• Impact on analysis accuracy: Risk scores, rebel rates, absence rates, violation breakdowns, party member counts, document metrics all corrected
• Impact on transparency features: Party performance, electoral trends, and longitudinal analysis reflect real parliamentary activity

Technical Impact

• Performance impact: Anomaly pattern view reduced from timeout (>5min) to <5s via Cartesian join elimination and materialized sub-views
• Security implications: None — SQL view definition fixes only
• Dependency changes: None

Testing

• Political data validation completed
• Security compliance verified
• Performance testing completed

All 22 changesets validated via Liquibase update, application startup verified (healthcheck OK), schema regenerated and verified.

Documentation

• Package/module documentation updated

Rule group mapping documented inline in SQL comments for backward-compatible column aliasing.

Screenshots

N/A — database view fixes, no UI changes.

Related Issues

Checklist

• Code follows project coding standards
• Comments are clear and helpful
• Documentation is updated
• Tests are passing
• Security compliance is maintained
• Performance impact is acceptable
• Breaking changes are documented
• Changes are backward compatible

Additional Notes

Data sources used for validation:

• vote_data.vote: JA(2.08M), FRÅNVARANDE(742K), NEJ(677K), AVSTÅR(203K)
• rule_violation: 2,301 rows, rule_group ∈ {Behavior, Assignments, Experience, Party, Role, Attribute, Structure}
• rule_violation.status ∈ {MINOR, MAJOR, CRITICAL} — not ACTIVE
• document_data.document_type ∈ {mot, bet, prop, kammakt} — not Motion/Proposition
• person_data.status: Swedish strings — not active/inactive

Security Considerations

• No sensitive data exposed
• Security best practices followed
• Compliance requirements met

Release Notes

Fixed 32 advanced analytical views returning empty/zero data due to case-sensitivity bugs in vote comparisons, incorrect enum values for rule violations and document types, wrong person status filters, and stale schema definitions. Eliminated timeout in election cycle anomaly pattern view. Applied 22 Liquibase changesets (1.76–1.78) and regenerated full_schema.sql.

💡 You can make Copilot smarter by setting up custom instructions, customizing its development environment and configuring Model Context Protocol (MCP) servers. Learn more Copilot coding agent tips in the docs.