Analytics query builder ignores widget dateGranularity, sortBy/sortOrder, and funnel stage order

[yinlianghui]

Description

Three separate widget options on the Executive Overview dashboard are accepted by the metadata layer but never reach the generated SQL. They share one root cause — the analytics query builder ignores presentation/ordering options declared on the widget — so grouping them here.

Each response from POST /api/v1/analytics/dataset/query echoes its sql, which is where the omissions are visible.

Steps to Reproduce

1. pnpm dev and open /_console/apps/app.objectstack.hotcrm/dashboard/executive_dashboard
2. Read the sql field of the dataset query responses

Expected vs Actual

1. options.dateGranularity: 'month' produces no date truncation

revenue_trend and new_accounts_by_month both declare options: { dateGranularity: 'month' }, but the query groups by the raw column:

-- revenue_trend
... GROUP BY "crm_opportunity"."close_date"
-- new_accounts_by_month
SELECT created_at AS "created_at", COUNT(*) AS "account_count" FROM "crm_account"
WHERE created_at >= $1 GROUP BY created_at

Expected a date_trunc('month', …)-equivalent grouping.

Visible consequences: revenue_trend's x-axis is labelled by day (7月6日 / 7月12日 / 7月18日) rather than by month. new_accounts_by_month returns five rows that all carry created_at = "2026-07-27" and account_count = 1 — one row per record rather than one bar per month, because grouping on the untruncated timestamp never collapses same-day rows. The chart consequently renders five identical bars and a fractional y-axis (0.8, 1).

2. options.sortBy / sortOrder produce no ORDER BY

The accounts_by_industry table declares sortBy: 'annual_revenue_sum', sortOrder: 'desc':

SELECT industry AS "industry", SUM(annual_revenue) AS "annual_revenue_sum", COUNT(*) AS "account_count"
FROM "crm_account" GROUP BY industry

No ORDER BY. Rows arrive in industry alphabetical order (Finance 3.5M, Healthcare 8M, Manufacturing 12M, Technology 30M), which happens to be ascending revenue — the exact opposite of the requested desc. options.limit: 10 should be checked too: with no deterministic ordering, a LIMIT would truncate an arbitrary subset.

3. Funnel stages are not ordered by the sales pipeline

pipeline_by_stage is a funnel widget and receives rows in alphabetical order:

Needs Analysis → Negotiation → Proposal → Qualification

The real pipeline order is Qualification → Needs Analysis → Proposal → Negotiation. A funnel chart implies a monotonically narrowing sequence, so rendering alphabetically produces a shape that reads as a pipeline but is not one. Either the picklist's declared option order should drive funnel ordering, or the widget needs a way to state it explicitly.

Environment

• Branch main @ 00c83e9f, dev server on :4001
• Affected file: src/dashboards/executive.dashboard.ts (also reproduces on the CRM and Sales dashboards, which use the same options)

Suggested Fix

These need framework work in the analytics query builder rather than metadata changes here:

• push dateGranularity down into the GROUP BY as a date truncation
• emit ORDER BY from sortBy / sortOrder, and make limit depend on it
• give funnel widgets a defined stage order (picklist order by default)

If any of these are intentionally view-layer-only rather than query-layer, the metadata in this repo should be corrected instead — right now the options read as if they work.