# ACAI — Chapter 36: Database Stack Selection & Setup

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

  

## 36.1 Chapter Objective

In Chapter 35, we created the implementation-ready database blueprint.

Now we select the concrete technology stack and prepare the database layer for ACAI.

The recommended architecture is:

```text
ACAI Application
      │
      ▼
Next.js / Backend
      │
      ▼
ORM / Database Client
      │
      ▼
PostgreSQL
      │
      ├── Structured Data
      │
      └── Metadata
```

For retrieval:

```text
Documents
   ↓
Chunks
   ↓
Embeddings
   ↓
Vector Search
```

The database architecture should remain modular so that individual infrastructure components can be replaced later if necessary.

* * *

# 36.2 Recommended Primary Database

For the primary relational database, use:

```text
PostgreSQL
```

PostgreSQL is suitable for ACAI because the application requires:

```text
Users
Relationships
Transactions
Indexes
Constraints
JSON metadata
Complex queries
Scalability
```

* * *

# 36.3 Why PostgreSQL?

ACAI contains many strongly related entities.

For example:

```text
User
 ↓
Project
 ↓
Conversation
 ↓
Message
```

and:

```text
Project
 ↓
File
 ↓
Document
 ↓
Chunk
```

A relational database is well suited to these relationships.

* * *

# 36.4 ORM Layer

A database abstraction layer should sit between the application and PostgreSQL.

Conceptually:

```text
ACAI Services
      ↓
Repository Layer
      ↓
ORM
      ↓
PostgreSQL
```

An ORM can provide:

```text
Schema Definition
Type Safety
Queries
Migrations
Relationships
Transactions
```

The exact ORM can be selected according to the project's implementation preferences.

* * *

# 36.5 Database Environment

The project should support multiple environments:

```text
Development
Testing
Staging
Production
```

Each environment should have its own database configuration.

Example:

```text
Development DB
Testing DB
Staging DB
Production DB
```

Do not use the production database for local development.

* * *

# 36.6 Environment Variable

The application should obtain the database connection from an environment variable.

Conceptually:

```text
DATABASE_URL
```

Example structure:

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

The real production value must remain secret.

* * *

# 36.7 Environment File

Local development can use:

```text
.env
```

A safe template can use:

```text
.env.example
```

The example file should contain placeholders rather than real credentials.

Example:

```text
DATABASE_URL="postgresql://USER:PASSWORD@HOST:5432/acai"
```

This is documentation only.

* * *

# 36.8 Never Commit Secrets

The repository should normally ignore:

```text
.env
.env.local
.env.production
```

unless a particular environment requires a non-secret configuration file.

Never commit:

```text
Database passwords
API keys
Private tokens
Authentication secrets
Production credentials
```

* * *

# 36.9 Database Naming Convention

Use consistent naming.

Recommended:

```text
snake_case
```

Examples:

```text
user_id
project_id
created_at
updated_at
deleted_at
conversation_id
```

Consistency becomes especially important as the schema grows.

* * *

# 36.10 Table Naming

Use plural table names:

```text
users
projects
conversations
messages
files
documents
document_chunks
memories
agent_tasks
agent_steps
tool_calls
usage_records
audit_logs
```

This creates a predictable schema.

* * *

# 36.11 Primary Key Convention

Every major table should have:

```text
id
```

as its primary identifier.

Example:

```text
users
---------
id
email
name
```

The exact underlying ID format can be UUID, ULID, or another suitable strategy.

* * *

# 36.12 Foreign Key Convention

Use explicit names:

```text
user_id
project_id
conversation_id
file_id
document_id
task_id
step_id
```

Example:

```text
messages.conversation_id
```

references:

```text
conversations.id
```

* * *

# 36.13 Timestamp Convention

Use:

```text
created_at
updated_at
```

For lifecycle-specific events:

```text
started_at
completed_at
deleted_at
```

This provides a consistent temporal model.

* * *

# 36.14 User Table

Conceptual SQL:

```sql
CREATE TABLE users (
    id UUID PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name TEXT,
    status TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL
);
```

The actual production schema should use the database and ORM conventions selected for the project.

* * *

# 36.15 Project Table

```sql
CREATE TABLE projects (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    status TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    deleted_at TIMESTAMP,

    FOREIGN KEY (user_id)
        REFERENCES users(id)
);
```

* * *

# 36.16 Conversation Table

