Skip to content
TimeTracking

Request an integration

Tell us which system you want to connect. We review every request weekly and publish a step-by-step guide or build the connection.

Export mappings

The ExportMapping JSON schema that turns TimeTracking pay lines into a payroll import file: fields, every transform with input/output examples, code tables, grouping, validation, and how to create and use mappings.

Last updated: · Markdown version

An export mapping is a JSON recipe. It turns pay lines into the exact CSV or TXT file a payroll system imports. Switching payroll provider means writing a new mapping, not new code.

approved days ─► rule pack ─► pay lines ─► export mapping ─► file (+ issues)

Where mappings live

Mapping mappingId Editable
SD Worx concept preset preset-sdworx No (copy it)
Generic CSV preset preset-generic No (copy it)
Your org's mappings UUID Yes. Admins use "Export" → "Exportformaten beheren" (export formats) in the dashboard: start from a preset ("Beginnen vanaf"), edit columns, code tables ("Codetabel") and validation ("Validatie vóór export"), check the live preview ("Live voorbeeld"), set a default ("Maak standaard"). Max 50 per org

To use a mapping, pass its id as mappingId to POST /exports. The id of an org mapping is the UUID in its dashboard URL (/export/mappings/<uuid>).

Planned: creating, reading and updating mappings over the API. Today the dashboard is the only editor, and POST /exports with an unknown id returns 404 Export mapping not found. Committed runs keep a full mapping_snapshot (GET /exports/runs/{id}), so you can always read the exact mapping a file was produced with.

Pay line (the input)

Each pay line is one person × date × hour type:

Field Type Example Notes
employeeNumber string "1004" people.employee_number, "" when missing
employeeName string "Sanne Janssen" first + last name
clientName string "Bakkerij Ruijters" "" for leave lines
placementRef string "PL-2026-104" placement ref, "" for leave
date YYYY-MM-DD "2026-12-01"
hourType string "normal", "overtime_125", "expense_km", "leave_vacation" see hour types
minutes integer 480 0 for money-only lines
amountCents integer | absent 750 allowances and expenses
componentType "hours" | "allowance" "hours"

Real pay lines are stored on every export run (GET /exports/runs/{id} → pay_lines):

[
  { "date": "2026-12-01", "minutes": 480, "hourType": "normal", "clientName": "Bakkerij Ruijters", "employeeName": "Sanne Janssen", "placementRef": "PL-2026-104", "componentType": "hours", "employeeNumber": "1004" },
  { "date": "2026-12-02", "minutes": 450, "hourType": "normal", "clientName": "Bakkerij Ruijters", "employeeName": "Sanne Janssen", "placementRef": "PL-2026-104", "componentType": "hours", "employeeNumber": "1004" }
]

ExportMapping schema

interface ExportMapping {
  id: string;                         // "preset-…" or the org mapping's UUID (set by the server)
  name: string;                       // 2–120 chars; appears in export.completed as data.mapping
  format: "csv" | "txt" | "fixed" | "xml"; // csv/txt = delimited; fixed = fixed-width .txt; xml = .xml
  delimiter: ";" | "," | "\t" | "|";
  includeHeader: boolean;             // first line = column headers
  encoding: "utf-8" | "utf-8-bom" | "windows-1252";
  lineEnding: "crlf" | "lf";
  groupBy?: PayLineField[];           // ≤ 9 fields; sum minutes and amountCents per group
  columns: ExportColumn[];            // 1–60; headers must be unique
  tables: Record<string, Record<string, string>>;   // code tables for "map"
  validation: ValidationRule[];       // ≤ 60
  xml?: { root: string; record: string; declaration?: boolean }; // format "xml" only
}
interface ExportColumn {
  header: string;                     // 1–80 chars
  source: PayLineField | `const:${string}`;          // a pay line field, or a constant ("const:NL01")
  transform?: Transform;
  width?: number;                     // fixed: field width 1–200 (default 10); longer values are cut
  align?: "left" | "right";           // fixed: default left
  padChar?: string;                   // fixed: one character, default space ("0" for zero-padded numbers)
  xmlName?: string;                   // xml: element name (default: header made XML-safe)
}
interface ValidationRule {
  column: string;                     // must be an existing header
  required?: boolean;                 // empty → issue
  pattern?: string;                   // JavaScript regex source, ≤ 200 chars
  min?: number;                       // numeric check ("7,50" is read as 7.5)
  max?: number;
}
type PayLineField = "employeeNumber" | "employeeName" | "clientName" | "placementRef" | "date"
                  | "hourType" | "minutes" | "amountCents" | "componentType";

