# AURA LIVE — Database Setup Guide (MySQL 8)

## Schema overview (45 tables, InnoDB, utf8mb4)

| Domain | Tables |
|---|---|
| Identity | `users`, `user_profiles`, `user_settings`, `user_sessions`, `user_devices`, `user_verifications`, `password_reset_tokens` |
| Social graph | `follows`, `blocks`, `friendships` |
| Feed | `social_posts`, `social_post_media`, `social_post_likes`, `social_post_comments` |
| Rooms | `rooms`, `room_members`, `room_seats`, `room_moderators`, `room_invitations`, `room_events`, `room_messages` |
| Messaging | `conversations`, `conversation_members`, `direct_messages` |
| Economy | `wallets`, `wallet_ledger`, `gifts`, `gift_categories`, `gift_transactions`, `coin_packages`, `profile_frames`, `user_owned_frames`, `vip_plans`, `user_subscriptions` |
| Payments | `payment_orders`, `payment_events`, `refund_records` |
| Games | `game_lobbies`, `game_players`, `game_sessions`, `game_events` |
| Notifications | `notifications`, `notification_preferences` |
| Moderation | `user_reports`, `content_reports`, `moderation_actions`, `support_tickets` |
| Admin & ops | `admin_users`, `admin_roles`, `admin_permissions`, `admin_audit_logs`, `app_settings`, `event_campaigns`, `event_participants`, `achievements`, `user_achievements`, `background_jobs`, `system_health_logs`, `rate_limit_hits` |

## Design principles
- **Primary keys**: `BIGINT UNSIGNED AUTO_INCREMENT`.
- **Foreign keys** with `ON DELETE CASCADE` where children are meaningless without parents (sessions, seats use `SET NULL` for users so room history survives).
- **Unique constraints** prevent duplicates at DB level: username, email, unique_id, room_code, follow pairs, block pairs, seat (room, index), payment provider order ids, webhook event ids, gift transaction uuid (idempotency).
- **Money-safe wallet**: `wallets` row is always mutated inside a transaction with `SELECT ... FOR UPDATE`; every change writes a `wallet_ledger` row (`coins_delta`, `balance_after`). Balances without ledger entries are impossible by design.
- **Soft deletion**: users (`deleted_at`, `status='deleted'`), posts (`status='removed'`), gift catalog (`is_active=0`).
- **Indexes** on all hot paths: room discovery (`status, category, participant_count`), room chat (`room_id, created_at`), DM inbox (`user_*, last_message_at`), notifications (`user_id, is_read, created_at`).

## Migrations
Files in `server/migrations/`, applied in filename order and tracked in `schema_migrations`:

```bash
cd server
npm run migrate       # reads DB_* from ../.env, creates the database if missing
```

## Seed data (development only)
```bash
cd server
npm run seed
```
Creates: app settings defaults, Super Admin role + admin (email/password from `ADMIN_INITIAL_EMAIL` / `ADMIN_INITIAL_PASSWORD`), 8 gift categories + 17 gifts, 4 coin packages (100/500/1000/5000), 7 profile frames, 3 VIP plans, 8 demo users, 5 live demo rooms (Fun Fiesta Hub, Game Night Getaway, Precision Play Lounge, Chat Corner, Let's Chat Together), achievements, one weekly event.

**Security notes**
- Passwords are bcrypt-hashed at runtime — no plaintext in seed files.
- Demo users all use `Demo#Pass123` (development only — remove before production or seed selectively).
- Payment secrets are NEVER seeded.

## Connecting apps
| App | Config |
|---|---|
| server | `DB_HOST/DB_PORT/DB_USER/DB_PASSWORD/DB_NAME` in `.env` |
| docker-compose | MySQL 8 service on `mysql:3306`, volume `mysql_data` |

## Backup & retention
```bash
mysqldump -u aura -p aura_live > backup-$(date +%F).sql
```
- Audit logs: retain per your compliance needs (see docs/SECURITY.md §Retention).
- Account deletion removes personal data after the 30-day grace period; financial records stay anonymised.