```sql
CREATE TABLE conversations (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    project_id UUID,
    title TEXT,
    status TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    deleted_at TIMESTAMP,

    FOREIGN KEY (user_id)
        REFERENCES users(id),

    FOREIGN KEY (project_id)
        REFERENCES projects(id)
);
```

* * *

# 36.17 Message Table

```sql
CREATE TABLE messages (
    id UUID PRIMARY KEY,
    conversation_id UUID NOT NULL,
    role TEXT NOT NULL,
    content TEXT NOT NULL,
    model TEXT,
    provider TEXT,
    input_tokens INTEGER,
    output_tokens INTEGER,
    created_at TIMESTAMP NOT NULL,

    FOREIGN KEY (conversation_id)
        REFERENCES conversations(id)
);
```

* * *

# 36.18 File Table

```sql
CREATE TABLE files (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    project_id UUID,
    name TEXT NOT NULL,
    mime_type TEXT NOT NULL,
    size BIGINT NOT NULL,
    storage_key TEXT NOT NULL,
    status TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    deleted_at TIMESTAMP,

    FOREIGN KEY (user_id)
        REFERENCES users(id),

    FOREIGN KEY (project_id)
        REFERENCES projects(id)
);
```

* * *

# 36.19 Document Table

```sql
CREATE TABLE documents (
    id UUID PRIMARY KEY,
    file_id UUID NOT NULL,
    project_id UUID,
    status TEXT NOT NULL,
    page_count INTEGER,
    text_length INTEGER,
    parser TEXT,
    processing_version TEXT,
    error_code TEXT,
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,

    FOREIGN KEY (file_id)
        REFERENCES files(id),

    FOREIGN KEY (project_id)
        REFERENCES projects(id)
);
```

* * *

# 36.20 Document Chunk Table

```sql
CREATE TABLE document_chunks (
    id UUID PRIMARY KEY,
    document_id UUID NOT NULL,
    project_id UUID,
    content TEXT NOT NULL,
    chunk_index INTEGER NOT NULL,
    token_count INTEGER,
    page_number INTEGER,
    section TEXT,
    created_at TIMESTAMP NOT NULL,

    FOREIGN KEY (document_id)
        REFERENCES documents(id)
);
```

Recommended constraint:

```sql
UNIQUE(document_id, chunk_index)
```

This prevents duplicate chunk positions within one document.

* * *

# 36.21 Memory Table

```sql
CREATE TABLE memories (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    project_id UUID,
    type TEXT NOT NULL,
    content TEXT NOT NULL,
    importance REAL,
    confidence REAL,
    source TEXT,
    status TEXT NOT NULL,
    last_used_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,

    FOREIGN KEY (user_id)
        REFERENCES users(id),

    FOREIGN KEY (project_id)
        REFERENCES projects(id)
);
```

* * *

# 36.22 Agent Task Table

```sql
CREATE TABLE agent_tasks (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    project_id UUID,
    conversation_id UUID,
    goal TEXT NOT NULL,
    status TEXT NOT NULL,
    current_step INTEGER,
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    started_at TIMESTAMP,
    completed_at TIMESTAMP,

    FOREIGN KEY (user_id)
        REFERENCES users(id),

    FOREIGN KEY (project_id)
        REFERENCES projects(id),

    FOREIGN KEY (conversation_id)
        REFERENCES conversations(id)
);
```

* * *

# 36.23 Agent Step Table

```sql
CREATE TABLE agent_steps (
    id UUID PRIMARY KEY,
    task_id UUID NOT NULL,
    step_number INTEGER NOT NULL,
    action TEXT NOT NULL,
    status TEXT NOT NULL,
    input JSONB,
    output JSONB,
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL,

    FOREIGN KEY (task_id)
        REFERENCES agent_tasks(id),

    UNIQUE(task_id, step_number)
);
```

* * *

# 36.24 Tool Call Table

```sql
CREATE TABLE tool_calls (
    id UUID PRIMARY KEY,
    task_id UUID NOT NULL,
    step_id UUID,
    tool_name TEXT NOT NULL,
    input JSONB,
    output JSONB,
    status TEXT NOT NULL,
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL,

    FOREIGN KEY (task_id)
        REFERENCES agent_tasks(id),

    FOREIGN KEY (step_id)
        REFERENCES agent_steps(id)
);
```

* * *

# 36.25 Usage Table

