The Two Records Build Standard

1. What this standard covers

Any pair of systems that each hold part of the truth about the same thing and are kept by different people. The job system and the ledger about a job. The bank feed and the invoices about a payment. The roster and the timesheets about a day. The register and the drive about a document. If two records share a key and can disagree, this applies.

Matching a supplier invoice to a purchase order before it is paid is the Invoice Check standard and is out of scope here. That one is a gate on money leaving. This one is a report on two records that should already agree. The accounts admin paper is the argument beside it, and the rule that a measure has one definition computed once comes from the Multi-Site Conformance standard.

The failure this prevents is specific. Not the mismatch; every business has those. The mismatch that was fixed by a machine, or by a person in a hurry, without either value being kept, so that the record now agrees and nobody can say why it ever did not.

2. The pair contract

Before a run reads a record, a person writes down four things. Which two sources. Which field is the shared key, and it is an id, never a name. Which source is master for each field that can differ. What tolerance each field carries, in a value, for rounding and timing. A run with no contract has to guess all four, and the guess is the part that changes between runs.

The master per field is the decision most businesses have never made explicitly. The ledger is master for the amount; the job system is master for the address; the bank feed is master for the date it landed. Write it once. Most of the standard is that sentence.

3. The objects, and what is in them

Five objects. The contract and the correction are the two most reconciliations leave out, because a spreadsheet with two columns and a highlighter appears to work without either.

PairContract
  - id, source_a, source_b, key (an id field both sources hold)
  - master_by_field { field: a | b }
  - tolerance_by_field { field: value }
  - threshold { amount, mismatch_rate }, schedule, owner

Run
  - id, at, contract_id, joined_on
  - matched, within_tolerance, mismatched, orphaned, corrected
  - published_at                   # present on an all-clear too

Difference
  - id, run_id, key, field
  - value_a, value_b, master_value
  - status (open | within_tolerance | accepted | rejected)

Orphan
  - run_id, key, source, reason, owner (a person)

