Reconcile entitlement, rather than payment status alone

The totals agree while the people do not

Five eligible members, five active access records: the totals agree. B has no access and E has two records. The extra one has neatly concealed the missing one without helping either person. To find the right repair, this membership example has to compare identities and the actual entitlement rule, including trials, refund grace and the time the report describes. Payment status alone would give some of the other members the wrong answer too.

An authorised trial can provide access before payment, and a refund-grace rule can preserve it afterwards. A payment confirmed after the cutoff belongs to the next observation. Those are not awkward exceptions to dismiss until paid and access counts agree. They are part of what entitled means for this service. A reconciliation that omits them could successfully remove a discrepancy by revoking access the policy expressly permits.

The original example uses six synthetic members and an invented membership policy. It is not advice about what refund or trial terms a business should offer. It shows how a reconciliation becomes meaningful when the expected state comes from an explicit rule, the observation time is declared and the repair remains separate from finding a discrepancy.

State the expected rule before reading the exceptions

At 4 December 2025, 12:00 UTC, this teaching policy grants access if the current term is paid and confirmed by the cutoff, an explicit trial has not expired, or an explicit refund-grace period has not expired. The paid state here refers to the relevant current term, not any payment the person has ever made. A production rule would need the real term boundaries and policy conditions.

Member A has a qualifying payment and one access record. Member B has the same eligibility but no access. Member C is in a trial until 7 December and correctly has one record without payment. Member D was refunded but has an authorised grace period until 9 December and also retains one record. Those four cases already show why a raw comparison of payment status with an access flag would propose both a useful grant and an incorrect revocation.

Member E is eligible but has two active access records. A count-only summary can let this extra record compensate numerically for B’s missing one. Member F’s payment is confirmed at 12:01, after the chosen cutoff; the no-access state at 12:00 is not a discrepancy under this report. A subsequent run should consider the later evidence. Excluding it now is a statement about the observation window, not a decision to ignore the person indefinitely.

Write the policy as an inspectable function or query and keep its version with the run. In the downloadable example, B and E are the discrepancy set, while the trial, grace period and cutoff explain why the other records need no repair. Keep those reasons in the output too. Without them, a later maintainer could remove the trial branch to make the query simpler and turn C’s legitimate access into a new alert. The program creates proposals, leaving grants and removals to their reviewed action paths.

Compare a defined population at a defined time

A stable member identifier is the join key; a display name or mutable email address should not silently determine which payment and access records belong together. Include tenant and product scope where the real system requires them. Otherwise the reconciliation can be perfectly consistent about the wrong pair of records. Duplicate access bindings also need their own identities so the investigation can distinguish two representations from two genuine permissions. Build the comparison population from the relevant membership and access records rather than an inner join that keeps only matched rows. A missing grant is precisely a relationship that would disappear from such a join. Orphan access records deserve visibility too, even when no corresponding paid member appears in the chosen billing extract.

The source extracts must describe comparable times. A payment query made after a new confirmation and an access query made just before it can manufacture an apparent missing grant. PostgreSQL’s isolation documentation explains how snapshots differ between statements and transactions under different levels. [1] That can help inside one database; it does not create a shared atomic snapshot across an external billing service and a separate application.

Across separate systems, retain the extract times and the relevant effective or event times, then agree how unsettled changes are treated. Without a defensible common cutoff, a missing grant may need another observation before it needs a mutation. Waiting five minutes is not a universal cure for that uncertainty. The provider’s delivery contract and the behaviour actually observed have to support the conclusion; otherwise the apparent fix could simply be compensating for reading the two systems at different moments.

The fixture’s event times are sufficient for its six teaching cases; it is not a general temporal database reconstructing every historical state. A real retrospective comparison needs evidence for later refunds, changed terms and superseded trials, not merely today’s status with an old date placed in the report title. If that history is unavailable, narrow the report’s claim and address the evidence gap.

Let the discrepancy tell you which investigation comes next

For B, the proposed action is a grant, but first establish why access is absent. Was a delivery delayed, a local operation rejected, a grant attempted with an unknown outcome or the person intentionally suspended under another applicable rule? The expected policy result identifies a relationship to investigate. It does not grant the reconciliation tool authority to override every other control in the access system.

For E, two active records require investigation of the bindings before choosing a deletion. They may involve different products or external identities, or one may preserve the valid audit relationship. Establish which are redundant under the real model and record the disposition. The count identifies an inconsistency with this fixture’s one-record rule, but does not identify the safe row to remove.

C and D are useful controls because the unusual-looking access is correct. Record the trial and grace reasons in the policy so the same members do not appear as errors every run. A legitimate manual exception also needs its authority and expiry represented, rather than a permanent instruction to ignore the alert. Otherwise the team learns that this noisy category is harmless just as a genuinely unjustified access record arrives among it.

For F, retain the later confirmation for the next eligible comparison. An output reading “no discrepancy at cutoff” should not be shortened in a customer-facing view to “not entitled”. The former describes one run’s evidence; the latter can sound like a current final decision. Good wording prevents the reporting boundary from becoming an accidental access policy.

A repair plan can become stale before execution

Suppose B’s missing grant is reviewed at 12:10, but another applicable suspension is recorded at 12:12 before the repair runs. A plan based on the 12:00 comparison must not blindly recreate access. Bind the proposed action to the evidence and expected current revision, then check current permissions and policy at the mutation boundary. PostgreSQL’s conditional UPDATE illustrates how a local write can require the state it was reviewed against. [2]

Keep the repair outcome linked to the discrepancy and original operation where relevant. If the destination supports an appropriate idempotent operation, use it consistently; if an outcome becomes uncertain, reconcile rather than assuming the attempt failed. A report rerun should recognise a completed correction instead of generating a fresh unrelated grant every time the same historical mismatch appears.

After a repair, inspect the actual member, product and permission. A lower discrepancy count may mean the relationship was corrected, or that a query no longer includes the difficult row. Keep a disposition for every case that leaves the set, including an explained decision to do nothing. That gives the team a way to recognise recovery that helped B, rather than a report which has merely become more agreeable.

The report is ready to be useful when it can leave C and D alone, bring B to an investigation, keep E’s duplicate question open and carry F’s later payment into the next eligible run. Those choices do not produce one universal repair command. They give each person the treatment justified by the agreed rule, with current checks before any change. Matching the total is the easy part; preserving those differences is what makes the reconciliation worth running.