Time, Money and IDs: The Data Types That Bite Backend Developers

The backend bugs that hurt most are rarely clever. Not a race condition in a distributed lock, not a subtle cache problem. A booking that shows up on the wrong day. A total that’s off by one cent. An ID in a URL that tells anyone how many customers you have.

They all come from three kinds of data: time, money and IDs. Every backend has all three, usually from the first week. They look simple, so they get the least design thought. Then they sit in the database for years, and changing them later means a migration on your biggest tables.

For each one: the bug, why it happens, and the fix. Examples are TypeScript and Postgres, but the ideas carry over to any stack.

Part 1: Time

In Integrating Messy APIs I wrote one rule: store moments in UTC, store calendar dates as dates, and always know which time zone a date belongs to. Here’s the longer version, because that one line hides most of the work.

The bug

A guest books a stay with check-in on 14 October. Your server runs in UTC. Somewhere in the code, someone writes:

const checkIn = new Date("2026-10-14");
await db.query("INSERT INTO bookings (check_in) VALUES ($1)", [checkIn]);

The column is a timestamptz. Everything works in testing. Then a host in New York opens the app, the frontend formats the value in local time, and the check-in shows as 13 October. Nothing is “wrong” in any single line. The data type is wrong.

Why it happens: four different things called “time”

Most languages give you one type, like JavaScript’s Date, and you use it for four different things:

• An instant. One exact moment, the same everywhere. “The payment succeeded at 02:14:07 UTC.”
• A local date. A calendar day, no time, no zone. “Check-in is 14 October.”
• A local date-time. A wall-clock time somewhere. “The cleaner arrives at 11:00” in Đà Nẵng. It becomes an instant only once you know the zone.
• A duration. An amount of time. “The token expires in 15 minutes.”

A JavaScript Date is always an instant, despite its name. Put a calendar date into it and the code must invent a time and a zone. That invention is the bug. Two famous quirks make it worse:

// Months start at 0. This is 14 OCTOBER, not 14 September.
new Date(2026, 9, 14);

// Date-only strings are parsed as UTC midnight...
new Date("2026-10-14");        // 2026-10-14T00:00:00.000Z

// ...but date-time strings without an offset are parsed as LOCAL time.
new Date("2026-10-14T00:00");  // depends on the server's zone

On a machine in Vietnam (UTC+7) the last two are seven hours apart. In New York, the first prints as 13 October. Same code, different answer.

The fix: one storage type per kind of time

In Postgres, pick the column type by what the value means:

CREATE TABLE bookings (
  id            uuid PRIMARY KEY DEFAULT uuidv7(),
  property_id   uuid NOT NULL,
  check_in      date NOT NULL,          -- a calendar day
  check_out     date NOT NULL,
  arrival_time  time,                   -- wall-clock, local to the property
  property_tz   text NOT NULL,          -- e.g. 'Asia/Ho_Chi_Minh'
  created_at    timestamptz NOT NULL DEFAULT now()  -- an instant
);

timestamptz doesn’t store a time zone. Despite the name, the Postgres docs say the value is converted to UTC and “the originally stated or assumed time zone is not retained.” When you read it back, Postgres converts it to the session’s TimeZone setting. So timestamptz is the right type for instants like created_at and paid_at. Plain timestamp stores a wall-clock reading with no zone, which is rarely right for an event that happened.

A check-in date is a date. It’s not midnight UTC, and it’s not midnight anywhere. It’s a square on a calendar. Store it as one, and the “off by one day” bug can’t happen, because there’s no time to shift.

Keep the IANA zone next to local times. An IANA zone is a name like Asia/Ho_Chi_Minh or Europe/Berlin from the standard time zone database. Store the name, not an offset like +07:00, because offsets change with daylight saving time and with politics. To answer “what instant is 11:00 on check-in day?” you need the date, the time and the zone, so store all three.

When you do need the instant, convert explicitly:

-- local wall-clock time in a zone -> instant
SELECT (check_in + time '11:00') AT TIME ZONE property_tz AS cleaner_arrives_at
FROM bookings;

In TypeScript, date-fns-tz (version 3) and Luxon both handle this. With date-fns-tz:

import { fromZonedTime, formatInTimeZone } from "date-fns-tz";

// "09:00 on 14 Oct in Vietnam" -> instant
const instant = fromZonedTime("2026-10-14 09:00", "Asia/Ho_Chi_Minh");
instant.toISOString(); // "2026-10-14T02:00:00.000Z"

