# openbmp/obmp-psql architecture

> Architecture diagram of the GitHub repository openbmp/obmp-psql, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-06-06). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/openbmp/obmp-psql
- Repository: https://github.com/openbmp/obmp-psql

## Overview

##### Project Type and Purpose
- This is an open-source backend consumer tool written in Java that reads parsed BMP/BGP messages from a Kafka message bus and writes them into a PostgreSQL database for real-time and historical reporting.
- Ancillary cron jobs and scripts supplement the main consumer by pulling additional data (WHOIS, PeeringDB, RPKI) and populating or augmenting the Postgres tables.

##### File‐Structure Analysis
- src/main/java: Java consumer app (org.openbmp.*)
- ConsumerApp, ConsumerRunnable, ConsumerRebalanceListener, PSQLHandler, WriterRunnable, plus SQL‐statement classes under psqlquery.
- Configuration under src/main/resources (obmp-psql.yml, log4j).
- cron_scripts: Python scripts invoked on schedule for WHOIS, PeeringDB, RPKI data.
- scripts: Helper scripts for bulk CSV import and geo data.
- database: Migration SQL files defining tables (base, aggregations), functions, views, triggers, and schema-version.
- docs: Build and run instructions.
- pom.xml: Maven build.
- test/src: Unit tests for core consumer.

##### Key README Insights
- The consumer is multi‐threaded; each thread owns a separate Postgres connection.
- Threads auto‐scale up/down based on queue depth; rebalancing occurs periodically.
- Fault tolerance is via Kafka consumer group rebalancing across hosts.
- Supports specific address families (IPv4, IPv6, link-state).

a. External Systems
– Kafka Cluster (topic partitions for parsed BMP messages)
– RPKI Validator Service (cron job)
– PeeringDB API (cron job)
– WHOIS sources (cron job)
– External Geo-IP CSV
b. Main Application
– ConsumerApp (bootstraps threads)
– Partitioned ConsumerRunnable instances (one per thread)
– Internal queue manager / load‐balancer
– WriterRunnable pool (per message type)
– PSQLHandler (executes SQL via JDBC)
– psqlquery classes (SQL generation)
c. Data Store
– PostgreSQL Instance
- Tables (peers, prefixes, link-state data)
- Views, Aggregations, Functions, Triggers
- schema-version tracking
d. Auxiliary Scripts
– scripts/db-ip-import.sh → bulk load IP blocks
– scripts/geo-csv-to-psql.py → load GeoIP data
e. Build & Deployment
– Maven (pom.xml)
– Docker/Kubernetes (optional, not in tree but can be added)

##### Relationships & Data Flows
- Scripts invoked manually/CI for initial/bulk loads.

##### Architectural Patterns & Principles
- Producer-Consumer with thread pool and queue‐based load balancing.
- Auto-scaling thread pools based on load thresholds.
- Clean separation of concerns: Kafka consumption, message processing, SQL execution.
- SQL encapsulation in dedicated query classes (psqlquery package).
- Schema‐versioned migrations for database.

