michael 81fe0a5120 changed database port

2025-11-05 13:51:30 -06:00

22 KiB

Raw Permalink Blame History

Ferrero Content Scaling - Production Server Deployment Guide

Target Server: Shared hosting with Python 3.6 Environment: Production Requirements: Python 3.6+, PostgreSQL, SSH access, cron access

📋 Pre-Deployment Checklist

Server Requirements

Python 3.6 or higher installed
SSH access to server
Ability to create virtual environments (python3.6 -m venv)
Cron/crontab access
PostgreSQL access (host/port/credentials)
Sufficient disk space (500MB minimum)
Network access to:
- ppr.dam.ferrero.com (DAM API)
- api.box.com (Box API)
- smtp.mailgun.org (Email)
- hook.us1.make.celonis.com (Webhook)

Credentials Needed

DAM OAuth credentials (client_id, client_secret)
Box JWT credentials (from Box-config.json)
PostgreSQL connection details
Mailgun SMTP credentials
Make.com webhook URL

🚀 Step-by-Step Deployment

Step 1: Upload Files to Server

Option A: Via Git (Recommended)

# SSH into server
ssh user@yourserver.com

# Clone repository
cd ~
git clone https://bitbucket.org/zlalani/ferrero-opentext.git
cd ferrero-opentext/Python-Version

Option B: Via SCP/SFTP

# From your local machine
scp -r Python-Version user@yourserver.com:~/ferrero-automation

Step 2: Verify Python Version

# Check Python version (must be 3.6+)
python3 --version

# Or specifically check for Python 3.6
python3.6 --version

# Verify which Python to use
which python3
which python3.6

Expected output:

Python 3.6.x or higher

If Python 3.6 not found:

Contact hosting provider to install Python 3.6+
Or check if it's available at a different path (e.g., /usr/local/bin/python3.6)

Step 3: Run Setup Script

cd ~/ferrero-automation  # Or wherever you uploaded files

# Make setup script executable
chmod +x setup.sh

# Run setup (will create venv and install dependencies)
./setup.sh

Expected output:

==========================================
Ferrero Content Scaling Automation Setup
==========================================

✓ Found Python 3.6 (server compatible)

Step 1: Creating virtual environment...
Step 2: Activating virtual environment...
Step 3: Upgrading pip...
Step 4: Installing dependencies...
Step 5: Creating directory structure...
Step 6: Creating .env file...
✓ Created .env file - PLEASE EDIT WITH YOUR CREDENTIALS
Step 7: Setting file permissions...
Step 8: Testing Python imports...

==========================================
✅ Setup Complete!
==========================================

If setup fails:

Check Python version is 3.6+
Check you have write permissions
Check internet connection (needs to download packages)

Step 4: Configure Environment Variables

# Edit .env file with your production credentials
nano .env

Required Variables:

# Environment
ENV=production

# DAM Credentials (from Creds.txt or Postman collection)
DAM_BASE_URL=https://ppr.dam.ferrero.com/otmmapi
DAM_AUTH_URL=https://ppr.dam.ferrero.com/otdsws/oauth2/token
DAM_CLIENT_ID=otds-OLV
DAM_CLIENT_SECRET=hs28LZ9ZzQ5I9rlW3P7Wwyw85oOatlC1

# Box Credentials (from Box-config.json)
BOX_CLIENT_ID=l2atwxxq4xna7phcjr2uifm4mbah69qp
BOX_CLIENT_SECRET=6XcuCQ6akpk9daE0UHaGSv3mSxWaER4l
BOX_JWT_KEY_ID=n1izyn3l
BOX_PASSPHRASE=971585f5fd6171428c14a7c8899af5ab
BOX_ENTERPRISE_ID=43984435

# Box Folders
BOX_ROOT_FOLDER_A1_A2=348304357505
BOX_ROOT_FOLDER_A2_A3=348526703108

# Database (verify with hosting provider)
DB_HOST=localhost
DB_PORT=5437
DB_USER=ferrero_user
DB_PASSWORD=ferrero_pass_2025

# Email (Mailgun SMTP)
SMTP_SERVER=smtp.mailgun.org
SMTP_PORT=587
SMTP_USER=twist@mail.dev.oliver.solutions
SMTP_PASSWORD=102115e9f3b9d7332d0cd1d4329bc0d4-77751bfc-ca066b71
SENDER_EMAIL=TWIST-UK-SERVER@oliver.agency
ERROR_EMAIL=daveporter@oliver.agency
REPORT_EMAILS=daveporter@oliver.agency

