Database Configuration

1. Overview

The FT Device Network Service uses ClickHouse, a column-oriented database management system, to efficiently store and process time-series network data. ClickHouse was chosen for its exceptional performance with analytical workloads, particularly when handling the large volumes of device telemetry data and network metrics that this service manages.

The database stores several types of information:

  1. External Interface Data - Information about the device’s connection to the internet (DSL, Fiber, Mobile, or Ethernet)

  2. WiFi Band Data - Details about Wi-Fi bands, their configuration, and performance metrics

  3. Network Client Data - Information about connected devices, their connection quality, and usage statistics

  4. Network Issues - Detected problems and their severity for troubleshooting

  5. Network Scores - Performance metrics that quantify the overall network health

  6. Diagnostic Data - Results from ping tests, speed tests, and other diagnostic procedures

All data is stored with timestamps to allow for historical analysis and trending. The application doesn’t create the database schema; it relies on existing ClickHouse instances configured and maintained externally.

2. Configuration

Spring Boot configuration properties are used to define the ClickHouse data source. These properties are set in the application.yml file and can be overridden with environment variables for different deployment environments.

spring:
  datasource:
    clickhouse:
      jdbcUrl: jdbc:clickhouse://${CLICKHOUSE_HOST:localhost}:${CLICKHOUSE_HTTP_PORT:8123}/${CLICKHOUSE_DATABASE:ftacs_qoe_ui_data}
      username: ${CLICKHOUSE_USERNAME:ftacs}
      password: ${CLICKHOUSE_PASSWORD:ftacs}
      driver-class-name: com.clickhouse.jdbc.ClickHouseDriver

The configuration uses environment variables with sensible defaults:

  • CLICKHOUSE_HOST - The hostname of the ClickHouse server (default: localhost)

  • CLICKHOUSE_HTTP_PORT - The port number for the ClickHouse server (defaults: 8123)

  • CLICKHOUSE_DATABASE - The name of the database to connect to (default: ftacs_qoe_ui_data)

  • CLICKHOUSE_USERNAME - The username for authentication (default: ftacs)

  • CLICKHOUSE_PASSWORD - The password for authentication (default: ftacs)

The service uses the com.clickhouse.jdbc.ClickHouseDriver JDBC driver for connecting to ClickHouse, which offers high performance for both read and write operations.

2.1. ClickHouse Tables

The application requires the following ClickHouse tables which store device network data and analytics:

CREATE TABLE network_client(
	serial String,
	model String,
	domain_name String,
	node_name String,
	created DateTime,
	hostname String,
	ip String,
	mac String,
	connected_by Enum8('ETHERNET', 'WIFI', 'UNDEFINED'),
	type Enum8('SATELLITE', 'NETWORK_DEVICE', 'OTHER'),
	health Nullable(Float32),
	wifi_protocol Nullable(Enum8('WIFI_2_4', 'WIFI_5', 'WIFI_6')),
	wifi_type Nullable(String),
	wifi_rssi Nullable(Int16),
	wifi_operating_standard Nullable(String),
	wifi_band Nullable(String),
	wifi_channel Nullable(String),
	wifi_ssid Nullable(String),
	wifi_snr Nullable(Int16),
	wifi_max_supported_data_dl_rate Nullable(Float32),
	wifi_max_supported_data_ul_rate Nullable(Float32),
	wifi_steering_history Nullable(Int16),
	wifi_signal_strength Nullable(Int16),
	wifi_last_data_dl_rate Nullable(Float32),
	wifi_last_data_ul_rate Nullable(Float32),
	wifi_retransmissions Nullable(Int32),
	eth_interface Nullable(String),
	eth_collisions Nullable(Int16),
	unit_model Nullable(String),
	unit_mesh_type Nullable(Enum8('CONTROLLER', 'SATELLITE')),
	unit_ip Nullable(String),
	unit_mac Nullable(String),
	stat_bytes_received Nullable(Int32),
	stat_bytes_sent Nullable(Int32),
	stat_errors_received Nullable(Int16),
	stat_errors_sent Nullable(Int16),
	stat_packets_received Nullable(Int16),
	stat_packets_sent Nullable(Int16),
	stat_error_dl_rate Nullable(Float32),
	stat_error_ul_rate Nullable(Float32),
	stat_connection_time Nullable(Int32)
)ENGINE = MergeTree
PARTITION BY toDate(created)
ORDER BY (serial, node_name, created)
SETTINGS index_granularity = 8192;

