Voyager's approval-workflow engine — the tables behind amendment approvals, contract sign-offs, and every other multi-step routing process in the system — splits cleanly into two sides that share a naming convention and almost nothing else. One side is the instance tables: what actually happened, on a specific record, as it moved through approval. The other is the definition tables: the master setup that says what steps exist, who can approve what, and what happens at each transition. Confuse the two and you'll write a query that runs fine and answers the wrong question.
The instance side: wf_tran_header and wf_tran_step
wf_tran_header is one workflow instance, attached to a specific record through a polymorphic pair: iType (the same ObjectType registry Voyager uses elsewhere — 525 is Amendment, which in practice covers new contracts, assignments, and terminations, since all of those are CommAmendments records) plus hRecord, the pointer to the actual record under approval.
wf_tran_step is where it gets easy to misread. Each row is a step visit, not a step definition — if a step gets sent back and re-entered, that produces a second row for the same step, not an update to the first. That has a direct consequence for how you order them: chronological order is dtStart, never iSequence. iSequence on the step-visit rows has gaps and repeats, because only steps that were actually visited materialize a row at all — the sequence numbers belong to the routing graph, not to a clean 1-2-3 timeline of what happened.
Steps advance by completion, not by a dedicated approval stamp. The person who actioned a step — including an approval-named step — lands in hUserComplete/dtComplete. The columns that look like the obvious place to check approval — dtApprove, hUserApprove, dtDeny, hUserDeny, bApproval — commonly go entirely unused. In practice, treat whoever completed an approval step as the approver, and expect rejection to be modeled as routing to a "Rejected"-style step rather than a deny flag being set — profile the specific install before trusting the approve/deny columns for anything.
bCurrent marks the current step, but terminal steps — the activated, approved, or rejected endpoints of a workflow — stay bCurrent even after they're done, because dtComplete gets set on them too. So "awaiting action" isn't bCurrent <> 0 alone. It's:
bCurrent <> 0 AND dtComplete IS NULL
Anything that filters on bCurrent by itself will count finished workflows as still pending.
The definition side: wf_header, wf_step, wf_step_next
wf_header is the workflow master — the template, not an instance. It uses the same iType/ObjectType registry as wf_tran_header. The first trap here is a genuinely dead column: wf_header.bActive reads 0 on every row observed live. It isn't a soft-deprecated field you can still lean on sometimes — it's dead. The actual retirement flag is the lowercase binactive (0 = active, -1 = retired), and dtInactive is typically unpopulated even on retired masters. Filter active workflows with:
ISNULL(binactive, 0) = 0
And retiring a master doesn't touch in-flight instances — never filter an instance-side query through the master's active flag, because a workflow that's already running keeps running whether or not its definition is still active.
wf_step (linked to wf_header via hWF) holds the actual step definitions: iSequence for display order, boolean markers (bApproval, bCompletionStep, bRejectionStep, bCorrectionStep), an iDays SLA, a per-step validation stored procedure (sCustomValidationSP), and an email template override (hEmailTemplate).
The routing columns on wf_step itself — hStep_appr, hStep_deny, hStep_Complete — can be entirely unused. The real transition graph lives in a separate table, wf_step_next (hStep → hNextStep), and it does double duty: it's also the authorization matrix. Each transition can be gated by a role (hrole → Role.hMy, where hrole = 0 means the move is unrestricted), plus amount and budget bands (cAmountFrom, cAmount, dOverbudgetPerc), an approver count, and a free-text condition. When a separate role-grant table (wf_step_approval) is empty on a given install — which it can legitimately be — the wf_step_next role gates are the approval model. Don't assume wf_step_approval is populated just because approvals clearly work; check both before concluding the authorization data is missing.
The linkage trap: wf_action doesn't join through wf_header
wf_action holds the operational side of a step — stored-procedure hooks (sDBProcedure) and notification bindings (hNotification → Notification). It carries a declared foreign key to wf_header via hWorkflow, and on a verified live install, hWorkflow was 0 on every single row despite that declared FK. Joining wf_action to wf_header through hWorkflow silently returns nothing — no error, just an empty result that looks like the install has no configured actions.
Actions attach exclusively through hWF_step → wf_step.hMy. Always join through the step, never through the header column the FK suggests. It's also worth filtering out no-op placeholder rows before treating a count as meaningful — rows with an empty sDBProcedure and no hNotification set carry nothing operational:
WHERE ISNULL(sDBProcedure, '') <> '' OR ISNULL(hNotification, 0) <> 0
Workflow notifications can bind through wf_action.hNotification directly rather than the separate NotificationWorkflow/NotificationDet binding tables — both surfaces can legitimately exist and both can legitimately be empty, so verify per install before diagnosing either as broken.
Email templates: two levels, step wins
Templates resolve at two levels — wf_header.hEmailTemplate is the workflow default, wf_step.hEmailTemplate is a per-step override — both pointing to email_template.hMy (sName, sSubject with #token# substitution, sBody/sBodyHtml, sCC/sBCC, hSenderRole). When resolving which template actually fires for a step, the step-level override wins over the workflow default:
COALESCE(NULLIF(ws.hEmailTemplate, 0), NULLIF(wfh.hEmailTemplate, 0))
Putting the two sides together
The practical shape of an instance-activity query — every workflow touched in a window, its step-visit history in true chronological order, and who actioned each step — joins wf_tran_header to wf_tran_step on hTranHeader, orders by dtStart, and resolves the completer through PMUSER:
SELECT
wfh.hMy AS workflow_id, wfh.sName AS workflow_name,
wfs.sName AS step_name, wfs.dtStart AS step_started, wfs.dtComplete AS step_actioned,
uc.UNAME AS actioned_by,
CASE WHEN wfs.bCurrent <> 0 AND wfs.dtComplete IS NULL THEN 'PENDING'
WHEN wfs.bCurrent <> 0 THEN 'TERMINAL'
ELSE 'DONE' END AS step_state
FROM wf_tran_header wfh
JOIN wf_tran_step wfs ON wfs.hTranHeader = wfh.hMy
LEFT JOIN PMUSER uc ON wfs.hUserComplete = uc.HMY
WHERE wfh.iType = 525 -- ObjectType 525 = Amendment
ORDER BY wfh.hMy, wfs.dtStart
And the definition-side blueprint — which steps exist, in what order, with what SLA and validation, for a given object type — joins wf_header to wf_step on hWF, filtered by the real binactive flag rather than the dead bActive column.
Why this is worth writing down
None of these traps are documented anywhere in the schema itself — wf_header.bActive doesn't carry a comment explaining that it's dead, and wf_action.hWorkflow's declared FK gives no hint that it's unreliable in practice. They only surface when someone builds a workflow report, gets an empty or wrong result, and has to trace it back to the actual column behavior on a live install. That's exactly the kind of verified, table-level knowledge PropETL's SQL Query Assistant carries — the instance side, the definition side, and the linkage trap between them, queryable inside Claude instead of rediscovered from scratch.
It ships in every tier. Start the free 7-day trial — no credit card — and ask it to draft the approval-workflow query for your object type; it'll come back with the wf_step_next authorization matrix built in, not just the step names.
