postgresql-bi-agent

# PostgreSQL Business Intelligence Agent This skill guides the agent in conducting comprehensive business intelligence analysis on a PostgreSQL database. It automatically discovers database metadata, samples data to understand business context, generates business-relevant SQL queries, executes them, and produces deep analytical reports for business operations insights. ## Purpose The primary goal of this skill is to transform raw database data into actionable business intelligence. It serves as an automated data analyst that: - **Discovers** database structure and metadata automatically - **Understands** business context through data sampling - **Generates** relevant SQL queries for business metrics - **Executes** queries efficiently and safely - **Analyzes** results to provide strategic insights - **Reports** findings with actionable recommendations ## Business Intelligence Framework The agent uses a multi-dimensional framework covering: ### 1. Business Scale Metrics - Transaction volumes and values - Customer base and distribution - Product/service inventory and categorization ### 2. Growth Trends - Year-over-year and month-over-month comparisons - Daily/Monthly active user trends - Revenue and order trends ### 3. User Behavior Analysis - User engagement and activity levels - Retention rates and cohort analysis - Conversion funnel metrics ### 4. Financial Insights - Revenue trends and patterns - Average order value (AOV) - Payment success rates and refund metrics ### 5. Inventory & Product Analysis - Inventory turnover rates - Product performance rankings - Stock-out and overstock indicators ### 6. Operational Efficiency - Order processing times - Customer satisfaction indicators - System performance metrics ## Workflow When activated, this skill executes a comprehensive analysis pipeline: 1. **Metadata Discovery** → Scan database structure, tables, columns, relationships 2. **Data Sampling** → Analyze data patterns, value distributions, business context 3. **SQL Generation** → Generate domain-specific business intelligence queries 4. **Query Execution** → Run all queries with proper error handling 5. **Deep Analysis** → Correlate results, identify patterns, calculate insights 6. **Report Generation** → Produce comprehensive business report ## Available Skills Each skill represents a callable analysis module that returns structured JSON output. --- ## 1. Metadata Discovery ### Skill: `discover_database_metadata` - **Description**: Scans and collects comprehensive database metadata including schemas, tables, columns, data types, constraints, indexes, and relationships. - **Usage**: `python3 business_intelligence_agent.py --skill discover_database_metadata` - **Expected Output**: ```json { "skill": "discover_database_metadata", "status": "success", "data": { "database_name": "ecommerce_db", "schemas": ["public", "analytics", "audit"], "total_tables": 45, "total_columns": 380, "tables": [ { "schema_name": "public", "table_name": "orders", "row_count": 1500000, "columns": [ {"name": "order_id", "type": "bigint", "is_pk": true}, {"name": "user_id", "type": "bigint", "is_fk": true}, {"name": "order_amount", "type": "decimal(12,2)", "is_nullable": false}, {"name": "created_at", "type": "timestamp", "is_nullable": false} ], "indexes": ["idx_orders_user_id", "idx_orders_created_at"] } ], "relationships": [ {"from": "orders.user_id", "to": "users.user_id", "type": "FK"} ] } } ``` ### Skill: `identify_business_tables` - **Description**: Identifies tables likely related to core business operations based on naming conventions, column patterns, and data characteristics. - **Usage**: `python3 business_intelligence_agent.py --skill identify_business_tables` - **Expected Output**: ```json { "skill": "identify_business_tables", "status": "success", "data": { "core_tables": [ {"name": "orders", "category": "transactions", "confidence": 0.95}, {"name": "users", "category": "customers", "confidence": 0.92}, {"name": "products", "category": "inventory", "confidence": 0.88} ], "transaction_tables": ["orders", "payments", "refunds"], "customer_tables": ["users", "customers", "accounts"], "product_tables": ["products", "inventory", "categories"] } } ``` ### Skill: `analyze_table_relationships` - **Description**: Maps foreign key relationships and infers implicit relationships to build a complete database relationship graph. - **Usage**: `python3 business_intelligence_agent.py --skill analyze_table_relationships` - **Expected Output**: ```json { "skill": "analyze_table_relationships", "status": "success", "data": { "explicit_relationships": 25, "inferred_relationships": 12, "relationship_graph": { "nodes": ["orders", "users", "products", "categories", "payments"], "edges": [ {"from": "orders", "to": "users", "type": "M:1"}, {"from": "orders", "to": "payments", "type": "1:1"}, {"from": "order_items", "to": "orders", "type": "M:1"}, {"from": "order_items", "to": "products", "type": "M:1"} ] } } } ``` --- ## 2. Data Sampling & Pattern Analysis ### Skill: `sample_table_data` - **Description**: Samples data from identified business tables to understand data distribution, value ranges, and business context. - **Usage**: `python3 business_intelligence_agent.py --skill sample_table_data --tables orders,users,products` - **Expected Output**: ```json { "skill": "sample_table_data", "status": "success", "data": { "sampled_tables": { "orders": { "sample_size": 100, "date_range": {"min": "2024-01-01", "max": "2024-01-15"}, "value_ranges": {"order_amount": {"min": 10.00, "max": 15000.00, "avg": 250.50}}, "categorical_distributions": {"payment_method": {"credit_card": 45, "debit_card": 30, "digital_wallet": 25}} }, "users": { "sample_size": 100, "date_joined_range": {"min": "2023-06-01", "max": "2024-01-15"}, "user_distribution": {"new_users_last_30_days": 15000} } } } } ``` ### Skill: `detect_data_patterns` - **Description**: Analyzes temporal patterns, value distributions, and data quality indicators across business tables. - **Usage**: `python3 business_intelligence_agent.py --skill detect_data_patterns` - **Expected Output**: ```json { "skill": "detect_data_patterns", "status": "success", "data": { "temporal_patterns": { "orders": {"peak_hours": "12:00-14:00, 19:00-21:00", "peak_days": "Friday, Saturday"}, "users": {"registration_spike": "Weekends", "activity_peak": "Evening 19:00-23:00"} }, "value_distributions": { "order_amount": {"skewness": 2.5, "outliers": 150, "percentile_95": 1200.00} }, "data_quality": { "null_rates": {"orders.shipping_address": 0.15, "orders.coupon_code": 0.85}, "duplicate_rates": {"orders.order_id": 0.0, "users.email": 0.001} } } } ``` ### Skill: `infer_business_context` - **Description**: Uses metadata and data patterns to infer the business domain, model type (B2B/B2C/SaaS), and key business entities. - **Usage**: `python3 business_intelligence_agent.py --skill infer_business_context` - **Expected Output**: ```json { "skill": "infer_business_context", "status": "success", "data": { "business_domain": "E-commerce Retail", "business_model": "B2C", "primary_entities": ["Orders", "Products", "Customers"], "secondary_entities": ["Payments", "Shipping", "Returns"], "key_metrics": ["GMV", "AOV", "Conversion Rate", "Customer LTV"], "time_granularities": ["daily", "weekly", "monthly", "quarterly"] } } ``` --- ## 3. Business Intelligence SQL Generation ### Skill: `generate_revenue_queries` - **Description**: Generates SQL queries for revenue analysis including revenue trends, breakdown by category/time/payment method, and financial KPIs. - **Usage**: `python3 business_intelligence_agent.py --skill generate_revenue_queries` - **Expected Output**: ```json { "skill": "generate_revenue_queries", "status": "success", "queries": [ { "name": "daily_revenue_trend", "sql": "SELECT DATE(created_at) as date, SUM(order_amount) as revenue, COUNT(*) as orders FROM orders WHERE created_at >= CURRENT_DATE - INTERVAL '30 days' GROUP BY DATE(created_at) ORDER BY date", "metrics": ["daily_revenue", "order_count", "avg_order_value"] }, { "name": "revenue_by_category", "sql": "SELECT c.category_name, SUM(oi.quantity * oi.unit_price) as revenue, COUNT(DISTINCT o.order_id) as orders FROM order_items oi JOIN products p ON oi.product_id = p.id JOIN categories c ON p.category_id = c.id JOIN orders o ON oi.order_id = o.id WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days' GROUP BY c.category_name ORDER BY revenue DESC", "metrics": ["category_revenue", "category_orders", "category_aov"] }, { "name": "monthly_revenue_comparison", "sql": "SELECT DATE_TRUNC('month', created_at) as month, SUM(order_amount) as revenue FROM orders WHERE created_at >= CURRENT_DATE - INTERVAL '12 months' GROUP BY DATE_TRUNC('month', created_at) ORDER BY month", "metrics": ["monthly_revenue", "mom_growth", "yoy_growth"] } ] } ``` ### Skill: `generate_customer_analytics_queries` - **Description**: Generates SQL for customer analytics including acquisition, retention, segmentation, LTV, and behavioral analysis. - **Usage**: `python3 business_intelligence_agent.py --skill generate_customer_analytics_queries` - **Expected Output**: ```json { "skill": "generate_customer_analytics_queries", "status": "success", "queries": [ { "name": "customer_acquisition_trend", "sql": "SELECT DATE_TRUNC('week', created_at) as week, COUNT(*) as new_customers FROM users WHERE created_at >= CURRENT_DATE - INTERVAL '12 weeks' GROUP BY DATE_TRUNC('week', created_at) ORDER BY week", "metrics": ["weekly_new_users", "user_growth_rate"] }, { "name": "customer_retention_cohort",