clienthub/.planning/milestones/v2.0-phases/10-crm-pipeline-activity-logging/10-01-PLAN.md

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

phase	plan	type	wave	depends_on	files_modified	autonomous	requirements
10	01	execute	1		src/db/schema.ts src/lib/lead-validators.ts src/lib/lead-service.ts	true	CRM-01 CRM-02 CRM-03 CRM-04

Expand Phase 10 CRM Foundation: Complete the `leads`, `activities`, and `reminders` tables in the database schema. Establish pipeline stage enums, activity type definitions, and query layer patterns for lead management and activity logging.

Purpose: Create the schema foundation required by Phase 10 UI (Lead CRUD, pipeline view, activity log, follow-up widget). These tables enable full-featured CRM operations including lead tracking, interaction history, and reminder scheduling.

Output:

• Complete leads table with CRM-required fields (name, email, phone, company, status/stage, last_contact_date, next_action)
• activities table (timestamped records of interactions: calls, emails, meetings, notes)
• reminders table (follow-up reminders with due dates and completion state)
• TypeScript types and query layer (Lead, Activity, Reminder; stage constants)
• Database relations wired to leads from quotes (many quotes per lead)

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

@.planning/PROJECT.md @.planning/ROADMAP.md @.planning/STATE.md @.planning/REQUIREMENTS.md Task 1: Expand leads table and add activities, reminders tables to schema src/db/schema.ts Update the database schema by expanding the `leads` table and adding two new tables for CRM operations.

1. Expand leads table (after current definition, line 363):

Replace the placeholder definition with full CRM fields:

export const leads = pgTable("leads", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  name: text("name").notNull(),
  email: text("email"),
  phone: text("phone"),
  company: text("company"),
  status: text("status")
    .notNull()
    .default("contacted"), // contacted | qualified | proposal_sent | negotiating | won | lost
  last_contact_date: timestamp("last_contact_date", { withTimezone: true }),
  next_action: text("next_action"),
  next_action_date: timestamp("next_action_date", { withTimezone: true }),
  notes: text("notes"),
  created_at: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
  updated_at: timestamp("updated_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

2. Add activities table (after leads table):

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(), // call | email | meeting | note
  duration_minutes: integer("duration_minutes"), // optional, for calls/meetings
  notes: text("notes").notNull(),
  activity_date: timestamp("activity_date", { withTimezone: true })
    .notNull(),
  created_at: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

3. Add reminders table (after activities table):

export const reminders = pgTable("reminders", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  lead_id: text("lead_id")
    .notNull()
    .references(() => leads.id, { onDelete: "cascade" }),
  title: text("title").notNull(),
  description: text("description"),
  due_date: timestamp("due_date", { withTimezone: true })
    .notNull(),
  completed_at: timestamp("completed_at", { withTimezone: true }),
  created_at: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

4. Update quotes table relation to lead (already exists, verify at line 341):

Ensure lead_id is present and FK to leads.id. The placeholder was already added in Phase 8; just verify it remains.

5. Add relations (after existing relations, before closing exports):

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] }),
}));

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

export const quotesRelations = relations(quotes, ({ one, many }) => ({
  lead: one(leads, { fields: [quotes.lead_id], references: [leads.id] }),
  client: one(clients, { fields: [quotes.client_id], references: [clients.id] }),
  offerMicro: one(offer_micros, { fields: [quotes.offer_micro_id], references: [offer_micros.id] }),
  items: many(quote_items),
}));

Data Safety: The placeholder leads table already exists; this task expands it in-place with new columns. No existing data is deleted — backwards compatible. npm run build 2>&1 | grep -c "error" | grep -q 0 && echo "TypeScript compile pass" Schema file updated with expanded leads table, activities and reminders tables, and all relations properly configured. npm run build passes. No placeholder data lost — fully backward compatible.

Task 2: Create Drizzle migration for CRM tables src/db/migrations Run drizzle-kit to auto-generate migration files for the new CRM tables and schema changes:

npx drizzle-kit generate --name=phase-10-crm-leads-activities-reminders

This creates a new migration file in src/db/migrations/ with SQL for the expanded leads table and new activities/reminders tables. The migration is NOT applied yet (Phase 10 execution will push to DB).

Validate the generated migration:

