# Qtrial — Action, Sub-Action, Employee Assignment & Sample Testing Management

Single-deploy Next.js SaaS monorepo for pharmaceutical organisations: **SSR pages + colocated REST API** (`/api/v1/...`) + **MySQL 8** + background worker.

- One app, one port: UI and API ship together; pages like the dashboard render server-side with direct service calls (no HTTP hop), interactive islands stay client-side.
- Same REST contract as before — Flutter/mobile and third-party integrations keep working unchanged (now same-origin; CORS retained for external clients).
- API routes enforce the exact auth/RBAC/tenant/limit rules of the former Express server via a small req/res compatibility adapter (`src/server/adapter.ts`).

> Full deployment walkthrough (dev + production VPS + Docker): see **[setup.md](./setup.md)**.

## Structure
```
src/server/   API layer (ported Express logic) served as Next.js Route Handlers at /api/v1/... — same REST contract, reusable by Flutter
app/  Next.js 14 App Router + Tailwind + RHF/Zod + TanStack Query (UI + /api/v1 + SSR)
db/migrations/  MySQL schema (counters, actions→sub_actions, samples→schedules, docs, audit)
docker-compose.yml  mysql + api + reminder worker + web
```

## Key behaviors
- `DEV-YYYY-000001` (new deviations; legacy `ACT-` rows keep their numbers) and `SMP-YYYY-000001` generated **transactionally** via `counters` table + `SELECT ... FOR UPDATE` (no duplicates under concurrency).
- CAPAs: `DEV-...-S01`, auto roll-up parent → Completed when all CAPAs complete.
- Session auth: JWT in **HTTP-only cookie** (`pharma_session`); role middleware + ownership checks enforced server-side.
- Forgot/reset: short-lived token (30 min), SMTP via env, dev logs to console when SMTP unset.
- Reminders: `scripts/worker.ts` (hourly) — testing offsets 7/3/1/0 days, due-soon, overdue; in-app + email.
- File uploads: private `uploads/`, randomized names, ext allowlist + size limit, malware scan, auth-checked downloads.
- Audit logs on all major mutations; notifications table; dashboards; calendar feed; CSV export.

## Quick start (local)
```bash
# 1. MySQL 8 running, then (all commands run from the repo root):
cp .env.example .env   # edit DB_* + secrets

# 2. Install, migrate, run (UI + API together on :3000)
npm install && npm run migrate
npm run dev                          # http://localhost:3000
# worker (second terminal): npm run worker
```

## Docker
```bash
docker compose up --build
# web :3000 (auto-migrates on boot), worker, mysql :3306
```

## Create first super admin
Register a user via the UI, then promote:
```sql
UPDATE users SET role='super_admin', organisation_id=NULL WHERE email='you@example.com';
```

## Env (.env)
See `.env.example`. Never commit secrets. SMTP: `SMTP_HOST/PORT/USER/PASSWORD/FROM_EMAIL/FROM_NAME/SECURE`.
Production fails fast on a missing/default `SESSION_SECRET` and on out-of-range numeric settings (`PORT`, `DB_*`, pool, file/scan limits).
DB pool is bounded per process (`DB_POOL_LIMIT`, `DB_POOL_MAX_IDLE`, idle auto-close via `DB_POOL_IDLE_TIMEOUT_MS`, default 5 min).

## Health
`GET /api/v1/health` (public) returns `{ ok, db: 'up', time }` with a DB ping — 503 when the database is unreachable. Use it for load-balancer checks and Docker `HEALTHCHECK`.

## Tests
```bash
npm test   # vitest: 210 tests — auth, RBAC, actions, samples, documents, scan, templates, reports, SaaS, imports, recurrence, announcements, SLA, holidays, risk, iCal, branding
npx tsc --noEmit && npm run build
```

## Reports / exports
`GET /api/v1/reports/actions|samples|schedules?format=xlsx|csv|pdf` — same filters as list endpoints, employee scoping enforced server-side. Reports page offers one-click downloads.

## Email templates
DB-backed (`email_templates`), seeded defaults, admin-editable at Settings → Email Templates. Password-reset mail already renders through the template; `templateService.getTemplate/renderTemplate` is the hook for the rest.

## Malware scanning
Uploads always pass extension heuristics (double-extension, executables, empty files). Set `CLAMAV_HOST/PORT` to also stream files to clamd; `FILE_SCAN_MODE=enforce` quarantines when the scanner is unreachable. Infected uploads are deleted + logged (`REPORT_QUARANTINED`) and blocked from download.

## Migrations
Apply in order (`npm run migrate` handles this + tracking): `001_initial_schema.sql` … `018_consultations.sql`.
```bash
npm run migrate   # tracks state in `migrations` table
# docker compose runs migrate automatically before starting web
```

