Black Shadow Team ©

Posted on Sep 4

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

#ai

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:

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:

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

The initial implementation will establish:

PostgreSQL connection
Prisma installation
Prisma schema
Database migration
Prisma Client generation
Database seed
Database singleton
Repository functions
CRUD operations
Transaction handling
API endpoint
Error handling
Development database verification

37.3 Project Structure

A recommended project structure is:

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:

API
 ↓
Service
 ↓
Repository
 ↓
Prisma

This makes the system easier to test and maintain.

37.4 Installing Prisma

From the project root:

npm install prisma @prisma/client

If PowerShell prevents the npm command from running, use:

npm.cmd install prisma @prisma/client

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

Initialize Prisma:

npx prisma init

If necessary:

npx.cmd prisma init

This creates:

prisma/
└── schema.prisma

.env

37.5 PostgreSQL Database

Create a PostgreSQL database for development.

Example database name:

acai_dev

The connection string conceptually looks like:

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

For a local installation, an example might be:

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:

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

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

Create .env.example:

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:

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:

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:

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:

npx prisma migrate dev --name init

Or:

npx.cmd prisma migrate dev --name init

Prisma will create a migration directory similar to:

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:

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:

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:

src/lib/prisma.ts

Code:

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:

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

This creates a consistent database access point.

37.13 Repository Layer

Create:

src/repositories/user.repository.ts

Example:

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:

src/services/user.service.ts

Example:

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.

Repository = HOW data is stored/retrieved

Service = WHY and WHEN an operation is allowed

37.15 API Route

Create:

src/app/api/users/route.ts

Example:

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:

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:

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:

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:

status = DELETED

rather than immediately destroying the record.

37.19 Transactions

Some operations require multiple database changes to succeed together.

For example:

Create Project
     +
Create Audit Log

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

A transaction can solve this.

Example:

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:

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

If a required operation fails:

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:

prisma/seed.ts

Example:

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:

npx prisma studio

This allows developers to inspect records during development.

For example:

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:

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:

npx prisma migrate dev

npx prisma generate

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:

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

Never:

Browser
   │
   ▼
PostgreSQL

Never expose:

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:

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:

PATCH /api/projects/123

must not mean:

"If project 123 exists, modify it."

Instead:

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:

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:

metadata

37.27 Error Handling

Database errors should not be exposed directly to end users.

Bad:

{
  "error": "PrismaClientKnownRequestError: ..."
}

Better:

{
  "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:

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:

Development data = disposable

Production data = protected

37.30 First End-to-End Test

Start PostgreSQL.

Verify:

DATABASE_URL

Then run migrations.

Start the Next.js application:

npm run dev

If PowerShell blocks npm scripts:

npm.cmd run dev

Then send a POST request to:

/api/users

with:

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

The expected conceptual flow is:

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:

[ ] 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:

PostgreSQL service
     ↓
Host
     ↓
Port

Case 2 — Authentication failure

Check:

Username
Password
Database

Case 3 — Prisma migration failure

Check:

schema.prisma
DATABASE_URL
database permissions
existing schema

Case 4 — API failure

Check:

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:

                 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.

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