# Database Schema 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: ```text 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) ```text 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_at ``` ## Phase 3C PostgreSQL Tables (Implemented) ```text 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_featured ``` ## Phase 3D PostgreSQL Tables (Implemented) ```text 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_at ``` ## Phase 4A PostgreSQL Tables (Implemented) ```text 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_at ``` Also extended: ```text 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_hash` is UNIQUE (dedup primary key for idempotent processing). - `encrypted_access_token` stores only `{key_version}:{nonce_b64}:{ciphertext_b64}` — never plaintext. - `message_provider_attempts` has no update path; append-only audit log. ## Hosted PostgreSQL Later (Remaining) Expected long-term tables: ```text 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`. ```sql 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`. ```text 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: ```text 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_at` coordinates per-connection Phase 4B rate limiting across workers. - `lease_heartbeat_at` records the latest worker claim heartbeat for stale-worker diagnostics and reclaim tests. - `created_at` and `updated_at` on provider templates, consents, and dispatch jobs have database `now()` 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. ```text 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: ```text 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_id` is mandatory and indexed for tenant isolation. - `storage_key` is 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 `MediaAsset` rows 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`. ```text 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). ```text 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: ```text 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_subscriptions` is the single source of truth for whether an org may write. `read_only` blocks mutations but never reads, so a lapsed tenant is never locked out of its own data. - `billing_events.organization_id` is nullable on purpose: a webhook whose tenant cannot be resolved is still recorded (with `processed=false`) rather than silently dropped. - `message_drafts` is deliberately NOT part of `messages`. 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_events` carries the same lease columns as `broadcast_dispatch_jobs` so workers can claim rows with `SELECT ... FOR UPDATE SKIP LOCKED`. `automation_executions` remains the result log; before this change nothing in the codebase ever wrote a row to it. - `ai_provider_configs.encrypted_api_key` holds `{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. ```text 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. ```text 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. ```text 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. ```text 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. ```text 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. ```text 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. ```text 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 = ` 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. ```text 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_events` is a separate ledger from `platform_audit_events` and `audit_events` (see `docs/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_admin` lets Security Center filter to the administrator-scoped subset it actually surfaces without a join back to `users`. - No update or delete route exists for this table; it is append-only by the same convention as `platform_audit_events` and tenant `audit_events`. - `AuthTelemetryRetentionService` deletes rows where `retention_expires_at <= now()` in batches (`AUTH_TELEMETRY_RETENTION_BATCH_SIZE`); `[redacted].py` mirrors `media_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). ```text 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_sessions` nor `step_up_sessions` ever stores a raw token, a password, or a TOTP code -- only the sha256 hash of the opaque session token itself, identical in shape to `refresh_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_codes` rows are never updated in place beyond stamping `used_at` -- a used code is retained (not deleted) so "was this code already used" stays answerable without a separate audit trail. - Retention: `mfa_challenge_sessions` and `step_up_sessions` are 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 for `auth_security_events` above (see `docs/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. ```text 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_id` is intentionally untyped (a string, not three separate nullable FK columns) because exactly one of three unrelated reference spaces applies depending on `target_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_flags` itself: retiring a flag sets `status="archived"` rather than deleting the row, preserving its historical targets and rollout percentage for audit purposes. Individual `feature_flag_targets` rows 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. - `enabled` on `FeatureFlag` is the step-5 global default in the resolution order (see `docs/ARCHITECTURE.md`'s "Platform Admin V3.4" entry for the full 5-step precedence); it is deliberately independent of `rollout_percentage` -- a flag can be globally `enabled=false` while still granting `true` to a percentage-hashed subset of workspaces via step 4, and a flag can be globally `enabled=true` while an explicit `enabled=false` target still overrides a single workspace/user via steps 1-3. - `platform.feature_flags.read` (migration `d3f7a2c5e819`) is granted to `platform_owner`, `platform_admin`, `support_admin`, `read_only_auditor` -- the same four roles that hold every other `.read` permission in the platform RBAC catalogue. `platform.feature_flags.manage` already existed as a reserved key (seeded by `b3f6d1a9c2e4`) 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.