DEV Community: Productive

How we handled MySQL deadlocks in Productive

Jean Petric — Mon, 11 Mar 2024 08:11:21 +0000

Disclaimer: note that I mostly redacted product’s internal details. In places I also used simplified explanations regarding MySQL indexes, especially where detailed explanations already exist elsewhere on the web. I provided links where appropriate.

In the rest of this post I first provide a short context of how we do certain things at Productive. Then I briefly explain what deadlocks are, followed by the approach we took to fix them.

Asynchronous jobs in Productive

In Productive we use Sidekiq for scheduling asynchronous jobs. Some of these jobs are sending emails to users when they do not track their time. Others are batch jobs which need to update some financial data, copy certain objects in a transaction safe manner, etc. Starting a few months ago we’ve started noticing a surge of deadlocks in our application. In particular, these deadlocks started appearing for batched Sidekiq jobs which have to process a high number of relatively heavy database transactions.

What are deadlocks?

Database deadlocks occur when two transactions cannot proceed as one holds a lock that the other one needs. There is nothing inherently wrong with deadlocks, as long as they do not impact your database in a way that you cannot process certain transactions at all. You do need, however, to have a mechanism in place to retry deadlocked transactions. It is generally sufficient to retry only the second transaction (the one that caused the deadlock), as MySQL only aborts that transaction to release the lock, allowing the first transaction to continue.

Finding deadlocks’ root causes in production is difficult. Deadlocks are difficult to reproduce (as they often depend on the throughput of your transactions at any given moment). Enabling extra logging specifically for deadlocks is also tricky as they produce logs which are large in size. Even when you find transactions and the locks they hold, an appropriate course of action for their mitigation is almost never immediately clear.

The experiment

Prep work

As the number of deadlocks in our application started to increase month on month, we had to do something to prevent more serious issues. We started by setting up a separate temporary environment which was a very close copy of our production environment. We also did certain code changes to the product so we can run “simulations” of the problematic Sidekiq job easier. We also hooked up the environment to our monitoring system so that we could capture logs. As soon as we started mass running our jobs we immediately began noticing deadlocks.

Deadlock detection

There are more ways to detect deadlocks. We used two. First is innodb status which displays operational information about a storage engine, amongst which the latest occurring deadlocks. Here is how an output from innodb status looks like:

The statement only shows one, the latest detected deadlock, with prints details about the table on which the deadlock occurred, as well as the records locked and the type of locks used. This information is reported for both transactions that participated in the deadlock.

The second place to find about deadlocks is the performance_schema database that keeps the records of all database locks that are granted. Here is an example output of the data_locks table which shows all currently granted locks (the data in this table is "live" which means that locks appear and disappear as statements get executed):

From that output you can figure out whether some SQL statements hold too many rows locked, as this can sometimes lead to deadlocks (at least it did in our case).

Analysing the deadlocks

Analysing the statements involved in deadlocks and looking at the locks they were holding didn’t really help us in addressing the chunks of code responsible for their execution. But even if it did, we would need to find a way to ensure that all transactions execute statements in the same order to avoid deadlocks.

Looking at the output of innodb status we found out which indexes were being used and on which tables. The output of innodb status is fairly straightforward and merely reveals which SQL transaction is being involved in a deadlock, which index and what type of lock was used. Every new deadlock overwrites innodb status, so by refreshing the status you can analyse which deadlocks occur.

From the output we observed that MySQL would pick two different indexes on the same table at different times. In both cases indexes were indexing a single column (simple index). It’s worth noting that depending on which index is being used, different records will end up being locked, which is the way MySQL ensures the integrity of data.

Knowing the indexes and type of locks that were causing the deadlocks helped us to further analyse performance_schema, in particular its data_locks table. This table holds live information about all locks being granted. Inside we found the offending locks and what index records were being locked by them (note that in MySQL it’s not rows that are being locked, but rather index records).

Index optimisation

Seeing the table helped us to realise that by using these two indexes individually, MySQL had to lock wider ranges of index records than it is really necessary. For example, our job needed to update one record D, but MySQL locked all records between B and F, as that was the smallest index record it could lock. As more rows than needed were being locked, and the throughput of our Sidekiq jobs was high, this led to a high number of deadlocks.

As a result of our analysis we concluded that we needed to optimise these two indexes. As the problematic MySQL statements were using both indexed columns in their WHERE conditions, we decided to drop these indexes.

Instead, we opted in for a composite index containing both of these columns. By doing that we hypothesised that less rows will be locked due to more fine grained index records. Combining two columns narrows down the search MySQL has to do to find the records that need to be altered, incidentally locking less rows. By executing the experiment in our testing environment we reduced deadlocks from 50.000 to 0.

The second experiment

New deadlocks

After re-running the experiment again, with our new composite index, we started noticing deadlocks again, but on a different table. Symptoms were very similar to the ones before, so we took a similar approach looking at the innodb status and locks and deciding to drop simple indexes, finally replacing them with a composite index. Repeating the experiment we found that the number of deadlocks actually increased two fold (from about 300 to 600) with the new composite index, so we decided not to keep the composite index here.

The increased number of deadlocks in the second experiment was confusing so we decided to dig deeper. It wasn’t until we looked at our database schema that we noticed a BEFORE INSERT database trigger. Inside there was a statement with two column conditions in the WHERE condition. One of these columns held a pattern whose typical value for most of our clients was “{n}”.

Trying composite indexes again

Unlike in our first experiment where we reduced 50.000 deadlocks to 0 by adding a composite index, the same approach didn't help us in this situation. The problem was no variability in the column holding a pattern (>98% of pattern was {n}), so MySQL was unable to narrow down the search and lock less records. As a result, MySQL had to lock almost the entire table, so deadlocks were inevitable. We couldn’t find other columns to form a more suitable index or to change the trigger. But as the number of deadlocks for the second table was relatively low, we decided not to do any changes.

Conclusion

