# Multi-tenancy and isolation

Devwebs is a multi-tenant SaaS. Every customer workspace is an **organization**, and nothing one organization owns is
reachable from another, whatever the request says. This document is the reference for the tenant hierarchy, data
ownership, the authorization flow, and the automated tests that prove isolation.

**Release gate:** the application is not production-ready unless `npm test` passes in `backend/`, including
`tests/isolation.test.ts` and `tests/isolation-refs.test.ts`, on SQLite **and** on MySQL
(`TEST_DB=mysql npx vitest run tests/isolation.test.ts tests/isolation-refs.test.ts`). In production, also run
`npm run tenancy:check` after migrations and after any manual data repair.

---

## 1. Tenant hierarchy

```
Platform organization (type=platform) ─ Devwebs staff: Super Admin, Finance Admin, Support Admin
│   (platform roles live on users.platform_role; they act on any organization through /api/admin only)
│
├── Reseller organization (type=reseller) ─ members: Reseller owner, Reseller staff
│     ├── Customer organization (type=customer, parent_id = reseller) ─ Owner, Admin, Agent, Viewer
│     └── Customer organization …
│
└── Customer organization (type=customer, parent_id = NULL) ─ direct customer
```

- `organizations` holds every tenant. `type` is `platform | reseller | customer`. `parent_id` links a customer to the
  reseller that owns it. A database trigger only allows `parent_id` on a customer, and only pointing at a reseller.
- A **user** is a platform-level identity (one login). A user can belong to several organizations through
  `organization_members (organization_id, user_id, role)`. Each session works in exactly one of them at a time
  (`sessions.current_organization_id`).
- Resellers are **not** members of their customers' workspaces. They manage customers (create, suspend, change plan,
  set prices, see payments and earnings) through `/api/reseller`, and never see the customers' contacts, chats or
  messages.
- `organizations.data_location` says which database holds the tenant's rows (`shared` today; see section 7).

## 2. Roles and permissions

Permissions are checked on the server for every route (`can(...)` in `src/auth/middleware.ts`). The frontend only
hides what the server would refuse anyway.

