Skip to content

example-narrative.md

Latest commit

 

History

History
238 lines (183 loc) · 13.7 KB

example-narrative.md

File metadata and controls

238 lines (183 loc) · 13.7 KB
Raw

Worked Example: Payment Reconciliation Service

This is a demonstration deliverable — a fictional project showing what a faithful execution of project-interview-docs produces. It models the discipline, not just the shape: metrics carry evidence labels, contribution uses grounded verbs, a drift note is resolved, and one evidence gap is left as a gap rather than fabricated. Project: a service that reconciles a payment provider's webhook stream against an internal ledger. Candidate role used in this example: implemented the reconciliation core; integrated with others on the webhook receiver.

1. Project overview

A subscription company's internal ledger drifted out of sync with its payment provider: webhooks arrived out of order, were redelivered, and occasionally lost, so finance could not trust the ledger's view of who had paid. The reconciliation service replays the provider's event stream, matches each event to the ledger, and emits a daily exception list of unmatched or duplicated transactions for a human operator to resolve. Built in Go on PostgreSQL, it runs as a scheduled worker and exposes a read-only exception API. The candidate implemented the matching engine and integrated with others on the webhook ingestion path.

(~95 words — note: over the 30-second band; trim the next two for the spoken version.)

2. Architecture

Provider API ──▶ Webhook receiver (team-owned) ──▶ event_store (PostgreSQL)
                                                          │
                              reconciliation worker ──────┘ (scheduled, idempotent)
                                                          │
                              ledger (PostgreSQL) ◀────── match & compare
                                                          │
                              exception_store ───────────┘ ──▶ exception API (read-only) ──▶ operator UI

