Never Let a Display Toggle Write to the Database

A support ticket arrives. Two users are looking at the same record and reading different numbers. Nothing in the logs. No exception, no failed request, no alert. The row is simply wrong, and nobody can say when it became wrong.

Trace it far enough and you often land on the same small piece of UI: a dropdown that lets the user change the unit a quantity is displayed in. Kilograms or pounds. Therms or MMBtu. Minutes or hours. Someone built it as a convenience, wired it straight to the field, and it writes back.

That control is not a formatting feature. It is a write path into your data, and it is almost never treated like one.

The shape of the bug

Here is the version that gets shipped. It looks harmless in review.

// The user picks a unit from the dropdown.
function onUnitChange(next: Unit) {
  const converted = convert(form.quantity, form.unit, next);
  form.quantity = converted;   // <- the display just wrote to the model
  form.unit = next;
}

Switch to the other unit and back again, and you have round-tripped the value through two divisions. With a non-terminating ratio you do not get your original number back. Do it four times and the drift is visible. Do it once on a record nobody looks at for a month and it is invisible, permanent, and indistinguishable from a legitimate edit.

The second failure is worse, because it needs no arithmetic error at all. A user switches the dropdown to read a value in a different unit, does not change anything, and hits save out of habit. The form submits the converted number against the original unit. The record is now off by whatever the conversion factor is.

Both of these are the same mistake: the presentation layer was given permission to write.

Rule 1: one canonical unit in storage

Pick a canonical unit per quantity type and store only that. The database does not know about user preferences, locales, or dropdowns.

CREATE TABLE measurement (
  id            bigint PRIMARY KEY,
  -- always stored in grams; the column name says so
  mass_g        numeric(20, 6) NOT NULL,
  recorded_at   timestamptz    NOT NULL
);

Put the unit in the column name. mass_g, duration_ms, volume_ml. A column called quantity with a sibling called unit invites every consumer to do its own conversion, and they will not all agree. A column called mass_g cannot be misread by the next person, and it cannot be misread by a reporting query written by someone who never read your code.

Conversion happens on the way out and on the way in at the edge, and nowhere else.

Rule 2: the converted value never travels back

Make display units a view concern that physically cannot reach the write path. The simplest way is to keep the canonical value in the model and derive the displayed one:

type Measurement = { massG: number };     // canonical, the only thing persisted

function display(m: Measurement, unit: Unit): string {
  return format(fromGrams(m.massG, unit), unit);
}

function onUnitChange(next: Unit) {
  viewState.unit = next;                  // view state only
  // model untouched
}

function onUserEdit(raw: string, unit: Unit) {
  model.massG = toGrams(parseDecimal(raw), unit);   // convert in, once
}

Two functions, toGrams and fromGrams, and a model that has no unit field at all. If the model cannot express "quantity in pounds", nothing downstream can accidentally persist one.

When the stored unit genuinely has to vary - because the source system sends mixed units and you need the original for audit - store both: the canonical value you compute with, and the as-received value and unit you never compute with. Never one field that means different things on different rows.

Rule 3: inexact types are the other half of the same bug

Unit conversion multiplies and divides. If the column is a binary floating point type, every one of those operations is an opportunity to lose the exact value, and the loss compounds silently.

The PostgreSQL documentation is blunt about this. real and double precision are listed as variable-precision, inexact, with 6 and 15 decimal digits of precision respectively. numeric is exact, with user-specified precision, and the docs say it "is especially recommended for storing monetary amounts and other quantities where exactness is required", with calculations yielding exact results where possible for addition, subtraction and multiplication. The same docs note that numeric calculations are slower than the floating point types - that is the trade, and for a quantity somebody will reconcile later it is an easy one.

-- wrong for anything that gets reconciled
volume double precision

-- right
volume_m3 numeric(20, 6)

The application side matters just as much. A JavaScript number is a double. If your API serialises a numeric column into JSON and the client parses it as a number, you have just thrown away the exactness you paid for in the database. Carry it as a string to the edge and parse it with a decimal library, or do the arithmetic server-side and send the client a formatted result it only displays.

And define rounding once, in one module, with the rounding mode written down. Two services that round differently will produce two totals, and the argument about which one is correct will cost more than the feature did.

Rule 4: a quantity is not always a single number

This is the modelling mistake that is cheap on day one and brutal to retrofit.

Sometimes the thing a user calls "the quantity" is a shape spread across a window, not a scalar. A monthly figure that is really a daily rate across a delivery period. A subscription allowance that resets. A forecast with a curve.

// day one, and it leaks into every report before anyone notices
type Line = { quantityG: number };

// what it actually is
type Line = {
  from: PlainDate;
  to: PlainDate;
  dailyQuantityG: number;     // or an explicit per-day profile
};

If you model it as a scalar, the first question that asks for a daily breakdown forces a migration through every report, export and integration that has already consumed the scalar. Aggregation over a shape is a different operation from summing a column, and you want to discover that before the data is in production.

