Merge branch 'dev' into dario-likes-mcps

hexclave · aadesh18 · Mar 23, 2026 · Mar 23, 2026 · Mar 24, 2026 · Mar 24, 2026
commit 30d53e8cc83d5910d8058e447023b2f67083b2ad
diff --git a/.gitmodules b/.gitmodules
@@ -1,4 +1,4 @@
-[submodule "packages/private"]
-	path = packages/private
+[submodule "backend-private-repo"]
+	path = apps/backend/src/private/implementation
 	url = https://github.com/stack-auth/private.git
 	branch = main
diff --git a/apps/backend/.gitignore b/apps/backend/.gitignore
@@ -1,4 +1,5 @@
 src/generated
+src/private/implementation.generated.ts
 
 # See https://help.github.com/articles/ignoring-files/ for more about ignoring files.
 

diff --git a/apps/backend/package.json b/apps/backend/package.json
@@ -21,10 +21,12 @@
     "start": "next start --port ${NEXT_PUBLIC_STACK_PORT_PREFIX:-81}02",
     "codegen-prisma": "STACK_DATABASE_CONNECTION_STRING=\"${STACK_DATABASE_CONNECTION_STRING:-placeholder-database-connection-string}\" pnpm run prisma generate",
     "codegen-prisma:watch": "STACK_DATABASE_CONNECTION_STRING=\"${STACK_DATABASE_CONNECTION_STRING:-placeholder-database-connection-string}\" pnpm run prisma generate --watch",
+    "generate-private-sign-up-risk-engine": "pnpm run with-env tsx scripts/generate-private-sign-up-risk-engine.ts",
+    "generate-private-sign-up-risk-engine:watch": "chokidar 'src/private/src/sign-up-risk-engine.ts' -c 'pnpm run generate-private-sign-up-risk-engine'",
     "codegen-route-info": "pnpm run with-env tsx scripts/generate-route-info.ts",
     "codegen-route-info:watch": "pnpm run with-env tsx watch --clear-screen=false scripts/generate-route-info.ts",
-    "codegen": "pnpm run with-env pnpm run generate-migration-imports && pnpm run with-env bash -c 'if [ \"$STACK_ACCELERATE_ENABLED\" = \"true\" ]; then pnpm run prisma generate --no-engine; else pnpm run codegen-prisma; fi' && pnpm run codegen-docs && pnpm run codegen-route-info",
-    "codegen:watch": "concurrently -n \"prisma,docs,route-info,migration-imports\" -k \"pnpm run codegen-prisma:watch\" \"pnpm run codegen-docs:watch\" \"pnpm run codegen-route-info:watch\" \"pnpm run generate-migration-imports:watch\"",
+    "codegen": "pnpm run with-env pnpm run generate-migration-imports && pnpm run with-env bash -c 'if [ \"$STACK_ACCELERATE_ENABLED\" = \"true\" ]; then pnpm run prisma generate --no-engine; else pnpm run codegen-prisma; fi' && pnpm run generate-private-sign-up-risk-engine && pnpm run codegen-docs && pnpm run codegen-route-info",
+    "codegen:watch": "pnpm run generate-private-sign-up-risk-engine && concurrently -n \"prisma,private-risk-engine,docs,route-info,migration-imports\" -k \"pnpm run codegen-prisma:watch\" \"pnpm run generate-private-sign-up-risk-engine:watch\" \"pnpm run codegen-docs:watch\" \"pnpm run codegen-route-info:watch\" \"pnpm run generate-migration-imports:watch\"",
     "psql-inner": "psql $(echo $STACK_DATABASE_CONNECTION_STRING | sed 's/\\?.*$//')",
     "clickhouse": "pnpm run with-env clickhouse-client --host localhost --port ${NEXT_PUBLIC_STACK_PORT_PREFIX:-81}37 --user stackframe --password PASSWORD-PLACEHOLDER--9gKyMxJeMx",
     "psql": "pnpm run with-env:dev pnpm run psql-inner",