# Webhook (Make.com)
CAMPAIGN_STATUS_WEBHOOK_URL=https://hook.us1.make.celonis.com/3f9ztwl8qnljufo0l65utfv5wvvnt9m5
WEBHOOK_AUTH_TOKEN=

# Box Webhooks (for A2→A3 - optional for now)
BOX_WEBHOOK_PRIMARY_KEY=
BOX_WEBHOOK_SECONDARY_KEY=

Important:

Set ENV=production (not staging)
Verify database credentials with hosting provider
Double-check all passwords (no typos!)
Save file: Ctrl+X, Y, Enter

Step 5: Copy Box JWT Config

The script needs access to Box-config.json for JWT authentication:

# If Box-config.json is in parent directory (ferrero-opentext/)
# Verify the path in config/config.yaml matches

# Option A: Verify path is correct (default: ../Box-config.json)
cat config/config.yaml | grep rsa_private_key_path

# Option B: Or copy Box-config.json to Python-Version directory
cp ../Box-config.json ./Box-config.json

# Then update config.yaml:
nano config/config.yaml
# Change: rsa_private_key_path: ./Box-config.json

Step 6: Test Connections

# Activate virtual environment
source venv/bin/activate

# Test all connections
python scripts/test_connection.py

Expected output:

============================================================
Testing Ferrero Automation Connections
============================================================

✓ Configuration loaded

Testing DAM connection...
✓ DAM connection OK
  URL: https://ppr.dam.ferrero.com/otmmapi

Testing Box connection...
✓ Box connection OK
  Enterprise ID: 43984435

Testing Database connection...
✓ Database connection OK
  Host: localhost:5437

============================================================
Testing complete!
============================================================

If any connection fails:

DAM: Check client_id and client_secret (note: 85o not 850)
Box: Check Box-config.json path and credentials
Database: Verify host, port, username, password

Step 7: Test A1→A2 Script Manually

# Still in venv
python scripts/a1_to_a2_download.py

Expected behavior:

If A1 campaigns exist: Downloads, uploads to Box, updates status
If no A1 campaigns: "No A1 campaigns found - exiting"

Check logs:

cat logs/a1_to_a2.log

Verify in Box:

Go to: https://oliver-na.app.box.com/folder/348304357505
Look for folder: C000000078-Campaign_Name
Files should have tracking IDs in filename

Step 8: Setup Cron Job for A1→A2

Edit crontab:

crontab -e

Add this line:

# Run A1→A2 script every 5 minutes
*/5 * * * * cd ~/ferrero-automation/Python-Version && venv/bin/python scripts/a1_to_a2_download.py >> logs/cron.log 2>&1

Important notes:

Adjust path if you installed in different location
Use full path to python: ~/ferrero-automation/Python-Version/venv/bin/python
Logs go to logs/cron.log

Save and exit:

Vim: Press Esc, type :wq, press Enter
Nano: Ctrl+X, Y, Enter

Verify cron is scheduled:

crontab -l

Monitor cron execution:

# Watch cron log in real-time
tail -f logs/cron.log

# Or check last 50 lines
tail -50 logs/cron.log

Step 9: Setup Webhook Server for A2→A3 (Optional for now)

Start webhook server in background:

cd ~/ferrero-automation/Python-Version
source venv/bin/activate

# Start in background with nohup
nohup python scripts/a2_to_a3_upload.py > logs/webhook.log 2>&1 &

# Save process ID
echo $! > webhook.pid

# Verify it's running
ps aux | grep a2_to_a3_upload.py

Create process monitor script:

nano monitor_webhook.sh

Paste this content:

#!/bin/bash
# Monitor and restart webhook if it stops

SCRIPT_DIR="$HOME/ferrero-automation/Python-Version"
PID_FILE="$SCRIPT_DIR/webhook.pid"
LOG_FILE="$SCRIPT_DIR/logs/monitor.log"

# Check if process is running
if [ -f "$PID_FILE" ]; then
    PID=$(cat "$PID_FILE")
    if ps -p $PID > /dev/null 2>&1; then
        echo "$(date): Webhook running (PID: $PID)" >> "$LOG_FILE"
        exit 0
    fi
