Export Vacations to Personio

Jan 21, 2025 by Alexander Huber

Recording and managing work hours is a fundamental aspect of modern businesses, but it also comes with challenges. Various requirements often lead to the parallel use of multiple systems: one for general time tracking (documentation of work hours, payroll basis, compliance with legal regulations) and another for project-specific time tracking.

Our experience shows that in such cases, optimizing the collaboration between these systems is crucial. This can reduce redundant processes and create synergies that promote both efficiency and transparency.

Challenges and Opportunities of Parallel Time Tracking Systems

Recording work hours is an indispensable part of daily business operations. However, employers often face the challenge of running different systems in parallel: a tool like Personio manages attendance, vacations, and absences. At the same time, specialized project time tracking tools provide deeper insights into project-specific work hours. This parallel structure often results in administrative duplication but also opens up potential for automation and process optimization.

In this article, we explore:

• the main functions of Personio and project time tracking tools using time cockpit as an example,
• the differences between work time tracking and project time tracking,
• and how interfaces can bridge these worlds.

Personio – Focus on HR Management

Personio is a comprehensive HR software solution specifically developed for small and medium-sized enterprises. The platform covers all essential areas of personnel management and offers the following key features:

• Personnel Management: Centralizes all employee data in a digital personnel file, manages absences, tracks work hours, and organizes documents (Personio – Personnel Management).
• Recruiting: Supports the entire recruitment process, including job postings, applicant management, and onboarding of new employees (Personio – Recruiting).
• Payroll: Prepares payroll with DATEV integration, optimizing the payroll process and minimizing errors (Personio – Payroll).
• Performance & Development: Manages employee performance and development through feedback sessions, goal setting, and training (Personio – Performance & Development).
• Workflow Automation: Automates recurring HR processes to improve efficiency and reduce manual errors (Personio – Workflow Automation).

Additionally, Personio offers over 200 integrations with other tools to ensure seamless workflows (Personio – Integrations). The software is cloud-based, enabling access from anywhere, which is particularly beneficial for companies with remote workplaces.

Overview of Project Time Tracking

Project time tracking systematically records work hours for tasks or projects. It provides transparency, optimizes resources, and ensures that projects are efficiently executed and budgets adhered to.

Basis for Billing and Profitability

For service providers, it is essential to accurately bill services rendered. Clients receive clear documentation of time spent, while companies can analyze the profitability of their projects.

Success Factors

Simple tools, clear processes, and intuitive software are critical for efficient implementation and team acceptance, making project time tracking practical and valuable.

Work Time Tracking vs. Project Time Tracking

Work time tracking focuses on recording attendance and absences to comply with legal and internal requirements. It addresses basic questions such as:

• When did the employee start and finish working? (e.g., starting work at 8:00 AM and finishing at 4:30 PM with a one-hour lunch break).
• How many hours were worked in total each day? (e.g., 7.5 hours on a regular workday).
• Were there overtime hours or absences? (e.g., 2 hours of overtime or a sick day).

Project time tracking, on the other hand, provides a more detailed perspective and addresses questions such as:

• What tasks were worked on? (e.g., creating a marketing campaign or fixing a software bug).
• How long was spent on a specific project or for a client? (e.g., 4 hours for the “Website Redesign” project of a client).
• How efficiently was time used? (e.g., 80% of time was productive, while 20% was spent in meetings or waiting for feedback).
• What budget was used for which projects or clients? (e.g., 15 hours out of a total budget of 40 hours for a project).

Complementary Integration

An integration of both systems enables companies to monitor both compliance with labor laws and the efficiency and profitability of projects. For example, employee times can be automatically transferred from the project tracking system to the work tracking system, eliminating duplicate entries and reducing errors.

Potential Challenges of Parallel Use

Companies that use both work time tracking and a project tracking tool often encounter the following challenges:

