source_intel/DATA_MODEL.md

📊 Citizen Intelligence Agency Data Model

This document provides a comprehensive view of the data model that powers the Citizen Intelligence Agency platform. It outlines the key data structures, relationships, and organization that enable parliamentary monitoring, political analysis, and transparency.

📚 Related Architecture Documentation

Document	Focus	Description	Documentation Link
Architecture	🏛️ Architecture	C4 model showing current system structure	View Source
Future Architecture	🏛️ Architecture	C4 model showing future system structure	View Source
Mindmaps	🧠 Concept	Current system component relationships	View Source
Future Mindmaps	🧠 Concept	Future capability evolution	View Source
SWOT Analysis	💼 Business	Current strategic assessment	View Source
Future SWOT Analysis	💼 Business	Future strategic opportunities	View Source
Data Model	📊 Data	Current data structures and relationships	View Source
Future Data Model	📊 Data	Enhanced political data architecture	View Source
Flowcharts	🔄 Process	Current data processing workflows	View Source
Future Flowcharts	🔄 Process	Enhanced AI-driven workflows	View Source
State Diagrams	🔄 Behavior	Current system state transitions	View Source
Future State Diagrams	🔄 Behavior	Enhanced adaptive state transitions	View Source
CI/CD Workflows	🔧 DevOps	Current automation processes	View Source
Future Workflows	🔧 DevOps	Enhanced CI/CD with ML	View Source
End-of-Life Strategy	📅 Lifecycle	Maintenance and EOL planning	View Source
Financial Security Plan	💰 Security	Cost and security implementation	View Source
CIA Features	🚀 Features	Platform features overview	View on hack23.com
Threat Model	🛡️ Security	Data integrity & abuse threat mapping	View Source

🧩 Core Data Model Overview

The Citizen Intelligence Agency platform is built on a robust data model that integrates political data from multiple sources into a unified analytical framework. The data model reflects three primary domains:

erDiagram
    ParliamentaryData {
        string id
        string documentType
        string status
        date createdDate
        string title
    }
    
    PoliticalEntities {
        string id
        string firstName
        string lastName
        string party
        int bornYear
        string gender
    }
    
    AnalyticalOutput {
        string metricType
        float scoreValue
        date analysisDate
        string description
    }
    
    ParliamentaryData ||--o{ PoliticalEntities : "references"
    PoliticalEntities ||--o{ AnalyticalOutput : "generates"
    ParliamentaryData ||--o{ AnalyticalOutput : "informs"

📊 Political Entities Data Model

👤 Person Focus: Illustrates the structure of political persons (politicians) and their relationships to parties, committees, and other entities.

🏛️ Organization Focus: Shows how political organizations are modeled and their relationships within the system.

classDiagram
    class Person {
        +string id
        +string firstName
        +string lastName
        +string party
        +int bornYear
        +string gender
        +string status
        +string electionRegion
        +PersonAssignment[] assignments
        +PersonDetail[] details
    }
    
    class PersonAssignment {
        +string assignmentType
        +string detail
        +date fromDate
        +date toDate
        +string orgCode
        +string roleCode
        +string status
    }
    
    class PersonDetail {
        +string code
        +string detail
        +string detailType
    }
    
    class Party {
        +string id
        +string name
        +string shortCode
        +date registeredDate
        +string website
    }
    
    class Committee {
        +string orgCode
        +string detail
        +boolean active
        +date firstAssignmentDate
        +date lastAssignmentDate
    }
    
    class Ministry {
        +string nameId
        +long totalAssignments
        +date firstAssignmentDate
        +date lastAssignmentDate
    }

    Person "1" *-- "many" PersonAssignment
    Person "1" *-- "many" PersonDetail
    Person "many" -- "1" Party
    PersonAssignment "many" -- "many" Committee
    PersonAssignment "many" -- "many" Ministry

📑 Parliamentary Document Data Model

📄 Document Focus: Illustrates the structure of parliamentary documents and their relationships.

🔄 Process Focus: Shows how documents flow through the parliamentary process.

classDiagram
    class DocumentData {
        +string id
        +string documentType
        +string title
        +string subTitle
        +string status
        +date madePublicDate
        +string org
        +string rm
        +string label
    }
    
    class DocumentContent {
        +string id
        +string content
    }
    
    class DocumentStatus {
        +string documentCategory
        +string documentId
    }
    
    class DocumentDetail {
        +string code
        +string detailName
        +string text
    }
    
    class DocumentPersonReference {
        +int orderNumber
        +string personReferenceId
        +string referenceName
        +string roleDescription
        +string partyShortCode
    }
    
    class CommitteeDocument {
        +string id
        +string label
        +string title
        +string org
        +string status
        +date createdDate
        +date publicDate
    }
    
    class CommitteeProposal {
        +string header
        +string proposal
        +string decisionType
        +string committeeReport
        +string winner
    }

    DocumentData "1" -- "0..1" DocumentContent
    DocumentData "1" -- "1" DocumentStatus
    DocumentStatus "1" *-- "many" DocumentDetail
    DocumentStatus "1" *-- "many" DocumentPersonReference
    CommitteeDocument "1" -- "many" CommitteeProposal

🗳️ Voting Data Model

🗳️ Vote Focus: Illustrates the structure of voting data from parliamentary sessions.

📊 Analysis Focus: Shows how vote data is organized to enable analytical insights.

classDiagram
    class VoteData {
        +string ballotId
        +string concern
        +string intressentId
        +string issue
        +string ballotType
        +string vote
        +date voteDate
        +string party
        +string firstName
        +string lastName
        +int bornYear
        +string gender
    }
    
    class BallotSummary {
        +string ballotId
        +string concern
        +string issue
        +boolean approved
        +int yesVotes
        +int noVotes
        +int absentVotes
        +int abstainVotes
        +float avgBornYear
    }
    
    class PartyBallotSummary {
        +string ballotId
        +string concern
        +string issue
        +string party
        +boolean approved
        +int partyYesVotes
        +int partyNoVotes
        +int partyAbsentVotes
        +int partyAbstainVotes
        +float partyAvgBornYear
    }
    
    class PoliticianBallotSummary {
        +string ballotId
        +string concern
        +string intressentId
        +string issue
        +string vote
        +boolean won
        +boolean rebel
        +string party
    }

    VoteData "many" -- "1" BallotSummary
    BallotSummary "1" -- "many" PartyBallotSummary
    BallotSummary "1" -- "many" PoliticianBallotSummary

🌍 External Data Sources Model

🌐 Integration Focus: Illustrates the structure of data from external sources like World Bank.

🔄 Standardization Focus: Shows how external data is standardized for platform integration.

classDiagram
    class WorldBankData {
        +string countryId
        +string countryValue
        +string indicatorId
        +string indicatorValue
        +string dataValue
        +int yearDate
    }
    
    class Indicator {
        +string id
        +string indicatorName
        +string sourceId
        +string sourceValue
        +string sourceNote
        +string sourceOrganization
    }
    
    class Country {
        +string id
        +string countryName
        +string regionId
        +string regionValue
        +string capitalCity
        +string latitude
        +string longitude
    }
    
    class Topic {
        +string id
        +string value
    }

    WorldBankData "many" -- "1" Country
    WorldBankData "many" -- "1" Indicator
    Indicator "many" -- "many" Topic

🗄️ Electoral Data Model

🗳️ Election Focus: Illustrates the structure of electoral data from the Swedish Election Authority.

🏛️ Geographic Focus: Shows how electoral regions are organized within the system.

classDiagram
    class ElectionRegion {
        +string regionName
        +string countyId
        +string municipalId
    }
    
    class PoliticalParty {
        +string partyId
        +string partyName
        +string shortCode
        +date registeredDate
        +string website
    }
    
    class CountyData {
        +int code
        +string countyName
    }
    
    class MunicipalityData {
        +int code
        +string municipalName
    }
    
    class CountyElectoralRegion {
        +int code
        +string countyName
        +int seats
    }
    
    class CountyElectoralArea {
        +int code
        +string electoralAreaName
        +int numberOfSeats
        +int numberOfVoters
    }
    
    class ParliamentElectoralRegion {
        +string electionRegionName
        +int numberOfSeats
        +int numberOfVoters
    }
    
    class MunicipalityElection {
        +int code
        +string electionRegionName
        +int numberOfSeats
        +int numberOfVoters
    }

    ElectionRegion "1" -- "many" PoliticalParty
    CountyData "1" -- "many" MunicipalityData
    CountyElectoralRegion "1" -- "many" CountyElectoralArea
    CountyData "1" -- "1" CountyElectoralRegion
    MunicipalityData "1" -- "many" MunicipalityElection
    ElectionRegion "1" -- "0..1" CountyData
    ElectionRegion "1" -- "0..1" MunicipalityData

👤 User and System Data Model

👤 User Focus: Illustrates the structure of user accounts and session data.

🔧 System Focus: Shows how system configuration and operation data is organized.

classDiagram
    class UserAccount {
        +string userId
        +string username
        +string userPassword
        +string email
        +string userRole
        +string userType
        +date createdDate
        +int numberOfVisits
    }
    
    class ApplicationSession {
        +date createdDate
        +date destroyedDate
        +string ipInformation
        +string userAgentInformation
        +string sessionId
        +string userId
    }
    
    class ApplicationActionEvent {
        +string actionName
        +string applicationOperation
        +date createdDate
        +string page
        +string pageMode
        +string elementId
        +string sessionId
        +string userId
    }
    
    class ApplicationConfiguration {
        +string component
        +string componentTitle
        +string configurationGroup
        +string propertyId
        +string propertyValue
        +date createdDate
        +date updatedDate
    }
    
    class Portal {
        +string portalName
        +string description
        +string portalType
    }
    
    class DomainPortal {
        +string domainName
    }

    ApplicationSession "1" -- "many" ApplicationActionEvent
    UserAccount "1" -- "many" ApplicationSession
    Portal <|-- DomainPortal

🔍 Analytical Views Data Model

📊 Analytics Focus: Illustrates the structure of analytical views for political metrics.

📊 Performance Focus: Shows how performance metrics are calculated and stored.

classDiagram
    class PoliticianView {
        +string personId
        +string firstName
        +string lastName
        +string party
        +boolean active
        +int totalDocuments
        +int documentsLastYear
        +date firstAssignmentDate
        +date lastAssignmentDate
    }
    
    class PartyView {
        +string party
        +string partyName
        +boolean active
        +int totalDocuments
        +int documentsLastYear
        +int totalAssignments
    }
    
    class CommitteeView {
        +string orgCode
        +string detail
        +boolean active
        +int currentMemberSize
        +int totalAssignments
        +int totalDaysServed
    }
    
    class MinistryView {
        +string nameId
        +boolean active
        +int totalAssignments
        +int totalDaysServed
    }
    
    class PoliticianBallotSummaryView {
        +string personId
        +string firstName
        +string lastName
        +string party
        +float absenceRate
        +float loyaltyRate
        +float rebelRate
        +float successRate
        +int totalVotes
    }
    
    class PoliticianExperienceSummary {
        +string personId
        +string firstName
        +string lastName
        +string experienceLevel
        +string careerPhase
        +string specializationLevel
        +long totalDays
        +long committeeDays
        +long partyDays
    }

    PoliticianView "1" -- "0..1" PoliticianBallotSummaryView
    PoliticianView "1" -- "0..1" PoliticianExperienceSummary

🔄 Data Flow Diagram

This diagram illustrates how data flows through the system from various sources to the analytical outputs and user interfaces.

flowchart TD
    A[External Data Sources] --> B{Data Integration}
    
    B -->|Parliamentary Data| C1[Parliament Data Processing]
    B -->|Electoral Data| C2[Election Data Processing]
    B -->|Economic Data| C3[World Bank Data Processing]
    
    C1 --> D1[Document Entities]
    C1 --> D2[Politician Entities]
    C1 --> D3[Vote Data]
    C1 --> D4[Committee Data]
    
    C2 --> D5[Party Entities]
    C2 --> D6[Electoral Region Data]
    
    C3 --> D7[Indicator Data]
    C3 --> D8[Country Data]
    
    D1 & D2 & D3 & D4 & D5 & D6 & D7 & D8 --> E[Data Storage]
    
    E --> F1[Analytical Processing]
    F1 --> G1[Performance Metrics]
    F1 --> G2[Voting Analysis]
    F1 --> G3[Document Analysis]
    F1 --> G4[Political Network Analysis]
    
    G1 & G2 & G3 & G4 --> H[Materialized Views]
    
    H --> I[Web Application]
    I --> J[User Interface]
    
    classDef sources fill:#bbdefb,stroke:#333,stroke-width:1px,color:black
    classDef integration fill:#a0c8e0,stroke:#333,stroke-width:1px,color:black
    classDef processing fill:#c8e6c9,stroke:#333,stroke-width:1px,color:black
    classDef entities fill:#d1c4e9,stroke:#333,stroke-width:1px,color:black
    classDef storage fill:#f8cecc,stroke:#333,stroke-width:1px,color:black
    classDef analytics fill:#fff2cc,stroke:#333,stroke-width:1px,color:black
    classDef views fill:#ffe0b2,stroke:#333,stroke-width:1px,color:black
    classDef application fill:#dcedc8,stroke:#333,stroke-width:1px,color:black
    
    class A sources
    class B integration
    class C1,C2,C3 processing
    class D1,D2,D3,D4,D5,D6,D7,D8 entities
    class E storage
    class F1 analytics
    class G1,G2,G3,G4 analytics
    class H views
    class I,J application

🧩 Key Database Tables

Category	Table Name	Description	Primary Keys	Key Relationships
👤 Person Data	PERSON_DATA	Political person entities	ID	PERSON_ASSIGNMENT_DATA, PERSON_DETAIL_DATA
👤 Person Data	ASSIGNMENT_DATA	Political assignments	HJID	PERSON_DATA (many-to-one)
📑 Document Data	DOCUMENT_DATA	Parliamentary documents	ID	DOCUMENT_STATUS_CONTAINER
📑 Document Data	DOCUMENT_CONTENT_DATA	Document content	HJID, ID	DOCUMENT_DATA (one-to-one)
🏢 Committee Data	COMMITTEE_DOCUMENT_DATA	Committee documents	ID	COMMITTEE_PROPOSAL_DATA
🗳️ Vote Data	VOTE_DATA	Parliamentary votes	Composite (BALLOT_ID, CONCERN, INTRESSENT_ID, ISSUE)	-
🌍 World Bank	WORLD_BANK_DATA	Economic indicators	HJID	DATA_ELEMENT
🌍 World Bank	COUNTRY_ELEMENT	Country data	HJID	COUNTRIES_ELEMENT
🗄️ Electoral Data	SWEDEN_ELECTION_REGION	Electoral regions	HJID	SWEDEN_POLITICAL_PARTY
🗄️ Electoral Data	SWEDEN_POLITICAL_PARTY	Political parties	HJID	SWEDEN_ELECTION_REGION
👤 User Data	USER_ACCOUNT	User accounts	HJID	APPLICATION_SESSION
👤 User Data	APPLICATION_SESSION	User sessions	HJID	APPLICATION_ACTION_EVENT
🔧 System Data	APPLICATION_CONFIGURATION	System configuration	HJID	-

📊 Materialized Views for Analytics

The CIA platform uses materialized views to provide efficient access to analytical data. These views transform raw data into meaningful insights about political entities and activities.

View Name	Purpose	Key Metrics	Base Tables
VIEW_RIKSDAGEN_POLITICIAN	Politician profile metrics	Activity levels, document counts, assignment history	PERSON_DATA, ASSIGNMENT_DATA
VIEW_RIKSDAGEN_PARTY	Party performance metrics	Member counts, document counts, activity levels	SWEDEN_POLITICAL_PARTY, related person tables
VIEW_RIKSDAGEN_COMMITTEE	Committee activity metrics	Member counts, document counts, assignment data	COMMITTEE_DOCUMENT_DATA, related assignment tables
VIEW_RIKSDAGEN_POLITICIAN_BALLOT_SUMMARY	Voting performance	Loyalty rate, rebel rate, success rate, absence rate	VOTE_DATA
View_Riksdagen_Vote_Data_Ballot_Summary	Vote outcome analysis	Yes/no percentages, absence rates, approval stats	VOTE_DATA
view_riksdagen_politician_experience_summary	Experience analysis	Career phase, knowledge areas, leadership profile	ASSIGNMENT_DATA, PERSON_DATA

🔄 Database Relationships

This diagram illustrates key relationships between major tables in the database:

erDiagram
    PERSON_DATA ||--o{ ASSIGNMENT_DATA : has
    PERSON_DATA ||--o{ PERSON_DETAIL_DATA : has
    DOCUMENT_DATA ||--|| DOCUMENT_STATUS_CONTAINER : has
    DOCUMENT_STATUS_CONTAINER ||--o{ DOCUMENT_DETAIL_DATA : contains
    DOCUMENT_STATUS_CONTAINER ||--o{ DOCUMENT_PERSON_REFERENCE_DA_0 : references
    COMMITTEE_DOCUMENT_DATA ||--o{ COMMITTEE_PROPOSAL_DATA : has
    VOTE_DATA }o--|| PERSON_DATA : cast_by
    USER_ACCOUNT ||--o{ APPLICATION_SESSION : has
    APPLICATION_SESSION ||--o{ APPLICATION_ACTION_EVENT : generates
    SWEDEN_ELECTION_REGION ||--o{ SWEDEN_POLITICAL_PARTY : contains
    SWEDEN_COUNTY_DATA ||--o{ SWEDEN_MUNICIPALITY_DATA : contains
    WORLD_BANK_DATA }o--|| COUNTRY_ELEMENT : relates_to
    WORLD_BANK_DATA }o--|| INDICATOR_ELEMENT : measures

📈 Data Flow for Analytics

🔄 Process Focus: Illustrates how raw data is transformed into analytical metrics.

📊 Metrics Focus: Shows the key transformations that generate political insights.

sequenceDiagram
    participant DataSource as External Data Sources
    participant ImportService as Import Services
    participant Database as PostgreSQL Database
    participant Analytics as Analytical Processes
    participant Views as Materialized Views
    participant UI as User Interface
    
    DataSource->>ImportService: Raw Political Data
    ImportService->>ImportService: Transform & Validate
    ImportService->>Database: Store Normalized Data
    
    Database->>Analytics: Raw Entity Data
    Analytics->>Analytics: Calculate Metrics
    Analytics->>Database: Store Derived Metrics
    
    Database->>Views: Create/Refresh Views
    Views->>UI: Provide Analytical Data
    UI->>UI: Visualize & Present

💾 PostgreSQL Implementation

The CIA platform uses a PostgreSQL database with:

• 60+ base tables for entity storage
• 30+ materialized views for analytical queries
• Complex relationships modeled through foreign keys
• Composite primary keys for many-to-many relationships
• Audit and tracking capabilities through timestamp fields
• Large text fields for document content (up to 10MB)
• Enumerated types for categorization

Schema Highlights

-- Political Person Entity
CREATE TABLE PERSON_DATA (
    ID varchar(255) NOT NULL,
    BORN_YEAR int4,
    ELECTION_REGION varchar(255),
    FIRST_NAME varchar(255),
    LAST_NAME varchar(255),
    GENDER varchar(255),
    PARTY varchar(255),
    STATUS varchar(255),
    PERSON_ASSIGNMENT_DATA_PERSO_0 int8,
    PERSON_DETAIL_DATA_PERSON_DA_0 int8,
    PRIMARY KEY (ID)
);

-- Parliamentary Vote Data
CREATE TABLE VOTE_DATA (
    EMBEDDED_ID_BALLOT_ID varchar(255) NOT NULL,
    EMBEDDED_ID_CONCERN varchar(255) NOT NULL,
    EMBEDDED_ID_INTRESSENT_ID varchar(255) NOT NULL,
    EMBEDDED_ID_ISSUE varchar(255) NOT NULL,
    BALLOT_TYPE varchar(255),
    VOTE varchar(255),
    VOTE_DATE date,
    PARTY varchar(255),
    FIRST_NAME varchar(255),
    LAST_NAME varchar(255),
    BORN_YEAR int4,
    GENDER varchar(255),
    PRIMARY KEY (EMBEDDED_ID_BALLOT_ID, EMBEDDED_ID_CONCERN, EMBEDDED_ID_INTRESSENT_ID, EMBEDDED_ID_ISSUE)
);

-- Political Performance View
CREATE TABLE VIEW_RIKSDAGEN_POLITICIAN_BALLOT_SUMMARY (
    PERSON_ID varchar(255) NOT NULL,
    ABSENCE_RATE float8,
    LOYALTY_RATE float8,
    REBEL_RATE float8,
    SUCCESS_RATE float8,
    TOTAL_VOTES int8,
    FIRST_NAME varchar(255),
    LAST_NAME varchar(255),
    PARTY varchar(255),
    PRIMARY KEY (PERSON_ID)
);

🔄 Data Model Evolution

🚀 Future Focus: Shows how the current data model will evolve in line with the future architecture vision.

🔍 Enhancement Focus: Illustrates the planned data model improvements.

timeline
    title Data Model Evolution Plan
    section Current Model
        Now : Core political entities
            : Basic analytical views
            : Simple aggregations
    section Near Future
        Q3 2024 : ML-ready data structures
               : Enhanced relationship models
               : Predictive analytics tables
    section Mid-Term
        Q1 2025 : Real-time data streams
               : Natural language processing
               : Network analysis models
    section Long-Term
        Q3 2025 : AI decision support structures
            : Semantic knowledge graphs
            : Autonomous pattern detection

🧩 Key Data Model Implementation Features

Feature	Implementation	Purpose	Examples
🔑 Entity Tracking	Unique identifiers	Track entities across data sources	PERSON_DATA.ID, DOCUMENT_DATA.ID
🔄 Historical Tracking	Date fields	Track changes over time	FROM_DATE, TO_DATE, CREATED_DATE
📊 Analytical Aggregation	Materialized views	Pre-compute metrics for performance	VIEW_RIKSDAGEN_POLITICIAN, VIEW_RIKSDAGEN_PARTY
🔍 Multi-dimensional Analysis	Time-based aggregation views	Enable time-series analysis	*_DAILY_SUMMARY, *_MONTHLY_SUMMARY, *_ANNUAL_SUMMARY views
🌐 External Integration	Standardized entity mapping	Connect with external data sources	World Bank tables, riksdagen tables
👤 User Behavior Tracking	Session and event tables	Track user interactions	APPLICATION_SESSION, APPLICATION_ACTION_EVENT
🔒 Security Implementation	User account structures	Manage authentication and authorization	USER_ACCOUNT, EncryptedValue

This data model documentation provides a comprehensive view of the CIA platform's current data structures and relationships. It illustrates how political data is organized, analyzed, and presented to enable transparency and citizen engagement with political processes.

For more detailed information on specific entities, please refer to the Entity Model Documentation.

Related Documentation

• Architecture Documentation - System architecture overview
• Entity Model Documentation - Detailed entity documentation
• Future Vision - Roadmap for AI-enhanced capabilities
• MINDMAP Documentation - Conceptual overview of system components
• AWS Services Stack - Cloud deployment architecture