Updating your LMS from our events

This is the page most integrations actually ship from: exactly how to write our events into your loan tables — correctly, idempotently, and crash-safe. It applies identically whether events arrive by webhook or the poll API: both lanes feed the same handler.

The rules the handler encodes

1. Dedupe on eventId. Delivery is at-least-once; the same event can arrive twice (retry, replay, webhook and poll). The inbox row's primary key makes the second copy a no-op.
2. Money is always booked. Every payment.collected becomes a ledger row — regardless of its sequence, of arrival order, and even if it belongs to an earlier case of the same loan. The ledger is idempotent on eventId and on (loan, reference). Never skip a payment for ordering reasons.
3. The watermark is per (sourceLoanNumber, caseId). sequence restarts at 1 whenever a closed loan is re-pushed and gets a new case. A new caseId for a loan means: adopt it and reset the watermark.
4. Case-state is sequence-gated. Status, totalCollected, outstandingAfter are applied only if the event's sequence is greater than the watermark for that case. Older events change nothing (their money was still booked by rule 2).
5. Gaps are normal — never wait for them. sequence counts every event type, including ones you are not subscribed to, and deliveries can overtake each other. Apply what you have; optionally reconcile with GET /cases/{loan} afterwards.
6. Absolute values, not deltas. totalCollected and outstandingAfter are the case totals after the payment — SET them, never +=.
7. One transaction per event. Claim, ledger, state — all or nothing. If it fails, leave the event unapplied (or answer non-2xx to the webhook) so it is retried.
8. ping is not a business event. Ignore it (sourceLoanNumber and sequence are null).
9. case.closed means "closed as of now". Fieldproof operations can re-open a closed_unrecovered case under the same caseId; its next event has a higher sequence and is applied like any other. Never drop events for a case you have marked closed.
10. Status can change without an event — and not every step has one. A case can drop back to pending silently (why), and a Mode A visit that takes money sends no visit.completed — its payment.collected is the signal. Reconcile open loans with GET /cases/{loan} on a schedule.

The three tables you need

You almost certainly have the first already. Amounts are whole INR rupees.

schema.sql (PostgreSQL syntax; adapt types for your database)

-- 1. Your existing loans table — add Fieldproof sync-state columns.
ALTER TABLE loans ADD COLUMN fp_case_id          VARCHAR(64);                     -- current Fieldproof case for this loan
ALTER TABLE loans ADD COLUMN fp_case_opened_at   TIMESTAMP;                       -- occurredAt of the first event seen for fp_case_id
ALTER TABLE loans ADD COLUMN fp_status           VARCHAR(32);                     -- pending | in_progress | collected | closed_unrecovered
ALTER TABLE loans ADD COLUMN fp_total_collected  INTEGER NOT NULL DEFAULT 0;      -- absolute, from payloads
ALTER TABLE loans ADD COLUMN fp_last_sequence    INTEGER NOT NULL DEFAULT 0;      -- watermark for fp_case_id
ALTER TABLE loans ADD COLUMN fp_last_outcome     VARCHAR(32);                     -- last visit.completed outcome (opt-in event)
ALTER TABLE loans ADD COLUMN fp_ptp_date         DATE;                            -- promise-to-pay date, from visit.completed ptpDate

-- 2. Event inbox = dedupe store + durable copy of what we sent you.
CREATE TABLE fp_events (
  event_id     VARCHAR(64) PRIMARY KEY,          -- our eventId
  type         VARCHAR(40) NOT NULL,
  loan_number  VARCHAR(64),                      -- sourceLoanNumber (NULL only for ping, which you need not store)
  case_id      VARCHAR(64),
  sequence     INTEGER,
  raw          TEXT NOT NULL,                    -- the exact JSON envelope as received
  received_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  applied_at   TIMESTAMP                         -- NULL until the handler below has committed
);

-- 3. Collections ledger — one row per payment.collected we report.
CREATE TABLE fp_collections (
  event_id     VARCHAR(64) PRIMARY KEY,          -- idempotent on eventId
  loan_number  VARCHAR(64) NOT NULL,
  case_id      VARCHAR(64) NOT NULL,
  reference    VARCHAR(128),                     -- UTR / gateway payment id / NULL
  amount       INTEGER NOT NULL,                 -- incremental — this payment only
  mode         VARCHAR(20) NOT NULL,             -- upi | card | netbanking | wallet | online | link | neft | nach | cheque | razorpay | other
  collected_at TIMESTAMP NOT NULL,
  sequence     INTEGER NOT NULL
);
-- Idempotent on the payment reference too (Mode B: the same gateway id you confirmed).
-- NULL references never collide in PostgreSQL / MySQL / SQLite; on SQL Server use a filtered index.
CREATE UNIQUE INDEX fp_collections_loan_reference ON fp_collections (loan_number, reference);

