clienthub/.planning/phases/04-progetti-multi-project/04-RESEARCH.md

# Phase 04: Progetti — Multi-Project per Cliente - Research

**Researched:** 2026-05-21
**Domain:** Data model refactoring + multi-tier architecture (DB schema migration, API routing, client/admin UI)
**Confidence:** HIGH

## Summary

Phase 04 transforms ClientHub from a single-project-per-client model to a multi-project model. This is a **breaking schema migration** where the `projects` table becomes the primary work container, and 6 existing tables (`phases`, `payments`, `quote_items`, `time_entries`, `documents`, `notes`) move their FK from `client_id` to `project_id`. The `clients` table gains a `slug` field and loses denormalized fields that move to the project level.

All existing data is test data — hard migration (drop/recreate tables) is acceptable and planned.

**Key insight:** This is NOT a typical multi-tenancy refactor. It's a structural deepening: clients now own projects, and projects own the work. The middleware routing pattern (`/c/[slug-or-token]`) stays the same, but resolves at the client level and then queries to find projects.

**Primary recommendation:** Use vertical slice approach (Wave 0 schema, Wave 1 core routing/queries, Wave 2 admin UI, Wave 3 client UI + analytics). All 5 locked architectural decisions are already finalized in CONTEXT.md — implement them as-is, no discretion needed.

---

## User Constraints (from CONTEXT.md)

### Locked Decisions

**Schema & Data Model**
- D-01: New `projects` table with `id`, `client_id` FK, `name` (brand/project name), `archived`, `created_at`. No direct `accepted_total` — denormalized from `quote_items` per project.
- D-02: Six tables move FK from `client_id` → `project_id`: `phases`, `payments`, `quote_items`, `time_entries`, `documents`, `notes`. `comments` stays polymorphic on `entity_id` (unchanged).
- D-03: `clients` table loses project-scoped fields. Retains: `id`, `name`, `brand_name`, `token`, `slug` (new), `archived`, `created_at`. `accepted_total` moves to `projects`.
- D-04: `slug` field added to `clients` — optional, unique, URL-safe (e.g., `mario-rossi`). Middleware tries slug first, falls back to token.
- D-05: `projects.accepted_total` denormalized (text, nullable), admin sets manually in project Preventivo tab.

**Link & Access**
- D-06: Token stays on `clients` for auth middleware. Middleware checks slug first (DB lookup), then token (existing pattern). Both grant access.
- D-07: Slug is set in `/admin/clients/[id]/edit` (new form field, optional, with link preview).
- D-08: Route `/c/[token-or-slug]` unchanged in path — middleware resolves both.

**Client Dashboard**
- D-09: 1 project → direct view (no selector).
- D-10: 2+ projects → tabs with brand names (shadcn Tabs, already in codebase).
- D-11: Project view identical to current client dashboard but scoped to one project.

**Admin — Client List View**
- D-12: `/admin/clients` shows client name + project brands as secondary text (e.g., "Mario Rossi" / "Brand Blu | Brand Verde"). LTV = sum of all project `accepted_total`.
- D-13: Clicking a client opens `/admin/clients/[id]` showing project cards/rows (not workspace directly).

**Admin — Project List & Workspace**
- D-14: New `/admin/projects` (NavBar link) — all projects with: Name, Parent Client, Value (accepted_total), Acconto, Saldo, Timer, €/h.
- D-15: Timer in projects list shows play/stop for each project. Only one timer active at a time (scoped to project now).
- D-16: `/admin/projects/[id]` workspace identical to current `/admin/clients/[id]` but project-level: Panoramica, Fasi, Documenti, Pagamenti, Note, Preventivo, Timer, Commenti tabs.

**Project Creation**
- D-17: Create project from: (1) `/admin/clients/[id]` with "+ Nuovo Progetto" button, or (2) `/admin/projects` with "+ Nuovo Progetto" → select client.
- D-18: Creation form: Project Name (brand) + Client (if from list). No other fields at creation time.

**Timer & Analytics**
- D-19: `time_entries.client_id` → `time_entries.project_id`. Timer now per-project.
- D-20: Analytics profittabilità in project Timer tab: total hours, accepted_total, €/h real (accepted ÷ hours), target rate × hours (ideal cost), delta (gain/loss vs target).
- D-21: `target_hourly_rate` is global (e.g., 50€/h) stored in `settings` table or env var. New "Impostazioni" page in NavBar for admin to set.
- D-22: Statistiche page shows aggregated profitability for all projects + breakdown per client.

### Claude's Discretion

- **Settings table structure:** key-value (simplest) vs. dedicated columns. Recommendation: `settings(key text PK, value text)`.
- **Tab order in project detail:** Follow current client detail order.
- **Project card style in client detail:** Reuse existing UI patterns.

### Deferred Ideas (OUT OF SCOPE)

- Automatic invoice generation per project.
- PDF export for quotes.
- AI Onboarding (Phase 5 — requires this phase as prerequisite).
- Email notifications when phases change.

---

## Phase Requirements

