# Conversation

Schema reference for the tables that back conversations, individual messages, and the memories extracted from them.

### Conversations Table

**Table Name:** `conversations`\
**Description:** Conversation sessions and their metadata

{% tabs %}
{% tab title="Schema" %}

| Column                       | Type      | Description               |
| ---------------------------- | --------- | ------------------------- |
| `_id`                        | string    | Internal identifier       |
| `completed_post_processings` | json      | Post-processing status    |
| `created_at`                 | timestamp | Conversation start time   |
| `interactions`               | json      | Interaction history       |
| `is_finished`                | boolean   | Completion status         |
| `org_id`                     | string    | Organization identifier   |
| `region_name`                | string    | Deployment region         |
| `service_id`                 | string    | Associated service ID     |
| `updated_at`                 | timestamp | Last update timestamp     |
| `user_id`                    | string    | Associated user ID        |
| `version_set_info`           | json      | Version set metadata      |
| `working_memories`           | json      | Conversation memory state |
| {% endtab %}                 |           |                           |

{% tab title="Query Example" %}

```sql
SELECT
  _id AS conversation_id,
  user_id,
  service_id,
  created_at,
  is_finished
FROM conversations
WHERE is_finished = false
  AND created_at >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY created_at DESC;
```

{% endtab %}
{% endtabs %}

### Messages Table

**Table Name:** `messages`\
**Description:** Individual messages within conversations

{% tabs %}
{% tab title="Schema" %}

| Column             | Type      | Description                                                                                     |
| ------------------ | --------- | ----------------------------------------------------------------------------------------------- |
| `_id`              | string    | Message identifier                                                                              |
| `content`          | string    | Message body                                                                                    |
| `conversation_id`  | string    | Parent conversation ID                                                                          |
| `created_at`       | timestamp | Message timestamp                                                                               |
| `format`           | string    | Message format (one of: `text`, `voice`)                                                        |
| `interaction_id`   | string    | Parent interaction ID                                                                           |
| `message_metadata` | json      | Additional message metadata                                                                     |
| `message_type`     | string    | Message type (one of: `user-message`, `agent-message`, `agent-inner-thought`, `external-event`) |
| `org_id`           | string    | Organization identifier                                                                         |
| `region_name`      | string    | Deployment region                                                                               |
| `sender`           | string    | Sender identifier (user/agent)                                                                  |
| `updated_at`       | timestamp | Last update timestamp                                                                           |
| `user_id`          | string    | Associated user ID                                                                              |
| {% endtab %}       |           |                                                                                                 |

{% tab title="Query Example" %}

```sql
SELECT
  conversation_id,
  sender,
  message_type,
  content,
  created_at
FROM messages
WHERE conversation_id = 'conv-123'
ORDER BY created_at ASC;
```

{% endtab %}
{% endtabs %}

### Memories Table

**Table Name:** `memories`\
**Description:** Stored conversation memories

{% tabs %}
{% tab title="Schema" %}

| Column            | Type      | Description                                                        |
| ----------------- | --------- | ------------------------------------------------------------------ |
| `_id`             | string    | Memory identifier                                                  |
| `content`         | json      | Memory content `{ reference, content, context, importance_score }` |
| `conversation_id` | string    | Associated conversation ID                                         |
| `created_at`      | timestamp | Creation timestamp                                                 |
| `org_id`          | string    | Organization identifier                                            |
| `region_name`     | string    | Deployment region                                                  |
| `updated_at`      | timestamp | Last update timestamp                                              |
| `user_id`         | string    | Associated user ID                                                 |
| {% endtab %}      |           |                                                                    |

{% tab title="Query Example" %}

```sql
SELECT
  user_id,
  conversation_id,
  content,
  created_at
FROM memories
WHERE user_id = 'user-123'
ORDER BY created_at DESC
LIMIT 20;
```

{% endtab %}
{% endtabs %}