fi

# Process not running - restart
echo "$(date): Webhook not running, restarting..." >> "$LOG_FILE"
cd "$SCRIPT_DIR"
source venv/bin/activate
nohup python scripts/a2_to_a3_upload.py > logs/webhook.log 2>&1 &
echo $! > "$PID_FILE"
echo "$(date): Webhook restarted (PID: $(cat $PID_FILE))" >> "$LOG_FILE"

Make executable:

chmod +x monitor_webhook.sh

Add to crontab (check every 5 minutes):

crontab -e

# Add:
*/5 * * * * ~/ferrero-automation/Python-Version/monitor_webhook.sh

Note: A2→A3 webhook can be deployed later when ready to receive Box webhooks

📊 Verification & Monitoring

Check Script is Running

View recent cron executions:

tail -50 logs/cron.log

Check for errors:

grep -i error logs/a1_to_a2.log | tail -20

Check successful uploads:

grep "Campaign completed successfully" logs/a1_to_a2.log

Monitor Database

# Check recent tracking IDs
PGPASSWORD=ferrero_pass_2025 psql -h localhost -p 5437 -U ferrero_user -d ferrero_tracking -c "
SELECT tracking_id, original_filename, created_at
FROM master_assets
ORDER BY created_at DESC
LIMIT 10;
"

# Count assets by status
PGPASSWORD=ferrero_pass_2025 psql -h localhost -p 5437 -U ferrero_user -d ferrero_tracking -c "
SELECT status, COUNT(*)
FROM master_assets
GROUP BY status;
"

Check Email Notifications

Check inbox: daveporter@oliver.agency
Success emails: Campaign completed
Error emails: Upload failed or partial completion

Check Box Uploads

Go to: https://oliver-na.app.box.com/folder/348304357505
Look for folders: C000000078-Campaign_Name
Files should have tracking IDs: filename_ABC123.mp4

Check Webhook

Check Make.com scenario for incoming data
Payload includes: campaign_id, campaign_number, asset_count, timestamp

🔧 Troubleshooting

Script Not Running

Check cron is scheduled:

crontab -l

Check cron log for errors:

tail -50 logs/cron.log

Run manually to see errors:

cd ~/ferrero-automation/Python-Version
source venv/bin/activate
python scripts/a1_to_a2_download.py

No A1 Campaigns Found

Verify campaigns exist:

python -c "
import sys
sys.path.insert(0, 'scripts')
from shared.config_loader import load_config
from shared.dam_client import DAMClient

config = load_config('config/config.yaml')
dam = DAMClient(config)

# Search without status filter
campaigns = dam.search_campaigns()
print('Total campaigns:', len(campaigns))

# Search for A1
a1_campaigns = dam.search_campaigns(status='A1')
print('A1 campaigns:', len(a1_campaigns))
"

Connection Errors

DAM Connection Failed:

Check client_secret has lowercase 'o' not zero: 85oO not 850
Verify URL: https://ppr.dam.ferrero.com/otmmapi
Test OAuth manually in PHP app to verify credentials still valid

Box Connection Failed:

Verify Box-config.json path is correct
Check JWT credentials haven't expired
Verify enterprise ID: 43984435

Database Connection Failed:

Check host and port with hosting provider
Verify username and password
Test connection: psql -h HOST -p PORT -U USER -d ferrero_tracking

Emails Not Sending

Check SMTP credentials:

# Test SMTP connection
python -c "
import smtplib
server = smtplib.SMTP('smtp.mailgun.org', 587)
server.starttls()
server.login('twist@mail.dev.oliver.solutions', '102115e9f3b9d7332d0cd1d4329bc0d4-77751bfc-ca066b71')
print('SMTP connection OK')
server.quit()
"

Check email template syntax:

HTML templates use {{ variable }} (Jinja2)
Subject uses {variable} (Python format)

Box Folder Not Created

Check root folder ID:

grep BOX_ROOT_FOLDER .env
# Should show: BOX_ROOT_FOLDER_A1_A2=348304357505

Check folder permissions:

Verify service account has write access to folder 348304357505

🔐 Security Best Practices

Protect Credentials

# Secure .env file (only owner can read)
chmod 600 .env

