Skip to content

Architecture: Database

Defaults

  • SQLite default for local runs (app.db or configured path).
  • Alembic migrations under backend/alembic/versions/.

Main model areas

  • identities/users
  • storage endpoints
  • S3 accounts/users/connections
  • audit and operational data
  • bucket migration and health/cost data

Schema diagrams

The database diagrams are generated from app.db.Base.metadata. The Mermaid views are rendered directly by the Developer Guide, while the complete ChartDB diagram contains every table, column, constraint, index and foreign-key relationship in one interactive model.

Regenerate the Mermaid and ChartDB sources after a model change:

backend/.venv/bin/python backend/scripts/generate_database_schema_docs.py

Use --check to verify that committed generated files still match the current SQLAlchemy metadata without modifying files. Every metadata table must belong to exactly one documented domain, so a new table cannot silently disappear from the diagrams.

Overview

flowchart LR
    %% Generated from backend/app/db SQLAlchemy models via app.db.Base.metadata.
    identity_ui_access["Identity and UI access<br/>21 tables"]
    storage_s3_execution["Storage and S3 execution<br/>15 tables"]
    tags_classification["Tags and classification<br/>6 tables"]
    portal["Portal<br/>6 tables"]
    operations_audit["Operations and audit<br/>11 tables"]
    observability_quota_cost["Observability quota and cost<br/>11 tables"]
    observability_quota_cost -->|"20 FK"| storage_s3_execution
    operations_audit -->|"4 FK"| identity_ui_access
    operations_audit -->|"3 FK"| storage_s3_execution
    portal -->|"8 FK"| identity_ui_access
    portal -->|"4 FK"| storage_s3_execution
    storage_s3_execution -->|"9 FK"| identity_ui_access
    tags_classification -->|"1 FK"| identity_ui_access
    tags_classification -->|"5 FK"| storage_s3_execution

Detailed Mermaid views

The detailed views include all columns in their local domain. Cross-domain foreign keys keep a minimal external entity so the relationship remains visible without duplicating another domain's full table definition.

