eletrotupi / tcc/ api/prisma/migrations/20260930212941_unique_activation_code/migration.sql390ce2952161e18712e999aba9eb762245263865
702 B | 17 lines Raw
1
-- Codes are issued for a five-minute window and are not unique today, so an
2
-- existing database can hold the same code against several users. Keep the most
3
-- recently issued holder of each code and clear the rest, together with their
4
-- expiry, so a cleared user is reissued a code on their next login.
5
UPDATE "users"
6
SET "activation_code" = NULL,
7
    "activation_code_expires_at" = NULL
8
WHERE "activation_code" IS NOT NULL
9
  AND "id" NOT IN (
10
    SELECT DISTINCT ON ("activation_code") "id"
11
    FROM "users"
12
    WHERE "activation_code" IS NOT NULL
13
    ORDER BY "activation_code", "updated_at" DESC
14
  );
15
16
-- CreateIndex
17
CREATE UNIQUE INDEX "users_activation_code_key" ON "users"("activation_code");