In the end we decided to live with some deadlocks. It turned out that in production we ended up with some 50 deadlocks when the particular jobs are at their peak, but often much less than that. As a matter of fact, we've been monitoring deadlocks for the last couple of months and week on week we get about 16 of them at most. However, it’s also worth mentioning that optimising indexes might not always be a suitable solution for deadlocks. As shown above, it depends on a case by case basis.

But what is also important is that having deadlocks is not necessarily a bad thing, as long as you have a strategy to retry transactions and database can operate normally. All in all, three months in and counting, more than 50.000 deadlocks were resolved by a simple index modification.

To conclude, we found a way to address deadlocks beyond what the official MySQL documentation suggests. Optimising indexes was a cheap but effective way to reduce deadlocks. However, adding indexes judiciously pays off even better than finding yourself later removing them due to issues they may cause.

Integrations Series: Authentication And Connection

antoniobajivic — Wed, 21 Feb 2024 13:32:57 +0000

Integrations are a big part of any software, but they’re crucial to the functionality of a tool like Productive.

That’s why we decided to dedicate a post series to discussing the importance of integrations in Productive. From enhancing functionality by connecting different tools to streamlining processes and improving user experience, integrations make life easier. We’ll showcase one of our most-used integrations, the one between Productive and Xero, which allows users to seamlessly transfer invoices and synchronize payments between the two tools.

Your agency uses Productive for managing client projects and project financials. At month’s end, you face the daunting task of issuing and sending invoices upon invoices, then replicating the process in Xero. Afterward, you also have to individually mark each payment as received.

And this is where the power of integrations kicks in. With one simple integration, invoices are synced between Xero and Productive, and any payment received in Xero is automatically recorded in Productive.

The integration between Productive and Xero looks interesting, right?

But before actually using the integration, we need to set it up first, and that’s the main focus of this blog post! We’re exploring the implementation of the OAuth 2.0 protocol.

Let’s dive right in!

First, How Do You Connect Xero and Productive?

OAuth 2.0 (Open Authorization 2.0) is an authentication protocol that is considered an industry standard.

Utilizing the OAuth protocol, Productive is granted access to the Xero account without accessing the user’s credentials.

Key features of the OAuth 2.0 protocol:

Granular access control
Authentication without sharing credentials
Token-based authentication (access_token & refresh_token)

In the following steps, the authentication process is explained:

1. The user authorizes Productive, granting access to their own data on the Xero platform:

2. After successful authentication, the user is redirected back to the `redirect_uri` defined in the previous step with two values added as query params:

code → short-lived token that needs to be exchanged for access_token
state → serves as protection against Cross-Site Request Forgery (CSRF) attacks: if the received state attribute value does not match the value submitted in the previous step, the authentication process is terminated

Ensuring the redirection of users back to the Productive app is a crucial aspect of the OAuth flow due to the sensitivity of the information contained in the redirect_uri.
To ensure the user’s redirection to the correct location, we have securely stored the redirect URI within the Xero app.

3. Exchanging the verification code (`code`) for an access token (`access_token`):

4. Retrieving the generated tokens:

access_token is used to call the Xero API, while the refresh_token is used to refresh the access token once it has expired.

5. Retrieving and selecting the tenant whose resources are being accessed:

Each user can have multiple Xero organizations (referred to as tenants).
Xero requires that the xero_tenant_id field is sent as a header param in every HTTP request.

The following code snippet shows the retrieval of all available tenants, from which the user later selects a tenant for current integration in the Marketplace settings:

Now, Let's Create an Integration

In Productive, as well as in many other applications, there is a list of applications called the marketplace. In the marketplace, customers choose the integration they want to use. When the integration is selected, the connection process begins by clicking the “Connect app” button. The connection flow will be demonstrated using the example of Xero.

1. Creating and Connecting An Integration

When establishing an integration, it’s generated in the system. However, to ensure its successful creation, it must align with the authentication requirements on the backend. All integrations require a redirect URI. This URI is used to redirect to Productive after connecting the integration to adjust the settings of each integration. After generating the URI, the integration creation process begins.

If the organization has branches or subsidiaries, you’ll first need to select the subsidiary (branch) for which you want to use Xero.

When creating an integration on the backend, authentication verification is performed, and if all requirements are met, the integration process begins.

During the integration connection, the customer is directed to the interface of the system they want to integrate with, where they also need to provide information to verify if the user is existing and valid. Once completed, the process returns to the marketplace via the redirect URI, initiating the setup of the integration.

2. Redirect

After the customer is redirected to Productive, the data obtained from the external tool is set on the integration model. For example, Xero requires that there is always a code and org representing the code and organization to which the integration connects and exports data in Xero.

Once the integration model has been updated, a call to the backend occurs again to update the integration with new data.

After updating the model, if there are no errors, we set the parameters of the model to transition to the integration setup route, i.e., editing the integration settings.

Conclusion

With successful authentication and integration connection, we’re finishing the first part of the integration posts series. As described in this blog post, understanding OAuth 2.0 becomes not just a necessity but a powerful tool to enhance user experience, safeguard sensitive information, and foster a more trustworthy digital ecosystem. After successful authentication, due to settings related to the external system redirect, in this case, Xero, it brings us back to Productive to continue with further integration setup.

Before we end this blog post, I want to emphasize that the backend part was written by my colleague @bartic.

In our next post, we’ll break down data mapping and show you why it’s a must-know for smooth integrations. Don’t miss out!

Publish/Subscribe with Sidekiq

štef — Wed, 21 Feb 2024 08:00:00 +0000

Introduction

Our Rails application is an old monolith that relies heavily on after/before callbacks to trigger code that has side effects. This usually means that one model can change records of another model. I suspect that we all have come across this "callback hell" at least once in our professional life.

We needed to introduce a new service for search. As we settled on using meilisearch, we needed a way to sync updates on our models with the records in meilisearch. We could've continued to use callbacks but we needed something better.

We settled on a Publish/Subscribe (Pub/Sub) pattern.
Pub/Sub is a design pattern where publishers broadcast messages to topics without specifying recipients, and subscribers listen for specific topics without knowing the source, promoting loose coupling.

