Database Inventory

The database inventory system provides dedicated databases and database_clusters tables for storing deep database instance data — replication topology, configuration snapshots, sizing, and logical cluster grouping. The PostgreSQL collector detects primary/replica roles automatically and groups instances into clusters.

Phase

Database CMDB Phase 2 complete. PostgreSQL topology detection is live. MySQL, Redis, and MongoDB schemas are ready but collectors are not yet built.

Overview

Unlike the earlier integration-to-asset bridge (which stored databases as lightweight assets rows with JSONB metadata), Database CMDB Phase 2 gives each database instance its own row in the databases table with 20+ typed columns. Logical groups (e.g., a primary with its replicas) are tracked in the database_clusters table.

Both tables link back to the assets table — individual databases use asset_type = 'database', and clusters use asset_type = 'database_cluster'.

Asset Types

Database CMDB Phase 2 introduces two asset types:

Asset Type	Source	ref_id Points To	Created By
database	Agent heartbeat (PostgreSQL collector)	databases.id	HeartbeatWorker (syncDatabases)
database_cluster	Auto-cluster detection	database_clusters.id	HeartbeatWorker (detectDatabaseCluster)

Topology Detection

The PostgreSQL collector runs three queries on each heartbeat to detect replication topology:

Role Detection

SELECT pg_is_in_recovery()

• Returns false on a primary (read-write) instance.
• Returns true on a replica (read-only, streaming from WAL).

The collector sets replication_role = "primary" or "replica" in the config map.

Upstream Discovery (Replicas Only)

SELECT conninfo FROM pg_stat_wal_receiver LIMIT 1

On a replica, this returns the connection string to the upstream primary. The collector parses host= and port= fields from the conninfo string and stores them as upstream_host and upstream_port.

Connected Replicas (Primaries Only)

SELECT COUNT(*) FROM pg_stat_replication

On a primary, this counts currently connected streaming replicas. Stored as connected_replicas in the config summary.

Auto-Cluster Grouping

The HeartbeatWorker automatically groups database instances into logical clusters after each database sync:

1. Primary creates cluster — When a database has role = "primary", the worker creates (or updates) a database_clusters row named {engine}-{hostname} (e.g., postgresql-db-prod-01). A corresponding database_cluster asset is created in the CMDB. The primary is linked to this cluster via databases.cluster_id.

2. Replica joins cluster — When a database has role = "replica" and an upstream_host, the worker looks up the host matching that hostname in the same environment, finds the primary's database row, and links the replica to the same cluster. The cluster's member_count is updated.

3. Orphan handling — Databases with role = "standalone" or without an upstream_host are not assigned to any cluster. They remain as standalone database assets.

The cluster topology is updated to "primary-replica" when replicas are detected, or stays "standalone" for single-instance clusters.

Config Summary

The PostgreSQL collector captures 18 pg_settings parameters on each heartbeat, organized by category:

Category	Parameters
Connections	max_connections, max_wal_senders
Memory	shared_buffers, effective_cache_size, work_mem, maintenance_work_mem
Performance	max_wal_size, checkpoint_timeout, random_page_cost, effective_io_concurrency, max_worker_processes, max_parallel_workers, max_parallel_workers_per_gather, wal_level
Logging	log_statement, log_min_duration_statement
Maintenance	autovacuum, autovacuum_max_workers
Storage	data_directory

Additionally, the topology detection adds up to 5 replication-related config entries: replication_role, is_read_only, upstream_host, upstream_port, connected_replicas.

All config entries are stored in the config_summary JSONB column with value, source, and category fields.

Supported Engines

Engine	Schema Ready	Collector Implemented
PostgreSQL	Yes	Yes
MySQL	Yes	No
Redis	Yes	No
MongoDB	Yes	No

The databases and database_clusters tables use an engine column (not an enum) so new engines can be added without migrations.

Table Schemas

database_clusters

Logical grouping of database instances (primary + replicas).

Column	Type	Description
id	UUID	Primary key
asset_id	UUID	FK to assets.id (ON DELETE CASCADE)
client_id	UUID	FK to clients.id
environment_id	UUID	FK to environments.id
name	TEXT	Cluster name (e.g., postgresql-db-prod-01)
engine	TEXT	Database engine (postgresql, mysql, etc.)
topology	TEXT	standalone or primary-replica
status	TEXT	Cluster status (active, degraded, unknown)
member_count	INTEGER	Number of database instances in the cluster
created_at	TIMESTAMPTZ	Record creation time
updated_at	TIMESTAMPTZ	Last modification time

Indexes: asset_id, client_id, environment_id, engine, status, unique on (environment_id, name).

databases

Individual database instances with deep inventory.

Column	Type	Description
id	UUID	Primary key
asset_id	UUID	FK to assets.id (ON DELETE CASCADE)
host_id	UUID	FK to hosts.id
cluster_id	UUID	FK to database_clusters.id (ON DELETE SET NULL)
name	TEXT	Collector name (e.g., postgresql-main)
engine	TEXT	Database engine (postgresql, mysql, etc.)
version	TEXT	Engine version (e.g., PostgreSQL 18.2)
port	INTEGER	Listen port
role	TEXT	primary, replica, or standalone
upstream_host	TEXT	Upstream primary hostname (replicas only)
upstream_port	INTEGER	Upstream primary port (replicas only)
replication_lag_seconds	NUMERIC(12,3)	Replication lag in seconds (replicas only)
max_connections	INTEGER	Configured max connections
current_connections	INTEGER	Current active connections
size_bytes	BIGINT	Total database size
database_count	INTEGER	Number of logical databases
config_summary	JSONB	Snapshot of key configuration parameters
status	TEXT	Instance status (active, degraded, inactive)
is_read_only	BOOLEAN	Whether the instance is read-only
last_backup_at	TIMESTAMPTZ	Last backup timestamp (reserved)
backup_method	TEXT	Backup method (reserved)
last_seen_at	TIMESTAMPTZ	Last heartbeat timestamp
created_at	TIMESTAMPTZ	Record creation time
updated_at	TIMESTAMPTZ	Last modification time

Indexes: asset_id, cluster_id, engine, role, host_id, status, unique on (host_id, name).

API Endpoints

All endpoints require authentication and the assets:read permission. Responses are scoped to the caller's allowed clients.

List Database Clusters

GET /api/v1/database-clusters

Query parameters: client_id, environment_id, engine, status, search, page, per_page.

Get Database Cluster

GET /api/v1/database-clusters/:databaseClusterID

Returns a single database cluster with full details.

List Databases

GET /api/v1/databases

Query parameters: client_id, environment_id, cluster_id, host_id, engine, role, status, search, page, per_page.

Get Database

GET /api/v1/databases/:databaseID

Returns a single database instance with full details.

Observability

Prometheus Metrics

Metric	Type	Description
proxima_databases_synced_total	counter	Total database instance upserts (label: engine)
proxima_database_cluster_detection_total	counter	Cluster detection attempts (label: result = created, linked, orphan)

OTel Spans

• worker.DatabaseSync — Top-level span for database sync during heartbeat processing
• worker.DatabaseClusterDetect — Cluster auto-detection span (attributes: engine, role, result)

Data Flow

Reserved Fields

The databases table includes two fields reserved for future backup detection:

• last_backup_at — Timestamp of the last successful backup. Will be populated when backup monitoring is implemented (e.g., via pg_stat_archiver or external backup tool integration).
• backup_method — Backup method identifier (e.g., pg_basebackup, pgbackrest, barman). Will be set by future backup detection collectors.

Both fields are nullable and default to NULL.