Ten Million Rows, Both Ways

<script lang="ts">
  // 1. GROUPED HEADERS. A column with children becomes a group header
  // spanning its leaves, and resizing the group distributes the change.
  const columns: ColumnDef<Order>[] = [
    { id: 'group-money', header: 'Money', children: [
      { id: 'total', header: 'Total', type: 'currency', cell: totalCell },
      { id: 'margin', header: 'Margin', cell: marginCell }
    ]}
  ];

  // 2. CONDITIONAL FORMATTING and 3. FLAGS are one feature each. Neither
  // needs anything the built-ins do not also use.
  const heatmap = (): GridFeature<Order> => ({
    id: 'heatmap',
    cellDecoration: ({ node, column }) =>
      column.id === 'margin' && node.row.margin < 0
        ? { class: 'bg-error/10 text-error font-medium' }
        : undefined
  });

  const orderFlags = (): GridFeature<Order> => ({
    id: 'order-flags',
    createState: () => new OrderFlags(),
    menuItems: ({ grid, node }) => node ? [{
      id: 'toggle-flag', label: 'Flag this order', icon: 'lucide:flag',
      onSelect: () => getFlags(grid)?.toggle(node.id)
    }] : [],
    keybindings: [{ key: 'Ctrl+g', handler: (grid) => /* ... */ }]
  });
</script>