Exploring Pub/Sub Solutions

While searching for a solution we came across these:

ActiveSupport::Notification: A Rails component for defining and subscribing to events within an application.
-> Active::Support is synchronous by default. It does not handle errors and retries out of the box. We would need to use a background job handler (like Sidekiq) to make it asynchronous.
Wisper: A Ruby gem providing a decoupled communication layer between different parts of an application
-> I personally dislike wisper. I used it in the past and dislike the way of defining subscribers in a global way. I wanted topics to be arbitrary and each class to define what to subscribe for itself.
Dry-events: A component of the dry-rb ecosystem focused on event publishing and subscription
-> This gem looks like a framework on top of which I would need to create my own Pub/Sub. It also does not do asynchronous execution out of the box.

We finally settled on Sidekiq!
Sidekiq is a background job processing tool, which we already use in our application and we figured a way to use it for processing Pub/Sub messages.

Implementation

Implementation is quite straightforward.

class PubSub
  class SubscriberJob < ApplicationJob
    queue_as :pub_sub

    def perform(message:, class_name:, handler:)
      class_name.constantize.public_send(handler, message)
    end
  end

  include Singleton

  # Publish a message to topic
  #
  # @param topic [String]
  # @param message [Hash]
  def self.publish(topic, **message)
   instance.subscribers(topic).each do |subscriber|
      PubSub::SubscriberJob.perform_later(message: message, **subscriber)
    end
  end

  # Subscribe a class + handler to a topic
  #
  # @param topic [String]
  # @param class_name [String]
  # @param handler [String]
  def self.subscribe(topic, class_name, handler, async: true)
    instance.subscribe(topic, class_name, handler)
  end

  def initialize
    @subscribers = {}
  end

  # return subscribers for the topic
  #
  # @param topic [String]
  # @return [Array<Hash>] { class_name: String, handler: String}
  def subscribers(topic)
    @subscribers.fetch(topic, Set.new)
  end

  # Subscribe a class + handler to a topic
  #
  # @param topic [String]
  # @param class_name [String]
  # @param handler [String]
  def subscribe(topic, class_name, handler, async: true)
      @subscribers[topic] ||= Set.new
      @subscribers[topic] << { class_name: class_name.to_s, handler: handler }
  end
end

Usage

Usage is also quite straightforward:

class Search::Tasks::Indexer
   PubSub.subscribe('task.upsert', self, :on_upsert)  )

   def self.on_upsert(message)
     item = message[:item]

     MeilisearchClient.reindex(item.to_meilisearch)
   end
end

class TaskForm < ApplicationForm
  def save
    super
    PubSub.publish('task.upsert', item: self)
  end
end

Experience

After 6 months of using this method, I can safely say that it works as intended.

It handles unexpected errors like network errors gracefully
It was easy to use where we needed it
Classes that handle the incoming messages are separated from the rest of the models and are easy to unit test

📖 Sidestory: Rethinking Pub-Sub
Recently Buha wrote a blog post how he gave up on the Pub/Sub approach. It is a good read to see the downsides of this approach.

Conclusion

We solved the problem of pub/sub messaging in our product with the help of Sidekiq. It proved to be reliable and of high quality, and the implementation itself is not complicated.

What are your experiences with Sidekiq? Are you using something else?

Engineering Ladder - Being in The Middle Sucks?

Nikola Buhinicek — Tue, 23 Jan 2024 07:59:12 +0000

You might be wondering to what "middle" am I referring to?

It's the middle I'm currently in, the middle of the Engineering Ladder, focusing on the Tech Lead role. This role sits between the Developer/Individual Contributor and "higher" roles like Staff Engineer (SE) or Engineering Manager (EM), combining aspects of both.

A Tech Lead is still contributing to the code base and has its own fair share of tasks/milestones/deliverables, but he is not anymore working "on its own". He has a team of people to lead, to watch over the codebase/architecture, to approve technical specifications, to watch over the product and more.

As I'm diving into this role, I wanted to give you my cent or two on what I think is the most interesting part of the role "upgrade". It's about the Tech Leads relationships to both the Developers on one and the SEs and EMs on the other side.

Just to be clear, this won't be 1:1 to all Tech Lead roles, their responsibilities and their descriptions. This is just my point of view on being a Tech Lead at Productive and the way we see this role.

Tech Lead - baby EM?

If you read books or posts about management, you probably read something like:

Being a Tech Lead is an exercise in leading without authority.

The without authority part is pretty true imo. Your work starts to depend on other people but at the same time you don't really have some authority over them. You can and must have the situation in control but if the situation gets out of hand, you can't resolve it on your own - you need your EM. Again, this depends on the level that your company expects you to work on as a TL.

For me and the way I see it, the most interesting and challenging part of being a Tech Lead is exactly that, the subtle intro to a management role.

It's a role where you should really start thinking a lot about the people you work with.

Several relations come into play and thats mostly bcz you have to watch both up and down from where you're standing.

Looking up the 🪜 - Staff Engineers and Engineering Managers

Your relationship with your EM must be build on solid ground. Your EM has to be a person you enjoy working with, you have a lot to learn from and a person you respect for the things he has done and still is doing. If you are not on the same page, it's less likely that you will benefit from this relationship as much as you could in a "good setup".
In my case, both of my EMs over the time were people I worked with from the first day in Productive. Over the years while working here, I learned a lot from them. The technical aspect is not as important here as the whole overview on how they get things done is - how do they approach problems and how do they solve them.
Other than having a good relationship with you SE/EMs, the main point here is that you have to trust their calls and support them publicly, to help them set those ideas in action. If you don't believe that what they are doing is going to help either you, your team or your organization, you will do a "bad job" here. That's because you in the first place won't accept those new ideas and implement them into your workflows and processes. How could we then expect the rest of our team to do so? One should lead by example.

Looking down the 🪜 - Software Engineers

