Best practices for database design when storing AI/LLM conversations with tool/function calls?

I'm building an application that stores conversations between users and AI assistants, including tool/function calls made during these conversations. I've designed the following schema in PostgreSQL and would appreciate feedback on whether this is optimal or if there are better approaches. Here's my current table structure: +------------------------+ +------------------------+ +------------------------+ | messages | | messages_usage | | tool_calls | +------------------------+ +------------------------+ +------------------------+ | id (PK) | | id (PK) | | id (PK) | | user_id | | message_id (FK) | | message_id (FK) | | role (ENUM) | | cached_tokens | | tool_call_id | | name | | prompt_tokens | | function_name | | content | | completion_tokens | | arguments (jsonb) | | created | | model | | status (ENUM) | | | | system_fingerprint | | response | | | | created | | created | | | | | | completed_at | +------------------------+ +------------------------+ +------------------------+ Is this schema well-designed for storing conversations with tool/function calls? Are there any glaring issues? I'm concerned about the relationship between messages and tool calls. Are there any scalability concerns I should address now before the database grows?

Mar 31, 2025 - 05:42

I'm building an application that stores conversations between users and AI assistants, including tool/function calls made during these conversations. I've designed the following schema in PostgreSQL and would appreciate feedback on whether this is optimal or if there are better approaches.

Here's my current table structure:

+------------------------+     +------------------------+     +------------------------+
| messages               |     | messages_usage         |     | tool_calls             |
+------------------------+     +------------------------+     +------------------------+
| id (PK)                |     | id (PK)                |     | id (PK)                |
| user_id                |     | message_id (FK)        |     | message_id (FK)        |
| role (ENUM)            |     | cached_tokens          |     | tool_call_id           |
| name                   |     | prompt_tokens          |     | function_name          |
| content                |     | completion_tokens      |     | arguments (jsonb)      |
| created                |     | model                  |     | status (ENUM)          |
|                        |     | system_fingerprint     |     | response               |
|                        |     | created                |     | created                |
|                        |     |                        |     | completed_at           |
+------------------------+     +------------------------+     +------------------------+

Is this schema well-designed for storing conversations with tool/function calls? Are there any glaring issues? I'm concerned about the relationship between messages and tool calls.

Are there any scalability concerns I should address now before the database grows?