| name | legislative-monitoring |
| description | Voting pattern analysis, committee effectiveness, bill tracking, parliamentary oversight for Swedish intelligence operations |
| license | Apache-2.0 |
Legislative Monitoring Skill
Purpose
This skill provides comprehensive legislative monitoring capabilities for the CIA platform, enabling systematic tracking of parliamentary activities, voting patterns, committee effectiveness, bill progression, and oversight mechanisms. It transforms raw parliamentary data into actionable intelligence for understanding legislative dynamics, predicting policy outcomes, and assessing institutional effectiveness within the Swedish Riksdag.
When to Use This Skill
Apply this skill when:
- ✅ Tracking bill progression through legislative process
- ✅ Analyzing voting deviation patterns within and across parties
- ✅ Assessing committee productivity and effectiveness
- ✅ Monitoring parliamentary oversight activities (questions, interpellations)
- ✅ Predicting legislative outcomes based on historical patterns
- ✅ Identifying cross-party collaboration opportunities
- ✅ Evaluating individual parliamentarian productivity
- ✅ Detecting legislative obstruction or procedural manipulation
- ✅ Measuring government vs. opposition effectiveness
- ✅ Tracking amendment success rates and strategic positioning
Do NOT use for:
- ❌ Manipulating legislative processes through intelligence
- ❌ Interfering with democratic parliamentary procedures
- ❌ Undermining legitimate opposition activities
- ❌ Violating parliamentary privilege or confidentiality
Legislative Monitoring Framework
Five Core Analytical Dimensions
The CIA platform monitors Swedish parliamentary activities across five critical dimensions to provide comprehensive legislative intelligence:
graph TB
subgraph "Legislative Data Collection"
A1["🗳️ Voting Records<br/>3.5M+ votes<br/>1971-present"]
A2["📄 Bill Tracking<br/>89K+ documents<br/>Full lifecycle"]
A3["👥 Committee Activity<br/>15 committees<br/>Productivity metrics"]
A4["🔍 Oversight Actions<br/>Questions, interpellations<br/>Accountability measures"]
A5["🤝 Cross-Party Collaboration<br/>Co-authorship networks<br/>Coalition patterns"]
end
subgraph "Legislative Intelligence Analysis"
A1 --> B1[Deviation Pattern Analysis]
A2 --> B2[Bill Success Prediction]
A3 --> B3[Committee Effectiveness Assessment]
A4 --> B4[Oversight Effectiveness Tracking]
A5 --> B5[Coalition Stability Measurement]
end
subgraph "Intelligence Products"
B1 --> C1[Voting Intelligence]
B2 --> C2[Legislative Forecasts]
B3 --> C3[Institutional Health Reports]
B4 --> C4[Accountability Metrics]
B5 --> C5[Coalition Analysis]
end
subgraph "Strategic Intelligence"
C1 & C2 & C3 & C4 & C5 --> D["📊 Legislative Dashboard"]
D --> E[Policy Outcome Predictions]
D --> F[Institutional Risk Assessment]
D --> G[Coalition Stability Forecasts]
end
style A1 fill:#e1f5ff
style A2 fill:#e1f5ff
style A3 fill:#e1f5ff
style A4 fill:#e1f5ff
style A5 fill:#e1f5ff
style D fill:#ffe6cc
style E fill:#ccffcc
style F fill:#ffcccc
style G fill:#fff9cc
1. Voting Deviation Pattern Analysis
Party Discipline Measurement
Voting deviation from party lines reveals internal party conflicts, ideological tensions, and coalition instability. The CIA platform tracks these patterns with temporal granularity.
Database Views:
view_riksdagen_vote_data_ballot_politician_summary_daily - Daily voting patternsview_riksdagen_vote_data_ballot_politician_summary_monthly - Monthly aggregatesview_riksdagen_vote_data_ballot_politician_summary_annual - Annual trendsview_riksdagen_politician_ballot_summary - Career-wide voting statisticsview_riksdagen_politician_decision_pattern - Decision pattern classification
Deviation Intelligence Framework
@Service
public class VotingDeviationAnalyzer {
public DeviationIntelligence analyzePartyDeviation(
String partyId,
LocalDate startDate,
LocalDate endDate
) {
String sql = """
WITH party_voting AS (
SELECT
v.ballot_id,
b.vote_date,
b.issue_category,
b.issue_subcategory,
b.vote_outcome,
-- Party position (mode of party votes)
MODE() WITHIN GROUP (ORDER BY v.vote) as party_position,
-- Deviation metrics
COUNT(*) as party_member_votes,
COUNT(DISTINCT v.vote) as vote_diversity,
-- Rebel identification
COUNT(CASE
WHEN v.vote != (MODE() WITHIN GROUP (ORDER BY v.vote))
THEN 1 END
) as rebel_count,
-- Rebel percentage
COUNT(CASE
WHEN v.vote != (MODE() WITHIN GROUP (ORDER BY v.vote))
THEN 1 END
)::float / NULLIF(COUNT(*), 0) * 100 as rebel_percentage,
-- Identify specific rebels
ARRAY_AGG(
CASE
WHEN v.vote != (MODE() WITHIN GROUP (ORDER BY v.vote))
THEN p.person_id::text
END
) FILTER (WHERE v.vote != (MODE() WITHIN GROUP (ORDER BY v.vote))) as rebel_ids
FROM vote v
JOIN ballot b ON v.ballot_id = b.ballot_id
JOIN person p ON v.person_id = p.person_id
WHERE p.party = :partyId
AND b.vote_date BETWEEN :startDate AND :endDate
GROUP BY v.ballot_id, b.vote_date, b.issue_category,
b.issue_subcategory, b.vote_outcome
),
deviation_patterns AS (
SELECT
issue_category,
COUNT(*) as total_votes,
AVG(rebel_percentage) as avg_deviation_rate,
MAX(rebel_percentage) as max_deviation_rate,
STDDEV(rebel_percentage) as deviation_volatility,
-- High-deviation votes (>15% rebels)
COUNT(CASE WHEN rebel_percentage > 15 THEN 1 END) as high_deviation_votes,
-- Identify consistent rebels
(
SELECT person_id, COUNT(*) as rebel_count
FROM UNNEST(ARRAY_AGG(rebel_ids)) as person_id
GROUP BY person_id
ORDER BY COUNT(*) DESC
LIMIT 10
) as top_rebels
FROM party_voting
GROUP BY issue_category
)
SELECT
p.party as party_id,
p.party_name,
-- Overall deviation metrics
AVG(dp.avg_deviation_rate) as overall_deviation_rate,
MAX(dp.max_deviation_rate) as peak_deviation,
AVG(dp.deviation_volatility) as avg_volatility,
-- High-risk categories (high deviation)
ARRAY_AGG(
dp.issue_category
ORDER BY dp.avg_deviation_rate DESC
) FILTER (WHERE dp.avg_deviation_rate > 10) as high_risk_categories,
-- Discipline classification
CASE
WHEN AVG(dp.avg_deviation_rate) < 3 THEN 'HIGHLY_DISCIPLINED'
WHEN AVG(dp.avg_deviation_rate) < 7 THEN 'MODERATELY_DISCIPLINED'
WHEN AVG(dp.avg_deviation_rate) < 12 THEN 'LOW_DISCIPLINE'
ELSE 'FRACTURED'
END as discipline_classification,
-- Risk assessment
CASE
WHEN AVG(dp.avg_deviation_rate) > 15
OR MAX(dp.max_deviation_rate) > 30
THEN 'CRITICAL_INSTABILITY'
WHEN AVG(dp.avg_deviation_rate) > 10
OR MAX(dp.max_deviation_rate) > 20
THEN 'HIGH_FRACTURE_RISK'
WHEN AVG(dp.avg_deviation_rate) > 5
THEN 'MODERATE_TENSION'
ELSE 'STABLE'
END as stability_risk
FROM party p
JOIN deviation_patterns dp ON 1=1
WHERE p.party = :partyId
GROUP BY p.party, p.party_name
""";
jdbcTemplate.queryForObject(sql, DeviationIntelligence.class,
Map.of(
, partyId,
, startDate,
, endDate
));
}
}
Deviation Pattern Taxonomy
| Deviation Rate | Discipline Level | Political Implications | Intelligence Assessment |
|---|
| < 3% | HIGHLY_DISCIPLINED | Strong leadership control, unified ideology | Reliable coalition partner |
| 3-7% | MODERATELY_DISCIPLINED | Occasional dissent, manageable tensions | Generally stable, monitor key issues |
| 7-12% | LOW_DISCIPLINE | Frequent internal conflicts, weak leadership | Coalition risks, factional monitoring needed |
| > 12% | FRACTURED | Severe internal divisions, leadership crisis | High breakaway risk, unstable partner |
Strategic Intelligence Applications
from typing import Dict, List
import pandas as pd
import numpy as np
class DeviationIntelligenceAnalyzer:
"""
Applies deviation pattern intelligence to strategic assessments.
Intelligence products:
1. Coalition stability forecasts
2. Leadership vulnerability assessment
3. Policy area risk mapping
4. Factional structure identification
"""
def assess_coalition_stability(
self,
coalition_parties: List[str],
date_from: str,
date_to: str
) -> Dict:
"""
Assesses coalition stability through deviation pattern analysis.
Coalition stability factors:
- Intra-party discipline (low deviation = stable)
- Cross-party voting alignment (high alignment = stable)
- Deviation trend (increasing deviation = instability)
"""
query = """
WITH coalition_deviation AS (
SELECT
p.party,
b.vote_date,
b.issue_category,
-- Party deviation rate
COUNT(CASE
WHEN v.vote != (
SELECT MODE() WITHIN GROUP (ORDER BY v2.vote)
FROM vote v2
JOIN person p2 ON v2.person_id = p2.person_id
WHERE p2.party = p.party
AND v2.ballot_id = v.ballot_id
) THEN 1 END
)::float / NULLIF(COUNT(*), 0) * 100 as deviation_rate
FROM vote v
JOIN person p ON v.person_id = p.person_id
JOIN ballot b ON v.ballot_id = b.ballot_id
WHERE p.party = ANY(%s)
AND b.vote_date BETWEEN %s AND %s
GROUP BY p.party, b.vote_date, b.issue_category, v.ballot_id
),
cross_party_alignment AS (
-- Measure voting alignment between coalition parties
SELECT
p1.party as party_a,
p2.party as party_b,
COUNT(CASE WHEN v1.vote = v2.vote THEN 1 END)::float /
NULLIF(COUNT(*), 0) * 100 as alignment_rate
FROM vote v1
JOIN person p1 ON v1.person_id = p1.person_id
JOIN vote v2 ON v1.ballot_id = v2.ballot_id
JOIN person p2 ON v2.person_id = p2.person_id
WHERE p1.party = ANY(%s)
AND p2.party = ANY(%s)
AND p1.party < p2.party -- Avoid duplicates
GROUP BY p1.party, p2.party
)
SELECT
cd.party,
AVG(cd.deviation_rate) as avg_deviation,
STDDEV(cd.deviation_rate) as deviation_volatility,
-- Trend analysis (linear regression slope)
REGR_SLOPE(
cd.deviation_rate,
EXTRACT(EPOCH FROM cd.vote_date)
) as deviation_trend,
-- Cross-party alignment (average with coalition partners)
(SELECT AVG(cpa.alignment_rate)
FROM cross_party_alignment cpa
WHERE cpa.party_a = cd.party OR cpa.party_b = cd.party
) as coalition_alignment
FROM coalition_deviation cd
GROUP BY cd.party
"""
results = pd.read_sql(
query,
.connection,
params=[coalition_parties, date_from, date_to,
coalition_parties, coalition_parties]
)
stability_components = []
_, party results.iterrows():
deviation_stability = (, ( - party[]) / ) *
alignment_stability = (party[] / ) *
trend_stability = (, - (party[]) * )
party_stability = deviation_stability + alignment_stability + trend_stability
stability_components.append({
: party[],
: (party_stability, ),
: (party[], ),
: (party[], ),
: (party[], )
})
overall_stability = np.mean([p[] p stability_components])
{
: coalition_parties,
: ,
: (overall_stability, ),
: stability_components,
: ._classify_stability(overall_stability),
: ._assess_stability_risks(stability_components)
}
() -> :
score >= :
score >= :
score >= :
:
() -> []:
risks = []
party components:
party[] > :
risks.append()
party[] < :
risks.append()
party[] > :
risks.append()
risks risks []
2. Committee Effectiveness Assessment
Parliamentary Committee Intelligence
Committees are the workhorses of legislative bodies. The CIA platform measures their productivity, influence, and effectiveness.
Database Views:
view_riksdagen_committee_decisions - Committee decision trackingview_riksdagen_committee_document - Committee-produced documentsview_riksdagen_committee_member_activity - Individual member contributionsview_committee_effectiveness_metrics - Composite effectiveness measures
Committee Productivity Metrics
WITH committee_activity AS (
SELECT
c.org_code,
c.committee_name,
c.policy_area,
COUNT(DISTINCT cd.document_id) as total_documents,
COUNT(DISTINCT CASE WHEN cd.document_type = 'committee_report'
THEN cd.document_id END) as reports_produced,
COUNT(DISTINCT CASE WHEN cd.document_type = 'committee_proposal'
THEN cd.document_id END) as proposals_made,
COUNT(DISTINCT CASE WHEN cd.adopted_by_parliament = TRUE
THEN cd.document_id END) as adopted_proposals,
COUNT(DISTINCT CASE WHEN cd.influenced_final_legislation = TRUE
THEN cd.document_id END) as legislative_influence,
COUNT( cm.person_id) member_count,
(cm.attendance_rate) avg_attendance,
(cm.contribution_score) avg_contribution,
( cd.document_type
cd.document_id ) oversight_actions,
( cd.oversight_response_received
cd.document_id ) oversight_responses,
(cd.processing_time_days) avg_processing_time,
STDDEV(cd.processing_time_days) processing_time_variance,
(
( dp.person_id)::
( p.party)
document_person dp
person p dp.person_id p.person_id
dp.document_id (
document_id
committee_document
org_code c.org_code
)
) cross_party_engagement
committee c
committee_document cd c.org_code cd.org_code
committee_member cm c.org_code cm.org_code
c.active
cd.created_date NOW()
c.org_code, c.committee_name, c.policy_area
),
committee_effectiveness (
ca.,
(
LEAST(ca.total_documents , )
LEAST(ca.reports_produced , )
(ca.adopted_proposals:: (ca.proposals_made, ))
(ca.oversight_responses:: (ca.oversight_actions, ))
ca.avg_attendance
) productivity_score,
(
(ca.adopted_proposals:: (ca.proposals_made, ))
(ca.legislative_influence:: (ca.total_documents, ))
LEAST(ca.adopted_proposals , )
) impact_score,
(
ca.avg_processing_time
ca.avg_processing_time
ca.avg_processing_time
ca.avg_processing_time
) efficiency_score,
(
ca.cross_party_engagement
ca.avg_contribution
) collaboration_score,
(
(
LEAST(ca.total_documents , )
LEAST(ca.reports_produced , )
(ca.adopted_proposals:: (ca.proposals_made, ))
(ca.oversight_responses:: (ca.oversight_actions, ))
ca.avg_attendance
)
(
(ca.adopted_proposals:: (ca.proposals_made, ))
(ca.legislative_influence:: (ca.total_documents, ))
LEAST(ca.adopted_proposals , )
)
(
ca.avg_processing_time
ca.avg_processing_time
ca.avg_processing_time
ca.avg_processing_time
)
(
ca.cross_party_engagement
ca.avg_contribution
)
) overall_effectiveness_score
committee_activity ca
)
org_code,
committee_name,
policy_area,
total_documents,
reports_produced,
adopted_proposals,
proposals_made,
ROUND(productivity_score, ) productivity,
ROUND(impact_score, ) impact,
ROUND(efficiency_score, ) efficiency,
ROUND(collaboration_score, ) collaboration,
ROUND(overall_effectiveness_score, ) overall_effectiveness,
overall_effectiveness_score
overall_effectiveness_score
overall_effectiveness_score
effectiveness_classification,
overall_effectiveness_score
efficiency_score
collaboration_score
overall_effectiveness_score
efficiency_score
institutional_health
committee_effectiveness
overall_effectiveness_score ;
3. Bill Success Prediction
Legislative Outcome Forecasting
The CIA platform predicts bill success based on historical patterns, sponsor characteristics, coalition dynamics, and timing factors.
from sklearn.ensemble import RandomForestClassifier
from sklearn.preprocessing import StandardScaler
import pandas as pd
import numpy as np
class BillSuccessPredictor:
"""
Predicts bill passage likelihood using machine learning on historical data.
Features:
- Sponsor characteristics (party, experience, influence)
- Coalition structure (government vs. opposition)
- Bill characteristics (policy area, complexity, novelty)
- Temporal factors (timing in parliamentary session)
- Historical patterns (similar bill success rates)
"""
def __init__(self):
self.model = RandomForestClassifier(n_estimators=100, max_depth=10)
self.scaler = StandardScaler()
self.trained = False
def prepare_training_data(self) -> Tuple[pd.DataFrame, pd.Series]:
"""
Extracts historical bill data for model training.
Returns features and labels (passed/failed).
"""
query = """
SELECT
d.document_id,
d.document_type,
d.issue_category,
d.issue_subcategory,
d.document_complexity_score,
d.document_novelty_score,
-- Sponsor characteristics
p.party,
p.years_in_parliament,
vim.influence_score,
vim.network_centrality,
vim.collaboration_score,
-- Coalition context
CASE WHEN p.party IN (SELECT party FROM government_coalition)
THEN TRUE ELSE FALSE END as is_government_sponsor,
-- Bill characteristics
LENGTH(dc.text_content) as bill_length,
(SELECT COUNT(*) FROM document_person WHERE document_id = d.document_id) as co_sponsor_count,
(SELECT COUNT(DISTINCT party) FROM document_person dp
JOIN person p2 ON dp.person_id = p2.person_id
WHERE dp.document_id = d.document_id) as party_diversity,
-- Temporal factors
EXTRACT(MONTH FROM d.created_date) as submission_month,
EXTRACT(DAY FROM (
SELECT MAX(s.session_end_date) FROM parliamentary_session s
WHERE d.created_date BETWEEN s.session_start_date AND s.session_end_date
) - d.created_date) as days_before_session_end,
-- Historical success rate for similar bills
(
SELECT AVG(CASE WHEN adopted = TRUE THEN 1.0 ELSE 0.0 END)
FROM document d2
WHERE d2.issue_category = d.issue_category
AND d2.created_date < d.created_date
AND d2.created_date >= d.created_date - INTERVAL '5 years'
) as category_historical_success_rate,
-- Label: Did the bill pass?
CASE WHEN d.adopted_by_parliament = TRUE THEN 1 ELSE 0 END as passed
FROM document d
JOIN document_person dp ON d.document_id = dp.document_id
JOIN person p ON dp.person_id = p.person_id
JOIN view_riksdagen_politician_influence_metrics vim ON p.person_id = vim.person_id
JOIN document_content dc ON d.document_id = dc.document_id
WHERE d.document_type IN ('motion', 'bill', 'proposal')
AND d.final_status IS NOT NULL -- Only completed bills
AND d.created_date >= '2010-01-01' -- Last 15 years
"""
df = pd.read_sql(query, .connection)
df[] = pd.Categorical(df[]).codes
df[] = pd.Categorical(df[]).codes
df[] = df[] * df[]
df[] = df[].astype() *
df[] = np.where(df[] < , , )
feature_cols = [
, , ,
, , ,
, , ,
, , , ,
, ,
, ,
]
X = df[feature_cols]
y = df[]
X, y
():
X, y = .prepare_training_data()
X_scaled = .scaler.fit_transform(X)
.model.fit(X_scaled, y)
.trained =
sklearn.model_selection cross_val_score
cv_scores = cross_val_score(.model, X_scaled, y, cv=)
{
: (X),
: (np.mean(cv_scores), ),
: ((
X.columns,
[(imp, ) imp .model.feature_importances_]
))
}
() -> :
.trained:
ValueError()
probability = .model.predict_proba(bill_features_scaled)[][]
{
: bill_id,
: (probability, ),
: probability >
probability > ,
: (probability - ) > ,
: ._identify_key_factors(bill_features),
: ._generate_recommendations(
bill_features,
probability
)
}
() -> []:
feature_importance = .model.feature_importances_
top_features = (
(features.columns, feature_importance, features.iloc[]),
key= x: x[],
reverse=
)[:]
[
feat, imp, val top_features]
() -> []:
recommendations = []
features[].iloc[] == probability < :
recommendations.append(
)
features[].iloc[] < :
recommendations.append(
)
features[].iloc[] < :
recommendations.append(
)
features[].iloc[] < :
recommendations.append(
)
features[].iloc[] > :
recommendations.append(
)
recommendations recommendations [
]
4. Parliamentary Oversight Tracking
Accountability Mechanism Analysis
Parliamentary questions, interpellations, and oversight requests are key accountability tools. The CIA platform tracks their effectiveness.
Database Views:
view_riksdagen_oversight_activity - All oversight actionsview_riksdagen_question_response_time - Response timelinessview_riksdagen_oversight_effectiveness - Impact assessment
@Service
public class OversightEffectivenessAnalyzer {
public OversightProfile analyzeOversight(
String politicianId,
LocalDate startDate,
LocalDate endDate
) {
String sql = """
WITH oversight_actions AS (
SELECT
d.document_id,
d.document_type, -- 'question', 'interpellation', 'oversight_request'
d.created_date as submitted_date,
d.target_minister_id,
d.target_ministry,
d.issue_category,
-- Response tracking
dr.response_document_id,
dr.response_date,
EXTRACT(DAY FROM dr.response_date - d.created_date) as response_time_days,
dr.response_quality_score, -- 0-1, algorithmic assessment
dr.substantive_response, -- Boolean: Did it address the question?
-- Impact tracking
di.policy_change_resulted,
di.media_coverage_count,
di.follow_up_actions_count,
di.public_attention_score
FROM document d
LEFT JOIN document_response dr ON d.document_id = dr.question_document_id
LEFT JOIN document_impact di ON d.document_id = di.document_id
JOIN document_person dp ON d.document_id = dp.document_id
WHERE dp.person_id = :politicianId
AND d.document_type IN ('question', 'interpellation', 'oversight_request')
AND d.created_date BETWEEN :startDate AND :endDate
)
SELECT
p.person_id,
p.first_name || ' ' || p.last_name as name,
p.party,
-- Volume metrics
COUNT(*) as total_oversight_actions,
COUNT(DISTINCT oa.issue_category) as issue_diversity,
COUNT(DISTINCT oa.target_minister_id) as ministers_targeted,
-- Response metrics
COUNT(oa.response_document_id)::float /
NULLIF(COUNT(*), 0) * 100 as response_rate,
AVG(oa.response_time_days) as avg_response_time,
COUNT(CASE WHEN oa.substantive_response = TRUE THEN 1 END)::float /
NULLIF(COUNT(oa.response_document_id), 0) * 100 as substantive_response_rate,
AVG(oa.response_quality_score) * 100 as avg_response_quality,
-- Impact metrics
COUNT(CASE WHEN oa.policy_change_resulted = TRUE THEN 1 END) as policy_changes_achieved,
AVG(oa.media_coverage_count) as avg_media_attention,
SUM(oa.follow_up_actions_count) as total_follow_ups,
AVG(oa.public_attention_score) * 100 as avg_public_attention,
-- Effectiveness score (0-100)
(
(COUNT(oa.response_document_id)::float / NULLIF(COUNT(*), 0) * 100) * 0.20 + -- Response rate (20%)
(COUNT(CASE WHEN oa.substantive_response = TRUE THEN 1 END)::float /
NULLIF(COUNT(oa.response_document_id), 0) * 100) * 0.25 + -- Substantive rate (25%)
(CASE
WHEN AVG(oa.response_time_days) <= 7 THEN 100
WHEN AVG(oa.response_time_days) <= 14 THEN 75
WHEN AVG(oa.response_time_days) <= 30 THEN 50
WHEN AVG(oa.response_time_days) <= 60 THEN 25
ELSE 10
END) * 0.15 + -- Timeliness (15%)
(COUNT(CASE WHEN oa.policy_change_resulted = TRUE THEN 1 END)::float /
NULLIF(COUNT(*), 0) * 100) * 0.25 + -- Policy impact (25%)
(AVG(oa.public_attention_score) * 100) * 0.15 -- Public attention (15%)
) as oversight_effectiveness_score,
-- Classification
CASE
WHEN (
(COUNT(oa.response_document_id)::float / NULLIF(COUNT(*), 0) * 100) * 0.20 +
(COUNT(CASE WHEN oa.substantive_response = TRUE THEN 1 END)::float /
NULLIF(COUNT(oa.response_document_id), 0) * 100) * 0.25 +
(CASE
WHEN AVG(oa.response_time_days) <= 7 THEN 100
WHEN AVG(oa.response_time_days) <= 14 THEN 75
WHEN AVG(oa.response_time_days) <= 30 THEN 50
WHEN AVG(oa.response_time_days) <= 60 THEN 25
ELSE 10
END) * 0.15 +
(COUNT(CASE WHEN oa.policy_change_resulted = TRUE THEN 1 END)::float /
NULLIF(COUNT(*), 0) * 100) * 0.25 +
(AVG(oa.public_attention_score) * 100) * 0.15
) >= 75 THEN 'HIGHLY_EFFECTIVE_OVERSIGHT'
WHEN (
(COUNT(oa.response_document_id)::float / NULLIF(COUNT(*), 0) * 100) * 0.20 +
(COUNT(CASE WHEN oa.substantive_response = TRUE THEN 1 END)::float /
NULLIF(COUNT(oa.response_document_id), 0) * 100) * 0.25 +
(CASE
WHEN AVG(oa.response_time_days) <= 7 THEN 100
WHEN AVG(oa.response_time_days) <= 14 THEN 75
WHEN AVG(oa.response_time_days) <= 30 THEN 50
WHEN AVG(oa.response_time_days) <= 60 THEN 25
ELSE 10
END) * 0.15 +
(COUNT(CASE WHEN oa.policy_change_resulted = TRUE THEN 1 END)::float /
NULLIF(COUNT(*), 0) * 100) * 0.25 +
(AVG(oa.public_attention_score) * 100) * 0.15
) >= 60 THEN 'MODERATELY_EFFECTIVE'
WHEN (
(COUNT(oa.response_document_id)::float / NULLIF(COUNT(*), 0) * 100) * 0.20 +
(COUNT(CASE WHEN oa.substantive_response = TRUE THEN 1 END)::float /
NULLIF(COUNT(oa.response_document_id), 0) * 100) * 0.25 +
(CASE
WHEN AVG(oa.response_time_days) <= 7 THEN 100
WHEN AVG(oa.response_time_days) <= 14 THEN 75
WHEN AVG(oa.response_time_days) <= 30 THEN 50
WHEN AVG(oa.response_time_days) <= 60 THEN 25
ELSE 10
END) * 0.15 +
(COUNT(CASE WHEN oa.policy_change_resulted = TRUE THEN 1 END)::float /
NULLIF(COUNT(*), 0) * 100) * 0.25 +
(AVG(oa.public_attention_score) * 100) * 0.15
) >= 40 THEN 'LOW_EFFECTIVENESS'
ELSE 'INEFFECTIVE_OVERSIGHT'
END as effectiveness_classification
FROM view_riksdagen_politician p
JOIN oversight_actions oa ON 1=1
WHERE p.person_id = :politicianId
GROUP BY p.person_id, p.first_name, p.last_name, p.party
""";
jdbcTemplate.queryForObject(sql, OversightProfile.class,
Map.of(
, politicianId,
, startDate,
, endDate
));
}
}
5. Cross-Party Collaboration Indicators
Coalition Building Intelligence
Cross-party collaboration signals potential coalition opportunities, ideological flexibility, and legislative compromise capacity.
Database Views:
view_riksdagen_cross_party_collaboration - Co-authorship networksview_riksdagen_coalition_patterns - Historical coalition structuresview_riksdagen_bipartisan_initiatives - Cross-party legislative efforts
WITH collaboration_events AS (
SELECT
dp1.person_id as person_a,
dp2.person_id as person_b,
p1.party as party_a,
p2.party as party_b,
d.document_id,
d.document_type,
d.issue_category,
d.created_date,
d.adopted_by_parliament
FROM document_person dp1
JOIN document_person dp2 ON dp1.document_id = dp2.document_id
JOIN person p1 ON dp1.person_id = p1.person_id
JOIN person p2 ON dp2.person_id = p2.person_id
JOIN document d ON dp1.document_id = d.document_id
WHERE dp1.person_id < dp2.person_id
AND p1.party != p2.party
AND d.created_date >= NOW() - INTERVAL '4 years'
),
party_pair_collaboration AS (
SELECT
ce.party_a,
ce.party_b,
COUNT(DISTINCT ce.document_id) as collaboration_count,
COUNT(DISTINCT ce.person_a) + COUNT(DISTINCT ce.person_b) as unique_collaborators,
COUNT(DISTINCT ce.issue_category) issue_diversity,
( ce.adopted_by_parliament )::
((), ) success_rate,
( ce.created_date NOW()
) recent_collaborations,
(
( ce.document_id)::
(( ce.person_a) ( ce.person_b))
( ce.issue_category)
(( ce.adopted_by_parliament )::
((), ))
) collaboration_intensity
collaboration_events ce
ce.party_a, ce.party_b
)
party_a,
party_b,
collaboration_count,
unique_collaborators,
issue_diversity,
ROUND(success_rate, ) success_rate,
recent_collaborations,
ROUND(collaboration_intensity, ) intensity_score,
collaboration_intensity
success_rate
recent_collaborations
collaboration_intensity
recent_collaborations
collaboration_intensity
coalition_potential,
issue_diversity success_rate
collaboration_count
recent_collaborations collaboration_count
relationship_type
party_pair_collaboration
intensity_score , collaboration_count ;
ISMS Compliance Mapping
ISO 27001:2022 Controls
| Control | Legislative Monitoring Application |
|---|
| A.5.9 - Inventory of information and other associated assets | Catalog legislative data sources and integrity verification |
| A.8.16 - Monitoring activities | Continuous monitoring of parliamentary data feeds |
| A.8.24 - Use of cryptography | Secure storage of political intelligence data |
NIST Cybersecurity Framework 2.0
| Function | Legislative Monitoring Integration |
|---|
| IDENTIFY (ID.AM) | Asset inventory of legislative data sources |
| PROTECT (PR.DS) | Data integrity protection for parliamentary records |
| DETECT (DE.DP) | Detection of data manipulation or tampering |
CIS Controls v8
| Control | Application |
|---|
| CIS Control 8 - Audit Log Management | Legislative activity audit logging |
| CIS Control 11 - Data Recovery | Parliamentary data backup and recovery |
Hack23 ISMS Policy References
This skill implements requirements from:
References
Legislative Studies Literature
- Cox, G. W., & McCubbins, M. D. (2005). Setting the Agenda: Responsible Party Government in the U.S. House of Representatives. Cambridge University Press.
- Strøm, K. (1990). "A Behavioral Theory of Competitive Political Parties." American Journal of Political Science, 34(2), 565-598.
- Döring, H. (1995). Parliaments and Majority Rule in Western Europe. Campus Verlag.
Database Intelligence Sources
