Mail Approval Pipeline

Executive summary

The delivery points — checked against the live database

Each row was verified by reading the actual code that runs today, then cross-checked against real configuration and data on the live unisoftgb4 server — not against documentation, which was found to be out of date in several places. Two issues found during this review are marked fixed live below: they were corrected directly on unisoftgb4 on 2026‑08‑26 and re-verified afterward.

Approval by mail

Fully built and working end to end. Confirmed live for the Leave module — a real Approve/Reject/Return email with clickable links exists and is wired up. It is not yet a "just works for any document" feature: each new document type needs its own configuration rows (see Part 1) the way Leave already has.

✓ delivered

Resend mail

POST /Action/Resend exists, is independent of everything else, and correctly re-sends a fresh email with new links while keeping the old link valid (still one-time-use, so no double-approval risk). Do not confuse this with the RESENDWORKFLOW event — see the callout in Part 3, Phase 6.

✓ delivered

Mail sent when approved

Wired, active, and the actual template content is correct — confirmed against the live APPROVEWORKFLOW mail template, which reads "Your leave request has been approved" with real leave-detail placeholders.

✓ delivered

Mail sent when rejected (and when returned)

The live REJECTWORKFLOW and RESENDWORKFLOW mail templates previously contained leftover "Visitor Details" content from an unrelated feature. Rewritten on 2026‑08‑26 to real rejection / returned-for-correction wording, matching the Approved mail's structure and using only field placeholders already proven to resolve correctly. Re-verified live against MMAILTEMPLATE — see Part 1.

✓ delivered · fixed live