The receiver (webhook or poll loop) writes the inbox row before acknowledging — that is the insertEventIfAbsent stub in the receiver samples:

insertEventIfAbsent — run by the receiver, before the 2xx

INSERT INTO fp_events (event_id, type, loan_number, case_id, sequence, raw)
VALUES (?, ?, ?, ?, ?, ?)
ON CONFLICT (event_id) DO NOTHING;      -- MySQL: INSERT IGNORE …
-- rowcount 1 → new event, hand to the handler; rowcount 0 → duplicate, just answer 2xx

The handler (complete, transactional)

Runs once per new inbox row — from a worker, or inline before you answer the webhook (it is a handful of SQL statements; if it throws, answer non-2xx and we retry). It is safe to run more than once for the same event: step 1 makes the second run a no-op.

apply-event.js (Node — any SQL client; adapt freely)

async function applyFieldproofEvent(event) {
  if (event.type === "ping") return;                        // self-test: sourceLoanNumber / sequence are null
  const p = event.payload;
  const loanNo = event.sourceLoanNumber;

  await db.transaction(async (tx) => {
    // 1. Claim the event — idempotency. Row was inserted by the receiver / poll loop.
    const claim = await tx.query(
      "UPDATE fp_events SET applied_at = CURRENT_TIMESTAMP WHERE event_id = ? AND applied_at IS NULL",
      [event.eventId],
    );
    if (claim.rowCount === 0) return;                       // already applied (duplicate / replay / rerun)

    // 2. Lock the loan's sync state.
    const loan = await tx.queryOne(
      "SELECT fp_case_id, fp_case_opened_at, fp_last_sequence FROM loans WHERE loan_number = ? FOR UPDATE",
      [loanNo],
    );
    if (!loan) throw new Error(`loan ${loanNo} not found in LMS`);   // you pushed it, so it must exist — roll back, investigate

    // 3. Which case is this event about? sequence is per CASE and restarts at 1 on a re-push.
    let isCurrentCase, watermark;
    if (loan.fp_case_id === p.caseId) {
      isCurrentCase = true;  watermark = loan.fp_last_sequence;
    } else if (!loan.fp_case_id || new Date(event.occurredAt) > new Date(loan.fp_case_opened_at)) {
      // A case we have not seen for this loan and it is NEWER than the one we track → adopt, reset the watermark.
      // (Cases of one loan never overlap: a new case only opens after the previous one closed.)
      isCurrentCase = true;  watermark = 0;
      await tx.query(
        `UPDATE loans SET fp_case_id = ?, fp_case_opened_at = ?, fp_last_sequence = 0,
                          fp_status = 'pending', fp_total_collected = 0,
                          fp_last_outcome = NULL, fp_ptp_date = NULL
         WHERE loan_number = ?`,
        [p.caseId, event.occurredAt, loanNo],
      );
    } else {
      // A straggler from an EARLIER (closed) case — its money still counts, its state does not.
      isCurrentCase = false;
    }

    // 4. MONEY — always booked, never gated. Idempotent on eventId (PK) and (loan_number, reference).
    //    Never require a visit.completed first: a Mode A visit that took money sends none.
    if (event.type === "payment.collected") {
      await tx.query(
        `INSERT INTO fp_collections (event_id, loan_number, case_id, reference, amount, mode, collected_at, sequence)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?)
         ON CONFLICT DO NOTHING`,
        [event.eventId, loanNo, p.caseId, p.reference, p.amount, p.mode, p.collectedAt, event.sequence],
      );
    }

    // 5. CASE STATE — only from the current case, only if newer than the watermark.
    //    Gaps (sequence > watermark + 1) are NORMAL — apply anyway; optionally reconcile afterwards.
    //    No terminal guard: a later event after case.closed on the same caseId supersedes the closure
    //    (Fieldproof re-opened the case; when it closes again a new case.closed follows).
    if (!isCurrentCase || event.sequence <= watermark) return;

    switch (event.type) {
      case "case.received":
        await tx.query(
          "UPDATE loans SET fp_status = 'pending', fp_last_sequence = ? WHERE loan_number = ?",
          [event.sequence, loanNo],
        );
        break;

      case "case.assigned":                                  // opt-in; fires again on every (re)assignment
        await tx.query(
          "UPDATE loans SET fp_status = 'in_progress', fp_last_sequence = ? WHERE loan_number = ?",
          [event.sequence, loanNo],
        );
        break;

      case "visit.completed":
        // Opt-in. A promise to pay is outcome 'delivered_acknowledged' with p.ptpDate ('YYYY-MM-DD') set —
        // there is no 'ptp' outcome. COALESCE keeps an earlier promise when a later visit carries none.
        await tx.query(
          `UPDATE loans SET fp_status = 'in_progress', fp_last_outcome = ?,
                            fp_ptp_date = COALESCE(?, fp_ptp_date), fp_last_sequence = ?
           WHERE loan_number = ?`,
          [p.outcome, p.ptpDate, event.sequence, loanNo],
        );
        break;

      case "payment.collected":
        // ABSOLUTE values from the payload — SET, never +=. A payment means the case is in progress;
        // a full clearance is followed by its own case.closed event, which sets 'collected'.
        await tx.query(
          `UPDATE loans SET
             outstanding_amount = ?,                        -- p.outstandingAfter
             fp_total_collected = ?,                        -- p.totalCollected
             fp_status          = 'in_progress',
             fp_last_sequence   = ?
           WHERE loan_number = ?`,
          [p.outstandingAfter, p.totalCollected, event.sequence, loanNo],
        );
        break;

      case "case.closed":
        // p.status: 'collected' | 'closed_unrecovered'; p.reason: full_settlement | closed_by_operations | recalled |
        // not_available | refused | written_off | legal_handover. outstanding_amount was already set by the payment
        // events. full_settlement: any remainder is inside our 1 % full-clear tolerance — waive or keep it per your
        // policy. closed_by_operations: our collection team closed it with a balance left (or no amount
        // recorded) — that balance was NOT reported as collected; reconcile it yourself.
        // "Closed as of now": a later event for this caseId (higher sequence) supersedes this one.
        await tx.query(
          `UPDATE loans SET
             fp_status          = ?,
             fp_total_collected = ?,
             fp_last_sequence   = ?
           WHERE loan_number = ?`,
          [p.status, p.totalCollected, event.sequence, loanNo],
        );
        break;

      default:
        // Unknown / future type (e.g. verification.completed on the address product): move the watermark only.
        await tx.query(
          "UPDATE loans SET fp_last_sequence = ? WHERE loan_number = ?",
          [event.sequence, loanNo],
        );
    }
  });
}

