47c34f129e
- Convert Lead.status from String to LeadStatus enum (NEW, CONTACTED, QUALIFIED, NEGOTIATING, CONVERTED, LOST) with safe data migration - Add FK constraint Transaction.buyerId -> User.id to prevent orphaned transactions - Add compound index on Inquiry(listingId, userId) for duplicate inquiry checks Co-Authored-By: Paperclip <noreply@paperclip.ing>
31 lines
1.4 KiB
SQL
31 lines
1.4 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "LeadStatus" AS ENUM ('NEW', 'CONTACTED', 'QUALIFIED', 'NEGOTIATING', 'CONVERTED', 'LOST');
|
|
|
|
-- AlterTable: Lead.status from String to LeadStatus enum
|
|
-- Step 1: Add temporary column with enum type
|
|
ALTER TABLE "Lead" ADD COLUMN "status_new" "LeadStatus" NOT NULL DEFAULT 'NEW';
|
|
|
|
-- Step 2: Migrate existing data (map known string values to enum)
|
|
UPDATE "Lead" SET "status_new" = CASE
|
|
WHEN "status" = 'new' THEN 'NEW'::"LeadStatus"
|
|
WHEN "status" = 'contacted' THEN 'CONTACTED'::"LeadStatus"
|
|
WHEN "status" = 'qualified' THEN 'QUALIFIED'::"LeadStatus"
|
|
WHEN "status" = 'negotiating' THEN 'NEGOTIATING'::"LeadStatus"
|
|
WHEN "status" = 'converted' THEN 'CONVERTED'::"LeadStatus"
|
|
WHEN "status" = 'lost' THEN 'LOST'::"LeadStatus"
|
|
ELSE 'NEW'::"LeadStatus"
|
|
END;
|
|
|
|
-- Step 3: Drop old column, rename new column
|
|
ALTER TABLE "Lead" DROP COLUMN "status";
|
|
ALTER TABLE "Lead" RENAME COLUMN "status_new" TO "status";
|
|
|
|
-- Step 4: Recreate index on Lead.status
|
|
CREATE INDEX "Lead_status_idx" ON "Lead"("status");
|
|
|
|
-- AddForeignKey: Transaction.buyerId -> User.id
|
|
ALTER TABLE "Transaction" ADD CONSTRAINT "Transaction_buyerId_fkey" FOREIGN KEY ("buyerId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- CreateIndex: Compound index on Inquiry for duplicate checks
|
|
CREATE INDEX "Inquiry_listingId_userId_idx" ON "Inquiry"("listingId", "userId");
|