diff --git a/apps/backend/prisma/migrations/20260308000000_add_signup_fraud_protection/migration.sql b/apps/backend/prisma/migrations/20260308000000_add_signup_fraud_protection/migration.sql
@@ -1,19 +1,18 @@
-ALTER TABLE "ProjectUser" ADD COLUMN "signUpRiskScoreBot" SMALLINT NOT NULL DEFAULT 0;
-ALTER TABLE "ProjectUser" ADD COLUMN "signUpRiskScoreFreeTrialAbuse" SMALLINT NOT NULL DEFAULT 0;
-
-ALTER TABLE "ProjectUser"
-  ADD CONSTRAINT "ProjectUser_risk_score_bot_range"
-  CHECK ("signUpRiskScoreBot" >= 0 AND "signUpRiskScoreBot" <= 100) NOT VALID;
-
-ALTER TABLE "ProjectUser"
-  ADD CONSTRAINT "ProjectUser_risk_score_free_trial_abuse_range"
-  CHECK ("signUpRiskScoreFreeTrialAbuse" >= 0 AND "signUpRiskScoreFreeTrialAbuse" <= 100) NOT VALID;
-
-ALTER TABLE "ProjectUser" ADD COLUMN "signUpCountryCode" TEXT;
-
+-- Add the sign-up metadata columns first.
+-- `signedUpAt` starts nullable so we can backfill existing rows before enforcing it.
 ALTER TABLE "ProjectUser"
+  ADD COLUMN "signUpRiskScoreBot" SMALLINT NOT NULL DEFAULT 0,
+  ADD COLUMN "signUpRiskScoreFreeTrialAbuse" SMALLINT NOT NULL DEFAULT 0,
+  ADD COLUMN "signUpCountryCode" TEXT,
   ADD COLUMN "signedUpAt" TIMESTAMP(3),
   ADD COLUMN "signUpIp" TEXT,
   ADD COLUMN "signUpIpTrusted" BOOLEAN,
   ADD COLUMN "signUpEmailNormalized" TEXT,
   ADD COLUMN "signUpEmailBase" TEXT;
+
+-- Add the risk score bounds without validating existing rows yet.
+ALTER TABLE "ProjectUser"
+  ADD CONSTRAINT "ProjectUser_risk_score_bot_range"
+    CHECK ("signUpRiskScoreBot" >= 0 AND "signUpRiskScoreBot" <= 100) NOT VALID,
+  ADD CONSTRAINT "ProjectUser_risk_score_free_trial_abuse_range"
+    CHECK ("signUpRiskScoreFreeTrialAbuse" >= 0 AND "signUpRiskScoreFreeTrialAbuse" <= 100) NOT VALID;
diff --git a/apps/backend/prisma/migrations/20260308000001_backfill_signup_fraud_protection/migration.sql b/apps/backend/prisma/migrations/20260308000001_backfill_signup_fraud_protection/migration.sql
@@ -1,16 +1,19 @@
+-- Backfill `signedUpAt` from `createdAt` in small batches so the migration stays
+-- safely under the transaction timeout on large tables.
 -- SINGLE_STATEMENT_SENTINEL
 -- CONDITIONALLY_REPEAT_MIGRATION_SENTINEL
 WITH to_update AS (
-    SELECT "projectUserId", "tenancyId"
-    FROM "ProjectUser"
-    WHERE "signedUpAt" IS NULL
-    LIMIT 10000
+  SELECT "projectUserId", "tenancyId"
+  FROM "ProjectUser"
+  WHERE "signedUpAt" IS NULL
+  LIMIT 10000
 ),
 updated AS (
-    UPDATE "ProjectUser" pu
-    SET "signedUpAt" = pu."createdAt"
-    FROM to_update tu
-    WHERE pu."tenancyId" = tu."tenancyId" AND pu."projectUserId" = tu."projectUserId"
-    RETURNING 1
+  UPDATE "ProjectUser" pu
+  SET "signedUpAt" = pu."createdAt"
+  FROM to_update tu
+  WHERE pu."tenancyId" = tu."tenancyId"
+    AND pu."projectUserId" = tu."projectUserId"
+  RETURNING 1
 )
 SELECT COUNT(*) > 0 AS should_repeat_migration FROM updated;
