Pharma Field-Force Reporting
Deploying to a server? Use PRODUCTION.md — the operator's guide (first deployment, real users, backups, updates, runbook). This README is for development.
Field-force reporting for a pharmaceutical sales team (Celtic Life Science Pvt Ltd): Medical Representatives (MRs) file Daily Call Reports, Area Managers (AMs) review and approve them (the General Manager has a read-only overview), and approved reports are locked and rendered as PDFs. Alongside the DCR, the system manages the company's paper forms — tour plans, fare charts, stock statements and more — and prints each one on its own PDF layout.
- Backend: FastAPI · SQLAlchemy 2 (async) · PostgreSQL 16 · Alembic
- PDFs: Jinja2 templates rendered by WeasyPrint
- Web app: React 18 + TypeScript (Vite), Celtic-branded, served by Caddy
- Deployment: Docker Compose on a single VPS, automatic HTTPS via Caddy
Deliberately not in scope: HR, payroll, attendance, distributor management, GPS, offline mode.
Contents
- Features
- Quick start with Docker — incl. VPS over SSH, ports, operations
- Local development
- Configuration
- Roles and permissions
- The approval workflow
- Company forms and PDFs
- API overview
- Project layout
- Design decisions
- Testing
- Production checklist
- Known limitations
- Troubleshooting
Features
| Area | What it does |
|---|---|
| Daily Call Reports | MR records doctor and chemist calls per day; one report per MR per date |
| Approval workflow | DRAFT → PENDING_AM → APPROVED, with a return-for-correction loop (the Area Manager is the final approver); approved reports are immutable |
| Audit trail | Append-only log of every state change; approval history is a view over it |
| PDFs | Approved DCRs are stored as PDFs; company forms download as PDFs on demand |
| Company forms | Ten form tables (tour plan, fare chart, stock statement, …) with per-form PDF layouts |
| Masters | Users, headquarters, AM↔HQ mapping, doctors, chemists, products, with MR assignments |
| Security | JWT with refresh-token rotation, argon2 password hashing, role and scope checks |
Quick start with Docker
Requires Docker with the Compose plugin. On any machine — laptop, or a VPS over SSH:
git clone <repo-url> pharma-web-celtic && cd pharma-web-celtic
make deploy # = ./scripts/setup.sh + docker compose up -d --build + wait until healthy
make deploy is safe to re-run; it is also how you update after git pull. The setup step (scripts/setup.sh) prepares .env for you:
- creates
.envfrom.env.exampleif it is missing; - generates a strong
SECRET_KEY(if missing or a placeholder), and setsENVIRONMENT=production,DEBUG=false; - generates
POSTGRES_PASSWORDfor a new database (an existing database keeps its password — see Rotating the database password); - picks free ports: if 80/443 are free, Caddy serves your
DOMAINwith automatic HTTPS; if another service already owns them, it switches to plain HTTP on the first free host port from 2000 (HTTPS side from 2443) and tells you which.
Then load the demo data once (skip this if the database already has users):
docker compose exec api python -m scripts.seed
Check it:
make status # containers, ports and the site URL
curl http://localhost:<HTTP_PORT>/health # {"status":"ok", ... "pdf_engine":"weasyprint"}
Deploying on a VPS over SSH
ssh user@your-vps
sudo apt-get install -y docker.io docker-compose-v2 git # or Docker's official install script
git clone <repo-url> pharma-web-celtic && cd pharma-web-celtic
DOMAIN=app.example.com make deploy # omit DOMAIN if you only have an IP address
Which case applies is decided by what is listening on the server:
| Situation | What setup.sh does |
How users reach it |
|---|---|---|
Ports 80/443 free, real DOMAIN (DNS already points at the server) |
Caddy takes 80/443 | https://app.example.com — certificate is automatic |
| Ports 80/443 free, no domain | DOMAIN=localhost |
https://<ip> with a self-signed certificate warning |
| 80/443 already used by another service | DOMAIN=:80, next free HTTP_PORT (e.g. 2000) |
http://<ip>:2000, or put your existing proxy in front (below) |
Open the chosen port in the firewall only if you want direct access (sudo ufw allow 2000/tcp); if a proxy on the same host fronts it, block it instead (sudo ufw deny 2000/tcp — the proxy still reaches it on 127.0.0.1).
Existing nginx in front (terminating TLS on 80/443):
server {
server_name app.example.com;
listen 443 ssl; # + your certificate directives
location / {
proxy_pass http://127.0.0.1:2000; # the HTTP_PORT chosen by setup.sh
proxy_set_header Host $host;
proxy_set_header X-Forwarded-Proto $scheme;
proxy_set_header X-Forwarded-For $remote_addr;
}
}
Choosing ports yourself: edit .env (HTTP_PORT, HTTPS_PORT, DOMAIN) and run docker compose up -d. setup.sh keeps ports your own containers already use and only moves them when something else holds them.
Day-to-day operations
docker compose logs -f api # API logs (also: web, caddy, db)
docker compose ps # status
make deploy # after git pull: rebuild and restart
docker compose down # stop (data volumes are kept)
docker compose down -v # stop AND DELETE the database and PDFs — never on a live system
docker compose exec db pg_dump -U pharma pharma > backup-$(date +%F).sql # database backup
Rotating the database password
The database keeps the password it was created with; changing POSTGRES_PASSWORD in .env alone would lock the API out. To rotate (needed if setup.sh warned about a default password):
NEW=$(python3 -c "import secrets;print(secrets.token_urlsafe(24))")
docker compose exec db psql -U pharma -d pharma -c "ALTER USER pharma PASSWORD '$NEW';"
sed -i "s|^POSTGRES_PASSWORD=.*|POSTGRES_PASSWORD=$NEW|" .env
docker compose up -d
What the stack contains
| Service | Image | Notes |
|---|---|---|
caddy |
caddy:2-alpine |
The only service publishing ports; routes /api, /docs, /health to the API and everything else to the web app |
api |
built from Dockerfile |
Non-root, read-only filesystem, runs alembic upgrade head on start |
web |
built from web/Dockerfile |
Static React build served by Caddy |
db |
postgres:16-alpine |
Internal network only; data in the pgdata volume |
Things worth knowing:
- Compose refuses to start without
POSTGRES_PASSWORDandSECRET_KEY(make setupfills them in). - In
ENVIRONMENT=productionthe API refuses to boot if WeasyPrint's system libraries are missing orSECRET_KEYis a placeholder / under 32 characters.GET /healthreports"pdf_engine": "weasyprint". - Set
RUN_MIGRATIONS=0on theapiservice to skip the start-up migration. - The database and API are not published to the host. To reach the database from the host (tests,
psql) use the development overlay:DB_PORT=5433 make dev-dbpublishes it on127.0.0.1:5433only. - Volumes to back up:
pgdata(usepg_dump) andpdfstore(approved DCR PDFs). - The seed creates demo accounts that share one well-known password (see
scripts/seed.py). Change every password before real use.
Local development
Prerequisites: Python 3.11+, Node.js 20+, Docker (for PostgreSQL).
# 1. Python environment
python -m venv .venv && source .venv/bin/activate # Windows: .\.venv\Scripts\Activate.ps1
pip install -e ".[dev]"
# 2. Settings
cp .env.example .env # development defaults; DATABASE_URL already points at localhost:5432
# 3. PostgreSQL on localhost:5432 (also creates the pharma_test database)
make dev-db # port taken? DB_PORT=5433 make dev-db, and use 5433 in DATABASE_URL
# 4. Schema and demo data
alembic upgrade head
python -m scripts.seed
# 5. API (http://localhost:8000, docs at /docs)
uvicorn app.main:app --reload
# 6. Web app (http://localhost:5173, proxies /api to port 8000)
cd web && npm install && npm run dev
make dev-up starts the whole stack in containers with the database (DB_PORT, default 5432) and API (API_PORT, default 8000) published on 127.0.0.1 only. Run make help for all targets.
PDF rendering on a dev machine: WeasyPrint needs Pango and HarfBuzz (apt install libpango-1.0-0 libpangoft2-1.0-0 libharfbuzz0b fonts-dejavu-core fonts-liberation fonts-crosextra-carlito on Debian/Ubuntu). Without them the app still runs but produces plain-text PDFs and logs a warning; use the Docker image to see the real layout.
Seeded accounts (scripts/seed.py): adm001@acmepharma.in (System Admin), gm001@acmepharma.in (GM), am001@acmepharma.in (AM), mr001@acmepharma.in (MR) — one company, one headquarters (BBSR), 15 doctors (3 core) and 5 chemists assigned to the MR.
Configuration
All settings come from environment variables (or .env); nothing secret lives in code. See app/config/settings.py.
Deployment (read by Docker Compose)
| Variable | Default | Purpose |
|---|---|---|
POSTGRES_USER / POSTGRES_DB |
pharma |
Database credentials and name |
POSTGRES_PASSWORD |
required | Database password |
SECRET_KEY |
required | JWT signing key — 64+ random characters |
DOMAIN |
localhost |
Host name Caddy serves and obtains a certificate for (:80 = plain HTTP, see below) |
HTTP_PORT / HTTPS_PORT |
80 / 443 |
Host ports Caddy publishes (setup.sh picks free ones) |
ENVIRONMENT |
production |
production enables the start-up safety checks |
WEB_CONCURRENCY |
2 |
Uvicorn worker processes |
Application
| Variable | Default | Purpose |
|---|---|---|
DATABASE_URL |
local Postgres | postgresql+asyncpg://… (Alembic derives a psycopg URL from it) |
PDF_STORAGE_PATH |
./storage/pdf |
Where approved DCR PDFs are written |
CORS_ORIGINS |
localhost dev origins | Allowed browser origins |
DEBUG / DB_ECHO |
false |
Verbose logging / SQL echo |
PDF_COMPANY_NAME, _ADDRESS, _WEBSITE, _EMAIL, _PHONE |
Celtic details | Letterhead on every PDF |
Business rules — settings rather than constants, so they change without a code change:
| Variable | Default | Rule |
|---|---|---|
BACKDATING_WINDOW_DAYS |
7 |
How many days back an MR may file |
SUBMISSION_DEADLINE_DAYS |
1 |
Report for date D must be submitted by end of D+N |
MIN_RETURN_REASON_LENGTH |
10 |
Minimum length of a manager's return reason |
ACCESS_TOKEN_EXPIRE_MINUTES |
15 |
Access-token life |
REFRESH_TOKEN_EXPIRE_DAYS |
7 |
Refresh-token life |
LOGIN_MAX_FAILED_ATTEMPTS |
10 |
Failed logins before lockout |
Web application
A React 18 + TypeScript (Vite) single-page app in web/, styled as a minimal SaaS console: sidebar navigation with the Celtic logo, a top bar with the user menu, cards, tables with search and filters, side drawers for forms, toast notifications, and loading / empty / error states on every screen. It works down to phone width (the sidebar becomes a slide-in menu) and remembers whether the sidebar is collapsed.
web/src/
├── main.tsx, App.tsx Entry point; role-aware route table and sign-in gate
├── api/ client.ts (fetch, token refresh, downloads) · index.ts (all endpoints)
├── lib/ router (hash-based), session (auth context), navigation, formatting
├── hooks/useAsync.ts Data loading with loading / error / reload state
├── components/
│ ├── ui/ Button, Card, Badge, Field, DataTable, Tabs, Modal/drawer, Toast, Icon, …
│ ├── layout/ AppShell, Sidebar, Topbar
│ └── reports/ StatusBadge, WorkflowTracker, ApprovalTimeline, VisitList, AddVisitForm, ReviewPanel
├── pages/ Login, Dashboard, Directory, Products, Profile
│ ├── reports/ list, new, detail (with review actions)
│ ├── forms/ company forms: table, row drawer, approval state
│ └── admin/ Setup (tabs) and Users
└── styles/ tokens.css · base.css · layout.css · components.css
Screens are role-aware: MR / AM / GM get a dashboard with counts and a "waiting on you" queue; the Administrator gets an organisation overview. Routes use hash URLs (#/reports/<id>), so every screen is linkable and the back button works behind any static file server. To add a page, create it under pages/, add a route in App.tsx and a menu entry in lib/navigation.ts.
Branding and theme
The UI uses the Celtic logo and the colours taken from it.
| Token | Value | Used for |
|---|---|---|
| Brand blue | #285AA8 (dark #23509A) |
Primary buttons, links, active navigation, sign-in panel |
| Brand orange | #E95C00 |
Small accents (sign-in panel highlight, logo star) |
| Brand grey | #808080 |
The logo's dot; text and borders use a neutral grey scale |
- The logo is
web/public/celtic-logo.svg; it is shown in the sidebar, on the sign-in page and as the browser-tab icon. Replace that one file to change it everywhere in the web app. - All colours, radii, shadows and sizes are CSS variables in
web/src/styles/tokens.css— change--brand-*there to re-theme the whole app. Status colours (green approved, amber in review, red returned) are semantic and intentionally not brand-coloured. - The PDF letterhead is text (company details from the
PDF_COMPANY_*settings) and shares its own stylesheet inapp/pdf/templates/base.html.
Roles and permissions
| Role | Can do |
|---|---|
| MR | File and edit own DCRs while DRAFT/RETURNED; submit; edit company forms |
| AM | Review DCRs of own headquarters: approve or return; sees only the approval forms (Tour Plan, Standard Tour Plan, Fare Chart) |
| GM | Read-only: view reports, users and headquarters across the company; no approval step |
| ADMIN | Provision users, headquarters and masters. Cannot approve or read report content |
Permissions are checked in two separate layers, because most RBAC bugs live in the gap between them:
# Layer 1 — WHAT may this role do?
current_user: User = Depends(require_role(Role.MR))
# Layer 2 — WHOSE data may this specific user touch?
await assert_can_view_dcr(db, current_user, dcr)
"AM" is a role; "AM of this headquarters" is a scope. Both are mandatory on scoped endpoints, and each has a negative test asserting 403 — not 404 or an empty 200 — so a scope bug cannot hide behind an ambiguous response.
The System Administrator can never approve: ADMIN matches no transition in the state machine, so the audit trail stays evidence that a manager made the decision.
The approval workflow
DRAFT ──submit(MR)──► PENDING_AM ──approve(AM)──► APPROVED (terminal, immutable)
▲ │
│ └──return(AM)──┐
└──────submit(MR)───── RETURNED ◄───────┘
RETURNEDrecords who returned it in thereturned_by_rolecolumn. There is no General Manager stage; the GM is a read-only role.APPROVEDis both "finally approved" and "locked": immutability is a property of the state, enforced in one guard, so there is no window where a report is approved but still editable.- Managers change state and add comments; they never edit another person's call rows.
- Returning a report requires a reason (
MIN_RETURN_REASON_LENGTH).
The whole workflow is app/approvals/state_machine.py, a table of transitions with the role allowed to make each one.
Company forms and PDFs
The csv_data module exposes the company's paper forms as editable tables, importable from the CSV files in the repository root.
| Form | Table | Layout highlights |
|---|---|---|
| Standard Tour Plan ¹ | standard_tour_plan |
Route places, place count |
| Tour Plan ¹ | tour_plan |
Date/from/to, POB total, stockist-visit count |
| Fare Chart ¹ | fare_chart |
Travel log with total kilometers |
| Area Covered | area_covered |
Area coverage and sale objectives |
| Core Doctor Call Conversion | core_dr_call_conversion |
Doctor follow-up with status summary |
| Field Activity Report | field_activity_report |
Daily activity, totals and call averages |
| Sales and Stock Statement | sales_and_stock_statement |
Opening/receipt/sale/closing with value totals |
| Sales Projection | sales_projection |
Landscape, product-wise projection |
| Weekly Report | weekly_report |
One page per saved report |
| Daily Call Report (CSV) | daily_call_report_csv |
Doctor and chemist sections (not exposed in the UI) |
¹ Approval forms — the AM can only see these, and their PDF carries the AM approval signature line.
Approval forms are per MR. Tour Plan, Standard Tour Plan and Fare Chart rows belong to the MR who entered them (owner_user_id), each MR sees and numbers only their own, and the approval (form_approval) is kept per form and MR. A request goes only to the Area Manager who currently manages that MR's headquarters: any other Area Manager gets a 403 and never sees it in their list, and an MR whose headquarters has no Area Manager cannot send a form. Editing an MR's rows withdraws only that MR's approval. An admin fills a form in on behalf of one chosen MR and can never approve.
Monthly download. Forms with a month (Sales Projection, Weekly Report, Field Activity Report, Core Doctor Call Conversion, Area Covered, Sales and Stock Statement) get a Month selector. The table and the PDF (GET /csv-data/{form}/pdf?month=November) then cover that month only; a month with no rows is refused rather than printed blank.
How PDFs are built. The DCR (dcr.html) extends the stylesheet app/pdf/templates/base.html. The company forms do not: each is a copy of the company's printed form — same letterhead, title, column headings, column proportions, number of ruled lines and signature block — built from app/pdf/templates/forms/_form_base.html plus one small template per form. app/pdf/forms.py decides what each form prints (headings, widths, how many blank lines); stored rows fill the lines from the top, and rows beyond the printed length continue on the next page with the header repeated. Fonts: Liberation Sans/Serif and Carlito (the Arial / Times / Calibri look-alikes of the printed forms), with DejaVu as fallback.
GET /api/v1/csv-data/{table}/pdfprints every stored row;…/rows/{id}/pdfprints one row.- Blank numbered lines pad each form to its printed length; longer data is never truncated.
- Identity fields (name, headquarter, month) are gathered from all rows, and for an MR always reflect the logged-in account.
Importing data. python -m scripts.import_csv_data loads the CSV files from the repo root. It replaces the contents of every form table — never run it against a database holding real data — use python -m scripts.import_csv_data --only-empty instead, which loads only tables that have no rows (e.g. the product lists) and never deletes anything. It is deliberately not part of container start-up, and the CSVs are not in the Docker image.
API overview
Base path /api/v1; interactive documentation at /docs and /redoc, schema at /openapi.json.
| Module | Prefix | Purpose |
|---|---|---|
| auth | /auth |
Login, refresh (rotating tokens), logout, current user |
| users | /users |
User administration and the MR profile |
| organization | /headquarters |
Headquarters and AM↔HQ mapping |
| doctors, chemists | /doctors, /chemists |
Masters and MR assignment |
| products | /products |
Product master and stock inventory list |
| reports | /dcr |
DCR CRUD, calls, submit / return / approve, PDF |
| audit | /audit |
Read access to the audit log |
| csv_data | /csv-data |
Company form tables, rows and PDFs |
Errors use one envelope: {"error": {"code": "…", "message": "…", "details": {…}}}.
Project layout
app/
├── main.py App factory, error envelope, CORS, production preflight
├── config/ Pydantic settings
├── database/ Engine, session, declarative base, model registry
├── common/ Enums, exceptions, base repository, helpers
├── auth/ JWT, argon2, refresh rotation, permission dependencies
├── users/ organization/ doctors/ chemists/ products/ Masters
├── reports/ DailyCallReport, DoctorCall, ChemistCall ← core domain
├── approvals/ The state machine ← the rules live here
├── audit/ Append-only log
├── csv_data/ Company form tables and their endpoints
├── pdf/ service.py, forms.py, templates/ (base, dcr, forms/*)
└── api/v1/ Router mount point
alembic/ Migrations
scripts/ seed, CSV import, audit hardening SQL, container entrypoint
web/ React application (its own Dockerfile) — see "Web application"
tests/ unit/, integration/, e2e/
Domain modules share the same file set, so a new module is a copy of a folder: models.py (ORM), schemas.py (Pydantic), service.py (business rules, transactions, audit calls) and router.py (HTTP only).
Design decisions
Database-enforced constraints. Under concurrency only the database can guarantee these, so they are constraints, not just service checks:
-- One report per MR per calendar day
ALTER TABLE daily_call_report ADD CONSTRAINT uq_dcr_mr_date UNIQUE (mr_user_id, report_date);
-- No duplicate doctor call within one report
ALTER TABLE doctor_call ADD CONSTRAINT uq_call_dcr_doctor UNIQUE (dcr_id, doctor_id);
-- Exactly one active Area Manager per headquarters
CREATE UNIQUE INDEX uq_active_am_per_hq ON am_headquarters (headquarters_id)
WHERE effective_to IS NULL;
tests/integration/test_db_constraints.py proves each rejects the bad case and permits the good one.
Tables deliberately not created. The original brief listed 28 tables; the core domain uses far fewer:
| Not created | Instead |
|---|---|
role |
user.role enum — four fixed roles; a lookup table adds a join to every request |
mr_profile, area_manager_profile |
Merged into user |
area_territory |
Merged into headquarters — the HQ is the territory |
mr_assignment |
user.headquarters_id — an MR has exactly one HQ |
core_doctor_conversion |
doctor.is_core_doctor + doctor_call.conversion_status |
comments |
audit_log.comment — every comment attaches to a state change |
approval_history |
A filtered query over audit_log — one source of truth |
Single-VPS deployment. No Kubernetes, message broker or microservices: one company and a handful of MRs do not need them, and each extra piece is something to operate.
Testing
Tests run against a real PostgreSQL database — never SQLite. The system relies on partial unique indexes, composite uniques and jsonb, which SQLite does not implement the same way.
make dev-db # starts Postgres and creates pharma_test
export TEST_DATABASE_URL=postgresql+asyncpg://pharma:<password>@localhost:5432/pharma_test
pytest -q # everything
pytest tests/unit -q # state machine, PDF layouts — see note below
tests/unit is mostly pure logic (state machine, form PDF view-models and rendering), but a couple of tests there use the database. make lint runs ruff.
Production checklist
- Set a real
SECRET_KEY(64 random characters) andPOSTGRES_PASSWORD, from a secrets manager — never in the repo. - Run
scripts/harden_audit.sqlonce as a superuser to makeaudit_loggenuinely append-only (revokes UPDATE/DELETE from the app role, plus a trigger as a backstop). Then connect the API as thepharma_approle it creates. - Change every seeded password, or do not seed at all and create real users.
- Set
DOMAINto the real host name and, once HTTPS is confirmed, enable the commented HSTS header inCaddyfile. - Confirm the blocking business rules marked in code as
A1–A16/Q1–Q9; the shipped defaults are assumptions, not decisions. - Back up
pgdataandpdfstore, and test a restore — an untested backup is not a backup. - Point an error tracker (e.g. Sentry) at the app so production 500s surface as errors.
Known limitations
- Company-form approval is per form, not per row. The tour plan, standard tour plan and fare chart each have one server-stored approval state (
draft → pending → approved). The MR sends a form that has rows, the Area Manager approves it, and the PDF signature is decided by the server. Any change to the rows withdraws the approval. web/node_modulesandweb/distare committed to the repository (with stale build output) and should be untracked; Docker builds the app from source.- The test suite needs a reachable PostgreSQL; some tests under
tests/unitalso need it.
Troubleshooting
| Symptom | Fix |
|---|---|
required variable POSTGRES_PASSWORD is missing |
Create .env from .env.example and fill it in |
API exits with SECRET_KEY must be a random value… |
Set a SECRET_KEY of 32+ random characters |
API exits with WeasyPrint system libraries are required |
Use the provided image, or install Pango/HarfBuzz and DejaVu fonts |
| PDFs are plain text | WeasyPrint is unavailable (/health shows "pdf_engine": "fallback") |
no pq wrapper available from Alembic |
pip install --upgrade --force-reinstall "psycopg[binary]" |
Web build in Docker fails on esbuild |
A host node_modules leaked in; check web/.dockerignore |
npm run build fails with tsc: Permission denied |
The committed web/node_modules has lost its executable bits. Delete it and run npm ci (or build through Docker, which does this) |
failed to bind host port 0.0.0.0:80 … address already in use |
Another service owns 80/443. Run make setup (or make deploy): it switches to free ports. Manually: DOMAIN=:80, HTTP_PORT=2000, HTTPS_PORT=2443 in .env |
password authentication failed after editing POSTGRES_PASSWORD |
The database keeps its original password — see Rotating the database password |
| API container unhealthy / restarting | docker compose logs api: a weak SECRET_KEY or missing WeasyPrint stops a production start on purpose |
Port 5432 in use during make dev-db |
Set DB_PORT (e.g. DB_PORT=5433) and adjust TEST_DATABASE_URL |