# Devwebs WhatsApp CRM — Architecture & Reference Inspection

## 1. Reference inspection (Ojentra screenshots)

The "existing UI project" is the set of 40 Ojentra/WhatsJet demo screenshots. No source code
existed, so the platform is built from scratch with original Devwebs branding (no Ojentra assets).

| Reference screen | What it does | Devwebs module | Phase |
|---|---|---|---|
| Dashboard (8 stat cards + Quick Start) | Tenant KPIs | Customer dashboard | 1 (cards wired to real counts as modules land) |
| Contacts › List | Table, search, per-row Details/Edit/Send Template/Chat/Delete | Contacts | **1 — built** |
| Add/Edit Contact modal | Name, country, phone (CC, no 0/+), language, email, groups | Contacts | **1 — built** |
| Contact Details modal | Read-only fields + Groups + Other Information (custom fields) | Contacts | **1 — built** |
| Export Contacts modal | Export with data / blank template (XLSX) | Contacts | **1 — built** |
| Import Contacts modal | XLSX, phone = unique key (upsert), groups comma-separated, never removed | Contacts | **1 — built** |
| Contacts › Custom Fields / Groups | Manage extra fields and groups | Contacts | **1 — built** |
| Delete confirmation | "Are you sure" dialog | Design system | **1 — built** |
| WhatsApp Chat (3-panel inbox) | Conversations, chat, contact panel, assign, labels, notes, 24h window | Team Inbox | **3 — built** |
| Send Template Message | Per-contact template send | Templates | **3 — built** |
| Templates | Sync/approval | Templates | **3 — built** |
| Campaigns list / create (2 steps) / report | Scheduled bulk template sends + delivery stats | Campaigns | 4 |
| Bot Replies (Simple / Media / Advance Interactive) | Triggered auto replies | Bot Replies | 5 |
| Bot Flows + Flow Builder canvas | Node graph of replies routed by button IDs | Flow Builder + engine | 5–6 |
| Team Members (System Users) | Staff + roles | Foundation | 1 (API), UI later |
| My Subscription / Wallet | Billing | Billing | 7 |
| Settings › Cloud API Setup | Connect number, webhook | WhatsApp setup | **2 — built** |
| Settings › AI Chat Bot, API Access | Integrations | AI, API keys | 8 |

## 2. Stack (changed from the master prompt)

Packagist/GitHub are blocked in the build workspace, so by decision of the project owner the
backend is **Node.js** instead of Laravel:

- **API:** Node 22, Express 4/5, TypeScript, Zod validation
- **DB:** Knex query builder + migrations. **MySQL 8 in production** (`mysql2`), SQLite for local dev/tests. Migrations use only portable column types.
- **Auth:** opaque session tokens (SHA-256 hashed in `sessions` table) in an httpOnly, SameSite=Lax cookie — same model as Laravel Sanctum SPA auth. Passwords: Node `crypto.scrypt`.
- **Tenancy:** every tenant-owned row has `organization_id`; all queries go through `tenantQuery(req, table)` which injects the filter. Cross-tenant IDs return 404.
- **Authorization:** role → permission map (`src/auth/permissions.ts`), enforced by `can('contacts.manage')` middleware.
- **Queues (later phases):** BullMQ on Redis for campaigns, webhooks, flow delays.
- **Real-time (later):** Socket.IO for inbox.
- **Frontend:** Next.js 16 (App Router) + React + TypeScript + Tailwind. `/api/*` is rewritten to the API so cookies stay first-party.
- **Flow builder (later):** React Flow (`@xyflow/react`).
- **Payments (later):** Razorpay with server-side signature/webhook verification; money stored as integer paise.

## 3. Roles

`super_admin`, `finance_admin`, `support_admin` (platform org), `reseller`, `customer_owner`,
`customer_staff`. Platform roles live on `users.platform_role`; tenant roles on
`organization_members.role`. Organizations have `type` = `platform | reseller | customer` and
`parent_id` (reseller → customer ownership). Phase 7 adds `reseller_staff` (read-only reseller portal) and the
`billing.*`, `reseller.*` and `platform.*` permissions: `finance_admin` handles payments, refunds and reconciliation;
`support_admin` is view-only. Customer workspaces have four roles: `customer_owner`, `customer_admin`,
`customer_agent` (formerly `customer_staff`) and `customer_viewer`. See docs/TENANCY.md for the full matrix.

## 4. Data model (Phase 1)

`organizations`, `users`, `organization_members`, `sessions`, `audit_logs`,
`contacts`, `contact_groups`, `contact_group_members`, `custom_fields`,
`contact_custom_values`, `tags`, `contact_tags`, `contact_activities`.

Key constraints: `unique(organization_id, phone)` on contacts; `unique(organization_id, name)` on
groups/tags/custom field keys; FK cascades; indexes on `(organization_id, created_at)`.

