docs/specs/FS-20251222-dataset-field-metadata.md

Rama master · réplica de lectura del repositorio original.

# Dataset Field Metadata & Multi-Series Chart Builder

## Overview
Datasets already infer field types via the datatype determination module, but the chart builder only receives the configured `xAxis`, `yAxis`, and optional `dateField`. This spec extends datasets with a structured “field metadata” layer that auto-classifies fields as metrics or dimensions, surfaces them in the UI, and allows manual overrides. Using that metadata, the chart builder will let users pick multiple metrics/dimensions from a single dataset and draw multiple series without duplicating datasets. Rendering modules (`AxisChart`, `LineChart`, `BarChart`, etc.) must consume the richer payload, and `ChartDatasetConfig` must evolve to store per-series configuration.

## Goals
- Automatically populate field metadata for each dataset using existing datatype inference on preview samples.
- Classify fields as `metric`, `dimension`, or `date` based on type heuristics, ranking best matches at the top of selection lists.
- Provide a Fields tab in the dataset editor to review auto-detected fields, edit labels/descriptions, and override type/classification.
- Expose field metadata to the chart builder API so charts can display all available fields grouped by role.
- Allow chart creators to select multiple metric fields (and, when needed, dimensions) from the same dataset to create multiple series at once.
- Update ChartDatasetConfig so a single config can reference a dataset and declare one or more series bound to that dataset’s fields.
- Update AxisChart, LineChart, BarChart, PieChart, and MatrixChart to iterate over series rather than assuming one dataset == one series.
- Keep backwards compatibility: existing charts keep working, and migrations map legacy configs into the new shape.

## Non-Goals
- No dataset query changes or derived fields in this iteration (only existing columns are selectable).
- KPI/table chart types keep their current behavior for now; focus is on Axis-based charts.
- Variables/filters remain scoped per dataset/CDC as they are today; per-series variables are out of scope.

## Auto-Classification Rules
- Use `determineType` output as the base (`number`, `date`, `string`, `boolean`, etc.).
- Mapping:
  - `number` → default `metric`.
  - `date` → default `date`.
  - `string`/`boolean` → default `dimension`.
- Add heuristics:
  - Field names containing “id”, “uuid”, “key” default to `dimension`.
  - Names like “count”, “total”, “sum”, “revenue”, “amount” prioritized as metrics even if type detection is ambiguous.
  - Allow opt-out for high-cardinality strings (optional `excludeFromCharts` flag).
- Assign initial `aggregation` suggestion for metrics (default `sum`; `count` for booleans).

## Data Model
- Extend dataset model with `fieldsMetadata` JSON array:
  - `id` (stable identifier, e.g., field path)
  - `name` (raw field name)
  - `label` (editable)
  - `type` (`number`, `date`, `string`, `boolean`, etc.)
  - `role` (`metric`, `dimension`, `date`)
  - `aggregation` (`sum`, `avg`, `count`, `none`)
  - `previewSample` (optional array of sample values)
  - `autoGenerated` (boolean)
  - `enabled` (boolean; defaults true)
- Migration script sets `fieldsMetadata = null` for existing datasets; first preview run populates it.

## Dataset Editor UI
- Add “Fields” tab next to existing Settings/Requests tabs.
- Table/grid listing fields with columns: Checkbox (enabled), Field name, Type (read-only), Role (dropdown), Aggregation (dropdown for metrics), Label (text input), Sample values (tooltip).
- “Refresh fields” button reruns inference on latest preview data; highlight new/removed fields (diff state).
- Manual overrides persist; refresh respects edited values unless forced reset.
- Validation: warn if no date role exists when chart type requires it; warn if all metrics disabled.

