setting-up-a-data-warehouse-source

# Setting up a data warehouse source Use this skill when the user wants to connect an external data source to PostHog's data warehouse for the first time. **Default to the one-step flow:** `data-warehouse-source-setup` validates credentials, discovers every table, enables them with sensible sync defaults (incremental where possible), and creates the source in a single call — no `schemas` array to assemble. For credentials, hand the user a secure browser link with `data-warehouse-source-connect-link` instead of collecting secrets in chat. Only drop to the manual `wizard → db-schema → create` flow when the user wants to hand-pick which tables sync or set non-default sync types per table. ## When to use this skill - The user wants to connect a new source: "connect Stripe", "import my Postgres orders table", "sync Hubspot contacts" - The user isn't sure what source types PostHog supports - The user has credentials but doesn't know how to structure the `schemas` payload - The user wants guidance on which sync method to pick per table ## Available tools | Tool | Purpose | | ------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------- | | `data-warehouse-source-connect-link` | **Preferred for credentials** — get a secure browser/OAuth link so the user authenticates without pasting secrets in chat | | `data-warehouse-source-setup` | **Preferred to create** — one call: validate creds, discover tables, apply sync defaults, create the source | | `external-data-sources-wizard` | Discover which source types exist and what fields each needs (advanced flow) | | `external-data-sources-db-schema` | Validate credentials and list tables with available sync methods per table (advanced flow) | | `external-data-sources-create` | Advanced create — requires a `schemas` array built from the db-schema response | | `external-data-sources-check-cdc-prerequisites-create` | Postgres CDC pre-flight check (optional, only for Postgres CDC) | | `external-data-sources-webhook-info-retrieve` | Check if a source supports webhooks and whether one has been registered | | `external-data-sources-create-webhook-create` | Register a webhook with the external service after source creation | | `external-data-sources-update-webhook-inputs-create` | Supply the signing secret manually when auto-registration failed | | `external-data-sources-list` | After creation, confirm the source is listed and see its initial status | | `external-data-schemas-list` | See per-table sync status once the source is created | ## Pre-flight: credential gotchas that cause most failures Surface these **before** collecting credentials — they're the top reasons setup fails on the first try. Validating against them up front avoids burning credential prompts on retries. - **The host must be reachable from PostHog's network.** `localhost`, `127.0.0.1`, and private/RFC-1918 hosts (`10.x`, `192.168.x`, `172.16–31.x`) are rejected — PostHog runs the connection from its own infrastructure, not the user's machine. Serverless/managed Postgres (Neon, Supabase, RDS behind strict rules) often also needs PostHog's egress IPs allowlisted first. If the DB isn't publicly reachable, route to the browser deep-link (`data-warehouse-source-connect-link`) or an SSH tunnel rather than collecting credentials that can't validate. - **Supabase is Postgres — don't collect it twice.** Use the **Session pooler** connection, not the direct host (the direct host is IPv6-only). The pooler host looks like `aws-0-<region>.pooler.supabase.com`, the **username** must be `postgres.<project-ref>`, and the **port is 6543** (not 5432). The password is the **database** password (Settings → Database), which is distinct from the `anon`/`service_role` JWT keys and from the Supabase account password. If `SUPABASE_URL` is in the project env, derive the project ref from `db.<ref>.supabase.co` to pre-fill these instead of asking the user to guess. - **Many SaaS sources need a specific key type or plan** — get the right one before the create call fails: - **Stripe** — a _restricted_ key (`rk_live_…`), not the standard secret key (`sk_live_…`). - **RevenueCat** — a v2 secret key (`sk_…`) with the read scopes enabled. - **Sentry** — an internal-integration token, not a DSN and not a personal auth token. - **Convex** — requires the Professional plan. - **Twilio** — API Key SID + Secret, not the account auth token. - **Mailchimp** — the key carries its datacenter suffix (`key-usX`). - For send-only services (Resend, Mailgun), the key already in the project env is often restricted; the warehouse import needs a full/read-access key. - **Never pass an unresolved secret reference.** If a credential field is still a `{"secretRef": ...}` object, PostHog can't resolve it — the create/db-schema/setup calls reject it with a clear error. Resolve it to the real value first, or collect credentials via `data-warehouse-source-connect-link` and pass the resulting `credential_id`. ## Recommended: one-step setup Most setups should use this path — it avoids the most common failures (skipping db-schema, malformed `schemas`, missing required fields). 1. **Discover the source type and its fields** (optional): `external-data-sources-wizard` lists every source type and the credential fields each needs. Use it to know what to ask the user for; skip it if the source type is obvious. 2. **Collect credentials securely**: call `data-warehouse-source-connect-link({source_type})`. It returns a `connect_url` to a minimal connect page rendering the source's full connection form — the user authorizes via OAuth or enters credentials there, whichever the source offers (the response's `auth_method` tells you which to expect). The page validates the details against a live connection and stashes them encrypted in a temporary store — it does NOT create the source. After the user confirms they're done, find the stored credential id via `data-warehouse-stored-credentials-list` (filter by `source_type`, newest first; the page also shows the id to the user) and pass `{"credential_id": <id>}` to setup. Stored credentials are single-use — deleted as soon as setup consumes them — and expire after 24 hours. Never ask the user to paste raw database passwords, API keys, or OAuth tokens into the chat. 3. **Create in one call**: `data-warehouse-source-setup({source_type, payload, prefix})`. The server validates credentials, discovers all tables, enables them with sync defaults (incremental where a tracking column exists, else append, else full_refresh — never CDC), sets `created_via=mcp`, and creates the source. The `payload` carries a credential reference (`{"credential_id": ...}` or the OAuth integration id key) — or inline credentials for headless automation; no `schemas` array is needed. On success you get the new source `id`; call `external-data-schemas-list` to show the user what was enabled and how each table will sync. Notes specific to this path: - **All discovered tables are enabled.** That's intended (incremental defaults keep ongoing cost low), but flag row counts for very large tables and offer the advanced flow if the user wants to sync only a subset. - **Webhooks are auto-registered** for sources that support them (currently Stripe). Check the `webhook` key in the setup response: on success, webhook-capable tables sync in real time and webhook-only tables (e.g. Stripe Discount) are enabled too; on failure (e.g. the API key can't create webhooks), tables keep the polling defaults and webhook-only tables stay disabled — relay the `webhook.error` to the user and offer Step 6 to register manually. If `webhook.pending_inputs` is non-empty, collect those values and submit via `external-data-sources-update-webhook-inputs-create`. CDC is never chosen automatically; use the advanced flow + CDC steps for near-real-time Postgres. - Inline credentials in `payload` still work for headless/automation, but prefer the connect-link handoff above. ## Advanced: hand-pick tables (three-step flow) Use this when the user wants to choose exactly which tables sync or set non-default sync types. Don't try to shortcut to `external-data-sources-create` — you need the db-schema response to build a valid `schemas` payload. ```text ┌────────────────────┐ │ 1. wizard │ What source types exist? What fields does each need? └────────┬───────────┘ ▼ ┌────────────────────┐ │ 2. db-schema │ Validate creds. List tables + available sync methods per table. └────────┬───────────┘ ▼ ┌────────────────────┐ │ 3. create │ Send source_type + credentials + schemas[] to actually create. └────────────────────┘ ``` ## Workflow ### Step 1 — Discover the source type Call `external-data-sources-wizard` **with `source_type` set to the kind(s) you need** (comma-separated, e.g. `Postgres,Stripe`). The unfiltered response describes every supported source and is hundreds of KB — large enough to blow your context budget. Only omit `source_type` when you genuinely need to enumerate every available type, and when you do, pass `fields: ['*.name', '*.caption']` so the response carries just the names instead of every source's config field definitions. The response is a dict keyed by source type. Each entry describes: - `name` — the canonical source_type string you'll pass to later calls (e.g. `"Postgres"`, `"Stripe"`, `"Hubspot"`). - `caption` — human-readable description. - `fields` — the config fields needed (host, port, database, api_key, client_id/secret, ...). Each has `name`, `type` (input, password, switch, select, file-upload), and `required`. - `featured`, `unreleasedSource` — use to gauge readiness. Skip sources marked `unreleasedSource: true` unless the user explicitly asked for a preview. Match the user's request to a source. If they said "Postgres", look up `Postgres`. If they said something ambiguous like "database", present the top relevant matches (Postgres, MySQL, MongoDB, BigQuery, Snowflake, Redshift) and let them pick. For OAuth-based sources (Hubspot, Salesforce, Google Ads), the wizard entry hints at an OAuth flow. These typically need the user to authorize in the PostHog UI rather than pasting credentials — explain this and direct them to the source setup page rather than trying to collect tokens in chat. OAuth is about _authentication_, not about how data flows; OAuth sources still use polling bulk sync, not webhooks. Gather the required credentials from the user. Never ask for more fields than the wizard entry says are required — asking for an unnecessary `port` when the source doesn't need one confuses users. ### Step 2 — Validate credentials and discover tables Call `external-data-sources-db-schema` with `source_type` plus all credential fields. This does two things at once: 1. Validates the credentials against the live source. Returns 400 with a `message` if anything is wrong (bad host, wrong password, permission denied). Show the error verbatim — it's often actionable ("password authentication failed for user 'x'"). 2. If valid, returns an array of table entries. Each entry: ```text { "table": "orders", "should_sync": false, "rows": 1_250_000, "incremental_available": true, # can do sync_type=incremental "append_available": true, # can do sync_type=append "cdc_available": true, # can do sync_type=cdc (null = not enabled for team) "supports_webhooks": false, # can do sync_type=webhook for real-time push "incremental_fields": [ # candidates: usually updated_at, created_at, id {"field": "updated_at", "type": "datetime", "label": "updated_at", ...}, {"field": "created_at", "type": "datetime", ...}, {"field": "id", "type": "integer", ...} ], "detected_primary_keys": ["id"], "available_columns": [{"field": "id", "type": "integer", "nullable": false}, ...], "description": "..." } ``` Present this to the user. Don't dump the raw JSON — summarize: which tables were found, row counts, and the default sync method recommendation per table (see [sync-type decision guide](./references/sync-types.md)). ### Step 3 — Confirm per-table sync configuration For each table the user wants to sync, pick a sync_type. See the [sync-type decision guide](./references/sync-types.md) for detailed rules, but the short version is: - **Small / dimension tables (<50k rows, no natural ordering column):** `full_refresh` — simple and always correct. - **Large tables with an `updated_at` / `modified_at`:** `incremental` — much cheaper per sync. - **Append-only immutable tables (logs, events):** `append` if available — preserves history. - **Postgres with CDC enabled and you need near-real-time:** `cdc` — requires primary keys and Postgres prerequisites. - **Sources that support webhooks (currently Stripe):** for near-real-time ingestion set `sync_type: "webhook"` on the tables where `supports_webhooks: true`, then register the webhook as a post-create step (see step 6 below). Tables that don't support webhooks on the same source still need a bulk sync_type. For each schema that will use `incremental`/`append`/`cdc`, you also need: - `incremental_field` — which column to track for high-water-mark ordering. Pick from the `incremental_fields` list returned by db-schema. Prefer `updated_at` over `created_at` (updated_at catches late-arriving updates; created_at misses them). For integer-only tables, use the monotonically increasing primary key. - `incremental_field_type` — must match the chosen field's type (`datetime`, `timestamp`, `date`, `integer`, `numeric`, `objectid`). - `primary_key_columns` — required for CDC. Use `detected_primary_keys` from db-schema. ### Step 4 — Pick a good prefix The source's `prefix` is prepended to table names in HogQL. Tables end up as `{prefix}_{table_name}`. - Default to the source type lowercased if there's only one source of that type: `stripe`, `postgres`. - If the user already has a Postgres source, pick something distinguishing: `postgres_prod`, `postgres_analytics`. - Use lowercase, underscore-separated. The prefix becomes part of every HogQL query the user writes. Confirm the prefix with the user before creating — changing it later is possible but renames every table. ### Step 5 — Create the source Call `external-data-sources-create` with: ```json { "source_type": "Postgres", "prefix": "postgres_prod", "payload": { "host": "...", "port": "5432", "dbname": "...", "user": "...", "password": "...", "schema": "public", "schemas": [ { "name": "orders", "should_sync": true, "sync_type": "incremental", "incremental_field": "updated_at", "incremental_field_type": "datetime", "primary_key_columns": ["id"] }, { "name": "users", "should_sync": true, "sync_type": "full_refresh" }, { "name": "audit_log", "should_sync": false } ] } } ``` Rules for the `schemas` array: - Every table returned by db-schema should be included, even ones the user doesn't want (set `should_sync: false`). Tables the user didn't mention default to `should_sync: false`. - `sync_type` is required only when `should_sync: true`. - `incremental_field` / `incremental_field_type` must be present when `sync_type` is `incremental` or `append`. - `primary_key_columns` must be present when `sync_type` is `cdc`. On success you'll get back a source with a new `id`. The first sync is triggered automatically. ### Step 6 — Register a webhook (only when any schema is `sync_type: "webhook"`)