CitadelPayroll batch payroll - Import Excel, disburse to 1000+ employees, reconcile in one dashboard

Processing payroll for a workforce exceeding 1,000 employees is rarely as simple as clicking a "send" button. For operators and developers using legacy banking infrastructure, batch payments often involve fragile CSV parsers, multiple wire transfer approvals, and a reconciliation nightmare involving disconnected spreadsheets. If you are building HR Tech, staffing platforms, or enterprise resource planning (ERP) tools, you need an API that handles high-volume disbursements with precision.

ThisKard’s CitadelPayroll—part of the Sovereign series designed for B2B enterprise solutions—abstracts the complexity of mass payouts. It provides a unified API for importing payroll data, validating recipient accounts via OnyxClearing, and disbursing funds instantly or via ACH/wire rails.

In this tutorial, we will build a Node.js script that automates the end-to-end lifecycle of a payroll cycle:

1. Parsing an Excel file containing 1,000+ employee records.
2. Ingesting this data into CitadelPayroll via the api.thiskard.com batch endpoint.
3. Monitoring the disbursement status.
4. Reconciling the results programmatically.

Prerequisites

Before we begin, ensure you have the following:

• Node.js v18+ installed.
• A ThisKard Developer Account with access to the Sovereign series sandbox.
• An API Key (TK_SECRET_KEY) generated from the dashboard.
• A sample Excel file (payroll_june_2024.xlsx) formatted with columns: EmployeeID, FullName, AccountNumber, RoutingNumber, Amount, Currency.

Step 1: Environment Setup and Dependencies

We will use standard libraries to handle Excel parsing and HTTP requests. Create a project folder and initialize your environment.

mkdir citadel-payroll-automation
cd citadel-payroll-automation
npm init -y
npm install axios xlsx dotenv

We will use axios for API calls, xlsx for parsing Excel data, and dotenv to manage our API keys securely.

Create a .env file in your root directory:

THISKARD_API_KEY=sk_sandbox_your_key_here
BASE_URL=https://api.thiskard.com

Step 2: Parsing the Excel Data

The first challenge in batch payroll is data hygiene. While CitadelPayroll has built-in validation, it is best practice to structure your payload correctly before sending it over the wire.

We will write a helper function to read the Excel file and transform it into the JSON array expected by the CitadelPayroll API.

Create a file named process_payroll.js:

require('dotenv').config();
const axios = require('axios');
const XLSX = require('xlsx');
const fs = require('fs');

// Configuration
const PAYROLL_FILE = './payroll_june_2024.xlsx';
const BATCH_SIZE = 500; // CitadelPayroll handles large batches, but we will chunk for demonstration

/**
 * Parses an Excel file and maps rows to CitadelPayroll Recipient Objects.
 * @param {string} filePath - Path to the Excel file.
 * @returns {Array} - Array of recipient objects.
 */
function parsePayrollExcel(filePath) {
    if (!fs.existsSync(filePath)) {
        throw new Error(`File not found at ${filePath}`);
    }

    const workbook = XLSX.readFile(filePath);
    const sheetName = workbook.SheetNames[0]; // Assume first sheet
    const sheet = workbook.Sheets[sheetName];
    
    // Convert sheet to JSON
    const rows = XLSX.utils.sheet_to_json(sheet);

    console.log(`Parsed ${rows.length} rows from Excel.`);

    // Map Excel columns to API schema
    // CitadelPayroll expects specific fields for disbursement
    const recipients = rows.map(row => {
        return {
            reference_id: `EMP-${row.EmployeeID}`, // Unique ID for idempotency
            recipient_name: row.FullName,
            amount: {
                value: row.Amount,
                currency: row.Currency || 'USD'
            },
            destination: {
                account_number: row.AccountNumber,
                routing_number: row.RoutingNumber,
                type: 'checking' // Default or map from Excel
            },
            metadata: {
                department: row.Department || 'General'
            }
        };
    });

    return recipients;
}

Step 3: Creating the Batch Payroll Job

Now that we have our data parsed, we need to send it to the Sovereign series API endpoint.

Endpoint: POST /v1/sovereign/citadel-payroll/batches

When working with 1,000+ employees, two critical factors come into play:

1. Idempotency: Ensuring that a network timeout doesn't result in double payment.
2. Source of Funds: CitadelPayroll draws from your VaultSpend balance or a linked external ledger.

Here is the function to submit the batch. We will implement a chunking mechanism to ensure large payloads are handled gracefully, though CitadelPayroll's infrastructure is optimized for high-throughput ingestion.

const axiosInstance = axios.create({
    baseURL: process.env.BASE_URL,
    headers: {
        'Authorization': `Bearer ${process.env.THISKARD_API_KEY}`,
        'Content-Type': 'application/json'
    },
    timeout: 30000 // 30-second timeout for large batches
});

/**
 * Submits a batch of payments to CitadelPayroll.
 * @param {Array} recipients - Array of formatted recipient objects.
 * @returns {Object} - API response containing Batch ID.
 */
async function submitPayrollBatch(recipients) {
    const idempotencyKey = `payroll-${Date.now()}`;

    try {
        console.log(`Submitting batch of ${recipients.length} recipients...`);

        const response = await axiosInstance.post(
            '/v1/sovereign/citadel-payroll/batches',
            {
                description: 'Monthly Payroll Disbursement',
                source_account: 'vlt_sp_123456789', // Your VaultSpend source ID
                recipients: recipients,
                idempotency_key: idempotencyKey
            }
        );

        console.log(`Batch created successfully. Batch ID: ${response.data.batch_id}`);
        return response.data;

    } catch (error) {
        handleApiError(error);
    }
}

Step 4: Error Handling and Validation

In fintech integration, "happy path" coding is dangerous. When disbursing to 1,000 employees, a single malformed account number can reject the entire batch if not handled correctly. CitadelPayroll supports partial acceptance, allowing valid payments to proceed while flagging errors.

However, we should handle API-level errors (authentication, rate limits) and validation errors.

Add this error handler to your process_payroll.js:

function handleApiError(error) {
    if (error.response) {
        // The request was made and the server responded with a status code
        // that falls out of the range of 2xx
        const { status, data } = error.response;
        
        if (status === 400) {
            console.error("Validation Error: Invalid payload structure.");
            console.error(data.details); // Specific validation errors from ThisKard
        } else if (status === 401) {
            console.error("Authentication Error: Check your THISKARD_API_KEY.");
        } else if (status === 409) {
            console.error("Conflict Error: Duplicate Idempotency Key detected.");
        } else if (status === 429) {
            console.error("Rate Limit: Please slow down requests.");
        } else {
            console.error(`API Error [${status}]:`, data.message);
        }
    } else if (error.request) {
        console.error("Network Error: No response received from api.thiskard.com.");
    } else {
        console.error("Script Error:", error.message);
    }
    throw error; // Re-throw to stop execution
}

Best Practice: Idempotency

Notice the idempotency_key in the POST request. This is crucial for batch operations. If your script times out after sending the request but before receiving a response, you can retry the exact same request with the same key. CitadelPayroll will recognize the key and return the status of the original request rather than creating a duplicate payroll run.

Step 5: Putting It Together (The Workflow)

Let's write the main execution function. We will simulate a scenario where we load the Excel file and submit the job.

async function main() {
    try {
        // 1. Import and Parse
        const recipients = parsePayrollExcel(PAYROLL_FILE);

        if (recipients.length === 0) {
            console.log("No recipients found in Excel file.");
            return;
        }

        // 2. Disburse
        // Note: CitadelPayroll supports large arrays, but for 1000+ records,
        // you may chunk requests if you prefer client-side throttling.
        // Here we send the full payload to demonstrate API capacity.
        const batchResult = await submitPayrollBatch(recipients);

        // 3. Initial Status Check
        const batchId = batchResult.batch_id;
        
        console.log("---------------------------------");
        console.log("Batch Submission Summary:");
        console.log(`Batch ID: ${batchId}`);
        console.log(`Total Submitted: ${batchResult.submitted_count}`);
        console.log(`Status: ${batchResult.status}`); // e.g., 'PROCESSING'
        console.log("---------------------------------");

        // 4. Polling for Reconciliation (Simplified)
        // In production, we recommend using Webhooks (Step 6)
        await monitor