114 lines
5.9 KiB
Go
114 lines
5.9 KiB
Go
package pipeline
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"strings"
|
|
|
|
"git.a-wan.net/alan/pipelines/internal/protocol"
|
|
)
|
|
|
|
const countsSQL = `COUNT(r.id),COALESCE(SUM(r.status='succeeded'),0),COALESCE(SUM(r.status='failed'),0),COALESCE(SUM(r.status='canceled'),0),COALESCE(SUM(r.status='interrupted'),0),COALESCE(SUM(r.status IN ('queued','running')),0),COALESCE(AVG(CASE WHEN r.finished_at IS NOT NULL THEN MAX(0,(julianday(r.finished_at)-julianday(r.created_at))*86400) END),0)`
|
|
|
|
func (s *Store) UsageStats(ctx context.Context) (*protocol.UsageStats, error) {
|
|
tx, err := s.db.BeginTx(ctx, nil)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer tx.Rollback()
|
|
stats := &protocol.UsageStats{Pipelines: []protocol.PipelineUsage{}, Days: []protocol.DailyUsage{}}
|
|
c := &stats.RunCounts
|
|
if err := tx.QueryRowContext(ctx, "SELECT "+countsSQL+" FROM pipeline_runs r").Scan(&c.Total, &c.Succeeded, &c.Failed, &c.Canceled, &c.Interrupted, &c.Active, &c.AverageSeconds); err != nil {
|
|
return nil, err
|
|
}
|
|
rows, err := tx.QueryContext(ctx, "SELECT p.id,p.name,"+countsSQL+" FROM pipelines p LEFT JOIN pipeline_runs r ON r.pipeline_id=p.id GROUP BY p.id ORDER BY COUNT(r.id) DESC,p.name")
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
for rows.Next() {
|
|
var p protocol.PipelineUsage
|
|
c := &p.RunCounts
|
|
if err := rows.Scan(&p.PipelineID, &p.Name, &c.Total, &c.Succeeded, &c.Failed, &c.Canceled, &c.Interrupted, &c.Active, &c.AverageSeconds); err != nil {
|
|
rows.Close()
|
|
return nil, err
|
|
}
|
|
stats.Pipelines = append(stats.Pipelines, p)
|
|
}
|
|
if err := rows.Err(); err != nil {
|
|
rows.Close()
|
|
return nil, err
|
|
}
|
|
rows.Close()
|
|
rows, err = tx.QueryContext(ctx, `WITH RECURSIVE days(day) AS (SELECT date('now','-13 days') UNION ALL SELECT date(day,'+1 day') FROM days WHERE day<date('now')) SELECT day,COUNT(r.id) FROM days LEFT JOIN pipeline_runs r ON r.created_at>=day AND r.created_at<date(day,'+1 day') GROUP BY day ORDER BY day`)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
for rows.Next() {
|
|
var d protocol.DailyUsage
|
|
if err := rows.Scan(&d.Date, &d.Runs); err != nil {
|
|
rows.Close()
|
|
return nil, err
|
|
}
|
|
stats.Days = append(stats.Days, d)
|
|
}
|
|
if err := rows.Err(); err != nil {
|
|
rows.Close()
|
|
return nil, err
|
|
}
|
|
rows.Close()
|
|
usage, err := usageTotals(ctx, tx)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
stats.Usage = usage
|
|
return stats, tx.Commit()
|
|
}
|
|
|
|
// usageSumColumns sums step_usage rows into the RunUsage/UsageTotals shape:
|
|
// stages, reported total, token-reporting stages, split in/out, cost,
|
|
// cost-reporting stages, token stages without a split, then reasoning, cache
|
|
// read and cache write sums and the stages that recorded them, in that order. It is
|
|
// the one SQL definition of the sum, shared by the history-wide totals and the
|
|
// per-run list aggregate, and mirrors protocol.TotalUsage over the same rows.
|
|
const usageSumColumns = `COALESCE(COUNT(*),0),
|
|
COALESCE(SUM(CASE WHEN json_extract(usage_json,'$.tokens_known') THEN json_extract(usage_json,'$.tokens_total') ELSE 0 END),0),
|
|
COALESCE(SUM(CASE WHEN json_extract(usage_json,'$.tokens_known') THEN 1 ELSE 0 END),0),
|
|
COALESCE(SUM(CASE WHEN json_extract(usage_json,'$.split_known') THEN json_extract(usage_json,'$.tokens_in') ELSE 0 END),0),
|
|
COALESCE(SUM(CASE WHEN json_extract(usage_json,'$.split_known') THEN json_extract(usage_json,'$.tokens_out') ELSE 0 END),0),
|
|
COALESCE(SUM(CASE WHEN json_extract(usage_json,'$.cost_known') THEN json_extract(usage_json,'$.cost_usd') ELSE 0 END),0),
|
|
COALESCE(SUM(CASE WHEN json_extract(usage_json,'$.cost_known') THEN 1 ELSE 0 END),0),
|
|
COALESCE(SUM(CASE WHEN json_extract(usage_json,'$.tokens_known') AND NOT COALESCE(json_extract(usage_json,'$.split_known'),0) THEN 1 ELSE 0 END),0),
|
|
COALESCE(SUM(CASE WHEN json_extract(usage_json,'$.tokens_known') AND json_extract(usage_json,'$.cache_known') THEN COALESCE(json_extract(usage_json,'$.tokens_reasoning'),0) ELSE 0 END),0),
|
|
COALESCE(SUM(CASE WHEN json_extract(usage_json,'$.tokens_known') AND json_extract(usage_json,'$.cache_known') THEN COALESCE(json_extract(usage_json,'$.cache_read'),0) ELSE 0 END),0),
|
|
COALESCE(SUM(CASE WHEN json_extract(usage_json,'$.tokens_known') AND json_extract(usage_json,'$.cache_known') THEN COALESCE(json_extract(usage_json,'$.cache_write'),0) ELSE 0 END),0),
|
|
COALESCE(SUM(CASE WHEN json_extract(usage_json,'$.tokens_known') AND json_extract(usage_json,'$.cache_known') THEN 1 ELSE 0 END),0)`
|
|
|
|
// usageFrom is the step_usage rows that belong to a recorded step. A usage row
|
|
// whose step (or run) no longer exists, such as one left behind by an old
|
|
// history wipe, is never counted, and is not deleted here either. It is the
|
|
// same row set the run detail reads through its step loop.
|
|
const usageFrom = `FROM step_usage u JOIN run_steps s ON s.run_id=u.run_id AND s.sequence=u.sequence`
|
|
|
|
// usageTotals is the one extra aggregate query the history-wide usage totals add,
|
|
// over the same step_usage rows the per-run figures read. It sums reported totals
|
|
// with json_extract, counts token/cost-reporting stages and leaves a stage that
|
|
// reported no cost uncounted rather than adding a fabricated zero; an old or
|
|
// read-only database with no step_usage table yields no totals, not an error.
|
|
func usageTotals(ctx context.Context, tx *sql.Tx) (*protocol.UsageTotals, error) {
|
|
var totals protocol.UsageTotals
|
|
err := tx.QueryRowContext(ctx, `SELECT `+usageSumColumns+` `+usageFrom).Scan(&totals.Stages, &totals.TokensTotal, &totals.TokensKnownStages, &totals.TokensIn, &totals.TokensOut, &totals.CostUSD, &totals.CostKnownStages, &totals.SplitUnknownStages, &totals.TokensReasoning, &totals.CacheRead, &totals.CacheWrite, &totals.CacheKnownStages)
|
|
if err != nil {
|
|
if strings.Contains(err.Error(), "no such table") {
|
|
return nil, nil
|
|
}
|
|
return nil, err
|
|
}
|
|
if totals.Stages == 0 {
|
|
return nil, nil
|
|
}
|
|
totals.CostUnknownStages = totals.Stages - totals.CostKnownStages
|
|
totals.SplitKnown = totals.TokensKnownStages > 0 && totals.SplitUnknownStages == 0
|
|
totals.CacheKnown = totals.TokensKnownStages > 0 && totals.CacheKnownStages == totals.TokensKnownStages
|
|
return &totals, nil
|
|
}
|