Database Design

Databases

Key Decisions

1. Single PostgreSQL database for all tenants (row-level isolation via tenantId)
2. No HRMS data in MongoDB - MongoDB is exclusively for AI service
3. Prisma 5.x as ORM - Type-safe queries, automatic migrations
4. Redis for caching - Permissions, dashboard stats, sessions

Multi-Tenancy

• Every table has tenantId column (except Tenant itself)
• Every query must filter by tenantId
• Complete data isolation between tenants

Row-Level Security (RLS)

In addition to application-level tenantId filtering, PostgreSQL RLS policies provide database-level isolation as a safety net:

-- Enable RLS on all tenant-scoped tables
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;
ALTER TABLE departments ENABLE ROW LEVEL SECURITY;
ALTER TABLE time_off_requests ENABLE ROW LEVEL SECURITY;
-- ... repeat for all tables

-- Create policy for tenant isolation
CREATE POLICY tenant_isolation ON employees
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Set tenant context before queries (done by Prisma middleware)
SET app.tenant_id = 'uuid-of-current-tenant';

Benefits:

• Prevents data leaks even if application code has bugs
• Defense in depth (application + database layer)
• Audit-friendly for compliance

Implementation:

• Prisma middleware sets app.tenant_id from request context (via x-tenant-id header)
• RLS policies check tenant_id column against app.tenant_id
• System admin queries bypass RLS with SET ROLE or separate connection

See Database Schema for complete model definitions.

What Goes Where

PostgreSQL (HRMS)

• Tenants, users, roles, permissions
• Employees, departments
• Time-off requests and balances
• Documents metadata
• Team posts, goals, workflows
• Audit logs

MongoDB (AI Service Only)

• RAG document embeddings
• Chat sessions and history
• AI-generated summaries

Caching Strategy

Note: Sessions are stored in PostgreSQL via Auth.js PrismaAdapter (not Redis). See Phase 01 for auth implementation.

Don't cache: Real-time data, user-specific queries, sensitive data

Migration Rules

• Use Prisma Migrate for schema changes
• Test migrations on staging first
• Multi-step migrations for breaking changes
• Always have rollback plan

Schema details to be defined during implementation