You're querying Voyager's approval-workflow engine, you find wf_action — the table holding the stored-procedure hooks and notification bindings a workflow step fires — and you spot a column called hWorkflow with a declared foreign key straight to wf_header. It looks like the obvious join. It returns nothing.
Not "sometimes nothing." Nothing, full stop, because on a verified install wf_action.hWorkflow is 0 on every row in the table. The column exists, the FK is declared, and it's dead weight. This is a documented trap, not a one-off data-quality issue on one client's database.
Two sides of the workflow engine, and where wf_action sits
Voyager's approval-workflow engine splits into a definition side and an instance side, and it's worth being clear on which one you're in before you start joining.
The instance side — wf_tran_header and wf_tran_step — is the running record: one workflow attached to a specific amendment or record, with a row per step visit as it moves through approval. That's where you'd look to answer "who approved this and when."
The definition side is the setup behind it: wf_header is the workflow master (iType uses the same ObjectType registry as instance headers — 525 is Amendment). wf_step (linked to wf_header via hWF -> wf_header.hMy) holds the step definitions — sequence, approval/completion/rejection/correction markers, SLA days, a custom validation stored procedure, and an email template reference. wf_step_next (hStep -> hNextStep) is the actual transition graph between steps, and it doubles as the authorization matrix: role gates, amount bands, approver counts, conditions.
wf_action lives on this definition side too. It's where a step's stored-procedure hooks (sDBProcedure) and notification bindings (hNotification -> Notification) are configured — the automation and messaging a step triggers, not who ran it.
The trap: hWorkflow doesn't do what the FK says it does
The declared foreign key tells you wf_action.hWorkflow should point at wf_header.hMy — action rows belonging to a workflow, joined directly at the workflow level. On a verified install, that column is 0 on every row. Join through it and you get zero results, silently — no error, no constraint violation, just an empty set that looks like "this workflow has no configured actions" when it isn't true at all.
The real, working link is one level down: wf_action attaches to a workflow exclusively through hWF_step -> wf_step.hMy. You join to the step, not the header, and the header comes along for free through the step's own hWF reference back to wf_header.
SELECT
ws.hMy AS step_id,
ws.sName AS step_name,
wa.sDBProcedure AS stored_proc,
wa.hNotification AS notification_id
FROM wf_header wh
JOIN wf_step ws ON ws.hWF = wh.hMy
JOIN wf_action wa ON wa.hWF_step = ws.hMy
WHERE wh.hMy = @workflow_header_id
AND (ISNULL(wa.sDBProcedure, '') <> '' OR ISNULL(wa.hNotification, 0) <> 0)
That last line matters as much as the join does. wf_action also carries no-op placeholder rows — no stored procedure, no notification, nothing configured — and if you don't filter them out, a query meant to show "what fires on this step" gets cluttered with rows that fire nothing.
Notifications have their own version of this trap
There's a second wrinkle worth knowing once you're joining through hWF_step correctly: workflow notifications commonly bind through wf_action.hNotification rather than through a separate NotificationWorkflow table you might expect to hold that link instead. If your first instinct is to look for notification wiring anywhere but wf_action, you'll spend time searching a table that isn't the one doing the work on that install.
Why this is easy to get wrong the first time
Nothing about wf_action.hWorkflow looks suspicious from the schema alone. It has a name that matches the concept, a declared foreign key pointing at exactly the table you'd expect, and no comment anywhere warning it's unreliable. The only way to know it doesn't work is to have run the query against a live install and watched it come back empty — which is exactly the kind of fact a static schema diagram or a generic "Yardi SQL scripting" guide has no way to carry, because it's a behavioral trap, not a structural one.
wf_header.bActive has a cousin trap worth flagging while you're in this area: it's a dead column, always 0, regardless of whether the workflow is actually retired. The live retirement flag is the lowercase binactive (0 = active, -1 = retired). If you're filtering for active workflow masters, that's the column to use — and don't filter workflow instance queries through the master's active flag at all, since retiring a definition doesn't cancel workflows already in flight.
The pattern behind the trap
It's tempting to treat wf_action.hWorkflow as a one-off oddity, but it fits a broader pattern in Voyager's schema: a declared foreign key describes what a column is supposed to reference, not necessarily where the working data actually lives. wf_header.bActive sitting dead next to a working binactive is the same shape of problem in the same corner of the schema — a plausible-looking column that isn't the one doing the job. Neither trap throws an error. Both just quietly return the wrong answer, or no answer, while looking like a normal, well-formed query.
That's the real risk with this class of trap: a query built on wf_action.hWorkflow will run cleanly, return an empty result set, and give no indication that anything is wrong. It reads as "this workflow has no configured actions or notifications," which is a perfectly reasonable thing for a workflow to have, so there's no obvious signal telling you to go check the join. The only way to catch it is already knowing the column doesn't behave the way its name and its foreign key suggest — which is exactly the kind of fact that has to be verified once, on a live install, and then carried forward as documented knowledge rather than rediscovered by every person who writes this query for the first time.
Verified, not guessed
PropETL's SQL Assistant carries the wf_action linkage trap — along with the rest of the workflow engine's definition-side conventions — as execution-verified knowledge, not something Claude has to reconstruct from a foreign key that turns out to be wrong. Ask it to draft a workflow-configuration query and it joins through hWF_step and filters the no-op rows from the start, instead of handing you back an empty result set to debug.
It's included in every paid tier, including the free 7-day trial — no credit card required.
