Chris Farhood
174d1c667b
Migration 0031 tries to ALTER the coat_type and pet_size_category columns on the pets table to use new enum types, but no prior migration adds these columns. On a fresh DB (after the reset CronJob wiped all tables), this causes the entire migration chain to fail and roll back. Added ADD COLUMN IF NOT EXISTS before the ALTER TYPE so the migration works both on fresh databases and existing ones with the columns. Co-Authored-By: Paperclip <noreply@paperclip.ing>
33 lines
1.9 KiB
SQL
33 lines
1.9 KiB
SQL
-- Migration: 0031_buffer_rules.sql
|
|
-- Buffer rules CRUD: pet size/coat enums, bufferRules table, services.defaultBufferMinutes
|
|
|
|
-- ─── Enums ───────────────────────────────────────────────────────────────────
|
|
|
|
CREATE TYPE "pet_size_category" AS ENUM ('small', 'medium', 'large', 'xlarge');
|
|
CREATE TYPE "coat_type" AS ENUM ('smooth', 'double', 'wire', 'curly', 'long', 'hairless');
|
|
|
|
-- ─── Add columns to pets if missing, then cast to enums ──────────────────────
|
|
|
|
ALTER TABLE "pets" ADD COLUMN IF NOT EXISTS "coat_type" text;
|
|
ALTER TABLE "pets" ADD COLUMN IF NOT EXISTS "pet_size_category" text;
|
|
ALTER TABLE "pets" ALTER COLUMN "coat_type" TYPE "coat_type" USING "coat_type"::text::"coat_type";
|
|
ALTER TABLE "pets" ALTER COLUMN "pet_size_category" TYPE "pet_size_category" USING "pet_size_category"::text::"pet_size_category";
|
|
|
|
-- ─── Services: add defaultBufferMinutes ───────────────────────────────────────
|
|
|
|
ALTER TABLE "services" ADD COLUMN "default_buffer_minutes" integer;
|
|
|
|
-- ─── Buffer Rules table ───────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE "buffer_rules" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
"service_id" uuid NOT NULL REFERENCES "services"("id") ON DELETE CASCADE,
|
|
"size_category" "pet_size_category",
|
|
"coat_type" "coat_type",
|
|
"buffer_minutes" integer NOT NULL,
|
|
"created_at" timestamp NOT NULL DEFAULT now(),
|
|
"updated_at" timestamp NOT NULL DEFAULT now(),
|
|
CONSTRAINT "uq_buffer_rules_service_size_coat" UNIQUE ("service_id", "size_category", "coat_type")
|
|
);
|
|
|
|
CREATE INDEX "idx_buffer_rules_service_id" ON "buffer_rules"("service_id"); |