Stop XP totals from changing at the JavaScript boundary

A player earns one more XP, but the API returns the same total as before. PostgreSQL stored both values correctly. Precision disappeared when the database value became a JavaScript number.

That is only one failure in this path. An aggregate can overflow an explicit PostgreSQL ::int cast before Node receives anything. Preserving the value as a JavaScript bigint avoids rounding, but sending it directly through JSON raises a serialization error. All three incidents involve large integers, yet they need different evidence and different fixes.

The safest test follows one value across named boundaries: database expression, driver result, application normalization, domain calculation, and JSON token. At each point, record the value and its runtime type. A TypeScript annotation is useful to the compiler, but it is not proof of what a database driver returned at runtime.

Locate every conversion, not just the bigint column

src/db/schema.ts declares users.xp as PostgreSQL bigint with Drizzle's { mode: "number" }. Drizzle documents that number mode is intended for values above the 32-bit range but below JavaScript's 53-bit safe-integer boundary. The selected TypeScript type is number, which makes arithmetic and JSON convenient as long as the product enforces that range.

Lifetime XP is not the only value on a leaderboard. points_ledger.delta is a PostgreSQL integer. Weekly and monthly leaderboards calculate sum(delta). PostgreSQL documents that sum(integer) returns bigint, because a sum can exceed the range of one input row. Several current queries then append ::int, narrowing that aggregate back to a four-byte signed integer before it leaves the database.

That explicit cast creates a lower failure boundary than JavaScript precision. A total of 2,147,483,648 is exactly representable by a JavaScript number and by PostgreSQL bigint, but not by PostgreSQL integer. The database rejects the cast. Increasing a Node memory limit, changing JSON serialization, or using BigInt() after the query cannot repair a query that never returned a row.

Other code paths avoid that cast but convert raw values later. RawLeaderboardRow.points is typed as number | string, and toLeaderboardEntry() calls Number(row.points). The conversion accepts a decimal string outside the safe range and returns a rounded number without throwing. Number.isSafeInteger() exists elsewhere in the library for page parameters, but it does not guard this row conversion.

The public leaderboard route then passes entries to Response.json(). Numbers serialize normally, including numbers that already lost integer precision. Native JavaScript bigints preserve exact integer arithmetic, but JSON has no BigInt value type. MDN documents that JSON.stringify() raises a TypeError when it encounters a BigInt unless code supplies an explicit conversion behavior. Response.json() cannot make that product decision on the application's behalf.

Profile statistics introduce another shape. aggregateTrackSkillStats() adds pointsAwarded values as JavaScript numbers. Each source column is an integer, but an unbounded number of additions can still leave the safe-integer range. That may be impossible under the business model, and if so the correct control is an explicit domain bound with a test. “Each row is small” is not a bound on the sum.

This repository therefore has at least four conversion sites worth classifying:

1. Drizzle maps users.xp from bigint to number.
2. SQL narrows some sum(integer) results with ::int.
3. Raw leaderboard rows pass through Number().
4. JSON serializes the chosen JavaScript representation.

