Vendale engineering
Database schema
Tables, tenancy columns, and how multi-tenant isolation is expressed in the data model.
Maintained with the code · Download .md
Phase 3B adds persistent PostgreSQL tables for customers, conversations, and messages. Phase 2 Stage 2E and earlier store remaining data in frontend memory only.
Phase 2 Stage 2E stores all data in frontend memory only. There is no SQLite cache, no PostgreSQL schema, and no persistence layer.
The current in-memory mock graph covers:
teamMembers tags customers customerNotes customerActivities recentOrderSummaries conversations conversationAssignments messages orders orderItems orderTimelines products productVariants catalogues dashboardMetrics dashboardActivities notifications searchRecords broadcasts broadcastTemplates customerSegments tickets ticketNotes analyticsData members invitations roles organizationSettings userPreferences auditEvents
Phase 3B PostgreSQL Tables (Implemented)
customers — id, organization_id, display_name, primary_phone (E.164), primary_email,
lifecycle_status, is_archived, archived_at, assigned_membership_id,
source, last_activity_at, version, created_at, updated_at
customer_notes — id, organization_id, customer_id, author_user_id, body, edited_at
customer_activities — id, organization_id, customer_id, actor_user_id, activity_type, summary,
entity_type, entity_id
customer_identities — id, organization_id, customer_id, platform, external_id, display_name
tags — id, organization_id, name, color_token
customer_tags — customer_id, tag_id (composite PK)
conversations — id, organization_id, customer_id, channel, status, priority,
assigned_membership_id, subject, external_thread_id,
last_message_at, last_message_preview, last_message_direction,
last_inbound_message_at, last_outbound_message_at,
unread_count, message_count,
resolved_at, closed_at, reopened_at, version, created_at, updated_at
messages — id, organization_id, conversation_id, customer_id,
sender_type, sender_user_id, sender_display_name,
direction, message_type, body, status, simulated,
client_message_id (partial unique: WHERE NOT NULL),
failure_code, failure_reason,
sent_at, delivered_at, read_at, failed_at, created_at, updated_atPhase 3C PostgreSQL Tables (Implemented)
orders — id, organization_id, order_number (ORD-YYYY-NNNNNN), customer_id,
assigned_membership_id, status, payment_status, fulfillment_status,
currency, subtotal, discount_total, tax_total, shipping_total, grand_total
(all Numeric(19,4)), delivery_address, customer_note, internal_note,
cancellation_reason, cancelled_at, confirmed_at, fulfilled_at,
version, created_at, updated_at
order_items — id, order_id, product_id (nullable FK), variant_id (nullable FK),
product_name (snapshot), sku (snapshot), variant_name (snapshot),
description, quantity, currency, unit_price, discount_total, tax_total,
line_total (all Numeric(19,4)), created_at
order_number_counters — id, organization_id (unique), year, last_sequence — SELECT FOR UPDATE
products — id, organization_id, name, normalized_name, sku (uppercase, nullable unique
per org), description, category, status, availability, currency,
unit_price (Numeric(19,4)), track_inventory, stock_quantity,
image_placeholder, version, created_at, updated_at
product_variants — id, product_id, name, sku (nullable), unit_price (Numeric(19,4)),
stock_quantity, position, created_at, updated_at
inventory_movements — id, organization_id, product_id, actor_user_id, reason,
quantity_delta, quantity_before, quantity_after, created_at
catalogues — id, organization_id, name, normalized_name, description, status,
is_default, archived_at, version, created_by_user_id,
created_at, updated_at
catalogue_products — catalogue_id, product_id (composite PK), position, is_featuredPhase 3D PostgreSQL Tables (Implemented)
tickets — id, organization_id, ticket_number (TKT-YYYY-NNNNNN), customer_id,
conversation_id, order_id, assigned_membership_id, title, description,
status, priority, category, source, due_at, resolved_at, closed_at,
version, created_by_user_id, created_at, updated_at
ticket_notes — id, organization_id, ticket_id, author_user_id, body, created_at
ticket_timeline — id, organization_id, ticket_id, event, detail, actor_user_id, created_at
ticket_number_counters — id, organization_id (unique), year, last_sequence — SELECT FOR UPDATE
broadcast_templates — id, organization_id, name, category, language, body, variables (JSONB),
status, is_archived, created_by_user_id, created_at, updated_at
customer_segments — id, organization_id, name, description, rules (JSONB with allowlisted
field/operator), estimated_count, created_by_user_id, created_at, updated_at
broadcasts — id, organization_id, name, description, channel, status, audience_type,
segment_id, template_id, message_body, scheduled_at, started_at,
completed_at, recipient_count, sent_count, delivered_count, read_count,
failed_count, failure_reason, created_by_user_id, created_at, updated_at
broadcast_recipients — id, organization_id, broadcast_id, customer_id, status, sent_at,
delivered_at, read_at, failed_at
broadcast_timeline — id, organization_id, broadcast_id, event, detail, actor_user_id, created_atPhase 4A PostgreSQL Tables (Implemented)
whatsapp_connections — id, organization_id, display_name, phone_number_id, waba_id, phone_number,
encrypted_access_token (AES-256-GCM ciphertext), credential_key_version,
status (pending/active/disabled/error), environment (test/live),
last_error_code, last_error_detail, last_error_at,
verified_at, activated_at, disabled_at, version, created_at, updated_at
meta_webhook_deliveries — id, organization_id (nullable), received_at, payload_hash (SHA-256, unique),
processed, skipped, error_summary, processing_duration_ms, created_at
meta_webhook_events — id, organization_id (nullable), delivery_id FK → meta_webhook_deliveries,
wamid (nullable), event_type (message/status/other), skipped, created_at
message_provider_attempts — id, message_id FK → messages, connection_id FK → whatsapp_connections,
attempt_number, succeeded, provider_message_id, error_code, error_summary,
http_status_code, created_atAlso extended:
messages — added: provider (whatsapp_cloud/mock), provider_connection_id (nullable FK),
provider_message_id (wamid, nullable), provider_error_code, provider_status_timestamp (unix int),
simulated (boolean, default True)
conversations — added: whatsapp_connection_id (nullable FK → whatsapp_connections)Key constraints:
meta_webhook_deliveries.payload_hashis UNIQUE (dedup primary key for idempotent processing).encrypted_access_tokenstores only{key_version}:{nonce_b64}:{ciphertext_b64}— never plaintext.message_provider_attemptshas no update path; append-only audit log.
Hosted PostgreSQL Later (Remaining)
Expected long-term tables:
organizations users organization_members roles permissions sessions whatsapp_accounts whatsapp_phone_numbers contacts contact_tags custom_fields conversations conversation_assignments messages message_statuses message_templates webhook_events products product_variants catalogues orders order_items payments broadcasts broadcast_recipients tickets notifications audit_logs
Every tenant-owned table must include organization_id.
Desktop SQLite Cache (Implemented, Read-Only Slice)
This is a Tauri-local SQLite database, entirely separate from the backend PostgreSQL schema documented above. It lives in the OS-managed per-app-identifier app-data directory (sqlite:cache.db, resolving to %APPDATA%\xyz.archeaxon.vendale\cache.db on Windows), registered via tauri-plugin-sql in desktop/src-tauri/src/lib.rs.
CREATE TABLE IF NOT EXISTS cache_entries ( entity_type TEXT NOT NULL, cache_key TEXT NOT NULL, payload_json TEXT NOT NULL, cached_at INTEGER NOT NULL, PRIMARY KEY (entity_type, cache_key) );
One generic table serves all 9 cached entities (Customers, Orders, Conversations, Products, Catalogues, Tickets, Broadcasts, Templates, Segments) identically — each holds only the most recently successful list-view API response (payload_json), replaced wholesale on every successful network fetch (desktop/src/services/cache/entityCache.ts). There is no per-filter caching and no TTL in this slice.
Constraints:
- API-mode only; mock mode never touches this database.
- Never contains access tokens, refresh tokens, Meta secrets, or any credential — only the already domain-typed list-response bodies for the 9 entities above.
- Read-only from the application's perspective in this slice: nothing written offline is queued or replayed. Drafts and an offline write/sync queue remain future, unauthorized work.
Phase 4B WhatsApp Template Broadcast Tables
Phase D5 UI note: the desktop Templates page uses broadcast_templates only. Provider-synced WhatsApp templates are stored separately in whatsapp_provider_templates.
Added in Alembic revision a6f2d4c9b817 and acceptance-hardened in f4b7c9d2e601.
whatsapp_provider_templates id, organization_id, connection_id, provider_template_id, name, language, category, status, components JSONB, variable_schema JSONB, body_text, header_text, footer_text, is_supported_text_template, unsupported_reason, quality_rating, synced_at, last_seen_at, removed_at, raw_provider_template, created_at, updated_at customer_channel_consents id, organization_id, customer_id, channel, purpose, status, source, external_reference, captured_at, revoked_at, actor_user_id, notes, created_at, updated_at broadcast_dispatch_jobs id, organization_id, broadcast_id, recipient_id, connection_id, status, available_at, leased_until, lease_heartbeat_at, lease_owner, attempts, max_attempts, last_error_code, last_error_summary, completed_at, cancelled_at, created_at, updated_at
Extended tables:
broadcasts delivery_mode, purpose, whatsapp_connection_id, provider_template_id, provider_template_snapshot JSONB, variable_mappings JSONB, selected_customer_ids_snapshot JSONB, audience_snapshot_hash, template_graph_api_version, last_prepared_at, queued_at, paused_at, cancelled_at, requires_attention_at, eligible_count, ineligible_count, queued_count, accepted_count, retry_scheduled_count, cancelled_count, ambiguous_count broadcast_recipients eligibility_status, ineligibility_reason, customer_identity_id, whatsapp_connection_id, provider_template_id, message_id, normalized_recipient, masked_recipient, consent_status_snapshot, template_parameters JSONB, template_snapshot JSONB, provider_message_id, attempt_count, next_retry_at, last_error_code, last_error_summary, queued_at, accepted_at, cancelled_at, ambiguous_at, version
Important constraints:
- Provider templates are unique per connection by provider template id and by
(connection_id, name, language). - Customer consent is unique per
(organization_id, customer_id, channel, purpose). - Each broadcast recipient has at most one active durable dispatch job.
- Raw Meta access tokens are still stored only as encrypted connection credentials; dispatch jobs and recipient snapshots never store tokens.
whatsapp_connections.broadcast_last_sent_atcoordinates per-connection Phase 4B rate limiting across workers.lease_heartbeat_atrecords the latest worker claim heartbeat for stale-worker diagnostics and reclaim tests.created_atandupdated_aton provider templates, consents, and dispatch jobs have databasenow()defaults so Alembic-created databases and service-created rows behave consistently.
Phase 4C Media And Interactive Messaging Tables
Added in Alembic revision c8a1d5e4f902, extended by d3e9b1f6a204 for durable media ingestion jobs, extended by e8f6a2d4c901 for media/interactive provider-template broadcast metadata, and extended by f9a1b2c3d4e5 for interactive reply correlation.
media_assets id, organization_id, provider_connection_id, message_id, created_by_user_id, direction, media_type, original_filename, safe_filename, content_type, byte_size, sha256_hex, storage_provider, storage_key, provider_media_id, provider_media_url_fetched_at, provider_media_expires_at, upload_state, download_state, scan_state, quarantine_state, retention_expires_at, last_error_code, last_error_summary, safe_metadata, created_at, updated_at media_ingestion_jobs id, organization_id, media_asset_id, provider_connection_id, status, available_at, leased_until, lease_heartbeat_at, lease_owner, attempts, max_attempts, last_error_code, last_error_summary, completed_at, cancelled_at, created_at, updated_at
Extended tables:
messages context_provider_message_id, interactive_type, interactive_reply_id, interactive_title, interactive_description, interactive_payload JSONB, source_message_id FK -> messages, source_provider_attempt_id FK -> message_provider_attempts, broadcast_recipient_id FK -> broadcast_recipients, interactive_correlation_status, interactive_correlation_detail whatsapp_provider_templates button_schema JSONB, component_schema JSONB, header_format, required_media_type, is_supported_media_template, is_supported_interactive_template broadcasts media_asset_id FK -> media_assets
Important constraints:
media_assets.organization_idis mandatory and indexed for tenant isolation.storage_keyis an opaque tenant-scoped key, not a raw filesystem path.- Provider bearer tokens, signed provider media URLs, cookies, and temporary local paths must not be stored.
- Inbound media webhook processing creates pending
MediaAssetrows without blocking webhook delivery on later media download/scanning work. - Interactive replies preserve safe provider metadata and context WAMIDs; backend correlation is scoped by organization and provider connection before linking source messages, provider attempts, or BroadcastRecipients. No automated business action is triggered by reply IDs in Phase 4C.
- Each media asset has at most one active ingestion job.
- Production object storage stores private objects only; snapshots and jobs never store access tokens or public signed URLs.
- Media-header broadcast snapshots freeze media asset metadata and provider-template component metadata; full desktop monitoring and native media-header Broadcast acceptance remain incomplete. Partial native evidence exists for single-conversation outbound/inbound image media and source-message interactive correlation.
Production Dashboard Operational Tables
Added in Alembic revision a4d1c8b2e905.
tasks id, organization_id, title, description, status, priority, due_at, assigned_to_user_id, created_by_user_id, completed_at, cancelled_at, related_entity_type, related_entity_id, created_at, updated_at automation_rules id, organization_id, name, description, status, trigger_type, conditions JSONB, actions JSONB, created_by_user_id, last_modified_by_user_id, last_validated_at, last_error_code, last_error_summary, created_at, updated_at automation_executions id, organization_id, automation_id, trigger_event JSONB, status, started_at, finished_at, attempt_count, safe_error_code, safe_error_summary, affected_entities JSONB, idempotency_key, created_at ai_provider_configs id, organization_id, provider_type, status, encrypted_api_key, credential_key_version, model_name, enabled, pii_redaction_enabled, retention_days, last_error_code, last_error_summary, last_checked_at, created_at, updated_at
Indexes are organization-scoped for dashboard and list queries, including status, created/updated time, task due date, task assignee/status, task priority/status, automation trigger/status, and execution status/start time.
Phase D5 Validation
Phase D5 added no database migrations. Alembic upgrade/current/check passed against disposable validation databases, including a native-smoke database that was dropped after evidence capture. No production-named database was created or modified.
SaaS Commercialization Tables (2026-07-31)
Added in Alembic revisions d5b2e7c41f83 (billing) and e7c4a91b3d20 (AI and automation execution).
organization_subscriptions id, organization_id (UNIQUE — one row per org), status (pending_setup|active|past_due|grace|read_only|cancelled), setup_fee_paid_at, setup_fee_reference, current_period_start, current_period_end, paystack_customer_code, paystack_subscription_code, paystack_email_token, past_due_since, grace_expires_at, read_only_since, cancelled_at, cancellation_reason, version, created_at, updated_at billing_events append-only; no update or delete path exists anywhere id, organization_id (nullable — an unresolvable payload is still recorded), event_type, source, paystack_reference, payload_hash (UNIQUE — webhook idempotency), previous_status, new_status, amount_kobo, currency, processed, error_summary, payload JSONB invoices id, organization_id, kind (setup_fee|subscription), status (pending|paid|failed|refunded), amount_kobo, currency, paystack_reference, description, period_start, period_end, paid_at, failed_at, failure_reason UNIQUE (organization_id, paystack_reference) automation_trigger_events the missing input queue for automations id, organization_id, trigger_type, payload JSONB, entity_type, entity_id, actor_user_id, status (queued|leased|processed|failed|cancelled), available_at, leased_until, lease_heartbeat_at, lease_owner, attempts, max_attempts, processed_at, matched_rule_count, last_error_code, last_error_summary message_drafts AI suggestions awaiting human approval id, organization_id, conversation_id, source_message_id, body, status (pending|accepted|discarded|superseded), source (copilot|automation), requested_by_user_id, resolved_by_user_id, resolved_at, model_used, input_tokens, output_tokens
Extended:
ai_provider_configs axon_organization_id the tenant's own Axon gateway organization
Design notes:
- Money is stored as integer kobo/cents, never floats, to avoid rounding.
organization_subscriptionsis the single source of truth for whether an org may write.read_onlyblocks mutations but never reads, so a lapsed tenant is never locked out of its own data.billing_events.organization_idis nullable on purpose: a webhook whose tenant cannot be resolved is still recorded (withprocessed=false) rather than silently dropped.message_draftsis deliberately NOT part ofmessages. Nothing in it has been sent, and keeping it separate means no send, list, or reconciliation path can pick up unreviewed AI output.automation_trigger_eventscarries the same lease columns asbroadcast_dispatch_jobsso workers can claim rows withSELECT ... FOR UPDATE SKIP LOCKED.automation_executionsremains the result log; before this change nothing in the codebase ever wrote a row to it.ai_provider_configs.encrypted_api_keyholds{key_version}:{nonce}:{ciphertext}under a key distinct from the WhatsApp credential key; startup rejects reused key material across secret classes.
WhatsApp Flow Table (2026-07-31, Phase F1-F2)
Added in Alembic revision f2bb5f6da0a8. Purely additive — no existing column or table is touched.
whatsapp_flow_definitions id, organization_id, connection_id (FK -> whatsapp_connections), name, status (draft|published|deprecated), flow_json JSONB, initial_screen, provider_flow_id (nullable until first publish), version, last_error_code, last_error_summary, published_at, deprecated_at, created_by_user_id, created_at, updated_at UNIQUE (connection_id, name)
Mirrors whatsapp_provider_templates' shape (a synced/published external object wrapped in a local status) rather than inventing a new pattern. provider_flow_id stays NULL until the first successful publish creates the Flow on Meta's side; version increments on every draft edit so a published Flow's authored history is preserved even though only drafts may be edited.
WhatsApp Flow Keypair Table (2026-07-31, Phase F3)
Added in Alembic revision 94307e83dcc8. Purely additive.
whatsapp_flow_keypairs id, organization_id, connection_id (FK -> whatsapp_connections, UNIQUE — one keypair per connection), public_key_pem (plaintext SPKI PEM; not a secret — must be uploaded to Meta), encrypted_private_key, encrypted_passphrase (both AES-256-GCM via credential_encryption.py, field_name="flow_private_key" / "flow_private_key_passphrase"), credential_key_version, uploaded_to_meta_at, created_at, updated_at
Used by the encrypted Flow data-exchange endpoint (app/api/whatsapp_flow_endpoint.py) to decrypt inbound requests and encrypt responses per Meta's documented RSA-OAEP + AES-128-GCM scheme. Rotating a connection's keypair (WhatsAppFlowKeyPairService.generate_for_connection()) overwrites the same row rather than inserting a new one — Meta only ever trusts the most recently uploaded public key per connection, so retaining old rows would serve no purpose. Rotation invalidates Meta's cached public key until the new one is re-uploaded, which is the documented client behavior (the next request encrypted with the old key correctly fails with HTTP 421).
Instagram Connection Table (2026-07-31, Phase I1-I2)
Added in Alembic revision b2222fddee2e. Purely additive — sibling to whatsapp_connections, not a shared/generalized connection table, per the locked Phase I architecture decision.
instagram_connections id, organization_id, display_name, ig_user_id, ig_username, status (draft|configured|verified|active|error|disabled), environment (test|production), encrypted_access_token, credential_key_version, last_error_code, last_error_detail, last_error_at, verified_account_name, verified_at, subscription_status, subscribed_at, activated_at, disabled_at, created_by_user_id, version, created_at, updated_at UNIQUE (organization_id, ig_user_id)
encrypted_access_token is AES-256-GCM via credential_encryption.py under WHATSAPP_CREDENTIAL_ENCRYPTION_KEY (same secret class as the WhatsApp connection token) with field_name="ig_access_token" — a distinct AAD binding from WhatsApp's field_name="access_token", never sharing key material across rows or tables. This table has no phone number, no WABA concept, and no per-connection webhook-subscription state beyond subscription_status/subscribed_at (set by activate(), since Instagram Login app webhook subscription is an app-level Meta developer console setting, not a per-connection API call).
Instagram Parallel Connection Columns (2026-07-31, Phase I3, I5)
Added in Alembic revisions 3c6933adc660 (Conversation/Message) and 3d96639a081e (MessageProviderAttempt). Purely additive — each is a parallel nullable FK alongside an existing WhatsApp-shaped column that is left completely untouched, per the locked Phase I architecture decision.
conversations.instagram_connection_id FK -> instagram_connections.id
(alongside whatsapp_connection_id)
messages.instagram_connection_id FK -> instagram_connections.id
(alongside provider_connection_id)
message_provider_attempts.instagram_connection_id FK -> instagram_connections.id
(alongside connection_id, which
is FK'd to whatsapp_connections.id
only and could not be reused)None of these columns is indexed — matching whatsapp_connection_id and provider_connection_id, neither of which is indexed either (a precedent from the c2e7f3b5a1d9 "reconcile FK-column indexes" migration). A row has at most one of the two connection FKs populated, matching its channel/provider value — enforced at the service layer, not a DB constraint, matching this codebase's existing convention for enum-as- free-text columns.
CONVERSATION_CHANNEL (in app/models/conversation.py) gained "instagram_dm" alongside "whatsapp_mock", "whatsapp_cloud", "manual".
Platform-Wide Search Indexes (2026-08-19, Platform Admin V3 part 1)
Added in Alembic revision 213afde5a3a9. Purely additive: three standalone, non-org-prefixed indexes needed for genuine cross-tenant lookup by the new GET /platform-admin/search endpoint.
ix_orders_order_number orders.order_number (standalone) ix_customers_normalized_phone customers.normalized_phone (standalone) ix_messages_provider_message_id messages.provider_message_id (standalone)
Each of these tables already had an org-prefixed composite index on the same column (ix_orders_org_status and friends do not cover this; the relevant existing indexes are uq_order_org_number on (organization_id, order_number) and ix_customers_org_phone on (organization_id, normalized_phone)), but an org-prefixed composite cannot be used efficiently when the organization is unknown, which is exactly the platform-admin search case. messages.provider_message_id had no index at all before this migration.
Note that orders.order_number is unique only per (organization_id, order_number), not globally: the same order number string can legitimately exist in multiple workspaces, so a platform-wide order search can return more than one match and must always show the owning workspace alongside each hit.
Platform Audit Foundation (2026-08-19, Platform Admin V3 Part 2)
Added in Alembic revision a7f2c9e1b4d6, extending the platform_audit_events table (created earlier by c4a1f8d2e7b9, still uncommitted at the time of this change). Purely additive: new nullable/defaulted columns and two new indexes, no existing column touched.
platform_audit_events (extended)
actor_email VARCHAR(255) NOT NULL DEFAULT '' snapshotted, not FK-derived,
so the record stays readable
after the user is edited
actor_role VARCHAR(50) NOT NULL DEFAULT '' the actor's own organization
role_type at action time; there
is no separate platform-role tier
before_state_json TEXT NULL full before-state snapshot,
redacted at write time
after_state_json TEXT NULL full after-state snapshot,
redacted at write time
user_agent VARCHAR(500) NULL from the request's User-Agent header
ix_platform_audit_actor_created (actor_id, created_at)
ix_platform_audit_action_created (action, created_at)before_state_json/after_state_json are a full-snapshot representation distinct from the existing changes_json per-field diff list (kept unchanged for backward compatibility with the two call sites that already used it). For today's two call sites (platform.workspace.updated, platform.user.updated) both representations are populated from the same underlying AuditChange list, so they always agree; before/after state exists as its own field pair because not every future audited action (job retry, automation pause, broadcast stop) will be a simple field update.
AuditService.append_platform() redacts any changes/before_state/ after_state key or field name matching a secret-shaped pattern (token, secret, password, credential, authorization, cookie, jwt, key, payload, stack, trace) to the literal string "REDACTED" before the row is persisted, so a call site cannot accidentally write a real secret value into this table even if it tries. request_id is populated on every row written from this point forward, via RequestIDMiddleware (backend/app/middleware/request_id.py, see docs/API_CONTRACT.md's "Request Correlation ID" section) -- rows written before that middleware existed still have request_id = null, a true historical fact, not backfilled.
platform_audit_events has no updated_at column and the router exposes no mutation route for it, matching the existing tenant-level audit_events table's immutability guarantee. Platform superusers are audited identically whether they act on themselves, another superuser, or an ordinary workspace; there is no actor-based exemption from the audit log.
Platform RBAC (2026-08-19, Platform Admin V3.3a)
Added in Alembic revision b3f6d1a9c2e4. Purely additive: three new tables plus one new nullable, FK column on users. Deliberately parallel to roles/permissions/role_permissions rather than a shared/generalized table: tenant roles are org-scoped (Role.organization_id), platform roles are global, a genuinely different concept.
platform_permissions id, key (unique, e.g. "platform.workspaces.manage"), label, group, is_high_risk, created_at, updated_at platform_roles id, name (unique), role_type (unique: platform_owner | platform_admin | security_admin | support_admin | billing_admin | read_only_auditor), description, is_system, created_at, updated_at platform_role_permissions role_id (FK -> platform_roles.id, CASCADE), permission_id (FK -> platform_permissions.id, CASCADE), composite PK users.platform_role_id FK -> platform_roles.id, ON DELETE SET NULL, nullable
users.platform_role_id is nullable by design, not an oversight: NULL means "legacy full access" when is_superuser=True (every platform admin created before this feature existed, until explicitly assigned a role), and simply "no platform role" when is_superuser=False. ON DELETE SET NULL means deleting a system role (not exposed via any route in this slice, but not blocked at the DB level either) safely degrades affected users to "no platform role" rather than failing the delete or leaving a dangling reference.
The migration seeds all 14 permission keys, all 6 system roles, and every role-permission mapping directly via connection.execute() with literal Python tuples (not imported from [redacted].py, since a migration must remain a stable historical snapshot even if the application-side catalogue module is later refactored), then backfills platform_role_id = <platform_owner's id> for every existing is_superuser=True user, preserving their access explicitly rather than relying on the NULL-fallback indefinitely. [redacted].py::ensure_platform_permission_catalogue() is a separate, idempotent reconciliation function (mirrors the tenant-side ensure_permission_catalogue()) used by the disposable test database (built via Base.metadata.create_all, which does not run Alembic data migrations) and available for future production reconciliation if the catalogue gains new keys after initial deployment.
Verified directly against a real schema (not just offline SQL rendering): 14 permissions / 6 roles / 49 role-permission rows seeded correctly, platform_owner holds all 14 keys, platform_admin holds 13 (all except platform.roles.manage), read_only_auditor holds exactly the 6 .read keys, the superuser backfill correctly links to platform_owner, and deleting a role correctly cascades its platform_role_permissions rows while setting dependent users.platform_role_id to NULL.
Auth Security Telemetry (2026-08-20, Platform Admin V3.3c-1)
Added in Alembic revision d4e8b3c1a962. Purely additive: one new table plus new nullable columns on two existing tables.
auth_security_events
id, event_type (fixed vocabulary, see AUTH_SECURITY_EVENT_TYPES in
app/models/auth_security_event.py), user_id (FK -> users.id, SET NULL,
nullable -- a failed login against an unknown email has no user),
organization_id (FK -> organizations.id, SET NULL, nullable),
session_id (FK -> refresh_tokens.id, SET NULL, nullable),
attempted_email (nullable -- populated on every login attempt, even when
no user could be resolved, for brute-force correlation),
attempted_organization_slug (nullable), target_is_admin (bool, default
false -- stamped at write time from the resolved/attempted account's
is_superuser flag), ip_address, user_agent, detail_json (small safe JSON:
reason codes, counts -- redacted at write time, never a password/token/hash),
retention_expires_at (not null, computed at insert as
now + AUTH_TELEMETRY_RETENTION_DAYS), created_at
Indexes: event_type, user_id, attempted_email, retention_expires_at,
created_at, (event_type, created_at), (attempted_email, ip_address, created_at)
refresh_tokens (extended)
ip_address, user_agent captured at creation (login/register), carried
forward unchanged across rotation
last_seen_at stamped to now() on every rotation
revoked_at set alongside is_revoked=True everywhere that
flag is set (logout, logout-all, single-session
revoke, platform-admin revoke, family-reuse
revoke)
users (extended)
mfa_enabled bool, default false -- V3.3c-1 placeholder only. Nothing
sets it yet; the secret, enrollment timestamp, and
recovery-code table are deferred to the migration that
ships real MFA enroll/verify behavior (V3.3c-2), not added
inert here.Design notes:
auth_security_eventsis a separate ledger fromplatform_audit_eventsandaudit_events(seedocs/ARCHITECTURE.md's V3.3c-1 entry for why all three stay distinct). It records every user's login/session activity, not just platform administrators;target_is_adminlets Security Center filter to the administrator-scoped subset it actually surfaces without a join back tousers.- No update or delete route exists for this table; it is append-only by the same convention as
platform_audit_eventsand tenantaudit_events. AuthTelemetryRetentionServicedeletes rows whereretention_expires_at <= now()in batches (AUTH_TELEMETRY_RETENTION_BATCH_SIZE);[redacted].pymirrorsmedia_retention_worker.py's shape but is not yet wired to a systemd unit or cron schedule.
MFA And Step-Up (2026-08-20, Platform Admin V3.3c-2)
Added in Alembic revision f1a7c9e4d5b3. Purely additive: three new tables plus new nullable columns on users (replacing the V3.3c-1 placeholder mfa_enabled default with real values once MfaService sets them).
users (extended, mfa_enabled column now actually set)
mfa_secret_encrypted TEXT nullable -- {key_version}:{nonce_b64}:{ciphertext_b64}
via SecretsProvider (MFA_CREDENTIAL_ENCRYPTION_KEY, a
distinct key from the WhatsApp/AI credential keys),
NULL until enrollment starts
mfa_enrolled_at nullable, set once complete_enrollment() succeeds
mfa_last_verified_at nullable, updated on every successful challenge
(TOTP or recovery code) at login or step-up time
mfa_recovery_codes
id, user_id (FK -> users.id, CASCADE), code_hash (fast SHA-256, not
Argon2 -- these are high-entropy generated secrets, not low-entropy
user-chosen passwords), used_at (nullable; NULL means still valid),
created_at
Unique per (user_id, code_hash). A full reset (self-disable or admin
reset) deletes all rows for the user; regenerate deletes the old set and
inserts a fresh one in the same transaction.
mfa_challenge_sessions
id, user_id (FK -> users.id, CASCADE), token_hash (sha256 of the opaque
raw token; only the hash is ever persisted, mirroring refresh_tokens),
purpose ("challenge" | "enroll", MFA_CHALLENGE_SESSION_PURPOSES),
expires_at, consumed_at (nullable; set once resolved so a token cannot be
replayed), ip_address, user_agent, created_at
step_up_sessions
id, user_id (FK -> users.id, CASCADE), token_hash (sha256, same pattern),
expires_at, ip_address, user_agent, created_at
Deliberately no consumed_at / single-use flag: valid for repeated use
within its TTL window (default 10 minutes, STEP_UP_SESSION_TTL_MINUTES),
matching "a short elevated time budget" rather than "one action only."Design notes:
- Neither
mfa_challenge_sessionsnorstep_up_sessionsever stores a raw token, a password, or a TOTP code -- only the sha256 hash of the opaque session token itself, identical in shape torefresh_tokens.token_hash. - Both tables are DB-checked on every use (
resolve_mfa_challenge_session(),StepUpService.require_valid()), not cached, so revocation/expiry takes effect immediately without waiting on any JWT's lifetime. mfa_recovery_codesrows are never updated in place beyond stampingused_at-- a used code is retained (not deleted) so "was this code already used" stays answerable without a separate audit trail.- Retention:
mfa_challenge_sessionsandstep_up_sessionsare both naturally short-lived and cheap to prune (expires_at-bounded), but no worker currently deletes expired rows from either table -- tracked as part of the same deployment-wiring gap documented forauth_security_eventsabove (seedocs/ARCHITECTURE.md's V3.3c-2 "Deployment backlog" note).
Platform Security-Manage Permission (2026-08-20, Platform Admin V3.3c-2)
Added in Alembic revision a2c8f5e1b7d4. Purely additive: one new platform_permissions row (platform.security.manage) plus its platform_role_permissions grants to platform_owner, platform_admin, and security_admin (looked up by role_type, not re-created). Gates POST /platform-admin/users/{id}/mfa/reset via RequirePlatformSecurityManage (app/api/dependencies/auth.py), distinct from the existing platform.security.read permission that already gated the Security Center's two read-only list/overview endpoints.
Feature Flags (2026-08-20, Platform Admin V3.4)
Added in Alembic revisions c8e2f4a916b7 (tables) and d3f7a2c5e819 (permission). Purely additive.
feature_flags
id, key (unique, indexed, lowercase snake_case), name, description,
enabled (bool, default false -- the global/default state), rollout_percentage
(0-100, default 0), classification ("normal" | "operational"), status
("active" | "archived"), created_by (FK -> users.id, SET NULL),
updated_by (FK -> users.id, SET NULL), created_at, updated_at
feature_flag_targets
id, feature_flag_id (FK -> feature_flags.id, CASCADE), target_type
("workspace" | "user" | "platform_role"), target_id (plain string --
deliberately not a typed FK, since its meaning depends on target_type:
an organization id, a user id, or a platform_role_type literal), enabled,
created_at
UNIQUE (feature_flag_id, target_type, target_id)Design notes:
target_idis intentionally untyped (a string, not three separate nullable FK columns) because exactly one of three unrelated reference spaces applies depending ontarget_type, and a target row is meaningless without knowing which. Referential existence (the workspace/user/platform role actually exists) is validated at the service layer (_validate_target_reference()), not enforced by the database.- No hard-delete route exists for
feature_flagsitself: retiring a flag setsstatus="archived"rather than deleting the row, preserving its historical targets and rollout percentage for audit purposes. Individualfeature_flag_targetsrows ARE hard-deleted on removal (DELETE .../targets/{target_id}) -- an override is disposable per-subject state, not part of the flag's own retirement history. enabledonFeatureFlagis the step-5 global default in the resolution order (seedocs/ARCHITECTURE.md's "Platform Admin V3.4" entry for the full 5-step precedence); it is deliberately independent ofrollout_percentage-- a flag can be globallyenabled=falsewhile still grantingtrueto a percentage-hashed subset of workspaces via step 4, and a flag can be globallyenabled=truewhile an explicitenabled=falsetarget still overrides a single workspace/user via steps 1-3.platform.feature_flags.read(migrationd3f7a2c5e819) is granted toplatform_owner,platform_admin,support_admin,read_only_auditor-- the same four roles that hold every other.readpermission in the platform RBAC catalogue.platform.feature_flags.managealready existed as a reserved key (seeded byb3f6d1a9c2e4) with no route behind it until this migration's application code shipped.
Platform Channels-Read Permission (2026-08-20, Platform Admin V4.1)
Added in Alembic revision e6a3f8c2d914. Purely additive: one new platform_permissions row (platform.channels.read) plus its platform_role_permissions grants to platform_owner, platform_admin, support_admin, and read_only_auditor (looked up by role_type, not re-created) -- mirroring platform.feature_flags.read's exact grant set. No new table: the Channel Control Center reads directly from the existing whatsapp_connections, instagram_connections, facebook_page_connections, messages, and message_provider_attempts tables via their existing parallel-FK columns (see the Phase I "Instagram Parallel Connection Columns" entry above), with no schema change of its own.
Platform Broadcasts Permissions (2026-08-20, Platform Admin V4.4)
Added in Alembic revision f4c2a8e719b6. Purely additive: two new platform_permissions rows (platform.broadcasts.read, platform.broadcasts.manage) plus their platform_role_permissions grants to existing roles (looked up by role_type, not re-created). Unlike V4.1-V4.3's channel-visibility permissions (all reused platform.channels.read), this is the first V4 capability that mutates tenant data, so it gets a dedicated permission pair rather than riding an existing read-only key. .read is granted to platform_owner, platform_admin, support_admin, read_only_auditor (the standard four .read-holding roles); .manage is granted to platform_owner, platform_admin, support_admin (the same three roles that already hold platform.workspaces.manage and platform.automations.manage), not security_admin or billing_admin.
No new table: every mutation reads and writes the existing broadcasts table via the existing BroadcastService (built in Phase 4B), with no schema change of its own.
Operational Freeze (2026-08-20, Platform Admin V4.5)
No new migration. Reuses the existing feature_flags/feature_flag_targets tables (V3.4) exactly as they already exist: freezing a workspace inserts one feature_flag_targets row (target_type="workspace", enabled=false) under either an existing flag row or a newly-created one; unfreezing deletes that row. new_automation_runs and outbound_broadcasts are the same flag rows V3.4 and V4.4 already introduced (no new key for either); workspace_outbound_messages is the one genuinely new flag key, created on first use exactly like any other operator-created flag, not seeded by any migration. See docs/ARCHITECTURE.md's "Platform Admin V4.5" entry for how each of the three background code paths resolves the per-workspace override.
Platform AI-Read Permission (2026-08-21, Platform Admin V4.6)
Added in Alembic revision 67f5fa5695ad. Purely additive: one new platform_permissions row (platform.ai.read) plus its platform_role_permissions grants to platform_owner, platform_admin, support_admin, read_only_auditor (looked up by role_type, not re-created) -- mirroring platform.channels.read's exact grant set. No new table: the AI Provider/Model Registry reads directly from the existing ai_provider_configs table (built in the SaaS Commercialization S1/S3 phase; see the "SaaS Commercialization" entries above), joined with organizations for workspace name/slug, with no schema change of its own.
AI Usage Accounting (2026-08-21, Platform Admin V4.7)
No new migration, no new table. ai_usage_summary() reads directly from the existing message_drafts table (built in the SaaS Commercialization S5 phase) with a GROUP BY status/GROUP BY source aggregate and a SUM(input_tokens)/SUM(output_tokens). get_workspace_ai_usage() touches no table at all beyond a single organizations lookup for workspace name/slug -- its real data comes from a live call to the Axon gateway via the existing AiService.account_usage(), not from any local table. Reuses V4.6's platform.ai.read permission; no new key.
Platform AI-Manage Permission (2026-08-21, Platform Admin V4.8)
Added in Alembic revision 4ae40777760f. Purely additive: one new platform_permissions row (platform.ai.manage, is_high_risk=true) plus its platform_role_permissions grants to platform_owner, platform_admin, support_admin (looked up by role_type, not re-created) -- the same three-role grant set platform.broadcasts.manage (V4.4) already uses, deliberately excluding billing_admin, security_admin, read_only_auditor. No new table: disabling/enabling AI Copilot for a workspace writes to the existing ai_provider_configs table via the existing tenant-facing AiService.update_config(), with no schema change of its own.
Platform Analytics-Read Permission (2026-08-21, Platform Admin V5.1)
Added in Alembic revision f30994e7cc90. Purely additive: one new platform_permissions row (platform.analytics.read) plus its platform_role_permissions grants to platform_owner, platform_admin, support_admin, read_only_auditor (looked up by role_type, not re-created) -- the standard four-role grant set every other .read permission in this catalogue uses. No new table: the Platform Analytics overview and trend endpoints read directly from the existing customers, conversations, messages, orders, and tickets tables, with no schema change of its own.
Platform Workspace-Health-Read Permission (2026-08-21, Platform Admin V5.3)
Added in Alembic revision d05146174330. Purely additive: one new platform_permissions row (platform.workspace_health.read) plus its platform_role_permissions grants to platform_owner, platform_admin, support_admin, read_only_auditor (looked up by role_type, not re-created) -- the standard four-role grant set every other .read permission in this catalogue uses. No new table: the workspace-health summary and list endpoints read directly from the existing organizations, organization_subscriptions, messages, and tickets tables, with no schema change of its own. health_status is computed at query time from those tables' existing columns, never stored.
Platform Support-Cases-Read Permission (2026-08-21, Platform Admin V5.5)
Added in Alembic revision 4a36b41d79c0. Purely additive: one new platform_permissions row (platform.support_cases.read) plus its platform_role_permissions grants to platform_owner, platform_admin, support_admin, read_only_auditor (looked up by role_type, not re-created) -- the standard four-role grant set every other .read permission in this catalogue uses. No new table: the support-cases summary and list endpoints read directly from the existing tickets table (joined to organizations for workspace name/slug), with no schema change of its own. age_days and is_overdue are computed at query time from Ticket.created_at/.due_at/.status, never stored.
Platform Workspace-Timeline-Read Permission (2026-08-21, Platform Admin V5.4)
Added in Alembic revision 9c2e5b8f4a17. Purely additive: one new platform_permissions row (platform.workspace_timeline.read) plus its platform_role_permissions grants to platform_owner, platform_admin, support_admin, read_only_auditor (looked up by role_type, not re-created) -- the standard four-role grant set every other .read permission in this catalogue uses. No new table: the workspace-timeline endpoint reads directly from the existing organizations, billing_events, and platform_audit_events tables (the latter filtered by target_organization_id), with no schema change of its own. The tenant audit_events table is deliberately never read by this endpoint -- see docs/ARCHITECTURE.md's V5.4 entry for why.
Platform Incidents-Read Permission (2026-09-02, Platform Admin V5)
Added in Alembic revision b7d9f3a1c5e8. Purely additive: one new platform_permissions row (platform.incidents.read) plus its platform_role_permissions grants to platform_owner, platform_admin, support_admin, read_only_auditor (looked up by role_type, not re-created) -- the standard four-role grant set every other .read permission in this catalogue uses.
No new table. The incidents endpoints are derived views over existing failure rows (automation_trigger_events, broadcast_dispatch_jobs, media_ingestion_jobs with status = 'failed'; meta_webhook_events with processing_error IS NOT NULL; message_provider_attempts with succeeded = false), clustered in application code by kind + workspace + error code within a 24-hour window. See docs/API_CONTRACT.md's V5 section for the derivation contract.
Practical guides live in the help center; release history in the changelog.