DEV Community

Cover image for ACAI — Chapter 35: Database Schema Implementation
Black Shadow Team ©
Black Shadow Team ©

Posted on

ACAI — Chapter 35: Database Schema Implementation

#ai

35.1 Chapter Objective

Chapter 34 defined the ACAI database architecture and data model.

This chapter converts that architecture into an implementation-ready schema design.

The objective is to define:

Tables
Primary Keys
Foreign Keys
Relationships
Indexes
Enums
Timestamps
Constraints
Soft Deletion
Migration Structure
Enter fullscreen mode Exit fullscreen mode

The schema should remain modular so that the database can grow with the application.

35.2 Schema Design Principles

The ACAI database should follow these principles:

1. Every major entity has a stable ID.
2. Relationships are explicit.
3. Ownership is traceable.
4. User data is isolated.
5. Important timestamps are stored.
6. Frequently queried fields are indexed.
7. Destructive operations are deliberate.
8. Schema changes are version controlled.
Enter fullscreen mode Exit fullscreen mode

35.3 Core Entity List

The first implementation should focus on the core entities:

users
projects
conversations
messages
files
documents
document_chunks
memories
agent_tasks
agent_steps
tool_calls
usage_records
audit_logs
Enter fullscreen mode Exit fullscreen mode

Additional entities can be introduced later.

35.4 ID Strategy

Every major record should have a unique identifier.

Conceptually:

id
Enter fullscreen mode Exit fullscreen mode

Example:

User ID
Project ID
Conversation ID
Message ID
File ID
Document ID
Agent Task ID
Enter fullscreen mode Exit fullscreen mode

The exact ID technology can be selected according to the database stack.

Possible approaches include:

UUID
ULID
Database-generated identifiers
Enter fullscreen mode Exit fullscreen mode

The important requirement is that IDs remain unique and stable.

35.5 Common Timestamp Fields

Most entities should contain:

createdAt
updatedAt
Enter fullscreen mode Exit fullscreen mode

Entities with lifecycle operations may also contain:

deletedAt
completedAt
startedAt
Enter fullscreen mode Exit fullscreen mode

Example:

createdAt
updatedAt
deletedAt
Enter fullscreen mode Exit fullscreen mode

35.6 User Schema

Conceptual schema:

users
────────────────────────
id
email
name
status
createdAt
updatedAt
Enter fullscreen mode Exit fullscreen mode

Possible status values:

ACTIVE
SUSPENDED
DELETED
Enter fullscreen mode Exit fullscreen mode

Authentication-specific data should be separated or handled through the chosen authentication architecture.

35.7 User Constraints

Potential constraints:

id → PRIMARY KEY
email → UNIQUE
email → NOT NULL
createdAt → NOT NULL
Enter fullscreen mode Exit fullscreen mode

The exact requirements depend on the authentication system.

35.8 Project Schema 

projects
────────────────────────
id
userId
name
description
status
createdAt
updatedAt
deletedAt
Enter fullscreen mode Exit fullscreen mode

Relationship:

projects.userId
        ↓
users.id
Enter fullscreen mode Exit fullscreen mode

35.9 Project Indexes

Common queries include:

Find all projects for a user
Find one project by ID
Find active projects
Enter fullscreen mode Exit fullscreen mode

Therefore useful indexes may include:

(userId)
(userId, status)
Enter fullscreen mode Exit fullscreen mode

The final indexes should be based on actual query patterns.

35.10 Conversation Schema 

conversations
────────────────────────
id
userId
projectId
title
status
createdAt
updatedAt
deletedAt
Enter fullscreen mode Exit fullscreen mode

Relationships:

userId
   ↓
users.id

projectId
   ↓
projects.id
Enter fullscreen mode Exit fullscreen mode

projectId may be nullable if standalone conversations are supported.

35.11 Message Schema 

messages
────────────────────────
id
conversationId
role
content
model
provider
inputTokens
outputTokens
createdAt
Enter fullscreen mode Exit fullscreen mode

Relationship:

messages.conversationId
          ↓
conversations.id
Enter fullscreen mode Exit fullscreen mode

35.12 Message Ordering

Messages need deterministic ordering.

The simplest approach is:

createdAt
Enter fullscreen mode Exit fullscreen mode

For stronger ordering guarantees, the system can additionally use:

sequenceNumber
Enter fullscreen mode Exit fullscreen mode

Example:

Conversation
│
├── sequence 1
├── sequence 2
├── sequence 3
└── sequence 4
Enter fullscreen mode Exit fullscreen mode

This can make message retrieval predictable.

35.13 File Schema 

files
────────────────────────
id
userId
projectId
name
mimeType
size
storageKey
status
createdAt
updatedAt
deletedAt
Enter fullscreen mode Exit fullscreen mode

The file record contains metadata.

The physical binary data is stored separately.

35.14 File Validation

Before creating a usable file record, validate:

Filename
MIME type
File size
Storage result
Ownership
Enter fullscreen mode Exit fullscreen mode

The backend should not rely only on the filename extension.

35.15 Document Schema 

documents
────────────────────────
id
fileId
projectId
status
pageCount
textLength
parser
processingVersion
errorCode
startedAt
completedAt
createdAt
updatedAt
Enter fullscreen mode Exit fullscreen mode

Relationship:

documents.fileId
      ↓
files.id
Enter fullscreen mode Exit fullscreen mode

35.16 Document Processing State

A document may transition through:

PENDING
   ↓
PROCESSING
   ↓
READY
Enter fullscreen mode Exit fullscreen mode

or:

PENDING
   ↓
PROCESSING
   ↓
FAILED
Enter fullscreen mode Exit fullscreen mode

This state should be stored in the database.

35.17 Document Chunk Schema 

document_chunks
────────────────────────
id
documentId
projectId
content
chunkIndex
tokenCount
pageNumber
section
createdAt
Enter fullscreen mode Exit fullscreen mode

Relationship:

document_chunks.documentId
            ↓
documents.id
Enter fullscreen mode Exit fullscreen mode

35.18 Chunk Ordering

Each document should preserve chunk order.

Example:

Document
│
├── chunkIndex = 0
├── chunkIndex = 1
├── chunkIndex = 2
└── chunkIndex = 3
Enter fullscreen mode Exit fullscreen mode

A useful constraint can ensure that the combination:

(documentId, chunkIndex)
Enter fullscreen mode Exit fullscreen mode

is unique.

35.19 Embedding Metadata

The database can maintain embedding metadata:

embeddings
────────────────────────
id
chunkId
model
dimensions
vectorReference
createdAt
Enter fullscreen mode Exit fullscreen mode

The actual vector may live in a dedicated vector storage system.

35.20 Memory Schema 

memories
────────────────────────
id
userId
projectId
type
content
importance
confidence
source
status
lastUsedAt
createdAt
updatedAt
Enter fullscreen mode Exit fullscreen mode

Potential status:

CANDIDATE
ACTIVE
ARCHIVED
Enter fullscreen mode Exit fullscreen mode

35.21 Memory Scope Constraints

A memory may be:

User-wide
Enter fullscreen mode Exit fullscreen mode

or:

Project-specific
Enter fullscreen mode Exit fullscreen mode

Therefore the schema should clearly distinguish:

userId
projectId
Enter fullscreen mode Exit fullscreen mode

A project memory should have a valid project relationship.

35.22 Agent Task Schema 

agent_tasks
────────────────────────
id
userId
projectId
conversationId
goal
status
currentStep
createdAt
updatedAt
startedAt
completedAt
Enter fullscreen mode Exit fullscreen mode

Relationship:

userId
projectId
conversationId
Enter fullscreen mode Exit fullscreen mode

connect the Agent task to the appropriate application context.

35.23 Agent Task State

Possible state machine:

PENDING
   ↓
PLANNING
   ↓
RUNNING
   ↓
COMPLETED
Enter fullscreen mode Exit fullscreen mode

Alternative paths:

RUNNING
   ↓
WAITING
   ↓
RUNNING
Enter fullscreen mode Exit fullscreen mode

or:

RUNNING
   ↓
APPROVAL_REQUIRED
   ↓
RUNNING
Enter fullscreen mode Exit fullscreen mode

Failure:

RUNNING
   ↓
FAILED
Enter fullscreen mode Exit fullscreen mode

Cancellation:

RUNNING
   ↓
CANCELLED
Enter fullscreen mode Exit fullscreen mode

35.24 Agent Step Schema 

agent_steps
────────────────────────
id
taskId
stepNumber
action
status
input
output
startedAt
completedAt
createdAt
Enter fullscreen mode Exit fullscreen mode

Relationship:

agent_steps.taskId
       ↓