Responsibilities, not names:

  • Webhook receiver (team-owned, not the candidate's): accepts provider events, writes them to event_store with the provider's event id and timestamp. Out-of-order and redelivered events are accepted as-is; ordering is the reconciler's problem.
  • Reconciliation worker (candidate's core): for each event not yet reconciled, loads the corresponding ledger record, classifies the pair as matched, unmatched, or duplicated, and writes the result to exception_store.
  • Ledger: the source of truth for what the system believes the customer was charged. Append-only; corrections are new rows, never updates.
  • Exception API: read-only view of exception_store; the operator UI fetches the daily exception list and a human resolves each row.

State locations: event_store and ledger are durable (PostgreSQL); exception_store is durable but derived — it can be rebuilt from the other two. No caching layer; the workload is batch, not low-latency.

3. Representative flow

scheduled trigger → load unreconciled events → for each: load ledger record →
classify (matched / unmatched / duplicated) → write exception or mark reconciled →
observable outcome: daily exception list shrinks as the operator resolves rows

One event, end to end:

  1. Trigger. The worker runs on a 5-minute schedule (verified: worker cron entry + the schedule config).
  2. Load unreconciled. It selects events from event_store with no matching reconciliation row, ordered by provider timestamp.
  3. Load ledger record. For each event, it fetches the ledger row keyed by the provider's transaction id.
  4. Classify.
    • matched: ledger amount and status agree with the event.
    • unmatched: the event has no ledger row (we were charged but recorded nothing).
    • duplicated: the event was already reconciled (provider redelivery).
  5. Write. matched marks the event reconciled; unmatched and duplicated write a row to exception_store with the classification and both timestamps.
  6. Outcome. The operator UI shows the new exceptions; a human decides whether to post a ledger correction or mark the exception resolved.

Branches that change the outcome: only the three classifications above. The worker does not retry provider calls (it reads only event_store, not the provider) and does not auto-correct the ledger — both are deliberate, addressed under core decisions.

4. Core technical decisions

Decision A — Idempotent reconciliation keyed on provider event id

Problem. Webhooks are redelivered; running the same event twice must not create a second exception or double-count.

Decision. The reconciliation row is keyed by the provider's event id. Re-processing an event that already has a reconciliation row short-circuits to duplicated.

Mechanism. A UNIQUE constraint on (provider_event_id) plus an INSERT ... ON CONFLICT DO NOTHING. The worker treats a conflict as "already reconciled."

Tradeoff. Rejected alternative: dedupe in application memory. Delta: in-memory dedupe survives only one process lifetime; a restart re-processes the redelivery. The DB constraint survives restarts and concurrent workers. Failure mode: if two workers race on the same event, exactly one wins the insert; the other sees duplicated — correct, not a bug.

Validation. Integration test: replay a redelivered event 100× and assert exactly one reconciliation row.

Decision B — Reconciliation never calls the provider; never auto-corrects the ledger

Problem. The reconciler could fetch the "truth" live from the provider, or could post corrections to the ledger directly. Both are tempting and both are dangerous.

Decision. The worker reads only event_store (written by the receiver) and writes only to exception_store. It neither calls the provider nor mutates the ledger.

Mechanism. The worker's database role has SELECT on event_store/ledger and INSERT on exception_store only — enforced at the DB grant level, not just in code.

Tradeoff. Rejected alternative: live-fetch the provider on unmatched to "self-heal." Delta: live-fetch hides receiver gaps and creates a runtime dependency on provider uptime; the chosen design surfaces gaps as exceptions for a human, making loss visible. Failure mode: if the provider is down, reconciliation still completes from event_store.

Validation. The DB grant is asserted by a test that attempts a forbidden write and expects a permission error.

Decision C — Append-only ledger; corrections are new rows

Problem. Mutating a ledger row to "fix" a reconciliation error destroys the audit trail of what was believed when.

Decision. The ledger is append-only. A correction is a new row referencing the original; the current view is the latest row per transaction.

Mechanism. Ledger rows are immutable; view_ledger_current is a ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY created_at DESC) view.

Tradeoff. Rejected alternative: in-place update. Delta: in-place is simpler to query but loses history; append-only costs a slightly more complex read but preserves an audit trail — a requirement for finance. Failure mode: a bug that appends a spurious correction is visible (excess rows) rather than silent (overwritten value).

Validation. Property test: the count of ledger rows for a transaction is monotonically non-decreasing across all operations.

5. Challenges and lessons

  1. Out-of-order events produced false unmatched exceptions. Symptom: high exception volume where the ledger row arrived seconds after the event. Diagnosis: the worker compared the event to the ledger at the moment of processing, with no grace window. Options: (a) a grace period before classifying unmatched, (b) re-run reconciliation on the next cycle, (c) block until ledger catches up. Final choice: (b) re-run on next cycle — unmatched is provisional until two consecutive cycles confirm it. Cost: exceptions appear one cycle later than strictly necessary. Verification: the false-positive rate dropped to near-zero in the integration replay suite.

  2. A provider schema change silently broke parsing. Symptom: events stopped matching; unmatched spiked. Diagnosis: the provider added a nullable field; the parser treated null as a missing transaction id. Options: (a) pin to a provider API version, (b) make the parser fail loud on unknown shapes. Final choice: both — pin the version header and add a strict parser that rejects unknown fields. Cost: legitimate new fields now require a deploy. Verification: the parser's strict mode is covered by a contract test against a captured provider schema sample.

  3. Concurrent workers double-processing events. Symptom: intermittent duplicated rows during scale testing. Diagnosis: two workers picked up the same event. Options: (a) a distributed lock, (b) the UNIQUE constraint already chosen in Decision A. Final choice: rely on the constraint — no lock needed. Cost: one worker does wasted work. Verification: a concurrency test with N workers produces exactly one reconciliation row per event.

6. Interview delivery

30-second intro

We had a trust problem: our ledger disagreed with the payment provider because webhooks arrive out of order and sometimes get redelivered. I built a reconciliation service that replays the event stream, matches each event to the ledger, and flags unmatched or duplicated transactions for a human to resolve. It's idempotent, so redelivery is safe, and it never auto-corrects the ledger — it surfaces the gaps instead of hiding them.

(~70 words.)

3-minute architecture explanation

The system has three durable stores and one worker. The webhook receiver — owned by the team, not me — writes every provider event to event_store exactly as it arrives, including redeliveries and out-of-order events. Ordering is deliberately the reconciler's problem. I implemented the reconciliation worker: on a five-minute schedule it loads unreconciled events, fetches the matching ledger row by transaction id, and classifies the pair as matched, unmatched, or duplicated. Matched events are marked reconciled; the other two write a row to exception_store. The operator UI reads exception_store read-only and a human resolves each row by posting a ledger correction or marking it resolved.

Two decisions matter most. First, reconciliation is idempotent on the provider's event id — a unique constraint plus ON CONFLICT DO NOTHING means a redelivered event is processed exactly once. Second, the worker has no grants to call the provider or to mutate the ledger; it only reads event_store and writes exception_store. That means a provider outage can't block reconciliation, and the system surfaces receiver gaps instead of silently self-healing them. The ledger is append-only — corrections are new rows — so the audit trail of what was believed when is never destroyed.

One limitation: unmatched is provisional until two consecutive cycles confirm it, so an exception can appear one cycle late. That's the cost of not blocking until the ledger catches up.

(~250 words — under the 400–450 band; expand with one classification example if asked for the full three minutes.)

10-minute walkthrough

The full flow is in sections 3–5 above. Spoken, walk the interviewer through one event from schedule trigger through classification, then open each of the three core decisions (idempotent reconciliation, no provider calls / no ledger mutations, append-only ledger), then the out-of-order and schema-change challenges. Land on the limitation: provisional unmatched and the one-cycle latency it costs.

Evidence and assumptions (appendix)

Claim Strongest evidence Evidence type Confidence Treatment
5-minute worker schedule worker cron entry + schedule config active config high State directly.
Idempotency via unique constraint migration DDL + ON CONFLICT in code + integration test implementation + test high State directly.
Worker DB grants are restricted DB role grants + forbidden-write test config + test high State directly.
Append-only ledger migration DDL + view definition + property test implementation + test high State directly.
Provider API version pinned version header in code + contract test implementation + test high State directly.
Exception volume dropped to "near-zero" integration replay suite test (not production metric) medium Qualify: "in the replay suite," not "in production."
Provider redelivery rate / production QPS not available — low — gap Do not state. If asked, say "not established by the available materials."
Candidate led the project evidence does not support "led" — low — gap Use "implemented the core / integrated with others"; do not claim "led."

Drift note (resolved)

Claim Sources Status Treatment
Does the worker fetch the provider live on unmatched? README says "self-heals by fetching the provider"; active code + DB grants show no provider calls and no ledger writes. resolved — README describes a planned feature, code is current. State the current behavior (no live fetch). Label the README claim as planned, not historical-presented-as-current.

Contribution boundary

The candidate implemented the reconciliation engine and its tests, and integrated with others on the webhook receiver. The webhook receiver and operator UI were team-owned. The candidate did not design the overall service topology. Use these verbs; do not generalize to "led" or "owned."