// instant -> what a person in Vietnam sees
formatInTimeZone(instant, "Asia/Ho_Chi_Minh", "yyyy-MM-dd HH:mm"); // "2026-10-14 09:00"

The rule: convert at the edges. Parse into the right kind as soon as data enters your system, keep it that way through the core, and format for a human only at the very end.

Daylight saving time: gaps and overlaps

Vietnam has no daylight saving time, but your users may live somewhere that does. DST causes two kinds of trouble.

Gaps. When clocks jump forward, some local times don’t exist. In New York on 14 March 2027, clocks go from 01:59:59 straight to 03:00. A reminder scheduled for “02:30 local” has no matching instant.

Overlaps. When clocks fall back, some local times happen twice. In New York on 1 November 2026, 01:30 happens once in daylight time and again an hour later in standard time.

Libraries don’t crash on these. They quietly pick an answer. I checked: Luxon and the Temporal polyfill both turn “02:30 on 14 March 2027 in New York” into 03:30, and both pick the earlier 01:30 in the November overlap. Reasonable, but it’s a decision made for you. Temporal also accepts disambiguation: "reject" to throw instead, useful when a person typed the time and you’d rather ask again.

The other DST trap is arithmetic. “One day later” and “24 hours later” are different things across a DST change:

import { DateTime } from "luxon";

const d = DateTime.fromObject(
  { year: 2027, month: 3, day: 13, hour: 9 },
  { zone: "America/New_York" },
);
d.plus({ days: 1 }).toISO();   // 2027-03-14T09:00:00.000-04:00
d.plus({ hours: 24 }).toISO(); // 2027-03-14T10:00:00.000-04:00

For “remind me at 9 a.m. every day”, you want calendar days. For “the token expires in 24 hours”, you want a duration. Choose on purpose.

The “end of day” bug

A classic report query:

-- Misses anything after 23:59:59.000, and uses the session's zone
SELECT * FROM payments
WHERE paid_at BETWEEN '2026-10-14 00:00:00' AND '2026-10-14 23:59:59';

Two bugs. A payment at 23:59:59.5 is missed (timestamptz has microsecond precision). And “14 October” means 14 October in the session’s TimeZone, which may not be the business’s zone.

Use half-open ranges (start included, end excluded) and say which zone’s day you mean:

SELECT * FROM payments
WHERE paid_at >= timestamp '2026-10-14 00:00' AT TIME ZONE 'Asia/Ho_Chi_Minh'
  AND paid_at <  timestamp '2026-10-15 00:00' AT TIME ZONE 'Asia/Ho_Chi_Minh';

“Today” is always “today somewhere.” Make the somewhere explicit.

What about Temporal?

Temporal is the new built-in JavaScript date/time API, designed to replace Date. It has a type for each kind of time above (Instant, PlainDate, PlainDateTime, ZonedDateTime, Duration), and months start at 1.

Status at the time of writing (October 2026): Node.js 26 enables it by default, and it ships in Firefox 139+ and Chrome and Edge 144+. Safari has it only in Technology Preview, so MDN still marks it “limited availability.” On a Node 26 backend you can start using it. In the browser you still need a polyfill such as @js-temporal/polyfill. On older Node, Luxon or date-fns-tz are fine.

Either way, the library is the easy part. The hard part is deciding which kind of time each field is.

Part 2: Money

The bug

0.1 + 0.2;                // 0.30000000000000004
Math.round(1.005 * 100);  // 100, not 101

The second is nastier: 1.005 * 100 is 100.49999999999999, so it rounds down. Do that across thousands of invoices and your totals stop matching your payment provider’s.

Why it happens

JavaScript numbers (and float/double elsewhere) are binary floating point. They can’t represent most decimal fractions exactly, just as 1/3 can’t be written exactly in decimal. Fine for physics, not for money.

The fix: integers in minor units, plus a currency

The simplest robust approach: store amounts as integers in the currency’s minor unit, and always store the currency next to it. A minor unit is the smallest unit of a currency: cents for USD, fils for KWD.

CREATE TABLE invoices (
  id            uuid PRIMARY KEY DEFAULT uuidv7(),
  amount_minor  bigint NOT NULL,     -- 1999 means 19.99 in USD
  currency      char(3) NOT NULL     -- ISO 4217 code: 'USD', 'VND', 'KWD'
);

