Replacing Project Online without moving anyone off Microsoft Project

A case study — RMZ Project Timesheet Sync

Summary

| | |
|---|---|
| **Problem** | Microsoft Project Online was being retired. The organisation still needed timesheeting, and still needed the Microsoft Project desktop client for real scheduling. |
| **Constraint** | Planners could not be moved off `.mpp` files. That continuity *was* the product. |
| **Solution** | A four-component system: a VSTO add-in inside Microsoft Project, Dataverse as the system of record, SPFx web parts for entry and approval, and Power Automate for notification. |
| **Outcome** | Live and working. The add-in installs and syncs both ways, the web parts are deployed, the approval loop functions, and the P1/P2 backlog is closed. |
| **Role** | Solution architecture, full implementation across all four components, deployment and code signing, documentation. |
| **Stack** | C# / .NET Framework 4.8 · VSTO · Office COM interop · Dataverse Web API v9.2 · SPFx 1.22.1 · React 17 · TypeScript · Fluent UI v8 · Power Automate · ClickOnce |

1. The problem

Microsoft was retiring Project Online. Two capabilities went with it, and the organisation needed both.

The first was timesheeting — employees logging hours against project tasks, managers approving them. The second was the Microsoft Project desktop client, which the organisation's planners used for genuine scheduling work: work breakdown structures, baselines, resource levelling, critical path.

The obvious migration paths all failed the same test. Moving to a cloud PPM product meant retraining planners and abandoning years of .mpp files. Moving to a spreadsheet-based timesheet meant losing the link between hours and the plan entirely.

So the brief inverted: keep the planning exactly where it is, and rebuild only the timesheet layer around it.

2. The constraint that shaped every decision

Planners must keep using Microsoft Project desktop. Anything that requires them to abandon .mpp files is a non-starter.

This is not a preference dressed up as a requirement. It is the reason the project has value, and it drove three consequences that show up everywhere in the architecture:

1. Something has to read `.mpp` files. That means COM interop, which means running
2. inside Microsoft Project, which means a VSTO add-in on .NET Framework — not .NET 8, not
3. a service, not a cloud function.
4. The plan is the source of truth for structure, but not for hours. Tasks, resources
5. and assignments flow *out* of Project. Approved hours flow *back in*. Both directions
6. have to be safe.
7. Identity has to survive the round trip, and Microsoft Project makes that
8. surprisingly hard. More on this in §4.

3. Architecture

Four components, each with one job.

Microsoft Project desktop Dataverse SharePoint Online ┌─────────────────────┐ ┌──────────────────┐ ┌──────────────────────┐ │ VSTO add-in │ │ 10 tables │ │ 3 SPFx web parts │ │ ───────────── │ │ ────────── │ │ ────────────── │ │ Send Project ──────┼─────▶│ Projects │◀────▶│ Timesheet Manager │ │ Get Approved ◀─────┼──────│ Tasks │ │ Dashboard │ │ Validate │ │ Resources │ │ Setup │ │ Connect / Settings │ │ Assignments │ └──────────────────────┘ └─────────────────────┘ │ Periods │ │ Headers │ ┌──────────────────────┐ ┌─────────────────────┐ │ Lines │ │ Power Automate │ │ Schema Setup CLI │─────▶│ Configuration │─────▶│ 3 flows │ │ (owns all schema) │ │ Project Mgrs │ │ notify · digest · │ └─────────────────────┘ │ Line Mgrs │ │ period auto-create │ └──────────────────┘ └──────────────────────┘

The VSTO add-in is the only component that touches .mpp files. It is split so the logic worth testing has no dependency on COM or HTTP: a Core project holds the DTOs, the sync engine and the validator behind repository interfaces, with separate ProjectReader (COM) and Dataverse (Web API) projects on either side of that seam.

Dataverse is the system of record. Ten tables, and — deliberately — no Dataverse relationships between any of them. Every join is by a text "external UID" column carried over from Microsoft Project, backed by alternate keys. That looks wrong until you see why: it lets the add-in upsert by natural key over the Web API without resolving GUIDs first, which is what makes a batched sync of several thousand tasks feasible from a desktop add-in.