# Verify permissions
ls -la .env
# Should show: -rw------- (600)

# Never commit .env to git
echo ".env" >> .gitignore

Log File Security

# Restrict log file access
chmod 640 logs/*.log

# Rotate logs periodically (optional)
# Add to crontab:
0 0 * * 0 cd ~/ferrero-automation/Python-Version && find logs/ -name "*.log" -mtime +30 -delete

📝 Configuration for Production

Update config/config.yaml

Create production environment config:

nano config/environments/production.yaml

Add production-specific settings:

# Production overrides

# Polling - more frequent in production
polling:
  interval_seconds: 180  # 3 minutes instead of 5

# Logging - less verbose
logging:
  level: INFO  # Not DEBUG

# Notifications - production recipients
notifications:
  recipients:
    success:
      - team@ferrero.com
      - agency@example.com
    errors:
      - admin@ferrero.com
      - daveporter@oliver.agency
    critical:
      - oncall@ferrero.com

🕐 Cron Job Examples

A1→A2 Every 5 Minutes

*/5 * * * * cd ~/ferrero-automation/Python-Version && venv/bin/python scripts/a1_to_a2_download.py >> logs/cron.log 2>&1

A1→A2 Every 3 Minutes (Production)

*/3 * * * * cd ~/ferrero-automation/Python-Version && venv/bin/python scripts/a1_to_a2_download.py >> logs/cron.log 2>&1

Log Cleanup (Weekly, Sunday at midnight)

0 0 * * 0 cd ~/ferrero-automation/Python-Version && find logs/ -name "*.log" -mtime +30 -delete

Temp File Cleanup (Daily at 2 AM)

0 2 * * * cd ~/ferrero-automation/Python-Version && find temp/downloads/ -mtime +1 -delete

🔄 Updates and Maintenance

Update Code from Git

cd ~/ferrero-automation/Python-Version

# Pull latest changes
git pull

# Restart virtual environment if dependencies changed
source venv/bin/activate
pip install -r requirements.txt --upgrade

# Restart webhook server if running
if [ -f webhook.pid ]; then
    kill $(cat webhook.pid)
    nohup python scripts/a2_to_a3_upload.py > logs/webhook.log 2>&1 &
    echo $! > webhook.pid
fi

Add New MVP Field

Edit config/field_mappings.yaml:

nano config/field_mappings.yaml

# Add new field ID to mvp_fields list:
mvp_fields:
  - FERRERO.FIELD.MKTG.ASSET TYPE
  # ... existing fields ...
  - NEW.FIELD.ID.HERE  # Just add here!

# Save and exit (Ctrl+X, Y, Enter)

No code changes needed! Next script run will use new field.

Change Polling Interval

# Edit crontab
crontab -e

# Change from */5 to */3 for 3-minute interval
*/3 * * * * cd ~/ferrero-automation/Python-Version && ...

Change Email Recipients

# Edit .env
nano .env

# Update:
ERROR_EMAIL=new-admin@ferrero.com
REPORT_EMAILS=team@ferrero.com,manager@ferrero.com

# Save and restart

🐛 Debugging

Enable Debug Logging

# Temporarily enable debug logging
nano config/config.yaml

# Change:
logging:
  level: DEBUG  # Instead of INFO

# Run script manually
source venv/bin/activate
python scripts/a1_to_a2_download.py

# Check detailed logs
tail -100 logs/a1_to_a2.log

Test Individual Components

Test DAM only:

python -c "
import sys
sys.path.insert(0, 'scripts')
from shared.config_loader import load_config
from shared.dam_client import DAMClient

config = load_config('config/config.yaml')
dam = DAMClient(config)
campaigns = dam.search_campaigns(status='A1')
print('Found {} A1 campaigns'.format(len(campaigns)))
for c in campaigns:
    print('  -', c.get('campaign_name'), c.get('campaign_id'))
"

Test Box only:

python -c "
import sys
sys.path.insert(0, 'scripts')
from shared.config_loader import load_config
from shared.box_client import BoxClient

config = load_config('config/config.yaml')
box = BoxClient(config)
print('Box connection:', box.test_connection())
"

Test Database only:

python -c "
import sys
sys.path.insert(0, 'scripts')
from shared.config_loader import load_config
from shared.database import Database

