From Gut Feelings to Data: PostHog + Metabase with AI in One Weekend

Decisions get made on gut feeling when nobody tracks user behavior systematically. PostHog (product analytics, open-source) and Metabase (BI dashboards, open-source) close that gap — both deploy self-hosted in a single weekend. AI speeds up the most tedious part: writing dashboards and SQL queries.

This guide covers Docker configuration, PostHog + Metabase integration, prompts for LLM-generated dashboards, and production patterns.

Why Two Tools Instead of One

PostHog and Metabase solve different problems. Pick just one and you’re looking at half the picture.

PostHog — product analytics. Event tracking, funnels, retention, feature flags, session replay. Answers the question: “what are users doing in the product?”

Metabase — business intelligence. SQL queries against any database, visualizations, dashboards. Answers the question: “how do business metrics relate to product data?”

┌───────────────────────────────────────────────────┐
│                 Data Stack                         │
├─────────────────────┬─────────────────────────────┤
│     PostHog         │        Metabase              │
├─────────────────────┼─────────────────────────────┤
│ Event tracking      │ SQL on any DB                │
│ Funnels             │ Cross-database joins         │
│ Retention           │ Scheduled reports            │
│ Feature flags       │ Embeddable dashboards        │
│ Session replay      │ Non-technical access         │
└─────────────────────┴─────────────────────────────┘

PostHog stores data in ClickHouse. Metabase connects to ClickHouse directly and runs arbitrary SQL against PostHog’s event data. That gives you a full stack: data collection, product analytics, and business analytics in one place.

Docker Setup: PostHog

Minimum requirements: 4 vCPU, 16 GB RAM, 30+ GB storage, Docker, docker compose. PostHog uses ClickHouse, Kafka, PostgreSQL, and Redis.

# docker-compose.posthog.yml
version: '3.8'

services:
  posthog:
    image: posthog/posthog:latest
    ports:
      - "8000:8000"
    environment:
      DATABASE_URL: postgres://posthog:posthog@postgres:5432/posthog
      REDIS_URL: redis://redis:6379/
      CLICKHOUSE_HOST: clickhouse
      CLICKHOUSE_DATABASE: posthog
      CLICKHOUSE_SECURE: "false"
      SECRET_KEY: your-secret-key-change-me
      SITE_URL: http://localhost:8000
    depends_on:
      - postgres
      - redis
      - clickhouse

  postgres:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: posthog
      POSTGRES_PASSWORD: posthog
      POSTGRES_DB: posthog
    volumes:
      - posthog_postgres:/var/lib/postgresql/data

  redis:
    image: redis:7-alpine
    volumes:
      - posthog_redis:/data

  clickhouse:
    image: clickhouse/clickhouse-server:24.3
    volumes:
      - posthog_clickhouse:/var/lib/clickhouse
    ulimits:
      nofile:
        soft: 262144
        hard: 262144

volumes:
  posthog_postgres:
  posthog_redis:
  posthog_clickhouse:

For production, use the official Helm chart or the docker compose from the PostHog repository. The configuration above shows the key components. The actual self-hosted setup:

git clone https://github.com/PostHog/posthog.git
cd posthog
docker compose -f docker-compose.hobby.yml up -d

PostHog ships docker-compose.hobby.yml — a file for self-hosted deployments with proper ClickHouse, Kafka, and dependency settings. After 2–3 minutes the UI is up at localhost:8000. Create an account, organization, and project.

Configuring Event Tracking

PostHog provides a JavaScript snippet for web apps:

