163 lines
18 KiB
SQL
163 lines
18 KiB
SQL
-- =====================================================
|
|
-- Tools Table Migration
|
|
-- Business/Productivity Tools for Domain-Based Recommendations
|
|
-- =====================================================
|
|
|
|
-- Create tools table
|
|
CREATE TABLE tools (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
category VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
primary_use_cases TEXT,
|
|
popularity_score INT CHECK (popularity_score >= 1 AND popularity_score <= 100),
|
|
created_at TIMESTAMP DEFAULT now()
|
|
);
|
|
|
|
-- Create indexes for better performance
|
|
CREATE INDEX idx_tools_category ON tools(category);
|
|
CREATE INDEX idx_tools_popularity ON tools(popularity_score);
|
|
CREATE INDEX idx_tools_name_search ON tools USING gin(to_tsvector('english', name));
|
|
|
|
-- =====================================================
|
|
-- SEED DATA - BUSINESS/PRODUCTIVITY TOOLS
|
|
-- =====================================================
|
|
|
|
INSERT INTO tools (name, category, description, primary_use_cases, popularity_score) VALUES
|
|
|
|
-- E-commerce Tools
|
|
('Shopify', 'e-commerce', 'Complete e-commerce platform for online stores with built-in payment processing, inventory management, and marketing tools', 'Online store creation, product management, order processing, payment handling, inventory tracking, customer management, marketing automation', 95),
|
|
('WooCommerce', 'e-commerce', 'WordPress plugin that transforms any WordPress site into a fully functional e-commerce store', 'WordPress e-commerce, product catalog, payment processing, order management, inventory control, customer accounts', 90),
|
|
('Magento', 'e-commerce', 'Enterprise-grade e-commerce platform with advanced customization and scalability features', 'Large-scale e-commerce, B2B commerce, multi-store management, advanced catalog management, enterprise integrations', 85),
|
|
('BigCommerce', 'e-commerce', 'SaaS e-commerce platform with built-in features for growing online businesses', 'Online store setup, payment processing, SEO optimization, multi-channel selling, inventory management', 80),
|
|
('Squarespace Commerce', 'e-commerce', 'Website builder with integrated e-commerce capabilities for small to medium businesses', 'Website creation with e-commerce, product showcase, payment processing, inventory management, customer management', 75),
|
|
('PrestaShop', 'e-commerce', 'Open-source e-commerce platform with extensive customization options', 'Custom e-commerce solutions, multi-language stores, advanced product management, payment gateway integration', 70),
|
|
|
|
-- CRM Tools
|
|
('HubSpot CRM', 'crm', 'Free CRM platform with sales, marketing, and customer service tools for growing businesses', 'Lead management, contact tracking, sales pipeline management, email marketing, customer support, analytics', 95),
|
|
('Salesforce CRM', 'crm', 'Enterprise-grade CRM platform with extensive customization and integration capabilities', 'Enterprise sales management, customer relationship management, marketing automation, analytics, custom applications', 98),
|
|
('Zoho CRM', 'crm', 'Comprehensive CRM solution with sales, marketing, and customer support features', 'Lead and contact management, sales automation, email marketing, customer support, analytics, mobile access', 85),
|
|
('Pipedrive', 'crm', 'Sales-focused CRM with visual pipeline management and automation features', 'Sales pipeline management, deal tracking, contact management, email integration, sales reporting', 80),
|
|
('Freshworks CRM', 'crm', 'Modern CRM platform with AI-powered insights and automation capabilities', 'Lead management, contact tracking, sales automation, email marketing, customer support, AI insights', 75),
|
|
('Monday.com CRM', 'crm', 'Visual CRM platform with customizable workflows and team collaboration features', 'Sales pipeline management, contact tracking, team collaboration, project management, automation', 70),
|
|
|
|
-- Analytics Tools
|
|
('Google Analytics', 'analytics', 'Web analytics service that tracks and reports website traffic and user behavior', 'Website traffic analysis, user behavior tracking, conversion tracking, audience insights, performance monitoring', 98),
|
|
('Mixpanel', 'analytics', 'Advanced analytics platform focused on user behavior and product analytics', 'User behavior analysis, funnel analysis, cohort analysis, A/B testing, product analytics, retention tracking', 85),
|
|
('Amplitude', 'analytics', 'Product analytics platform for understanding user behavior and driving growth', 'User journey analysis, behavioral analytics, cohort analysis, retention analysis, feature adoption tracking', 80),
|
|
('Hotjar', 'analytics', 'User behavior analytics tool with heatmaps, session recordings, and feedback collection', 'Heatmap analysis, session recordings, user feedback, conversion optimization, user experience analysis', 75),
|
|
('Tableau', 'analytics', 'Business intelligence and data visualization platform for advanced analytics', 'Data visualization, business intelligence, advanced analytics, reporting, data exploration, dashboard creation', 90),
|
|
('Power BI', 'analytics', 'Microsoft business analytics service for data visualization and business intelligence', 'Data visualization, business intelligence, reporting, dashboard creation, data modeling, advanced analytics', 85),
|
|
|
|
-- Payment Processing
|
|
('Stripe', 'payments', 'Online payment processing platform for internet businesses with developer-friendly APIs', 'Online payments, subscription billing, marketplace payments, international payments, fraud prevention, API integration', 95),
|
|
('PayPal', 'payments', 'Global payment platform supporting online payments, money transfers, and business solutions', 'Online payments, money transfers, business payments, international transactions, mobile payments, invoicing', 90),
|
|
('Razorpay', 'payments', 'Payment gateway solution designed for Indian businesses with local payment methods', 'Indian payment processing, UPI payments, card payments, subscription billing, payment links, business banking', 85),
|
|
('Square', 'payments', 'Payment processing platform with point-of-sale and online payment solutions', 'Point-of-sale payments, online payments, invoicing, business management, payment analytics, mobile payments', 80),
|
|
('Adyen', 'payments', 'Global payment platform for enterprise businesses with advanced fraud prevention', 'Enterprise payments, global payment processing, fraud prevention, payment optimization, unified commerce', 75),
|
|
('Braintree', 'payments', 'PayPal-owned payment platform with advanced features for online and mobile payments', 'Online payments, mobile payments, marketplace payments, subscription billing, fraud protection, global payments', 70),
|
|
|
|
-- Communication Tools
|
|
('Slack', 'communication', 'Business communication platform with channels, direct messaging, and app integrations', 'Team communication, project collaboration, file sharing, app integrations, video calls, workflow automation', 95),
|
|
('Microsoft Teams', 'communication', 'Collaboration platform with chat, video meetings, and Microsoft 365 integration', 'Team communication, video conferencing, file collaboration, Microsoft 365 integration, project management', 90),
|
|
('Discord', 'communication', 'Voice, video, and text communication platform popular with gaming and tech communities', 'Community building, voice/video calls, text chat, server management, bot integration, streaming', 85),
|
|
('Zoom', 'communication', 'Video conferencing platform with meeting, webinar, and collaboration features', 'Video meetings, webinars, screen sharing, recording, virtual events, team collaboration', 90),
|
|
('Telegram', 'communication', 'Cloud-based messaging platform with group chats, channels, and bot support', 'Messaging, group chats, channels, file sharing, bot integration, voice/video calls, cloud storage', 80),
|
|
('WhatsApp Business', 'communication', 'Business messaging platform for customer communication and marketing', 'Customer communication, business messaging, marketing campaigns, catalog sharing, payment integration', 75),
|
|
|
|
-- Project Management
|
|
('Trello', 'project-management', 'Visual project management tool using boards, lists, and cards for task organization', 'Task management, project tracking, team collaboration, workflow visualization, deadline management, progress tracking', 85),
|
|
('Jira', 'project-management', 'Agile project management tool designed for software development teams', 'Agile project management, issue tracking, sprint planning, bug tracking, release management, team collaboration', 90),
|
|
('Asana', 'project-management', 'Work management platform for teams to organize, track, and manage their work', 'Task management, project planning, team collaboration, workflow automation, progress tracking, deadline management', 85),
|
|
('Monday.com', 'project-management', 'Work operating system with customizable workflows and visual project management', 'Project management, team collaboration, workflow automation, resource management, time tracking, reporting', 80),
|
|
('Notion', 'project-management', 'All-in-one workspace combining notes, docs, wikis, and project management', 'Note-taking, documentation, project management, team collaboration, knowledge management, task tracking', 85),
|
|
('Basecamp', 'project-management', 'Project management and team communication platform with simple, organized interface', 'Project management, team communication, file sharing, scheduling, progress tracking, client collaboration', 75),
|
|
|
|
-- Marketing Tools
|
|
('Mailchimp', 'marketing', 'Email marketing and automation platform with audience management and analytics', 'Email marketing, marketing automation, audience segmentation, campaign management, analytics, landing pages', 90),
|
|
('Klaviyo', 'marketing', 'E-commerce marketing automation platform with advanced segmentation and personalization', 'E-commerce marketing, email automation, SMS marketing, customer segmentation, personalization, analytics', 85),
|
|
('SEMrush', 'marketing', 'Digital marketing toolkit with SEO, PPC, content, and social media marketing tools', 'SEO analysis, keyword research, competitor analysis, PPC management, content marketing, social media management', 80),
|
|
('HubSpot Marketing', 'marketing', 'Inbound marketing platform with lead generation, email marketing, and analytics', 'Lead generation, email marketing, marketing automation, landing pages, analytics, CRM integration', 85),
|
|
('Hootsuite', 'marketing', 'Social media management platform for scheduling, monitoring, and analytics', 'Social media scheduling, content management, social listening, analytics, team collaboration, brand monitoring', 80),
|
|
('Canva', 'marketing', 'Graphic design platform with templates and tools for creating marketing materials', 'Graphic design, social media graphics, presentations, marketing materials, brand assets, team collaboration', 90),
|
|
|
|
-- Design & Content Creation
|
|
('Figma', 'design', 'Collaborative interface design tool with real-time editing and prototyping features', 'UI/UX design, prototyping, design systems, team collaboration, design handoff, component libraries', 95),
|
|
('Adobe Creative Suite', 'design', 'Comprehensive suite of creative tools for design, photography, and video production', 'Graphic design, photo editing, video production, web design, illustration, animation, print design', 90),
|
|
('Sketch', 'design', 'Digital design toolkit for creating user interfaces and user experiences', 'UI design, prototyping, design systems, vector graphics, collaboration, design handoff', 85),
|
|
('InVision', 'design', 'Digital product design platform with prototyping and collaboration features', 'Prototyping, design collaboration, user testing, design handoff, design systems, workflow management', 80),
|
|
('Adobe XD', 'design', 'User experience design tool with prototyping and collaboration capabilities', 'UX design, prototyping, design systems, collaboration, user testing, design handoff', 85),
|
|
('Framer', 'design', 'Interactive design tool for creating high-fidelity prototypes and animations', 'Interactive prototyping, animation design, responsive design, user testing, design handoff', 75),
|
|
|
|
-- Development & DevOps
|
|
('GitHub', 'development', 'Code hosting platform with version control, collaboration, and project management features', 'Code hosting, version control, collaboration, project management, CI/CD, code review, issue tracking', 95),
|
|
('GitLab', 'development', 'DevOps platform with Git repository management, CI/CD, and project management', 'Version control, CI/CD, project management, code review, issue tracking, DevOps automation', 85),
|
|
('Bitbucket', 'development', 'Git repository management solution with built-in CI/CD and collaboration tools', 'Version control, code collaboration, CI/CD, project management, code review, issue tracking', 80),
|
|
('Jira Software', 'development', 'Agile project management tool specifically designed for software development teams', 'Agile project management, sprint planning, issue tracking, release management, team collaboration', 90),
|
|
('Confluence', 'development', 'Team collaboration and documentation platform for knowledge sharing and project documentation', 'Documentation, knowledge management, team collaboration, project documentation, meeting notes, wikis', 85),
|
|
('Jenkins', 'development', 'Open-source automation server for building, testing, and deploying software', 'CI/CD automation, build automation, testing automation, deployment automation, pipeline management', 80),
|
|
|
|
-- Customer Support
|
|
('Zendesk', 'customer-support', 'Customer service platform with ticketing, knowledge base, and communication tools', 'Customer support, ticket management, knowledge base, live chat, customer communication, analytics', 90),
|
|
('Intercom', 'customer-support', 'Customer messaging platform with support, engagement, and marketing features', 'Customer support, live chat, messaging, customer engagement, marketing automation, analytics', 85),
|
|
('Freshdesk', 'customer-support', 'Cloud-based customer support software with ticketing and communication features', 'Customer support, ticket management, knowledge base, live chat, customer communication, automation', 80),
|
|
('Help Scout', 'customer-support', 'Customer service platform focused on team collaboration and customer satisfaction', 'Customer support, ticket management, team collaboration, customer communication, knowledge base, analytics', 75),
|
|
('LiveChat', 'customer-support', 'Live chat software for customer support and sales with automation features', 'Live chat, customer support, sales chat, chat automation, visitor tracking, analytics', 70),
|
|
('Crisp', 'customer-support', 'Customer messaging platform with live chat, email, and social media integration', 'Live chat, customer support, email integration, social media integration, visitor tracking, analytics', 65),
|
|
|
|
-- Business Intelligence & Reporting
|
|
('Google Data Studio', 'business-intelligence', 'Free data visualization and reporting tool that integrates with Google services', 'Data visualization, reporting, dashboard creation, Google Analytics integration, data exploration', 80),
|
|
('Looker', 'business-intelligence', 'Business intelligence platform with data modeling and visualization capabilities', 'Business intelligence, data modeling, visualization, reporting, analytics, data exploration', 85),
|
|
('Qlik Sense', 'business-intelligence', 'Self-service data visualization and business intelligence platform', 'Data visualization, business intelligence, self-service analytics, reporting, data exploration', 80),
|
|
('Sisense', 'business-intelligence', 'Business intelligence platform with embedded analytics and data visualization', 'Business intelligence, embedded analytics, data visualization, reporting, data modeling', 75),
|
|
('Domo', 'business-intelligence', 'Cloud-based business intelligence platform with real-time data visualization', 'Business intelligence, real-time analytics, data visualization, reporting, dashboard creation', 70),
|
|
('Metabase', 'business-intelligence', 'Open-source business intelligence tool with easy-to-use interface for data exploration', 'Business intelligence, data exploration, reporting, dashboard creation, SQL queries, data visualization', 75),
|
|
|
|
-- Accounting & Finance
|
|
('QuickBooks', 'accounting', 'Accounting software for small and medium businesses with invoicing and expense tracking', 'Accounting, invoicing, expense tracking, financial reporting, tax preparation, payroll management', 90),
|
|
('Xero', 'accounting', 'Cloud-based accounting software for small businesses with bank reconciliation and reporting', 'Accounting, bank reconciliation, invoicing, expense tracking, financial reporting, inventory management', 85),
|
|
('FreshBooks', 'accounting', 'Cloud-based accounting software designed for small businesses and freelancers', 'Accounting, invoicing, expense tracking, time tracking, project management, financial reporting', 80),
|
|
('Wave', 'accounting', 'Free accounting software for small businesses with invoicing and receipt scanning', 'Accounting, invoicing, expense tracking, receipt scanning, financial reporting, tax preparation', 75),
|
|
('Sage', 'accounting', 'Business management software with accounting, payroll, and HR features', 'Accounting, payroll management, HR management, financial reporting, inventory management, business intelligence', 80),
|
|
('Zoho Books', 'accounting', 'Online accounting software with invoicing, expense tracking, and financial reporting', 'Accounting, invoicing, expense tracking, financial reporting, inventory management, project management', 75);
|
|
|
|
-- =====================================================
|
|
-- VERIFICATION QUERIES
|
|
-- =====================================================
|
|
|
|
-- Verify data insertion
|
|
SELECT
|
|
category,
|
|
COUNT(*) as tool_count,
|
|
AVG(popularity_score) as avg_popularity
|
|
FROM tools
|
|
GROUP BY category
|
|
ORDER BY tool_count DESC;
|
|
|
|
-- Example query: Get tools by category
|
|
SELECT name, description, popularity_score
|
|
FROM tools
|
|
WHERE category = 'e-commerce'
|
|
ORDER BY popularity_score DESC;
|
|
|
|
-- Example query: Search for tools by use case
|
|
SELECT name, category, primary_use_cases
|
|
FROM tools
|
|
WHERE primary_use_cases ILIKE '%payment%'
|
|
ORDER BY popularity_score DESC;
|
|
|
|
-- =====================================================
|
|
-- MIGRATION COMPLETED
|
|
-- =====================================================
|
|
|
|
-- Display completion message
|
|
DO $$
|
|
BEGIN
|
|
RAISE NOTICE 'Tools table migration completed successfully!';
|
|
RAISE NOTICE 'Created tools table with % categories and % total tools',
|
|
(SELECT COUNT(DISTINCT category) FROM tools),
|
|
(SELECT COUNT(*) FROM tools);
|
|
RAISE NOTICE 'Ready for domain-based tool recommendations';
|
|
END $$;
|
|
|