clienthub/.planning/milestones/v1.0-phases/05-offer-system/05-01-PLAN.md

---
plan_id: 05-01
phase: 5
wave: 1
title: "Schema migration — 5 new offer tables + Drizzle relations"
type: execute
depends_on: []
files_modified:
  - src/db/schema.ts
requirements_addressed: [OFFER-01, OFFER-02, OFFER-03, OFFER-04]
autonomous: true
must_haves:
  truths:
    - "Five new tables exist in the database: offer_macros, offer_micros, offer_services, offer_micro_services, project_offers"
    - "No existing table (clients, projects, payments, phases) is modified, dropped, or truncated"
    - "Drizzle relations are defined for all new tables"
    - "TypeScript types are exported for all new tables"
  artifacts:
    - path: "src/db/schema.ts"
      provides: "Five new pgTable definitions appended after existing tables"
      contains: "offer_macros, offer_micros, offer_services, offer_micro_services, project_offers"
  key_links:
    - from: "src/db/schema.ts"
      to: "Postgres DB"
      via: "npx drizzle-kit push"
      pattern: "offer_macros|offer_micros|offer_services|offer_micro_services|project_offers"
---

<objective>
Add five new tables for the Offer System to the Drizzle schema and push to the database.

Purpose: Every subsequent plan in Phase 5 reads from or writes to these tables. This plan is the blocking dependency for all other Phase 5 work.
Output: Updated `src/db/schema.ts` with five new table definitions, Drizzle relations, exported TypeScript types, and a successful `drizzle-kit push`.
</objective>

<execution_context>
@/Users/simonecavalli/IAMCAVALLI/.claude/get-shit-done/workflows/execute-plan.md
@/Users/simonecavalli/IAMCAVALLI/.claude/get-shit-done/templates/summary.md
</execution_context>

<context>
@/Users/simonecavalli/IAMCAVALLI/.planning/ROADMAP.md
@/Users/simonecavalli/IAMCAVALLI/.planning/STATE.md
@/Users/simonecavalli/IAMCAVALLI/.planning/phases/05-offer-system/05-RESEARCH.md
</context>

<tasks>

<task type="auto">
  <name>Task 1: Append five new table definitions and relations to schema.ts</name>
  <files>src/db/schema.ts</files>

  <read_first>
    - src/db/schema.ts — read the FULL file before touching it (existing tables, import block, relations block, TypeScript types block)
  </read_first>

  <action>
Open `src/db/schema.ts`. At the top of the file, the existing import from `"drizzle-orm/pg-core"` is:

```typescript
import {
  pgTable,
  text,
  integer,
  numeric,
  timestamp,
  boolean,
} from "drizzle-orm/pg-core";
```

Add `primaryKey, date` to this import (needed for `offer_micro_services` composite PK and `project_offers.start_date`).

Append the following five table definitions AFTER the `settings` table definition and BEFORE the `// ============ RELATIONS ============` section. Use the exact column names from the research (not the phase_scope variant — research is authoritative):

```typescript
// ============ OFFER MACROS ============
export const offer_macros = pgTable("offer_macros", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  internal_name: text("internal_name").notNull(),
  public_name: text("public_name").notNull(),
  transformation_promise: text("transformation_promise"),
  sort_order: integer("sort_order").notNull().default(0),
  created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});

// ============ OFFER MICROS ============
export const offer_micros = pgTable("offer_micros", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  macro_id: text("macro_id")
    .notNull()
    .references(() => offer_macros.id, { onDelete: "cascade" }),
  internal_name: text("internal_name").notNull(),
  public_name: text("public_name").notNull(),
  transformation_promise: text("transformation_promise"),
  duration_months: integer("duration_months").notNull().default(1),
  sort_order: integer("sort_order").notNull().default(0),
});

// ============ OFFER SERVICES (distinct from service_catalog — marketing/transformation semantics) ============
export const offer_services = pgTable("offer_services", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  name: text("name").notNull(),
  price: numeric("price", { precision: 10, scale: 2 }).notNull(),
  transformation_description: text("transformation_description"),
  active: boolean("active").notNull().default(true),
});

// ============ OFFER MICRO SERVICES (junction: offer_micros <-> offer_services) ============
export const offer_micro_services = pgTable(
  "offer_micro_services",
  {
    micro_id: text("micro_id")
      .notNull()
      .references(() => offer_micros.id, { onDelete: "cascade" }),
    service_id: text("service_id")
      .notNull()
      .references(() => offer_services.id, { onDelete: "cascade" }),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.micro_id, t.service_id] }),
  })
);

// ============ PROJECT OFFERS (assignment of micro-offer to project) ============
export const project_offers = pgTable("project_offers", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  project_id: text("project_id")
    .notNull()
    .references(() => projects.id, { onDelete: "cascade" }),
  micro_id: text("micro_id")
    .notNull()
    .references(() => offer_micros.id, { onDelete: "restrict" }),
  // NOT NULL with defaultNow — required for forecast computation (null start_date breaks forecast)
  start_date: timestamp("start_date", { withTimezone: true }).notNull().defaultNow(),
  // Offer-level accepted total — separate from projects.accepted_total (quote builder total)
  accepted_total: numeric("accepted_total", { precision: 10, scale: 2 }),
  created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});
```

Then add Drizzle relations at the end of the `// ============ RELATIONS ============` section (after `serviceCatalogRelations`):

