Feature/activity logger

backend/config/database/schema.sql

@@ -13,12 +13,11 @@ CREATE TABLE IF NOT EXISTS  users (
   FOREIGN KEY (roleID) REFERENCES rolesPermissions(roleID)
 );
 
--- Table to log login/logout information
-CREATE TABLE IF NOT EXISTS userLoginLogout (
+-- Table to log login information
+CREATE TABLE IF NOT EXISTS userLogin (
   eventId INT AUTO_INCREMENT PRIMARY KEY,
   eventTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   userID INT NOT NULL, 
-  eventType VARCHAR(255) NOT NULL,   -- successfulLogin, unsuccessfulLogin, logout
   FOREIGN KEY (userID) REFERENCES users(userID)
 );
 
@@ -120,8 +119,18 @@ CREATE TABLE IF NOT EXISTS peopleXInvolvement (
     FOREIGN KEY (involvementId) REFERENCES involvementLookup(involvementId)
 );
 
+CREATE TABLE IF NOT EXISTS changeLog (
+    logId INT PRIMARY KEY AUTO_INCREMENT,
+    changedTable VARCHAR(255) NOT NULL,   -- e.g., "people" to specify the table updated
+    recordId INT NOT NULL,                -- ID of the record that was updated
+    userId INT NOT NULL,                  -- User who made the change (foreign key to `users` table)
+    changeTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+    changeType ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
+    changedColumns TEXT NOT NULL,         -- Names of columns that were changed
+    oldValues TEXT,                       -- JSON format for previous values
+    newValues TEXT,                       -- JSON format for new values
+    FOREIGN KEY (userId) REFERENCES users(userID)
+);
+
 INSERT INTO countryLookup (countryId, name) 
 VALUES (1, 'United States');
-
--- I think there is already a people table that exists in our DB and 
--- has data that does not include peopoleID
\ No newline at end of file