Outray cli is vulnerable to race conditions in tunnels creation

@@ -1,6 +1,6 @@
 import { createFileRoute } from "@tanstack/react-router";
 import { json } from "@tanstack/react-start";
-import { eq } from "drizzle-orm";
+import { eq, sql } from "drizzle-orm";
 import { randomUUID } from "crypto";
 import { db } from "../../../db";
 import { tunnels } from "../../../db/app-schema";
@@ -73,97 +73,103 @@ export const Route = createFileRoute("/api/tunnel/register")({
             tunnelId = providedTunnelId!;
           }
 
-          // Check subscription limits
-          const [subscription] = await db
-            .select()
-            .from(subscriptions)
-            .where(eq(subscriptions.organizationId, organizationId));
+          // Use the URL passed from the tunnel server
+          const tunnelUrl = url;
 
-          const currentPlan = subscription?.plan || "free";
-          const planLimits = getPlanLimits(currentPlan as any);
-          const tunnelLimit = planLimits.maxTunnels;
+          // Use a transaction with row-level locking to prevent race conditions
+          const result = await db.transaction(async (tx) => {
+            // Lock the organization's subscription row to serialize concurrent requests
+            const [subscription] = await tx
+              .select()
+              .from(subscriptions)
+              .where(eq(subscriptions.organizationId, organizationId))
+              .for("update");
 
-          const setKey = `org:${organizationId}:online_tunnels`;
+            const currentPlan = subscription?.plan || "free";
+            const planLimits = getPlanLimits(currentPlan as any);
+            const tunnelLimit = planLimits.maxTunnels;
 
-          // Use the URL passed from the tunnel server
-          const tunnelUrl = url;
+            const setKey = `org:${organizationId}:online_tunnels`;
+
+            // Check if tunnel already exists in database (with lock)
+            const [existingTunnel] = await tx
+              .select()
+              .from(tunnels)
+              .where(eq(tunnels.url, tunnelUrl))
+              .for("update");
+
+            const isReconnection = !!existingTunnel;
 
-          // Check if tunnel already exists in database
-          const [existingTunnel] = await db
-            .select()
-            .from(tunnels)
-            .where(eq(tunnels.url, tunnelUrl));
-
-          const isReconnection = !!existingTunnel;
-
-          console.log(
-            `[TUNNEL LIMIT CHECK] Org: ${organizationId}, Tunnel: ${tunnelId}`,
-          );
-          console.log(
-            `[TUNNEL LIMIT CHECK] Is Reconnection: ${isReconnection}`,
-          );
-          console.log(
-            `[TUNNEL LIMIT CHECK] Plan: ${currentPlan}, Limit: ${tunnelLimit}`,
-          );
-
-          // Check limits only for NEW tunnels (not reconnections)
-          if (!isReconnection) {
-            // Count active tunnels from Redis SET
-            const activeCount = await redis.scard(setKey);
             console.log(
-              `[TUNNEL LIMIT CHECK] Active count in Redis: ${activeCount}`,
+              `[TUNNEL LIMIT CHECK] Org: ${organizationId}, Tunnel: ${tunnelId}`,
+            );
+            console.log(
+              `[TUNNEL LIMIT CHECK] Is Reconnection: ${isReconnection}`,
+            );
+            console.log(
+              `[TUNNEL LIMIT CHECK] Plan: ${currentPlan}, Limit: ${tunnelLimit}`,
             );
 
-            // The current tunnel is NOT yet in the online_tunnels set (added after successful registration)
-            // So we check if activeCount >= limit (not >)
-            if (activeCount >= tunnelLimit) {
+            // Check limits only for NEW tunnels (not reconnections)
+            if (!isReconnection) {
+              // Count active tunnels from Redis SET
+              const activeCount = await redis.scard(setKey);
               console.log(
-                `[TUNNEL LIMIT CHECK] REJECTED - ${activeCount} >= ${tunnelLimit}`,
+                `[TUNNEL LIMIT CHECK] Active count in Redis: ${activeCount}`,
               );
-              return json(
-                {
+
+              // The current tunnel is NOT yet in the online_tunnels set (added after successful registration)
+              // So we check if activeCount >= limit (not >)
+              if (activeCount >= tunnelLimit) {
+                console.log(
+                  `[TUNNEL LIMIT CHECK] REJECTED - ${activeCount} >= ${tunnelLimit}`,
+                );
+                return {
                   error: `Tunnel limit reached. The ${currentPlan} plan allows ${tunnelLimit} active tunnel${tunnelLimit > 1 ? "s" : ""}.`,
-                },
-                { status: 403 },
+                  status: 403,
+                };
+              }
+              console.log(
+                `[TUNNEL LIMIT CHECK] ALLOWED - ${activeCount} < ${tunnelLimit}`,
               );
+            } else {
+              console.log(`[TUNNEL LIMIT CHECK] SKIPPED - Reconnection detected`);
             }
-            console.log(
-              `[TUNNEL LIMIT CHECK] ALLOWED - ${activeCount} < ${tunnelLimit}`,
-            );
-          } else {
-            console.log(`[TUNNEL LIMIT CHECK] SKIPPED - Reconnection detected`);
-          }
 
-          if (existingTunnel) {
-            // Tunnel with this URL already exists, update lastSeenAt
-            await db
-              .update(tunnels)
-              .set({ lastSeenAt: new Date() })
-              .where(eq(tunnels.id, existingTunnel.id));
-
-            return json({
-              success: true,
-              tunnelId: existingTunnel.id,
-            });
-          }
+            if (existingTunnel) {
+              // Tunnel with this URL already exists, update lastSeenAt
+              await tx
+                .update(tunnels)
+                .set({ lastSeenAt: new Date() })
+                .where(eq(tunnels.id, existingTunnel.id));
 
-          // Create new tunnel record
-          const tunnelRecord = {
-            id: randomUUID(),
-            url: tunnelUrl,
-            userId,
-            organizationId,
-            name: name || null,
-            protocol,
-            remotePort: remotePort || null,
-            lastSeenAt: new Date(),
-            createdAt: new Date(),
-            updatedAt: new Date(),
-          };
+              return { success: true, tunnelId: existingTunnel.id };
+            }
 
-          await db.insert(tunnels).values(tunnelRecord);
+            // Create new tunnel record
+            const tunnelRecord = {
+              id: randomUUID(),
+              url: tunnelUrl,
+              userId,
+              organizationId,
+              name: name || null,
+              protocol,
+              remotePort: remotePort || null,
+              lastSeenAt: new Date(),
+              createdAt: new Date(),
+              updatedAt: new Date(),
+            };
+
+            await tx.insert(tunnels).values(tunnelRecord);
+
+            return { success: true, tunnelId: tunnelRecord.id };
+          });
+
+          if ("error" in result) {
+            return json({ error: result.error }, { status: result.status });
+          }
 
-          return json({ success: true, tunnelId: tunnelRecord.id });
+          return json({ success: true, tunnelId: result.tunnelId });
         } catch (error) {
           console.error("Tunnel registration error:", error);
           return json({ error: "Internal server error" }, { status: 500 });