A multi-tenant Human Resource Management System (HRMS) and Payroll platform foundation built with React, Express, TypeScript, Tailwind CSS, Prisma, self-managed PostgreSQL, and Keycloak OpenID Connect.
PayPilot provides growing and enterprise organizations with a unified interface for workforce administration:
- Workforce & Department Directory: Centralized employee records, job profiles, and structural hierarchy.
- Attendance & Time Logs: Daily check-in tracking, on-site vs. remote workforce indicators, and shift durations.
- Leave Request Management: Time-off workflows with manager reviews and category balances.
- Payroll Cycles & Payslips: Batch period runs, gross-to-net salary reconciliations, and payslip distribution.
- Multi-Tenant Foundation: Database-enforced tenant isolation plus role-based access levels (
SUPER_ADMIN,ADMIN,HR_MANAGER,MANAGER,EMPLOYEE).
Current phase: Supabase has been replaced with independently managed PostgreSQL and Keycloak. Authentication uses Authorization Code + PKCE S256, the API verifies Keycloak JWTs through JWKS, and PostgreSQL RLS isolates tenants using transaction-local context. The employee directory is backed by live database records with a validated API and audit logging. Payroll registers can now be imported and validated into dedicated import tables; the employee directory, attendance, leave, payroll imports, and payroll calculation are backed by live APIs, while dashboard analytics remain illustrative.
| Layer | Technologies |
|---|---|
| Frontend | React 18, Vite, TypeScript, Tailwind CSS, React Router 6, Lucide React, Recharts, keycloak-js |
| Backend | Node.js, Express, TypeScript, Zod, jose, dotenv |
| Database & ORM | Self-managed PostgreSQL 16, Prisma ORM 5.x, native Row-Level Security |
| Identity provider | Keycloak 26.2.5 (Authorization Code + PKCE S256, JWKS) |
| Local infrastructure | Docker Compose (PostgreSQL, Keycloak, Prometheus, Grafana) |
| Workspace & Tooling | npm Workspaces, TypeScript (strict mode) |
paypilot/
├── apps/
│ ├── web/ # Frontend React SPA
│ │ ├── public/silent-check-sso.html
│ │ └── src/
│ │ ├── app/ # App entry & React Router setup
│ │ ├── components/ # Layout, auth guard, UI primitives
│ │ ├── contexts/ # AuthContext (Keycloak session in memory)
│ │ ├── pages/ # Navigable views
│ │ ├── lib/ # keycloak.ts, api.ts, utilities
│ │ └── main.tsx
│ │
│ └── api/ # Backend Express Service
│ └── src/
│ ├── config/ # Zod-validated environment config
│ ├── lib/ # Prisma singleton, tenant context, logger
│ ├── middleware/ # Keycloak JWT verification, errors, logging
│ ├── routes/ # API router & health endpoints
│ ├── types/ # Authenticated request/auth context types
│ └── server.ts # Express bootstrap & graceful shutdown
│
├── prisma/
│ ├── schema.prisma # Canonical schema (provider-neutral identity)
│ └── migrations/ # Active forward-only migrations
├── prisma/migrations-legacy-supabase/# Archived Supabase-era history (not applied)
├── docker/
│ ├── postgres/init/01-init.sh # Roles, databases, default privileges
│ ├── keycloak/realm/ # Realm/client/audience import
│ ├── prometheus/ # Scrape config, alert rules, rule tests
│ └── grafana/ # Provisioned datasource and dashboards
├── scripts/provision-dev-user.ts # Controlled Keycloak + PayPilot provisioning
├── docs/
│ ├── architecture.md
│ ├── authentication.md
│ ├── OBSERVABILITY.md # Metrics, dashboards, alerts, runbook
│ └── RLS-IMPLEMENTATION.md
├── docker-compose.yml
├── .env.example
└── package.json
- Node.js: v18 or later (tested on Node.js v24)
- npm: v9 or later
- Docker: with either the
docker composeplugin or the standalonedocker-composecommand - Ports:
5433(PostgreSQL),8080(Keycloak),4000(API),9090(Prometheus), and3000(Grafana) by default
Port 5433 is the default host port so the stack does not collide with a local PostgreSQL listening on 5432. Change POSTGRES_PORT if needed. Prometheus and Grafana publish on 127.0.0.1 only; change PROMETHEUS_PORT or GRAFANA_PORT if those ports are already in use.
npm installcp .env.example .envReplace every change-me value in .env with a strong, unique secret. The file is gitignored. At minimum these must be set:
POSTGRES_ADMIN_PASSWORDPAYPILOT_MIGRATOR_PASSWORDPAYPILOT_APP_PASSWORDKEYCLOAK_DB_PASSWORDKEYCLOAK_ADMIN_PASSWORDKEYCLOAK_DEV_USER_PASSWORD
Connection strings in .env must embed the same role credentials:
# Runtime: least-privileged role used by the API (no BYPASSRLS, does not own tables)
DATABASE_URL="postgresql://paypilot_app:<app-password>@localhost:5433/paypilot?schema=public"
# Migrations and controlled provisioning only
DIRECT_URL="postgresql://paypilot_migrator:<migrator-password>@localhost:5433/paypilot?schema=public"npm run infra:up
# equivalent: docker-compose up -d postgres keycloakinfra:up starts only PostgreSQL and Keycloak so a fresh clone does not need the metrics token yet. The full observability stack is started separately in section 10.2 after generating the token. Docker Compose validates the whole file, so .env must still define GRAFANA_ADMIN_PASSWORD (copy it from .env.example) even for this partial start.
Wait until the containers are healthy:
docker ps --filter name=paypilotThe PostgreSQL init script creates:
- database
paypilot, owned bypaypilot_migrator - database
keycloak, owned by thekeycloakrole - runtime role
paypilot_appwith connection and DML privileges only - no shared database credentials between PayPilot and Keycloak
npm run prisma:generate
npm run db:migrate
npm run db:statusdb:migrate runs prisma migrate deploy against DIRECT_URL. Do not use prisma db push; the RLS policies and grants are part of the migration history.
The provisioning script creates the Keycloak user when needed, then links that exact Keycloak subject to a PayPilot user. It never links accounts by email during authentication.
npm run provision:user -- \
--email admin@paypilot.local \
--org-name "PayPilot Demo" \
--role ADMINThe password for the created Keycloak user is KEYCLOAK_DEV_USER_PASSWORD from .env.
Seed the six demo departments (idempotent, requires the demo organization):
npm run seed:devOverride the organization with SEED_ORGANIZATION_SLUG. The script never creates employees or organizations.
npm run dev- Frontend: http://localhost:5173
- API: http://localhost:4000
- Keycloak: http://localhost:8080
Or run them separately:
npm run dev:web
npm run dev:apiSign in at http://localhost:5173/login with the provisioned email and development password.
| Variable | Purpose |
|---|---|
POSTGRES_PORT, POSTGRES_ADMIN_USER, POSTGRES_ADMIN_PASSWORD |
PostgreSQL container and bootstrap admin |
PAYPILOT_DB_NAME, PAYPILOT_MIGRATOR_USER, PAYPILOT_MIGRATOR_PASSWORD |
PayPilot database and table-owning migration role |
PAYPILOT_APP_USER, PAYPILOT_APP_PASSWORD |
Least-privileged runtime role |
KEYCLOAK_DB_NAME, KEYCLOAK_DB_USER, KEYCLOAK_DB_PASSWORD |
Separate Keycloak database credentials |
KEYCLOAK_PORT, KEYCLOAK_ADMIN_USER, KEYCLOAK_ADMIN_PASSWORD |
Keycloak bootstrap admin |
PORT, CORS_ORIGIN, NODE_ENV |
API runtime settings |
HOST |
API listen interface; 0.0.0.0 is required for Docker-hosted Prometheus |
METRICS_TOKEN_FILE |
Path to the /metrics bearer token; relative paths resolve from the repo root |
HOST_GID |
Host group id that owns the metrics token; Prometheus joins this group via group_add to read the Compose secret |
DATABASE_URL, DIRECT_URL |
Runtime and migration connection strings |
PAYROLL_IMPORT_STORAGE_DIR |
Optional private directory for raw payroll registers; defaults to <repo>/var/payroll-imports |
KEYCLOAK_URL, KEYCLOAK_REALM, KEYCLOAK_CLIENT_ID |
Keycloak server and public client |
KEYCLOAK_ISSUER, KEYCLOAK_AUDIENCE |
Exact JWT issuer and API audience |
KEYCLOAK_JWKS_URI |
Optional JWKS override; defaults to issuer /protocol/openid-connect/certs |
KEYCLOAK_DEV_USER_PASSWORD |
Development-only password used by provisioning |
VITE_API_URL, VITE_KEYCLOAK_URL, VITE_KEYCLOAK_REALM, VITE_KEYCLOAK_CLIENT_ID |
Frontend build-time configuration |
PROMETHEUS_PORT, GRAFANA_PORT |
Localhost-bound Prometheus and Grafana ports |
GRAFANA_ADMIN_USER, GRAFANA_ADMIN_PASSWORD |
Grafana admin login |
- The SPA calls
keycloak.login()with Authorization Code flow and PKCE S256. - Keycloak redirects back to
http://localhost:5173/with an authorization code. keycloak-jsexchanges the code and keeps the access token in memory.- The SPA calls
GET /api/auth/mewithAuthorization: Bearer <token>. - The API verifies signature,
RS256, issuer,paypilot-apiaudience,exp, andnbfagainst the realm JWKS. - The API resolves the exact
(issuer, subject)pair withpublic.paypilot_resolve_identity(text, text). - The API loads the user and organization inside a tenant-scoped transaction and updates
lastLoginAt. - Invalid, expired, tampered, or unprovisioned identities receive
401. Inactive users receive403.
Tokens are never written to localStorage or sessionStorage. The realm disables implicit and hybrid flows, and the public client has restricted redirect URIs and web origins. See docs/authentication.md.
npm run prisma:validate # validate schema
npm run prisma:generate # generate Prisma Client
npm run db:migrate # apply forward-only migrations via DIRECT_URL
npm run db:status # show migration status
npm run purge:payroll-imports # remove raw import files past 90-day retentionKey security properties:
- The API connects as
paypilot_app, which is not a table owner, is not a superuser, and does not haveBYPASSRLS. - RLS is enabled and forced on all tenant-owned tables.
withOrganizationContext()setsapp.current_organization_idtransaction-locally in the same Prisma interactive transaction as the query.- Missing or invalid tenant context returns no rows and rejects writes.
- The migration role cannot be assumed by the runtime role.
- Audit logs are append-only for the runtime role.
See docs/RLS-IMPLEMENTATION.md.
The active history begins at 20261014000000_baseline_schema. Supabase-era migrations are retained under prisma/migrations-legacy-supabase/ for historical reference but are not executed against native PostgreSQL. Existing databases that already applied those migrations should mark the baseline as applied once and then deploy the forward migrations:
npx prisma migrate resolve --applied 20261014000000_baseline_schema
npm run db:migratenpm run prisma:validate
npm run typecheck
npm run build
npm test
# Requires the Docker stack and DIRECT_URL.
# Runs the real-PostgreSQL RLS and identity-resolution suites.
npm run test:integrationRUN_DB_INTEGRATION=1 gates database integration tests. Without it, those suites are skipped while unit tests still run.
Prometheus and Grafana run in the local Docker Compose stack. The API runs on the host and exposes a token-protected GET /metrics endpoint.
Host API (HOST=0.0.0.0, PORT=4000)
GET /metrics -> Bearer token required
^
| host.docker.internal:4000
Prometheus (127.0.0.1:9090) -> 15s scrape, 15d retention
Grafana (127.0.0.1:3000) -> provisioned datasource + two dashboards
mkdir -p .secrets
chmod 700 .secrets
printf '%s' "$(openssl rand -hex 32)" > .secrets/paypilot_metrics_token
chmod 640 .secrets/paypilot_metrics_token.secrets/ is git-ignored. The API reads the token from METRICS_TOKEN_FILE; Prometheus reads the same file through a Compose secret at /run/secrets/paypilot_metrics_token. Set HOST_GID in .env to the token file's group (stat -c '%g' .secrets/paypilot_metrics_token on Linux, stat -f '%g' .secrets/paypilot_metrics_token on macOS) so Prometheus can read it without the token being world-readable. Rotating the token requires restarting the API; Prometheus picks up the new file on its next scrape.
The API must be running on the host for Prometheus to scrape it; start it with npm run dev:api (or npm run dev) before or after the containers.
Add GRAFANA_ADMIN_PASSWORD to .env if it is missing, then:
docker-compose up -d prometheus grafana- Prometheus: http://localhost:9090 (targets at
/targets, alerts at/alerts) - Grafana: http://localhost:3000 (login with
GRAFANA_ADMIN_USER/GRAFANA_ADMIN_PASSWORD)
These are the default ports; if you changed PROMETHEUS_PORT or GRAFANA_PORT, use those values instead. Both ports bind to 127.0.0.1. Grafana anonymous access and sign-up are disabled.
curl -i http://localhost:4000/metrics
# HTTP/1.1 401 Unauthorized
# WWW-Authenticate: Bearer
curl -s -H "Authorization: Bearer $(cat .secrets/paypilot_metrics_token)" \
http://localhost:4000/metrics | head
# prints metric lines beginning with # HELP paypilot_.../metrics is not proxied by Vite and never emits CORS headers.
Because host.docker.internal cannot reach a loopback-only listener, the API binds 0.0.0.0. On Linux, restrict port 4000 to loopback and the Docker network subnet:
sudo iptables -N PAYPILOT_API 2>/dev/null || true
sudo iptables -F PAYPILOT_API
sudo iptables -A PAYPILOT_API -s 127.0.0.0/8 -j ACCEPT
sudo iptables -A PAYPILOT_API -s 172.16.0.0/12 -j ACCEPT
sudo iptables -A PAYPILOT_API -j DROP
sudo iptables -I INPUT -p tcp --dport 4000 -j PAYPILOT_API172.16.0.0/12 covers Docker's default address pools, including the Compose network that Prometheus uses. If you changed Docker's default-address-pools, inspect the Compose network instead:
docker network inspect payroll_default --format '{{(index .IPAM.Config 0).Subnet}}'Replace payroll_default with the actual network name (<compose-project>_default).
These rules are session-only and disappear on reboot. Persist them with sudo netfilter-persistent save or your distribution's firewall tool. Binding HOST to the Docker bridge gateway address would avoid the firewall rule, but it makes the API unreachable at localhost:4000, so you would also have to point the Vite proxy (apps/web/vite.config.ts) and local health checks at that address. On macOS and Windows, Docker Desktop routes host.docker.internal without a host firewall change; this section does not apply.
Grafana provisions two dashboards from JSON in the PayPilot folder:
- PayPilot API Overview — target/DB health, request rate, 5xx ratio, latency, in-flight requests, process CPU/memory, event loop, DB probe, top routes.
- PayPilot Business Activity — attendance punches, leave requests and reviews, payroll imports.
Prometheus provisions five alerts: PayPilotApiDown, PayPilotHigh5xxRate, PayPilotHighP95Latency, PayPilotDatabaseDown, and PayPilotDatabaseProbeStale. There is no Alertmanager; firing alerts appear in the Prometheus UI and can be added to a Grafana panel.
Rule files are not hot-reloaded (the stack does not enable the lifecycle API). After editing docker/prometheus/alerts.yml, run docker-compose restart prometheus.
Dashboard JSON is the source of truth. UI edits are overwritten on restart. Export changes back to JSON. Changing histogram buckets requires updating dashboard and alert queries in the same change.
See docs/OBSERVABILITY.md for the metric catalog, label policy, alert thresholds, and troubleshooting.
Payroll runs are calculated from each active employee's base salary, prorated over
Monday–Friday working days, with approved unpaid leave and ABSENT/HALF_DAY
attendance reducing gross pay. Paid leave and PRESENT/REMOTE attendance count
fully. Deductions come from per-organization rules (RATE or FIXED) stored in
payroll_deduction_rules and seeded with defaults on first use.
Organizations whose currency is INR additionally receive employee-side
statutory deductions: PF (12% of basic pay capped at ₹15,000), ESI (0.75% when
gross is at or below ₹21,000), state professional tax, and monthly TDS projected
from annual base salary under the new income-tax regime. Rates and slabs are
effective-dated, editable from Payroll Settings, and seeded with FY 2025-26
values. Non-INR organizations keep the custom-rule behavior only.
Lifecycle: DRAFT → CALCULATING → CALCULATED → APPROVED → PAID, with VOID
available from APPROVED or PAID. Recalculation is allowed from DRAFT and
CALCULATED; APPROVED freezes amounts; PAID and VOID are terminal. Every
transition writes an audit log entry.
Roles: SUPER_ADMIN, ADMIN, and HR_MANAGER create, calculate, approve,
pay, void, and manage deduction rules and statutory settings. MANAGER reads
runs and payslips. EMPLOYEE sees only their own payslips through My
Payslips.
Output: structured payslips with earning and deduction line items, run totals, and CSV export. India new-regime TDS is calculated; PDF generation and old-regime tax planning are not implemented.
See docs/PAYROLL.md for the algorithm, API, and operational
details.
Attendance is live at /attendance. Employees clock in and out from the page;
HR can upsert records for any employee. Each record is one employee-day and
stores status, check-in/out instants, notes, and optional location/device
metadata.
- Office locations: HR defines one or more offices with latitude, longitude, and an allowed radius from the Locations panel on the Attendance page.
- Geofence enforcement: Off by default. When enabled, clock-in and clock-out require a browser location within an allowed office. Employees assigned to an office are checked against that office; unassigned employees may match any active office.
- Device awareness: The browser stores a device ID and label. The first punch from a device is flagged as a new device. Flagging never blocks a punch.
- Timezone: Work dates default to today in the organization timezone, and exported times use that timezone.
- Timesheet export: HR exports any date range; managers and employees export their own records. CSV columns cover employee, date, status, times, hours, office, geofence result, device flag, and notes. Exports are capped at 92 days and 50,000 rows.
- Privacy: Raw coordinates and device IDs are not returned by the attendance list; HR sees office and geofence summaries.
| Method | Endpoint | Description |
|---|---|---|
GET |
/api/health |
API status, uptime, and memory usage |
GET |
/api/health/db |
Live database connectivity check |
GET |
/api/ |
API version metadata banner |
| Method | Endpoint | Description |
|---|---|---|
GET |
/api/auth/me |
Authenticated user's profile, role, and organization |
GET |
/api/employees |
Paginated employee list with search, filters, and summary |
GET |
/api/employees/:id |
Single employee |
POST |
/api/employees |
Create an employee |
PATCH |
/api/employees/:id |
Update employee fields |
PATCH |
/api/employees/:id/status |
Change employee status |
GET |
/api/departments |
Departments for the current organization |
POST |
/api/payroll-imports |
Upload a text/csv payroll register with X-File-Name |
GET |
/api/payroll-imports |
Paginated import history with optional status |
GET |
/api/payroll-imports/:id |
Import batch detail and counts |
GET |
/api/payroll-imports/:id/records |
Paginated imported records with optional valid |
GET |
/api/payroll-imports/:id/findings |
Paginated findings with optional severity and ruleCode |
GET |
/api/payroll-imports/:id/exception-report |
Downloadable CSV exception report |
Employee and department endpoints require SUPER_ADMIN, ADMIN, or HR_MANAGER. Employee numbers are generated server-side (PP-1001, PP-1002, …). Employees are offboarded by changing status, never by hard delete. Payroll import endpoints use the same HR role restriction.
Upload one CSV per batch with exactly these columns:
employee_number,pay_period_start,pay_period_end,currency,gross_pay,total_deductions,net_pay
PP-1001,2026-10-01,2026-10-31,INR,75000.00,12500.00,62500.00- Dates are ISO
YYYY-MM-DD; currency is a three-letter uppercase code; money allows at most two decimal places. - Limits are 5 MB per request and 10,000 data rows. Files must use one currency and one pay period per batch.
- File-level problems (empty file, invalid header, CSV parse error, row limit) persist a
FAILEDbatch. Row-level problems keep the batchCOMPLETEDwith invalid rows and findings. - Rules include missing/invalid values, invalid dates and periods, unknown employees, negative amounts, arithmetic mismatches, duplicate employee periods, inconsistent currency/period, inactive employees, and periods before the join date. Findings carry a stable rule code, severity, row/field, explanation, and expected/actual values under ruleset
payroll-import-rules/v1. - The invariant is
gross_pay - total_deductions = net_pay, compared as decimal cents rather than floating point. - Employees are resolved only within the authenticated organization. Inactive employees are not rejected for status alone.
- Re-uploading identical bytes is rejected with
409 DUPLICATE_IMPORTwhen a completed batch with the same SHA-256 hash exists. Failed batches may be retried. - Raw files are stored privately outside the web root with
0700directories and0600files, retained for 90 days, then removed bynpm run purge:payroll-imports. Batch metadata, records, and findings remain. There is no raw-file download endpoint. - Imported records never write to
payroll_runsorpayslips; they are validation input, not finalized payroll.
The web app exposes Payroll Imports at /payroll/imports with upload, history, filters, detail, findings/records tabs, and the exception report download.
Authentication & tenant isolation:Complete — Keycloak OIDC, JWKS verification, provider-neutral identity mapping, native PostgreSQL RLS.Employee Directory CRUD:Complete — validated API, tenant-scoped service, RLS enforcement, audit logging, live UI with create/edit and status changes.Payroll Import & Integrity Validation:Complete — CSV register upload, deterministic findings, persisted batches/records, history/detail APIs, exception reports, and the Payroll Imports UI.Attendance System:Complete — optional geofence enforcement, multi-office locations, device flags, organization-timezone work dates, and CSV timesheet export.- Leave Approval Engine: Tiered manager approval workflows and holiday calendar calculations.
Payroll Calculation Engine:Complete — salary-plus-attendance proration, configurable flat-rate deductions, guarded run lifecycle, structured payslips, and CSV export. India statutory deductions (PF, ESI, professional tax, new-regime TDS) are effective-dated and configurable from Payroll Settings. PDF payslips, employer contributions, and old-regime tax planning remain future work.