clienthub/.planning/milestones/v2.0-phases/09-quote-builder-client-acceptance/09-01-PLAN.md

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

phase	plan	type	wave	depends_on	files_modified	autonomous	requirements
09	01	execute	0		src/db/schema.ts src/lib/quote-validators.ts src/lib/quote-service.ts	true	QUOTE-01 QUOTE-02 QUOTE-03 QUOTE-04 QUOTE-05

Phase 8 Foundation: Add `offer_phases`, `quotes`, and `quote_items` tables to the database schema. Establish immutability constraints and query layer patterns for public/admin separation.

Purpose: Create the schema foundation required by Phase 9 (Quote Builder UI) and Phase 10 (CRM) phases. These tables enable quote generation, public token-gated access, and acceptance tracking with immutable accepted_at timestamps.

Output:

• offer_phases table (phases within an offer_micro, e.g., Discovery → Strategy → Execution)
• quotes table (quote header with token, client, offer, total, state, accepted_at)
• Updated quote_items table (per Phase 8 schema, tracks line items per quote)
• TypeScript types and query layer (PublicQuoteView, safe queries that filter quote_items)
• Database migrations (drizzle-kit push)

<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 @.planning/phases/09-quote-builder-client-acceptance/09-RESEARCH.md Task 1: Add offer_phases, quotes, quote_items tables to schema src/db/schema.ts Add three new tables to the Drizzle schema after the offer_services section (after line 256):

1. offer_phases table (hierarchical phases within an offer_micro):

   • id: text PK, nanoid
   • micro_id: FK to offer_micros (cascade delete)
   • title: text, required (e.g., "Discovery", "Strategy", "Execution")
   • description: text, optional
   • sort_order: integer, default 0
   • created_at: timestamp, defaultNow

2. quotes table (quote header, one per admin-created quote):

   • id: text PK, nanoid
   • client_id: FK to clients (cascade delete) — nullable for CRM leads in Phase 10
   • offer_micro_id: FK to offer_micros (restrict delete) — which offer was quoted
   • token: text UNIQUE NOT NULL — nanoid 21 char, use nanoid() default (NOT the clients.token, separate token)
   • state: text, default "draft" (draft | sent | viewed | accepted | rejected)
   • accepted_total: numeric(10,2) NOT NULL — immutable snapshot of agreed-upon total
   • accepted_at: timestamp nullable — immutable once set; NULL means not yet accepted
   • client_email: text nullable — captured on accept
   • client_notes: text nullable — captured on accept
   • created_at: timestamp, defaultNow
   • updated_at: timestamp, defaultNow (track state transitions, NOT price changes)

3. quote_items table (updated from previous schema):

   • id: text PK, nanoid
   • quote_id: FK to quotes (cascade delete) — which quote owns this item
   • offer_phase_id: FK to offer_phases (restrict delete) — which phase this service is in
   • service_id: FK to services (restrict) — nullable for custom items
   • quantity: numeric(10,2) NOT NULL
   • unit_price: numeric(10,2) NOT NULL — snapshot of service price at quote creation time
   • subtotal: numeric(10,2) NOT NULL
   • custom_label: text nullable — for custom items without catalog entry
   • created_at: timestamp, defaultNow

Update relations:

• Add quotesRelations: one client (nullable), one offer_micro, many quote_items
• Add quoteItemsRelations: one quote, one offer_phase, one service (nullable)
• Add offerPhasesRelations: one micro, many quote_items
• Remove old quote_items→project relation (Phase 1-8 used projects; Phase 9+ uses quotes)

Add TypeScript types at the end:

• export type OfferPhase = typeof offer_phases.$inferSelect
• export type NewOfferPhase = typeof offer_phases.$inferInsert
• export type Quote = typeof quotes.$inferSelect
• export type NewQuote = typeof quotes.$inferInsert

Note: quote_items stays as is but now fk quote_id and offer_phase_id instead of project_id. Previous quote_items (if any exist via Phase 3 quote builder) remain tied to projects; new quote_items use the quotes table. Phase 9 does NOT migrate old quote_items. npm run build 2>&1 | grep -c "error" | grep -q 0 && echo "TypeScript compile pass" Schema file updated with 3 new tables, relations added, types exported. npm run build passes. No old quote_items data touched — backward compatible.

Task 2: Create Drizzle migration and validate schema src/db/migrations Run drizzle-kit to auto-generate migration files:

npx drizzle-kit generate --name=phase-8-quotes-and-phases

This creates a new migration file in src/db/migrations/ with SQL for the three new tables. The migration is NOT applied yet (Phase 9 execution will push to DB).

Validate the generated migration:

• Ensure quotes.token has UNIQUE constraint
• Ensure quotes.accepted_at is nullable (not NOT NULL)
• Ensure offer_phases.micro_id has CASCADE delete
• Ensure quote_items columns renamed from project_id to quote_id + offer_phase_id

If migration looks incorrect, manually edit the SQL in src/db/migrations/{migration_file}.sql to fix. ls src/db/migrations/ | grep phase-8 | head -1 Migration file generated and located in src/db/migrations/. SQL is syntactically valid and matches schema.

Task 3: Add quote validators (Zod schemas) and public query layer src/lib/quote-validators.ts, src/lib/quote-service.ts Create two new library files:

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

import { z } from "zod";

// Quote creation (admin builder)
export const createQuoteSchema = z.object({
  client_id: z.string().min(1, "Cliente richiesto"),
  offer_micro_id: z.string().min(1, "Offerta richiesta"),
  accepted_total: z.string().regex(/^\d+(\.\d{1,2})?$/, "Formato prezzo invalido"),
});

