# WhatsApp Ticketing System

A self-hosted, real-time WhatsApp support platform. Every inbound WhatsApp message becomes a structured ticket. Agents reply from a polished dashboard. Runs with a single `docker-compose up --build`.

---

## Quick Start

### 1. Copy and configure environment variables

```bash
cp .env.example .env
```

Generate secrets:
```bash
sh utils/generate-keys.sh
```

Edit `.env` and set all values. For `ANON_KEY` and `SERVICE_ROLE_KEY`, follow the [Supabase self-hosting guide](https://supabase.com/docs/guides/self-hosting/docker#generate-and-configure-api-keys).

### 2. Start everything

```bash
docker compose up --build
```

### 3. Seed the admin user

```bash
docker compose exec api npm run seed
# Default: admin@example.com / Admin123! (same as docker-compose API env defaults)
# Set ADMIN_EMAIL and ADMIN_PASSWORD in .env to override
```

### 4. Access

Set **`PUBLIC_SITE_HOST`** in `.env` to the hostname or IP the **browser** uses (`localhost`, LAN IP, or domain). **`docker-compose.yml`** then aligns public URLs with **`PUBLIC_SITE_HOST`** and the **`_*_HOST_PORT`** variables (Kong, API, UI) so the stack only talks to itself.

| Service | URL pattern |
|---------|------------|
| App | `http://<PUBLIC_SITE_HOST>:<FRONTEND_PORT>` (default port **3000**) |
| API | `http://<PUBLIC_SITE_HOST>:<API_HOST_PORT>` (default **3001**) |
| Supabase (Kong) | `http://<PUBLIC_SITE_HOST>:<KONG_HTTP_HOST_PORT>` (default **8001**) |
| Supabase Studio | `http://<PUBLIC_SITE_HOST>:<STUDIO_HOST_PORT>` (default **8002**) |

You usually **do not** need to set **`VITE_*`**, **`SUPABASE_PUBLIC_URL`**, **`SITE_URL`**, or **`FRONTEND_URL`** by hand—compose derives them. Override those only for **https**, a reverse proxy, or unusual paths. After changing ports or **`PUBLIC_SITE_HOST`**, rebuild the frontend image (`docker compose build frontend`).

### Multiple Docker stacks on one machine

Each stack should use a **unique** **`COMPOSE_PROJECT_NAME`** (e.g. `wa-ticketing-b`) so networks and named volumes stay isolated, and a **free port block** in `.env`:

| Variable | Default | Service |
|----------|---------|---------|
| `PUBLIC_SITE_HOST` | `localhost` | Hostname/IP used in browser URLs |
| `FRONTEND_PORT` | 3000 | React UI |
| `API_HOST_PORT` | 3001 | Ticketing API (Node) |
| `STUDIO_HOST_PORT` | 8002 | Supabase Studio |
| `KONG_HTTP_HOST_PORT` | 8001 | Kong (Supabase API gateway) |
| `KONG_HTTPS_HOST_PORT` | 8444 | Kong HTTPS |
| `POSTGRES_HOST_PORT` | 5432 | Postgres |
| `POOLER_HOST_PORT` | 6543 | Supavisor pooler |
| `ANALYTICS_HOST_PORT` | 14000 | Logflare / analytics |

Changing ports updates the **derived** Kong and API URLs automatically. For **hybrid dev** (Vite on `:5173` + API in Docker), set **`CORS_ORIGIN`** to a comma-separated list including your Vite origin (compose’s default CORS allows only the stack’s UI origin).

**Scope & user guide (HTML, with screenshots):** [docs/scope_wa_ticketing_system.html](docs/scope_wa_ticketing_system.html)

The **WhatsApp worker** is not exposed on the host by default (the API calls it as `http://whatsapp:3002` inside Docker). That avoids clashes when something else already uses port 3002. To hit the worker from your machine, add a `ports` mapping in `docker-compose.override.yml`.

### Troubleshooting: `password authentication failed` for `supabase_auth_admin` (or other `supabase_*` roles)

Postgres **role passwords are set when the data volume is first initialized**. Changing **`POSTGRES_PASSWORD`** in `.env` afterwards updates compose env vars (connection strings) but **does not** automatically change passwords stored inside the existing database—so GoTrue/REST/analytics may fail with SASL / `28P01`.

**Fix (keep data):** from the repo root, with the `db` container running:

```bash
npm run sync:db-passwords
```

The script connects as **`supabase_admin`** (the superuser in the Supabase Postgres image; `postgres` is not superuser and cannot `ALTER` reserved roles).

Then recreate dependent services:

```bash
docker compose up -d --force-recreate auth rest realtime storage meta analytics supavisor
```

**Alternative (empty dev DB acceptable):** remove the project volumes and bring the stack up again so init scripts re-run with the current `POSTGRES_PASSWORD` (this **destroys local DB data**):

```bash
docker compose down -v
docker compose up -d --build
```

See also: [Supabase — changing database password (self-hosted)](https://supabase.com/docs/guides/self-hosting/docker#changing-database-password).

---

## Architecture

```
React UI (3000)
    │  REST + WebSocket
Node API / Socket.IO (3001)
    │
    ├── authService      JWT + bcrypt
    ├── ticketService    Ticket lifecycle
    ├── messagingService Message persistence + ordering
    ├── contactService   E.164 normalisation + dedup
    ├── channelService   Baileys session management
    └── auditService     Immutable audit trail
    │
PostgreSQL 15 (Supabase self-hosted)
    │
Baileys WhatsApp Worker (per channel)
```

---

## Stack

| Layer | Technology |
|-------|-----------|
| API | Node.js 20 LTS + Fastify + Socket.IO |
| WhatsApp | @whiskeysockets/baileys |
| Frontend | React 18 + Vite + Shadcn UI + Tailwind CSS |
| Database | PostgreSQL 15 (self-hosted Supabase) |
| DB Client | postgres.js |
| Auth | Custom JWT (jsonwebtoken) + bcrypt |
| Real-time | Socket.IO |
| State | Zustand + React Query |

---

## Directory Structure

```
wa_support_node_v1/
├── apps/
│   ├── frontend/          React + Vite + Shadcn UI
│   └── api/               Node.js + Fastify + Socket.IO
├── services/
│   └── whatsapp/          Baileys worker
├── packages/
│   └── shared/            Shared TypeScript types
├── supabase/
│   └── migrations/        001_users → 014_signup_sessions_company_codes
├── volumes/               Supabase runtime (git-ignored)
├── utils/
│   └── generate-keys.sh
├── docker-compose.yml
└── .env.example
```

---

## Public signup (login page chat)

From **Sign in**, users can open **Sign up** and complete a short scripted chat. The API emails a verification link; the user sets a password on `/signup/complete` and becomes a `company_user`.

- **Apply migration** `014_signup_sessions_company_codes.sql` (adds `signup_sessions`, `users.phone`, `companies.account_code`).
- **SMTP** must be configured on the API (`SMTP_HOST`, `SMTP_FROM`, …) or the flow cannot send mail.
- **`FRONTEND_PORT`** — host port for the UI (default `3000`); set in the monorepo root `.env` for Docker and for local Vite (the dev server reads it via `loadEnv` from the repo root).
- **`FRONTEND_URL`** — optional override for signup email links; compose defaults to `http://PUBLIC_SITE_HOST:FRONTEND_PORT`.
- **`TURNSTILE_SECRET_KEY`** (optional) — if set, the first signup chat POST must pass Cloudflare Turnstile verification (wire a site key + token on the client when you enable this).

Rate limit: **40 requests/minute** per IP on `/api/v1/signup/*`.

---

## Adding a WhatsApp Channel

1. Log in as admin
2. Navigate to **Channels** → **Add Channel**
3. Enter a name and phone number
4. Scan the QR code in WhatsApp (Linked Devices → Link a Device)
5. The channel status turns **connected**

---

## Channel settings, onboarding, and AI

### Database migrations

**Automated:** `./deploy.sh` or `deploy.bat` (repo root). On Linux: `chmod +x deploy.sh`, then run **`./deploy.sh` as the user that owns the clone** (not `sudo ./deploy.sh`) so Git does not report *dubious ownership*; default is `git pull --ff-only` without sudo. They run `git pull`, **`docker compose down`** (stop this project’s stack so ports/names are free), then `docker compose build` / `up`, and `node scripts/apply-sql-migrations.mjs`, which applies `supabase/migrations/*.sql` in order and records filenames in `public._wa_sql_migrations` (skips already applied; tolerates duplicate errors if initdb already created objects). Override compose command with `DOCKER_COMPOSE` (deploy.sh uses `DOCKER`). Use `SKIP_MIGRATIONS=1` to skip migrations, `SKIP_STACK_STOP=1` to skip the `down` step. Manual run: `npm run migrate:sql` after `docker compose up -d`.

On the bundled Supabase Postgres image, app tables are often owned by **`supabase_admin`**, not `postgres`. The migration script defaults to **`supabase_admin`**. If you apply SQL by hand and `psql -U postgres` fails with “must be owner”, use:

```bash
docker compose exec -T db psql -U supabase_admin -d postgres < supabase/migrations/013_email_channels_alerts.sql
```

### Email channels

- **Add email** under Channels (admin). Inbound uses **HTTP POST** JSON to a secret URL — no POP/IMAP worker. Configure **`SMTP_*`** on the API to send agent replies and automated messages to the customer’s address.
- Webhook: `POST {API}/api/v1/inbound/email/{inbound_token}` with body `{ "from", "text", "subject?", "from_name?", "message_id?" }`.
- **Staff alerts** (all channel types): per channel, enable **SMS** (optional **Twilio** `TWILIO_*` on the API) and/or **staff email** (SMTP). **Escalation** can send email first then SMS and **flag the ticket** for follow-up (tick off in the ticket sidebar).

**Web chat** (`Web chat` / `portal:system`) appears in **Channels** as always on. It does not use WhatsApp. Onboarding prompts, AI, and ticket-closing text are **inherited from your oldest WhatsApp channel** (by creation date). Edit that WhatsApp channel to change web chat behaviour. Company portal messages run the same automated onboarding and AI flow as WhatsApp.

1. Open **Channels** and click a channel (or go to `/channels/{id}`).
2. **Onboarding** (admin only): enable the flow to send up to four WhatsApp prompts for a contact’s **first ticket on that channel** only: who they are, company, query, and query status. Answers are stored on the ticket (visible in the ticket sidebar).
**Onboarding checklist:** turn on **Enable onboarding flow** on the channel detail page and **Save**. Link WhatsApp so messages flow. Preset prompts run only for a contact’s **first ticket ever on that channel** (returning customers with past tickets here won’t see the flow again). If replies still don’t arrive, check API audit for `message.automated_failed` and rebuild the **whatsapp** image after worker changes.

3. **AI** (admin only): enable automated replies using OpenAI after onboarding completes (or immediately if onboarding is off). Configure model (dropdown lists models returned by OpenAI when a key is configured), system prompt, and a plain-text knowledge base per channel.
4. **OpenAI credentials** (admin): under **Settings → OpenAI**, save an API key to `platform_settings` in the database (optional base URL). The API prefers this key over the environment variable. Alternatively, set on the **API** host only:

   - `OPENAI_API_KEY` — used when no database key is stored.
   - `OPENAI_BASE_URL` — optional; default `https://api.openai.com/v1` (use for Azure OpenAI or compatible proxies). Can also be set in Settings.

Apply new DB columns on an existing database (if `postgres` is not the table owner, use `supabase_admin` or the owner user):

```bash
docker compose exec -T db psql -U supabase_admin -d postgres -c "ALTER TABLE public.channels ADD COLUMN IF NOT EXISTS settings JSONB NOT NULL DEFAULT '{}'; ALTER TABLE public.tickets ADD COLUMN IF NOT EXISTS intake JSONB NOT NULL DEFAULT '{}';"
```

Fresh Postgres volumes run app SQL from `docker-compose.yml` through `008_platform_settings.sql` (OpenAI row in `platform_settings`).

**Existing volumes** created before `008` was added: apply once with  
`docker compose exec -T db psql -U postgres -d postgres < supabase/migrations/008_platform_settings.sql`  
(or paste the file in `psql`). The migration is idempotent (`IF NOT EXISTS` / `ON CONFLICT DO NOTHING`).

### Shared package build

`@wa-ticketing/shared` compiles to `packages/shared/dist` (Node cannot load `.ts` at runtime). Root `npm run build` builds shared first, then all workspaces. After `npm ci` / clone, run `npm run build` (or `npm run build --workspace=@wa-ticketing/shared`) before starting the API or running a production frontend build locally.

### E2E smoke tests (Playwright)

With the app on **http://localhost:3000** and API on **http://localhost:3001**:

```bash
npx playwright install
npm run test:e2e
```

See [docs/testsprite_run.md](docs/testsprite_run.md) for TestSprite cloud status and troubleshooting. Product spec: [PRD_v2_WhatsApp_Ticketing_System.md](PRD_v2_WhatsApp_Ticketing_System.md); TestSprite inputs: `testsprite_tests/standard_prd.json`, `testsprite_tests/testsprite_frontend_test_plan.json`.

---

## Roles

| Role | Access |
|------|--------|
| `admin` | All screens including User Management, Audit Log, channel add/remove, Companies |
| `user` | View/reply to tickets, manage contacts, view channels, Companies |
| `company_user` | Dashboard + Tickets for their company only; messages via **Web Portal** (no WhatsApp). Created under **Companies → portal users**. |

---

## Companies and Web Portal

- **Companies** (staff): sidebar → Companies. Create or edit companies; view contact numbers and channel numbers from tickets; create **portal users** (email/password) for a company.
- **WhatsApp intake**: when onboarding captures a company name, a `companies` row is upserted and the contact is linked (`contacts.company_id`).
- **Portal channel**: migration `009` seeds a synthetic channel `Web Portal` (`phone_number` `portal:system`). It never connects to Baileys; tickets from company portal logins use this channel and `portal:{userId}` contacts.
- **Portal contacts**: migration `010` widens `contacts.phone_number` to `VARCHAR(64)` so values like `portal:{uuid}` fit (the column was `VARCHAR(20)` and blocked portal messaging). Apply on existing DBs the same way as `009` (PowerShell: pipe `010_contact_phone_portal_length.sql` into `psql -U supabase_admin`).
- **Portal display names**: migration `011` adds nullable `users.display_name` for portal users. Tickets show **company name** as the title, **name · email** under it and on inbound portal message footers. Optional **Display name** when creating a portal user under Companies.
- **Tickets**: staff reply on portal tickets from the ticket UI (messages stay in-app, not sent to WhatsApp). Company users reply via `POST /api/v1/portal/messages` (same thread; one open ticket per portal user until closed).
- **Ordering**: ticket lists sort by `updated_at` (bumped when messages arrive).

Apply `009` on existing databases. In this stack, `public.users` / `tickets` / `messages` are owned by **`supabase_admin`**, so use that role (not `postgres`) for `ALTER`:

```bash
docker compose exec -T db psql -U supabase_admin -d postgres -f - < supabase/migrations/009_companies_and_portal.sql
```

On Windows PowerShell:

```powershell
Get-Content supabase/migrations/009_companies_and_portal.sql -Raw | docker compose exec -T db psql -U supabase_admin -d postgres
```

---

## Development (without Docker)

```bash
# Install all dependencies
npm install

# Start API (requires DATABASE_URL in .env)
npm run dev:api

# Start frontend dev server
npm run dev:frontend

# Start WhatsApp worker
npm run dev:whatsapp
```

---

## Useful Commands

```bash
# View logs
docker compose logs -f api
docker compose logs -f whatsapp

# Restart a single service
docker compose restart api

# Stop (data preserved)
docker compose down

# Wipe all data (destructive)
docker compose down -v
```

---

## System Requirements

| Resource | Minimum | Recommended |
|----------|---------|-------------|
| RAM | 4 GB | 8 GB+ |
| CPU | 2 cores | 4 cores+ |
| Disk | 50 GB SSD | 80 GB+ SSD |