Database Integration

Purpose

Implement reliable and efficient database operations using the ORM, including queries, transactions, and migrations.

When to Use

• Data model implementation

• Query optimization

• Transaction handling

• Migration writing

Prerequisites

• Database schema defined

• ORM configured (Prisma)

• Connection pooling set up

Process

Step 1: Define Schema

Prisma schema:

• Models
• Relations
• Indexes
• Constraints

Step 2: Generate Client

Commands:

• prisma generate
• prisma migrate dev

Step 3: Implement Repository

Repository pattern:

• CRUD operations
• Complex queries
• Transactions

Step 4: Optimize Queries

Optimization:

• Select only needed fields
• Use indexes
• Avoid N+1 queries
• Use transactions

Step 5: Handle Errors

Error handling:

• Constraint violations
• Connection errors
• Deadlocks
• Timeouts

Inputs

Input Type Required Description

database_schema SQL Yes Schema design

queries Markdown Yes Required queries

Outputs

Output Type Description

schema.prisma Prisma Schema definition

migrations/ SQL Migration files

repositories/ TypeScript Data access

StudyAbroad-Specific Considerations

• User data privacy

• Application audit trail

• Soft deletes for GDPR

• Efficient search queries

Integration Points

• Architecture Agent: Schema design

• Security Agent: Data protection

• DevOps Agent: Migrations

Examples

// prisma/schema.prisma generator client { provider = "prisma-client-js" }

datasource db { provider = "postgresql" url = env("DATABASE_URL") }

model User { id String @id @default(uuid()) email String @unique passwordHash String? firstName String lastName String dateOfBirth DateTime? nationality String? gdprConsent Boolean @default(false) gdprConsentAt DateTime? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt deletedAt DateTime? // Soft delete for GDPR

applications Application[] documents Document[] oauthAccounts OAuthAccount[]

@@index([email]) @@index([deletedAt]) }

model Application { id String @id @default(uuid()) userId String programId String status String @default("draft") progress Int @default(0) submittedAt DateTime? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt

user User @relation(fields: [userId], references: [id]) program Program @relation(fields: [programId], references: [id]) documents Document[] statusHistory ApplicationStatusHistory[]

@@unique([userId, programId]) @@index([userId]) @@index([status]) }

model ApplicationStatusHistory { id String @id @default(uuid()) applicationId String oldStatus String? newStatus String changedBy String? notes String? createdAt DateTime @default(now())

application Application @relation(fields: [applicationId], references: [id])

@@index([applicationId]) }

// src/prisma/prisma.service.ts import { Injectable, OnModuleInit, OnModuleDestroy } from '@nestjs/common' import { PrismaClient, Prisma } from '@prisma/client'

@Injectable() export class PrismaService extends PrismaClient implements OnModuleInit, OnModuleDestroy { constructor() { super({ log: [ { emit: 'event', level: 'query' }, { emit: 'stdout', level: 'error' }, { emit: 'stdout', level: 'warn' } ] }) }

async onModuleInit() { await this.$connect()

// Soft delete middleware
this.$use(async (params, next) => {
  if (params.model === 'User') {
    if (params.action === 'delete') {
      params.action = 'update'
      params.args['data'] = { deletedAt: new Date() }
    }
    if (params.action === 'deleteMany') {
      params.action = 'updateMany'
      if (params.args.data !== undefined) {
        params.args.data['deletedAt'] = new Date()
      } else {
        params.args['data'] = { deletedAt: new Date() }
      }
    }
    // Filter out soft-deleted by default
    if (params.action === 'findUnique' || params.action === 'findFirst') {
      params.action = 'findFirst'
      params.args.where['deletedAt'] = null
    }
    if (params.action === 'findMany') {
      if (params.args.where) {
        if (params.args.where.deletedAt === undefined) {
          params.args.where['deletedAt'] = null
        }
      } else {
        params.args['where'] = { deletedAt: null }
      }
    }
  }
  return next(params)
})

}

async onModuleDestroy() { await this.$disconnect() }

// Transaction helper async executeInTransaction<T>( fn: (prisma: Prisma.TransactionClient) => Promise<T> ): Promise<T> { return this.$transaction(fn, { maxWait: 5000, timeout: 10000, isolationLevel: Prisma.TransactionIsolationLevel.Serializable }) } }

// src/application/application.repository.ts @Injectable() export class ApplicationRepository { constructor(private readonly prisma: PrismaService) {}

async findByUserWithPrograms( userId: string, options: { status?: string; page: number; limit: number } ) { const { status, page, limit } = options const where: Prisma.ApplicationWhereInput = { userId }

if (status) {
  where.status = status
}

const [applications, total] = await Promise.all([
  this.prisma.application.findMany({
    where,
    include: {
      program: {
        include: {
          university: true
        }
      },
      documents: {
        select: { id: true, documentType: true, fileName: true }
      }
    },
    skip: (page - 1) * limit,
    take: limit,
    orderBy: { createdAt: 'desc' }
  }),
  this.prisma.application.count({ where })
])

return { applications, total }

}

async submitWithHistory( id: string, userId: string ): Promise<Application> { return this.prisma.executeInTransaction(async (tx) => { // Get current application const application = await tx.application.findFirst({ where: { id, userId } })

  if (!application) {
    throw new NotFoundException()
  }

  // Update status
  const updated = await tx.application.update({
    where: { id },
    data: {
      status: 'submitted',
      submittedAt: new Date()
    }
  })

  // Create history record
  await tx.applicationStatusHistory.create({
    data: {
      applicationId: id,
      oldStatus: application.status,
      newStatus: 'submitted',
      changedBy: userId
    }
  })

  return updated
})

} }

Validation

• Schema matches design

• Migrations run cleanly

• Queries are efficient

• Transactions used correctly

• Errors handled properly