# Database Table Catalogue ## Scope The ordered SQL migrations create 75 domain tables. The migration runner independently creates four metadata tables, so a fully initialized database contains 79 base tables plus three reconciliation views. All domain tables explicitly use `InnoDB`, `utf8mb4`, and `utf8mb4_0900_ai_ci`. Identifiers such as Telegram IDs and database primary keys use `BIGINT UNSIGNED`; application-facing public IDs use 26-character opaque IDs. Monetary values use integer minor-unit columns ending in `_minor`. ## Migration metadata | Table | Authority | | ----------------------------- | ------------------------------------------------------------ | | `schema_migrations` | Applied immutable migration versions, checksums, and batches | | `schema_migration_statements` | Per-statement up/down resume evidence | | `schema_seeds` | Applied immutable seed versions and checksums | | `schema_seed_statements` | Per-statement seed resume evidence | These tables are created by the runner before migration `001` and are never dropped by an application migration. ## Domain tables | Area | Tables | | --------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Admin and RBAC | `admin_roles`, `permissions`, `admins`, `admin_role_assignments`, `admin_role_permissions`, `admin_sessions`, `admin_2fa_secrets`, `admin_recovery_codes` | | Users | `users`, `user_sessions`, `user_devices`, `user_bans`, `user_activity_logs` | | Settings | `currencies`, `settings`, `setting_versions`, `themes`, `managed_links`, `feature_flags` | | Tasks | `task_categories`, `tasks`, `task_attempts`, `task_completions`, `task_verifications` | | Referrals | `referral_codes`, `referrals`, `referral_activations`, `referral_rewards`, `referral_status_history` | | Wallet and ledger | `wallet_accounts`, `ledger_transactions`, `ledger_entries`, `ledger_transaction_approvals`, `idempotency_keys`, `wallet_balance_snapshots` | | Withdrawals | `withdrawal_methods`, `withdrawal_method_fields`, `withdrawal_quotes`, `withdrawal_requests`, `withdrawal_status_history`, `withdrawal_approvals`, `payout_transactions`, `payout_provider_events` | | Free reward box | `mystery_boxes`, `mystery_box_rewards`, `mystery_box_claims` | | Content | `media_files`, `banners`, `announcements`, `announcement_reads`, `tutorials`, `maintenance_windows` | | Bot and notifications | `bot_message_templates`, `bot_message_template_versions`, `notifications`, `reminder_schedules`, `reminder_deliveries`, `telegram_updates`, `broadcasts`, `broadcast_deliveries`, `message_delivery_attempts` | | Security and audit | `security_events`, `fraud_flags`, `admin_audit_logs`, `login_attempts` | | Jobs and outbox | `outbox_messages`, `failed_jobs`, `scheduler_locks`, `job_runs` | | Analytics | `daily_statistics`, `task_statistics`, `user_statistics`, `leaderboard_snapshots`, `leaderboard_entries`, `analytics_rollup_runs` | ## Authoritative versus projected data | Classification | Tables | Rule | | --------------------- | ---------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------- | | Financial authority | `ledger_transactions`, `ledger_entries`, `wallet_accounts` | Posted entries are append-only; corrections are linked reversals. | | Financial projection | `wallet_balance_snapshots` | Rebuildable from the ledger; never the source of truth. | | Operational authority | Attempts, completions, referrals, claims, withdrawals, status histories | State changes occur transactionally with idempotency and row locks in service phases. | | Analytics projection | Statistics and leaderboard tables | Rebuildable and unable to alter ledger truth. | | Immutable evidence | Audit logs, security events, provider events, update deduplication, outbox records | Retained according to the future data-retention policy. | ## Important constraints - A Telegram ID is positive and globally unique. - An attempt snapshots its task type/title, destination/browser mode, limits, reward/currency, and server timestamps. - A generated active slot allows only one active attempt per task/user pair. - One task attempt can create only one completion. - `completion_period_key` enforces one-time/daily/repeat-window uniqueness per user and task. - A per-user HMAC claim-key hash makes task completion replay idempotent. - A referee has only one immutable referral attribution and self-referral is rejected. - Referral activation and reward event keys are unique; activation retains its source task completion. - Referral state changes use optimistic versions and append immutable status-history evidence. - Ledger transaction idempotency keys and reversals are unique. - Every ledger entry has a positive integer minor-unit amount. - A withdrawal quote can be consumed once; withdrawal idempotency is unique per user. - Withdrawal quotes and requests retain method, currency, fee, rule, and request fingerprints; protected evidence cannot be deleted. - One payout transaction is permitted per withdrawal request, and approval counts are constrained to the snapshotted one- or two-person requirement. - Withdrawal status history and Admin approvals are append-only at the database layer. - Provider events and Telegram `update_id` values are deduplicated. - Template versions and delivery attempts are immutable; reminder/broadcast deliveries retain rendered-payload hashes and exact template versions. - Broadcast creators cannot approve their own drafts, and the audience is frozen before bounded recipient materialization. - Status history and audit tables contain new rows instead of rewriting history. - `mystery_boxes.access_cost_minor` and `requires_purchase` are permanently constrained to zero: the feature is free-only and cannot become a paid chance mechanism. - A generated Mystery Box active slot allows only one `active` box at a time. - Box and reward configuration use optimistic versions; claim rows snapshot both versions. - Mystery Box claims retain task totals, frequency, opportunity boundaries, reward/currency, selection proof, and ledger evidence. - Database triggers reject update or deletion of a Mystery Box claim. - One analytics run exists per closed UTC date and settlement currency; run evidence and leaderboard entries are immutable. - One leaderboard generation key exists per metric, period, and generated date; score/rank values must be positive. ## Reconciliation views | View | Purpose | | ----------------------------- | ---------------------------------------------------------------------------------------------------------- | | `ledger_transaction_balances` | Shows debit total, credit total, difference, entry count, and balance result for every ledger transaction. | | `wallet_account_balances` | Rebuilds each account's signed balance and last ledger-entry watermark. | | `wallet_user_balances` | Rebuilds available, held, lifetime-earned, and watermark values from posted/reversed ledger entries. | The application must block posting unless total debit equals total credit. The reconciliation view provides detection and monitoring; a SQL view alone cannot atomically enforce a cross-row sum during insertion. Phase 19 completion adds `admin_control_locks` (one transactional access-control lock) and `wallet_adjustment_requests` (immutable correction requests with independent approval). Migration 023 also versions administrators, roles, user restrictions, fraud flags and failed-job reviews.