# ACAI — Chapter 37: Database Connection, ORM Configuration, Migrations & First Working Operations

![Post cover](https://dev-to-uploads.s3.us-east-2.amazonaws.com/uploads/articles/g7izxo38punw5yo47qof.png align="center")

  

## 37.1 Introduction

The previous chapter established the database stack and selected PostgreSQL as the primary relational database. This chapter converts that architectural decision into an executable backend implementation.

The objective is not merely to create tables. A production-grade AI platform requires a controlled mechanism for:

*   connecting application code to the database;
    
*   defining schemas;
    
*   validating relationships;
    
*   creating and versioning migrations;
    
*   inserting initial data;
    
*   reading and updating records safely;
    
*   executing transactions;
    
*   separating development, staging, and production environments;
    
*   preventing database credentials from reaching the browser;
    
*   providing a maintainable data-access layer.
    

For the implementation described in this chapter, Prisma is used as the ORM layer.

The architecture becomes:

```text
Next.js Application
        │
        ▼
Application / Service Layer
        │
        ▼
Repository / Data Access Layer
        │
        ▼
Prisma ORM
        │
        ▼
PostgreSQL
```

The browser must never communicate directly with PostgreSQL.

* * *

# 37.2 Implementation Objectives

At the end of this chapter, the system should support the following flow:

```text
User Request
     │
     ▼
API Route / Server Action
     │
     ▼
Validation
     │
     ▼
Service Layer
     │
     ▼
Repository
     │
     ▼
Prisma
     │
     ▼
PostgreSQL
```

The initial implementation will establish:

1.  PostgreSQL connection
    
2.  Prisma installation
    
3.  Prisma schema
    
4.  Database migration
    
5.  Prisma Client generation
    
6.  Database seed
    
7.  Database singleton
    
8.  Repository functions
    
9.  CRUD operations
    
10.  Transaction handling
     
11.  API endpoint
     
12.  Error handling
     
13.  Development database verification
     

* * *

# 37.3 Project Structure

A recommended project structure is:

```text
acai/
│
├── src/
│   ├── app/
│   │   └── api/
│   │       └── users/
│   │           └── route.ts
│   │
│   ├── lib/
│   │   ├── prisma.ts
│   │   └── validation.ts
│   │
│   ├── repositories/
│   │   └── user.repository.ts
│   │
│   └── services/
│       └── user.service.ts
│
├── prisma/
│   ├── schema.prisma
│   └── seed.ts
│
├── .env
├── .env.example
├── package.json
└── tsconfig.json
```

This separation is important.

The API route should not contain large amounts of database logic.

Instead:

```text
API
 ↓
Service
 ↓
Repository
 ↓
Prisma
```

This makes the system easier to test and maintain.

* * *

# 37.4 Installing Prisma

From the project root:

```bash
npm install prisma @prisma/client
```

If PowerShell prevents the `npm` command from running, use:

```bash
npm.cmd install prisma @prisma/client
```

Alternatively, open Command Prompt (`cmd.exe`) and execute the normal command.

Initialize Prisma:

```bash
npx prisma init
```

If necessary:

```bash
npx.cmd prisma init
```

This creates:

```text
prisma/
└── schema.prisma

.env
```

* * *

# 37.5 PostgreSQL Database

Create a PostgreSQL database for development.

Example database name:

```text
acai_dev
```

The connection string conceptually looks like:

```text
postgresql://USERNAME:PASSWORD@HOST:PORT/DATABASE
```

For a local installation, an example might be:

```text
postgresql://postgres:YOUR_PASSWORD@localhost:5432/acai_dev
```

The actual password must never be committed to Git.

* * *

# 37.6 Environment Configuration

The `.env` file should contain the database connection:

```env
DATABASE_URL="postgresql://postgres:YOUR_PASSWORD@localhost:5432/acai_dev"
```

Do not place the real password into source-controlled files.

Create `.env.example`:

```env
DATABASE_URL="postgresql://USER:PASSWORD@HOST:5432/DATABASE"
```

The `.env.example` file is safe to commit because it contains a placeholder.

The real `.env` should be excluded from Git.

Example `.gitignore`:

```gitignore
node_modules
.next
.env
.env.local
.env.production
```

* * *

# 37.7 Initial Prisma Schema

The initial schema should remain manageable.

A practical first implementation can start with:

*   User
    
*   Project
    
*   Conversation
    
*   Message
    
*   File
    
*   AuditLog
    

Additional models can be introduced through later migrations.

Example:

```prisma
generator client {
  provider = "prisma-client-js"
}

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

enum UserStatus {
  ACTIVE
  SUSPENDED
  DELETED
}

enum ProjectStatus {
  ACTIVE
  ARCHIVED
  DELETED
}

enum ConversationStatus {
  ACTIVE
  ARCHIVED
  DELETED
}

enum MessageRole {
  SYSTEM
  USER
  ASSISTANT
  TOOL
}

enum FileStatus {
  UPLOADING
  UPLOADED
  PROCESSING
  READY
  FAILED
  DELETED
}

model User {
  id           String       @id @default(uuid())
  email        String       @unique
  name         String?
  status       UserStatus   @default(ACTIVE)

  projects     Project[]
  conversations Conversation[]
  files        File[]
  auditLogs    AuditLog[]

  createdAt    DateTime     @default(now())
  updatedAt    DateTime     @updatedAt
}

model Project {
  id           String        @id @default(uuid())
  name         String
  description  String?
  status       ProjectStatus  @default(ACTIVE)

  ownerId      String
  owner        User          @relation(fields: [ownerId], references: [id], onDelete: Cascade)

  conversations Conversation[]
  files         File[]

  createdAt    DateTime      @default(now())
  updatedAt    DateTime      @updatedAt

  @@index([ownerId])
  @@index([status])
}

model Conversation {
  id           String             @id @default(uuid())
  title        String?
  status       ConversationStatus @default(ACTIVE)

  userId       String
  user          User              @relation(fields: [userId], references: [id], onDelete: Cascade)

  projectId    String?
  project      Project?           @relation(fields: [projectId], references: [id], onDelete: SetNull)

  messages     Message[]

  createdAt    DateTime           @default(now())
  updatedAt    DateTime           @updatedAt

  @@index([userId])
  @@index([projectId])
}

model Message {
  id             String       @id @default(uuid())
  role           MessageRole
  content        String

  conversationId String
  conversation   Conversation @relation(fields: [conversationId], references: [id], onDelete: Cascade)

  metadata       Json?

  createdAt      DateTime     @default(now())

  @@index([conversationId])
}

model File {
  id          String     @id @default(uuid())
  name        String
  mimeType    String
  sizeBytes   BigInt
  status      FileStatus @default(UPLOADING)

  ownerId     String
  owner       User       @relation(fields: [ownerId], references: [id], onDelete: Cascade)

  projectId   String?
  project     Project?   @relation(fields: [projectId], references: [id], onDelete: SetNull)

  storageKey  String?

  metadata    Json?

  createdAt   DateTime   @default(now())
  updatedAt   DateTime   @updatedAt

  @@index([ownerId])
  @@index([projectId])
  @@index([status])
}

model AuditLog {
  id          String   @id @default(uuid())

  userId      String?
  user        User?    @relation(fields: [userId], references: [id], onDelete: SetNull)

  action      String
  resource    String?
  resourceId  String?
  metadata    Json?

  createdAt   DateTime @default(now())

  @@index([userId])
  @@index([action])
  @@index([resource])
  @@index([createdAt])
}
```

This schema represents the first executable database layer.

* * *

# 37.8 Understanding Relationships

The most important relationships are:

```text
User
 │
 ├── Projects
 │
 ├── Conversations
 │      │
 │      └── Messages
 │
 ├── Files
 │
 └── Audit Logs
```

A user can own multiple projects.

A project can contain multiple conversations.

A conversation can contain multiple messages.

A user can own multiple files.

Audit records can optionally identify the user responsible for an action.

* * *

# 37.9 Running the First Migration

After configuring the schema:

```bash
npx prisma migrate dev --name init
```

Or:

```bash
npx.cmd prisma migrate dev --name init
```

Prisma will create a migration directory similar to:

```text
prisma/
└── migrations/
    └── 20260904_init/
        └── migration.sql
```

The exact generated folder name may differ.

The migration is the version-controlled history of database structure.

* * *

# 37.10 Why Migrations Matter

Without migrations, developers may manually change databases and eventually lose track of what changed.

With migrations:

```text
Migration 001
     ↓
Migration 002
     ↓
Migration 003
     ↓
Migration 004
```

Every environment can progressively reach the same schema state.

A migration should be treated as part of the application's source code.

* * *

# 37.11 Prisma Client

After the schema has been processed, Prisma Client provides typed database access.

Example:

```ts
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();
```

However, creating a new Prisma client repeatedly during development can create too many database connections.

Therefore the application should use a singleton pattern.

* * *

# 37.12 Prisma Singleton

Create:

```text
src/lib/prisma.ts
```

Code:

```ts
import { PrismaClient } from "@prisma/client";

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient();

if (process.env.NODE_ENV !== "production") {
  globalForPrisma.prisma = prisma;
}
```

The application can now import:

```ts
import { prisma } from "@/lib/prisma";
```

This creates a consistent database access point.

* * *

# 37.13 Repository Layer

Create:

```text
src/repositories/user.repository.ts
```

Example:

```ts
import { prisma } from "@/lib/prisma";

export async function createUser(
  email: string,
  name?: string
) {
  return prisma.user.create({
    data: {
      email,
      name,
    },
  });
}

export async function findUserById(id: string) {
  return prisma.user.findUnique({
    where: {
      id,
    },
  });
}

export async function findUserByEmail(email: string) {
  return prisma.user.findUnique({
    where: {
      email,
    },
  });
}

export async function updateUser(
  id: string,
  data: {
    name?: string;
  }
) {
  return prisma.user.update({
    where: {
      id,
    },
    data,
  });
}
```

The repository abstracts database operations.

The API does not need to know how Prisma queries PostgreSQL.

* * *

# 37.14 Service Layer

Create:

```text
src/services/user.service.ts
```

Example:

```ts
import {
  createUser,
  findUserByEmail,
} from "@/repositories/user.repository";

export async function registerUser(
  email: string,
  name?: string
) {
  const existingUser =
    await findUserByEmail(email);

  if (existingUser) {
    throw new Error("User already exists");
  }

  return createUser(email, name);
}
```

The service layer contains business rules.

The repository contains database access.

This distinction is important.

```text
Repository = HOW data is stored/retrieved

Service = WHY and WHEN an operation is allowed
```

* * *

# 37.15 API Route

Create:

```text
src/app/api/users/route.ts
```

Example:

```ts
import { NextResponse } from "next/server";
import { registerUser } from "@/services/user.service";

export async function POST(request: Request) {
  try {
    const body = await request.json();

    const email = body.email;
    const name = body.name;

    if (!email || typeof email !== "string") {
      return NextResponse.json(
        {
          error: "Valid email is required",
        },
        {
          status: 400,
        }
      );
    }

    const user = await registerUser(
      email,
      name
    );

    return NextResponse.json(
      {
        id: user.id,
        email: user.email,
        name: user.name,
      },
      {
        status: 201,
      }
    );
  } catch (error) {
    console.error(error);

    return NextResponse.json(
      {
        error: "Unable to create user",
      },
      {
        status: 500,
      }
    );
  }
}
```

The browser now communicates with the API rather than directly with PostgreSQL.

* * *

# 37.16 Database Read Operation

A server-side operation can retrieve a user:

```ts
import { prisma } from "@/lib/prisma";

const user = await prisma.user.findUnique({
  where: {
    email: "user@example.com",
  },
});
```

The result can then be processed by the application.

* * *

# 37.17 Database Update Operation

Example:

```ts
const updatedUser =
  await prisma.user.update({
    where: {
      id: userId,
    },
    data: {
      name: "Updated Name",
    },
  });
```

Only the intended fields should be updated.

* * *

# 37.18 Database Delete Operation

A hard delete can be performed using:

```ts
await prisma.user.delete({
  where: {
    id: userId,
  },
});
```

However, for important records, hard deletion should be considered carefully.

For many production systems, a soft-delete strategy is preferable.

For example:

```text
status = DELETED
```

rather than immediately destroying the record.

* * *

# 37.19 Transactions

Some operations require multiple database changes to succeed together.

For example:

```text
Create Project
     +
Create Audit Log
```

If project creation succeeds but audit logging fails, the system may become inconsistent.

A transaction can solve this.

Example:

```ts
const result = await prisma.$transaction(
  async (tx) => {
    const project = await tx.project.create({
      data: {
        name: "Research Project",
        ownerId: userId,
      },
    });

    await tx.auditLog.create({
      data: {
        userId,
        action: "PROJECT_CREATED",
        resource: "project",
        resourceId: project.id,
      },
    });

    return project;
  }
);
```

The intended principle is:

```text
Transaction starts
       ↓
Operation A
       ↓
Operation B
       ↓
Success
       ↓
Commit
```

If a required operation fails:

```text
Transaction starts
       ↓
Operation A
       ↓
Operation B fails
       ↓
Rollback
```

This prevents partially completed database workflows.

* * *

# 37.20 Seed Data

Development environments benefit from predictable sample data.

Create:

```text
prisma/seed.ts
```

Example:

```ts
import {
  PrismaClient,
  UserStatus,
} from "@prisma/client";

const prisma = new PrismaClient();

async function main() {
  const user = await prisma.user.upsert({
    where: {
      email: "demo@example.com",
    },
    update: {},
    create: {
      email: "demo@example.com",
      name: "Demo User",
      status: UserStatus.ACTIVE,
    },
  });

  const project =
    await prisma.project.create({
      data: {
        name: "Demo Project",
        description:
          "Development demonstration project",
        ownerId: user.id,
      },
    });

  const conversation =
    await prisma.conversation.create({
      data: {
        title: "Demo Conversation",
        userId: user.id,
        projectId: project.id,
      },
    });

  await prisma.message.create({
    data: {
      conversationId: conversation.id,
      role: "USER",
      content: "Hello ACAI.",
    },
  });

  console.log("Seed completed.");
}

main()
  .catch((error) => {
    console.error(error);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });
```

The seed system should only contain non-sensitive development data.

Do not insert real user credentials, API keys, payment information, or private files.

* * *

# 37.21 Database Inspection

Prisma provides a visual database interface:

```bash
npx prisma studio
```

This allows developers to inspect records during development.

For example:

```text
User
 ├── demo@example.com
 │
Project
 └── Demo Project
     │
Conversation
     └── Demo Conversation
         │
Message
         └── Hello ACAI.
```

This is particularly useful when learning database relationships.

* * *

# 37.22 Development Workflow

The recommended workflow is:

```text
1. Edit schema.prisma
        ↓
2. Create migration
        ↓
3. Apply migration
        ↓
4. Generate Prisma Client
        ↓
5. Seed development data
        ↓
6. Start application
        ↓
7. Test API
        ↓
8. Inspect database
```

Typical development commands:

```bash
npx prisma migrate dev
```

```bash
npx prisma generate
```

```bash
npx prisma studio
```

The exact seed command/configuration should follow the Prisma version installed in the project.

* * *

# 37.23 Database Security Boundary

The architecture must enforce:

```text
Browser
   │
   │ HTTPS
   ▼
Next.js API
   │
   │ Server-side credentials
   ▼
Prisma
   │
   ▼
PostgreSQL
```

Never:

```text
Browser
   │
   ▼
PostgreSQL
```

Never expose:

```text
DATABASE_URL
POSTGRES_PASSWORD
DATABASE_USERNAME
PRIVATE_DATABASE_HOST
```

to client-side JavaScript.

* * *

# 37.24 Input Validation

Database constraints are not a replacement for application validation.

For example:

```text
Application validation
        +
Database constraints
        +
Authorization
```

should work together.

An email field should be validated before attempting the database operation.

A project ID should be checked for ownership before allowing modifications.

A user should not be able to modify another user's project merely because they know its identifier.

* * *

# 37.25 Authorization

A request such as:

```text
PATCH /api/projects/123
```

must not mean:

```text
"If project 123 exists, modify it."
```

Instead:

```text
Authenticate user
      ↓
Find project
      ↓
Check ownership/access
      ↓
Validate requested change
      ↓
Update project
      ↓
Write audit event
```

This prevents a common class of access-control failures.

* * *

# 37.26 Audit Logging

Security-sensitive operations should be auditable.

Examples include:

```text
USER_CREATED
PROJECT_CREATED
PROJECT_UPDATED
PROJECT_DELETED
FILE_UPLOADED
FILE_DELETED
API_KEY_CREATED
API_KEY_REVOKED
MODEL_REQUEST_STARTED
MODEL_REQUEST_COMPLETED
MODEL_REQUEST_FAILED
ADMIN_ACTION
```

Audit logs should avoid storing unnecessary sensitive information.

For example, do not place passwords or secret API keys inside:

```text
metadata
```

* * *

# 37.27 Error Handling

Database errors should not be exposed directly to end users.

Bad:

```json
{
  "error": "PrismaClientKnownRequestError: ..."
}
```

Better:

```json
{
  "error": "Unable to complete the requested operation."
}
```

Detailed technical errors should be logged securely on the server.

The user receives a safe error message.

* * *

# 37.28 Development, Staging and Production

The database environment should be separated:

```text
Development
    ↓
PostgreSQL Development DB

Staging
    ↓
PostgreSQL Staging DB

Production
    ↓
PostgreSQL Production DB
```

Never use the production database as a testing playground.

A developer should not run destructive reset commands against production.

* * *

# 37.29 Database Reset During Development

During early development, developers may need to reset the local database.

This is acceptable only for disposable development data.

Production databases must use controlled migration and recovery procedures instead.

The fundamental rule is:

```text
Development data = disposable

Production data = protected
```

* * *

# 37.30 First End-to-End Test

Start PostgreSQL.

Verify:

```text
DATABASE_URL
```

Then run migrations.

Start the Next.js application:

```bash
npm run dev
```

If PowerShell blocks npm scripts:

```bash
npm.cmd run dev
```

Then send a POST request to:

```text
/api/users
```

with:

```json
{
  "email": "test@example.com",
  "name": "Test User"
}
```

The expected conceptual flow is:

```text
HTTP Request
     ↓
Next.js API
     ↓
Validation
     ↓
User Service
     ↓
User Repository
     ↓
Prisma
     ↓
PostgreSQL
     ↓
Created User
     ↓
JSON Response
```

* * *

# 37.31 Verification Checklist

Before continuing to the next chapter, verify:

```text
[ ] PostgreSQL is running
[ ] DATABASE_URL exists
[ ] Prisma is installed
[ ] schema.prisma exists
[ ] Migration completed
[ ] Prisma Client generated
[ ] Database tables exist
[ ] Seed data can be inserted
[ ] Prisma Studio opens
[ ] Prisma singleton works
[ ] Repository works
[ ] Service works
[ ] API route works
[ ] Transaction example works
[ ] .env is ignored by Git
[ ] No credentials are exposed to the browser
```

* * *

# 37.32 Failure Isolation

If the system fails, diagnose from the bottom upward.

### Case 1 — PostgreSQL unavailable

Check:

```text
PostgreSQL service
     ↓
Host
     ↓
Port
```

### Case 2 — Authentication failure

Check:

```text
Username
Password
Database
```

### Case 3 — Prisma migration failure

Check:

```text
schema.prisma
DATABASE_URL
database permissions
existing schema
```

### Case 4 — API failure

Check:

```text
API route
     ↓
Service
     ↓
Repository
     ↓
Prisma
```

### Case 5 — Client-side database error

This usually indicates an architectural mistake.

Database access should remain server-side.

* * *

# 37.33 Architectural Result

At the end of Chapter 37, the architecture has moved from conceptual design to a working implementation:

```text
                 ACAI
                   │
          ┌────────┴────────┐
          │                 │
       Frontend           Backend
          │                 │
          │             API Routes
          │                 │
          │             Services
          │                 │
          │            Repositories
          │                 │
          │              Prisma
          │                 │
          └────────── PostgreSQL
```

The database is now a real executable component rather than merely a design document.

* * *

# 37.34 Security Principle

The most important principle introduced in this chapter is separation of responsibility.

```text
UI
 ↓
API
 ↓
Validation
 ↓
Authorization
 ↓
Service
 ↓
Repository
 ↓
ORM
 ↓
Database
```

Each layer has a defined responsibility.

This makes the system easier to audit, test, secure, and extend.

* * *

# 37.35 Conclusion

Chapter 37 established the first concrete database implementation for ACAI.

The system now has:

*   PostgreSQL as the relational database;
    
*   Prisma as the ORM;
    
*   version-controlled migrations;
    
*   typed database access;
    
*   a reusable Prisma singleton;
    
*   repository abstraction;
    
*   service-layer business logic;
    
*   API integration;
    
*   transactions;
    
*   development seed data;
    
*   database inspection;
    
*   security boundaries;
    
*   environment separation;
    
*   audit logging foundations.
    

This provides the foundation required for the next major subsystem.

The next chapter will extend the database layer into **authentication, sessions, user identity, authorization, ownership checks, role-based access control, and secure API access**, while keeping database credentials and privileged operations strictly server-side.

**END OF CHAPTER 37**