##### Essential Elements to Capture
- Component boundaries and their configuration sources.
- Inter‐component queues (in-memory Java queues) and scaling logic.
- SQL generation and data persistence pipeline.
- External data enrichment pipelines running on cron.
- Build/test workflow (Maven → unit tests → packaging).

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% External Systems
    subgraph "External Systems"
        direction TB
        KafkaCluster["Kafka Cluster"]:::streaming
        PeeringDB["PeeringDB API"]:::external
        WHOIS["WHOIS Sources"]:::external
        RPKI["RPKI Validator Service"]:::external
        ExternalGeoCSV["External Geo-IP CSV"]:::external
    end

    %% Java Consumer Application
    subgraph "Java Consumer Application" 
        direction TB
        ConsumerApp["ConsumerApp"]:::app
        ConsumerRebalanceListener["ConsumerRebalanceListener"]:::app
        ConsumerRunnable["Partitioned ConsumerRunnable"]:::app
        InternalQueueManager["Internal Queue Manager"]:::app
        WriterRunnable["WriterRunnable Pool"]:::app
        PSQLHandler["PSQLHandler (JDBC)"]:::app
        subgraph "SQL Query Classes" 
            direction TB
            BaseAttributeQuery["BaseAttributeQuery"]:::app
            BmpStatQuery["BmpStatQuery"]:::app
            CollectorQuery["CollectorQuery"]:::app
            L3VpnPrefixQuery["L3VpnPrefixQuery"]:::app
            LsLinkQuery["LsLinkQuery"]:::app
            LsNodeQuery["LsNodeQuery"]:::app
            LsPrefixQuery["LsPrefixQuery"]:::app
            PeerQuery["PeerQuery"]:::app
            PsqlFunctions["PsqlFunctions"]:::app
            Query["Query"]:::app
            RouterQuery["RouterQuery"]:::app
            UnicastPrefixQuery["UnicastPrefixQuery"]:::app
        end
        subgraph "Domain & Processor Objects"
            direction TB
            ConsumerMessageObject["ConsumerMessageObject"]:::app
            WriterObject["WriterObject"]:::app
            RouterObject["RouterObject"]:::app
            ParseLongEmptyAsZero["ParseLongEmptyAsZero"]:::app
            ParseNullAsEmpty["ParseNullAsEmpty"]:::app
            ParseTimestamp["ParseTimestamp"]:::app
        end
        ConfigYML["obmp-psql.yml"]:::config
        LogConfig["log4j2.yml"]:::config
    end

    %% Data Store
    subgraph "PostgreSQL Database" 
        direction TB
        Postgres["PostgreSQL Instance"]:::db
        Tables["Tables / Views / Functions"]:::db
        Migrations["DB Migrations & Schema"]:::db
    end

    %% Cron Jobs
    subgraph "Cron Enrichment Jobs" 
        direction TB
        CronWHOIS["WHOIS Enrichment Cron"]:::scripts
        CronPeeringDB["PeeringDB Enrichment Cron"]:::scripts
        CronRPKI["RPKI Validation Cron"]:::scripts
    end

    %% Scripts
    subgraph "Helper Scripts"
        direction TB
        BulkImport["db-ip-import.sh"]:::scripts
        GeoImport["geo-csv-to-psql.py"]:::scripts
    end

    %% Build & Docs
    subgraph "Build & Documentation"
        direction TB
        Maven["pom.xml"]:::config
        DocsBuild["docs/BUILD.md"]:::config
        DocsRun["docs/RUN.md"]:::config
        UnitTests["ConsumerRunnableAppTest"]:::config
    end

    %% Relationships
    KafkaCluster -->|consumes offsets| ConsumerApp
    ConsumerApp -->|commits offsets| KafkaCluster
    KafkaCluster --> ConsumerRunnable
    ConsumerRunnable -->|enqueue| InternalQueueManager
    InternalQueueManager -->|dispatch| WriterRunnable
    WriterRunnable -->|executes| PSQLHandler
    PSQLHandler -->|queries| Tables
    Tables --> Postgres
    Migrations --> Postgres

    ConsumerApp -.->|configures threads| ConfigYML
    ConsumerApp -.->|logging config| LogConfig

    PeeringDB --> CronPeeringDB --> Postgres
    WHOIS --> CronWHOIS --> Postgres
    RPKI --> CronRPKI --> Postgres
    ExternalGeoCSV -.-> GeoImport --> Postgres
    BulkImport --> Postgres

    Maven -->|builds/tests| ConsumerApp
    UnitTests -->|tests| ConsumerRunnable
    DocsBuild -->|instructions| ConsumerApp
    DocsRun -->|instructions| ConsumerApp

    %% Click Events
    click ConsumerApp "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/ConsumerApp.java"
    click ConsumerRunnable "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/ConsumerRunnable.java"
    click ConsumerRebalanceListener "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/ConsumerRebalanceListener.java"
    click InternalQueueManager "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/WriterQueueMsg.java"
    click WriterRunnable "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/WriterRunnable.java"
    click PSQLHandler "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/PSQLHandler.java"
    click ConsumerMessageObject "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/ConsumerMessageObject.java"
    click WriterObject "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/WriterObject.java"
    click RouterObject "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/RouterObject.java"
    click BaseAttributeQuery "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/psqlquery/BaseAttributeQuery.java"
    click BmpStatQuery "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/psqlquery/BmpStatQuery.java"
    click CollectorQuery "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/psqlquery/CollectorQuery.java"
    click L3VpnPrefixQuery "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/psqlquery/L3VpnPrefixQuery.java"
    click LsLinkQuery "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/psqlquery/LsLinkQuery.java"
    click LsNodeQuery "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/psqlquery/LsNodeQuery.java"
    click LsPrefixQuery "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/psqlquery/LsPrefixQuery.java"
    click PeerQuery "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/psqlquery/PeerQuery.java"
    click PsqlFunctions "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/psqlquery/PsqlFunctions.java"
    click Query "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/psqlquery/Query.java"
    click RouterQuery "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/psqlquery/RouterQuery.java"
    click UnicastPrefixQuery "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/psqlquery/UnicastPrefixQuery.java"
    click ParseLongEmptyAsZero "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/processor/ParseLongEmptyAsZero.java"
    click ParseNullAsEmpty "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/processor/ParseNullAsEmpty.java"
    click ParseTimestamp "https://github.com/openbmp/obmp-psql/blob/main/src/main/java/org/openbmp/processor/ParseTimestamp.java"
    click ConfigYML "https://github.com/openbmp/obmp-psql/blob/main/src/main/resources/obmp-psql.yml"
    click LogConfig "https://github.com/openbmp/obmp-psql/blob/main/src/main/resources/log4j2.yml"
    click CronWHOIS "https://github.com/openbmp/obmp-psql/tree/main/cron_scripts/gen-whois/"
    click CronPeeringDB "https://github.com/openbmp/obmp-psql/tree/main/cron_scripts/peeringdb/"
    click CronRPKI "https://github.com/openbmp/obmp-psql/tree/main/cron_scripts/rpki/"
    click BulkImport "https://github.com/openbmp/obmp-psql/blob/main/scripts/db-ip-import.sh"
    click GeoImport "https://github.com/openbmp/obmp-psql/blob/main/scripts/geo-csv-to-psql.py"
    click Migrations "https://github.com/openbmp/obmp-psql/tree/main/database/"
    click Maven "https://github.com/openbmp/obmp-psql/blob/main/pom.xml"
    click DocsBuild "https://github.com/openbmp/obmp-psql/blob/main/docs/BUILD.md"
    click DocsRun "https://github.com/openbmp/obmp-psql/blob/main/docs/RUN.md"
    click UnitTests "https://github.com/openbmp/obmp-psql/blob/main/src/test/java/org/openbmp/ConsumerRunnableAppTest.java"

    %% Styles
    classDef streaming fill:#D0E6FF,stroke:#1E70BF,color:#1E70BF
    classDef app fill:#E6F9E6,stroke:#27AE60,color:#27AE60
    classDef db fill:#FFE6CC,stroke:#E67E22,color:#E67E22
    classDef scripts fill:#F0F0F0,stroke:#8C8C8C,color:#8C8C8C
    classDef config fill:#FFF2CC,stroke:#F1C232,color:#B45F06
    classDef external fill:#F5F5F5,stroke:#AAAAAA,color:#555555
```
