Investigate change history solutions

[artur-intech]

Requirements

• Soft delete for everything (every record should leave a trail in the system)
  • Objects to exclude from this rule?
  • Should not affect performance (significantly)
• No recovery needed
• Searching and filtering (by what columns?)
  • by EPP operations

Current problems

• Users cannot trust history view in admin (no tests, poor UI)
• Some bugs logging a user who made a change
• Impossible to make any filtering and sorting due to JSON-based object change history

Solutions

Continue using PaperTrail

Disadvantages

• Version management and recovery are not needed

• Needs some refactoring anyway

• Needs update; latest version has incompatible changes, so our code needs to be modified

  • Find out if we can index JSON fields on DB level to perform filtering and searching

Get rid of PaperTrail and create archive database

• Same structure (without constraints?)
• DELETE/UPDATE triggers
• How to keep structure up-to-date
• Similar approach is used in Fred (https://fred.nic.cz/files/fred/fred-database.png)
• https://dba.stackexchange.com/a/14412

Disadvantages

• Old data conversion

---

Notes:

• Fred log system is ok
• https://docs.google.com/a/eestiinternet.ee/document/d/1GFs_kxVElYoCicZ69vAaUIuol4ri23_WbxJNir0RV6M/edit?usp=sharing_eil&ts=59364fb7

---

related tickets:
#944

[artur-intech]

Needed for #269
See also #426

[artur-intech]

https://docs.google.com/document/d/1GFs_kxVElYoCicZ69vAaUIuol4ri23_WbxJNir0RV6M/edit?ts=59364fb7#heading=h.57eceh79hphg

[artur-intech]

@vohmar Please provide requirements for 3rd list item in "Requirements" section

[vohmar]

domain:

• date + period
• domain name
• contact
  • contact name
  • contact code (personal, business id, birthday)
  • contact country
  • contact type
• status
• host name
• host ip
• registrar
• epp requests (create, update, renew, transfer, delete)

contact:

• date + period
• contact name
• contact code (personal, business id, birthday)
• contact country
• contact type
• status
• registrar
• epp requests (create, update, renew, transfer, delete) + related epp requests (domain update, domain create)
• domain (domains that contact is associated to)

registrar:

• date + period
• registrar name
• business id
• domain

[artur-intech]

@ratM1n This needs your input regarding JSON support in Postgres

[teadur]

@artur-beljajev Postgres does support indexing on JSON fields.

There are two JSON data types: json and jsonb. They accept almost identical sets of values as input. The major practical difference is one of efficiency. The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. jsonb also supports indexing, which can be a significant advantage.

https://www.postgresql.org/docs/9.4/static/datatype-json.html

[artur-intech]

https://www.postgresql.org/docs/9.4/static/tutorial-inheritance.html
https://dba.stackexchange.com/questions/28655/database-archive-solutions

[artur-intech]

https://wiki.postgresql.org/wiki/Audit_trigger

[artur-intech]

http://propelorm.org/blog/2011/08/29/introducing-archivable-behavior-and-why-soft-delete-is-deprecated.html