Voice Recording Storage

Objective

Create a system for storing voice memos and recordings with timestamps and metadata. This pattern is useful for voice note apps, dictation systems, and meeting recordings.

Step 1: Create Voice Recordings Table

Create a table for storing voice recordings.

CREATE TABLE voice_recordings (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255),
    recording AUDIO(WAV),
    recording_type VARCHAR(50),
    duration_seconds INTEGER,
    file_size BIGINT,
    recorded_by VARCHAR(100),
    device VARCHAR(100),
    location VARCHAR(255),
    is_transcribed BOOLEAN DEFAULT FALSE,
    is_starred BOOLEAN DEFAULT FALSE,
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Insert Sample Voice Recordings

Add sample voice recording records.

INSERT INTO voice_recordings (id, title, recording_type, duration_seconds, file_size, recorded_by, device, location) VALUES
    (1, 'Meeting Notes - Project Alpha', 'meeting', 1800, 86400000, 'john_doe', 'iPhone 15', 'Conference Room A'),
    (2, 'Idea: New Feature Concept', 'idea', 120, 5760000, 'jane_smith', 'MacBook Pro', 'Home Office'),
    (3, 'Client Call Summary', 'call', 900, 43200000, 'john_doe', 'iPhone 15', 'Office'),
    (4, 'Personal Reminder - Groceries', 'reminder', 30, 1440000, 'jane_smith', 'Apple Watch', 'Car'),
    (5, 'Interview Recording - Candidate A', 'interview', 2700, 129600000, 'hr_manager', 'Zoom H6', 'Meeting Room B'),
    (6, 'Quick Note - Password Reset', 'note', 15, 720000, 'john_doe', 'iPhone 15', 'Desk'),
    (7, 'Lecture Notes - Marketing 101', 'lecture', 3600, 172800000, 'student_alex', 'Voice Recorder', 'Classroom'),
    (8, 'Brainstorm Session', 'brainstorm', 1200, 57600000, 'team_lead', 'Conference Phone', 'War Room');

Step 3: Query Recent Recordings

Get recently created recordings.

SELECT
    title,
    recording_type,
    duration_seconds / 60 as duration_minutes,
    recorded_by,
    recorded_at
FROM voice_recordings
ORDER BY recorded_at DESC
LIMIT 10;

Step 4: Filter by Recording Type

Find recordings by type.

SELECT
    title,
    duration_seconds,
    recorded_by,
    location
FROM voice_recordings
WHERE recording_type = 'meeting'
ORDER BY recorded_at DESC;

Step 5: Recordings by User

Get all recordings for a specific user.

SELECT
    recording_type,
    COUNT(*) as recording_count,
    SUM(duration_seconds) / 60 as total_minutes,
    SUM(file_size) / 1048576 as total_mb
FROM voice_recordings
WHERE recorded_by = 'john_doe'
GROUP BY recording_type
ORDER BY recording_count DESC;

Step 6: Star Important Recordings

Mark recordings as starred for quick access.

UPDATE voice_recordings
SET is_starred = TRUE
WHERE id IN (1, 5, 7);

SELECT
    title,
    recording_type,
    recorded_at
FROM voice_recordings
WHERE is_starred = TRUE
ORDER BY recorded_at DESC;

Step 7: Unprocessed Recordings

Find recordings pending transcription.

SELECT
    id,
    title,
    recording_type,
    duration_seconds,
    recorded_at
FROM voice_recordings
WHERE is_transcribed = FALSE
ORDER BY duration_seconds DESC;

Step 8: Recording Statistics

Generate recording statistics.

SELECT
    recording_type,
    COUNT(*) as count,
    SUM(duration_seconds) as total_seconds,
    AVG(duration_seconds) as avg_seconds,
    SUM(file_size) / 1048576 as total_mb
FROM voice_recordings
GROUP BY recording_type
ORDER BY count DESC;

Step 9: Recordings by Device

Analyze recordings by device used.

SELECT
    device,
    COUNT(*) as recording_count,
    SUM(duration_seconds) / 60 as total_minutes
FROM voice_recordings
GROUP BY device
ORDER BY recording_count DESC;

Step 10: Recordings by Location

Find recordings by location.

SELECT
    location,
    COUNT(*) as recording_count,
    recording_type
FROM voice_recordings
WHERE location IS NOT NULL
GROUP BY location, recording_type
ORDER BY recording_count DESC;

Step 11: Long Recordings Report

Find recordings that may need review.

SELECT
    title,
    recording_type,
    duration_seconds / 60 as duration_minutes,
    file_size / 1048576 as size_mb,
    CASE
        WHEN duration_seconds > 3600 THEN 'Very Long'
        WHEN duration_seconds > 1800 THEN 'Long'
        WHEN duration_seconds > 600 THEN 'Medium'
        ELSE 'Short'
    END as length_category
FROM voice_recordings
WHERE duration_seconds > 600
ORDER BY duration_seconds DESC;

Step 12: Storage Usage Summary

Calculate total storage used.

SELECT
    COUNT(*) as total_recordings,
    SUM(duration_seconds) / 3600 as total_hours,
    SUM(file_size) / 1073741824 as total_gb,
    AVG(file_size) / 1048576 as avg_mb_per_recording,
    COUNT(CASE WHEN is_transcribed THEN 1 END) as transcribed_count,
    COUNT(CASE WHEN is_starred THEN 1 END) as starred_count
FROM voice_recordings;

Cleanup (Optional)

DROP TABLE IF EXISTS voice_recordings;

Expected Outcomes

• Voice recordings stored with metadata
• Type-based filtering works
• User recordings tracked
• Storage analysis available
• Transcription status tracked

Recording Types

Key Concepts Learned

• AUDIO(WAV) for high-quality recordings
• Metadata tracking for organization
• Type-based categorization
• Storage usage analysis
• Transcription workflow tracking