Database schema reference¶
Auto-generated from
migrations/V*.sql. Runscripts/docs/regen.pyto 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_logcheck_eventscommand_executionsconfig_versionsenrollment_eventsstate_transitionsswitchover_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
app_share_links¶
(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
component_links¶
(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_eventsis partitioned by day (PostgreSQL) — see migrationV005and the partition-creation logic in the backend. Retention drops whole partitions rather than deleting rows.- UUIDs are
UUID(native) on PostgreSQL andTEXT(withDbUuidcodec) on SQLite. Application code usesuuid::Uuid; the codec is transparent. - JSONB columns are
JSONBon PostgreSQL andTEXT(withDbJsoncodec) on SQLite. - See Backup & restore for
pg_dumprecipes that respect the partition layout.