Points Queue System- Technical Documentation

Zoho Catalyst × Zoho CRM Integration | Node Version 20.0

Table of Contents

1. System Overview

2. Push Function (HTTP Endpoint)

3. Scheduler Job (Every 1 Minute)

4. Queue Table Schema

5. Point Type to CRM Field Mapping

6. Error Handling & Edge Cases

7. Notification Flow

8. Deployment Notes

1. System Overview

The Points Queue System is an asynchronous, fault-tolerant pipeline that safely distributes loyalty/reward points into Zoho CRM without causing race conditions or duplicate updates.

Instead of updating CRM fields directly at the moment a transaction occurs, every point operation is first written to a Catalyst Datastore queue. A scheduled job then processes transactions one at a time, in order — ensuring atomicity and full auditability.

Why a Queue? Direct CRM updates from multiple concurrent webhook calls can overwrite each other (last-write-wins). The queue serialises all writes so each point delta is applied exactly once, in sequence.

1.1 Architecture at a Glance

Two serverless functions collaborate with a persistent Catalyst Datastore table:

2. Push Function (HTTP Endpoint)

The Push Function is the entry point into the system. It is a lightweight HTTP handler that reads a JSON body, inserts a row into the points_queue Catalyst Datastore table, and responds — all within milliseconds.

2.1 Responsibilities

• Accept inbound HTTP requests from Zoho CRM workflows or any caller

• Parse the raw request body as JSON (no framework body-parser assumed)

• Insert one row per transaction into points_queue with STATUS = 'Pending'

• Return a JSON response indicating success or failure

2.2 Request Payload

The caller must send a JSON body with the following fields:

2.3 Sample Request

POST /functions/push_points_to_queue
Content-Type: application/json

{
  "TRANSACTIONID":        "TXN-2025-00123",
  "POINT_TRANSACTION_ID": "5678901234567890",
  "MODULE":               "Contacts",
  "RECORDID":             "1234567890123456",
  "TXN_TYPE":             "Credit",
  "POINT_TYPE":           "Actual",
  "POINT":                150
}

2.4 Sample Response

// Success
{ "success": true, "row": { "ROWID": "...", "STATUS": "Pending", "..." : "..." } }

// Failure
{ "success": false, "error": "<exception message>" }

2.5 Internal Logic

const app   = catalyst.initialize(req);
const table = app.datastore().table("points_queue");
const row   = await table.insertRow({ ...body, STATUS: "Pending" });

The function uses the Catalyst Node.js SDK to interact with the Datastore. No CRM API calls are made in this function — it only writes to the queue and returns.

3. Scheduler Job (Every 1 Minute)

The Job function is the engine of the system. Catalyst triggers it on a 1-minute interval. Each execution processes at most one Pending transaction, ensuring there are never two concurrent updates to the same CRM record.

Step A — Housekeeping: Delete Completed Rows

At the start of every run, the job queries for rows with STATUS = 'Completed' and deletes them. This prevents the queue table from growing indefinitely and keeps queries fast.

SELECT ROWID FROM points_queue WHERE STATUS = 'Completed'

Step B — Concurrency Guard

If any row with STATUS = 'Processing' exists, the job exits immediately:

"Already Processing" → context.closeWithSuccess(...)

This ensures that if a previous run is still in flight, no second instance will start processing the same or the next row.

Step C — Fetch Oldest Pending Row

The job queries for the oldest Pending row and immediately locks it:

SELECT ROWID, TRANSACTIONID, POINT_TRANSACTION_ID,
MODULE, RECORDID, TXN_TYPE, POINT_TYPE, POINT
FROM points_queue
WHERE STATUS = 'Pending'
ORDER BY CREATEDTIME ASC
LIMIT 1

The row is immediately updated to STATUS = 'Processing' to act as a distributed lock.

Step D — Fetch Current CRM Value

GET /crm/v2/{MODULE}/{RECORDID}
Authorization: Zoho-oauthtoken {ACCESS_TOKEN}

The current value of the target point field is read from the CRM record before any calculation.

Step E — Compute New Value

const operationMap = { Debit: -1, Credit: +1 };

const typeMap = {
  "Actual":                "Actual_Point",
  "Provisional":           "Provisional_Point",
  "Tactical Provisional":  "Bonus_Point",
  "Tactical Actual":       "Actual_Point"
};

