Skip to content

Commit b5b3115

Browse files
BilalG1N2D4
BilalG1
and
N2D4
authored
Metrics Endpoint Speed (#966)
<img width="567" height="249" alt="Screenshot 2025-10-20 at 11 23 10 AM" src="https://github.com/user-attachments/assets/340df844-f619-489f-8d41-cc26bc165018" /> <img width="595" height="255" alt="Screenshot 2025-10-20 at 11 24 00 AM" src="https://github.com/user-attachments/assets/9321bda1-e6f0-4f53-8c6b-e29d0fc16038" /> <!-- Make sure you've read the CONTRIBUTING.md guidelines: https://github.com/stack-auth/stack-auth/blob/dev/CONTRIBUTING.md --> <!-- RECURSEML_SUMMARY:START --> ## High-level PR Summary This PR optimizes the performance of user list and metrics endpoints by refactoring SQL queries to use more efficient patterns. The changes include rewriting queries to use `LATERAL` joins and CTEs with proper filtering, extracting common user mapping logic into reusable functions, and adding performance tests with SQL scripts to generate realistic test data (10,000 mock users and activity events across 100 countries). ⏱️ Estimated Review Time: 30-90 minutes <details> <summary>💡 Review Order Suggestion</summary> | Order | File Path | |-------|-----------| | 1 | `apps/e2e/tests/backend/performance/mock-users.sql` | | 2 | `apps/e2e/tests/backend/performance/mock-metric-events.sql` | | 3 | `apps/e2e/tests/backend/performance/users-list.test.ts` | | 4 | `apps/backend/src/app/api/latest/users/crud.tsx` | | 5 | `apps/backend/src/app/api/latest/internal/metrics/route.tsx` | </details> [![Need help? Join our Discord](https://img.shields.io/badge/Need%20help%3F%20Join%20our%20Discord-5865F2?style=plastic&logo=discord&logoColor=white)](https://discord.gg/n3SsVDAW6U) [![Analyze latest changes](https://img.shields.io/badge/Analyze%20latest%20changes-238636?style=plastic)](https://squash-322339097191.europe-west3.run.app/interactive/f22b2c44a15da2027f3566cd60d31ef7f86e5c2a1262a1622c033a93a8e7284b/?repo_owner=stack-auth&repo_name=stack-auth&pr_number=966) <!-- RECURSEML_SUMMARY:END --> <!-- ELLIPSIS_HIDDEN --> ---- > [!IMPORTANT] > Optimize metrics and user list endpoints with SQL refactoring, caching, and performance tests, adding a `CacheEntry` model and mock data scripts. > > - **Performance Optimization**: > - Refactor SQL queries in `route.tsx` to use `LATERAL` joins and CTEs for efficient data retrieval. > - Implement caching in `route.tsx` using `getOrSetCacheValue()` to reduce database load. > - **Database Changes**: > - Add `CacheEntry` model to `schema.prisma` and create corresponding table and index in `migration.sql`. > - Remove auto-migration metadata step from `check-prisma-migrations.yaml`. > - **Testing**: > - Add performance tests in `metrics.test.ts` to benchmark metrics and user endpoints. > - Create mock data scripts `mock-users.sql` and `mock-metric-events.sql` for testing with 10,000 users and events across 100 countries. > - **Miscellaneous**: > - Update `db-migrations.ts` to include new migration file generation logic. > - Add `cache.tsx` for caching logic implementation. > > <sup>This description was created by </sup>[<img alt="Ellipsis" src="https://img.shields.io/badge/Ellipsis-blue?color=175173">](https://www.ellipsis.dev?ref=stack-auth%2Fstack-auth&utm_source=github&utm_medium=referral)<sup> for 4d9be71. You can [customize](https://app.ellipsis.dev/stack-auth/settings/summaries) this summary. It will automatically update as commits are pushed.</sup> ---- <!-- ELLIPSIS_HIDDEN --> <!-- This is an auto-generated comment: release notes by coderabbit.ai --> ## Summary by CodeRabbit * **New Features** * Metrics now use a cache layer with per-entry TTL and tenancy-aware loaders. * **Bug Fixes** * Improved accuracy of daily active and related metrics with tenancy-aware counting and more robust last-active computation. * **Performance** * Faster metrics responses via batched reads and cache-backed endpoints. * **Tests** * Added end-to-end performance benchmarks and SQL seed scripts for metrics/user load testing. * **Chores** * DB migration added support for cached entries; CI migration check flow adjusted; migration tooling improved. <!-- end of auto-generated comment: release notes by coderabbit.ai --> --------- Co-authored-by: Konsti Wohlwend <n2d4xc@gmail.com>
1 parent 9fa7e3b commit b5b3115

File tree

10 files changed

+780
-92
lines changed

10 files changed

+780
-92
lines changed

‎.github/workflows/check-prisma-migrations.yaml‎

Lines changed: 0 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -59,12 +59,5 @@ jobs:
5959
- name: Initialize database
6060
run: pnpm run db:init
6161

62-
- name: Remove auto-migration metadata
63-
run: |
64-
cd apps/backend
65-
pnpm run prisma db execute --url postgres://postgres:PASSWORD-PLACEHOLDER--uqfEC1hmmv@localhost:8128/stackframe --stdin <<'SQL'
66-
DROP TABLE IF EXISTS "SchemaMigration";
67-
SQL
68-
6962
- name: Check for differences in Prisma schema and current DB
7063
run: cd apps/backend && pnpm run prisma migrate diff --from-url postgres://postgres:PASSWORD-PLACEHOLDER--uqfEC1hmmv@localhost:8128/stackframe --to-schema-datamodel ./prisma/schema.prisma --shadow-database-url postgres://postgres:PASSWORD-PLACEHOLDER--uqfEC1hmmv@localhost:8128/shadow_db --exit-code

‎apps/backend/prisma/migrations/20251020210214_add_cache_entry/migration.sql‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,16 @@
1+
-- CreateTable
2+
CREATE TABLE "CacheEntry" (
3+
"id" UUID NOT NULL,
4+
"namespace" TEXT NOT NULL,
5+
"cacheKey" TEXT NOT NULL,
6+
"payload" JSONB NOT NULL,
7+
"expiresAt" TIMESTAMP(3) NOT NULL,
8+
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
9+
"updatedAt" TIMESTAMP(3) NOT NULL,
10+
11+
CONSTRAINT "CacheEntry_pkey" PRIMARY KEY ("id")
12+
);
13+
14+
-- CreateIndex
15+
CREATE UNIQUE INDEX "CacheEntry_namespace_cacheKey_key" ON "CacheEntry"("namespace", "cacheKey");
16+

‎apps/backend/prisma/schema.prisma‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,16 @@ datasource db {
99
directUrl = env("STACK_DIRECT_DATABASE_CONNECTION_STRING")
1010
}
1111

12+
model SchemaMigration {
13+
id String @id @default(dbgenerated("gen_random_uuid()"))
14+
finishedAt DateTime
15+
migrationName String @unique
16+
17+
@@ignore
18+
}
19+
20+
21+
1222
model Project {
1323
// Note that the project with ID `internal` is handled as a special case. All other project IDs are UUIDs.
1424
id String @id
@@ -828,3 +838,15 @@ model DataVaultEntry {
828838
@@unique([tenancyId, storeId, hashedKey])
829839
@@index([tenancyId, storeId])
830840
}
841+
842+
model CacheEntry {
843+
id String @id @default(uuid()) @db.Uuid
844+
namespace String
845+
cacheKey String
846+
payload Json
847+
expiresAt DateTime
848+
createdAt DateTime @default(now())
849+
updatedAt DateTime @updatedAt
850+
851+
@@unique([namespace, cacheKey])
852+
}

‎apps/backend/scripts/db-migrations.ts‎

Lines changed: 95 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -2,9 +2,12 @@ import { applyMigrations } from "@/auto-migrations";
22
import { MIGRATION_FILES_DIR, getMigrationFiles } from "@/auto-migrations/utils";
33
import { globalPrismaClient, globalPrismaSchema, sqlQuoteIdent } from "@/prisma-client";
44
import { Prisma } from "@prisma/client";
5-
import { execSync } from "child_process";
6-
import * as readline from 'readline';
5+
import { spawnSync } from "child_process";
6+
import fs from "fs";
7+
import path from "path";
8+
import * as readline from "readline";
79
import { seed } from "../prisma/seed";
10+
import { getEnvVariable } from "@stackframe/stack-shared/dist/utils/env";
811

912
const dropSchema = async () => {
1013
await globalPrismaClient.$executeRaw(Prisma.sql`DROP SCHEMA ${sqlQuoteIdent(globalPrismaSchema)} CASCADE`);
@@ -13,26 +16,108 @@ const dropSchema = async () => {
1316
await globalPrismaClient.$executeRaw(Prisma.sql`GRANT ALL ON SCHEMA ${sqlQuoteIdent(globalPrismaSchema)} TO public`);
1417
};
1518

16-
const promptDropDb = async () => {
19+
20+
const askQuestion = (question: string) => {
1721
const rl = readline.createInterface({
1822
input: process.stdin,
19-
output: process.stdout
23+
output: process.stdout,
2024
});
2125

22-
const answer = await new Promise<string>(resolve => {
23-
rl.question('Are you sure you want to drop everything in the database? This action cannot be undone. (y/N): ', resolve);
26+
return new Promise<string>((resolve) => {
27+
rl.question(question, (answer) => {
28+
rl.close();
29+
resolve(answer);
30+
});
2431
});
25-
rl.close();
32+
};
33+
34+
const promptDropDb = async () => {
35+
const answer = (await askQuestion(
36+
'Are you sure you want to drop everything in the database? This action cannot be undone. (y/N): ',
37+
)).trim();
2638

2739
if (answer.toLowerCase() !== 'y') {
2840
console.log('Operation cancelled');
2941
process.exit(0);
3042
}
3143
};
3244

33-
const migrate = async () => {
45+
const formatMigrationName = (input: string) =>
46+
input
47+
.trim()
48+
.toLowerCase()
49+
.replace(/[^a-z0-9]+/g, '_')
50+
.replace(/^_+|_+$/g, '');
51+
52+
const promptMigrationName = async () => {
53+
while (true) {
54+
const rawName = (await askQuestion('Enter a migration name: ')).trim();
55+
const formattedName = formatMigrationName(rawName);
56+
57+
if (!formattedName) {
58+
console.log('Migration name cannot be empty. Please try again.');
59+
continue;
60+
}
61+
62+
if (formattedName !== rawName) {
63+
console.log(`Using sanitized migration name: ${formattedName}`);
64+
}
65+
66+
return formattedName;
67+
}
68+
};
69+
70+
const timestampPrefix = () => new Date().toISOString().replace(/\D/g, '').slice(0, 14);
71+
72+
const generateMigrationFile = async () => {
73+
const migrationName = await promptMigrationName();
74+
const folderName = `${timestampPrefix()}_${migrationName}`;
75+
const migrationDir = path.join(MIGRATION_FILES_DIR, folderName);
76+
const migrationSqlPath = path.join(migrationDir, 'migration.sql');
77+
const diffUrl = getEnvVariable('STACK_DIRECT_DATABASE_CONNECTION_STRING');
78+
79+
console.log(`Generating migration ${folderName}...`);
80+
const diffResult = spawnSync(
81+
'pnpm',
82+
[
83+
'-s',
84+
'prisma',
85+
'migrate',
86+
'diff',
87+
'--from-url',
88+
diffUrl,
89+
'--to-schema-datamodel',
90+
'prisma/schema.prisma',
91+
'--script',
92+
],
93+
{
94+
cwd: process.cwd(),
95+
encoding: 'utf8',
96+
},
97+
);
98+
99+
if (diffResult.error || diffResult.status !== 0) {
100+
console.error(diffResult.stdout);
101+
console.error(diffResult.stderr);
102+
throw diffResult.error ?? new Error(`Failed to generate migration (exit code ${diffResult.status})`);
103+
}
104+
105+
const sql = diffResult.stdout;
106+
107+
if (!sql.trim()) {
108+
console.log('No schema changes detected. Migration file was not created.');
109+
} else {
110+
fs.mkdirSync(migrationDir, { recursive: true });
111+
fs.writeFileSync(migrationSqlPath, sql, 'utf8');
112+
console.log(`Migration written to ${path.relative(process.cwd(), migrationSqlPath)}`);
113+
console.log('Applying migration...');
114+
await migrate([{ migrationName: folderName, sql }]);
115+
}
116+
};
117+
118+
const migrate = async (selectedMigrationFiles?: { migrationName: string, sql: string }[]) => {
34119
const startTime = performance.now();
35-
const migrationFiles = getMigrationFiles(MIGRATION_FILES_DIR);
120+
const migrationFiles = selectedMigrationFiles ?? getMigrationFiles(MIGRATION_FILES_DIR);
36121
const totalMigrations = migrationFiles.length;
37122

38123
const result = await applyMigrations({
@@ -98,10 +183,9 @@ const main = async () => {
98183
}
99184
case 'generate-migration-file': {
100185
await promptDropDb();
101-
execSync('pnpm prisma migrate reset --force --skip-seed', { stdio: 'inherit' });
102-
execSync('pnpm prisma migrate dev --skip-seed', { stdio: 'inherit' });
103186
await dropSchema();
104187
await migrate();
188+
await generateMigrationFile();
105189
await seed();
106190
break;
107191
}

0 commit comments

Comments
 (0)