Database Setup

WorkForce uses PostgreSQL with Prisma ORM for data management.

Installing PostgreSQL

Windows

1. Download from postgresql.org
2. Run the installer
3. Set a password for the postgres user
4. Default port: 5432

Ubuntu/Debian

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo systemctl enable postgresql

Docker

docker run -d \
  --name workforce-db \
  -e POSTGRES_USER=workforce \
  -e POSTGRES_PASSWORD=your_password \
  -e POSTGRES_DB=workforce \
  -p 5432:5432 \
  postgres:16

Creating the Database

-- Connect as postgres superuser
CREATE USER workforce WITH PASSWORD 'your_secure_password';
CREATE DATABASE workforce OWNER workforce;
GRANT ALL PRIVILEGES ON DATABASE workforce TO workforce;

Connection String

Format:

postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=public

Examples:

# Local development
DATABASE_URL="postgresql://workforce:password@localhost:5432/workforce?schema=public"

# Remote server
DATABASE_URL="postgresql://workforce:password@db.example.com:5432/workforce?schema=public"

# With SSL
DATABASE_URL="postgresql://workforce:password@db.example.com:5432/workforce?schema=public&sslmode=require"

Running Migrations

# Apply all migrations (production)
npx prisma migrate deploy

# Create a new migration (development)
npx prisma migrate dev --name description_of_change

# Reset database (WARNING: destroys all data)
npx prisma migrate reset

Prisma Schema

The database schema is defined in prisma/schema.prisma. Key models include:

Model	Purpose
Tenant	Organization / company
Employee	User account with role
Department	Organizational unit
Shift	Work schedule definition
Attendance	Check-in/out records
Request	Employee requests (leave, etc.)
Approval	Approval records for requests
WorkflowConfig	Approval workflow definitions
WorkflowStage	Individual stages in a workflow
OfficeLocation	Geofence locations
Subscription	Tenant plan and billing

Prisma Studio

Browse and edit data visually:

npx prisma studio

Opens a web UI at http://localhost:5555.

Backups

Manual Backup

pg_dump -U workforce -d workforce -F c -f backup_$(date +%Y%m%d).dump

Restore

pg_restore -U workforce -d workforce -c backup_20240101.dump

Automated Backups

Set up a cron job (Linux) or Task Scheduler (Windows) to run pg_dump daily.