Overview
The consumption views are already pre-joined for the common cases: v_findings carries asset, owner, and definition context directly, so most questions need no join at all (see Joining views in the query cookbook). This page is for the rest: BI tools that model relationships explicitly (Looker Studio, Tableau, Power BI) and any query that needs to reach a directory view or association not already flattened onto a core view.
Two things to keep in mind while reading the tables below:
- Target PK is
id for most views. A few views rename their primary key, for example v_owner_details.owner_id and v_request_details.request_id. Every FK column named *owner_id (risk_owner_id, remediation_owner_id, plain owner_id) joins to v_owner_details.owner_id.
- Fact-like views don't fan out on their own.
v_findings and v_assets are pre-aggregated to one row per entity. Fan-out only shows up when you join to a bridge view (for example v_asset_owner, v_ticket_finding) or unnest an array column. See Numeric id joins, never uid for the sentinel-row and skeleton-row rules that make these joins safe.
Core join graph
The most common analytical path: findings resolve to their asset and owners, and separately to their definition and CVE intelligence.
Every edge above is a foreign key documented in the tables below, except the v_finding_definitions-to-v_cve edge, which is many-to-many and goes through the v_definition_cve bridge view rather than a scalar FK column. The optional (o|) cardinality on the target side reflects that a foreign key can be absent (NULL) or resolve to a hidden internal placeholder row: a real target row isn't guaranteed, so use a LEFT JOIN and check for a missing match. See Sentinel rows in Core concepts.
Foreign keys on the core views
v_findings
| FK column | Target view | Target PK |
|---|
asset_id | v_assets | id |
definition_id | v_finding_definitions | id |
environment_id | v_environments | id |
finding_type_id | v_finding_types | id |
risk_owner_id | v_owner_details | owner_id |
remediation_owner_id | v_owner_details | owner_id |
risk_scoring_model_id | v_risk_scoring_models | id |
status_config_model_id | v_status_config_models | id |
sla_definition_id | v_sla_definitions | id |
asset_id and environment_id are each a single pick (the first asset, the first environment) for a finding that could technically span more than one. environment_ids (array) carries the full set; unnest it if you need every environment a finding touches.
v_assets
| FK column | Target view | Target PK | Notes |
|---|
asset_type_id | v_asset_types | id | |
os_family_id | v_os_families | id | |
owner_id | v_owner_details | owner_id | First confirmed owner only. Join v_asset_owner for the full owner set. |
risk_owner_id | v_owner_details | owner_id | |
remediation_owner_id | v_owner_details | owner_id | |
risk_scoring_model_id | v_risk_scoring_models | id | |
status_config_model_id | v_status_config_models | id | |
sla_definition_id | v_sla_definitions | id | |
environment_id | v_environments | id | First environment only. Join v_asset_environment for the full set. |
host_id | v_assets | id | Self-join: parent Host asset. |
package_id | v_assets | id | Self-join: parent Package catalog entry (populated on InstalledPackage rows). |
host_image_id | v_assets | id | Self-join: parent HostImage. |
image_id | v_assets | id | Self-join: parent ContainerImage (populated on Container rows). |
v_tickets
| FK column | Target view | Target PK |
|---|
sprint_id | v_sprints | id |
environment_id | v_environments | id |
Foreign keys on context and threat-intel views
| Source view | FK column | Target view | Target PK |
|---|
v_business_services | owner_id | v_owner_details | owner_id |
v_cloud_accounts | owner_id | v_owner_details | owner_id |
v_finding_definitions | finding_type_id | v_finding_types | id |
v_cve_details | cve_id | v_cve | id |
v_cve_details | finding_id | v_findings | id |
v_cve_products | cve_id | v_cve | id |
v_cve_products | cpe_id | v_cpe | id |
v_threat_intel | finding_id | v_findings | id |
v_threat_intel | definition_id | v_finding_definitions | id |
v_persons | asset_type_id | v_asset_types | id |
Foreign keys on history and snapshot views
The change-history and snapshot views (see the Core concepts glossary for Event Table / State-Snapshot View definitions) carry the same foreign keys as their current-state counterparts:
| Source view | FK column | Target view | Target PK |
|---|
v_finding_history | id | v_findings | id |
v_finding_history | asset_id | v_assets | id |
v_finding_history | definition_id | v_finding_definitions | id |
v_finding_history | risk_owner_id | v_owner_details | owner_id |
v_finding_history | remediation_owner_id | v_owner_details | owner_id |
v_finding_history | environment_id | v_environments | id |
v_asset_history | id | v_assets | id |
v_asset_history | risk_owner_id | v_owner_details | owner_id |
v_asset_history | remediation_owner_id | v_owner_details | owner_id |
v_asset_history | environment_id | v_environments | id |
v_asset_owner_history | asset_id | v_assets | id |
v_asset_owner_history | owner_id | v_owner_details | owner_id |
v_finding_asset_history | finding_id | v_findings | id |
v_finding_asset_history | asset_id | v_assets | id |
v_finding_asset_history | definition_id | v_finding_definitions | id |
v_coverage_gaps | environment_id | v_environments | id |
v_mttr_sla | sla_definition_id | v_sla_definitions | id |
v_mttr_sla_by_env | sla_definition_id | v_sla_definitions | id |
v_mttr_sla_by_env | environment_id | v_environments | id |
Many-to-many bridge views
Bridge views resolve a many-to-many relationship between two entity views. Both endpoint columns are join keys, and a bridge view has one row per confirmed pair. There are no sentinel rows, since an unresolved endpoint is dropped rather than pointed at "Unknown." See Bridge Table in the Core concepts glossary.
Asset bridges
| Bridge view | Left endpoint | Left view | Right endpoint | Right view | Notes |
|---|
v_asset_application | asset_id | v_assets | application_id | v_assets | Application is an asset subtype, so both endpoints resolve against v_assets. |
v_asset_business_service | asset_id | v_assets | business_service_id | v_business_services | |
v_asset_domain | asset_id | v_assets | asset_domain_id | v_asset_domains | |
v_asset_environment | asset_id | v_assets | environment_id | v_environments | Latest snapshot only. |
v_asset_informed_user | asset_id | v_assets | owner_id | v_owner_details | |
v_asset_owner | asset_id | v_assets | owner_id | v_owner_details | Latest snapshot, all confirmed owners (v_assets.owner_id is a single pick). |
v_asset_profile | asset_id | v_assets | asset_profile_id | v_asset_profiles | |
v_asset_installed_package | installed_package_id | v_assets | asset_id | v_assets | InstalledPackage and its target asset are both asset subtypes. |
v_asset_subnet | asset_id | v_assets | subnet_id | v_assets | Subnet is an asset subtype. |
v_asset_technology | asset_id | v_assets | asset_technology_id | v_asset_technologies | |
Finding bridges
| Bridge view | Left endpoint | Left view | Right endpoint | Right view |
|---|
v_finding_risk_factor | finding_id | v_findings | risk_factor_id | v_risk_factors |
v_finding_informed_user | finding_id | v_findings | owner_id | v_owner_details |
v_finding_definition_profile | finding_definition_id | v_finding_definitions | finding_profile_id | v_finding_profiles |
v_finding_definition_technology | finding_definition_id | v_finding_definitions | affected_technology_id | v_affected_technologies |
Ticket, owner, and request bridges
| Bridge view | Left endpoint | Left view | Right endpoint | Right view | Notes |
|---|
v_ticket_finding | ticket_id | v_tickets | finding_id | v_findings | Latest snapshot only. |
v_ticket_prev_finding | ticket_id | v_tickets | finding_id | v_findings | The ticket's previous-finding association, not snapshotted. |
v_ticket_user | ticket_id | v_tickets | user_id | v_owner_details | |
v_owner_member | owner_id | v_owner_details | user_id | v_owner_details | Both endpoints resolve against v_owner_details. |
v_request_finding | request_id | v_request_details | finding_id | v_findings | |
Threat-intel bridges
| Bridge view | Left endpoint | Left view | Right endpoint | Right view |
|---|
v_definition_cve | definition_id | v_finding_definitions | cve_record_id | v_cve |
v_cve_weakness | cve_id | v_cve | weakness_id | v_cwe |
v_definition_weakness | definition_id | v_finding_definitions | weakness_id | v_cwe |
v_cve_attack_technique | cve_id | v_cve | attack_technique_id | v_attack_techniques |
v_cve_attack_pattern | cve_id | v_cve | attack_pattern_id | v_attack_patterns |
v_attack_technique_tactic | attack_technique_id | v_attack_techniques | attack_tactic_id | v_attack_tactics |
v_mitigation_technique | mitigation_id | v_attack_mitigations | attack_technique_id | v_attack_techniques |
v_attack_pattern_weakness | attack_pattern_id | v_attack_patterns | weakness_id | v_cwe |
Self-referential hierarchy bridges
These three bridges walk a parent/child hierarchy within a single directory view.
| Bridge view | Child endpoint | Parent endpoint | View |
|---|
v_weakness_parent | weakness_id | parent_weakness_id | v_cwe |
v_attack_technique_parent | attack_technique_id | parent_technique_id | v_attack_techniques |
v_attack_pattern_parent | attack_pattern_id | parent_pattern_id | v_attack_patterns |