Identity and UI access 
erDiagram
    %% Generated from backend/app/db SQLAlchemy models via app.db.Base.metadata.
    %% Domain: Identity and UI access.
    %% PK=primary key, FK=foreign key, UK=single-column unique, NN=not nullable.
    users {
        integer id PK "NN"
        varchar email UK "NN"
        varchar full_name
        varchar picture_url
        varchar avatar_preference "NN; default=auto"
        blob avatar_image
        varchar avatar_content_type
        datetime avatar_updated_at
        varchar hashed_password
        integer auth_version "NN; default=1"
        boolean is_active
        varchar role "NN; default=ui_user"
        boolean can_access_ceph_admin "NN; default=0"
        boolean can_access_storage_ops "NN; default=0"
        boolean can_access_manager_bucket_compare "NN; default=0"
        boolean can_access_manager_bucket_integrity_check "NN; default=0"
        boolean can_access_manager_bucket_migration "NN; default=0"
        boolean can_access_manager_feature_rules "NN; default=0"
        boolean can_access_manager_bucket_purge "NN; default=0"
        boolean can_create_manual_private_connections "NN; default=0"
        boolean can_provision_managed_private_connections "NN; default=0"
        boolean browser_advanced_features_enabled "NN; default=0"
        datetime created_at
        datetime updated_at "NN"
        datetime last_login_at
        varchar ui_language
        boolean quota_alerts_enabled "NN; default=1"
        boolean quota_alerts_global_watch "NN; default=0"
        text ui_preferences_json "NN; default={}"
    }
    ui_groups {
        integer id PK "NN"
        varchar name UK "NN"
        text description
        varchar avatar_source "NN; default=initials"
        varchar avatar_icon
        blob avatar_image
        varchar avatar_content_type
        datetime avatar_updated_at
        boolean can_access_ceph_admin "NN; default=0"
        boolean can_access_storage_ops "NN; default=0"
        boolean can_access_manager_bucket_compare "NN; default=0"
        boolean can_access_manager_bucket_integrity_check "NN; default=0"
        boolean can_access_manager_bucket_migration "NN; default=0"
        boolean can_access_manager_feature_rules "NN; default=0"
        boolean can_access_manager_bucket_purge "NN; default=0"
        boolean can_create_manual_private_connections "NN; default=0"
        boolean can_provision_managed_private_connections "NN; default=0"
        boolean browser_advanced_features_enabled "NN; default=0"
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    user_ui_groups {
        integer id PK "NN"
        integer user_id FK "NN"
        integer group_id FK "NN"
        datetime created_at "NN"
    }
    api_tokens {
        varchar id PK "NN"
        varchar jti UK "NN"
        varchar token_hash UK "NN"
        integer user_id FK "NN"
        varchar name "NN"
        text scopes_json "NN; default=[]"
        integer auth_version "NN; default=1"
        datetime created_at "NN"
        datetime last_used_at
        datetime expires_at "NN"
        datetime revoked_at
    }
    auth_sessions {
        varchar id PK "NN"
        integer user_id FK
        varchar s3_session_id FK
        varchar principal_type "NN"
        varchar auth_type "NN"
        integer auth_version "NN; default=1"
        datetime created_at "NN"
        datetime last_activity_at "NN"
        datetime idle_expires_at "NN"
        datetime absolute_expires_at "NN"
        datetime mfa_verified_at
        varchar ip_address
        text user_agent
        varchar csrf_token_hash "NN"
        datetime revoked_at
        varchar revoke_reason
    }
    refresh_tokens {
        varchar id PK "NN"
        varchar family_id "NN"
        varchar auth_session_id FK "NN"
        varchar parent_id FK
        varchar token_hash UK "NN"
        datetime created_at "NN"
        datetime expires_at "NN"
        datetime used_at
        varchar replaced_by_id FK
        datetime revoked_at
        varchar revoke_reason
    }
    external_identities {
        varchar id PK "NN"
        integer user_id FK "NN"
        varchar provider_type "NN"
        varchar provider_id "NN"
        varchar subject "NN"
        varchar email
        boolean email_verified "NN; default=0"
        datetime created_at "NN"
        datetime last_login_at
        datetime revoked_at
        varchar link_source "NN; default=jit"
    }
    external_identity_link_requests {
        varchar id PK "NN"
        integer user_id FK "NN"
        varchar provider_type "NN"
        varchar provider_id "NN"
        varchar subject "NN"
        varchar email "NN"
        varchar display_name
        varchar picture_url
        varchar status "NN; default=pending"
        datetime created_at "NN"
        datetime expires_at "NN"
        datetime decided_at
        integer decided_by_user_id FK
        varchar decision_reason
        varchar decision_source
    }
    webauthn_credentials {
        varchar id PK "NN"
        integer user_id FK "NN"
        varchar credential_id UK "NN"
        text public_key "NN"
        integer sign_count "NN; default=0"
        text transports_json "NN; default=[]"
        varchar name "NN"
        datetime created_at "NN"
        datetime last_used_at
        datetime revoked_at
    }
    auth_challenges {
        varchar id PK "NN"
        integer user_id FK "NN"
        varchar binding_sid
        varchar purpose "NN"
        varchar challenge_hash UK "NN"
        text payload_json "NN; default={}"
        datetime created_at "NN"
        datetime expires_at "NN"
        datetime consumed_at
    }
    recovery_codes {
        varchar id PK "NN"
        integer user_id FK "NN"
        varchar code_hash UK "NN"
        datetime created_at "NN"
        datetime consumed_at
    }
    auth_rate_limits {
        varchar id PK "NN"
        varchar bucket_key "NN"
        datetime window_started_at "NN"
        integer attempts "NN; default=0"
        datetime updated_at "NN"
    }
    first_admin_bootstrap {
        integer id PK "NN"
        varchar_64 token_digest
        datetime expires_at
        datetime consumed_at
        integer created_user_id FK
    }
    s3_sessions {
        varchar id PK "NN"
        varchar access_key_enc
        varchar secret_key_enc
        varchar access_key_hash "NN"
        varchar actor_type "NN"
        varchar role "NN"
        varchar account_id
        varchar account_name
        varchar user_uid
        text capabilities "NN"
        datetime created_at "NN"
        datetime last_used_at "NN"
        datetime idle_expires_at "NN"
        datetime absolute_expires_at "NN"
        datetime revoked_at
        varchar revoke_reason
    }
    oidc_providers {
        integer id PK "NN"
        varchar provider_id UK "NN"
        varchar display_name "NN"
        varchar discovery_url "NN"
        varchar client_id "NN"
        varchar client_secret
        varchar redirect_uri "NN"
        text scopes_json "NN; default=['openid','email','profile']"
        varchar prompt
        boolean enabled "NN"
        varchar icon_url
        boolean use_pkce "NN"
        boolean use_nonce "NN"
        varchar linking_policy "NN; default=manual"
        text trusted_email_domains_json "NN; default=[]"
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    oidc_login_states {
        varchar state PK "NN"
        varchar provider "NN"
        varchar code_verifier "NN"
        varchar nonce
        varchar redirect_path
        varchar redirect_uri
        datetime created_at "NN"
    }
    oidc_authorization_codes {
        varchar id PK "NN"
        varchar provider "NN"
        varchar code_hash UK "NN"
        datetime created_at "NN"
        datetime expires_at "NN"
    }
    ldap_providers {
        integer id PK "NN"
        varchar provider_id UK "NN"
        varchar display_name "NN"
        varchar url "NN"
        varchar bind_dn
        varchar bind_password
        varchar user_base_dn "NN"
        text user_filter "NN"
        varchar email_attribute "NN"
        varchar name_attribute
        varchar subject_attribute
        boolean start_tls "NN"
        boolean tls_verify "NN"
        varchar tls_ca_file
        boolean allow_legacy_tls "NN"
        float timeout_seconds "NN"
        boolean enabled "NN"
        boolean allow_insecure "NN"
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    browser_favorites {
        varchar_36 id PK "NN"
        integer user_id FK "NN"
        varchar_24 surface "NN"
        varchar_120 name "NN"
        varchar_24 workspace "NN"
        varchar_256 context "NN"
        varchar_255 bucket "NN"
        varchar_1024 prefix "NN"
        integer revision "NN"
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    onboarding_preferences {
        integer user_id PK, FK "NN"
        boolean dismissed "NN"
        datetime initial_setup_completed_at
        datetime updated_at "NN"
    }
    onboarding_journeys {
        varchar_36 id PK "NN"
        integer user_id FK "NN"
        integer revision "NN"
        text draft_json "NN"
        text resources_json "NN"
        varchar_40 pending_step
        datetime configured_at
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    user_ui_groups }o--|| users : "user_id to id"
    user_ui_groups }o--|| ui_groups : "group_id to id"
    api_tokens }o--|| users : "user_id to id"
    auth_sessions }o--o| users : "user_id to id; ON DELETE CASCADE"
    auth_sessions }o--o| s3_sessions : "s3_session_id to id; ON DELETE CASCADE"
    refresh_tokens }o--|| auth_sessions : "auth_session_id to id; ON DELETE CASCADE"
    refresh_tokens }o--o| refresh_tokens : "parent_id to id; ON DELETE SET NULL"
    refresh_tokens }o--o| refresh_tokens : "replaced_by_id to id; ON DELETE SET NULL"
    external_identities }o--|| users : "user_id to id; ON DELETE CASCADE"
    external_identity_link_requests }o--|| users : "user_id to id; ON DELETE CASCADE"
    external_identity_link_requests }o--o| users : "decided_by_user_id to id; ON DELETE SET NULL"
    webauthn_credentials }o--|| users : "user_id to id; ON DELETE CASCADE"
    auth_challenges }o--|| users : "user_id to id; ON DELETE CASCADE"
    recovery_codes }o--|| users : "user_id to id; ON DELETE CASCADE"
    first_admin_bootstrap }o--o| users : "created_user_id to id; ON DELETE SET NULL"
    browser_favorites }o--|| users : "user_id to id; ON DELETE CASCADE"
    onboarding_preferences o|--|| users : "user_id to id; ON DELETE CASCADE"
    onboarding_journeys }o--|| users : "user_id to id; ON DELETE CASCADE"