## Platform notes
- Org SMTP passwords are stored AES-256-GCM encrypted (`SECRETS_KEY` env; legacy plaintext still readable). Never commit `.env`.
- Assignment pickers: `GET /api/v1/employees` (stakeholder+) returns id/name/code/department for dropdowns.
- Departments, Categories and QC Teams have dedicated UI pages (org-scoped); super admins can additionally create `super_admin` accounts via `POST /api/v1/users`.
- Dashboards accept `?from=&to=` plus UI presets (today/week/month/quarter/year/custom); Calendar is a month grid fetching its visible range.

## Bulk import
- `POST /api/v1/imports/users` (admin) and `POST /api/v1/imports/samples` (admin/stakeholder): multipart CSV/XLSX (max 1000 rows), per-row validation with `{created, failed, errors[]}` report. Users import resolves departments by name, enforces org plan limits, and auto-creates stakeholder profiles. Samples import validates date windows and generates `SMP-` refs transactionally.
- `POST /api/v1/imports/actions` (admin/stakeholder): `title, category, department, assignee (email), priority, target_date, reminder_lead_days, remarks, sub_actions (; separated), sub_assignees (; separated emails, positional)` — one transaction, per-org `DEV-` refs with `-S0N` CAPA children, deviation + CAPA limits enforced up front.
- `GET /api/v1/imports/:kind/template?format=csv|xlsx` downloads a filled example template. UI lives on the Users, QC Samples, and Deviations pages.

## Recurring testing schedules
- `POST /api/v1/schedules` accepts `recurrence: {freq: daily|weekly|monthly, interval?, end_date?, count?}` — the master carries the rule, occurrences link via `recurrence_parent_id`, each with independent status/reminders/docs. Occurrences outside the sample window are skipped (reported as `skippedOutsideWindow`); monthly steps clamp month-end. Migrations: `008_recurrence.sql`.

## Announcements
- Org notices with priority, audience (all/admins/stakeholders/employees), optional department, publish scheduling, and expiry (`009_announcements.sql`). Publishing fans out in-app + email + push to the audience; read receipts tracked, unread badge in the nav. Stakeholders author within their scope (author-or-admin edit/delete).

## Recurring actions
- Actions accept `recurrence: {freq: daily|weekly|monthly, interval?, end_date*}` at creation. Completing one spawns the next occurrence (new `DEV-` ref, fresh CAPAs with preserved date offsets, assignee notified) until `end_date` or 100 occurrences. Detail page shows `↻` badge, series list, and Stop repeating. Migration: `010_recurring_actions.sql`.

## SLA breach reports
- Resolution-time SLA targets per priority (global defaults Critical 1d / High 3d / Medium 7d / Low 14d, overridable per org via Settings → SLA targets or `PUT /api/v1/sla-policies`; `GET` shows builtin/global/organisation scopes). Migration: `012_sla_policies.sql`.
- A ticket breaches when open longer than its priority SLA **or** when escalated. `GET /api/v1/reports/sla/summary` (per-level + per-priority breakdown) and `GET /api/v1/reports/sla?format=&level=&priority=&breached=` exports, sorted by severity.

## Calendar
- The Calendar page is an internal month view (`GET /api/v1/calendar?from=&to=`) over the user's tickets, testing schedules, and holidays — no external sync. (External iCal feeds/tokens were removed; the token tables from `013_calendar_tokens.sql` / `015_feed_clients.sql` are legacy and unused.)

## Escalation thresholds per priority
- L1 fires on the due date (all priorities); L2/L3 use per-priority hours-overdue (`escalation_policies`, org override → global → legacy day-settings fallback). Defaults: Critical 12/24h, High 24/72h, Medium 48/120h, Low 72/168h — so Critical reaches management in hours. `GET/PUT /api/v1/escalation-policies`, matrix editor in Settings. Migration: `014_escalation_policies.sql`.

## AI escalation-risk engine
- Transparent feature model (no API keys, deterministic, explainable — suited to compliance): priority base + SLA burn + overdue hours + update stagnation + progress stall + sub-task health + status churn + assignee overload + escalation history → 0–100 score, bands ≥80 Critical / ≥60 High / ≥40 Medium. Latest assessment stored on the ticket with its factors (`016_risk_scores.sql`).
- Hourly worker notifies the assignee once per rise into High/Critical (factors included; Critical also CCs the stakeholder; cooled-off tickets re-arm). `GET /api/v1/dashboard/risk` powers the dashboard watch widget; detail pages show the badge + signals. Kill-switch: `ai_risk_enabled` setting.
- Optional LLM one-liner (`AI_PROVIDER=http` + `AI_API_URL/KEY/MODEL`, OpenAI-compatible, 8s timeout, silent fallback) enriches the alert; off by default.