On the other side, you also have to have really good relations with the people you "manage", the people in your team. In my case, being a TL for the Core Team, there are 3 Backend (BE) and 4 Frontend (FE) engineers (including me and the FE TL).
It is essential for us to know the strengths and weaknesses of our peers so that we could assign them the tasks in which they will prosper and in which they could learn and gain knowledge.
You also need to know them in order to know how to best approach them and to set your EMs ideas in motion. How to present those ideas to them in a way that they also see a benefit in them and not just "something new" they have to do.
These relationships and the work you make on this front will tell you if you are fit to be in management or not. Working with people is not for everyone and those who do it should do it for the right reasons - and that's basically to do it for the people you are managing and seeing them grow.

Looking at yourself 🪞

This is really important. Don't forget to look on yourself - on your motivation, on your moral and your overall "standards".
You could be great with both your SE/EM and your team members, but you shouldn't just be a proxy between them.
Have your opinions, question your EMs ideas, question your team members and look at things without bias. Not everything proposed by any side should be accepted blindly - as I said before, you should know all the details of the changes you are implementing so that you could expect people to follow.
For each idea that you are working on you should know what is the thing/problem your are solving, how are you solving it, what are the benefits of doing this and what could you come by down the road.
Those are the topics that you will most likely be going over with your EM.

What to take home from this 🙃

Ask for Feedback

What I think that people in this role, actually any new position, would benefit the most is feedback

For me, feedback is always welcome. Often times I even ask for it before the scheduled 1-on-1s, 360s or other kinds of performance reviews.

It should be pretty obvious when a newly promoted TL starts doing some of his new chores and that can go in both directions. Anyway it goes, it's great when your EM has your back. When he shares positive feedback about the good things you are doing and how to make the "bad" things better, how to handle them in a more appropriate way.

So my dear EMs, watch on your TLs, look out what they are doing, tailor your 1-on-1's to check the progress and their thoughts. Try remembering when you went thru this phase and what helped you to adjust to this new role.

Tell the people around you what's up

I noticed that my communication to some of my peers lately changed. In our chat there was a lot more work related messages - asking for status updates, asking for estimates, proposing them changes in their workflows, asking for explanations on decision, ... But I never actually told them why did I start making all those questions, asking for answers and started giving advice on workflows/processes.

So I wrote them a message explaining that now, as a TL and owner of some milestones, I have to have a better overview of their work and that we will have to communicate more often.
That I will probably be annoying them with status checks on tasks - trying not to micromanage tho.
That they can involve me in their Github Pull Requests (PRs) where they usually wouldn't - not as a reviewer, just as a subscriber.
I told them that I'm here to help them get unstuck with their task, at least try to help and to further delegate the issue.
That they can always ping me if they needed advice.
And I ended it with - "give me feedback at any point, correct me if doing something wrong or if you don't agree with me on something".

And when I start a conversation with someone new, this is now one of the first messages I start the conversation with.

That seems to be working well so far.

All in all

Ain't gonna lie, this really is a journey with a lot of downs and occasional ups. It's a change that sets you far out of your comfort zone. Just when you think you figured something out, you spot a flaw and find yourself two steps back.

Thoughts of going back to being an individual contributor sound great most the times you hit a low 😅 But if you remember how you learned any new thing, that probably looked a lot like this: you get out of your comfort zone, fail a few or a lot of times but eventually you figure it out. We must get out of our comfort zone to learn and to grow.

Ultimately, this trip to management is not irreversible, you can always take a step back and give it another shot later. Reflect to this experience and try to get the max out of it so that you know better next time.

So, does it suck to be in the middle?

If you ask me, depends on the day 😅 I would say no it doesn't, but damn, it sure isn't easy.

Watch Out When Overriding Memoized Methods

Nikola Buhinicek — Mon, 08 Jan 2024 09:34:33 +0000

This post was inspired by a 🐛 that was caused by not thinking enough when overriding a memoized method. Once we noticed the buggy behaviour, it took me some binding.pry time to figure out what was going on.

Lets check out what happened.

Code snippets

module Actions
  class FormAction
    attr_reader ...

    def initialize(...); ...; end

    def execute_action
      interpolate_properties

      form.process(form_attributes)
    end

    private

    def interpolate_properties
      interpolatable_attributes.each do |attribute|
        form_attributes[attribute] = interpolate(attribute)
      end
    end

    def interpolatable_attributes
      raise NotImplementedError
    end

    ...

    def form_attributes
      @form_attributes ||= action[:attributes].stringify_keys!
    end
  end  
end

A simple Ruby class which is the base class for the specific actions we need to implement for our Rails app.

The action for which we figured out the bug was the action of creating comments on various objects. Its code looks like:

module Actions
  class CreateComment < FormAction
    private

    def interpolatable_attributes
      ['body']
    end

    ...

    def form_attributes
      super.merge("#{commentable_type}_id": action_object.id)
    end
  end
end

Not much code is actually shown but just enough to spot the bug 🐛. Found it?

What was going on

Basically what was happening when running CreateComment#execute_action was the following:

In the execute_action we firstly need to interpolate some attributes - defined by the specific action class. In this case, for the CreateComment action that was only the 'body' attribute.

def execute_action
  # form_attributes = { 'body' => 'non interpolated' } 
  interpolate_properties

  form.process(form_attributes)
end

That's fine - we do some logic, the 'body' attribute gets interpolated and that value is set as the new value for the 'body' key in the form_attributes hash.

def execute_action
  interpolate_properties
  # form_attributes = { 'body' => 'interpolated' }
  form.process(form_attributes)
end

But, as the implementation of the CreateComment class uses super.merge(...) what we get is actually a new hash each time the method is called.

def execute_action
  interpolate_properties

  form.process(form_attributes) 
  # process method called with { 'body' => 'non interpolated' }
end

The next time, after the interpolation is over, the method form_attributes is called when passing that hash to our form object and in that moment, the form object gets a newly generated hash that doesn't have the interpolated 'body' value. So the interpolation was actually pointless as would any other modification of the form_attributes hash prior to the form.process call be.

The solution 🐛🔨