diff --git a/...migrations/20260308000001_backfill_signup_fraud_protection/tests/backfill-and-defaults.ts b/...migrations/20260308000001_backfill_signup_fraud_protection/tests/backfill-and-defaults.ts
@@ -8,10 +8,54 @@ export const preMigration = async (sql: Sql) => {
   const regularUserId = randomUUID();
   const anonUserId = randomUUID();
 
-  await sql`INSERT INTO "Project" ("id", "createdAt", "updatedAt", "displayName", "description", "isProductionMode") VALUES (${projectId}, NOW(), NOW(), 'Test', '', false)`;
-  await sql`INSERT INTO "Tenancy" ("id", "createdAt", "updatedAt", "projectId", "branchId", "hasNoOrganization") VALUES (${tenancyId}::uuid, NOW(), NOW(), ${projectId}, 'main', 'TRUE'::"BooleanTrue")`;
-  await sql`INSERT INTO "ProjectUser" ("projectUserId", "tenancyId", "mirroredProjectId", "mirroredBranchId", "createdAt", "updatedAt", "lastActiveAt") VALUES (${regularUserId}::uuid, ${tenancyId}::uuid, ${projectId}, 'main', NOW(), NOW(), NOW())`;
-  await sql`INSERT INTO "ProjectUser" ("projectUserId", "tenancyId", "mirroredProjectId", "mirroredBranchId", "createdAt", "updatedAt", "lastActiveAt", "isAnonymous") VALUES (${anonUserId}::uuid, ${tenancyId}::uuid, ${projectId}, 'main', NOW(), NOW(), NOW(), true)`;
+  await sql`
+    INSERT INTO "Project" ("id", "createdAt", "updatedAt", "displayName", "description", "isProductionMode")
+    VALUES (${projectId}, NOW(), NOW(), 'Test', '', false)
+  `;
+  await sql`
+    INSERT INTO "Tenancy" ("id", "createdAt", "updatedAt", "projectId", "branchId", "hasNoOrganization")
+    VALUES (${tenancyId}::uuid, NOW(), NOW(), ${projectId}, 'main', 'TRUE'::"BooleanTrue")
+  `;
+  await sql`
+    INSERT INTO "ProjectUser" (
+      "projectUserId",
+      "tenancyId",
+      "mirroredProjectId",
+      "mirroredBranchId",
+      "createdAt",
+      "updatedAt",
+      "lastActiveAt"
+    ) VALUES (
+      ${regularUserId}::uuid,
+      ${tenancyId}::uuid,
+      ${projectId},
+      'main',
+      NOW(),
+      NOW(),
+      NOW()
+    )
+  `;
+  await sql`
+    INSERT INTO "ProjectUser" (
+      "projectUserId",
+      "tenancyId",
+      "mirroredProjectId",
+      "mirroredBranchId",
+      "createdAt",
+      "updatedAt",
+      "lastActiveAt",
+      "isAnonymous"
+    ) VALUES (
+      ${anonUserId}::uuid,
+      ${tenancyId}::uuid,
+      ${projectId},
+      'main',
+      NOW(),
+      NOW(),
+      NOW(),
+      true
+    )
+  `;
 
   return { regularUserId, anonUserId };
 };

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
@@ -1,3 +1,4 @@
+-- Add the indexes needed for recent sign-up heuristics and sorting.
 -- SPLIT_STATEMENT_SENTINEL
 -- SINGLE_STATEMENT_SENTINEL
 -- RUN_OUTSIDE_TRANSACTION_SENTINEL
@@ -16,6 +17,7 @@ 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");
 
+-- Validate the risk score bounds once every row has the new columns.
 -- SPLIT_STATEMENT_SENTINEL
 -- SINGLE_STATEMENT_SENTINEL
 -- RUN_OUTSIDE_TRANSACTION_SENTINEL
