Auto-track Work Logs with Jira, Slack, and Open-Source Tools
NOV 08, 2024

NOV 08, 2024
In our fast-growing startup, we noticed an urgent need for agile processes for efficient project management projects and tracking costs. With a team of almost 100 employees, tracking hours became essential for project planning and cost management for any time-tracking software. The need for tracking time led us to implement Jira automation for project tracking due to its robust features. However, as the team grew, we faced a challenge: ensuring every team member logged their 8 hours daily in Jira and notifying managers if their team members were behind to boost employee productivity and keep all logs organized.
Despite Jira's extensive features, we needed help finding an existing, cost-effective project-tracking solution for automating work logs in Jira, especially with Slack, our organization's primary communication tool. Here's how we created our custom solution to automatically notify employees and managers of their daily logged hours in Jira.
As part of our agile transformation, we rolled out a new policy where employees must log 8 hours daily in Jira. However, managers needed a way to track compliance to ensure their teams adhered to this policy. We needed an automated system to:
1) Remind employees to log their hours if they haven't done so at the end of the day.
2) Notify project managers about their team's daily logged hours.
3) Provide an overview to management regarding employees' compliance with logging hours.
We aimed to make this process seamless and cost-effective, leveraging open-source tools where possible.
To address the requirements, we broke down the solution into four main tasks:
1) Avoid Overloading Jira's API: Directly polling Jira's API for every user's work log across multiple projects was impractical.
2) Sync Data from Jira to a Database: We needed a way to sync Jira work log data into a local database for easy querying.
3) Expose Data via API: Create an API endpoint to fetch daily work log data for each user.
4) Automate Notifications: Use a workflow automation tool to send reminders and summaries to Slack.
After extensive research, we settled on three tools to create this automation:
1) Airbyte is used to sync data from Jira to PostgreSQL.
2) Cube.js to create APIs for fetching data.
3) n8n for workflow automation to notify users via Slack.
Our first step was configuring Airbyte to sync work log data from Jira to PostgreSQL. Airbyte allowed us to automate this process with minimal coding. We set up a connection where Jira was the source while PostgreSQL was the destination.
1) In Airbyte, we configured Jira as a source and PostgreSQL as a destination.
2) We scheduled the sync process every hour to update our database with the latest work log entries.
Configuration of Airbyte sync from Jira to PostgreSQL
With the data now in PostgreSQL, the next step was to expose it via an API that could handle custom date range queries. We used Cube.js, a powerful analytics API tool that allowed us to create metrics and dimensions based on the data in PostgreSQL.
1) We set up Cube.js to connect to our PostgreSQL instance.
2) Created a metric for (worklogSpentTimeMinutes) to aggregate daily work logs by the user.
3) Configured filters to fetch data based on employee name and date ranges.
This API made retrieving specific information for tracking each employee's work log easy, enabling us to build a customized notification system.
Cube.js query configuration
The final piece was to conduct n8n workflow automation and automate notifications. n8n is a useful no-code automation tool because of its integration capabilities with both Slack and custom APIs. Since our organization uses the same email addresses in Slack and Jira, we could map users effectively.
1) In n8n, we created a daily workflow. We scheduled it to run at 11 pm.
2) The workflow queries the Cube.js API for each employee's logged daily hours.
3) It then sends notifications to:
n8n Workflow
Our custom solution automates work log tracking by syncing Jira data with PostgreSQL, querying it via Cube.js, and sending automated notifications via n8n and Slack.
1) Employee Notification: Each employee receives a message detailing the hours they logged that day, with a reminder to complete 8 hours if they haven't.
2) Manager Summary: Managers get a list of their team members with the hours logged, making it easy to follow up if any team member is behind.
3) Management Summary: Management receives an overview with a breakdown of the number of employees who logged eight or more hours, fewer than eight hours, and zero hours.
With the right tools and setup, organizations can create customized project-tracking solutions that integrate seamlessly with communication tools.
Daily Logged Hours Report Notification (Warning)
Daily Logged Hours Report Notification (Positive)
Summary Notification For Manager
Summary Notification For Management
Implementing Jira automation helped us streamline our time-tracking process and ensure consistent employee accountability. This automated solution has made a significant impact on our organization's workflow.
This solution has streamlined our operations, making our agile transformation smoother and more effective.
Through Airbyte, Cube.js, and n8n workflow automation, we created an end-to-end automated solution for work log tracking and notifications with minimal cost and effort. This approach demonstrates the power of open-source tools in addressing real-world business challenges, especially for startups like ours, where resource efficiency is critical.
If your team faces similar challenges in project management, consider exploring these tools to build time-tracking software and project-tracking solutions tailored to your needs and boost employee productivity. With a bit of setup, you can automate processes, increase employee productivity, and keep your team informed and accountable.
Tech Lead & DevOps Enthusiast
Aiyaj is the kind of tech mind who thrives on building seamless systems and tackling challenges head-on. A natural problem-solver, he’s happiest when fine-tuning processes and diving into the intricacies of DevOps. While he’s all about efficiency, don’t be surprised to see him experiment with new tech or streamline a setup just for the joy of it.