Also fixed while verifying: the live database was missing the REMARKS column on TDIRECTACTIONTOKEN, which the current code writes to on every single click of an Approve/Reject/Return link — not just the ones that collect remarks. Without it, every approval-link click would have failed with a SQL error. Added live on 2026‑08‑26 (NVARCHAR(2000) NULL, matching the code's expectations exactly) and re-verified — see TDIRECTACTIONTOKEN.

Orientation

The pipeline at a glance

Eight stages, from a form being submitted to the final outcome mail landing in someone's inbox. Part 3 walks through each one in full detail with exact table and column names.

Submit

User saves a document that needs approval.

Workflow starts

An approval task is created for level 1.

Mail goes out

Approver gets an email with Approve / Reject buttons.

Click

Approver clicks a button — no login needed.

Token checked

Signature, expiry, one-time-use all verified.

Decision applied

Approve advances or finishes; Reject/Return stop it.

Outcome mail

Approved / Rejected notice sent automatically.

Resend (if needed)

A fresh link can be issued any time, independently.

Part 1 of 3 — Configuration

Setup tables — filled in once, by an administrator

These tables answer: which documents need approval, who approves them, and what should the emails say? They're set up once per document type and rarely touched afterward. Every table below also carries the framework's standard audit columns (CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SORTORDER, VERSION, SOURCETYPE) which are omitted below to keep the focus on what's specific to this feature.

MWORKFLOWCONFIG

one row per "this kind of document, in this office, needs this workflow"

Column	Meaning
ENTITYID	Which document type this rule applies to (e.g. Leave Request). -1 = any.
OUID	Which office/branch. -1 = any office.
CLIENTID	Which company/tenant. -1 = any.
BIZTRANSACTIONCLASSID / BIZTRANSACTIONID	Optional narrower scoping (e.g. a specific transaction type). -1 = any. ISBIZTRANSACTIONWISE controls whether BIZTRANSACTIONID is checked at all — see enum appendix.
EVALCONDITION	An extra plain-English-style rule, e.g. TaskDetailType == 0. Leave blank to always apply. If several rows could match the same document, the most specific one wins and its condition decides.
WORKFLOWID	Which approval workflow to run (points into MWORKFLOW below). -1 = this row doesn't actually start anything (kept for record-keeping only).
ISFORMBASEDAPPROVAL	The single most important switch on this table. 0 = Form-based (WIP) — the document is not saved anywhere real until the last approver says yes; it only exists as a pending draft until then. 1 (or anything else) = Regular — the document is saved immediately, and approval happens alongside it, level by level.
CALLBACKENDPOINT	Form-based (WIP) only: the real "save" address to call once every level has approved, e.g. /Leave/SaveLeave.

Live snapshot: 8 rows configured today — 2 use Form-based/WIP (0), 6 use Regular (1).

MWORKFLOW → MWORKFLOWDETAIL → MWORKFLOWASSIGNMENT

the actual approval levels, and who sits at each one

Table	Column	Meaning
MWORKFLOW	WORKFLOWNAME / ENTITYID	The workflow's name and which document type it's built for. Only the latest STATUS=1 version is used.
MWORKFLOWDETAIL	APPROVALLEVEL	The step's position in the chain. Steps sharing the same level run in parallel — all of them must finish before the workflow moves to the next level.
MWORKFLOWDETAIL	DISPLAYNAME	The human name shown for this approval step, e.g. "Manager Approval".
MWORKFLOWDETAIL	ISAUTOAPPROVE	If a task at this step sits unanswered past its due time, should it auto-approve itself? 0 = yes, auto-approve. 1 = no, keep waiting (a background job checks this).
MWORKFLOWDETAIL	SLAHOURS	How many hours an approver has before this step is considered overdue.
MWORKFLOWASSIGNMENT	STRATEGYTYPE	How the system decides who the approver actually is — see enum appendix for the four options (fixed user, user group, a rule that reads a field, or a SQL lookup).
MWORKFLOWASSIGNMENT	RULEEXPRESSION	The actual value used by STRATEGYTYPE — a user ID, a group ID, a field name, or a SQL query, depending on the strategy chosen above.

MDIRECTACTION → MDIRECTACTIONDETAIL

the clickable buttons that go inside the approval email

Table	Column	Meaning
MDIRECTACTION	CONTEXTIDFIELD	The name of the field, inside the event's data, that holds the document's ID — e.g. WorkflowTaskId. This is how the system knows which document a click is about.
MDIRECTACTION	ASSIGNEEUSERIDFIELD	Same idea, for the approver's user ID — e.g. AssignedToUserId.
MDIRECTACTIONDETAIL	ACTIONCODE	A short code for this one button, e.g. WORKFLOW_APPROVE. This code travels inside the signed link and tells the system what to do when clicked.
MDIRECTACTIONDETAIL	ACTIONLABEL	The button's visible text, e.g. "Approve".
MDIRECTACTIONDETAIL	EMAILPLACEHOLDER	The exact word the mail template uses for this button's link, e.g. APPROVE_URL — the template writer types ##APPROVE_URL## and the system swaps in a real clickable link at send time.
MDIRECTACTIONDETAIL	APIENDPOINT / PAYLOADTEMPLATE	Which internal address gets called when this button is clicked, and what to send it — e.g. /WorkFlow/WorkFlowActions with a small JSON body carrying the task ID and the decision.
MDIRECTACTIONDETAIL	NEEDSINPUT	0 = one click and done — approve/reject happens the instant the link is opened. 1 = ask for something first — the person sees a small "enter your remarks" box (or a full multi-step form) before the decision is submitted.
MDIRECTACTIONDETAIL	FLOWID	Only matters when NEEDSINPUT=1. -1 or 0 = show the simple one-box remarks form. Any real flow ID = show a proper multi-step guided form instead (built in the same engine that powers WhatsApp/Teams conversations).

Live example — the Leave workflow's real button set (group DIRECTACTIONID=1): WORKFLOW_APPROVE → placeholder APPROVE_URL · WORKFLOW_REJECT → REJECT_URL · WORKFLOW_RETURN → RETURN_URL. All three call POST /WorkFlow/WorkFlowActions, none of them ask for remarks.

MEVENTTYPE → MEVENTTYPEACTION → MEVENTTYPEACTIONDETAIL → MACTION

"when this happens, send this mail" — the wiring in between

Table	Column	Meaning
MEVENTTYPE	EVENTTYPEID	A fixed ID for a "thing that happened" — e.g. Approve Work Flow. Three of these already exist in every GB5 install and never need to be created: see the box below.
MEVENTTYPEACTION	EVENTTYPEID	Links a group of actions to one event type.
MEVENTTYPEACTIONDETAIL	ACTIONID	Points at the specific mail (or SMS, etc.) to run — see MACTION below.
MEVENTTYPEACTIONDETAIL	ISACTIVE	⚠ Counter-intuitive on purpose: 0 = YES, this is active and will fire. 1 = NO, switched off.
MACTION	ACTIONTYPE	What kind of action this is: 0 = Email, 1 = SMS, 2 = Webhook call, 3 = Correspondence/document, 5 = In-app notification.
MACTION	TEMPLATEID	Which mail template to use (points into MMAILTEMPLATE below).
MACTION	SENDTO / TODELIVERYTYPE	Who receives it, and how to read the SENDTO value — 2 = it's a field name (look up the actual address from the document, e.g. MailId), 3 = it's already a real email address, use as-is.
MACTION	DIRECTACTIONID	-1 = plain notification, no buttons. Any real ID = attach the button set from MDIRECTACTION and generate signed links for this mail.

Live check: the button-bearing MACTION row (the one with a real DIRECTACTIONID) is currently wired only to the Leave module's own save event — not to a generic "a new approval task was created" event. See Gaps.

MMAILTEMPLATE

the actual wording of the email

Column	Meaning
SUBJECT / BODY	The email's subject and body text. Write it like a normal email — placeholders get swapped for real values right before sending.
##FieldName##	Anywhere in SUBJECT or BODY, this gets replaced with the matching field's real value from the document — e.g. ##EmployeeName##, or a button placeholder like ##APPROVE_URL## (see MDIRECTACTIONDETAIL above).
EVENTTYPEID	Which event this template is meant for — mostly for the admin screen to group templates sensibly.

The three approval-outcome events already exist — don't recreate them. Every GB5 install already has these three system event types pre-loaded, confirmed live:

-1399999792Approve Work Flow — fires right after the last approver says yes

-1399999791Reject WorkFlow — fires the moment anyone rejects

-1399999790Resend WorkFlow — fires when an approver clicks "Return" (send back for changes), not to be confused with the /Action/Resend feature

Part 2 of 3 — What actually happens

Runtime tables — filled in automatically, while a document moves through approval

Nobody edits these directly. They're the system's own record of what's pending, what link was clicked, and what's already been sent.

TWORKFLOWINSTANCE and TWORKFLOWTASK

one instance per document in approval; one task per approver per level

Table	Column	Meaning
TWORKFLOWINSTANCE	CURRENTAPPROVALLEVEL	Which level the document is sitting at right now.
TWORKFLOWINSTANCE	WORKFLOWSTATUS	0 = Pending (still moving through levels) · 1 = Completed (fully approved) · 2 = Rejected · 3 = Returned (sent back).
TWORKFLOWTASK	ASSIGNEDTOUSERID	Who this particular task is waiting on.
TWORKFLOWTASK	WORKFLOWTASKSTATUS	0 = Pending (waiting for a decision) · 3 = Completed (a decision was made — check ACTIONTAKEN to see which one).
TWORKFLOWTASK	ACTIONTAKEN	The actual decision made on this task: 1 = Approve, 2 = Reject, 3 = Return. This is the column that tells you what happened — not WORKFLOWTASKSTATUS.
TWORKFLOWTASK	DUEON / COMPLETEDON	When the approver was expected to act, and when they actually did.
TWORKFLOWTASK	COMMENT	Any remarks the approver typed in.

TWORKFLOWWIP

Form-based (WIP) documents only — the "not saved yet" holding area

Column	Meaning
DATAJSON	The entire document, saved as a snapshot here instead of its real table, until final approval.
STATUS	1 = Pending (still in approval) · 2 = Approved — the real document is now created/updated for real · 3 = Rejected · 4 = Returned.
RESUBMISSIONCOUNT	How many times this document has been sent back and resubmitted.

This is exactly why Form-based documents can't be edited mid-approval: until STATUS reaches 2 (Approved), there is no real document row anywhere else to edit.

TDIRECTACTIONTOKEN

one row per link that has actually been clicked — the anti-double-click record

Column	Meaning
TOKENHASH	A scrambled fingerprint of the link that was clicked (never the raw link itself — this is a one-way hash).
ACTIONCODE / CONTEXTID / ASSIGNEEUSERID	What was clicked (Approve/Reject/Return), on which document, by whom.
STATUS	0 = Unused — a row only appears here after a click, so in practice this value is never actually seen at rest. 1 = Used — the normal, expected value for every row you'll find. 2 = Revoked — the meaning is defined, but nothing in the system currently sets it; there is no "revoke a link" button yet.
EXPIRESAT	Links stop working automatically 48 hours after the email was sent.
REMARKS	Whatever the approver typed in, only when the button asked for input (NEEDSINPUT=1) — blank for ordinary one-click Approve/Reject/Return.

A row only exists here because the link was clicked — that's what makes a second click on the same link get "already actioned" instead of running twice. REMARKS added live to unisoftgb4 on 2026‑08‑26 — the column was missing before this fix, and every click (this table's only purpose) would have failed until it was added.

