clienthub/.planning/milestones/v2.0-phases/07-unified-service-catalog/07-01-PLAN.md

---
phase: "07-unified-service-catalog"
plan: 01
type: execute
wave: 1
depends_on: []
files_modified:
  - src/db/schema.ts
  - scripts/migrate-services.ts
  - scripts/validate-services-migration.ts
autonomous: true
requirements:
  - CAT-U-01

must_haves:
  truths:
    - "A single `services` table exists in Postgres with columns: id, name, description, unit_price, category, active, migrated_from, migrated_id, created_at"
    - "Every active row from service_catalog (21 rows) and offer_services (35 rows) has a corresponding row in services with migrated_from + migrated_id set"
    - "Name collisions between the two source tables are resolved by suffixing — no two services rows silently overwrite each other"
    - "service_catalog and offer_services tables are NOT dropped or truncated — old data remains queryable for rollback"
    - "A validation script proves zero data loss: row counts match (21 + 35 = 56 services rows minimum) and no orphaned references exist"
  artifacts:
    - path: "src/db/schema.ts"
      provides: "New `services` pgTable definition + relations + Service/NewService types"
      contains: "export const services = pgTable(\"services\""
    - path: "scripts/migrate-services.ts"
      provides: "Idempotent backfill script: service_catalog + offer_services -> services with dedup"
      contains: "migrated_from"
    - path: "scripts/validate-services-migration.ts"
      provides: "Post-migration validation: row count checks, orphan checks, name collision report"
      contains: "SELECT COUNT"
  key_links:
    - from: "scripts/migrate-services.ts"
      to: "services.migrated_from / services.migrated_id"
      via: "INSERT ... migrated_from='service_catalog'|'offer_services', migrated_id=<old.id>"
      pattern: "migrated_from"
    - from: "src/db/schema.ts services table"
      to: "Postgres production DB (Coolify)"
      via: "drizzle-kit push (additive only — no drop statements)"
      pattern: "export const services = pgTable"
---

<objective>
Create the unified `services` table (per ARCHITECTURE.md "NEW: services Table") as an ADDITIVE schema change, then backfill it from both `service_catalog` (21 rows, operational pricing used by quote_items) and `offer_services` (35 rows, marketing pricing used by offer_micro_services) using the expand-phase pattern from PITFALLS_V2.md Pitfall 1.

Purpose: Establish the single source of truth for service pricing without touching `service_catalog`, `offer_services`, `quote_items`, or `offer_micro_services` — those keep working unchanged. This is the "Expand" half of expand-contract; Phase 8 will build `offer_phase_services` against the new `services` table and progressively retire the old junction tables. Per Data Safety (LOCKED): no migration in this plan drops or truncates `clients`, `projects`, `payments`, `phases`, `service_catalog`, or `offer_services`.

Output: `services` table live in production Postgres, fully backfilled with audit trail (`migrated_from`, `migrated_id`), validated against zero data loss with a checksum/row-count script.
</objective>

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

<context>
@.planning/PROJECT.md
@.planning/ROADMAP.md
@.planning/STATE.md
@.planning/research/ARCHITECTURE.md
@.planning/research/PITFALLS_V2.md

<interfaces>
<!-- Current src/db/schema.ts — relevant existing tables this plan reads from but does NOT modify -->

From src/db/schema.ts (service_catalog — 21 rows, used by quote_items.service_id):
```typescript
export const service_catalog = pgTable("service_catalog", {
  id: text("id").primaryKey().$defaultFn(() => nanoid()),
  name: text("name").notNull(),
  description: text("description"),
  unit_price: numeric("unit_price", { precision: 10, scale: 2 }).notNull(),
  active: boolean("active").notNull().default(true),
});
```

From src/db/schema.ts (offer_services — 35 rows, used by offer_micro_services junction):
```typescript
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),
});
```

From src/db/index.ts (db client — used identically in scripts/seed.ts):
```typescript
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
const client = postgres(process.env.DATABASE_URL ?? "postgres://build-placeholder");
export const db = drizzle(client);
```

Target shape for the new table (from ARCHITECTURE.md "NEW: services Table"):
```typescript
export const services = pgTable("services", {
  id: text("id").primaryKey().$defaultFn(() => nanoid()),
  name: text("name").notNull(),
  description: text("description"),
  unit_price: numeric("unit_price", { precision: 10, scale: 2 }).notNull(),
  category: text("category"),
  active: boolean("active").notNull().default(true),
  migrated_from: text("migrated_from"),  // "service_catalog" | "offer_services" | null
  migrated_id: text("migrated_id"),      // original row id from source table
  created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});
```
</interfaces>
</context>