The SPFx web parts are where everyone except planners lives. Three of them — the Timesheet Manager (entry grid, timesheet list, approvals queue, finance view, period admin), a Dashboard, and a Setup web part for configuration and approver mapping.

The Schema Setup CLI is the only path that creates or alters a table or column. The add-in and the web parts read and write rows; neither can touch schema. That separation is what makes the environment reproducible.

4. The hard problems

This is the part worth reading. Each of these was a real defect or a real design decision, not a hypothetical.

4.1 Microsoft Project task IDs restart at 1 in every file

Microsoft Project assigns each task a UniqueID. It is unique *within one file*. Open a second plan and its first task is also UniqueID 1.

Sync two projects into one Dataverse environment and their tasks collide. Worse, they collide silently — the second sync overwrites the first project's rows, and nothing errors.

The fix has two halves. Task and assignment UIDs are composed with the project's UID, so task 42 becomes unambiguous. Resources are the exception: they are keyed on email/UPN instead, so the same person appearing in five plans is one resource record rather than five.

The same problem resurfaced on the return path. Get Approved Hours matches an approved line back to a plan assignment using the trailing integer of the external UID — so without a project scope on the fetch, another project's approved hours could be written onto this plan's tasks. The fetch is now scoped by project, and assignment matching prefers an exact assignment-UID match with task+resource only as a fallback.

Every part of the identity design traces back to one quirk of the desktop file format.

4.2 Two project managers approving at the same time

The approval model is two-stage and per-project:

1. An employee submits a week's hours → header becomes Submitted.
2. Their line manager approves (resolved from Azure AD directReports) → header becomes
3. Line Approved; the lines stay Submitted.
4. Each project manager then approves the lines for *their own* projects. A timesheet
5. spanning three projects shows three independent approval cards.
6. The header flips to Approved only when no project remains outstanding.

Step 3 is where it gets interesting. Every header transition uses optimistic concurrency — If-Match with the row's ETag, so a 412 becomes "reload and try again" rather than a silent overwrite. But if each project approval also wrote the header, two PMs clicking Approve within the same second would collide on that ETag and one would get a spurious failure on an action that should have succeeded.

So the header is deliberately not written on intermediate project approvals. It is written only on the final flip, when the last project clears. Rejection does the same thing from the other direction — rejectProjectLines writes the header *without* an ETag.

Two deliberate exceptions to the concurrency rule, both documented in the code, both existing because the general rule produced the wrong behaviour in a specific case.

A send-back from one PM returns only that project's lines; the other projects keep their approvals. On resubmission the header returns to Line Approved rather than Submitted — the line manager is not asked twice — and only the corrected project's PM is re-notified.

4.3 An ordering bug that silently destroyed data

Get Approved Hours writes approved hours onto the plan's assignments, then marks those Dataverse rows as Synced so they are not applied twice.

Originally it did those in the other order. If saving the .mpp then threw — file locked, disk full, Project busy — the rows were already flipped to Synced. Those hours were now unfetchable *and* not in the file. Gone.

The rule now is explicit and commented: save the file first, then mark synced. A crash between the two costs a duplicate application at worst, which is recoverable. The other ordering costs data, which is not.

The same class of bug appears twice more in the closed-defect list: entries for the same assignment and day overwriting rather than summing, and a token expiring mid-session being reused. All three are cheap to write and expensive to find.

4.4 Notifications as a schedule, not a trigger

The obvious design for "tell the PM their project was approved" is a Dataverse trigger on the timesheet line table.

That breaks immediately. A single project approval writes *dozens* of line rows in one $batch changeset. A line-triggered flow fires once per row — so one approval sends one manager thirty identical emails.

So the per-project notification flow is a 15-minute scheduled digest instead, reading lines changed since a watermark stored in the configuration table. It owns "your project was approved" and all send-back mail. The header-triggered flow, where one action really does mean one row, stays a trigger.

Choosing a schedule over a trigger looks like a step backwards until you count the emails.

