Metabase Setup Guide

This guide explains how to connect Metabase (meta.comma.cm) to your production RDS database to analyze visitor data from comma.cm.

Architecture Overview

  • Metabase URL: https://meta.comma.cm
  • RDS Database: PostgreSQL 15.5 in private subnet
  • Data Source: Anonymous visitor data from comma.cm
  • Security: SSL/TLS enforced, IAM role-based access

Infrastructure Components

1. Metabase EC2 Instance

  • Instance Type: t3.medium
  • Location: Private subnet with ALB access
  • Docker: Runs Metabase container with auto-restart
  • Monitoring: CloudWatch metrics and logs

2. RDS Security Configuration

  • Metabase EC2 security group has access to RDS on port 5432
  • SSL required for all database connections
  • Credentials stored in AWS Secrets Manager

3. Database Access

  • Host: Retrieved from AWS Secrets Manager
  • Port: 5432
  • Database: comma_production
  • SSL Mode: require

Deployment Steps

Step 1: Deploy Infrastructure

cd infra/terraform/environments/production

# Plan the changes
terraform plan -target=module.metabase

# Apply the Metabase module
terraform apply -target=module.metabase

# After Metabase is deployed, update RDS security group
terraform apply -target=aws_security_group_rule.rds_from_metabase

Step 2: Initial Metabase Setup

  1. Navigate to https://meta.comma.cm
  2. Complete the initial setup wizard:
    • Create admin account
    • Skip the database setup (we'll configure it manually)

Step 3: Configure Database Connection

  1. Go to Admin Panel → Databases → Add Database

  2. Select PostgreSQL as the database type

  3. Configure connection:

    • Display Name: Comma Production Database
    • Host: (RDS endpoint from Secrets Manager)
    • Port: 5432
    • Database name: comma_production
    • Username: (from Secrets Manager)
    • Password: (from Secrets Manager)
    • SSL: Required - don't connect without SSL

  4. Advanced options:

    • Additional JDBC connection string options: ?ssl=true&sslmode=require
    • Schemas: Filter to only necessary schemas
    • Re-run queries for simple explorations: Yes

For better security, create a read-only user for Metabase:

-- Connect to your RDS instance
CREATE USER metabase_reader WITH PASSWORD 'secure_password';

-- Grant connect privilege
GRANT CONNECT ON DATABASE comma_production TO metabase_reader;

-- Grant usage on schema
GRANT USAGE ON SCHEMA public TO metabase_reader;

-- Grant select on all tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO metabase_reader;

-- Grant select on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO metabase_reader;

Step 5: Verify Connection

  1. In Metabase, click Save on the database configuration
  2. Metabase will test the connection
  3. If successful, it will scan the database schema

Available Data for Analytics

Visitor Data Tables

Based on your Django models, you should have access to:

  1. Visitor Sessions

    • Session IDs
    • Visit timestamps
    • Referrer information
    • User agent data

  2. Page Views

    • Page URLs
    • View counts
    • Time on page
    • Bounce rates

  3. Geographic Data

    • IP-based location
    • Country/region
    • City (if available)

  4. Device Information

    • Browser types
    • Operating systems
    • Device categories (mobile/desktop)

Creating Your First Dashboard

1. Visitor Overview Dashboard

Create visualizations for:

  • Total unique visitors
  • Page views over time
  • Top referrers
  • Geographic distribution
  • Device breakdown

2. Coming Soon Page Analytics

Track engagement with:

  • Form submissions
  • Email signups
  • Time spent on page
  • Conversion funnel

3. Real-time Monitoring

Set up:

  • Current active users
  • Live page views
  • Recent form submissions

Security Best Practices

  1. Access Control

    • Use IAM roles for EC2 instance
    • Implement IP whitelisting for admin access
    • Enable 2FA for Metabase accounts

  2. Database Security

    • Use read-only database user
    • Limit schema access
    • Monitor query performance

  3. Data Privacy

    • Anonymize PII data
    • Implement data retention policies
    • Regular security audits

Monitoring and Maintenance

CloudWatch Dashboards

Monitor these metrics:

  • Metabase CPU/Memory usage
  • Database connection pool
  • Query response times
  • Error rates

Regular Tasks

  1. Weekly

    • Review slow queries
    • Check disk usage
    • Validate backups

  2. Monthly

    • Update Metabase version
    • Review user access
    • Optimize dashboards

  3. Quarterly

    • Security audit
    • Performance tuning
    • Cost optimization

Troubleshooting

Connection Issues

  1. Check security group rules
  2. Verify RDS is accessible from Metabase subnet
  3. Confirm SSL certificate is valid
  4. Check Secrets Manager permissions

Performance Issues

  1. Enable query caching in Metabase
  2. Create database indexes for common queries
  3. Increase EC2 instance size if needed
  4. Optimize dashboard refresh schedules

Data Sync Issues

  1. Check Metabase sync schedule
  2. Verify database permissions
  3. Review CloudWatch logs
  4. Manually trigger schema sync

Next Steps

  1. Set up alerts for important metrics
  2. Create scheduled reports for stakeholders
  3. Implement embedded analytics in your application
  4. Configure backup strategy for Metabase data

Support

For issues or questions:

  • Check Metabase logs in CloudWatch
  • Review RDS performance insights
  • Contact your DevOps team for infrastructure support