<tasks>

<task type="auto">
  <name>Task 1: Add `services` table to schema.ts and push additive migration to Postgres</name>
  <files>
    src/db/schema.ts
  </files>
  <action>
    In `src/db/schema.ts`, add the new `services` pgTable definition immediately after the `service_catalog` table definition (around line 173), per ARCHITECTURE.md "NEW: services Table (Unified Catalog)":

    ```typescript
    // ============ SERVICES (UNIFIED CATALOG — replaces service_catalog + offer_services) ============
    // migrated_from/migrated_id provide audit trail for safe rollback during the
    // expand-contract migration (Phase 7 expand; Phase 8 begins contract on offer side).
    export const services = pgTable("services", {
      id: text("id")
        .primaryKey()
        .$defaultFn(() => nanoid()),
      name: text("name").notNull(),
      description: text("description"),
      unit_price: numeric("unit_price", { precision: 10, scale: 2 }).notNull(),
      category: text("category"),
      active: boolean("active").notNull().default(true),
      migrated_from: text("migrated_from"),   // "service_catalog" | "offer_services" | null (new rows after Phase 7)
      migrated_id: text("migrated_id"),       // original id from source table, null for new rows
      created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
    });

    export const servicesRelations = relations(services, (_) => ({
      // No FK relations yet in Phase 7 — quote_items and offer_micro_services
      // continue to reference service_catalog/offer_services until Phase 8.
    }));
    ```

    Add TypeScript types in the "TYPESCRIPT TYPES" section at the bottom of the file, near `ServiceCatalog`:
    ```typescript
    export type Service = typeof services.$inferSelect;
    export type NewService = typeof services.$inferInsert;
    ```

    Do NOT modify `service_catalog`, `offer_services`, `quote_items`, `offer_micro_services`, or any other existing table. Do NOT add foreign keys from `services` to anything yet — this is a standalone additive table.

    Push the schema change to production Postgres on Coolify using the same SSH-based `drizzle-kit push` pattern used in Phase 5 (commit d670d49 / 1b0b2ea). Run:
    ```bash
    npx drizzle-kit push
    ```
    Confirm the prompt shows ONLY a new table creation (`services`) with no ALTER/DROP statements on existing tables. If drizzle-kit proposes anything beyond `CREATE TABLE services` (e.g., proposes dropping or renaming an unrelated column), STOP and report — do not proceed with an unexpected diff.
  </action>
  <verify>
    <automated>grep -c "export const services = pgTable(\"services\"" src/db/schema.ts | grep -q "^1$" && echo "services table defined"</automated>
    <automated>grep -q "export type Service = typeof services" src/db/schema.ts && echo "Service type exported"</automated>
    <automated>grep -q "migrated_from: text(\"migrated_from\")" src/db/schema.ts && echo "audit trail columns present"</automated>
    <automated>npm run build 2>&1 | grep -v "warning" | grep -qi "error" && echo "BUILD ERRORS" || echo "TypeScript OK"</automated>
  </verify>
  <done>
    - `services` table defined in src/db/schema.ts with all columns from ARCHITECTURE.md spec including migrated_from/migrated_id audit columns
    - `Service` and `NewService` types exported
    - `service_catalog`, `offer_services`, `quote_items`, `offer_micro_services` definitions unchanged (verify with `git diff src/db/schema.ts` shows only additions)
    - drizzle-kit push applied to production DB — `services` table exists in Postgres, confirmed via psql `\d services` or equivalent
    - npm run build passes
  </done>
</task>

