# Data Model: Conversaciones múltiples del asistente con contexto

**Feature**: `004-chat-conversations`

## Overview

Introduce entidad **ChatConversation** como contenedor de hilos por usuario. **ChatMessage** pasa de `session_id` (string) a `conversation_id` (FK). **ConversationHistoryService** define ventana LLM (24 mensajes) sin persistir entidad aparte.

## Entities

### ChatConversation (NUEVO — `chat_conversations`)

| Field | Type | Constraints | Description |
|-------|------|-------------|-------------|
| `id` | bigint | PK, auto | Identificador estable (FR-012) |
| `user_id` | bigint | FK → `users.id`, index | Propietario (FR-001, FR-009) |
| `title` | string(120) | NOT NULL | Título visible; auto o placeholder |
| `created_at` | timestamp | | Creación |
| `updated_at` | timestamp | index | Última actividad; orden listado (FR-004) |

**Indexes**: `(user_id, updated_at DESC)`

---

### ChatMessage (MODIFICADO — `chat_messages`)

| Field | Type | Constraints | Description |
|-------|------|-------------|-------------|
| `id` | bigint | PK | |
| `conversation_id` | bigint | FK, NOT NULL | Reemplaza `session_id` |
| `role` | string(32) | `user` \| `assistant` | |
| `content` | text | | Contenido completo |
| `meta` | json nullable | | Adjuntos, consulta BD (FR-013) |
| `created_at` | timestamp | | Orden cronológico |

**Removed post-migration**: `session_id`

**Meta assistant (agent_database)** — FR-013:

```json
{
  "capability": "agent_database",
  "query": {
    "query_name": "contar_clientes",
    "parameters": {},
    "row_count": 1,
    "truncated": false
  }
}
```

---

### ConversationContext (conceptual)

Ventana de hasta 24 mensajes recientes de una conversación para el LLM (FR-006). No se persiste.

---

## Relationships

```mermaid
erDiagram
    User ||--o{ ChatConversation : owns
    ChatConversation ||--o{ ChatMessage : has
```

---

## Migration (FR-007)

1. Crear `chat_conversations`
2. Añadir `conversation_id` nullable a `chat_messages`
3. Comando `chat:migrate-legacy-sessions`: por cada `session_id` `api-user-{id}` → conversación + backfill
4. `conversation_id` NOT NULL; DROP `session_id`; CASCADE delete

---

## Validation Rules

| Rule | Source |
|------|--------|
| Usuario solo accede conversaciones propias | FR-009 |
| Mensajes ligados a conversación | FR-002 |
| Listado por `updated_at` desc | FR-004 |
| Título auto en primer mensaje | FR-012 |