TEVENTACTIONRUN and TACTIONOUTBOX

the delivery queue between "something happened" and "email actually sent"

Table	Column	Meaning
TEVENTACTIONRUN	RUNSTATUS	0 = Pending · 1 = In progress · 2 = Completed · 3 = Failed. One row per action per event — this is the audit trail of "did this mail actually go out."
TACTIONOUTBOX	SENDSTATUS	0 = Pending · 1 = Sent · 2 = Failed. The queue row that actually gets picked up and delivered.
TACTIONOUTBOX	DESTINATIONTOPIC	Which internal delivery lane this message travels on — purely a technical routing detail.

Part 3 of 3 — The full story

The journey, start to finish

Everything above, in the order it actually happens. Read this section top to bottom and you have the whole feature.

Someone submits a document

e.g. an employee submits a Leave Request

The system checks MWORKFLOWCONFIG for a matching rule (right entity, right office, condition passes). If one matches, this document now needs approval — otherwise it saves normally and nothing below happens.

If the matched rule is Form-based (WIP), the document is not saved to its real table yet — only a snapshot goes into TWORKFLOWWIP. If it's Regular, the document is saved immediately, and approval runs alongside it.

The first approval task is created

TWORKFLOWINSTANCE + TWORKFLOWTASK

A new instance is created at level 1, and one task row is created per approver at that level (using the rule in MWORKFLOWASSIGNMENT — a fixed person, a group, or a lookup). Each task starts as WORKFLOWTASKSTATUS = 0 (Pending).