<task type="auto" tdd="false">
  <name>Task 2: Write and run the backfill script (service_catalog + offer_services -> services) with dedup</name>
  <files>
    scripts/migrate-services.ts
  </files>
  <behavior>
    - Migrating an empty `services` table: inserts 21 rows from service_catalog (migrated_from='service_catalog') + 35 rows from offer_services (migrated_from='offer_services') = 56 total rows
    - Name collision (same `name` exists in both source tables): the offer_services row is inserted with `name` suffixed as `"<name> (Offer)"` so both rows survive distinctly — per PITFALLS_V2.md Pitfall 1 prevention #2
    - Re-running the script on an already-migrated `services` table is a no-op (idempotent — skips rows where migrated_from+migrated_id pair already exists)
    - service_catalog.description maps to services.description; offer_services.transformation_description maps to services.description (offer side has no separate description field)
    - service_catalog.unit_price maps to services.unit_price; offer_services.price maps to services.unit_price
    - active flag is copied verbatim from each source row
  </behavior>
  <action>
    Create `scripts/migrate-services.ts` following the pattern of `scripts/seed.ts` (imports `db` from `@/db`, imports tables from `@/db/schema`, runs as a standalone script via `npx tsx scripts/migrate-services.ts`).

    Implementation outline:

    ```typescript
    import { db } from "@/db";
    import { service_catalog, offer_services, services } from "@/db/schema";
    import { eq, and } from "drizzle-orm";

    async function migrate() {
      console.log("Starting services unification migration...\n");

      // 1. Backfill from service_catalog (operational pricing, used by quote_items)
      const catalogRows = await db.select().from(service_catalog);
      let catalogInserted = 0;
      let catalogSkipped = 0;

      for (const row of catalogRows) {
        const existing = await db
          .select()
          .from(services)
          .where(and(eq(services.migrated_from, "service_catalog"), eq(services.migrated_id, row.id)))
          .limit(1);

        if (existing.length > 0) {
          catalogSkipped++;
          continue;
        }

        await db.insert(services).values({
          name: row.name,
          description: row.description,
          unit_price: row.unit_price,
          category: "catalog",
          active: row.active,
          migrated_from: "service_catalog",
          migrated_id: row.id,
        });
        catalogInserted++;
      }
      console.log(`service_catalog: ${catalogInserted} inserted, ${catalogSkipped} skipped (already migrated)`);

      // 2. Backfill from offer_services (marketing pricing, used by offer_micro_services)
      const offerRows = await db.select().from(offer_services);
      let offerInserted = 0;
      let offerSkipped = 0;
      let renamed = 0;

      for (const row of offerRows) {
        const existing = await db
          .select()
          .from(services)
          .where(and(eq(services.migrated_from, "offer_services"), eq(services.migrated_id, row.id)))
          .limit(1);

        if (existing.length > 0) {
          offerSkipped++;
          continue;
        }

        // Name collision check against ALL services rows inserted so far (both sources)
        const collision = await db
          .select()
          .from(services)
          .where(eq(services.name, row.name))
          .limit(1);

        const finalName = collision.length > 0 ? `${row.name} (Offer)` : row.name;
        if (collision.length > 0) renamed++;

        await db.insert(services).values({
          name: finalName,
          description: row.transformation_description,
          unit_price: row.price,
          category: "offer",
          active: row.active,
          migrated_from: "offer_services",
          migrated_id: row.id,
        });
        offerInserted++;
      }
      console.log(`offer_services: ${offerInserted} inserted, ${offerSkipped} skipped (already migrated), ${renamed} renamed for collision`);

      console.log("\nMigration complete. Run scripts/validate-services-migration.ts next.");
      process.exit(0);
    }

    migrate().catch((err) => {
      console.error("Migration failed:", err);
      process.exit(1);
    });
    ```

    Run the script against production:
    ```bash
    npx tsx scripts/migrate-services.ts
    ```

    Report the printed counts (inserted/skipped/renamed for each source table).
  </action>
  <verify>
    <automated>test -f scripts/migrate-services.ts && grep -q "migrated_from" scripts/migrate-services.ts && echo "migration script exists"</automated>
    <automated>grep -q "(Offer)" scripts/migrate-services.ts && echo "collision suffix logic present"</automated>
    <automated>grep -q "and(eq(services.migrated_from" scripts/migrate-services.ts && echo "idempotency check present"</automated>
  </verify>
  <done>
    - scripts/migrate-services.ts exists, follows seed.ts conventions (imports db from @/db)
    - Script executed successfully against production DB
    - services table contains >= 56 rows (21 from service_catalog + 35 from offer_services), each with migrated_from + migrated_id set
    - Any name collisions resolved via "(Offer)" suffix — verified by printed `renamed` count in script output
    - Re-running the script is a no-op (idempotent skip path exercised)
  </done>
</task>