const oldPoints  = normalize(crmData[fieldName]);          // current value
const delta      = normalize(queue.POINT) * operationMap[queue.TXN_TYPE];
const finalPoints = normalize(oldPoints + delta);           // result

normalize() rounds to 4 decimal places to avoid floating-point drift.

Step F — Write Back to CRM & Verify

PUT /crm/v2/{MODULE}/{RECORDID}
{ "data": [{ "id": "...", "{fieldName}": finalPoints }] }

After the PUT, the job immediately GETs the record again to verify the written value:

const actualValue = normalize(verifyRes.data.data[0][fieldName]);
const EPSILON = 0.0001;

if (Math.abs(actualValue - finalPoints) > EPSILON) {
  throw new Error(`CRM MISMATCH: expected ${finalPoints}, got ${actualValue}`);
}

If the values don't match, the row is marked Failed and no further action is taken.

Step G — Update Points_Transaction & Mark Completed

PUT /crm/v2/Points_Transaction/{POINT_TRANSACTION_ID}
{ "data": [{ "id": "...", "Processing_Status": "Done" }] }

This update is wrapped in its own try/catch — a failure here does not roll back the already-verified point adjustment.

Finally, the queue row is marked complete:

await table.updateRow({
  ROWID: rowId,
  STATUS: "Completed",
  PROCESSEDTIME: new Date().toISOString(),
  ERROR: ""
});

4. Queue Table Schema (points_queue)

4.1 Transaction Status Lifecycle

[Inserted by Push Function]
          │
          ▼
       Pending  ──── Scheduler picks up ────►  Processing
                                                    │
                              ┌─────────────────────┴───────────────────────┐
                              ▼                                             ▼
                          Completed                                       Failed
                   (deleted on next run)                          (manual review needed)

Note: A Failed row is not retried automatically. An operator must reset STATUS back to Pending after investigating.

5. Point Type to CRM Field Mapping

6. Error Handling & Edge Cases

All errors in the scheduler job transition the Processing row to STATUS = 'Failed' and record the exception text in the ERROR column.

Error Recovery — Scheduler

// On any unhandled error, the job catches and marks the row Failed:
await table.updateRow({
  ROWID: rows[0].points_queue.ROWID,
  STATUS: "Failed",
  ERROR: e.message,
  PROCESSEDTIME: new Date().toISOString()
});

7. Notification Flow

Notifications are not sent directly by either function. They rely on a Zoho CRM Workflow that fires when the Points_Transaction record's Processing_Status field changes to Done.

Scheduler Job
     │
     │  PUT Processing_Status = "Done"
     ▼
Points_Transaction (CRM Record)
     │
     │  Field-update trigger fires
     ▼
Zoho CRM Workflow
     │
     │  Notify action
     ▼
User Notification (Email / In-App / SMS)

Setting Up the Notification Workflow

1. Go to Zoho CRM → Settings → Automation → Workflows

2. Create a new rule on the Points_Transaction module

3. Set trigger condition: Processing_Status equals Done

4. Add a Notify action with the desired template and recipient field

5. Save and activate the workflow

8. Deployment Notes

8.1 Dependencies

{
  "zcatalyst-sdk-node": "latest",
  "axios": "latest"
}

8.2 Environment & Configuration

• The Zoho OAuth Access Token is fetched dynamically on each job run via a Zoho CRM Function (get_global_variable). No token is hard-coded in the job.

• The Catalyst API Key (zapikey) used to call the token function is currently in source code — consider moving to Catalyst Environment Config for production.

• The Catalyst Datastore table must be named exactly points_queue with all columns matching those listed in Section 4.

8.3 Scheduler Setup

1. Deploy the Job function in Catalyst as a Job type function

2. Create a Catalyst Scheduled Job pointing to this function with a 1-minute frequency

3. Ensure the job's execution timeout is at least 30 seconds

4. Verify the Catalyst service account has read/write access to the points_queue table

8.4 Scaling Considerations

The current design processes one transaction per scheduler tick (1 per minute = max 60/hour).

For higher throughput:

• Reduce the scheduler interval to the platform minimum

• Extend the job to loop over multiple rows per execution while maintaining the Processing lock pattern

• Consider partitioning the queue by MODULE if multiple CRM modules are updated independently

Points Queue System — Internal Technical Documentation — v1.0