Objective
Create an audio processing pipeline that transcribes audio content, extracts key information, performs sentiment analysis, and enables semantic search across audio transcripts.
Step 1: Create Audio Files Table
Store audio content.
CREATE TABLE audio_files (
id INTEGER PRIMARY KEY,
audio_code VARCHAR(50) NOT NULL UNIQUE,
title VARCHAR(300),
description TEXT,
audio_file AUDIO(MP3),
file_size_mb DECIMAL(10, 2),
duration_seconds INTEGER,
sample_rate INTEGER,
channels INTEGER,
audio_type VARCHAR(50),
language VARCHAR(20) DEFAULT 'en',
source VARCHAR(100),
recorded_at TIMESTAMP,
uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Step 2: Create Transcripts Table
Store transcription results.
CREATE TABLE audio_transcripts (
id INTEGER PRIMARY KEY,
audio_id INTEGER NOT NULL,
full_transcript TEXT,
word_count INTEGER,
transcript_language VARCHAR(20),
confidence_score DECIMAL(3, 2),
processing_time_seconds INTEGER,
transcript_embedding VECTOR(384),
is_reviewed BOOLEAN DEFAULT FALSE,
reviewed_by VARCHAR(100),
reviewed_at TIMESTAMP,
transcribed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (audio_id) REFERENCES audio_files(id)
);
Step 3: Create Speaker Segments Table
Track speaker turns.
CREATE TABLE speaker_segments (
id INTEGER PRIMARY KEY,
transcript_id INTEGER NOT NULL,
segment_number INTEGER,
speaker_label VARCHAR(100),
speaker_id VARCHAR(50),
start_time_seconds DECIMAL(10, 3),
end_time_seconds DECIMAL(10, 3),
segment_text TEXT,
segment_embedding VECTOR(384),
word_count INTEGER,
confidence DECIMAL(3, 2),
FOREIGN KEY (transcript_id) REFERENCES audio_transcripts(id)
);
Step 4: Create Analysis Results Table
Store AI analysis.
CREATE TABLE transcript_analysis (
id INTEGER PRIMARY KEY,
transcript_id INTEGER NOT NULL,
analysis_type VARCHAR(50),
summary TEXT,
key_topics TEXT,
sentiment_score DECIMAL(3, 2),
sentiment_label VARCHAR(20),
entities_extracted TEXT,
action_items TEXT,
analyzed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (transcript_id) REFERENCES audio_transcripts(id)
);
Step 5: Create Keywords Table
Extract important terms.
CREATE TABLE transcript_keywords (
id INTEGER PRIMARY KEY,
transcript_id INTEGER NOT NULL,
keyword VARCHAR(100),
keyword_type VARCHAR(50),
frequency INTEGER,
relevance_score DECIMAL(3, 2),
first_occurrence_seconds DECIMAL(10, 3),
FOREIGN KEY (transcript_id) REFERENCES audio_transcripts(id)
);
Step 6: Insert Sample Audio Files
Add audio content.
INSERT INTO audio_files (id, audio_code, title, description, file_size_mb, duration_seconds, sample_rate, channels, audio_type, language, source, recorded_at) VALUES
(1, 'AUD-001', 'Q1 Earnings Call', 'Quarterly earnings call with investors', 45.8, 3600, 44100, 2, 'conference_call', 'en', 'Investor Relations', '2024-01-25 14:00:00'),
(2, 'AUD-002', 'Customer Support Call #1234', 'Support call regarding product issue', 8.5, 720, 16000, 1, 'support_call', 'en', 'Support Center', '2024-01-20 10:30:00'),
(3, 'AUD-003', 'Product Strategy Meeting', 'Internal meeting on 2024 product roadmap', 28.4, 2400, 44100, 2, 'meeting', 'en', 'Product Team', '2024-01-18 09:00:00'),
(4, 'AUD-004', 'Sales Training Session', 'Training on new sales methodology', 65.2, 5400, 44100, 2, 'training', 'en', 'Sales Enablement', '2024-01-22 13:00:00'),
(5, 'AUD-005', 'Customer Interview', 'User research interview for mobile app', 22.6, 1800, 44100, 2, 'interview', 'en', 'UX Research', '2024-01-15 11:00:00'),
(6, 'AUD-006', 'Podcast Episode 45', 'Tech trends discussion with industry experts', 52.4, 4200, 44100, 2, 'podcast', 'en', 'Marketing', '2024-01-28 00:00:00');
Step 7: Insert Transcripts
Add transcription results.
INSERT INTO audio_transcripts (id, audio_id, full_transcript, word_count, transcript_language, confidence_score, processing_time_seconds) VALUES
(1, 1, 'Good afternoon everyone and welcome to our Q1 2024 earnings call. I am pleased to report strong financial results with revenue growth of 25% year over year. Our key metrics show continued momentum across all business segments. Customer acquisition costs decreased while lifetime value increased. We are raising our full year guidance based on this performance...', 8500, 'en', 0.94, 180),
(2, 2, 'Hello, thank you for calling support. How can I help you today? The customer explained they were having issues with the login feature on the mobile app. After troubleshooting, we identified the issue was related to cached credentials. The solution involved clearing the app cache and re-authenticating. Customer confirmed the issue was resolved.', 1200, 'en', 0.92, 45),
(3, 3, 'Today we will discuss our product roadmap for 2024. Our main priorities are improving user experience, expanding our API capabilities, and launching the mobile application. We have allocated resources across three main initiatives. Timeline shows Q2 for API launch and Q3 for mobile...', 4500, 'en', 0.95, 120),
(4, 4, 'Welcome to sales training. Today we cover the consultative selling methodology. The key principles are understanding customer needs, building value propositions, and handling objections effectively. We will practice with role-playing exercises...', 9200, 'en', 0.93, 210),
(5, 5, 'Thank you for participating in this research session. Can you tell me about your typical workflow when using our application? The user described their daily usage patterns and pain points with the current navigation. Key feedback included desire for better search functionality and faster load times...', 3200, 'en', 0.91, 90);
Step 8: Generate Embeddings
Create vector representations.
UPDATE audio_transcripts
SET transcript_embedding = EMBED(full_transcript)
WHERE transcript_embedding IS NULL;
SELECT
af.audio_code,
af.title,
CASE WHEN at.transcript_embedding IS NOT NULL THEN 'Yes' ELSE 'No' END as embedded
FROM audio_files af
LEFT JOIN audio_transcripts at ON af.id = at.audio_id
ORDER BY af.id;