Skip to main content

Explicit Handling of Absent Data

NULL is not 0. NULL is not "". NULL is not false. NULL is not "no change." Treating any of those as equivalent in DTC-built code is a production bug waiting to happen.

The rule: every comparison, every filter, every conditional that can encounter NULL handles it explicitly. Do not rely on implicit behavior of language-level equality or SQL's tri-valued logic to "do the right thing" — because they very often don't.

The production bug that taught us this

A HaloPSA quote's approvalstate stayed stuck at 0 in our cache for an entire day after the customer approved it. Twelve webhook events arrived with approvalstate=2. Every one of them was silently dropped.

The root cause: the cache upsert used a skip-if-unchanged guard:

ON CONFLICT (id) DO UPDATE SET ...
WHERE cached_quotes.halo_updated_at IS DISTINCT FROM EXCLUDED.halo_updated_at

Seemed correct. IS DISTINCT FROM is the NULL-safe comparison operator. But here's the trap: HaloPSA's quote webhook payload does not include last_update_time as a field (it's there on some payloads, absent on others — inconsistency across endpoints is a whole separate principle). Both cached and incoming halo_updated_at were NULL. NULL IS DISTINCT FROM NULL evaluates to FALSE in Postgres — they're not distinct. So the WHERE clause said "skip this update," and every webhook silently did nothing.

The fix:

WHERE cached_quotes.halo_updated_at IS NULL
   OR EXCLUDED.halo_updated_at IS NULL
   OR cached_quotes.halo_updated_at <> EXCLUDED.halo_updated_at

Explicit: if either side is NULL, we don't have enough information to confidently skip, so update. Only skip when both sides have real timestamps and they match.

Patterns to use

SQL

-- Skip only when we're certain nothing changed
WHERE a IS NULL OR b IS NULL OR a <> b

-- Treat NULL as a sentinel value for comparison
WHERE COALESCE(a, 'epoch'::timestamptz) <> COALESCE(b, NOW())

-- Filter out rows where something is missing
WHERE col IS NOT NULL

Rust / TypeScript

// Good: pattern-match explicitly
match (cached, incoming) {
    (Some(a), Some(b)) if a == b => skip,
    _ => update,
}

// Bad: relies on Option equality that also treats None == None as true
if cached == incoming { skip }

// Good: explicit null check
const changed = a == null || b == null || a !== b;

// Bad: strict equality treats null === null as true
const changed = a !== b;

HTTP query parameters

A missing query parameter is different from an empty one. /search?q= means "search for empty string"; /search means "no filter." Handle both in request parsing explicitly.

Why we do this

  • Silent data loss is the worst kind of bug. It doesn't page anyone; it just erodes user trust over time.
  • Third-party payloads are inconsistent about what they send. Sometimes a field is present, sometimes absent, sometimes explicitly null, sometimes "". Our code shouldn't care which — but only if it handles all of them.
  • Migrations evolve. A column that's always populated today might be nullable tomorrow (new columns default to NULL for existing rows). Explicit handling means that migration doesn't silently break behavior.

When this applies

Every comparison, filter, upsert guard, or conditional in a DTC-built system. Reviewers should flag any comparison that can touch a nullable value and doesn't handle NULL explicitly.

When it does not apply

  • Columns declared NOT NULL at the schema level — though you should still confirm the invariant with a NOT NULL constraint or a CHECK, not just a comment.
  • Request-local values that are constructed inside the handler and provably non-NULL. But at integration boundaries (database, HTTP, message queue), assume NULL is possible.
Back to top