The same rules as JSON Schema (draft 2020-12), for validating mappings in your own tooling:

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "$id": "https://timetracking.tech/schemas/export-mapping.json",
  "type": "object",
  "required": ["name", "format", "delimiter", "includeHeader", "encoding", "lineEnding", "columns", "tables", "validation"],
  "properties": {
    "id": { "type": "string", "maxLength": 80 },
    "name": { "type": "string", "minLength": 2, "maxLength": 120 },
    "format": { "enum": ["csv", "txt", "fixed", "xml"] },
    "delimiter": { "enum": [";", ",", "\t", "|"] },
    "includeHeader": { "type": "boolean" },
    "encoding": { "enum": ["utf-8", "utf-8-bom", "windows-1252"] },
    "lineEnding": { "enum": ["crlf", "lf"] },
    "groupBy": { "type": "array", "maxItems": 9, "items": { "$ref": "#/$defs/field" } },
    "columns": {
      "type": "array", "minItems": 1, "maxItems": 60,
      "items": {
        "type": "object", "required": ["header", "source"],
        "properties": {
          "header": { "type": "string", "minLength": 1, "maxLength": 80 },
          "source": { "anyOf": [{ "$ref": "#/$defs/field" }, { "type": "string", "pattern": "^const:.{0,200}$" }] },
          "transform": { "$ref": "#/$defs/transform" }
        }
      }
    },
    "tables": { "type": "object", "additionalProperties": { "type": "object", "additionalProperties": { "type": "string", "maxLength": 60 } } },
    "validation": {
      "type": "array", "maxItems": 60,
      "items": {
        "type": "object", "required": ["column"],
        "properties": {
          "column": { "type": "string", "minLength": 1, "maxLength": 80 },
          "required": { "type": "boolean" },
          "pattern": { "type": "string", "maxLength": 200 },
          "min": { "type": "number" },
          "max": { "type": "number" }
        }
      }
    }
  },
  "$defs": {
    "field": { "enum": ["employeeNumber", "employeeName", "clientName", "placementRef", "date", "hourType", "minutes", "amountCents", "componentType"] },
    "sep": { "enum": [".", ","] },
    "transform": {
      "oneOf": [
        { "type": "object", "required": ["kind"], "properties": { "kind": { "const": "hours_decimal" }, "decimals": { "type": "integer", "minimum": 0, "maximum": 4 }, "decimalSeparator": { "$ref": "#/$defs/sep" } } },
        { "type": "object", "required": ["kind"], "properties": { "kind": { "const": "hours_hm" } } },
        { "type": "object", "required": ["kind"], "properties": { "kind": { "const": "minutes" } } },
        { "type": "object", "required": ["kind", "format"], "properties": { "kind": { "const": "date" }, "format": { "enum": ["YYYY-MM-DD", "DD-MM-YYYY", "DD/MM/YYYY", "YYYYMMDD"] } } },
        { "type": "object", "required": ["kind", "table"], "properties": { "kind": { "const": "map" }, "table": { "type": "string", "minLength": 1, "maxLength": 60 }, "fallback": { "type": "string", "maxLength": 60 } } },
        { "type": "object", "required": ["kind"], "properties": { "kind": { "const": "cents_decimal" }, "decimalSeparator": { "$ref": "#/$defs/sep" } } },
        { "type": "object", "required": ["kind"], "properties": { "kind": { "const": "upper" } } },
        { "type": "object", "required": ["kind", "length"], "properties": { "kind": { "const": "pad" }, "length": { "type": "integer", "minimum": 1, "maximum": 40 }, "char": { "type": "string", "maxLength": 1 } } }
      ]
    }
  }
}

The dashboard editor also checks three cross-field rules: column headers are unique, every map transform names an existing table, and every validation rule names an existing column.

Transforms

Without a transform, the raw value is printed (null/absent → empty).

kind Options Input → output
hours_decimal decimals (default 2), decimalSeparator "." (default) or "," minutes: 450 → 7.50; with {"decimals":2,"decimalSeparator":","} → 7,50; {"decimals":0} → 8 (rounded)
hours_hm — 450 → 7:30; 480 → 8:00
minutes — 450 → 450
date format: YYYY-MM-DD, DD-MM-YYYY, DD/MM/YYYY, YYYYMMDD 2026-12-01 → 2026-12-01 / 01-12-2026 / 01/12/2026 / 20261201
map table (key into tables), fallback (default "") hourType: "overtime_125" with tables.wageCodes.overtime_125 = "1125" → 1125; unmapped → fallback
cents_decimal decimalSeparator amountCents: 750 → 7.50 / 7,50; absent → empty
upper — "pl-2026-104" → PL-2026-104
pad length, char (default "0") "1004" with {"length":8} → 00001004 (left-padded; longer values are not cut)

Constants: "source": "const:NL01" always prints NL01. Transforms apply to constants too.

Grouping

groupBy sums minutes and amountCents of lines with equal values in the listed fields. For example, ["employeeNumber","hourType"] produces one line per employee per hour type for the whole export range.

All fields not in groupBy (including date) are taken from the first line of the group. Captured from the SD Worx preset: two days (8.00 + 7.50 h) became one row 1004;Sanne Janssen;[CODE_NORMAAL];15,50;;01-12-2026. The date column then shows the first day, not the period. Add date to groupBy for per-day lines, or use const: for a period column.

