Boundary data warehouse

Boundary's PostgreSQL database includes a built-in data warehouse (wh_* tables) that captures the complete lifecycle of sessions, connections, authentication tokens, users, hosts, and credentials. This dimensional model is populated automatically via database triggers as Boundary events occur, enabling real-time analytics without external ETL pipelines.

Refer to Audit the data warehouse for examples of queries that you can run against the data warehouse.

Architecture overview

The warehouse implements a star schema dimensional model:

Triggers on operational tables (11 total across 6 tables) fire on INSERT and UPDATE, ensuring near-real-time data flow from session creation through termination and connection rollup.

Fact tables

wh_session_accumulating_fact

This table tracks the complete lifecycle of user sessions from pending to termination. Uses the accumulating snapshot pattern where a single row is inserted on session creation and updated as the session progresses through state transitions.

wh_session_connection_accumulating_fact

This table tracks individual connections within sessions, providing per-connection network metrics.

wh_auth_token_accumulating_fact

This table tracks authentication token lifecycle: issuance, access, and deletion.

Dimension tables

All dimension tables use Slowly Changing Dimension Type 2 with current_row_indicator, row_effective_time, and row_expiration_time columns for historical tracking. Filter on current_row_indicator = 'Current' for the latest version of each entity.

wh_user_dimension

This table stores user identity with auth method, account, and organizational context.

Key attributes: user_id, user_name, auth_account_id, auth_account_type (password auth account / oidc auth account / ldap auth account), auth_account_email, auth_account_full_name, auth_method_type (password auth method / oidc auth method / ldap auth method), user_organization_id, user_organization_name.

wh_host_dimension

This table is a denormalized host dimension capturing the full target hierarchy.

Key attributes: host_id, host_type (static host / plugin host / direct address / Not Applicable), host_name, host_set_id, host_catalog_id, target_id, target_name, target_type (tcp target / ssh target / rdp target), target_default_port_number, project_id, project_name, organization_id, organization_name.

wh_credential_dimension

This table tracks credential libraries and stores with Vault integration metadata.

Key attributes: credential_purpose, credential_library_id, credential_library_type, credential_library_vault_path, credential_store_id, credential_store_type, credential_store_vault_address.

wh_network_address_dimension

This table classifies network addresses by type and address family.

Key attributes: address (PK, text), address_type (DNS Name / IP Address / Unknown), ip_address_family (IPv4 / IPv6 / Not Applicable), private_ip_address_indicator (Private IP address / Public IP address / Not Applicable).

wh_date_dimension and wh_time_of_day_dimension

This table features pre-populated conformed dimensions for time-series analysis from 2019-10-09 through a fixed future date. wh_time_of_day_dimension contains one row per second per day. These tables enable efficient date/time grouping without runtime calculations.

Use wh_date_dimension.key = wsf.session_pending_date_key to join and filter by date_actual, day_of_week, weekday_indicator, or calendar_month.

ETL architecture

Data flows from operational tables into the warehouse through an 11-trigger pipeline:

Operational Event          Trigger / Function             Warehouse Table(s)
─────────────────         ────────────────────           ──────────────────
session INSERT            wh_insert_session              wh_session_accumulating_fact
session_state INSERT      wh_insert_session_state        wh_session_accumulating_fact (state transitions)
session_connection INSERT wh_insert_session_connection   wh_session_connection_accumulating_fact
session_connection UPDATE wh_update_session_connection   wh_session_connection_accumulating_fact (bytes)
session_connection_state  wh_insert_session_connection_state  wh_session_connection_accumulating_fact
session_credential_dynamic INSERT  wh_insert_session_credential_dynamic  credential group mapping
                         + wh_upsert_credential_group    wh_credential_group + wh_credential_group_membership
session_host_set_host    wh_upsert_host                  wh_host_dimension (host resolution)
session_target_address   wh_upsert_host_direct_network_address  wh_host_dimension (direct address targets)
auth_token INSERT/UPDATE wh_insert_auth_token            wh_auth_token_accumulating_fact
session termination      wh_session_rollup               wh_session_accumulating_fact (connection count + bytes rollup)

ETL source views

Seven whx_* views provide the foundation for upserting dimension records:

• whx_user_dimension_source - Joins iam_user, auth_account, auth_password_account/auth_oidc_account/auth_ldap_account, auth_method, iam_scope
• whx_host_dimension_source - UNION of two CTEs: host-source targets (joins host, host_catalog, host_set, target) and direct-address targets (targets without host catalogs)
• whx_credential_dimension_source - Sources from credential_library, credential_store, credential_vault_library, target
• whx_network_address_dimension_source - Sources from static_host and host_plugin_host addresses

Related operational tables

The warehouse draws from these key operational tables not directly in the wh_* schema:

More information

Refer to Audit the data warehouse for examples of queries that you can run against the data warehouse.