# 02 — Database & Migration Map

Conventions:
- Engine InnoDB, `utf8mb4` / `utf8mb4_0900_ai_ci`, MySQL 8.4.
- `id` is `CHAR(36)` UUIDv7 unless noted (`bigint` = auto-increment unsigned big int for append-only logs).
- Timestamps are `DATETIME(6)` in UTC. `created_at`/`updated_at` everywhere, and `deleted_at` (soft delete) where noted with **SD**.
- Money is `BIGINT UNSIGNED` in **paise**, with a `currency CHAR(3)`.
- Enums are `VARCHAR(32)` backed by PHP enums (no MySQL `ENUM`, which keeps migrations simple).
- There are **no foreign keys across databases**. Cross-DB references (e.g. tenant `user_id` → master `users.id`) are plain indexed UUID columns, validated in the application.
- Column **M** = milestone in which the migration ships.

---

## A. Master database — `social_saas_master`

Migrations live in `database/migrations/master`. Run with `php artisan migrate --database=master --path=database/migrations/master`.

| Table | Key columns | Keys / indexes | M |
|-------|-------------|----------------|---|
| `users` | id, name, email, email_verified_at, password, two_factor_secret (enc), two_factor_recovery_codes (enc), two_factor_confirmed_at, status (`active/locked/disabled`), is_super_admin bool, timezone, locale, last_login_at, last_login_ip, remember_token, **SD** | UQ(email), IX(status) | M1 |
| `password_reset_tokens`, `sessions` | Laravel defaults. Sessions are stored in Redis in prod, so the table is only a fallback | | M1 |
| `tenants` | id, slug, name, status, plan_id, owner_user_id, timezone, locale, country, provisioned_at, suspended_at, suspension_reason, trial_ends_at, settings JSON, **SD** | UQ(slug), IX(status), IX(owner_user_id) | M1 |
| ~~`tenant_domains`~~ | Dropped (`2026_11_05_000100`): one address for all agencies, workspace chosen after sign-in | | M1 |
| `tenant_database_connections` | id, tenant_id, driver, host, port, database_name, username, secret_ref, secret_version, status (`pending/created/migrated/verified/failed/rotating`), schema_version, last_verified_at | UQ(tenant_id), UQ(host, database_name) | M1 |
| `tenant_users` | id, tenant_id, user_id, role (`owner/admin/manager/creator/approver/client/viewer`), status (`invited/active/suspended/revoked`), invited_by, invited_at, accepted_at, invitation_token_hash, invitation_expires_at | UQ(tenant_id, user_id), IX(user_id, status) | M1 |
| `tenant_provisioning_jobs` | id, tenant_id, step, state (`queued/running/completed/failed`), attempts, failure_reason, started_at, finished_at, meta JSON | IX(tenant_id, state) | M1 |
| `admin_audit_logs` | **bigint** id, actor_user_id, actor_type, action, tenant_id nullable, subject_type, subject_id, ip, user_agent, request_id, metadata JSON (redacted), created_at | IX(tenant_id, created_at), IX(actor_user_id, created_at), IX(action) | M1 |
| `system_settings` | id, name, type (`string/int/bool/json`), value, updated_by, updated_at | UQ(name) | M1 |
| `support_access_grants` | id, tenant_id, granted_by_user_id (tenant owner), support_user_id, reason, expires_at, revoked_at | IX(tenant_id, expires_at) | M1 |
| `plans` | id, code (`starter/professional/agency/enterprise`), name, is_public, sort, trial_days, status | UQ(code) | M1 (seed), M6 (UI) |
| `plan_prices` | id, plan_id, term (`monthly/annual`), amount_paise, currency, gateway_plan_ref, active | UQ(plan_id, term, currency) | M6 |
| `plan_features` | id, plan_id, feature_code (e.g. `clients.max`, `social_accounts.max`, `users.max`, `storage.gb`, `ai.credits.monthly`, `posts.scheduled.monthly`, `whitelabel`, `inbox`), limit_value BIGINT nullable (null = unlimited), enabled bool | UQ(plan_id, feature_code) | M1 (seed) |
| `tenant_feature_overrides` | id, tenant_id, feature_code, limit_value, enabled, reason, set_by, expires_at | UQ(tenant_id, feature_code) | M1 |
| `subscriptions` | id, tenant_id, plan_id, term, status (`trialing/active/past_due/grace/suspended/cancelled`), gateway, gateway_subscription_id, current_period_start, current_period_end, cancel_at, grace_ends_at | IX(tenant_id, status), UQ(gateway, gateway_subscription_id) | M6 (M1 creates trial row) |
| `payments` | id, tenant_id, subscription_id, gateway, gateway_payment_id, amount_paise, tax_paise, currency, status, paid_at, raw JSON (redacted) | UQ(gateway, gateway_payment_id) | M6 |
| `invoices` | id, tenant_id, number (sequential per FY), gstin_customer, place_of_supply, subtotal_paise, cgst_paise, sgst_paise, igst_paise, total_paise, currency, status, issued_at, pdf_path | UQ(number), IX(tenant_id) | M6 |
| `coupons`, `coupon_redemptions` | code, type (`percent/flat`), value, max_redemptions, valid_from/to; tenant_id, coupon_id | UQ(code), UQ(coupon_id, tenant_id) | M6 |
| `billing_webhook_events` | id, gateway, event_id, type, signature_valid, payload JSON, processed_at, error | UQ(gateway, event_id) | M6 |
| `usage_summaries` | id, tenant_id, period (`YYYY-MM` or `YYYY-MM-DD`), metric_name, total BIGINT, updated_at | UQ(tenant_id, period, metric_name) | M1 (table), M3+ (populated) |
| `platform_integrations` | id, provider (*integration* key: meta, threads, linkedin, google, x, tiktok, pinterest), environment, client_id, secret_ref, callback_url, review_status (`not_submitted/in_review/approved/rejected/n_a`), approved_scopes JSON, capability_version, enabled bool, notes | UQ(provider, environment) | M2 |
| `provider_capabilities` | id, provider, capability_version, account_type, action (`publish_text/publish_image/publish_video/publish_carousel/publish_reel/publish_story/schedule_native/delete_post/read_insights/read_comments/reply_comments/read_messages/reply_messages/read_reviews/reply_reviews`), status (`available/configurable/blocked/unavailable`), required_scopes JSON, limits JSON (char limits, media specs), docs_url, verified_at | UQ(provider, capability_version, account_type, action) | M2 |
| `publish_dispatch_index` | **bigint** id, tenant_id, post_target_id, post_version_id, idempotency_key, due_at, status (`pending/enqueued/done/cancelled`), enqueued_at, attempts, locked_until | UQ(idempotency_key), IX(status, due_at), IX(tenant_id, post_target_id) | M3 |
| `oauth_states` | id, state_hash, tenant_id, user_id, provider, pkce_verifier (enc), client_id (tenant), expires_at, consumed_at, result (enc: tokens + discovered accounts), result_token_hash, result_expires_at, linked_at | UQ(state_hash), UQ(result_token_hash), IX(expires_at) | M2 |
| `social_identity_links` | id, provider, provider_user_hash (HMAC of the provider user id), tenant_id, social_account_id | UQ(provider, hash, tenant, account), IX(provider, hash) | M2 |
| `inbound_webhooks` | **bigint** id, provider, external_event_id, type, signature_valid, payload (minimal), received_at, processed_at, error | UQ(provider, external_event_id) | M2 |
| `incidents` | id, title, severity, status, affected_providers JSON, started_at, resolved_at, body | IX(status) | M6 |
| `backup_runs` | id, tenant_id nullable, type (`tenant_logical/cluster_snapshot/restore_test`), status, location, size_bytes, checksum, started_at, finished_at, error | IX(tenant_id, started_at) | M1 (table), M6 (dashboard) |
| `features` | Laravel Pennant store (scope = `Tenant:{id}`) | | M1 |
| `jobs_batches`, `failed_jobs` | Laravel defaults (failed jobs carry tenant_id in payload tags) | | M1 |

