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.

The three tables you need

You almost certainly have the first already:

-- 1. Your existing loans table — add two columns for sync state:
ALTER TABLE loans ADD COLUMN qk_total_collected DECIMAL(14,2) NOT NULL DEFAULT 0;
ALTER TABLE loans ADD COLUMN qk_last_sequence   INT           NOT NULL DEFAULT 0;

-- 2. Event dedupe store (at-least-once delivery → replays are normal):
CREATE TABLE qk_events (
  event_id    VARCHAR(64) PRIMARY KEY,      -- our eventId
  received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3. Collections ledger (one row per payment we report):
CREATE TABLE qk_collections (
  reference     VARCHAR(128),               -- UTR / gateway txn id (may be NULL for some proofs)
  event_id      VARCHAR(64) NOT NULL,
  loan_number   VARCHAR(64) NOT NULL,
  amount        DECIMAL(14,2) NOT NULL,
  mode          VARCHAR(20),
  collected_at  TIMESTAMP,
  PRIMARY KEY (event_id)
);

The handler (complete, transactional)

The four rules encoded once — dedupe, sequence-guard, absolute values, single transaction:

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

async function applyQuikkredEvent(event) {
  const loanNo = event.sourceLoanNumber;

  await db.transaction(async (tx) => {
    // Rule 1 — dedupe: INSERT the eventId first; a duplicate aborts cleanly.
    const inserted = await tx.query(
      "INSERT INTO qk_events (event_id) VALUES (?) ON CONFLICT DO NOTHING",
      [event.eventId],
    );
    if (inserted.rowCount === 0) return;              // replay → already applied

    // Rule 2 — sequence guard: never apply older state over newer.
    const { qk_last_sequence } = await tx.queryOne(
      "SELECT qk_last_sequence FROM loans WHERE loan_number = ? FOR UPDATE",
      [loanNo],
    );
    if (event.sequence <= qk_last_sequence) return;    // stale → skip (dedupe row stays)

    switch (event.type) {
      case "payment.collected": {
        const p = event.payload;
        // Rule 3 — ABSOLUTE values: SET totals from the payload, never += .
        await tx.query(
          `UPDATE loans SET
             outstanding_amount = ?,        -- p.outstandingAfter
             qk_total_collected = ?,        -- p.totalCollected
             qk_last_sequence   = ?
           WHERE loan_number = ?`,
          [p.outstandingAfter, p.totalCollected, event.sequence, loanNo],
        );
        // Ledger row for your finance team (amount here IS incremental).
        await tx.query(
          `INSERT INTO qk_collections (event_id, reference, loan_number, amount, mode, collected_at)
           VALUES (?, ?, ?, ?, ?, ?)`,
          [event.eventId, p.reference, loanNo, p.amount, p.mode, p.collectedAt],
        );
        break;
      }

      case "case.closed": {
        const p = event.payload;
        await tx.query(
          `UPDATE loans SET
             collection_status  = ?,        -- 'collected' | 'closed_unrecovered'
             qk_total_collected = ?,
             outstanding_amount = CASE WHEN ? = 'collected' THEN 0 ELSE outstanding_amount END,
             qk_last_sequence   = ?
           WHERE loan_number = ?`,
          [p.status, p.totalCollected, p.status, event.sequence, loanNo],
        );
        break;
      }

      case "case.received":
      case "case.assigned":
      case "visit.completed":
        // Status-only — store what you find useful (e.g. p.ptpDate on
        // visit.completed for your own follow-up scheduling).
        await tx.query(
          "UPDATE loans SET qk_last_sequence = ? WHERE loan_number = ?",
          [event.sequence, loanNo],
        );
        break;
    }
  });
}

Rule 4 — the gap re-fetch

If event.sequence > qk_last_sequence + 1, you missed something (out-of-order delivery, or an outage). You can still apply the event — absolute values keep your totals correct — but reconcile the loan afterwards from the authoritative snapshot:

if (event.sequence > lastSeq + 1) {
  const { case: c } = await quikkred("GET", `/api/v1/partner/cases/${loanNo}`);
  await db.query(
    `UPDATE loans SET outstanding_amount = ?, qk_total_collected = ?, qk_last_sequence = ?
     WHERE loan_number = ?`,
    [c.outstandingAmount, c.totalCollected, event.sequence, loanNo],
  );
}

Worked example — one loan, full lifecycle

A ₹12,500 loan, collected in two payments. Events as your endpoint receives them (note: 3 and 4 can arrive in either order — the handler above is correct both ways):

#	seq	type	payload (relevant)	Your loans row after applying
1	1	case.received	status: "pending"	unchanged (seq=1)
2	2	payment.collected	amount: 5000, totalCollected: 5000, outstandingAfter: 7500	outstanding 7500, collected 5000
3	3	payment.collected	amount: 7500, totalCollected: 12500, outstandingAfter: 0	outstanding 0, collected 12500
4	4	case.closed	status: "collected", totalCollected: 12500	status collected

If #3 is lost entirely, #4 still lands your row on collected/12500/0 — that is why the payloads carry absolutes.

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. reference carries the UTR where extracted; amount is the operator-confirmed figure (it may correct the partner's claim — trust the event).
• Mode B — the event mirrors your own payments/confirm call, so reference is your transaction ID. Your books and ours agree by construction; treat the event as the ack that we booked it (partner earning credited, case advanced).

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

The reference implementation's loan book is driven by exactly this logic — run it beside your build and both should show identical state for every case. Then use delivery replay to fire a duplicate at your endpoint (must no-op) and the sandbox lifecycle to prove the out-of-order case.