Schema location
prisma/schema.prisma
Applying schema changes
pnpm install --silent && pnpm run db:push && pnpm run db:generate
pnpm install --silent is required first because prisma.config.ts imports from the prisma package.
Do not add a url property to the datasource block in schema.prisma. Database URLs are configured in prisma.config.ts.
Built-in models
User (do not modify fields)
model User {
id String @id
externalId String? @unique
email String?
emailVerified Boolean @default(false)
name String?
picture String?
lastSeenAt DateTime @default(now()) @updatedAt
}
User from other models. Use externalId to map to internal user IDs.
To store additional user attributes, create a separate table with a relation to User (e.g., a UserProfile model) rather than modifying the User model directly.
Organization
For multi-tenant apps, use the built-in organization tables:
model Organization {
id String @id @default(cuid())
name String
externalId String? @unique
createdAt DateTime @default(now())
members OrganizationMember[]
}
model OrganizationMember {
orgId String
userId String
org Organization @relation(fields: [orgId], references: [id], onDelete: Cascade)
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
assignedAt DateTime @default(now())
@@id([orgId, userId])
@@index([userId])
}
ctx.currentOrgId for organization-scoped data:
getSummaries: scopedProcedure([])
.meta({ description: 'Get summaries for the current organization' })
.query(async ({ ctx }) => {
if (!ctx.currentOrgId) {
throw new TRPCError({ code: 'BAD_REQUEST', message: 'No organization selected' });
}
return ctx.db.summary.findMany({
where: { orgId: ctx.currentOrgId },
});
}),
Additional built-in models
Beyond User and Organization, the scaffolded schema includes framework-managed models for:
| Category | Models |
|---|
| Services | AppService, AppServiceSetting, AppServiceCustomAuthSetting, AppServiceSystemCredential, AppServiceUserCredential, AppServiceOAuthSystemCredential, AppServiceOAuthUserCredential, AppServiceOAuthState |
| OAuth | OAuthClient, OAuthAuthorizationCode, OAuthRefreshToken, OAuthConsent |
| Identity providers | IdentityProvider, IdentityProviderLink, IdpAuthSession |
| Workflows | WfJob, WfSchedule, WfStepLog, WfDraft, WfWorker |
scopes.json. You do not need to modify them.
Database access
Always use ctx.db in procedure handlers. It injects RLS context automatically.
Indexing requirements
Prisma does not automatically create indexes on foreign key columns. Add @@index explicitly for:
| Column type | Reason |
|---|
| Foreign key columns | Efficient joins |
| WHERE clause columns | Efficient filtering |
| ORDER BY columns | Efficient sorting |
| Membership columns | RLS group membership lookups |
Relations and data fetching
Use Prisma include to fetch related data in a single query:
getTasksWithAuthors: scopedProcedure([])
.meta({ description: 'Get tasks with author details' })
.query(async ({ ctx }) => {
return ctx.db.task.findMany({
where: { userId: ctx.userId },
include: { author: { select: { id: true, name: true, email: true, picture: true } } },
orderBy: { createdAt: 'desc' },
});
}),
Transactions
Use Prisma transactions for operations that must succeed or fail together:
transferTask: scopedProcedure(['tasks:editAll'])
.meta({ description: 'Transfer a task to another user' })
.input(z.object({ taskId: z.string(), toUserId: z.string() }))
.mutation(async ({ ctx, input }) => {
return ctx.db.$transaction([
ctx.db.task.update({
where: { id: input.taskId },
data: { userId: input.toUserId },
}),
ctx.db.taskHistory.create({
data: {
taskId: input.taskId,
action: 'transferred',
performedBy: ctx.userId,
},
}),
]);
}),
Environment differences
| Environment | Database | RLS enforcement |
|---|
| Development | SQLite (LibSQL) | Application-level filtering |
| Production | PostgreSQL | Database-level RLS policies |
User identity
| Context | How to get user ID |
|---|
| Server (procedures) | ctx.userId |
| Client (components) | useAuth().user.id |
ctx.userId on the backend.
