Velora — System Flowcharts

Emergency medicine discovery and admin-attested 24/7 pharmacy access. A patient searches a medicine, sees which nearby verified pharmacies are open and have it in stock, and can hold one unit for 30 minutes before travelling.

One Express + TypeScript API · Prisma ORM on PostgreSQL as the only source of truth · MongoDB as a copy used for fast search · JWT + refresh tokens + RBAC · React 18 SPA with React Router and TanStack Query. No Redis, no queue, no PostGIS, no microservices: none of them are in the syllabus.

CSA213: Node, Express, Prisma, JWT, RBAC, TS CSA214: PostgreSQL, SQL, normalisation, indexing, MongoDB Frontend: React 18 + Vite + TS Postgres decides, Mongo copies Search needs no login Hold = one transaction Verification is admin-attested REST /api/v1 · 30 endpoints 9 tables · 2 collections Roles: PATIENT · PHARMACIST · ADMIN Hold lasts 30 min

Colour key Client / React Express routes, middleware, checks Services, hooks, Prisma Databases Success Error / rejected Outside Velora Note Sequence diagrams read top to bottom; circles are step numbers; a dotted alt box shows the possible outcomes.

01 System Architecture CSA213CSA214

Who uses Velora, what sits outside it, and the three layers inside it.

1a. Context · three user roles and the two outside things Velora points to

Mermaid source

flowchart TB
  P(["Patient<br/>no login to search"])
  PH(["Pharmacist<br/>owns one pharmacy"])
  AD(["Admin<br/>seeded account"])
  V["Velora"]
  GM["Google Maps<br/>link only, no API key"]
  LIC["State pharmacy council<br/>checked by hand, no API"]
  P -->|"search, hold, cancel"| V
  PH -->|"stock, hours, collect holds"| V
  AD -->|"approve, suspend, reports"| V
  V -->|"Directions button opens"| GM
  AD -.->|"looks up licence manually"| LIC
  class P,PH,AD client
  class V api
  class GM,LIC ext

1b. The three layers · one Express app, two databases

Mermaid source

flowchart TB
  subgraph L1["Layer 1 - Client"]
    FE["React SPA<br/>pages call /api/v1"]
  end
  subgraph L2["Layer 2 - Express API, Node.js + TypeScript"]
    RT["Routes<br/>auth, search, holds,<br/>pharmacies, admin"]
    MW["Middleware<br/>authenticate, authorize, validate"]
    CT["Controllers<br/>read req, send res"]
    SV["Services<br/>business rules, transactions"]
  end
  subgraph L3["Layer 3 - Data"]
    PR["Prisma Client"]
    PG[("PostgreSQL<br/>source of truth")]
    MD["MongoDB driver"]
    MG[("MongoDB<br/>search copy")]
  end
  FE -->|"HTTP + JSON"| RT
  RT --> MW --> CT --> SV
  SV -->|"all writes + final checks"| PR --> PG
  SV -->|"fast search reads"| MD --> MG
  class FE client
  class RT,MW,CT api
  class SV,PR,MD svc
  class PG,MG db

Why two databases: CSA214 teaches both, and each has a real job here. PostgreSQL holds relations, constraints and transactions. MongoDB holds one ready-made document per stock item, shaped for the search screen. A hold is never confirmed from MongoDB.

02 Syllabus Coverage Map CSA213CSA214

Every week of both courses mapped to where it shows up in Velora. If a part of the design can't be traced to a row here, it isn't in the design.

Wk	CSA213 Advanced Programming	Where in Velora
1	HTTP methods, status codes, Node modules, process object	200 / 201 / 400 / 401 / 403 / 404 / 409 per route (§14); config from `process.env`
2	fs module, sync vs async	seed script reads JSON with `fs.promises` (§07)
3	HTTP module, npm, Express, GET/POST routing	`app.listen`, one route file per resource
4	Built-in, custom, third-party middleware; REST; versioning	express.json, cookie-parser, morgan, authenticate, authorize, validate; `/api/v1` (§03)
5	Express folder structure, DB + ORM intro	routes / controllers / services split (§03)
6	Prisma setup, CRUD	all service-layer reads and writes
7	Migrations, seeding, relations, transactions	schema relations (§09), `seed.ts` (§07), hold transaction (§06)
8	JWT, password hashing, protected routes	register + login (§04)
9	Refresh tokens, RBAC	RefreshToken table, `authorize(role)` (§04)
10	Event loop, error handling, custom error classes	`AppError`, `asyncHandler`, loop rules (§12)
11	TypeScript, OOP in JS	TS throughout; service classes; `AppError extends Error`
12	Deployment, dev vs prod, LLD	Vercel + Heroku, `.env` (§15); this page

Wk	CSA214 Data Engineering	Where in Velora
1	DBMS, schema, data types, constraints	UNIQUE email, CHECK quantity ≥ 0, NOT NULL keys
2	SQL, DDL / DML	Prisma migrations produce the DDL; raw SQL for the view
3	Filtering, wildcards, string + date functions, aggregates, CASE	ILIKE medicine search; stock badge CASE (§05)
4	GROUP BY, HAVING, COALESCE, date/time	expired-holds report; missing-hours report (§08)
5	ER modelling, inner / left / right joins	ER diagram (§09); pharmacy – stock – medicine
6	Self, cross, non-equi joins; non-correlated subqueries	same-salt alternatives, open-now join, missing days (§05, §08)
7	Correlated subqueries, EXISTS / NOT EXISTS, CTE, views	compliance queries, verified-open view (§08)
8	Normalisation 1NF–BCNF, transactions, ACID	credential split (§09); hold transaction (§06)
9	Indexing, query optimisation	B-Tree indexes + EXPLAIN ANALYZE (§11)
10	MongoDB, BSON, CRUD, $set / $inc	copy writes after each commit (§06, §07)
11	Operators, embedding vs referencing	availability document design (§10)
12	Aggregation, $lookup	search and report pipelines (§10)

03 Request Path + MVC Structure CSA213 wk 3–7

First the app-level middleware every request passes through, in the order it is registered in app.ts. Then one real request, placing a hold, followed through the folders it touches.

3a. App-level middleware order · app.ts

Mermaid source

flowchart TB
  IN(["Incoming request"]) --> M1["express.json<br/>parse JSON body"]
  M1 --> M2["cookieParser<br/>read refresh cookie"]
  M2 --> M3["morgan<br/>log method, path, status"]
  M3 --> R["app.use /api/v1 routers"]
  R --> HIT{"route matched?"}
  HIT -->|"yes"| CHAIN["route middleware + controller"]
  HIT -->|"no"| NF["notFound<br/>AppError 404"]
  CHAIN -->|"success"| OUT(["JSON response"])
  CHAIN -->|"next(err)"| EH["errorHandler<br/>always registered last"]
  NF --> EH
  EH --> OUT2(["JSON error"])
  class IN,OUT client
  class M1,M2,M3,R api
  class HIT api
  class CHAIN svc
  class NF,EH bad
  class OUT2 bad

3b. One request through the folders · POST /api/v1/holds

Mermaid source