? placeholders and ON CONFLICT DO NOTHING are PostgreSQL/SQLite flavoured — use INSERT IGNORE on MySQL. FOR UPDATE serialises concurrent events for the same loan (remember deliveries can arrive in parallel).

Reconcile

Reconcile with GET /cases/{sourceLoanNumber} on a gap, and on a schedule.

On a gap (optional). If event.sequence > watermark + 1 for the current case, something between was either not subscribed (normal) or has not arrived yet (also normal — it will, or you can replay it). Your row is already correct because payloads carry absolute values. If you want belt and braces, refresh from the authoritative snapshot after the transaction commits — never instead of applying.

On a schedule (recommended). Some changes have no event: a case can go back to pending (lease lapsed, timeouts, unassignment, rejected proof…) and Fieldproof operations can re-open a closed_unrecovered case — see returns to pending. Run reconcileLoan periodically (nightly is typical) over loans whose fp_status is pending or in_progress — plus recently closed_unrecovered ones if you want to see a re-open before its next event — paced well inside the 60 requests/min limit you share with pushes and polls:

reconcile.js — after commit / from a scheduled job, off the request path

async function reconcileLoan(loanNo) {
  const { case: c } = await fieldproof("GET", `/api/v1/partner/cases/${encodeURIComponent(loanNo)}`);
  // c: { sourceLoanNumber, caseId, status, isActive, assignedAt, totalCollected, outstandingAmount, visits[], ... }
  // Only touches the case you already track; a newer caseId is adopted by the event handler.
  // The watermark is left alone, so later events still apply.
  await db.query(
    `UPDATE loans SET fp_status = ?, fp_total_collected = ?, outstanding_amount = ?
     WHERE loan_number = ? AND fp_case_id = ?`,
    [c.status, c.totalCollected, c.outstandingAmount, loanNo, c.caseId],
  );
}

// Scheduled sweep — catches silent returns to pending. Add recently closed_unrecovered loans
// to the WHERE clause if you also want to see a Fieldproof re-open before its next event.
async function reconcileOpenLoans() {
  const rows = await db.query(
    "SELECT loan_number FROM loans WHERE fp_status IN ('pending', 'in_progress')",
  );
  for (const { loan_number } of rows) {
    await reconcileLoan(loan_number);
    await new Promise((r) => setTimeout(r, 2000));                   // ~30 req/min — leave room for pushes and polls
  }
}

(fieldproof() is your signed client — the same one you use for pushes; the path is percent-encoded and signed without any query string.) Note that visits[].amountCollected in that snapshot is the partner's unverified claim until a payment.collected confirms it — reconcile money from the events, not from visits.

