User Sync Project

This project synchronizes user and team hierarchy data between the EMMA employee database and the WebUI user database. It fetches employee data from an EMMA API, builds hierarchical team names, and updates user information in the WebUI database.

Features

• Fetches employee data from EMMA backend API
• Builds hierarchical team names (e.g., "Samsung > SIRC > Platform > DevOps")
• Matches employees with WebUI users by email, username, or full name
• Updates the user_details table in the monitoring database with immediate manager group information
• Creates CSV reports for analysis
• Handles domain swaps between partner.samsung.com and samsung.com email addresses
• Identifies inactive users and truly unmatched users
• Restores missing webui_logs data from backup database

Installation

1. Clone the repository:

   git clone <repository-url>
   cd user_sync_project

2. Ensure uv is installed:

   pip install uv

3. Install dependencies:

   uv sync

4. Set up environment variables:

   cp .env.example .env
   # Edit .env and fill in your API tokens

Usage

Run the main sync script:

uv run python user_sync_main.py

Restoring Missing Logs

If the monitoring database is missing webui_logs data (e.g., due to a database restore), you can restore it from the backup database:

uv run python restore_missing_logs.py

This compares webui_monitoring.db with webui_monitoring.db.copy and inserts any missing log records.

Key Functions

1. fetch_employees() - Fetches employee data from EMMA API
2. fetch_webui_users_from_api() - Retrieves users from WebUI API (preferred)
3. fetch_webui_users_from_db() - Retrieves users from WebUI SQLite database (fallback)
4. build_full_team_name_and_manager() - Constructs hierarchical team names and captures immediate manager info
5. process_employees() - Processes raw employee data with team hierarchies
6. update_user_details_table() - Updates the user_details table with immediate manager group information
7. create_webui_users_csv() - Generates a CSV report with user information
8. get_teams_for_users() - Looks up teams for specific user names
9. restore_missing_logs.py - Standalone script to restore missing webui_logs from backup database

Database Updates

The script updates the user_details table in the monitoring database:

• Sets the group field to the immediate manager's group name
• Matches users by email address
• Preserves existing user information while updating only the group field

Configuration

The script can be configured using environment variables. Copy .env.example to .env and fill in your values:

• EMMA_API_TOKEN: API token for accessing the EMMA backend
• WEBUI_TOKEN: API token for accessing the WebUI API
• WEBUI_DB_PATH: Path to the WebUI SQLite database (default: platform-specific path)
• WEBUI_MONITORING_DB_PATH: Path to the monitoring database with user_details table (default: /raid/tools/devops/logs/database/webui_monitoring.db)
• EMMA_API_URL: URL for the EMMA API endpoint (default: https://emma_backend.transchip.com/getAllEmps)
• WEBUI_API_URL: URL for the WebUI API (default: https://llm.transchip.com/api/v1/users/all)
• VERIFY_SSL: Whether to verify SSL certificates (default: true)

Output

The script generates:

• Console output with synchronization statistics
• CSV reports on the desktop with user information
• Updates to the monitoring database user_details table
• Restored webui_logs data when using the restore script

Security Considerations

• API tokens are stored in .env file (gitignored) - not hardcoded in the script
• SSL verification is disabled for API calls (verify=False) - should be addressed for production use
• Database connections use direct file path access to network locations

Troubleshooting

If you encounter issues:

1. Database not found: Ensure network paths are properly mounted or set the WEBUI_DB_PATH environment variable to point to your local database file.

2. API connection failures: Check network connectivity and ensure the EMMA_API_TOKEN and WEBUI_TOKEN in your .env file are valid.

3. SSL errors: The script currently disables SSL verification. For production use, proper SSL certificate handling should be implemented.