Same employees. Same substrate. The hours that turn compensation into actual gross.
The Workforce Triangle: HR owns identity, Payroll owns comp events, Time owns the hours. Three modules reading from one substrate. No reconciliation because there is nothing to reconcile — the employee who clocks in is the same employee who gets paid, resolved from the same Dim_Employee row. Here's how.
Time & Attendance closes the triangle. HR defines who works here. Payroll defines what they earn. Time defines when and how long they worked. All three read from the same substrate.
Every punch is an immutable Fact_TimePunch. Clock-ins, clock-outs, breaks, corrections — all events, never mutations. The provenance grain: where did this hour claim come from?
effectiveDate is when the punch happened. recordedDate is when we knew about it. The correction (FTP-003-003) was effective on March 5 but recorded the next morning — that's bitemporality. See Section 7.{
"factId": "FTP-003-001",
"factType": "Fact_TimePunch",
"employeeId": "EMP-003",
"effectiveDate": "2026-03-05T14:00:00Z",
"recordedDate": "2026-03-05T14:00:05Z",
"sourceTimezone": "America/Chicago",
"punchType": "CLOCK_IN",
"source": "HARDWARE",
"sourceRecordId": "hw-priya-20260305-in",
"geolocation": {
"lat": 30.267,
"lng": -97.743,
"accuracy": 5
},
"authoredBy": "EMP-003",
"correction": {
"isCorrection": false,
"correctsFactId": null,
"reason": null
}
}{
"factId": "FTP-003-002",
"factType": "Fact_TimePunch",
"employeeId": "EMP-003",
"effectiveDate": "2026-03-05T22:30:00Z",
"recordedDate": "2026-03-05T22:30:08Z",
"sourceTimezone": "America/Chicago",
"punchType": "CLOCK_OUT",
"source": "HARDWARE",
"sourceRecordId": "hw-priya-20260305-out-orig",
"geolocation": {
"lat": 30.267,
"lng": -97.743,
"accuracy": 4
},
"authoredBy": "EMP-003",
"correction": {
"isCorrection": false,
"correctsFactId": null,
"reason": null
}
}{
"factId": "FTP-003-003",
"factType": "Fact_TimePunch",
"employeeId": "EMP-003",
"effectiveDate": "2026-03-05T23:15:00Z",
"recordedDate": "2026-03-06T15:00:00Z",
"sourceTimezone": "America/Chicago",
"punchType": "CORRECTION",
"source": "MANAGER_ENTRY",
"sourceRecordId": "cx-priya-20260305-out-fix",
"geolocation": null,
"authoredBy": "EMP-001",
"correction": {
"isCorrection": true,
"correctsFactId": "FTP-003-002",
"reason": "Actual departure 5:15pm, original punch at 4:30pm was premature badge tap."
}
}{
"factId": "FTP-003-004",
"factType": "Fact_TimePunch",
"employeeId": "EMP-003",
"effectiveDate": "2026-03-17T14:00:00Z",
"recordedDate": "2026-03-17T14:00:03Z",
"sourceTimezone": "America/Chicago",
"punchType": "CLOCK_IN",
"source": "HARDWARE",
"sourceRecordId": "hw-priya-20260317-in",
"geolocation": {
"lat": 30.267,
"lng": -97.743,
"accuracy": 5
},
"authoredBy": "EMP-003",
"correction": {
"isCorrection": false,
"correctsFactId": null,
"reason": null
}
}{
"factId": "FTP-003-005",
"factType": "Fact_TimePunch",
"employeeId": "EMP-003",
"effectiveDate": "2026-03-17T23:00:00Z",
"recordedDate": "2026-03-17T23:00:06Z",
"sourceTimezone": "America/Chicago",
"punchType": "CLOCK_OUT",
"source": "HARDWARE",
"sourceRecordId": "hw-priya-20260317-out",
"geolocation": {
"lat": 30.267,
"lng": -97.743,
"accuracy": 4
},
"authoredBy": "EMP-003",
"correction": {
"isCorrection": false,
"correctsFactId": null,
"reason": null
}
}Fact_TimeAllocation is the business-consumption grain. Punches are raw events; allocations are what the business consumed — duration, attribution, billable flag, and a citation chain back to the punches that produced them.
| Employee | employeeId | Date | Duration | Source | Category | Billable | Cited facts |
|---|---|---|---|---|---|---|---|
| Priya Shankar | EMP-003 | 2026-03-05 | 555m | HARDWARE | internal | No | FTP-003-001, FTP-003-002, FTP-003-003 |
| Priya Shankar | EMP-003 | 2026-03-17 | 540m | HARDWARE | internal | No | FTP-003-004, FTP-003-005 |
| Jordan Webb | EMP-004 | 2026-03-10 | 480m | HARDWARE | internal | No | FTP-004-001, FTP-004-002, FTP-004-003, FTP-004-004 |
| Theo Grant | EMP-009 | 2026-03-19 | 480m | HARDWARE | internal | No | FTP-009-001, FTP-009-002 |
| Noor Al-Sayed | EMP-012 | 2026-03-21 | 480m | HARDWARE | internal | No | FTP-012-001, FTP-012-002 |
| Maya Okafor | EMP-001 | 2026-03-10 | 480m | SELF_REPORT | internal | No | self-report |
| Daniel Reyes | EMP-002 | 2026-03-15 | 420m | SELF_REPORT | internal | No | self-report |
| Clara Nilsson | EMP-005 | 2026-03-12 | 480m | SELF_REPORT | internal | No | self-report |
| Hana Takeda | EMP-007 | 2026-03-14 | 450m | SELF_REPORT | internal | No | self-report |
| Elena Duarte | EMP-008 | 2026-03-11 | 420m | SELF_REPORT | internal | No | self-report |
| Amara Johnson | EMP-010 | 2026-03-10 | 480m | SELF_REPORT | contractor | YES | self-report |
| Luca Ferrara | EMP-011 | 2026-03-10 | 360m | SELF_REPORT | contractor | YES | self-report |
| Luca Ferrara | EMP-011 | 2026-03-17 | 420m | SELF_REPORT | contractor | YES | self-report |
Hours from Time. Rate from Payroll. Identity from HR. One query. Three modules. No sync.
-- Three modules. One query. No sync. No nightly job.
SELECT
hr.employee_id,
hr.first_name || ' ' || hr.last_name AS employee_name,
SUM(ta.duration_minutes) / 60.0 AS total_hours,
comp.base_amount AS hourly_rate,
(SUM(ta.duration_minutes) / 60.0)
* comp.base_amount AS labor_cost
FROM hr.dim_employee AS hr
JOIN payroll.dim_current_comp AS comp
ON comp.employee_id = hr.employee_id -- same identity, no join key translation
JOIN time.fact_time_allocation AS ta
ON ta.employee_id = hr.employee_id -- same identity again
WHERE hr.status_parent IN ('Active')
AND comp.base_cadence = 'HOURLY'
GROUP BY hr.employee_id, hr.first_name, hr.last_name, comp.base_amount;hr.employee_id = comp.employee_id = ta.employee_id.Period-over-period hours delta, fully accounted for by cited facts.
Real-world time scenarios the substrate handles. Three edge cases, three invariants, zero data loss.
Theo clocks in at 10:00 PM CST on March 18 and clocks out at 6:00 AM CST on March 19. The shift crosses midnight.
effectiveDate is stored in UTC. The workday boundary resolves via employeeTimeZone (America/Chicago). The substrate preserves both the UTC instant and the local context — no ambiguity about which calendar date the shift belongs to.
Daniel self-reports his March 15 hours three days late, on March 18. The substrate preserves both dates and computes retroactiveDays automatically.
retroactiveDays = floor(recordedDate − effectiveDate). The substrate never silently backdates — it preserves both timestamps and flags the gap.
Priya's original CLOCK_OUT at 4:30 PM was a premature badge tap. Her manager enters a correction the next morning with the actual departure time of 5:15 PM. The original punch is preserved — never mutated, never deleted.
The allocation (FTA-003-001) resolves to the corrected duration of 555 minutes and cites all three punch facts in its citation chain. Nothing was rewritten.
Every decision that shaped this module is on the record.