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.

Only PostgreSQL writes to these tables today, which is why this page is careful about the difference between a column that exists and a column something populates — see Columns with no writer. Seven of the databases table's columns are still unwritten.

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. Clusters always use asset_type = 'database_cluster'; an individual instance's asset is typed by its collector, which for the only shipped collector is database — see Asset Types.

Asset Types

Database CMDB Phase 2 introduces two asset types:

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

ref_id does not point at databases.id

assets.ref_id is a bare UUID with no foreign key, so which table it resolves against depends entirely on asset_type. For a database asset it is the host_integrations row — the collector — because the asset is created by EnsureForIntegration with the integration id it just wrote. The databases row then reuses that same asset as its own asset_id, so an instance's CMDB identity is its collector's asset.

domain.AssetReferents is the single place that mapping is written down, and the orphan reaper is driven from it (see Orphan reaping).

A database instance's asset is therefore typed by its collector, not by the databases table: PostgreSQL produces database, Redis produces cache, and both produce a databases row. That is deliberate — see Supported Engines.

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.

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

4. Already-linked members re-derive — A database that reports neither role but is already linked still re-derives its cluster's state, so a cluster can recover from the staleness sweep without waiting for a role change.

One derivation for topology, member count and status

member_count, topology and status are not written independently. Every path — both heartbeat paths and the stale detector — funnels through refreshDatabaseClusterState, which reads the cluster's member list once and hands it to deriveDatabaseClusterState. "2 nodes" and "primary-replica" are the same fact seen twice, and the page renders them in one breath, so they must not come from separate derivations. While the count was updated on its own, a freshly discovered primary+replica pair advertised standalone · 2 nodes until the primary next heartbeated.

Status has four tiers, in precedence order:

Tier	Condition
unreachable	every member is unreachable — the whole fleet has stopped reporting
degraded	any member unreachable, or any member degraded
active	any member active
inactive	otherwise

A cluster whose member list reads empty is left unchanged rather than zeroed. Every caller arrives having just written a member, so an empty read means the cluster row is gone or the host was re-homed out of the cluster's environment — writing the zero back would blank a live cluster on behalf of a host that no longer belongs to it.

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 as per-key objects mirroring the collector's ConfigItem: value, unit, source and category. unit is omitted when the collector reports none, so a setting without a unit carries no unit key at all — the same shape the host_integrations.config column writes.

The database detail page groups settings by category, stripping the collector's engine prefix ("PostgreSQL / Memory" renders as Memory). Entries with no category fall into a trailing Other group; a summary where nothing is categorised renders as a flat list.

note

config_summary is also the signal DatabaseStore.Upsert uses to decide whether a heartbeat carried collector config: a SQL NULL means "no config reported" and every config-derived column keeps its stored value. The sync worker therefore builds the map only when the collector sent at least one entry — it must never be non-nil but empty.

Supported Engines

Engine	Schema Ready	Collector Implemented	CMDB Asset Type
PostgreSQL	Yes	Yes	database
MySQL	Yes	No	— (unregistered)
Redis	Yes	No	cache
MongoDB	Yes	No	— (unregistered)

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

Redis is both a cache and a datastore, on purpose

Redis maps to the cache asset type while also counting as a database engine, so a Redis collector would produce a cache asset and a databases row from one heartbeat. That was raised as audit finding A-13 and closed as working-as-designed.

A heartbeat still creates exactly one asset per collector instance (EnsureForIntegration upserts on (asset_type, ref_id)), and the databases row reuses that asset as its own asset_id — it is a facet of the cache asset, not a rival identity. Nothing requires databases.asset_id to point at a database-typed asset. The duality is also true: Redis is the cache tier operators think in and a replicated datastore whose replicaof topology is exactly what the databases table models.

domain.integrationClaimTables maps both database and cache to databases for this reason, so the orphan reaper skips a claimed cache asset instead of decommissioning a live Redis instance's identity. Do not "fix" the classification without reading that map.

An engine with no entry in domain.IntegrationAssetTypes (mysql, mongodb) has its integration ID passed through as the asset_type verbatim, and such assets have no registered referent — they are deliberately never reaped and never aged. The stale detector reports them once per process instead of acting on them (CountUnregisteredAssetTypes).

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, derived from its members (active, degraded, inactive, unreachable, unknown until first derivation)
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 — no writer
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 — no writer
max_connections	INTEGER	Configured max connections
current_connections	INTEGER	Current active connections — no writer
size_bytes	BIGINT	Total database size — no writer
database_count	INTEGER	Number of logical databases — no writer
config_summary	JSONB	Snapshot of key configuration parameters
status	TEXT	Instance status (active, degraded, inactive, or unreachable once the stale detector finds last_seen_at older than PROXIMA_AGENT_STALE_TIMEOUT_SECONDS)
is_read_only	BOOLEAN	Whether the instance is read-only
last_backup_at	TIMESTAMPTZ	Last backup timestamp — no writer
backup_method	TEXT	Backup method — no writer
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).

Seven columns carry no writer — see Columns with no writer before building anything on them.

Config-less heartbeats preserve what they do not carry