| Role | Scope | Can |
|---|---|---|
| **Owner** (`customer_owner`) | customer | Everything in the workspace, including wallet, plan and payments (`billing.manage`), and making or removing other owners |
| **Admin** (`customer_admin`) | customer | Everything an owner can except billing changes and owner-level team changes (can't add, change or remove an owner) |
| **Agent** (`customer_agent`) | customer | Inbox (reply, take chats), add and edit contacts; view templates, campaigns and bots. No delete, import or export |
| **Viewer** (`customer_viewer`) | customer | Read only: contacts, inbox, templates, campaigns, bots, team |
| **Reseller** (`reseller`) | reseller | Own customers, plan prices within the markup cap, earnings, reseller team |
| **Reseller staff** (`reseller_staff`) | reseller | Read only in the reseller portal |
| **Super Admin** | platform | All platform administration |
| **Finance Admin** | platform | Customers (view), payments, refunds, reconciliation, wallet adjustments |
| **Support Admin** | platform | Customers and audit log, view only |

Team rules (`src/modules/team/routes.ts`):
- Only an owner can grant the owner role, change an owner's role, or remove an owner.
- The last owner can't be removed or demoted, and nobody can change their own role.
- Removing a member returns their open chats to the queue and drops the workspace from their sessions.

## 3. Data ownership rules

`backend/src/db/tenancy.ts` classifies **every table**. The schema audit test fails if a table is added without being
classified there.

| Class | Tables | Rule |
|---|---|---|
| platform | `organizations`, `users`, `sessions`, `plans`, `platform_settings`, `pricing_rules`, `invoice_counters`, `system_heartbeats` | Global platform configuration and identity. No `organization_id`. Only platform staff can change them (`/api/admin`). |
| tenant | the rest: contacts, groups, group members, custom fields and values, tags and contact tags, activities, WhatsApp accounts (phone numbers), templates, conversations, messages, notes, events, campaigns, recipients, bot assets, media uploads, bot replies, bot flows, versions, executions (`flow_runs`, `flow_run_steps`), subscriptions, wallets, ledger entries, payments, refunds, invoices, billing profiles, usage records, notifications, memberships, reseller records, reseller prices, commission entries, API keys, API request logs, idempotency records, webhook endpoints, outgoing events, webhook deliveries and attempts, notification settings and emails, support tickets and messages | `organization_id NOT NULL`, indexed, foreign key to `organizations`, never changes after insert. |
| tenant_optional | `webhook_events`, `whatsapp_api_logs`, `audit_logs` | Owned by an organization when one is known. NULL only for platform-level rows (e.g. a webhook for a number nobody has connected). |

API credentials and outgoing webhooks (Phase 8) are tenant tables under the same rules. An API key belongs to its
organization; an outgoing event is only ever queued for that organization's own endpoints.

Rules enforced on top of `organization_id`:

1. **Same-tenant references.** Every reference between tenant rows (e.g. `messages.contact_id`,
   `campaign_recipients.campaign_id`, `contact_group_members.group_id`, `usage_records.ledger_entry_id`) must point at
   a row of the same organization. These are listed in `RELATIONS`.
2. **Members only.** A chat can only be assigned to a member of the organization.
3. **Reseller ownership.** Reseller records (`reseller_plan_prices`, `commission_entries`) belong to the reseller's
   organization. A commission's `customer_id` must be one of that reseller's customers.
4. **Single owner of WhatsApp assets.** A phone number (`phone_number_id`) and a WhatsApp Business Account (`waba_id`)
   can each be connected to one workspace only. Template events are per WABA, so sharing one would leak templates.
5. **Per-tenant uniqueness.** Unique keys that are really per tenant include `organization_id`:
   - `(organization_id, phone)` on contacts, `(organization_id, name)` on groups and tags, `(organization_id, key)` on
     custom fields, `(organization_id, waba_id, name, language)` on templates.
   - `(organization_id, idempotency_key)` on the wallet ledger and on commissions: a client-supplied key can never
     collide with another tenant's.
   - `(whatsapp_account_id, wa_message_id)` on messages. Webhook dedup keys also include the number.
6. **Money.** Wallet balances, ledger entries, payments, refunds, invoices, usage and commissions are only read
   through the owner's organization (or by platform staff). Wallet writes check the tenant context (section 5).
   Payment verification looks up the payment within the caller's organization, so replaying another tenant's signed
   checkout result returns 404 and moves no money.

## 4. Authorization flow (every API request)

```
request
 → helmet, rate limit, CSRF header check (X-Requested-With)
 → loadSession           control-plane DB: session token (hashed) → active user → membership of
                         sessions.current_organization_id, re-checked on EVERY request (removal is immediate)
 → requireAuth
 → requireOrg            organization must be active (else 423); resolves the tenant's database (data_location);
                         runs the rest of the request inside the tenant context (AsyncLocalStorage)
 → can(permission)       role → permission matrix
 → handler               every query filters by orgId(req), which only ever comes from the session
 → sinks                 wallet ledger, file storage, real-time events, WhatsApp API calls: assertTenant(org)
 → database              triggers reject cross-tenant references and owner changes
```

- **The client never chooses the organization.** `organization_id` in a body, query string or header (e.g.
  `X-Organization-Id`) is ignored. The only way to change it is `POST /api/auth/switch-organization`, which requires
  an existing membership.
- **Not found, not forbidden.** A record id from another organization behaves exactly like an id that doesn't exist
  (404), so ids can't be probed.
- **Platform routes** (`/api/admin`) run in a platform context: they need a platform role plus a platform permission,
  and every change is audited.
- **Reseller routes** (`/api/reseller`) run in the reseller's context. After the ownership check
  (`organizations.parent_id = reseller`) passes for a customer, the request may act on that customer only
  (`delegateTo`).
- **API keys** (`/api/v1`): the key, not a session, identifies the tenant.
  - It is looked up by prefix in the control-plane database and compared in constant time (current secret, or the
    previous one during a rotation grace period).
  - Revoked or expired keys, suspended organizations and plans without `api_access` are refused.
  - The request then runs inside the key's organization context with only the key's scopes. Cookies and
    `X-Organization-Id` headers are ignored.
  - Only an explicit allowlist of endpoints exists under `/api/v1`, and they forward to the same tenant-scoped
    handlers as the app.
- **Long-lived connections.** The inbox event stream (SSE) re-checks the session, the membership and the
  organization's status on every keep-alive (25 s) and closes as soon as one fails.

## 5. Tenant context in background work

`src/tenancy/context.ts` keeps the current tenant in `AsyncLocalStorage`. Background work sets it from the
`organization_id` of the row being processed, never from external input:

| Work | Context set from |
|---|---|
| Campaign worker (start, send, retries) | `campaigns.organization_id`, per campaign |
| Flow worker (delays, retries, timeouts) | `flow_runs.organization_id`, per run |
| Bot automation after a webhook | the WhatsApp account's organization, per message |
| WhatsApp webhooks (messages, statuses, templates) | the organization owning the phone number / WABA, per event |
| Inbound media download | the message's organization |
| Razorpay webhooks | the organization of the payment or refund in **our** records |
| Subscription renewals, grace, suspension | `subscriptions.organization_id`, per subscription |
| Usage charging | `usage_records.organization_id`, per organization batch |
| WhatsApp health checks | `whatsapp_accounts.organization_id`, per number |

`assertTenant(org)` guards the places where a mistake would cross tenants: wallet ledger writes, file storage
(`put`/`get`/`remove`), real-time publishing, and every Graph API call (so one tenant's work can never use another
tenant's WhatsApp credentials). A mismatch throws `TenantViolation`, which is logged and fails the operation.

## 6. Tenant-scoped resources

| Resource | How it is scoped |
|---|---|
| Database rows | `organization_id` on every tenant row + scoped queries + triggers |
| Files (media, bot assets) | Storage keys are `org-<id>/YYYY-MM/<random>`; reads verify the key's organization against the tenant context |
| Exports (contacts Excel, campaign CSV) | Generated per request from scoped queries; never written to shared disk |
| Search and filters | Contacts search, inbox filters, campaign audiences: always `WHERE organization_id = ?`; filter ids from another tenant match nothing; campaign audiences reject foreign groups |
| Analytics | Dashboard, campaign stats, billing usage: scoped by organization |
| Real-time channels | SSE channel `org:<id>`, subscribed from the session's organization, re-validated on every keep-alive |
| Caches | No shared caches. The in-memory per-conversation lock is keyed by globally unique conversation ids |
| Audit log | `audit_logs.organization_id` = the actor's organization; customers see only their own |
| Configuration | Platform settings (`platform_settings`: tax, invoice series, Razorpay keys, grace days) are separate from customer settings (billing profile, auto-renew, WhatsApp numbers, bot settings). Only platform staff can read or change platform settings |

## 7. Database

- **Shared MySQL database** with `organization_id` isolation. Migration `000009_tenancy` adds:
  - the missing `organization_id` columns (backfilled from parents) and indexes;
  - the per-tenant unique keys;
  - `organizations.data_location`;
  - **integrity triggers** generated from `RELATIONS`, so a missed application check fails loudly instead of linking
    two tenants' data. There are 201 triggers, and the same rules apply on SQLite.

  Triggers are used rather than composite foreign keys because many references are `ON DELETE SET NULL`. A composite
  key would null `organization_id` too.
- **MySQL privilege note:** creating triggers needs the `TRIGGER` privilege. With binary logging on (e.g. Amazon RDS),
  either the migration user needs `SUPER`, or `log_bin_trust_function_creators=1` must be set in the parameter group.
- **Integrity check:** `npm run tenancy:check` scans every relation for cross-tenant references and exits 1 if it
  finds any.

### Dedicated databases for enterprise customers (future)

`src/tenancy/connections.ts` resolves the database for each request from `organizations.data_location`:

- `shared` uses the main database.
- Any other value must be registered with `TenantConnections.register(name, knex)`. Otherwise the tenant's requests
  fail with 503 (`tenant_database_unavailable`) and **never** fall back to the shared database.
- Identity and platform data (the control plane: users, sessions, organizations, memberships, plans, settings)
  always come from `req.controlDb`.

Before moving a real tenant to a dedicated database:

1. Run the migrations against the dedicated database.
2. Copy the tenant's rows for every table in `tenantTables()`.
3. Point the workers at each registered database.
4. Change the remaining joins from tenant tables to `users` / `organizations` / `plans` (agent names, plan names;
   about 18 query sites in inbox, billing, campaigns, contacts, team and bots) to look those up through
   `req.controlDb`.
5. Repeat the isolation suite against that setup.

## 8. Automated isolation tests

| Test | Proves |
|---|---|
| `isolation.test.ts` · every route with an id | All 201 session API routes come from `API_MOUNTS`. Each of the 107 routes that take an id is called as tenant B with each of A's 21 distinct record ids (2,247 requests). Each request carries a body that is valid for the route (so it reaches the ownership check) and stuffed with A's ids, plus `X-Organization-Id: A`. Every response must be 403/404 and must not contain A's marker text, A's email, or any of A's ids. |
| … list/search/export/analytics | Every list route, also with A's ids in 15 query parameters, returns only B's data. The contacts Excel export is opened and checked. Dashboard counts are B's alone. |
| … positive control | The same id routes return 200 for B's own records, so the 404s really mean tenancy. |
| … no side effects | A snapshot of every row A owns is identical before and after the attack, and `findIsolationProblems` finds nothing. |
| `isolation-refs.test.ts` · ids in bodies | A's groups, custom fields, contacts, number, templates, teammates, files and conversations used inside B's contact, inbox, campaign, bot, flow and billing requests are refused. Bulk actions on A's ids affect 0 rows. A replayed signed checkout moves no money. Notification ids are scoped. Idempotency keys are per tenant. |
| … organization context | No switching into a non-member organization. Headers are ignored. Removal and suspension take effect on the next request. A user in two organizations only sees the active one. |
| … roles | Owner / Admin / Agent / Viewer matrix, owner protections, last-owner rule, chats unassigned on removal. |
| … resellers & platform | A reseller reaches only its own customers (not another reseller's, not direct customers). Customers are always created under the requesting reseller. Resellers can't get into customer workspaces or platform settings. Platform roles are separated. Customers attach only to resellers. |
| … webhooks & workers | An inbound message on A's number creates A's contact even when B has one with the same phone, and never triggers B's flows. A status for B's message posted to A's URL changes nothing. Forged signatures are refused. A WABA has one owner. Campaigns send only through their own number. Tenant-context guards block wallet, file, channel and WhatsApp-credential access across tenants. |
| … real-time | B's stream receives only B's events and closes within one interval of the member's removal. |
| … database | Every table is classified. Every tenant table has `organization_id` (NOT NULL on MySQL), an index and a foreign key. Every registered relation rejects a cross-tenant update. Owner changes and cross-tenant inserts are rejected. |
| … public API | B's full-access API key is used against every `/api/v1` endpoint with every one of A's ids, and with A's ids in query strings and bodies (A's template, number, groups): every response is 403/404 with nothing of A's. A's events are never queued for B's webhook endpoints. |
| … database resolution | Registered dedicated databases are used; unknown ones fail closed with 503. |

The suite was also checked against deliberately broken code: removing the organization filter from one contact
lookup makes it fail, naming the route and the leaked fields.

Running:

```bash
cd backend
npm test                                             # everything on SQLite (in memory)
TEST_DB=mysql DB_HOST=127.0.0.1 DB_USER=… DB_PASSWORD=… \
  npx vitest run tests/isolation.test.ts tests/isolation-refs.test.ts   # same suites on MySQL (a temporary database per file)
npm run tenancy:check                                # integrity scan of the configured database
```

### Checklist for new code

- A new table is added to `TABLES` in `db/tenancy.ts`. A tenant table gets `organization_id NOT NULL`, an index and a
  foreign key, and every reference to another tenant table goes into `RELATIONS`. Rebuild the triggers in the
  migration with `rebuildTenancyTriggers(knex)`.
- A new router is added to `API_MOUNTS` (then the crawler attacks it automatically). Every query uses `orgId(req)`;
  never take an organization id from the client.
- Background work wraps each item in `runAsTenant(row.organization_id, 'worker', …)` and is registered in `services/workers.ts` (heartbeat).
- A new public API endpoint is added to `V1_ROUTES` with a scope; the public API crawler attacks it automatically.
- Domain events go through `emitEvent(trx, org, …)` inside the change's transaction.
- Files go through `getStorage()` (keys are per organization); real-time events through `publish(org, …)`.

## 9. Known limits

- The full backend suite (199 tests, including both isolation suites) passes on SQLite and on **MariaDB 10.11**
  (MySQL-compatible). Run `TEST_DB=mysql npx vitest run` against your MySQL 8 server once before going live.
- On SQLite (development and tests only), `NOT NULL` on the columns added later is enforced by trigger, not by the
  column definition.
- "Actor" columns (`created_by`, `sent_by`, `published_by`, …) record who did something and may name platform staff,
  so they are not membership-checked. Assignment (`assigned_user_id`) is.
- Platform staff actions on a customer are audited under the platform organization (with the target in the entry),
  not in the customer's own audit log.
- The real-time bus is in-process. Several API instances need Redis pub/sub on the same per-organization channels.