Performance issue with Kanban board when storing many item positions

[julian]

Hey,
I’m building a Kanban board to manage invoices. Each lane represents an invoice state („In progress“, „waiting for approval“, „approved“ and „finalize process“). The user moves one item (invoice) from one lane to another once it reaches a new state. If the user clicks on an item in a specific lane, a specific modal opens (depending on the lane the item belongs to).

This is the workflow I’ve set up. It does the following: Once a user moves an item from one lane to another, the loop makes sure that for all items in the new lane the new order is stored in the database by updating the entries accordingly (Alexis mentioned this approach in this post). Then, there is a check if the item was moved to a lane with lane_id >= 3 (because then it will receive an additional approver timestamp - if not, the approver timestamp will be nulled (in case the user moved the item for example from lane 3 to lane 2). In the end, another workflow reloads the data.

This approach results in two problems:
First, there are sometimes quite many items in a lane, which lead to the situation, that when a user moves an invoice from lane „waiting for approval“ to lane „approved“ and clicks the element, there is still the modal from lane „waiting for approval“ displayed, instead of the modal which should open when the item is in lane „approved“ (this is probably due to the fact that the moved item has not received the new lane id yet when clicked (because the loop in the workflow has not updated this item).

Second, items might jump in a different order. (I uploaded a video for demonstration purposes here - in this video I shown an extreme case, but it also happens when I’m not moving so fast that many items).

Anyone an idea how these issues can be resolved?
Thanks for your help!

[Broberto]

Hello, I see this pattern very often. Usually when there is a high rate of interactivity required and people rely on the backend too much.

Loops and database actions

Currently, you’re doing a lot of calls. From what I see, I’d solve the first part first though, as loops (front-end) and Database Actions are never good. This can lead to high time complexity of your workflows, so you should either:

1. Simplify your query, I’ve recorded a video about this case yesterday for an another post
2. Move your (looping) logic to the backend - if you’re unable to simplify it in the front-end

This should reduce the latency and increase the speed by quite a lot, because in both cases, it will reduce the amount of request, thus reducing the amount of the idle while you’re awaiting the responses of those requests.

Relying on the backend too much

As for this second part, I usually suggest what I call a “Redux-like Pattern”, this is a pattern which is actually used in the standard web development - but don’t be frightened, no coding required - and I actually often teach it on my 1:1 no-code coaching sessions.

As we already successfully found out, the issue is the latency of the calls between Supabase and WeWeb and this is a normal thing, nothing wrong with the two. This pattern I mentioned, solves this issue by relying on the state of your app’s state which you’d store on the front-end. But… what does this mean?

What is even “the state”

WeWeb actually is pretty powerful in this, because it has something called “the state”, which basically you can immagine as a storage which remembers the current state of your app, this can be variables, widths, heights, but also, as in your case, it can be more complex stuff, such as the cards in your kanban, which in simpler terms is just an array of objects with some properties.

Using the Redux-like pattern in WeWeb

To kinda bring all of these concepts together, and of course improve your app, you want to rely on the above mentioned state of your WeWeb application. You can do this, by creating variables, that will store the state, such as an array for your kanban items, which you will, with a workflow on page load set to be your initial database’s information. After that, you no longer re-fetch this, unless strictly necessary. Instead, you will be updating this state, stored in your array.

So in your case, you would go and Update the items by Add approver or Database | Update and instead of hitting the Load active invoices, you’d simply use a Change variable value workflow action, to go and update the object, in the array in your state, thus having a faster feedback and eliminating the last call - thus saving time and having again a faster feedback.

This simply means, that you should use the variables, to store informations in your app, and change those variables after successful changes to the db, instead of relying to over fetching the database each time, because it’s slow and inefficient. Also - don’t do loops with Database Actions, it collects so much latency that you can’t immagine.

Edit: By the way, it’s not a performance issue of WeWeb, it actually is the latency of the calls, if you for example had 0.00X latency (NASA’s 10000 Gbit internet) you’d have instant feedback, but that’s not realistic, so you have to optimize for it.

[julian]

Thanks a lot for your detailed explanation, @Broberto. Your suggestion regarding not relying on the backend too much convinced me to change the approach in that regard that all items only are loaded once (on page load), then there will be only queries for updating single items when necessary (when moved from one lane to another) and the order ids will not be stored in the backend for now.

[thomlov]

Regarding saving the order of each item I have a similar use case, and I’m using this postgres function to do backend update of the table, when ordering is changed.

CREATE OR REPLACE FUNCTION trigger_pl_products_components_order()
RETURNS TRIGGER AS $$
BEGIN
    -- Update only the row with the exact same order
    UPDATE pl_products_components
    SET "order" = "order" + 1
    WHERE pl_products_id = NEW.pl_products_id
    AND pl_difficulties_id = NEW.pl_difficulties_id
    AND "order" = NEW."order"
    AND id != NEW.id;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

I run this as a trigger on new and updated.

It works by updating the order number that already exists, +1.
That again triggers a new update operation, that runs the function again and handles the new row. Again and again until it runs out of numbers to change.

For me it’s normally only 10-20 rows to update, if it’s a lot more then this solution also will not be good performance wise. Also there is some potential errors to happen if the same row is updated at the same time from another client, it will create a race.

[Broberto]

I’m not sure how important is the order of the items after reload, if it’s not that important, the approach that I describe eliminates the need for this whole thing. Because where you drag the items, there they’ll stay, due to the fact that the Kanban will change the apps state, and no new order will be pulled from the backend. But a great approach indeed. I used to do this with just a timestamp, so the last added will be last. I wish we had more control over kanban, the unerlying library is pretty powerful.