Event-Driven Cache Sync: Keeping Your Task Cache 100% Fresh 🔄🔔

🇪🇬 النسخة العربية: اقرأ هذا المقال بالعامية المصرية من هنا.

Part 3 of the "Enterprise APEX Workflows at Scale" Series.

---

⚡ The Quick TL;DR

There are two ways to know your latte is ready at a packed morning cafe: you can stand by the pickup counter every 15 seconds shouting "Is my drink done yet?" (polling), or the barista can ring the service bell the exact second your cup hits the counter (event-driven notification).

To keep our operational cache table (wf_task_cache) synchronized with Oracle APEX workflow transitions, we avoid expensive background polling jobs. Instead, we use event-driven database triggers paired with atomic PL/SQL package synchronization routines.

This delivers 100% real-time consistency in the APEX user interface with zero cron jobs, zero lag, and zero database lock contention.

---

☕ The Polling Trap vs. The Service Bell Pattern

In caching architectures, developers who don't want to deal with cache synchronization often take the path of least resistance: they set up an automated DBMS_SCHEDULER job that runs every 5 minutes to rebuild the cache table.

In a busy enterprise environment, scheduled batch polling creates two severe headaches:

1. The "Ghost Approval" Lag

A department director opens their APEX dashboard, reviews a critical $50,000 procurement request, and clicks Approve. The page refreshes—and because the cache job hasn't run yet, the request is still sitting on their screen marked PENDING. Confused, the director clicks Approve again, throwing errors or creating duplicate downstream purchase orders.

2. Relentless I/O Waste

A scheduler job running every 60 seconds burns database CPU and disk I/O 24 hours a day, 7 days a week—even at 3:00 AM on a Sunday when nobody has submitted a request in eight hours.

Instead of polling, we use the Service Bell Pattern: Whenever a workflow action executes, the database engine updates the cache record in the very same database transaction. By the time the APEX page re-renders in the manager's browser, the operational cache is already 100% up to date!

sequenceDiagram
    autonumber
    actor User as Approver
    participant UI as APEX Page
    participant WF as APEX Workflow Engine
    participant Log as wf_task_event_log
    participant Trg as trg_wf_task_log_cache
    participant Cache as wf_task_cache

    User->>UI: Clicks "Approve"
    UI->>WF: Executes Workflow Transition
    WF->>Log: Writes Immutable Event Record
    Log->>Trg: AFTER INSERT Trigger Fires
    Trg->>Cache: Updates status to 'APPROVED' (or purges)
    Cache-->>UI: Page refreshes in 20ms with updated queue

---

🏗️ The Event Log Architecture: wf_task_event_log

To achieve clean event-driven synchronization, we decouple the write operations into an immutable event history table in our data schema (app_data):

-- ====================================================================
-- TABLE: app_data.wf_task_event_log
-- PURPOSE: Immutable event ledger tracking every workflow transition
-- ====================================================================

create table app_data.wf_task_event_log (
    event_id         number generated always as identity primary key,
    request_id       number not null,
    task_id          number,
    action_type      varchar2(50)  not null, -- 'CREATE', 'APPROVE', 'REJECT', 'REASSIGN'
    previous_status  varchar2(50),
    new_status       varchar2(50)  not null, -- 'PENDING', 'APPROVED', 'REJECTED'
    action_user      varchar2(100) default coalesce(sys_context('APEX$SESSION','APP_USER'), user) not null,
    action_comments  varchar2(4000),
    event_timestamp  timestamp with local time zone default current_timestamp not null
);

create index app_data.wf_task_event_req_idx 
    on app_data.wf_task_event_log(request_id, event_timestamp desc);

Every state transition—whether triggered by APEX native workflow processes, automated timer timeouts, or human clicks—inserts one row into this ledger.

---

🔔 The Thin Trigger: Ringing the Bell

Database triggers should never be bloated with complex business logic, web service calls, or large loops. A good trigger does only one thing: it rings the bell and hands the keys to a compiled PL/SQL package.

-- ====================================================================
-- TRIGGER: app_data.trg_wf_task_log_cache
-- ====================================================================

