# Project Memory - Database Notes

## Last Updated: 2026-05-29

---

## Connection
- **Driver**: SQLite
- **Config**: `DB_CONNECTION=sqlite` in `.env`

## Key Tables

### conversations
- `id`, `contact_id`, `assigned_to`, `team_id`, `status`, `priority`
- `unread_count` (integer, default 0) — tracks unread messages
- `last_read_at` (timestamp, nullable) — last time agent read
- `last_message_at` (timestamp) — for sorting by latest activity
- `created_at`, `updated_at`
- **Scopes**: `AssignedScope` (filters by agent for non-admin); `system()` bypasses scope

### contacts
- `id`, `name`, `phone`, `email`, `notes`
- `created_at`, `updated_at`

### messages
- `id`, `conversation_id`, `contact_id`, `user_id`
- `body` (text) — message content
- `type` — incoming / outgoing / system
- `status` — sent / delivered / read / failed
- `whatsapp_message_id` — WhatsApp message SID
- `created_at`, `updated_at`

### assignments
- `id`, `conversation_id`, `agent_id`, `team_id`
- `assigned_by`, `assigned_at`, `unassigned_at`
- `status` — active / completed / transferred

### teams
- `id`, `name`, `description`, `supervisor_id`
- `created_at`, `updated_at`

### users
- Standard Laravel users + Spatie roles
- Roles: `admin`, `supervisor`, `agent`

### webhook_events
- `id`, `event_type`, `payload` (json), `processed`
- `processed_at`, `created_at`

## Migrations
- `2026_05_28_172213_add_new_message_tracking_to_conversations_table.php`
  - Added `unread_count` (integer, default 0)
  - Added `last_read_at` (timestamp, nullable)

## Queue Tables
- `jobs` — Queue jobs (QUEUE_CONNECTION=database)
- `failed_jobs` — Failed job tracking