How to reliably check in PowerShell whether a WSUS update (by UpdateId) is fully downloaded, consistently with what the WSUS console shows?

Hi,

I'm working on a PowerShell pipeline that performs targeted daily exports of a WSUS database (SUSDB) and its associated WsusContent files. As part of the filtering step, I need to exclude updates that haven't been fully downloaded yet — i.e., updates that would show 0% download in the WSUS console — to avoid exporting incomplete content. The updates I work with are all approved by the server via automatic approval rules.

My current approach

My current implementation works in two steps:

Step 1 – Transitive closure

Starting from the root UpdateId, I resolve its latest RevisionID via tbUpdate / tbRevision, then perform a BFS over tbPrerequisiteDependency and tbBundleDependency to collect all dependent RevisionIDs (prerequisites + bundles).

Step 2 – File state check

I insert the collected RevisionIDs into a temp table, then join tbFileForRevision and tbFileOnServer to count how many associated files have DesiredState = 3 and ActualState = 12. If all files satisfy both conditions, I consider the update as fully downloaded.

The value ActualState = 12 was observed empirically in my logs and appears to correspond to fdsLocallyPublished, meaning the file is physically present on the server.

The problem

For updates that arrived the same day and show 0% download in the WSUS console, my script incorrectly reports them as fully downloaded — because it finds associated files with DesiredState = 3 and ActualState = 12. This is the core discrepancy I'm trying to understand and fix.

I also explored the PowerShell WSUS API (via Get-WsusServer, Get-WsusUpdate, etc.) but could not find any property or method on the update objects that reliably reflects the download state as shown in the console.

My questions

1. Is tbFileOnServer.ActualState = 12 (fdsLocallyPublished) truly sufficient to conclude that a file is physically present on disk, or can a file reach that state before actually being fully written to WsusContent?
2. Is DesiredState = 3 the right filter to restrict to files that are expected to be present for an approved update?
3. Could the discrepancy be caused by file sharing between updates — i.e., files that are already present on disk (from a previously downloaded update) and are referenced by the new update, causing my script to see ActualState = 12 even though the new update itself hasn't been downloaded yet?
4. Is there a more robust way — either via SUSDB or any other mechanism — to determine whether a specific update has been fully downloaded by the server, consistently with what the WSUS console displays?

Any insight or pointer to relevant documentation would be greatly appreciated. Thanks!