## Holiday calendars
- `holidays` table: org dates + global (`organisation_id 0`) rows; per-org `working_days` (default global `working_days` setting `1,2,3,4,5`). Lead/testing reminders pause on non-working days and resume next workday; escalations always fire. Manage in Settings → Holiday calendar (super-admins can add global dates). Migration: `011_holidays.sql`.

## Email templates (per-org overrides)
- `organisation_id 0` = global default; an org row shadows it (migration `007`). Emails render org-first → global → builtin.
- Org admins saving in Settings → Email Templates create their org's override; super-admin without org context edits globals; `DELETE /api/v1/email-templates/:key` resets an override. The UI badges `org override` vs `global`.

## SaaS multi-tenancy
- `organisations` table; every tenant record carries `organisation_id` with FKs and per-org uniqueness (departments, categories); reference counters are per-org (sequences restart per tenant).
- Roles: `super_admin` (omnipotent, cross-org) > `admin` (org admin) > `stakeholder` > `employee`. Non-super users are pinned to their org in SQL — cross-org rows return 404. Suspended orgs block login and writes.
- Super admin: Organisations page (or `GET/POST/PATCH/DELETE /api/v1/super/organisations`) to create orgs, set plan limits (`max_stakeholders/employees/tickets/sub_tickets`, NULL = unlimited), per-org SMTP, suspend/activate. Use the org switcher (or `X-Organisation-Id` header) to operate inside one tenant.
- Per-org SMTP: when an org configures its own account it is used; otherwise mail falls back to the super-admin global `SMTP_*` env config (forgot-password, assignments, reminders, escalations all org-aware).
- Limits are enforced at creation time with 403 + message (`assertWithinLimit`).
- First super admin: `UPDATE users SET role='super_admin', organisation_id=NULL WHERE email='you@example.com';` (all pre-existing data is backfilled into the `default` organisation by the migration).

## User roles, permissions & access
Four roles, enforced **server-side** on every API route (`app/api/v1/*/route.ts` → `assertRoles`/`requireRole`) and in controllers (ownership checks). `super_admin` bypasses every role gate but is still tenant-pinned unless an org context is selected. `cannot see` = endpoint returns 403 or the row is excluded by SQL scoping; cross-org rows always 404.

| Role | Scope | Default access |
|---|---|---|
| `super_admin` | Cross-org platform operator | Everything (all rows, all orgs), plus platform/org administration. Uses the org switcher (or `X-Organisation-Id`) to operate inside one tenant. Not tied to an organisation. |
| `admin` | One organisation | Org-wide read/write on all modules; manages org settings, users, departments, policies, email templates, holidays. |
| `stakeholder` | One organisation | Creates/manages deviations, CAPAs, QC samples and their imports, categories, announcements; assignment pickers; no org settings or user management. |
| `employee` | Own assignments only | Views and updates only the deviations/CAPAs assigned to them; no create/edit/delete on deviations or configuration. |

Employees are scoped in SQL — e.g. `actionController.ts:29` filters to `assigned_employee_id = ?` (or a CAPA assignment); samples, calendar, dashboards, and reports apply the same rule (`calendarController.ts:78`, `reportController.ts:27`, `metaController.ts:80`).

### Page accessibility (sidebar, `app/(dashboard)/shell.tsx:12`)
| Page | super_admin | admin | stakeholder | employee |
|---|---|---:|---:|---:|---:|
| Dashboard (`My Work` for employees), Deviations, Workflow Board, QC Samples, Calendar, Reports, Notices | ✓ | ✓ | ✓ | ✓ (own data) |
| Notifications (bell icon in the top bar, `/notifications`) | ✓ | ✓ | ✓ | ✓ (own data) |
| Departments, Categories, Teams | ✓ | ✓ | ✓ | ✗ |
| Users, Audit Logs, Settings | ✓ | ✓ | ✗ | ✗ |
| Organisations | ✓ | ✗ | ✗ | ✗ |

