Pivot Table

npx shadcn@latest add https://ui.zyeon.ai/r/pivot-table.json

Build a React + TypeScript + Tailwind "PivotTable" component with zod and
lucide-react — a cross-tabulation grid computed from flat records.

Contract
- A sibling zod contract file is the single source of truth:
  value  = string | number | null
  record = Record<string, value>            // one flat row, as a SELECT returns it
  field  = { key, label, role: "dimension" | "measure", prefix?, suffix? }
  config = { rowField, subRowField: string | null, columnField, valueField,
             aggregator: "sum" | "count" | "average" }
  data   = { status: "loading" | "empty" | "error" | "ready", fields, records }
  The component crosses the records itself, so the caller ships the rows it
  already has instead of pre-shaping a matrix. `status` is the view's own render
  state and is NOT a field of any record.
- Props: the contract data, plus config? / defaultConfig? / onConfigChange?
  (controlled or uncontrolled, same pattern as an input's value/defaultValue);
  defaultExpandedRows?: string[]; showControls?; showRowTotals?;
  showColumnTotals?; onRetry?; emptyState?; caption?; locale? (default "en-US");
  formatValue?(value, { aggregator, field }); emptyCellLabel? (default an em
  dash); blankLabel? (default "(blank)"); maxHeight?; skeletonRows?;
  skeletonColumns?; className.
- Also export the pure `aggregateRecords(records, valueField, aggregator)` so a
  consumer can recompute the very same numbers for a CSV export or a KPI beside
  the grid without re-deriving the rules below.

Behavior
- Four first-class branches on `status`. loading → a skeleton whose first column
  is the same width as the pinned row-header column, so nothing jumps when data
  lands; empty → an icon+text slot overridable via `emptyState`; error → message
  plus a "Try again" button only when `onRetry` is passed; ready → the grid. A
  "ready" that has no records, or a config that cannot be satisfied (fewer than
  two dimensions, or no measure), resolves to the empty branch — otherwise you
  render chrome around nothing.
- The maths is pure and re-runs from the records on every prop change; nothing is
  cached across configs. One pass builds four kinds of bucket — column axis, row
  groups, optional sub-groups, whole dataset — and every bucket stores its
  RECORDS, not a running number:
    cell     = aggregate(records in this row AND this column)
    subtotal = aggregate(every record in the row)
    total    = aggregate(every record in the column / in the dataset)
  Folding cell values into the subtotal is the classic pivot bug: the average of
  four averages is not the average of the underlying rows unless every cell holds
  the same count. Recompute, never fold.
- The three aggregates disagree about missing data on purpose. `count` counts
  RECORDS, so a record whose measure is null or a string still counts. `sum` and
  `average` skip anything that is not a finite number and return null when
  nothing numeric survived. A bucket with no records at all is always null. Null
  renders as the em dash: a hole in the crossing is not a zero, and an all-null
  cell must not read as a confident 0.
- Axis labels are the stringified dimension values; null, undefined and
  whitespace-only collapse into one `blankLabel` bucket, because three
  near-identical empty rows are worse than one honest "(blank)". Axis ORDER is
  first appearance in `records` — the caller controls it by ordering its data,
  and it never depends on the current locale's collation.
- The pickers (Rows / Then by / Columns / Aggregate / Value) rewrite the config.
  Picking a dimension that already occupies another axis SWAPS the two rather
  than duplicating it, so no dimension is ever placed twice and the displaced one
  is never silently dropped. Choosing "count" unmounts the Value picker instead
  of showing a control with no effect — it sits last in the row so its arrival
  and departure shift nothing else; likewise a dataset with only two dimensions
  has nothing left to nest, so the Then-by picker is not rendered at all rather
  than offered with a single "None". When `config` is passed without
  `onConfigChange` the pickers are not rendered at all: a control with nowhere to
  report a change would snap back on every pick.
- The config is re-validated against `fields` on EVERY render, not trusted once:
  a key that no longer names a field of the right role is dropped and the default
  layout (first two dimensions, first measure, sum) takes over. Field lists
  reload; a config outliving them must not render an empty grid.
- `subRowField` turns each row group into a disclosure: a chevron button with
  `aria-expanded`, collapsed by default, `defaultExpandedRows` opening named
  ones, plus one Expand all / Collapse all button. Collapsing UNMOUNTS the child
  rows rather than hiding them — a zero-height container keeps its cells in the
  accessibility tree, which is how a "collapsed" group ends up read out in full.
- Keyboard, on the group toggles: they share a roving tabindex, so the whole
  group column is ONE tab stop. ArrowDown / ArrowUp move between groups, Home /
  End jump to the first / last, ArrowRight expands, ArrowLeft collapses, and
  Enter / Space toggle (the native button behaviour). Move focus imperatively
  inside the key handler — every toggle stays mounted when a group opens (only
  the child rows appear), so the ref lookup and the focus() happen in the same
  event, with no "setState now, focus in an effect" round trip. Clamp the
  remembered group during render: changing the row dimension replaces every
  group, and the roving index must not point at one that no longer exists.
- "Try again" moves focus to the component root (tabIndex={-1}) BEFORE calling
  `onRetry`, because a successful retry swaps that branch away and takes the
  button with it; without the handover, focus would fall to <body>.
- Cleanup is trivial by construction and should stay that way: no timers, no
  rAF, no window listeners, no observers. The only imperative act is the focus()
  above, and the toggle ref map deletes its entry from the ref callback when a
  row unmounts, so it cannot pin detached nodes.

Rendering & styling
- A real <table>: <caption className="sr-only">, <th scope="col"> per column
  value, <th scope="row"> per row group and sub-row, <tfoot> for the totals row.
  Numbers are text you read, not colour you interpret — there is deliberately no
  heat tint; use a heatmap component when the colour IS the message.
- `border-separate border-spacing-0`, never `border-collapse`: a collapsed table
  paints the cell's border itself, so the pinned column loses its right edge the
  moment the grid scrolls. Draw the rules with per-cell border-b / border-r.
- Sticky cells need an OPAQUE background or the columns travelling underneath
  show through: the pinned row-header column is bg-card, the header row is
  bg-card, the totals row and totals column are bg-muted, and the corner cell is
  sticky on both axes with the highest z-index of the three.
- `w-full min-w-max` on the table: fill the host when there is room, otherwise
  take the natural width and let the wrapper scroll horizontally, instead of
  squeezing columns until the numbers wrap. `maxHeight` makes the component's own
  scroll container the scrollport, which is what lets the header actually pin.
- Semantic tokens only: bg-card (surfaces, pinned cells), bg-muted (totals,
  skeleton bars), text-muted-foreground (secondary text, sub-row labels),
  text-destructive (error icon), border, focus-visible:ring-2 ring-ring on every
  control. cn() merges the consumer className into the root.
- Numbers go through one Intl.NumberFormat built from an explicit `locale` prop
  (fixed default, never the browser default) so the server and the visitor's
  browser print the same string; `formatValue` replaces it wholesale, prefix and
  suffix included. Cells are tabular-nums and right-aligned so digits line up.
- Only decorative motion: the chevron rotate (transition-[rotate], because
  Tailwind v4 writes `rotate`, not `transform`) and the skeleton pulse, both
  carrying motion-reduce:*-none. Nothing in the state machine waits on them.
- A polite live region under the grid names the current crossing (aggregate,
  axes, group and column counts, record count) — recomputing is otherwise
  silent: every number changes and nothing says what they now mean.

Customization levers
- Aggregate vocabulary: the enum in the contract, the label map and the branch
  in `aggregateRecords` are the whole vocabulary — add median or distinct-count
  by editing those three places, keeping "no records → null" intact.
- Row-header column: it ships at w-56 with wrapping labels; widen it for long
  dimension values, narrow it to fit more columns in a panel.
- Density: p-2 cells read as comfortable; drop to px-2 py-1 + text-xs for a
  dense report, or raise skeletonRows/skeletonColumns to match your real page.
- Turn features off cleanly: `showControls={false}` when your app drives the
  layout from its own toolbar, `showRowTotals` / `showColumnTotals` for grids
  that only want the crossing, `subRowField: null` for a flat two-axis pivot.
- Formatting: `formatValue` for currency, percentages or compact notation;
  `prefix` / `suffix` on a measure field when the default formatter is enough;
  `emptyCellLabel` and `blankLabel` for the two kinds of nothing.
- Axis order: sort the incoming records — first appearance is the axis order, so
  the component never fights a server-side ORDER BY you already applied.