Storage and S3 execution 
erDiagram
    %% Generated from backend/app/db SQLAlchemy models via app.db.Base.metadata.
    %% Domain: Storage and S3 execution.
    %% PK=primary key, FK=foreign key, UK=single-column unique, NN=not nullable.
    storage_endpoints {
        integer id PK "NN"
        varchar name UK "NN"
        varchar endpoint_url UK "NN"
        varchar region
        varchar provider "NN"
        varchar admin_access_key
        varchar admin_secret_key
        varchar_32 identity_namespace UK "NN"
        boolean ceph_admin_allowed "NN; default=0"
        text features_config
        float latitude
        float longitude
        boolean force_path_style "NN; default=0"
        boolean verify_tls "NN; default=1"
        boolean is_default "NN; default=0"
        boolean is_editable "NN; default=1"
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    endpoint_service_identities {
        integer id PK "NN"
        integer endpoint_id FK "NN"
        varchar_24 kind "NN"
        varchar_16 mode "NN"
        varchar_128 rgw_uid
        varchar access_key
        varchar secret_key
        varchar_128 provenance
        varchar_24 status "NN"
        varchar_256 last_error
        datetime last_reconciled_at
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    key_rotation_intents {
        integer id PK "NN"
        integer endpoint_id FK "NN"
        varchar_32 key_type "NN"
        integer target_id "NN"
        varchar rgw_uid "NN"
        varchar tenant
        varchar rgw_endpoint "NN"
        varchar old_access_key "NN"
        varchar new_access_key "NN"
        varchar new_secret_key "NN"
        boolean deactivate_only "NN"
        varchar_16 phase "NN"
        varchar actor_email "NN"
        varchar_256 last_error
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    manager_access_key_metadata {
        integer id PK "NN"
        integer account_id FK
        integer s3_user_id FK
        varchar_256 principal_name
        varchar_256 access_key_id "NN"
        varchar_128 name
        text notes
        datetime expires_at
        varchar_32 expiration_state
        datetime expiration_enforced_at
        datetime expiration_last_attempt_at
        text expiration_last_error
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    s3_accounts {
        integer id PK "NN"
        varchar name "NN"
        varchar rgw_account_id UK "NN"
        varchar rgw_tenant "NN; default="
        varchar email
        varchar rgw_access_key
        varchar rgw_secret_key
        varchar rgw_user_uid "NN"
        datetime created_at "NN"
        datetime updated_at "NN"
        integer storage_endpoint_id FK "NN"
        text portal_settings_override
        boolean portal_settings_delegated "NN; default=0"
        boolean portal_collaborator_role_management_delegated "NN; default=0"
        boolean portal_collaborator_addition_delegated "NN; default=0"
        boolean allow_bucket_quota_management
        boolean allow_access_key_metadata
        boolean allow_access_key_expiration
        boolean allow_activity_history
        boolean allow_rgw_usage_metrics
        boolean allow_cost_estimation
        boolean allow_bucket_usage_stats
        boolean allow_bucket_usage_stats_collection
    }
    user_s3_accounts {
        integer id PK "NN"
        integer user_id FK "NN"
        integer account_id FK "NN"
        varchar manager_role
        varchar portal_role
        boolean allow_manager_browser_data_access "NN; default=0"
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    account_iam_users {
        integer id PK "NN"
        integer user_id FK "NN"
        integer account_id FK "NN"
        varchar iam_user_id UK "NN"
        varchar iam_username
        varchar active_access_key
        varchar active_secret_key
        datetime created_at "NN"
    }
    s3_users {
        integer id PK "NN"
        varchar name "NN"
        varchar rgw_user_uid "NN"
        varchar rgw_tenant "NN; default="
        varchar email
        varchar rgw_access_key "NN"
        varchar rgw_secret_key "NN"
        datetime created_at "NN"
        datetime updated_at "NN"
        integer storage_endpoint_id FK "NN"
        boolean allow_bucket_quota_management
        boolean allow_access_key_management
        boolean allow_access_key_metadata
        boolean allow_access_key_expiration
        boolean allow_activity_history
        boolean allow_rgw_usage_metrics
        boolean allow_cost_estimation
        boolean allow_bucket_usage_stats
        boolean allow_bucket_usage_stats_collection
        boolean allow_managed_private_connection_provisioning
    }
    user_s3_users {
        integer id PK "NN"
        integer user_id FK "NN"
        integer s3_user_id FK "NN"
        boolean allow_manager_browser_data_access "NN; default=0"
    }
    s3_connections {
        integer id PK "NN"
        integer created_by_user_id FK "NN"
        varchar name "NN"
        boolean is_shared "NN; default=0"
        boolean is_active "NN; default=1"
        boolean access_manager "NN; default=0"
        boolean access_browser "NN; default=1"
        boolean allow_activity_history
        boolean remediation_required "NN; default=0"
        varchar remediation_reason
        boolean server_managed "NN; default=0"
        varchar rgw_user_uid
        varchar rgw_tenant
        varchar rgw_account_id
        integer storage_endpoint_id FK
        text custom_endpoint_config
        varchar access_key_id "NN"
        varchar secret_access_key "NN"
        varchar session_token
        datetime expires_at
        text capabilities_json "NN; default={'can_manage_iam':false}"
        datetime created_at "NN"
        datetime updated_at "NN"
        datetime last_used_at
    }
    user_s3_connections {
        integer id PK "NN"
        integer user_id FK "NN"
        integer s3_connection_id FK "NN"
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    managed_private_accesses {
        integer id PK "NN"
        integer owner_user_id FK "NN"
        varchar source_context_type "NN"
        integer source_context_id "NN"
        varchar remote_principal_type "NN"
        varchar remote_principal_identifier "NN"
        varchar iam_username
        varchar access_key_id
        integer s3_connection_id FK, UK
        varchar state "NN; default=provisioning"
        text cleanup_error
        text iam_groups_json "NN; default=[]"
        text iam_managed_policies_json "NN; default=[]"
        text iam_inline_policy_names_json "NN; default=[]"
        boolean created_remote_principal "NN; default=0"
        boolean created_access_key "NN; default=0"
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    ui_group_s3_accounts {
        integer id PK "NN"
        integer group_id FK "NN"
        integer account_id FK "NN"
        varchar manager_role
        varchar portal_role
        boolean allow_manager_browser_data_access "NN; default=0"
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    ui_group_s3_users {
        integer id PK "NN"
        integer group_id FK "NN"
        integer s3_user_id FK "NN"
        boolean allow_manager_browser_data_access "NN; default=0"
    }
    ui_group_s3_connections {
        integer id PK "NN"
        integer group_id FK "NN"
        integer s3_connection_id FK "NN"
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    ui_groups {
        integer id PK "external reference"
    }
    users {
        integer id PK "external reference"
    }
    endpoint_service_identities }o--|| storage_endpoints : "endpoint_id to id; ON DELETE CASCADE"
    key_rotation_intents }o--|| storage_endpoints : "endpoint_id to id; ON DELETE CASCADE"
    manager_access_key_metadata }o--o| s3_accounts : "account_id to id; ON DELETE CASCADE"
    manager_access_key_metadata }o--o| s3_users : "s3_user_id to id; ON DELETE CASCADE"
    s3_accounts }o--|| storage_endpoints : "storage_endpoint_id to id"
    user_s3_accounts }o--|| users : "user_id to id"
    user_s3_accounts }o--|| s3_accounts : "account_id to id"
    account_iam_users }o--|| users : "user_id to id"
    account_iam_users }o--|| s3_accounts : "account_id to id"
    s3_users }o--|| storage_endpoints : "storage_endpoint_id to id"
    user_s3_users }o--|| users : "user_id to id"
    user_s3_users }o--|| s3_users : "s3_user_id to id"
    s3_connections }o--|| users : "created_by_user_id to id"
    s3_connections }o--o| storage_endpoints : "storage_endpoint_id to id"
    user_s3_connections }o--|| users : "user_id to id"
    user_s3_connections }o--|| s3_connections : "s3_connection_id to id"
    managed_private_accesses }o--|| users : "owner_user_id to id"
    managed_private_accesses o|--o| s3_connections : "s3_connection_id to id; ON DELETE SET NULL"
    ui_group_s3_accounts }o--|| ui_groups : "group_id to id"
    ui_group_s3_accounts }o--|| s3_accounts : "account_id to id"
    ui_group_s3_users }o--|| ui_groups : "group_id to id"
    ui_group_s3_users }o--|| s3_users : "s3_user_id to id"
    ui_group_s3_connections }o--|| ui_groups : "group_id to id"
    ui_group_s3_connections }o--|| s3_connections : "s3_connection_id to id"
