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.

Use aggregationSemantics: 'server' to pin plugin/controller execution to the trusted backend. A pin prevents accidental engine switching; it does not guarantee identical local/server results. Choose null, count, distinct, totals and precision policies in the backend contract and validate them against independent facts. For local analysis, use the V2 numeric/scalar aggregation configuration.

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 default 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 and optional distinct).

Distinct counts and nonadditive totals

For unique customers or other scalar counts, allow distinct for the authorized field and implement COUNT(DISTINCT column). Null is excluded and empty text is included; use an explicit backend collation policy. Recompute each aggregation grain from facts rather than adding lower-grain distinct counts.

The optional distinct capability has its own DISTINCT_FACTS dataset and distinctCases/distinctExportCases. Its oracle includes duplicate facts, customers spanning groups, null-only and sparse coordinates, empty text, source/HAVING filters, tenant isolation, duplicate-preserving drillthrough and complete exports. Supply distinctFixture adapters when advertising this profile. Existing baseline targets that omit it keep their original obligations.

The SQLite reference also advertises a requested grand-total column in its logical column window. That member counts toward the declared axis count and window limit. The grid will not invent a total column omitted from an explicitly authoritative server window.

Complete the production checks

The flat baseline rejects hierarchy expansion/collapse, subtotals and calculations. Separate trusted hierarchy and analytical opt-ins support their declared prefix, subtotal, calculation, ranking and grand-opposite summary-sort profiles. Date/bucket grouping and independent local summaries remain excluded. See current reference profiles. 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.

Each SQLite load, complete export, member lookup and drillthrough uses one operation-owned read snapshot. Real two-connection WAL tests commit new facts between statements and verify counts, member paths, facts and totals remain consistent within the response. Operation-owned savepoints preserve an existing caller transaction on success, failure and observed cancellation. Separate requests do not share a snapshot; a later export can reflect newer facts than an earlier viewport.