The aggregate is correct in PostgreSQL and wrong in JSON

A leaderboard query returns the correct total in psql, but the API moves that row below a smaller total. The database value crossed JavaScript as a string, one layer converted it to Number, and two distinct integers became indistinguishable. Small fixtures never reached the boundary, so every earlier test passed.

Follow the value through five separate type boundaries

The only reliable diagnosis follows the value through each representation. Capture the PostgreSQL result type, the driver's runtime value, the server's serialization input, the raw HTTP bytes, and the client's parsed value. A green assertion at one boundary cannot certify the next.

The source column does not determine an aggregate's result type. PostgreSQL documents count as returning bigint. It documents sum(smallint) and sum(integer) as returning bigint, while sum(bigint) returns numeric. The database widens results to reduce overflow risk during aggregation.

Empty input changes the value as well as the type question. count returns zero when no rows qualify. Most other aggregates, including sum, return null when no rows qualify unless the query uses coalesce. A client that quietly turns null into zero can hide a query regression.

The driver owns the next conversion. node-postgres documents that a database type without a registered parser is returned as a JavaScript string. Parser configuration, an ORM, or a SQL cast can change that behavior, so log typeof row.total in a controlled test. A TypeScript declaration such as total: number does not convert the runtime value.

The serializer sees whatever the driver or mapper produced:

• A JavaScript string serializes as a JSON string and preserves its characters.
• A safe JavaScript number serializes as a JSON number and preserves the intended integer.
• An unsafe JavaScript number may already have lost precision before serialization.
• A JavaScript bigint causes JSON.stringify to throw unless code supplies an explicit conversion behavior.
• A null aggregate serializes as null unless a mapper changes it.

Raw JSON is the fourth boundary. JSON's number grammar permits integer tokens larger than JavaScript can safely represent, but syntax does not guarantee exact interoperability in every consumer. RFC 8259 calls out the integer range from -(2^53)+1 through (2^53)-1 as the range where common binary64 implementations agree exactly.

JavaScript parsing is the fifth boundary. JSON.parse turns a number token into Number. By the time an assertion reads the parsed object, the original digits may already be rounded. That is why the test sometimes needs the raw response text as well as the parsed field.

Run this database diagnostic before changing API code:

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL'
SELECT
  pg_typeof(count(*)) AS count_type,
  count(*) AS row_count
FROM orders
WHERE status = 'paid';

SELECT
  pg_typeof(sum(amount_minor)) AS sum_type,
  sum(amount_minor) AS total_minor
FROM orders
WHERE status = 'paid';

SELECT
  '9007199254740991'::bigint AS max_safe,
  '9007199254740992'::bigint AS next_integer,
  '9007199254740993'::bigint AS collision_witness;
SQL

The last query does not require nine quadrillion rows. It verifies that the database and test connection can carry selected boundary values. Use a small real aggregate to prove query semantics, then use controlled literals or an injected repository result to exercise serialization boundaries.

Interpret the evidence in order. If psql already shows the wrong total, investigate data and SQL. If the driver shows the correct decimal string but the mapper shows a rounded number, the lossy conversion is in application code. If the raw JSON contains the exact number token but the parsed JavaScript value differs, the client representation is the boundary. If the raw JSON contains a decimal string and the UI sorts incorrectly, the consumer comparison is wrong.

Choose the wire contract before writing assertions

There is no universally best representation. There is a best representation for a known domain bound and client set.

A JSON number is convenient when the product enforces a durable upper and lower bound inside the exact range of every supported consumer. The server must check that bound before converting. "Our current table is small" is not a bound. A quota, schema constraint, or business invariant that is tested on every write can be one.

A canonical decimal string preserves an integer's digits across JSON. Clients must validate and parse it deliberately. The contract should define whether negatives are allowed, whether leading zeros are allowed, and whether a plus sign is forbidden. A simple non-negative count can use ^(0|[1-9][0-9]*)$.

Do not emit either a number or a string depending on size. That creates a union type precisely at the boundary where clients are most likely to fail. One field should have one documented wire type.

A native JavaScript bigint is useful inside the server or client after parsing a decimal string. It is not a native JSON data type. MDN documents that JSON.stringify throws when it encounters a BigInt without custom serialization. Convert at the API boundary rather than patching BigInt.prototype globally in an application you do not fully control.