Note: `publish_dispatch_index` and `oauth_states` are additions to the spec's master table list. Both hold **references only**, no post content or tokens in plaintext. They are required by spec §7 ("central scheduling index") and §2.2 (state/PKCE).

---

## B. Tenant database — `social_tenant_{0001…}`

Migrations live in `database/migrations/tenant`. Run with `php artisan tenants:migrate [--tenant=uuid] [--pretend]`. The command iterates active tenants, records `schema_version` in master, and stops on the first failure unless `--continue-on-error` is passed.

### B1. Settings, teams & clients (M1 core, M2 full)

| Table | Key columns | Keys / indexes | M |
|-------|-------------|----------------|---|
| `tenant_settings` | key, value JSON, updated_by | UQ(key) | M1 |
| `team_roles` | id, code, name, scope (`agency/client`), is_system, description | UQ(code) | M1 (seed system roles) |
| `team_permissions` | id, team_role_id, permission (string from `config/rbac.php`) | UQ(team_role_id, permission) | M1 |
| `team_members` | id, user_id (master), display_name, email (denormalised for display), title, timezone, status | UQ(user_id) | M1 |
| `tenant_audit_logs` | **bigint** id, actor_user_id, action, subject_type, subject_id, client_id nullable, changes JSON (redacted), ip, user_agent, request_id, created_at | IX(subject_type, subject_id), IX(client_id, created_at), IX(actor_user_id, created_at) | M1 |
| `clients` | id, name, slug, status (`prospect/active/paused/archived`), industry, website, logo_asset_id, timezone, locale, notes, brand_voice TEXT, competitors JSON, **SD** | UQ(slug), IX(status) | M2 |
| `client_contacts` | id, client_id, name, email, phone, role, is_primary | IX(client_id) | M2 |
| `client_users` | id, client_id, user_id (master), team_role_id, granted_by, created_at | UQ(client_id, user_id), IX(user_id) | M2 |
| `client_brands` | id, client_id, name, colors JSON, fonts JSON, logo_asset_ids JSON, voice_guidelines TEXT, hashtags_default JSON | IX(client_id) | M2 |
| `team_tasks` | id, client_id, post_id nullable, title, description, assignee_user_id, due_at, status, checklist JSON, created_by | IX(client_id, status), IX(assignee_user_id, status) | M4 |

