# A wallet opened between reconciliation cuts gets flagged: zero-fill or skip the missing snapshot?

> When a new wallet has no snapshot, NULL can cause a reconciliation mismatch. This PostgreSQL example sets out the assumptions behind zero-filling.

Source: https://sheng.page/en/posts/reconciliation-new-wallet-without-prior-snapshot/ · Published: 2026-11-10 · Author: Sheng

At each fixed cut, this example adds the interval's ledger movements to each wallet's previous balance and compares the result with the current snapshot. When I worked on a wallet service, wallets opened between cuts were wrongly flagged for having no previous snapshot. The fix was `coalesce`. The SQL below reproduces that kind of mismatch. The original query is omitted. The `LEFT JOIN` supplies NULL for the missing snapshot, adding to NULL yields NULL, and the test data produces a mismatch for the operator and currency. Using `coalesce` to replace NULL with 0 in both terms restores an ok status.

Excluding wallets without a previous snapshot misses a discrepancy in this PostgreSQL 16.15 example. Zero-filling finds it. A meaningful result assumes that the previous balance can be treated as 0 and that the interval's ledger is complete. The missing snapshot still needs explaining.

## The calculation

That service recorded one result per cut and (operator, currency) pair. This example reports ok or mismatch. It calculates each wallet's expected balance as follows:

```text
expected = balance at previous cut + sum of ledger movements in (previous cut, this cut]
actual   = balance at this cut
```

Expected and actual are summed per operator and currency, then compared. Offsetting discrepancies can leave totals equal even when individual wallets do not balance.

The test data has three wallets, all under `op-a` in `TWD`. Cut 1 is at 00:00 on 9 November 2026, and cut 2 is at 01:00. `w1` already existed and has two movements in the window; `w2` already existed and has none; `w3` was opened at 00:30, so it has no snapshot at cut 1.

```sql
CREATE TABLE cuts (id int PRIMARY KEY, cut_at timestamptz NOT NULL);
CREATE TABLE wallets (id text PRIMARY KEY, operator text NOT NULL, currency text NOT NULL, created_at timestamptz NOT NULL);
CREATE TABLE balance_snapshots (cut_id int REFERENCES cuts, wallet_id text REFERENCES wallets, balance numeric NOT NULL, PRIMARY KEY (cut_id, wallet_id));
CREATE TABLE ledger (wallet_id text REFERENCES wallets, amount numeric NOT NULL, created_at timestamptz NOT NULL);

INSERT INTO cuts VALUES (1, '2026-11-09 00:00+08'), (2, '2026-11-09 01:00+08');
INSERT INTO wallets VALUES
  ('w1', 'op-a', 'TWD', '2026-11-01 12:00+08'),  -- existed before the cut, has transactions in the window
  ('w2', 'op-a', 'TWD', '2026-11-01 12:00+08'),  -- existed before the cut, no transactions in the window
  ('w3', 'op-a', 'TWD', '2026-11-09 00:30+08');  -- opened between the two cuts
INSERT INTO balance_snapshots VALUES
  (1, 'w1', 100), (1, 'w2', 50),
  (2, 'w1', 120), (2, 'w2', 50), (2, 'w3', 180);
INSERT INTO ledger VALUES
  ('w1', -30, '2026-11-09 00:10+08'), ('w1', 50, '2026-11-09 00:40+08'),
  ('w3', 200, '2026-11-09 00:31+08'), ('w3', -20, '2026-11-09 00:45+08');
```

Under the assumptions in the example, every wallet balances: `w1` is 100 - 30 + 50 = 120, `w2` stays at 50, and `w3` starts at 0 and ends at 200 - 20 = 180.

## Adding them up: balances correct, status mismatch

The first version joins the previous snapshot and movement totals with `LEFT JOIN`:

```sql
-- Version 1: add the previous snapshot and the window's movements as they are
WITH delta AS (
  SELECT l.wallet_id, sum(l.amount) AS amount
  FROM ledger l
  WHERE l.created_at >  (SELECT cut_at FROM cuts WHERE id = 1)
    AND l.created_at <= (SELECT cut_at FROM cuts WHERE id = 2)
  GROUP BY l.wallet_id
), per_wallet AS (
  SELECT w.operator, w.currency, w.id,
         prev.balance + d.amount AS expected,
         cur.balance             AS actual
  FROM wallets w
  JOIN balance_snapshots cur       ON cur.wallet_id = w.id AND cur.cut_id = 2
  LEFT JOIN balance_snapshots prev ON prev.wallet_id = w.id AND prev.cut_id = 1
  LEFT JOIN delta d                ON d.wallet_id = w.id
)
SELECT operator, currency, sum(expected) AS expected, sum(actual) AS actual,
       CASE WHEN sum(expected) = sum(actual) THEN 'ok' ELSE 'mismatch' END AS status
FROM per_wallet GROUP BY operator, currency;
```

```text
 operator | currency | expected | actual |  status  
----------+----------+----------+--------+----------
 op-a     | TWD      |      120 |    350 | mismatch
(1 row)
```

Expected comes to 120. The same joins and interval show the breakdown per wallet:

```sql
-- How expected is built, wallet by wallet
SELECT w.id, prev.balance AS prev, d.amount AS delta,
       prev.balance + d.amount AS expected, cur.balance AS actual
FROM wallets w
JOIN balance_snapshots cur       ON cur.wallet_id = w.id AND cur.cut_id = 2
LEFT JOIN balance_snapshots prev ON prev.wallet_id = w.id AND prev.cut_id = 1
LEFT JOIN (
  SELECT wallet_id, sum(amount) AS amount FROM ledger
  WHERE created_at >  (SELECT cut_at FROM cuts WHERE id = 1)
    AND created_at <= (SELECT cut_at FROM cuts WHERE id = 2)
  GROUP BY wallet_id
) d ON d.wallet_id = w.id
ORDER BY w.id;
```

```text
 id | prev | delta | expected | actual 
----+------+-------+----------+--------
 w1 |  100 |    20 |      120 |    120
 w2 |   50 |       |          |     50
 w3 |      |   180 |          |    180
(3 rows)
```

`w3` has no snapshot at cut 1, so `prev` is NULL. `w2` had no movements, so the `delta` subquery has no row for it and the `LEFT JOIN` supplies NULL. Adding either missing value leaves expected as NULL. `sum` only adds non-null inputs, totalling 120 from `w1` as against 350 across all three.

Of these two NULL sources, the new wallet's missing snapshot is the one I actually ran into. The NULL for the wallet with no movements comes from the test data. The PostgreSQL documentation notes that some aggregates return NULL when there are no input rows (see Further reading). Here, the grouped query produces no row for that wallet, and the outer join supplies NULL.

## Zero-fill

Use `coalesce` for both nullable terms:

```sql
-- Version 2: both NULLs become 0
WITH delta AS (
  SELECT wallet_id, sum(amount) AS amount FROM ledger
  WHERE created_at >  (SELECT cut_at FROM cuts WHERE id = 1)
    AND created_at <= (SELECT cut_at FROM cuts WHERE id = 2)
  GROUP BY wallet_id
), per_wallet AS (
  SELECT w.operator, w.currency, w.id,
         coalesce(prev.balance, 0) + coalesce(d.amount, 0) AS expected,
         cur.balance AS actual
  FROM wallets w
  JOIN balance_snapshots cur       ON cur.wallet_id = w.id AND cur.cut_id = 2
  LEFT JOIN balance_snapshots prev ON prev.wallet_id = w.id AND prev.cut_id = 1
  LEFT JOIN delta d                ON d.wallet_id = w.id
)
SELECT operator, currency, sum(expected) AS expected, sum(actual) AS actual,
       CASE WHEN sum(expected) = sum(actual) THEN 'ok' ELSE 'mismatch' END AS status
FROM per_wallet GROUP BY operator, currency;
```