<task type="auto">
  <name>Task 3: Write and run the validation script proving zero data loss</name>
  <files>
    scripts/validate-services-migration.ts
  </files>
  <action>
    Create `scripts/validate-services-migration.ts`, following the same standalone-script pattern. It must run these checks and print PASS/FAIL for each:

    ```typescript
    import { db } from "@/db";
    import { service_catalog, offer_services, services, quote_items, offer_micro_services } from "@/db/schema";
    import { sql, eq } from "drizzle-orm";

    async function validate() {
      let failures = 0;

      // Check 1: row counts match
      const [catalogCount] = await db.select({ n: sql<number>`count(*)::int` }).from(service_catalog);
      const [offerCount] = await db.select({ n: sql<number>`count(*)::int` }).from(offer_services);
      const [migratedCatalog] = await db
        .select({ n: sql<number>`count(*)::int` })
        .from(services)
        .where(eq(services.migrated_from, "service_catalog"));
      const [migratedOffer] = await db
        .select({ n: sql<number>`count(*)::int` })
        .from(services)
        .where(eq(services.migrated_from, "offer_services"));

      console.log(`service_catalog rows: ${catalogCount.n} | migrated: ${migratedCatalog.n}`);
      console.log(`offer_services rows: ${offerCount.n} | migrated: ${migratedOffer.n}`);

      if (catalogCount.n !== migratedCatalog.n) {
        console.log("FAIL: service_catalog row count does not match migrated count");
        failures++;
      } else {
        console.log("PASS: service_catalog fully migrated");
      }

      if (offerCount.n !== migratedOffer.n) {
        console.log("FAIL: offer_services row count does not match migrated count");
        failures++;
      } else {
        console.log("PASS: offer_services fully migrated");
      }

      // Check 2: no orphaned migrated_id (every migrated_id from service_catalog still exists in service_catalog)
      const orphanedCatalog = await db.execute(sql`
        SELECT COUNT(*)::int AS n FROM services s
        WHERE s.migrated_from = 'service_catalog'
        AND NOT EXISTS (SELECT 1 FROM service_catalog sc WHERE sc.id = s.migrated_id)
      `);
      const orphanedCatalogCount = (orphanedCatalog as unknown as Array<{ n: number }>)[0]?.n ?? 0;
      if (orphanedCatalogCount > 0) {
        console.log(`FAIL: ${orphanedCatalogCount} services rows reference missing service_catalog ids`);
        failures++;
      } else {
        console.log("PASS: no orphaned service_catalog references");
      }

      const orphanedOffer = await db.execute(sql`
        SELECT COUNT(*)::int AS n FROM services s
        WHERE s.migrated_from = 'offer_services'
        AND NOT EXISTS (SELECT 1 FROM offer_services os WHERE os.id = s.migrated_id)
      `);
      const orphanedOfferCount = (orphanedOffer as unknown as Array<{ n: number }>)[0]?.n ?? 0;
      if (orphanedOfferCount > 0) {
        console.log(`FAIL: ${orphanedOfferCount} services rows reference missing offer_services ids`);
        failures++;
      } else {
        console.log("PASS: no orphaned offer_services references");
      }

      // Check 3: existing quote_items.service_id still resolve in service_catalog (untouched FK — must remain valid)
      const orphanedQuoteItems = await db.execute(sql`
        SELECT COUNT(*)::int AS n FROM quote_items qi
        WHERE qi.service_id IS NOT NULL
        AND NOT EXISTS (SELECT 1 FROM service_catalog sc WHERE sc.id = qi.service_id)
      `);
      const orphanedQuoteItemsCount = (orphanedQuoteItems as unknown as Array<{ n: number }>)[0]?.n ?? 0;
      if (orphanedQuoteItemsCount > 0) {
        console.log(`FAIL: ${orphanedQuoteItemsCount} quote_items reference missing service_catalog ids (pre-existing FK broken!)`);
        failures++;
      } else {
        console.log("PASS: quote_items.service_id -> service_catalog FK intact (unchanged by this migration)");
      }

      // Check 4: existing offer_micro_services.service_id still resolve in offer_services (untouched FK — must remain valid)
      const orphanedMicroServices = await db.execute(sql`
        SELECT COUNT(*)::int AS n FROM offer_micro_services oms
        WHERE NOT EXISTS (SELECT 1 FROM offer_services os WHERE os.id = oms.service_id)
      `);
      const orphanedMicroServicesCount = (orphanedMicroServices as unknown as Array<{ n: number }>)[0]?.n ?? 0;
      if (orphanedMicroServicesCount > 0) {
        console.log(`FAIL: ${orphanedMicroServicesCount} offer_micro_services reference missing offer_services ids (pre-existing FK broken!)`);
        failures++;
      } else {
        console.log("PASS: offer_micro_services.service_id -> offer_services FK intact (unchanged by this migration)");
      }

      // Check 5: name collision report (informational)
      const collisions = await db.execute(sql`
        SELECT name, COUNT(*)::int AS n FROM services GROUP BY name HAVING COUNT(*) > 1
      `);
      const collisionRows = collisions as unknown as Array<{ name: string; n: number }>;
      if (collisionRows.length > 0) {
        console.log(`WARNING: ${collisionRows.length} duplicate names remain in services (review):`, collisionRows);
      } else {
        console.log("PASS: no duplicate names in services");
      }

      console.log(`\n${failures === 0 ? "ALL CHECKS PASSED" : `${failures} CHECK(S) FAILED`}`);
      process.exit(failures === 0 ? 0 : 1);
    }

    validate().catch((err) => {
      console.error("Validation failed:", err);
      process.exit(1);
    });
    ```

    Run:
    ```bash
    npx tsx scripts/validate-services-migration.ts
    ```

    All checks must print PASS. If any check prints FAIL, STOP — do not proceed to Plan 07-02 until resolved (do not drop/modify any table to "fix" a failure; investigate the migrate-services.ts logic instead since source tables must remain untouched).
  </action>
  <verify>
    <automated>test -f scripts/validate-services-migration.ts && echo "validation script exists"</automated>
    <automated>npx tsx scripts/validate-services-migration.ts 2>&1 | grep -q "ALL CHECKS PASSED" && echo "validation passed"</automated>
  </verify>
  <done>
    - scripts/validate-services-migration.ts exists and runs against production DB
    - All checks print PASS: row counts match, no orphaned migrated_id references, existing quote_items/offer_micro_services FKs remain intact (untouched by this migration)
    - Any name collisions are reported and resolved via "(Offer)" suffix (Check 5 shows zero unresolved duplicates)
    - Script exits 0
  </done>
