Skip to content

Database schema reference

Auto-generated from migrations/V*.sql. Run scripts/docs/regen.py to refresh.

The schema is defined by 69 versioned migrations applied in order at backend startup. PostgreSQL 16 is the production target; SQLite is supported for development and small-scale deployments. Schema parity between the two backends is mandatory — see SQLite implementation notes.

Append-only tables (no UPDATE, no DELETE, ever — they are the audit substrate):

  • action_log
  • check_events
  • command_executions
  • config_versions
  • enrollment_events
  • state_transitions
  • switchover_log

Entity-relationship diagram (foreign keys only)

Tables without foreign keys to other tables (lookup / configuration tables) are omitted from this diagram but listed below.

erDiagram
    agent_binaries ||--o{ users : "uploaded_by"
    agent_metrics ||--o{ agents : "agent_id"
    agent_update_tasks ||--o{ agents : "agent_id"
    agents ||--o{ organizations : "organization_id"
    agents ||--o{ gateways : "gateway_id"
    api_keys ||--o{ users : "user_id"
    app_permissions_teams ||--o{ applications : "application_id"
    app_permissions_teams ||--o{ teams : "team_id"
    app_permissions_teams ||--o{ users : "granted_by"
    app_permissions_users ||--o{ applications : "application_id"
    app_permissions_users ||--o{ users : "user_id"
    app_permissions_users ||--o{ users : "granted_by"
    app_references ||--o{ applications : "source_app_id"
    app_references ||--o{ applications : "target_app_id"
    app_references ||--o{ components : "component_id"
    app_references ||--o{ users : "created_by"
    app_share_links ||--o{ applications : "application_id"
    app_share_links ||--o{ users : "created_by"
    app_variables ||--o{ applications : "application_id"
    applications ||--o{ organizations : "organization_id"
    applications ||--o{ sites : "site_id"
    approval_decisions ||--o{ approval_requests : "request_id"
    approval_decisions ||--o{ users : "decided_by"
    approval_policies ||--o{ organizations : "organization_id"
    approval_requests ||--o{ organizations : "organization_id"
    approval_requests ||--o{ users : "requested_by"
    binding_profile_mappings ||--o{ binding_profiles : "profile_id"
    binding_profile_mappings ||--o{ agents : "agent_id"
    binding_profiles ||--o{ applications : "application_id"
    binding_profiles ||--o{ users : "created_by"
    break_glass_accounts ||--o{ organizations : "organization_id"
    break_glass_sessions ||--o{ break_glass_accounts : "account_id"
    break_glass_sessions ||--o{ organizations : "organization_id"
    certificate_events ||--o{ agents : "agent_id"
    certificate_rotations ||--o{ organizations : "organization_id"
    certificate_rotations ||--o{ agents : "agent_id"
    certificate_rotations ||--o{ gateways : "gateway_id"
    cluster_member_state ||--o{ cluster_members : "cluster_member_id"
    cluster_members ||--o{ components : "component_id"
    cluster_members ||--o{ agents : "agent_id"
    cluster_members ||--o{ sites : "site_id"
    command_input_params ||--o{ component_commands : "command_id"
    component_catalog ||--o{ organizations : "org_id"
    component_commands ||--o{ components : "component_id"
    component_groups ||--o{ applications : "application_id"
    component_links ||--o{ components : "component_id"
    component_log_sources ||--o{ components : "component_id"
    component_log_sources ||--o{ organizations : "organization_id"
    component_log_sources ||--o{ users : "created_by"
    components ||--o{ applications : "application_id"
    components ||--o{ agents : "agent_id"
    components ||--o{ agents : "rebuild_agent_id"
    dependencies ||--o{ applications : "application_id"
    dependencies ||--o{ components : "from_component_id"
    dependencies ||--o{ components : "to_component_id"
    discovery_draft_components ||--o{ discovery_drafts : "draft_id"
    discovery_draft_components ||--o{ agents : "agent_id"
    discovery_draft_dependencies ||--o{ discovery_drafts : "draft_id"
    discovery_draft_dependencies ||--o{ discovery_draft_components : "from_component"
    discovery_draft_dependencies ||--o{ discovery_draft_components : "to_component"
    discovery_drafts ||--o{ organizations : "organization_id"
    discovery_drafts ||--o{ applications : "applied_app_id"
    discovery_reports ||--o{ agents : "agent_id"
    dr_pattern_rules ||--o{ organizations : "organization_id"
    embedded_sessions ||--o{ organizations : "org_id"
    embedded_sessions ||--o{ users : "user_id"
    embedded_sessions ||--o{ applications : "simulation_app_id"
    enrollment_events ||--o{ organizations : "organization_id"
    enrollment_events ||--o{ enrollment_tokens : "token_id"
    enrollment_tokens ||--o{ organizations : "organization_id"
    enrollment_tokens ||--o{ users : "created_by"
    enrollment_tokens ||--o{ users : "revoked_by"
    failover_health_status ||--o{ binding_profiles : "profile_id"
    failover_health_status ||--o{ agents : "agent_id"
    gateway_status_events ||--o{ organizations : "organization_id"
    gateway_status_events ||--o{ gateways : "gateway_id"
    gateways ||--o{ organizations : "organization_id"
    hostings ||--o{ organizations : "organization_id"
    knowledge_approvals ||--o{ knowledge_proposals : "proposal_id"
    knowledge_approvals ||--o{ users : "reviewed_by"
    knowledge_proposals ||--o{ applications : "application_id"
    knowledge_proposals ||--o{ users : "created_by"
    knowledge_proposals ||--o{ knowledge_revisions : "application_id,revision"
    knowledge_publications ||--o{ knowledge_proposals : "proposal_id"
    knowledge_publications ||--o{ applications : "application_id"
    knowledge_publications ||--o{ users : "published_by"
    knowledge_revisions ||--o{ applications : "application_id"
    knowledge_revisions ||--o{ users : "created_by"
    log_access_audit ||--o{ organizations : "organization_id"
    log_access_audit ||--o{ users : "user_id"
    log_access_audit ||--o{ components : "component_id"
    log_access_audit ||--o{ component_log_sources : "log_source_id"
    manual_task_validations ||--o{ components : "component_id"
    manual_task_validations ||--o{ applications : "application_id"
    manual_task_validations ||--o{ users : "started_by"
    manual_task_validations ||--o{ users : "validated_by"
    notification_preferences ||--o{ users : "user_id"
    operation_locks ||--o{ applications : "app_id"
    operation_locks ||--o{ users : "user_id"
    operation_schedule_executions ||--o{ operation_schedules : "schedule_id"
    operation_schedule_executions ||--o{ action_log : "action_log_id"
    operation_schedules ||--o{ organizations : "organization_id"
    operation_schedules ||--o{ applications : "application_id"
    operation_schedules ||--o{ components : "component_id"
    operation_schedules ||--o{ action_log : "last_action_log_id"
    operation_schedules ||--o{ users : "created_by"
    pki_maintenance_installations ||--o{ organizations : "organization_id"
    pki_maintenance_requests ||--o{ pki_maintenance_installations : "organization_id,installation_id"
    portfolio_generation_applications ||--o{ organizations : "organization_id"
    portfolio_generation_applications ||--o{ applications : "published_application_id"
    portfolio_generation_jobs ||--o{ portfolio_generation_applications : "organization_id,source,application_key"
    portfolio_source_refreshes ||--o{ organizations : "organization_id"
    rebuild_operations ||--o{ applications : "application_id"
    revoked_certificates ||--o{ organizations : "organization_id"
    revoked_certificates ||--o{ agents : "agent_id"
    revoked_certificates ||--o{ users : "revoked_by"
    rotation_progress ||--o{ organizations : "organization_id"
    rotation_progress ||--o{ users : "initiated_by"
    saml_group_mappings ||--o{ teams : "team_id"
    saved_views ||--o{ users : "user_id"
    saved_views ||--o{ applications : "application_id"
    scheduled_snapshots ||--o{ snapshot_schedules : "schedule_id"
    scheduled_snapshots ||--o{ organizations : "organization_id"
    site_mock_agents ||--o{ sites : "site_id"
    site_mock_agents ||--o{ agents : "agent_id"
    site_mock_agents ||--o{ users : "updated_by"
    site_overrides ||--o{ components : "component_id"
    site_overrides ||--o{ sites : "site_id"
    site_overrides ||--o{ agents : "agent_id_override"
    sites ||--o{ organizations : "organization_id"
    snapshot_schedules ||--o{ organizations : "organization_id"
    snapshot_schedules ||--o{ users : "created_by"
    team_members ||--o{ teams : "team_id"
    team_members ||--o{ users : "user_id"
    teams ||--o{ organizations : "organization_id"
    user_favorites ||--o{ users : "user_id"
    user_favorites ||--o{ applications : "application_id"
    users ||--o{ organizations : "organization_id"
    webhook_deliveries ||--o{ webhook_endpoints : "webhook_id"
    webhook_endpoints ||--o{ organizations : "organization_id"
    webhook_endpoints ||--o{ applications : "application_id"
    workspace_members ||--o{ workspaces : "workspace_id"
    workspace_members ||--o{ users : "user_id"
    workspace_members ||--o{ teams : "team_id"
    workspace_sites ||--o{ workspaces : "workspace_id"
    workspace_sites ||--o{ sites : "site_id"
    workspaces ||--o{ organizations : "organization_id"
    x4b_external_sessions ||--o{ users : "user_id"
    x4b_external_sessions ||--o{ x4b_module_grants : "organization_id,user_id,module_key"
    x4b_handoffs ||--o{ users : "user_id"
    x4b_handoffs ||--o{ x4b_module_grants : "organization_id,user_id,module_key"
    x4b_knowledge_settings ||--o{ organizations : "organization_id"
    x4b_module_grants ||--o{ users : "user_id"
    x4b_module_grants ||--o{ x4b_modules : "organization_id,module_key"
    x4b_modules ||--o{ organizations : "organization_id"
    x4b_user_policies ||--o{ organizations : "organization_id"
    x4b_user_policies ||--o{ users : "user_id"

Tables

Total: 91 tables.

action_log

Append-only

No UPDATE or DELETE is ever issued against this table. Retention is enforced by partition drops (check_events) or by external archival jobs.

Append-only record of every user-initiated action. The DORA Article 16 source of truth.

Primary key: (id)

Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
user_id U NOT NULL
action V NOT NULL
resource_type V NOT NULL
resource_id U NOT NULL
details J default '{}'
created_at T NOT NULL, default NOW()

Indexes: idx_action_log_user, idx_action_log_resource, idx_action_log_action, idx_action_log_status, idx_action_log_member

Defined in: migrations/V005__event_tables.sql

agent_binaries

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (uploaded_by) → users(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
version T NOT NULL, UNIQUE
platform T NOT NULL, default 'LINUX-AMD64'
size_bytes B NOT NULL
binary_data B NOT NULL
uploaded_by U FK→users
uploaded_at T NOT NULL, default NOW()

Defined in: migrations/V021__discovery_estimates_airgap.sql

agent_metrics

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (agent_id, created_at)

Foreign keys:

  • (agent_id) → agents(id)
Column Type Constraints
id B —
agent_id U NOT NULL, FK→agents
cpu_pct R NOT NULL
memory_pct R NOT NULL
disk_used_pct R —
created_at T NOT NULL, default NOW()

Indexes: idx_agent_metrics_agent_time

Defined in: migrations/V027__agent_metrics.sql

agent_update_tasks

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (agent_id) → agents(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
agent_id U NOT NULL, FK→agents
target_version T NOT NULL
status T NOT NULL, default 'PENDING'
chunks_sent I NOT NULL, default 0
total_chunks I NOT NULL, default 0
error T —
started_at T NOT NULL, default NOW()
completed_at T —

Indexes: idx_agent_update_tasks_agent, idx_agent_update_tasks_status, idx_agent_update_tasks_agent, idx_agent_update_tasks_status

Defined in: migrations/V021__discovery_estimates_airgap.sql

agents

Every host running the AppControl agent binary. last_heartbeat_at drives the UNREACHABLE state.

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
  • (gateway_id) → gateways(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
hostname V NOT NULL
gateway_id U FK→gateways
labels J default '{}'
version V —
last_heartbeat_at T —
is_active B NOT NULL, default TRUE
created_at T NOT NULL, default NOW()

Indexes: idx_agents_org, idx_agents_gateway, idx_agents_hostname, idx_agents_status

Defined in: migrations/V002__agents_gateways.sql

api_keys

Long-lived API tokens for schedulers and CLI users.

Primary key: (id)

Foreign keys:

  • (user_id) → users(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
user_id U NOT NULL, FK→users
name V NOT NULL
key_hash V NOT NULL, UNIQUE
key_prefix V NOT NULL
scopes J default '["*"]'
is_active B NOT NULL, default TRUE
expires_at T —
last_used_at T —
created_at T NOT NULL, default NOW()

Indexes: idx_api_keys_user, idx_api_keys_hash

Defined in: migrations/V007__api_keys_notifications.sql

app_permissions_teams

Per-app permission grant to a team.

Primary key: (id)

Foreign keys:

  • (application_id) → applications(id)
  • (team_id) → teams(id)
  • (granted_by) → users(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
application_id U NOT NULL, FK→applications
team_id U NOT NULL, FK→teams
permission_level V NOT NULL
granted_by U NOT NULL, FK→users
expires_at T —
created_at T NOT NULL, default NOW()
updated_at T NOT NULL, default NOW()

Indexes: idx_app_perms_teams_app, idx_app_perms_teams_team

Defined in: migrations/V006__teams_permissions.sql

app_permissions_users

Direct per-app permission grant to a user.

Primary key: (id)

Foreign keys:

  • (application_id) → applications(id)
  • (user_id) → users(id)
  • (granted_by) → users(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
application_id U NOT NULL, FK→applications
user_id U NOT NULL, FK→users
permission_level V NOT NULL
granted_by U NOT NULL, FK→users
expires_at T —
created_at T NOT NULL, default NOW()
updated_at T NOT NULL, default NOW()

Indexes: idx_app_perms_users_app, idx_app_perms_users_user

Defined in: migrations/V006__teams_permissions.sql

app_references

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (source_app_id) → applications(id)
  • (target_app_id) → applications(id)
  • (component_id) → components(id)
  • (created_by) → users(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
source_app_id U NOT NULL, FK→applications
target_app_id U NOT NULL, FK→applications
component_id U NOT NULL, FK→components
created_at T NOT NULL, default NOW()
created_by U FK→users

Indexes: idx_app_references_target, idx_app_references_source

Defined in: migrations/V034__app_references.sql

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (application_id) → applications(id)
  • (created_by) → users(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
application_id U NOT NULL, FK→applications
token V NOT NULL, UNIQUE
permission_level V NOT NULL
created_by U NOT NULL, FK→users
expires_at T —
max_uses I —
use_count I NOT NULL, default 0
is_active B NOT NULL, default TRUE
created_at T NOT NULL, default NOW()

Indexes: idx_share_links_app, idx_share_links_token

Defined in: migrations/V006__teams_permissions.sql

app_variables

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (application_id) → applications(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
application_id U NOT NULL, FK→applications
name V NOT NULL
value T NOT NULL, default ''
description T —
is_secret B NOT NULL, default FALSE
created_at T NOT NULL, default NOW()
updated_at T NOT NULL, default NOW()

Indexes: idx_app_variables_app

Defined in: migrations/V010__variables_groups_params.sql

applications

Top-level operational unit. Each application is a DAG of components.

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
  • (site_id) → sites(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
name V NOT NULL
description T —
site_id U NOT NULL, FK→sites
tags J default '[]'
created_at T NOT NULL, default NOW()
updated_at T NOT NULL, default NOW()

Indexes: idx_applications_org, idx_applications_site, idx_applications_suspended, idx_applications_source_reference

Defined in: migrations/V003__sites_applications.sql

approval_decisions

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (request_id) → approval_requests(id)
  • (decided_by) → users(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
request_id U NOT NULL, FK→approval_requests
decided_by U NOT NULL, FK→users
decision V NOT NULL
reason T —
created_at T NOT NULL, default NOW()

Indexes: idx_approval_decisions_request

Defined in: migrations/V013__security_resilience.sql

approval_policies

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
operation_type V NOT NULL
risk_level V NOT NULL, default 'HIGH'
required_approvals I NOT NULL, default 1
timeout_minutes I NOT NULL, default 15
enabled B NOT NULL, default TRUE
created_at T NOT NULL, default NOW()

Defined in: migrations/V013__security_resilience.sql

approval_requests

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
  • (requested_by) → users(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
operation_type V NOT NULL
resource_type V NOT NULL
resource_id U NOT NULL
risk_level V NOT NULL, default 'MEDIUM'
requested_by U NOT NULL, FK→users
request_payload J NOT NULL, default '{}'
status V NOT NULL, default 'PENDING'
required_approvals I NOT NULL, default 1
created_at T NOT NULL, default NOW()
expires_at T NOT NULL
resolved_at T —
executed_at T —
execution_result J —

Indexes: idx_approval_requests_org, idx_approval_requests_status, idx_approval_requests_resource

Defined in: migrations/V013__security_resilience.sql

binding_profile_mappings

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (profile_id) → binding_profiles(id)
  • (agent_id) → agents(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
profile_id U NOT NULL, FK→binding_profiles
component_name V NOT NULL
host V NOT NULL
agent_id U NOT NULL, FK→agents
resolved_via V NOT NULL, default 'MANUAL'
CHECK (RESOLVED_VIA IN ('EXACT_HOSTNAME'
created_at T NOT NULL, default NOW()

Indexes: idx_binding_profile_mappings_profile, idx_binding_profile_mappings_agent

Defined in: migrations/V030__binding_profiles.sql

binding_profiles

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (application_id) → applications(id)
  • (created_by) → users(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
application_id U NOT NULL, FK→applications
name V NOT NULL
description T —
profile_type V NOT NULL, default 'CUSTOM'
CHECK (PROFILE_TYPE IN ('PRIMARY'
is_active B NOT NULL, default FALSE
gateway_ids U NOT NULL, default '{}'
auto_failover B NOT NULL, default FALSE
created_at T NOT NULL, default NOW()
created_by U FK→users

Indexes: idx_binding_profiles_active, idx_binding_profiles_app, idx_binding_profiles_type

Defined in: migrations/V030__binding_profiles.sql

break_glass_accounts

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
username V NOT NULL
password_hash V NOT NULL
is_active B NOT NULL, default TRUE
created_at T NOT NULL, default NOW()
last_rotated_at T NOT NULL, default NOW()

Defined in: migrations/V013__security_resilience.sql

break_glass_sessions

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (account_id) → break_glass_accounts(id)
  • (organization_id) → organizations(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
account_id U NOT NULL, FK→break_glass_accounts
organization_id U NOT NULL, FK→organizations
activated_by_ip V NOT NULL
reason T NOT NULL
started_at T NOT NULL, default NOW()
expires_at T NOT NULL
ended_at T —
actions_taken I NOT NULL, default 0

Indexes: idx_break_glass_sessions_active

Defined in: migrations/V013__security_resilience.sql

certificate_events

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (agent_id) → agents(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
agent_id U FK→agents
gateway_id U —
event_type V NOT NULL
fingerprint V —
cn V —
issued_at T —
expires_at T —
created_at T NOT NULL, default NOW()

Indexes: idx_certificate_events_agent

Defined in: migrations/V013__security_resilience.sql

certificate_rotations

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
  • (agent_id) → agents(id)
  • (gateway_id) → gateways(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
rotation_id U NOT NULL
agent_id U FK→agents
gateway_id U FK→gateways
old_fingerprint V —
new_fingerprint V —
status V NOT NULL, default 'PENDING'
rotated_at T NOT NULL, default NOW()
hostname V —
error_message T —

Indexes: idx_cert_rotations_org_rotation, idx_cert_rotations_agent, idx_cert_rotations_gateway

Defined in: migrations/V025__certificate_rotation.sql

check_events

Append-only

No UPDATE or DELETE is ever issued against this table. Retention is enforced by partition drops (check_events) or by external archival jobs.

Append-only, partitioned by day. One row per health check execution. Retention controlled by RETENTION_CHECK_EVENTS_DAYS.

Column Type Constraints
id B —
component_id U NOT NULL
exit_code S NOT NULL
stdout T —
duration_ms I NOT NULL
created_at T NOT NULL, default NOW()

Indexes: idx_check_events_component, idx_check_events_type, idx_check_events_metrics, idx_check_events_member

Defined in: migrations/V005__event_tables.sql

cluster_member_state

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (cluster_member_id)

Foreign keys:

  • (cluster_member_id) → cluster_members(id)
Column Type Constraints
cluster_member_id U PK, FK→cluster_members
current_state V NOT NULL, default 'UNKNOWN'
last_check_at T —
last_check_exit_code S —
last_check_duration_ms I —
last_stdout T —
updated_at T NOT NULL, default NOW()

Indexes: idx_cluster_member_state_state

Defined in: migrations/V049__fan_out_clusters.sql

cluster_members

Members of a fan-out cluster (each member is an addressable host within a parent component).

Primary key: (id)

Foreign keys:

  • (component_id) → components(id)
  • (agent_id) → agents(id)
  • (site_id) → sites(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
component_id U NOT NULL, FK→components
hostname V NOT NULL
agent_id U NOT NULL, FK→agents
site_id U FK→sites
start_cmd_override T —
stop_cmd_override T —
install_path T —
env_vars_override J —
member_order I NOT NULL, default 0
is_enabled B NOT NULL, default TRUE
tags J NOT NULL, default '[]'
created_at T NOT NULL, default NOW()
updated_at T NOT NULL, default NOW()

Indexes: idx_cluster_members_component, idx_cluster_members_agent, idx_cluster_members_site

Defined in: migrations/V049__fan_out_clusters.sql

command_executions

Append-only

No UPDATE or DELETE is ever issued against this table. Retention is enforced by partition drops (check_events) or by external archival jobs.

Append-only record of custom-command dispatches and their results.

Primary key: (id)

Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
request_id U NOT NULL, UNIQUE
component_id U NOT NULL
agent_id U —
command_type V NOT NULL, default 'CUSTOM'
exit_code S —
stdout T —
stderr T —
duration_ms I —
status V NOT NULL, default 'DISPATCHED'
dispatched_at T NOT NULL, default NOW()
completed_at T —
created_at T NOT NULL, default NOW()

Indexes: idx_command_executions_component, idx_command_executions_request, idx_command_executions_status, idx_command_executions_rebuild

Defined in: migrations/V014__command_executions.sql

command_input_params

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (command_id) → component_commands(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
command_id U NOT NULL, FK→component_commands
name V NOT NULL
description T —
default_value T —
validation_regex T —
required B NOT NULL, default TRUE
display_order I NOT NULL, default 0
created_at T NOT NULL, default NOW()

Indexes: idx_command_input_params_cmd

Defined in: migrations/V010__variables_groups_params.sql

component_catalog

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (org_id) → organizations(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
org_id U NOT NULL, FK→organizations
type_key V NOT NULL
label V NOT NULL
description T —
icon V NOT NULL, default 'BOX'
color V NOT NULL, default '#455A64'
category V —
default_check_cmd T —
default_start_cmd T —
default_stop_cmd T —
default_env_vars J —
display_order I NOT NULL, default 0
is_builtin B NOT NULL, default FALSE
created_at T NOT NULL, default NOW()
updated_at T NOT NULL, default NOW()

Indexes: idx_component_catalog_org, idx_component_catalog_category

Defined in: migrations/V048__component_catalog.sql

component_commands

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (component_id) → components(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
component_id U NOT NULL, FK→components
name V NOT NULL
command T NOT NULL
description T —
requires_confirmation B NOT NULL, default FALSE
min_permission_level V NOT NULL, default 'OPERATE'
CHECK (MIN_PERMISSION_LEVEL IN ('OPERATE'
created_at T NOT NULL, default NOW()

Indexes: idx_component_commands_component

Defined in: migrations/V004__components_dependencies.sql

component_groups

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (application_id) → applications(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
application_id U NOT NULL, FK→applications
name V NOT NULL
description T —
color V default '#6366F1'
display_order I NOT NULL, default 0
created_at T NOT NULL, default NOW()

Indexes: idx_component_groups_app

Defined in: migrations/V010__variables_groups_params.sql

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (component_id) → components(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
component_id U NOT NULL, FK→components
label V NOT NULL
url T NOT NULL
link_type V NOT NULL, default 'DOCUMENTATION'
CHECK (LINK_TYPE IN ('DOCUMENTATION'
display_order I NOT NULL, default 0
created_at T NOT NULL, default NOW()

Indexes: idx_component_links_component

Defined in: migrations/V010__variables_groups_params.sql

component_log_sources

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (component_id) → components(id)
  • (organization_id) → organizations(id)
  • (created_by) → users(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
component_id U NOT NULL, FK→components
organization_id U NOT NULL, FK→organizations
name V NOT NULL
source_type V NOT NULL
file_path T —
event_log_name V —
event_log_source V —
event_log_level V —
command T —
command_timeout_seconds I default 30
max_lines I default 1000
max_age_hours I default 24
is_sensitive B default FALSE
display_order I default 0
description T —
created_by U FK→users
created_at T NOT NULL, default NOW()
updated_at T NOT NULL, default NOW()

Indexes: idx_log_sources_component, idx_log_sources_org

Defined in: migrations/V043__component_log_sources.sql

components

Individual processes / services that make up an application.

Primary key: (id)

Foreign keys:

  • (application_id) → applications(id)
  • (agent_id) → agents(id)
  • (rebuild_agent_id) → agents(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
application_id U NOT NULL, FK→applications
name V NOT NULL
component_type V NOT NULL
agent_id U FK→agents
start_cmd T —
stop_cmd T —
integrity_check_cmd T —
post_start_check_cmd T —
infra_check_cmd T —
rebuild_cmd T —
rebuild_infra_cmd T —
rebuild_agent_id U FK→agents
rebuild_protected B NOT NULL, default FALSE
start_timeout_seconds I NOT NULL, default 120
stop_timeout_seconds I NOT NULL, default 60
is_optional B NOT NULL, default FALSE
position_x R default 0
position_y R default 0
env_vars J default '{}'
tags J default '[]'
created_at T NOT NULL, default NOW()
updated_at T NOT NULL, default NOW()

Indexes: idx_components_app, idx_components_agent, idx_components_host, idx_components_state, idx_components_referenced_app, idx_components_detected_site, idx_components_passive_status

Defined in: migrations/V004__components_dependencies.sql

config_versions

Append-only

No UPDATE or DELETE is ever issued against this table. Retention is enforced by partition drops (check_events) or by external archival jobs.

Snapshot of applications / components state before/after each config change. Every edit produces a row.

Primary key: (id)

Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
resource_type V NOT NULL
resource_id U NOT NULL
changed_by U NOT NULL
before_snapshot J —
after_snapshot J NOT NULL
created_at T NOT NULL, default NOW()

Indexes: idx_config_versions_resource

Defined in: migrations/V005__event_tables.sql

dependencies

Edges of the DAG between components. strong=true blocks the sequencer; strong=false is informational only.

Primary key: (id)

Foreign keys:

  • (application_id) → applications(id)
  • (from_component_id) → components(id)
  • (to_component_id) → components(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
application_id U NOT NULL, FK→applications
from_component_id U NOT NULL, FK→components
to_component_id U NOT NULL, FK→components
created_at T NOT NULL, default NOW()

Indexes: idx_dependencies_app, idx_dependencies_from, idx_dependencies_to, idx_dependencies_type

Defined in: migrations/V004__components_dependencies.sql

discovery_draft_components

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (draft_id) → discovery_drafts(id)
  • (agent_id) → agents(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
draft_id U NOT NULL, FK→discovery_drafts
suggested_name T NOT NULL
process_name T —
host T —
agent_id U FK→agents
listening_ports I —
component_type T NOT NULL, default 'SERVICE'
metadata J NOT NULL, default '{}'::JSONB

Indexes: idx_draft_components_draft

Defined in: migrations/V021__discovery_estimates_airgap.sql

discovery_draft_dependencies

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (draft_id) → discovery_drafts(id)
  • (from_component) → discovery_draft_components(id)
  • (to_component) → discovery_draft_components(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
draft_id U NOT NULL, FK→discovery_drafts
from_component U NOT NULL, FK→discovery_draft_components
to_component U NOT NULL, FK→discovery_draft_components
inferred_via T NOT NULL, default 'TCP_CONNECTION'

Indexes: idx_draft_deps_draft

Defined in: migrations/V021__discovery_estimates_airgap.sql

discovery_drafts

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
  • (applied_app_id) → applications(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
name T NOT NULL
status T NOT NULL, default 'PENDING'
inferred_at T NOT NULL, default NOW()
applied_app_id U FK→applications
created_at T NOT NULL, default NOW()

Defined in: migrations/V021__discovery_estimates_airgap.sql

discovery_reports

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (agent_id) → agents(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
agent_id U NOT NULL, FK→agents
hostname T NOT NULL
report J NOT NULL
scanned_at T NOT NULL
created_at T NOT NULL, default NOW()

Indexes: idx_discovery_reports_agent, idx_discovery_reports_created

Defined in: migrations/V021__discovery_estimates_airgap.sql

dr_pattern_rules

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
name V NOT NULL
search_pattern V NOT NULL
replace_pattern V NOT NULL
priority I NOT NULL, default 0
is_active B NOT NULL, default TRUE
created_at T NOT NULL, default NOW()

Indexes: idx_dr_pattern_rules_org

Defined in: migrations/V030__binding_profiles.sql

embedded_sessions

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (org_id) → organizations(id)
  • (user_id) → users(id)
  • (simulation_app_id) → applications(id)
Column Type Constraints
id U PK
org_id U NOT NULL, FK→organizations
user_id U NOT NULL, FK→users
reference_app_id U NOT NULL
simulation_app_id U NOT NULL, UNIQUE, FK→applications
token_hash V NOT NULL, UNIQUE
parent_origin T NOT NULL
expires_at B NOT NULL
revoked_at B —
snapshot J NOT NULL
state J NOT NULL
created_at T NOT NULL, default NOW()

Indexes: idx_embedded_sessions_owner

Defined in: migrations/V062__embedded_sessions.sql

enrollment_events

Append-only

No UPDATE or DELETE is ever issued against this table. Retention is enforced by partition drops (check_events) or by external archival jobs.

Append-only audit of certificate issuance.

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
  • (token_id) → enrollment_tokens(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
token_id U FK→enrollment_tokens
event_type V NOT NULL
hostname V —
ip_address V —
agent_id U —
cert_fingerprint V —
cert_cn V —
created_at T NOT NULL, default NOW()

Indexes: idx_enrollment_events_org, idx_enrollment_events_token

Defined in: migrations/V015__enrollment_tokens.sql

enrollment_tokens

Short-lived tokens used by the gateway to issue agent client certs.

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
  • (created_by) → users(id)
  • (revoked_by) → users(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
token_hash V NOT NULL, UNIQUE
token_prefix V NOT NULL
name V NOT NULL
max_uses I —
current_uses I NOT NULL, default 0
expires_at T NOT NULL
scope V NOT NULL, default 'AGENT'
created_by U FK→users
created_at T NOT NULL, default NOW()
revoked_at T —
revoked_by U FK→users

Indexes: idx_enrollment_tokens_org, idx_enrollment_tokens_hash, idx_enrollment_tokens_active, idx_enrollment_tokens_zone, idx_enrollment_tokens_site

Defined in: migrations/V015__enrollment_tokens.sql

failover_health_status

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (profile_id) → binding_profiles(id)
  • (agent_id) → agents(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
profile_id U NOT NULL, FK→binding_profiles
agent_id U NOT NULL, FK→agents
is_reachable B NOT NULL, default TRUE
last_check_at T NOT NULL, default NOW()
unreachable_since T —

Indexes: idx_failover_health_profile, idx_failover_health_unreachable

Defined in: migrations/V030__binding_profiles.sql

gateway_status_events

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
  • (gateway_id) → gateways(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
gateway_id U NOT NULL, FK→gateways
event_type V NOT NULL
previous_state J —
new_state J —
triggered_by V —
created_at T NOT NULL, default NOW()

Indexes: idx_gateway_status_events_gateway, idx_gateway_status_events_org_time

Defined in: migrations/V024__gateway_failover_zones.sql

gateways

TLS-terminating reverse proxies between agents and the backend.

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
name V NOT NULL
zone V NOT NULL
hostname V —
port I default 443
is_active B NOT NULL, default TRUE
last_heartbeat_at T —
created_at T NOT NULL, default NOW()

Indexes: idx_gateways_org, idx_gateways_status, idx_gateways_site, idx_gateways_one_primary_per_zone, idx_gateways_zone_priority, idx_gateways_one_primary_per_site

Defined in: migrations/V002__agents_gateways.sql

hostings

Optional grouping above sites — typically used to label datacenters or cloud providers.

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
name V NOT NULL
description T —
created_at T NOT NULL, default NOW()
updated_at T NOT NULL, default NOW()

Indexes: idx_hostings_org

Defined in: migrations/V046__hostings.sql

knowledge_approvals

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (proposal_id)

Foreign keys:

  • (proposal_id) → knowledge_proposals(id)
  • (reviewed_by) → users(id)
Column Type Constraints
proposal_id U PK, FK→knowledge_proposals
reviewed_by U NOT NULL, FK→users
digest T NOT NULL
created_at T NOT NULL, default NOW()

Indexes: idx_knowledge_reviewers

Defined in: migrations/V060__knowledge_revisions.sql

knowledge_proposals

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (application_id) → applications(id)
  • (created_by) → users(id)
  • (application_id, revision) → knowledge_revisions(application_id, revision)
Column Type Constraints
id U PK
application_id U NOT NULL, FK→applications
revision B NOT NULL
digest T NOT NULL
document T NOT NULL
created_by U NOT NULL, FK→users
created_at T NOT NULL, default NOW()

Indexes: idx_knowledge_proposals, idx_knowledge_proposal_authors

Defined in: migrations/V060__knowledge_revisions.sql

knowledge_publications

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (proposal_id)

Foreign keys:

  • (proposal_id) → knowledge_proposals(id)
  • (application_id) → applications(id)
  • (published_by) → users(id)
Column Type Constraints
proposal_id U PK, FK→knowledge_proposals
application_id U NOT NULL, FK→applications
published_by U NOT NULL, FK→users
result_hash T NOT NULL
created_at T NOT NULL, default NOW()

Indexes: idx_knowledge_publications, idx_knowledge_publishers

Defined in: migrations/V060__knowledge_revisions.sql

knowledge_revisions

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (application_id, revision)

Foreign keys:

  • (application_id) → applications(id)
  • (created_by) → users(id)
Column Type Constraints
application_id U NOT NULL, FK→applications
revision B NOT NULL
request_hash T NOT NULL
document T NOT NULL
created_by U NOT NULL, FK→users
created_at T NOT NULL, default NOW()

Indexes: idx_knowledge_created, idx_knowledge_authors

Defined in: migrations/V060__knowledge_revisions.sql

log_access_audit

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
  • (user_id) → users(id)
  • (component_id) → components(id)
  • (log_source_id) → component_log_sources(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
user_id U FK→users
component_id U FK→components
log_source_id U FK→component_log_sources
source_type V NOT NULL
source_name V —
lines_requested I —
filter_applied T —
time_range_hours I —
accessed_at T NOT NULL, default NOW()
ip_address I —
user_agent T —

Indexes: idx_log_audit_component, idx_log_audit_user, idx_log_audit_time

Defined in: migrations/V043__component_log_sources.sql

manual_task_validations

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (component_id) → components(id)
  • (application_id) → applications(id)
  • (started_by) → users(id)
  • (validated_by) → users(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
component_id U NOT NULL, FK→components
application_id U NOT NULL, FK→applications
started_at T NOT NULL, default NOW()
started_by U FK→users
validated_at T —
validated_by U FK→users
status T NOT NULL, default 'PENDING'
CHECK (STATUS IN ('PENDING'
comment T —
duration_seconds I —

Indexes: idx_manual_task_validations_component, idx_manual_task_validations_pending

Defined in: migrations/V054__manual_task_components.sql

notification_preferences

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (user_id) → users(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
user_id U NOT NULL, FK→users
channel V NOT NULL
event_types J NOT NULL, default '["STATE_CHANGE"
config J default '{}'
is_enabled B NOT NULL, default TRUE
created_at T NOT NULL, default NOW()

Indexes: idx_notifications_user

Defined in: migrations/V007__api_keys_notifications.sql

operation_locks

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (app_id)

Foreign keys:

  • (app_id) → applications(id)
  • (user_id) → users(id)
Column Type Constraints
app_id U PK, FK→applications
operation V NOT NULL
user_id U NOT NULL, FK→users
started_at T NOT NULL, default NOW()
last_heartbeat T NOT NULL, default NOW()
status V NOT NULL, default 'RUNNING' CHECK (STATUS IN ('RUNNING'
backend_instance V —
details J default '{}'

Indexes: idx_operation_locks_heartbeat, idx_operation_locks_status

Defined in: migrations/V038__operation_locks.sql

operation_schedule_executions

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (schedule_id) → operation_schedules(id)
  • (action_log_id) → action_log(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
schedule_id U NOT NULL, FK→operation_schedules
action_log_id U FK→action_log
executed_at T NOT NULL, default NOW()
status V NOT NULL
message T —
duration_ms I —

Indexes: idx_operation_schedule_executions_schedule

Defined in: migrations/V042__operation_schedules.sql

operation_schedules

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
  • (application_id) → applications(id)
  • (component_id) → components(id)
  • (last_action_log_id) → action_log(id)
  • (created_by) → users(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
application_id U FK→applications
component_id U FK→components
name V NOT NULL
description T —
operation V NOT NULL
cron_expression V NOT NULL
timezone V NOT NULL, default 'EUROPE/PARIS'
is_enabled B NOT NULL, default TRUE
last_run_at T —
next_run_at T —
last_run_status V —
last_run_message T —
last_action_log_id U FK→action_log
created_by U FK→users
created_at T NOT NULL, default NOW()
updated_at T NOT NULL, default NOW()

Indexes: idx_operation_schedules_next_run, idx_operation_schedules_app, idx_operation_schedules_component, idx_operation_schedules_org

Defined in: migrations/V042__operation_schedules.sql

organizations

Tenant boundary. Everything else fans out from here. One row per customer.

Primary key: (id)

Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
name V NOT NULL, UNIQUE
slug V NOT NULL, UNIQUE
settings J default '{}'
created_at T NOT NULL, default NOW()
updated_at T NOT NULL, default NOW()

Defined in: migrations/V001__organizations_users.sql

pki_maintenance_installations

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (organization_id, id)

Foreign keys:

  • (organization_id) → organizations(id)
Column Type Constraints
organization_id U NOT NULL, FK→organizations
id U NOT NULL
worker_id U NOT NULL
name T NOT NULL
identities J NOT NULL
last_seen B NOT NULL

Defined in: migrations/V065__pki_maintenance.sql

pki_maintenance_requests

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (organization_id, installation_id) → pki_maintenance_installations(organization_id, id)
Column Type Constraints
id U PK
organization_id U NOT NULL
installation_id U NOT NULL
identity_id T NOT NULL
status T NOT NULL
requested_by U NOT NULL
created_at B NOT NULL
claimed_at B —
finished_at B —
message T —

Indexes: idx_pki_maintenance_active, idx_pki_maintenance_history

Defined in: migrations/V065__pki_maintenance.sql

portfolio_generation_applications

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (organization_id, source, application_key)

Foreign keys:

  • (organization_id) → organizations(id)
  • (published_application_id) → applications(id)
Column Type Constraints
organization_id U NOT NULL, FK→organizations
source T NOT NULL
application_key T NOT NULL
sha T NOT NULL
fingerprint T NOT NULL
is_present B NOT NULL
observed_at B NOT NULL
published_application_id U FK→applications
published_sha T —
published_fingerprint T —
published_at B —

Indexes: idx_portfolio_generation_published_app

Defined in: migrations/V064__portfolio_generation.sql

portfolio_generation_jobs

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (organization_id, source, application_key) → portfolio_generation_applications(organization_id, source, application_key)
Column Type Constraints
id U PK
organization_id U NOT NULL
source T NOT NULL
application_key T NOT NULL
sha T NOT NULL
fingerprint T NOT NULL
status T NOT NULL
attempts I NOT NULL, default 0 CHECK (ATTEMPTS >= 0)
available_at B NOT NULL
lease_token U —
lease_until B —
error T —
candidate J —
created_at B NOT NULL
updated_at B NOT NULL

Indexes: idx_portfolio_generation_due

Defined in: migrations/V064__portfolio_generation.sql

portfolio_source_refreshes

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (organization_id, source)

Foreign keys:

  • (organization_id) → organizations(id)
Column Type Constraints
organization_id U NOT NULL, FK→organizations
source T NOT NULL
status T NOT NULL
requested_at B NOT NULL
updated_at B NOT NULL
last_success_at B —
lease_token U —
lease_until B —
error T —

Defined in: migrations/V064__portfolio_generation.sql

rate_limit_counters

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (key)

Column Type Constraints
key T PK
count I NOT NULL, default 1
window_start T NOT NULL, default NOW()

Indexes: idx_rate_limit_window

Defined in: migrations/V017__remove_redis_dependency.sql

rebuild_operations

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (application_id) → applications(id)
Column Type Constraints
id U PK
application_id U NOT NULL, FK→applications
state J NOT NULL
updated_at T NOT NULL, default NOW()

Indexes: idx_rebuild_operations_app

Defined in: migrations/V058__verified_recovery.sql

revoked_certificates

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
  • (agent_id) → agents(id)
  • (revoked_by) → users(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
fingerprint V NOT NULL
cn V —
agent_id U FK→agents
gateway_id U —
reason T NOT NULL
revoked_by U NOT NULL, FK→users
revoked_at T NOT NULL, default NOW()

Indexes: idx_revoked_certs_org, idx_revoked_certs_fingerprint, idx_revoked_certificates_fingerprint_unique

Defined in: migrations/V022__platform_admin_sites_gateways.sql

revoked_tokens

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (fingerprint)

Column Type Constraints
fingerprint T PK
revoked_at T NOT NULL, default NOW()
expires_at T NOT NULL

Indexes: idx_revoked_tokens_expires

Defined in: migrations/V017__remove_redis_dependency.sql

rotation_progress

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
  • (initiated_by) → users(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
rotation_id U NOT NULL, UNIQUE
total_agents I NOT NULL, default 0
total_gateways I NOT NULL, default 0
migrated_agents I NOT NULL, default 0
migrated_gateways I NOT NULL, default 0
failed_agents I NOT NULL, default 0
failed_gateways I NOT NULL, default 0
started_at T NOT NULL, default NOW()
completed_at T —
finalized_at T —
status V NOT NULL, default 'IN_PROGRESS'
initiated_by U FK→users
grace_period_secs I NOT NULL, default 3600

Indexes: idx_rotation_progress_org

Defined in: migrations/V025__certificate_rotation.sql

saml_group_mappings

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (team_id) → teams(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
saml_group V NOT NULL
team_id U NOT NULL, FK→teams
default_role V NOT NULL, default 'VIEWER'
CHECK (DEFAULT_ROLE IN ('ADMIN'
created_at T NOT NULL, default NOW()

Indexes: idx_saml_group_mappings_group, idx_saml_group_mappings_team

Defined in: migrations/V009__saml_oidc.sql

saved_views

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (user_id) → users(id)
  • (application_id) → applications(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
user_id U NOT NULL, FK→users
application_id U NOT NULL, FK→applications
name V NOT NULL
view_config J NOT NULL, default '{}'
created_at T NOT NULL, default NOW()

Indexes: idx_saved_views_user

Defined in: migrations/V006__teams_permissions.sql

scheduled_snapshots

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (schedule_id) → snapshot_schedules(id)
  • (organization_id) → organizations(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
schedule_id U NOT NULL, FK→snapshot_schedules
organization_id U NOT NULL, FK→organizations
agent_ids U NOT NULL, default '{}'
report_ids U NOT NULL, default '{}'
captured_at T NOT NULL, default NOW()
expires_at T —
correlation_result J —

Indexes: idx_scheduled_snapshots_schedule, idx_scheduled_snapshots_org, idx_scheduled_snapshots_captured, idx_scheduled_snapshots_expires

Defined in: migrations/V033__snapshot_schedules.sql

site_mock_agents

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (site_id, profile)

Foreign keys:

  • (site_id) → sites(id)
  • (agent_id) → agents(id)
  • (updated_by) → users(id)
Column Type Constraints
site_id U NOT NULL, FK→sites
profile V NOT NULL
agent_id U NOT NULL, UNIQUE, FK→agents
updated_by U NOT NULL, FK→users
updated_at T NOT NULL, default NOW()

Indexes: idx_site_mock_agents_updated_by

Defined in: migrations/V061__site_mock_agents.sql

site_overrides

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (component_id) → components(id)
  • (site_id) → sites(id)
  • (agent_id_override) → agents(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
component_id U NOT NULL, FK→components
site_id U NOT NULL, FK→sites
agent_id_override U FK→agents
start_cmd_override T —
stop_cmd_override T —
rebuild_cmd_override T —
env_vars_override J —
created_at T NOT NULL, default NOW()

Indexes: idx_site_overrides_component

Defined in: migrations/V004__components_dependencies.sql

sites

Physical or logical location (datacenter, cloud region). Components are bound to one site at a time; switchover moves them.

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
name V NOT NULL
code V NOT NULL
site_type V NOT NULL, default 'PRIMARY'
CHECK (SITE_TYPE IN ('PRIMARY'
location V —
is_active B NOT NULL, default TRUE
created_at T NOT NULL, default NOW()

Indexes: idx_sites_org, idx_sites_hosting

Defined in: migrations/V003__sites_applications.sql

snapshot_schedules

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
  • (created_by) → users(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
name V NOT NULL
agent_ids U NOT NULL, default '{}'
frequency V NOT NULL, default 'DAILY'
cron_expression V —
enabled B NOT NULL, default TRUE
retention_days I NOT NULL, default 30
last_run_at T —
next_run_at T —
created_at T NOT NULL, default NOW()
updated_at T NOT NULL, default NOW()
created_by U FK→users

Indexes: idx_snapshot_schedules_org, idx_snapshot_schedules_enabled, idx_snapshot_schedules_next_run

Defined in: migrations/V033__snapshot_schedules.sql

state_transitions

Append-only

No UPDATE or DELETE is ever issued against this table. Retention is enforced by partition drops (check_events) or by external archival jobs.

Append-only record of every FSM transition (every component, every state change).

Primary key: (id)

Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
component_id U NOT NULL
from_state V NOT NULL
to_state V NOT NULL
trigger V NOT NULL, default 'CHECK'
details J default '{}'
created_at T NOT NULL, default NOW()

Indexes: idx_state_transitions_component, idx_state_transitions_state, idx_state_transitions_member

Defined in: migrations/V005__event_tables.sql

switchover_log

Append-only

No UPDATE or DELETE is ever issued against this table. Retention is enforced by partition drops (check_events) or by external archival jobs.

Append-only record of every DR switchover phase, with start/end timestamps.

Primary key: (id)

Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
switchover_id U NOT NULL
application_id U NOT NULL
phase V NOT NULL
status V NOT NULL, default 'IN_PROGRESS'
CHECK (STATUS IN ('IN_PROGRESS'
details J default '{}'
created_at T NOT NULL, default NOW()

Indexes: idx_switchover_log_app, idx_switchover_log_switchover

Defined in: migrations/V005__event_tables.sql

team_members

Many-to-many between users and teams.

Primary key: (id)

Foreign keys:

  • (team_id) → teams(id)
  • (user_id) → users(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
team_id U NOT NULL, FK→teams
user_id U NOT NULL, FK→users
role V NOT NULL, default 'MEMBER'
CHECK (ROLE IN ('LEAD'
joined_at T NOT NULL, default NOW()

Indexes: idx_team_members_team, idx_team_members_user

Defined in: migrations/V006__teams_permissions.sql

teams

Groups of users for shared permission grants.

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
name V NOT NULL
description T —
created_at T NOT NULL, default NOW()
updated_at T NOT NULL, default NOW()

Indexes: idx_teams_org

Defined in: migrations/V006__teams_permissions.sql

user_favorites

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (user_id) → users(id)
  • (application_id) → applications(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
user_id U NOT NULL, FK→users
application_id U NOT NULL, FK→applications
created_at T NOT NULL, default NOW()

Indexes: idx_user_favorites_user

Defined in: migrations/V006__teams_permissions.sql

users

Local user accounts plus SSO-linked identities (external_id).

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
external_id V NOT NULL
email V NOT NULL
display_name V NOT NULL
role V NOT NULL, default 'VIEWER'
CHECK (ROLE IN ('ADMIN'
is_active B NOT NULL, default TRUE
last_login_at T —
created_at T NOT NULL, default NOW()

Indexes: idx_users_org, idx_users_email, idx_users_oidc_sub, idx_users_saml_name_id, idx_users_email_auth

Defined in: migrations/V001__organizations_users.sql

webhook_deliveries

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (webhook_id) → webhook_endpoints(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
webhook_id U NOT NULL, FK→webhook_endpoints
event_type V NOT NULL
payload J NOT NULL
status_code I —
response_body T —
attempt I NOT NULL, default 1
delivered_at T NOT NULL, default NOW()

Indexes: idx_webhook_deliveries_webhook

Defined in: migrations/V016__fsm_cache_notifications_locks.sql

webhook_endpoints

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
  • (application_id) → applications(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
application_id U FK→applications
name V NOT NULL
url T NOT NULL
secret V —
event_types J NOT NULL, default '["STATE_CHANGE"
headers J default '{}'
is_enabled B NOT NULL, default TRUE
retry_count I NOT NULL, default 3
last_triggered_at T —
last_status_code I —
created_at T NOT NULL, default NOW()
updated_at T NOT NULL, default NOW()

Indexes: idx_webhook_endpoints_org, idx_webhook_endpoints_app

Defined in: migrations/V016__fsm_cache_notifications_locks.sql

workspace_members

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (id)

Foreign keys:

  • (workspace_id) → workspaces(id)
  • (user_id) → users(id)
  • (team_id) → teams(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
workspace_id U NOT NULL, FK→workspaces
user_id U FK→users
team_id U FK→teams
role V NOT NULL, default 'MEMBER'
CHECK (ROLE IN ('ADMIN'
created_at T NOT NULL, default NOW()

Indexes: idx_workspace_members_workspace, idx_workspace_members_user, idx_workspace_members_team

Defined in: migrations/V011__agent_ip_workspace_access_heartbeat.sql

workspace_sites

Many-to-many: workspaces ↔ sites.

Primary key: (workspace_id, site_id)

Foreign keys:

  • (workspace_id) → workspaces(id)
  • (site_id) → sites(id)
Column Type Constraints
workspace_id U NOT NULL, FK→workspaces
site_id U NOT NULL, FK→sites
created_at T NOT NULL, default NOW()

Indexes: idx_workspace_sites_workspace, idx_workspace_sites_site

Defined in: migrations/V011__agent_ip_workspace_access_heartbeat.sql

workspaces

Optional access control above sites. Restricts which sites a user can see.

Primary key: (id)

Foreign keys:

  • (organization_id) → organizations(id)
Column Type Constraints
id U PK, default GEN_RANDOM_UUID()
organization_id U NOT NULL, FK→organizations
name V NOT NULL
description T —
created_at T NOT NULL, default NOW()
updated_at T NOT NULL, default NOW()

Defined in: migrations/V006__teams_permissions.sql

x4b_external_sessions

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (token_hash)

Foreign keys:

  • (user_id) → users(id)
  • (organization_id, user_id, module_key) → x4b_module_grants(organization_id, user_id, module_key)
Column Type Constraints
token_hash T PK
organization_id U NOT NULL
user_id U NOT NULL, FK→users
module_key T NOT NULL
parent_fingerprint T NOT NULL
expires_at B NOT NULL

Indexes: idx_x4b_external_sessions_expires

Defined in: migrations/V066__x4b_access.sql

x4b_handoffs

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (code_hash)

Foreign keys:

  • (user_id) → users(id)
  • (organization_id, user_id, module_key) → x4b_module_grants(organization_id, user_id, module_key)
Column Type Constraints
code_hash T PK
organization_id U NOT NULL
user_id U NOT NULL, FK→users
module_key T NOT NULL
challenge T NOT NULL
parent_fingerprint T NOT NULL
parent_expires_at B NOT NULL
expires_at B NOT NULL

Indexes: idx_x4b_handoffs_expires

Defined in: migrations/V066__x4b_access.sql

x4b_knowledge_settings

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (organization_id)

Foreign keys:

  • (organization_id) → organizations(id)
Column Type Constraints
organization_id U PK, FK→organizations
url T NOT NULL
source T NOT NULL
refresh_seconds B NOT NULL
encrypted_token B NOT NULL
revision U NOT NULL

Defined in: migrations/V068__knowledge_settings.sql

x4b_module_grants

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (organization_id, user_id, module_key)

Foreign keys:

  • (user_id) → users(id)
  • (organization_id, module_key) → x4b_modules(organization_id, module_key)
Column Type Constraints
organization_id U NOT NULL
user_id U NOT NULL, FK→users
module_key T NOT NULL

Defined in: migrations/V066__x4b_access.sql

x4b_modules

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (organization_id, module_key)

Foreign keys:

  • (organization_id) → organizations(id)
Column Type Constraints
organization_id U NOT NULL, FK→organizations
module_key T NOT NULL
name T NOT NULL
entry_url T NOT NULL
callback_url T NOT NULL
secret_hash T NOT NULL, UNIQUE

Defined in: migrations/V066__x4b_access.sql

x4b_user_policies

(purpose: add to scripts/docs/gen_database_schema.py::TABLE_PURPOSE)

Primary key: (organization_id, user_id)

Foreign keys:

  • (organization_id) → organizations(id)
  • (user_id) → users(id)
Column Type Constraints
organization_id U NOT NULL, FK→organizations
user_id U NOT NULL, FK→users
allow_appcontrol B NOT NULL

Defined in: migrations/V066__x4b_access.sql

Notes

  • check_events is partitioned by day (PostgreSQL) — see migration V005 and the partition-creation logic in the backend. Retention drops whole partitions rather than deleting rows.
  • UUIDs are UUID (native) on PostgreSQL and TEXT (with DbUuid codec) on SQLite. Application code uses uuid::Uuid; the codec is transparent.
  • JSONB columns are JSONB on PostgreSQL and TEXT (with DbJson codec) on SQLite.
  • See Backup & restore for pg_dump recipes that respect the partition layout.