# Projects Module

Audience: AI coding agents first.

**Date:** 2026-05-30  
**Status:** Draft  
**Depends on:** `foundation-auth-rbac`, `customers-module`  
**Referenced by:** `tasks-board-engine`, `time-management`, `invoices-core`, `contractor-payouts`

---

## Overview

Projects are the central organizing unit. Tasks, time tracking, invoices, and expenses attach to projects. A project belongs to a customer and has a billing type that drives invoice automation.

---

## Data Model

```sql
projects (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  customer_id UUID,             -- nullable (internal projects with no customer)
  name TEXT NOT NULL,
  description TEXT,
  status TEXT DEFAULT 'active', -- 'active' | 'on_hold' | 'completed' | 'archived'
  billing_type TEXT NOT NULL,   -- 'fixed' | 'hourly' | 'retainer'
  billing_config JSONB,         -- type-specific config (see below)
  currency TEXT DEFAULT 'ILS',
  start_date DATE,
  end_date DATE,
  created_by UUID NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
)

project_members (
  project_id UUID NOT NULL,
  user_id UUID NOT NULL,
  tenant_id UUID NOT NULL,
  role TEXT DEFAULT 'member',   -- 'owner' | 'member' | 'viewer'
  hourly_rate NUMERIC(10,2),   -- contractor-specific override
  PRIMARY KEY (project_id, user_id)
)
```

### `billing_config` JSONB shapes

**Fixed price:**
```json
{ "total_amount": 5000, "deposit_pct": 30 }
```

**Hourly:**
```json
{ "rate_per_hour": 150, "overtime_enabled": false, "overtime_threshold_hours": 8, "overtime_multiplier": 1.5 }
```

**Retainer:**
```json
{
  "monthly_amount": 3000,
  "monthly_hours_included": 20,
  "auto_invoice": true,
  "hour_bank_overflow_action": "invoice" | "carry_over"
}
```

Retainer `hour_bank_overflow_action`:
- `"invoice"` — when hours bank depleted, auto-generate invoice for excess hours (global default, overridable per project)
- `"carry_over"` — roll unused hours to next month

---

## Features

### Project list (`/projects`)

Cards or table (user-togglable, stored in `localStorage`).  
Card view: project name, customer, billing type badge, status, active tasks count, hours this month.  
Filter: by status, billing type, customer.  
Sort: by name, start date, updated date.  
Quick create: "+ New Project" → slide-in sheet form.

### Project detail (`/projects/:id`)

**Header:** Project name, customer link, status badge, billing type badge, actions (edit, archive).

**Summary bar:** Total tasks, Open tasks, Hours tracked, Invoice status.

**Tabs:**

**[Tab] Overview**
- Billing config summary (rate, included hours, etc.)
- Hour bank gauge (for retainers): used / included per month
- Total hours tracked
- Invoice summary: paid, outstanding
- Recent tasks (last 5)
- Team members list

**[Tab] Tasks**
- Embedded task board filtered to this project (same component as `/tasks`, pre-filtered)

**[Tab] Time**
- Time entries for this project (table: user, date, duration, task, description)
- Total hours this month / all time

**[Tab] Invoices**
- Invoices for this project
- "Create invoice" action (for fixed/hourly billing)

**[Tab] Files**
- Attachments from task correspondence + manually uploaded
- PDF viewer for invoices

**[Tab] Settings**
- Edit billing config
- Overtime toggle (hourly projects)
- Retainer settings (auto-invoice toggle, overflow action)
- Danger zone: archive / delete project

### Create / edit project

Slide-in sheet form (`Sheet` component):
- Name, customer (select), description
- Billing type (radio: Fixed / Hourly / Retainer)
- Type-specific fields appear dynamically (currency, rate, hours, etc.)
- Start/end date
- Add initial team members

---

## Overtime Billing

Applies to hourly projects with `overtime_enabled = true`.

When generating invoices for hourly projects:
- Hours up to `overtime_threshold_hours` per day billed at base rate
- Hours above threshold billed at `base_rate × overtime_multiplier`
- Global default on/off in `/settings/business`; overridable per project

---

## Retainer Hour Bank

Monthly ledger per retainer project:

```sql
retainer_months (
  id UUID PRIMARY KEY,
  project_id UUID NOT NULL,
  tenant_id UUID NOT NULL,
  month TEXT NOT NULL,            -- 'YYYY-MM'
  hours_included NUMERIC(6,2),
  hours_used NUMERIC(6,2) DEFAULT 0,
  hours_rolled_over NUMERIC(6,2) DEFAULT 0,
  invoice_triggered_at TIMESTAMPTZ,
  UNIQUE (project_id, month)
)
```

`hours_used` incremented by time tracking entries. When `hours_used >= hours_included`:
- Webhook `retainer.depleted` fires
- If `auto_invoice = true` + `overflow_action = 'invoice'`: enqueue invoice generation

---

## Permissions

| Action | Required permission |
|--------|-------------------|
| View projects | `projects:read` |
| Create / edit project | `projects:write` |
| Archive / delete | `projects:delete` |
| Manage project members | `projects:write` |

Project members only see projects they're assigned to (unless `projects:read` grants full visibility — configurable per role).

---

## API Endpoints

```
GET    /api/projects                      → list (paginated, filterable)
POST   /api/projects                      → create
GET    /api/projects/:id                  → detail + stats
PATCH  /api/projects/:id                  → update
DELETE /api/projects/:id                  → archive (soft)
GET    /api/projects/:id/members          → list members
POST   /api/projects/:id/members          → add member
DELETE /api/projects/:id/members/:uid     → remove member
GET    /api/projects/:id/hours            → time summary
GET    /api/projects/:id/retainer-months  → retainer ledger
```

### Current-month aggregate invariant

- `GET /api/projects/:id` and `GET /api/projects/:id/hours` MUST compute month start in UTC.
- Raw SQL TIMESTAMPTZ parameters MUST use ISO 8601 strings. NEVER interpolate JavaScript `Date` objects into Drizzle `sql` templates.
- Regression coverage MUST prove UTC month-start conversion returns `YYYY-MM-01T00:00:00.000Z`.

**Rationale:** Neon rejects JavaScript `Date.toString()` output passed through raw SQL parameters, causing authenticated project detail and hours requests to return HTTP 500.

---

## Architecture Decisions

| Decision | Choice | Reason |
|----------|--------|--------|
| Billing config | JSONB per project | Three types have very different fields; JSONB avoids sparse nullable columns |
| Retainer ledger | Separate `retainer_months` table | Time series; queried per-month for reporting and invoice triggers |
| Tasks in project | Embedded tasks component | Reuse tasks module rather than duplicate board |
| Internal projects | `customer_id` nullable | Some projects have no client (internal R&D, etc.) |