# Setup & Deployment Guide Qtrial is a **single-deploy Next.js app**: SSR pages + colocated REST API (`/api/v1/...`, same contract the Express server used to serve) + **MySQL 8** database + an **hourly worker** (same image, different command). ``` Browser ──> Next.js (:3000) ├── SSR pages (dashboard renders server-side, no HTTP hop) ├── /api/v1/* (auth, tickets, samples, reports — Flutter-ready) └── /uploads/ (evidence files, served access-controlled) Worker ──> tsx scripts/worker.ts (reminders, escalations, AI risk) MySQL ──> :3306 ``` No CORS configuration is needed for the web app (same origin); external clients (Flutter, integrations) use the absolute API URL with the CORS headers the API sends. --- ## 1. Prerequisites | Requirement | Development | Production | |---|---|---| | Node.js | 20 LTS | 20 LTS | | MySQL | 8.0 (local or Docker) | 8.0 managed or self-hosted | | npm | bundled with Node | bundled with Node | | Reverse proxy / TLS | not needed | Nginx + certbot (or Caddy) | | Process manager | terminals | PM2 or systemd | --- ## 2. Development setup ### 2.1 Database ```bash # Option A — Docker MySQL docker run -d --name pharma-mysql \ -e MYSQL_ROOT_PASSWORD=root_secret \ -e MYSQL_DATABASE=pharma_db \ -e MYSQL_USER=pharma \ -e MYSQL_PASSWORD=pharma_secret \ -p 3306:3306 mysql:8.0 # Option B — local MySQL 8: create schema + least-privilege user mysql -u root -p -e "CREATE DATABASE pharma_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" mysql -u root -p -e "CREATE USER 'pharma'@'%' IDENTIFIED BY 'pharma_secret'; GRANT ALL ON pharma_db.* TO 'pharma'@'%';" ``` ### 2.2 App (UI + API together) ```bash npm install cp .env.example .env # then edit DB_*, SESSION_SECRET, SECRETS_KEY npm run migrate # applies db/migrations/*.sql in order (tracked) npm run dev # UI + API on http://localhost:3000 ``` Key `.env` values for dev: ```env DB_HOST=127.0.0.1 DB_PORT=3306 DB_USER=pharma DB_PASSWORD=pharma_secret DB_NAME=pharma_db SESSION_SECRET= SECRETS_KEY= # encrypts org SMTP passwords at rest COOKIE_SECURE=false # true in production (HTTPS) APP_URL=http://localhost:3000 FRONTEND_URL=http://localhost:3000 # NEXT_PUBLIC_API_URL= # leave empty = same-origin API ``` ### 2.3 Reminder worker (second terminal) ```bash npm run worker # hourly cycle; --once for a single pass: npx tsx scripts/worker.ts --once ``` ### 2.4 First super admin ```sql -- register a user via the UI, then promote: UPDATE users SET role='super_admin', organisation_id=NULL WHERE email='you@example.com'; ``` Log in → Organisations → create your first organisation (slug, limits, SMTP), then create org admins/employees. Share the white-label login: `http://localhost:3000/login?org=`. ### 2.5 Optional integrations (dev) ```bash npx web-push generate-vapid-keys # -> VAPID_* env for browser push # ClamAV for upload scanning: leave CLAMAV_HOST empty (heuristics only) ``` Run tests: `npm test` (vitest, 210 tests), typecheck with `npx tsc --noEmit`, production-check with `npm run build`. --- ## 3. Production deployment (single VPS) ### 3.1 Server prep (Ubuntu 24.04) ```bash sudo apt update && sudo apt install -y nodejs npm mysql-server nginx certbot python3-certbot-nginx node -v # want v20.x — use NodeSource if the distro ships older sudo mysql_secure_installation ``` ### 3.2 Database ```bash sudo mysql -e "CREATE DATABASE pharma_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" sudo mysql -e "CREATE USER 'pharma'@'localhost' IDENTIFIED BY '';" sudo mysql -e "GRANT ALL ON pharma_db.* TO 'pharma'@'localhost'; FLUSH PRIVILEGES;" ``` ### 3.3 Deploy the code ```bash sudo mkdir -p /opt/pharma && sudo chown $USER:$USER /opt/pharma cd /opt/pharma && git clone . # or copy the release bundle npm install ``` ### 3.4 Production environment (`.env`) ```env NODE_ENV=production APP_URL=https://app.example.com FRONTEND_URL=https://app.example.com DB_HOST=127.0.0.1 DB_PORT=3306 DB_USER=pharma DB_PASSWORD= DB_NAME=pharma_db SESSION_SECRET=<64+ random chars: openssl rand -hex 32> SECRETS_KEY=<64+ random chars, different value> COOKIE_SECURE=true JWT_EXPIRES_IN=8h SMTP_HOST=smtp.example.com SMTP_PORT=587 SMTP_USER=... SMTP_PASSWORD=... SMTP_FROM_EMAIL=no-reply@example.com SMTP_FROM_NAME="Qtrial" SMTP_SECURE=false STORAGE_PATH=./uploads MAX_FILE_MB=25 VAPID_PUBLIC_KEY=... # npx web-push generate-vapid-keys VAPID_PRIVATE_KEY=... VAPID_SUBJECT=mailto:ops@example.com CLAMAV_HOST= # set to enable ClamAV scanning ORG_TIMEZONE=Asia/Kolkata ``` > Never commit `.env`. `SESSION_SECRET` and `SECRETS_KEY` must differ between > environments; rotating `SECRETS_KEY` invalidates stored org SMTP passwords > (re-enter them afterwards). ### 3.5 Build, migrate, storage ```bash cd /opt/pharma npm run build npm run migrate # safe to re-run; tracks applied files mkdir -p /opt/pharma/uploads/logos chmod 750 /opt/pharma/uploads ``` ### 3.6 Run web + worker (PM2) ```bash sudo npm install -g pm2 cd /opt/pharma pm2 start npm --name qtrial-web -- start -- -p 3000 pm2 start npm --name qtrial-worker -- run worker pm2 save && pm2 startup # follow the printed steps ``` ### 3.7 Nginx reverse proxy + TLS (single upstream) ```nginx # /etc/nginx/sites-available/qtrial server { listen 80; server_name app.example.com; client_max_body_size 30m; # >= MAX_FILE_MB location / { proxy_pass http://127.0.0.1:3000; proxy_set_header Host $host; proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for; proxy_set_header X-Forwarded-Proto $scheme; } } ``` ```bash sudo ln -s /etc/nginx/sites-available/qtrial /etc/nginx/sites-enabled/ sudo nginx -t && sudo systemctl reload nginx sudo certbot --nginx -d app.example.com # HTTPS; COOKIE_SECURE=true requires it ``` ### 3.8 Backups & maintenance ```bash # Nightly DB dump (crontab) 0 2 * * * mysqldump -u pharma -p'' pharma_db | gzip > /var/backups/pharma/pharma-$(date +\%F).sql.gz # Uploads are plain files — back up /opt/pharma/uploads with the same job (rsync/restic). ``` Updating: ```bash cd /opt/pharma && git pull npm install && npm run build && npm run migrate && pm2 restart qtrial-web qtrial-worker ``` Health checks: `GET https://app.example.com/api/health` → `{"ok":true,...}`. Watch worker logs (`pm2 logs qtrial-worker`) for `[reminder] cycle done` hourly. ### 3.9 Docker Compose alternative ```bash cp .env.example .env # fill secrets (optional; compose has defaults) docker compose up --build -d # mysql :3306, web :3000 (migrates on boot), worker ``` For production compose, put Nginx in front (as above) and mount named volumes for `mysql-data` and `./uploads`. --- ## 4. Troubleshooting | Symptom | Likely cause / fix | |---|---| | `Organisation suspended` at login | Org status is `suspended` — super admin → Organisations → Activate | | 404 on cross-org records | Correct tenant isolation; switch org context (super) or check assignment | | Mails not sending | Check `SMTP_*`; org without SMTP falls back to global; dev logs to console | | Uploads rejected (422/413) | Malware heuristics or ClamAV (422); over size limit (413); check logs | | Push not offered | `VAPID_*` unset on server, or browser denied permission | | `SECRET` rotation broke org mail | Re-enter org SMTP passwords after changing `SECRETS_KEY` | | iCal URL 404 | Token revoked or user deactivated; regenerate from Calendar page | ## 5. Security checklist (production) - [ ] Unique `SESSION_SECRET`, `SECRETS_KEY`, DB and SMTP passwords - [ ] `COOKIE_SECURE=true` behind HTTPS - [ ] MySQL bound to localhost, least-privilege `pharma` user - [ ] Uploads directory outside the web root, non-executable - [ ] Nightly DB + uploads backups, restore tested - [ ] `pm2` (or systemd) auto-restart enabled; logs rotated - [ ] Super-admin account uses a strong, unique password