config = load_config('config/config.yaml')
db = Database(config)
print('Database connection:', db.test_connection())
db.close()
"

📞 Support & Escalation

Check Script Health

Quick health check script:

#!/bin/bash
# health_check.sh

echo "=== Ferrero Automation Health Check ==="
echo ""

# Check cron is scheduled
echo "Cron Jobs:"
crontab -l | grep ferrero

echo ""
echo "Recent Executions:"
tail -5 logs/cron.log

echo ""
echo "Recent Successes:"
grep "Campaign completed successfully" logs/a1_to_a2.log | tail -3

echo ""
echo "Recent Errors:"
grep -i error logs/a1_to_a2.log | tail -5

echo ""
echo "Database Records (last 24h):"
PGPASSWORD=ferrero_pass_2025 psql -h localhost -p 5437 -U ferrero_user -d ferrero_tracking -c "
SELECT COUNT(*) as count, DATE(created_at) as date
FROM master_assets
WHERE created_at > NOW() - INTERVAL '24 hours'
GROUP BY DATE(created_at);
"

Make executable and run:

chmod +x health_check.sh
./health_check.sh

If Script Stops Working

Check cron is running:

service cron status  # or: systemctl status cron

Check disk space:
```
df -h
```

Check logs for errors:

tail -100 logs/a1_to_a2.log | grep -i error

Test manually:

source venv/bin/activate
python scripts/a1_to_a2_download.py

Check database connectivity:
```
python scripts/test_connection.py
```

Contact Information

For script issues:

Check logs first: logs/a1_to_a2.log
Check cron log: logs/cron.log
Email: daveporter@oliver.agency

For server issues:

Contact hosting provider
Check Python 3.6 is still available
Check cron service is running

🎯 Production Deployment Checklist

Pre-Deployment

Uploaded all files to server
Created virtual environment
Installed dependencies (requirements.txt)
Configured .env with production credentials
Copied Box-config.json to correct location
Verified all paths in config.yaml

Testing

Ran test_connection.py - all green
Ran a1_to_a2_download.py manually - worked
Verified files uploaded to Box (correct folder)
Verified database records created
Received email notification
Webhook sent to Make.com

Deployment

Setup cron job for A1→A2
Verified cron is scheduled (crontab -l)
Waited 5 minutes, checked cron.log
Verified script executed automatically
Setup A2→A3 webhook server (if ready)
Setup webhook monitor cron

Post-Deployment

Monitor logs for 24 hours
Check emails are being received
Verify campaigns progressing through workflow
Check database growth
Verify Box folders being created correctly

📖 Quick Reference Commands

Daily Operations

# Check if script is working
tail -20 logs/a1_to_a2.log

# Count processed assets today
grep "$(date +%Y-%m-%d)" logs/a1_to_a2.log | grep "Campaign completed" | wc -l

# View latest email sent
grep "Email sent" logs/a1_to_a2.log | tail -1

# Check webhook status
grep "Webhook sent" logs/a1_to_a2.log | tail -5

Emergency Procedures

Stop A1→A2 automation:

# Remove from crontab
crontab -e
# Comment out or delete the line with a1_to_a2_download.py

Stop A2→A3 webhook:

# If running
kill $(cat webhook.pid)
rm webhook.pid

Restart everything:

# Re-add to crontab
crontab -e

# Restart webhook
cd ~/ferrero-automation/Python-Version
source venv/bin/activate
nohup python scripts/a2_to_a3_upload.py > logs/webhook.log 2>&1 &
echo $! > webhook.pid

✅ Success Criteria

A1→A2 automation is working if:

✅ Cron runs every 5 minutes (check cron.log)
✅ A1 campaigns are processed (check a1_to_a2.log)
✅ Files appear in Box folder 348304357505
✅ Database records increase
✅ Campaign status changes from A1 to A2
✅ Emails arrive at daveporter@oliver.agency
✅ Webhooks arrive at Make.com

📞 Support

Documentation:

README.md - Quick start guide
DEPLOYMENT.md - This file
PYTHON_AUTOMATION_PLAN.md - Complete architecture
PROJECT_STATUS_2025-10-30.md - Full session summary

Logs Location:

Application logs: logs/a1_to_a2.log
Cron logs: logs/cron.log
Webhook logs: logs/webhook.log
Error logs: logs/errors.log

