Audit Trail
Context
Audit Trail
One of our customers is planning to use multiple Ketida instances for various publishers and they wanted to track all the activities performed by the users in Ketida.
Proposal
Following activities should be captured as an action and recorded in the database.It should include the user name, date and time, and the action performed by the user.
Login - Login, Reset password - Send email, Signup -Signup,
Book Dash - Add book, Book Dash - Edit button, Book Dash -Rename, Book Dash - Archive, Book Dash - Assign team members, Book Dash - Profile, Book Dash -Team Manager, Book Dash - Show archived,
Project page - Upload word files, Project page - Team Manager, Project page - Meta data, Project page - Asset Manager, Project page - Export book, Project page - Add component, Project page - Edit, Project page - Delete, Project page - Add chapter, Project page - Add unnumbered, Project page - Add notes placeholder, Project page - Workflow items,
Templates - Add template, Templates - Edit, Templates - Delete, Templates - Save
Technical Implementation (if applicable)
Technical Document: Tracking User Events and Storing in an Event Table with Many-to-One Relationship.
Introduction 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)
Alternative approaches (if applicable) None Open issues (if applicable) None