# Database Schema

## ERDiagram

```
users
  │
  ├─┐ wa_sessions (1:N)
  │ │
  │ └───────┐
  │         │
  ├─┐ contacts (1:N)    campaigns (1:N)    subscriptions (1:1)
  │ │           │           │
  │ │           └───┐       │
  │ │               │       │
  │ └───────────────┼───────┘
  │                 │
  └─────────────────┼──── campaign_messages (N:N through campaign_id, contact_id)
                    │
                    └──── message_logs (1:N from campaign_messages)
```

## Tables

### users

Primary table for user accounts.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | BIGINT UNSIGNED | PK, AI | User ID |
| uuid | CHAR(36) | Unique | Public-facing UUID |
| email | VARCHAR(255) | Unique, Not Null | Login email |
| email_verified_at | TIMESTAMP | Nullable | Verification timestamp |
| password | VARCHAR(255) | Not Null | Hashed (bcrypt) |
| name | VARCHAR(255) | | Full name |
| phone | VARCHAR(20) | | User's own phone (optional) |
| timezone | VARCHAR(50) | Default 'UTC' | For scheduling UI |
| stripe_customer_id | VARCHAR(255) | Unique, Nullable | Stripe customer ID |
| stripe_subscription_id | VARCHAR(255) | Unique, Nullable | Active subscription ID |
| stripe_price_id | VARCHAR(255) | Nullable | Current plan price ID |
| subscription_status | ENUM('trialing','active','past_due','canceled','unsubscribed') | Default 'trialing' |
| trial_ends_at | TIMESTAMP | Nullable | Trial expiry |
| messages_used_this_month | INT | Default 0 | Counter for quota |
| messages_limit | INT | Default 50 | Monthly limit based on plan |
| max_sessions | INT | Default 1 | Max active WhatsApp sessions |
| is_admin | BOOLEAN | Default false | Admin privileges |
| last_login_at | TIMESTAMP | Nullable | |
| created_at | TIMESTAMP | | |
| updated_at | TIMESTAMP | | |

**Indexes**:
- `idx_email` (email)
- `idx_stripe_customer` (stripe_customer_id)
- `idx_stripe_subscription` (stripe_subscription_id)

**Notes**:
- On registration: create Stripe customer, attach trial subscription (if offering trials)
- Monthly counter resets by cron: `UPDATE users SET messages_used_this_month = 0 WHERE subscription_status = 'active'` on billing cycle date.

---

### wa_sessions

WhatsApp session per user. Each corresponds to one phone number connected via OpenWA.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | BIGINT UNSIGNED | PK, AI | Session ID |
| user_id | BIGINT UNSIGNED | FK → users.id, OnDelete CASCADE | Owner |
| uuid | CHAR(36) | Unique | Public ID |
| name | VARCHAR(100) | | User-defined label (e.g., "My iPhone") |
| phone_number | VARCHAR(20) | Unique, Nullable | WhatsApp number (E.164 format) |
| session_id | VARCHAR(100) | Unique | OpenWA session directory suffix |
| status | ENUM('creating','qr_ready','connected','disconnected','banned','error') | Default 'creating' |
| qr_code_path | VARCHAR(500) | Nullable | Path to stored QR image |
| last_connected_at | TIMESTAMP | Nullable | |
| last_disconnected_at | TIMESTAMP | Nullable | |
| ban_reason | TEXT | Nullable | If banned |
| metadata | JSON | Nullable | Additional info (carrier, device type) |
| created_at | TIMESTAMP | | |
| updated_at | TIMESTAMP | | |

**Indexes**:
- `idx_user_id` (user_id)
- `idx_status` (status)
- `idx_phone_number` (phone_number)

**Session directory** on Mac mini: `storage/app/sessions/{user_id}_{session_id}/`

**Lifecycle**:
1. User clicks "Add WhatsApp Number" → `SessionManager::createSession()` creates record, spawns OpenWA → status `creating`
2. OpenWA generates QR → status `qr_ready`, `qr_code_path` populated
3. User scans QR → OpenWA connects → webhook updates to `connected`, `phone_number` filled, `last_connected_at` set
4. If WhatsApp logs out (phone offline) → webhook => `disconnected`
5. If number banned → webhook => `banned`, `ban_reason` set
6. User can delete session → soft delete or purge directory

**Quota enforcement**: User's `max_sessions` limits how many `wa_sessions` with status `connected` or `qr_ready` they can have.

---

### contacts

User's contact lists. Imported via CSV or manually added.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | BIGINT UNSIGNED | PK, AI | Contact ID |
| user_id | BIGINT UNSIGNED | FK → users.id, OnDelete CASCADE | Owner |
| uuid | CHAR(36) | Unique | Public ID |
| name | VARCHAR(255) | | Contact name (optional) |
| phone | VARCHAR(20) | | Phone number (E.164) |
| email | VARCHAR(255) | Nullable | Optional email |
| tags | JSON | Default '[]' | Array of tag strings (e.g., ["customer","vip"]) |
| consent_status | ENUM('unknown','granted','denied') | Default 'unknown' | Has user confirmed consent? |
| consent_source | VARCHAR(255) | Nullable | How consent was obtained |
| consent_timestamp | TIMESTAMP | Nullable | When consent recorded |
| opted_out_at | TIMESTAMP | Nullable | If user replied STOP |
| metadata | JSON | Nullable | Additional fields from import |
| created_at | TIMESTAMP | | |
| updated_at | TIMESTAMP | | |

