Validate a SQL Backend

The repository includes a Node-only SQLite reference and a reusable backend acceptance kit under packages/pivot/server-reference/. These development tools execute actual SQL against independent numerical fixtures. They are not part of the published browser package or a hosted production backend.

From the repository root, run:

pnpm --filter @revolist/pivot test:backend

Use a Node runtime with node:sqlite; the baseline was verified with Node 26.7.0. The command typechecks the reference and acceptance kit and runs the dedicated Node suite with one worker. It needs no external database service.

Backend contract

The reference implements PivotEngineAdapter and reuses the field registry, request normalization, and logical SQL planner. Its application-owned configuration fixes the authorized view, field-registry version, tenant, row/column fields, and fact ordering key. Authenticate before creating that configuration and reduce the registry to authorized fields. Request JSON must never choose database tables, tenant scope, or raw SQL expressions.

The supported baseline includes flat row/column axes; SQL sum, average, count, minimum and maximum; source filters; row aggregate filters; independent axis windows; typed member search/pages; duplicate measures; authorized drillthrough pages; and complete bounded flat export from one atomic export-time transaction. The export suite includes independent CSV/XLSX fixtures and more groups/columns than normal load windows. Unknown selectors, stale registry versions, forbidden views, oversized requests, and unsupported operations fail explicitly.

Source filters select facts before aggregation. Aggregate filters select row groups and independently test the global summary. Global totals use the complete source-filtered dataset, even when row windows or aggregate filters hide individual groups. They are not sums of visible rows. Column members use the source-filtered scope.

SQL null semantics are explicit: COUNT(column) excludes null but counts empty text, and averages use non-null observations. All-null sums, averages, minima and maxima return null. These semantics differ from legacy local aggregate coercion; a passing SQL baseline does not establish local/remote parity.

Validate your adapter

acceptance-kit.ts exports runBackendAcceptance and REQUIRED_BASELINE_PROFILE. Populate the independent fixture from fixtures.ts, provide application adapters authenticated as each fixture tenant, and run the same assertions against your HTTP or database implementation. See packages/pivot/server-reference/README.md for the factory contract and capability profile.

The kit fails when a required capability is missing. Advertising a capability with no corresponding acceptance assertions also fails. A passing result establishes only the required baseline profile and any additional capabilities whose dedicated assertions ran (currently fullExport).

Complete the production checks

This reference rejects hierarchy expansion/collapse, subtotals, date/bucket grouping, calculations, independent summaries and sort-by-summary. Strict local aggregate parity still requires its own acceptance profile. Complete flat export is covered by the dedicated fullExportCases; see complete server export. Add implementation and independent fixtures before advertising those capabilities.

Run the kit against your actual server, then validate that deployment’s authentication, concurrency, timeouts, database transactions and cancellation. The synchronous SQLite reference checks AbortSignal between statements; it cannot interrupt a running statement. This baseline does not certify browser rendering, customer infrastructure or production load.