Cross-Platform Media Sync

Objective

Create a media synchronization system that distributes content across multiple platforms, tracks sync status, handles conflicts, and ensures consistency across all destinations.

Step 1: Create Source Assets Table

Store master media assets.

CREATE TABLE source_assets (
    id INTEGER PRIMARY KEY,
    asset_code VARCHAR(50) NOT NULL UNIQUE,
    title VARCHAR(300),
    description TEXT,
    asset_type VARCHAR(50),
    category VARCHAR(100),
    file_path TEXT,
    file_size_mb DECIMAL(10, 2),
    checksum VARCHAR(64),
    version INTEGER DEFAULT 1,
    metadata TEXT,
    status VARCHAR(50) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
);

Step 2: Create Target Platforms Table

Define sync destinations.

CREATE TABLE sync_platforms (
    id INTEGER PRIMARY KEY,
    platform_code VARCHAR(50) NOT NULL UNIQUE,
    platform_name VARCHAR(200),
    platform_type VARCHAR(50),
    api_endpoint VARCHAR(500),
    auth_type VARCHAR(50),
    supported_formats TEXT,
    max_file_size_mb INTEGER,
    rate_limit_per_hour INTEGER,
    sync_enabled BOOLEAN DEFAULT TRUE,
    last_health_check TIMESTAMP,
    health_status VARCHAR(50) DEFAULT 'unknown',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 3: Create Sync Configurations Table

Define sync rules.

CREATE TABLE sync_configurations (
    id INTEGER PRIMARY KEY,
    config_code VARCHAR(50) NOT NULL UNIQUE,
    config_name VARCHAR(200),
    source_category VARCHAR(100),
    asset_types TEXT,
    platform_id INTEGER NOT NULL,
    sync_mode VARCHAR(50),
    transform_rules TEXT,
    schedule_cron VARCHAR(50),
    auto_sync BOOLEAN DEFAULT TRUE,
    priority INTEGER DEFAULT 5,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (platform_id) REFERENCES sync_platforms(id)
);

Step 4: Create Sync Jobs Table

Track sync operations.

CREATE TABLE sync_jobs (
    id INTEGER PRIMARY KEY,
    job_code VARCHAR(50) NOT NULL UNIQUE,
    config_id INTEGER NOT NULL,
    asset_id INTEGER NOT NULL,
    platform_id INTEGER NOT NULL,
    job_type VARCHAR(50),
    status VARCHAR(50) DEFAULT 'pending',
    progress_percent DECIMAL(5, 2) DEFAULT 0,
    source_version INTEGER,
    target_version INTEGER,
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    error_message TEXT,
    retry_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (config_id) REFERENCES sync_configurations(id),
    FOREIGN KEY (asset_id) REFERENCES source_assets(id),
    FOREIGN KEY (platform_id) REFERENCES sync_platforms(id)
);

Step 5: Create Platform Assets Table

Track synced copies.

CREATE TABLE platform_assets (
    id INTEGER PRIMARY KEY,
    source_asset_id INTEGER NOT NULL,
    platform_id INTEGER NOT NULL,
    platform_asset_id VARCHAR(200),
    platform_url VARCHAR(500),
    synced_version INTEGER,
    local_checksum VARCHAR(64),
    remote_checksum VARCHAR(64),
    sync_status VARCHAR(50) DEFAULT 'pending',
    last_synced_at TIMESTAMP,
    last_verified_at TIMESTAMP,
    metadata TEXT,
    FOREIGN KEY (source_asset_id) REFERENCES source_assets(id),
    FOREIGN KEY (platform_id) REFERENCES sync_platforms(id)
);

Step 6: Create Sync Conflicts Table

Track synchronization issues.

CREATE TABLE sync_conflicts (
    id INTEGER PRIMARY KEY,
    platform_asset_id INTEGER NOT NULL,
    conflict_type VARCHAR(50),
    source_value TEXT,
    target_value TEXT,
    detected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolution VARCHAR(50),
    resolved_by VARCHAR(100),
    resolved_at TIMESTAMP,
    notes TEXT,
    FOREIGN KEY (platform_asset_id) REFERENCES platform_assets(id)
);

Step 7: Create Sync Events Log Table

Detailed activity log.

CREATE TABLE sync_events_log (
    id INTEGER PRIMARY KEY,
    job_id INTEGER,
    platform_asset_id INTEGER,
    event_type VARCHAR(50),
    event_status VARCHAR(50),
    message TEXT,
    details TEXT,
    event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (job_id) REFERENCES sync_jobs(id),
    FOREIGN KEY (platform_asset_id) REFERENCES platform_assets(id)
);

Step 8: Insert Sample Platforms

Add sync destinations.

INSERT INTO sync_platforms (id, platform_code, platform_name, platform_type, api_endpoint, auth_type, supported_formats, max_file_size_mb, rate_limit_per_hour, sync_enabled, health_status) VALUES
    (1, 'CDN-PRIMARY', 'Primary CDN', 'cdn', 'https://cdn.example.com/api/v1', 'api_key', 'image/jpeg,image/png,video/mp4', 5000, 1000, TRUE, 'healthy'),
    (2, 'YOUTUBE', 'YouTube Channel', 'video_platform', 'https://www.googleapis.com/youtube/v3', 'oauth2', 'video/mp4,video/mov', 128000, 50, TRUE, 'healthy'),
    (3, 'VIMEO', 'Vimeo Account', 'video_platform', 'https://api.vimeo.com', 'oauth2', 'video/mp4,video/mov,video/avi', 25000, 100, TRUE, 'healthy'),
    (4, 'S3-BACKUP', 'AWS S3 Backup', 'cloud_storage', 'https://s3.amazonaws.com', 'aws_signature', 'all', 50000, 5000, TRUE, 'healthy'),
    (5, 'SOCIAL-MEDIA', 'Social Media Hub', 'social', 'https://api.socialhub.com/v2', 'oauth2', 'image/jpeg,image/png,video/mp4', 500, 200, TRUE, 'healthy'),
    (6, 'DAM-EXTERNAL', 'External DAM', 'dam', 'https://dam.partner.com/api', 'api_key', 'all', 10000, 500, TRUE, 'degraded');

Step 9: Insert Source Assets

Add master content.

INSERT INTO source_assets (id, asset_code, title, description, asset_type, category, file_path, file_size_mb, checksum, version, status, created_at) VALUES
    (1, 'VID-LAUNCH-2024', 'Product Launch 2024', 'Annual product launch keynote video', 'video', 'corporate', '/media/videos/launch_2024.mp4', 2850.5, 'abc123def456', 3, 'active', '2024-01-15 10:00:00'),
    (2, 'VID-TUTORIAL-01', 'Getting Started Tutorial', 'Platform onboarding tutorial', 'video', 'education', '/media/videos/tutorial_01.mp4', 580.2, 'def456ghi789', 2, 'active', '2024-01-10 09:00:00'),
    (3, 'IMG-HERO-PROD', 'Product Hero Image', 'Main product marketing image', 'image', 'marketing', '/media/images/hero_product.jpg', 15.2, 'ghi789jkl012', 1, 'active', '2024-01-12 14:00:00'),
    (4, 'VID-TESTIMONIAL', 'Customer Testimonial', 'Customer success story video', 'video', 'marketing', '/media/videos/testimonial.mp4', 420.8, 'jkl012mno345', 1, 'active', '2024-01-18 11:00:00'),
    (5, 'IMG-TEAM-2024', 'Team Photo 2024', 'Annual company team photo', 'image', 'corporate', '/media/images/team_2024.jpg', 8.5, 'mno345pqr678', 1, 'active', '2024-01-20 15:00:00');

Step 10: Insert Sync Configurations

Define sync rules.

INSERT INTO sync_configurations (id, config_code, config_name, source_category, asset_types, platform_id, sync_mode, transform_rules, schedule_cron, auto_sync, priority, is_active) VALUES
    (1, 'SYNC-CDN-ALL', 'CDN Full Sync', 'all', 'image,video', 1, 'full', '{"resize_images": true, "max_dimension": 2000}', '0 */6 * * *', TRUE, 10, TRUE),
    (2, 'SYNC-YT-VIDEO', 'YouTube Video Sync', 'corporate,marketing', 'video', 2, 'selective', '{"add_watermark": true, "quality": "1080p"}', '0 9 * * 1', TRUE, 8, TRUE),
    (3, 'SYNC-VIMEO-EDU', 'Vimeo Education', 'education', 'video', 3, 'selective', '{"quality": "1080p"}', '0 10 * * *', TRUE, 7, TRUE),
    (4, 'SYNC-S3-BACKUP', 'S3 Backup All', 'all', 'all', 4, 'full', '{"encrypt": true}', '0 2 * * *', TRUE, 5, TRUE),
    (5, 'SYNC-SOCIAL', 'Social Media Sync', 'marketing', 'image,video', 5, 'selective', '{"optimize_for_social": true}', '0 8 * * *', TRUE, 6, TRUE);

Step 11: Insert Sync Jobs

Add sync operations.

INSERT INTO sync_jobs (id, job_code, config_id, asset_id, platform_id, job_type, status, progress_percent, source_version, started_at, completed_at, created_at) VALUES
    (1, 'SYNC-001', 1, 1, 1, 'initial', 'completed', 100.0, 3, '2024-01-25 06:00:00', '2024-01-25 06:15:00', '2024-01-25 06:00:00'),
    (2, 'SYNC-002', 2, 1, 2, 'initial', 'completed', 100.0, 3, '2024-01-25 09:00:00', '2024-01-25 09:45:00', '2024-01-25 09:00:00'),
    (3, 'SYNC-003', 3, 2, 3, 'initial', 'completed', 100.0, 2, '2024-01-25 10:00:00', '2024-01-25 10:20:00', '2024-01-25 10:00:00'),
    (4, 'SYNC-004', 4, 1, 4, 'initial', 'completed', 100.0, 3, '2024-01-25 02:00:00', '2024-01-25 02:30:00', '2024-01-25 02:00:00'),
    (5, 'SYNC-005', 1, 3, 1, 'initial', 'completed', 100.0, 1, '2024-01-25 06:20:00', '2024-01-25 06:22:00', '2024-01-25 06:20:00'),
    (6, 'SYNC-006', 5, 4, 5, 'initial', 'processing', 65.0, 1, '2024-01-25 08:00:00', NULL, '2024-01-25 08:00:00'),
    (7, 'SYNC-007', 1, 4, 1, 'update', 'pending', 0.0, 1, NULL, NULL, '2024-01-25 12:00:00');

Step 12: Insert Platform Assets

Track synced copies.

INSERT INTO platform_assets (id, source_asset_id, platform_id, platform_asset_id, platform_url, synced_version, local_checksum, remote_checksum, sync_status, last_synced_at, last_verified_at) VALUES
    (1, 1, 1, 'cdn-12345', 'https://cdn.example.com/v/launch_2024.mp4', 3, 'abc123def456', 'abc123def456', 'synced', '2024-01-25 06:15:00', '2024-01-25 12:00:00'),
    (2, 1, 2, 'yt-abcdefg', 'https://youtube.com/watch?v=abcdefg', 3, 'abc123def456', 'abc123def456', 'synced', '2024-01-25 09:45:00', '2024-01-25 12:00:00'),
    (3, 2, 3, 'vim-123456', 'https://vimeo.com/123456', 2, 'def456ghi789', 'def456ghi789', 'synced', '2024-01-25 10:20:00', '2024-01-25 12:00:00'),
    (4, 1, 4, 's3://backup/launch_2024.mp4', 's3://backup-bucket/media/launch_2024.mp4', 3, 'abc123def456', 'abc123def456', 'synced', '2024-01-25 02:30:00', '2024-01-25 12:00:00'),
    (5, 3, 1, 'cdn-67890', 'https://cdn.example.com/i/hero_product.jpg', 1, 'ghi789jkl012', 'ghi789jkl012', 'synced', '2024-01-25 06:22:00', '2024-01-25 12:00:00'),
    (6, 4, 5, 'social-98765', 'https://socialhub.com/media/98765', 1, 'jkl012mno345', NULL, 'syncing', '2024-01-25 08:00:00', NULL);

Step 13: Insert Conflicts and Events

Add issue tracking.

INSERT INTO sync_conflicts (id, platform_asset_id, conflict_type, source_value, target_value, detected_at, resolution, notes) VALUES
    (1, 6, 'checksum_mismatch', 'jkl012mno345', 'xyz987', '2024-01-25 08:30:00', 'pending', 'Upload interrupted, needs retry');

INSERT INTO sync_events_log (id, job_id, platform_asset_id, event_type, event_status, message, event_time) VALUES
    (1, 1, 1, 'upload_start', 'success', 'Started upload to CDN', '2024-01-25 06:00:00'),
    (2, 1, 1, 'upload_complete', 'success', 'Upload completed successfully', '2024-01-25 06:15:00'),
    (3, 1, 1, 'verification', 'success', 'Checksum verified', '2024-01-25 06:15:30'),
    (4, 6, 6, 'upload_start', 'success', 'Started upload to Social Hub', '2024-01-25 08:00:00'),
    (5, 6, 6, 'upload_progress', 'success', 'Upload at 65%', '2024-01-25 08:20:00'),
    (6, 6, 6, 'verification', 'failed', 'Checksum mismatch detected', '2024-01-25 08:30:00');

Step 14: Sync Status Dashboard

View overall sync status.

SELECT
    sp.platform_name,
    sp.health_status,
    COUNT(DISTINCT pa.source_asset_id) as synced_assets,
    COUNT(CASE WHEN pa.sync_status = 'synced' THEN 1 END) as up_to_date,
    COUNT(CASE WHEN pa.sync_status = 'outdated' THEN 1 END) as outdated,
    COUNT(CASE WHEN pa.sync_status = 'syncing' THEN 1 END) as in_progress,
    COUNT(sc.id) as conflicts
FROM sync_platforms sp
LEFT JOIN platform_assets pa ON sp.id = pa.platform_id
LEFT JOIN sync_conflicts sc ON pa.id = sc.platform_asset_id AND sc.resolution = 'pending'
WHERE sp.sync_enabled = TRUE
GROUP BY sp.id, sp.platform_name, sp.health_status
ORDER BY conflicts DESC, synced_assets DESC;

Step 15: Asset Distribution Report

View where assets are synced.

SELECT
    sa.asset_code,
    sa.title,
    sa.asset_type,
    sa.version as source_version,
    COUNT(pa.id) as platforms_synced,
    COUNT(CASE WHEN pa.synced_version = sa.version THEN 1 END) as up_to_date,
    COUNT(CASE WHEN pa.synced_version < sa.version THEN 1 END) as needs_update,
    STRING_AGG(sp.platform_name, ', ') as platforms
FROM source_assets sa
LEFT JOIN platform_assets pa ON sa.id = pa.source_asset_id
LEFT JOIN sync_platforms sp ON pa.platform_id = sp.id
WHERE sa.status = 'active'
GROUP BY sa.id, sa.asset_code, sa.title, sa.asset_type, sa.version
ORDER BY needs_update DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS sync_events_log;
DROP TABLE IF EXISTS sync_conflicts;
DROP TABLE IF EXISTS platform_assets;
DROP TABLE IF EXISTS sync_jobs;
DROP TABLE IF EXISTS sync_configurations;
DROP TABLE IF EXISTS sync_platforms;
DROP TABLE IF EXISTS source_assets;

Expected Outcomes

• Assets synced to platforms
• Sync status tracked
• Conflicts detected
• Events logged
• Distribution visible

Sync Status States

Key Concepts Learned

• Multi-platform sync
• Version tracking
• Conflict resolution
• Event logging
• Distribution management