flowchart TB
  A["routes/holds.routes.ts<br/>router.post with authenticate,<br/>authorize PATIENT, validate"] --> B["controllers/hold.controller.ts<br/>create(req, res)"]
  B --> C["services/HoldService.ts<br/>placeHold(patientId, stockItemId)"]
  C --> D["db/prisma.ts<br/>prisma.$transaction"]
  C --> E["db/mongo.ts<br/>availability.updateOne"]
  B --> F(["res.status(201).json(hold)"])
  C -.->|"throws AppError 409"| G["middlewares/errorHandler.ts"]
  class A,B api
  class C svc
  class D,E db
  class F good
  class G bad

// src/app.ts
const app = express();
app.use(express.json());          // built-in
app.use(cookieParser());          // third-party
app.use(morgan('dev'));           // third-party logging

app.use('/api/v1/auth',       authRoutes);
app.use('/api/v1/search',     searchRoutes);
app.use('/api/v1/holds',      holdRoutes);
app.use('/api/v1/pharmacies', pharmacyRoutes);
app.use('/api/v1/admin',      adminRoutes);

app.use(notFound);                // 404 for unknown paths
app.use(errorHandler);            // must be last
export default app;

// src/routes/holds.routes.ts
router.post('/', authenticate, authorize('PATIENT'),
            validate(placeHoldSchema), asyncHandler(holdController.create));

velora/
├─ client/                  React SPA (§13)
└─ server/
   ├─ prisma/
   │  ├─ schema.prisma      models, relations, @@index
   │  ├─ migrations/
   │  └─ seed.ts
   ├─ data/                 medicines, pharmacies, areas .json
   └─ src/
      ├─ server.ts          app.listen(process.env.PORT)
      ├─ app.ts
      ├─ routes/            auth search holds pharmacies admin
      ├─ controllers/       one per route file
      ├─ services/          AuthService SearchService HoldService
      │                     StockService PharmacyService AdminService
      ├─ middlewares/       authenticate authorize validate
      │                     notFound errorHandler
      ├─ validators/        request schemas per route
      ├─ db/                prisma.ts, mongo.ts
      ├─ types/             express.d.ts adds req.user
      └─ utils/             AppError.ts asyncHandler.ts jwt.ts

routes/ only lists the endpoint and its middleware. No logic.
controllers/ reads req, calls one service method, sends res. Never imports Prisma.
services/ are TypeScript classes holding the business rules; they run every database call and every transaction.
errorHandler turns any thrown AppError into a clean JSON error (§12).

04 Authentication + RBAC CSA213 wk 8–9

A short-lived access JWT kept in the React app's memory, plus a 7-day refresh token sent as an httpOnly cookie and stored hashed in PostgreSQL. Registration offers PATIENT or PHARMACIST; the one ADMIN account comes from the seed script.

4a. Register · POST /api/v1/auth/register

Mermaid source

sequenceDiagram
  autonumber
  actor U as User
  participant FE as React app
  participant API as Express /auth
  participant AS as AuthService
  participant PG as PostgreSQL
  U->>FE: fill name, email, password, role
  FE->>FE: Zod checks the form before sending
  FE->>API: POST /api/v1/auth/register
  API->>API: validate body, role must be PATIENT or PHARMACIST
  API->>AS: register(data)
  AS->>AS: bcrypt.hash with 10 rounds
  AS->>PG: prisma.user.create
  alt email is new
    PG-->>AS: user created
    AS-->>API: user without password hash
    API-->>FE: 201 Created
    FE-->>U: go to the login page
  else email already used
    PG-->>AS: Prisma error P2002
    AS-->>API: AppError 409
    API-->>FE: 409 Email already registered
  end

4b. Login · POST /api/v1/auth/login

Mermaid source

sequenceDiagram
  autonumber
  actor U as User
  participant FE as React app
  participant API as Express /auth
  participant AS as AuthService
  participant PG as PostgreSQL
  U->>FE: enter email and password
  FE->>API: POST /api/v1/auth/login
  API->>AS: login(email, password)
  AS->>PG: find user by email
  PG-->>AS: user row with password hash
  Note right of AS: bcrypt.compare
  alt password correct
    Note right of AS: sign access JWT 15 min, make refresh token 7 days
    AS->>PG: save the SHA-256 hash of the refresh token
    AS-->>API: access token, refresh token, user
    API-->>FE: 200 with access token and user in JSON, refresh token in an httpOnly cookie
    FE-->>U: logged in, sent to the home page for that role
  else wrong email or password
    AS-->>API: AppError 401
    API-->>FE: 401 Invalid email or password
  end

4c. Protected request · the checks run in this order

Mermaid source

flowchart TB
  R(["Request<br/>Authorization: Bearer token"]) --> A{"authenticate<br/>jwt.verify ok?"}
  A -->|"missing or expired"| E1["401 Unauthorized"]
  A -->|"ok, sets req.user id + role"| B{"authorize<br/>role in allowed list?"}
  B -->|"no"| E2["403 Forbidden"]
  B -->|"yes"| C{"validate<br/>body and params ok?"}
  C -->|"no"| E3["400 Bad Request<br/>with field errors"]
  C -->|"yes"| D["controller runs"]
  D --> O{"service ownership check<br/>for pharmacy routes"}
  O -->|"not the owner"| E4["403 Forbidden"]
  O -->|"owner or not needed"| OK["200 / 201"]
  class R client
  class A,B,C,O api
  class D svc
  class OK good
  class E1,E2,E3,E4 bad

