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

> 🇪🇬 **النسخة العربية:** اقرأ هذا المقال بالعامية المصرية [من هنا](https://ash2osh.github.io/arabic-blog/posts/017_event-driven-cache-sync-keeping-your-task-cache-fresh-with-triggers/).

*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!

```mermaid
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`):

```sql
-- ====================================================================
-- 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**.

```sql
-- ====================================================================
-- 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:

```sql
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! 🎛️📋