The "please approve this" email goes out

MEVENTTYPEACTION → MACTION → MMAILTEMPLATE

Creating that task is itself something that happened — an event. If a mail action is wired to that event (as it is today for Leave, via its own save event), the system builds the email from the template, and because that action's DIRECTACTIONID points at a real button group, it generates one signed, expiring link per button and drops each into its ##PLACEHOLDER## spot in the email body.

The approver clicks a button — no login required

GET /Action/Execute?token=...

The link is checked: is the signature genuine, has it expired (48 hours), and — by looking it up in TDIRECTACTIONTOKEN — has it already been used? Any failure shows a plain "this link is invalid / expired / already used" page and stops here.

If the button needs remarks first (NEEDSINPUT = 1), a small form (or a guided multi-step one) is shown instead, and the click is only finalised once that's submitted.

The decision is recorded and applied

POST /WorkFlow/WorkFlowActions

The click is recorded in TDIRECTACTIONTOKEN as Used, then the workflow engine applies the decision to the matching task:

Approve — if other approvers at this level haven't answered yet, nothing moves on until they do. Once everyone at the level is done: if there's a next level, new tasks are created there (back to Phase 2/3 for the next approver); if this was the last level, the workflow finishes.

Reject — stops immediately, regardless of level. No further approvers are asked.