@@ -26,27 +28,8 @@ ALTER TABLE "ProjectUser" VALIDATE CONSTRAINT "ProjectUser_risk_score_bot_range"
 -- RUN_OUTSIDE_TRANSACTION_SENTINEL
 ALTER TABLE "ProjectUser" VALIDATE CONSTRAINT "ProjectUser_risk_score_free_trial_abuse_range";
 
--- SPLIT_STATEMENT_SENTINEL
--- SINGLE_STATEMENT_SENTINEL
--- RUN_OUTSIDE_TRANSACTION_SENTINEL
-CREATE OR REPLACE FUNCTION "set_project_user_signed_up_at_from_created_at"()
-RETURNS TRIGGER AS $$
-BEGIN
-  IF NEW."signedUpAt" IS NULL THEN
-    NEW."signedUpAt" := NEW."createdAt";
-  END IF;
-  RETURN NEW;
-END;
-$$ LANGUAGE plpgsql;
-
--- SPLIT_STATEMENT_SENTINEL
--- SINGLE_STATEMENT_SENTINEL
--- RUN_OUTSIDE_TRANSACTION_SENTINEL
-CREATE TRIGGER "ProjectUser_set_signedUpAt_from_createdAt"
-BEFORE INSERT ON "ProjectUser"
-FOR EACH ROW
-EXECUTE FUNCTION "set_project_user_signed_up_at_from_created_at"();
-
+-- Enforce `signedUpAt` after the backfill is complete. We intentionally require
+-- inserts to provide the value explicitly instead of hiding that behavior in a trigger.
 -- SPLIT_STATEMENT_SENTINEL
 -- SINGLE_STATEMENT_SENTINEL
 -- RUN_OUTSIDE_TRANSACTION_SENTINEL