1. Redundant Data Entry: Employees must record their work hours separately in both systems, e.g., attendance in the work tracking system and project-specific hours in the project tracking system. This leads to duplicated effort and user dissatisfaction.

2. Error-Prone Processes: Multiple entries increase the risk of discrepancies, such as an employee entering different work hours in both systems or forgetting to synchronize changes. This can lead to inconsistencies in reports and billing.

3. Increased Administrative Workload: The lack of integration between systems requires manual reconciliation of data. This particularly affects HR departments and project managers, who must ensure all data is accurate and synchronized. This process is time-consuming and resource-intensive.

A lack of integration between the two systems can hinder efficiency, reduce transparency, and negatively impact user acceptance.

Interfaces as a Solution

In this blog post, we demonstrate how the challenges mentioned above can be resolved by integrating work time tracking and project time tracking systems. We use Personio as the HR tool for work time tracking and time cockpit as an example of a project time tracking system.

An interface between the two systems allows companies to streamline processes and improve efficiency. At the same time, it breaks down data silos, avoids redundant entries, and creates a consistent data foundation.

Solution Approach

In this solution, time cockpit is used as the primary system for time entries and vacations, as employees record not only project-related hours but also detailed activities. These granular entries are essential for ensuring transparency and accuracy in project and performance-related time tracking.

The implementation is done using Azure Functions, a scalable and cost-efficient solution. The Azure Function synchronizes time entries and vacations between time cockpit and Personio on an hourly basis. The architecture involves the following Azure resources:

• Azure Function: Executes the regular synchronization jobs.
• Azure Key Vault: Securely stores secrets such as the Time Cockpit PAT (Personal Access Token) and the Personio Client Secret.
• Application Insights: Logs and monitors the function’s execution.

To enable communication with the two systems, a client is implemented for each system. Since the functionality of the clients is manageable, they are developed manually without generating client libraries using tools like AutoRest.

API Integration: PersonioClient

To authenticate with Personio, you first need to create an integration in Personio. Follow these steps:

1. Navigate to Settings: Go to Settings → API Access Data.

2. Create Integration: Create a custom integration and give it a meaningful name.

3. Assign Permissions: Select the necessary permissions. In our case, these are:
   • Read employees
   • Read and edit attendances
   • Read and edit absences

After generating the Client Secret for Personio, it should be stored securely. We use Azure Key Vault to ensure secure storage.

The following code shows how to configure the PersonioClient for dependency injection in .NET Core.

var host = new HostBuilder()
    .ConfigureFunctionsWebApplication()
    .ConfigureAppConfiguration(builder =>
    {
        // Configure settings
    })
    .ConfigureServices((hostContext, services) =>
    {
        // Add HTTP client for Personio
        services.AddHttpClient("Personio", httpClient =>
        {
            httpClient.BaseAddress = new Uri(config["PersonioBaseUri"]);
            httpClient.DefaultRequestHeaders.Add("X-Personio-Partner-ID", "YOUR-PARTNER-ID");
            httpClient.DefaultRequestHeaders.Add("X-Personio-App-ID", "YOUR-APP-ID");
            SetToken(httpClient, config);
        });
    })
    .Build();

host.Run();

The SetToken method exchanges the Client Secret for a Bearer Token, which must be included in the Authorization header of all API requests. This ensures secure communication with Personio.

After successfully exchanging the credentials, the Bearer Token is stored in the Authorization header of the HTTP client. This ensures that the token is automatically included with all subsequent API requests. It is essential to verify that the token is set before sending requests, as no authenticated API calls can be made without a valid token.

void SetToken(HttpClient httpClient, IConfiguration configuration)
{
	var body = new
	{
		client_id = configuration["PersonioClientId"],
		client_secret = configuration["PersonioClientSecret"]
	};

	var json = JsonConvert.SerializeObject(body);
	var payload = new StringContent(json, Encoding.UTF8, "application/json");
	var response = httpClient.PostAsync("auth", payload).Result;
	var responseContent = response.Content.ReadAsStringAsync().Result;

	if (!response.IsSuccessStatusCode)
	{
		throw new Exception(responseContent);
	}

	JObject jsonObject = JObject.Parse(responseContent);
	var token = (string)jsonObject["data"]["token"];
	httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token);
}