**Indexes**:
- `idx_user_phone` (user_id, phone) - ensure phone uniqueness per user
- `idx_user_tags` (user_id, tags) - using generated column for GIN index if Postgres, or simple like queries
- `idx_consent` (consent_status)

**Notes**:
- Importing CSV: user maps columns, we validate phone format (Google libphonenumber recommended)
- Duplicate detection: same `(user_id, phone)` blocks insert or merges
- STOP handling: if message to a contact fails with "blocked" or contact replies STOP, set `opted_out_at` and exclude from future campaigns

---

### campaigns

Message campaigns scheduled by users.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | BIGINT UNSIGNED | PK, AI | Campaign ID |
| user_id | BIGINT UNSIGNED | FK → users.id, OnDelete CASCADE | Owner |
| uuid | CHAR(36) | Unique | Public ID |
| name | VARCHAR(255) | | Campaign title |
| wa_session_id | BIGINT UNSIGNED | FK → wa_sessions.id | Which phone to send from |
| message_type | ENUM('text','image','document','template') | Default 'text' |
| message_body | TEXT | | Message text (supports `{name}`, `{var}`) |
| media_url | VARCHAR(500) | Nullable | URL to image/document (must be publicly accessible to WhatsApp) |
| media_caption | TEXT | Nullable | Caption for media |
| variables | JSON | Default '{}' | Custom variable values for template |
| schedule_type | ENUM('now','specific','recurring') | Default 'now' |
| scheduled_at | TIMESTAMP | Nullable | When to send (if specific) |
| recurring_pattern | JSON | Nullable | For recurring: {interval: 'daily|weekly|monthly', days: [], end_date} |
| status | ENUM('draft','scheduled','running','completed','failed','paused') | Default 'draft' |
| total_recipients | INT | Default 0 | Snapshot of contact count at campaign start |
| messages_sent | INT | Default 0 | Incremented as messages are sent |
| messages_failed | INT | Default 0 | Incremented on failures |
| started_at | TIMESTAMP | Nullable | When first send began |
| completed_at | TIMESTAMP | Nullable | When all messages finished |
| error_message | TEXT | Nullable | If failed |
| created_at | TIMESTAMP | | |
| updated_at | TIMESTAMP | | |

**Indexes**:
- `idx_user_status` (user_id, status)
- `idx_scheduled_at` (scheduled_at) - for queue worker scanning
- `idx_wa_session` (wa_session_id)

**Notes**:
- When campaign created: status `draft`
- When user clicks "Send Now" or scheduled time arrives: status `scheduled` → `running` after first message
- Job: `SendCampaignJob` processes all contacts linked to campaign
- Snapshot `total_recipients` at campaign start so it remains constant even if contact list changes later

---

### campaign_messages

Junction table linking campaigns to contacts, with per-recipient status.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | BIGINT UNSIGNED | PK, AI | Message ID |
| campaign_id | BIGINT UNSIGNED | FK → campaigns.id, OnDelete CASCADE | |
| contact_id | BIGINT UNSIGNED | FK → contacts.id, OnDelete CASCADE | |
| status | ENUM('pending','queued','sent','delivered','read','failed','bounced') | Default 'pending' |
| whatsapp_message_id | VARCHAR(255) | Nullable | Message ID from WhatsApp (if available) |
| sent_at | TIMESTAMP | Nullable | When API call returned success |
| delivered_at | TIMESTAMP | Nullable | From webhook delivery receipt |
| read_at | TIMESTAMP | Nullable | From webhook read receipt |
| failure_reason | TEXT | Nullable | Error message |
| attempts |INT | Default 0 | How many times retried |
| last_attempt_at | TIMESTAMP | Nullable | |
| rendered_message | TEXT | Nullable | Actual text sent after variable substitution |
| created_at | TIMESTAMP | | |
| updated_at | TIMESTAMP | | |

**Indexes**:
- `idx_campaign_status` (campaign_id, status) for campaign progress queries
- `idx_contact` (contact_id)
- `idx_status` (status) for retry/recovery jobs
- Unique: `campaign_id` + `contact_id` (one message per contact per campaign)

**Notes**:
- When campaign starts: create a row for each contact (bulk insert), status `pending`
- Job picks `pending` rows, marks `queued`, sends, updates to `sent` or `failed`
- Webhooks update `delivered_at`/`read_at` if available
- Retry logic: if `failed` and `attempts < 3` and not permanent error (like banned), requeue after delay

---

### subscriptions