</task>

</tasks>

<threat_model>
## Trust Boundaries

| Boundary | Description |
|----------|-------------|
| Migration script -> Production Postgres | Script runs with full DB credentials (DATABASE_URL); writes new rows only, must never DELETE/UPDATE/DROP on service_catalog or offer_services |
| drizzle-kit push -> Production schema | Schema diff applied directly to production (no staging DB available); diff must be additive-only |

## STRIDE Threat Register

| Threat ID | Category | Component | Disposition | Mitigation Plan |
|-----------|----------|-----------|-------------|-----------------|
| T-07-01 | Tampering | drizzle-kit push proposes unintended ALTER/DROP on existing tables | mitigate | Task 1 explicitly requires reviewing the push diff before confirming; abort if anything beyond `CREATE TABLE services` appears |
| T-07-02 | Repudiation | Migration cannot be traced back to source rows after running | mitigate | `migrated_from` + `migrated_id` columns on every backfilled row provide full audit trail for rollback |
| T-07-03 | Tampering | Re-running migrate-services.ts creates duplicate rows on retry | mitigate | Idempotency check via `migrated_from` + `migrated_id` lookup before each insert (Task 2 behavior spec) |
| T-07-04 | Information Disclosure | DATABASE_URL exposed in script output/logs | accept | Scripts use `process.env.DATABASE_URL` via existing `@/db` client; never logged; same pattern as scripts/seed.ts already in repo |
| T-07-05 | Denial of Service | Backfill script run against production during business hours locks tables | accept | 56 rows total — INSERT-only operations on a new empty table; no lock contention with existing read paths (service_catalog/offer_services untouched) |
</threat_model>

<verification>
After plan execution:
1. `npm run build` — no TypeScript errors
2. `npx drizzle-kit push` diff (re-run, should show "No changes detected" — confirms schema already applied)
3. `npx tsx scripts/validate-services-migration.ts` — prints "ALL CHECKS PASSED"
4. Manually inspect production DB: `SELECT migrated_from, COUNT(*) FROM services GROUP BY migrated_from` returns `service_catalog: 21, offer_services: 35` (or current row counts if they've changed since planning)
5. Confirm `service_catalog` and `offer_services` tables still exist with original row counts (no rows deleted)
</verification>

<success_criteria>
- `services` table exists in production Postgres with the full column set from ARCHITECTURE.md
- 100% of service_catalog and offer_services rows have a corresponding services row with migrated_from/migrated_id set
- Zero orphaned references; existing quote_items and offer_micro_services FKs remain valid (untouched)
- Name collisions resolved via deterministic suffixing, no silent overwrites
- service_catalog and offer_services tables remain in place, unmodified — rollback is possible by simply not using the new table
- npm run build passes
</success_criteria>

<output>
After completion, create `.planning/phases/07-unified-service-catalog/07-01-SUMMARY.md`
</output>