> ## Documentation Index
> Fetch the complete documentation index at: https://synthetiq.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Database reference

> Prisma schema conventions, built-in models, multi-tenant support, and development vs. production databases

## Schema location

`prisma/schema.prisma`

## Applying schema changes

```bash theme={null}
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.

<Note>
  Do not add a `url` property to the datasource block in `schema.prisma`. Database URLs are configured in `prisma.config.ts`.
</Note>

## Built-in models

### User (do not modify fields)

```prisma theme={null}
model User {
  id            String   @id
  externalId    String?  @unique
  email         String?
  emailVerified Boolean  @default(false)
  name          String?
  picture       String?
  lastSeenAt    DateTime @default(now()) @updatedAt
}
```

You may add relations to `User` from other models. Use `externalId` to map to internal user IDs.

<Tip>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.</Tip>

### Organization

For multi-tenant apps, use the built-in organization tables:

```prisma theme={null}
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])
}
```

Filter queries by `ctx.currentOrgId` for organization-scoped data:

```typescript theme={null}
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`                                                                                                                                                              |

These models are managed by the framework and their RLS policies are configured in `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:

```typescript theme={null}
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:

```typescript theme={null}
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 |

The framework handles the difference transparently.

## User identity

| Context             | How to get user ID  |
| ------------------- | ------------------- |
| Server (procedures) | `ctx.userId`        |
| Client (components) | `useAuth().user.id` |

Never accept user identity as tRPC input — it cannot be considered authentic, and may break due to procedure-level or RLS-level scoping. Always use `ctx.userId` on the backend.