Database schema

Cadence stores all persistent state in PostgreSQL. The schema is defined by SQLAlchemy models in cadence/infra/persistence/postgresql/models.py and migrations are managed by Alembic. This page is a complete reference for every table.

Conventions

• Primary keys: UUID7 (time-ordered, generated by uuid_utils.uuid7) for entity tables; auto-increment integer for join and settings tables. provider_model_configs is the exception: its PK is auto-increment integer.
• Timestamps: Most tables carry created_at and updated_at (both DateTime(timezone=True) with UTC defaults; updated_at uses onupdate). The messages table is partitioned and has only created_at.
• Soft delete: Most tables have is_deleted (boolean, default false). Soft-deleted rows are excluded from normal queries; sys_admins can see them. Tables without is_deleted: user_org_memberships, user_oauth_identities, role_permissions, user_roles, plugins (uses enabled boolean instead), provider_model_configs, messages.
• Audit columns: Most tables carry created_by and updated_by (string). api_keys has only updated_by. user_oauth_identities, role_permissions, provider_model_configs, and messages have no audit columns.
• String lengths: SHORT=50, STANDARD=255, LONG=512, VERY_LONG=1000.
• JSONB: Used for flexible config, settings values, and arrays (redirect URIs, scopes, capabilities).

Entity relationship diagram