agent_tasks.id
Enter fullscreen mode Exit fullscreen mode

35.25 Agent Step Ordering

Each Agent task should maintain deterministic step ordering.

Recommended logical constraint:

(taskId, stepNumber)
Enter fullscreen mode Exit fullscreen mode

should be unique.

Example:

Task #100

Step 1
Step 2
Step 3
Step 4
Enter fullscreen mode Exit fullscreen mode

35.26 Tool Call Schema 

tool_calls
────────────────────────
id
taskId
stepId
toolName
input
output
status
startedAt
completedAt
createdAt
Enter fullscreen mode Exit fullscreen mode

Relationships:

taskId
  ↓
agent_tasks.id

stepId
  ↓
agent_steps.id
Enter fullscreen mode Exit fullscreen mode

35.27 Tool Call State

Possible states:

PENDING
RUNNING
SUCCESS
FAILED
CANCELLED
Enter fullscreen mode Exit fullscreen mode

This enables the Agent system to understand what happened during execution.

35.28 Agent Approval Schema

If a tool or action requires user approval:

agent_approvals
────────────────────────
id
taskId
stepId
action
status
requestedAt
respondedAt
Enter fullscreen mode Exit fullscreen mode

Possible states:

PENDING
APPROVED
REJECTED
EXPIRED
Enter fullscreen mode Exit fullscreen mode

35.29 Usage Schema 

usage_records
────────────────────────
id
userId
projectId
requestType
model
provider
inputTokens
outputTokens
toolCalls
duration
createdAt
Enter fullscreen mode Exit fullscreen mode

This provides raw usage information.

35.30 Usage Query Examples

The system may need to answer:

How many tokens did this user consume today?

How many Agent tasks did this project run?

How many model requests occurred this month?

How many tool calls were executed?
Enter fullscreen mode Exit fullscreen mode

Indexes should be designed around these real queries.

35.31 Audit Log Schema 

audit_logs
────────────────────────
id
userId
action
resourceType
resourceId
result
metadata
createdAt
Enter fullscreen mode Exit fullscreen mode

Example actions:

LOGIN
PROJECT_CREATED
FILE_UPLOADED
FILE_DELETED
AGENT_STARTED
AGENT_CANCELLED
APPROVAL_GRANTED
Enter fullscreen mode Exit fullscreen mode

35.32 Relationship Map

The database relationships can be represented as:

users
  │
  ├──────── projects
  │              │
  │              ├──────── files
  │              │            │
  │              │            └── documents
  │              │                    │
  │              │                    └── chunks
  │              │
  │              ├──────── conversations
  │              │                 │
  │              │                 └── messages
  │              │
  │              ├──────── memories
  │              │
  │              └──────── agent_tasks
  │                              │
  │                              ├── agent_steps
  │                              │       │
  │                              │       └── tool_calls
  │                              │
  │                              └── approvals
  │
  ├──────── usage_records
  │
  ├──────── notifications
  │
  └──────── audit_logs
Enter fullscreen mode Exit fullscreen mode

35.33 Foreign Key Strategy

Important relationships should use foreign keys where supported.

Examples:

projects.userId → users.id

conversations.userId → users.id

conversations.projectId → projects.id

messages.conversationId → conversations.id

files.userId → users.id

files.projectId → projects.id

documents.fileId → files.id

documents.projectId → projects.id

document_chunks.documentId → documents.id

memories.userId → users.id

memories.projectId → projects.id

agent_tasks.userId → users.id

agent_tasks.projectId → projects.id

agent_steps.taskId → agent_tasks.id

tool_calls.taskId → agent_tasks.id

tool_calls.stepId → agent_steps.id
Enter fullscreen mode Exit fullscreen mode

35.34 Referential Integrity

Foreign keys prevent orphaned records.

For example:

message
   ↓
conversation
   ↓
user
Enter fullscreen mode Exit fullscreen mode

If a message references a nonexistent conversation, the database should reject the invalid relationship when foreign-key enforcement is enabled.

35.35 Delete Strategy

Deletion rules must be defined individually.

For example:

Delete User
   ↓
Projects?
Conversations?
Files?
Memories?
Usage?
Audit Logs?
Enter fullscreen mode Exit fullscreen mode

Some records may need:

CASCADE
Enter fullscreen mode Exit fullscreen mode

Others may require:

SET NULL
Enter fullscreen mode Exit fullscreen mode

or:

RESTRICT
Enter fullscreen mode Exit fullscreen mode

The correct behavior depends on retention and product requirements.

35.36 Soft Delete Strategy

For recoverable application resources:

deletedAt
Enter fullscreen mode Exit fullscreen mode

can be used.

Example:

projects.deletedAt
files.deletedAt
conversations.deletedAt
Enter fullscreen mode Exit fullscreen mode

Normal queries should exclude records where:

deletedAt IS NOT NULL
Enter fullscreen mode Exit fullscreen mode

unless an administrative or recovery operation specifically requests them.

35.37 Index Strategy

Initial indexes should focus on common access patterns.

Examples:

users(email)

projects(userId)

conversations(userId)
conversations(projectId)

messages(conversationId, createdAt)

files(userId)
files(projectId)

documents(fileId)

document_chunks(documentId, chunkIndex)

memories(userId)
memories(projectId)

agent_tasks(userId)
agent_tasks(projectId)
agent_tasks(status)

agent_steps(taskId, stepNumber)

tool_calls(taskId)

usage_records(userId, createdAt)

audit_logs(userId, createdAt)
Enter fullscreen mode Exit fullscreen mode

These are starting points, not mandatory final indexes.

35.38 Composite Indexes

Composite indexes can support common multi-condition queries.

Example:

messages(conversationId, createdAt)
Enter fullscreen mode Exit fullscreen mode

helps retrieve messages for a specific conversation in chronological order.

Similarly:

agent_tasks(userId, status)
Enter fullscreen mode Exit fullscreen mode

can help find active tasks belonging to a user.

35.39 Unique Constraints

Potential unique constraints include:

users.email
(documentId, chunkIndex)
(taskId, stepNumber)
Enter fullscreen mode Exit fullscreen mode

Other unique constraints should be introduced only when the business rules require them.

35.40 JSON / Metadata Fields

Some records may need flexible metadata.

Examples:

tool_calls.input
tool_calls.output
audit_logs.metadata
Enter fullscreen mode Exit fullscreen mode

Structured JSON fields can be useful for these cases.

However, important fields that are frequently queried should generally remain explicit database columns.

35.41 Migration Structure

Database schema changes should be version controlled.

Example:

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_agents
010_create_usage
011_create_audit_logs
Enter fullscreen mode Exit fullscreen mode

The exact migration mechanism depends on the selected ORM or database tooling.

35.42 Initial Migration Order

A safe dependency-aware order is:

1. users
2. projects
3. conversations
4. messages
5. files
6. documents
7. document_chunks
8. embeddings metadata
9. memories
10. agent_tasks
11. agent_steps
12. tool_calls
13. agent_approvals
14. usage_records
15. notifications
16. audit_logs
Enter fullscreen mode Exit fullscreen mode

Parent entities should normally exist before dependent entities.

35.43 Seed Data

Development seed data can include:

Example User
Example Project
Example Conversation
Example Tool
Example Plan
Enter fullscreen mode Exit fullscreen mode

Seed data should be clearly separated from production data.

35.44 Environment Configuration

The application should load the database connection from environment configuration.

Conceptually:

DATABASE_URL
Enter fullscreen mode Exit fullscreen mode

The actual value should never be committed to a public repository.

Use:

.env
Enter fullscreen mode Exit fullscreen mode

locally and:

.env.example
Enter fullscreen mode Exit fullscreen mode

for documentation.

35.45 Database Connection Layer

The backend should expose a single database abstraction to application modules.

Conceptually:

Application
     ↓
Database Client
     ↓
Database
Enter fullscreen mode Exit fullscreen mode

Avoid creating unnecessary database connections for every request.

Connection management should be handled by the selected database client or ORM.

35.46 Repository Implementation

The repository layer should hide database-specific details.

Example:

ProjectService
      ↓
ProjectRepository
      ↓
Database Client
Enter fullscreen mode Exit fullscreen mode

The service should ask for operations such as:

findProjectById()
createProject()
updateProject()
Enter fullscreen mode Exit fullscreen mode

rather than embedding raw database logic throughout the application.

35.47 Transaction Example

Consider creating an Agent task.

Potential operation:

Create Agent Task
+
Create Initial Step
+
Create Audit Record
Enter fullscreen mode Exit fullscreen mode

If these records must remain consistent, they can be executed inside a database transaction.

Conceptually:

BEGIN
   ↓
Create Task
   ↓
Create Step
   ↓
