Objective
Build a unified search system that enables searching across different content types (images, videos, audio, documents) using a common semantic interface and vector embeddings.
Step 1: Create Unified Content Table
Store all content types together.
CREATE TABLE unified_content (
id INTEGER PRIMARY KEY,
content_code VARCHAR(50) NOT NULL UNIQUE,
content_type VARCHAR(50) NOT NULL,
title VARCHAR(300) NOT NULL,
description TEXT,
source_table VARCHAR(100),
source_id INTEGER,
category VARCHAR(100),
tags TEXT,
file_size_mb DECIMAL(10, 2),
duration_seconds INTEGER,
page_count INTEGER,
width INTEGER,
height INTEGER,
created_by VARCHAR(100),
content_embedding VECTOR(384),
indexed_at TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Step 2: Create Content Relationships Table
Track content connections.
CREATE TABLE content_relationships (
id INTEGER PRIMARY KEY,
source_content_id INTEGER NOT NULL,
related_content_id INTEGER NOT NULL,
relationship_type VARCHAR(50),
similarity_score DECIMAL(5, 4),
is_manual BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (source_content_id) REFERENCES unified_content(id),
FOREIGN KEY (related_content_id) REFERENCES unified_content(id)
);
Step 3: Create Search Sessions Table
Track user search sessions.
CREATE TABLE search_sessions (
id INTEGER PRIMARY KEY,
session_id VARCHAR(100) NOT NULL UNIQUE,
user_id VARCHAR(50),
started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ended_at TIMESTAMP,
query_count INTEGER DEFAULT 0,
click_count INTEGER DEFAULT 0
);
Step 4: Create Search Queries Table
Log search requests.
CREATE TABLE search_queries (
id INTEGER PRIMARY KEY,
session_id INTEGER NOT NULL,
query_text TEXT,
query_embedding VECTOR(384),
content_type_filter VARCHAR(50),
category_filter VARCHAR(100),
results_count INTEGER,
search_time_ms INTEGER,
queried_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (session_id) REFERENCES search_sessions(id)
);
Step 5: Create Search Results Table
Track result interactions.
CREATE TABLE search_results (
id INTEGER PRIMARY KEY,
query_id INTEGER NOT NULL,
content_id INTEGER NOT NULL,
result_position INTEGER,
relevance_score DECIMAL(5, 4),
was_clicked BOOLEAN DEFAULT FALSE,
clicked_at TIMESTAMP,
time_on_content_seconds INTEGER,
FOREIGN KEY (query_id) REFERENCES search_queries(id),
FOREIGN KEY (content_id) REFERENCES unified_content(id)
);
Step 6: Insert Mixed Content
Add various content types.
-- Images
INSERT INTO unified_content (id, content_code, content_type, title, description, source_table, category, tags, file_size_mb, width, height, created_by) VALUES
(1, 'IMG-001', 'image', 'Product Hero Shot', 'High-resolution product photography showing the new Model X device from multiple angles', 'product_images', 'product', 'product,photography,hero,model-x', 15.2, 4000, 3000, 'design_team'),
(2, 'IMG-002', 'image', 'Team Photo 2024', 'Annual company team photo taken at headquarters', 'company_images', 'corporate', 'team,company,people,office', 8.5, 5000, 3333, 'hr_team'),
(3, 'IMG-003', 'image', 'Infographic - Sales Process', 'Visual guide to the sales process workflow', 'marketing_assets', 'marketing', 'infographic,sales,process,workflow', 2.1, 2000, 4000, 'marketing_team');
-- Videos
INSERT INTO unified_content (id, content_code, content_type, title, description, source_table, category, tags, file_size_mb, duration_seconds, created_by) VALUES
(4, 'VID-001', 'video', 'Product Launch Keynote', 'Annual product launch event featuring new announcements and demonstrations', 'video_library', 'corporate', 'keynote,launch,product,event', 2850.5, 5400, 'events_team'),
(5, 'VID-002', 'video', 'Customer Testimonial - TechCorp', 'Customer success story interview with TechCorp executives', 'video_library', 'marketing', 'testimonial,customer,success,interview', 580.2, 1200, 'marketing_team'),
(6, 'VID-003', 'video', 'Platform Tutorial Episode 1', 'Getting started tutorial covering basic features', 'video_library', 'education', 'tutorial,training,getting-started,beginner', 890.4, 1800, 'training_team');
-- Audio
INSERT INTO unified_content (id, content_code, content_type, title, description, source_table, category, tags, file_size_mb, duration_seconds, created_by) VALUES
(7, 'AUD-001', 'audio', 'Podcast Episode 45 - AI Trends', 'Discussion on emerging AI trends with industry experts', 'podcast_episodes', 'marketing', 'podcast,ai,trends,discussion,expert', 52.4, 4200, 'content_team'),
(8, 'AUD-002', 'audio', 'Q1 Earnings Call Recording', 'Quarterly earnings call with investors', 'earnings_calls', 'corporate', 'earnings,financial,investor,quarterly', 45.8, 3600, 'ir_team'),
(9, 'AUD-003', 'audio', 'Sales Training - Objection Handling', 'Audio training on handling common sales objections', 'training_audio', 'education', 'training,sales,objections,techniques', 28.5, 2400, 'sales_enablement');
-- Documents
INSERT INTO unified_content (id, content_code, content_type, title, description, source_table, category, tags, file_size_mb, page_count, created_by) VALUES
(10, 'DOC-001', 'document', 'Product Requirements Document', 'Detailed PRD for Product X version 3.0 release', 'documents', 'product', 'prd,requirements,product,specifications', 2.8, 35, 'product_team'),
(11, 'DOC-002', 'document', 'Employee Handbook 2024', 'Comprehensive guide to company policies and benefits', 'documents', 'hr', 'handbook,policies,hr,employee,benefits', 5.2, 85, 'hr_team'),
(12, 'DOC-003', 'document', 'Technical Architecture Guide', 'System architecture documentation for developers', 'documents', 'technical', 'architecture,technical,documentation,developer', 8.5, 120, 'engineering_team');