SQL schema and entity map

SQL schema and entity map

This page compresses the concrete storage learnings from Codex, OpenCode, and Pi into one quick-reference sheet.

OpenCode, concrete SQLite tables

session
- id (pk)
- project_id
- workspace_id
- parent_id
- slug, directory, path, title, version
- share_url, metadata
- cost, tokens_input, tokens_output, tokens_reasoning
- tokens_cache_read, tokens_cache_write
- revert, permission, agent, model
- time_created, time_updated, time_archived, time_compacting

session_message
- id (pk)
- session_id
- type
- seq (unique per session)
- time_created, time_updated
- data (json)

session_input
- id (pk, same namespace as prompt/message ids)
- session_id
- prompt (json)
- delivery
- admitted_seq
- promoted_seq
- time_created

session_context_epoch
- session_id (pk)
- baseline
- snapshot (json)
- baseline_seq

Mental model: durable events feed projections. The UI mostly reads session_message, not raw execution events.

Pi, concrete SQLite tables

sessions
- id (pk)
- created_at
- cwd
- parent_session_id
- metadata (json)
- active_leaf_id

session_entries
- session_id
- id
- entry_seq
- parent_id
- type
- timestamp
- payload (json)
- pk(session_id, id)
- unique(session_id, entry_seq)

session_sequences
- session_id (pk)
- next_seq

branch_entries
- session_id
- branch_id
- entry_id
- entry_seq
- pk(session_id, branch_id, entry_id)

session_materialized
- session_id (pk)
- payload (json)

entry_materialized
- session_id
- entry_seq
- type
- payload (json)
- pk(session_id, entry_seq, type)

Mental model: append to a tree, then materialize views for fast reads.

Pi, durable entry types

Type	Purpose
message	User or assistant conversational content.
thinking_level_change	Records reasoning configuration changes.
model_change	Records model/provider switches.
active_tools_change	Records tool-policy changes.
compaction	Stores summary text and compaction metadata.
branch_summary	Stores an explanation for switching to another branch or leaf.
label	Human-friendly label for an entry.
session_info	Session naming or metadata entry.
leaf	Represents the active branch head.

Codex, exposed API entities

• Thread, durable conversation container.
• Turn, one agent execution inside a thread.
• Thread fork, clone a thread, optionally through last_turn_id.
• Thread items, such as agent messages, file changes, collab-agent tool calls, and sub-agent activity.
• Thread spawn, a child-agent thread with parent_thread_id.

Codex is a strong source for API shape and relationships, but not for backend SQL because that schema is not exposed in this repo.

Sub-agent relationship model

Project	How sub-agents appear	Storage implication
Codex	Explicit child-thread and collaboration item models	Use a separate thread row per agent and link them with relationship rows
OpenCode	Agent switching and prompt agent selection exist, but not a strong child-thread SQL pattern in the inspected files	Good for event structure, weaker as a direct sub-agent lineage model
Pi	No special sub-agent thread entity in the inspected storage layer, but the tree model can hold custom delegation entries	If using Pi’s structure, add explicit child-thread relations yourself

Recommended schema for Gio’s app

threads
- id
- parent_thread_id nullable
- root_thread_id
- title
- agent_role
- created_at
- archived_at nullable
- active_leaf_event_id nullable
- latest_turn_id nullable
- metadata jsonb

thread_events
- id
- thread_id
- parent_event_id nullable
- seq
- kind
- actor_role
- turn_id nullable
- created_at
- payload jsonb

thread_branches
- id
- thread_id
- leaf_event_id
- label nullable
- created_at

thread_summaries
- id
- thread_id
- boundary_event_id
- summary_kind
- summary_text
- token_count nullable
- created_at

agent_relationships
- id
- parent_thread_id
- child_thread_id
- relation_kind
- source_event_id
- created_at

message_projection
- id
- thread_id
- event_id
- turn_id nullable
- role
- display_index
- content jsonb
- created_at

Recommendation: if you only choose one advanced feature early, choose branching over full event replay. Retrofitting branch semantics later is painful.