### B2. Social connections (M2)

| Table | Key columns | Keys / indexes | M |
|-------|-------------|----------------|---|
| `social_accounts` | id, client_id, provider, account_type (`fb_page/ig_business/ig_creator/li_member/li_org/yt_channel/x_user/tt_user/pin_user/threads_user/gbp_location`), external_account_id, parent_external_id (e.g. FB page for IG), display_name, handle, avatar_url, status (`active/needs_reconnect/revoked/disconnected/error`), connected_by_user_id, consent_at, consent_context JSON, last_synced_at, last_health_check_at, health_error, **SD** | **UQ(provider, external_account_id)** (one owner client per tenant; cross-tenant duplication allowed and flagged), IX(client_id, provider), IX(status) | M2 |
| `social_account_tokens` | id, social_account_id, access_token (enc, `encrypted` cast with dedicated key), refresh_token (enc), token_type, expires_at, refresh_expires_at, scopes_granted JSON, issued_at, last_refreshed_at, refresh_failures | UQ(social_account_id), IX(expires_at) | M2 |
| `social_permissions` | id, social_account_id, scope, granted bool, checked_at | UQ(social_account_id, scope) | M2 |
| `social_account_events` | **bigint** id, social_account_id, type (`connected/refreshed/refresh_failed/scope_changed/revoked/reconnected/disconnected/health_ok/health_failed`), detail JSON, actor_user_id, created_at | IX(social_account_id, created_at) | M2 |

### B3. Media & campaigns (M3 / M4)

| Table | Key columns | Keys / indexes | M |
|-------|-------------|----------------|---|
| `asset_folders` | id, client_id nullable, parent_id, name, path | IX(client_id, parent_id) | M3 |
| `media_assets` | id, client_id nullable, folder_id, disk, path, original_name, mime, size_bytes, checksum_sha256, width, height, duration_ms, status (`uploading/scanning/ready/infected/failed/archived`), scan_result, metadata JSON, tags JSON, uploaded_by, **SD** | IX(client_id, status), IX(checksum_sha256), FULLTEXT(original_name) | M3 |
| `media_variants` | id, media_asset_id, kind (`thumb/1_1/4_5/9_16/16_9/transcode_h264`), path, mime, width, height, size_bytes, status | UQ(media_asset_id, kind) | M3 |
| `media_usages` | id, media_asset_id, post_version_id | UQ(media_asset_id, post_version_id) | M3 |
| `content_categories` | id, client_id nullable, name, color | | M3 |
| `templates` | id, client_id nullable, type (`caption/post/report`), name, body JSON | IX(client_id, type) | M3 |
| `campaigns` | id, client_id, name, objective, starts_on, ends_on, status, budget_paise nullable, **SD** | IX(client_id, status) | M4 |
| `campaign_posts` | campaign_id, post_id | PK(campaign_id, post_id) | M4 |

### B4. Publishing (M3)

