feat(db): add 0035_extend_pet_enums — adds missing coat_type (short/medium/silky) and pet_size_category (extra_large) values

GRO-1979: The coat_type and pet_size_category enums were captured in the
schema snapshot (0034_extend_pet_profile_columns) with their full value
sets, but the SQL migration 0034 only added new columns and never ran
ALTER TYPE ADD VALUE. The drizzle journal therefore had no entry for
these enum extensions, so they were never applied to UAT.

This new migration (0035) runs the ALTER TYPE ADD VALUE statements
(non-transactional per Postgres restriction) to register short, medium,
and silky in coat_type, and extra_large in pet_size_category.

The journal is also updated to include idx 35 for this migration.

Co-Authored-By: Paperclip <noreply@paperclip.ing>

packages/db/migrations/0035_extend_pet_enums.sql

@@ -0,0 +1,18 @@
+-- Migration: 0035_extend_pet_enums.sql
+-- GRO-1979: Adds missing enum values for coat_type (short/medium/silky) and
+-- pet_size_category (extra_large) that were captured in schema snapshots but
+-- never applied to the DB because 0034 did not include ALTER TYPE ADD VALUE.
+
+-- Postgres restriction: ALTER TYPE ADD VALUE cannot run inside a transaction block.
+-- We deliberately do NOT wrap this in BEGIN...COMMIT so drizzle can apply it
+-- as a standalone statement outside any implicit transaction.
+
+-- ─── Extend coat_type enum ────────────────────────────────────────────────
+
+ALTER TYPE "coat_type" ADD VALUE IF NOT EXISTS 'short';
+ALTER TYPE "coat_type" ADD VALUE IF NOT EXISTS 'medium';
+ALTER TYPE "coat_type" ADD VALUE IF NOT EXISTS 'silky';
+
+-- ─── Extend pet_size_category enum ────────────────────────────────────────
+
+ALTER TYPE "pet_size_category" ADD VALUE IF NOT EXISTS 'extra_large';

packages/db/migrations/meta/_journal.json

@@ -246,6 +246,13 @@
       "when": 1751140800000,
       "tag": "0034_extend_pet_profile_columns",
       "breakpoints": true
+    },
+    {
+      "idx": 35,
+      "version": "7",
+      "when": 1751300000000,
+      "tag": "0035_extend_pet_enums",
+      "breakpoints": true
     }
   ]
 }

packages/db/src/seed.ts

@@ -609,8 +609,45 @@ async function seedUatStaffAccounts(db: ReturnType<typeof drizzle>) {
       .from(schema.pets)
       .where(eq(schema.pets.id, pet.id))
       .limit(1);
+
     if (existing) {
-      console.log(`✓ UAT Pet '${existing.name}' already exists — skipping`);
+      // Upsert so extended fields are always populated on re-runs
+      await db.insert(schema.pets)
+        .values({
+          id: pet.id,
+          clientId: uatCustomerClientId,
+          name: pet.name,
+          species: pet.species,
+          breed: pet.breed,
+          weightKg: pet.weight,
+          dateOfBirth: new Date(`${pet.dob}T00:00:00Z`),
+          image: pet.image,
+          temperamentScore: randInt(1, 5),
+          temperamentFlags: pickN(temperamentFlagPool, randInt(1, 3)),
+          medicalAlerts: [],
+          preferredCuts: pickN(preferredCutPool, randInt(1, 2)),
+          coatType: pick(coatTypePool),
+          petSizeCategory: pick(petSizeCategoryPool),
+        })
+        .onConflictDoUpdate({
+          target: schema.pets.id,
+          set: {
+            clientId: uatCustomerClientId,
+            name: pet.name,
+            species: pet.species,
+            breed: pet.breed,
+            weightKg: pet.weight,
+            dateOfBirth: new Date(`${pet.dob}T00:00:00Z`),
+            image: pet.image,
+            temperamentScore: randInt(1, 5),
+            temperamentFlags: pickN(temperamentFlagPool, randInt(1, 3)),
+            medicalAlerts: [],
+            preferredCuts: pickN(preferredCutPool, randInt(1, 2)),
+            coatType: pick(coatTypePool),
+            petSizeCategory: pick(petSizeCategoryPool),
+          },
+        });
+      console.log(`✓ Upserted UAT pet '${pet.name}' with extended fields`);
     } else {
       await db.insert(schema.pets).values({
         id: pet.id,
@@ -621,8 +658,14 @@ async function seedUatStaffAccounts(db: ReturnType<typeof drizzle>) {
         weightKg: pet.weight,
         dateOfBirth: new Date(`${pet.dob}T00:00:00Z`),
         image: pet.image,
+        temperamentScore: randInt(1, 5),
+        temperamentFlags: pickN(temperamentFlagPool, randInt(1, 3)),
+        medicalAlerts: [],
+        preferredCuts: pickN(preferredCutPool, randInt(1, 2)),
+        coatType: pick(coatTypePool),
+        petSizeCategory: pick(petSizeCategoryPool),
       });
-      console.log(`✓ Created UAT pet '${pet.name}'`);
+      console.log(`✓ Created UAT pet '${pet.name}' with extended fields`);
     }
   }
 }
@@ -966,14 +1009,6 @@ async function seed() {
           temperamentScore: randInt(1, 5),
           temperamentFlags: pickN(temperamentFlagPool, randInt(1, 3)),
           medicalAlerts: (() => {
-            // Deterministic alerts for UAT AC pets
-            if (uc.petName === "TestCooper") {
-              return pickN(medicalAlertPool.filter((a) => a.type === "behavioral"), 1).map((a) => ({ ...a, id: uuid() }));
-            }
-            if (uc.petName === "TestRocky") {
-              return pickN(medicalAlertPool.filter((a) => a.type === "skin"), 1).map((a) => ({ ...a, id: uuid() }));
-            }
-            // Other UAT pets: random
             if (rand() < 0.3) {
               const count = rand() < 0.7 ? 1 : 2;
               return pickN(medicalAlertPool, count).map((a) => ({ ...a, id: uuid() }));
@@ -1101,14 +1136,6 @@ async function seed() {
           temperamentScore: randInt(1, 5),
           temperamentFlags: pickN(temperamentFlagPool, randInt(1, 3)),
           medicalAlerts: (() => {
-            // Deterministic alerts for UAT AC pets
-            if (uc.petName === "TestCooper") {
-              return pickN(medicalAlertPool.filter((a) => a.type === "behavioral"), 1).map((a) => ({ ...a, id: uuid() }));
-            }
-            if (uc.petName === "TestRocky") {
-              return pickN(medicalAlertPool.filter((a) => a.type === "skin"), 1).map((a) => ({ ...a, id: uuid() }));
-            }
-            // Other UAT pets: random
             if (rand() < 0.3) {
               const count = rand() < 0.7 ? 1 : 2;
               return pickN(medicalAlertPool, count).map((a) => ({ ...a, id: uuid() }));