### Permission matrix (module → who can act)
| Module | Read | Write | Notes |
|---|---|---|---|
| Deviations / CAPAs | all roles | create/update/delete: admin, stakeholder; employees only update **status/progress/remarks** on own items | Employees cannot edit parent deviations or create/delete (`actionController.ts:131,158,207`); status changes allowed on assigned deviations |
| Samples | all roles | create/update/delete: admin, stakeholder | Date-window overrides are admin-only (`sampleController.ts:148`) |
| Assignments / pickers (`/employees`, `/stakeholders`) | admin, stakeholder | — | Employee dropdowns for task assignment |
| Users (`/users`, import) | admin | admin | admin manages org users; only super_admin can create `super_admin` accounts |
| Departments / categories | all roles | departments: admin; categories: admin + stakeholder (delete: admin) | — |
| Teams (department-wise) | all roles | create: admin + stakeholder (UI hides the page from employees) | One team per department; team name = department name, lead auto = department stakeholder, members = department employees (`sampleController.ts:createTeam`) |
| Settings, SLA targets, escalation policies | admin | admin | `system_settings` + per-org policies |
| Email templates | admin + stakeholder | admin | Org overrides v/s global scope resolved per role (`templateController.ts`) |
| Holidays | all roles | admin (global dates: super_admin) | `holidayController.ts:36,57` |
| Announcements | all roles | admin + stakeholder (author-or-admin edit/delete) | Employees cannot post (`announcementController.ts:70,107,126`) |
| Audit logs | admin + super_admin (org-wide); others see only their own entries | — | `masterController.ts:167` |
| Documents | on tickets the user can access | on tickets the user can access | Employees only for tickets they're assigned to (`documentController.ts:16`) |
| Reports / exports | all roles | — | Same employee scoping as list endpoints |
| iCal feed / calendar tokens | all roles | own tokens only | `calendarController.ts:78` |
| Notifications / web push / device tokens | all roles | own subscriptions | — |
| Organisations, org branding, consultations inbox, super accounts | super_admin | super_admin | `GET/POST/PATCH/DELETE /api/v1/super/*` |

### Access rules (enforced everywhere)
- **Tenant pinning:** non-super users are fixed to their `organisation_id` via `tenantClause`/`tenant.ts`; suspended orgs block login and writes (`session.ts:34`).
- **Ownership:** employees get 403 when a deviation/CAPA isn't assigned to them; stakeholders auto-fill their own stakeholder/department on create.
- **Limits:** `assertWithinLimit` returns 403 when an org's plan limits are hit (tickets, sub-tickets, stakeholders, employees).
- **Super admin context:** `?organisation_id=` / `X-Organisation-Id` scopes a super admin into one tenant; no context = cross-org platform view.

## Ticket reminders & escalation
- Stakeholders set **reminder lead days** per deviation/CAPA (create form, detail page, or `PATCH /api/v1/actions/:id`); falls back to `default_reminder_lead_days`. The hourly worker notifies the assigned employee once per day while the target is inside the window — in-app + email + **web push**.
- If a ticket is not marked completed: **L1** (due date) → assignee + stakeholder; **L2** (overdue ≥ `escalation_l2_days`) → stakeholder + higher management (admins); **L3** (overdue ≥ `escalation_l3_days`) → management critical. Each level fires once (`escalation_level`), rendered through the admin-editable `escalation` email template.
- Web push: generate VAPID keys (`npx web-push generate-vapid-keys`), set `VAPID_*` env, employees opt in at Notifications → Enable push. Subscriptions stored in `push_subscriptions`; native FCM tokens for the future Flutter app go to `device_tokens` via `POST /api/v1/push/devices`.

## Web UI/UX
- Public multipage site (region-neutral branding): landing `/` (full-height hero carousel with locally vendored stock photography — no runtime CDN dependency — pillars, stats, roles, reviews, security strip, CTA) plus `/features`, `/how-it-works`, `/clients`, `/reviews`, `/pricing` (packages without prices + instant **Request Quote** estimate calculator), `/contact` (consultation form with **Request a Customised Proposal** prefill from the estimate), `/faq`, `/privacy`, `/terms`, `/gdpr` — shared navbar (overlapping hero, mobile menu), footer © Qtrial by Soulitum Labs, skip links and labelled regions throughout. Consultation posts to `POST /api/v1/consultations` (validated, rate-limited, stored + emailed to super-admins, 2-business-hour SLA; super-admin inbox API included).
- Inputs use 0.25rem radius; buttons match. Client-side JS validation mirrors backend rules on every auth and dashboard form with inline `role=alert` errors.
- Footer: © Qtrial by Soulitum Labs; GDPR compliance page linked.
- Brand: **Qtrial** with primary **#0B1626** (deep navy), secondary **#E8F0F7** (mist).
- Light/dark mode toggle (topbar + login), persisted, OS preference default, no-flash pre-paint script, full dark contrast pass over status colors.
- Responsive shell: sticky desktop sidebar, slide-over drawer with Escape/backdrop close on mobile, sticky topbar, horizontal-snap kanban on small screens, overflow-safe tables and month grid.
- Accessibility: skip-to-content link, landmarks (`nav`/`main`), `aria-current` nav, labelled controls and table scopes, `role=alert/status` feedback, `aria-pressed` toggles, `prefers-reduced-motion` support (CSS + framer-motion), password show/hide with labels.
- White-label organisations: per-org logo (PNG/JPG/WebP ≤2MB) + app name, shown in the sidebar, topbar, and on the white-label login (`/login?org=<slug>`, public branding API). Org admins manage it in Settings → Organisation branding; super-admins in Organisations (migration `017`).