Weekend Project: Creating a Data-Driven Application Using Multiple Database Technologies

Introduction to the Weekend Project

In this weekend project, you will build a data-driven application that leverages multiple database technologies. This experience will solidify your understanding of various database paradigms and help you make informed decisions about when to use each technology in real-world scenarios.

Throughout the project, we'll use George Polya's famous 4-step problem-solving framework to guide our approach:

This approach, developed by mathematician George Polya in his book "How to Solve It" (1945), provides a systematic framework for tackling complex problems. It's particularly valuable in software development where requirements and constraints must be carefully analyzed before building solutions.

Project Overview: Multi-Database Task Management System

You will create a task management system called TaskForge that uses different database technologies for different aspects of the application:

• MongoDB: For flexible, document-based storage of task details and user profiles
• MySQL/PostgreSQL: For structured relational data like team memberships, permissions, and audit logs
• Redis: For caching, session management, and real-time notifications

Step 1: Understand the Problem

The first step in Polya's method is to clearly understand the problem. For our TaskForge application, we need to identify the requirements, constraints, and use cases.

Requirements Analysis

• Functional Requirements:
  • User management (registration, authentication, profiles)
  • Task management (creation, assignment, updates, deletion)
  • Team organization (creation, membership, permissions)
  • Comments and attachments on tasks
  • Notifications (real-time and email)
  • Activity tracking and audit logging
  • Search and filtering capabilities
  • Dashboard with analytics
• Technical Requirements:
  • Node.js backend with Express
  • Integration with MongoDB, MySQL/PostgreSQL, and Redis
  • RESTful API design
  • Security (authentication, authorization, data protection)
  • Performance optimization through appropriate database usage
  • Simple frontend for demonstration (can use templates or minimal React)

Database Technology Selection Rationale

Understanding the Challenges

• Data Consistency: How to maintain consistency across different databases
• Transaction Management: Handling operations that span multiple databases
• Data Synchronization: Keeping data in sync when it exists in multiple places
• Complexity: Managing the increased complexity of multiple database systems
• Error Handling: Dealing with partial failures in distributed systems

Step 2: Devise a Plan

In this step, we'll outline the architectural design of our application and plan how we'll integrate the different database technologies.

System Architecture

Data Models

Let's define the core data models for each database:

MongoDB Models

// User Model
const userSchema = new mongoose.Schema({
    userId: String,  // UUID, links to relational DB
    email: {
        type: String,
        required: true,
        unique: true
    },
    passwordHash: String,
    firstName: String,
    lastName: String,
    profilePicture: String,
    bio: String,
    preferences: {
        theme: {
            type: String,
            enum: ['light', 'dark', 'system'],
            default: 'system'
        },
        notifications: {
            email: {
                type: Boolean,
                default: true
            },
            push: {
                type: Boolean,
                default: true
            },
            frequency: {
                type: String,
                enum: ['immediate', 'hourly', 'daily', 'weekly'],
                default: 'immediate'
            }
        }
    },
    metadata: {
        created: {
            type: Date,
            default: Date.now
        },
        lastLogin: Date,
        lastPasswordChange: Date
    }
});

// Task Model
const taskSchema = new mongoose.Schema({
    taskId: String,  // UUID, links to relational DB if needed
    title: {
        type: String,
        required: true
    },
    description: String,
    status: {
        type: String,
        enum: ['backlog', 'todo', 'in_progress', 'in_review', 'done'],
        default: 'todo'
    },
    priority: {
        type: String,
        enum: ['low', 'medium', 'high', 'urgent'],
        default: 'medium'
    },
    assigneeId: String,
    creatorId: String,
    teamId: String,
    dueDate: Date,
    tags: [String],
    attachments: [{
        name: String,
        url: String,
        size: Number,
        mimetype: String,
        uploadedAt: {
            type: Date,
            default: Date.now
        },
        uploadedBy: String
    }],
    customFields: mongoose.Schema.Types.Mixed,  // Flexible fields depending on task type
    metadata: {
        created: {
            type: Date,
            default: Date.now
        },
        updated: Date
    }
});

