Files
Shubhankar45 8fdd20b29f Production
2026-09-22 06:23:46 +00:00

27 KiB
Raw Permalink Blame History

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

  1. Features
  2. Quick start with Docker — incl. VPS over SSH, ports, operations
  3. Local development
  4. Configuration
  5. Roles and permissions
  6. The approval workflow
  7. Company forms and PDFs
  8. API overview
  9. Project layout
  10. Design decisions
  11. Testing
  12. Production checklist
  13. Known limitations
  14. 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 .env from .env.example if it is missing;
  • generates a strong SECRET_KEY (if missing or a placeholder), and sets ENVIRONMENT=production, DEBUG=false;
  • generates POSTGRES_PASSWORD for 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 DOMAIN with 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_PASSWORD and SECRET_KEY (make setup fills them in).
  • In ENVIRONMENT=production the API refuses to boot if WeasyPrint's system libraries are missing or SECRET_KEY is a placeholder / under 32 characters. GET /health reports "pdf_engine": "weasyprint".
  • Set RUN_MIGRATIONS=0 on the api service 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-db publishes it on 127.0.0.1:5433 only.
  • Volumes to back up: pgdata (use pg_dump) and pdfstore (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 in app/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 ◄───────┘
  • RETURNED records who returned it in the returned_by_role column. There is no General Manager stage; the GM is a read-only role.
  • APPROVED is 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}/pdf prints every stored row; …/rows/{id}/pdf prints 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

  1. Set a real SECRET_KEY (64 random characters) and POSTGRES_PASSWORD, from a secrets manager — never in the repo.
  2. Run scripts/harden_audit.sql once as a superuser to make audit_log genuinely append-only (revokes UPDATE/DELETE from the app role, plus a trigger as a backstop). Then connect the API as the pharma_app role it creates.
  3. Change every seeded password, or do not seed at all and create real users.
  4. Set DOMAIN to the real host name and, once HTTPS is confirmed, enable the commented HSTS header in Caddyfile.
  5. Confirm the blocking business rules marked in code as A1–A16 / Q1–Q9; the shipped defaults are assumptions, not decisions.
  6. Back up pgdata and pdfstore, and test a restore — an untested backup is not a backup.
  7. 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_modules and web/dist are 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/unit also 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