<script>
  !function(t,e){var o,n,p,r;e.__SV||(window.posthog=e,e._i=[],
  e.init=function(i,s,a){function g(t,e){var o=e.split(".");
  2==o.length&&(t=t[o[0]],e=o[1]),t[e]=function(){t.push([e]
  .concat(Array.prototype.slice.call(arguments,0)))}}
  (p=t.createElement("script")).type="text/javascript",
  p.async=!0,p.src=s.api_host+"/static/array.js",
  (r=t.getElementsByTagName("script")[0]).parentNode
  .insertBefore(p,r);var u=e;for(void 0!==a?u=e[a]=[]:
  a="posthog",u.people=u.people||[],u.toString=function(t)
  {var e="posthog";return"posthog"!==a&&(e+="."+a),t||(e+=" (stub)"),e},
  u.people.toString=function(){return u.toString(1)+".people (stub)"},
  o="capture identify alias people.set people.set_once set_config"
  .split(" "),n=0;n<o.length;n++)g(u,o[n]);e._i.push([i,s,a])},
  e.__SV=1)}(document,window.posthog||[]);
  posthog.init('YOUR_PROJECT_API_KEY', {
    api_host: 'http://localhost:8000'
  });
</script>

For server-side apps — Python SDK:

pip install posthog

from posthog import Posthog

posthog = Posthog(
    project_api_key='YOUR_PROJECT_API_KEY',
    host='http://localhost:8000'
)

# Track an event
posthog.capture(
    distinct_id='user-123',
    event='purchase_completed',
    properties={
        'amount': 49.99,
        'currency': 'USD',
        'plan': 'pro',
        'source': 'pricing_page'
    }
)

PostHog’s autocapture collects clicks, pageviews, and form submissions without any extra code. Custom events give precision. The rule: autocapture to start, custom events for key conversions.

Docker Setup: Metabase

Metabase is easier to deploy. One container plus a metadata database:

# docker-compose.metabase.yml
version: '3.8'

services:
  metabase:
    image: metabase/metabase:latest
    ports:
      - "3000:3000"
    environment:
      MB_DB_TYPE: postgres
      MB_DB_DBNAME: metabase
      MB_DB_PORT: 5432
      MB_DB_USER: metabase
      MB_DB_PASS: metabase
      MB_DB_HOST: metabase-db
    depends_on:
      - metabase-db

  metabase-db:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: metabase
      POSTGRES_PASSWORD: metabase
      POSTGRES_DB: metabase
    volumes:
      - metabase_data:/var/lib/postgresql/data

volumes:
  metabase_data:

docker compose -f docker-compose.metabase.yml up -d

After a minute, Metabase is up at localhost:3000. Walk through the initial setup: language, admin account, database connection.

Combined Docker Compose

In production, combining both tools into one stack makes sense. The key piece: a shared Docker network so Metabase can reach PostHog’s ClickHouse.

# docker-compose.analytics.yml
version: '3.8'