// Quote accept (public page, Step 3)
export const acceptQuoteSchema = z.object({
  token: z.string().length(21, "Token invalido"),
  email: z.string().email("Email valida").optional().or(z.literal("")),
  notes: z.string().max(500, "Max 500 caratteri").optional().or(z.literal("")),
});

// Quote step validation (public page Steps 1-2)
export const quoteStep2Schema = z.object({
  tier: z.enum(["A", "B", "C"]).optional(),
  priceOverrides: z.record(z.string(), z.number().min(0)).optional(),
});

src/lib/quote-service.ts — Query layer for quotes:

import { eq, and, isNull } from "drizzle-orm";
import { db } from "@/db";
import { quotes, quote_items, offer_phases, offer_micros } from "@/db/schema";

// Public view: safe for client API (NO line item prices exposed)
export type PublicQuoteView = {
  id: string;
  token: string;
  state: "draft" | "sent" | "viewed" | "accepted" | "rejected";
  accepted_total: string;
  accepted_at: Date | null;
  offerName: string;
  phaseSummary: Array<{
    id: string;
    title: string;
    serviceCount: number;
  }>;
};

// Get quote for public page (token-gated, no line items)
export async function getQuoteByToken(token: string): Promise<PublicQuoteView | null> {
  const [quote] = await db
    .select({
      id: quotes.id,
      token: quotes.token,
      state: quotes.state,
      accepted_total: quotes.accepted_total,
      accepted_at: quotes.accepted_at,
    })
    .from(quotes)
    .where(eq(quotes.token, token))
    .limit(1);

  if (!quote) return null;

  // Fetch offer name and phase summary (separately to avoid line items)
  // Phase 9 will wire this up — for now, return skeleton
  return {
    ...quote,
    offerName: "", // fetch from offer_micros via quote.offer_micro_id
    phaseSummary: [], // fetch from offer_phases, count items per phase
  };
}

// Get quote for admin (includes line items and full data)
export async function getQuoteByTokenAdmin(token: string) {
  const quote = await db.query.quotes.findFirst({
    where: eq(quotes.token, token),
  });

  if (!quote) return null;

  const items = await db
    .select()
    .from(quote_items)
    .where(eq(quote_items.quote_id, quote.id));

  return {
    ...quote,
    items,
  };
}

// Check if quote is already accepted (immutability guard)
export async function isQuoteAccepted(token: string): Promise<boolean> {
  const [quote] = await db
    .select({ accepted_at: quotes.accepted_at })
    .from(quotes)
    .where(and(eq(quotes.token, token), isNull(quotes.accepted_at).negate()))
    .limit(1);

  return !!quote;
}

These are skeleton implementations. Phase 9 will flesh out the offer/phase summary logic. npm run build 2>&1 | grep -c "error" | grep -q 0 && echo "Validators compile" Quote validators and service layer created. Schemas handle email/notes validation per WCAG. Public query layer explicitly excludes quote_items. npm run build passes.

<threat_model>

Trust Boundaries

Boundary	Description
Client → API	Token-gated route; unauthenticated quote access via unique token
Admin → API	Session-authenticated via Auth.js; admin actions return full quote data
Public Page → Database	Quote read via token; immutability enforced via DB constraint

STRIDE Threat Register

Threat ID	Category	Component	Disposition	Mitigation Plan
T-09-01	Spoofing	Token guessing / brute force	mitigate	Nanoid 21-char tokens (122-bit entropy); rate limit 3 views/min per IP via middleware (Phase 9 Task 4)
T-09-02	Tampering	Quote price manipulation (client-side total)	mitigate	Server-side recalculation in acceptQuote action (Phase 9 Task 6); reject if mismatch with accepted_total
T-09-03	Tampering	Quote accepted twice	mitigate	Database constraint: accepted_at IS NOT NULL prevents re-accept; server action checks before update (Phase 9 Task 6)
T-09-04	Information Disclosure	quote_items exposure via public API	mitigate	Query layer separation: getQuoteByToken returns PublicQuoteView (no line items); admin uses getQuoteByTokenAdmin (Phase 8 complete, enforced in Phase 9)
T-09-05	Information Disclosure	Quote token leaked in logs	mitigate	Never log full tokens; log only last 4 chars for debugging
T-09-06	Denial of Service	Email capture spam	mitigate	Email field optional; rate limit public page (3 views/min)
T-09-07	Elevation	Unauth user marks quote as accepted	mitigate	Token validation required; nanoid unguessable; middleware prevents brute force (Phase 9 Task 4)

</threat_model>

After Phase 8 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. Quote validators import cleanly: import { createQuoteSchema } from '@/lib/quote-validators' works
4. Public query layer excludes line items: PublicQuoteView type has no quote_items field
5. Relations updated: No circular dependencies; offer_phases and quote_items properly FK'd

Data safety check:

• Old quote_items (if any from Phase 3 quote builder) still tied to projects: NOT touched
• New quote_items will use quotes table: backward compatible
• No migrations have been pushed to DB yet; Phase 9 execution will apply schema

<success_criteria>

• offer_phases, quotes, quote_items tables defined in Drizzle schema
• TypeScript compiles cleanly; Quote/OfferPhase types exported
• Drizzle migration file generated (not yet applied to DB)
• Quote validators (Zod) handle email, notes, token, accepted_total
• Public quote query layer defined; explicitly excludes line items
• Backward compatible: old quote_items from Phase 3 remain functional
• Database schema is ready for Phase 9 UI and routes </success_criteria>

After execution, create `.planning/phases/09-quote-builder-client-acceptance/09-01-SUMMARY.md`