Video Content Intelligence

Objective

Build a video content intelligence system that analyzes videos to extract scenes, detect objects, generate summaries, and enable semantic search across video libraries.

Step 1: Create Video Library Table

Store video content.

CREATE TABLE video_library (
    id INTEGER PRIMARY KEY,
    video_code VARCHAR(50) NOT NULL UNIQUE,
    title VARCHAR(300),
    description TEXT,
    video_file VIDEO(MP4),
    thumbnail IMAGE(JPEG),
    duration_seconds INTEGER,
    file_size_mb DECIMAL(10, 2),
    resolution VARCHAR(20),
    frame_rate DECIMAL(5, 2),
    aspect_ratio VARCHAR(10),
    video_type VARCHAR(50),
    genre VARCHAR(100),
    language VARCHAR(20) DEFAULT 'en',
    content_rating VARCHAR(20),
    description_embedding VECTOR(384),
    processing_status VARCHAR(50) DEFAULT 'pending',
    processed_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Video Scenes Table

Store detected scenes.

CREATE TABLE video_scenes (
    id INTEGER PRIMARY KEY,
    video_id INTEGER NOT NULL,
    scene_number INTEGER,
    start_time_seconds DECIMAL(10, 3),
    end_time_seconds DECIMAL(10, 3),
    duration_seconds DECIMAL(10, 3),
    scene_thumbnail IMAGE(JPEG),
    scene_description TEXT,
    scene_embedding VECTOR(384),
    scene_type VARCHAR(50),
    dominant_colors TEXT,
    brightness_level VARCHAR(20),
    motion_intensity VARCHAR(20),
    FOREIGN KEY (video_id) REFERENCES video_library(id)
);

Step 3: Create Detected Objects Table

Track objects in scenes.

CREATE TABLE scene_objects (
    id INTEGER PRIMARY KEY,
    scene_id INTEGER NOT NULL,
    object_type VARCHAR(100),
    object_label VARCHAR(200),
    confidence DECIMAL(3, 2),
    bounding_box TEXT,
    first_appearance_seconds DECIMAL(10, 3),
    screen_time_seconds DECIMAL(10, 3),
    FOREIGN KEY (scene_id) REFERENCES video_scenes(id)
);

Step 4: Create Video Transcripts Table

Store spoken content.

CREATE TABLE video_transcripts (
    id INTEGER PRIMARY KEY,
    video_id INTEGER NOT NULL,
    full_transcript TEXT,
    word_count INTEGER,
    transcript_language VARCHAR(20),
    transcript_embedding VECTOR(384),
    has_captions BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (video_id) REFERENCES video_library(id)
);

Step 5: Create Video Analysis Table

Store AI insights.

CREATE TABLE video_analysis (
    id INTEGER PRIMARY KEY,
    video_id INTEGER NOT NULL,
    summary TEXT,
    key_moments TEXT,
    topics TEXT,
    sentiment_score DECIMAL(3, 2),
    content_tags TEXT,
    brand_mentions TEXT,
    scene_count INTEGER,
    face_count INTEGER,
    analyzed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (video_id) REFERENCES video_library(id)
);

Step 6: Create Video Chapters Table

Define navigable sections.

CREATE TABLE video_chapters (
    id INTEGER PRIMARY KEY,
    video_id INTEGER NOT NULL,
    chapter_number INTEGER,
    chapter_title VARCHAR(200),
    start_time_seconds INTEGER,
    end_time_seconds INTEGER,
    description TEXT,
    chapter_embedding VECTOR(384),
    thumbnail IMAGE(JPEG),
    FOREIGN KEY (video_id) REFERENCES video_library(id)
);

Step 7: Insert Sample Videos

Add video catalog.

INSERT INTO video_library (id, video_code, title, description, duration_seconds, file_size_mb, resolution, frame_rate, aspect_ratio, video_type, genre, content_rating, processing_status) VALUES
    (1, 'VID-001', 'Product Launch Keynote 2024', 'Annual product launch event featuring new product announcements and demonstrations', 5400, 2850.5, '4K', 30.0, '16:9', 'presentation', 'corporate', 'G', 'completed'),
    (2, 'VID-002', 'Customer Success Story - TechCorp', 'Interview with TechCorp about their success using our platform', 1200, 580.2, '1080p', 24.0, '16:9', 'testimonial', 'marketing', 'G', 'completed'),
    (3, 'VID-003', 'Platform Tutorial Series Ep.1', 'Getting started tutorial covering basic features and setup', 1800, 890.4, '1080p', 30.0, '16:9', 'tutorial', 'education', 'G', 'completed'),
    (4, 'VID-004', 'Tech Conference Talk', 'Technical deep dive on architecture and scaling', 2700, 1250.8, '1080p', 30.0, '16:9', 'conference', 'technical', 'G', 'completed'),
    (5, 'VID-005', 'Brand Story Documentary', 'Documentary about company history and mission', 3600, 1850.6, '4K', 24.0, '21:9', 'documentary', 'corporate', 'G', 'completed'),
    (6, 'VID-006', 'Product Demo - Enterprise Features', 'Demonstration of enterprise-specific features', 900, 420.3, '1080p', 30.0, '16:9', 'demo', 'product', 'G', 'completed');

UPDATE video_library
SET description_embedding = EMBED(description)
WHERE description_embedding IS NULL;

Step 8: Insert Video Scenes

Add scene detection results.

INSERT INTO video_scenes (id, video_id, scene_number, start_time_seconds, end_time_seconds, duration_seconds, scene_description, scene_type, dominant_colors, brightness_level, motion_intensity) VALUES
    (1, 1, 1, 0.0, 120.5, 120.5, 'Opening with company logo animation and title card', 'intro', 'blue,white', 'high', 'medium'),
    (2, 1, 2, 120.5, 480.2, 359.7, 'CEO on stage introducing the event and company vision', 'speaker', 'blue,black', 'medium', 'low'),
    (3, 1, 3, 480.2, 1200.8, 720.6, 'Product demonstration with live demo on large screen', 'demo', 'white,blue,green', 'high', 'high'),
    (4, 1, 4, 1200.8, 1800.5, 599.7, 'Panel discussion with product team', 'panel', 'neutral', 'medium', 'low'),
    (5, 2, 1, 0.0, 60.2, 60.2, 'Introduction and company overview B-roll', 'intro', 'blue,white', 'high', 'medium'),
    (6, 2, 2, 60.2, 600.5, 540.3, 'Interview with customer executive discussing challenges', 'interview', 'neutral', 'medium', 'low'),
    (7, 2, 3, 600.5, 1000.8, 400.3, 'Customer showing platform usage with screen recording', 'demo', 'white,blue', 'high', 'medium'),
    (8, 3, 1, 0.0, 90.0, 90.0, 'Tutorial intro with instructor and overview', 'intro', 'blue,white', 'high', 'low'),
    (9, 3, 2, 90.0, 600.5, 510.5, 'Step-by-step walkthrough of dashboard setup', 'tutorial', 'white,gray', 'high', 'medium'),
    (10, 3, 3, 600.5, 1200.2, 599.7, 'Advanced configuration options demonstration', 'tutorial', 'white,blue', 'high', 'medium');

UPDATE video_scenes
SET scene_embedding = EMBED(scene_description)
WHERE scene_embedding IS NULL;

Step 9: Insert Detected Objects

Add object detection results.

INSERT INTO scene_objects (id, scene_id, object_type, object_label, confidence, first_appearance_seconds, screen_time_seconds) VALUES
    (1, 2, 'person', 'CEO Speaker', 0.98, 120.5, 359.7),
    (2, 3, 'screen', 'Presentation Display', 0.95, 480.2, 720.6),
    (3, 3, 'laptop', 'Demo Laptop', 0.92, 520.0, 680.6),
    (4, 4, 'person', 'Panel Member 1', 0.96, 1200.8, 599.7),
    (5, 4, 'person', 'Panel Member 2', 0.95, 1200.8, 599.7),
    (6, 6, 'person', 'Customer Executive', 0.97, 60.2, 540.3),
    (7, 7, 'screen', 'Platform Dashboard', 0.94, 600.5, 400.3),
    (8, 9, 'screen', 'Tutorial Screen', 0.96, 90.0, 510.5),
    (9, 9, 'cursor', 'Mouse Pointer', 0.88, 95.0, 505.5),
    (10, 10, 'settings_panel', 'Configuration Menu', 0.91, 605.0, 595.2);

Step 10: Insert Transcripts and Analysis

Add video intelligence.

INSERT INTO video_transcripts (id, video_id, full_transcript, word_count, transcript_language, has_captions) VALUES
    (1, 1, 'Welcome to our 2024 product launch. Today we are excited to announce revolutionary new features that will transform how you work. Our platform now includes AI-powered analytics, enhanced collaboration tools, and a completely redesigned user interface...', 8500, 'en', TRUE),
    (2, 2, 'When we first started looking for a solution, we had significant challenges with data management. After implementing the platform, we saw a 40% improvement in efficiency. The support team has been incredible...', 2200, 'en', TRUE),
    (3, 3, 'In this tutorial, we will cover the basics of getting started with the platform. First, let us look at the dashboard. You will see your main metrics here, and you can customize the view to show what matters most to you...', 3500, 'en', TRUE),
    (4, 4, 'Today I want to talk about our architecture decisions and how we scaled to handle millions of requests. We use a microservices approach with event-driven communication...', 5200, 'en', TRUE);

UPDATE video_transcripts
SET transcript_embedding = EMBED(full_transcript)
WHERE transcript_embedding IS NULL;

INSERT INTO video_analysis (id, video_id, summary, key_moments, topics, sentiment_score, content_tags, scene_count, face_count) VALUES
    (1, 1, 'Annual product launch event showcasing new AI features, collaboration tools, and UI redesign. CEO presents company vision followed by live demonstrations and panel discussion.', 'Product reveal at 8:00, Live demo at 15:00, Q&A at 45:00', 'product launch, AI features, collaboration, user interface', 0.88, 'keynote,product,announcement,demo', 4, 8),
    (2, 2, 'Customer testimonial highlighting 40% efficiency improvement after platform adoption. Includes interview with executive and platform usage demonstration.', 'Challenge discussion at 1:00, Results reveal at 10:00', 'customer success, efficiency, ROI, implementation', 0.92, 'testimonial,customer,success-story', 3, 2),
    (3, 3, 'Beginner tutorial covering dashboard navigation, metric customization, and initial setup steps. Clear step-by-step instructions with screen recordings.', 'Dashboard overview at 1:30, Customization at 10:00, Settings at 20:00', 'tutorial, getting started, dashboard, configuration', 0.75, 'tutorial,beginner,how-to,education', 3, 1),
    (4, 4, 'Technical conference presentation on microservices architecture and scaling strategies. Covers event-driven design patterns and performance optimization.', 'Architecture overview at 5:00, Scaling deep-dive at 25:00', 'architecture, microservices, scaling, performance', 0.78, 'technical,conference,architecture,engineering', 2, 1);

Step 11: Insert Video Chapters

Create navigable sections.

INSERT INTO video_chapters (id, video_id, chapter_number, chapter_title, start_time_seconds, end_time_seconds, description) VALUES
    (1, 1, 1, 'Welcome & Introduction', 0, 480, 'Opening remarks and company vision'),
    (2, 1, 2, 'Product Demonstrations', 480, 1800, 'Live demos of new features'),
    (3, 1, 3, 'Panel Discussion', 1800, 3600, 'Q&A with the product team'),
    (4, 1, 4, 'Closing & Next Steps', 3600, 5400, 'Summary and roadmap preview'),
    (5, 3, 1, 'Getting Started', 0, 90, 'Introduction and prerequisites'),
    (6, 3, 2, 'Dashboard Overview', 90, 600, 'Navigating the main dashboard'),
    (7, 3, 3, 'Configuration', 600, 1200, 'Setting up your workspace'),
    (8, 3, 4, 'Best Practices', 1200, 1800, 'Tips and recommendations');

UPDATE video_chapters
SET chapter_embedding = EMBED(description)
WHERE chapter_embedding IS NULL;

Step 12: Semantic Video Search

Find videos by meaning.

SELECT
    vl.video_code,
    vl.title,
    vl.video_type,
    vl.duration_seconds / 60 as duration_minutes,
    COSINE_SIMILARITY(vl.description_embedding, EMBED('customer success and business results')) as relevance
FROM video_library vl
WHERE vl.description_embedding IS NOT NULL
ORDER BY relevance DESC
LIMIT 5;

Step 13: Search Scene Content

Find specific scenes.

SELECT
    vl.title as video,
    vs.scene_number,
    vs.scene_description,
    vs.start_time_seconds,
    vs.scene_type,
    COSINE_SIMILARITY(vs.scene_embedding, EMBED('product demonstration and live demo')) as relevance
FROM video_scenes vs
INNER JOIN video_library vl ON vs.video_id = vl.id
WHERE vs.scene_embedding IS NOT NULL
ORDER BY relevance DESC
LIMIT 5;

Step 14: Video Analytics Dashboard

Summarize video intelligence.

SELECT
    vl.video_type,
    COUNT(*) as video_count,
    AVG(vl.duration_seconds) / 60 as avg_duration_minutes,
    AVG(va.sentiment_score) as avg_sentiment,
    SUM(va.scene_count) as total_scenes,
    SUM(va.face_count) as total_faces
FROM video_library vl
LEFT JOIN video_analysis va ON vl.id = va.video_id
GROUP BY vl.video_type
ORDER BY video_count DESC;

Step 15: Chapter Navigation

Find chapters by topic.

SELECT
    vl.title as video,
    vc.chapter_number,
    vc.chapter_title,
    vc.start_time_seconds,
    vc.description,
    COSINE_SIMILARITY(vc.chapter_embedding, EMBED('configuration and settings setup')) as relevance
FROM video_chapters vc
INNER JOIN video_library vl ON vc.video_id = vl.id
WHERE vc.chapter_embedding IS NOT NULL
ORDER BY relevance DESC
LIMIT 5;

Cleanup (Optional)

DROP TABLE IF EXISTS video_chapters;
DROP TABLE IF EXISTS video_analysis;
DROP TABLE IF EXISTS video_transcripts;
DROP TABLE IF EXISTS scene_objects;
DROP TABLE IF EXISTS video_scenes;
DROP TABLE IF EXISTS video_library;

Expected Outcomes

• Videos cataloged with metadata
• Scenes automatically detected
• Objects identified in scenes
• Transcripts searchable
• Chapters enable navigation

Key Concepts Learned

• Video scene detection
• Object recognition storage
• Multi-level embeddings
• Chapter-based navigation
• Video analytics