Responsibilities of the PersonioClient

The PersonioClient serves as the connection to the Personio API and facilitates the efficient management of attendance and absence records. Its main responsibilities include:

• Data Retrieval: Fetching vacation and attendance data for specific employees.
• Data Transmission: Creating, updating, and deleting entries in Personio.

Client Structure

The PersonioClient leverages constructor dependency injection in .NET Core to ensure a clear separation of responsibilities and simplified testability.

An example of the structure is the constructor:

In Program.cs, both the client for Personio and the one for time cockpit are initialized. The HttpClientFactory is used to provide and automatically manage the desired HttpClient. This client is then injected into the PersonioClient.

This approach promotes reusability and ensures that all dependencies are centrally configured and readily available.

public PersonioClient(IHttpClientFactory httpClientFactory, ILogger<PersonioClient> logger, IOptions<Settings> configuration)
{
    this.httpClient = httpClientFactory.CreateClient("Personio");
    this.configuration = configuration;
    this.logger = logger;
}

The central method of the PersonioClient is GetVacationsOfUser. This method retrieves the absences of a specific user using the Personio API.

Special attention is given to handling large datasets: the method is implemented with paging support. This ensures that even with extensive datasets, such as a large number of absence records, the query remains efficient and performant.

Retrieving Vacation Data

internal async Task<List<TimeOffPeriod>> GetVacationsOfUser(
	string personioId,
	Func<TimeOffPeriod, bool> filterPredicate)
{
	List<TimeOffPeriod> vacations = new List<TimeOffPeriod>();
	const int limit = 200; // Max items per page
	int offset = 1; // Start at the beginning

	while (true)
	{
		var url = $"company/time-offs?limit={limit}&offset={offset}";
		var response = await this.httpClient.GetAsync(url);
		var responseContent = await response.Content.ReadAsStringAsync();

		if (response.IsSuccessStatusCode)
		{
			// Assuming the API returns an object that includes both the data and pagination info
			var pageData = JsonConvert.DeserializeObject<PersonioResponse<TimeOffPeriod>>(responseContent);

			// Add the retrieved vacations to the list
			if (pageData?.Data != null)
			{
				vacations.AddRange(pageData.Data); // Assuming Data is a list of objects representing vacations
			}

			// Check if all pages have been retrieved
			if (pageData?.Metadata?.CurrentPage >= pageData?.Metadata?.TotalPages)
			{
				break; // Exit the loop if there are no more pages to fetch
			}

			// Prepare for the next request
			offset += pageData.Metadata.CurrentPage; // Adjust the offset for the next page
		}
		else
		{
			throw new Exception($"Failed to fetch data: {responseContent}");
		}
	}

	// Apply the filter predicate passed as a parameter
	return vacations?.Where(filterPredicate).ToList();
}

Here is an example demonstrating how a vacation object is created and sent to Personio:

var payload = new
{
    employee_id = user.USR_PersonioId.ToString(),
    time_off_type_id = user.USR_IsExternal ? "2567057" : "2228792",
    start_date = tcVacation.APP_BeginTime.Value.ToString("yyyy-MM-dd"),
    end_date = tcVacation.APP_EndTime.Value.ToString("yyyy-MM-dd"),
    skip_approval = true,
    half_day_start = !tcVacation.APP_IsWholeDay.Value && tcVacation.APP_BeginTime < tcVacation.APP_BeginTime.Value.Date.AddHours(12),
    half_day_end = !tcVacation.APP_IsWholeDay.Value && tcVacation.APP_BeginTime >= tcVacation.APP_BeginTime.Value.Date.AddHours(12),
};