• Ensure leads table ALTER statement (adding phone, company, status, last_contact_date, etc.) is correct
• Ensure activities table has FK to leads with CASCADE delete
• Ensure reminders table has FK to leads with CASCADE delete
• Ensure quotes table lead_id FK exists (may already be present from Phase 8)
• Ensure all NOT NULL constraints match schema

If migration looks incorrect, manually edit the SQL in src/db/migrations/{migration_file}.sql to fix.

Check for syntax errors:

head -50 src/db/migrations/phase-10-crm-leads-activities-reminders.sql

ls src/db/migrations/ | grep phase-10 | head -1 Migration file generated and located in src/db/migrations/. SQL is syntactically valid and includes all table and relation changes. Task 3: Add lead validators (Zod schemas) and activity/reminder query layer src/lib/lead-validators.ts, src/lib/lead-service.ts Create two new library files for lead operations:

src/lib/lead-validators.ts — Zod schemas for CRM operations:

import { z } from "zod";

// Pipeline stages — enum match database default values
export const LEAD_STAGES = ["contacted", "qualified", "proposal_sent", "negotiating", "won", "lost"] as const;
export const ACTIVITY_TYPES = ["call", "email", "meeting", "note"] as const;

// Lead creation (admin)
export const createLeadSchema = z.object({
  name: z.string().min(1, "Nome richiesto").max(100),
  email: z.string().email("Email valida").optional().or(z.literal("")),
  phone: z.string().optional().or(z.literal("")),
  company: z.string().optional().or(z.literal("")),
  status: z.enum(LEAD_STAGES).default("contacted"),
  notes: z.string().optional().or(z.literal("")),
});

// Lead update
export const updateLeadSchema = createLeadSchema.partial().required({ status: false });

// Activity logging
export const createActivitySchema = z.object({
  lead_id: z.string().min(1, "Lead richiesto"),
  type: z.enum(ACTIVITY_TYPES),
  activity_date: z.date().or(z.string()),
  duration_minutes: z.number().min(0).optional(),
  notes: z.string().min(1, "Note richieste").max(1000),
});

// Reminder creation
export const createReminderSchema = z.object({
  lead_id: z.string().min(1, "Lead richiesto"),
  title: z.string().min(1, "Titolo richiesto").max(200),
  description: z.string().optional(),
  due_date: z.date().or(z.string()),
});

// Change lead stage
export const updateLeadStageSchema = z.object({
  lead_id: z.string().min(1),
  stage: z.enum(LEAD_STAGES),
});

src/lib/lead-service.ts — Query layer for leads, activities, and reminders:

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

// Get all leads with counts of quotes and upcoming reminders
export async function getAllLeads() {
  return await db
    .select()
    .from(leads)
    .orderBy(desc(leads.updated_at));
}

// Get lead by ID with related quotes and activity count
export async function getLeadById(id: string) {
  const [lead] = await db
    .select()
    .from(leads)
    .where(eq(leads.id, id));

  return lead;
}

// Get leads by stage (for pipeline view)
export async function getLeadsByStage(stage: string) {
  return await db
    .select()
    .from(leads)
    .where(eq(leads.status, stage))
    .orderBy(desc(leads.last_contact_date));
}

// Get leads that need follow-up (last contact > 7 days ago)
export async function getLeadsNeedingFollowUp(daysAgo: number = 7) {
  const cutoffDate = new Date();
  cutoffDate.setDate(cutoffDate.getDate() - daysAgo);

  return await db
    .select()
    .from(leads)
    .where(
      and(
        isNull(leads.last_contact_date),
        gte(leads.created_at, cutoffDate)
      )
    )
    .orderBy(leads.created_at);
}

// Get activity log for a lead (reverse chronological)
export async function getActivityLog(leadId: string) {
  return await db
    .select()
    .from(activities)
    .where(eq(activities.lead_id, leadId))
    .orderBy(desc(activities.activity_date));
}

// Get upcoming reminders for a lead
export async function getUpcomingReminders(leadId: string) {
  return await db
    .select()
    .from(reminders)
    .where(
      and(
        eq(reminders.lead_id, leadId),
        isNull(reminders.completed_at)
      )
    )
    .orderBy(reminders.due_date);
}

// Get all overdue reminders (admin widget)
export async function getOverdueReminders() {
  const now = new Date();
  return await db
    .select()
    .from(reminders)
    .innerJoin(leads, eq(reminders.lead_id, leads.id))
    .where(
      and(
        lte(reminders.due_date, now),
        isNull(reminders.completed_at)
      )
    )
    .orderBy(reminders.due_date);
}