4.5 The largest table had no index and no constraint

rmz_timesheetline is one row per person, per task, per day. It is the biggest table by a wide margin, and it had no alternate key at all — no index on the columns every query filtered by, and nothing stopping a duplicate day-line except JavaScript in the browser.

Adding header + task + date as an alternate key does both jobs at once: it indexes the hot query path and makes a duplicate impossible at the database rather than only in the UI. Because the task UID is already project-scoped, the project does not need to be part of the key.

The header table got the same treatment — resource + period — which is what stops two browser tabs racing to create the same person's timesheet twice.

4.6 Declarative schema, and a static-initialiser trap

Schema is a static C# list. SchemaDefinitions.cs declares each table as a record — logical name, columns, alternate keys — and the setup service walks that list making the environment match. To add a column you add it to the list and re-run. There is no migration script and no ordering to maintain.

Except in one place. The choice-option lists (ApprovalStatus, SyncStatus, PeriodStatus) are declared before the Tables list, and there is a comment in the file explaining why: static property initialisers run in textual order. Declared after, they would still be null when Tables initialises — and every choice column would be created with no options at all. That failure does not surface at setup time. It surfaces weeks later as "value outside the valid range" the first time someone submits a timesheet.

The run is idempotent throughout. It also handles its own likely failure gracefully: creating an alternate key fails if the existing data already violates it, so that failure is reported and the run continues rather than aborting an otherwise-fine migration. Clean the duplicates, re-run, pick up the key.

4.7 Making it fast enough on real data

A large-dataset audit late in the build replaced full-table scans with paged reads (@odata.nextLink with Prefer: odata.maxpagesize), and parallelised chunked reads with bounded concurrency — mapLimit at six. Unbounded parallelism had been opening 40+ connections on a large approval queue.

On the add-in side, tasks, resources and assignments are read from COM in a single pass with an indexed assignment lookup, rather than walking the collections repeatedly. The same audit fixed a silently truncated task picker and a 41 MB export.

4.8 Two signatures, and the order they go in

Distribution is ClickOnce from Azure Blob Storage. Getting it to install cleanly needed two separate signatures, for two separate gatekeepers:

• The ClickOnce manifests satisfy the Office Customization Installer. Without this,
• users see a publisher-trust failure with no Install button at all.
• Authenticode on `setup.exe` and the payload satisfies SmartScreen. Without this,
• "Windows protected your PC — Unknown publisher".

And they interact: the manifests store a hash of every payload file, so the payload has to be signed *before* the manifests are re-hashed. The publish script got this wrong twice — once signing the DLL after the manifests had already hashed it, once building a non-web bootstrapper that could not install from a URL.

Certificates are OV, held in a cloud HSM rather than as a .pfx in the repository. The dev .pfx that existed early on was removed.

One constraint worth knowing before choosing ClickOnce: the install URL is baked into every installed copy. Moving the hosting means republishing *and* reinstalling on every machine.

5. Security: a documented accepted risk

The web parts call Dataverse as the signed-in user. That is the right call — every read and write is subject to that person's own role privileges, and there is no service account whose compromise would expose everything.

It also creates a gap that no amount of configuration closes, and the honest thing was to write it down rather than imply it wasn't there.

Dataverse security roles are row-level. They decide *which rows* a user may touch. They cannot express *which column values are legal*, or *who the designated approver for this particular project is*. Since a submitter needs Write on their own lines to edit a draft, the same privilege lets them set the approval status column directly against the Web API, bypassing the app entirely.

Two residual scenarios follow:

| | Scenario | Why roles don't stop it |
|---|---|---|
| **R1** | A submitter approves their own lines from any API client. | They need Write on their own lines to edit a draft; the same privilege permits the status value. |
| **R2** | An approver approves a timesheet they are not the mapped approver for. | Role scope is by ownership and business unit, not by the project-manager mapping the app uses. |

The decision was to accept and detect, not to prevent — no plugin or elevated custom API at this stage. What makes that defensible is that it comes with three things:

1. Auditing turned on at environment, table and column level, with a deliberate
2. retention period, on exactly the columns that matter.
3. A monthly exception report looking for two specific patterns: an approved line whose
4. approver is also the submitter, and an audit record whose real modifiedby differs from
5. the rmz_approvedby the app recorded. The report is sent even when empty, so a
6. silently broken flow is visible — "0 exceptions" is a result; no email is not.
7. A written escalation trigger. Revisit this decision if approved hours start driving
8. billing or payroll, if an external audit requires preventative control, or if the user
9. base extends beyond trusted internal staff.

One detail from that work is worth repeating because it is a genuine security-design insight: rmz_approvedby, rmz_approvedon and the status log are all written by the client, so a forged call can put any name in them. The trustworthy record of who actually made a change is Dataverse's own modifiedby. The app's own audit trail is for humans; the platform's is for auditors.

There is also a testing escape hatch — a allowSelfApproval flag that disables the only self-approval guard. It was deliberately moved out of central configuration and onto the web part instance, precisely because one edit in central config disabled the guard for everyone at once. The trade-off is that the monthly review now has to check every page hosting the web part rather than one value — which is the correct trade, and is documented as such.

6. Outcome

Live and working. The add-in installs and syncs in both directions, the web parts are deployed, the two-stage approval loop functions on real data, and the notification flows are in place. The P1 and P2 backlogs are closed; CI publishes the ClickOnce build.

What remains open is four P3 items, all of them honest about their own cost: TypeScript strictness flags, the C# sync running on the UI thread (a COM constraint, mitigated by batching), an O(n×m) check in the validator that only matters on very large plans, and a decision on automated testing.

On that last point: there are no automated tests. A hand-rolled console test project was deleted rather than left in place implying coverage it never ran. If tests are added, the note in the backlog says where to start — xUnit over the mapper and the validator, the two pieces that are pure functions over DTOs and already sit behind the Core seam that was built for exactly this.

7. What was deliberately not built

Two things, and the distinction matters.

Approval delegation (manager-on-vacation, matrix approval) was deferred by the customer, then skipped by decision. It is recorded in the backlog with its revival cost already priced: the User.Read.All Graph permission with tenant-admin consent, a delegation table, a management UI, and approval-scope logic. It stays listed specifically so it is not re-proposed as though it were new.

The live change request is revenue and billing — quoted versus actual hours by department and client, variance reporting, a client portfolio view. It has not been built because four business questions are unanswered, and none of them is an engineering question:

1. How does a task map to a department? The plan doesn't record it. Department exists at
2. the *person* level, read from the Project resource field — so actual hours can be
3. attributed, but quoted hours cannot.
4. Does "actual hours" mean submitted or approved? Real-time visibility and revenue
5. recognition pull in opposite directions.
6. Where does the client identity come from? Nothing groups projects into clients today.
7. Is the department restriction real security or screen filtering? The answer changes
8. the cost materially.

Notably, the *data* is largely already there — quoted hours sync into rmz_baselineworkminutes from Baseline10 Work. The blocker is definitional, not technical, and saying so was more useful than building the wrong report.

8. What I'd take from this

The constraint was the product. Every instinct said "migrate them to a modern PPM tool." The value was in not doing that. Identifying which constraint is load-bearing — and refusing to design it away — mattered more than any individual technical decision here.

Legacy file formats leak into your data model. A single quirk of .mpp files — UniqueID restarting at 1 — dictated the identity scheme across ten tables, both sync directions, and the return-path matching logic. Integration work is mostly this.

Ordering bugs are the expensive ones. Save-before-mark-synced, choice-options-before- tables, sign-payload-before-hashing-manifests. All three are one line in the wrong place. None of them fails loudly. Each one was found the hard way, and each now carries a comment explaining why the order is what it is.

Writing down what you did not do is part of the deliverable. The accepted-risk section, the skipped-delegation entry, the four unanswered questions, the deleted test project — a document that only lists what works is not a system anyone can safely inherit.

*Compiled from the source, architecture documentation and backlog of github.com/rmapps006/RMZ.ProjectTimesheetSync.*