Narrowing in SQL can be correct when the domain really is narrower. Casting count(*) to integer gives consumers a smaller result type, but PostgreSQL will reject a value outside that type's range. That failure is safer than silent rounding, yet it still becomes an availability risk if the product can grow past the assumption. Test the bound and the overflow response.

A useful contract table looks like this:

Do not treat an OpenAPI format: int64 annotation as proof that a JavaScript client will preserve every 64-bit value. Test the generated client and runtime representation you actually support. The API description should make the safe wire choice visible, often by using a string schema with a decimal pattern and examples at meaningful boundaries.

For money, identifiers, counters, and aggregate totals, avoid one generic rule. Money may need a decimal type and a scale. Identifiers should usually be opaque strings even if the database stores numbers. Counts are non-negative integers. A sum over bigint can return PostgreSQL numeric and may exceed the signed 64-bit range. Give each field a semantic contract.

Reproduce three bugs that small fixtures conceal

Worked example one: a string passes a loose equality check.

The database returns count(*) as bigint. The driver produces "42". A JavaScript test uses == 42 and passes because loose equality coerces the string. A client generated from the API description expects a number and later rejects the same response.

Assert type before value. The following normalizer creates a decimal-string contract without accepting an unsafe number.

import assert from "node:assert/strict";
import test from "node:test";

const canonicalInteger = /^(0|[1-9][0-9]*|-[1-9][0-9]*)$/;

export function exactIntegerString(value: unknown): string {
  if (typeof value === "string") {
    assert.match(value, canonicalInteger);
    return value;
  }

  if (typeof value === "bigint") {
    return value.toString();
  }

  if (typeof value === "number") {
    assert.ok(
      Number.isSafeInteger(value),
      "number must be a safe integer",
    );
    return String(value);
  }

  throw new TypeError(
    "expected a decimal string, bigint, or safe integer number",
  );
}

test("keeps exact database text", () => {
  assert.equal(
    exactIntegerString("9223372036854775807"),
    "9223372036854775807",
  );
});

test("rejects an unsafe Number", () => {
  assert.throws(
    () => exactIntegerString(9007199254740992),
    /safe integer/,
  );
});

test("rejects non-canonical text", () => {
  assert.throws(() => exactIntegerString("0042"));
  assert.throws(() => exactIntegerString("-0"));
  assert.throws(() => exactIntegerString("12.5"));
});

The function accepts a safe number to ease migration inside a server, but the public field should still emit one chosen type. If the contract is a string, call this normalizer and serialize its return value. Do not let the client accept both types forever unless the schema explicitly promises that union.

Worked example two: two integers collapse to one Number.

The first unsafe region is useful because it creates a collision witness:

import assert from "node:assert/strict";
import test from "node:test";

test("shows why parsed JSON numbers cannot carry every bigint", () => {
  const leftText = "9007199254740992";
  const rightText = "9007199254740993";

  assert.notEqual(BigInt(leftText), BigInt(rightText));
  assert.equal(Number(leftText), Number(rightText));

  const unsafePayload = '{"left":9007199254740992,"right":9007199254740993}';
  const parsed = JSON.parse(unsafePayload) as {
    left: number;
    right: number;
  };

  assert.equal(parsed.left, parsed.right);
});

test("preserves both values when the wire type is string", () => {
  const safePayload =
    '{"left":"9007199254740992","right":"9007199254740993"}';
  const parsed = JSON.parse(safePayload) as {
    left: string;
    right: string;
  };

  assert.notEqual(BigInt(parsed.left), BigInt(parsed.right));
});

Every number in this example is part of a deterministic language-boundary demonstration. No benchmark or production measurement is implied.

A subtle bad test builds its expected value with Number("9007199254740993"). The expected value rounds in the same way as the actual value, so equality passes. Keep expected boundary values as decimal strings or BigInt literals and derive them independently from the code under test.

Worked example three: exact strings sort in the wrong order.

Changing the API field to a string fixes precision but can break ranking. Lexicographic comparison considers the first differing character. As a result, "900" can sort above "1000" in descending string order even though 1000 is larger.

Use exact numeric comparison and a deterministic secondary key:

import assert from "node:assert/strict";
import test from "node:test";

type Leader = {
  accountId: string;
  totalMinor: string;
};

function compareIntegerText(left: string, right: string): number {
  assert.match(left, /^(0|[1-9][0-9]*|-[1-9][0-9]*)$/);
  assert.match(right, /^(0|[1-9][0-9]*|-[1-9][0-9]*)$/);

  const leftValue = BigInt(left);
  const rightValue = BigInt(right);

  if (leftValue < rightValue) return -1;
  if (leftValue > rightValue) return 1;
  return 0;
}