```sql
CREATE TABLE usage_records (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    project_id UUID,
    request_type TEXT NOT NULL,
    model TEXT,
    provider TEXT,
    input_tokens INTEGER,
    output_tokens INTEGER,
    tool_calls INTEGER,
    duration INTEGER,
    created_at TIMESTAMP NOT NULL,

    FOREIGN KEY (user_id)
        REFERENCES users(id),

    FOREIGN KEY (project_id)
        REFERENCES projects(id)
);
```

* * *

# 36.26 Audit Log Table

```sql
CREATE TABLE audit_logs (
    id UUID PRIMARY KEY,
    user_id UUID,
    action TEXT NOT NULL,
    resource_type TEXT,
    resource_id UUID,
    result TEXT,
    metadata JSONB,
    created_at TIMESTAMP NOT NULL,

    FOREIGN KEY (user_id)
        REFERENCES users(id)
);
```

* * *

# 36.27 Index Creation

Initial indexes can be created for common access patterns.

```sql
CREATE INDEX idx_projects_user_id
ON projects(user_id);

CREATE INDEX idx_conversations_user_id
ON conversations(user_id);

CREATE INDEX idx_messages_conversation_id_created_at
ON messages(conversation_id, created_at);

CREATE INDEX idx_files_project_id
ON files(project_id);

CREATE INDEX idx_documents_file_id
ON documents(file_id);

CREATE INDEX idx_chunks_document_id
ON document_chunks(document_id);

CREATE INDEX idx_memories_user_id
ON memories(user_id);

CREATE INDEX idx_agent_tasks_user_id_status
ON agent_tasks(user_id, status);

CREATE INDEX idx_agent_steps_task_id
ON agent_steps(task_id);

CREATE INDEX idx_tool_calls_task_id
ON tool_calls(task_id);

CREATE INDEX idx_usage_user_id_created_at
ON usage_records(user_id, created_at);

CREATE INDEX idx_audit_logs_user_id_created_at
ON audit_logs(user_id, created_at);
```

These indexes should later be validated using actual query performance.

* * *

# 36.28 PostgreSQL JSONB

Some ACAI entities need flexible structured data.

PostgreSQL provides:

```text
JSONB
```

This is useful for:

```text
Tool inputs
Tool outputs
Agent metadata
Audit metadata
Configuration
```

Example:

```sql
input JSONB
```

However, frequently queried fields should not be hidden unnecessarily inside JSON.

* * *

# 36.29 Structured vs Flexible Data

Use normal columns for:

```text
user_id
project_id
status
created_at
model
provider
```

Use JSONB for:

```text
dynamic tool parameters
optional metadata
variable tool output
```

This produces a balanced schema.

* * *

# 36.30 Migration System

The database schema should be created using migrations.

Example:

```text
migrations/
│
├── 001_create_users
├── 002_create_projects
├── 003_create_conversations
├── 004_create_messages
├── 005_create_files
├── 006_create_documents
├── 007_create_chunks
├── 008_create_memories
├── 009_create_agent_tasks
├── 010_create_agent_steps
├── 011_create_tool_calls
├── 012_create_usage_records
└── 013_create_audit_logs
```

* * *

# 36.31 Migration Rule

A migration should be:

```text
Versioned
Repeatable in a new environment
Reviewable
Traceable
```

Never depend on undocumented manual production changes.

* * *

# 36.32 Migration Workflow

Development:

```text
Modify Schema
      ↓
Generate Migration
      ↓
Review Migration
      ↓
Apply Migration
      ↓
Test
```

Production:

```text
Approved Migration
      ↓
Backup / Safety Check
      ↓
Apply Migration
      ↓
Verify
      ↓
Monitor
```

* * *

# 36.33 Repository Structure

The database implementation can be organized as:

```text
src/
└── server/
    └── database/
        ├── client
        ├── schema
        ├── migrations
        ├── repositories
        └── seed
```

The exact paths can vary depending on the ACAI codebase.

* * *

# 36.34 Repository Example

Conceptually:

```text
ProjectRepository
│
├── create()
├── findById()
├── findByUserId()
├── update()
└── softDelete()
```

Then:

```text
ProjectService
        ↓
ProjectRepository
        ↓
Database
```

* * *

# 36.35 Why Use a Repository Layer?

Without a repository layer:

```text
Route
 ↓
Raw SQL
 ↓
Database
```

can spread database-specific logic across the entire application.

With a repository:

```text
Route
 ↓
Service
 ↓
Repository
 ↓
Database
```

database operations remain centralized.

* * *

# 36.36 Transaction Layer

Transactions should be exposed where multiple changes must remain consistent.

Example:

```text
createAgentTask()
```

may perform:

```text
Create Task
Create Initial Step
Create Audit Log
```

inside one transaction.

* * *

# 36.37 Database Error Handling

Database errors should not be returned directly to end users.

Instead:

```text
Database Error
      ↓
Repository
      ↓
Service Error
      ↓
API Error
```

The API should expose a safe, meaningful error response.

* * *

# 36.38 Connection Management

The backend should reuse database connections efficiently.

Avoid:

```text
Request
 ↓
Create new database connection
 ↓
Query
 ↓
Destroy connection
```

for every request.

Instead, use the connection management mechanism provided by the selected database client.

* * *

# 36.39 Development Database

For local development, use a dedicated PostgreSQL instance.

Conceptually:

```text
Windows
  ↓
PostgreSQL
  ↓
ACAI Development Database
```

Alternatively, a containerized PostgreSQL environment can be used.

The important rule is isolation from production.

* * *

# 36.40 Testing Database

Automated tests should not modify the production database.

Use:

```text
ACAI_TEST_DB
```

or an isolated database/container.

Example:

```text
Tests
 ↓
Test Database
 ↓
Cleanup
```

* * *

# 36.41 Staging Database

Before production:

```text
Development
    ↓
Testing
    ↓
Staging
    ↓
Production
```

The staging environment should be sufficiently similar to production to detect deployment issues.

* * *

# 36.42 Production Database

Production should include:

```text
Secure Credentials
Encrypted Connections
Backups
Monitoring
Access Control
Migration Management
Recovery Plan
```

Only authorized backend infrastructure should have database access.

* * *

# 36.43 Vector Database Integration

The relational database does not necessarily need to perform all vector-search operations.

Architecture:

```text
PostgreSQL
   │
   └── Document Metadata
          │
          ▼
Vector Storage
          │
          └── Embeddings
```

The exact vector technology can be selected later.

* * *

# 36.44 RAG Metadata Link

Every vector record should be traceable back to:

```text
vector
 ↓
chunk
 ↓
document
 ↓
file
 ↓
project
 ↓
user
```

This is essential for authorization.

A search result must not be returned merely because its vector is similar; it must also belong to an accessible project/user scope.

* * *

# 36.45 Database Security Boundary

The security architecture should look like:

```text
Browser
   │
   ▼
API
   │
   ▼
Authorization
   │
   ▼
Service
   │
   ▼
Repository
   │
   ▼
Database
```

The browser should never connect directly to the primary database.

* * *

# 36.46 Chapter 36 Checklist

```text
[✓] PostgreSQL selected
[✓] ORM/database abstraction planned
[✓] Environment configuration defined
[✓] User schema defined
[✓] Project schema defined
[✓] Conversation schema defined
[✓] Message schema defined
[✓] File schema defined
[✓] Document schema defined
[✓] Chunk schema defined
[✓] Memory schema defined
[✓] Agent task schema defined
[✓] Agent step schema defined
[✓] Tool call schema defined
[✓] Usage schema defined
[✓] Audit schema defined
[✓] Foreign keys defined
[✓] Initial indexes defined
[✓] Migration strategy defined
[✓] Repository layer defined
[✓] Transaction strategy defined
[✓] Environment separation defined
[✓] Security boundary defined
```

* * *

# 36.47 Final Architecture

The resulting database stack is:

```text
                 ACAI APPLICATION
                        │
                        ▼
                SERVICE LAYER
                        │
                        ▼
              REPOSITORY LAYER
                        │
                        ▼
                 ORM / CLIENT
                        │
                        ▼
                  POSTGRESQL
                        │
            ┌───────────┴───────────┐
            ▼                       ▼
      Structured Data          Metadata
```

RAG extends the architecture:

```text
Documents
    ↓
Chunks
    ↓
Embeddings
    ↓
Vector Storage
```

And files are handled separately:

```text
Uploaded File
     ↓
Object Storage
     +
File Metadata
     ↓
PostgreSQL
```

* * *

# 36.48 Next Stage

The database technology and implementation blueprint are now established.

The next chapter will move from schema design into the **actual ACAI backend database connection, ORM configuration, migrations, seed system, and first working database operations**.

**END OF CHAPTER 36**