DEV Community

fulfillmentyx
fulfillmentyx

Posted on Edited on

Showing inventory snapshots reproducibly when timestamps tie

By YX Fulfillment

An inventory snapshot may contain multiple updates with the same recorded instant because the source exports timestamps at second precision. Picking the row with the greatest timestamp is incomplete when two rows tie. Define a second sort key tied to the event order the source actually promises. A stable output without a defensible tie policy can still misrepresent which balance won.

Assume a synthetic source whose sequence increases within each SKU and whose timestamps are already normalised as fixed-width UTC strings. Each event carries the on-hand balance after applying its change. The snapshot reader sorts by instant and then by source sequence.

import sqlite3

rows = [
    ("A", "2026-05-12T08:00:00Z", 10, 12),
    ("A", "2026-05-12T08:30:00Z", 11, 9),
    ("A", "2026-05-12T08:30:00Z", 12, 10),
    ("B", "2026-05-12T08:20:00Z", 3, 5),
]

def snapshot(events, cutoff):
    db = sqlite3.connect(":memory:")
    db.execute("""CREATE TABLE events (
        sku TEXT NOT NULL, recorded_at TEXT NOT NULL, seq INTEGER NOT NULL,
        balance INTEGER NOT NULL, PRIMARY KEY (sku, seq)
    )""")
    db.executemany("INSERT INTO events VALUES (?,?,?,?)", events)
    result = db.execute("""
        WITH ranked AS (
            SELECT sku, seq, balance,
                   ROW_NUMBER() OVER (
                       PARTITION BY sku ORDER BY recorded_at DESC, seq DESC
                   ) AS rank
            FROM events WHERE recorded_at <= ?
        )
        SELECT sku, seq, balance FROM ranked WHERE rank=1 ORDER BY sku
    """, (cutoff,)).fetchall()
    db.close()
    return result

at = "2026-05-12T08:30:00Z"
first = snapshot(rows, at)
second = snapshot(list(reversed(rows)), at)
assert first == second == [("A", 12, 10), ("B", 3, 5)]
assert snapshot(rows, "2026-05-12T08:29:59Z") == [("A", 10, 12), ("B", 3, 5)]
print("Run 1:", first)
print("Run 2:", second)
Enter fullscreen mode Exit fullscreen mode

Output from a local run on Python 3.9.6

Run 1: [('A', 12, 10), ('B', 3, 5)]
Run 2: [('A', 12, 10), ('B', 3, 5)]
Enter fullscreen mode Exit fullscreen mode

Reversing insertion order leaves both snapshots identical. Including a cutoff a second earlier proves the ranking considers only events eligible at the requested instant. The warehouse can use the result for reconciliation if its source really does provide the stated sequence and after-event balances.

Random event identifiers sometimes make a useful display tie-breaker, but they cannot establish business order. If timestamps and sequence numbers conflict or come from independent streams, document how to reconcile them before claiming a balance is authoritative. If events contain deltas rather than after-event balances, replay those deltas from a valid baseline instead of selecting one row.

Top comments (0)