public async Task<HttpResponseMessage> PostAttendance(object payload)
{
    var jsonPayload = JsonConvert.SerializeObject(payload);
    var jsonContent = new StringContent(jsonPayload, Encoding.UTF8, "application/json");
    var response = await httpClient.PostAsync($"company/attendances", jsonContent);
    if (!response.IsSuccessStatusCode)
    {
        var content = await response.Content.ReadAsStringAsync();
        throw new Exception(content);
    }

    return response;
}

public async Task<HttpResponseMessage> DeleteTimeOffPeriod(string id)
{
    var response = await httpClient.DeleteAsync($"company/time-offs/{id}");
    if (!response.IsSuccessStatusCode)
    {
        if (response.StatusCode == System.Net.HttpStatusCode.NotFound)
        {
            this.logger.LogWarning($"Could not find and delete time off period with id {id} in personio.");
        }
        else
        {
            var content = await response.Content.ReadAsStringAsync();
            throw new Exception(content);
        }
    }

    return response;
}

API Integration: TimeCockpitClient

To authenticate with time cockpit, a Personal Access Token (PAT) is required. Instructions for creating such a token can be found in the official documentation: time cockpit Web API - Authentication.

Secure Handling of the PAT

After creating the PAT, we strongly recommend storing it securely. For production use, storing it in Azure Key Vault is advised to ensure secure and centralized management of access credentials.

Integrating the PAT into the TimeCockpitClient

The TimeCockpitClient is configured for dependency injection in .NET Core. Since time cockpit uses a PAT that does not need to be converted into a token, it can be directly set in the Authorization header as a Bearer Token. This simplifies integration and ensures direct and straightforward authentication with the API.

var host = new HostBuilder()
    .ConfigureFunctionsWebApplication()
    .ConfigureAppConfiguration(builder =>
        {
            ...
        })
    .ConfigureServices((hostContext, services) =>
    {
        services.AddHttpClient("TimeCockpit", httpClient =>
        {
            httpClient.BaseAddress = new Uri(config["TimeCockpitBaseUri"]);
            httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue($"Bearer", $"{config["TimeCockpitDataApiPAT"]}");
        });
    })
    .Build();

host.Run();

The Personal Access Token (PAT) is directly set in the Authorization header as a Bearer Token, simplifying the authentication process.

After configuring the HttpClient to interact with time cockpit, the entire interaction with the API is encapsulated within the TimeCockpitClient class.

Responsibilities of the TimeCockpitClient

The TimeCockpitClient serves as the central interface to the time cockpit API, providing a clear separation between API communication and the rest of the application code. Its main responsibilities include:

• Data Retrieval: The client allows access to time entries and other relevant data from time cockpit.
• CRUD Operations: Supports creating, updating, and deleting entries, enabling comprehensive data management.

Client Structure

The TimeCockpitClient is designed modularly and uses HttpClient. By leveraging constructor dependency injection in .NET Core, the client is ensured to be easily configurable and testable.

public TimeCockpitClient(IHttpClientFactory httpClientFactory, ILogger<TimeCockpitClient> logger, IOptions<Settings> configuration)
{
    httpClient = httpClientFactory.CreateClient("TimeCockpit");
    this.configuration = configuration;
    baseUri = this.configuration.Value.TimeCockpitBaseUri;
    this.logger = logger;
}

The TimeCockpitClient serves as the central interface to the Time Cockpit API, offering comprehensive functionality for interacting with the platform.

Utilizing the Dynamic Data Model

Since time cockpit provides a dynamic data model (details on the dynamic data model), this example leverages the Query Endpoint of the API (details on the Query interface). This endpoint allows flexible access to various data structures and supports executing complex queries.

TCQL Integration

The TimeCockpitClient supports the execution of TCQL queries (Time Cockpit Query Language). This query language, specifically developed for time cockpit, enables developers to perform data queries on the dynamic data model. The following method demonstrates how TCQL queries can be executed to retrieve information from the platform.