Correction
  - difference_id, proposed_value, target (a | b)
  - accepted_by (a person, null until they do), accepted_at
  - written_id (the target's own id for the write)
  - history { from_a, from_b }     # both originals, kept forever

A Correction with a written_id and no accepted_by is the defect this standard exists to make unrepresentable. A Run that joined on a name field is the other, and neither should be writable.

4. The checks, in order

The order is the claim. The contract is read before a record, the join is on the id before any field is compared, and the difference exists as an object before anyone thinks about a fix.

1. A pair is declared in a written contract, never guessed. Every reconciliation names its two sources, the shared key that joins them, and which source is master for each field, in a contract a person wrote. A run with no declared master fails.
2. Records are joined on the shared id, never on a name or an amount. The join key is the id the contract names. Two records with the same customer name, or the same amount, are not a pair unless they share that id.
3. A matched pair produces nothing; a mismatch produces a Difference, never a corrected record. Agreement is counted and not shown. A disagreement becomes a Difference carrying the field, both values and the master's value. No record in either source is changed by the comparison.
4. A correction is proposed with both values and written only after a named person accepts it. The run proposes the master's value for the other source. The write happens once, after a named person accepts the proposal, and never before. A write with no acceptance fails.
5. A record with no partner is an orphan with a reason and an owner, not a silent gap. A record in one source with no record under the same id in the other is raised as an orphan naming the source, a reason and the person who owns it. It is counted on the run and never dropped.
6. Rounding and timing tolerances are declared per field with a value, and never assumed. A tolerance is a number a person wrote against a field in the contract. A difference inside it is recorded as within_tolerance, still an object. A field with no declared tolerance has none, so any difference on it is open.
7. The run publishes on schedule with its counts, including the all-clear. Every scheduled run publishes a count of matched, within tolerance, mismatched, orphaned and corrected, even when every number but matched is zero. Its absence is never the signal.
8. A difference over the declared threshold, or too many mismatches, reaches a named person that run. The contract declares an amount and a rate. A single difference over the amount, or a run whose open mismatches exceed the rate, goes to the named person in the same run, not the next one.
9. Every accepted correction keeps both original values and the person who accepted. The Difference keeps both values as they were. The Correction records the proposed value, who accepted, when, and the id the write returned. A fix that overwrites the history fails.
10. For any shared id, the runs, differences and corrections are retrievable. When somebody asks why the ledger says what it says about a job, the record by that id shows every run that raised a difference on it, each difference with both values, and each correction with who accepted it.
11. It survives the builder. Someone other than the builder can explain what it does, and it runs on an account the business owns.
12. The pass test passes. On the business's real sources: one seeded mismatch found and shown side by side, one orphan raised, one correction accepted by a person and written once, and an all-clear published the following run.

5. The four queues

The all-clear publishes. A run that says nothing when everything matched is indistinguishable from a run that did not happen, and the week the schedule silently stops is the week it mattered.

6. What stays with a person

• ✓Accepting every correction, with their name on it.
• ✓Declaring which source is master for each field, once, in the contract.
• ✓Setting a tolerance, and changing one.
• ✓Deciding an orphan is real: a record that should exist in the other source, or one that should not exist at all.

7. Provider: what to stub, and what not to hand-roll

One step not to hand-roll: the accepted correction's write into the target source, with both original values kept. Everything else a coding agent builds against the stub in an afternoon.

provider:
  stub:       local.mock_pair
  production: none                       # no hosted step; runs on your own systems

stays local                          # yours, and the standard
  - the pair contract, in a file with a date on it
  - the join, the comparison, the tolerance
  - the queue a person reads

the live step
  - the write of an accepted correction into the ledger or the job
    system, keyed so it lands once, with history kept. Runs on your
    own systems; there is no hosted product for it.

A vendor selling reconciliation is usually selling the comparison, which is the easy half, and is silent about who accepted the last fix and where the old value went. Ask them that first.

8. Conformance checklist

Hold a DIY build or a vendor to this. If a box is empty, it is not in production.

• ✓1. A pair is declared in a written contract, never guessed. Every reconciliation names its two sources, the shared key that joins them, and which source is master for each field, in a contract a person wrote. A run with no declared master fails.
• ✓2. Records are joined on the shared id, never on a name or an amount. The join key is the id the contract names. Two records with the same customer name, or the same amount, are not a pair unless they share that id.
• ✓3. A matched pair produces nothing; a mismatch produces a Difference, never a corrected record. Agreement is counted and not shown. A disagreement becomes a Difference carrying the field, both values and the master's value. No record in either source is changed by the comparison.
• ✓4. A correction is proposed with both values and written only after a named person accepts it. The run proposes the master's value for the other source. The write happens once, after a named person accepts the proposal, and never before. A write with no acceptance fails.
• ✓5. A record with no partner is an orphan with a reason and an owner, not a silent gap. A record in one source with no record under the same id in the other is raised as an orphan naming the source, a reason and the person who owns it. It is counted on the run and never dropped.
• ✓6. Rounding and timing tolerances are declared per field with a value, and never assumed. A tolerance is a number a person wrote against a field in the contract. A difference inside it is recorded as within_tolerance, still an object. A field with no declared tolerance has none, so any difference on it is open.
• ✓7. The run publishes on schedule with its counts, including the all-clear. Every scheduled run publishes a count of matched, within tolerance, mismatched, orphaned and corrected, even when every number but matched is zero. Its absence is never the signal.
• ✓8. A difference over the declared threshold, or too many mismatches, reaches a named person that run. The contract declares an amount and a rate. A single difference over the amount, or a run whose open mismatches exceed the rate, goes to the named person in the same run, not the next one.
• ✓9. Every accepted correction keeps both original values and the person who accepted. The Difference keeps both values as they were. The Correction records the proposed value, who accepted, when, and the id the write returned. A fix that overwrites the history fails.
• ✓10. For any shared id, the runs, differences and corrections are retrievable. When somebody asks why the ledger says what it says about a job, the record by that id shows every run that raised a difference on it, each difference with both values, and each correction with who accepted it.
• ✓11. It survives the builder. Someone other than the builder can explain what it does, and it runs on an account the business owns.
• ✓12. The pass test passes. On the business's real sources: one seeded mismatch found and shown side by side, one orphan raised, one correction accepted by a person and written once, and an all-clear published the following run.