CREATE TABLE network_wifi_band(
	serial String,
	created DateTime,
	model String,
	domain_name String,
	protocol Enum8('WIFI_2_4', 'WIFI_5', 'WIFI_6'),
	node_name String,
	frequency Nullable(String),
	operating_standards Nullable(String),
	clients_count UInt16,
	channel UInt16,
	bandwidth Int16,
	ssids Array(String),
	security Array(Tuple(name String, encryption_mode String)),
	satellites Array(Tuple(model Nullable(String), ip Nullable(String), mac Nullable(String), rssi Nullable(Int16)))
) ENGINE = MergeTree
PARTITION BY toDate(created)
ORDER BY (serial, protocol, created)
SETTINGS index_granularity = 8192;

CREATE TABLE network_external_interface(
	serial String,
	created DateTime,
	model String,
	domain_name String,
    node_name String,
	active_connection String,
	ip_address String,
	addressing_type String,
	eth_mac Nullable(String),
	eth_duplex_mode Nullable(String),
	eth_current_bit_rate Nullable(Int16),
	optical_max_bit_rate Nullable(Int32),
	optical_signal_level Nullable(Int16),
	optical_transmit_level Nullable(Int16),
	cell_imei Nullable(String),
	cell_iccid Nullable(String),
	cell_msisdn Nullable(String),
	cell_imsi Nullable(String),
	cell_rssi Nullable(Int16),
	cell_rsrp Nullable(Int16),
	cell_rsrq Nullable(Int16),
	cell_ds_max_bit_rate Nullable(Int32),
	cell_us_max_bit_rate Nullable(Int32),
	cell_current_access_technology Nullable(String),
	dsl_ds_max_bit_rate Nullable(Int32),
	dsl_us_max_bit_rate Nullable(Int32),
	dsl_ds_noise_margin Nullable(Int16),
	dsl_us_noise_margin Nullable(Int16),
	dsl_ds_attenuation Nullable(Int16),
	dsl_us_attenuation Nullable(Int16),
	dsl_current_profile Nullable(String),
	stat_bytes_received Nullable(Int32),
	stat_bytes_sent Nullable(Int32),
	stat_errors_received Nullable(Int16),
	stat_errors_sent Nullable(Int16),
	stat_packets_received Nullable(Int16),
	stat_packets_sent Nullable(Int16),
	stat_discard_packets_received Nullable(Int16),
	stat_discard_packets_sent Nullable(Int16)
) ENGINE = MergeTree
PARTITION BY toDate(created)
ORDER BY (serial, created)
SETTINGS index_granularity = 8192;

