data-model.md

Data Model

All tables use UUID primary keys generated by PostgreSQL's gen_random_uuid(). The Prisma schema lives at backend/prisma/schema.prisma.

Entity-Relationship Diagram

erDiagram
    clients ||--o{ engagements : "has many"
    engagements ||--o{ assignments : "has many"
    users ||--o{ assignments : "has many"
    users ||--o{ cost_rates : "has many"
    users ||--o{ timesheets : "has many"
    timesheets ||--o{ time_entries : "has many"
    assignments ||--o{ time_entries : "has many"

    clients {
        uuid id PK
        varchar name UK
        enum status
    }
    engagements {
        uuid id PK
        uuid client_id FK
        varchar code UK
        varchar description
        enum type
        decimal budget
        date start_date
        date end_date
    }
    users {
        uuid id PK
        varchar username UK
        varchar password
        enum role
        enum status
    }
    cost_rates {
        uuid id PK
        uuid user_id FK
        decimal hourly_rate
        date start_date
        date end_date
    }
    assignments {
        uuid id PK
        uuid user_id FK
        uuid engagement_id FK
        decimal billing_rate
        date start_date
        date end_date
    }
    timesheets {
        uuid id PK
        uuid user_id FK
        date period_start
        enum status
    }
    time_entries {
        uuid id PK
        uuid timesheet_id FK
        uuid assignment_id FK
        date date
        decimal actual_hours
        decimal billed_hours
        text description
    }

Loading

---

Tables

clients

Column	Type	Constraints
id	UUID	PK, default gen_random_uuid()
name	VARCHAR(255)	UNIQUE, NOT NULL
status	ClientStatus enum	NOT NULL. Values: active, inactive

engagements

Column	Type	Constraints
id	UUID	PK, default gen_random_uuid()
client_id	UUID	FK → clients.id, NOT NULL
code	VARCHAR(255)	UNIQUE, NOT NULL
description	VARCHAR(255)	NOT NULL
type	EngagementType enum	NOT NULL. Values: fixed-price, t&m
budget	DECIMAL(12,2)	NOT NULL
start_date	DATE	NOT NULL
end_date	DATE	NOT NULL

users

Column	Type	Constraints
id	UUID	PK, default gen_random_uuid()
username	VARCHAR(255)	UNIQUE, NOT NULL. Always stored lowercase
password	VARCHAR(255)	NOT NULL. Hashed with bcrypt (cost 12). Never returned by any API
role	UserRole enum	NOT NULL. Values: admin, employee
status	UserStatus enum	NOT NULL. Values: active, inactive. Only active users can log in

cost_rates

Column	Type	Constraints
id	UUID	PK, default gen_random_uuid()
user_id	UUID	FK → users.id, NOT NULL
hourly_rate	DECIMAL(12,2)	NOT NULL
start_date	DATE	NOT NULL
end_date	DATE	Nullable. NULL means valid indefinitely

assignments

Column	Type	Constraints
id	UUID	PK, default gen_random_uuid()
user_id	UUID	FK → users.id, NOT NULL
engagement_id	UUID	FK → engagements.id, NOT NULL
billing_rate	DECIMAL(12,2)	Nullable. Required if engagement type = t&m; NULL if fixed-price
start_date	DATE	NOT NULL. Must be ≥ engagement.start_date
end_date	DATE	NOT NULL. Must be ≤ engagement.end_date

Business rule: For the same (user_id, engagement_id) pair, assignment date ranges must be non-overlapping (disjoint).

timesheets

Column	Type	Constraints
id	UUID	PK, default gen_random_uuid()
user_id	UUID	FK → users.id, NOT NULL
period_start	DATE	NOT NULL. Always the 1st of a month (e.g. 2026-04-01)
status	TimesheetStatus enum	NOT NULL. Values: draft, submitted, approved

Unique constraint: (user_id, period_start) — one timesheet per user per month.

time_entries

Column	Type	Constraints
id	UUID	PK, default gen_random_uuid()
timesheet_id	UUID	FK → timesheets.id, NOT NULL
assignment_id	UUID	FK → assignments.id, NOT NULL
date	DATE	NOT NULL
actual_hours	DECIMAL(4,2)	NOT NULL. Worked hours
billed_hours	DECIMAL(4,2)	Nullable. Required if engagement = t&m; NULL if fixed-price
description	TEXT	NOT NULL

---

Enums

Enum	Values	Notes
ClientStatus	active, inactive
EngagementType	fixed-price, t&m	Prisma maps: fixed_price → fixed-price, t_and_m → t&m
UserRole	admin, employee
UserStatus	active, inactive
TimesheetStatus	draft, submitted, approved

---

Prisma Enum Mapping

The Prisma schema uses identifiers fixed_price and t_and_m (since & and - are not valid in Prisma enum variants). These are mapped to the database values fixed-price and t&m using @map():

enum EngagementType {
  fixed_price @map("fixed-price")
  t_and_m     @map("t&m")
}

The API uses the Prisma identifiers (fixed_price, t_and_m) in request and response payloads.

---

Seed Data

The seed script (backend/prisma/seed.ts) creates:

Entity	Count	Details
Users	3	admin (admin), john.doe (employee), jane.smith (employee)
Clients	3	Acme Corp (active), GlobalTech (active), StartupXYZ (inactive)
Engagements	5	Mix of fixed-price and T&M across the three clients
Cost rates	2	45 EUR/h for john.doe, 50 EUR/h for jane.smith (open-ended)
Assignments	5	Various user-engagement pairings
Timesheets	3	March 2026 (approved + submitted), April 2026 (draft)
Time entries	~95	15 working days in March for both employees, 2 entries in April