Tags and classification 
erDiagram
    %% Generated from backend/app/db SQLAlchemy models via app.db.Base.metadata.
    %% Domain: Tags and classification.
    %% PK=primary key, FK=foreign key, UK=single-column unique, NN=not nullable.
    tag_definitions {
        integer id PK "NN"
        varchar domain_kind "NN"
        integer owner_user_id FK
        varchar label "NN"
        varchar label_key "NN"
        varchar color_key "NN"
        varchar scope "NN; default=standard"
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    bucket_ui_tag_assignments {
        integer id PK "NN"
        integer storage_endpoint_id FK "NN"
        varchar tenant_key "NN; default="
        varchar bucket_name "NN"
        integer tag_definition_id FK "NN"
        integer position "NN"
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    storage_endpoint_tags {
        integer id PK "NN"
        integer storage_endpoint_id FK "NN"
        integer tag_definition_id FK "NN"
        integer position "NN"
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    s3_account_tags {
        integer id PK "NN"
        integer account_id FK "NN"
        integer tag_definition_id FK "NN"
        integer position "NN"
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    s3_user_tags {
        integer id PK "NN"
        integer s3_user_id FK "NN"
        integer tag_definition_id FK "NN"
        integer position "NN"
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    s3_connection_tags {
        integer id PK "NN"
        integer s3_connection_id FK "NN"
        integer tag_definition_id FK "NN"
        integer position "NN"
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    s3_accounts {
        integer id PK "external reference"
    }
    s3_connections {
        integer id PK "external reference"
    }
    s3_users {
        integer id PK "external reference"
    }
    storage_endpoints {
        integer id PK "external reference"
    }
    users {
        integer id PK "external reference"
    }
    tag_definitions }o--o| users : "owner_user_id to id"
    bucket_ui_tag_assignments }o--|| storage_endpoints : "storage_endpoint_id to id; ON DELETE CASCADE"
    bucket_ui_tag_assignments }o--|| tag_definitions : "tag_definition_id to id; ON DELETE CASCADE"
    storage_endpoint_tags }o--|| storage_endpoints : "storage_endpoint_id to id; ON DELETE CASCADE"
    storage_endpoint_tags }o--|| tag_definitions : "tag_definition_id to id; ON DELETE CASCADE"
    s3_account_tags }o--|| s3_accounts : "account_id to id; ON DELETE CASCADE"
    s3_account_tags }o--|| tag_definitions : "tag_definition_id to id; ON DELETE CASCADE"
    s3_user_tags }o--|| s3_users : "s3_user_id to id; ON DELETE CASCADE"
    s3_user_tags }o--|| tag_definitions : "tag_definition_id to id; ON DELETE CASCADE"
    s3_connection_tags }o--|| s3_connections : "s3_connection_id to id; ON DELETE CASCADE"
    s3_connection_tags }o--|| tag_definitions : "tag_definition_id to id; ON DELETE CASCADE"