erDiagram
    organizations ||--o{ organization_settings : has
    organizations ||--o{ organization_llm_configs : has
    organizations ||--o{ orchestrator_instances : has
    organizations ||--o{ central_points : has
    organizations ||--o{ user_org_memberships : has

    users ||--o{ user_org_memberships : has
    users ||--o{ user_oauth_identities : has
    users ||--o{ user_roles : has
    users ||--o{ api_keys : has
    users ||--o{ conversations : has

    roles ||--o{ role_permissions : has
    roles ||--o{ user_roles : has

    orchestrator_instances ||--o{ conversations : has
    orchestrator_instances ||--o{ central_points : referenced_by

    plugins }o--o| organizations : "org-scoped or system"

    central_points }o--|| orchestrator_instances : "backing instance"
    oauth2_clients ||--o{ user_oauth_identities : "links to"
    api_keys }o--|| users : "owner"

provider_model_configs is a flat standalone catalog of known LLM models per provider; it has no foreign keys and is not connected to any other table. messages is partitioned and has no foreign keys either.

Table reference

global_settings

Platform-wide configuration (Tier 2 in the settings cascade).

| Column | Type | Notes | | -------------- | ------------ | ---------------------------------- | | id | Integer PK | Auto-increment | | key | string(255) | Unique setting key (NOT NULL) | | value | JSONB | Setting value (NOT NULL) | | value_type | string(50) | string, integer, boolean, float, json (NOT NULL) | | description | text | Human-readable description | | overridable | boolean | If false, orgs cannot override (default false) | | category | string(50) | Optional grouping (tier, telemetry) | | created_at / updated_at | DateTime(tz) | UTC defaults (updated_at onupdate) | | created_by / updated_by | string(255) | Audit, nullable | | is_deleted | boolean | Soft delete |

Tier quotas are stored as JSON values under keys tier.<tier_name> with category = "tier". value_type is not strictly enforced — the codebase stores free-form strings; the values in use today are string, integer, boolean, json, and float.

organizations

Tenant organization — the primary isolation boundary.

| Column | Type | Notes | | ------------------- | ------------ | ------------------------------------------- | | id | UUID7 PK | | | name | string(255) | NOT NULL | | status | string(50) | active (default) | | display_name | string(500) | nullable | | domain | string(255) | Unique (partial index where NOT NULL) | | subscription_tier | string(50) | free (default); serialized as tier in API responses | | description | text | nullable | | contact_email | string(255) | nullable | | website | string(512) | nullable | | logo_url | string(1000) | nullable | | country | string(100) | nullable | | timezone | string(100) | nullable | | created_at / updated_at | DateTime(tz) | UTC defaults | | created_by / updated_by | string(255) | nullable audit columns | | is_deleted | boolean | Soft delete |

Relationships: organization_settings, organization_llm_configs, orchestrator_instances, user_org_memberships, central_points (all CASCADE).

organization_settings

Org-scoped settings (Tier 3 in the cascade).

| Column | Type | Notes | | -------------- | ------------ | ---------------------------------- | | id | Integer PK | Auto-increment | | org_id | UUID7 FK | → organizations.id (CASCADE) | | key | string(255) | | | value | JSONB | | | overridable | boolean | Whether instance can override | | is_deleted | boolean | Soft delete |

Constraints: Unique (org_id, key).

organization_llm_configs

BYOK LLM provider configurations per org. Provider is immutable after creation.

| Column | Type | Notes | | ------------------- | ------------ | ------------------------------------------- | | id | UUID7 PK | | | org_id | UUID7 FK | → organizations.id (CASCADE) | | name | string(255) | Not unique per org | | provider | string(50) | openai, anthropic, google, etc. | | api_key | text | Stored as-is (encrypt at service level) | | base_url | string(512) | Optional custom base URL | | additional_config | JSONB | Provider-specific extras (Azure deployment) | | is_enabled | boolean | If false, hidden from new orchestrators | | is_deleted | boolean | Soft delete |

orchestrator_instances

AI App configurations (Tier 4 in the cascade).

| Column | Type | Notes | | ------------------- | ------------ | ---------------------------------------------- | | id | UUID PK | | | org_id | UUID FK | → organizations.id (CASCADE) | | name | string(255) | | | framework_type | string(50) | langgraph / openai_agents / google_adk — immutable | | mode | string(50) | supervisor / coordinator / handoff / grounded — immutable | | status | string(50) | active (default), suspended, inactive | | config | JSONB | Mutable config (mode_config, active_plugins) | | tier | string(20) | demand (default) or hot | | whoami | text | Identity context for system prompts | | plugin_settings | JSONB | Per-plugin settings map | | config_hash | string(64) | First 16 hex chars of SHA-256 of effective config (pool dedup) | | last_accessed_at | DateTime(tz) | Updated on chat access | | created_at | DateTime(tz) | UTC | | updated_at | DateTime(tz) | UTC, onupdate | | created_by | string(255) | Audit, nullable | | updated_by | string(255) | Audit, nullable | | is_deleted | boolean | Soft delete (sys_admin only) |

Indexes: org_id, (org_id, status), last_accessed_at, tier, (org_id, is_deleted).

Relationships: conversations, central_points.

central_points

Stable API handles mapping to orchestrator instances. Pro+ tier only.

| Column | Type | Notes | | ---------------- | ------------ | ------------------------------------------- | | id | UUID7 PK | | | org_id | UUID7 FK | → organizations.id (CASCADE) | | orchestrator_id| UUID7 FK | → orchestrator_instances.id (RESTRICT) | | name | string(255) | Unique per org (active) | | description | text | | | visibility | string(50) | private (default) or public | | status | string(50) | active (default) | | is_deleted | boolean | Soft delete |

Constraints: Unique (org_id, name) where is_deleted = FALSE.

users

Platform users — not bound to a single org.

| Column | Type | Notes | | --------------- | ------------ | ------------------------------------------- | | id | UUID7 PK | | | username | string(255) | Unique (where is_deleted = FALSE) | | email | string(255) | Unique (where is_deleted = FALSE AND NOT NULL) | | password_hash | text | Nullable (OAuth-only users) | | display_name | string(255) | | | avatar_url | text | | | locale | string(35) | | | timezone | string(64) | | | bio | text | | | is_deleted | boolean | Soft delete |

Relationships: user_org_memberships, user_oauth_identities, user_roles, api_keys, conversations (all CASCADE).

user_org_memberships

Membership linking users to organizations.

| Column | Type | Notes | | --------- | -------- | ---------------------------------- | | id | Integer PK | Auto-increment | | user_id | UUID7 FK | → users.id (CASCADE) | | org_id | UUID7 FK | → organizations.id (CASCADE) |

Constraints: Unique (user_id, org_id).

user_oauth_identities

Links external OAuth identities to platform users.

| Column | Type | Notes | | ------------------ | ------------ | ---------------------------------- | | id | UUID7 PK | | | user_id | UUID7 FK | → users.id (CASCADE) | | provider | string(50) | google, github, oauth2 | | provider_user_id | string(255) | Provider’s unique user ID |

Constraints: Unique (provider, provider_user_id).

roles

Named roles with permissions via role_permissions.

| Column | Type | Notes | | --------------- | ------------ | ---------------------------------- | | id | UUID7 PK | | | name | string(255) | Unique | | description | text | | | scope | string(50) | system or org | | is_built_in | boolean | Built-in roles cannot be deleted | | is_deleted | boolean | Soft delete |

role_permissions

Permission strings granted to a role.

| Column | Type | Notes | | ------------ | ------------ | ---------------------------------- | | id | Integer PK | Auto-increment | | role_id | UUID7 FK | → roles.id (CASCADE) | | permission | string(255) | cadence:* permission string |

Constraints: Unique (role_id, permission).

user_roles

Role assignments to users. org_id is null for system-scoped roles.

| Column | Type | Notes | | --------- | ------------ | ---------------------------------- | | id | Integer PK | Auto-increment | | user_id | UUID7 FK | → users.id (CASCADE) | | role_id | UUID7 FK | → roles.id (CASCADE) | | org_id | UUID7 FK | → organizations.id (CASCADE), nullable |

Constraints: Unique (user_id, role_id) where org_id IS NULL; Unique (user_id, role_id, org_id) where org_id IS NOT NULL.

oauth2_clients

Registered OAuth2 client applications.

| Column | Type | Notes | | -------------------- | ------------ | ---------------------------------- | | id | UUID7 PK | | | client_id | string(255) | Unique | | client_secret_hash | text | Hashed (nullable for public clients) | | name | string(255) | | | client_type | string(50) | public or confidential | | is_first_party | boolean | | | redirect_uris | JSONB | Array of URI strings | | allowed_grant_types| JSONB | Array of grant type strings | | allowed_scopes | JSONB | Array of scope strings | | is_active | boolean | | | is_deleted | boolean | Soft delete |

api_keys

User-issued API keys for programmatic access. Prefixed with cdk_.

| Column | Type | Notes | | -------------- | ------------ | ---------------------------------- | | id | UUID PK | | | user_id | UUID FK | → users.id (CASCADE) | | name | string(255) | | | key_hash | string(64) | SHA-256 hash of full key | | key_prefix | string(16) | First chars for display | | scopes | JSONB | Array of permission strings | | expires_at | DateTime(tz) | Nullable (no expiration) | | last_used_at | DateTime(tz) | Best-effort timestamp | | is_active | boolean | false means soft-revoked | | created_at | DateTime(tz) | UTC | | updated_at | DateTime(tz) | UTC | | updated_by | string(255) | Audit (no created_by column) | | is_deleted | boolean | Soft delete |

Indexes: user_id, key_hash.

plugins

Unified plugin catalog — system-wide (source='system', org_id NULL) or org-scoped.

| Column | Type | Notes | | ----------------- | ------------ | ------------------------------------------- | | id | UUID7 PK | | | source | string(10) | system or org | | org_id | UUID7 FK | Nullable (system plugins), → organizations | | pid | string(255) | Plugin ID (reverse-domain notation) | | version | string(50) | Semver string | | name | string(255) | Display name | | description | text | | | tag | string(100) | Category tag | | is_latest | boolean | Marks the latest version of a pid | | s3_path | string(512) | S3 storage path (if S3 enabled) | | logo_image | text | Base64 logo | | default_settings| JSONB | Default plugin settings | | settings_schema | JSONB | Settings schema for UI generation | | capabilities | JSONB | Plugin capability tags | | is_specialized | boolean | Has specialized agents | | is_scoped | boolean | Has scoped agents (supports grounded mode) | | stateless | boolean | Stateless plugin (default true) | | enabled | boolean | |

Constraints: Check (source='system' AND org_id IS NULL) OR (source='org' AND org_id IS NOT NULL). Unique (pid, version) per source; unique (org_id, pid) for latest version per source.

provider_model_configs

Platform-level catalog of known LLM models per provider.

| Column | Type | Notes | | ----------------------- | ------------ | ---------------------------------- | | id | Integer PK | Auto-increment (UUID7 elsewhere) | | provider | string(50) | openai, anthropic, etc. | | model_id | string(255) | Model identifier sent to API | | display_name | string(255) | Human-readable label | | aliases | JSONB | Alternative identifiers | | enabled | boolean | Visible in selection list | | model_category | string(32) | text_generation (default), etc. | | input_billing_unit | string(64) | per_1m_tokens (default) | | output_billing_unit | string(64) | per_1m_tokens (default) | | input_price_per_unit | Numeric(18,8)| Nullable reference price | | output_price_per_unit | Numeric(18,8)| Nullable reference price | | currency | string(3) | ISO 4217 (default USD) | | created_at | DateTime(tz) | UTC | | updated_at | DateTime(tz) | UTC |

No created_by / updated_by / is_deleted columns.

Constraints: Unique (provider, model_id) where enabled = TRUE.

conversations

Conversation metadata per user per orchestrator instance.

| Column | Type | Notes | | -------------- | ------------ | ---------------------------------- | | id | UUID7 PK | | | title | string(500) | Nullable | | org_id | UUID7 FK | → organizations.id (CASCADE) | | user_id | UUID7 FK | → users.id (CASCADE) | | instance_id | UUID7 FK | → orchestrator_instances.id (SET NULL) | | is_deleted | boolean | Soft delete |

Indexes: user_id, instance_id, org_id.

messages

Conversation messages. Roles: user (human), assistant (bot), intent (orchestrator pre-LLM, excluded from history reads). The actual MessageRepository._role_from_message produces user, assistant, tool, or "unknown" — the intent role is documented in the model but is not currently produced by the SDK mapping.

| Column | Type | Notes | | --------------- | ------------ | ---------------------------------- | | id | UUID PK | | | org_id | UUID | Denormalized for queries (no FK) | | conv_id | UUID | → conversations.id (no FK) | | turn_id | UUID | Groups messages in a single turn | | user_id | UUID | (no FK) | | instance_id | UUID | (no FK) | | role | string(20) | user, assistant, tool, intent | | content | text | Message content | | status | string(20) | | | is_compacted | boolean | Compacted by context policy | | source | string(20) | human (default) | | metadata | JSONB | Python attribute metadata_; column name metadata | | created_at | DateTime(tz) | UTC; no updated_at (table is partitioned) |

Migration management

Migrations are managed by Alembic. The migration runner is in cadence/infra/persistence/postgresql/migrations.py. Migration scripts live in the alembic/versions/ directory.

Never edit applied migrations

Migration files are immutable once applied to any environment. Create a new migration for schema changes. See the project’s AGENTS.md for the absolute rules on migrations.

Next steps

Settings cascade How settings resolve across global, org, and instance tiers.

Multi-tenancy Organization isolation and tenant context.

Developer onboarding Repository layout, architecture layers, and code tour.