Stripe subscription tracking. Optional if you want to keep detailed history.

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | BIGINT UNSIGNED | PK, AI | |
| user_id | BIGINT UNSIGNED | FK → users.id, Unique | One-to-one with user |
| stripe_subscription_id | VARCHAR(255) | Unique | Stripe subscription object ID |
| stripe_price_id | VARCHAR(255) | | Plan price ID |
| status | VARCHAR(50) | | Stripe status (active, canceled, etc.) |
| current_period_start | TIMESTAMP | | |
| current_period_end | TIMESTAMP | | |
| cancel_at_period_end | BOOLEAN | Default false | |
| canceled_at | TIMESTAMP | Nullable | |
| ended_at | TIMESTAMP | Nullable | |
| created_at | TIMESTAMP | | |
| updated_at | TIMESTAMP | | |

**Notes**:
- Sync with Stripe webhooks to keep this table current
- Used to compute `users.messages_limit` and `max_sessions` based on plan

---

### message_logs

Audit trail for all message sending attempts (for debugging, compliance).

| Column | Type | Constraints | Description |
|--------|------|-------------|-------------|
| id | BIGINT UNSIGNED | PK, AI | |
| user_id | BIGINT UNSIGNED | FK → users.id | |
| campaign_id | BIGINT UNSIGNED | FK → campaigns.id, Nullable | |
| campaign_message_id | BIGINT UNSIGNED | FK → campaign_messages.id, Nullable | |
| wa_session_id | BIGINT UNSIGNED | FK → wa_sessions.id | |
| direction | ENUM('inbound','outbound') | | |
| from_number | VARCHAR(20) | | |
| to_number | VARCHAR(20) | | |
| message_type | VARCHAR(50) | | text, image, etc. |
| content_summary | VARCHAR(255) | | Truncated text for quick view |
| media_url | VARCHAR(500) | Nullable | |
| status | VARCHAR(50) | | raw status from WhatsApp |
| raw_response | JSON | Nullable | Full webhook payload |
| logged_at | TIMESTAMP | Default CURRENT_TIMESTAMP | |

**Indexes**:
- `idx_user_time` (user_id, logged_at DESC)
- `idx_to_number` (to_number)
- `idx_from_number` (from_number)

**Notes**:
- Do NOT store full message content if privacy is a concern; keep only summaries
- Could also be used for STOP detection (inbound messages)

---

## Enums Reference

### wa_sessions.status
- `creating`: Session record created, OpenWA not yet started
- `qr_ready`: QR code generated, waiting for user scan
- `connected`: WhatsApp is connected and ready to send
- `disconnected`: WhatsApp logged out (temporary, may auto-reconnect)
- `banned`: Number banned by WhatsApp (permanent, need new number)
- `error`: Unrecoverable error (Check logs)

### campaigns.status
- `draft`: User is editing, not yet scheduled
- `scheduled`: Scheduled for future time
- `running`: Currently sending messages
- `completed`: All messages sent (success or final failure)
- `failed`: Campaign aborted due to critical error
- `paused`: User paused; can be resumed

### campaign_messages.status
- `pending`: Created but not yet queued
- `queued`: Job picked up, about to send
- `sent`: API call returned success (delivery pending)
- `delivered`: WhatsApp confirmed delivery to recipient device
- `read`: Recipient read the message
- `failed`: Send failed, may retry
- `bounced`: Permanent failure (blocked, invalid number)

### campaigns.message_type
- `text`: Plain text only
- `image`: Image with optional caption
- `document`: File (PDF, etc.)
- `template`: WhatsApp template (requires pre-approval, not supported in OpenWA)

### campaigns.schedule_type
- `now`: Send immediately upon clicking "Send"
- `specific`: One-time at `scheduled_at`
- `recurring`: Recurring pattern defined in `recurring_pattern`

---

## Migration Scripts (Laravel)

Example migration for `wa_sessions`:

```php
Schema::create('wa_sessions', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->onDelete('cascade');
    $table->char('uuid', 36)->unique();
    $table->string('name', 100);
    $table->string('phone_number', 20)->nullable()->unique();
    $table->string('session_id', 100)->unique();
    $table->enum('status', ['creating','qr_ready','connected','disconnected','banned','error'])->default('creating');
    $table->string('qr_code_path', 500)->nullable();
    $table->timestamp('last_connected_at')->nullable();
    $table->timestamp('last_disconnected_at')->nullable();
    $table->text('ban_reason')->nullable();
    $table->json('metadata')->nullable();
    $table->timestamps();

    $table->index(['user_id']);
    $table->index(['status']);
    $table->index(['phone_number']);
});
```

All other tables follow similar pattern.

---

## Seed Data

Optional initial data:
- Admin user (you)
- Default subscription tiers (code-based, not DB)
- Sample contacts and campaign for testing

---

## Backup Strategy

- Database: Daily mysqldump or pg_dump, keep 30 days
- Session files: Periodic tar of `storage/app/sessions/` (encrypted, offsite)
- Media uploads: If stored locally, backup separately; consider S3 for durability

---

Next: See `docs/api-spec.md` for REST endpoints.