DatabaseStore.Upsert treats a SQL NULL config_summary as "this heartbeat carried no collector config", and every config-derived column then keeps the value already stored: version, role, upstream_host, upstream_port, max_connections, config_summary, is_read_only.

This matters because the sync worker rebuilds domain.Database from scratch on every heartbeat while the agent attaches the collector's config only "if available" — both the config cache and the version string live in agent process memory, so an agent restart while the database is briefly unreachable ships a perfectly valid heartbeat with an empty config. Committing that blind reset role to standalone and NULLed version, upstream_host, max_connections and config_summary on a fully enrolled row.

The guard is row-level (a CASE on the config argument), not per-column COALESCE: is_read_only is NOT NULL, so an absent config sends false, which is indistinguishable from a measured false. Only a signal outside the column values can separate the two.

cluster_id has its own, narrower guard — COALESCE(EXCLUDED.cluster_id, databases.cluster_id) — so a caller passing NULL cannot unlink a database from its cluster. Unlinking is done by dedicated statements elsewhere (the inventory worker's direct UPDATE, host deletion, and the FK's ON DELETE SET NULL), all of which bypass this one.

API Endpoints

All endpoints require authentication and the assets:read permission. Responses are scoped to the caller's environment-level tenant scope, not merely client membership: scopeForPtr(ac, PermAssetsRead) unions client-wide grants with env-scoped grants, so an env-scoped user never sees a sibling environment's databases.

A databases row's tenant lineage is dual — a host-attached row resolves through host → environment, a cluster-attached row through its database_cluster — and the list query COALESCEs both paths so neither lineage leaks a sibling environment and neither is silently dropped from a scoped list.

Filtering and pagination are server-side. Every filter below is applied in SQL, and page / per_page slice the filtered set — the client does not fetch a large slice and narrow it locally, so the reported total is the count of matching rows rather than the count of fetched ones.

List Database Clusters

GET /api/v1/database-clusters

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

Get Database Cluster

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

Returns { cluster, members } — the cluster plus its member instances. The member list is restricted to databases whose host lives in the cluster's own environment: hosts carry no client_id, so environment equality is what makes the read tenant-safe when a host has been re-homed while its rows still point at the previous client's cluster.

List Databases

GET /api/v1/databases

Query parameters: 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. Lineage is resolved through the host (authoritative), falling back to the cluster only when there is no host.

Sort order is total, not just updated_at

Both list endpoints sort ORDER BY updated_at DESC, id DESC, and the member list sorts role, name, id. The trailing key is load-bearing, not decoration.

updated_at is not unique: every heartbeat rewrites it, and the staleness sweep rewrites it for every row it touches in one statement where NOW() is evaluated once — so a fleet that goes quiet together lands on byte-identical timestamps. PostgreSQL guarantees no order for equal sort keys, and each page is an independent LIMIT/OFFSET query, so ties were broken differently per page: measured on a 40-row/8-page fixture, one row came back on three pages and two rows were never returned at all. The primary key makes the order total and every row reachable exactly once.

