PayPilot HRMS & Payroll SaaS

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.

---

1. Project Overview

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. Business data mutations remain scoped to demo/sandbox states pending employee CRUD implementation.

---

2. Technology Stack

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)
Workspace & Tooling	npm Workspaces, TypeScript (strict mode)

---

3. Repository Architecture

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
├── scripts/provision-dev-user.ts     # Controlled Keycloak + PayPilot provisioning
├── docs/
│   ├── architecture.md
│   ├── authentication.md
│   └── RLS-IMPLEMENTATION.md
├── docker-compose.yml
├── .env.example
└── package.json

---

4. Prerequisites

• Node.js: v18 or later (tested on Node.js v24)
• npm: v9 or later
• Docker: with either the docker compose plugin or the standalone docker-compose command
• Ports: 5433 (PostgreSQL) and 8080 (Keycloak) 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.

---

5. Installation & Setup

5.1 Install dependencies

npm install

5.2 Configure environment

cp .env.example .env

Replace every change-me value in .env with a strong, unique secret. The file is gitignored. At minimum these must be set:

• POSTGRES_ADMIN_PASSWORD
• PAYPILOT_MIGRATOR_PASSWORD
• PAYPILOT_APP_PASSWORD
• KEYCLOAK_DB_PASSWORD
• KEYCLOAK_ADMIN_PASSWORD
• KEYCLOAK_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"

5.3 Start PostgreSQL and Keycloak

npm run infra:up
# equivalent: docker-compose up -d

Wait until both containers are healthy:

docker ps --filter name=paypilot

The PostgreSQL init script creates:

• database paypilot, owned by paypilot_migrator
• database keycloak, owned by the keycloak role
• runtime role paypilot_app with connection and DML privileges only
• no shared database credentials between PayPilot and Keycloak

5.4 Apply migrations and generate Prisma Client

npm run prisma:generate
npm run db:migrate
npm run db:status

db: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.

5.5 Provision a development user

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 ADMIN

The password for the created Keycloak user is KEYCLOAK_DEV_USER_PASSWORD from .env.

5.6 Run the applications

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:api

Sign in at http://localhost:5173/login with the provisioned email and development password.

---

6. Environment Variables

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
DATABASE_URL, DIRECT_URL	Runtime and migration connection strings
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

---

7. Authentication Flow

1. The SPA calls keycloak.login() with Authorization Code flow and PKCE S256.
2. Keycloak redirects back to http://localhost:5173/ with an authorization code.
3. keycloak-js exchanges the code and keeps the access token in memory.
4. The SPA calls GET /api/auth/me with Authorization: Bearer <token>.
5. The API verifies signature, RS256, issuer, paypilot-api audience, exp, and nbf against the realm JWKS.
6. The API resolves the exact (issuer, subject) pair with public.paypilot_resolve_identity(text, text).
7. The API loads the user and organization inside a tenant-scoped transaction and updates lastLoginAt.
8. Invalid, expired, tampered, or unprovisioned identities receive 401. Inactive users receive 403.

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.

---

8. Database & RLS Operations

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

Key security properties:

• The API connects as paypilot_app, which is not a table owner, is not a superuser, and does not have BYPASSRLS.
• RLS is enabled and forced on all tenant-owned tables.
• withOrganizationContext() sets app.current_organization_id transaction-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.

Migration history note

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:migrate

---

9. Verification Commands

npm 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:integration

RUN_DB_INTEGRATION=1 gates database integration tests. Without it, those suites are skipped while unit tests still run.

---

10. API Endpoints

Public

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

Protected (Bearer token required)

Method	Endpoint	Description
GET	/api/auth/me	Authenticated user's profile, role, and organization

---

11. Next Implementation Milestones

1. Authentication & tenant isolation: Complete — Keycloak OIDC, JWKS verification, provider-neutral identity mapping, native PostgreSQL RLS.
2. Employee Directory CRUD: Zod validation, profile avatars, and pagination.
3. Attendance System: Geo/device-aware clock-in punch engine and timesheet export.
4. Leave Approval Engine: Tiered manager approval workflows and holiday calendar calculations.
5. Payroll Calculation Engine: Localized tax brackets, statutory deductions, and automated PDF payslip generation.