Before Replacing a Data-Access Loop, Write the Equivalence Matrix

Changing a repeated database lookup into a batch operation alters the shape of the code immediately. The caller, however, should continue receiving the same answers for the cases the change is meant to preserve.

I handle that review with an equivalence matrix. Each row records the earlier answer, the required replacement answer, whether the case was executed or deliberately excluded, and the evidence used.

I used this approach for a duplicate-preview lookup. The original path queried once per valid row; the replacement queried once for a valid batch. Returned data still needed its own acceptance criteria, written before code approval.

Start with what the caller can observe

I start with the function's public behavior. In this preview, the caller receives one result for each input position. Those returned objects have two fields: a duplicate flag and the stored row ID when a match exists.

The identity rule is part of that behavior. A match requires the same user, LOINC code, full timestamp, and canonical source. If a replacement drops one field or compares a display date where the old path used a full timestamp, it has changed the result even when most fixtures still pass.

This is the matrix used for the recorded change:

I keep the “required observation” column concrete. “Duplicates still work” is too vague to catch a missing existing ID or a shifted output. The comparison needs the whole ordered return value, including the exact existing IDs.

Compare two functions against one fixed state

For the main equivalence check, both implementations see the same committed database contents. Each call receives a fresh copy of the input so an accidental mutation cannot influence the next result.

A small harness can express the decision without knowing how either function performs its lookup:

def observable(preview):
    return [
        (item["is_duplicate"], item["existing"])
        for item in preview
    ]


for case in equivalence_cases:
    restore_database(case.fixed_state)

    old_result = old_duplicate_preview(
        case.user_id, copy.deepcopy(case.inputs)
    )
    restore_database(case.fixed_state)
    new_result = new_duplicate_preview(
        case.user_id, copy.deepcopy(case.inputs)
    )

    assert observable(new_result) == observable(old_result), case.name
    assert observable(new_result) == case.expected, case.name

This generic harness checks both agreement between implementations and the independently written expected result.

I also check lengths and positions directly when the result is a sequence:

assert len(new_result) == len(case.inputs)
for index, expected_item in enumerate(case.expected):
    assert observable(new_result)[index] == expected_item

The explicit index makes failures easier to investigate when invalid rows have been excluded from database work.

Timestamp fixtures

The executed equivalence case uses an aware UTC value such as datetime(2025, 7, 10, 14, 30, tzinfo=UTC) and the ISO form 2025-07-10T10:30:00-04:00. Both represent the same instant. Malformed strings have their own invalid-input case. Date-only values follow the existing midnight conversion, but the recorded offset comparison does not use them.

The expected result follows the parser and database type already in use. This change retained a TIMESTAMPTZ comparison and the existing parsing boundary.

Source handling needs the same separation. Alias normalization is tested on its own, then the lookup matrix receives canonical outputs. I include one case where similar-looking source labels remain distinct so the data-access rewrite cannot hide a normalization change.

Put concurrency in a separate row

Fixed-state equivalence has a precise limit. Several statements under PostgreSQL READ COMMITTED can observe a commit that arrives between rows. A single batched statement uses one statement snapshot. The two paths can therefore disagree if another transaction writes during the preview.

I record concurrency as an intentional difference. For complete non-null identities, the database uniqueness rule remains the final write-time protection, and a preview can become stale after either lookup style. A product requiring one live observation per row would need a different design.

The performed checks cover the rows marked Executed. Concurrency is a separate decision, and nullable identity components stay outside the complete-identity guarantee.

Bound rollback before release

The rollback review covered the state touched by this release. It changed the read function and its tests; no schema migration or existing-data rewrite accompanied it. The previous known-good code release therefore remained usable without a reverse data operation.

I would use a different release plan if the replacement added an index concurrently, changed a uniqueness constraint, backfilled identities, or wrote a new cache. Each of those adds a database state that code rollback alone may not undo.

The complete implementation, including the PostgreSQL batch shape, is in my primary case study. For verification, I keep the matrix independent of that implementation so a future rewrite can be judged against the same observable cases.

For this release, every row marked Executed passed against the recorded fixed database states. The concurrency and nullable-identity boundaries remain explicit in the matrix.