module Actions
  class CreateComment < FormAction
    private

    ...

    def form_attributes
      @form_attributes ||= super.merge("#{commentable_type}_id": action_object.id)
    end
  end
end

Added memoization to the CreateComment#form_attributes method.

Simple as that and when you think about it, also pretty obvious that it needed to look like this from the start.

p.s. These snippets maybe look a bit too simple and memoization probably looks like not needed but it's a stripped version of the real classes. The form_attributes method is called and modified quite a few times 🙃

A Close Call with Real-Time: How Rethinking Pub-Sub Saved the Day

Nikola Buhinicek — Fri, 15 Dec 2023 08:43:10 +0000

All of this started while I was working on our new feature - Automations 🤖. In a nutshell, Automations allow customers to set up actions triggered under specific conditions. Some of the currently implemented actions are sending slack messages, creating and updating tasks, or posting comments on objects.

I wanted that actions that modify tasks and comments would send a message over our real-time system so that our frontend clients (browser, mobile, desktop app) could pick that up and show those changes as they occur.

Currently, our app’s real-time updates are tied to POST/PATCH/DELETE requests. We have a controller extension Extensions::Broadcastable that hooks on save_form and destroy_resource methods and sends a real-time event if the action was successful. However, this approach wasn't suitable for my automation actions, as they don't go thru controllers.

module Api::V2::Extensions::Broadcastable
  extend ActiveSupport::Concern

  private

  def save_form
    super && broadcast_event(action_name == 'create' ? 'created' : 'updated')
  end

  def destroy_resource
    super && broadcast_event('deleted')
  end

  ...
end

As I was digging into this topic, I somehow changed the scope of my task from making the automations actions “live” to revamping our whole broadcasting architecture. I wanted to move that logic out of controllers to a place where I could catch all the changes - which would also catch the automations actions.

📖 Sidestory: A Recent Development in Pub-Sub

Just recently, Stef implemented a Publish-Subscribe architecture in our Rails app. He made it while revamping our search feature. That was a pretty cool moment for the team and it sounded really useful. After the presentation about it, we immediately started thinking what of our current code could've been done with Pub-Sub. No one really revamped anything by the time I was working on this real-time topic.

🧐 Exploring Different Approaches

1. Callbacks

Yeah... altho we all know that callbacks are evil, I couldn’t at least think about them, just for a moment...

2. Forms instead of Controllers

Both the controller actions and my automation actions use the same forms to handle our data. So, why wouldn't I hook on forms and send my real-time messages from there.
This approach was bugging me a bit as we don't use Form objects in all the places we are actually changing our data. So this wouldn't make the whole app feel "live" but I would cover more places than what we have currently. That sounded promising to me.

Wanted to pitch my thoughts to the rest of the Core team. Thru that discussion, a lightbulb moment happened 💡

3. Embracing Pub/Sub

I was mind-blown 🤯 That is exactly what I wanted!!

Publishing events happens for every change. Ofc, except the places we are using methods that skip callbacks (update_column, update_all, ...) as Pub/Sub and the aspect of publishing changes essentially is hooked to callbacks - but that’s a topic for itself 🫠

Making a PoC

As with all big changes in our codebase, and generally in our product, I was putting this code behind a Feature Flag (FF).
Simply, when one would have the pubSubBroadcasting FF enabled I would skip sending the real-time events from the controller actions and I would handle the published events accordingly. If you didn't have that FF, nothing changed.

class Api::V2::Tenanted::TasksController < ApiController
  include Extenstensions::Broadcastable

  ...

  def should_broadcast?
    return false if FeatureFlags.enabled?('pub-sub-broadcasting')

    super
  end

  ...
end

Made a few Subscribers that would listen for all the task and comment related changes and simply handle them as sending a real-time event.

class Core::Tasks::Broadcaster < Realtime::Broadcaster
  PubSub.subscribe('task.upsert', self, :on_upsert)
  PubSub.subscribe('task.delete', self, :on_delete)
  PubSub.subscribe('comment.upsert', self, :on_comment_upsert)
  PubSub.subscribe('comment.delete', self, :on_comment_upsert)

  ...
end

And ofc, added RSpec tests and set up some widgets on New Relic to cover the difference in number of events that we are sending now - as we knew that we are going to send more events now.

Basically that was it. The next step was to slowly propagate this FF over all organizations, check our New Relic metrics and see if nothing breaks. Once we would release that change to all of our user base, we should cover the remaining objects and make subscribers for their Pub/Sub events too.

Showing off with it

As I was pretty proud of this solution, I kinda talked a lot about it and so naturally it popped up in a 1on1 meeting with my EM Lucin. He wanted to discuss that a bit more so I told him the same thing I wrote here. My vibe was like "Isn't that great? Our APP will be LIVE, all the data would be in sync."

His response was "But do we really want that?".

That wasn't the response that I was looking for 🙃

What I didn't know was that our frontend client, once it gets socket messages, depending on the screen the user is, has to make additional API calls so that all the required data could be fetched again. So, a great deal of my real-time events actually ends up generating additional requests to our server and in a way, we are just generating a lot more traffic (self DDoSing?). As I didn't know about this, I wasn't even paying attention to those metrics along the way.

One step forward, two steps back

Let's get better data so that we can make a better call on this.

I took a period of one week from our logs and checked the number of the POST/PATCH/DELETE requests versus the number of dispatched publish events. This in the end would roughly be the same number as the events we are sending over the socket in the current and in the new way.

Tasks endpoint
controller POST/PATCH/DELETE actions - 211k
task.upsert + task.delete publishes  - 263k
> thats ~25% more real-time events

Companies endpoint
controller POST/PATCH/DELETE actions      -  5.2k
company.upsert + company.delete publishes - 20.1k
> thats ~4 times more real-time events

Deals endpoint
controller POST/PATCH/DELETE actions -    31k
deal.upsert + deal.delete publishes  - 1_728k
> thats ~55 times more real-time events!!!

I wasn't really aware of how badly this could end up. I was making a PoC out of this for tasks and comments and you can see here that there wasn't such a big difference. 25% more events was okay, I knew it would be more.