```typescript
export const offerMacrosRelations = relations(offer_macros, ({ many }) => ({
  micros: many(offer_micros),
}));

export const offerMicrosRelations = relations(offer_micros, ({ one, many }) => ({
  macro: one(offer_macros, { fields: [offer_micros.macro_id], references: [offer_macros.id] }),
  services: many(offer_micro_services),
  projectOffers: many(project_offers),
}));

export const offerServicesRelations = relations(offer_services, ({ many }) => ({
  microAssignments: many(offer_micro_services),
}));

export const offerMicroServicesRelations = relations(offer_micro_services, ({ one }) => ({
  micro: one(offer_micros, { fields: [offer_micro_services.micro_id], references: [offer_micros.id] }),
  service: one(offer_services, { fields: [offer_micro_services.service_id], references: [offer_services.id] }),
}));

export const projectOffersRelations = relations(project_offers, ({ one }) => ({
  project: one(projects, { fields: [project_offers.project_id], references: [projects.id] }),
  micro: one(offer_micros, { fields: [project_offers.micro_id], references: [offer_micros.id] }),
}));
```

Also add the new tables to `projectsRelations`:
```typescript
// Add to the existing projectsRelations many() block:
projectOffers: many(project_offers),
```

Finally, append TypeScript types at the end of the `// ============ TYPESCRIPT TYPES ============` section:

```typescript
export type OfferMacro = typeof offer_macros.$inferSelect;
export type NewOfferMacro = typeof offer_macros.$inferInsert;
export type OfferMicro = typeof offer_micros.$inferSelect;
export type NewOfferMicro = typeof offer_micros.$inferInsert;
export type OfferService = typeof offer_services.$inferSelect;
export type NewOfferService = typeof offer_services.$inferInsert;
export type OfferMicroService = typeof offer_micro_services.$inferSelect;
export type NewOfferMicroService = typeof offer_micro_services.$inferInsert;
export type ProjectOffer = typeof project_offers.$inferSelect;
export type NewProjectOffer = typeof project_offers.$inferInsert;
```

**Data Safety check before push:** Confirm the migration only adds tables — grep the generated SQL for DROP or TRUNCATE before accepting.
  </action>

  <verify>
    <automated>cd /Users/simonecavalli/IAMCAVALLI && npx tsc --noEmit 2>&1 | head -20</automated>
  </verify>

  <acceptance_criteria>
    - `grep -c "offer_macros\|offer_micros\|offer_services\|offer_micro_services\|project_offers" src/db/schema.ts` returns 5 or more (table definitions present)
    - `npx tsc --noEmit` exits 0 (no TypeScript errors)
    - `grep "primaryKey" src/db/schema.ts` matches (composite PK import present)
    - `grep "onDelete.*restrict" src/db/schema.ts` matches (project_offers.micro_id FK is restrict, not cascade)
  </acceptance_criteria>

  <done>schema.ts compiles without errors; five new table definitions present; relations defined; TypeScript types exported</done>
</task>

<task type="auto">
  <name>Task 2: Run drizzle-kit push — create tables in database</name>
  <files>src/db/schema.ts</files>

  <read_first>
    - src/db/schema.ts — verify Task 1 output before pushing
    - .env (existence check only — do NOT log contents)
  </read_first>

  <action>
Run the migration command. Before accepting the push, read the SQL preview that drizzle-kit outputs and verify it contains only CREATE TABLE statements — NO DROP TABLE, NO ALTER TABLE DROP COLUMN, NO TRUNCATE.

```bash
npx drizzle-kit push
```

When drizzle-kit shows the diff, confirm only additions are shown. If any destructive statement appears, ABORT and report to the user immediately.

Expected tables created:
1. `offer_macros`
2. `offer_micros`
3. `offer_services`
4. `offer_micro_services` (composite PK: micro_id + service_id)
5. `project_offers`

If push fails with "relation does not exist", the definition order in schema.ts is wrong. Fix order: `offer_macros` → `offer_micros` → `offer_services` → `offer_micro_services` → `project_offers` (each references only tables defined before it).
  </action>

  <verify>
    <automated>npx tsc --noEmit 2>&1 | head -5</automated>
  </verify>

  <acceptance_criteria>
    - `npx drizzle-kit push` exits without error
    - No DROP TABLE or TRUNCATE appears in the migration output
    - All five table names appear in the drizzle-kit push output as CREATE TABLE operations
  </acceptance_criteria>

  <done>All five tables created in database; no existing data modified</done>
</task>

</tasks>

<threat_model>
## Trust Boundaries

| Boundary | Description |
|----------|-------------|
| schema.ts → DB | Schema changes are applied via drizzle-kit push — only additive changes allowed (CLAUDE.md Data Safety) |

## STRIDE Threat Register

| Threat ID | Category | Component | Disposition | Mitigation Plan |
|-----------|----------|-----------|-------------|-----------------|
| T-05-01 | Tampering | drizzle-kit push | mitigate | Read migration preview before accepting; abort if DROP TABLE or TRUNCATE appears |
| T-05-02 | Tampering | project_offers.micro_id FK | mitigate | Use `onDelete: "restrict"` — prevents silent history destruction when a micro-offer is deleted while assigned to projects |
</threat_model>

<verification>
After both tasks complete:
- `grep -c "offer_macros" src/db/schema.ts` → at least 3 (table def + relation + type)
- `grep "primaryKey" src/db/schema.ts` → matches (composite PK import added)
- `npx tsc --noEmit` → exits 0
- DB tables exist (confirmed by drizzle-kit push output)
</verification>

<success_criteria>
1. Five new tables in database: offer_macros, offer_micros, offer_services, offer_micro_services, project_offers
2. Zero modifications to existing tables (clients, projects, payments, phases untouched)
3. TypeScript compiles without errors
4. Drizzle relations defined for all five tables
</success_criteria>

<output>
After completion, create `.planning/phases/05-offer-system/05-01-SUMMARY.md` using the summary template.
</output>