Data Model

Data Model

app_user 1 ────< agent_task 1 ────< agent_step

Source: V1__initial_schema.sql

app_user

Column	Type	Note
id	UUID PK
email	TEXT UNIQUE	Auth attaches here in Month 5
display_name	TEXT
created_at	TIMESTAMPTZ

agent_task

A whole agent task.

Column	Type	Note
id	UUID PK
user_id	UUID FK → app_user	ON DELETE CASCADE
prompt	TEXT	The user's request
status	TEXT CHECK	pending → running → succeeded / failed / cancelled
final_answer	TEXT
max_steps	INT	Infinite-loop guard (Month 2)
created_at / finished_at	TIMESTAMPTZ	For duration calculation

State machine

pending ──▶ running ──┬──▶ succeeded
                      ├──▶ failed
                      └──▶ cancelled

A task never goes back from succeeded. This rule becomes critical when the queue is added in Month 4.

agent_step

The heart of the project. Each individual step of the task.

Column	Type	Note
id	UUID PK
task_id	UUID FK → agent_task
step_index	INT	UNIQUE (task_id, step_index)
step_type	TEXT CHECK	thought, tool_call, tool_result, final_answer, error
content	TEXT	The text the model produced
tool_name	TEXT	Which tool was selected
tool_input	JSONB	What was sent to the tool
tool_output	TEXT	What the tool returned
input_tokens / output_tokens	INT	Basis of Month 4 cost tracking
latency_ms	INT	Basis of Month 4 latency analysis
created_at	TIMESTAMPTZ

Why a separate table?

An agent doesn't finish work in a single step: it thinks → selects a tool → calls it → observes the result → thinks again. If you don't keep each step as a separate row, six months later there is no answer to "why did this agent make this decision".

The entire observability claim of the project rests on this schema decision. Also:

• Month 3 (eval) computes tool selection accuracy and step efficiency from this table
• Month 4 derives the token and latency distribution from here
• Month 5 feeds the trace/span visualization from here

So a single table set up correctly in Month 1 saves the work of three separate months.

Why is tool_input JSONB?

Tool parameters vary by tool; a separate column per tool would rot the schema. JSONB is both flexible and indexable (GIN). If Month 3 needs queries like "tools called with this parameter", a GIN index is added.

Indexes

⚠️ V1 deliberately has no index. In Month 1 / Week 4, a measurement is taken first without an index (EXPLAIN ANALYZE), then it's added via V2__add_indexes.sql and the difference is documented. If you add the index up front, there is nothing left to learn.

The expected first index:

CREATE INDEX idx_agent_step_task_id_step_index ON agent_step (task_id, step_index);

Why composite and why this order? The query is WHERE task_id = ? ORDER BY step_index. When the equality-filter column is first and the sort column second, the planner can satisfy both the filter and the sort from a single index. If you reverse the order, the sort isn't free — see it for yourself by measuring in Week 4.