export function rankLeaders(rows: Leader[]): Leader[] {
  return [...rows].sort((left, right) => {
    const byTotalDescending = compareIntegerText(
      right.totalMinor,
      left.totalMinor,
    );
    return (
      byTotalDescending ||
      left.accountId.localeCompare(right.accountId)
    );
  });
}

test("sorts by numeric value and resolves ties by account ID", () => {
  const ranked = rankLeaders([
    { accountId: "b", totalMinor: "900" },
    { accountId: "c", totalMinor: "1000" },
    { accountId: "a", totalMinor: "1000" },
  ]);

  assert.deepEqual(
    ranked.map((row) => row.accountId),
    ["a", "c", "b"],
  );
});

Do not subtract BigInts inside an array comparator and return that result. JavaScript sort comparators return numbers, while subtracting two BigInts returns a BigInt. The explicit less-than and greater-than branches keep the types correct.

A near-miss resembles a precision bug but comes from null semantics. sum over no qualifying rows returns null, while the API contract expects "0". If the client converts null to zero, the screen looks correct and the producer defect stays hidden. Decide whether the query should use coalesce or the API should expose null, then assert the raw field exactly.

Test the real HTTP path without losing the raw digits

A serializer unit test proves conversion logic. A database integration test proves the driver value. An HTTP test proves those pieces are wired together. You need all three for a high-risk aggregate.

This black-box test expects a decimal-string contract. The route and fixture query are illustrative and must be replaced with your supported endpoint and test-data mechanism.

import assert from "node:assert/strict";
import test from "node:test";

type StatsResponse = {
  paidOrderCount: string;
  paidAmountMinor: string | null;
};

test("returns exact aggregate strings over HTTP", async () => {
  const baseUrl = process.env.API_BASE_URL;
  assert.ok(baseUrl, "API_BASE_URL is required");

  const response = await fetch(
    baseUrl + "/test-fixtures/aggregate-boundary/stats",
  );
  assert.equal(response.status, 200);

  const raw = await response.text();
  assert.match(
    raw,
    /"paidOrderCount":"9007199254740993"/,
  );

  const body = JSON.parse(raw) as StatsResponse;
  assert.equal(typeof body.paidOrderCount, "string");
  assert.equal(
    BigInt(body.paidOrderCount),
    9007199254740993n,
  );
  assert.equal(body.paidAmountMinor, null);
});

Raw substring matching is not the main schema assertion. It is diagnostic evidence that the exact digits and quote characters crossed HTTP before parsing. The parsed checks enforce type and exact value.

Avoid a fixture route in production. Better options include a repository seam in a component test, a database view available only to the isolated test deployment, or seeded rows whose real aggregate is small plus a separate serializer boundary test with the large literal. The goal is to run production conversion code without creating an unsafe operational endpoint.

Capture a boundary report when the test fails:

database.pg_typeof=bigint
database.text=9007199254740993
driver.typeof=string
driver.value=9007199254740993
serializer.input_type=number
raw_json={"paidOrderCount":9007199254740992}
client.typeof=number
client.value=9007199254740992

This is illustrative output showing a conversion bug. It is not the result of a performed experiment. The first divergence occurs at serializer.input_type, where exact text became an unsafe number. Fix that mapper rather than changing the database query or client assertion.

A different report may show the driver already returning a rounded number. Inspect custom type parsers and ORM conversions. Another may show exact quoted digits at HTTP but lexicographic ranking in the UI. The five-boundary report assigns each failure without guesswork.

Add adjacent values, not just one large maximum. Test Number.MAX_SAFE_INTEGER, the next integer, and a collision witness above it. Include zero, a normal count, the database maximum when the field can reach it, negative values only where the domain permits them, and null for aggregates that can be empty.

Run the same boundary through any generated client used by the product. A raw endpoint test can pass while a generated model narrows the field or a response adapter calls Number. Save the generated client's runtime type in the evidence, and keep that check when the API description or generator version changes.

For PostgreSQL sum(bigint), remember that the result is numeric and can exceed bigint. If your JavaScript contract parses it with BigInt, require an integer decimal string. Do not reuse that parser for avg or scaled decimal money values that can contain a decimal point.

Migrate a live endpoint without surprising clients

