Suggestion: Database schema for conversations could be better

[habema]

Right now, we're storing all the parts of a conversation (user messages, tool calls, tool outputs, AI responses) as a simple list of events in one table. Each event is basically a JSON blob.

Here's what it looks like:

id	session_id	message_data	created_at
1	user_123	{"content": "What's the time?", "role": "user"}	2025-08-06 14:06:39
2	user_123	{"arguments": "{}", "call_id": "call_ErZ8...", "name": "get_server_time", "type": "function_call", "id": "fc_6893...", "status": "completed"}	2025-08-06 14:06:39
3	user_123	{"call_id": "call_ErZ8...", "output": "2025-08-06T17:06:37.796774", "type": "function_call_output"}	2025-08-06 14:06:39
4	user_123	{"id": "msg_6893...", "content": [{"annotations": [], "text": "Hello! The current server time is 5:06 PM on August 6, 2025. How can I assist you today?", "type": "output_text", "logprobs": []}], "role": "assistant", "status": "completed", "type": "message"}	2025-08-06 14:06:39

This works, but it's not great for a few reasons:

• Querying for specific things (like all messages and their respective responses) is a pain because you have to parse JSON in the query.
• I am specifically running into this problem as I'm trying to implement an "edit message" functionality, which needs to change the course of a conversation at a specific message in the session history.

We should probably switch to a more structured schema with separate tables, especially considering the ongoing developments on extending the session functionality.

Perhaps something like:

A runs table to hold the main conversation thread:

• id
• session_id
• user_message
• assistant_message
• created_at

And a tool_calls table to log all the tool activity for a run:

• id
• run_id (foreign key to runs)
• tool_name
• arguments
• output
• created_at

This would make our lives a lot easier when it comes to analyzing or debugging conversations.

[seratch]

Thanks, yeah I personally agree with this. @rm-openai any thoughts on this?

[rm-openai]

No strong opinions. It makes sense of course, but also not every app has a concept of a single user vs assistant, or needs tool calls to be separate, etc.

[habema]

@rm-openai Single or multi user, the same criticism applies. If it were multi user, its a matter of tying each session id to a user, and that can be done easily or even externally. Perhaps for the current built-in SQLite session its reasonable not to consider multi user systems within scope, but the schema will be standardized across session extension implementations.

The core problem is that the current schema doesn't make it easy to query which user message is connected to which assistant reply, or which tool calls (and arguments and results) were executed in that interaction. The problem grows as more tools are called in an interaction.

[rm-openai]

Totally, i think your feedback on the current interface is fair. Just saying that I don't know if there's one blessed way to store things versus maybe a few different common ways + a long tail of custom storage schemas.

[habema]

That's understandable.
I think for production usecases, it's expected that people should create their custom implementations that use the Session protocol (this is what I am currently doing) to adapt to what is best for their specific usecase.
That being said, I think we can come up with a "standard" schema that could serve as a good common ground, and provides some enhancement for analyzing or debugging through SQL.