Return — sends the document back one level (or to the original submitter if it was already at level 1) for changes.

The outcome mail is sent automatically

Approve Work Flow / Reject WorkFlow / Resend WorkFlow events

Whatever the outcome, it fires the matching built-in event — Approved, Rejected, or (for Return) the event literally named "Resend WorkFlow". Whatever mail actions are wired to that event send automatically, the same way as Phase 3.

Naming gotcha: "Resend WorkFlow" is the notification sent when someone clicks Return — it has nothing to do with re-sending a lost or expired approval link. That's a completely separate feature, covered in Phase 8.

Form-based (WIP) documents: the real save happens now

only for documents using WIP mode — skip if Regular

Only once every level has approved does the system take the snapshot sitting in TWORKFLOWWIP and actually call the document's real save address (CALLBACKENDPOINT from MWORKFLOWCONFIG). This is the moment the document first becomes a real, editable row — which is also why it couldn't be edited by anyone before this point.

Resend — an independent safety net, any time

POST /Action/Resend

If a link was never clicked, expired, or the email was simply lost, anyone with the right permission can trigger a fresh email with brand-new links for the same pending task — no need to wait, no need to restart the approval. The old link, if still valid, keeps working too; whichever one gets clicked first wins, and the other becomes "already actioned" the moment it's tried.

Appendix

Every status code used in this feature, in one place

Every number that means something different depending on context — collected here so nobody has to go hunting through the tables above to remember what a "3" means in a given column.

ISFORMBASEDAPPROVAL — MWORKFLOWCONFIG

0Form-based (WIP) — not saved until fully approved
1Regular — saved immediately, multi-level approval runs after

STRATEGYTYPE — MWORKFLOWASSIGNMENT

0User — a specific fixed person
1User Group — anyone in a group
2Enrich Qualifier — read from a field on the document
3Dynamic SQL — run a query to find the approver
4Service — reserved, not built yet

ACTIONTAKEN — TWORKFLOWTASK

0Submit
1Approve
2Reject
3Return
4Escalate

WORKFLOWSTATUS — TWORKFLOWINSTANCE

0Pending
1Completed (approved)
2Rejected
3Returned

WORKFLOWTASKSTATUS — TWORKFLOWTASK

0Pending
3Completed — check ACTIONTAKEN for the actual decision

Values 1 (Approved) and 2 (Rejected) are defined but never actually used — the engine always writes 3 and relies on ACTIONTAKEN instead.

STATUS — TWORKFLOWWIP

1Pending (just started)
2Approved — real document now saved
3Rejected
4Returned

NEEDSINPUT — MDIRECTACTIONDETAIL

0Decide instantly on click
1Ask for remarks (or run a guided form) first

STATUS — TDIRECTACTIONTOKEN

0Unused
1Used — the value you'll actually see
2Revoked — meaning defined, nothing sets it yet

DIRECTACTIONID — MACTION

-1Plain notification, no buttons
≥0Attach this button group, generate links

ACTIONTYPE — MACTION

0Email
1SMS
2Webhook call
3Correspondence / document
5In-app notification

The admin screen's own tooltip lists a different, older numbering (0-6) that doesn't match what the code actually does past value 2 — go by this list, not the tooltip.

TODELIVERYTYPE — MACTION (recipient)

1A saved mailing list
2A field name — look the address up on the document
3A literal email address, used as-is
12Run a saved query to find the address

ISACTIVE — MEVENTTYPEACTIONDETAIL

0YES — active, will fire
1NO — switched off

Backwards from what most people expect — double-check before assuming.

RUNSTATUS — TEVENTACTIONRUN

0Pending
1In progress
2Completed
3Failed

SENDSTATUS — TACTIONOUTBOX

0Pending
1Sent
2Failed

STATUS — shared framework row-status

0Pending
1Active
2Deleted
3Amended
4Inactive
5Archived

