The Integration That Doubled the Hours

The symptoms point to a single structural cause with several amplifiers: two sources of truth writing to the same assignment records without a reconciliation key or effective dating.

Duplicates. The HR feed creates assignments from organizational data while resource managers create assignments manually, and the hourly rule engine also writes. With no unique key (person + project + period) and no idempotency, each source creates its own record. 11% duplication is consistent with one cycle of overlap. The nightly full-file load makes it worse: without effective dating, a record that should have closed is re-sent and re-created.

Ghost assignments. People appearing on projects they were never assigned to is the same mechanism viewed from the other side - the HR feed's organizational mapping may be defaulting people onto projects based on department or cost center, and those defaults are being treated as assignments.

Cost overstatement. Duplicate assignment records cause duplicate labor cost postings when time is allocated by assignment. A 6% overstatement against payroll control is consistent with roughly half the duplication rate hitting costed roles. The link is: duplicate assignment → duplicate allocation basis → duplicate cost posting → timesheet rejection when the system cannot resolve which assignment to charge.

Rejection spike. 27% rejection is the visible symptom of unresolved assignment ambiguity: the timesheet cannot determine a valid charge target, so it rejects. The rejection rate is a diagnostic, not the disease.

Effective dating. Terminated employees persisting one cycle is a separate but related defect: without effective dates, the system cannot distinguish 'active as of today' from 'was active when the file was built.'

Fix the data model before tuning the pipeline, and stage the rollout so the business is not exposed twice.

Design changes.

1. Single system of record per attribute. HR owns person and employment status; the project system owns assignments. The HR feed should not create assignments - it should create or update people and org data only.
2. Unique key and idempotency. Enforce a composite key (person, project, assignment period) with upsert semantics so repeated loads cannot duplicate.
3. Effective dating. Carry valid-from and valid-to on every record; the consuming system selects the record effective as of the transaction date. This resolves the terminated-employee lag.
4. Change-data-capture or delta feed instead of a nightly full file, with a reconciliation count each cycle.
5. Manual entry controls. Manual assignments require an approver and are flagged as manual so they can be reconciled against source.
6. Reconciliation control. Daily automated comparison of assignment count and labor cost against payroll control total, with an alert threshold (for example, variance above 1%).

Sequencing.

• Phase 1: stop the bleeding - disable HR-driven assignment creation, run a de-duplication and cleanup, and reconcile cost to payroll. Confirm the control total ties.
• Phase 2: implement effective dating and the unique key in a test environment with a full production data copy; validate with a parallel run.
• Phase 3: switch to delta feed with reconciliation monitoring; keep the old path read-only for one cycle.
• Phase 4: re-enable automated assignment creation only after rejection rate and duplicate rate are within tolerance for two consecutive cycles.

Measure success on rejection rate, duplicate rate, and cost variance to payroll - not on go-live date.