Skip to content
4 min read · 771 words

ORM Models — models.py

Nine tables model the whole domain: an Agent at the center owning messages, runs, tasks, tool calls, documents, chunks, and workflow traces — all cascade-deleted with the agent.

Purpose

backend/app/models.py defines every database table as a SQLAlchemy declarative model on database.Base. There is no other persistence definition anywhere in the codebase.

Entity relationship overview

Rendering diagram…

Full column-level ERD and DDL notes: Database Schema.

The models

Agent — the aggregate root

ColumnTypeNotes
idInteger PK
nameString(100), requireddisplay name, injected into prompts
typeString(50), requiredfunctional area (e.g. "Email Ops")
descriptionText, nullableinternal notes
system_promptText, requiredthe agent's instructions — see Prompts
language / toneString(50), defaults English / Professionalshape every LLM call
enabled_toolsText, default ""comma-separated allow-list — see Tools
created_atDateTime(tz), server default now()

Relationships — all with cascade="all, delete": messages, runs, tasks, tool_calls, documents, workflow_runs. Deleting an agent deletes its entire history.

The SQL cascade removes documents and document_chunks rows, but the corresponding vectors in Chroma are not deleted — there is no code path that calls documents_collection.delete. Orphaned vectors remain searchable in the collection (still scoped by agent_id, so they only affect a reused agent ID). Tracked in Known Issues.

Message — chat history

agent_id FK · role ("user" / "assistant", stored as String(20)) · content Text · created_at. Two rows are written per chat exchange (Request Lifecycle).

AgentRun — one chat execution

agent_id FK · input_text · output_text · latency_ms · status (default "success", set to "failed" on LLM errors) · error_message · created_at. Also owns tool_calls (cascade) via the optional ToolCall.agent_run_id back-reference.

Task — actionable items extracted from email

agent_id FK · title String(200) · description · priority (default "medium") · status (default "open"; API allows open | in_progress | completed) · due_date String(100) · source_type (default "email") · created_at.

Deadlines arrive from LLM analysis as free text ("tomorrow", "Friday", ISO dates). normalize_due_date() in the agents router keeps simple words, validates ISO dates (dropping past ones), and passes other text through — so the column stores what the model said, normalized, rather than forcing a lossy date parse.

ToolCall — the observability atom

agent_id FK · agent_run_id FK nullable · tool_name · tool_input · tool_output · success (String "true"/"false") · error_message · latency_ms · model_name · estimated_tokens (Integer) · estimated_cost (String(30)) · created_at.

The last three columns are the ones added by startup migrations. Why success/cost are strings: Design Decisions.

Document & DocumentChunk — RAG source of truth

  • Document: agent_id FK · filename · file_type (default "txt") · status (set to "indexed" on upload) · chunks_count · created_at.
  • DocumentChunk: document_id FK · agent_id FK (denormalized for direct per-agent queries) · chunk_text · chunk_index · vector_id (the Chroma ID: agent_{a}_document_{d}_chunk_{i}) · created_at.

The vector_id is the bridge between SQLite and Chroma — see RAG Ingestion.

WorkflowRun & WorkflowStep — pipeline traces

  • WorkflowRun: agent_id FK · workflow_name · input_text · final_output (JSON as Text) · status (running → success/failed) · quality_score (String — the reviewer's 0–1 score) · error_message · created_at. Owns steps (cascade).
  • WorkflowStep: workflow_run_id FK · step_name · input_data / output_data (JSON as Text) · status · latency_ms · error_message · created_at.

Written exclusively by the email workflow endpoint today — see Workflows.

Conventions

  • Every table has an autoincrement id PK with index=True and a timezone-aware created_at defaulting to func.now() (server-side).
  • Foreign keys are nullable=False except ToolCall.agent_run_id (tool calls outside chats are legal and common).
  • JSON payloads (tool inputs/outputs, workflow step data, final outputs) are serialized with json.dumps into Text columns — SQLite has no JSON column type worth using here.

Edge cases

  • String booleans/floats (ToolCall.success, estimated_cost, WorkflowRun.quality_score) require careful comparison/parsing — the dashboard uses safe_int/safe_float helpers; do the same in new code.
  • Task.agent uses a plain relationship("Agent") without back_populates — navigable from task to agent, while the agent side uses its own tasks list.
  • No uniqueness constraints beyond PKs — duplicate agent names, duplicate filenames per agent, etc., are all legal.

Future improvements

  • Numeric columns for estimated_cost / quality_score; Boolean for success.
  • Delete Chroma vectors when documents/agents are deleted.
  • Indexes on the FK columns used by dashboard aggregation.
PreviousDatabase LayerNextSchemas