diff --git a/...migrations/20260308000002_finalize_signup_fraud_protection/tests/constraints-validated.ts b/...migrations/20260308000002_finalize_signup_fraud_protection/tests/constraints-validated.ts
diff --git a/...sma/migrations/20260308000002_finalize_signup_fraud_protection/tests/default-on-insert.ts b/...sma/migrations/20260308000002_finalize_signup_fraud_protection/tests/default-on-insert.ts
diff --git a/...0260308000002_finalize_signup_fraud_protection/tests/finalized-signup-fraud-protection.ts b/...0260308000002_finalize_signup_fraud_protection/tests/finalized-signup-fraud-protection.ts
@@ -0,0 +1,139 @@
+import { randomUUID } from 'crypto';
+import type { Sql } from 'postgres';
+import { expect } from 'vitest';
+
+export const postMigration = async (sql: Sql) => {
+  const projectId = `test-${randomUUID()}`;
+  const tenancyId = randomUUID();
+  const userId = randomUUID();
+  const explicitSignedUpAt = '2026-03-08 12:34:56.789';
+
+  const triggers = await sql`
+    SELECT tgname
+    FROM pg_trigger
+    WHERE tgrelid = '"ProjectUser"'::regclass
+      AND tgname = 'ProjectUser_set_signedUpAt_from_createdAt'
+      AND NOT tgisinternal
+  `;
+  expect(triggers).toHaveLength(0);
+
+  const functions = await sql`
+    SELECT proname
+    FROM pg_proc
+    WHERE proname = 'set_project_user_signed_up_at_from_created_at'
+  `;
+  expect(functions).toHaveLength(0);
+
+  const constraints = await sql`
+    SELECT conname, convalidated
+    FROM pg_constraint
+    WHERE conrelid = '"ProjectUser"'::regclass
+      AND conname IN (
+        'ProjectUser_risk_score_bot_range',
+        'ProjectUser_risk_score_free_trial_abuse_range',
+        'ProjectUser_signedUpAt_not_null'
+      )
+    ORDER BY conname
+  `;
+  expect(constraints).toHaveLength(3);
+  for (const constraint of constraints) {
+    expect(constraint.convalidated, `${constraint.conname} should be validated`).toBe(true);
+  }
+
+  const indexes = await sql`
+    SELECT indexname, indexdef
+    FROM pg_indexes
+    WHERE schemaname = current_schema()
+      AND tablename = 'ProjectUser'
+      AND indexname IN (
+        'ProjectUser_signedUpAt_asc',
+        'ProjectUser_signUpIp_recent_idx',
+        'ProjectUser_signUpEmailBase_recent_idx'
+      )
+    ORDER BY indexname
+  `;
+  expect(indexes.map((row) => row.indexname)).toEqual([
+    'ProjectUser_signUpEmailBase_recent_idx',
+    'ProjectUser_signUpIp_recent_idx',
+    'ProjectUser_signedUpAt_asc',
+  ]);
+
+  const indexDefByName = Object.fromEntries(indexes.map((row) => [row.indexname, row.indexdef]));
+  expect(indexDefByName['ProjectUser_signedUpAt_asc']).toContain('"tenancyId", "isAnonymous", "signedUpAt"');
+  expect(indexDefByName['ProjectUser_signUpIp_recent_idx']).toContain('"tenancyId", "isAnonymous", "signUpIp", "signedUpAt"');
+  expect(indexDefByName['ProjectUser_signUpEmailBase_recent_idx']).toContain('"tenancyId", "isAnonymous", "signUpEmailBase", "signedUpAt"');
+
+  const colInfo = await sql`
+    SELECT is_nullable, column_default
+    FROM information_schema.columns
+    WHERE table_name = 'ProjectUser'
+      AND column_name = 'signedUpAt'
+  `;
+  expect(colInfo).toHaveLength(1);
+  expect(colInfo[0].is_nullable).toBe('NO');
+  expect(colInfo[0].column_default).toBe(null);
+
+  await sql`
+    INSERT INTO "Project" ("id", "createdAt", "updatedAt", "displayName", "description", "isProductionMode")
+    VALUES (${projectId}, NOW(), NOW(), 'Test', '', false)
+  `;
+  await sql`
+    INSERT INTO "Tenancy" ("id", "createdAt", "updatedAt", "projectId", "branchId", "hasNoOrganization")
+    VALUES (${tenancyId}::uuid, NOW(), NOW(), ${projectId}, 'main', 'TRUE'::"BooleanTrue")
+  `;
+
+  await expect(sql`
+    INSERT INTO "ProjectUser" (
+      "projectUserId",
+      "tenancyId",
+      "mirroredProjectId",
+      "mirroredBranchId",
+      "createdAt",
+      "updatedAt",
+      "lastActiveAt"
+    ) VALUES (
+      ${userId}::uuid,
+      ${tenancyId}::uuid,
+      ${projectId},
+      'main',
+      NOW(),
+      NOW(),
+      NOW()
+    )
+  `).rejects.toThrow(/signedUpAt/);
+
+  await sql`
+    INSERT INTO "ProjectUser" (
+      "projectUserId",
+      "tenancyId",
+      "mirroredProjectId",
+      "mirroredBranchId",
+      "createdAt",
+      "updatedAt",
+      "lastActiveAt",
+      "signedUpAt"
+    ) VALUES (
+      ${userId}::uuid,
+      ${tenancyId}::uuid,
+      ${projectId},
+      'main',
+      NOW(),
+      NOW(),
+      NOW(),
+      ${explicitSignedUpAt}::timestamp
+    )
+  `;
+
+  const insertedRows = await sql`
+    SELECT
+      "signedUpAt",
+      "createdAt",
+      "signedUpAt" = ${explicitSignedUpAt}::timestamp AS "matchesExplicitSignedUpAt"
+    FROM "ProjectUser"
+    WHERE "projectUserId" = ${userId}::uuid
+  `;
+  expect(insertedRows).toHaveLength(1);
+  expect(insertedRows[0].signedUpAt).not.toBeNull();
+  expect(insertedRows[0].matchesExplicitSignedUpAt).toBe(true);
+  expect(insertedRows[0].signedUpAt.toISOString()).not.toBe(insertedRows[0].createdAt.toISOString());
+};