Portal 
erDiagram
    %% Generated from backend/app/db SQLAlchemy models via app.db.Base.metadata.
    %% Domain: Portal.
    %% PK=primary key, FK=foreign key, UK=single-column unique, NN=not nullable.
    portal_storage_space_metadata {
        integer id PK "NN"
        integer account_id FK "NN"
        varchar bucket_name "NN"
        varchar display_name
        text description
        integer owner_user_id FK
        varchar visibility "NN"
        varchar share_scope "NN"
        varchar account_member_role
        varchar project_key
        varchar dataset_label
        varchar icon_source "NN; default=preset"
        varchar icon_preset "NN; default=bucket"
        blob icon_image
        varchar icon_content_type
        datetime icon_updated_at
        varchar origin "NN"
        boolean name_editable "NN"
        datetime archived_at
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    portal_storage_space_grants {
        integer id PK "NN"
        integer storage_space_metadata_id FK "NN"
        integer user_id FK "NN"
        varchar role "NN"
        integer created_by_user_id FK
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    portal_public_links {
        integer id PK "NN"
        varchar token UK "NN"
        integer account_id FK "NN"
        varchar bucket_name "NN"
        text object_key "NN"
        varchar label
        integer created_by_user_id FK
        varchar created_by_email
        datetime expires_at
        datetime revoked_at
        datetime created_at "NN"
    }
    portal_external_access_credentials {
        integer id PK "NN"
        integer account_id FK "NN"
        integer storage_space_metadata_id FK "NN"
        varchar bucket_name "NN"
        integer created_by_user_id FK
        varchar external_email "NN"
        varchar permission "NN"
        varchar iam_user_id
        varchar iam_username UK "NN"
        varchar access_key_id UK "NN"
        varchar status "NN; default=Active"
        datetime revoked_at
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    portal_admin_requests {
        integer id PK "NN"
        integer account_id FK "NN"
        integer requester_user_id FK
        varchar requester_email "NN"
        varchar request_type "NN"
        varchar status "NN; default=pending"
        text payload_json "NN"
        text result_json
        text error_message
        integer decided_by_user_id FK
        varchar decided_by_email
        datetime decided_at
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    portal_admin_request_messages {
        integer id PK "NN"
        integer request_id FK "NN"
        integer author_user_id FK
        varchar author_email "NN"
        varchar author_role
        text message "NN"
        datetime created_at "NN"
    }
    s3_accounts {
        integer id PK "external reference"
    }
    users {
        integer id PK "external reference"
    }
    portal_storage_space_metadata }o--|| s3_accounts : "account_id to id; ON DELETE CASCADE"
    portal_storage_space_metadata }o--o| users : "owner_user_id to id"
    portal_storage_space_grants }o--|| portal_storage_space_metadata : "storage_space_metadata_id to id; ON DELETE CASCADE"
    portal_storage_space_grants }o--|| users : "user_id to id; ON DELETE CASCADE"
    portal_storage_space_grants }o--o| users : "created_by_user_id to id"
    portal_public_links }o--|| s3_accounts : "account_id to id; ON DELETE CASCADE"
    portal_public_links }o--o| users : "created_by_user_id to id"
    portal_external_access_credentials }o--|| s3_accounts : "account_id to id; ON DELETE CASCADE"
    portal_external_access_credentials }o--|| portal_storage_space_metadata : "storage_space_metadata_id to id; ON DELETE CASCADE"
    portal_external_access_credentials }o--o| users : "created_by_user_id to id; ON DELETE SET NULL"
    portal_admin_requests }o--|| s3_accounts : "account_id to id; ON DELETE CASCADE"
    portal_admin_requests }o--o| users : "requester_user_id to id; ON DELETE SET NULL"
    portal_admin_requests }o--o| users : "decided_by_user_id to id; ON DELETE SET NULL"
    portal_admin_request_messages }o--|| portal_admin_requests : "request_id to id; ON DELETE CASCADE"
    portal_admin_request_messages }o--o| users : "author_user_id to id; ON DELETE SET NULL"