create or replace trigger app_data.trg_wf_task_log_cache
after insert or update on app_data.wf_task_event_log
for each row
begin
    -- Delegate immediately to the central cache synchronization package
    app_code.pkg_task_cache.sync_task_cache(
        p_request_id => :new.request_id,
        p_task_id    => :new.task_id,
        p_action     => :new.action_type,
        p_new_status => :new.new_status,
        p_user       => :new.action_user
    );
end;
/

---

⚙️ The Cache Synchronizer: pkg_task_cache

Now let's examine the central cache package inside our code schema (app_code). Notice how it handles both ongoing progress and terminal states:

create or replace package app_code.pkg_task_cache as
    procedure sync_task_cache(
        p_request_id in number,
        p_task_id    in number,
        p_action     in varchar2,
        p_new_status in varchar2,
        p_user       in varchar2
    );
end pkg_task_cache;
/

create or replace package body app_code.pkg_task_cache as

    procedure sync_task_cache(
        p_request_id in number,
        p_task_id    in number,
        p_action     in varchar2,
        p_new_status in varchar2,
        p_user       in varchar2
    ) is
    begin
        -- SCENARIO A: Terminal Status Reached (Task Complete)
        -- When a task is Approved, Rejected, or Cancelled, it is no longer
        -- active operational work. Remove it from the fast cache immediately!
        if p_new_status in ('APPROVED', 'REJECTED', 'CANCELLED', 'EXPIRED') then
            delete from app_data.wf_task_cache
             where request_id = p_request_id;
             
        -- SCENARIO B: Active State Transition (Claimed, In-Progress, Reassigned)
        else
            update app_data.wf_task_cache
               set status     = p_new_status,
                   task_id    = coalesce(p_task_id, task_id),
                   updated_at = current_timestamp
             where request_id = p_request_id;

            -- If the record did not exist in cache yet (e.g. initial task creation),
            -- insert a new entry into the operational cache
            if sql%rowcount = 0 then
                insert into app_data.wf_task_cache (
                    request_id,
                    task_id,
                    task_group,
                    task_type,
                    status,
                    title,
                    priority,
                    requester_name,
                    potential_owners,
                    created_at,
                    updated_at
                )
                select r.request_id,
                       p_task_id,
                       r.request_group,
                       r.request_type,
                       p_new_status,
                       r.title,
                       r.priority,
                       r.requester_name,
                       r.potential_owners,
                       current_timestamp,
                       current_timestamp
                  from app_data.wf_requests r
                 where r.request_id = p_request_id;
            end if;
        end if;
    end sync_task_cache;

end pkg_task_cache;
/

---

🛡️ The 3 Golden Rules for Zero-Contention Triggers

To ensure your event-driven cache scales under heavy concurrent load:

1. Atomic Single-Row Targeted Writes

Notice that the update and delete statements in pkg_task_cache strictly filter by where request_id = p_request_id. Because request_id has a unique or b-tree index, Oracle locks only the single affected row for a fraction of a millisecond.

2. Never Use AUTONOMOUS_TRANSACTION for Cache Sync

Some developers attempt to make cache triggers autonomous. Don't do this! If the main business transaction rolls back due to a constraint violation, an autonomous cache transaction would remain committed—leaving an orphan "ghost" task in the cache. Keeping them in the same transaction guarantees perfect ACID consistency.

3. Immediate Purging of Terminal Records

The operational cache table should strictly mirror active work. As soon as a task reaches a terminal state, delete it from wf_task_cache. The historical record is already permanently preserved in wf_task_event_log.

---

🚀 Coming Up Next: Part 4

Our backend is blazing fast and synchronizes in real time. But what about the front-end user experience?

When an approver opens an individual task, different workflow steps require different action buttons:

• Step 1 needs Approve, Reject, and Request More Info.
• Step 2 needs Confirm Budget and Upload Receipt.
• Step 3 needs Release Payment.

In Part 4 ("Goodbye Hardcoded Buttons: Storing UI Actions in Database Rows"), we will see how storing button configurations in a metadata table eliminates the nightmare of managing 40 conditional buttons inside APEX Page Designer!

Stay tuned for Part 4! 🎛️📋