# 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 }>; payments: Payment[]; documents: Document[]; notes: Note[]; comments: Comment[]; quoteItems: QuoteItemWithLabel[]; activeServices: ServiceCatalog[]; totalTrackedSeconds: number; // for profitability calc }; export async function getProjectFullDetail(id: string): Promise { 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`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`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 { 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 (

Profittabilità

Ore lavorate

{hours.toFixed(1)}h

Importo accettato

€{acceptedTotal.toFixed(2)}

€/h reale €{realHourlyRate.toFixed(2)}/h
€/h target €{targetHourlyRate.toFixed(2)}/h
Costo ideale €{idealCost.toFixed(2)}
Delta (guadagno/perdita) {deltaIsProfit ? "+" : ""}€{delta.toFixed(2)}
); } ``` --- ## 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 `` 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).