Database Schema

IAM Tables

erDiagram
workspace_user }o--|| workspace : workspace_id
workspace_user }o--|| users : user_id

workspace ||--o{ service_api_key : workspace_id

users ||--o{ user_api_key: user_id

workspace {
    id uuid PK
    name varchar
    archived bool
    created_at timestamp
    updated_at timestamp
    deleted_at timestamptz
    deletion_reason varchar
}

users {
    id uuid PK
    username varchar
    password_hash varchar
    display_name varchar
    status user_status
    is_sysadmin bool
    is_admin bool
    deleted_at timestamptz
    deletion_reason varchar
}

workspace_user {
    id uuid PK
    user_id uuid FK
    workspace_id uuid FK
    workspace_role workspace_role
    deleted_at timestamptz
    deletion_reason varchar
}

service_api_key {
    id uuid PK
    workspace_id uuid FK
    name varchar
    key_hash varchar
    key_preview varchar(16)
    permissions api_key_permission
    expires_at timestamptz(6)
    deleted_at timestamptz
    deletion_reason varchar
}

user_api_key {
    id uuid PK
    user_id uuid FK
    name varchar
    key_hash varchar
    key_preview varchar
    expires_at timestamptz(6)
    deleted_at timestamptz
    deletion_reason varchar
}

Data Tables

erDiagram

workspace ||--o{ system_event : workspace_id

system_event ||--|{ data : system_event_id
subsystem_event ||--|{ data : subsystem_event_id
component_event ||--|{ data: component_event_id
subcomponent_event ||--|{ data: subcomponent_event_id

data ||--|{ runtime : ""
data ||--o{ io : ""
data ||--o{ metadata : ""

api_access_audit_logs ||--o| user_query : api_access_audit_log_id
workspace ||--o{ user_query : allowed_workspace_ids

system_event {
    id uuid PK
    workspace_id uuid FK
    name varchar
    parameters json
    version varchar
    environment varchar
}

subsystem_event {
    id uuid PK
    system_event_id uuid FK
    name varchar
    parameters json
    version varchar
    environment varchar
}

component_event {
    id uuid PK
    system_event_id uuid FK
    name varchar
    parameters json
    version varchar
    environment varchar
}

subcomponent_event {
    id uuid PK
    system_event_id uuid FK
    name varchar
    parameters json
    version varchar
    environment varchar
}

runtime {
    id uuid PK
    system_event_id uuid FK
    subsystem_event_id uuid FK
    component_event_id uuid FK
    subcomponent_event_id uuid FK
    start_time timestamp
    end_time timestamp
    error_type varchar
    error_content varchar
}

io {
    id uuid PK
    system_event_id uuid FK
    subsystem_event_id uuid FK
    component_event_id uuid FK
    subcomponent_event_id uuid FK
    field_name varchar
    field_value_str varchar
    field_value_int int8
    field_value_float float8
    field_value_bool bool
    field_value_json json
    field_value_type field_value_type
}

metadata {
    id uuid PK
    system_event_id uuid FK
    subsystem_event_id uuid FK
    component_event_id uuid FK
    subcomponent_event_id uuid FK
    field_name varchar
    field_value varchar
}

user_query {
    id uuid PK
    api_access_audit_log_id uuid FK
    allowed_workspace_ids uuid[] FK
    query_type query_type
    access_reason access_reason
    query_access_details varchar
    query_text varchar
    operation_name varchar
    variables jsonb
    query_metadata jsonb
    query_start_time timestamptz not null
    failure_details jsonb
}

Audit Tables

erDiagram

api_access_audit_logs ||--o{ api_auth_audit_logs : api_access_audit_log_id
api_access_audit_logs ||--o{ iam_audit_logs : api_access_audit_log_id
api_access_audit_logs ||--o{ record_access_audit_logs : api_access_audit_log_id

service_api_key ||--o{ api_auth_audit_logs : service_api_key_id
user_api_key ||--o{ api_auth_audit_logs : user_api_key_id
users ||--o{ api_auth_audit_logs : user_id

record_access_audit_logs ||--|| user_query : user_query_id
user_query ||--|| user_query_results : user_query_id

api_access_audit_logs {
    id uuid PK
    created_at timestamptz
    source varchar
    request_id uuid
    ip_address inet
    archive_status archive_status
}

api_auth_audit_logs {
    id uuid PK
    api_access_audit_log_id uuid FK
    service_api_key_id uuid FK
    user_api_key_id uuid FK
    user_id uuid FK
    auth_method auth_method
    auth_payload_hash bytea
    success boolean
    failure_details jsonb
}

iam_audit_logs {
    id uuid PK
    api_access_audit_log_id uuid FK
    resource_id uuid
    table_name varchar
    operation_type operation_type
    old_state bytea
    new_state bytea
    failure_reason varchar
    query_metadata jsonb
}

user_query_results {
    id uuid PK
    user_query_id uuid FK
    failure_details jsonb
    query_end_time timestamptz
    query_status query_status
    resource_usage jsonb
}

record_access_audit_logs {
    id uuid PK
    api_access_audit_log_id uuid FK
    user_query_id uuid FK
    operation_type operation_type
    schema_name name
    table_name name
    entity_ids uuid[]
}