Changing total: 42 to total: "42" is a breaking change for many clients even though the digits look the same. Generated models, validators, sort functions, and UI formatters can all fail. Treat it as a contract migration.

Add a new exact field or version the endpoint. For example, keep total temporarily and add totalExact as a decimal string. During the transition, assert equality whenever the old value is safe. For unsafe values, the old field cannot be authoritative, so document the limitation and direct clients to the new field.

Do not populate an unsafe legacy number with a rounded value just to preserve its type. Depending on the product, return an error, omit the field under a versioned rule, cap it only if the semantics permit, or accelerate client migration. Silent corruption is the worst compatibility strategy.

Update consumers in this order:

1. Parse and validate the new string without using it for display.
2. Compare the new value against the old value inside the safe range.
3. Switch calculations and sorting to exact logic.
4. Switch rendering to the new field.
5. Observe usage of the legacy field.
6. Remove it only under the API's versioning policy.

The dual-field period costs payload size, code branches, and monitoring. It also creates a risk that fields disagree. Add a producer invariant and a client diagnostic for mismatches. Set a retirement owner and date before launch so temporary compatibility does not become permanent.

Parser configuration has its own trade-off. Registering a global node-postgres bigint parser that returns Number makes ordinary code convenient but risks silent precision loss. Returning BigInt preserves integers but requires explicit JSON conversion and may surprise libraries. Keeping decimal strings is verbose but makes the lossless boundary visible. Prefer per-query or domain-specific conversion when different fields need different policies.

CI should exercise database, serializer, and HTTP layers separately:

name: aggregate-contract

on:
  pull_request:
    paths:
      - "src/database/**"
      - "src/api/stats/**"
      - "tests/aggregates/**"
      - "openapi/**"
  workflow_dispatch:

jobs:
  bigint-boundaries:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:17
        env:
          POSTGRES_PASSWORD: test
        ports:
          - 5432:5432
        options: >-
          --health-cmd "pg_isready -U postgres"
          --health-interval 5s
          --health-timeout 5s
          --health-retries 10
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: "22"
          cache: npm
      - run: npm ci
      - run: npm run test:aggregate-contract
        env:
          DATABASE_URL: "postgres://postgres:test@localhost:5432/postgres"

The command name is a project placeholder. The suite behind it should query pg_typeof, inspect the driver runtime value, exercise boundary serialization, and call the HTTP handler or endpoint. A schema-only job cannot replace these exact-value checks.

The ongoing cost is client complexity. Decimal strings need validation and formatting. BigInt arithmetic cannot mix directly with Number arithmetic. Some analytics or spreadsheet consumers prefer numbers. Those are valid concerns, but they do not create missing precision. Either enforce a safe domain bound and use numbers honestly or preserve the digits and pay the conversion cost.

Separate numeric corruption from an exact value read at the wrong time

An aggregate that is one unit lower than a later database query looks like a precision failure, especially near the JavaScript safe-integer boundary. A second root cause can produce the same API digits: the request read an earlier snapshot or a different data source before the last qualifying row became visible there. In the precision failure, the intended digits exist at an upstream boundary and change during conversion. In the read-consistency failure, every boundary preserves its input exactly, but the input was already a different valid integer.

Do not compare an HTTP response with an unrelated query run several seconds later and call the difference serialization loss. The data can legitimately change between observations. Reproduce with controlled immutable fixtures, or correlate the aggregate query and boundary report from the same test operation. Record which logical data source and query definition were used without exposing credentials. If the architecture deliberately serves a lagging copy, test its published freshness behavior separately from numeric exactness.

The existing boundary report answers the precision question when read top to bottom. database.pg_typeof identifies the database result type, but it does not establish correctness of the query. database.text is the exact textual value observed at that boundary. driver.typeof and driver.value show whether the database representation survived the driver. serializer.input_type is the most important conversion clue. raw_json proves what crossed the HTTP boundary, including whether digits were quoted. The client type and value show what remained after parsing or generated-client adaptation.

For a healthy decimal-string contract, the database and driver values carry the same digits, the serializer receives a lossless representation, the raw JSON contains those digits inside quotes, and the client retains a string until it deliberately constructs an exact integer. For the shown precision defect, database.text and driver.value end in 993, then serializer.input_type becomes number and raw_json ends in 992. The first changed digits identify the conversion boundary.

