_⚠️ Potential issue_ | _🟠 Major_

[podalls97]

⚠️ Potential issue | 🟠 Major

Add unique constraint on email column to prevent race conditions.

The accessRequest table has a unique constraint on username but not on email. The API code checks for duplicate emails, but without a database-level constraint, concurrent requests with the same email could both pass the application check and create duplicates.

🔎 Proposed fix

 CREATE TABLE "accessRequest" (
 	"id" text PRIMARY KEY NOT NULL,
 	"username" text NOT NULL,
 	"displayUsername" text,
 	"name" text NOT NULL,
 	"email" text NOT NULL,
 	"reason" text NOT NULL,
 	"status" text DEFAULT 'pending' NOT NULL,
 	"requestedAt" timestamp DEFAULT now() NOT NULL,
 	"reviewedAt" timestamp,
 	"reviewedBy" text,
 	"reviewerNotes" text,
 	"createdAt" timestamp DEFAULT now() NOT NULL,
 	"updatedAt" timestamp DEFAULT now() NOT NULL,
-	CONSTRAINT "accessRequest_username_unique" UNIQUE("username")
+	CONSTRAINT "accessRequest_username_unique" UNIQUE("username"),
+	CONSTRAINT "accessRequest_email_unique" UNIQUE("email")
 );

📝 Committable suggestion

‼️ IMPORTANT
Carefully review the code before committing. Ensure that it accurately replaces the highlighted code, contains no missing lines, and has no issues with indentation. Thoroughly test & benchmark the code to ensure it meets the requirements.

CREATE TABLE "accessRequest" (
	"id" text PRIMARY KEY NOT NULL,
	"username" text NOT NULL,
	"displayUsername" text,
	"name" text NOT NULL,
	"email" text NOT NULL,
	"reason" text NOT NULL,
	"status" text DEFAULT 'pending' NOT NULL,
	"requestedAt" timestamp DEFAULT now() NOT NULL,
	"reviewedAt" timestamp,
	"reviewedBy" text,
	"reviewerNotes" text,
	"createdAt" timestamp DEFAULT now() NOT NULL,
	"updatedAt" timestamp DEFAULT now() NOT NULL,
	CONSTRAINT "accessRequest_username_unique" UNIQUE("username"),
	CONSTRAINT "accessRequest_email_unique" UNIQUE("email")
);

🤖 Prompt for AI Agents

In drizzle/0001_faulty_valeria_richards.sql around lines 1 to 16 the
accessRequest table lacks a database-level unique constraint on the email
column, which allows concurrent inserts with the same email; add a UNIQUE
constraint for "email" (either by adding CONSTRAINT "accessRequest_email_unique"
UNIQUE("email") inside the CREATE TABLE or by issuing an ALTER TABLE ... ADD
CONSTRAINT statement in this migration) so the database enforces email
uniqueness and prevents race-condition duplicates.

Originally posted by @coderabbitai[bot] in #1 (comment)

[coderabbitai]

📝 CodeRabbit Plan Mode

Generate an implementation plan and prompts that you can use with your favorite coding agent.

• Create Plan

Examples

• Example 1
• Example 2

---

🔗 Related PRs

podalls97/next-convex-betterauth-template#2 - Fix reset-password no auth required, and "Mutation context required" [merged]

👤 Suggested Assignees

• pietgit

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!