How to Sync Deputy Leave Balances into a MySQL HR Database

Build a Spojit workflow that pulls leave records from Deputy on a schedule and upserts them into a MySQL table so HR can run reporting and payroll reconciliation against a single source of truth.

What This Integration Does

HR and payroll teams need leave data sitting in their own database, not locked inside Deputy. Once leave records land in a MySQL table you control, you can join them against payroll runs, build dashboards, reconcile accruals, and feed downstream reports without hitting the Deputy API every time. This workflow keeps that table current automatically so nobody has to export spreadsheets by hand.

The workflow runs on a Schedule trigger (for example every morning before payroll cutoff). On each run it asks Deputy for the current set of leave records, reshapes each record into the columns your table expects, and writes them into MySQL using an upsert so existing rows update in place and new ones are inserted. Because every write is keyed on the Deputy leave ID, re-runs are safe and idempotent: running the workflow twice in a row leaves the table in the same state rather than creating duplicates.

Prerequisites

A Deputy connection (API token) added under Connections. See the Deputy connector article for setup.
A MySQL connection (host, database, user, password) added under Connections. See the MySQL connector article.
A destination table in MySQL with a unique key on the Deputy leave ID so upserts work. A starting schema is given in Step 2.
Permission to create and enable workflows in your workspace.

Step 1: Add a Schedule trigger

Open the Workflow Designer, create a new workflow, and set the Trigger node type to Schedule. A Schedule trigger uses a 5-field Unix cron expression plus an IANA timezone. For a weekday run at 6:00 AM Sydney time, use:

Cron:     0 6 * * 1-5
Timezone: Australia/Sydney

A single Schedule trigger can hold more than one schedule, so you can add an extra entry (for example a Sunday catch-up run) later. The trigger output is { scheduledAt }, which you can reference downstream as {{ scheduledAt }} if you want to stamp the sync time onto each row.

Step 2: Prepare the destination MySQL table

Before the workflow writes anything, create the table once in MySQL. The unique key on deputy_leave_id is what makes the later upsert idempotent. Run this once against your database (you can do it from a MySQL client, or from a one-off Connector node in Direct mode calling the MySQL execute-query tool):

CREATE TABLE IF NOT EXISTS hr_leave (
  deputy_leave_id   BIGINT      NOT NULL,
  employee_id       BIGINT,
  date_start        DATE,
  date_end          DATE,
  status            VARCHAR(64),
  comment           TEXT,
  synced_at         DATETIME,
  PRIMARY KEY (deputy_leave_id)
);

You can confirm the columns at any time with a Connector node in Direct mode using the MySQL describe-table tool with table set to hr_leave.

Step 3: Fetch leave records from Deputy

Add a Connector node, choose the Deputy connector, and use Direct mode with the list-leave tool. This tool takes no inputs and returns the full set of leave records from your Deputy account. Set the node's output variable to leave so the records are available downstream as {{ leave }}.

Each Deputy leave record carries fields such as the leave Id, Employee, DateStart, DateEnd, Status, and ApprovalComment. You will map these into your table columns in the next two steps. If you have a high volume of historical leave and only care about recent activity, you can filter the list down in the Transform step rather than pulling more than you need.

Step 4: Reshape each record with a Transform node

Add a Transform node to convert the Deputy field names into the column names your table uses, and to normalize values. Produce a clean array, one object per row, keyed exactly the way your SQL parameters expect:

{{ leave }} mapped to:
[
  {
    "deputy_leave_id": Id,
    "employee_id": Employee,
    "date_start": DateStart,
    "date_end": DateEnd,
    "status": Status,
    "comment": ApprovalComment,
    "synced_at": "{{ scheduledAt }}"
  }
]

Store the result in an output variable such as rows. Keeping the mapping in a Transform node (rather than inside the SQL) means schema drift in Deputy only changes one place, and you can preview the shaped data in the run log before it ever touches the database.

Step 5: Loop over the rows and upsert into MySQL

Add a Loop node in ForEach mode iterating over {{ rows }}. Inside the loop body, add a Connector node using the MySQL connector in Direct mode with the execute-query tool. Use a parameterized query with ? placeholders and an ON DUPLICATE KEY UPDATE clause so each row inserts if new and updates if it already exists:

INSERT INTO hr_leave
  (deputy_leave_id, employee_id, date_start, date_end, status, comment, synced_at)
VALUES (?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
  employee_id = VALUES(employee_id),
  date_start  = VALUES(date_start),
  date_end    = VALUES(date_end),
  status      = VALUES(status),
  comment     = VALUES(comment),
  synced_at   = VALUES(synced_at);

Map the params array in order to the current loop item:

params: [
  {{ item.deputy_leave_id }},
  {{ item.employee_id }},
  {{ item.date_start }},
  {{ item.date_end }},
  {{ item.status }},
  {{ item.comment }},
  {{ item.synced_at }}
]

Using ? placeholders keeps values escaped and avoids SQL injection from free-text fields like the approval comment. The unique key on deputy_leave_id from Step 2 is what makes the upsert deterministic.

Step 6: Notify on completion (optional)

Add a Send Email node after the loop to confirm the sync ran. Send Email uses Spojit's built-in mail service, so no connection is required. Set Recipients to your HR distribution list and template the body with the number of records processed, for example: Synced leave records at {{ scheduledAt }}. If you would rather know only about failures, set If sending fails to Continue anyway and pair this with a separate failure alert. For a Slack alert instead, see setting up Slack alerts for workflow failures.

Tips

Have Miraxa, the intelligent layer across your automation, scaffold the skeleton for you. Try: "Add a Schedule trigger that runs at 6am on weekdays, a Deputy Connector node in Direct mode calling list-leave, a Transform node, and a Loop that upserts each row into MySQL." Then fine-tune fields in the properties panel.
If your leave volume is large, batch the writes by building a single multi-row INSERT ... ON DUPLICATE KEY UPDATE in the Transform step instead of looping one row at a time. Fewer round trips means faster runs.
Stamp each row with synced_at so you can spot stale records: any row whose synced_at is older than the last run was removed in Deputy and may need archiving.
Add a second schedule to the same trigger for a weekend catch-up rather than building a separate workflow.

Common Pitfalls

No unique key: without the PRIMARY KEY or UNIQUE index on deputy_leave_id, ON DUPLICATE KEY UPDATE never fires and every run inserts duplicates.
Date format mismatch: Deputy date fields may arrive as full timestamps. If MySQL rejects them, format them to YYYY-MM-DD in the Transform node before binding to the query.
Timezone confusion: the Schedule trigger fires in the IANA timezone you set, not your browser's. Confirm Australia/Sydney (or your zone) so the run lands before payroll cutoff.
Parameter order drift: the params array binds positionally to the ? placeholders. If you reorder columns in the query, reorder params to match or values land in the wrong columns.

Testing

Validate on a small scope before enabling the schedule. Temporarily limit the Transform output to a handful of records (or filter to one employee), then use the Run button to execute the workflow manually. Open the execution log to confirm the Deputy list-leave output, the shaped rows array, and each MySQL execute-query call. Query the table afterward (a Connector node in Direct mode with execute-query running SELECT * FROM hr_leave) and run the workflow a second time to confirm row counts stay constant, proving the upsert is idempotent. Once that checks out, remove the limit and enable the Schedule trigger.