CREATE TABLE network_issue(
	serial String,
	created DateTime,
	model String,
	domain_name String,
	type Enum8('HIGH_CPU_UTILIZATION', 'HIGH_MEMORY_UTILIZATION', 'GATEWAY_HIGH_TEMPERATURE', 'GATEWAY_REBOOTS_TOO_MANY_TIMES', 'GATEWAY_RESETS_TOO_MANY_TIMES', 'SLOW_DOWNLOAD_SPEED', 'SLOW_UPLOAD_SPEED', 'HIGH_LATENCY', 'INTERNET_CONNECTION_DROPS', 'INTERNET_CONNECTION_DOWN', 'LOW_INTERNET_CONNECTION_SPEED', 'INTERNET_CONNECTION_HALF_DUPLEX_MODE', 'WEAK_CELLULAR_SIGNAL', 'LEGACY_CONNECTION_TECHNOLOGY', 'WEAK_OPTICAL_SIGNAL_LEVEL', 'HIGH_OPTICAL_SIGNAL_LEVEL', 'WEAK_TRANSFER_OPTICAL_LEVEL', 'HIGH_TRANSFER_OPTICAL_LEVEL', 'LOW_DSL_INTERNET_CONNECTION_QUALITY', 'INTERNET_CONNECTION_HIGH_PACKET_ERROR_RATE', 'INTERNET_CONNECTION_HIGH_PACKET_DISCARD_RATE', 'WIFI_CLIENT_LOW_CONNECTION_QUALITY', 'WIFI_CLIENT_WEAK_SIGNAL', 'LEGACY_DEVICE_CONNECTED', 'HIGH_PACKET_RETRANSMISSION_RATE', 'TOO_MANY_DISCONNECTIONS','WIFI_CHANNEL_CONGESTION', 'WIFI_NETWORK_NOT_SECURED', 'WIFI_NETWORK_LOW_SECURITY', 'TOO_MANY_CLIENTS_ON_WIFI', 'LEGACY_WIFI_STANDARD_IN_USE'),
	value String,
	limit Nullable(String),
	data Nullable(String)
) ENGINE = MergeTree
PARTITION BY toDate(created)
ORDER BY (serial, type, created)
SETTINGS index_granularity = 8192;

CREATE TABLE network_score(
	serial String,
	created DateTime,
	model String,
	domain_name String,
	score Int16,
	cpu Int16,
	mem Int16,
	lan Int16,
	wan Int16,
	wlan Int16
) ENGINE = MergeTree
PARTITION BY toDate(created)
ORDER BY (serial, created)
SETTINGS index_granularity = 8192;

CREATE TABLE network_device_status(
	serial String,
	created DateTime,
	model Nullable(String),
	domain_name Nullable(String),
	status Enum8('Online', 'Offline')
) ENGINE = MergeTree
PARTITION BY toDate(created)
ORDER BY (serial, created)
SETTINGS index_granularity = 8192;

CREATE TABLE network_interface_stats (
    serial String,
    node_name String,
    model String,
    domain_name String,
    created DateTime,

    stat_errors_received Nullable(Int16),
    stat_errors_sent Nullable(Int16),
    stat_packets_received Nullable(Int16),
    stat_packets_sent Nullable(Int16),
    stat_discard_packets_received Nullable(Int16),
    stat_discard_packets_sent Nullable(Int16)
)
    ENGINE = MergeTree
    PARTITION BY toDate(created)
    ORDER BY (serial, node_name, created)
    SETTINGS index_granularity = 8192;

3. Defining DataSource Beans

A @Configuration class defines beans for the ClickHouse data source using HikariCP connection pool, which provides optimal performance and connection management:

Unresolved include directive in modules/ROOT/pages/database.adoc - include::../src/main/java/com/friendly/network/config/DatabaseConfig.java[]

4. Database Migrations (Flyway)

The application uses Flyway to manage database schema migrations in a versioned, repeatable, and automated way. Flyway tracks which migrations have already been applied and runs only new ones on each application startup.

4.1. Migration Principle

Flyway works by maintaining a special table — flyway_schema_history — directly in the target database. Each migration file is registered in this table after successful execution, along with its version, description, checksum, and execution status. On every application start, Flyway compares the migration files on the classpath against the history table and applies only those that haven’t been executed yet.

Key behaviors:

  • baseline-on-migrate: true — marks the existing database state as the baseline (version 1) when Flyway runs for the first time against a pre-existing schema, preventing it from trying to run migrations that predate the baseline.

  • validate-on-migrate: false — disables checksum validation on startup, which is useful when migration files may have been retroactively corrected without changing their version.

4.2. Configuration

Flyway is configured in application.yml with a dedicated connection separate from the application’s main data source. This allows Flyway to use its own JDBC driver and credentials:

flyway:
  enabled: true
  locations: classpath:db/changelog
  baseline-on-migrate: true
  validate-on-migrate: false
  url: jdbc:clickhouse://${CLICKHOUSE_HOST:localhost}:${CLICKHOUSE_HTTP_PORT:8123}/${CLICKHOUSE_DATABASE:ftacs_qoe_ui_data}
  user: ${CLICKHOUSE_USERNAME:ftacs}
  password: ${CLICKHOUSE_PASSWORD:ftacs}
  driver-class-name: com.clickhouse.jdbc.ClickHouseDriver