// Comment Model
const commentSchema = new mongoose.Schema({
    taskId: {
        type: String,
        required: true
    },
    authorId: {
        type: String,
        required: true
    },
    content: {
        type: String,
        required: true
    },
    attachments: [{
        name: String,
        url: String,
        size: Number,
        mimetype: String
    }],
    mentionedUsers: [String],
    created: {
        type: Date,
        default: Date.now
    },
    edited: Boolean,
    lastEditedAt: Date
});
            

MySQL/PostgreSQL Models

-- Users Table
CREATE TABLE users (
    user_id VARCHAR(36) PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Teams Table
CREATE TABLE teams (
    team_id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_by VARCHAR(36) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(user_id)
);

-- Team Memberships Table
CREATE TABLE team_memberships (
    team_id VARCHAR(36) NOT NULL,
    user_id VARCHAR(36) NOT NULL,
    role ENUM('admin', 'member', 'guest') NOT NULL DEFAULT 'member',
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (team_id, user_id),
    FOREIGN KEY (team_id) REFERENCES teams(team_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- Tasks Table (minimal reference info - detail in MongoDB)
CREATE TABLE tasks (
    task_id VARCHAR(36) PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    team_id VARCHAR(36) NOT NULL,
    assignee_id VARCHAR(36),
    status ENUM('backlog', 'todo', 'in_progress', 'in_review', 'done') DEFAULT 'todo',
    created_by VARCHAR(36) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (team_id) REFERENCES teams(team_id),
    FOREIGN KEY (assignee_id) REFERENCES users(user_id),
    FOREIGN KEY (created_by) REFERENCES users(user_id)
);

-- Audit Logs Table
CREATE TABLE audit_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    entity_type ENUM('user', 'team', 'task', 'comment') NOT NULL,
    entity_id VARCHAR(36) NOT NULL,
    action ENUM('create', 'update', 'delete', 'assign', 'status_change') NOT NULL,
    actor_id VARCHAR(36) NOT NULL,
    action_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    previous_state JSON,
    new_state JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    FOREIGN KEY (actor_id) REFERENCES users(user_id)
);

-- Permissions Table
CREATE TABLE permissions (
    permission_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT
);

-- Role Permissions Table
CREATE TABLE role_permissions (
    team_id VARCHAR(36) NOT NULL,
    role ENUM('admin', 'member', 'guest') NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (team_id, role, permission_id),
    FOREIGN KEY (team_id) REFERENCES teams(team_id),
    FOREIGN KEY (permission_id) REFERENCES permissions(permission_id)
);
            

Redis Data Structures

// Session Management
// Key: `session:{sessionId}`
// Value: JSON string with session data
{
    "userId": "user123",
    "email": "user@example.com",
    "name": "John Doe",
    "issuedAt": 1620000000,
    "expiresAt": 1620086400
}

// User Sessions Index
// Key: `user:sessions:{userId}`
// Value: Set of active sessionIds
["session1", "session2", "session3"]

// Cache for User Profile
// Key: `user:profile:{userId}`
// Value: JSON string with user profile
// TTL: 3600 seconds (1 hour)
{
    "userId": "user123",
    "name": "John Doe",
    "email": "user@example.com",
    "profilePicture": "https://example.com/pic.jpg"
}

// Cache for Task Details
// Key: `task:{taskId}`
// Value: JSON string with task details
// TTL: 300 seconds (5 minutes)
{
    "taskId": "task123",
    "title": "Implement Redis",
    "status": "in_progress",
    "assignee": "user123",
    "dueDate": "2023-08-01"
}

// Notification Queue
// Key: `notifications:queue`
// Value: List of notification payloads
[
    "{\"userId\":\"user123\",\"message\":\"New task assigned\",\"taskId\":\"task123\"}",
    "{\"userId\":\"user456\",\"message\":\"Task completed\",\"taskId\":\"task789\"}"
]

// User Notification Counter
// Key: `user:notifications:count:{userId}`
// Value: Integer counting unread notifications
5

// Online User Tracking
// Key: `online:users`
// Value: Sorted Set with userId and last activity timestamp
["user123", 1620000000, "user456", 1620000030]

// Rate Limiting
// Key: `ratelimit:api:{userId}`
// Value: Integer count of API calls in time window
// TTL: 60 seconds
15
            

Integration Strategy

We need a clear strategy for how the databases will interact:

Key integration patterns:

• Data Duplication with Source of Truth: For each entity, designate one database as the source of truth, and duplicate only necessary data in other databases.
• Event-Driven Updates: When data changes in one database, publish events that other components can subscribe to for updating their own data.
• Bounded Transactions: For operations requiring consistency, limit transaction scope to a single database when possible.
• Eventual Consistency: Accept that some cross-database operations will achieve consistency over time, not immediately.
• Caching Strategy: Use Redis to cache frequently accessed data with appropriate TTLs and invalidation strategies.

Project Planning

Breaking down the implementation into manageable tasks:

1. Day 1 (Morning): Setup and Configuration
   • Set up project structure and initialize Git repository
   • Install necessary packages and dependencies
   • Configure MongoDB, MySQL/PostgreSQL, and Redis connections
   • Create basic Express server and API structure
2. Day 1 (Afternoon): Core Models and Database Schema
   • Implement MongoDB schemas
   • Create SQL database tables
   • Define Redis data structures
   • Implement database connection management
3. Day 2 (Morning): User Management and Authentication
   • Implement user registration and authentication
   • Set up session management with Redis
   • Create profile management functionality
4. Day 2 (Afternoon): Task and Team Management
   • Implement task CRUD operations
   • Create team management functionality
   • Set up permissions and access control
5. Day 3 (Morning): Comments, Notifications, and Audit Logging
   • Add comment functionality
   • Implement notification system using Redis
   • Create audit logging for all operations
6. Day 3 (Afternoon): Frontend Integration and Testing
   • Create a simple frontend for demonstration
   • Test all functionality end-to-end
   • Fix bugs and refine implementation

Step 3: Execute the Plan

Now we'll implement our TaskForge application according to our planned architecture. Let's work through the key implementation aspects.

Project Setup

mkdir taskforge
cd taskforge
npm init -y
npm install express mongoose mysql2 redis dotenv cors helmet jsonwebtoken bcrypt morgan
npm install --save-dev nodemon

# Create project structure
mkdir -p src/{config,controllers,models,routes,services,middleware,utils}
touch .env .gitignore README.md
            

Database Configuration

// src/config/mongodb.js
const mongoose = require('mongoose');

const connectMongoDB = async () => {
    try {
        await mongoose.connect(process.env.MONGODB_URI, {
            useNewUrlParser: true,
            useUnifiedTopology: true
        });
        console.log('MongoDB connected successfully');
    } catch (error) {
        console.error('MongoDB connection error:', error);
        process.exit(1);
    }
};

module.exports = connectMongoDB;

// src/config/mysql.js
const mysql = require('mysql2/promise');

let pool;

const connectMySQL = async () => {
    try {
        pool = mysql.createPool({
            host: process.env.MYSQL_HOST,
            user: process.env.MYSQL_USER,
            password: process.env.MYSQL_PASSWORD,
            database: process.env.MYSQL_DATABASE,
            waitForConnections: true,
            connectionLimit: 10,
            queueLimit: 0
        });
        
        // Test connection
        const connection = await pool.getConnection();
        console.log('MySQL connected successfully');
        connection.release();
    } catch (error) {
        console.error('MySQL connection error:', error);
        process.exit(1);
    }
};

const getPool = () => pool;

module.exports = {
    connectMySQL,
    getPool
};

// src/config/redis.js
const redis = require('redis');
const { promisify } = require('util');

let client;
let getAsync;
let setAsync;
let delAsync;
let expireAsync;

const connectRedis = async () => {
    try {
        client = redis.createClient({
            host: process.env.REDIS_HOST,
            port: process.env.REDIS_PORT,
            password: process.env.REDIS_PASSWORD
        });
        
        // Promisify Redis commands
        getAsync = promisify(client.get).bind(client);
        setAsync = promisify(client.set).bind(client);
        delAsync = promisify(client.del).bind(client);
        expireAsync = promisify(client.expire).bind(client);
        
        client.on('connect', () => {
            console.log('Redis connected successfully');
        });
        
        client.on('error', (err) => {
            console.error('Redis connection error:', err);
        });
    } catch (error) {
        console.error('Redis initialization error:', error);
        process.exit(1);
    }
};

module.exports = {
    connectRedis,
    getClient: () => client,
    getAsync,
    setAsync,
    delAsync,
    expireAsync
};
            

Key Implementation Components

Service Layer for Task Management

// src/services/taskService.js
const Task = require('../models/mongodb/task');
const { getPool } = require('../config/mysql');
const { setAsync, delAsync } = require('../config/redis');
const { v4: uuidv4 } = require('uuid');

class TaskService {
    async createTask(taskData, userId) {
        const mysqlPool = getPool();
        const connection = await mysqlPool.getConnection();
        
        try {
            // Begin transaction
            await connection.beginTransaction();
            
            // Generate a UUID for the task
            const taskId = uuidv4();
            
            // Insert basic task info into MySQL
            const sqlQuery = `
                INSERT INTO tasks (task_id, title, team_id, assignee_id, created_by)
                VALUES (?, ?, ?, ?, ?)
            `;
            
            await connection.execute(sqlQuery, [
                taskId,
                taskData.title,
                taskData.teamId,
                taskData.assigneeId || null,
                userId
            ]);
            
            // Create detailed task document in MongoDB
            const task = new Task({
                taskId,
                title: taskData.title,
                description: taskData.description,
                status: taskData.status || 'todo',
                priority: taskData.priority || 'medium',
                assigneeId: taskData.assigneeId,
                creatorId: userId,
                teamId: taskData.teamId,
                dueDate: taskData.dueDate,
                tags: taskData.tags || [],
                customFields: taskData.customFields || {}
            });
            
            await task.save();
            
            // Log audit record
            const auditQuery = `
                INSERT INTO audit_logs 
                (entity_type, entity_id, action, actor_id, new_state)
                VALUES ('task', ?, 'create', ?, ?)
            `;
            
            await connection.execute(auditQuery, [
                taskId,
                userId,
                JSON.stringify({ 
                    title: taskData.title,
                    teamId: taskData.teamId,
                    assigneeId: taskData.assigneeId
                })
            ]);
            
            // Commit transaction
            await connection.commit();
            
            // Cache task in Redis (5 minutes TTL)
            await setAsync(`task:${taskId}`, JSON.stringify({
                taskId,
                title: taskData.title,
                status: taskData.status || 'todo',
                assigneeId: taskData.assigneeId,
                teamId: taskData.teamId
            }), 'EX', 300);
            
            // If task is assigned, add to notification queue
            if (taskData.assigneeId) {
                const notification = {
                    userId: taskData.assigneeId,
                    type: 'task_assigned',
                    data: {
                        taskId,
                        taskTitle: taskData.title
                    },
                    timestamp: new Date().toISOString()
                };
                
                await this.queueNotification(notification);
            }
            
            return task;
        } catch (error) {
            // Rollback transaction on error
            await connection.rollback();
            throw error;
        } finally {
            connection.release();
        }
    }
    
    async getTaskById(taskId) {
        try {
            // Try to get from cache first
            const cachedTask = await getAsync(`task:${taskId}`);
            if (cachedTask) {
                return JSON.parse(cachedTask);
            }
            
            // If not in cache, get from MongoDB
            const task = await Task.findOne({ taskId });
            if (!task) {
                throw new Error('Task not found');
            }
            
            // Update cache
            await setAsync(`task:${taskId}`, JSON.stringify(task), 'EX', 300);
            
            return task;
        } catch (error) {
            throw error;
        }
    }
    
    async updateTask(taskId, updateData, userId) {
        const mysqlPool = getPool();
        const connection = await mysqlPool.getConnection();
        
        try {
            await connection.beginTransaction();
            
            // Get current task state for audit logging
            const currentTask = await Task.findOne({ taskId });
            if (!currentTask) {
                throw new Error('Task not found');
            }
            
            // Update in MongoDB
            const updatedTask = await Task.findOneAndUpdate(
                { taskId },
                { $set: updateData },
                { new: true }
            );
            
            // Update in MySQL if necessary fields changed
            if (updateData.title || updateData.assigneeId || updateData.status) {
                const updates = [];
                const params = [];
                
                if (updateData.title) {
                    updates.push('title = ?');
                    params.push(updateData.title);
                }
                
                if (updateData.assigneeId !== undefined) {
                    updates.push('assignee_id = ?');
                    params.push(updateData.assigneeId);
                }
                
                if (updateData.status) {
                    updates.push('status = ?');
                    params.push(updateData.status);
                }
                
                if (updates.length > 0) {
                    params.push(taskId);
                    
                    const sqlQuery = `
                        UPDATE tasks
                        SET ${updates.join(', ')}, updated_at = CURRENT_TIMESTAMP
                        WHERE task_id = ?
                    `;
                    
                    await connection.execute(sqlQuery, params);
                }
            }
            
            // Log audit record
            const auditQuery = `
                INSERT INTO audit_logs 
                (entity_type, entity_id, action, actor_id, previous_state, new_state)
                VALUES ('task', ?, 'update', ?, ?, ?)
            `;
            
            await connection.execute(auditQuery, [
                taskId,
                userId,
                JSON.stringify(currentTask),
                JSON.stringify(updatedTask)
            ]);
            
            await connection.commit();
            
            // Invalidate cache
            await delAsync(`task:${taskId}`);
            
            // If assignee changed, send notification
            if (updateData.assigneeId && updateData.assigneeId !== currentTask.assigneeId) {
                const notification = {
                    userId: updateData.assigneeId,
                    type: 'task_assigned',
                    data: {
                        taskId,
                        taskTitle: updateData.title || currentTask.title
                    },
                    timestamp: new Date().toISOString()
                };
                
                await this.queueNotification(notification);
            }
            
            // If status changed, notify creator
            if (updateData.status && updateData.status !== currentTask.status) {
                const notification = {
                    userId: currentTask.creatorId,
                    type: 'task_status_changed',
                    data: {
                        taskId,
                        taskTitle: updateData.title || currentTask.title,
                        previousStatus: currentTask.status,
                        newStatus: updateData.status
                    },
                    timestamp: new Date().toISOString()
                };
                
                await this.queueNotification(notification);
            }
            
            return updatedTask;
        } catch (error) {
            await connection.rollback();
            throw error;
        } finally {
            connection.release();
        }
    }
    
    async deleteTask(taskId, userId) {
        const mysqlPool = getPool();
        const connection = await mysqlPool.getConnection();
        
        try {
            await connection.beginTransaction();
            
            // Get current task for audit
            const task = await Task.findOne({ taskId });
            if (!task) {
                throw new Error('Task not found');
            }
            
            // Delete from MongoDB
            await Task.deleteOne({ taskId });
            
            // Delete from MySQL
            await connection.execute('DELETE FROM tasks WHERE task_id = ?', [taskId]);
            
            // Log audit record
            const auditQuery = `
                INSERT INTO audit_logs 
                (entity_type, entity_id, action, actor_id, previous_state)
                VALUES ('task', ?, 'delete', ?, ?)
            `;
            
            await connection.execute(auditQuery, [
                taskId,
                userId,
                JSON.stringify(task)
            ]);
            
            await connection.commit();
            
            // Invalidate cache
            await delAsync(`task:${taskId}`);
            
            return { success: true, message: 'Task deleted successfully' };
        } catch (error) {
            await connection.rollback();
            throw error;
        } finally {
            connection.release();
        }
    }
    
    async queueNotification(notification) {
        const client = require('../config/redis').getClient();
        client.lpush('notifications:queue', JSON.stringify(notification));
        client.incr(`user:notifications:count:${notification.userId}`);
    }
}

module.exports = new TaskService();
            

Authentication with Redis Session Management

// src/services/authService.js
const { getPool } = require('../config/mysql');
const User = require('../models/mongodb/user');
const { v4: uuidv4 } = require('uuid');
const bcrypt = require('bcrypt');
const jwt = require('jsonwebtoken');
const redisConfig = require('../config/redis');

class AuthService {
    async register(userData) {
        const mysqlPool = getPool();
        const connection = await mysqlPool.getConnection();
        
        try {
            // Check if email already exists
            const [existingUsers] = await connection.execute(
                'SELECT * FROM users WHERE email = ?',
                [userData.email]
            );
            
            if (existingUsers.length > 0) {
                throw new Error('Email already registered');
            }
            
            const userId = uuidv4();
            const saltRounds = 10;
            const passwordHash = await bcrypt.hash(userData.password, saltRounds);
            
            // Begin transaction
            await connection.beginTransaction();
            
            // Create user in MySQL
            await connection.execute(
                'INSERT INTO users (user_id, email, password_hash) VALUES (?, ?, ?)',
                [userId, userData.email, passwordHash]
            );
            
            // Create user profile in MongoDB
            const userProfile = new User({
                userId,
                email: userData.email,
                firstName: userData.firstName,
                lastName: userData.lastName,
                passwordHash // For demo only - in production, consider not storing this in MongoDB
            });
            
            await userProfile.save();
            
            // Commit transaction
            await connection.commit();
            
            return {
                userId,
                email: userData.email,
                firstName: userData.firstName,
                lastName: userData.lastName
            };
        } catch (error) {
            await connection.rollback();
            throw error;
        } finally {
            connection.release();
        }
    }
    
    async login(email, password) {
        const mysqlPool = getPool();
        
        try {
            // Get user from MySQL
            const [users] = await mysqlPool.execute(
                'SELECT * FROM users WHERE email = ?',
                [email]
            );
            
            if (users.length === 0) {
                throw new Error('Invalid credentials');
            }
            
            const user = users[0];
            
            // Verify password
            const passwordMatch = await bcrypt.compare(password, user.password_hash);
            if (!passwordMatch) {
                throw new Error('Invalid credentials');
            }
            
            // Get user profile from MongoDB
            const userProfile = await User.findOne({ userId: user.user_id });
            
            // Create JWT
            const token = jwt.sign(
                { userId: user.user_id, email: user.email },
                process.env.JWT_SECRET,
                { expiresIn: '1h' }
            );
            
            // Create session in Redis
            const sessionId = uuidv4();
            const sessionData = {
                userId: user.user_id,
                email: user.email,
                name: userProfile ? `${userProfile.firstName} ${userProfile.lastName}` : '',
                issuedAt: Math.floor(Date.now() / 1000),
                expiresAt: Math.floor(Date.now() / 1000) + 3600 // 1 hour
            };
            
            // Store session
            await redisConfig.setAsync(`session:${sessionId}`, JSON.stringify(sessionData), 'EX', 3600);
            
            // Add to user sessions list
            const client = redisConfig.getClient();
            client.sadd(`user:sessions:${user.user_id}`, sessionId);
            
            // Update last login
            await mysqlPool.execute(
                'UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE user_id = ?',
                [user.user_id]
            );
            
            if (userProfile) {
                userProfile.metadata.lastLogin = new Date();
                await userProfile.save();
                
                // Cache user profile
                await redisConfig.setAsync(
                    `user:profile:${user.user_id}`,
                    JSON.stringify({
                        userId: user.user_id,
                        name: `${userProfile.firstName} ${userProfile.lastName}`,
                        email: user.email,
                        profilePicture: userProfile.profilePicture
                    }),
                    'EX',
                    3600
                );
            }
            
            return {
                token,
                sessionId,
                user: {
                    userId: user.user_id,
                    email: user.email,
                    firstName: userProfile?.firstName,
                    lastName: userProfile?.lastName
                }
            };
        } catch (error) {
            throw error;
        }
    }
    
    async logout(sessionId, userId) {
        try {
            // Delete session
            await redisConfig.delAsync(`session:${sessionId}`);
            
            // Remove from user sessions
            const client = redisConfig.getClient();
            client.srem(`user:sessions:${userId}`, sessionId);
            
            return { success: true };
        } catch (error) {
            throw error;
        }
    }
    
    async verifySession(sessionId) {
        try {
            const sessionData = await redisConfig.getAsync(`session:${sessionId}`);
            if (!sessionData) {
                return null;
            }
            
            const session = JSON.parse(sessionData);
            const now = Math.floor(Date.now() / 1000);
            
            if (session.expiresAt < now) {
                await redisConfig.delAsync(`session:${sessionId}`);
                return null;
            }
            
            return session;
        } catch (error) {
            throw error;
        }
    }
}

module.exports = new AuthService();
            

API Routes and Controllers

// src/controllers/taskController.js
const taskService = require('../services/taskService');

exports.createTask = async (req, res) => {
    try {
        const task = await taskService.createTask(req.body, req.user.userId);
        res.status(201).json(task);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
};

exports.getTask = async (req, res) => {
    try {
        const task = await taskService.getTaskById(req.params.id);
        res.status(200).json(task);
    } catch (error) {
        res.status(404).json({ error: error.message });
    }
};

exports.updateTask = async (req, res) => {
    try {
        const task = await taskService.updateTask(
            req.params.id,
            req.body,
            req.user.userId
        );
        res.status(200).json(task);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
};

exports.deleteTask = async (req, res) => {
    try {
        const result = await taskService.deleteTask(req.params.id, req.user.userId);
        res.status(200).json(result);
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
};

// src/routes/taskRoutes.js
const express = require('express');
const router = express.Router();
const taskController = require('../controllers/taskController');
const auth = require('../middleware/auth');

router.post('/', auth, taskController.createTask);
router.get('/:id', auth, taskController.getTask);
router.put('/:id', auth, taskController.updateTask);
router.delete('/:id', auth, taskController.deleteTask);

module.exports = router;

// src/middleware/auth.js
const authService = require('../services/authService');

module.exports = async (req, res, next) => {
    try {
        const sessionId = req.headers['x-session-id'];
        if (!sessionId) {
            return res.status(401).json({ error: 'Authentication required' });
        }
        
        const session = await authService.verifySession(sessionId);
        if (!session) {
            return res.status(401).json({ error: 'Invalid or expired session' });
        }
        
        req.user = {
            userId: session.userId,
            email: session.email
        };
        
        next();
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
};
            

Server Setup

// src/app.js
const express = require('express');
const cors = require('cors');
const helmet = require('helmet');
const morgan = require('morgan');
require('dotenv').config();

// Database connections
const connectMongoDB = require('./config/mongodb');
const { connectMySQL } = require('./config/mysql');
const { connectRedis } = require('./config/redis');

// Routes
const authRoutes = require('./routes/authRoutes');
const taskRoutes = require('./routes/taskRoutes');
const teamRoutes = require('./routes/teamRoutes');
const userRoutes = require('./routes/userRoutes');

// Initialize Express app
const app = express();

// Middleware
app.use(cors());
app.use(helmet());
app.use(morgan('dev'));
app.use(express.json());

// Routes
app.use('/api/auth', authRoutes);
app.use('/api/tasks', taskRoutes);
app.use('/api/teams', teamRoutes);
app.use('/api/users', userRoutes);

// Connect to databases
const startServer = async () => {
    try {
        await connectMongoDB();
        await connectMySQL();
        await connectRedis();
        
        const PORT = process.env.PORT || 3000;
        app.listen(PORT, () => {
            console.log(`Server running on port ${PORT}`);
        });
    } catch (error) {
        console.error('Failed to start server:', error);
        process.exit(1);
    }
};

startServer();
            

Implementation Challenges and Solutions

Step 4: Review and Reflect

The final step in Polya's problem-solving approach is to review our solution and reflect on what we've learned. This helps us improve our understanding and identify strategies for future problems.

Reviewing Our Solution

Architecture Analysis

• Strengths:
  • Clear separation of concerns with service-oriented architecture
  • Strategic use of each database technology according to its strengths
  • Robust error handling and transaction management
  • Performance optimization through caching
  • Scalable authentication and session management
• Potential Improvements:
  • Implement a message queue system (like RabbitMQ or Kafka) for more robust event-driven architecture
  • Add database connection pooling and retry mechanisms for better reliability
  • Implement circuit breakers for graceful handling of database failures
  • Add observability features (logging, monitoring, tracing) across database operations

Lessons Learned

When to Use Each Database Technology

Through building our TaskForge application, we've gained practical insights about when to use different database technologies:

Real-World Applications of Multi-Database Architecture

Our TaskForge project mirrors approaches used by major technology companies:

• Netflix: Uses Cassandra for streaming data, MySQL for customer data, and EVCache (Redis-based) for caching
• Uber: Uses MySQL for transactional data, MongoDB for trip data, and Redis for geospatial features
• Twitter: Uses MySQL for user data, Redis for timelines, and Cassandra for high-volume data
• Airbnb: Uses MySQL for core data, Redis for caching, and Hadoop/HBase for analytics

Practical Exercises

Complete the following exercises to further enhance your understanding of multi-database applications:

1. Database Integration Exercise

   Extend the TaskForge application to include one additional feature that leverages all three databases. Options include:

   • A reporting/analytics system that aggregates data across databases
   • A comprehensive search feature that searches across all data types
   • A real-time collaboration system using Redis pub/sub
2. Performance Optimization

   Identify potential performance bottlenecks in the application and implement optimizations:

   • Add appropriate indexes to MongoDB and MySQL
   • Optimize the caching strategy
   • Implement query optimization techniques
   • Add performance logging and monitoring
3. Error Resilience

   Improve the application's resilience to database failures:

   • Implement connection pooling and retry mechanisms
   • Add circuit breakers for database operations
   • Create a fallback strategy when a database is unavailable
   • Implement proper logging and alerting for database issues
4. Alternative Implementation

   Re-implement one aspect of the application using a different approach:

   • Replace direct Redis operations with a message queue system
   • Try using MongoDB transactions instead of the two-phase approach
   • Implement a CQRS (Command Query Responsibility Segregation) pattern
5. Documentation and Reflection

   Create comprehensive documentation for your TaskForge application:

   • Document the architecture and database design decisions
   • Create API documentation
   • Write a reflection on challenges faced and solutions implemented
   • Suggest future improvements

Further Resources

• MongoDB Use Cases and Examples
• MySQL Documentation
• Redis Documentation
• Microservices by Martin Fowler
• The Twelve-Factor App
• Designing Data-Intensive Applications by Martin Kleppmann
• How to Solve It by George Polya