This approach leverages the full flexibility of time cockpit’s dynamic data model without introducing unnecessary complexity to the application.

public async Task<List<T>> SelectAsync<T>(string query, List<Tuple<string, string>> parameters = null) where T : IJsonDeserializable<T>
{
    JObject jsonRequest = new JObject(
        new JProperty("query", query),
        new JProperty("parameters", parameters?.ToDictionary(p => p.Item1, p => p.Item2)));

    Func<JObject, List<T>> deserialize = json =>
        json["value"].Select(item => T.FromJson(item)).ToList();

    return await GetResult(jsonRequest, deserialize);
}

The SelectAsync method is used to execute queries against the time cockpit API. The following example shows how to query all time entries that have been created or updated since a specific date.

The response is returned as JSON data, which is generically deserialized into a Plain Old CLR Object (POCO), such as TcTimesheet, using Json.NET.

The combination of SelectAsync and generic deserialization offers maximum flexibility and reusability, as the method can work with different TCQL queries and return structures.

var createdOrUpdates = await this.timeCockpitClient.SelectAsync<TcTimesheet>(
                "From V In Timesheet Where V.UserDetail.UserDetailUuid = @Id " +
                "And (V.CreationDateUtc >= @LastExportedDate " +
                "Or V.UpdateDateUtc >= @LastExportedDate) " +
                "Select V",
                new List<Tuple<string, string>> {
            Tuple.Create("LastExportedDate",$"datetime'{lastExportedDate.Value:yyyy-MM-ddTHH:mm:ss}'"),
            Tuple.Create("Id", $"guid'{user.APP_UserDetailUuid}'")
                });

Overview of the Vacation Synchronization Process

The synchronization process is performed as part of an hourly data reconciliation. The steps are as follows:

1. Determine the Last Export:

   • For each export, time cockpit checks the last successful export to Personio.
   • If no export has been performed yet, the lastExportDate is initially set to January 1, 1970. In this case, all vacations in time cockpit are considered for export.

2. Retrieve Vacations from Personio:

   • All vacations for a specific user are retrieved from Personio and organized into a dictionary based on the Personio ID of the vacations.

3. Match Vacations from time cockpit:

   • For each vacation created or modified after the lastExportDate, it is checked whether it has already been exported to Personio.
   • This check is performed using the USR_PersonioId field, which can be easily added to the APP_Vacation entity in time cockpit using its customizable data model. This field stores the internal Personio ID for each vacation.

4. Handle Changes:

   • If a modified vacation from time cockpit already exists in Personio, it is deleted in Personio.

5. Add New Vacations:
   • Each vacation modified in time cockpit is exported to Personio. The new Personio ID is then saved back to time cockpit.