## 4b. Data model (Phase 2)

`whatsapp_accounts` (one row per phone number; unique `phone_number_id`; encrypted token/app secret/verify token;
random `webhook_key`), `whatsapp_api_logs`, `webhook_events` (unique `event_key` for dedup), `conversations`
(unique per number + contact; `last_inbound_at` drives the 24h window), `messages` (unique `wa_message_id`).

## 4c. Data model (Phase 3)

`message_templates` (per WABA; unique org+waba+name+language; components JSON as Meta returns them),
`conversation_notes`, `conversation_events`; `messages` gained template/media columns
(`media_id`, `media_storage_key`, ...); `conversations` gained `last_message_preview/direction`, `closed_at/by`.

## 4d. Data model (Phase 4)

`campaigns` (template, number, audience JSON, bindings JSON, status draft/scheduled/running/paused/completed/cancelled,
`scheduled_at` in UTC + the chosen `timezone`, `last_error` for automatic pauses) and `campaign_recipients`
(one row per phone, unique `campaign_id + phone`; status pending/sending/sent/failed/skipped, skip reason, attempts,
`next_attempt_at`, `claim_token`, link to the `messages` row whose webhook status drives delivered/read).
The send queue is these tables plus an in-process worker (see README, Campaigns) rather than BullMQ, so a
deployment needs only Node and MySQL.

## 4e. Data model (Phase 5)

`bot_assets` (private files for replies and flow steps), `media_uploads` (Meta media id per number + asset, reused
until near expiry), `bot_replies` (type, trigger type/subject + normalised form, content JSON, number scope, status,
send counters), `bot_flows` (trigger, status, draft JSON with a revision counter, published version pointer),
`bot_flow_versions` (immutable published definitions, unique flow + version). `messages` gained `bot_reply_id` and
`bot_flow_id`; `conversations` gained `automation_paused` (human handoff). Phase 6 adds flow executions and steps
pinned to a version.

## 4f. Data model (Phase 6)

`flow_runs` (flow, pinned version, conversation, contact, status running/waiting/completed/handed_off/failed/
cancelled/expired, current step, what it waits for and until when, vars such as last_reply, attempts, DB claim)
and `flow_run_steps` (per-step history with outcome, detail and the message sent). The interpreter
(`modules/bots/engine.ts`) has no database access; `runner.ts` supplies real effects and `preview.ts` dry-run
effects, so preview and production run the same code.

## 4g. Data model (Phase 7)

`platform_settings` (key/value; secrets such as Razorpay keys encrypted), `plans` (price, interval, trial days,
limits and features as JSON), `subscriptions` (one per org: trialing/active/past_due/suspended/cancelled, period,
grace end, auto-renew), `resellers` (commission and max markup in basis points), `reseller_plan_prices`, `wallets`
and `ledger_entries` (append-only: signed amount, balance after, unique idempotency key, reference), `payments`
(Razorpay order/payment ids, receipt, purpose recharge/subscription, subtotal, tax, total; unique on each
provider id), `refunds`, `billing_profiles` (GSTIN, state code), `invoice_counters` + `invoices` (number per
financial year, line items, CGST/SGST/IGST, seller and buyer snapshots), `pricing_rules` (category + country prefix:
customer price in paise, provider cost in micro-rupees), `usage_records` (one per template message:
estimated → billable/free → charged, or void), `commission_entries` (commission, markup, reversal, payout; unique
idempotency key) and `notifications`. Money is integer paise throughout. The wallet balance always equals the sum of
its ledger, and reconciliation checks this.

Billing flow: browser checkout → server signature check + payment fetch → one transaction that credits the wallet
or activates the plan, issues the invoice and records reseller earnings. The Razorpay webhook (HMAC over the raw
body, de-duplicated on event id) repeats the same idempotent fulfilment. Usage: estimate at send, reconcile from
Meta's status `pricing` object, debit the wallet in batches (`modules/billing/usage.ts`, `subscriptions.ts`
worker).

## 4h. Tenancy hardening (7b)

`db/tenancy.ts` classifies every table (platform / tenant / tenant_optional) and lists every cross-table reference.
Migration `000009_tenancy`:
- adds `organization_id` to the seven tables that lacked it, plus indexes;
- makes the idempotency keys and WhatsApp message ids unique per tenant / number;
- adds `organizations.data_location`;
- generates 201 integrity triggers (SQLite and MySQL) rejecting cross-tenant references and owner changes.

`tenancy/context.ts` carries the tenant through requests, workers and webhooks (AsyncLocalStorage) and guards wallet,
storage, real-time and WhatsApp calls. `tenancy/connections.ts` resolves each tenant's database (shared today; fails
closed for unregistered dedicated databases). `tenancy/integrity.ts` backs `npm run tenancy:check`. Automated
cross-tenant suites: `tests/isolation.test.ts`, `tests/isolation-refs.test.ts`. Full write-up: docs/TENANCY.md.