Use bigint, not int. A 32-bit integer tops out around 2.1 billion, only about 21 million dollars in cents.

The alternative is Postgres NUMERIC, which stores exact decimals. It’s the better choice when you need fractions of a minor unit, like exchange rates. But node-postgres returns NUMERIC as a string, so you’ll need a decimal library for maths. For plain amounts, integers are simpler.

Not every currency has two decimals

This one hits teams the first time they go international. The official ISO 4217 list gives each currency’s minor-unit digits:

If your code does amount / 100 everywhere, every yen and đồng price is wrong by a factor of 100, and every dinar price by a factor of 10. Keep the exponent in one place:

const MINOR_DIGITS: Record<string, number> = { USD: 2, EUR: 2, JPY: 0, VND: 0, KWD: 3 };

function toMinor(major: string, currency: string): bigint {
  const digits = MINOR_DIGITS[currency];
  if (digits === undefined) throw new Error(`Unknown currency ${currency}`);
  const [whole, frac = ""] = major.split(".");
  if (frac.length > digits) throw new Error(`Too many decimals for ${currency}`);
  return BigInt(whole + frac.padEnd(digits, "0"));
}

toMinor("19.99", "USD"); // 1999n
toMinor("1250000", "VND"); // 1250000n

It parses a string, never a float. Amounts should arrive from clients as strings or integers, never as JSON numbers with decimals.

Where to round

Tax, discounts and conversion all produce fractions, so you will round. Decide where, and write it down as a business rule:

• Do the maths in exact types, then round once, at a defined point.
• Decide per-line vs per-total. They can give different totals. Your accountant or local tax rules decide, not you.
• Decide the rounding mode (half-up, half-even, always down), and use one function everywhere.

Splitting without losing a cent

Split $10.00 three ways: 3.33 + 3.33 + 3.33 = 9.99. A cent disappeared.

The fix is to allocate the remainder explicitly:

function allocate(totalMinor: number, weights: number[]): number[] {
  const sum = weights.reduce((a, b) => a + b, 0);
  const shares = weights.map((w) => Math.floor((totalMinor * w) / sum));
  let remainder = totalMinor - shares.reduce((a, b) => a + b, 0);
  for (let i = 0; remainder > 0; i = (i + 1) % shares.length) {
    shares[i] += 1;
    remainder -= 1;
  }
  return shares;
}

allocate(1000, [1, 1, 1]); // [334, 333, 333], sums to 1000
allocate(10000, [2, 1]);   // [6667, 3333]

The parts always add up to the total. (For huge amounts, use bigint so totalMinor * w can’t lose precision.)

Formatting is for display only

Intl.NumberFormat is excellent for showing money to people:

new Intl.NumberFormat("en-US", { style: "currency", currency: "USD" }).format(1999 / 100);
// "$19.99"
new Intl.NumberFormat("vi-VN", { style: "currency", currency: "VND" }).format(1250000);
// "1.250.000 ₫"

Use it only when rendering. Never store a formatted string or parse one back. In Vietnam a dot separates thousands; in the US it marks decimals.

Part 3: IDs

The bug

Your API returns /invoices/1042. A curious user changes it to /invoices/1041. If your access checks are perfect, they get a 404. If one endpoint forgot a check, they get someone else’s invoice. Even with perfect checks, they’ve learned you’ve issued about 1,042 invoices. Check again next month and they know your volume.

Why it happens

Auto-increment IDs (serial or GENERATED ALWAYS AS IDENTITY in Postgres) are fantastic inside a database: small, fast, naturally ordered. The problems start when they leave it:

• They’re guessable. Sequential IDs make enumeration trivial. They turn every missing authorization check into a data leak. (Row-level security helps here; I cover it in Multi-Tenant SaaS Database Design.)
• They leak business numbers. Counts of users, orders and invoices are often things you’d rather not publish.
• They’re tied to one database. Merge two databases, split a tenant out, or create records offline on a phone, and sequential IDs collide.

UUIDs, and why v4 isn’t the end of the story

A UUID is a 128-bit identifier like 0199e0a3-6f2b-7c41-9a1e-3f6c2d8b5a10. The common one, UUIDv4, is almost entirely random. That fixes guessing and collisions, but it creates a performance problem in big tables.

A B-tree index (the default kind in Postgres and most databases) keeps keys sorted. Sequential keys go at the end, so the hot pages stay in memory. Random keys land anywhere, so inserts touch pages all over the index: more cache misses and page splits. RFC 9562, the current UUID standard, notes that non-time-ordered UUIDs like v4 have poor index locality.