Worked example — one loan, full lifecycle

A ₹12,500 loan, default subscription (case.received, payment.collected, case.closed), collected in two payments. Sequences 2 and 3 were a case.assigned and a visit.completed you are not subscribed to, so you never see them — the gap is expected. Deliveries reach you out of order: 1, 5, 4, 6.

Arrives	seq	type	payload (relevant)	Ledger	loans row after applying
1st	1	case.received	status: "pending"	—	status pending, watermark 1
2nd	5	payment.collected	amount: 7500, totalCollected: 12500, outstandingAfter: 0	+₹7,500	status in_progress, outstanding 0, collected 12,500, watermark 5
3rd	4	payment.collected	amount: 5000, totalCollected: 5000, outstandingAfter: 7500	+₹5,000	unchanged (4 ≤ 5 — state gate); money still booked
4th	6	case.closed	status: "collected", reason: "full_settlement", totalCollected: 12500	—	status collected, watermark 6

Ledger total ₹12,500 = totalCollected — both payments booked, no double counting, and the stale seq 4 never overwrote the newer totals. If seq 4 had been lost entirely, seq 5 and 6 still land the row on collected / 12,500 / 0 — that is why payloads carry absolutes — and the missing ledger row is recoverable by replay or the poll API.

Re-push after closure. Say the borrower had instead refused and the case closed closed_unrecovered (seq 4). Next week you push the loan again: a new caseId arrives with case.received, sequence: 1. Step 3 sees a newer, unknown case, adopts it (status back to pending, collected total back to 0) and resets the watermark to 0 — sequence 1 is applied. Had you kept a per-loan watermark of 4, every event of the new case would have been silently dropped. If the new case's first event to reach you is a payment.collected (its case.received overtaken in transit), the same branch adopts the case from that event; the late case.received is then correctly ignored by the watermark.

Re-open instead of re-push. Say that closed_unrecovered case (seq 4) is re-opened by Fieldproof operations (admin review, attempts reset or address correction). Nothing is sent at that moment — only GET /cases/{loan} (or a reconcile sweep that includes recently closed loans) shows the same caseId back at pending. When a partner later takes it, case.assigned (by webhook only if you opted in; always in the poll feed) is seq 5 on that caseId: 5 > 4, so step 5 applies it and the loan is in_progress again. If ops instead assign the re-opened case straight to a partner, that case.assigned comes at once and the case never shows pending. When it later closes again, a new case.closed (new eventId, higher sequence) closes it again — the case.assigned in between guarantees that, even when it closes the same way as before.

When is a payment "real"?

A payment.collected is only ever emitted once the money is booked — never for a claim still waiting on verification or confirmation:

• Mode A — after our operations team verifies the partner's payment proof. amount is the operator-confirmed figure (it may correct the partner's claim — trust the event); reference carries the UTR where extracted; mode is what the partner recorded (usually upi); collectedAt is the partner's visit check-out time, not the verification time. That visit sends no visit.completed. Rejected proofs emit nothing.
• Mode B — the event mirrors your own payments/confirm call: reference is the gateway payment id you sent, mode is the mode you sent (default link). Your books and ours agree by construction; treat the event as the ack that we booked it (partner earning credited, case advanced). If you already wrote a ledger row from your gateway webhook, the unique (loan_number, reference) index turns the event's ledger insert into a no-op while the state update still applies.
• Booked by Fieldproof's collection team — a payment taken outside the partner flow. Record payment: mode is the mode they recorded (other for cash, IMPS or anything outside the enum), reference the UTR / reference they entered (or null), collectedAt the payment date they entered (or when they recorded it). Direct close: mode: "other", reference: null, sent only if an amount was recorded. When the case closes, case.closed follows the payment with full_settlement (it cleared the balance) or closed_by_operations (closed with a balance left). With no reference, pair it with your statement by amount and collectedAt.

So: post the event to your loan ledger as the booking of record, and use your bank/gateway statement as the audit pair.

Testing this handler

• Sandbox has no simulated field agents; lifecycle events (assignment, visits, Mode A verification) are driven by the Fieldproof team on request. You can self-drive push, dryRun, webhooks/test, replay, poll, recall, and — for org_gateway orgs — payments/confirm, which does emit payment.collected / case.closed.
• Use delivery replay to fire a duplicate at your endpoint: it must be a no-op (inbox PK).
• To prove the out-of-order path, delete one payment's fp_events and fp_collections rows locally, then replay that event after its case.closed has been applied: the ledger row reappears, the loan row does not change.
• The org-simulator reference implementation drives its mock loan book purely from webhook payloads — ask the integration team ([email protected]) for it and run it beside your build to compare state case by case.