Add task attempt activity stats for agent work analytics

[legreffier]

Context

Follow-up to #1295. AI Native DevCon notes surfaced the need for an agent-work dashboard that can answer whether task runs are getting cheaper, shorter, more reliable, and more knowledge-aware across many attempts.

Signed diary decision: 74c7948b-f2e0-4d06-8a95-6a3eebfd3472.

Prior related decision: 4bbf1174-f924-4462-8b0a-88ddfb9f27c0 established explicit task cohorts before semantic clustering.

Decision Summary

Keep product analytics in MoltNet rather than querying an OTel backend as the dashboard's source of truth. OTel remains valuable for traces/logs/runtime drilldown, but dashboard success-rate and cohort analytics need to join against MoltNet-owned concepts: tasks, attempts, messages, outputs, judgments, tags, teams, diary/pack provenance, and permissions.

Avoid duplicating canonical fields:

• tasks owns task identity, type, title/tags, team/diary/correlation, and task status.
• task_attempts owns lifecycle state, final status, output/error, timing, executor fingerprints, and reported usage tokens/model/provider/toolCalls.
• task_messages owns the raw observed event stream.
• New derived data should only summarize activity inferred from task_messages.

Proposal

Add a derived per-attempt activity summary table named task_attempt_activity_stats.

The table is a cache/read model, not a source of truth. It should be recomputable from task_messages and should not copy fields already owned by tasks or task_attempts.

Candidate v1 columns:

• task_id
• attempt_n
• computed_at
• source_last_seq
• message_count
• turn_count
• tool_call_count
• failed_tool_call_count
• knowledge_tool_call_count
• entry_search_count
• entry_get_count
• pack_get_count

Primary key: (task_id, attempt_n).

Compute this row when an attempt reaches a terminal state. Dashboard aggregate queries should join tasks, task_attempts, and task_attempt_activity_stats with bounded filters such as team and date range.

Metric Semantics

Do not expose one global success-rate definition. Support layered definitions:

• accepted-output rate: terminal tasks with acceptedAttemptN present
• first-attempt accepted rate
• judged success rate where a downstream judge exists
• retry recovery rate
• cost/turn/tool-call medians per cohort

Task type should remain a filter, not the primary grouping assumption. freeform may become the dominant task shape, so cohorts should primarily use tags/search/correlation/executor/model and inferred activity categories.

Acceptance Criteria

• Add task_attempt_activity_stats schema + migration.
• The table only stores derived activity counts from task_messages; no duplicated task metadata, attempt status, timing, token usage, model, or provider.
• Add an extraction path that computes stats for one terminal attempt.
• Extraction records source_last_seq and computed_at.
• Add tests showing stats can be recomputed from representative task_messages payloads.
• Add a bounded analytics API query or repository method that aggregates by at least date range + team + tags.
• Add or verify a GIN index for tasks.tags if tag cohort filtering needs it.
• Console dashboard v1 uses the aggregate API without querying raw task_messages directly.

Out of Scope For First Slice

• Querying Axiom/Datadog/Prometheus as the product dashboard backend.
• Currency pricing tables; task_attempts.usage token counts are enough for v1.
• A broad task_attempt_analytics view unless repeated query fragments justify it.
• Precomputing every dashboard aggregate by day/tag/model before real dashboard usage proves the need.
• Semantic clustering or embeddings for cohort discovery.

Related

• Add task cohort metadata and searchable task observability filters #1295: task cohort metadata and searchable observability filters
• agent-daemon: adopt @moltnet/observability auto-instrumentation for distributed tracing + log/trace correlation #1367: daemon OTel auto-instrumentation and log/trace correlation