Worklog Processing

Worklogs from Tempo (time tracking in Jira) are the primary data source for invoice line items. They flow from Jira → Tempo → n8n sync → ClickHouse → Prefill pipeline.

Data Sources

Tempo Worklogs (tempo.worklogs_flat)

Column	Type	Description
duration_seconds	UInt32	Time logged in seconds
account_id	String	Jira user account ID
user_name	String	Display name
start_date	Date	Date work was performed
project_label	String	Project label (maps to application)
issue_key	String	Jira issue key (e.g., PROJ-123)

Jira Issues (jira.jira_issues)

Column	Type	Description
issue_key	String	Jira issue key
issue_type	String	Bug, Task, Story, Incident, etc.
priority	String	P1, P2, P3, P4, P5
project_label	String	Project label
summary	String	Issue summary/title

Processing Pipeline

Step 1: Fetch Worklogs

Query tempo.worklogs_flat for the given period, joined with jira.jira_issues for issue metadata:

SELECT
    w.issue_key,
    j.summary AS issue_summary,
    j.issue_type AS issue_type_name,
    j.priority AS priority_name,
    toUInt32(w.duration_seconds) AS time_spent_seconds,
    w.start_date AS started
FROM tempo.worklogs_flat w
LEFT JOIN jira.jira_issues j ON w.issue_key = j.issue_key
WHERE w.project_label IN (?)
  AND w.start_date >= ?
  AND w.start_date <= ?
ORDER BY w.start_date, w.issue_key

Step 2: Application Mapping

Applications map to worklogs through the labels field:

• cdm.jira_applications.labels contains project labels (e.g., "ProjectA")
• tempo.worklogs_flat.project_label matches these labels
• One application may have multiple project labels

Step 3: Employee Exclusions

Some employees are excluded from billing:

Exclusion Type	Effect
overhead_only	Hours tracked but not billed
excluded	Hours completely ignored

Stored in the employee_exclusions table, matched by account_id.

Step 4: Minimum Billable Time

Each individual worklog is rounded up to 1800 seconds (30 minutes):

if worklog.duration_seconds < 1800:
    worklog.billable_seconds = 1800
else:
    worklog.billable_seconds = worklog.duration_seconds

This is a per-worklog rule, not per-issue. Multiple short worklogs on the same issue are each rounded independently.

Step 5: Line Item Categorization

Worklogs are categorized based on issue type and priority:

Line Type	Condition
incident_critical	Issue type = Incident AND priority in (P1, P2, P3) AND off-hours
incident	Issue type = Incident AND priority in (P1, P2, P3)
meeting	Issue type = Meeting
task	Default for all other issue types
support	Fixed line for SUP base amount
monitoring	Fixed line for monitoring services
overtime	Hours beyond monthly limit (SUP only)
off_hours	Non-critical work during off-hours

Step 6: Rate Tier Assignment

Each worklog gets the highest-priority applicable rate tier:

1. Critical + off-hours (p1_p3_off_hours): Incident P1-P3 outside business hours → multiplier 1.5
2. Critical (p1_p3): Incident P1-P3 during business hours → multiplier 1.0
3. Off-hours (off_hours): Any work outside business hours → multiplier 1.0
4. Standard (standard): Normal business hours → multiplier 1.0

Step 7: Aggregation

Worklogs with the same issue_key are aggregated into a single line item:

line_item.time_seconds = sum(worklog.duration_seconds)  // original time
line_item.billable_seconds = sum(worklog.billable_seconds)  // after min billable
line_item.quantity = billable_seconds / 3600  // hours
line_item.line_total = quantity * unit_price * rate_multiplier

Project Classifications

Some projects are classified as non-billable:

Classification	Effect
billable	Normal billing
internal	Excluded from client invoices
overhead	Company overhead, not billed
excluded	Completely ignored

Stored in project_classifications table. Seed data includes Proxima (internal), Education (internal), and Internal-Infra (overhead).

Data Freshness

ClickHouse data is synced by n8n workflows approximately every 15 minutes. The prefill pipeline always reads the latest available data, but there may be a delay of up to 15 minutes from when time is logged in Tempo.