Meeting Recordings Archive

Objective

Create a meeting recordings archive system with searchable transcripts, participant tracking, action item extraction, and retention policy management.

Step 1: Create Meeting Series Table

Define recurring meetings.

CREATE TABLE meeting_series (
    id INTEGER PRIMARY KEY,
    series_code VARCHAR(50) NOT NULL UNIQUE,
    series_name VARCHAR(200),
    description TEXT,
    meeting_type VARCHAR(50),
    owner_id VARCHAR(50),
    owner_name VARCHAR(200),
    department VARCHAR(100),
    recurrence_pattern VARCHAR(50),
    default_duration_minutes INTEGER,
    auto_record BOOLEAN DEFAULT TRUE,
    retention_days INTEGER DEFAULT 365,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Meeting Instances Table

Track individual meetings.

CREATE TABLE meeting_instances (
    id INTEGER PRIMARY KEY,
    series_id INTEGER,
    meeting_code VARCHAR(50) NOT NULL UNIQUE,
    title VARCHAR(300),
    description TEXT,
    agenda TEXT,
    meeting_date DATE,
    start_time TIME,
    end_time TIME,
    actual_duration_minutes INTEGER,
    location VARCHAR(200),
    meeting_url VARCHAR(500),
    host_id VARCHAR(50),
    host_name VARCHAR(200),
    participant_count INTEGER,
    is_recorded BOOLEAN DEFAULT FALSE,
    status VARCHAR(50) DEFAULT 'scheduled',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (series_id) REFERENCES meeting_series(id)
);

Step 3: Create Meeting Recordings Table

Store recording files.

CREATE TABLE meeting_recordings (
    id INTEGER PRIMARY KEY,
    meeting_id INTEGER NOT NULL,
    recording_type VARCHAR(50),
    video_file VIDEO(MP4),
    audio_file AUDIO(MP3),
    screen_share_file VIDEO(MP4),
    duration_seconds INTEGER,
    file_size_mb DECIMAL(10, 2),
    resolution VARCHAR(20),
    processing_status VARCHAR(50) DEFAULT 'processing',
    playback_url VARCHAR(500),
    download_url VARCHAR(500),
    thumbnail IMAGE(JPEG),
    view_count INTEGER DEFAULT 0,
    retention_until DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (meeting_id) REFERENCES meeting_instances(id)
);

Step 4: Create Meeting Transcripts Table

Store searchable transcripts.

CREATE TABLE meeting_transcripts (
    id INTEGER PRIMARY KEY,
    recording_id INTEGER NOT NULL,
    full_transcript TEXT,
    transcript_language VARCHAR(20) DEFAULT 'en',
    word_count INTEGER,
    confidence_score DECIMAL(3, 2),
    is_edited BOOLEAN DEFAULT FALSE,
    edited_by VARCHAR(100),
    edited_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (recording_id) REFERENCES meeting_recordings(id)
);

Step 5: Create Transcript Segments Table

Enable timestamp navigation.

CREATE TABLE transcript_segments (
    id INTEGER PRIMARY KEY,
    transcript_id INTEGER NOT NULL,
    segment_number INTEGER,
    start_time_seconds INTEGER,
    end_time_seconds INTEGER,
    speaker_name VARCHAR(200),
    speaker_id VARCHAR(50),
    text_content TEXT,
    confidence DECIMAL(3, 2),
    FOREIGN KEY (transcript_id) REFERENCES meeting_transcripts(id)
);

Step 6: Create Meeting Participants Table

Track attendees.

CREATE TABLE meeting_participants (
    id INTEGER PRIMARY KEY,
    meeting_id INTEGER NOT NULL,
    participant_id VARCHAR(50),
    participant_name VARCHAR(200),
    participant_email VARCHAR(200),
    participant_role VARCHAR(50),
    department VARCHAR(100),
    joined_at TIMESTAMP,
    left_at TIMESTAMP,
    duration_minutes INTEGER,
    is_external BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (meeting_id) REFERENCES meeting_instances(id)
);

Step 7: Create Action Items Table

Extract meeting actions.

CREATE TABLE meeting_action_items (
    id INTEGER PRIMARY KEY,
    meeting_id INTEGER NOT NULL,
    action_text TEXT,
    assigned_to VARCHAR(200),
    assigned_to_id VARCHAR(50),
    due_date DATE,
    priority VARCHAR(20),
    status VARCHAR(50) DEFAULT 'open',
    extracted_from_timestamp INTEGER,
    completed_at TIMESTAMP,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (meeting_id) REFERENCES meeting_instances(id)
);

Step 8: Create Meeting Access Log Table

Track viewing.

CREATE TABLE meeting_access_log (
    id INTEGER PRIMARY KEY,
    recording_id INTEGER NOT NULL,
    user_id VARCHAR(50),
    user_name VARCHAR(200),
    access_type VARCHAR(50),
    watched_seconds INTEGER,
    accessed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (recording_id) REFERENCES meeting_recordings(id)
);

Step 9: Insert Sample Meeting Series

Add recurring meetings.

INSERT INTO meeting_series (id, series_code, series_name, description, meeting_type, owner_name, department, recurrence_pattern, default_duration_minutes, auto_record, retention_days) VALUES
    (1, 'EXEC-WEEKLY', 'Executive Weekly Sync', 'Weekly executive leadership meeting', 'leadership', 'CEO Office', 'Executive', 'weekly', 60, TRUE, 730),
    (2, 'ENG-STANDUP', 'Engineering Daily Standup', 'Daily engineering team sync', 'standup', 'Engineering Manager', 'Engineering', 'daily', 15, FALSE, 30),
    (3, 'ALL-HANDS', 'Company All-Hands', 'Monthly all-company meeting', 'all_hands', 'CEO Office', 'All', 'monthly', 90, TRUE, 1095),
    (4, 'SALES-PIPELINE', 'Sales Pipeline Review', 'Weekly sales pipeline review', 'review', 'Sales Director', 'Sales', 'weekly', 60, TRUE, 365),
    (5, 'PRODUCT-REVIEW', 'Product Review', 'Bi-weekly product review', 'review', 'Product Manager', 'Product', 'biweekly', 60, TRUE, 365);

Step 10: Insert Meeting Instances

Add meeting records.

INSERT INTO meeting_instances (id, series_id, meeting_code, title, description, meeting_date, start_time, end_time, actual_duration_minutes, location, host_name, participant_count, is_recorded, status) VALUES
    (1, 1, 'EXEC-2024-01-15', 'Executive Weekly - Jan 15', 'Weekly sync on Q1 priorities', '2024-01-15', '09:00:00', '10:05:00', 65, 'Conference Room A / Zoom', 'John CEO', 8, TRUE, 'completed'),
    (2, 3, 'AH-2024-01', 'January All-Hands', 'Monthly all-hands - January', '2024-01-25', '14:00:00', '15:30:00', 90, 'Main Auditorium / Zoom', 'John CEO', 250, TRUE, 'completed'),
    (3, 4, 'SALES-2024-01-22', 'Sales Pipeline - Jan 22', 'Weekly pipeline review', '2024-01-22', '10:00:00', '11:00:00', 58, 'Sales War Room / Zoom', 'Sarah Sales', 12, TRUE, 'completed'),
    (4, 5, 'PROD-2024-01-18', 'Product Review - Jan 18', 'Bi-weekly product review', '2024-01-18', '15:00:00', '16:00:00', 55, 'Product Lab / Zoom', 'Mike Product', 15, TRUE, 'completed'),
    (5, 1, 'EXEC-2024-01-22', 'Executive Weekly - Jan 22', 'Weekly sync on initiatives', '2024-01-22', '09:00:00', '10:00:00', 60, 'Conference Room A / Zoom', 'John CEO', 8, TRUE, 'completed'),
    (6, 3, 'AH-2024-02', 'February All-Hands', 'Monthly all-hands - February', '2024-02-22', '14:00:00', '15:30:00', NULL, 'Main Auditorium / Zoom', 'John CEO', NULL, FALSE, 'scheduled');

Step 11: Insert Recordings and Transcripts

Add media files.

INSERT INTO meeting_recordings (id, meeting_id, recording_type, duration_seconds, file_size_mb, resolution, processing_status, view_count, retention_until) VALUES
    (1, 1, 'combined', 3900, 485.5, '1080p', 'ready', 42, '2026-01-15'),
    (2, 2, 'combined', 5400, 1250.8, '1080p', 'ready', 186, '2027-01-25'),
    (3, 2, 'audio_only', 5400, 82.4, NULL, 'ready', 45, '2027-01-25'),
    (4, 3, 'combined', 3480, 425.2, '1080p', 'ready', 28, '2025-01-22'),
    (5, 4, 'combined', 3300, 398.6, '1080p', 'ready', 35, '2025-01-18'),
    (6, 5, 'combined', 3600, 445.8, '1080p', 'ready', 38, '2026-01-22');

INSERT INTO meeting_transcripts (id, recording_id, full_transcript, transcript_language, word_count, confidence_score) VALUES
    (1, 1, 'Good morning everyone. Let us start with the Q1 priorities update...', 'en', 4250, 0.94),
    (2, 2, 'Welcome to our January all-hands meeting. I am excited to share our 2023 results...', 'en', 8500, 0.92),
    (3, 4, 'Good morning sales team. Let us review this weeks pipeline...', 'en', 3800, 0.93),
    (4, 5, 'Welcome to our product review. Today we will discuss the roadmap...', 'en', 3500, 0.95),
    (5, 6, 'Good morning everyone. Lets dive into this weeks priorities...', 'en', 4100, 0.94);

Step 12: Insert Participants and Actions

Add attendee and action data.

INSERT INTO meeting_participants (id, meeting_id, participant_id, participant_name, participant_role, department, joined_at, left_at, duration_minutes, is_external) VALUES
    (1, 1, 'USR-CEO', 'John CEO', 'host', 'Executive', '2024-01-15 08:58:00', '2024-01-15 10:05:00', 67, FALSE),
    (2, 1, 'USR-CFO', 'Jane CFO', 'participant', 'Finance', '2024-01-15 09:00:00', '2024-01-15 10:05:00', 65, FALSE),
    (3, 1, 'USR-CTO', 'Bob CTO', 'participant', 'Engineering', '2024-01-15 09:01:00', '2024-01-15 10:05:00', 64, FALSE),
    (4, 2, 'USR-CEO', 'John CEO', 'host', 'Executive', '2024-01-25 13:55:00', '2024-01-25 15:30:00', 95, FALSE),
    (5, 3, 'USR-SALES', 'Sarah Sales', 'host', 'Sales', '2024-01-22 09:58:00', '2024-01-22 11:00:00', 62, FALSE);

INSERT INTO meeting_action_items (id, meeting_id, action_text, assigned_to, due_date, priority, status, extracted_from_timestamp) VALUES
    (1, 1, 'Finalize Q1 budget allocation', 'Jane CFO', '2024-01-22', 'high', 'completed', 1200),
    (2, 1, 'Present engineering roadmap at next meeting', 'Bob CTO', '2024-01-22', 'medium', 'completed', 2400),
    (3, 1, 'Schedule customer advisory board meeting', 'Sarah Sales', '2024-01-31', 'medium', 'open', 3000),
    (4, 3, 'Follow up with Enterprise Corp on proposal', 'Tom Sales', '2024-01-25', 'high', 'completed', 1800),
    (5, 3, 'Update CRM with new pipeline stages', 'Sales Ops', '2024-01-29', 'low', 'open', 2700),
    (6, 4, 'Complete user research for mobile feature', 'UX Team', '2024-02-01', 'high', 'open', 1500);

Step 13: Meeting Archive Search

Search recordings.

SELECT
    mi.meeting_code,
    mi.title,
    mi.meeting_date,
    mi.host_name,
    mi.participant_count,
    mr.duration_seconds / 60 as duration_minutes,
    mr.view_count,
    ms.series_name
FROM meeting_instances mi
LEFT JOIN meeting_series ms ON mi.series_id = ms.id
LEFT JOIN meeting_recordings mr ON mi.id = mr.meeting_id
WHERE mi.is_recorded = TRUE
  AND mr.processing_status = 'ready'
ORDER BY mi.meeting_date DESC;

Step 14: Transcript Search

Find content in meetings.

SELECT
    mi.title,
    mi.meeting_date,
    ts.speaker_name,
    ts.start_time_seconds,
    ts.text_content
FROM transcript_segments ts
INNER JOIN meeting_transcripts mt ON ts.transcript_id = mt.id
INNER JOIN meeting_recordings mr ON mt.recording_id = mr.id
INNER JOIN meeting_instances mi ON mr.meeting_id = mi.id
WHERE ts.text_content LIKE '%roadmap%'
ORDER BY mi.meeting_date DESC, ts.start_time_seconds;

Step 15: Action Items Dashboard

Track meeting actions.

SELECT
    mi.title as meeting,
    mi.meeting_date,
    mai.action_text,
    mai.assigned_to,
    mai.due_date,
    mai.priority,
    mai.status,
    CASE
        WHEN mai.status = 'completed' THEN 'Done'
        WHEN mai.due_date < CURRENT_DATE THEN 'Overdue'
        ELSE 'On Track'
    END as health
FROM meeting_action_items mai
INNER JOIN meeting_instances mi ON mai.meeting_id = mi.id
WHERE mai.status != 'completed'
   OR mai.completed_at >= CURRENT_DATE - 7
ORDER BY mai.due_date;

Cleanup (Optional)

DROP TABLE IF EXISTS meeting_access_log;
DROP TABLE IF EXISTS meeting_action_items;
DROP TABLE IF EXISTS meeting_participants;
DROP TABLE IF EXISTS transcript_segments;
DROP TABLE IF EXISTS meeting_transcripts;
DROP TABLE IF EXISTS meeting_recordings;
DROP TABLE IF EXISTS meeting_instances;
DROP TABLE IF EXISTS meeting_series;

Expected Outcomes

• Meetings organized by series
• Recordings archived
• Transcripts searchable
• Actions tracked
• Access logged

Key Concepts Learned

• Meeting management
• Recording archival
• Transcript search
• Action item tracking
• Retention policies