## 4i. Data model (Phase 8)

- **Public API:** `api_keys` (prefix + SHA-256, scopes, rate limit, expiry, rotation grace, revocation), `api_requests` (30-day request log) and `api_idempotency` (stored responses for Idempotency-Key, 24 h).
- **Outgoing webhooks:**
  - `webhook_endpoints` (encrypted secret plus the previous one during rotation, subscribed events, auto-disable state).
  - `outgoing_events` (outbox; unique event id and per-tenant dedupe key; written in the same transaction as the change).
  - `webhook_deliveries` (one per endpoint and event; status, attempts, backoff, claim) and `webhook_delivery_attempts` (log).
- **Notifications:** `notifications.category`, `user_notification_settings` (email per category per member) and `notification_emails` (outbox).
- **Support:** `support_tickets` (customer organization, assigned platform staff) and `support_ticket_messages` (customer/support side, internal notes).
- **Health:** `system_heartbeats` (platform table: last run, duration and error per worker).

All new tenant tables are registered in `db/tenancy.ts`, so they carry the integrity triggers and are covered by the isolation tests.

Workers (`services/workers.ts`) cover:
- campaigns, flows, billing, webhooks, email, support auto-close;
- WhatsApp health checks, retention housekeeping, watchdog.

Each records a heartbeat for Admin → System health.

## 4j. Security and operations (Phase 9)

- **Platform tables:**
  - `auth_throttles`: sign-in and two-step failures per key (SHA-256 of the email or user), with lock-until.
  - `password_resets`: SHA-256 token hash, expiry, used-at.
  - `user_recovery_codes`: hashed, single use.
- **New columns:**
  - `users`: `totp_secret_encrypted` (AES-256-GCM), `totp_pending_secret_encrypted`, `totp_enabled_at`,
    `totp_last_step` (replay guard), `password_changed_at`.
  - `sessions`: `mfa_verified_at` (null while the second step is pending).
- **Workspace and messages:**
  - `organizations.message_retention_days` (null = keep).
  - `messages.redacted_at`: content and media erased by retention; the row stays.
- **Processes:** `PROCESS_ROLE` = `api`, `worker` or `all`.
  - The API is stateless apart from Redis (rate-limit counters, SSE pub/sub) when `REDIS_URL` is set.
  - Workers claim jobs from MySQL, so both roles scale horizontally.
- **Startup and shutdown:**
  - `services/preflight.ts` validates production configuration; errors stop the start.
  - `/api/ready` checks the database, pending migrations and Redis.
  - SIGTERM drains HTTP connections and waits for the current worker batches.
- **Deployment artefacts:**
  - Dockerfiles (non-root, health checks) and `docker-compose.yml` (MySQL 8.4, Redis 7, migrate, api, worker,
    web, Caddy).
  - `deploy/` holds the Caddyfile, nginx.conf, systemd units and the backup/restore scripts.

## 5. Phase plan

1. Foundation + design system + **Contacts & Groups** ← **done**
2. WhatsApp account connection (manual system-user token, encrypted; Embedded Signup endpoint ready pending Meta approval), webhook receiver with signature verification & event dedup, test messages, health checks ← **done**
3. Templates sync/builder/send + Team Inbox (3-panel, SSE real-time, 24h window rules, media, assignment, notes) ← **done**
4. Campaigns (wizard, database-backed queue, rate limits, retries, recipient tracking, report) ← **done**
5. Bot Replies (simple/media/interactive, trigger priority, live auto-replies) + Flow Builder UI (React Flow, validation, versions, publish) ← **done**
6. Flow execution engine (pinned versions, interactive reply ID routing, question matching, conditions, delays, retries, handoff, run history, preview) ← **done**
7. Subscriptions, wallet ledger, Razorpay (orders, checkout verification, webhooks, refunds), GST invoices, per-message usage billing, plan limits, platform admin portal, reseller portal and commissions ← **done**
   7b. Multi-tenant isolation hardening: roles, tenant context, integrity triggers, cross-tenant test suite (SQLite + MySQL) ← **done**
8. Public REST API (scoped, rotatable keys, rate limits, request logs, idempotency), signed outgoing webhooks (outbox, retries, delivery logs, SSRF-safe), analytics + CSV exports, email notifications with preferences, support tickets, system health ← **done**
9. Security hardening (sign-in lockout, password reset, TOTP two-step sign-in required for staff, session management, upload content checks, redacted structured logs, CSP/HSTS, per-workspace message retention), production tooling (preflight, readiness, graceful shutdown, process roles, Redis), Docker/VPS deployment, backups, deployment/security/integration/troubleshooting guides ← **done**