Create Audit Record
   ↓
COMMIT
Enter fullscreen mode Exit fullscreen mode

If an essential operation fails:

ROLLBACK
Enter fullscreen mode Exit fullscreen mode

35.48 Database Consistency

The database should protect basic invariants.

Examples:

A message must belong to a conversation.

A document chunk must belong to a document.

An Agent step must belong to an Agent task.

A tool call must belong to a valid execution context.
Enter fullscreen mode Exit fullscreen mode

Business rules can then be enforced at the service layer as well.

35.49 Multi-Tenant Preparation

If ACAI later introduces organizations, the schema can evolve by adding:

organizations
organization_members
Enter fullscreen mode Exit fullscreen mode

and relevant:

organizationId
Enter fullscreen mode Exit fullscreen mode

fields.

Future architecture:

Organization
   │
   ├── Members
   ├── Projects
   ├── Files
   ├── Conversations
   └── Agents
Enter fullscreen mode Exit fullscreen mode

This should be considered before finalizing authorization assumptions.

35.50 Database Security

Database credentials must be protected.

Never place credentials directly in:

Source code
Git repository
Frontend JavaScript
Public configuration
Enter fullscreen mode Exit fullscreen mode

Only trusted backend infrastructure should access the primary database.

35.51 Backup and Recovery

Production should have:

Automated Backups
Recovery Procedures
Monitoring
Backup Verification
Enter fullscreen mode Exit fullscreen mode

A backup is only useful if restoration has been tested.

A basic recovery model:

Production Database
        ↓
Backup
        ↓
Recovery Environment
        ↓
Restore Test
Enter fullscreen mode Exit fullscreen mode

35.52 Performance Considerations

Do not optimize prematurely.

Start with:

Correct schema
Correct relationships
Correct indexes
Correct queries
Enter fullscreen mode Exit fullscreen mode

Then measure:

Query latency
Database CPU
Memory
Connection count
Slow queries
Storage growth
Enter fullscreen mode Exit fullscreen mode

Optimization should be based on real measurements.

35.53 Database Monitoring

Production monitoring should watch:

Connection usage
Query latency
Error rate
Storage size
Slow queries
Lock contention
Backup status
Enter fullscreen mode Exit fullscreen mode

Alerts should be configured for critical failures.

35.54 Complete Schema Blueprint

The final conceptual schema is:

USERS
│
├── PROJECTS
│   ├── FILES
│   │   └── DOCUMENTS
│   │       └── DOCUMENT_CHUNKS
│   │
│   ├── CONVERSATIONS
│   │   └── MESSAGES
│   │
│   ├── MEMORIES
│   │
│   └── AGENT_TASKS
│       ├── AGENT_STEPS
│       │   └── TOOL_CALLS
│       │
│       └── AGENT_APPROVALS
│
├── USAGE_RECORDS
├── NOTIFICATIONS
└── AUDIT_LOGS
Enter fullscreen mode Exit fullscreen mode

35.55 Implementation Checklist

Before starting the actual database migration:

[✓] Define database technology
[✓] Define ID strategy
[✓] Define users
[✓] Define projects
[✓] Define conversations
[✓] Define messages
[✓] Define files
[✓] Define documents
[✓] Define document chunks
[✓] Define embeddings metadata
[✓] Define memories
[✓] Define Agent tasks
[✓] Define Agent steps
[✓] Define tool calls
[✓] Define approvals
[✓] Define usage
[✓] Define notifications
[✓] Define audit logs
[✓] Define foreign keys
[✓] Define indexes
[✓] Define unique constraints
[✓] Define deletion behavior
[✓] Define migrations
[✓] Define backup strategy
Enter fullscreen mode Exit fullscreen mode

35.56 Final Result

At the end of Chapter 35, ACAI has a clear implementation-ready database blueprint.

The data flow is:

USER
 ↓
PROJECT
 ↓
FILES → DOCUMENTS → CHUNKS → EMBEDDINGS

USER
 ↓
CONVERSATION → MESSAGES

USER
 ↓
MEMORY

USER
 ↓
AGENT TASK
 ↓
AGENT STEP
 ↓
TOOL CALL

USER
 ↓
USAGE
 ↓
AUDIT
Enter fullscreen mode Exit fullscreen mode

The next stage is to choose the concrete database stack and build the actual schema, migrations, connection layer, and repository implementation.

END OF CHAPTER 35

Top comments (0)