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).

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

-- 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
  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
         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).
    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.
    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":
      case "visit.completed":                                // opt-in events; store p.outcome / p.ptpDate if useful
        await tx.query(
          "UPDATE loans SET fp_status = 'in_progress', fp_last_sequence = ? WHERE loan_number = ?",
          [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 | recalled | not_available |
        // refused | written_off | legal_handover. outstanding_amount was already set by the payment events;
        // on 'collected' any remainder is inside our 1 % full-clear tolerance — waive or keep it per your policy.
        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).

Optional: reconcile on a gap

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:

reconcile.js — after commit, 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[], ... }
  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],
  );
}

(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.

When is a payment "real"?

A payment.collected is only ever emitted for money verifiably in your account:

• 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). 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.

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.