| Table | Key columns | Keys / indexes | M |
|-------|-------------|----------------|---|
| `posts` | id, client_id (primary client for portal scoping), title, status (post aggregate: `draft/awaiting_review/changes_requested/approved/scheduled/processing/published/partially_failed/failed/cancelled`), current_version_id, approved_version_id, category_id, created_by, publish_mode (`now/scheduled/queue_slot`), **SD** | IX(client_id, status), IX(status, updated_at) | M3 |
| `post_versions` | id, post_id, version_no, body TEXT, link_url, payload JSON (master content), content_hash (sha256 over normalised body + media checksums + target set + overrides), created_by, created_at — **immutable** (no update) | UQ(post_id, version_no), IX(content_hash) | M3 |
| `post_media` | id, post_version_id, media_asset_id, position, alt_text | UQ(post_version_id, position) | M3 |
| `post_targets` | id, post_id, social_account_id, client_id (denormalised for scoping), status (target state: see doc 06), override JSON (caption, hashtags, mentions, title, thumbnail, first_comment, provider options), validation JSON, external_post_id, external_url, published_at, last_error_code, last_error_message, retry_count, next_retry_at | **UQ(post_id, social_account_id)**, IX(status, next_retry_at), IX(client_id, status) | M3 |
| `post_schedules` | id, post_id, scheduled_at_utc, local_datetime, timezone, recurrence JSON nullable, status | IX(scheduled_at_utc, status) | M3 |
| `publishing_attempts` | **bigint** id, post_target_id, post_version_id, attempt_no, idempotency_key, state (`in_flight/succeeded/failed_retryable/failed_permanent/unknown/reconciled`), provider_request_id, external_post_id, error_class, error_code, error_message (redacted), http_status, started_at, finished_at | **UQ(idempotency_key)**, UQ(post_target_id, attempt_no) | M3 |
| `publishing_logs` | **bigint** id, post_target_id, level, message, context JSON (redacted), created_at | IX(post_target_id, created_at) | M3 |
| `queue_slots` | id, social_account_id, weekday, local_time, timezone | UQ(social_account_id, weekday, local_time) | M4 |
| `blackout_windows` | id, client_id nullable, starts_at, ends_at, reason | IX(client_id, starts_at) | M4 |
| `outbox_events` | **bigint** id, aggregate_type, aggregate_id, event_type (`target.schedule/target.cancel/target.reschedule`), payload JSON, idempotency_key, created_at, relayed_at, attempts, last_error | UQ(idempotency_key), IX(relayed_at, id) | M3 |

### B5. Approvals (M4 — minimal gate in M3)

| Table | Key columns | Keys / indexes | M |
|-------|-------------|----------------|---|
| `approval_workflows` | id, client_id nullable (null = agency default), name, stages JSON (ordered: `{type: internal/client, approvers: [user_ids or role], min_approvals, deadline_hours}`), active | IX(client_id, active) | M3 (single-stage) / M4 |
| `approval_requests` | id, post_id, post_version_id, content_hash, workflow_snapshot JSON, status (`pending/approved/changes_requested/superseded/cancelled`), requested_by, requested_at, completed_at | IX(post_id, status), UQ(post_version_id) | M3 / M4 |
| `approval_steps` | id, approval_request_id, stage_no, type, status, due_at, delegated_to_user_id | UQ(approval_request_id, stage_no) | M4 |
| `approval_histories` | **bigint** id, approval_request_id, approval_step_id, actor_user_id, decision (`approved/changes_requested/delegated/superseded`), comment, content_hash, ip, created_at | IX(approval_request_id) | M3 / M4 |
| `review_comments` | id, post_id, post_version_id, target_id nullable, parent_id, author_user_id, body, anchor JSON, resolved_at | IX(post_id, created_at) | M4 |

### B6. Analytics & reports (M5)

| Table | Key columns | Keys / indexes | M |
|-------|-------------|----------------|---|
| `social_metrics` | **bigint** id, social_account_id, metric (e.g. `followers`, `impressions`), date, value BIGINT, value_decimal nullable, source_api, source_version, fetched_at | UQ(social_account_id, metric, date) | M5 |
| `post_metrics` | **bigint** id, post_target_id, metric, value, fetched_at, window (`lifetime/day`), date nullable | UQ(post_target_id, metric, window, date) | M5 |
| `metric_sync_runs` | id, social_account_id, scope, status, started_at, finished_at, error, rows | IX(social_account_id, started_at) | M5 |
| `analytics_snapshots` | id, client_id, period_start, period_end, data JSON, generated_at | IX(client_id, period_start) | M5 |
| `report_templates` | id, client_id nullable, name, sections JSON, branding JSON, schedule_cron, recipients JSON, format (`pdf/xlsx`) | | M5 |
| `report_exports` | id, report_template_id, client_id, status, file_path, delivered_to JSON, delivered_at, error | IX(client_id, created_at) | M5 |