UUIDv7 and ULID: time-ordered and still hard to guess

UUIDv7, defined in RFC 9562, puts a Unix timestamp in milliseconds in the first 48 bits and fills the rest with random bits. New IDs sort roughly in creation order, so inserts behave almost like a sequence, while the random part keeps them impractical to guess.

Postgres 18 has it built in:

SELECT uuidv7();
-- also new in 18: uuidv4() as an alias for gen_random_uuid()

CREATE TABLE guests (
  id   uuid PRIMARY KEY DEFAULT uuidv7(),
  name text NOT NULL
);

On Postgres 17 or earlier, generate v7 in your application (the uuid npm package has v7()) or use an extension.

ULID is an older, similar idea (timestamp plus randomness, written as 26 base32 characters). On Postgres I’d pick UUIDv7: it’s a standard and fits the native uuid type.

One honest caveat: a time-ordered ID reveals when the record was created. That’s usually fine. If creation time is sensitive, use v4 for that public ID.

Prefixed public IDs

Stripe popularized IDs that say what they are, like cus_... for a customer. You can do the same:

type BookingId = `bk_${string}`;

function toPublicBookingId(uuid: string): BookingId {
  return `bk_${uuid.replaceAll("-", "")}`;
}

When someone pastes bk_0199e0a36f2b... into a support chat, everyone knows it’s a booking. Your API can reject a guest ID passed where a booking ID belongs, and the template literal type catches some mixups at compile time.

Don’t expose IDs you might need to change

Anything that leaves your system is a promise. Once an ID is in a customer’s URL, spreadsheet or integration, you can’t change it. For an existing system with integer keys:

• keep the internal bigint primary key for joins and foreign keys;
• add a separate public_id column (UUIDv7, prefixed when shown) with a unique index;
• only ever expose public_id in APIs, URLs and webhooks.

Now you can merge or renumber internally without breaking anyone. It’s the same idea as in Clean Architecture in Practice: what’s inside is yours to change.

Idempotency keys: IDs the client chooses

An idempotency key is a unique ID the client sends with a request, so a retry is recognized and not done twice. A mobile app sends “pay $50,” the connection drops, and it retries. Without a key, that might charge twice. With one, the server returns the original result.

CREATE TABLE idempotency_keys (
  tenant_id     uuid NOT NULL,
  key           text NOT NULL,
  request_hash  text NOT NULL,
  response      jsonb,
  created_at    timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, key)
);

Insert the key in the same transaction as the work. A primary key conflict means a retry: return the stored response. The same key with a different request body is a client bug, so reject it. Scope keys per tenant and delete old rows after a day or so. Stripe’s API uses an Idempotency-Key header this way. It’s the mirror image of using platform IDs to make webhooks safe in the messy APIs post.

A checklist for your next schema review

None of this is hard. It just has to be decided early.

Time

• Every time column is clearly an instant (timestamptz), a date (date), a local time with a stored IANA zone, or a duration.
• Day ranges use >= start AND < next_start, in a named zone.
• DST gap and overlap behaviour is known and tested for anything scheduled in local time.

Money

• No floats anywhere in the money path, including JSON from clients.
• Integer minor units (bigint) or NUMERIC, always with a currency code.
• Minor-unit digits come from one table, not a hard-coded / 100.
• One rounding function, one documented rule. Splits allocate remainders.

IDs

• No sequential IDs in public URLs or APIs.
• New tables use time-ordered IDs (UUIDv7) rather than random v4 for primary keys.
• Public IDs are separate from internal keys if you might ever need to change them.
• Endpoints with side effects accept an idempotency key.

If you only fix one thing this week, grep your codebase for new Date(" and / 100. You’ll probably find a bug in at least one of them.

Sources: PostgreSQL 18 documentation (Date/Time Types; UUID Functions), RFC 9562 (Universally Unique IDentifiers), ISO 4217 currency list published by SIX (maintenance agency), Node.js 26.0.0 release notes, MDN and Can I Use pages for Temporal, Luxon and date-fns-tz behaviour checked by running the examples, all at the time of writing (October 2026).

Related: Integrating Airbnb, Booking.com and WhatsApp: Lessons From Messy APIs, Multi-Tenant SaaS Database Design, and Background Jobs Done Right.