clienthub/.planning/phases/20-knowledge-base-cliente/20-02-PLAN.md

phase, plan, type, wave, depends_on, files_modified, autonomous, requirements, must_haves

phase	plan	type	wave	depends_on	files_modified	autonomous	requirements	must_haves
20-knowledge-base-cliente	02	execute	2	20-01	src/db/schema.ts src/lib/lead-service.ts src/app/admin/leads/actions.ts	true	KB-01 KB-02	truths artifacts key_links La tabella clientTranscripts è definita in schema.ts con le relazioni Drizzle corrette getTranscripts(leadId) restituisce i transcript in ordine call_date DESC addTranscript e deleteTranscript sono server actions con requireAdmin guard path provides contains src/db/schema.ts Definizione tabella clientTranscripts + relazioni Drizzle per leads e clients export const clientTranscripts = pgTable path provides exports src/lib/lead-service.ts Query getTranscripts(leadId) — tutti i campi incluso content completo getTranscripts path provides exports src/app/admin/leads/actions.ts Server actions addTranscript e deleteTranscript con requireAdmin addTranscript deleteTranscript from to via pattern src/app/admin/leads/actions.ts src/db/schema.ts import clientTranscripts clientTranscripts from to via pattern src/lib/lead-service.ts src/db/schema.ts import clientTranscripts getTranscripts

Aggiungere la definizione Drizzle della tabella `clientTranscripts` in schema.ts, la query `getTranscripts` in lead-service.ts e le server actions `addTranscript` / `deleteTranscript` in actions.ts.

Purpose: Fornisce il layer dati completo per la UI del piano 20-03. Phase 21 (AI) leggerà i transcript via getTranscripts(leadId) — la firma deve restituire tutti i campi incluso content completo.

Output: Schema Drizzle + query + actions pronte, build TypeScript senza errori.

<execution_context> @$HOME/.claude/get-shit-done/workflows/execute-plan.md @$HOME/.claude/get-shit-done/templates/summary.md </execution_context>

@.planning/phases/20-knowledge-base-cliente/20-CONTEXT.md @.planning/phases/20-knowledge-base-cliente/20-01-SUMMARY.md

Pattern tabella CRM con FK lead_id (activities, linee 464-481):

export const activities = pgTable("activities", {
  id: text("id").primaryKey().$defaultFn(() => nanoid()),
  lead_id: text("lead_id").notNull().references(() => leads.id, { onDelete: "cascade" }),
  type: text("type").notNull(),
  notes: text("notes").notNull(),
  activity_date: timestamp("activity_date", { withTimezone: true }).notNull(),
  created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});

Pattern relazioni (leadsRelations, linea 631-635):

export const leadsRelations = relations(leads, ({ many }) => ({
  quotes: many(quotes),
  activities: many(activities),
  reminders: many(reminders),
}));

export const activitiesRelations = relations(activities, ({ one }) => ({
  lead: one(leads, { fields: [activities.lead_id], references: [leads.id] }),
}));

Pattern TypeScript types (fine file):

export type Activity = typeof activities.$inferSelect;
export type NewActivity = typeof activities.$inferInsert;

Pattern query (lead-service.ts linee 51-57):

export async function getActivityLog(leadId: string) {
  return await db
    .select()
    .from(activities)
    .where(eq(activities.lead_id, leadId))
    .orderBy(desc(activities.activity_date));
}

Pattern requireAdmin + server action (actions.ts linee 164-205):

async function requireAdmin() {
  const session = await getServerSession(authOptions);
  if (!session) throw new Error("Non autorizzato");
}

export async function updateLeadField(leadId: string, ...) {
  await requireAdmin();
  // ... validazione ...
  await db.update(leads).set({...}).where(eq(leads.id, leadId));
  revalidatePath("/admin/leads");
  revalidatePath(`/admin/leads/${leadId}`);
}

Import correnti in actions.ts (linee 1-16):

"use server";
import { z } from "zod";
import { db } from "@/db";
import { leads, quotes, tags } from "@/db/schema";
import { eq, and } from "drizzle-orm";
import { revalidatePath } from "next/cache";
import { getServerSession } from "next-auth";
import { authOptions } from "@/lib/auth";

Import correnti in lead-service.ts (linee 1-4):

import { eq, and, isNull, desc, gte, lte, ilike, or } from "drizzle-orm";
import { db } from "@/db";
import { leads, activities, reminders, quotes } from "@/db/schema";

Task 1: Aggiungere clientTranscripts a src/db/schema.ts src/db/schema.ts - src/db/schema.ts (leggere tutto il file per capire dove inserire la nuova tabella e come aggiornare le relations esistenti) - .planning/phases/20-knowledge-base-cliente/20-CONTEXT.md (D-01, D-02 — campi esatti) Aggiungere in `src/db/schema.ts` tre blocchi, rispettando la struttura esistente del file:

1. Definizione tabella — aggiungere dopo // ============ REMINDERS TABLE (dopo la chiusura della definizione reminders a linea ~499) e prima di // ============ RELATIONS:

// ============ CLIENT TRANSCRIPTS TABLE (Knowledge Base — Phase 20) ============
// Transcript datati delle call, multipli per lead o cliente.
// lead_id e client_id sono entrambi nullable (D-01): un transcript può appartenere
// a un lead pre-conversione (Phase 20 UI) o a un cliente post-conversione (futuro).
// onDelete cascade su entrambe le FK — se il lead/cliente viene eliminato, i transcript vengono eliminati.
export const clientTranscripts = pgTable("client_transcripts", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  lead_id: text("lead_id")
    .references(() => leads.id, { onDelete: "cascade" }),
  client_id: text("client_id")
    .references(() => clients.id, { onDelete: "cascade" }),
  title: text("title"), // opzionale — es. "Discovery call 12 giugno"
  content: text("content").notNull(), // testo grezzo illimitato (PostgreSQL text)
  call_date: text("call_date").notNull(), // DATE come text "YYYY-MM-DD" — coerente col pattern date nel progetto
  created_at: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

Nota su call_date: usare text invece di un tipo date Drizzle — il progetto gestisce le date come string ISO nei form e nel DB (vedi activity_date che è timestamp, ma call_date è date pura). Se Drizzle pg-core espone date nativo, usarlo; altrimenti usare text("call_date").notNull() per coerenza con i pattern di input da <input type="date"> che restituisce stringhe "YYYY-MM-DD".

2. Relazioni — aggiungere nei blocchi relations esistenti:

Aggiornare leadsRelations (attuale linea ~631):

export const leadsRelations = relations(leads, ({ many }) => ({
  quotes: many(quotes),
  activities: many(activities),
  reminders: many(reminders),
  transcripts: many(clientTranscripts),  // ← aggiungere questa riga
}));

Aggiornare clientsRelations (attuale linea ~503):

export const clientsRelations = relations(clients, ({ many }) => ({
  projects: many(projects),
  transcripts: many(clientTranscripts),  // ← aggiungere questa riga
}));

Aggiungere le nuove relations dopo remindersRelations:

export const clientTranscriptsRelations = relations(clientTranscripts, ({ one }) => ({
  lead: one(leads, {
    fields: [clientTranscripts.lead_id],
    references: [leads.id],
  }),
  client: one(clients, {
    fields: [clientTranscripts.client_id],
    references: [clients.id],
  }),
}));

3. TypeScript types — aggiungere in fondo al file dopo export type Reminder:

export type ClientTranscript = typeof clientTranscripts.$inferSelect;
export type NewClientTranscript = typeof clientTranscripts.$inferInsert;

cd /Users/simonecavalli/Vault/IAMCAVALLI && npx tsc --noEmit 2>&1 | head -20 - `grep -c "export const clientTranscripts = pgTable" src/db/schema.ts` restituisce 1 - `grep -c "transcripts: many(clientTranscripts)" src/db/schema.ts` restituisce 2 (leadsRelations + clientsRelations) - `grep -c "export const clientTranscriptsRelations" src/db/schema.ts` restituisce 1 - `grep -c "export type ClientTranscript" src/db/schema.ts` restituisce 1 - `npx tsc --noEmit` passa senza errori TypeScript relativi a schema.ts clientTranscripts definita in schema.ts con relazioni Drizzle bidirezionali e TypeScript types esportati. Task 2: Aggiungere getTranscripts a lead-service.ts e addTranscript/deleteTranscript ad actions.ts src/lib/lead-service.ts, src/app/admin/leads/actions.ts - src/lib/lead-service.ts (leggere per vedere gli import correnti e dove appendere getTranscripts) - src/app/admin/leads/actions.ts (leggere per vedere gli import correnti e la posizione di requireAdmin) - src/db/schema.ts (dopo il Task 1 — per verificare il nome esatto dell'export clientTranscripts) **In src/lib/lead-service.ts:**

Aggiungere clientTranscripts agli import esistenti:

import { leads, activities, reminders, quotes, clientTranscripts } from "@/db/schema";

Appendere in fondo al file (dopo getQuotesByLeadId):

// Get transcript log for a lead — call_date DESC (D-05)
// Restituisce tutti i campi incluso content completo (Phase 21 li legge in blocco).
export async function getTranscripts(leadId: string) {
  return await db
    .select()
    .from(clientTranscripts)
    .where(eq(clientTranscripts.lead_id, leadId))
    .orderBy(desc(clientTranscripts.call_date));
}

---

In src/app/admin/leads/actions.ts:

Aggiungere clientTranscripts agli import dal DB schema:

import { leads, quotes, tags, clientTranscripts } from "@/db/schema";

Aggiungere nanoid agli import (serve per generare l'id del transcript):

import { nanoid } from "nanoid";

Definire lo schema Zod per il transcript e appendere le due actions dopo le tag actions esistenti (dopo renameLeadTag):

// ── Transcript actions (Phase 20 — KB-02) ────────────────────────────────────

const addTranscriptSchema = z.object({
  lead_id: z.string().min(1),
  title: z.string().optional(),
  content: z.string().min(1, "Il testo del transcript è obbligatorio"),
  call_date: z.string().regex(/^\d{4}-\d{2}-\d{2}$/, "Data non valida (YYYY-MM-DD)"),
});

export async function addTranscript(data: z.infer<typeof addTranscriptSchema>) {
  await requireAdmin();

  const parsed = addTranscriptSchema.safeParse(data);
  if (!parsed.success) {
    return { success: false, error: parsed.error.issues[0].message };
  }

  try {
    const [transcript] = await db
      .insert(clientTranscripts)
      .values({
        id: nanoid(),
        lead_id: parsed.data.lead_id,
        title: parsed.data.title || null,
        content: parsed.data.content,
        call_date: parsed.data.call_date,
      })
      .returning();

    revalidatePath(`/admin/leads/${parsed.data.lead_id}`);
    return { success: true, transcript };
  } catch (error) {
    console.error("addTranscript error:", error);
    return { success: false, error: "Errore nel salvataggio del transcript" };
  }
}

export async function deleteTranscript(transcriptId: string, leadId: string) {
  await requireAdmin();

  if (!transcriptId) throw new Error("ID transcript richiesto");

  try {
    await db
      .delete(clientTranscripts)
      .where(eq(clientTranscripts.id, transcriptId));

    revalidatePath(`/admin/leads/${leadId}`);
    return { success: true };
  } catch (error) {
    console.error("deleteTranscript error:", error);
    return { success: false, error: "Errore nell'eliminazione del transcript" };
  }
}

cd /Users/simonecavalli/Vault/IAMCAVALLI && npx tsc --noEmit 2>&1 | grep -E "lead-service|actions" | head -10 - `grep -c "export async function getTranscripts" src/lib/lead-service.ts` restituisce 1 - `grep -c "orderBy(desc(clientTranscripts.call_date))" src/lib/lead-service.ts` restituisce 1 - `grep -c "export async function addTranscript" src/app/admin/leads/actions.ts` restituisce 1 - `grep -c "export async function deleteTranscript" src/app/admin/leads/actions.ts` restituisce 1 - `grep -c "await requireAdmin()" src/app/admin/leads/actions.ts` è >= 3 (addTranscript e deleteTranscript devono averlo, più le actions esistenti) - `grep -c "revalidatePath" src/app/admin/leads/actions.ts` aumenta di 2 rispetto al file originale (una per addTranscript, una per deleteTranscript) - `npx tsc --noEmit` passa senza errori su lead-service.ts e actions.ts getTranscripts esportata da lead-service.ts, addTranscript e deleteTranscript aggiunte ad actions.ts con requireAdmin guard e revalidatePath.

<threat_model>

Trust Boundaries

Boundary	Descrizione
browser → server action	addTranscript e deleteTranscript sono "use server" — input non trusted
server action → database	Query Drizzle con parametri typed

STRIDE Threat Register

Threat ID	Category	Component	Disposition	Mitigation Plan
T-20-04	Elevation of Privilege	addTranscript / deleteTranscript	mitigate	await requireAdmin() come prima istruzione in entrambe le actions — lancia Error se sessione assente, blocca esecuzione
T-20-05	Tampering	addTranscript — content	accept	Nessuna sanitization HTML richiesta (testo grezzo, non renderizzato come HTML — CONTEXT.md security note); Zod valida presenza e tipo
T-20-06	Tampering	deleteTranscript — transcriptId	mitigate	L'ID viene passato dalla UI admin-only (protetta da requireAdmin); Drizzle usa query parametrizzata — no SQL injection
T-20-07	Information Disclosure	getTranscripts	mitigate	Funzione usata solo in server components admin (/admin/leads/[id]/page.tsx); non esposta su API client (D-04 — client_id FK presente ma non UI client in Phase 20)
</threat_model>

```bash cd /Users/simonecavalli/Vault/IAMCAVALLI

Schema: tabella e relazioni presenti

grep -E "clientTranscripts|ClientTranscript" src/db/schema.ts | grep -v "^//"

lead-service: query con ordinamento corretto

grep -A 6 "getTranscripts" src/lib/lead-service.ts

actions: requireAdmin su entrambe le nuove actions

grep -B 1 "requireAdmin" src/app/admin/leads/actions.ts

Build TypeScript

npx tsc --noEmit 2>&1 | tail -5

</verification>

<success_criteria>
- schema.ts contiene `clientTranscripts` table, relazioni bidirezionali (leads + clients), e TypeScript types (KB-01)
- lead-service.ts esporta `getTranscripts(leadId)` che ordina per `call_date DESC` (KB-02, D-05)
- actions.ts ha `addTranscript` e `deleteTranscript` entrambe con `requireAdmin()` guard (KB-02)
- `npx tsc --noEmit` passa senza errori sui file modificati
</success_criteria>

<output>
Dopo il completamento, creare `.planning/phases/20-knowledge-base-cliente/20-02-SUMMARY.md` con:
- Tabella Drizzle aggiunta: clientTranscripts con 7 campi
- Query aggiunta: getTranscripts(leadId) in lead-service.ts
- Actions aggiunte: addTranscript, deleteTranscript in actions.ts
- TypeScript check: passed/failed
</output>