private async Task UpsertVacationsInPersonio(DateTime? lastExportedDate, TcUserDetail user)
{
	var tcVacations = await this.timeCockpitClient.SelectAsync<TcVacation>(
						"From V In Vacation Where V.UserDetail.UserDetailUuid = @Id " +
						"And (V.CreationDateUtc >= @LastExportedDate " +
						"Or V.UpdateDateUtc >= @LastExportedDate) " +
						"Select V",
						new List<Tuple<string, string>>() {
				Tuple.Create("LastExportedDate",$"datetime'{lastExportedDate.Value.ToString("yyyy-MM-dd")}'"),
				Tuple.Create("Id", $"guid'{user.APP_UserDetailUuid.ToString()}'")
						});

	Func<TimeOffPeriod, bool> filter = x => x.Attributes.TimeOffType.Attributes.Category == "paid_vacation"
		|| x.Attributes.TimeOffType.Attributes.Category == "unpaid_vacation";
	var personioAbsences = (await this.personioClient.GetAbsencensOfUser(user.USR_PersonioId, filter)).ToDictionary(x => x.Attributes.Id.ToString());

	foreach (var tcVacation in tcVacations)
	{
		if (tcVacation.USR_PersonioId != null && personioAbsences.TryGetValue(tcVacation.USR_PersonioId, out var timeOffPeriod))
		{
			// there is no update in personio
			await this.personioClient.DeleteTimeOffPeriod(timeOffPeriod.Attributes.Id.ToString());
		}

		var newTimeOffPeriod = new
		{
			employee_id = user.USR_PersonioId.ToString(),
			time_off_type_id = user.USR_IsExternal ? "2567057" : "2228792",
			start_date = tcVacation.APP_BeginTime.Value.ToString("yyyy-MM-dd"),
			end_date = tcVacation.APP_EndTime.Value.ToString("yyyy-MM-dd"),
			skip_approval = true,
			half_day_start = !tcVacation.APP_IsWholeDay.Value && tcVacation.APP_BeginTime < tcVacation.APP_BeginTime.Value.Date.AddHours(12),
			half_day_end = !tcVacation.APP_IsWholeDay.Value && tcVacation.APP_BeginTime >= tcVacation.APP_BeginTime.Value.Date.AddHours(12),
		};

		var response = await this.personioClient.PostTimeOffPeriod(newTimeOffPeriod);

		if (response.IsSuccessStatusCode)
		{
			var allJsonData = await response.Content.ReadAsStringAsync();
			var timeOffPeriodJson = JsonConvert.DeserializeObject<TimeOffPeriod>(JObject.Parse(allJsonData)["data"].ToString());

			tcVacation.USR_PersonioId = timeOffPeriodJson.Attributes.Id.ToString();
			await this.timeCockpitClient.PatchAsync("APP_Vacation", tcVacation.APP_VacationUuid, tcVacation);
		}
		else
		{
			this.logger.LogError($"Could not create absence for vacation with id {tcVacation.APP_VacationUuid}");
		}
	}
}

Useful Tips:

Integration with Azure Key Vault

This solution uses Azure Key Vault for securely storing secrets and configuration settings. Using Key Vault offers significant benefits for both production environments and local development:

builder.AddAzureKeyVault(
    new Uri(builtConfig["VaultUri"]),
    new DefaultAzureCredential(
        new DefaultAzureCredentialOptions
        {
            ExcludeManagedIdentityCredential = false,
            ExcludeAzureCliCredential = false,
            ExcludeInteractiveBrowserCredential = true,
        }
    )
);

Advantages of Key Vault Integration

• Centralized Management: Secrets and configurations are centrally stored in Key Vault, ensuring that changes take immediate effect across all environments.
• Security: Secrets no longer need to be stored locally in files like local.settings.json or User Secrets, minimizing the risk of leaks.
• Consistency: Both local development and production environments use the same configuration settings, avoiding discrepancies.

Use in Production

In production, access to Key Vault is managed using Managed Identity. This eliminates the need to store access credentials within the application or environment.

Use in Local Development

Key Vault is also used for local development. Configuring access is straightforward:

1. Permissions: The user must be authorized as a Key Vault Secrets User.
2. Configuration: The Vault URI is specified in the local.settings.json file. This is the only local configuration required.

The AddAzureKeyVault method registers Azure Key Vault as a configuration source in a .NET application. This allows secrets and configuration settings to be retrieved directly from Key Vault and used within the application without requiring local storage.

Conclusion

The integration of Personio and time cockpit provides a pragmatic solution to the challenges of modern work time and project time tracking. By leveraging Azure-based technologies like Azure Functions, Azure Key Vault, and Application Insights, companies can implement efficient, secure, and scalable processes.

The described synchronization mechanisms demonstrate how connecting two specialized systems can create synergies. The Personio interface facilitates the management of attendance and absence records for HR purposes, while time cockpit offers granular project time tracking, providing deeper insights into tasks and budgets. The hourly synchronization ensures that both systems remain consistent, without manual effort or increased error susceptibility.