The member list ties for a different reason: role and name are exactly the two columns a replica set holds constant (every replica reports role='replica', and name is the collector's name, identical across hosts running the same agent config), so its rows reshuffled between polls.

Lifecycle: going stale, and being reaped

The heartbeat path only ever writes what a collector just reported. Nothing in it writes the absence of a report, so for a long time a dead database kept status = 'active' with a frozen last_seen_at indefinitely and the page showed a green badge for something that had not reported in weeks. Four passes of the StaleDetectorWorker close that, running every PROXIMA_AGENT_STALE_CHECK_SECONDS (default 60) against a threshold of PROXIMA_AGENT_STALE_TIMEOUT_SECONDS (default 300).

1. Database instances go unreachable

DatabaseStore.MarkStaleUnreachable marks every instance whose last_seen_at predates the threshold. last_seen_at is the right predicate precisely because it covers both causes at once, which look identical from this table: a dead agent stops every collector's row advancing, and a collector removed from an agent's config stops its own row advancing while the host keeps heartbeating. A host cascade would miss the second.

The pass returns the distinct clusters those rows belong to, and each is re-derived through refreshDatabaseClusterState — otherwise the instances read unreachable while the cluster above them still reads active.

2. Clusters re-derive, and recover

Cluster status is never written directly by the sweep; it falls out of the member derivation above. Recovery needs no separate path either: the next heartbeat's upsert writes the reported status straight back, and the already-linked branch of cluster detection re-derives the cluster even for a member that reports no role.

3. Integration assets age when their collector goes silent

AssetStore.MarkStaleIntegrationsUnreachable takes the CMDB asset of every collector that stopped reporting to unreachable, reading freshness from host_integrations.last_seen_at.

This is a genuinely separate hole from the reaper's. When a host is deleted its host_integrations rows cascade away, the asset's ref_id dangles, and the orphan reaper finds it. Here the host is alive and the ref_id still resolves — the sweep joins through it — so no reaper can reach the row. Nothing deletes a host_integrations row when a collector leaves the agent's config, so it persists with a frozen last_seen_at while the asset's status is written only by EnsureForIntegration, on a heartbeat that reports that collector.

Which statuses age, and which are none of the sweep's business

Both sweeps age a whitelist of the statuses a collector itself can write — active, degraded, inactive (domain.CollectorReportedAssetStatuses) — rather than "anything that is not already unreachable". The whitelist subsumes idempotency, and it additionally answers a question a blacklist cannot: who wrote the status being replaced. maintenance is an operator's deliberate claim and decommissioned is the orphan reaper's end state; a row parked for maintenance is silent because it was parked, so aging it would turn a deliberate state into a fault report.

inactive stays in the aged set, and the reason is worth stating precisely because it is easy to get wrong: it does not mean "a collector the operator switched off". A collector disabled in the agent's config is never built and never appears in a heartbeat at all. inactive is written for Enabled: false, whose only producer is agent auto-discovery — a service found running on the host with no collector configured for it — and those entries are re-sent on every heartbeat. So inactive is a live claim that must stop being advertised when the agent dies, and must never be reused to express silence.

A NULL last_seen_at is left alone by both sweeps (last_seen_at < $n never matches NULL): a row that never recorded a report time is not evidence of staleness. Same posture as hosts.MarkStaleOffline.

Orphan reaping

assets.ref_id has no foreign key, so an asset outlives its referent silently. AssetStore.DecommissionOrphanedAssets sweeps by asset type, driven by the closed whitelist in domain.AssetReferents:

• An asset type with no registered referent is left completely alone — there is no default branch, because the heartbeat worker passes an unregistered integration ID through as the asset_type verbatim and sweeping those would mean guessing which table the ref_id resolves against.
• An asset a live row still claims through <claim table>.asset_id is skipped. Such an asset is misreferenced, not stranded — it is a real entity's CMDB identity. Migration 000193 repairs those rows rather than removing them.
• An asset created within a one-minute grace period is skipped, because clusters.asset_id and database_clusters.asset_id are NOT NULL: their writers must commit the asset before the row that references it, leaving a brief window in which a legitimately-arriving asset has no referent yet. Integration assets are written the other way round and need no such grace.

Assets are decommissioned, not deleted. databases and database_clusters reference assets(id) ON DELETE CASCADE, so removing an asset could take live inventory with it, and the compliance verdicts recorded against a machine that really existed are worth keeping.

The cluster path also self-heals: when a cluster upsert conflicts onto a pre-existing row and returns an id the caller never supplied, healClusterAsset ensures the asset for the id that actually exists and repoints the cluster at it, downgrading the stale row from "the cluster's own identity" to an unclaimed orphan a reaper can safely remove.

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)

Lifecycle log lines

The stale detector's database passes emit no metrics of their own; they log under component=worker sub=stale:

Message	Fields	Emitted when
marked stale databases unreachable	count, cluster_count	instances aged, with the clusters queued for re-derivation
cluster membership refreshed (DEBUG)	cluster_id, members, topology, status	a cluster re-derived its denormalized state
cluster membership read returned no members; leaving counts unchanged (WARN)	cluster_id	the member read came back empty — the cluster is gone, or a host was re-homed out of its environment
marked stale integration assets unreachable	count	collector assets aged
decommissioned orphaned assets	asset_type, count	the orphan reaper acted, per type
asset_type has no registered referent and is never reaped (WARN)	asset_type, count	once per process, reporting types the reaper deliberately never sweeps

Data Flow

The read existing cluster_id step is not incidental. The struct the worker builds is the first upsert of every heartbeat, and it used to leave ClusterID nil — so every heartbeat unlinked the database from its cluster and relied on the topology paths below to relink it. A heartbeat that failed in between, or a collector whose role resolves to standalone and reaches no cluster path at all, left the row unlinked and dropped it out of its own cluster's member list.

And the stale path, running on its own timer rather than on a heartbeat:

Columns with no writer

worker.syncDatabases is the sole writer of the databases table, and the struct it builds sets AssetID, HostID, ClusterID, Name, Engine, Version, Role, UpstreamHost, UpstreamPort, MaxConnections, ConfigSummary, Status, IsReadOnly, LastSeenAt — and nothing else. Seven columns are consequently always NULL in production:

Column	Why it is empty	Where the data actually is
port	Never parsed out of the collector's DSN	—
current_connections	Collected as a metric, not as config	pg_connections_active / pg_connections_idle in VictoriaMetrics
replication_lag_seconds	Collected as a metric	pg_replication_lag_seconds
size_bytes	Collected as a metric	pg_database_size_bytes
database_count	Not collected	—
last_backup_at	Reserved for future backup detection (pg_stat_archiver or an external backup tool)	—
backup_method	Reserved for future backup detection (pg_basebackup, pgbackrest, barman, …)	—

Four of the seven are already sitting in VictoriaMetrics; they simply never reach this table or the page. The database detail page omits them entirely rather than showing a fabricated 0 — an absent measurement and a measured zero are different claims, and a database with no reported size must not read as an empty one. port is rendered only when non-null for the same reason.

These seven are also the columns Upsert deliberately does not guard against a config-less heartbeat. Guarding them would add a can-never-be-cleared rule to columns whose first real writer may well need to clear one. Anyone who starts populating one must revisit that decision.