Database Architecture & ID Relationships

AILANG uses three SQLite databases with cross-references. Understanding ID relationships is critical for debugging, dashboard development, and coordinator work.

Database Locations

Database	Location	Purpose
observatory.db	~/.ailang/state/observatory.db	OTEL spans, traces, task/agent hierarchy
coordinator.db	~/.ailang/state/coordinator.db	Task execution state, approvals, events
collaboration.db	~/.ailang/state/collaboration.db	Messages, threads, agents

ID Flow Diagram

COLLABORATION.DB                    COORDINATOR.DB                     OBSERVATORY.DB
===============                     ==============                     ==============
threads.id --------------------------> tasks.thread_id
     |
messages.id (UUID) ------------------> tasks.message_id
     |                                     |
     |                                     +-- tasks.id = "task-" + first_8(message_id)
     |                                     |        |
     |                                     |        +--------------------> tasks.id (same)
     |                                     |                                    |
     |                                     |   tasks.session_id <---------------+-- spans.attributes.session.id
     |                                     |                                    |
     |                                     |                               agent_assignments.id
     |                                     |                                    |
     |                                     |                               spans.id <-- span_id (16-char hex)
     |                                     |                                    +-- trace_id (32-char hex)
     |                                     |                                    +-- parent_span_id
     |                                     |                                    +-- task_id (FK)
     |                                     |                                    +-- agent_assignment_id (FK)

ID Formats

ID Type	Format	Example	Generated By
task_id	task-<8-char-uuid>	task-29404032	Coordinator (from message UUID)
message_id	Full UUID	29404032-74b3-40c6-acc3-23d6bbe14b68	Collaboration.db
thread_id	thread_<ts>_<rand>	thread_1768064360112_5f96886d	Collaboration.db
workspace_id	ws_<16-char-hash>	ws_a1b2c3d4e5f67890	SHA256(path)
agent_assignment_id	aa_<16-char-hash>	aa_f1e2d3c4b5a69870	SHA256(task+agent+time)
span_id	16-char hex	0f9632b58df815e4	OTEL SDK
trace_id	32-char hex	0ebf5e64bb654fcc1d19256b59f05ae3	OTEL SDK
session.id	UUID	4df60536-caed-4e2f-af2c-e386c361f4e7	Claude Code/Gemini CLI
chain_id	UUID	e9c7501d-3f6e-48cd-8900-27a2b91d5345	Observatory (execution_chains table)
stage_id	UUID	a1b2c3d4-5678-9abc-def0-123456789abc	Observatory (chain_stages table)

How Spans Link to Tasks

1. Parent-child (OTEL standard): spans.parent_span_id builds tree within a trace
2. Task linkage: spans.task_id directly references coordinator task
3. Session correlation: spans.attributes.session.id links Claude Code sessions
4. Assignment linkage: spans.agent_assignment_id links to agent that created span

spans.task_id Formats (CRITICAL!)

The spans.task_id column can have multiple formats depending on the source:

Format	Example	Source	Count (typical)
task-<8char>	task-29404032	Coordinator-executed tasks	~3,000 spans
eval-<timestamp>	eval-1768073642020901000	Eval harness runs	~100 spans
Full UUID	013aca1d-1475-4fdf-a04b-aec531ed6296	Claude Code direct sessions	~25,000 spans
Other	varies	Legacy/custom	~300 spans

Why this matters:

• Filtering by task_id LIKE 'task-%' only matches coordinator tasks
• Claude Code sessions use UUID as their task_id (from session ID)
• Eval runs use eval-<timestamp> format
• Dashboard filtering must handle ALL formats, not just coordinator format

Database reality check:

sqlite3 ~/.ailang/state/observatory.db "
SELECT
  CASE
    WHEN task_id LIKE 'task-%' THEN 'coordinator'
    WHEN task_id LIKE 'eval-%' THEN 'eval'
    ELSE 'other/uuid'
  END as format,
  COUNT(*)
FROM spans WHERE task_id IS NOT NULL AND task_id <> ''
GROUP BY format"

Common Pitfalls

• DON'T assume span.task_id is always set — orphan spans from CLI tools may not have it
• DON'T confuse trace_id with task_id — trace_id groups OTEL spans, task_id links to coordinator
• DON'T expect TRACEPARENT propagation through Claude Code — it's not supported (see CLAUDE.md critical warnings)
• DO use session.id attribute for cross-trace correlation
• DO use parent_span_id for hierarchy within a trace
• DO use timestamp correlation for linking spans from different traces

Querying Examples

# Find spans by task
sqlite3 ~/.ailang/state/observatory.db \
  "SELECT id, name, trace_id FROM spans WHERE task_id = 'task-29404032'"

# Find message that created a task
sqlite3 ~/.ailang/state/coordinator.db \
  "SELECT message_id, thread_id FROM tasks WHERE id = 'task-29404032'"

# Find all turns in a session
sqlite3 ~/.ailang/state/observatory.db \
  "SELECT id, json_extract(attributes, '\$.turn.number') FROM spans \
   WHERE json_extract(attributes, '\$.session.id') = 'UUID'"

Dashboard Architecture Lesson

Lesson learned from 12+ failed attempts (January 2026):

When implementing filtering in the dashboard, use the same data source as other views.

Pattern that WORKS (Tree, Timeline, Chat, Waterfall views):

Event selected -> spans fetched -> `spans` prop passed -> view renders only those spans

Pattern that FAILS (TaskHierarchyGraph before fix):

Event selected -> separate API call to coordinator.db -> ID format mismatch -> shows all/nothing

Root cause: The dashboard has two databases with different ID formats. Attempting to cross-reference via ID matching is fragile because only ~10% of events have matching coordinator tasks.

The fix: Make TaskHierarchyGraph accept and use the spans prop like other views. Transform spans into graph format client-side rather than making separate API calls.

Design doc: See design_docs/planned/v0_7_0/m-task-graph-spans-unification.md