Do not search only for the word bigint. Search for Number(, parseInt, arithmetic reductions, sql<number>, integer casts, Response.json, and schema modes. A bug often appears in code that believes the hard type decision was already made upstream.

The sql<number> generic deserves special attention. It tells TypeScript how the application wants to view a selected field. By itself it does not add a runtime safe-integer check. Driver and Drizzle behavior depend on the selected SQL type and codec. Inspect typeof row.points in a controlled integration test rather than treating the generic as conversion evidence.

Capture that runtime evidence before normalizing it. A useful repository test returns one safe aggregate and one boundary literal from the same query, then records typeof, the exact printable value, and the PostgreSQL type reported by pg_typeof. If a dependency upgrade changes a bigint result from string to bigint, the test fails at the adapter boundary with a clear type difference. A later UI assertion would only report that formatting or JSON broke.

Do not assume every driver path uses the same codec. A schema-aware Drizzle select, a raw db.execute() call, a SQL expression annotated with a generic, and a JSON aggregation can expose different runtime shapes. Test the paths the application actually calls. Converting one fixture through a standalone pg client does not prove the Neon or Drizzle adapter used by production returns the same type.

The all-time and windowed leaderboards are especially useful comparison points. All-time reads users.xp through the declared bigint column mapping. Weekly and monthly views aggregate integer ledger rows and use explicit SQL. If only windowed views fail near 32-bit totals, investigate the aggregate cast. If all-time values round only near the 53-bit boundary, investigate number-mode mapping and later coercion. The UI label “points” hides those different sources.

Reproduce the exact boundaries without a huge dataset

You do not need billions of ledger rows to test aggregate type behavior. PostgreSQL VALUES expressions can create a few controlled integers whose sum crosses the four-byte limit. The query is read-only and reports both the result type and exact decimal text.

#!/usr/bin/env bash
set -euo pipefail

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL'
select
  pg_typeof(sum(value))::text as aggregate_type,
  sum(value)::text as exact_total
from (values (2147483647::integer), (1::integer)) as sample(value);
SQL

if psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL'
select sum(value)::int
from (values (2147483647::integer), (1::integer)) as sample(value);
SQL
then
  echo "expected the narrowing cast to fail" >&2
  exit 1
fi

The first query demonstrates the widening rule with values whose arithmetic is easy to review. The second deliberately exercises the failing cast. Its success branch fails the shell script, so the negative oracle cannot pass merely because the database command ran. No production rows are created or changed.

Test JavaScript's boundary with adjacent decimal strings. 9007199254740991 is Number.MAX_SAFE_INTEGER. The next integer is outside the safe range, and not every larger adjacent integer has a distinct Number representation. Keep the input and expected value exact before conversion.

A small normalizer makes the policy executable. It accepts safe numbers, decimal strings, or native bigints and returns one exact internal representation. It rejects unsafe numbers because their original digits may already be unrecoverable.

// src/lib/exact-integer.ts
export function toExactInteger(value: unknown, field: string): bigint {
  if (typeof value === "bigint") return value;

  if (typeof value === "number") {
    if (!Number.isSafeInteger(value)) {
      throw new RangeError(`${field} must be a safe integer before conversion`);
    }
    return BigInt(value);
  }

  if (typeof value === "string" && /^-?(0|[1-9]\d*)$/.test(value)) {
    return BigInt(value);
  }

  throw new TypeError(`${field} must be an integer number, bigint, or decimal string`);
}

export function toSafeNumber(value: bigint, field: string): number {
  const converted = Number(value);
  if (!Number.isSafeInteger(converted)) {
    throw new RangeError(`${field} exceeds the JavaScript safe-integer range`);
  }
  return converted;
}

// src/lib/exact-integer.test.ts
import { describe, expect, it } from "vitest";
import { toExactInteger, toSafeNumber } from "./exact-integer";

describe("exact integer conversion", () => {
  it("preserves a decimal string above the safe-number boundary", () => {
    expect(toExactInteger("9007199254740993", "points")).toBe(
      9007199254740993n,
    );
  });

  it("rejects a number after its exact origin can no longer be trusted", () => {
    const unsafe = Number("9007199254740993");
    expect(Number.isSafeInteger(unsafe)).toBe(false);
    expect(() => toExactInteger(unsafe, "points")).toThrow(RangeError);
  });

  it("allows an exact value to become a number only inside the safe range", () => {
    expect(toSafeNumber(2_147_483_648n, "points")).toBe(2_147_483_648);
    expect(() => toSafeNumber(9_007_199_254_740_993n, "points")).toThrow(
      RangeError,
    );
  });
});

toSafeNumber() carries exactly one guard, and the guard it does not carry is worth a paragraph. A round-trip comparison such as BigInt(converted) !== value looks like the belt to Number.isSafeInteger()'s braces, and it is dead code. Every integer whose magnitude is at most 2 to the 53rd minus 1 has an exact double, so once Number.isSafeInteger(converted) is true, converted already holds every digit of value and the round-trip cannot disagree. Any value whose magnitude reaches 2 to the 53rd rounds to a double whose magnitude is at least 2 to the 53rd, which Number.isSafeInteger() rejects, so the second half of the condition never evaluates. A sweep over 400,000 random magnitudes up to 100 bits, the exact neighborhoods of 2 to the 52nd, 53rd, 62nd, 63rd and 100th in both signs, and every integer within 5,000 of 2 to the 53rd found not one value where the round-trip added a rejection.

That is the lesson worth keeping. Delete the round-trip and the suite still passes; delete Number.isSafeInteger() and it fails. A condition that cannot change an outcome is not a safety net, it is a comment that reviewers mistake for a check, and the only reliable way to tell the two apart is to remove each one and see whether anything notices. The input-side guard in toExactInteger() survives that test: it rejects a Number that arrived already unsafe, because converting the rounded result to BigInt cannot recreate the digits rounding destroyed.

Avoid an expected value such as Number("9007199254740993"). Production and test would perform the same lossy conversion, then agree on the same wrong number. A BigInt literal or validated decimal string keeps the oracle independent.

Boundary fixtures should cover zero, negative values if the domain permits adjustments, the 32-bit edges, Number.MAX_SAFE_INTEGER, and the first rejected value. Random moderate integers are still useful for arithmetic properties, but they rarely discover a type boundary. Name the boundary in the test so a future maintainer knows why an unusual value is present.

Choose the wire contract before asserting JSON

There are two honest JSON contracts for an integer total. One sends a JSON number and enforces that every possible value is safely representable. The other sends a decimal string and preserves a larger range. Native BigInt can be the server's internal representation, but it must become one of those JSON-compatible forms at the boundary.

A number contract fits XP when product rules place a durable ceiling far below Number.MAX_SAFE_INTEGER. It keeps existing clients, sorting, charting, and formatting simple. The cost is enforcement. Database writes, aggregate reads, arithmetic updates, and deserialization must reject anything outside the documented range. A TypeScript number field without runtime checks is not enforcement.

A decimal-string contract preserves the full integer digits supported by the database or chosen domain. The cost moves to consumers. Clients must validate the string, compare it numerically rather than lexicographically, format it deliberately, and convert it before arithmetic. Changing an existing API field from number to string is a breaking contract even if every digit looks the same in logs.

Keep serialization close to the endpoint and name the decision. The following implementation uses an exact internal BigInt and emits a decimal string. It does not patch global prototypes or silently affect unrelated values.

// src/lib/xp-wire.ts
import { toExactInteger } from "./exact-integer";

export type XpWireValue = {
  xp: string;
};

export function serializeXp(value: unknown): XpWireValue {
  const exact = toExactInteger(value, "xp");
  if (exact < 0n) throw new RangeError("xp must not be negative");
  return { xp: exact.toString(10) };
}

// A route can return Response.json(serializeXp(row.xp)).

Test the raw API token as well as the parsed value. Response.text() preserves the JSON source for diagnosis, while JSON.parse() confirms the public field type. A string contract should reject scientific notation, signs that policy does not allow, decimal points, and whitespace before converting with BigInt().

// src/lib/xp-wire.test.ts
import { describe, expect, it } from "vitest";
import { serializeXp } from "./xp-wire";

describe("XP JSON contract", () => {
  it("publishes an exact decimal string", async () => {
    const response = Response.json(serializeXp("9007199254740993"));
    expect(response.status).toBe(200);
    expect(response.headers.get("content-type")).toContain("application/json");

    const raw = await response.text();
    const body = JSON.parse(raw) as { xp: unknown };

    expect(typeof body.xp).toBe("string");
    expect(body.xp).toMatch(/^(0|[1-9]\d*)$/);
    expect(BigInt(body.xp as string)).toBe(9_007_199_254_740_993n);
    expect(raw).toContain('"xp":"9007199254740993"');
  });
});

This test calls the real web-standard serializer used by route handlers without exposing a test-only endpoint. A separate integration test should still exercise the deployed route with a controlled repository result. Inject the boundary through an internal seam or isolated database fixture. Do not create a public backdoor that lets callers choose arbitrary database outputs.

If the repository keeps its current numeric public contract, replace the serializer with toSafeNumber(exact, "xp") and assert a JSON number plus the enforced maximum. The crucial rule is consistency. Do not send small totals as numbers and silently switch to strings only after a threshold. Consumers would then face a union type driven by user data.

Separate precision loss from query and data defects

The first worked failure occurs at 2,147,483,648 points on a weekly leaderboard. sum(points_ledger.delta) has PostgreSQL type bigint, but the query's ::int cast cannot represent the result. The request fails before toLeaderboardEntry() runs. Database logs and the direct SQL boundary query identify the cast. Removing Number() does nothing for this incident.

The appropriate query fix is to stop narrowing the aggregate unless the business contract genuinely requires a four-byte result. Let PostgreSQL return the widened sum, inspect the driver's runtime representation, normalize it exactly, and only then apply the selected public bound. The cost is explicit conversion code. The benefit is that the database no longer imposes an accidental lower limit just to obtain a convenient JavaScript type.

The second failure occurs at 9,007,199,254,740,993. A driver or raw query returns the exact decimal string. Number(row.points) produces a Number that cannot represent the original integer, and the endpoint still returns 200. Evidence should show raw.type=string, the raw digits, normalized.type=number, and the chosen wire token. A serializer test sees a wrong successful response rather than an exception.

Do not log sensitive player data to prove this. A test fixture ID, field name, runtime type, safe-range result, and redacted or controlled boundary value are enough. For production telemetry, record whether a value was rejected and which boundary failed. Huge exact XP totals may themselves reveal account activity, so use established logging policy.

The third failure looks like conversion loss but is actually a query-semantic defect. Suppose the exact database aggregate already ends in 992 when the expected business total ends in 993. Every later layer faithfully preserves 992. Converting to BigInt cannot create the missing point. Investigate filters, joins, transaction visibility, duplicate or absent ledger entries, and the definition of the time window.

This repository documents an invariant that lifetime users.xp equals the sum of ledger deltas. A read-only diagnostic can compare exact decimal text before any JavaScript conversion:

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL'
select
  u.id,
  u.xp::text as stored_xp,
  coalesce(sum(p.delta), 0)::text as ledger_xp
from users as u
left join points_ledger as p on p.user_id = u.id
group by u.id, u.xp
having u.xp <> coalesce(sum(p.delta), 0)
order by u.id
limit 100;
SQL

An empty result supports the invariant for the observed database snapshot. It does not prove every historical transaction was correct or that a replica saw the same state. A nonempty result identifies data consistency work, not a JSON conversion bug. Run production diagnostics through approved read-only access and avoid copying identifiers into public tickets.

Read both sides from one database statement, as the diagnostic does. Querying users.xp, waiting, and then querying the ledger can report a false mismatch while a legitimate award transaction commits between the two reads. The single grouped statement observes one statement snapshot under PostgreSQL's normal semantics. If the application uses replicas, record which endpoint served the query because replica delay can still make an application response differ from a primary-side diagnostic.

Concurrency can expose another false fix. Checking currentXp + delta in application code before acquiring the same row lock used for the update lets two requests validate against the same old total. Each addition looks safe independently, while the serialized writes may produce a different final state or one update may be lost depending on implementation. Keep range validation inside the transaction after locking the row, and test two concurrent awards near the chosen maximum.

The oracle for that concurrency test is the committed ledger and user row, not the two HTTP success statuses. Both requests might return success even if one total overwrote the other. After they settle, require the expected number of ledger entries, their exact summed delta, the exact stored XP, and the defined response for an award that would cross the maximum.

The fourth failure happens after a successful exact query. Changing the schema to { mode: "bigint" } makes application arithmetic exact, but an unconverted BigInt reaches Response.json(). The route throws during serialization. The evidence is a TypeError at the JSON boundary, not rounded digits. Add an explicit field serializer; do not replace BigInt with Number merely to make the exception disappear.

One more near-miss is numeric sorting. Decimal strings sorted with the default JavaScript string order place "1000" before "900". That is a client contract bug even though every digit survived. Convert validated strings to BigInt for comparison, or sort in the database before serialization and preserve that order. Do not subtract two BigInts in an array comparator because comparators return numbers; use less-than and greater-than branches.

Finally, distinguish integer totals from decimal quantities. PostgreSQL sum(bigint) returns numeric, but the sum remains integral for integral inputs. avg, ratios, and money represented in major units can contain fractional values. A regex and BigInt() parser designed for XP must reject decimal points. Reusing it for averages would trade one type error for another.

Migrate the leaderboard without hiding a breaking change

Begin with an inventory of conversions. Search the schema for bigint modes, SQL for ::int and aggregate expressions, TypeScript for Number() and numeric generics, reducers for accumulating totals, and endpoints for JSON serialization. For each field, write down its database type, runtime driver type, internal domain type, wire type, and enforced range.

Then choose the XP contract. If the product can guarantee a safe numeric ceiling, document it below Number.MAX_SAFE_INTEGER, enforce it in write paths and normalization helpers, and add database protection where appropriate. Remember that adding two individually safe numbers can produce an unsafe total. Check the result before committing both the ledger and cached lifetime value.

If exact range matters more than numeric compatibility, migrate internal calculations to BigInt and the wire field to a decimal string. Inventory every consumer first: level calculation, number formatting, sorting, charts, caches, snapshots, mobile clients, analytics exports, and third-party integrations. BigInt cannot mix directly with Number arithmetic, so helpers such as levelForXp(number) need an explicit bounded conversion or a compatible redesign.

Do not change one response field in place and rely on TypeScript to find every consumer. External clients and stored JSON are outside the compiler. Use a versioned API, a coordinated flag day, or a temporary second field with a stated removal date. During a dual-field migration, derive both representations from the same exact value and reject the request if the legacy number is unsafe. Never emit a rounded legacy number beside an exact string because consumers may continue trusting the wrong field.

Before introducing a second field, inspect how consumers detect capabilities. A web client deployed with the server can often change in one release, while a mobile client or exported dataset may need a longer overlap. State which field controls sorting and display during the overlap. If one consumer reads the number for sorting and the string for display, the page can show exact digits in the wrong order, which is harder to notice than a type error.

Database constraints are useful when the number contract has a durable maximum, but add them after auditing existing rows and every administrative write path. A constraint rejects invalid state regardless of which application wrote it. Its trade-off is operational: a previously tolerated backfill or adjustment now fails at the database boundary and needs a deliberate migration procedure. Name the constraint so logs explain the policy rather than showing an anonymous check violation.

Add tests in layers. Pure conversion tests cover exact strings, safe numbers, unsafe numbers, malformed text, and negative policy. Repository integration tests capture the actual runtime type returned by the installed driver. SQL boundary tests exercise aggregate widening and any intentional casts. API tests assert raw token type. Consumer tests verify numeric ordering and formatting.

CI should keep the database behavior check separate from the fast unit suite. The commands below assume an isolated PostgreSQL service and a controlled migration have already been provided by the test environment.

steps:
  - name: Test exact integer normalization
    run: pnpm exec vitest run src/lib/exact-integer.test.ts

  - name: Verify PostgreSQL aggregate boundaries
    run: bash scripts/check-xp-aggregate-boundaries.sh
    env:
      DATABASE_URL: ${{ secrets.TEST_DATABASE_URL }}

  - name: Verify the public XP wire type
    run: pnpm exec playwright test e2e/xp-wire-contract.spec.ts

Roll out guards before changing representation. A guard can reveal whether existing values already violate the proposed bound without silently converting them. Monitor rejections with controlled metadata, investigate every occurrence, then change SQL and serializers. This order avoids deploying a new representation while unknown bad data is already present.

Cache keys and cached values deserve a migration plan. A serialized leaderboard produced under the numeric contract can survive after code expects strings. Version the cache namespace or invalidate the relevant entries when the wire shape changes. A mixed cache can create intermittent type failures that disappear after expiry and look like random conversion bugs.

Snapshots and fixtures can carry the old type too. Search JSON files for numeric xp, mocked database rows for string versus number assumptions, and analytics schemas for numeric columns. Update them through the same contract decision, not with a global textual replacement. Some numeric fields such as score and level should remain numbers, and converting them along with XP would spread unnecessary complexity.

Keep rollback possible. If the new string field causes an unexpected client failure, the server can continue deriving the legacy safe number while values remain under the enforced ceiling. Once data is allowed above that ceiling, returning to a number contract is no longer lossless. Treat raising the accepted maximum as the point of no simple rollback, and require consumer readiness before crossing it.

The trade-offs are concrete. Safe numbers minimize client work but require a durable ceiling. BigInt preserves server arithmetic but spreads type changes through calculations and cannot enter JSON directly. Decimal strings preserve the wire value but add validation, formatting, and numeric comparison work to every consumer. PostgreSQL numeric supports larger and fractional values but needs a decimal policy rather than a BigInt shortcut.

Do not use BigInt for every numeric field

Scores bounded from 0 to 100, page numbers, small attempt counts, and level values are ordinary numbers when validation enforces their range. Converting them to BigInt makes common arithmetic and UI libraries harder to use without buying meaningful safety.

Identifiers should usually be opaque strings even if a database happens to store them in an integer column. Clients do not add user IDs or calculate with submission IDs. A string avoids precision trouble and prevents accidental arithmetic while preserving identity.

Money, averages, probabilities, and ratios need a different decision. BigInt works for integer minor units when currency and scale are defined. It does not represent a fractional average. Use an exact decimal strategy or a carefully bounded floating-point contract according to domain requirements. Calling every large-looking value “bigint” hides those semantics.

Do not patch BigInt.prototype.toJSON globally in a shared application just to stop one route from throwing. That changes serialization for unrelated libraries and fields, and reviewers can no longer see whether each API intended a string or lossy number. Convert named fields at a visible boundary.

Avoid removing a PostgreSQL ::int cast blindly if downstream code genuinely relies on a four-byte range. First identify why the cast exists, then replace accidental narrowing with explicit application validation or retain it as a documented database constraint. The test should prove the chosen limit and the error response, not merely maximize range.

Likewise, do not migrate the current XP column to BigInt based only on an unreachable synthetic value. Boundary tests reveal technical capability. Product rules determine reachable values. If maximum awards, account lifetime, and administrative adjustments establish a much smaller ceiling, enforce that ceiling and keep the simpler number contract. Revisit it when the reward model changes.

The goal is not to carry the largest type through every layer. It is to preserve the value required by the domain, reject values the contract cannot represent, and make each conversion reviewable. When a leaderboard total is wrong, that discipline tells the engineer whether to fix SQL, a codec, arithmetic, serialization, or the data itself.