# Server Error Fixes

## Issues Identified

Based on the PM2 logs, there were two critical errors:

### 1. JWT Token Expiration Error (Server Logs)
```
Token refresh error: TokenExpiredError: jwt expired
expiredAt: 2025-11-14T08:27:57.000Z
```

**Problem**: The refresh token endpoint was being called but didn't exist in the codebase, causing authentication failures.

### 2. MySQL Connection Error (App Logs)
```
Error: Packets out of order. Got: 0 Expected: 3
code: 'PROTOCOL_PACKETS_OUT_OF_ORDER'
```

**Problem**: Database connection pool issues causing packet synchronization errors, typically due to:
- Connection timeouts
- Network instability
- Concurrent query conflicts
- Improper connection handling

## Fixes Applied

### Fix 1: Implemented Refresh Token System

#### Changes to `authController.js`:
1. **Added `generateRefreshToken` function** - Creates refresh tokens with 30-day expiration
2. **Modified `login` endpoint** - Now returns both access token and refresh token
3. **Modified `staffLogin` endpoint** - Now returns both access token and refresh token
4. **Added `refreshToken` endpoint** - New endpoint to refresh expired access tokens

#### Changes to `authRoutes.js`:
- Added new route: `POST /refresh-token`

#### How it works:
- Access tokens expire in 7 days (configurable via `JWT_EXPIRES_IN`)
- Refresh tokens expire in 30 days (configurable via `JWT_REFRESH_EXPIRES_IN`)
- When access token expires, client can call `/refresh-token` with the refresh token
- Server validates refresh token and issues new access + refresh tokens
- Supports both regular users and staff members

### Fix 2: Enhanced MySQL Connection Pool

#### Changes to `database.js`:
1. **Added connection timeout settings**:
   - `connectTimeout: 60000` (60 seconds)
   - `acquireTimeout: 60000` (60 seconds)
   - `timeout: 60000` (60 seconds)

2. **Added connection safety settings**:
   - `multipleStatements: false` - Prevents SQL injection
   - `charset: 'utf8mb4'` - Proper character encoding

3. **Added error event handler**:
   - Monitors pool errors
   - Logs specific error types (CONNECTION_LOST, CON_COUNT_ERROR, ECONNREFUSED)
   - Helps diagnose connection issues

## Environment Variables Required

Add these to your `.env` file on the server:

```env
# JWT Settings
JWT_SECRET=your_secret_key_here
JWT_EXPIRES_IN=7d
JWT_REFRESH_SECRET=your_refresh_secret_key_here
JWT_REFRESH_EXPIRES_IN=30d

# Database Settings
DB_HOST=localhost
DB_USER=your_db_user
DB_PASSWORD=your_db_password
DB_NAME=faceml_db
DB_PORT=3306
```

## Deployment Steps

1. **Update the code on your server**:
   ```bash
   # Navigate to your project directory
   cd /home/hrbackendtest/public_html
   
   # Pull latest changes or upload modified files:
   # - controllers/authController.js
   # - routes/authRoutes.js
   # - config/database.js
   ```

2. **Update environment variables**:
   ```bash
   # Edit .env file
   nano .env
   
   # Add the JWT_REFRESH_SECRET and JWT_REFRESH_EXPIRES_IN variables
   ```

3. **Restart PM2 processes**:
   ```bash
   pm2 restart all
   
   # Or restart specific apps
   pm2 restart server
   pm2 restart app
   ```

4. **Monitor logs**:
   ```bash
   # Watch all logs
   pm2 logs
   
   # Watch specific app
   pm2 logs server
   pm2 logs app
   ```

## Testing the Fixes

### Test Refresh Token Endpoint:
```bash
curl -X POST http://your-server/api/auth/refresh-token \
  -H "Content-Type: application/json" \
  -d '{"refreshToken": "your_refresh_token_here"}'
```

Expected response:
```json
{
  "message": "Token refreshed successfully",
  "token": "new_access_token",
  "refreshToken": "new_refresh_token"
}
```

### Monitor Database Connections:
```bash
# Check MySQL connections
mysql -u root -p -e "SHOW PROCESSLIST;"

# Check for connection errors in PM2 logs
pm2 logs | grep -i "database"
```

## Client-Side Integration

Your Flutter app should:

1. **Store both tokens** after login:
   ```dart
   final loginResponse = await apiService.login(email, password);
   await storage.write(key: 'access_token', value: loginResponse['token']);
   await storage.write(key: 'refresh_token', value: loginResponse['refreshToken']);
   ```

2. **Implement token refresh logic**:
   ```dart
   // When API returns 401/403 (token expired)
   try {
     final response = await apiService.refreshToken(refreshToken);
     await storage.write(key: 'access_token', value: response['token']);
     await storage.write(key: 'refresh_token', value: response['refreshToken']);
     // Retry the original request
   } catch (e) {
     // Refresh token also expired, redirect to login
     navigateToLogin();
   }
   ```

## Additional Recommendations

1. **Database Connection Monitoring**:
   - Set up alerts for connection pool exhaustion
   - Monitor slow queries
   - Consider using a connection pooler like ProxySQL for production

2. **Token Security**:
   - Store refresh tokens securely (encrypted storage)
   - Implement token rotation
   - Consider adding refresh token revocation (store in database)

3. **Error Logging**:
   - Set up centralized logging (e.g., Winston, Sentry)
   - Monitor token expiration patterns
   - Track database connection errors

4. **Performance**:
   - Consider Redis for token blacklisting
   - Implement rate limiting on refresh endpoint
   - Add request caching where appropriate