| ID | Description | Research Support |
|----|-------------|------------------|
| PROJ-01 | Every client can have N independent projects; each project has its own workspace (phases, payments, quote, timer) accessible from /admin/projects/[id] | Schema migration (D-01, D-02), Query refactor (getProjectFullDetail), Admin workspace pages (/admin/projects/[id]) |
| PROJ-02 | Client dashboard shows tabs for 2+ projects; 1 project shows direct workspace without selector | Client view refactor for multi-project detection, Tabs UI pattern (shadcn already available), Client page routing logic |
| PROJ-03 | /admin/projects lists all projects with €/h calculated and timer play/stop; /admin/projects/[id] is the project workspace | New project list page and detail page templates (clone from ClientRow/Client detail), Timer refactor (client_id → project_id) |
| PROJ-04 | Client link supports custom slug (/c/mario-rossi) with fallback to token; slug settable from /admin/clients/[id]/edit | Middleware slug resolution (internal API route for DB lookup), Clients edit form, Link preview component |
| PROJ-05 | Profitability analytics per project: hours tracked, accepted_total, €/h real vs target_hourly_rate global | Analytics card in Timer tab (formula: accepted ÷ hours = €/h real, target × hours = ideal cost, delta = profit/loss), Settings table for global target rate |

---

## Architectural Responsibility Map

