Coko Server work for Ketida Audit Trail
As in https://gitlab.coko.foundation/ketida/ketida/-/issues/754
Context
See https://gitlab.coko.foundation/ketida/ketida/-/issues/711 for the full context of the use case and actions that need to be tracked.
On the application's side (ketida/server
) the method auditLog
should be injected in all the controllers relevant to required actions that you want to log. That work will be done by Amnet's team in https://gitlab.coko.foundation/ketida/ketida/-/issues/711
In coko/server
a new service will be created which will expose a method e.g. auditLog(actingUserId:uuid, event:Object)
. Additionally, a corresponding db table will be introduced and provided by coko/server
where all these type of records will be stored. The schema of that table will be similar to Amnet's suggestion provided below.
Tracking User Events and Storing in an Event Table with Many-to-One Relationship.
This technical document outlines the process of tracking user events, such as button clicks, and storing them in a table linked to the user table. The goal is to capture and store user events along with relevant metadata (date, time, user ID) for analysis and monitoring purposes. In this document, we will focus on creating an audit_trail table that establishes a many-to-one relationship with the user table.
- Database Design Design the database schema to store user events. The schema should include the following tables:
User Table: Contains user information, including a unique user ID. audit_trail Table: Stores user events, including the event ID, user ID (foreign key), event type, timestamp, and any additional event-specific metadata. Establish a many-to-one relationship between the Event Table and the User Table. Each user can have multiple events associated with them.
- Database Implementation To implement the database and table structure, follow these steps:
Create the audit_trail Table: Define the audit_trail Table with the necessary fields such as event_id, user_id (foreign key), event_type, timestamp, and additional event-specific fields as required. Establish Many-to-One Relationship: Set up a foreign key constraint in the audit_trail Table, linking the user_id column to the id column in the User Table. This establishes the many-to-one relationship between the two tables.
- Event Tracking Implementation To track user events and store them in the database, follow these steps:
Event Handling: Implement the common function that triggers every time we try to save an event Store Event Data in the Database: Use database operations (e.g., SQL queries or ORM methods) to insert the event data into the audit_trail Table. Ensure that the user ID is correctly associated with the event by linking it to the User Table.
-- Pseudocode for Audit Trail –
// Middleware to run after the mutation has completed
function postMutationMiddleware(req, res, next) { // Perform actions after the mutation, e.g., logging, notifications, etc. console.log('Post-mutation middleware executed.'); // You can access response data here if needed. next(); }
LIST OF GRAPHQL’s API for Audit Trail:-
Login - KetidaLogin Reset password - RequestPasswordReset Signup - SignUpInput Add book - CreateBook Edit button - GetBook Rename - RenameBook Archive - ArchiveBook Profile - CurrentUser Team Manager - GetUsersTeams Show archived - GetBookCollections Upload word files - IngestWordFiles MetaData and Workflow stages - GetBook Asset manager - GetEntityFilesQuery ExportBook - ExportBook Add component, Add chapter, Add unnumbered and Add notes Placeholder - CreateBookComponent Edit chapter - GetBookComponent Delete Chapter - DeleteBookComponent Templates To get all templates - GetTemplates To get a single template - GetTemplate To Create a template - CreateTemplate To Update a template - UpdateTemplate To Delete a template - DeleteTemplate
Data Retention
Data Retention Default Period Should be 1 Month, Make it as configurable in the environment file.
Cleanup works:
Implement a cron job that cleans the data in the main database and moves it to a secondary database if necessary.
Pseudocode for Cron job to delete data from TableA and move it to TableB
Schedule the Cron job:
- Set the interval at which the Cron job should run (e.g.,monthly.)
- Set the time for the Cron job to start
End the Cron job and close the database connection
Sample pseudocode for moving data from TableA to TableB:
Function moveDataFromTableAToTableB(): Try: StartTransaction() # Delete data from TableB (optional step if TableB needs to be cleared before moving data) ExecuteSQLQuery("DELETE FROM TableB;") # Move data from TableA to TableB ExecuteSQLQuery("INSERT INTO TableB (column1, column2, ...) SELECT column1, column2, ... FROM TableA;") CommitTransaction() Catch Exception as e: RollbackTransaction() LogError(e)