services:
  # === PostHog stack ===
  posthog:
    image: posthog/posthog:latest
    ports:
      - "8000:8000"
    environment:
      DATABASE_URL: postgres://posthog:posthog@posthog-db:5432/posthog
      REDIS_URL: redis://redis:6379/
      CLICKHOUSE_HOST: clickhouse
      SECRET_KEY: ${POSTHOG_SECRET_KEY}
      SITE_URL: ${POSTHOG_SITE_URL:-http://localhost:8000}
    networks:
      - analytics

  posthog-db:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: posthog
      POSTGRES_PASSWORD: posthog
      POSTGRES_DB: posthog
    volumes:
      - posthog_pg:/var/lib/postgresql/data
    networks:
      - analytics

  redis:
    image: redis:7-alpine
    networks:
      - analytics

  clickhouse:
    image: clickhouse/clickhouse-server:24.3
    volumes:
      - clickhouse_data:/var/lib/clickhouse
    ports:
      - "8123:8123"  # HTTP interface for Metabase
    networks:
      - analytics

  # === Metabase ===
  metabase:
    image: metabase/metabase:latest
    ports:
      - "3000:3000"
    environment:
      MB_DB_TYPE: postgres
      MB_DB_DBNAME: metabase
      MB_DB_PORT: 5432
      MB_DB_USER: metabase
      MB_DB_PASS: metabase
      MB_DB_HOST: metabase-db
    depends_on:
      - metabase-db
      - clickhouse
    networks:
      - analytics

  metabase-db:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: metabase
      POSTGRES_PASSWORD: metabase
      POSTGRES_DB: metabase
    volumes:
      - metabase_pg:/var/lib/postgresql/data
    networks:
      - analytics

networks:
  analytics:
    driver: bridge

volumes:
  posthog_pg:
  clickhouse_data:
  metabase_pg:

docker compose -f docker-compose.analytics.yml up -d

PostHog runs at localhost:8000, Metabase at localhost:3000, and ClickHouse is reachable inside the network by the service name clickhouse.

Connecting Metabase to PostHog’s ClickHouse

Starting with Metabase 54 (April 2025), ClickHouse support is bundled in the official Metabase image — no separate driver installation needed. If you’re running an older version, download the jar from the archived ClickHouse/metabase-clickhouse-driver repository and mount it via a plugins volume. For any current deployment, just proceed to connection configuration.

Connection Configuration

In Metabase UI: Admin → Database → Add database:

Parameter	Value
Database type	ClickHouse
Host	clickhouse (Docker service name)
Port	8123
Database name	posthog
Username	default
Password	(empty if not set)

After connecting, Metabase scans the tables. Key PostHog tables in ClickHouse:

• events — all events (central table)
• persons — users
• person_distinct_id2 — distinct_id → person_id mapping
• session_replay_events — session replay data

AI Prompts for Dashboard Generation

The most time-consuming part of analytics setup is writing SQL queries and wiring up visualizations. AI cuts that time by 5–10x. Below are prompts tested on real projects.

Prompt 1: Product Overview Dashboard

You are a senior data analyst. I've connected Metabase to PostHog's ClickHouse.

The events table has the following columns:
- uuid (String)
- event (String) — event name
- properties (String) — JSON with arbitrary properties
- timestamp (DateTime64)
- distinct_id (String) — user identifier
- created_at (DateTime64)

Create SQL queries for the following dashboard cards:

1. DAU/WAU/MAU over the last 30 days (line chart)
2. Top 10 events over the last 7 days (bar chart)
3. Retention Day 1 / Day 7 / Day 30 (table)
4. Conversion funnel: signup → activation → purchase (funnel)
5. Average number of events per user per day (line chart)

Format: clean ClickHouse SQL. Each query with a comment.
Use JSONExtractString/JSONExtractFloat for working with properties.

Prompt 2: Revenue Dashboard

Context: ClickHouse PostHog, events table.
Custom events: purchase_completed (properties: amount, currency, plan),
subscription_started, subscription_cancelled, trial_started.

Create SQL queries for a revenue dashboard:
1. MRR over the last 12 months
2. ARPU (average revenue per user) by month
3. Monthly churn rate
4. Revenue by plan (breakdown by plan from properties)
5. LTV cohorts (by month of first purchase)

ClickHouse syntax. Optimized queries.

Prompt 3: Event Taxonomy Generation

I'm running a SaaS product: [product description].
Core features: [feature list].

Create an event taxonomy for PostHog:
1. Event names (snake_case, verb_noun format)
2. Required properties for each event
3. Grouping by category (onboarding, core_action, monetization, engagement)
4. Recommendations for super properties (set once per user)

Format: table with columns Event | Category | Properties | Description.

Example Generated Query: DAU/WAU/MAU

The first pass AI usually produces looks like this — and it doesn’t work:

-- DOESN'T WORK: distinct_id is no longer available after GROUP BY day,
-- and a window function over an already-computed dau can't correctly
-- merge the overlapping sets of users across neighboring days
SELECT
    toDate(timestamp) AS day,
    uniqExact(distinct_id) AS dau,
    uniqExact(distinct_id) OVER (
        ORDER BY toDate(timestamp)
        RANGE BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS wau
FROM events
GROUP BY day

The problem is inherent to what WAU/MAU actually mean: they’re not a sum of daily DAU figures (a user active three days in a row shouldn’t be counted three times) — they’re a true uniqExact over the raw underlying events across the whole window. A window function layered on top of an already-collapsed dau column can’t recover that; you need per-user aggregation preserved as a mergeable state. The working version uses the -State/-Merge combinators: compute a uniqExact state per day once, then merge neighboring days’ states with a window function:

-- DAU / WAU / MAU for the last 30 days
-- Pull 59 days of raw data (30 displayed + a 29-day lookback for MAU)
-- so the window is complete even for the earliest displayed day.
WITH daily AS (
    SELECT
        toDate(timestamp) AS day,
        uniqExactState(distinct_id) AS dau_state
    FROM events
    WHERE timestamp >= now() - INTERVAL 59 DAY
      AND event != '$feature_flag_called'
    GROUP BY day
)
SELECT
    day,
    uniqExactMerge(dau_state) AS dau,
    uniqExactMerge(dau_state) OVER (
        ORDER BY day
        RANGE BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS wau,
    uniqExactMerge(dau_state) OVER (
        ORDER BY day
        RANGE BETWEEN 29 PRECEDING AND CURRENT ROW
    ) AS mau
FROM daily
WHERE day >= today() - 30
ORDER BY day

uniqExactState stores each day’s full set of distinct_ids as a mergeable object instead of a plain count. uniqExactMerge inside the window function unions those per-day sets across the RANGE BETWEEN ... PRECEDING frame, giving a correctly deduplicated count instead of a double-counted sum. AI-generated queries need validation. ClickHouse window functions have quirks, and this is exactly what a typical first-pass mistake looks like — test every query on a small dataset before adding it to a dashboard.

Metabase API: Automating Dashboard Creation

Creating cards manually in the UI is fine for 5–10 visualizations. Past 20 cards, the Metabase API saves hours.

Creating a Card via API

# Get the database_id
curl -s http://localhost:3000/api/database \
  -H "X-Metabase-Session: $SESSION_TOKEN" | jq '.[].id'

# Create a card (question)
curl -X POST http://localhost:3000/api/card \
  -H "Content-Type: application/json" \
  -H "X-Metabase-Session: $SESSION_TOKEN" \
  -d '{
    "name": "DAU / WAU / MAU",
    "dataset_query": {
      "type": "native",
      "native": {
        "query": "SELECT toDate(timestamp) AS day, uniqExact(distinct_id) AS dau FROM events WHERE timestamp >= now() - INTERVAL 30 DAY GROUP BY day ORDER BY day"
      },
      "database": 2
    },
    "display": "line",
    "visualization_settings": {
      "graph.x_axis.title_text": "Date",
      "graph.y_axis.title_text": "Users"
    }
  }'

Prompt for AI: Batch Creation via API

I have the Metabase API (localhost:3000) with ClickHouse database_id=2.
Session token: $SESSION_TOKEN.

Generate a bash script that uses the Metabase REST API to:
1. Create a dashboard "Product Overview"
2. Create 6 cards with SQL queries against PostHog ClickHouse
3. Add all cards to the dashboard with a layout (2 columns)

Cards:
- DAU (line chart)
- Top Events (bar chart)
- Signup Funnel (funnel)
- Retention Table (table)
- Events per User (line chart)
- Active Users by Country (map)

Use curl + jq. Each step with response validation.

AI generates a ready-to-run script. A fully configured dashboard in 10 minutes instead of 2 hours of clicking through the UI.

PostHog Feature Flags + Metabase: Closing the Loop

PostHog’s feature flags let you roll out features to a percentage of users. Metabase shows the impact on metrics. Together, they give you a data-driven rollout loop.

Setting Up a Feature Flag

In PostHog UI: Feature Flags → New Feature Flag:

Key: new-pricing-page
Rollout: 50% of users
Filters: country = US (optional)

PostHog automatically tracks the $feature_flag_called event with the properties $feature_flag and $feature_flag_response.

SQL Query in Metabase: A/B Comparison

-- Compare conversion between feature flag variants
WITH flag_users AS (
    SELECT
        distinct_id,
        JSONExtractString(properties, '$feature_flag_response') AS variant
    FROM events
    WHERE event = '$feature_flag_called'
      AND JSONExtractString(properties, '$feature_flag') = 'new-pricing-page'
      AND timestamp >= now() - INTERVAL 7 DAY
    GROUP BY distinct_id, variant
),
purchases AS (
    SELECT distinct_id
    FROM events
    WHERE event = 'purchase_completed'
      AND timestamp >= now() - INTERVAL 7 DAY
    GROUP BY distinct_id
)
SELECT
    f.variant,
    count(DISTINCT f.distinct_id) AS users,
    count(DISTINCT p.distinct_id) AS purchasers,
    round(count(DISTINCT p.distinct_id) / count(DISTINCT f.distinct_id) * 100, 2) AS conversion_rate
FROM flag_users f
LEFT JOIN purchases p ON f.distinct_id = p.distinct_id
GROUP BY f.variant
ORDER BY f.variant

This query shows purchase conversion for each feature flag variant. Add it to a dashboard and the whole team sees up-to-date A/B test data.

Production Configuration: What to Do Before Going Live

Security

Secrets via environment variables. No hardcoded passwords in docker compose:

# .env
POSTHOG_SECRET_KEY=generated-64-char-secret
CLICKHOUSE_PASSWORD=strong-password
METABASE_DB_PASS=another-strong-password

Reverse proxy with HTTPS. Put Caddy or nginx in front of PostHog and Metabase:

# Caddyfile
analytics.yourdomain.com {
    reverse_proxy posthog:8000
}

bi.yourdomain.com {
    reverse_proxy metabase:3000
}

Restrict ClickHouse access. Port 8123 should never be exposed externally. Keep it on the internal Docker network only:

clickhouse:
  image: clickhouse/clickhouse-server:24.3
  # DO NOT expose the port in production
  # ports:
  #   - "8123:8123"
  networks:
    - analytics

Backups

Three components need backups:

1. PostgreSQL (PostHog) — metadata, users, settings
2. ClickHouse — event data (largest volume)
3. PostgreSQL (Metabase) — dashboards, questions, settings

#!/bin/bash
# backup.sh — run via cron daily
DATE=$(date +%Y%m%d)
BACKUP_DIR=/backups/analytics

# PostHog PostgreSQL
docker exec posthog-db pg_dump -U posthog posthog | gzip > $BACKUP_DIR/posthog_pg_$DATE.sql.gz

# Metabase PostgreSQL
docker exec metabase-db pg_dump -U metabase metabase | gzip > $BACKUP_DIR/metabase_pg_$DATE.sql.gz

# ClickHouse — built-in backup mechanism
docker exec clickhouse clickhouse-backup create "backup_$DATE"

Monitoring

PostHog and Metabase both expose health endpoints:

# PostHog
curl -f http://localhost:8000/_health || echo "PostHog down"

# Metabase
curl -f http://localhost:3000/api/health || echo "Metabase down"

# ClickHouse
curl -f http://localhost:8123/ping || echo "ClickHouse down"

Wire these into any monitoring system — UptimeRobot, Grafana, or a simple cron with a Telegram alert.

Common Issues and Solutions

ClickHouse OOM on large queries. Cap memory per query:

SET max_memory_usage = 2000000000;  -- 2 GB per query

In ClickHouse user settings for Metabase:

<profiles>
  <metabase>
    <max_memory_usage>2000000000</max_memory_usage>
    <max_execution_time>60</max_execution_time>
  </metabase>
</profiles>

Metabase timeout on heavy queries. Raise the timeout via environment variable:

metabase:
  environment:
    MB_DB_CONNECTION_TIMEOUT_MS: 60000
    MB_DB_QUERY_TIMEOUT_MINUTES: 30

PostHog events not appearing. Check the API key, make sure api_host points to the right address, and confirm the event isn’t filtered. PostHog UI → Activity → Live Events shows the real-time event stream.

Metabase can’t see new ClickHouse tables. Go to Admin → Databases → Sync database schema. Automatic sync runs every hour.

Final Architecture

┌─────────────────────────────────────────────────────────┐
│                      Users / App                         │
│                          │                               │
│                    posthog-js SDK                         │
│                          │                               │
│                    ┌─────▼─────┐                         │
│                    │  PostHog  │ :8000                    │
│                    │   (App)   │                          │
│                    └─────┬─────┘                         │
│                          │                               │
│              ┌───────────▼───────────┐                   │
│              │     ClickHouse        │                   │
│              │  (events storage)     │                   │
│              └───────────┬───────────┘                   │
│                          │                               │
│                    ┌─────▼─────┐                         │
│                    │ Metabase  │ :3000                    │
│                    │   (BI)    │                          │
│                    └───────────┘                         │
└─────────────────────────────────────────────────────────┘

Data flows in one direction: app → PostHog → ClickHouse → Metabase. PostHog owns product analytics — funnels, retention, feature flags. Metabase builds arbitrary SQL reports on top of the same data.

What’s Next

Three directions to grow the stack after the basic setup:

Alerts. Metabase sends email and Slack notifications when thresholds are crossed. Set up an alert when DAU drops below N or errors spike.

Embeddable dashboards. Metabase lets you embed dashboards into your app via iframe with JWT auth. Customers see their own analytics without leaving your product.

dbt + data modeling. As data volume grows, raw SQL against events gets slow. dbt creates intermediate models (materialized views) in ClickHouse. Metabase works against those models instead of raw tables.

For more on monitoring LLM components in an analytics stack: LLM Observability with Langfuse.

Self-hosted analytics takes an upfront investment to set up. In return, your data stays with you, there are no volume limits, and costs don’t scale with event count. PostHog + Metabase covers most of a startup’s analytics needs from MVP to Series A.

---

Need help setting up self-hosted analytics for your startup? I help startups build AI products and automate processes — belov.works.

FAQ

How do you handle schema migrations when PostHog’s ClickHouse table structure changes after an upgrade?

PostHog runs its own schema migration scripts during upgrades, but Metabase doesn’t automatically pick up changed column types or renamed fields. After each PostHog upgrade, run a manual schema sync in Metabase (Admin → Databases → Sync database schema) and review saved questions that touch the events or persons tables. The safest approach is to create Metabase questions against ClickHouse views rather than raw tables — this adds a layer of abstraction so that a column rename in the underlying table only requires updating the view, not every individual query.

What’s the practical limit of ClickHouse query performance for a startup without a dedicated data engineer?

For event volumes under 500 million rows, well-written queries against the events table with proper WHERE timestamp >= filters will return results in seconds without tuning. Past that threshold, the main bottleneck becomes the lack of materialized views for common aggregations. The Metabase query timeout settings (MB_DB_QUERY_TIMEOUT_MINUTES) buy time, but the real fix at scale is creating ClickHouse materialized views for retention, DAU/WAU/MAU, and funnel calculations — an AI model can generate those view definitions from the SQL queries already in your dashboards.

Can Metabase connect to both PostHog’s ClickHouse and a separate PostgreSQL database simultaneously?

Yes. Metabase supports multiple database connections; each one appears as a separate data source in the question builder. This is particularly useful when PostHog tracks behavioral events while transactional data (orders, subscriptions, support tickets) lives in a separate Postgres. Cross-database joins aren’t possible within a single Metabase query, but you can create a dashboard that combines cards from both sources side by side, or use ClickHouse’s built-in PostgreSQL engine to query Postgres tables directly from within ClickHouse SQL.