Operations and audit 
erDiagram
    %% Generated from backend/app/db SQLAlchemy models via app.db.Base.metadata.
    %% Domain: Operations and audit.
    %% PK=primary key, FK=foreign key, UK=single-column unique, NN=not nullable.
    audit_logs {
        integer id PK "NN"
        datetime created_at "NN"
        integer user_id FK
        varchar user_email "NN"
        varchar user_role "NN"
        varchar scope "NN"
        varchar action "NN"
        varchar entity_type
        varchar entity_id
        varchar context_kind
        varchar context_id
        varchar context_name
        varchar status "NN"
        varchar message
        text metadata_json
        varchar request_id
        varchar ip_address
        varchar user_agent
    }
    user_notifications {
        integer id PK "NN"
        integer user_id FK "NN"
        varchar notification_type "NN"
        varchar severity "NN"
        varchar title "NN"
        text message "NN"
        varchar subject_type
        integer storage_endpoint_id FK
        integer s3_account_id FK
        integer s3_user_id FK
        varchar event_key "NN"
        text payload_json "NN; default={}"
        datetime created_at "NN"
        datetime read_at
    }
    webhook_endpoints {
        integer id PK "NN"
        varchar name UK "NN"
        text url "NN"
        boolean enabled "NN; default=0"
        varchar signing_secret
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    webhook_endpoint_subscriptions {
        integer id PK "NN"
        integer endpoint_id FK "NN"
        varchar event_type "NN"
    }
    webhook_deliveries {
        integer id PK "NN"
        varchar delivery_id UK "NN"
        varchar event_id "NN"
        integer endpoint_id FK "NN"
        varchar event_type "NN"
        text payload_json "NN"
        boolean is_test "NN; default=0"
        varchar status "NN; default=pending"
        integer attempt_count "NN; default=0"
        datetime next_attempt_at "NN"
        integer last_http_status
        text last_error
        datetime created_at "NN"
        datetime updated_at "NN"
        datetime delivered_at
    }
    bucket_migrations {
        integer id PK "NN"
        integer created_by_user_id FK
        varchar source_context_id "NN"
        varchar target_context_id "NN"
        varchar mode "NN; default=pre_sync"
        boolean copy_bucket_settings "NN; default=0"
        boolean strong_integrity_check "NN; default=0"
        boolean lock_target_writes "NN; default=1"
        boolean use_same_endpoint_copy "NN; default=0"
        boolean auto_grant_source_read_for_copy "NN; default=0"
        varchar status "NN; default=draft"
        varchar worker_lease_owner
        datetime worker_lease_until
        varchar precheck_status "NN; default=pending"
        text precheck_report_json
        datetime precheck_checked_at
        integer workflow_version "NN; default=2"
        integer configuration_revision "NN; default=1"
        integer checked_revision
        varchar preparation_status "NN; default=unverified"
        boolean preparation_active_checks "NN; default=0"
        integer preparation_completed_items "NN; default=0"
        datetime preparation_requested_at
        varchar maintenance_operation
        varchar maintenance_status "NN; default=idle"
        text maintenance_error
        integer maintenance_requested_by_user_id FK
        integer parallelism_max "NN; default=16"
        integer total_items "NN; default=0"
        integer completed_items "NN; default=0"
        integer failed_items "NN; default=0"
        integer skipped_items "NN; default=0"
        integer awaiting_items "NN; default=0"
        varchar error_message
        datetime started_at
        datetime finished_at
        datetime last_heartbeat_at
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    bucket_migration_items {
        integer id PK "NN"
        integer migration_id FK "NN"
        varchar source_bucket "NN"
        varchar target_bucket "NN"
        varchar status "NN; default=pending"
        varchar step "NN; default=create_bucket"
        boolean pre_sync_done "NN; default=0"
        boolean read_only_applied "NN; default=0"
        boolean target_lock_applied "NN; default=0"
        boolean target_created_by_migration "NN; default=0"
        text preparation_effects_json
        boolean source_deleted "NN; default=0"
        text cleanup_error
        text cleanup_verification_json
        integer objects_copied "NN; default=0"
        integer objects_deleted "NN; default=0"
        integer source_count
        integer target_count
        integer matched_count
        integer different_count
        integer only_source_count
        integer only_target_count
        text diff_sample_json
        text source_snapshot_json
        text target_snapshot_json
        varchar execution_strategy
        text replication_state_json
        text source_policy_backup_json
        text target_policy_backup_json
        varchar error_message
        datetime started_at
        datetime finished_at
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    bucket_migration_events {
        integer id PK "NN"
        integer migration_id FK "NN"
        integer item_id FK
        varchar level "NN; default=info"
        text message "NN"
        text metadata_json
        datetime created_at "NN"
    }
    bucket_usage_stats_snapshots {
        integer id PK "NN"
        varchar scope_kind "NN"
        varchar scope_id "NN"
        varchar scope_name
        varchar bucket_tenant "NN; default="
        varchar bucket_name "NN"
        varchar scan_mode "NN; default=versions"
        boolean version_listing_available "NN; default=1"
        integer object_version_count "NN; default=0"
        integer current_version_count "NN; default=0"
        integer noncurrent_version_count "NN; default=0"
        integer delete_marker_count "NN; default=0"
        integer total_bytes "NN; default=0"
        integer current_bytes "NN; default=0"
        integer noncurrent_bytes "NN; default=0"
        text data_type_distribution_json "NN"
        text storage_class_distribution_json "NN"
        text size_distribution_json "NN"
        text age_distribution_json "NN"
        text current_noncurrent_distribution_json "NN"
        text warnings_json
        datetime calculated_at "NN"
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    backend_operation_leases {
        varchar operation_name PK "NN"
        varchar lease_owner "NN"
        datetime lease_until "NN"
        datetime updated_at "NN"
        text metadata_json
    }
    app_settings {
        varchar key PK "NN"
        text payload_json "NN"
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    s3_accounts {
        integer id PK "external reference"
    }
    s3_users {
        integer id PK "external reference"
    }
    storage_endpoints {
        integer id PK "external reference"
    }
    users {
        integer id PK "external reference"
    }
    audit_logs }o--o| users : "user_id to id"
    user_notifications }o--|| users : "user_id to id; ON DELETE CASCADE"
    user_notifications }o--o| storage_endpoints : "storage_endpoint_id to id"
    user_notifications }o--o| s3_accounts : "s3_account_id to id"
    user_notifications }o--o| s3_users : "s3_user_id to id"
    webhook_endpoint_subscriptions }o--|| webhook_endpoints : "endpoint_id to id; ON DELETE CASCADE"
    webhook_deliveries }o--|| webhook_endpoints : "endpoint_id to id; ON DELETE CASCADE"
    bucket_migrations }o--o| users : "created_by_user_id to id"
    bucket_migrations }o--o| users : "maintenance_requested_by_user_id to id"
    bucket_migration_items }o--|| bucket_migrations : "migration_id to id"
    bucket_migration_events }o--|| bucket_migrations : "migration_id to id"
    bucket_migration_events }o--o| bucket_migration_items : "item_id to id"
