Video Transcription

Objective

Transcribe video content to text using AI transcription. This enables full-text search, subtitle generation, accessibility compliance, and content analysis.

Step 1: Create Video Table

Create a table for videos with transcription support.

CREATE TABLE transcribed_videos (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    video_file VIDEO(MP4),
    audio_track AUDIO(MP3),
    duration_seconds INTEGER,
    language VARCHAR(10) DEFAULT 'en',
    has_speech BOOLEAN DEFAULT TRUE,
    transcription_status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Transcription Table

Create a table for storing transcriptions.

CREATE TABLE video_transcriptions (
    id INTEGER PRIMARY KEY,
    video_id INTEGER NOT NULL,
    full_transcript TEXT,
    word_count INTEGER,
    confidence_score DECIMAL(5, 4),
    language_detected VARCHAR(10),
    transcription_model VARCHAR(50),
    processing_time_seconds INTEGER,
    is_reviewed BOOLEAN DEFAULT FALSE,
    reviewer VARCHAR(100),
    reviewed_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (video_id) REFERENCES transcribed_videos(id)
);

Step 3: Create Transcript Segments Table

Store time-coded transcript segments.

CREATE TABLE transcript_segments (
    id INTEGER PRIMARY KEY,
    transcription_id INTEGER NOT NULL,
    segment_index INTEGER NOT NULL,
    start_time DECIMAL(10, 3),
    end_time DECIMAL(10, 3),
    text_content TEXT NOT NULL,
    speaker VARCHAR(100),
    confidence DECIMAL(5, 4),
    FOREIGN KEY (transcription_id) REFERENCES video_transcriptions(id)
);

Step 4: Insert Sample Videos

Add sample video records.

INSERT INTO transcribed_videos (id, title, duration_seconds, language, has_speech, transcription_status) VALUES
    (1, 'CEO Quarterly Update', 1200, 'en', TRUE, 'completed'),
    (2, 'Product Tutorial', 600, 'en', TRUE, 'completed'),
    (3, 'Customer Interview', 900, 'en', TRUE, 'completed'),
    (4, 'Silent Demo Video', 300, 'en', FALSE, 'skipped'),
    (5, 'Technical Webinar', 3600, 'en', TRUE, 'processing'),
    (6, 'Spanish Training', 1500, 'es', TRUE, 'completed'),
    (7, 'Panel Discussion', 2700, 'en', TRUE, 'pending');

Step 5: Insert Transcriptions

Add transcription records.

INSERT INTO video_transcriptions (id, video_id, full_transcript, word_count, confidence_score, language_detected, transcription_model, processing_time_seconds, is_reviewed) VALUES
    (1, 1, 'Welcome to our quarterly update. This quarter we have achieved significant milestones...', 2850, 0.9567, 'en', 'whisper-large', 45, TRUE),
    (2, 2, 'In this tutorial, we will walk through the key features of our product...', 1425, 0.9234, 'en', 'whisper-large', 23, TRUE),
    (3, 3, 'Today we are speaking with one of our valued customers about their experience...', 2100, 0.9456, 'en', 'whisper-large', 34, FALSE),
    (4, 6, 'Bienvenidos a esta sesión de capacitación donde aprenderemos...', 3500, 0.9123, 'es', 'whisper-large', 56, TRUE);

Step 6: Insert Transcript Segments

Add time-coded segments.

INSERT INTO transcript_segments (id, transcription_id, segment_index, start_time, end_time, text_content, speaker, confidence) VALUES
    -- CEO Update segments
    (1, 1, 1, 0.000, 5.500, 'Welcome to our quarterly update.', 'CEO', 0.9876),
    (2, 1, 2, 5.500, 12.200, 'This quarter we have achieved significant milestones in our growth strategy.', 'CEO', 0.9654),
    (3, 1, 3, 12.200, 20.100, 'Let me walk you through the highlights of our performance.', 'CEO', 0.9789),
    (4, 1, 4, 20.100, 35.800, 'Revenue increased by 25 percent compared to the previous quarter.', 'CEO', 0.9567),
    -- Tutorial segments
    (5, 2, 1, 0.000, 4.200, 'In this tutorial, we will walk through the key features.', 'Instructor', 0.9345),
    (6, 2, 2, 4.200, 10.500, 'First, let me show you the main dashboard.', 'Instructor', 0.9234),
    (7, 2, 3, 10.500, 18.300, 'Here you can see all your projects listed on the left side.', 'Instructor', 0.9456),
    -- Interview segments
    (8, 3, 1, 0.000, 6.800, 'Today we are speaking with one of our valued customers.', 'Interviewer', 0.9678),
    (9, 3, 2, 6.800, 15.200, 'Thank you for having me. I am excited to share our story.', 'Customer', 0.9234),
    (10, 3, 3, 15.200, 28.500, 'Can you tell us about your experience with our product?', 'Interviewer', 0.9567);

Step 7: Query Transcribed Videos

Get videos with their transcription status.

SELECT
    v.title,
    v.duration_seconds / 60 as minutes,
    v.language,
    v.transcription_status,
    vt.word_count,
    vt.confidence_score
FROM transcribed_videos v
LEFT JOIN video_transcriptions vt ON v.id = vt.video_id
ORDER BY v.transcription_status, v.title;

Step 8: Search Transcripts

Find videos containing specific keywords.

SELECT
    v.title,
    vt.full_transcript,
    vt.confidence_score
FROM transcribed_videos v
INNER JOIN video_transcriptions vt ON v.id = vt.video_id
WHERE vt.full_transcript LIKE '%quarterly%'
   OR vt.full_transcript LIKE '%milestone%'
ORDER BY vt.confidence_score DESC;

Step 9: Get Segment Timeline

Retrieve time-coded segments for a video.

SELECT
    ts.start_time,
    ts.end_time,
    ts.end_time - ts.start_time as duration,
    ts.speaker,
    ts.text_content,
    ts.confidence
FROM transcript_segments ts
INNER JOIN video_transcriptions vt ON ts.transcription_id = vt.id
WHERE vt.video_id = 1
ORDER BY ts.segment_index;

Step 10: Speaker Analysis

Analyze speaking time by participant.

SELECT
    speaker,
    COUNT(*) as segment_count,
    SUM(end_time - start_time) as total_seconds,
    AVG(confidence) as avg_confidence
FROM transcript_segments
WHERE transcription_id = 1
GROUP BY speaker
ORDER BY total_seconds DESC;

Step 11: Transcription Quality Report

Review transcription quality metrics.

SELECT
    v.title,
    vt.word_count,
    vt.confidence_score,
    vt.processing_time_seconds,
    vt.is_reviewed,
    CASE
        WHEN vt.confidence_score >= 0.95 THEN 'Excellent'
        WHEN vt.confidence_score >= 0.90 THEN 'Good'
        WHEN vt.confidence_score >= 0.80 THEN 'Acceptable'
        ELSE 'Needs Review'
    END as quality_rating
FROM transcribed_videos v
INNER JOIN video_transcriptions vt ON v.id = vt.video_id
ORDER BY vt.confidence_score DESC;

Step 12: Videos Needing Transcription

Find videos pending transcription.

SELECT
    id,
    title,
    duration_seconds / 60 as minutes,
    language,
    'Pending transcription' as status
FROM transcribed_videos
WHERE transcription_status IN ('pending', 'processing')
  AND has_speech = TRUE
ORDER BY duration_seconds;

Cleanup (Optional)

DROP TABLE IF EXISTS transcript_segments;
DROP TABLE IF EXISTS video_transcriptions;
DROP TABLE IF EXISTS transcribed_videos;

Expected Outcomes

• Videos transcribed with AI
• Time-coded segments stored
• Full-text search enabled
• Speaker identification works
• Quality metrics tracked

Transcription Status

Key Concepts Learned

• AI-powered transcription
• Time-coded segment storage
• Full-text search on video
• Speaker diarization
• Quality confidence tracking