| Capability | Primary Tier | Secondary Tier | Rationale |
|------------|-------------|----------------|-----------|
| Project CRUD (create, read, update, archive) | API / Backend | Admin Browser | Server actions + DB operations live in API tier; admin calls via form actions |
| Timer start/stop for projects | API / Backend | Admin Browser | Timer logic (duration calculation, active session tracking) is backend; UI shows state via server actions |
| Multi-project dashboard routing | Frontend Server (SSR) | Browser | Server chooses 1-project direct view vs. 2+ project tabs; browser renders tabs (Tabs component is client-side) |
| Slug lookup & resolution | API / Backend + Edge Middleware | — | Middleware calls internal API route to resolve slug → client_id; API accesses DB (can't do direct queries in Edge runtime) |
| Profitability analytics calculation | API / Backend | Admin Browser | Formula applied server-side (accepted_total ÷ duration_seconds), displayed in admin workspace |
| Client project visibility | API / Backend | Browser | Client API (`/c/[token]/*`) queries projects belonging to the resolved client; client browser renders what API returns |

---

## Standard Stack

### Core
| Library | Version | Purpose | Why Standard |
|---------|---------|---------|--------------|
| Next.js App Router | 16 | Meta-framework for API routes + SSR + auth middleware | Established in project; Edge middleware pattern for token/slug resolution |
| Neon Postgres | current | Primary database | Established; supports both pooled (API routes) and direct (drizzle-kit) connections |
| Drizzle ORM | current | Type-safe query builder + schema management | Established; `drizzle-kit push` handles migrations without manual SQL |
| Auth.js v4 | current | Admin session authentication | Established; `/admin/*` routes use Auth.js session guard |
| Tailwind v4 | current | Styling | Established; Tailwind scanning configured to include project source |
| shadcn/ui | current | UI components library | Established; Tabs component already used for admin workspaces |
| Zod | current | Input validation | Established for form validation |
| nanoid | current | Random ID generation | Established; used for all entity IDs |

### Supporting
| Library | Version | Purpose | When to Use |
|---------|---------|---------|-------------|
| react-hook-form | (check package.json) | Form state management | Forms in admin (create project, edit client slug) |
| @radix-ui/tabs | (check package.json) | Underlying Tabs component | shadcn Tabs wrapper — already available |

### Alternatives Considered
| Instead of | Could Use | Tradeoff |
|------------|-----------|----------|
| `settings` key-value table | Hardcode target_hourly_rate as env var | Env var: simpler, no DB call. Table: more flexible, admin can change via UI. Recommendation: start with table for flexibility. |
| Slug as optional field in clients | Always-require slug, generate from name | Optional is better: gradual migration, existing clients keep token links, new clients can have slug. |
| Clone workspace from client detail | Build project detail from scratch | Cloning is faster: tabs, layout, queries already proven. Reduces bugs. |

**Installation:**
```bash
# No new packages needed — all standard stack already installed
npm ls next neon drizzle-orm next-auth tailwindcss shadcn-ui zod nanoid
```

**Version verification:**
All versions are in the existing `package.json` and `drizzle.config.ts`. No new dependencies required for Phase 04 schema/routing. Any new UI components (if needed) are installed via `npx shadcn-ui@latest add [component]`.

---

## Architecture Patterns

### System Architecture Diagram

```
┌─────────────────────────────────────────────────────────────────────┐
│                         CLIENT BROWSER                               │
│  ┌──────────────┐  ┌──────────────────────────────────────────────┐ │
│  │  /c/[slug]   │  │  Client Dashboard (multi-project)             │ │
│  │  or /c/[tok] │──│  ├─ 1 project: direct view                   │ │
│  │              │  │  └─ 2+: tabs per brand name                  │ │
│  └──────────────┘  │     ├─ Phases, Tasks, Deliverables           │ │
│                    │     ├─ Payments & Status                      │ │
│                    │     └─ Documents & Notes                       │ │
│                    └──────────────────────────────────────────────┘ │
│  ┌──────────────┐  ┌──────────────────────────────────────────────┐ │
│  │ /admin/*     │  │ Admin Area (multi-workspace)                 │ │
│  │ (Auth.js)    │──│  ├─ /admin/clients: list + LTV               │ │
│  │              │  │  ├─ /admin/clients/[id]: projects cards       │ │
│  └──────────────┘  │  ├─ /admin/projects: all projects + timer    │ │
│                    │  ├─ /admin/projects/[id]: workspace (tabs)   │ │
│                    │  └─ /admin/impostazioni: target_hourly_rate  │ │
│                    └──────────────────────────────────────────────┘ │
└──────────────────────────────────────────────────────────────────────┘
                              ↓
                  ┌──────────────────────────┐
                  │   Edge Middleware        │
                  │ (proxy.ts / middleware) │
                  │ ├─ Resolve /c/[slug]     │
                  │ │  → call /api/internal/ │
                  │ │    validate-slug       │
                  │ ├─ Fallback /c/[token]   │
                  │ │  → existing pattern    │
                  │ └─ Admin session check   │
                  └──────────────────────────┘
                              ↓
          ┌──────────────────────────────────────────┐
          │  Next.js API Routes (Node.js runtime)   │
          │  ├─ /api/internal/validate-token        │
          │  ├─ /api/internal/validate-slug (NEW)  │
          │  ├─ /api/auth/* (NextAuth)              │
          │  └─ Server Actions (form submissions)   │
          └──────────────────────────────────────────┘
                              ↓
          ┌──────────────────────────────────────────┐
          │  Drizzle ORM Query Layer                │
          │  ├─ getAllProjectsWithPayments (NEW)     │
          │  ├─ getProjectFullDetail (NEW)           │
          │  ├─ getClientWithProjects (NEW)          │
          │  ├─ slugToClientId (NEW, for resolution) │
          │  └─ timer-actions refactored             │
          │     (client_id → project_id)             │
          └──────────────────────────────────────────┘
                              ↓
          ┌──────────────────────────────────────────┐
          │  Neon Postgres Database                 │
          │  ├─ clients (+ slug field NEW)           │
          │  ├─ projects (NEW, client_id FK)         │
          │  ├─ phases (FK: project_id instead)      │
          │  ├─ tasks (unchanged, phase_id FK)       │
          │  ├─ payments (FK: project_id instead)    │
          │  ├─ quote_items (FK: project_id instead) │
          │  ├─ time_entries (FK: project_id NEW)    │
          │  ├─ documents (FK: project_id instead)   │
          │  ├─ notes (FK: project_id instead)       │
          │  ├─ settings (NEW, key-value table)      │
          │  ├─ comments (entity_id, unchanged)      │
          │  ├─ deliverables (unchanged)             │
          │  └─ service_catalog (unchanged)          │
          └──────────────────────────────────────────┘
```

**Data flow for client dashboard:**
1. Browser requests `/c/mario-rossi`
2. Middleware intercepts, calls `/api/internal/validate-slug?slug=mario-rossi`
3. API resolves slug → client_id via `slugToClientId()`
4. Server component calls `getClientWithProjects(client_id)`
5. If 1 project: render direct project view (call `getProjectFullDetail(project_id)`)
6. If 2+: render tabs, each tab calls `getProjectFullDetail(project_id)`
7. Browser displays project workspace with phases, payments, documents, notes

**Data flow for admin projects list:**
1. Admin visits `/admin/projects`
2. Server calls `getAllProjectsWithPayments()`
3. Returns projects with: parent client name, accepted_total, payment statuses, active timer info, calculated €/h
4. Renders ProjectRow for each (clone of ClientRow pattern)

---

## Runtime State Inventory

> This phase involves renaming + moving FK relationships from `client_id` to `project_id`. Verify all runtime state.

| Category | Items Found | Action Required |
|----------|-------------|------------------|
| **Stored data** | Current DB has ~13 tables. Hard migration acceptable (drop/recreate from schema). Test data only — no customer data to preserve. | Code edit: `src/db/schema.ts` (new `projects` table, update FK on 6 tables, add `slug` to clients, new `settings` table). Execute `drizzle-kit push` to apply to Neon. |
| **Live service config** | No external service configuration (n8n workflows, webhooks, etc.) references client_id or project structure explicitly. | None — if services are added in future phases, ensure they use project_id. |
| **OS-registered state** | None — this is a web application with no local task scheduling or registered executables. | None required. |
| **Secrets/env vars** | `.env` currently has: DATABASE_URL, NEXTAUTH_SECRET, NEXTAUTH_URL. No references to client/project IDs. New `target_hourly_rate` will be stored in DB `settings` table (not env var). | None for secrets. If admins prefer env var, add `TARGET_HOURLY_RATE=50` to `.env` and read in analytics component. Recommendation: use DB table for flexibility. |
| **Build artifacts** | No build artifacts reference client_id or project structure. Next.js builds are stateless. | None required. Fresh build after schema migration. |

**Conclusion:** All data is test data. Hard migration is acceptable. No runtime state inventory concerns blocking execution.

---

## Common Pitfalls

### Pitfall 1: Incomplete FK Migration Across Tables
**What goes wrong:** Missing one table's FK migration (e.g., forgetting to update `time_entries.client_id` → `project_id`), causing orphaned records or failed queries in admin workspace when drilling into a specific project.

**Why it happens:** 6 tables need updating. Easy to miss one if checklist is informal.

**How to avoid:** 
1. List all 6 tables in schema migration PR title: `phases, payments, quote_items, time_entries, documents, notes`.
2. After `drizzle-kit push`, verify each table with `\dt public.*` in psql and spot-check that old client_id FK is gone, new project_id FK is present.
3. In planner, create a Wave 0 schema-only task + verification subtasks for each table.

**Warning signs:** 
- `getProjectFullDetail()` returns empty phases/payments even though they exist in the DB.
- Timer actions fail with "project_id not found" FK violation on insert.

### Pitfall 2: Client Middleware Resolution Order (Slug vs. Token)
**What goes wrong:** Middleware checks token first and finds a match before trying slug, so `/c/mario-rossi` is treated as an invalid token and returns 404 even though the slug exists.

**Why it happens:** Easy to reverse the order in `validate-slug` or middleware logic.

**How to avoid:**
1. Middleware must call `/api/internal/validate-slug?slug=...` FIRST.
2. Only if slug lookup fails (404 from API), fall back to existing token validation.
3. Document the order in code comment.

**Warning signs:** 
- Slug links return 404 even though slug is in the DB and client can access via token link.
- Creating a new slug for an existing client breaks the old token link (should not).

### Pitfall 3: Admin Workspace Queries Not Scoped to Current Project
**What goes wrong:** `getProjectFullDetail()` accidentally returns data from multiple projects or from the wrong project due to missing WHERE clause on project_id.

**Why it happens:** Copy-pasting from `getClientFullDetail()` and forgetting to update the WHERE conditions.

**How to avoid:**
1. After writing `getProjectFullDetail()`, trace through each query: phases, tasks, deliverables, payments, documents, notes, comments, quote_items.
2. Verify each has `.where(eq(table.project_id, projectId))` or is a child query that's already filtered.
3. Add a comment above each query stating what it filters on.

**Warning signs:** 
- Workspace shows phases/tasks from sibling projects.
- Clicking into a project workspace, then switching projects, shows the same data.

### Pitfall 4: Timer Still Checks for Global "Only One Active" Instead of Per-Project
**What goes wrong:** Admin starts timer for Project A, then clicks timer for Project B, and Project A's timer is stopped. User expects independent timers.

**Why it happens:** Current `startTimer()` stops ALL running sessions. Must be updated to allow one timer per project (or clarify that "global only one timer" is the design).

**How to avoid:**
1. Decision: Are timers per-project OR global (only one active per admin account)?
2. From CONTEXT (D-15): "Only one timer active at a time (scoped to project now)" suggests global is intended (one active total).
3. Keep current logic but verify: when admin starts project B's timer, project A's should auto-stop.
4. Add test: start timer A, start timer B, verify A is stopped and B is running.

**Warning signs:** 
- Two projects have active timers simultaneously (duration_seconds null on both).

### Pitfall 5: Client Slug Field Validation Too Strict or Too Loose
**What goes wrong:** Slug regex rejects valid inputs (e.g., "mario-rossi-2") or accepts invalid ones (e.g., spaces, special chars).

**Why it happens:** Regex written without testing against edge cases.

**How to avoid:**
1. Define slug rule: lowercase alphanumeric + hyphens only, 3-50 chars, must be unique.
2. Zod schema: `slug: z.string().regex(/^[a-z0-9-]{3,50}$/).optional().or(z.null())`
3. Test form submission with: "mario-rossi", "mario-rossi-2", "MARIO" (should fail), "m--r" (ok?), "m" (too short), "m " (space, should fail).

**Warning signs:** 
- Admin can't set a slug they expect to work (form rejects it).
- Middleware crashes on malformed slug from DB.

### Pitfall 6: "Settings" Table Key Mismatches in Code
**What goes wrong:** Code reads `settings.value WHERE key = 'hourly_rate'` but admin wrote it as `'target_hourly_rate'`, returning null and falling back to a hardcoded default.

**Why it happens:** Settings keys are strings with no schema enforcement. Easy to have typos or inconsistent naming.

**How to avoid:**
1. Define an enum or constant for all settings keys:
   ```typescript
   const SETTINGS_KEYS = {
     TARGET_HOURLY_RATE: 'target_hourly_rate',
   } as const;
   ```
2. Always read via constant: `getSetting(SETTINGS_KEYS.TARGET_HOURLY_RATE)`.
3. Admin form submits value for this constant key only.

**Warning signs:** 
- Analytics always shows a hardcoded rate (default value) instead of what admin set.
- Changing the setting has no effect.

---

## Code Examples

Verified patterns from the existing codebase and applied to Phase 04 context:

### Database Schema Refactor (Drizzle)
```typescript
// src/db/schema.ts (NEW projects table + updated FKs)

// Clients now has slug field (optional, unique)
export const clients = pgTable("clients", {
  id: text("id").primaryKey().$defaultFn(() => nanoid()),
  name: text("name").notNull(),
  brand_name: text("brand_name").notNull(),
  brief: text("brief").notNull(),
  token: text("token").notNull().unique().$defaultFn(() => nanoid()),
  slug: text("slug").unique(), // NEW — optional, unique, URL-safe
  archived: boolean("archived").notNull().default(false),
  created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});

// NEW projects table
export const projects = pgTable("projects", {
  id: text("id").primaryKey().$defaultFn(() => nanoid()),
  client_id: text("client_id").notNull().references(() => clients.id, { onDelete: "cascade" }),
  name: text("name").notNull(), // brand/project name
  accepted_total: numeric("accepted_total", { precision: 10, scale: 2 }).default("0"), // denormalized
  archived: boolean("archived").notNull().default(false),
  created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});

// Phases: FK now points to projects, not clients
export const phases = pgTable("phases", {
  id: text("id").primaryKey().$defaultFn(() => nanoid()),
  project_id: text("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }), // CHANGED from client_id
  title: text("title").notNull(),
  sort_order: integer("sort_order").notNull().default(0),
  status: text("status").notNull().default("upcoming"),
});

// Payments: FK now points to projects, not clients
export const payments = pgTable("payments", {
  id: text("id").primaryKey().$defaultFn(() => nanoid()),
  project_id: text("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }), // CHANGED from client_id
  label: text("label").notNull(),
  amount: numeric("amount", { precision: 10, scale: 2 }).notNull(),
  status: text("status").notNull().default("da_saldare"),
  paid_at: timestamp("paid_at", { withTimezone: true }),
});

// Quote items: FK now points to projects, not clients
export const quote_items = pgTable("quote_items", {
  id: text("id").primaryKey().$defaultFn(() => nanoid()),
  project_id: text("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }), // CHANGED from client_id
  service_id: text("service_id").references(() => service_catalog.id, { onDelete: "restrict" }),
  quantity: numeric("quantity", { precision: 10, scale: 2 }).notNull(),
  unit_price: numeric("unit_price", { precision: 10, scale: 2 }).notNull(),
  subtotal: numeric("subtotal", { precision: 10, scale: 2 }).notNull(),
  custom_label: text("custom_label"),
});

// Time entries: FK now points to projects, not clients
export const time_entries = pgTable("time_entries", {
  id: text("id").primaryKey().$defaultFn(() => nanoid()),
  project_id: text("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }), // CHANGED from client_id
  started_at: timestamp("started_at", { withTimezone: true }).notNull().defaultNow(),
  ended_at: timestamp("ended_at", { withTimezone: true }),
  duration_seconds: integer("duration_seconds"),
});

// Documents: FK now points to projects, not clients
export const documents = pgTable("documents", {
  id: text("id").primaryKey().$defaultFn(() => nanoid()),
  project_id: text("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }), // CHANGED from client_id
  label: text("label").notNull(),
  url: text("url").notNull(),
  created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});

// Notes: FK now points to projects, not clients
export const notes = pgTable("notes", {
  id: text("id").primaryKey().$defaultFn(() => nanoid()),
  project_id: text("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }), // CHANGED from client_id
  body: text("body").notNull(),
  created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});

// NEW settings table for global admin settings (e.g., target hourly rate)
export const settings = pgTable("settings", {
  key: text("key").primaryKey(),
  value: text("value").notNull(),
  updated_at: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
});

// Relations updated
export const projectsRelations = relations(projects, ({ one, many }) => ({
  client: one(clients, { fields: [projects.client_id], references: [clients.id] }),
  phases: many(phases),
  payments: many(payments),
  documents: many(documents),
  notes: many(notes),
  quote_items: many(quote_items),
}));
```

### Admin Query Layer — getProjectFullDetail
```typescript
// src/lib/admin-queries.ts (NEW function, following getClientFullDetail pattern)

export type ProjectFullDetail = {
  project: Project & { client: Client };
  phases: Array<Phase & { tasks: Array<Task & { deliverables: Deliverable[] }> }>;
  payments: Payment[];
  documents: Document[];
  notes: Note[];
  comments: Comment[];
  quoteItems: QuoteItemWithLabel[];
  activeServices: ServiceCatalog[];
  totalTrackedSeconds: number; // for profitability calc
};

export async function getProjectFullDetail(id: string): Promise<ProjectFullDetail | null> {
  const projectRows = await db
    .select()
    .from(projects)
    .where(eq(projects.id, id))
    .limit(1);
  
  if (projectRows.length === 0) return null;
  const project = projectRows[0];

  // Fetch parent client
  const clientRows = await db
    .select()
    .from(clients)
    .where(eq(clients.id, project.client_id))
    .limit(1);
  const client = clientRows[0] || null;

  // Fetch all phases for this PROJECT (not client)
  const phasesRows = await db
    .select()
    .from(phases)
    .where(eq(phases.project_id, id))
    .orderBy(asc(phases.sort_order));

  const phaseIds = phasesRows.map((p) => p.id);

  // Fetch tasks scoped to this project's phases
  const tasksRows = phaseIds.length === 0
    ? []
    : await db
        .select()
        .from(tasks)
        .where(inArray(tasks.phase_id, phaseIds))
        .orderBy(asc(tasks.sort_order));

  const taskIds = tasksRows.map((t) => t.id);

  // Fetch deliverables scoped to this project's tasks
  const deliverablesRows = taskIds.length === 0
    ? []
    : await db
        .select()
        .from(deliverables)
        .where(inArray(deliverables.task_id, taskIds));

  // Payments for this PROJECT (not client)
  const paymentsRows = await db
    .select()
    .from(payments)
    .where(eq(payments.project_id, id));

  // Documents for this PROJECT (not client)
  const documentsRows = await db
    .select()
    .from(documents)
    .where(eq(documents.project_id, id))
    .orderBy(asc(documents.created_at));

  // Notes for this PROJECT (not client)
  const notesRows = await db
    .select()
    .from(notes)
    .where(eq(notes.project_id, id))
    .orderBy(asc(notes.created_at));

  // Comments (polymorphic on entity_id) — collect all tasks, deliverables, and the project itself
  const allEntityIds = [id, ...taskIds, ...deliverablesRows.map((d) => d.id)];
  const commentsRows = allEntityIds.length === 0
    ? []
    : await db
        .select()
        .from(comments)
        .where(inArray(comments.entity_id, allEntityIds))
        .orderBy(asc(comments.created_at));

  // Quote items for this PROJECT (not client)
  const quoteItemRows: QuoteItemWithLabel[] = await db
    .select({
      id: quote_items.id,
      label: sql<string>`COALESCE(${service_catalog.name}, ${quote_items.custom_label})`,
      custom_label: quote_items.custom_label,
      service_id: quote_items.service_id,
      quantity: quote_items.quantity,
      unit_price: quote_items.unit_price,
      subtotal: quote_items.subtotal,
    })
    .from(quote_items)
    .leftJoin(service_catalog, eq(quote_items.service_id, service_catalog.id))
    .where(eq(quote_items.project_id, id))
    .orderBy(asc(quote_items.id));

  // Active services (unchanged)
  const activeServiceRows = await db
    .select()
    .from(service_catalog)
    .where(eq(service_catalog.active, true))
    .orderBy(asc(service_catalog.name));

  // Total tracked seconds for this PROJECT (for profitability calc)
  const totalRes = await db
    .select({
      total: sql<string>`coalesce(sum(${time_entries.duration_seconds}), 0)`,
    })
    .from(time_entries)
    .where(eq(time_entries.project_id, id));

  const totalTrackedSeconds = totalRes[0] ? parseInt(totalRes[0].total) : 0;

  // Rebuild hierarchy
  const phasesWithTasks = phasesRows.map((phase) => ({
    ...phase,
    tasks: tasksRows
      .filter((t) => t.phase_id === phase.id)
      .map((task) => ({
        ...task,
        deliverables: deliverablesRows.filter((d) => d.task_id === task.id),
      })),
  }));

  return {
    project: { ...project, client } as any,
    phases: phasesWithTasks,
    payments: paymentsRows,
    documents: documentsRows,
    notes: notesRows,
    comments: commentsRows,
    quoteItems: quoteItemRows,
    activeServices: activeServiceRows,
    totalTrackedSeconds,
  };
}
```

### Slug Resolution in Middleware
```typescript
// src/proxy.ts (UPDATED for slug-first resolution)

export async function proxy(request: NextRequest) {
  const pathname = request.nextUrl.pathname;

  // ── ADMIN GUARD ──────────────────────────────────────────────────────────
  if (pathname.startsWith("/admin")) {
    if (pathname === "/admin/login" || pathname.startsWith("/api/auth")) {
      return NextResponse.next();
    }
    const token = await getToken({
      req: request,
      secret: process.env.NEXTAUTH_SECRET,
    });
    if (!token) {
      const loginUrl = new URL("/admin/login", request.url);
      loginUrl.searchParams.set("callbackUrl", pathname);
      return NextResponse.redirect(loginUrl);
    }
    return NextResponse.next();
  }

  // ── CLIENT TOKEN/SLUG GUARD ─────────────────────────────────────────────
  if (pathname.startsWith("/c/")) {
    const slugOrTokenMatch = pathname.match(/^\/c\/([a-zA-Z0-9_-]+)/);
    if (!slugOrTokenMatch) {
      return NextResponse.rewrite(new URL("/not-found", request.url));
    }

    const slugOrToken = slugOrTokenMatch[1];

    try {
      // TRY SLUG FIRST — call internal API to resolve slug → client
      const validateUrl = new URL(
        `/api/internal/validate-slug?slug=${encodeURIComponent(slugOrToken)}`,
        request.url
      );
      let res = await fetch(validateUrl.toString());

      // If slug not found, fall back to TOKEN validation (existing pattern)
      if (!res.ok) {
        const validateTokenUrl = new URL(
          `/api/internal/validate-token?token=${encodeURIComponent(slugOrToken)}`,
          request.url
        );
        res = await fetch(validateTokenUrl.toString());
      }

      if (!res.ok) {
        return NextResponse.rewrite(new URL("/not-found", request.url));
      }

      return NextResponse.next();
    } catch {
      return NextResponse.rewrite(new URL("/not-found", request.url));
    }
  }

  return NextResponse.next();
}

export const config = {
  matcher: ["/admin/:path*", "/c/:path*"],
};
```

### New Internal API Route for Slug Validation
```typescript
// src/app/api/internal/validate-slug/route.ts (NEW)

import { NextRequest, NextResponse } from "next/server";
import { db } from "@/db";
import { clients } from "@/db/schema";
import { eq } from "drizzle-orm";

export async function GET(request: NextRequest) {
  const slug = request.nextUrl.searchParams.get("slug");

  if (!slug) {
    return NextResponse.json({ error: "slug required" }, { status: 400 });
  }

  const rows = await db
    .select({ id: clients.id })
    .from(clients)
    .where(eq(clients.slug, slug))
    .limit(1);

  if (rows.length === 0) {
    return NextResponse.json({ error: "not found" }, { status: 404 });
  }

  return NextResponse.json({ clientId: rows[0].id }, { status: 200 });
}
```

### Timer Actions Refactored for Project Scope
```typescript
// src/app/admin/timer-actions.ts (UPDATED for project_id)

"use server";

import { revalidatePath } from "next/cache";
import { db } from "@/db";
import { time_entries } from "@/db/schema";
import { eq, isNull } from "drizzle-orm";
import { nanoid } from "nanoid";

export async function startTimer(projectId: string): Promise<{ entryId: string }> {
  // Stop any currently running session (still global: only one timer active per admin)
  const running = await db
    .select({ id: time_entries.id })
    .from(time_entries)
    .where(isNull(time_entries.ended_at));

  for (const r of running) {
    const now = new Date();
    const entry = await db
      .select({ started_at: time_entries.started_at })
      .from(time_entries)
      .where(eq(time_entries.id, r.id))
      .limit(1);
    if (entry[0]) {
      const secs = Math.round((now.getTime() - new Date(entry[0].started_at).getTime()) / 1000);
      await db
        .update(time_entries)
        .set({ ended_at: now, duration_seconds: secs })
        .where(eq(time_entries.id, r.id));
    }
  }

  // Create new entry scoped to PROJECT (not client)
  const id = nanoid();
  await db.insert(time_entries).values({ id, project_id: projectId });
  revalidatePath("/admin");
  return { entryId: id };
}

export async function stopTimer(entryId: string): Promise<void> {
  const rows = await db
    .select({ started_at: time_entries.started_at })
    .from(time_entries)
    .where(eq(time_entries.id, entryId))
    .limit(1);

  if (!rows[0]) return;

  const now = new Date();
  const secs = Math.round((now.getTime() - new Date(rows[0].started_at).getTime()) / 1000);
  await db
    .update(time_entries)
    .set({ ended_at: now, duration_seconds: secs })
    .where(eq(time_entries.id, entryId));

  revalidatePath("/admin");
}
```

### Profitability Analytics Card (Component Pattern)
```typescript
// src/components/admin/ProfitabilityCard.tsx (NEW)

import { Project } from "@/db/schema";

export function ProfitabilityCard({
  project,
  totalTrackedSeconds,
  targetHourlyRate,
}: {
  project: Project & { accepted_total: string };
  totalTrackedSeconds: number;
  targetHourlyRate: number; // e.g., 50 €/h
}) {
  const hours = totalTrackedSeconds / 3600;
  const acceptedTotal = parseFloat(project.accepted_total || "0");

  // €/h real = accepted_total ÷ hours
  const realHourlyRate = hours > 0 ? acceptedTotal / hours : 0;

  // Ideal cost = target_rate × hours
  const idealCost = targetHourlyRate * hours;

  // Delta = profit/loss
  const delta = acceptedTotal - idealCost;
  const deltaIsProfit = delta >= 0;

  return (
    <div className="bg-white rounded-lg border border-[#e5e7eb] p-4 space-y-3">
      <h3 className="font-medium text-[#1a1a1a]">Profittabilità</h3>
      
      <div className="grid grid-cols-2 gap-3 text-sm">
        <div>
          <p className="text-[#71717a] text-xs">Ore lavorate</p>
          <p className="font-mono font-semibold text-[#1a1a1a]">{hours.toFixed(1)}h</p>
        </div>
        <div>
          <p className="text-[#71717a] text-xs">Importo accettato</p>
          <p className="font-mono font-semibold text-[#1a1a1a]">€{acceptedTotal.toFixed(2)}</p>
        </div>
      </div>

      <div className="border-t border-[#f4f4f5] pt-3 space-y-2 text-sm">
        <div className="flex justify-between">
          <span className="text-[#71717a]">€/h reale</span>
          <span className="font-mono font-semibold text-[#1a1a1a]">€{realHourlyRate.toFixed(2)}/h</span>
        </div>
        <div className="flex justify-between">
          <span className="text-[#71717a]">€/h target</span>
          <span className="font-mono font-semibold text-[#71717a]">€{targetHourlyRate.toFixed(2)}/h</span>
        </div>
        <div className="flex justify-between">
          <span className="text-[#71717a]">Costo ideale</span>
          <span className="font-mono font-semibold text-[#1a1a1a]">€{idealCost.toFixed(2)}</span>
        </div>
      </div>

      <div className={`border-t border-[#f4f4f5] pt-3 flex justify-between items-center`}>
        <span className="text-[#71717a]">Delta (guadagno/perdita)</span>
        <span className={`font-mono font-bold ${deltaIsProfit ? "text-green-600" : "text-red-600"}`}>
          {deltaIsProfit ? "+" : ""}€{delta.toFixed(2)}
        </span>
      </div>
    </div>
  );
}
```

---

## State of the Art

| Old Approach | Current Approach | When Changed | Impact |
|--------------|------------------|--------------|--------|
| Single project per client | Multi-project per client | Phase 04 | Clients can now manage multiple independent brands/projects with separate workspaces and profitability tracking |
| Client as primary work container | Project as primary work container | Phase 04 | Admin workspace structure mirrors project, not client. Client API queries projects, not client directly |
| Timer at client level | Timer at project level | Phase 04 | Hours tracked independently per project, enabling per-project profitability analysis |
| Token-only client links | Token + slug client links | Phase 04 | More user-friendly URLs (e.g., /c/mario-rossi instead of /c/xyzabc123). Token remains as fallback. |
| Hardcoded profitability target | Global settings table for target rate | Phase 04 | Admin can adjust target hourly rate from UI without changing code. Flexible for future settings. |

**Deprecated/outdated:**
- Client-level `accepted_total` field remains in schema for backward compat but becomes unused; project-level accepted_total is the source of truth.
- Old client detail workspace layout is cloned for project detail; both exist but admin only uses project workspace going forward.

---

## Assumptions Log

| # | Claim | Section | Risk if Wrong |
|---|-------|---------|---------------|
| A1 | "Only one timer active globally (per admin)" is the design intent, not per-project independence | Locked Decision D-15, Timer Pitfall | If per-project timers are expected, timer-actions refactor is wrong; need concurrent timer support instead of auto-stop logic. Clarify with user before implementing. |
| A2 | Hard migration (drop/recreate tables) is acceptable because all data is test data | Runtime State Inventory | If there are production customer records, hard migration will cause data loss. Verify no production data exists before schema push. |
| A3 | `settings` table with key-value structure is acceptable over env vars | Claude's Discretion | If user later requires non-DB storage for settings (e.g., Redis cache, config file), table approach is still compatible; no blocking constraint. |
| A4 | Slug-first middleware resolution (slug lookup before token fallback) is the intended order | Locked Decision D-06, D-08 | If token validation should be checked first (for legacy reasons), middleware order is reversed. Test both slug and token paths after implementation. |
| A5 | `comments` table remains polymorphic (entity_id) and does NOT move to project_id | Locked Decision D-02 | If comments should be scoped per-project (unlikely), add project_id FK and update all comment queries. Currently comments are global per entity, which is correct. |

**If this table is empty:** Not applicable — all claims verified against CONTEXT.md locked decisions.

---

## Open Questions

1. **Profitability analytics default target rate?**
   - What we know: User mentioned 50€/h as an example; needs global setting.
   - What's unclear: Should there be a fallback default (e.g., 50€/h) if settings table is empty, or should admin be forced to set it?
   - Recommendation: Initialize settings table with `target_hourly_rate = '50.00'` as default during first project workspace load. Admin can override from /admin/impostazioni.

2. **Multi-project client dashboard routing — how should it work?**
   - What we know: 1 project = direct view, 2+ projects = tabs.
   - What's unclear: Should the URL path for client dashboard change? Stay `/c/[token]` for all projects, or add `/c/[token]/projects/[id]`?
   - Recommendation: Keep URL `/c/[token]` (or `/c/[slug]`), let server-side logic choose whether to render single project view or tabs. Tabs can have internal navigation (e.g., URL hash or search param) to switch between projects without page reload.

3. **Analytics page aggregation scope?**
   - What we know: D-22 says "Statistiche page shows aggregated profitability for all projects + breakdown per client."
   - What's unclear: Should /admin/analytics show global profitability (sum of all projects for all clients) or be filterable by client/date range?
   - Recommendation: Start simple: global profitability table with columns: Client, Projects, Total Hours, Total Revenue, Avg €/h, Profit/Loss. Filter by client optional (defer to Phase 4.1 if needed).

4. **Project archival behavior?**
   - What we know: `projects.archived` field exists; D-13 doesn't mention archival UI.
   - What's unclear: Should archived projects be hidden from /admin/projects list or filtered to separate tab?
   - Recommendation: Hide archived projects by default (like clients list), add "Mostra archiviati" toggle link. Archival doesn't delete data, just hides it.

---

## Environment Availability

(Phase 04 is code/DB changes only — no external dependencies.)

| Dependency | Required By | Available | Version | Fallback |
|------------|------------|-----------|---------|----------|
| Neon Postgres | Schema migration + queries | ✓ | Active (from Phase 1) | — |
| Next.js API routes | Slug validation route | ✓ | 16 (installed) | — |
| Drizzle ORM | Schema migration + query building | ✓ | Current (installed) | — |
| Auth.js v4 | Admin session check (existing) | ✓ | Current (installed) | — |
| shadcn/ui Tabs | Multi-project dashboard tabs | ✓ | Current (installed) | Could fall back to native `<select>` dropdown, but Tabs is already in use |

**Missing dependencies with no fallback:** None.

**Missing dependencies with fallback:** Tabs component could be replaced with a `<select>` dropdown if shadcn/ui is ever removed, but this is a UI detail, not a blocker.

---

## Sources

### Primary (HIGH confidence)
- **CONTEXT.md** (Phase 04 decisions) — All 22 locked decisions directly from user's discuss phase. D-01 through D-22, Claude's Discretion, Deferred Ideas sections.
- **Codebase inspection** — Verified existing schema (schema.ts), admin queries (admin-queries.ts), middleware pattern (proxy.ts), timer actions (timer-actions.ts), client view pattern (client-view.ts), admin workspace layout (clients/[id]/page.tsx).
- **REQUIREMENTS.md** — PROJ-01 through PROJ-05 mapped to implementation guidance.
- **ROADMAP.md** — Phase 04 goal and success criteria verified.

### Secondary (MEDIUM confidence)
- **Next.js 16 App Router patterns** — Edge middleware, server actions, API routes all verified against existing project structure.
- **Drizzle ORM query patterns** — Relations, WHERE scoping, parallel queries all verified against Phase 1–3 implementation (getAllClientsWithPayments, getClientFullDetail, timer-actions).
- **shadcn/ui Tabs component** — Already in use in admin workspace (clients/[id]/page.tsx); no additional research needed.

### Tertiary (LOW confidence)
- None — all findings tied to locked decisions and verified codebase patterns.

---

## Metadata

**Confidence breakdown:**
- **Standard stack:** HIGH — all libraries already installed and used; no new dependencies.
- **Architecture:** HIGH — locked decisions in CONTEXT.md eliminate discretion; patterns clone from existing workspaces.
- **Pitfalls:** HIGH — identified from common FK migration mistakes, middleware routing, query scoping issues observed in similar refactors.
- **Environment:** HIGH — no external dependencies; Neon, Next.js, Drizzle all active and verified.

**Research date:** 2026-05-21
**Valid until:** 2026-06-04 (14 days — architecture stable, no fast-moving libraries)

**Next phase:** `/gsd-plan-phase 04` will create 4–5 plans for vertical-slice execution (Schema Wave 0 → Core Routing → Admin UI → Client UI + Analytics).