Flyway uses the com.clickhouse.jdbc.ClickHouseDriver (the official ClickHouse JDBC driver).

4.3. Migration File Format and Location

All migration SQL files are placed in:

src/main/resources/db/changelog/

Files must follow a strict naming convention:

V{version}__{description}.sql

Where:

  • {version} — a numeric version identifier (e.g., 1, 2, 1_1). Versions are applied in ascending order.

  • __ — two underscores separate the version from the description.

  • {description} — a short human-readable description using underscores instead of spaces (e.g., add_network_score_table).

Examples of valid migration file names:

V1__create_network_client_table.sql
V2__create_network_wifi_band_table.sql
V3__add_index_to_network_score.sql
V4__add_stat_error_columns.sql
Each migration file should contain one logical action (e.g., create one table, add one column, or modify one index). This makes migrations easier to review, debug, and roll back if necessary. Combining unrelated changes in a single file makes it harder to diagnose issues when a migration fails partway through.

4.4. Custom Migration Strategy

The application defines a custom FlywayMigrationStrategy bean in FlywayConfig to handle migration failures gracefully — particularly important in ClickHouse environments where certain DDL statements may fail due to schema drift or partial prior execution.

Unresolved include directive in modules/ROOT/pages/database.adoc - include::../src/main/java/com/friendly/network/config/FlywayConfig.java[]

The strategy works as follows:

  1. Attempt migration — Flyway runs all pending migrations normally.

  2. Handle failure — If a FlywayMigrateException is thrown, the strategy inspects flyway_schema_history for rows where success = false.

  3. Skip failed migrations — Each failed entry is deleted and re-inserted with a [SKIPPED] prefix in the description and success = true. This prevents Flyway from blocking on a migration that cannot succeed in the current environment (e.g., a CREATE TABLE for a table that already exists without IF NOT EXISTS).

  4. Retry — After cleaning up failed records, Flyway retries the migration loop. The loop continues until either all migrations succeed or no more failed entries are found to fix.

The skip-and-retry strategy means that a failed migration will be silently bypassed rather than blocking startup. It is important to verify after deployment that the expected schema changes have actually been applied, especially for critical structural changes.

5. Repository Implementation

The application implements two repository classes for ClickHouse operations:

5.1. NetworkDataPushRepository

This repository handles all write operations to ClickHouse, including:

  • Saving device data in batches

  • Processing network context information

  • Storing diagnostic test results

  • Recording network scores

The repository uses batch operations and asynchronous processing to optimize performance when dealing with large volumes of data.

5.2. NetworkDataGetRepository

This repository manages all read operations, providing methods to:

  • Retrieve the latest network status information

  • Query historical data within specific time ranges

  • Map database results to domain objects

The repository implements custom row mappers for each entity type to efficiently convert database records into application DTOs.

6. Monitoring and Metrics

The database operations are monitored using Micrometer metrics, which track:

  • Count of successful data insertions

  • Count of failed insertions

  • Processing time for database operations

  • Number of parameters processed

These metrics are exposed through Spring Boot Actuator endpoints and can be integrated with monitoring systems like Prometheus for observability.

7. Performance Considerations

The application is designed to efficiently handle high-volume data operations with ClickHouse:

  • Data is processed in configurable batch sizes to reduce the number of database operations

  • Asynchronous processing is used to prevent database operations from blocking the main application flow

  • Query timeouts are set appropriately for long-running operations

  • Connection pooling with HikariCP ensures efficient database connection management

  • Time-based partitioning in ClickHouse tables allows for efficient querying of recent data

The configuration class provides two primary beans:

  • The clickhouseDataSource bean configures the HikariCP connection pool with properties from the spring.datasource.clickhouse prefix in the application configuration.

  • The clickhouseJdbcTemplate bean creates a JdbcTemplate with the ClickHouse data source and sets a longer query timeout (300 seconds) to accommodate bulk insert operations that might take more time.

← Back | Main Page