But look at our deals endpoint for example - 55 times more events would be sent. That would add up to the traffic we sent over sockets, to our infrastructure bill for that services, and I don't want to imagine the number of API calls generated as a result of this - by ourselves...

Deals have that much traffic because a lot of other objects update financial data on deals so that was understandable too, it immediately came to us...

Back to the drawing board

1. Pub/Sub shouldn't be a bad call for this

As this is the part of our code that gets all the changes in our data, when wanting to make a frontend client to be as live as it gets, this should be a good call. The solution would be in not sending all the changes over sockets but filter them by relevant and not relevant. This way we would surely see a drop of those mad company and deal numbers.

2. Send all the needed data to front?

As mentioned before, each real-time message already contains the object that was changed. The issue here is that there are a lot of screens in our client and we can't know all the contexts our users are in and what additional data should be sent - which is exactly why our client makes API calls when receiving some socket messages.

3. Why didn't I just resolve the problem I had

To make those actions "live" in our frontend clients (browser, mobile, desktop APP), I wanted to plug them into our real-time system.
So, why didn't I just put a bit of explicit calls to the code of my automations actions where I would just call the class that sends the real-time message.
But no, I wanted to play smart and to fix a problem that wasn't really there in the first place - to make everything more live while no one was asking for it.

The Aftermath

So yeah, the Pub/Sub usage in our real-time part of the app is put on pause until we leverage things up.

I went on with the 3rd solution and added 5 lines of code - a call to the Broadcaster class is one line and I have 5 events over 4 classes to send...

This was a nice learning opportunity for me and I would say that:

When having concrete problems, stick to handling them and resolve them first
I had the data the whole time in New Relic, I should've prepare better
It's not bad to be explicit in code, not everything should be an abstraction, generalization, metaprogramming, ... Hope to write on this point a bit more soon

Good thing in this story is that we didn't actually do any damage with this and we didn't lose a lot of time.

Anyone faced similar problems? If yes, how did you deal with them?

Custom Fields: Give Your Customers the Fields They Need

Nikola Buhinicek — Wed, 06 Dec 2023 10:20:00 +0000

Here at Productive, we’re building an operating system for digital agencies.

But, because each agency is different (think type, size, services they offer, the way they’re set up as an organization…), they need customization options for their workflows. So it’s pretty hard to model all those needs and use cases through a unified data model.

If only there were a way to let them shape those models to their own needs.

Let’s say that one of our customers, ACME digital agency wants to keep track of their employees’ nicknames and to be able to search them by that field. Other than that, they would also like to keep track of their birthdays and be able to sort them and group them by that date.

To me, as a developer, this sounds as simple as it gets—add two new columns to the people table, open those attributes to be editable over the API and send them back in the response.
But should we do that? Should we add all kinds of fields to our models even if those fields are going to be used only by a handful of our customers?

Let me show you how we tackled this type of feature request and made a pretty generic system around it.

What Did Our Customers Want?

It was pretty clear to us what our customers wanted, and that was:

to be able to add additional fields to some of our models (People, Projects, Tasks, …)
to have various data types on those fields (text, number, or date)
to be able to search, sort, or even group by those fields

Our Approach

The Custom Field Model

As we’re building a RESTful API that’s formatted by the JSON:API specification and store our data in a MySQL8 relational database, a few things were pretty straightforward – we need a new model and we’ll name it Custom Field (naming wasn’t an issue here 🥲).

The main attributes of that model should be:

name - string
description - string
data_type_id - integer (1 - text, 2 - number, 3 - date)
  -> enumeration that described the data type of the field
customizable_type - string 
  -> tells us on what data model will that custom field be available, eg. Person, Project, Task, ...
  -> could've also been an enumeration 😅

How To Store the Field Values?

OK, so now that we know how to define custom fields, how can we know which value someone assigned to a custom field for some object? And where to store that information?

Three possible solutions came to mind:

1. Add a limited number of custom_field columns to our models

We can add a few custom_field columns to our models and that will work for some of our customers but there will always be others that need few extra fields. Adding numerous columns to our models surely isn’t the best solution, we can do better than this 😅

2. Add a join table

As mentioned before, while relying on a relational database, a join table sounds like the go-to approach. That table would be a simple join table between the custom field and a polymorphic target (yay, Rails 🥳). Other than those foreign keys, we would have a column to store the value.

3. Add a single JSON column to our models

This sounded as flexible as it gets. It would be a simple map where the key would be the custom field ID and the value would be the assigned value for that custom field.

Why We Ended Up Choosing JSON

The first solution was just too limited so we discarded that one immediately and focused on the remaining two solutions.

On one hand, a better design would be to have the custom field values represented by a model but on the other hand, we won’t actually do much with that data. That would just be data that our users set on our objects, data that isn’t important for our business logic. So a simple JSON column didn’t sound bad either.

The searching and sorting aspect of this feature request was probably the most important one for us. That was supposed to work as fast as it gets, without being a burden to our performance.

That’s why we implemented both solutions, tested a lot of searching/sorting/grouping scenarios (we’ll go through that in more detail soon), and then checked the metrics.

The faster solution was the second one, the one with the JSON column, and that made sense to us. That solution doesn’t use JOIN clauses in SQL since the values are written directly in the searched table and can be queried in the WHERE clause. Luckily for us, MySQL8 supports a bunch of great functions to work with JSON columns (JSON_EXTRACT, JSON_UNQUOTE, JSON_CONTAINS and others).

Great! Now that we know how to store the custom field values too, let’s dig into the coding.

From a development point of view, we did the following:

Added a new model, Custom Field, and implemented CRUD operations that can be called over the API
Wrote schema migrations that added a JSON column – custom_fields – to some of our models (people, projects, tasks, …)
Opened the custom_fields attribute so it can be edited over the API
Wrote a generic validation that checks if all the values in the custom_fields hash have the appropriate data type
Added the custom_fields attribute to the API response of the appropriate models