Rule 5: one concept, three structures

The same trap in a different costume. You have a field that can be set three ways - a fixed value, a value derived from an index, or an offset against a reference. The tempting schema is one table, a type column, and a handful of nullable columns.

-- the shortcut
CREATE TABLE price (
  kind              text NOT NULL,      -- 'fixed' | 'indexed' | 'differential'
  fixed_amount      numeric,            -- null unless kind = 'fixed'
  index_code        text,               -- null unless kind = 'indexed'
  index_multiplier  numeric,            -- null unless ...
  base_reference    text,
  offset_amount     numeric
);

It works until the first reconciliation. After that, every query that has to understand the field carries a branch, the branches get copied, and two of them disagree. Nothing enforces that fixed_amount is present exactly when kind = 'fixed'.

Model each variant as its own structure and put the branching in one mapping layer:

type Price =
  | { kind: 'fixed'; amount: Decimal }
  | { kind: 'indexed'; indexCode: string; multiplier: Decimal }
  | { kind: 'differential'; baseRef: string; offset: Decimal };

A discriminated union makes the illegal states unrepresentable in the code, and the exhaustiveness check in your compiler tells you every place that needs updating when a fourth variant arrives. In the database, either use separate tables with a shared parent or add check constraints that make the nullability rules explicit:

ALTER TABLE price ADD CONSTRAINT price_shape CHECK (
  (kind = 'fixed'        AND fixed_amount IS NOT NULL AND index_code IS NULL)
  OR (kind = 'indexed'   AND index_code  IS NOT NULL AND fixed_amount IS NULL)
  OR (kind = 'differential' AND base_reference IS NOT NULL)
);

Rule 6: amend, do not overwrite

Once a record has been acted on by somebody else, an in-place UPDATE destroys the only evidence of what they acted on. "What did this say on the 14th" is a question that gets asked eventually, and the honest answer should not be "we cannot tell you".

Append amendments instead, and derive the current state:

CREATE TABLE measurement_amendment (
  id              bigserial PRIMARY KEY,
  measurement_id  bigint      NOT NULL REFERENCES measurement(id),
  before          jsonb       NOT NULL,
  after           jsonb       NOT NULL,
  amended_by      text        NOT NULL,
  amended_at      timestamptz NOT NULL DEFAULT now()
);

Three rules keep this honest. Every amendment carries who and when, not just what. Deletions are reversals - a new row that negates the previous state - not DELETE statements, because a deleted row and a row that was never written look identical afterwards. And the reconstruction path gets a test, because an audit trail nobody has ever replayed is a hope, not a guarantee.

Rule 7: concurrency, surfaced rather than swallowed

Two people editing the same record is normal, not an edge case. The default behaviour of a naive update is last-write-wins, and it is silent.

UPDATE measurement
   SET mass_g = $1, version = version + 1
 WHERE id = $2
   AND version = $3;         -- the version the editor loaded
-- 0 rows affected means somebody else got there first

Check the affected row count and tell the user. A conflict dialog is annoying. A silently discarded edit is a support ticket three weeks later with no evidence attached.

Rule 8: bulk import bypasses your form

Every system that reaches a certain size grows a spreadsheet importer, and the importer does not go through your UI. Validation that lives only in the form is not validation.

Three things make an importer safe. The rules live in the domain layer, so the form and the importer call the same code. There is a preview stage that shows exactly what will be created, changed and skipped, before anything is written. And the commit is all-or-nothing, in one transaction, because a half-applied import of a thousand rows is worse than a rejected one - a rejection is obvious, a partial apply looks like success.

One calculation, two readers

The last one is not really about data types at all.

If the total on the screen and the total in the export disagree by a rounding step, users stop trusting the whole system. Downtime is forgivable because it is understood. A number that changes depending on where you read it is not.

So the aggregate is computed in one place, called by both the screen and the export, with the rounding rules defined once. Not two implementations that are "basically the same". One.

Ship checklist

1. Every quantity column names its unit (mass_g, not quantity plus a unit column).
2. The write model has no display-unit field at all.
3. Conversion happens exactly twice: parse in at the edge, format out at the edge.
4. Money and reconciled quantities use an exact decimal type, never float or double precision.
5. Decimal values cross the API as strings, not JSON numbers.
6. Rounding mode and precision are defined in one module and imported everywhere.
7. Quantities that are really shapes over a window are modelled as shapes from day one.
8. Variant concepts are discriminated unions in code and check constraints in the schema.
9. Edits append amendments with actor and timestamp; deletes are reversals.
10. Updates carry a version and surface conflicts to the user instead of resolving them.
11. The importer shares the domain validation, previews its effect, and commits atomically.

Most of these cost a morning at the start of a project. All of them cost a migration and a trust problem if you add them after the data is live.

We build and maintain data-entry and data-management systems where the numbers have to reconcile afterwards. The patterns above are the ones we have found worth enforcing from the first screen rather than the first incident.