# Database Index and Constraint Strategy

## General rules

- Every base table has a primary key.
- Public IDs, external event identifiers, request idempotency hashes, and immutable business-event keys use unique indexes.
- Foreign-key columns are indexed explicitly where they are used for joins or queue scans; InnoDB may also create required supporting indexes.
- List endpoints use composite indexes matching equality filters first, then time/order columns, then the primary key as a deterministic tie-breaker.
- Large history endpoints use `(owner_id, created_at, id)`-style indexes for stable cursor pagination.
- No index includes encrypted account details, message bodies, raw payloads, secrets, or other high-entropy/private content.

## Race-condition uniqueness

| Invariant                                     | Index/constraint                                |
| --------------------------------------------- | ----------------------------------------------- |
| One Telegram identity                         | `uq_users_telegram_id`                          |
| One active referral code per user             | `uq_referral_codes_active_user` generated guard |
| One referrer per referee                      | `uq_referrals_referee`                          |
| One activation event                          | `uq_referral_activations_event`                 |
| One active attempt per task/user              | `uq_task_attempts_active`                       |
| One completion per attempt                    | `uq_task_completions_attempt`                   |
| One completion per task/user/window           | `uq_task_completions_period`                    |
| One task claim key per user                   | `uq_task_completions_user_claim_key`            |
| One reward event                              | `uq_referral_rewards_event`                     |
| One public referral state-history event       | `uq_referral_status_history_public_id`          |
| One ledger idempotency event                  | `uq_ledger_transactions_idempotency`            |
| One reversal per original transaction         | `uq_ledger_transactions_reversal`               |
| One idempotent API operation per actor/scope  | `uq_idempotency_scope_actor_key`                |
| One withdrawal per quote                      | `uq_withdrawal_requests_quote`                  |
| One withdrawal submission key per user        | `uq_withdrawal_requests_user_idempotency`       |
| One payout record per withdrawal request      | `uq_payout_transactions_withdrawal`             |
| One provider event                            | `uq_payout_provider_events_event`               |
| One active free Mystery Box                   | `uq_mystery_boxes_active_slot`                  |
| One free reward opportunity                   | `uq_mystery_box_claims_opportunity`             |
| One Mystery Box claim key per user            | `uq_mystery_box_claims_user_idempotency`        |
| One ledger transaction per free claim         | `uq_mystery_box_claims_ledger_tx`               |
| One Telegram webhook update                   | `uq_telegram_updates_update_id`                 |
| One outbox event                              | `uq_outbox_messages_deduplication`              |
| One template revision                         | `uq_bot_template_versions_version`              |
| One provider attempt number                   | `uq_message_attempts_number`                    |
| One reminder occurrence per recipient         | `uq_reminder_deliveries_key`                    |
| One broadcast recipient                       | `uq_broadcast_deliveries_recipient`             |
| One scheduler/job run key                     | `uq_job_runs_key`                               |
| One analytics date/currency rollup            | `uq_analytics_rollup_runs_date_currency`        |
| One leaderboard metric/period/date generation | `uq_leaderboard_snapshots_generation`           |

Application code must still catch duplicate-key errors and return the previously committed safe outcome where the idempotency contract requires it.

## Queue indexes

The queue/outbox/reminder indexes begin with status and availability time so workers can claim small ordered batches. Claim queries in later phases must use transactions and `SELECT ... FOR UPDATE SKIP LOCKED` where supported.

Examples:

- `idx_outbox_messages_queue (status, available_at, locked_until, id)`
- `idx_reminder_deliveries_queue (status, scheduled_for, id)`
- `idx_withdrawal_requests_queue (status, requested_at, id)`
- `idx_telegram_updates_processing (processing_status, received_at, id)`

## Financial indexes

- Ledger history: `idx_ledger_entries_account_time (account_id, created_at, id)`.
- Transaction reconciliation: `idx_ledger_entries_transaction (transaction_id, id)`.
- Posted transaction lookup: `idx_ledger_transactions_currency_posted`.
- Referral review timeline: `idx_referral_status_history_referral`.
- Referral reward inspection: `idx_referral_rewards_referral_status`.
- Mystery Box cooldown/opportunity lookup: `idx_mystery_claims_box_user_time`.
- Withdrawal ledger references are unique to prevent one hold/payout/refund transaction being attached twice.
- Withdrawal daily eligibility uses `idx_withdrawal_requests_user_day`; canonical replay diagnostics use the user/fingerprint indexes without storing raw keys.
- Analytics scans use date-first task/user projections and the available-account ledger index; leaderboard entry reads use snapshot/rank order.

Balance remains a computed ledger result. `wallet_account_balances` and `wallet_user_balances` include only transactions whose immutable state is `posted` or `reversed`; pending entries never affect a user balance. `wallet_balance_snapshots` is a performance projection and has a last-entry watermark so reconciliation can detect staleness.

## Maintenance

Before adding an index, capture the target query plan and expected cardinality. Duplicate or unused indexes increase write cost. Production index changes require a new immutable migration; previously applied migration files are never edited.

## Phase 19 operations

Migration 023 adds unique invitation/enrollment token hashes, unique wallet correction request keys and ledger links, a pending-adjustment queue index, and a unique outbox/attempt failure index. Existing table versions are used for optimistic review checks after row locking.