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');