Intermittent Data Sync Issue Between Postgres and Neo4j in Ruby on Rails Application

I have a Ruby on Rails application that uses both Postgres and Neo4j databases. For certain records, data is first saved to Postgres, and then a callback is triggered to update the Neo4j database. This usually works as expected, but occasionally, the Neo4j update doesn't happen, and I can't figure out why.

Here’s some additional context:

• The Postgres-to-Neo4j update is handled inline in the Ruby code usually right after a Postgres update.

• There’s no apparent pattern to when this issue occurs.

• Sometimes, we call a "redundancy" sidekiq job to double save the data to Neo to increase the chance that it actually saves.

• The condition is so intermittent that it's hard to replicate. But it happens often enough that it affects our users' experience in a detrimental way.

My questions are:

• What are potential causes for this kind of behavior in a Rails application?

• How can I debug or trace the root cause more effectively?

• Are there any best practices for ensuring consistent synchronization between Postgres and Neo4j in a Rails application?

Here is some code...

# The space table gets updated on the postgres side
    @space.save
    
    #Neo gets called
    Neo4j::Space.new(@space).save


# The interface with Neo...

    def initialize(space)
    @space = space
    @neo = Neo4j::Db.instance
    end

    def save(send=true)
    return if ENV["NEO4J_NOTUSE"].present?
    return if space.nil?
    
    slug = space.slug
    parents_slugs = space.parents.pluck(:slug)
    linked_parents_slugs = space.linked_parents.pluck(:slug)
    first_parent = space.parents.first

    query = "MERGE (s:Space {slug: '#{slug}'}) " +
    "SET #{Neo4j::Utils.params_to_set_neo4j(data)} " +
    "WITH s " +
    "MATCH (u:User{auth_token: '#{space.creator.present? ? space.creator.authentication_token : SecureRandom.uuid}'}) " +
    "MERGE (s)<-[creator:CREATOR_OF]-(u) " + 
    "WITH s " +
    "OPTIONAL MATCH (parents:Space)-[pp:PARENT_OF]->(s) " +
    "OPTIONAL MATCH (link_parents:Space)-[lp:LINKED_PARENT_OF]->(s) " +
    "DELETE pp " +
    "DELETE lp "
    
    if @space.creator.nil?
      query = "MERGE (s:Space {slug: '#{slug}'}) " +
      "SET #{Neo4j::Utils.params_to_set_neo4j(data)} " +
      "WITH s " +
      "OPTIONAL MATCH (parents:Space)-[pp:PARENT_OF]->(s) " +
      "OPTIONAL MATCH (link_parents:Space)-[lp:LINKED_PARENT_OF]->(s) " +
      "DELETE pp " +
      "DELETE lp "
    end
    
    parents_slugs.each_with_index do |parent_slug, index|
      p = EditorJs::Circle.find_by_slug parent_slug
      _block = EditorJs::Block.find_by_circle_id(slug, p.blocks)
      priority = _block.present? ? _block.priority : 9999
      query += "WITH s "
      query += "MATCH (parent#{index}:Space {slug: '#{parent_slug}'}) " +
      "MERGE (parent#{index})-[rel#{index}:PARENT_OF]->(s) "+
      "ON CREATE SET rel#{index}.position = #{priority} "+
      "ON MATCH SET rel#{index}.position = #{priority} "
    end
    
    linked_parents_slugs.each_with_index do |parent_slug, index|
      query += "WITH s "
      query += "MATCH (link_parent#{index}:Space {slug: '#{parent_slug}'}) " +
      "MERGE (link_parent#{index})-[:LINKED_PARENT_OF]->(s) "
    end
    
    query += "RETURN s;"
    r = @neo.query(query, "write")
    
    space.skip_neo4j = true
    
    puts "neo4jed " * 100
    
    space.update_column :neo4j_updated_at, Time.now
    space.update_column :neo4j_version, ENV["NEO4J_SPACE_DB_VERSION"]

    if first_parent.present? && first_parent.visibility.present?
      space.update_column :visibility, first_parent.visibility
    end
    
    Neo4j::Server.new({action: "space", slug: slug}).send if send
    
    
  end