<!-- 4. CELLS THAT DRAW. A cell snippet is a component: it gets the row,
     the value and the column, and renders whatever you like. -->
{#snippet marginCell({ value }: DataGridCellContext<Order>)}
  <span class="flex items-center justify-end gap-2">
    <span class="h-1.5 w-12 rounded-full bg-on-surface/10">
      <span class="block h-full rounded-full {value < 0 ? 'bg-error' : 'bg-success'}"
            style="width: {Math.abs(value) * 160}%"></span>
    </span>
    <span class="tabular-nums">{pct.format(value)}</span>
  </span>
{/snippet}

<!-- 5. THE CHROME. One prop brings the quick filter, the filter chips, the
     column chooser, the density toggle, the export menu and the status bar. -->
<DataGrid {grid} toolbar class="h-144" />

// The width axis. A metric column is generated on demand rather than
// declared: its value is the same hash the real columns use, with a salt
// past the end of them, so nothing about it is stored on either side.
function metricDef(index: number): ColumnDef<WarehouseRow> {
  const source = extraColumnAt(index);          // { id: 'm41', header: 'M42', kind }
  return {
    id: source.id,
    header: source.header,
    sortable: true,
    filter: 'number',
    align: 'right',
    width: 110,
    // Computed where it is drawn. The server sends the 46 real columns and
    // nothing else: 200 rows across 20,000 metrics would be four million
    // values on the wire, and windowing the render does not shrink a
    // response. This is the one cost column virtualization cannot save.
    accessor: (row) => valueAt(Number(row.id) - 1, source.id)
  };
}

// Swapping the whole list is one assignment, and it is what costs: the
// cells stay windowed, but resolved widths, prefix sums and the CSS grid
// template every row declares all scale with the total.
grid.columns.defs = [...realColumns, ...Array.from({ length: 20_000 }, (_, i) => metricDef(i))];

// initialColumns bounds the first paint, before there is a viewport to
// window by. Without it a grid this wide renders every column for a frame.
virtualization({ rowHeight: 44, overscan: 8, columns: { initialColumns: 24 } });

// Sorting and filtering a metric still happen on the server, because a row
// index is all either side needs to agree on the value:
sortKeyAt(row, 'm41');            // the same uint32 key the radix sort takes
valueAt(row, 'm41');              // what a filter compares

import { createDataGrid, getVirtualization, virtualization } from '@sv5ui/datagrid';

const grid = createDataGrid<Order>({
  columns, data: [], getRowId,
  rowModel: 'server',
  features: [sorting(), filtering(), columnOps(), selection(), editing(),
             // columns: true, so 46 columns do not become 46 cells per row
             virtualization({ rowHeight: 40, overscan: 8, columns: true })]
});

const virtual = getVirtualization(grid)!;
const CHUNK = 200;
const THRESHOLD = 60;   // how close to the end the window may come

let inFlight = false;
let exhausted = false;

// The watcher. It reads the range being rendered and appends when the end
// comes near. The append is untracked: the row count is what the range is
// measured against, so a tracked write would feed the read that triggered it.
$effect(() => {
  const end = virtual.virtualizer.range.end;
  untrack(() => {
    if (inFlight || exhausted) return;
    if (end + THRESHOLD >= grid.data.length) void fetchChunk(grid.data.length);
  });
});

// Sorting and filtering decide which rows exist, so what is loaded is no
// longer in the right order. Throw it away and ask again from the top.
for (const event of ['sortChanged', 'filterChanged'] as const) {
  grid.events.on(event, () => {
    exhausted = false;
    grid.data = [];
    void fetchChunk(0);
  });
}

async function fetchChunk(offset: number) {
  inFlight = true;
  try {
    const response = await fetch('/api/orders', {
      method: 'POST',
      body: JSON.stringify({
        // quickFields: the columns a bare query applies to, so hiding one
        // narrows the search on the server too. nulls: which end blanks
        // belong at, so the order survives the wire.
        filter: toFilterRequest(
          getFiltering(grid)?.model ?? { quick: '', columns: {} },
          grid.columns.visible.map((column) => column.id)
        ),
        sort: toSortRequest(
          getSorting(grid)?.sort ?? [],
          grid.columns.defs,
          getSorting(grid)?.nulls
        ),
        offset,
        limit: CHUNK
      })
    });
    const { rows, total } = await response.json();

    grid.data = offset === 0 ? rows : [...grid.data, ...rows];
    grid.api.setRowCount?.(total);       // what the footer and the announcer count
    if (rows.length < CHUNK) exhausted = true;
  } finally {
    inFlight = false;
  }
}

// Ten million rows the server never holds: a row is a pure function of its
// index, so a query is a scan over a range of integers.

function valueAt(row: number, columnId: string): unknown {
  const salt = columnIndex(columnId) + 1;
  const r = hash(row, salt);              // well-mixed, deterministic
  // ...one branch per column kind: dictionary, int, money, date, bool, stars
}

// FILTER: one pass, nothing allocated per row. Ten million rows in tens of
// milliseconds because no object is ever built for a row that does not match.
const hits = new Int32Array(size);
let count = 0;
for (let row = 0; row < size; row++) {
  if (quick && !quickFields.some((f) => contains(valueAt(row, f), quick))) continue;
  if (columnFilters.every((f) => f.test(valueAt(row, f.columnId)))) hits[count++] = row;
}

// SORT: a radix sort over uint32 keys. A comparator sort of ten million
// indices costs about four seconds; four passes of eight bits costs about
// half of one. Every column exposes its order as an integer key for exactly
// this reason: text packs its first four characters, a date is a day number,
// a dictionary column is its index in a sorted dictionary.
indices = radixSortByKey(indices, (row) => sortKeyAt(row, field));

// CACHE: the query resolves to a list of row indices, held under a signature
// of itself. Scrolling then asks for window after window of that list and the
// server does no work beyond materializing the 200 rows it returns.
cache = { signature: JSON.stringify({ size, filter, sort }), indices, total };

const rows = [];
for (let i = offset; i < Math.min(offset + limit, total); i++) {
  rows.push(rowAt(indices ? indices[i] : i));
}
return json({ rows, total });

// An edit is a write. The event carries everything a PATCH needs, so the
// write path is a listener rather than a wrapper around the editor.

grid.events.on('cellEdited', ({ rowId, columnId, oldValue, newValue }) => {
  void save(rowId, columnId, newValue);
});

async function save(id: string, column: string, value: unknown) {
  const response = await fetch('/api/orders', {
    method: 'PATCH',
    body: JSON.stringify({ id, changes: { [column]: value } })
  });
  if (!response.ok) {
    // The grid already holds the new value, so a failure is yours to
    // surface. Refetching the row is the honest undo.
    error = 'The edit did not reach the server';
  }
}

// The column decides what the editor is, and validation runs before commit,
// so an invalid value never reaches this listener.
const columns: ColumnDef<Order>[] = [
  {
    id: 'status',
    header: 'Status',
    editable: true,
    type: 'badge',
    typeOptions: { colors: { paid: 'success', pending: 'warning', failed: 'error' } },
    editor: { type: 'select', options: [{ label: 'paid', value: 'paid' }] }
  }
];