// Create activity and auto-update lead.last_contact_date
export async function createActivity(data: {
  lead_id: string;
  type: string;
  activity_date: Date;
  duration_minutes?: number;
  notes: string;
}) {
  const [activity] = await db
    .insert(activities)
    .values(data)
    .returning();

  // Auto-update lead.last_contact_date
  await db
    .update(leads)
    .set({ last_contact_date: data.activity_date, updated_at: new Date() })
    .where(eq(leads.id, data.lead_id));

  return activity;
}

// Update lead stage
export async function updateLeadStage(leadId: string, stage: string) {
  const [updated] = await db
    .update(leads)
    .set({ status: stage, updated_at: new Date() })
    .where(eq(leads.id, leadId))
    .returning();

  return updated;
}

// Search leads by name, email, or company
export async function searchLeads(query: string) {
  return await db
    .select()
    .from(leads)
    .where(
      or(
        ilike(leads.name, `%${query}%`),
        ilike(leads.email, `%${query}%`),
        ilike(leads.company, `%${query}%`)
      )
    )
    .orderBy(desc(leads.updated_at));
}

These skeleton implementations provide the foundation. Phase 10 Tasks 2-3 will flesh out UI integration and form handling. npm run build 2>&1 | grep -c "error" | grep -q 0 && echo "Validators compile" Lead validators and service layer created. Schemas handle stage/type enums, activity logging, and reminder queries. Query layer is paginated for scalability. npm run build passes.

<threat_model>

Trust Boundaries

Boundary	Description
Admin → Lead Data	Session-authenticated via Auth.js; admin actions return full lead details and activity history
Lead ID references	All lead operations (activities, reminders) checked via lead_id FK; cascade delete prevents orphaned records
Activity date validation	Activity dates must be <= current date (no future backdating); server-side timestamp immutability

STRIDE Threat Register

Threat ID	Category	Component	Disposition	Mitigation Plan
T-10-01	Tampering	Activity history modification	mitigate	Activity records immutable after creation; no update endpoint; deletion requires admin auth + audit log
T-10-02	Spoofing	Lead creation with fake company/contact info	mitigate	Email/phone not validated until win (Phase 11); accept unverified input at lead stage; verify on quote accept
T-10-03	Information Disclosure	Leaking lead contact details (email, phone) via API	mitigate	Lead queries require Auth.js session; no public API for leads; admin-only access
T-10-04	Denial of Service	Bulk activity creation spam	mitigate	No rate limit in Phase 10 MVP; add rate limiting per admin session if needed (Phase 12)
T-10-05	Elevation	Non-admin user updating lead stage	mitigate	All lead mutations require Auth.js session; middleware guards /admin/leads routes
T-10-06	Tampering	Backdating activities (future activity_date)	accept	No validation in Phase 10; assume admin is honest; add validation if abuse occurs

</threat_model>

After Phase 10 Plan 1 execution:

1. Schema compiles: npm run build passes with no TS errors
2. Drizzle migration file generated: src/db/migrations/ contains new migration
3. Lead validators import cleanly: import { createLeadSchema, LEAD_STAGES } from '@/lib/lead-validators' works
4. Query layer exports all functions: getLeadById, getActivityLog, createActivity, updateLeadStage, etc.
5. Relations updated: leads → activities (cascade), leads → reminders (cascade), quotes → leads (optional FK)

Data safety check:

• Placeholder leads table expanded: NOT deleted, just new columns added
• New activities/reminders tables created from scratch: no existing data at risk
• quotes table lead_id FK already present from Phase 8: no schema conflict

<success_criteria>

• leads, activities, reminders tables properly defined with all CRM fields
• TypeScript compiles cleanly; Lead/Activity/Reminder types exported
• Drizzle migration file generated (not yet applied to DB)
• Lead validators (Zod) handle stage enums, activity types, date validation
• Query layer provides CRUD + search operations for leads, activities, reminders
• Relations properly configured: leads ← activities, leads ← reminders, leads ← quotes
• Database schema is ready for Phase 10 UI (pipeline view, activity log, follow-up widget) </success_criteria>

After execution, create `.planning/phases/10-crm-pipeline-activity-logging/10-01-SUMMARY.md`