```text
 operator | currency | expected | actual | status 
----------+----------+----------+--------+--------
 op-a     | TWD      |      350 |    350 | ok
(1 row)
```

For a wallet opened after the previous cut, filling its missing snapshot assumes both a balance of 0 at that cut and a complete ledger of subsequent changes in the interval. A missing snapshot establishes neither assumption. Treating a missing movement total as no balance change also requires a complete ledger.

## NULL can hide a per-wallet discrepancy

A per-wallet check can leave a wallet out without an explicit instruction to skip it. If expected is NULL before filling, `WHERE expected <> actual` excludes that wallet. An ordinary comparison with NULL yields NULL, and `WHERE` keeps only rows where the condition is true. The query puts the unfilled balance in a separate column so both versions can be compared.

Set `w3`'s current snapshot to 999 to simulate a new wallet that does not balance, then try both rules:

```sql
-- Per-wallet check: w3's snapshot is set to 999; what does each rule catch?
BEGIN;
UPDATE balance_snapshots SET balance = 999 WHERE cut_id = 2 AND wallet_id = 'w3';
CREATE TEMP VIEW per_wallet AS
SELECT w.id,
       prev.balance + d.amount                             AS raw_expected,
       coalesce(prev.balance, 0) + coalesce(d.amount, 0)   AS expected,
       cur.balance                                         AS actual
FROM wallets w
JOIN balance_snapshots cur       ON cur.wallet_id = w.id AND cur.cut_id = 2
LEFT JOIN balance_snapshots prev ON prev.wallet_id = w.id AND prev.cut_id = 1
LEFT JOIN (
  SELECT wallet_id, sum(amount) AS amount FROM ledger
  WHERE created_at >  (SELECT cut_at FROM cuts WHERE id = 1)
    AND created_at <= (SELECT cut_at FROM cuts WHERE id = 2)
  GROUP BY wallet_id
) d ON d.wallet_id = w.id;
SELECT 'raw <>' AS rule, id FROM per_wallet WHERE raw_expected <> actual
UNION ALL
SELECT 'coalesce IS DISTINCT FROM', id FROM per_wallet WHERE expected IS DISTINCT FROM actual;
ROLLBACK;
```

```text
           rule            | id 
---------------------------+----
 coalesce IS DISTINCT FROM | w3
(1 row)
```

The raw `<>` returns no rows and so misses `w3`'s excess of 819 over its expected balance. If later runs examine only their own intervals, with no rerun or separate check, the first stretch of activity, including the first deposit, may go unchecked.

Zero-filling and `IS DISTINCT FROM` find `w3`. Both terms are zero-filled and the current snapshot cannot be NULL, so `<>` and `IS DISTINCT FROM` give the same result. Keeping `IS DISTINCT FROM` preserves the comparison if actual is later allowed to be NULL (not demonstrated here). The join excludes wallets without a current snapshot. Changing the comparison cannot recover those rows. Missing current snapshots are untested here.

## A missing snapshot is not the same as a new wallet

Zero-filling requires establishing that the wallet did not exist at the previous cut. For an existing wallet, it assigns a previous balance of 0 without evidence. Delete `w2`'s cut-1 snapshot and run again:

```sql
-- w2 existed all along but its previous snapshot is missing; rerun the zero-filled version and label where prev came from
BEGIN;
DELETE FROM balance_snapshots WHERE cut_id = 1 AND wallet_id = 'w2';
SELECT w.id,
       CASE
         WHEN prev.wallet_id IS NOT NULL THEN 'has_prev'
         WHEN w.created_at > c1.cut_at   THEN 'opened_after_cut'
         ELSE 'snapshot_missing'
       END AS prev_source,
       coalesce(prev.balance, 0) + coalesce(d.amount, 0) AS expected,
       cur.balance AS actual
FROM wallets w
CROSS JOIN (SELECT cut_at FROM cuts WHERE id = 1) c1
JOIN balance_snapshots cur       ON cur.wallet_id = w.id AND cur.cut_id = 2
LEFT JOIN balance_snapshots prev ON prev.wallet_id = w.id AND prev.cut_id = 1
LEFT JOIN (
  SELECT wallet_id, sum(amount) AS amount FROM ledger
  WHERE created_at >  (SELECT cut_at FROM cuts WHERE id = 1)
    AND created_at <= (SELECT cut_at FROM cuts WHERE id = 2)
  GROUP BY wallet_id
) d ON d.wallet_id = w.id
ORDER BY w.id;
ROLLBACK;
```

```text
 id |   prev_source    | expected | actual 
----+------------------+----------+--------
 w1 | has_prev         |      120 |    120
 w2 | snapshot_missing |        0 |     50
 w3 | opened_after_cut |      180 |    180
(3 rows)
```

Here, `w2` still has a discrepancy after zero-filling. The amounts imply that the earlier aggregate query would report a mismatch. Balances could happen to match after filling, so `snapshot_missing` still needs investigation. The snapshot was deliberately deleted here. A live system would need to establish why it is missing. Comparing `created_at` with the previous cut distinguishes wallets opened afterwards from existing wallets with missing snapshots. Wallets opened afterwards still need to meet the filling assumptions. Existing wallets with missing snapshots should be labelled for investigation.

This requires trustworthy `created_at` values comparable with cut times. The query classifies a wallet as newly opened only if its creation time is later than the previous cut. An exact match without a previous snapshot is classified as a missing snapshot. Clock differences between services could misclassify wallets near a cut.

The query only labels the source. It still zero-fills every gap, and subsequent handling falls outside this example.

Offsetting wallet discrepancies, exact cut boundaries and clock differences are untested. The NULL for wallets with no movements comes from the test data. This post does not check whether the original job had it as well. Rows that the per-wallet query misses only surface if the system has a separate check that catches them.

## Notes

- The example adds interval movements to the previous snapshot. For new wallets without that snapshot, the `LEFT JOIN` returns NULL, leaving expected as NULL.
- `sum` ignores NULL inputs, making expected too low and producing a mismatch here. Wallets without interval movements also produce NULL.
- Filling a missing snapshot assumes that the wallet did not exist at the previous cut, that its balance can be treated as zero, and that the ledger covers subsequent changes. Under those assumptions, the data reconciles as ok.
- The per-wallet `<>` check misses the new wallet's discrepancy. Zero-filling and `IS DISTINCT FROM` find it.
- The existing wallet with a missing snapshot has a discrepancy here. If `created_at` is reliable and comparable with cut times, it can help flag missing snapshots for investigation.

## Further reading

- PostgreSQL 16 documentation, [Aggregate Functions](https://www.postgresql.org/docs/16/functions-aggregate.html): `sum` adds only non-null inputs. Apart from `count`, the general-purpose aggregates listed there return NULL when there are no input rows. `coalesce` can supply a replacement value when appropriate.
- PostgreSQL 16 documentation, [Comparison Functions and Operators](https://www.postgresql.org/docs/16/functions-comparison.html): ordinary comparison operators yield NULL when either input is NULL. `IS DISTINCT FROM` treats NULL as a comparable value.
- PostgreSQL 16 documentation, [Conditional Expressions](https://www.postgresql.org/docs/16/functions-conditional.html): `COALESCE` returns its first non-null argument.

> Ideas and technical judgement by Sheng; drafted with Claude · Coordinated and drafted by Claude Fable, documentation lookup and tone by Grok (grok-4.7), second review by Codex (gpt-6-astra); examples run on Linux with PostgreSQL 16.15, using made-up test data.