That was most of the work we needed to do to be able to manage custom fields in our models.

But…what about the searching and sorting aspect of custom fields?

Searching Through Custom Field Values

We already had a generic solution written for searching over the API.

We have a format of sending query params for searching, like filter[attribute][operation]=value. For searching through custom fields, we wanted to keep the same format so we ended with a quite similar one – filter[custom_fields][custom_field_id][operation]=value.

We had to add an if-else statement that would handle the custom fields filtering in a different way than filtering through other attributes as the format contained one additional argument — custom_field_id.

What was different in the filtering logic was that we have to load the custom field that’s being filtered by and check what data type its values are. That’s needed to cast the values into numbers or dates—text values don’t make a difference.

So the query params and its SQL query counterparts, based on custom field type, would look like this:

/* for a text custom field and query filter[custom_fields][1][eq]=abc */
LOWER(custom_fields->>'$."1"') LIKE '%abc%'; 

/* for a number custom field and query filter[custom_fields][2][eq]=42 */
CAST(LOWER(custom_fields->>'$."2"') AS DECIMAL(20,10)) = 42; 

/* for a date custom field and query filter[custom_fields][3][eq]=2022-11-01 */
DATE(LOWER(custom_fields->>'$."3"')) = '2022-11-01';

/* The ->> operator is an alias for JSON_UNQUOTE(JSON_EXTRACT(...)) */

Sorting by Custom Field Values

The concept of sorting by attributes is something we also already tackled by abstracting logic.

The only thing that changes when sorting by custom fields is that we first need to cast the values and then sort by them.

Once again, there’s a small change in the format for custom fields sorters (sort=custom_fields[custom_field_id]) compared to when sorting by a standard attribute (sort=attribute). We need to handle the custom_fields sorters separately because we have to load the desired custom_field and check its type.

Then the ORDER BY statement, based on custom field types, looks like this:

/* for a text custom field and query sort=custom_fields[1] */
ORDER BY CAST(custom_fields->>'$."1"' AS CHAR) ASC; 

/* for a number custom field and query sort=-custom_fields[2]
Noticed the - on the begining? That defines the order of sorting - DESC or ASC */
ORDER BY CAST(custom_fields->>'$."2"' AS unsigned) DESC;

/* for a date custom field and query sort=custom_fields[3] */
ORDER BY DATE(custom_fields->>'$."3"') ASC;

Grouping by Custom Field Values

This was a fun one. The main point here was that you should include the custom fields as some kind of columns stated in the SELECT statement so that you could later use those columns in the GROUP BY statement.

To get the custom field in the SELECT statement, you have to create a virtual column for it. All we needed to do was to extract the values of the grouped custom field and give that virtual column an alias so that we could reference it in the GROUP BY statement. For the column alias we went with the format custom_fields_{custom_field_id}.

For a custom field with id=x, this is done as following:

SELECT ..., custom_fields->>'$."x"' AS custom_fields_x

Once we have the virtual column defined, the grouping part gets done simply, by adding the GROUP BY statement with the earlier mentioned alias.

So in the end, you get a SQL query like:

/* query group=custom_fields[1] */
SELECT ..., custom_fields->>'$."1"' AS custom_fields_1
FROM ...
WHERE ...
GROUP BY custom_fields_1

What Our Customers Got

A simple way to define Custom Fields:

And a place to assign values to their fields:

Summa Summarum

We made it possible for our customers to define custom fields in our data models. Also, we made it possible to search, sort and group by those fields.
It wasn’t long before we had even more requests that built upon our custom fields architecture. The fields we supported at first were okay, but now our customers wanted more field types. They wanted:

to have dropdown custom fields
to have relational custom fields
a field where the values would be objects from one of our existing data models

But before we dig into that, let’s give some time for this basics to sink in. I’ll be back soon with another blog post in which I cover how we solved that new set of feature requests

Decoupling Deployment From Release With Feature Toggles

Davor Tvorić — Wed, 06 Dec 2023 09:47:41 +0000

Deploying new features is a nerve-racking task, but it must be done.

Here’s an issue we’ve all faced or will inevitably face in our careers. Allow me to set the scene…

You’ve finished your work on some features, fixed some bugs, etc. You’re feeling good because some features were a bit more complicated. You decide it’s high time to let your users experience these changes. You bundle up all your teams’ changes and deploy them to the production server. A couple of hours later, disaster strikes! There’s a critical bug on a feature you’ve worked on. Now, you have to revert to the old version of the application or deploy a new one, but without your changes.

As these situations can have a huge impact, reacting quickly is key. Unfortunately, developers usually learn how to handle this situation once it happens, so it only prolongs the agony.

Deploying and releasing at the same time isn’t such an uncommon practice because, at the beginning of a project, you want your users to see the changes immediately. Many projects continue using this process, but as the project grows, it makes sense to decouple the deployment of the code and the release process (activating changes to users).

Even if this hasn’t happened to you yet, we can all agree that it’s a very stressful situation, and establishing a process for handling this is something you want to do ahead of time.

But how much ahead of time are we talking about here?

Feature toggles (aka feature flags)

There are many ways to handle these types of outages, and it’s important to establish a rollback procedure. In addition to that, we also use something called feature toggles. It’s not something unique to us, but it’s a pretty sweet thing to have in your toolbox. For those unfamiliar with feature toggles, it’s a mechanism that allows you to turn a feature on or off for a user without needing to deploy the whole application. It’s really simple, but it’s huge!

Although it’s called a “feature toggle”, it doesn’t necessarily mean that you have to use them for features only. You can use them basically for anything. Whether it’s A/B testing, refactoring a part of the screen, or navigating a user to a different page entirely! I’m sure you can think of a couple of more examples that would be useful to you.

This also allows us to oversee which users can access a certain change. This is helpful when trying to collect feedback or just observing how users interact with something you’ve developed. Once you’ve established your feature toggle system, you don’t need to put in continuous work to keep it going. It’s also a great way to manage the lifecycle of the change by progressively rolling it out to your canary, beta, and all users!

