Deriving Employee Project Dates From Labor History Instead of Manual Entry

A client was in the middle of a CRM data cleanup effort. They wanted accurate start and end dates for every employee on every project — role history, effectively — but the data simply didn’t exist in Vantagepoint. With thousands of projects on the books, many of them years old, asking employees to go back and manually populate that history wasn’t realistic. It wasn’t a discipline problem. It was a scale problem, and manual entry wasn’t going to solve it going forward either.

The insight: the data already exists, just not where you’d look for it

Vantagepoint doesn’t track “when was this person on this project” as a first-class field. But it does track labor postings — every time someone charges time to a project, that’s a timestamped record of them being active on it. If someone posted time to a project, that’s a real signal of when they were on it. The start and end dates weren’t missing. They were just implicit, buried in the Labor LD table instead of sitting in a field anyone could query directly.

That reframing is most of the solution. Once start/end dates become “first and last labor posting date per employee per project,” it’s a derivation problem, not a data-entry problem.

Workflow-only, on purpose

I built the solution as a scheduled workflow rather than a stored procedure — a deliberate choice, not a default. A stored procedure would have been faster to run, but it puts the logic behind a wall the client can’t touch without a dev cycle. Workflow-only keeps it inside Vantagepoint’s own configuration layer, adjustable by the client’s own admins later.

I was upfront about what that choice costs. A stored procedure could have backfilled the entire historical dataset in about a minute. The workflow-only version — running project by project, employee by employee — took roughly two days to process about 15,000 won/lost projects from the last three years, around 150,000 employee-project rows. I named that tradeoff on the call before building anything. The client took the deal; the flexibility mattered more to them than the runtime.

The harder problem: dormancy

The naive version of this workflow would just skip dormant projects on every recurring run — no new activity, nothing to update. But that’s wrong in a specific way: if a project goes dormant shortly after someone posts time to it, a blanket exclusion misses that final posting entirely, and the derived end date goes stale.

The fix is a dormancy window instead of a blanket exclusion. Anything dormant longer than the client’s posting cadence — about two weeks, in their case — gets skipped on the recurring run. Anything more recent still gets evaluated, so a project that just went quiet doesn’t lose its most recent posting data before the workflow catches up to it.

Getting the window right required understanding how Vantagepoint’s Labor LD table structures its posting period field — not something documented anywhere, but necessary to write index-aware SQL that stays performant instead of scanning the full labor history on every run.

Rescoping after the backfill

The two-day runtime number is real, but it’s also misleading if you don’t separate it from the ongoing job. That number was for the one-time historical backfill — three years of data, all at once. Once that backfill completed, I re-scoped the recurring workflow to active projects only. Dormant projects aren’t generating new labor postings, so there’s nothing left that could change their dates. Re-processing settled data on every cycle would just be wasted work.

That rescoping cut the ongoing runtime down substantially from the backfill, without touching the workflow-only architecture decision I’d already committed to with the client.

Where it landed

The client settled on a monthly recurring cadence, tied to how often labor actually posts against their projects — the dates primarily feed resumes and proposals, where month-level accuracy is what matters, not daily precision. A manual trigger button, scoped to a single project, also got added for cases where someone needs an immediate update rather than waiting on the schedule.

They now have accurate, automatically maintained start and end date data across thousands of historical projects — a dataset that didn’t exist in any reliable form before — feeding directly into resume and proposal workflows with no manual entry required.

The technical mechanics here aren’t complicated on their own. What made the solution hold up was treating the runtime, the dormancy logic, and the post-backfill rescoping as design decisions worth naming out loud, rather than details to bury in the implementation.