Used identically by MACTION, MEVENTTYPE, MEVENTTYPEACTION, MWORKFLOW and MDIRECTACTION's own row-status — not to be confused with any of the feature-specific statuses above.

Found during live verification

Resolved during this review, and what's still open

Everything below was found by checking the actual unisoftgb4 database and code together, not assumed. Two issues were fixed live on the spot; one remains open.

Fixed live

Every approval-link click would have failed on this database

The code that records a click (used by every Approve/Reject/Return link, not just the ones asking for remarks) always writes to a REMARKS column on TDIRECTACTIONTOKEN. That column did not exist on the live unisoftgb4 database. Added on 2026‑08‑26 as NVARCHAR(2000) NULL and re-verified against INFORMATION_SCHEMA.COLUMNS.

Fixed & verified: TDIRECTACTIONTOKEN now has 10 columns including REMARKS, nullable, no data loss

Fixed live

Reject and Return outcome emails had the wrong wording

Both templates contained "Visitor Details" boilerplate (Name / Company / Designation / Mobile / Visit Date...) left over from an unrelated feature. Rewritten on 2026‑08‑26 to match the Approve template's structure and tone — a real rejection notice and a real returned-for-correction notice, both using only placeholders already proven to resolve. Confirmed no other live feature referenced these two template rows before changing them.

Fixed & verified: MMAILTEMPLATE -1499999473 (Reject) and -1499999472 (Resend/Return) — new subject and body confirmed live

Scoped narrowly

The clickable-button email is only wired up for one module

The only live MACTION row that carries a real button group is tied to the Leave module's own save event. New document types need the same wiring done by hand — there is currently no generic "a new approval task was created for anyone" trigger that every module gets automatically.

Live check: exactly one MACTION row across the whole database has DIRECTACTIONID > -1

The Mail Approval Pipeline

The delivery points — checked against the live database

Approval by mail

Resend mail

Mail sent when approved

Mail sent when rejected (and when returned)

The pipeline at a glance

Submit

Workflow starts

Mail goes out

Click

Token checked

Decision applied

Outcome mail

Resend (if needed)

Setup tables — filled in once, by an administrator

MWORKFLOWCONFIG

MWORKFLOW → MWORKFLOWDETAIL → MWORKFLOWASSIGNMENT

MDIRECTACTION → MDIRECTACTIONDETAIL

MEVENTTYPE → MEVENTTYPEACTION → MEVENTTYPEACTIONDETAIL → MACTION

MMAILTEMPLATE

Runtime tables — filled in automatically, while a document moves through approval

TWORKFLOWINSTANCE and TWORKFLOWTASK

TWORKFLOWWIP

TDIRECTACTIONTOKEN

TEVENTACTIONRUN and TACTIONOUTBOX

The journey, start to finish

Someone submits a document

The first approval task is created

The "please approve this" email goes out

The approver clicks a button — no login required

The decision is recorded and applied

The outcome mail is sent automatically

Form-based (WIP) documents: the real save happens now

Resend — an independent safety net, any time

Every status code used in this feature, in one place

ISFORMBASEDAPPROVAL — MWORKFLOWCONFIG

STRATEGYTYPE — MWORKFLOWASSIGNMENT

ACTIONTAKEN — TWORKFLOWTASK

WORKFLOWSTATUS — TWORKFLOWINSTANCE

WORKFLOWTASKSTATUS — TWORKFLOWTASK

STATUS — TWORKFLOWWIP

NEEDSINPUT — MDIRECTACTIONDETAIL

STATUS — TDIRECTACTIONTOKEN

DIRECTACTIONID — MACTION

ACTIONTYPE — MACTION

TODELIVERYTYPE — MACTION (recipient)

ISACTIVE — MEVENTTYPEACTIONDETAIL

RUNSTATUS — TEVENTACTIONRUN

SENDSTATUS — TACTIONOUTBOX

STATUS — shared framework row-status

Resolved during this review, and what's still open

Every approval-link click would have failed on this database

Reject and Return outcome emails had the wrong wording

The clickable-button email is only wired up for one module