Configuration Files:

Main config: config/config.yaml
Field mappings: config/field_mappings.yaml
Environment: .env
Production overrides: config/environments/production.yaml

Deployment Version: 1.0.0 Python Version Required: 3.6+ Tested With: Python 3.6 (server), Python 3.10 (local) Status: Production Ready

For additional support, refer to PROJECT_STATUS_2025-10-30.md

🎯 Key Features

A1→A2 Script Features:

✅ Single-run mode (process one campaign and exit)
✅ Downloads all master assets from campaign
✅ Uploads to Box with tracking IDs
✅ Folder naming: C000000078-Campaign_Name
✅ All-done check before status update
✅ Status update A1 → A2 only when all successful
✅ Webhook notification to Make.com
✅ Detailed email with list of all processed assets (names, tracking IDs, Box URLs)
✅ Log rotation (10MB files, keeps 28 backups = 1 month)
✅ Retry failed campaigns on next run

A2→A3 Script Features:

✅ Polling mode (checks Box folder, no webhook needed)
✅ Single-run mode (process one file and exit)
✅ Parses V2 filenames with strict validation
✅ Loads master metadata from PostgreSQL
✅ Builds 27 MVP fields from master
✅ Updates Description, State, Language from filename
✅ Uploads to DAM with clean filename
✅ Deletes file from Box after successful upload
✅ Email notification for each upload (asset ID, tracking ID, filename)
✅ Log rotation (10MB files, keeps 28 backups = 1 month)
✅ Processes files one at a time

Email Notifications:

✅ Detailed asset lists in emails (not just counts)
✅ Successful uploads: Show asset name, tracking ID, Box URL
✅ Failed uploads: Show asset name and specific error
✅ Partial completion: Lists both successful and failed assets
✅ Per-file notifications for A2→A3 uploads
✅ Sent via SMTP (Mailgun)
✅ HTML formatted

Log Management:

✅ Automatic rotation at 10MB per file
✅ Keeps 28 backups (approximately 1 month)
✅ Total log space: ~280MB maximum
✅ Automatic cleanup (old logs auto-deleted)
✅ No manual maintenance required

Box File Management:

✅ A1→A2: Uploads to folder 348304357505
✅ A2→A3: Polls folder 348526703108
✅ Automatic deletion from Box after DAM upload
✅ Prevents reprocessing same files
✅ Keeps Box folders clean

22 KiB Raw Permalink Blame History

Ferrero Content Scaling - Production Server Deployment Guide

📋 Pre-Deployment Checklist

Server Requirements

Credentials Needed

🚀 Step-by-Step Deployment

Step 1: Upload Files to Server

Step 2: Verify Python Version

Step 3: Run Setup Script

Step 4: Configure Environment Variables

Step 5: Copy Box JWT Config

Step 6: Test Connections

Step 7: Test A1→A2 Script Manually

Step 8: Setup Cron Job for A1→A2

Step 9: Setup Webhook Server for A2→A3 (Optional for now)

📊 Verification & Monitoring

Check Script is Running

Monitor Database

Check Email Notifications

Check Box Uploads

Check Webhook

🔧 Troubleshooting

Script Not Running

No A1 Campaigns Found

Connection Errors

Emails Not Sending

Box Folder Not Created

🔐 Security Best Practices

Protect Credentials

Log File Security

📝 Configuration for Production

Update config/config.yaml

🕐 Cron Job Examples

A1→A2 Every 5 Minutes

A1→A2 Every 3 Minutes (Production)

Log Cleanup (Weekly, Sunday at midnight)

Temp File Cleanup (Daily at 2 AM)

🔄 Updates and Maintenance

Update Code from Git

Add New MVP Field

Change Polling Interval

Change Email Recipients

🐛 Debugging

Enable Debug Logging

Test Individual Components

📞 Support & Escalation

Check Script Health

If Script Stops Working

Contact Information

🎯 Production Deployment Checklist

Pre-Deployment

Testing

Deployment

Post-Deployment

📖 Quick Reference Commands

Daily Operations

Emergency Procedures

✅ Success Criteria

📞 Support

🎯 Key Features

A1→A2 Script Features:

A2→A3 Script Features:

Email Notifications:

Log Management:

Box File Management:

22 KiB

Raw Permalink Blame History