You gain a lot more control over who sees what, and the best part is that turning toggles on or off can be done by anyone! Someone like a product manager or customer support person could decide to turn a toggle on because it’s just something that the customer needs. After a feature has been fully developed, the toggle can be globally released to all customers, and everyone can enjoy the stable version.

The number of annoyed users will decrease, and valuable feedback on changes will increase.

It’s also worth mentioning that you can base your whole QA process on the toggles. We can test a change by turning a toggle on, gathering feedback, and then turning the toggle off. When you have such fine-grained control, testing on production isn’t a big deal! We also use these toggles in other environments, so you can isolate a change to a specific set of users on any server.

It’s a win-win for everyone!

Okay, but what about development?

The main question that’s probably going around in your head is how the feature toggles affect the development of a feature.

Feature toggles do complicate things a bit when writing code, but if you spend some time on planning, it can be done elegantly in most cases. You must be careful to add every change behind the toggle so nothing leaks when the toggle isn’t turned on. Other people working on the feature must also be aware of using the toggle since it probably will span through multiple files and can sometimes be forgotten.

Sometimes, it’s not that complicated, but you gain a lot. As I’ve already mentioned, it can be used for basically anything, and complexity may increase, but it really is worth it:

The added complexity isn’t such a big drawback. You’re getting a lot more out of feature toggles, and if that means that developers have to be a bit more careful when writing and reviewing code, so be it. It’s much less stressful for a developer when they know a feature can be turned off if it’s causing problems. This doesn’t mean we approve of sloppy code, but sometimes, these things happen.

To conclude, feature toggles (once established) don’t affect the development process as much, but they offer a lot in return.

Alleviating the downsides

The first one does introduce technical debt, but if we create a task for deleting a toggle after it’s been globally released, we can be sure that it will be deleted as soon as possible. The person who created the toggle is responsible for deleting it, which is a great way to handle this efficiently. In any case, deleting a toggle usually takes significantly less time than developing it!

As for the other issue, there are two ways to handle this. We can do either of these two:

Release the first toggle globally and delete its references in the code if the feature is ready to be released or
Merge the toggles and consider them to be the same feature from now on

It’s not an ideal situation, but it can happen. The solution mostly depends on the state of the features, so it’s impossible to determine the best way without knowing all the facts. Either way, both solutions can simplify things for development.

Decisions, decisions

For us, implementing feature toggles was a no-brainer. Considering all of the benefits like the control, progressive rollouts, and others, it seriously outweighs the downsides it introduces. Even the downsides can be managed to an extent.

It’s important that everyone in the company understands the whole process and embraces why we’re doing it in such a way. It’s an essential part of a company like ours since every department is involved in the release process of product updates. Once people are aware of this, building an admin system to control this is up to you and your company’s needs.

Here’s an example of our own. We call it the “Backoffice”:

THERE ARE ~200 ACTIVE FEATURE TOGGLES IN OUR CODEBASE

As a developer, I’m grateful we can release features quickly and stress-free, knowing we have some wiggle room.

As for you, I hope I’ve shown you why it’s a good idea to think about something like this if you don’t use a similar mechanism. If you need a bit more convincing, you can read more about it in an article by Martin Fowler! Even if you’re not in a position to make your feature toggle system, there are a lot of external tools and libraries that might help you implement the same!

DEV Community: Productive

How we handled MySQL deadlocks in Productive

Asynchronous jobs in Productive

What are deadlocks?

The experiment

Prep work

Deadlock detection

Analysing the deadlocks

Index optimisation

The second experiment

New deadlocks

Trying composite indexes again

Conclusion

Integrations Series: Authentication And Connection

Integrations are a big part of any software, but they’re crucial to the functionality of a tool like Productive.

First, How Do You Connect Xero and Productive?

1. The user authorizes Productive, granting access to their own data on the Xero platform:

2. After successful authentication, the user is redirected back to the redirect_uri defined in the previous step with two values added as query params:

3. Exchanging the verification code (code) for an access token (access_token):

4. Retrieving the generated tokens:

5. Retrieving and selecting the tenant whose resources are being accessed:

Now, Let's Create an Integration

1. Creating and Connecting An Integration

2. Redirect

Conclusion

Publish/Subscribe with Sidekiq

Introduction

Exploring Pub/Sub Solutions

Implementation

Usage

Experience

Conclusion

Engineering Ladder - Being in The Middle Sucks?

Tech Lead - baby EM?

Looking up the 🪜 - Staff Engineers and Engineering Managers

Looking down the 🪜 - Software Engineers

Looking at yourself 🪞

What to take home from this 🙃

Ask for Feedback

Tell the people around you what's up

All in all

So, does it suck to be in the middle?

Watch Out When Overriding Memoized Methods

Code snippets

What was going on

The solution 🐛🔨

A Close Call with Real-Time: How Rethinking Pub-Sub Saved the Day

📖 Sidestory: A Recent Development in Pub-Sub

🧐 Exploring Different Approaches

1. Callbacks

2. Forms instead of Controllers

3. Embracing Pub/Sub

Making a PoC

Showing off with it

One step forward, two steps back

Back to the drawing board

1. Pub/Sub shouldn't be a bad call for this

2. Send all the needed data to front?

3. Why didn't I just resolve the problem I had

The Aftermath

Custom Fields: Give Your Customers the Fields They Need

What Did Our Customers Want?

Our Approach

The Custom Field Model

How To Store the Field Values?

Why We Ended Up Choosing JSON

Great! Now that we know how to store the custom field values too, let’s dig into the coding.

Searching Through Custom Field Values

Sorting by Custom Field Values

Grouping by Custom Field Values

What Our Customers Got

Summa Summarum

Decoupling Deployment From Release With Feature Toggles

Feature toggles (aka feature flags)

Okay, but what about development?

Alleviating the downsides

Decisions, decisions

2. After successful authentication, the user is redirected back to the `redirect_uri` defined in the previous step with two values added as query params:

3. Exchanging the verification code (`code`) for an access token (`access_token`):