Persist entity history (objects) using Mongodb

Question

I have the following entity (decreased from the actual entity):

@Entity
@SequenceGenerator(name = "produtoSequence", sequenceName = "sequenceproduto")
public class Produto {
    
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "produtoSequence")
    private long id;
    
    private String nome;
    
    @Temporal(TemporalType.TIMESTAMP)
    @Column(nullable = true)
    private Date dataExclusao;
    
    private String categoria;

    //Getters e setters .....

Now you would need to include another entity, which will refer to a history. This history will basically store information about what happens to the product. A log in this case would not be enough, because I need to take some metrics from this history.

An example of history: a product has been deleted, a product exclusion history will be created, or a new product has been added, so a product inclusion history will be created.

I decided to use Nosql to store this information, especially Mongodb, because the data is saved in JSON, and do not have a pattern that I should follow (tables that appear in all queries, if I need a new data, I need to insert a new column). This is because the histories will sometimes have different data depending on the saved history.

Examples of Jsons that would represent the history.

History of product creation:

{
    "id": 1,
    "produto": {
            "id": 15,
            "nome": "Máquina de lavar XPTO2000",
            "categoria": "Máquinas de lavar"
        },
    "dataCriacao": "11/06/2021",
    "categoriaHistorico": "Criação de produto"
}

History of category change:

{
    "id": 2,
    "produto": {
            "id": 15,
            "nome": "Máquina de lavar XPTO2000",
            "categoria": "Linha branca"
        },
    "categoriaAnterior": "Máquinas de lavar",
    "categoriaNova": "Linha Branca",
    "categoriaHistorico": "Alteração de categoria do produto"
}

This lack of pattern will help me a lot to develop, but how would you build a model for History that would communicate with the product model? If it were SQL to SQL it would be something like (below) using JoinColumn and fetch, but handling SQL for Nosql, I haven’t found any notes that make this conversion or even an integration between the two databases.

 public class Historico {
     @Id
     private long id;
    
     @ManyToOne(fetch = FetchType.EAGER)
     @JoinColumn(name = "PRODUTO", referencedColumnName = "ID", foreignKey = @ForeignKey(name = "HISTORICO_PRODUTO_ID_FK"))
     private Produto produto;
    
     @Temporal(TemporalType.TIMESTAMP)
     private Date dataCriacao = new Date();
     //Getters e setters......

How could I save several "layouts" of history? I thought, in the backend, write an interface that contains the attributes that will be used in all history, and write implementations that have unique attributes, but I don’t know if there is such a possibility. Is that so wrong? Otherwise, I think of a gigantic class that would grow as new histories were created, which would be impossible to scale.

Answer 1

Mongodb as a choice

How to build a model for History that communicates with the product model?

The idea of MongoDB is good, in my view. The communication from one bank to the other would be recording the id of postgres in the Mongo. I see Mongodb as a good bank to maintain the system because it is very easy to abstract the idea that is implemented.

I thought of a service specific to Mongo, as well as a repository. You receive the postgres id in this service to make the history query in the Mongo.

Examples of History Objects

I thought of something like this..

{ 
    version: 1,
    id_relacional: 10,
    author: "joaosilva",
    when: new Date('06 07 2021'),
    created: 1,
    fields: {id: 5, name: "raquete de tenis", price: 450}
},
{ 
    version: 2,
    id_relacional: 10,
    author: "luisduarte",
    when: new Date('06 07 2021'),
    updated: 1,
    fields: {price: 400} -- o preço mudou para 400
},
{ 
    version: 3,
    id_relacional: 10,
    author: "luisduarte",
    when: new Date('06 10 2021'),
    updated: 1,
    fields: {name: "Raquete de tenis amarela e verde"} -- mudou o nome
},
{ 
    version: 4,
    id_relacional: 10,
    author: "fulanodetal",
    when: new Date('06 15 2021'),
    updated: 1,
    fields: {description: "A raquete é legal, bonita etc"} -- adicionou description
}

I considered that the important thing is that you have mapped which fields are modifying.

Real inserts in Mongodb for testing:

db.history.insertOne({ 
    version: 1,
    id_relacional: 10,
    author: "joaosilva",
    when: new Date('06 07 2021'),
    created: 1,
    fields: {id: 5, name: "raquete de tenis", price: 450}
})
db.history.insertOne({ 
    version: 2,
    id_relacional: 10,
    author: "luisduarte",
    when: new Date('06 07 2021'),
    updated: 1,
    fields: {price: 400}
})
db.history.insertOne({ 
    version: 3,
    id_relacional: 10,
    author: "luisduarte",
    when: new Date('06 10 2021'),
    updated: 1,
    fields: {name: "Raquete de tenis amarela e verde"}
})

db.history.insertOne({ 
    version: 4,
    id_relacional: 10,
    author: "fulanodetal",
    when: new Date('06 15 2021'),
    updated: 1,
    fields: {description: "A raquete é legal, bonita etc"}
})

Possible queries in the history

Get all product history from id_relacional (id in the postgres bank) = 10

db.history.find({id_relacional: 10})

Change the product of id_relacional = 10 and author = fulanodetal:

db.history.find({id_relacional: 10, author: "fulanodetal"})

Modifications after 10/06/2021:

db.history.find({id_relacional: 10, when: {$gt : new Date('2021 06 10')} })

Changes in the product description field:

db.history.find({id_relacional: 10, "fields.description" : {$exists: true} })

After elaborating the answer I think it would be interesting to add the time in the field when because the time when the change was made would be important.

If this solution does not meet, explain the reasons why I see this demand constantly and I think we can make good discussions about it.