Fixed-width and XML files

format: "fixed" writes one line per row with every column padded or cut to its width (no delimiter, no quoting; tabs and newlines in values become spaces). With includeHeader the first line holds the headers in the same widths. align: "right" with padChar: "0" gives zero-padded numbers. Example (preset preset-generic-fixed, widths 8/30/20/8/8):

00001001Jan de Vries                  normal                 15.50 20261102

format: "xml" writes <root><record><element>value</element>…</record>…</root>, indented two spaces, with the mapping's line ending. Element names come from xmlName or the header ("Aantal uren" → Aantal_uren; names starting with a digit or xml get a leading _). Values are escaped (&, <, >, "). The declaration states UTF-8, or windows-1252 for that encoding; delimiter and includeHeader are ignored. Example (preset preset-generic-xml):

<?xml version="1.0" encoding="UTF-8"?>
<timesheet>
  <line>
    <employee_number>1001</employee_number>
    <employee_name>Jan de Vries</employee_name>
    <client>VDL Montage</client>
    <placement>PL-2026-101</placement>
    <date>2026-11-02</date>
    <hour_type>normal</hour_type>
    <hours>8.00</hours>
  </line>
</timesheet>

Validation, grouping and code tables work the same for every format. GET /exports/runs/{id}/file serves the file as text/plain (fixed, txt), application/xml (xml) or text/csv; ?as=json and ?as=xlsx convert any format (XML and fixed-width runs are rebuilt from the run's frozen pay lines).

Validation

Rules run on the formatted cell values. Every violation is an issue {row, column, message} (row is 1-based, without the header). Messages: Required value missing, Does not match <pattern>, Below <min>, Above <max>. Any issue sets ok: false, and a commit then fails with 422 Export has N validation issue(s). Typical rules:

[
  { "column": "Werknemersnummer", "required": true, "pattern": "^\\d+$" },
  { "column": "Looncomponent", "required": true },
  { "column": "Aantal", "required": true, "min": 0, "max": 400 }
]

Encoding and line endings

  • utf-8-bom prefixes the file with a UTF-8 BOM (U+FEFF). Excel then opens accented names correctly.
  • windows-1252 is applied when you download the committed file with GET /exports/runs/{id}/file. The response is text/csv; charset=windows-1252, without BOM. Characters outside U+0000–U+00FF (€, curly quotes, em dash, ł, ő …) become ?, so keep codes and headers plain. Preview content, the commit response and the dashboard download remain UTF-8. Convert those yourself if needed (iconv -f UTF-8 -t WINDOWS-1252).
  • crlf is recommended for Windows-based payroll importers.
  • Values containing the delimiter, quotes or newlines are quoted CSV-style (" doubled).
  • Cells that start with =, +, -, @, a tab or CR, and are not plain numbers, get a leading ' (protection against spreadsheet formula injection). Negative numbers like -7,50 are left alone.

Complete example: a custom mapping

A per-week file for a payroll system that wants employee number padded to 6, a wage code, decimal hours with a comma, an amount, and the week's Monday:

{
  "name": "Loket — variabele gegevens per week",
  "format": "csv",
  "delimiter": ";",
  "includeHeader": true,
  "encoding": "utf-8-bom",
  "lineEnding": "crlf",
  "groupBy": ["employeeNumber", "hourType"],
  "columns": [
    { "header": "Werknemer", "source": "employeeNumber", "transform": { "kind": "pad", "length": 6 } },
    { "header": "Looncomponent", "source": "hourType", "transform": { "kind": "map", "table": "codes" } },
    { "header": "Aantal", "source": "minutes", "transform": { "kind": "hours_decimal", "decimals": 2, "decimalSeparator": "," } },
    { "header": "Bedrag", "source": "amountCents", "transform": { "kind": "cents_decimal", "decimalSeparator": "," } },
    { "header": "Periode", "source": "const:2026-W49" }
  ],
  "tables": { "codes": { "normal": "1000", "overtime_125": "1125", "ort_night": "2100", "holiday": "2300", "expense_km": "8010", "leave_vacation": "3000" } },
  "validation": [
    { "column": "Werknemer", "required": true, "pattern": "^\\d{6}$" },
    { "column": "Looncomponent", "required": true }
  ]
}

The const: period above is static. Change it per run in the dashboard, or leave it out and let the payroll import assign the period. Wage codes here are placeholders: get the real codes from your payroll system or consultant. See the per-system guides, e.g. Loket.

Using a mapping

# Preview (no changes)
curl -s -X POST "$TT/exports" -H "Authorization: Bearer $KEY" -H "content-type: application/json" \
  -d '{"mappingId":"<mapping uuid or preset id>","startsOn":"2026-11-30","endsOn":"2026-12-06","mode":"preview"}'

Check ok, issues, warnings and pendingCount, then commit. See payroll files.