# 03 — Domain Model & Entity Relationships

## 1. Master database

```mermaid
erDiagram
    USERS ||--o{ TENANT_USERS : "member of"
    TENANTS ||--o{ TENANT_USERS : has
    TENANTS ||--o{ TENANT_DOMAINS : "routed by"
    TENANTS ||--|| TENANT_DATABASE_CONNECTIONS : "stored in"
    TENANTS ||--o{ TENANT_PROVISIONING_JOBS : "provisioned by"
    TENANTS }o--|| PLANS : "on plan"
    PLANS ||--o{ PLAN_FEATURES : defines
    PLANS ||--o{ PLAN_PRICES : "priced as"
    TENANTS ||--o{ TENANT_FEATURE_OVERRIDES : overrides
    TENANTS ||--o{ SUBSCRIPTIONS : bills
    SUBSCRIPTIONS ||--o{ PAYMENTS : collects
    TENANTS ||--o{ INVOICES : receives
    TENANTS ||--o{ USAGE_SUMMARIES : aggregates
    TENANTS ||--o{ PUBLISH_DISPATCH_INDEX : "due work refs"
    TENANTS ||--o{ SUPPORT_ACCESS_GRANTS : grants
    PLATFORM_INTEGRATIONS ||--o{ PROVIDER_CAPABILITIES : "versioned caps"
    USERS ||--o{ ADMIN_AUDIT_LOGS : "acts in"
    USERS ||--o{ TENANTS : owns
```

## 2. Tenant database

```mermaid
erDiagram
    CLIENTS ||--o{ CLIENT_USERS : "assigned users"
    CLIENTS ||--o{ CLIENT_CONTACTS : has
    CLIENTS ||--o{ CLIENT_BRANDS : has
    CLIENTS ||--o{ SOCIAL_ACCOUNTS : owns
    SOCIAL_ACCOUNTS ||--|| SOCIAL_ACCOUNT_TOKENS : "auth via"
    SOCIAL_ACCOUNTS ||--o{ SOCIAL_PERMISSIONS : "granted scopes"
    SOCIAL_ACCOUNTS ||--o{ SOCIAL_ACCOUNT_EVENTS : history
    CLIENTS ||--o{ POSTS : "primary client"
    POSTS ||--o{ POST_VERSIONS : "immutable revisions"
    POST_VERSIONS ||--o{ POST_MEDIA : includes
    MEDIA_ASSETS ||--o{ POST_MEDIA : "used in"
    MEDIA_ASSETS ||--o{ MEDIA_VARIANTS : renditions
    ASSET_FOLDERS ||--o{ MEDIA_ASSETS : contains
    POSTS ||--o{ POST_TARGETS : "sent to"
    SOCIAL_ACCOUNTS ||--o{ POST_TARGETS : receives
    POST_TARGETS ||--o{ PUBLISHING_ATTEMPTS : tries
    POST_TARGETS ||--o{ PUBLISHING_LOGS : logs
    POSTS ||--o{ POST_SCHEDULES : "scheduled by"
    POSTS ||--o{ APPROVAL_REQUESTS : "reviewed via"
    POST_VERSIONS ||--o| APPROVAL_REQUESTS : "approval bound to"
    APPROVAL_REQUESTS ||--o{ APPROVAL_STEPS : stages
    APPROVAL_REQUESTS ||--o{ APPROVAL_HISTORIES : decisions
    POSTS ||--o{ REVIEW_COMMENTS : discussed
    CAMPAIGNS ||--o{ CAMPAIGN_POSTS : groups
    POSTS ||--o{ CAMPAIGN_POSTS : "belongs to"
    CLIENTS ||--o{ CAMPAIGNS : runs
    SOCIAL_ACCOUNTS ||--o{ SOCIAL_METRICS : measured
    POST_TARGETS ||--o{ POST_METRICS : measured
    SOCIAL_ACCOUNTS ||--o{ CONVERSATIONS : inbox
    CONVERSATIONS ||--o{ MESSAGES : contains
    CLIENTS ||--o{ REPORT_TEMPLATES : reports
    REPORT_TEMPLATES ||--o{ REPORT_EXPORTS : generates
    CLIENTS ||--o{ AI_REQUESTS : "AI usage"
    AI_REQUESTS ||--o{ AI_USAGE : meters
```

Cross-DB references (not enforced by FK): every `*_user_id` in the tenant DB points to master `users.id`, and `tenant_id` is implicit (it is the database itself).

## 3. Aggregates and invariants

| Aggregate | Root | Invariants enforced in code (and DB where possible) |
|-----------|------|------------------------------------------------------|
| Tenant | `Tenant` (master) | Exactly one owner. Slug is immutable after activation. Not `active` until the DB is verified |
| Membership | `TenantUser` | A user can belong to many tenants with one role each. Revoking a membership kills that tenant's sessions |
| Client | `Client` | Archiving disables publishing to its accounts. Client users only see their client |
| Social account | `SocialAccount` | One owning client per tenant for a (provider, external_id) pair. Tokens are never exposed to the UI. Status changes emit `social_account_events` |
| Post | `Post` | Only one `current_version`. Versions are immutable. `approved_version_id` must equal `current_version_id` before scheduling. Aggregate status is derived from targets |
| Post target | `PostTarget` | Unique per (post, account). Status transitions go through `TargetStateMachine`. External publish happens at most once per idempotency key |
| Approval | `ApprovalRequest` | Bound to `post_version_id` + `content_hash`. A new material version → `superseded` |
| Subscription | `Subscription` | One live subscription per tenant. Entitlements are derived from plan + overrides |

## 4. Status enums (PHP backed enums)

- **TenantStatus:** `provisioning_pending, provisioning, provisioning_failed, active, suspended, deleting, deleted`
- **PostStatus** (spec §2.4): `draft, awaiting_review, changes_requested, approved, scheduled, processing, published, partially_failed, failed, cancelled`
- **TargetStatus:** `draft, invalid, ready, scheduled, queued, publishing, published, retry_scheduled, failed, needs_reconnect, unknown_outcome, cancelled`
- **SocialAccountStatus:** `active, needs_reconnect, revoked, disconnected, error`
- **ApprovalStatus:** `pending, approved, changes_requested, superseded, cancelled`
- **AttemptState:** `in_flight, succeeded, failed_retryable, failed_permanent, unknown, reconciled`
- **CapabilityStatus:** `available, configurable, blocked, unavailable`
- **SubscriptionStatus:** `trialing, active, past_due, grace, suspended, cancelled`

## 5. Post aggregate status derivation

```
if all targets cancelled                → cancelled
elif any target publishing/queued       → processing
elif all non-cancelled targets published → published
elif some published and some failed      → partially_failed
elif all non-cancelled failed           → failed
elif any scheduled                      → scheduled
else workflow status (draft / awaiting_review / changes_requested / approved)
```

## 6. "Material change" definition (approval invalidation)

`content_hash = sha256(json_canonical({body, link_url, media: [checksum, position, alt], targets: [social_account_id + override], title, thumbnails}))`

Any change that alters the hash creates a new `post_version`. That marks open or approved `approval_requests` as `superseded` and returns the post to `awaiting_review` (if the client's workflow requires approval). Non-material edits (internal labels, category, campaign, internal notes) do not change the hash.