mirror of
https://github.com/multica-ai/multica.git
synced 2026-07-05 13:29:44 +02:00
614dfae884
* docs(timezone): add scheduling/viewing timezone architecture RFC * feat(db): replace daily rollups with task_usage_hourly, add user.timezone Migrations 100-104: add "user".timezone (Viewing tz), build the UTC hourly task_usage_hourly rollup with its pipeline, drop the legacy task_usage_daily / task_usage_dashboard_daily pipelines, and drop the agent_runtime.timezone column. Report queries now slice day boundaries at read time by the caller-supplied @tz instead of materialising in a fixed tz. Regenerate sqlc. * feat(server): add task_usage_hourly backfill command Replace the two legacy backfill commands (daily / dashboard_daily) with a single backfill_task_usage_hourly that loads historical task_usage into the new UTC hourly rollup, sliced per workspace. * refactor(server): resolve viewing timezone in report handlers Report handlers resolve the Viewing tz per request (?tz query param, then user.timezone, then UTC) and pass it to the hourly-rollup queries. Drop the UseDailyRollup feature flags and the old raw-scan/daily-rollup dual paths, remove the /api/usage endpoints, and stop the daemon from reporting and the runtime handler from accepting host timezone. * refactor(core): switch report queries to viewing timezone API client and dashboard/runtime queries send ?tz with each report request, the user schema/types carry the new timezone field, and the runtime timezone field/mutation is removed. * feat(views): add viewing timezone preference and UI Add the useViewingTimezone hook and a Timezone setting in Preferences; report charts and the dashboard week boundary follow the viewer tz. Remove the runtime detail timezone editor and its locale strings. * fix(test): update fixtures and stabilize tests for timezone refactor The timezone architecture refactor changed several types without updating dependent test code: - RuntimeDevice no longer has a timezone field — drop it from the create-agent-dialog runtime fixture. - User now requires a timezone field — add it to the apps/web mockUser fixture. - The PreferencesTab timezone tests asserted on the async save handler (PATCH then store update) with a bare expect, racing the mutation's settle callback, and timed out querying the Select's ~600-option IANA list on a loaded CI runner. Wrap the assertions in waitFor and extend the timeout for those three tests. * docs(timezone): document self-host migration order and trigger invariant Add a SELF-HOST UPGRADE ORDER runbook to the backfill command's package comment: applying migrations 100-104 in a single migrate-up drops the legacy daily rollups before the hourly backfill runs, leaving dashboards empty until cron catches up. Add an INVARIANT comment on trg_atq_dirty_hourly noting that agent_id must be added to the trigger's OF list if it ever becomes mutable, otherwise dirty buckets for the old agent_id are silently missed. * style(runtimes): drop trailing blank line in runtime-detail
83 lines
3.7 KiB
SQL
83 lines
3.7 KiB
SQL
-- name: ListRuntimeUsage :many
|
|
-- Reads from the UTC-bucketed `task_usage_hourly` rollup table,
|
|
-- aggregated to per-(date, provider, model) under the
|
|
-- caller-supplied @tz. Powers the trend chart on the runtime detail
|
|
-- page and the per-row cost cell on the runtimes list.
|
|
--
|
|
-- @tz is required, even if the caller intends "UTC", so the bucket
|
|
-- cast is unambiguous — `bucket_hour` is UTC and the caller picks the
|
|
-- calendar boundary per request.
|
|
SELECT
|
|
DATE(bucket_hour AT TIME ZONE sqlc.arg('tz')::text) AS date,
|
|
provider,
|
|
model,
|
|
SUM(input_tokens)::bigint AS input_tokens,
|
|
SUM(output_tokens)::bigint AS output_tokens,
|
|
SUM(cache_read_tokens)::bigint AS cache_read_tokens,
|
|
SUM(cache_write_tokens)::bigint AS cache_write_tokens
|
|
FROM task_usage_hourly
|
|
WHERE runtime_id = $1
|
|
AND bucket_hour >= sqlc.arg('since')::timestamptz
|
|
GROUP BY DATE(bucket_hour AT TIME ZONE sqlc.arg('tz')::text), provider, model
|
|
ORDER BY DATE(bucket_hour AT TIME ZONE sqlc.arg('tz')::text) DESC, provider, model;
|
|
|
|
-- name: GetRuntimeTaskHourlyActivity :many
|
|
-- Hour-of-day distribution for queue starts. Bucketed in the viewer's
|
|
-- tz so "this runtime is busy in the afternoon" actually means
|
|
-- the operator's afternoon, not UTC's.
|
|
SELECT EXTRACT(HOUR FROM started_at AT TIME ZONE @tz::text)::int AS hour,
|
|
COUNT(*)::int AS count
|
|
FROM agent_task_queue
|
|
WHERE runtime_id = $1 AND started_at IS NOT NULL
|
|
GROUP BY hour
|
|
ORDER BY hour;
|
|
|
|
-- name: ListRuntimeUsageByAgent :many
|
|
-- Per-(agent, model) token aggregates for a runtime since a cutoff. Powers
|
|
-- the runtime-detail "Cost by agent" tab. task_usage only carries task_id,
|
|
-- so we join the queue to expose agent_id. The model dimension is kept on
|
|
-- purpose: cost is computed client-side from a per-model pricing table, so
|
|
-- collapsing models server-side would erase the information needed to do
|
|
-- that arithmetic. The client groups by agent_id and sums cost per agent.
|
|
--
|
|
-- This view doesn't bucket by date, so it doesn't need @tz; only the
|
|
-- @since cutoff is provided in runtime-local terms (computed in Go).
|
|
SELECT
|
|
atq.agent_id,
|
|
tu.model,
|
|
SUM(tu.input_tokens)::bigint AS input_tokens,
|
|
SUM(tu.output_tokens)::bigint AS output_tokens,
|
|
SUM(tu.cache_read_tokens)::bigint AS cache_read_tokens,
|
|
SUM(tu.cache_write_tokens)::bigint AS cache_write_tokens,
|
|
COUNT(DISTINCT tu.task_id)::int AS task_count
|
|
FROM task_usage tu
|
|
JOIN agent_task_queue atq ON atq.id = tu.task_id
|
|
WHERE atq.runtime_id = $1
|
|
AND tu.created_at >= @since::timestamptz
|
|
GROUP BY atq.agent_id, tu.model
|
|
ORDER BY atq.agent_id, tu.model;
|
|
|
|
-- name: GetRuntimeUsageByHour :many
|
|
-- Per-(hour, model) token aggregates (hour ∈ 0..23) for a runtime since a
|
|
-- cutoff. Powers the "By hour" tab — shows when in the day this runtime is
|
|
-- doing real work, with model preserved for client-side cost calculation
|
|
-- (same reason as ListRuntimeUsageByAgent above). Hours with zero activity
|
|
-- are omitted; the client fills the 24-bucket axis.
|
|
--
|
|
-- Hours are extracted in the viewer's tz via @tz so afternoon
|
|
-- work bucketed at UTC 06:00 lands in 14:00 for a UTC+8 viewer.
|
|
SELECT
|
|
EXTRACT(HOUR FROM tu.created_at AT TIME ZONE @tz::text)::int AS hour,
|
|
tu.model,
|
|
SUM(tu.input_tokens)::bigint AS input_tokens,
|
|
SUM(tu.output_tokens)::bigint AS output_tokens,
|
|
SUM(tu.cache_read_tokens)::bigint AS cache_read_tokens,
|
|
SUM(tu.cache_write_tokens)::bigint AS cache_write_tokens,
|
|
COUNT(DISTINCT tu.task_id)::int AS task_count
|
|
FROM task_usage tu
|
|
JOIN agent_task_queue atq ON atq.id = tu.task_id
|
|
WHERE atq.runtime_id = $1
|
|
AND tu.created_at >= @since::timestamptz
|
|
GROUP BY EXTRACT(HOUR FROM tu.created_at AT TIME ZONE @tz::text), tu.model
|
|
ORDER BY hour, tu.model;
|