For the look-alike read defect, database.text in the request-correlated report already ends in 992, and every later value remains 992 with the correct types. A separate query that returns 993 proves only that another observation saw another value. It does not prove where the extra unit became visible or which query semantics applied. Investigate filters, joins, transaction timing, and data-source freshness before touching the serializer. The misleading healthy field is database.pg_typeof=bigint: a perfectly exact bigint can still be the wrong business aggregate.

Another misleading result is agreement between actual and expected when the expected value was derived through the same lossy path. If both became the same rounded Number, the boundary test certifies a shared defect. Keep expected digits as reviewed text or exact database literals, and keep their derivation independent from the response mapper. Exactness requires two independent representations, not two references to one converted value.

In an existing suite, snapshots and typed fixtures usually break first because they encode the old field as an unquoted numeric literal. Generated clients and dashboards fail next, often at validation, sorting, or formatting rather than at the endpoint call. Land boundary fixtures that preserve expected digits before changing the producer. Then release consumer parsing that can read the new exact field without using it for decisions. Verify shadow comparisons only where the legacy number is safe, because an unsafe legacy value is not a valid oracle.

Move calculation and ordering consumers before presentation-only consumers if they affect business decisions. A display can often render decimal text while a ranking or threshold comparison silently uses the wrong numeric operation. Update generated-client expectations and analytics ingestion before making the exact field authoritative. Change the producer only after those consumers are observable in the environments that matter. Remove the legacy representation after its readers are gone, not merely after the main user interface changes.

The rollout is working when the same controlled digits can be followed from query through every supported client, and when a deliberately stale or alternate read is reported as a source mismatch rather than a conversion mismatch. Keep one adjacent-value collision case permanently. Keep freshness tests separate so a delayed data source does not make the precision gate flaky.

Request-correlated boundary evidence has a specific operational cost. Capturing query results and source identity adds instrumentation, while issuing a second diagnostic query adds database load and can observe a different snapshot anyway. Prefer controlled component fixtures for full traces and keep production diagnostics sparse and sanitized. Stronger snapshot coordination can make a test more deterministic, but holding database work open longer consumes connection and transaction capacity. Use it for bounded diagnosis, not as a blanket monitoring strategy.

Ownership follows the first incorrect exact value. The data or query owner proves filters, joins, aggregation semantics, and the read source. The API owner proves driver and serializer boundaries. The API-description and SDK owners prove the declared wire type and generated runtime type. The consuming team proves comparison, formatting, and persistence after parsing. A handoff needs the exact digits at each boundary, their runtime types, the query or fixture version, the observation order, and whether the data was immutable. A screenshot of a rounded dashboard number cannot distinguish these owners.

An exact representation test does not prove the aggregate means the right thing. A query can count duplicated join rows, exclude a status, use the wrong time window, or sum minor units under the wrong currency assumption while preserving every digit perfectly. Test query semantics with small auditable fixtures, then test large exact values at the representation boundaries. Neither test substitutes for the other.

Do not use BigInt everywhere just because one aggregate needs it

Keep a JSON number when a real invariant makes the entire domain safe. A page size capped at 1000, a percentage bounded from 0 to 100, or a small retry count does not benefit from a decimal string. Test the invariant that justifies the simpler type.

Do not use BigInt for non-integer decimal values. Prices with fractional major units, averages, and ratios need a scale-aware decimal contract. Minor-unit integers can work for currencies with a defined scale, but the currency and scale still belong in the contract.

Avoid moving authoritative sorting to every client when the server already owns ranking. Database ordering can compare exact numeric types and apply a stable tie-breaker. Return the rank and exact value, then test that pagination preserves the same order. Client-side exact sorting is still useful for local subsets, not as a replacement for a global leaderboard query.

Do not patch BigInt.prototype.toJSON in a shared runtime without understanding every consumer. A global conversion affects unrelated serialization. Convert named fields at the boundary so reviewers can see whether the wire type is a string or number.

Skip huge physical fixtures. Boundary injection proves serialization more cheaply and safely. Keep a few seeded rows to prove that count, sum, filters, joins, and null behavior are correct. Those are different risks and deserve different fixtures.

Finally, do not accept a number-or-string union as a permanent convenience unless consumers truly support both. It doubles branches, weakens generated types, and lets producer drift pass unnoticed. Pick one representation, publish its limits, and fail loudly when a value cannot satisfy it.

The trade-off is straightforward. Exact decimal strings add parsing and migration work. Safe JSON numbers are easier but require a durable bound. A lossy conversion appears easiest until a total crosses the boundary, a ranking changes, and the team has no raw digits left to recover the correct value.