# Database Relationships

## Identity and authorization

```mermaid
erDiagram
  ADMINS ||--o{ ADMIN_ROLE_ASSIGNMENTS : receives
  ADMIN_ROLES ||--o{ ADMIN_ROLE_ASSIGNMENTS : assigns
  ADMIN_ROLES ||--o{ ADMIN_ROLE_PERMISSIONS : grants
  PERMISSIONS ||--o{ ADMIN_ROLE_PERMISSIONS : contains
  ADMINS ||--o{ ADMIN_SESSIONS : owns
```

Administrators can have multiple roles. Authorization reads permission mappings; role names are not hard-coded authorization shortcuts.

## Verified task reward

```mermaid
erDiagram
  USERS ||--o{ TASK_ATTEMPTS : starts
  TASKS ||--o{ TASK_ATTEMPTS : snapshots
  TASK_ATTEMPTS ||--o| TASK_COMPLETIONS : completes
  TASK_COMPLETIONS ||--|| LEDGER_TRANSACTIONS : rewards
  LEDGER_TRANSACTIONS ||--|{ LEDGER_ENTRIES : balances
```

The Phase 11 task service creates the completion and its balanced ledger transaction in one database transaction. The server, never the browser timer, decides whether `eligible_at` has passed.

## Referral activation and reward

```mermaid
erDiagram
  USERS ||--o{ REFERRAL_CODES : owns
  USERS ||--o{ REFERRALS : refers
  REFERRALS ||--o| REFERRAL_ACTIVATIONS : qualifies
  TASK_COMPLETIONS ||--o{ REFERRAL_ACTIVATIONS : proves
  REFERRALS ||--o{ REFERRAL_REWARDS : awards
  REFERRALS ||--o{ REFERRAL_STATUS_HISTORY : records
  REFERRAL_REWARDS o|--o| LEDGER_TRANSACTIONS : posts
```

`referee_user_id` is unique, attribution is immutable, and reward event keys prevent replay. Phase 12 activates a referral inside the qualifying task-completion transaction, preserves threshold/reward snapshots, and appends every automatic or Admin state transition.

## Wallet and withdrawal

```mermaid
erDiagram
  USERS ||--o{ WALLET_ACCOUNTS : owns
  LEDGER_TRANSACTIONS ||--|{ LEDGER_ENTRIES : contains
  WALLET_ACCOUNTS ||--o{ LEDGER_ENTRIES : receives
  WITHDRAWAL_QUOTES ||--o| WITHDRAWAL_REQUESTS : authorizes
  WITHDRAWAL_REQUESTS ||--o{ WITHDRAWAL_STATUS_HISTORY : records
  WITHDRAWAL_REQUESTS ||--o{ WITHDRAWAL_APPROVALS : reviews
  WITHDRAWAL_REQUESTS ||--o| PAYOUT_TRANSACTIONS : confirms
```

The financial flow is represented by balanced entries:

1. Task/referral/free reward: system reward account to user available account.
2. Withdrawal request: user available account to user held account.
3. Paid withdrawal: user held gross to payout clearing net plus withdrawal fee account.
4. Rejected/failed withdrawal: user held account back to user available account.

No `users.balance` column exists.

Phase 14 binds each request to an immutable quote/method/currency snapshot. Rejection refunds the entire gross hold atomically. Paid confirmation requires unique manual provider evidence and settles gross = net + fee in the same transaction as the terminal state/history update. High-value requests may require two distinct Admin approvals; approval rows and status history cannot be changed or deleted.

`ledger_entries` are append-only at the database layer. A correction creates a linked reversal with opposite entries; it never edits or deletes the original entries. User-facing balances are rebuilt only from transactions in `posted` or `reversed` state, so a pending transaction cannot affect spendable funds.

## Free reward box

```mermaid
erDiagram
  MYSTERY_BOXES ||--o{ MYSTERY_BOX_REWARDS : offers
  USERS ||--o{ MYSTERY_BOX_CLAIMS : receives
  MYSTERY_BOX_REWARDS ||--o{ MYSTERY_BOX_CLAIMS : snapshots
  MYSTERY_BOX_CLAIMS ||--|| LEDGER_TRANSACTIONS : posts
```

Eligibility depends only on authoritative completed tasks, server time, frequency/cooldown, account state, and available reward inventory. Database constraints permanently prohibit a fee, purchase requirement, deposit, stake, or wager. A successful claim and its balanced ledger reward commit in one transaction; immutable snapshots retain the exact eligibility and selection evidence.

## Reliable messaging

```mermaid
erDiagram
  USERS ||--o{ NOTIFICATIONS : receives
  REMINDER_SCHEDULES ||--o{ REMINDER_DELIVERIES : creates
  BROADCASTS ||--o{ BROADCAST_DELIVERIES : creates
  BOT_MESSAGE_TEMPLATES ||--o{ BOT_MESSAGE_TEMPLATE_VERSIONS : records
  OUTBOX_MESSAGES ||--o{ MESSAGE_DELIVERY_ATTEMPTS : proves
  OUTBOX_MESSAGES }o--|| JOB_RUNS : dispatches
```

Business transactions write outbox rows atomically. External Telegram calls happen after commit so network waits never hold financial database locks. Scheduler and worker leases recover interrupted work; template revisions and delivery attempts remain immutable evidence.

## Analytics projections

```mermaid
erDiagram
  LEDGER_TRANSACTIONS ||--o{ DAILY_STATISTICS : projects
  USERS ||--o{ USER_STATISTICS : summarizes
  TASKS ||--o{ TASK_STATISTICS : summarizes
  ANALYTICS_ROLLUP_RUNS ||--o{ LEADERBOARD_SNAPSHOTS : records
  LEADERBOARD_SNAPSHOTS ||--o{ LEADERBOARD_ENTRIES : publishes
```

Statistics and leaderboards are rebuildable read models. They never post ledger entries or change authoritative task, referral, claim, or withdrawal events. Each closed-date run records source watermarks and row counts, while published leaderboard entries retain only the privacy-safe display snapshot used for that generation.

## Phase 19 operations

`wallet_adjustment_requests` links a user and currency to separate requesting/reviewing administrators and, only after approval, a unique immutable ledger transaction. `failed_jobs.outbox_message_id` links a failure to its authoritative outbox message; `(outbox_message_id, attempt_count)` is unique. `admin_control_locks` is a singleton serialized within management transactions before rechecking actor access.