## Multi-Series Chart Builder UI
- Update chart dataset fetch API to include `fieldsMetadata`.
- In the dataset picker within the chart editor:
  - Selecting a dataset opens a right-hand panel listing its fields grouped into “Recommended metrics”, “Other metrics”, and “Dimensions”.
  - Users can check multiple metric fields to add them as series. Selecting a dimension defines the shared X-axis (default to the dataset’s auto-detected date/dimension if only one exists).
  - Each selected metric shows inline controls for color, axis (left/right), line/bar style, and optional goal. Defaults mirror existing CDC defaults.
- Existing charts migrating to the new UI will display their legacy `xAxis`/`yAxis` as a locked series entry; users can convert it to the new model via a “Convert to multi-series” button.
- When adding additional series later, the same panel can be reopened to toggle more fields on/off without re-adding the dataset.

## ChartDatasetConfig Changes
- Keep ChartDatasetConfig as the glue entity between a chart and a dataset so filters/variables remain per dataset.
- Extend the model with a `series` array:
  - `id` (uuid)
  - `fieldId` (references `fieldsMetadata.id`)
  - `label` (defaults to field label)
  - `color`, `fillColor`, `pointRadius`, `lineStyle`, `goal`, `order`
  - `axis` (`left`, `right`)
  - `enabled`
- Legacy fields (`datasetColor`, `legend`, etc.) map to a single-series entry during migration. Keep them for backward compatibility but mark as deprecated once all charts have `series`.
- Allow multiple ChartDatasetConfigs to reference the same dataset if they need different dataset-level filters/variables; otherwise, a single CDC with multiple `series` handles the common case.
- Update validation to ensure at least one enabled series exists per CDC and to prevent duplicate `order` collisions across series.

## Rendering Pipeline Updates
- `ChartController.updateChartData()`:
  - When building `chartData`, iterate over each CDC’s `series` list; for every series, map the dataset’s payload to the required Chart.js dataset entry using the referenced `fieldId`.
  - Maintain previous behavior for CDCs lacking `series` (treat dataset result as a single series using existing options).
- `AxisChart`:
  - Accepts dataset + CDC `series` metadata; loops through series to build axis structures.
  - Optimize date handling by extracting the chosen dimension once per dataset and sharing across series.
- `LineChart`, `BarChart`, `PieChart`, `MatrixChart`:
  - Accept multiple series objects per dataset; ensure stacking/legends respect per-series config.
  - Update tests to cover multi-series datasets and mixed scenarios (multiple CDCs referencing same dataset vs. different datasets).
- Ensure tooltips, legends, and filters operate on series identifiers instead of dataset IDs.

## API Changes
- `GET /project/:project_id/chart/:id`/`POST .../query`: include `series` in each CDC payload.
- `POST /project/:project_id/chart/:id/chart-dataset-config`: accept `series` definitions; if omitted, auto-create one from legacy fields.
- Migration script to backfill `series` for existing CDCs using their `legend`, `datasetColor`, and `options.yAxis`.

## Telemetry
- Track `dataset_fields_auto_generated` (count of fields inferred) and `dataset_field_override` (role/aggregation edits).
- Track new events: `chart_series_added`, `chart_series_removed`, `chart_series_converted_from_legacy`.
- Include dataset_id, team_id, number of fields, duration of inference.

## Rollout & Flags
- Feature flag `datasetFieldMetadata` gates metadata generation.
- Separate flag `chartMultiSeries` wraps the new chart builder UI + CDC/Axis changes. Enable only after metadata flag is on for the team.
- CLI/admin toggle to backfill inference for selected teams.

## Risks & Mitigations
- **Inaccurate inference**: Provide clear manual override UI and “Reset to auto” option.
- **Performance**: Run inference on sample data only (already available); cache results to avoid repetitive scans.
- **Backward compatibility**: Charts ignoring fields metadata still work; metadata only augments API responses.
- **AxisChart complexity**: Implement feature flagging and extensive tests before defaulting to the new flow.
- **Multiple CDCs referencing same dataset**: Document that this remains supported for scenarios requiring distinct filters; UI should warn when redundant CDCs exist to encourage consolidation.