Real-Time Collaborative Editing in IG

Real Time Collaborative Editing in Oracle APEX Interactive Grid

How I got two users editing the same IG at the same time without breaking anything

I have been working with Oracle APEX for a while now, mostly building internal tools for my team. One request that kept coming back was simple to say but hard to build. My users wanted to open the same Interactive Grid page from two different systems and see each other's changes without hitting refresh every time. This blog is basically my notes from that whole journey, including the parts where I got stuck.

Why I started this

In my office we have a shared planning grid where two or three people update rows almost at the same time. Earlier if one person saved a row, the other person had no idea unless they manually refreshed the page. Sometimes this caused both of them to overwrite each other's work. I wanted a small demo where changes made by one user in the Interactive Grid show up for another user almost instantly, without a full page reload.

What I tried first

My first idea was very basic. I thought I will just add a Dynamic Action with a timer that refreshes the whole IG region every 5 seconds. I built it quickly and it did work in the sense that new data showed up. But there was a big issue.

One issue I faced was that if a user was in the middle of editing a cell and the grid refreshed, their unsaved edit just disappeared. It also felt very jerky because the entire grid re rendered, so the scroll position and selected row used to reset every single time. This approach was not something I could show to my team with confidence.

I tried increasing the refresh time to 15 seconds thinking it will reduce the interruption, but that just made the sync feel slow, so users still ended up working on old data for longer.

Finally this approach worked

After a lot of trial and error I changed the whole strategy. Instead of refreshing the full grid, I decided to track changes using a small tracking table in the database, and only fetch what actually changed using ORDS REST and a lightweight polling call from JavaScript. This way the grid itself stays untouched unless there is really a new change from another session.

Finally this approach worked because I stopped trying to refresh the whole region. Instead I compared a last_updated timestamp on the server with the timestamp stored in the browser session. Only when there was a difference, I called apex.region("ig_region").widget().interactiveGrid("getViews","grid").model.refresh() on the exact rows that changed, not the whole grid.

The tracking table

I added one small table that just stores which record was touched, by whom, and when. Every time a save happens on the IG, a small PL SQL process in the Post processing of the Interactive Grid inserts or updates a row here.

PL/SQL - track_changes.sql

begin
  merge into ig_change_tracker t
  using dual
  on (t.record_id = :ID)
  when matched then
    update set t.updated_by = :APP_USER,
               t.updated_on = systimestamp
  when not matched then
    insert (record_id, updated_by, updated_on)
    values (:ID, :APP_USER, systimestamp);
end;

This table is very small, it just holds the record id, who touched it last, and the exact timestamp. It does not store the actual data, only the fact that something changed.

The JavaScript polling part

On the page I added a small script that checks this tracker table every 4 seconds using an ORDS REST endpoint. If the latest timestamp is newer than what the browser already has, only then it triggers a partial refresh on the grid.

JavaScript - live_sync.js

let lastKnown = 0;

function checkForChanges() {
  fetch("https://myserver/ords/app/ig/last_change")
    .then(res => res.json())
    .then(data => {
      if (data.updated_epoch > lastKnown) {
        lastKnown = data.updated_epoch;
        let grid = apex.region("ig_region").widget().interactiveGrid("getViews", "grid");
        grid.model.refresh();
      }
    });
}

setInterval(checkForChanges, 4000);

I tried calling grid.refresh() first but that method reloads the whole region again, same problem as before. Switching to model.refresh() was the actual fix because it just re fetches the data model quietly without disturbing the UI state of the user who is currently editing.

One issue I faced with my own changes

There was one more annoying issue. Since my own save was also updating the tracker table, my own browser used to detect its own change and try to refresh itself right after saving, which looked odd. I fixed this by storing my own session id along with the tracker entry, and skipping the refresh whenever the change came from my own session.

JavaScript - skip_self.js

const mySession = apex.env.APP_SESSION;

function checkForChanges() {
  fetch("https://myserver/ords/app/ig/last_change")
    .then(res => res.json())
    .then(data => {
      if (data.updated_epoch > lastKnown && data.session_id != mySession) {
        lastKnown = data.updated_epoch;
        apex.region("ig_region").widget().interactiveGrid("getViews", "grid").model.refresh();
      }
    });
}

How it feels now

Right now when I open the grid in two browser tabs, one as user A and one as user B, editing a row in one tab shows the update in the other tab within a few seconds, without any manual refresh and without losing whatever the other user was typing. It is not full real time like a chat app, there is a small delay of a few seconds, but for our internal use case that delay is completely fine.

Oracle APEX Interactive Grid ORDS REST JavaScript Polling PL/SQL

Try it yourself

I have put up a working demo on Oracle APEX and the full source is on my GitHub, in case anyone wants to check the actual page structure or reuse the tracker table idea in their own project.

Live Demo GitHub Link

Code copied