### B7. Inbox (M6)

| Table | Key columns | Keys / indexes | M |
|-------|-------------|----------------|---|
| `conversations` | id, social_account_id, client_id, kind (`dm/comment_thread/review`), external_thread_id, subject, status (`open/pending/closed`), last_message_at, assigned_user_id, tags JSON | UQ(social_account_id, external_thread_id), IX(client_id, status, last_message_at) | M6 |
| `messages` | id, conversation_id, external_message_id, direction, author_external_id, author_name, body (encrypted at rest), sent_at, status | UQ(conversation_id, external_message_id) | M6 |
| `comments` | id, social_account_id, post_target_id nullable, external_comment_id, parent_external_id, author, body, hidden, created_at_provider | UQ(social_account_id, external_comment_id) | M6 |
| `inbox_assignments` | id, conversation_id, user_id, assigned_by, created_at | IX(user_id) | M6 |
| `saved_replies` | id, client_id nullable, title, body | | M6 |
| `inbox_notes` | id, conversation_id, author_user_id, body | | M6 |

### B8. AI & usage (M5)

| Table | Key columns | Keys / indexes | M |
|-------|-------------|----------------|---|
| `ai_templates` | id, client_id nullable, name, purpose, prompt_template, version | | M5 |
| `ai_requests` | id, client_id, user_id, purpose (`caption/rewrite/hashtags/translate/plan`), provider, model, prompt_version, input_hash, input_redacted TEXT, output TEXT, status, consent_at, created_at | IX(client_id, created_at) | M5 |
| `ai_usage` | **bigint** id, ai_request_id, input_tokens, output_tokens, cost_micros, credits_charged | IX(ai_request_id) | M5 |
| `tenant_usage_events` | **bigint** id, metric (`post.published/ai.credit/storage.bytes/video.minutes`), quantity, occurred_at, ref_type, ref_id, rolled_up_at | IX(metric, occurred_at), IX(rolled_up_at) | M3 |

### B9. Operations (M1–M3)

| Table | Key columns | Keys / indexes | M |
|-------|-------------|----------------|---|
| `notifications` | Laravel database notifications (uuid id, notifiable, type, data, read_at) | | M1 |
| `notification_preferences` | id, user_id, channel (`mail/database/slack`), event, enabled | UQ(user_id, channel, event) | M4 |
| `webhook_events` | **bigint** id, provider, external_event_id, type, signature_valid, payload JSON, received_at, processed_at, attempts, error | **UQ(provider, external_event_id)**, IX(processed_at) | M2 (Meta deauth) / M6 |

---

## C. Migration ordering per milestone

| Milestone | Master migrations | Tenant migrations |
|-----------|-------------------|-------------------|
| **M1** | users, password_reset_tokens, sessions, cache, jobs, failed_jobs, job_batches, features (Pennant), tenants, tenant_domains, tenant_database_connections, tenant_users, tenant_provisioning_jobs, plans, plan_features, tenant_feature_overrides, subscriptions (trial only), usage_summaries, system_settings, admin_audit_logs, support_access_grants, backup_runs | tenant_settings, team_roles, team_permissions, team_members, tenant_audit_logs, notifications |
| **M2** ✅ | platform_integrations, provider_capabilities, oauth_states, social_identity_links, inbound_webhooks | clients, client_contacts, client_users, client_brands, social_accounts (no soft delete; disconnect = status), social_account_tokens, social_permissions, social_account_events. Tenant `webhook_events` moved to M6 (inbox) |
| **M3** ✅ | publish_dispatch_index | asset_folders, media_assets, media_variants, media_usages, content_categories, templates, posts, post_versions, post_media, post_targets, post_schedules, publishing_attempts, publishing_logs, outbox_events, approval_workflows, approval_requests, approval_histories (single-stage gate), tenant_usage_events |
| **M4** ✅ | — | approval_steps, review_comments, campaigns, campaign_posts, team_tasks, queue_slots, blackout_windows, notification_preferences |
| **M5** | — | social_metrics, post_metrics, metric_sync_runs, analytics_snapshots, report_templates, report_exports, ai_templates, ai_requests, ai_usage |
| **M6** | plan_prices, payments, invoices, coupons, coupon_redemptions, billing_webhook_events, incidents | conversations, messages, comments, inbox_assignments, saved_replies, inbox_notes |

Rule: shipped migrations are **never edited**. Every change adds a new migration. Tenant migrations must be backward-compatible for one release (expand → migrate → contract), because tenants are migrated progressively.