Implement Audit Trail History for Character Changes.

[nazar]

entries should include: timestamp, user making change, field deltas, optional Game ID, maybe even Game Message.

[nazar]

Hey @mikelax - Some thoughts on the character audit trail feature plus how it will be used when creating and displaying game messages and character headers.

Table structures

character_logs

Records any change to characters.character_details, starting from character creation. Additionally, a change_description column is added to record reasons (i.e. character born, HP loss from damage, etc...)

characters

We add a last_character_log_id column to hold the last recorded change. This is slightly denormalises the table relationship but it makes it quicker and easier to lookup the last character_logs entry for a given character_id

game_messages

This table is modified to record the character_log_id instead of the character_id. Each time a game_messages record is created, we use the character_log_id from characters.last_character_log_id

A pretty sequence diagram to illustrate logic

[mikelax]

hey @nazar great work. I think this is just about there, and totally makes sense. Here are a couple of small comments.

• I would still have a characterId column in the game_messages table. I know it de normalizes, but I think it could aid in looking up character details, ie. profile image. I assume character profile image will always shows the most recent as that will be a quick visual indicator when scrolling through messages who posted what.
• Add a fk to the character_logs table that will link to the prev log id. The initially created log entry will have a null entry for this field.