Observability, quota and cost 
erDiagram
    %% Generated from backend/app/db SQLAlchemy models via app.db.Base.metadata.
    %% Domain: Observability quota and cost.
    %% PK=primary key, FK=foreign key, UK=single-column unique, NN=not nullable.
    endpoint_health_checks {
        integer id PK "NN"
        integer storage_endpoint_id FK "NN"
        datetime checked_at "NN"
        integer http_status
        integer latency_ms
        varchar check_mode "NN; default=http"
        varchar status "NN"
        varchar error_message
    }
    endpoint_health_latest {
        integer id PK "NN"
        integer storage_endpoint_id FK "NN"
        varchar check_mode "NN; default=http"
        varchar check_type "NN; default=availability"
        varchar scope "NN; default=endpoint"
        datetime checked_at "NN"
        varchar status "NN"
        integer latency_ms
        integer http_status
        varchar error_message
        integer min_latency_ms
        integer avg_latency_ms
        integer max_latency_ms
        integer latency_sample_count "NN; default=0"
        integer availability_24h
        datetime updated_at "NN"
    }
    endpoint_health_status_segments {
        integer id PK "NN"
        integer storage_endpoint_id FK "NN"
        varchar check_mode "NN; default=http"
        varchar check_type "NN; default=availability"
        varchar scope "NN; default=endpoint"
        varchar status "NN"
        datetime started_at "NN"
        datetime ended_at
        integer min_latency_ms
        integer avg_latency_ms
        integer max_latency_ms
        integer latency_sample_count "NN; default=0"
        datetime updated_at "NN"
    }
    endpoint_health_rollups {
        integer id PK "NN"
        integer storage_endpoint_id FK "NN"
        varchar check_mode "NN; default=http"
        varchar check_type "NN; default=availability"
        varchar scope "NN; default=endpoint"
        integer resolution_seconds "NN; default=300"
        datetime bucket_start "NN"
        integer up_count "NN; default=0"
        integer degraded_count "NN; default=0"
        integer down_count "NN; default=0"
        integer unknown_count "NN; default=0"
        integer latency_avg_ms
        integer latency_p95_ms
        integer latency_sample_count "NN; default=0"
        datetime updated_at "NN"
    }
    quota_usage_hourly {
        integer id PK "NN"
        datetime hour_ts "NN"
        integer storage_endpoint_id FK "NN"
        integer s3_account_id FK
        integer s3_user_id FK
        bigint used_bytes "NN; default=0"
        bigint used_objects "NN; default=0"
        integer bucket_count
        bigint quota_size_bytes
        bigint quota_objects
        numeric_8_3 usage_ratio_pct
        datetime collected_at "NN"
    }
    quota_usage_daily {
        integer id PK "NN"
        date day "NN"
        integer storage_endpoint_id FK "NN"
        integer s3_account_id FK
        integer s3_user_id FK
        bigint last_used_bytes "NN; default=0"
        bigint last_used_objects "NN; default=0"
        integer bucket_count
        numeric_8_3 max_ratio_pct
        integer samples_count "NN; default=1"
        datetime updated_at "NN"
    }
    quota_alert_states {
        integer id PK "NN"
        integer storage_endpoint_id FK "NN"
        integer s3_account_id FK
        integer s3_user_id FK
        varchar last_level "NN; default=normal"
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    cost_usage_daily {
        integer id PK "NN"
        date day "NN"
        integer storage_endpoint_id FK "NN"
        integer s3_account_id FK
        integer s3_user_id FK
        bigint bytes_in "NN; default=0"
        bigint bytes_out "NN; default=0"
        bigint ops_total "NN; default=0"
        text ops_breakdown
        varchar source "NN"
        datetime collected_at "NN"
    }
    cost_storage_daily {
        integer id PK "NN"
        date day "NN"
        integer storage_endpoint_id FK "NN"
        integer s3_account_id FK
        integer s3_user_id FK
        bigint total_bytes "NN; default=0"
        bigint total_objects "NN; default=0"
        text by_bucket
        varchar source "NN"
        datetime collected_at "NN"
    }
    cost_models {
        integer id PK "NN"
        varchar currency "NN; default=EUR"
        numeric_12_6 storage_gb_month_price "NN"
        numeric_12_6 egress_gb_price "NN"
        numeric_12_6 ingress_gb_price "NN"
        numeric_12_6 requests_per_1000_price "NN"
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    cost_endpoint_overrides {
        integer id PK "NN"
        integer storage_endpoint_id FK, UK "NN"
        numeric_12_6 storage_gb_month_price "NN"
        numeric_12_6 egress_gb_price "NN"
        numeric_12_6 ingress_gb_price "NN"
        numeric_12_6 requests_per_1000_price "NN"
        datetime created_at "NN"
        datetime updated_at "NN"
    }
    s3_accounts {
        integer id PK "external reference"
    }
    s3_users {
        integer id PK "external reference"
    }
    storage_endpoints {
        integer id PK "external reference"
    }
    endpoint_health_checks }o--|| storage_endpoints : "storage_endpoint_id to id"
    endpoint_health_latest }o--|| storage_endpoints : "storage_endpoint_id to id"
    endpoint_health_status_segments }o--|| storage_endpoints : "storage_endpoint_id to id"
    endpoint_health_rollups }o--|| storage_endpoints : "storage_endpoint_id to id"
    quota_usage_hourly }o--|| storage_endpoints : "storage_endpoint_id to id"
    quota_usage_hourly }o--o| s3_accounts : "s3_account_id to id"
    quota_usage_hourly }o--o| s3_users : "s3_user_id to id"
    quota_usage_daily }o--|| storage_endpoints : "storage_endpoint_id to id"
    quota_usage_daily }o--o| s3_accounts : "s3_account_id to id"
    quota_usage_daily }o--o| s3_users : "s3_user_id to id"
    quota_alert_states }o--|| storage_endpoints : "storage_endpoint_id to id"
    quota_alert_states }o--o| s3_accounts : "s3_account_id to id"
    quota_alert_states }o--o| s3_users : "s3_user_id to id"
    cost_usage_daily }o--|| storage_endpoints : "storage_endpoint_id to id"
    cost_usage_daily }o--o| s3_accounts : "s3_account_id to id"
    cost_usage_daily }o--o| s3_users : "s3_user_id to id"
    cost_storage_daily }o--|| storage_endpoints : "storage_endpoint_id to id"
    cost_storage_daily }o--o| s3_accounts : "s3_account_id to id"
    cost_storage_daily }o--o| s3_users : "s3_user_id to id"
    cost_endpoint_overrides o|--|| storage_endpoints : "storage_endpoint_id to id; ON DELETE CASCADE"

Complete interactive model in ChartDB

Download the generated ChartDB diagram

Open ChartDB, choose Import diagram, and select the downloaded JSON file. The generated canvas groups every application table into the same six documented domains using native ChartDB Areas. Tables are expanded and laid out deterministically so regenerating an unchanged model does not move them or create noisy diffs.

The ChartDB file uses SQLite as its interchange dialect because SQLite is the default local BucketReef database and ChartDB's JSON import workflow requires a concrete database type. The SQLAlchemy model remains shared with PostgreSQL, and the generated diagram is a structural model rather than a claim about the production database engine.

Operational note

Always apply schema migrations as part of deployment lifecycle.