Add index for normalized sign-up email in ProjectUser model

mantrakp04 · mantrakp04 · commit b57f00514634 · 2026-04-03T18:25:59.000-07:00
- Introduced a new index on the ProjectUser model for the signUpEmailNormalized field, enhancing query performance related to user sign-up activities.
- Updated the migration SQL to create the corresponding index in the database, ensuring efficient data retrieval for analytics and fraud protection measures.
diff --git a/apps/backend/prisma/migrations/20260308000002_finalize_signup_fraud_protection/migration.sql b/apps/backend/prisma/migrations/20260308000002_finalize_signup_fraud_protection/migration.sql
@@ -17,6 +17,12 @@ CREATE INDEX CONCURRENTLY IF NOT EXISTS "ProjectUser_signUpIp_recent_idx"
 CREATE INDEX CONCURRENTLY IF NOT EXISTS "ProjectUser_signUpEmailBase_recent_idx"
   ON "ProjectUser"("tenancyId", "isAnonymous", "signUpEmailBase", "signedUpAt");
 
+-- SPLIT_STATEMENT_SENTINEL
+-- SINGLE_STATEMENT_SENTINEL
+-- RUN_OUTSIDE_TRANSACTION_SENTINEL
+CREATE INDEX CONCURRENTLY IF NOT EXISTS "ProjectUser_signUpEmailNormalized_recent_idx"
+  ON "ProjectUser"("tenancyId", "isAnonymous", "signUpEmailNormalized", "signedUpAt");
+
 -- Validate the risk score bounds once every row has the new columns.
 -- SPLIT_STATEMENT_SENTINEL
 -- SINGLE_STATEMENT_SENTINEL
diff --git a/apps/backend/prisma/schema.prisma b/apps/backend/prisma/schema.prisma
@@ -312,6 +312,7 @@ model ProjectUser {
   @@index([tenancyId, createdAt(sort: Desc)], name: "ProjectUser_createdAt_desc")
   @@index([tenancyId, isAnonymous, signedUpAt(sort: Asc)], name: "ProjectUser_signedUpAt_asc")
   @@index([tenancyId, isAnonymous, signUpIp, signedUpAt], name: "ProjectUser_signUpIp_recent_idx")
+  @@index([tenancyId, isAnonymous, signUpEmailNormalized, signedUpAt], name: "ProjectUser_signUpEmailNormalized_recent_idx")
   @@index([tenancyId, isAnonymous, signUpEmailBase, signedUpAt], name: "ProjectUser_signUpEmailBase_recent_idx")
   @@index([tenancyId, sequenceId], name: "ProjectUser_tenancyId_sequenceId_idx")
   @@index([shouldUpdateSequenceId, tenancyId], name: "ProjectUser_shouldUpdateSequenceId_idx")
