Node.js + SQL Server Operations API

The REST API behind a field-operations mobile app: guards check in at their site (geofenced), report incidents from the field (offline-safe), supervisors get push notifications and emails for critical events, scheduled jobs chase forgotten shifts and send daily summaries, and monthly PDF reports are generated on demand, all on SQL Server, hostable on IIS or anywhere Node runs.

Clean-room portfolio version of the kind of API I maintain in production for a mobile app published on Google Play (Node.js + SQL Server on IIS, 20+ business modules). Written from scratch; data is fictional.

Highlights

Topic	What is implemented
SQL injection impossible by construction	A sql tagged template turns every ${value} into a typed parameter (@p0, @p1…), expands arrays for IN (…), and only accepts code-chosen fragments through raw().
SQL Server-aware parameter typing	ASCII strings are sent as VARCHAR, so comparing against VARCHAR columns does not force implicit conversions (which turn index seeks into scans). Fixed parameter lengths keep one cached plan per query.
One connection pool, transactions, slow-query log	Database wraps mssql: a shared pool, query / one / execute / queryMultiple / transaction, and a warning for queries over 1 s.
Migrations	Ordered .sql files with GO batches, applied once and recorded with a checksum, so editing an applied migration is detected.
Database-enforced rules	A filtered unique index allows only one open shift per guard, even under concurrency (returns 409). CHECK constraints, idempotency keys, a covering index and a stored procedure with two result sets.
Auth	bcrypt, a short-lived JWT plus an opaque refresh token stored hashed, rotated on every use and revocable. Timing-safe login (no user enumeration), rate limiting and roles.
Offline-safe writes	Idempotency-Key on incidents: a phone that retries after losing signal gets the original record back, never a duplicate.
Scheduled jobs (node-cron)	No overlap within a process, plus a distributed lock in SQL Server, so with several instances only one runs each job. Failures are contained and recorded, and there is a JOBS_DISABLED kill switch.
Push notifications	Firebase Cloud Messaging HTTP v1 with a service account. Tokens of uninstalled apps are cleaned up automatically. Without credentials, pushes are logged instead of sent.
Email	Nodemailer over SMTP with TLS. User text is HTML-escaped, and without SMTP settings the email is logged instead of sent.
PDF reports	PDFKit (pure JS, no headless browser on the server): KPI cards and incidents by category.
Logging	Winston: one file per module per day, an errors file, rotation, compression and retention. A request id is carried through AsyncLocalStorage into every log line and the X-Request-Id header. Request bodies are never logged.
Errors	Validation with Zod. SQL unique-key violations map to 409. Unknown errors return a generic 500, so no SQL or stack trace reaches the client.
IIS hosting	web.config for iisnode with a run.cjs shim that boots the ES-module build. node_modules, logs and src are hidden from the web.

Modules

POST /auth/login | /auth/refresh | /auth/logout
POST /devices                         register an FCM token
GET  /shifts/current
POST /shifts/check-in                 geofenced (radius grows with GPS accuracy, capped)
POST /shifts/check-out
POST /incidents                       Idempotency-Key required; high severity -> push + email
GET  /incidents?siteId&from&to        supervisors
GET  /reports/sites/:id/monthly.pdf?month=YYYY-MM   supervisors
GET  /health                          includes database status

Each module is a router registered in one place (src/app.ts). Its requests are logged to logs/<module>/, and existing URLs never change, because installed mobile apps depend on them.

Run it

docker compose up -d                    # SQL Server 2022 for local development
cp .env.example .env                    # DB_SERVER=localhost DB_USER=sa DB_PASSWORD=Dev!Strong_Passw0rd ...
npm install
npm run dev                             # applies migrations on start, http://localhost:3000

Demo users (password demo-password-123): guard1, guard2, super1, admin.

Tests

npm test                     # 21 unit tests: SQL template & typing, migrations, geofence, scheduler
                             # (overlap, distributed lock, failures), request-id logging, FCM payloads, PDF
npm run test:integration     # 8 end-to-end tests against a real SQL Server (run in CI via Docker):
                             # migrations, auth + token rotation, injection attempts, geofence,
                             # one-open-shift index, reminder job + lock, idempotent incidents,
                             # supervisor alerts, PDF report, no SQL leaks

License

MIT