Route group	Who	Extra check in the service
GET /search	anyone	—
/holds	PATIENT	only their own holds
/pharmacies/:id/*	PHARMACIST	must own pharmacy `:id`; a role alone doesn't prove that
/admin/*	ADMIN	every decision writes an audit_log row

// access token payload, signed with JWT_ACCESS_SECRET
{ "sub": 12, "role": "PHARMACIST", "iat": 1790580000, "exp": 1790580900 }

// refresh cookie set on login and refresh
res.cookie('refreshToken', token, {
  httpOnly: true,               // JavaScript can't read it
  secure: isProd,
  sameSite: 'lax',
  path: '/api/v1/auth',         // only sent to auth routes
  maxAge: 7 * 24 * 60 * 60 * 1000,
});

// middlewares/authorize.ts
export const authorize = (...roles: Role[]) =>
  (req, _res, next) =>
    roles.includes(req.user.role) ? next()
      : next(new AppError(403, 'Forbidden'));

4d. Refresh and logout · when the 15-minute access token runs out

Mermaid source

sequenceDiagram
  autonumber
  participant FE as React api client
  participant API as Express /auth
  participant PG as PostgreSQL
  FE->>API: GET /api/v1/holds/me with an expired access token
  API-->>FE: 401 token expired
  FE->>API: POST /api/v1/auth/refresh, browser sends the cookie
  API->>PG: find the token hash, check not revoked and not expired
  alt valid
    API->>PG: revoke the old row and save the new hash in one transaction
    API-->>FE: new access token in JSON, new refresh cookie
    FE->>API: retry GET /api/v1/holds/me once
    API-->>FE: 200
  else revoked, expired or missing
    API-->>FE: 401
    FE->>FE: clear auth state, go to login
  end
  Note over FE,PG: Logout sets revokedAt on the row and clears the cookie

Why a cookie for the refresh token: localStorage can be read by any script on the page, an httpOnly cookie can't. The frontend and API share one origin through a Vercel rewrite (§15), so the cookie stays first-party and needs no CORS setup.

05 Emergency Search CSA214 wk 3–7, 12

The most important read path, with no login in the way. MongoDB finds candidates quickly; PostgreSQL then confirms each pharmacy is verified and open right now, and sorts them by distance using plain SQL.

5a. Search, step by step · GET /api/v1/search

Mermaid source

sequenceDiagram
  autonumber
  actor P as Patient
  participant FE as React search page
  participant API as Express /search
  participant MG as MongoDB
  participant PG as PostgreSQL
  P->>FE: type medicine, allow location
  FE->>API: GET /api/v1/search?medicine=X and lat, lng, radiusKm
  Note right of API: public route, no token
  API->>MG: aggregate availability with $match, $lookup, $sort, $limit
  MG-->>API: up to 50 pharmacies listing it in stock
  API->>PG: keep VERIFIED and open now, measure distance
  PG-->>API: nearest first, with the stock badge
  alt at least one result
    API-->>FE: 200 with results
    FE-->>P: result cards with distance, open now, badge, Directions
  else nothing found
    API->>PG: self join on composition for same-salt medicines
    API-->>FE: 200 with alternatives
    FE-->>P: "Not found nearby, these contain the same salt"
  end

Steps 1–2: the page sends the medicine name and the patient's location. No token is needed.
Steps 3–4: MongoDB returns up to 50 pharmacies whose copy says the medicine is in stock (§10).
Steps 5–6: PostgreSQL keeps only VERIFIED pharmacies that are open now, measures the distance, and sorts nearest first.
Steps 7–11: the badge says CONFIRMED if stock was updated in the last 6 hours, otherwise STALE. If nothing is found, same-salt alternatives are offered.

5b. Distance without any extension · bounding box, then Haversine

Mermaid source

flowchart TB
  A["Input<br/>lat, lng, radiusKm = 5"] --> B["Bounding box<br/>dLat = r / 111<br/>dLng = r / (111 x cos lat)"]
  B --> C["WHERE lat BETWEEN lat - dLat AND lat + dLat<br/>AND lng BETWEEN lng - dLng AND lng + dLng<br/>uses the B-Tree index"]
  C --> D["Haversine on rows left<br/>6371 x 2 x asin(sqrt(...))"]
  D --> E["WHERE distance at most radiusKm<br/>ORDER BY distance LIMIT 20"]
  E --> F(["nearest pharmacies"])
  class A client
  class B,C,D,E db
  class F good

-- SearchService: $1 ids from Mongo, $2 lat, $3 lng,
-- $4 dLat, $5 dLng, $6 radiusKm, $7 medicine id
WITH nearby AS (
  SELECT v.id, v.name, v.phone, v.lat, v.lng,
         6371 * 2 * ASIN(SQRT(
           POWER(SIN(RADIANS(v.lat - $2) / 2), 2) +
           COS(RADIANS($2)) * COS(RADIANS(v.lat)) *
           POWER(SIN(RADIANS(v.lng - $3) / 2), 2)
         )) AS distance_km
  FROM verified_open_pharmacy v          -- view, §08
  WHERE v.id = ANY($1)
    AND v.lat BETWEEN $2 - $4 AND $2 + $4
    AND v.lng BETWEEN $3 - $5 AND $3 + $5
)
SELECT n.*, s.quantity, s.price,
       CASE WHEN s.last_updated >= NOW() - INTERVAL '6 hours'
            THEN 'CONFIRMED' ELSE 'STALE' END AS stock_badge
FROM nearby n
JOIN stock_item s ON s.pharmacy_id = n.id AND s.medicine_id = $7
WHERE n.distance_km <= $6 AND s.quantity > 0
ORDER BY n.distance_km
LIMIT 20;

-- no results: self join for the same salt
SELECT m2.id, m2.name
FROM medicine m1
JOIN medicine m2 ON m2.composition = m1.composition
                AND m2.id <> m1.id
WHERE m1.id = $7;

The final s.quantity > 0 check runs against PostgreSQL, so a stale "in stock" in MongoDB can never show a pharmacy that is actually sold out.

06 Stock Hold: One ACID Transaction CSA213 wk 7CSA214 wk 8correctness-critical

Two patients tap "Hold" on the last unit at the same moment. Only one may win. The quantity is lowered by one only if it is still at least one, inside a Prisma transaction, so the database decides the winner.

6a. Two patients, one unit left · how the race is decided

Mermaid source

sequenceDiagram
  autonumber
  actor A as Patient A
  actor B as Patient B
  participant API as Express /holds
  participant PG as PostgreSQL
  Note over PG: stock item 42 has quantity 1
  A->>API: POST /holds for item 42
  B->>API: POST /holds for item 42
  API->>PG: A - BEGIN, lower quantity by 1 only if at least 1
  PG-->>API: A - 1 row updated, quantity now 0
  API->>PG: A - insert hold ACTIVE for 30 min, COMMIT
  API-->>A: 201 reserved for 30 minutes
  API->>PG: B - BEGIN, lower quantity by 1 only if at least 1
  PG-->>API: B - 0 rows updated
  API->>PG: B - ROLLBACK
  API-->>B: 409 just missed it
  Note over API,PG: after A commits, MongoDB gets $inc quantity -1

6b. Life of a hold · every hold ends in exactly one of three states

Mermaid source

stateDiagram-v2
  direction LR
  [*] --> ACTIVE: patient places hold
  ACTIVE --> COLLECTED: pharmacist marks picked up
  ACTIVE --> CANCELLED: patient cancels
  ACTIVE --> EXPIRED: 30 min pass
  COLLECTED --> [*]
  CANCELLED --> [*]
  EXPIRED --> [*]
  note right of CANCELLED: unit goes back to stock
  note right of EXPIRED: unit goes back to stock

// services/HoldService.ts
async placeHold(patientId: number, stockItemId: number) {
  const active = await prisma.hold.count({ where: { patientId, status: 'ACTIVE' } });
  if (active > 0) throw new AppError(409, 'You already have an active hold');

  const hold = await prisma.$transaction(async (tx) => {
    // 1. lazy expiry: release this item's old holds and give the units back
    const expired = await tx.hold.updateMany({
      where: { stockItemId, status: 'ACTIVE', expiresAt: { lt: new Date() } },
      data:  { status: 'EXPIRED' },
    });
    if (expired.count > 0) {
      await tx.stockItem.update({
        where: { id: stockItemId },
        data:  { quantity: { increment: expired.count } },
      });
    }
    // 2. lower quantity by 1, but only if at least 1 is left
    const res = await tx.stockItem.updateMany({
      where: { id: stockItemId, quantity: { gte: 1 } },
      data:  { quantity: { decrement: 1 } },
    });
    if (res.count === 0) throw new AppError(409, 'Just missed it');   // rolls back

    // 3. record the hold
    return tx.hold.create({
      data: { stockItemId, patientId, status: 'ACTIVE',
              expiresAt: new Date(Date.now() + 30 * 60_000) },
    });
  });

  await this.mongo.availability.updateOne({ _id: stockItemId }, { $inc: { quantity: -1 } });
  return hold;
}

6c. Cancel and collect · patient cancels, pharmacist hands over

Mermaid source

sequenceDiagram
  autonumber
  actor P as Patient
  actor PH as Pharmacist
  participant API as Express
  participant PG as PostgreSQL
  participant MG as MongoDB
  P->>API: PATCH /api/v1/holds/7/cancel
  API->>PG: BEGIN, is hold 7 ACTIVE and owned by this patient?
  alt yes
    API->>PG: set CANCELLED, quantity + 1, COMMIT
    API->>MG: $inc quantity +1
    API-->>P: 200 cancelled
  else not active or not theirs
    API-->>P: 409 or 403
  end
  PH->>API: PATCH /api/v1/pharmacies/3/holds/8/collect
  API->>PG: is hold 8 ACTIVE, not expired, at pharmacy 3?
  API->>PG: set COLLECTED, quantity unchanged
  API-->>PH: 200 collected

Why the conditional update works: UPDATE ... WHERE quantity >= 1 takes a row lock, so the second transaction waits, then re-checks the condition against the committed value and updates 0 rows. That is the Isolation part of ACID doing the work.

07 Stock, Opening Hours, Seed Data CSA213 wk 2, 6–7CSA214 wk 10

Pharmacists keep their own stock and hours up to date; a seed script provides demo data. PostgreSQL saves first, then MongoDB's copy is updated. If the copy fails, the row is flagged so an admin can repair search, instead of it silently going wrong.

7a. Stock update · PATCH /api/v1/pharmacies/:id/stock/:itemId

Mermaid source

sequenceDiagram
  autonumber
  actor PH as Pharmacist
  participant FE as React dashboard
  participant API as Express /pharmacies
  participant PG as PostgreSQL
  participant MG as MongoDB
  PH->>FE: change quantity to 12 and price to 30
  FE->>API: PATCH /api/v1/pharmacies/3/stock/42
  Note right of API: authenticate, authorize PHARMACIST, validate
  API->>PG: does this user own pharmacy 3, is it VERIFIED?
  alt not the owner
    API-->>FE: 403
  else owner
    API->>PG: update stock item 42, lastUpdated = now
    PG-->>API: committed
    API->>MG: updateOne with $set quantity, price, available, lastUpdated
    alt MongoDB ok
      API-->>FE: 200 saved
    else MongoDB fails
      API->>PG: set mongoSynced = false on item 42
      API-->>FE: 200 saved, search copy will be repaired
    end
  end

7b. Opening hours · PUT /api/v1/pharmacies/:id/hours

Mermaid source

flowchart TB
  A["PUT /api/v1/pharmacies/3/hours<br/>7 days of open and close times"] --> B{"a day closes<br/>after midnight?"}
  B -->|"yes, e.g. 20:00 to 02:00"| C["split into two rows<br/>day d 20:00-23:59<br/>day d+1 00:00-02:00"]
  B -->|"no"| D["one row per day"]
  C --> T
  D --> T
  subgraph T["prisma.$transaction"]
    T1["deleteMany old hours<br/>for pharmacy 3"] --> T2["createMany new rows"]
  end
  T --> OK(["200, open-now view<br/>uses the new rows"])
  class A client
  class B api
  class C,D svc
  class T1,T2 db
  class OK good

7c. Seed script · npx prisma db seed, uses the fs module

Mermaid source

flowchart TB
  J["data/medicines.json<br/>data/pharmacies.json<br/>data/areas.json"] -->|"fs.promises.readFile"| S["prisma/seed.ts"]
  S -->|"createMany"| PG[("PostgreSQL")]
  S -->|"create ADMIN user<br/>password from .env"| PG
  PG -->|"read back rows with ids"| S2["build one availability<br/>document per stock item"]
  S2 -->|"deleteMany, then insertMany"| MG[("MongoDB")]
  class J client
  class S,S2 svc
  class PG,MG db

7d. Repairing the search copy · POST /api/v1/admin/search-index/rebuild

Mermaid source

sequenceDiagram
  autonumber
  actor AD as Admin
  participant API as Express /admin
  participant PG as PostgreSQL
  participant MG as MongoDB
  AD->>API: POST /api/v1/admin/search-index/rebuild
  API->>PG: find stock items where mongoSynced is false
  PG-->>API: 3 rows with pharmacy and medicine joined
  loop for each row
    API->>MG: replaceOne with upsert true
    API->>PG: set mongoSynced = true
  end
  API-->>AD: 200 rebuilt 3 documents

08 Pharmacy Approval + Compliance Queries CSA214 wk 4, 6–7admin-attested

No Indian pharmacy council offers a public licence-check API, so an admin checks each licence by hand. Only VERIFIED pharmacies appear in search, and every admin decision writes an audit_log row in the same transaction.

8a. Pharmacy status · searchable only while VERIFIED

Mermaid source

stateDiagram-v2
  direction LR
  [*] --> PENDING: pharmacist submits licence
  PENDING --> VERIFIED: admin approves
  PENDING --> REJECTED: admin rejects with reason
  REJECTED --> PENDING: pharmacist resubmits
  VERIFIED --> SUSPENDED: licence expired or complaint
  SUSPENDED --> VERIFIED: admin reinstates
  note right of VERIFIED: only state shown in search

8b. Pharmacist onboarding · POST /api/v1/pharmacies

Mermaid source

sequenceDiagram
  autonumber
  actor PH as Pharmacist
  participant FE as React onboarding form
  participant API as Express /pharmacies
  participant PG as PostgreSQL
  PH->>FE: pharmacy name, address, location, licence number, issue and expiry dates
  FE->>API: POST /api/v1/pharmacies
  Note right of API: authorize PHARMACIST, validate, expiry must be in the future
  API->>PG: BEGIN
  API->>PG: create pharmacy with status PENDING
  API->>PG: create credential linked to it
  API->>PG: COMMIT
  API-->>FE: 201, waiting for admin approval
  FE-->>PH: status page shows PENDING

8c. Admin approval · PATCH /api/v1/admin/pharmacies/:id/verify

Mermaid source

sequenceDiagram
  autonumber
  actor AD as Admin
  participant API as Express /admin
  participant PG as PostgreSQL
  participant MG as MongoDB
  AD->>API: GET /api/v1/admin/pharmacies?status=PENDING
  API->>PG: pharmacy JOIN credential WHERE status PENDING
  PG-->>API: rows
  API-->>AD: pending list with licence number and expiry
  Note left of AD: checks the licence by hand
  AD->>API: PATCH /api/v1/admin/pharmacies/3/verify
  API->>PG: BEGIN, status VERIFIED, insert audit log row, COMMIT
  API->>MG: pharmacies collection, $set verified true
  API-->>AD: 200, pharmacy now in search

Compliance dashboard

Question the admin asks	SQL technique	Wk
Which licences expire in the next 30 days with no newer licence on file?	CTE + correlated NOT EXISTS	7
Has this pharmacy been suspended before?	correlated EXISTS on audit_log	7
Which verified pharmacies are missing hours for some day?	CROSS JOIN days 0–6, LEFT JOIN hours, keep NULLs	6
Which pharmacies let more than 5 holds expire this week?	GROUP BY + HAVING + date functions	4
Which pharmacies are open right now?	VIEW with a non-equi join on hours	6–7
Which pharmacies stock nothing at all?	non-correlated subquery with NOT IN	6

-- wk 7: CTE + correlated NOT EXISTS
WITH expiring AS (
  SELECT c.pharmacy_id, c.license_number, c.expiry_date
  FROM credential c
  WHERE c.expiry_date BETWEEN CURRENT_DATE
                          AND CURRENT_DATE + INTERVAL '30 days'
)
SELECT p.name, e.license_number, e.expiry_date
FROM expiring e
JOIN pharmacy p ON p.id = e.pharmacy_id
WHERE NOT EXISTS (
  SELECT 1 FROM credential c2
  WHERE c2.pharmacy_id = e.pharmacy_id
    AND c2.expiry_date > e.expiry_date
);

-- wk 7: correlated EXISTS
SELECT p.id, p.name,
       EXISTS (SELECT 1 FROM audit_log a
               WHERE a.pharmacy_id = p.id
                 AND a.action = 'SUSPEND') AS was_suspended
FROM pharmacy p
WHERE p.status = 'VERIFIED';

-- wk 6-7: view with a non-equi join for "open now"
CREATE VIEW verified_open_pharmacy AS
SELECT p.id, p.name, p.phone, p.lat, p.lng, p.is_24x7
FROM pharmacy p
JOIN operating_hours oh
  ON  oh.pharmacy_id = p.id
  AND oh.day_of_week = EXTRACT(DOW FROM NOW())
  AND CURRENT_TIME BETWEEN oh.open_time AND oh.close_time
WHERE p.status = 'VERIFIED';

-- wk 6: cross join to find missing days
SELECT p.name, d.day
FROM pharmacy p
CROSS JOIN generate_series(0, 6) AS d(day)
LEFT JOIN operating_hours oh
  ON oh.pharmacy_id = p.id AND oh.day_of_week = d.day
WHERE p.status = 'VERIFIED' AND oh.id IS NULL;

-- wk 4: GROUP BY + HAVING
SELECT s.pharmacy_id, COUNT(*) AS expired_holds
FROM hold h JOIN stock_item s ON s.id = h.stock_item_id
WHERE h.status = 'EXPIRED'
  AND h.expires_at >= DATE_TRUNC('week', NOW())
GROUP BY s.pharmacy_id
HAVING COUNT(*) > 5;

09 PostgreSQL Data Model CSA214 wk 1–2, 5, 8

Nine tables, normalised to BCNF. PK = primary key, FK = foreign key, UK = unique. The crow's foot end of a line is the "many" side.

9a. Entity relationships · use Full size to read every column

Mermaid source

erDiagram
  USER ||--o{ REFRESH_TOKEN : has
  USER ||--o{ PHARMACY : owns
  USER ||--o{ HOLD : places
  USER ||--o{ AUDIT_LOG : writes
  PHARMACY ||--o{ CREDENTIAL : submits
  PHARMACY ||--o{ OPERATING_HOURS : keeps
  PHARMACY ||--o{ STOCK_ITEM : stocks
  MEDICINE ||--o{ STOCK_ITEM : "listed as"
  STOCK_ITEM ||--o{ HOLD : "reserved by"
  PHARMACY ||--o{ AUDIT_LOG : "target of"

  USER {
    int id PK
    string name
    string email UK
    string password_hash
    string role "PATIENT PHARMACIST ADMIN"
    timestamp created_at
  }
  REFRESH_TOKEN {
    int id PK
    int user_id FK
    string token_hash UK
    timestamp expires_at
    timestamp revoked_at
  }
  PHARMACY {
    int id PK
    int owner_id FK
    string name
    string address
    string area
    decimal lat
    decimal lng
    string phone
    boolean is_24x7
    string status "PENDING VERIFIED REJECTED SUSPENDED"
  }
  CREDENTIAL {
    int id PK
    int pharmacy_id FK
    string license_number
    date issue_date
    date expiry_date
  }
  OPERATING_HOURS {
    int id PK
    int pharmacy_id FK
    int day_of_week "0 to 6"
    time open_time
    time close_time
  }
  MEDICINE {
    int id PK
    string name
    string composition
    string form
    boolean prescription_required
  }
  STOCK_ITEM {
    int id PK
    int pharmacy_id FK
    int medicine_id FK
    int quantity "CHECK at least 0"
    decimal price
    timestamp last_updated
    boolean mongo_synced
  }
  HOLD {
    int id PK
    int stock_item_id FK
    int patient_id FK
    string status "ACTIVE COLLECTED CANCELLED EXPIRED"
    timestamp created_at
    timestamp expires_at
  }
  AUDIT_LOG {
    int id PK
    int actor_id FK
    int pharmacy_id FK
    string action
    string reason
    timestamp created_at
  }

Form	Rule	Where Velora shows it
1NF	one value per column	opening hours are one row per day, never "Mon-Sat 9-9" in a text column
2NF	nothing depends on part of a composite key	stock_item has UNIQUE (pharmacy_id, medicine_id); quantity and price depend on both
3NF	no non-key column depends on another non-key column	licence details live in credential, so a renewal adds a row instead of editing pharmacy
BCNF	every determinant is a candidate key	composition and form live only in medicine, keyed by its id

// prisma/schema.prisma (excerpt)
model StockItem {
  id          Int      @id @default(autoincrement())
  pharmacy    Pharmacy @relation(fields: [pharmacyId], references: [id])
  pharmacyId  Int
  medicine    Medicine @relation(fields: [medicineId], references: [id])
  medicineId  Int
  quantity    Int                          // CHECK added in SQL migration
  price       Decimal  @db.Decimal(8, 2)
  lastUpdated DateTime @default(now())
  mongoSynced Boolean  @default(true)
  holds       Hold[]

  @@unique([pharmacyId, medicineId])
  @@index([medicineId, quantity])
}

enum HoldStatus { ACTIVE COLLECTED CANCELLED EXPIRED }

10 MongoDB Documents + Pipelines CSA214 wk 10–12

Two collections, both rebuilt from PostgreSQL. Medicine details are embedded because search always shows them together; pharmacies are referenced because one pharmacy appears in hundreds of documents.

10a. Two collections · embedded vs referenced

Mermaid source

flowchart LR
  subgraph AV["availability collection"]
    D2["_id: stock item 42<br/>pharmacyId: 3<br/>medicine: embedded<br/>  name, composition, form<br/>quantity: 4, price: 32<br/>available: true<br/>lastUpdated: date"]
  end
  subgraph PHC["pharmacies collection"]
    D1["_id: 3<br/>name, area, phone<br/>lat, lng, is24x7<br/>verified: true"]
  end
  D2 -->|"pharmacyId, joined by $lookup"| D1
  class D1,D2 db

10b. Where each field comes from · PostgreSQL to MongoDB

Mermaid source

flowchart LR
  subgraph PGS["PostgreSQL rows"]
    S1["stock_item 42<br/>quantity, price, last_updated"]
    S2["medicine 9<br/>name, composition, form"]
    S3["pharmacy 3<br/>name, lat, lng, status"]
  end
  subgraph MGS["MongoDB documents"]
    A1["availability 42<br/>quantity, price, lastUpdated,<br/>available = quantity > 0,<br/>medicine: copy of medicine 9"]
    A2["pharmacies 3<br/>verified = status is VERIFIED"]
  end
  S1 --> A1
  S2 -->|"embedded copy"| A1
  S3 --> A2
  class S1,S2,S3 db
  class A1,A2 db

10c. Search pipeline · five stages, left to right

Mermaid source

flowchart LR
  S1["$match<br/>medicine.name matches X<br/>available is true"] --> S2["$lookup<br/>from pharmacies<br/>on pharmacyId"]
  S2 --> S3["$match<br/>pharmacy.verified true"]
  S3 --> S4["$sort<br/>lastUpdated desc"]
  S4 --> S5["$limit<br/>50"]
  S5 --> OUT(["ids to PostgreSQL<br/>open-now + distance"])
  class S1,S2,S3,S4,S5 db
  class OUT good

10d. Admin report pipeline · most often out of stock

Mermaid source

flowchart LR
  G1["$match<br/>available false"] --> G2["$group<br/>by medicine.name<br/>count pharmacies"]
  G2 --> G3["$sort<br/>count desc"]
  G3 --> G4["$limit 10"]
  G4 --> OUT(["admin sees the 10 medicines<br/>most often out of stock"])
  class G1,G2,G3,G4 db
  class OUT good

Choice	Embed or reference	Reason
medicine inside availability	embed	always read together, medicine rarely changes
pharmacy inside availability	reference	one pharmacy, hundreds of docs; verified status changes
holds	not in MongoDB	holds need transactions; PostgreSQL only

// SearchService, step 1
db.availability.aggregate([
  { $match:  { 'medicine.name': { $regex: q, $options: 'i' },
               available: true } },
  { $lookup: { from: 'pharmacies', localField: 'pharmacyId',
               foreignField: '_id', as: 'pharmacy' } },
  { $match:  { 'pharmacy.verified': true } },
  { $sort:   { lastUpdated: -1 } },
  { $limit:  50 },
]);

11 Indexing + Query Optimisation CSA214 wk 9

Seed enough rows that a full scan is slow, measure, add an index, measure again. The before/after timings go into the project report.

11a. Measure, index, measure · EXPLAIN ANALYZE

Mermaid source

flowchart TB
  A["Seed 500 pharmacies<br/>and 20,000 stock rows"] --> B["EXPLAIN ANALYZE<br/>search and compliance SQL"]
  B --> C{"Seq Scan on<br/>a big table?"}
  C -->|"yes"| D["add a B-Tree index<br/>with Prisma @@index<br/>then prisma migrate dev"]
  D -->|"measure again"| B
  C -->|"no, Index Scan"| E["record before and after<br/>timings in the report"]
  class A,B svc
  class C api
  class D db
  class E good

Index	Speeds up
stock_item (medicine_id, quantity)	who has medicine X in stock
pharmacy (status, lat, lng)	verified pharmacies inside the search box
operating_hours (pharmacy_id, day_of_week)	the open-now view
credential (pharmacy_id, expiry_date)	expiring-licence query
hold (stock_item_id, status, expires_at)	lazy expiry inside the hold transaction
availability { 'medicine.name': 1, available: 1 }	MongoDB $match stage

-- what to look for in the output
EXPLAIN ANALYZE SELECT ... ;
-- before: Seq Scan on stock_item  (actual time=0.02..41.8 rows=...)
-- after:  Index Scan using stock_item_medicine_id_quantity_idx
-- record "Execution Time" for each, 5 runs, take the median

12 Error Handling + Event Loop CSA213 wk 10–11

One custom error class, one async wrapper, and one error middleware registered last. Known errors get a clear message; unknown ones become a generic 500 with the stack logged on the server, never sent to the browser.

12a. Where every error ends up · errorHandler

Mermaid source

flowchart TB
  A["controller wrapped<br/>in asyncHandler"] --> B{"what failed?"}
  B -->|"AppError thrown on purpose"| C["known error"]
  B -->|"Prisma P2002 unique clash"| D["AppError 409"]
  B -->|"Prisma P2025 not found"| E["AppError 404"]
  B -->|"anything else"| F["unknown error"]
  C --> H["errorHandler<br/>registered last"]
  D --> H
  E --> H
  F --> H
  H --> I{"instance of<br/>AppError?"}
  I -->|"yes"| J["send its status<br/>and message"]
  I -->|"no"| K["log the stack<br/>send 500 Something went wrong"]
  class A,B,I api
  class C,D,E,H svc
  class F,K bad
  class J good

12b. Why blocking code hurts · one main thread for every request

Mermaid source

flowchart TB
  R1(["request 1<br/>POST /auth/login"]) --> MAIN["main thread<br/>runs your JS, one thing at a time"]
  R2(["request 2<br/>GET /search"]) --> MAIN
  MAIN -->|"await bcrypt.compare"| POOL["libuv thread pool<br/>hashing runs here"]
  MAIN -->|"await prisma query"| DBW["database socket<br/>waits for PostgreSQL"]
  POOL -->|"done"| Q["callback queue"]
  DBW -->|"rows back"| Q
  Q -->|"event loop picks next"| MAIN
  BAD["bcrypt.hashSync or<br/>a big for-loop"] -.->|"blocks the main thread,<br/>every request waits"| MAIN
  class R1,R2 client
  class MAIN api
  class POOL,DBW,Q svc
  class BAD bad

// utils/AppError.ts
export class AppError extends Error {
  constructor(public status: number, message: string,
              public details?: unknown) {
    super(message);
  }
}

// utils/asyncHandler.ts
export const asyncHandler = (fn) => (req, res, next) =>
  Promise.resolve(fn(req, res, next)).catch(next);

// error body the frontend always receives
{ "error": { "status": 409, "message": "Just missed it" } }

Use bcrypt's async API, never hashSync.
Use fs.promises in the seed script; never readFileSync inside a request.
Paginate every list endpoint with Prisma take and skip (default 20, max 50).
Let SQL filter and sort; never load a whole table and loop over it in a handler.
Never await inside a loop when the calls are independent; use Promise.all.

13 Frontend Architecture React, Sem 2

A client-rendered React 18 + TypeScript SPA built with Vite. React Router handles pages, TanStack Query holds everything that came from the API, AuthContext with useReducer holds the logged-in user, and plain useState holds the rest.

13a. Route map · public, then guarded by role, admin and pharmacist chunks lazy-loaded

Mermaid source

flowchart TB
  ROOT["createBrowserRouter<br/>RootLayout: header, toasts, error boundary"]
  ROOT --> PUB["Public"]
  ROOT --> PR{"RequireAuth<br/>+ RequireRole"}
  PUB --> P1["/ search"]
  PUB --> P2["/pharmacy/:id"]
  PUB --> P3["/login, /register"]
  PR -->|"PATIENT"| PA["/holds"]
  PR -->|"PHARMACIST"| PHL["/pharmacist<br/>lazy loaded"]
  PHL --> PH1["onboarding"]
  PHL --> PH2["stock"]
  PHL --> PH3["hours"]
  PHL --> PH4["incoming holds"]
  PR -->|"ADMIN"| ADL["/admin<br/>lazy loaded"]
  ADL --> AD1["verifications"]
  ADL --> AD2["compliance"]
  ADL --> AD3["search index"]
  PR -->|"not logged in"| LG["redirect /login<br/>then back"]
  PR -->|"wrong role"| NO["403 page"]
  ROOT --> NF["* 404 page"]
  class ROOT,PUB,P1,P2,P3,PA,PHL,PH1,PH2,PH3,PH4,ADL,AD1,AD2,AD3 client
  class PR api
  class LG,NO,NF bad

13b. Search page component tree · every query state has its own UI

Mermaid source

flowchart TB
  SP["SearchPage<br/>reads ?medicine and ?area from the URL"] --> SB["SearchBar<br/>useDebounce 300 ms"]
  SP --> LOC["LocationPicker<br/>useGeolocation, area dropdown fallback"]
  SP --> RL["ResultsList<br/>useSearch query"]
  RL --> ST{"query state"}
  ST -->|"loading"| SK["ResultSkeleton x 3"]
  ST -->|"error"| ER["ErrorState + Retry"]
  ST -->|"empty"| EM["EmptyState<br/>+ AlternativesList"]
  ST -->|"data"| RC["ResultCard x n"]
  RC --> B1["StockBadge<br/>CONFIRMED or STALE"]
  RC --> B2["OpenNowTag"]
  RC --> B3["DistanceText"]
  RC --> B4["HoldButton<br/>usePlaceHold mutation"]
  RC --> B5["DirectionsLink"]
  class SP,SB,LOC,RL,RC,B1,B2,B3,B4,B5,SK,EM client
  class ST api
  class ER bad

13c. How data moves · component, hook, cache, API client

Mermaid source

flowchart LR
  C["component<br/>ResultsList"] --> H["custom hook<br/>useSearch(medicine, lat, lng)"]
  H --> Q["TanStack Query<br/>key: search, medicine, lat, lng<br/>staleTime 30 s, refetch 60 s,<br/>refetch on window focus"]
  Q -->|"cache miss or stale"| API["api/client.ts<br/>fetch /api/v1/search"]
  API --> BE["Express backend"]
  BE --> API
  API -->|"ApiError on non-2xx"| Q
  Q -->|"data, isLoading, error"| C
  M["HoldButton"] --> MU["usePlaceHold<br/>useMutation"]
  MU --> API
  MU -->|"onSuccess"| INV["invalidate search<br/>and holds me"]
  INV --> Q
  class C,M client
  class H,MU,Q,INV svc
  class API client
  class BE api

13d. Silent token refresh · two requests fail, only one refresh runs

Mermaid source

sequenceDiagram
  autonumber
  participant C1 as Component A
  participant C2 as Component B
  participant AC as api client
  participant BE as Express
  C1->>AC: GET /holds/me
  C2->>AC: GET /pharmacies/mine
  AC->>BE: both requests with the old access token
  BE-->>AC: 401 and 401
  Note right of AC: only one refresh runs, the second waits on the same promise
  AC->>BE: POST /auth/refresh with cookie
  alt refresh ok
    BE-->>AC: new access token
    Note right of AC: AuthContext dispatch TOKEN_REFRESHED
    AC->>BE: retry both requests once
    BE-->>AC: 200 and 200
    AC-->>C1: data
    AC-->>C2: data
  else refresh fails
    BE-->>AC: 401
    Note right of AC: dispatch LOGOUT, clear query cache
    AC-->>C1: redirect to /login
  end

13e. Placing a hold from the UI · button, mutation, countdown

Mermaid source

sequenceDiagram
  autonumber
  actor P as Patient
  participant UI as ResultCard
  participant HK as usePlaceHold
  participant BE as Express
  participant HP as MyHoldsPage
  P->>UI: tap Hold 1 unit
  UI->>HK: mutate stockItemId 42
  Note right of UI: button shows Holding and is disabled
  HK->>BE: POST /api/v1/holds
  alt 201
    BE-->>HK: hold with expiresAt
    HK-->>UI: success toast, open My holds
    UI->>HP: navigate to /holds
    Note right of HP: useCountdown(expiresAt) ticks every second
    Note right of HP: at 0, refetch, status shows EXPIRED
  else 409 just missed it
    BE-->>HK: 409
    HK-->>UI: toast "Just missed it", refetch results
  else 401 not logged in
    HK-->>UI: go to /login, come back to this result
  end

Concern	Choice	Detail
Build	Vite + React 18 + TypeScript	dev server proxies `/api` to the local API
Routing	React Router v6 `createBrowserRouter`	nested layouts, `RequireAuth` and `RequireRole` wrappers, `React.lazy` for dashboards
Server state	TanStack Query	search: stale 30 s, refetch every 60 s and on focus; mutations invalidate `['search']` and `['holds','me']`
Auth state	AuthContext + useReducer	actions LOGIN, TOKEN_REFRESHED, LOGOUT; access token in memory only
URL state	`useSearchParams`	`?medicine=&area=` so a search can be shared or reloaded
Forms	React Hook Form + Zod	same rules as the API; server 400 field errors mapped back onto inputs
Custom hooks	useAuth, useSearch, usePlaceHold, useGeolocation, useDebounce, useCountdown	components never call fetch directly
Styling	Tailwind CSS, mobile-first	44 px minimum tap targets, text contrast at least 4.5:1
Errors	route-level error boundary + toasts	ApiError status decides the message
Testing	Vitest + React Testing Library	RequireRole, the refresh logic in api/client.ts, useCountdown

Location comes from the browser's location prompt. If the patient says no, they pick their area from a list of seeded areas with known coordinates. "Directions" opens a Google Maps link with the pharmacy's coordinates, so no maps API key is needed. The companion Velora Frontend page has the full frontend breakdown.

14 API Reference CSA213 wk 1, 4

Every endpoint, prefixed with /api/v1. Lists accept ?page and ?limit.

Method	Path	Role	Does	Success	Main errors
POST	/auth/register	public	create PATIENT or PHARMACIST	201	400, 409
POST	/auth/login	public	access token + refresh cookie	200	400, 401
POST	/auth/refresh	cookie	new token pair	200	401
POST	/auth/logout	cookie	revoke token, clear cookie	204	—
GET	/auth/me	any logged in	current user	200	401
GET	/search	public	nearest open verified pharmacies with the medicine	200	400
GET	/search/medicines?q=	public	name suggestions while typing	200	—
GET	/search/areas	public	area list for the location fallback	200	—
GET	/pharmacies/:id/public	public	pharmacy detail, hours, stock	200	404
POST	/holds	PATIENT	hold 1 unit for 30 min	201	401, 409
GET	/holds/me	PATIENT	my holds	200	401
PATCH	/holds/:id/cancel	PATIENT	cancel, unit returned	200	403, 409
POST	/pharmacies	PHARMACIST	register pharmacy + licence, PENDING	201	400, 409
GET	/pharmacies/mine	PHARMACIST	my pharmacy and status	200	404
POST	/pharmacies/:id/credentials	PHARMACIST	upload a renewed licence	201	403
GET	/pharmacies/:id/stock	PHARMACIST	my stock list	200	403
POST	/pharmacies/:id/stock	PHARMACIST	add a medicine to stock	201	403, 409
PATCH	/pharmacies/:id/stock/:itemId	PHARMACIST	change quantity or price	200	403, 404
PUT	/pharmacies/:id/hours	PHARMACIST	replace the week's hours	200	400, 403
GET	/pharmacies/:id/holds	PHARMACIST	incoming active holds	200	403
PATCH	/pharmacies/:id/holds/:holdId/collect	PHARMACIST	mark picked up	200	403, 409
GET	/admin/pharmacies?status=	ADMIN	list by status	200	403
PATCH	/admin/pharmacies/:id/verify	ADMIN	approve	200	404, 409
PATCH	/admin/pharmacies/:id/reject	ADMIN	reject with a reason	200	400, 404
PATCH	/admin/pharmacies/:id/suspend	ADMIN	suspend with a reason	200	400, 404
PATCH	/admin/pharmacies/:id/reinstate	ADMIN	back to VERIFIED	200	404, 409
GET	/admin/compliance/expiring	ADMIN	licences expiring in 30 days	200	403
GET	/admin/compliance/missing-hours	ADMIN	days with no hours	200	403
GET	/admin/compliance/expired-holds	ADMIN	more than 5 expired this week	200	403
POST	/admin/search-index/rebuild	ADMIN	repair the MongoDB copy	200	403

15 Deployment CSA213 wk 12

The two platforms the course names. Anything that differs between laptop and production lives in .env files, never in code.

15a. From laptop to production · Vercel + Heroku

Mermaid source

flowchart TB
  DEV["Laptop<br/>Vite dev server proxies /api<br/>to localhost:4000"] -->|"git push main"| GH["GitHub"]
  GH --> VC["Vercel<br/>npm run build"]
  GH --> HK["Heroku<br/>npm run build, then<br/>prisma migrate deploy"]
  U(["Browser"]) --> VC
  VC -->|"vercel.json rewrite /api/*<br/>same origin, cookie stays first-party"| HK
  HK --> PG[("Managed PostgreSQL")]
  HK --> MG[("MongoDB Atlas")]
  class DEV,GH ext
  class U,VC client
  class HK api
  class PG,MG db

Variable	Where	Example
DATABASE_URL	API	managed PostgreSQL URL
MONGO_URL	API	Atlas connection string
JWT_ACCESS_SECRET	API	long random string
JWT_REFRESH_SECRET	API	a different long random string
ADMIN_EMAIL, ADMIN_PASSWORD	seed only	the one admin account
NODE_ENV	API	development or production
PORT	API	set by Heroku

// client/vercel.json: /api goes to Heroku, same origin
{
  "rewrites": [
    { "source": "/api/:path*",
      "destination": "https://velora-api.herokuapp.com/api/:path*" },
    { "source": "/(.*)", "destination": "/index.html" }
  ]
}

16 Build Plan suggested

One order that keeps something demo-able at every point, fitted around the mid-term (16 Oct), Diwali weeks (6–13 Nov) and AP Contest 2 (27 Nov) from the handbook. Move the dates to fit your own week.

16a. Timeline · backend and frontend run in parallel

Mermaid source

gantt
  title Suggested build order, Oct to Nov 2026
  dateFormat YYYY-MM-DD
  axisFormat %d %b
  todayMarker off
  section Backend
  Schema migrations and seed      :b1, 2026-09-29, 7d
  Auth and RBAC                   :b2, after b1, 7d
  Search Postgres and Mongo       :b3, after b2, 10d
  Hold transaction and tests      :b4, after b3, 7d
  Pharmacist and admin APIs       :b5, after b4, 10d
  Compliance SQL and indexing     :b6, after b5, 7d
  section Frontend
  Router layouts and auth pages   :f1, 2026-10-06, 7d
  Search and results UI           :f2, after f1, 10d
  Holds and countdown             :f3, after f2, 7d
  Pharmacist and admin dashboards :f4, after f3, 12d
  section Ship
  Deploy and EXPLAIN report       :s1, 2026-11-20, 8d
  AP contest 2                    :milestone, m2, 2026-11-27, 0d
  Mid term                        :milestone, m1, 2026-10-16, 0d

Milestone	Done when
M1 data in place	`prisma migrate dev` and `db seed` fill both databases
M2 login works	register, login, refresh, logout, and a 403 test for each role
M3 search works	a public search returns nearest open verified pharmacies with badges
M4 hold is safe	a test fires two holds at a quantity-1 item and exactly one succeeds
M5 dashboards	pharmacist edits stock and hours; admin approves and sees compliance tables
M6 shipped	live on Vercel + Heroku, EXPLAIN ANALYZE before/after table in the report

17 Combined Architecture + Open Items listed, not hidden

17a. Everything on one page · solid = PostgreSQL, dashed = MongoDB copy

Mermaid source

flowchart TB
  U(["Browser"]) --> FE["React SPA on Vercel"]
  FE -->|"/api/v1 rewrite"| API["Express API on Heroku"]
  API --> AUTH["Auth + RBAC"]
  API --> SRCH["Search"]
  API --> HOLD["Holds"]
  API --> PHAR["Pharmacy"]
  API --> ADM["Admin"]
  AUTH --> PG[("PostgreSQL<br/>source of truth")]
  HOLD --> PG
  PHAR --> PG
  ADM --> PG
  SRCH --> PG
  SRCH -.->|"read"| MG[("MongoDB<br/>search copy")]
  HOLD -.->|"copy after commit"| MG
  PHAR -.->|"copy after commit"| MG
  ADM -.->|"rebuild"| MG
  class U,FE client
  class API api
  class AUTH,SRCH,HOLD,PHAR,ADM svc
  class PG,MG db

#	Open item	Current decision
O1	Where stock data comes from	pharmacists update it by hand; no pharmacy POS feed
O2	How "verified" is proven	admin-attested only; say so plainly in the report
O3	MongoDB copy falls behind	row flagged `mongo_synced = false` + admin rebuild endpoint
O4	Expiring old holds without a scheduler	released inside the next hold transaction for that item; `GET /holds/me` also shows past-expiry holds as EXPIRED
O5	Hours that cross midnight	stored as two rows, e.g. 20:00–23:59 and 00:00–02:00
O6	No rate limit on public search	not in the syllabus; mention it as a known limit
O7	Prescription medicines	shown with a "prescription needed" tag; the pharmacist checks it at pickup

Velora design spec v3 · 2026-09-28 · backend and database scope: CSA213 Advanced Programming + CSA214 Data Engineering, Semester 3 handbook, AY 2026-27 · Node.js + Express + TypeScript · Prisma ORM · PostgreSQL (source of truth) + MongoDB (search copy) · JWT + refresh tokens + RBAC · React 18 SPA · Vercel + Heroku · diagrams are pre-drawn from Mermaid; open "Mermaid source" under any diagram to copy it into Obsidian.