npm package: @multiplayer-app/ai-agent-db
Description: Shared MongoDB schemas and connection for Multiplayer AI agent services
Latest version: 0.1.0-beta.70

# @multiplayer-app/ai-agent-db

A database-agnostic repository abstraction layer for Multiplayer AI agent services. This library provides TypeScript interfaces for data access operations, allowing you to implement repository patterns for any database backend (MongoDB, PostgreSQL, MySQL, etc.).

## Features

- **Database-Agnostic Interfaces**: Abstract repository interfaces that work with any database
- **Type-Safe Operations**: Full TypeScript support with generic types
- **Repository Pattern**: Clean separation of data access logic from business logic
- **Specialized Repositories**: Domain-specific interfaces for `AgentChat` and `AgentMessage` entities
- **Flexible Querying**: Support for filtering, sorting, and pagination

## Prerequisites

- Node.js >= 18
- TypeScript >= 5.0
- A database implementation library (e.g., `@multiplayer-app/ai-agent-mongo` for MongoDB)

## Architecture

This library provides **interfaces only** - no concrete implementations. It follows the Repository pattern to abstract database operations, making your code database-agnostic and easily testable.

### Core Interfaces

#### `BaseRepository<T, ID>`

Generic interface providing standard CRUD operations:

```typescript
interface BaseRepository<T, ID = string> {
  findById(id: ID): Promise<T | null>;
  find(
    filter?: Partial<T>,
    options?: { sort?: { field: string; order: SortOrder }; skip?: number; limit?: number }
  ): Promise<T[]>;
  findOne(filter: Partial<T>): Promise<T | null>;
  create(entity: Omit<T, 'id' | 'createdAt' | 'updatedAt'>, id?: string): Promise<T>;
  update(id: ID, updates: Partial<Omit<T, 'id' | 'createdAt'>>): Promise<T | null>;
  delete(id: ID): Promise<boolean>;
  exists(id: ID): Promise<boolean>;
  count(filter?: Partial<T>): Promise<number>;
}
```

#### `AgentChatRepository`

Extends `BaseRepository<AgentChat>` with chat-specific operations:

- `findByUserId(userId: string)`: Find all chats for a user
- `findByContextKey(contextKey: string)`: Find all chats for a context
- `findByUserIdAndContextKey(userId: string, contextKey: string)`: Find chats by user and context
- `findGuestChatsByContextKey(contextKey: string)`: Find guest chats (no userId)
- `updateTitle(id: string, title: string)`: Update chat title
- `deleteByUserId(userId: string)`: Delete all chats for a user
- `deleteByContextKey(contextKey: string)`: Delete all chats for a context
- `findWithMessages(...)`: Find chats with related messages (aggregation)

#### `AgentMessageRepository`

Extends `BaseRepository<AgentMessage>` with message-specific operations:

- `findByChatId(chatId: string)`: Find all messages for a chat
- `findByRole(role: MessageRole)`: Find messages by role
- `findByChatIdAndRole(chatId: string, role?: MessageRole)`: Find messages by chat and role
- `findWithToolCalls(chatId?: string)`: Find messages with tool calls
- `findWithAttachments(chatId?: string)`: Find messages with attachments
- `deleteByChatId(chatId: string)`: Delete all messages for a chat
- `deleteByRole(role: MessageRole)`: Delete messages by role
- `deleteByChatIdAndRole(chatId: string, role: MessageRole)`: Delete messages by chat and role

## Usage

### Using with MongoDB Implementation

The easiest way to use this library is with the MongoDB implementation:


```typescript
import { MongoAgentChatRepository, MongoAgentMessageRepository } from '@multiplayer-app/ai-agent-mongo';

// Initialize repositories
const chatRepository = new MongoAgentChatRepository();
const messageRepository = new MongoAgentMessageRepository();

// Use the repositories
const chat = await chatRepository.findById('chat-id');
const messages = await messageRepository.findByChatId('chat-id');
```

### Implementing Your Own Repository

To implement these interfaces for a different database (e.g., PostgreSQL, MySQL):

