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:

# 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:

   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)

// 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

// 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

// 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

// 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

// 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)

// 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).