```typescript
import type { AgentChatRepository } from '@multiplayer-app/ai-agent-db';
import type { AgentChat } from '@multiplayer-app/ai-agent-types';

export class PostgresAgentChatRepository implements AgentChatRepository {
  async findById(id: string): Promise<AgentChat | null> {
    // Your PostgreSQL implementation
    const result = await db.query('SELECT * FROM chats WHERE id = $1', [id]);
    return result.rows[0] || null;
  }

  async find(
    filter?: Partial<AgentChat>,
    options?: { sort?: { field: string; order: SortOrder }; skip?: number; limit?: number }
  ): Promise<AgentChat[]> {
    // Your implementation with filtering, sorting, and pagination
  }

  // Implement all other required methods...
}
```

### Example: Querying Chats

```typescript
import type { AgentChatRepository } from '@multiplayer-app/ai-agent-db';

// Find all chats for a user
const userChats = await chatRepository.findByUserId('user-123');

// Find chats with pagination and sorting
const recentChats = await chatRepository.find(
  { contextKey: 'my-context' },
  {
    sort: { field: 'updatedAt', order: SortOrder.Desc },
    limit: 10
  }
);

// Find chats with messages (aggregation)
const chatsWithMessages = await chatRepository.findWithMessages(
  { userId: 'user-123' },
  { sort: { field: 'createdAt', order: SortOrder.Desc } }
);
```

### Example: Managing Messages

```typescript
import type { AgentMessageRepository } from '@multiplayer-app/ai-agent-db';

// Find all messages in a chat
const messages = await messageRepository.findByChatId('chat-id');

// Find only assistant messages
const assistantMessages = await messageRepository.findByChatIdAndRole(
  'chat-id',
  MessageRole.Assistant
);

// Find messages with tool calls
const toolCallMessages = await messageRepository.findWithToolCalls('chat-id');

// Create a new message
const newMessage = await messageRepository.create({
  chat: 'chat-id',
  role: MessageRole.User,
  content: 'Hello, AI!'
});
```

## Database Access Patterns

### Generic Repository Pattern

The `BaseRepository` interface provides a consistent API across all entity types:

```typescript
// Works with any entity type
const repository: BaseRepository<MyEntity> = getRepository();

// Standard CRUD operations
const entity = await repository.findById('id');
const entities = await repository.find({ status: 'active' });
const created = await repository.create({ name: 'New Entity' });
const updated = await repository.update('id', { name: 'Updated' });
const deleted = await repository.delete('id');
const exists = await repository.exists('id');
const count = await repository.count({ status: 'active' });
```

### MongoDB Example

See `@multiplayer-app/ai-agent-mongo` for a complete MongoDB implementation using Mongoose:

- Uses Mongoose schemas and models
- Handles ObjectId conversion
- Implements aggregation pipelines for complex queries
- Provides transaction support

## Type Safety

All interfaces are fully typed using TypeScript generics and types from `@multiplayer-app/ai-agent-types`:

```typescript
import type { AgentChat, AgentMessage, SortOrder } from '@multiplayer-app/ai-agent-types';
import type { AgentChatRepository, AgentMessageRepository } from '@multiplayer-app/ai-agent-db';

// TypeScript ensures type safety
const chat: AgentChat = await chatRepository.findById('id');
const messages: AgentMessage[] = await messageRepository.findByChatId('chat-id');
```

## Best Practices

1. **Dependency Injection**: Pass repository instances to your services rather than creating them directly
2. **Error Handling**: Implement proper error handling in your repository implementations
3. **Transactions**: For databases that support it, implement transaction support in your repositories
4. **Caching**: Consider adding caching layers above the repository layer for frequently accessed data
5. **Testing**: Use the interfaces to create mock repositories for unit testing

## Related Packages

- `@multiplayer-app/ai-agent-types`: Type definitions for AgentChat, AgentMessage, etc.
- `@multiplayer-app/ai-agent-mongo`: MongoDB implementation of these interfaces
- `@multiplayer-app/ai-agent-node`: Node.js library that uses these repositories

## License

MIT