Setting Up A Data Warehouse Source

1---
2name: setting-up-a-data-warehouse-source
3description: >
4  Guide the user through connecting a new data warehouse source — Postgres, MySQL, Stripe, Hubspot, MongoDB,
5  Salesforce, BigQuery, Snowflake, and so on. Use when the user wants to "connect Stripe", "import data from
6  Postgres", "add a new data source", "sync my warehouse tables", or wants to pick sync methods for each table. Walks
7  through source-type discovery, credential validation, table discovery, per-table sync_type selection, and the
8  final create call. Also covers picking a good prefix and what to do right after creation.
9---
10 
11# Setting up a data warehouse source
12 
13Use this skill when the user wants to connect an external data source to PostHog's data warehouse for the first time.
14 
15**Default to the one-step flow:** `data-warehouse-source-setup` validates credentials, discovers every table, enables
16them with sensible sync defaults (incremental where possible), and creates the source in a single call — no
17`schemas` array to assemble. For credentials, hand the user a secure browser link with `data-warehouse-source-connect-link`
18instead of collecting secrets in chat. Only drop to the manual `wizard → db-schema → create` flow when the user wants to
19hand-pick which tables sync or set non-default sync types per table.
20 
21## When to use this skill
22 
23- The user wants to connect a new source: "connect Stripe", "import my Postgres orders table", "sync Hubspot contacts"
24- The user isn't sure what source types PostHog supports
25- The user has credentials but doesn't know how to structure the `schemas` payload
26- The user wants guidance on which sync method to pick per table
27 
28## Available tools
29 
30| Tool                                                   | Purpose                                                                                                                   |
31| ------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------- |
32| `data-warehouse-source-connect-link`                   | **Preferred for credentials** — get a secure browser/OAuth link so the user authenticates without pasting secrets in chat |
33| `data-warehouse-source-setup`                          | **Preferred to create** — one call: validate creds, discover tables, apply sync defaults, create the source               |
34| `external-data-sources-wizard`                         | Discover which source types exist and what fields each needs (advanced flow)                                              |
35| `external-data-sources-db-schema`                      | Validate credentials and list tables with available sync methods per table (advanced flow)                                |
36| `external-data-sources-create`                         | Advanced create — requires a `schemas` array built from the db-schema response                                            |
37| `external-data-sources-check-cdc-prerequisites-create` | Postgres CDC pre-flight check (optional, only for Postgres CDC)                                                           |
38| `external-data-sources-webhook-info-retrieve`          | Check if a source supports webhooks and whether one has been registered                                                   |
39| `external-data-sources-create-webhook-create`          | Register a webhook with the external service after source creation                                                        |
40| `external-data-sources-update-webhook-inputs-create`   | Supply the signing secret manually when auto-registration failed                                                          |
41| `external-data-sources-list`                           | After creation, confirm the source is listed and see its initial status                                                   |
42| `external-data-schemas-list`                           | See per-table sync status once the source is created                                                                      |
43 
44## Recommended: one-step setup
45 
46Most setups should use this path — it avoids the most common failures (skipping db-schema, malformed `schemas`,
47missing required fields).
48 
491. **Discover the source type and its fields** (optional): `external-data-sources-wizard` lists every source type and
50   the credential fields each needs. Use it to know what to ask the user for; skip it if the source type is obvious.
512. **Collect credentials securely**: call `data-warehouse-source-connect-link({source_type})`. It returns a
52   `connect_url` to a minimal connect page rendering the source's full connection form — the user authorizes via
53   OAuth or enters credentials there, whichever the source offers (the response's `auth_method` tells you which to
54   expect). The page validates the details against a live connection and stashes them encrypted in a temporary
55   store — it does NOT create the source. After the user confirms they're done, find the stored credential id via
56   `data-warehouse-stored-credentials-list` (filter by `source_type`, newest first; the page also shows the id to
57   the user) and pass `{"credential_id": <id>}` to setup. Stored credentials are single-use — deleted as soon as
58   setup consumes them — and expire after 24 hours.
59 
60   Never ask the user to paste raw database passwords, API keys, or OAuth tokens into the chat.
61 
623. **Create in one call**: `data-warehouse-source-setup({source_type, payload, prefix})`. The server validates
63   credentials, discovers all tables, enables them with sync defaults (incremental where a tracking column exists,
64   else append, else full_refresh — never CDC), sets `created_via=mcp`, and creates the source. The `payload` carries
65   a credential reference (`{"credential_id": ...}` or the OAuth integration id key) — or inline credentials for
66   headless automation; no `schemas` array is needed. On success you get the new source `id`; call
67   `external-data-schemas-list` to show the user what was enabled and how each table will sync.
68 
69Notes specific to this path:
70 
71- **All discovered tables are enabled.** That's intended (incremental defaults keep ongoing cost low), but flag row
72  counts for very large tables and offer the advanced flow if the user wants to sync only a subset.
73- **Webhooks are auto-registered** for sources that support them (currently Stripe). Check the `webhook` key in the
74  setup response: on success, webhook-capable tables sync in real time and webhook-only tables (e.g. Stripe Discount)
75  are enabled too; on failure (e.g. the API key can't create webhooks), tables keep the polling defaults and
76  webhook-only tables stay disabled — relay the `webhook.error` to the user and offer Step 6 to register manually.
77  If `webhook.pending_inputs` is non-empty, collect those values and submit via
78  `external-data-sources-update-webhook-inputs-create`. CDC is never chosen automatically; use the advanced flow +
79  CDC steps for near-real-time Postgres.
80- Inline credentials in `payload` still work for headless/automation, but prefer the connect-link handoff above.
81 
82## Advanced: hand-pick tables (three-step flow)
83 
84Use this when the user wants to choose exactly which tables sync or set non-default sync types. Don't try to shortcut
85to `external-data-sources-create` — you need the db-schema response to build a valid `schemas` payload.
86 
87```text
88         ┌────────────────────┐
89         │ 1. wizard          │  What source types exist? What fields does each need?
90         └────────┬───────────┘
91                  ▼
92         ┌────────────────────┐
93         │ 2. db-schema       │  Validate creds. List tables + available sync methods per table.
94         └────────┬───────────┘
95                  ▼
96         ┌────────────────────┐
97         │ 3. create          │  Send source_type + credentials + schemas[] to actually create.
98         └────────────────────┘
99```
100 
101## Workflow
102 
103### Step 1 — Discover the source type
104 
105Call `external-data-sources-wizard` (no params). The response is a dict keyed by source type. Each entry describes:
106 
107- `name` — the canonical source_type string you'll pass to later calls (e.g. `"Postgres"`, `"Stripe"`, `"Hubspot"`).
108- `label` / `caption` — human-readable.
109- `fields` — the config fields needed (host, port, database, api_key, client_id/secret, ...). Each has `name`,
110  `type` (input, password, switch, select, file-upload), and `required`.
111- `featured`, `unreleasedSource` — use to gauge readiness. Skip sources marked `unreleasedSource: true` unless the
112  user explicitly asked for a preview.
113 
114Match the user's request to a source. If they said "Postgres", look up `Postgres`. If they said something ambiguous
115like "database", present the top relevant matches (Postgres, MySQL, MongoDB, BigQuery, Snowflake, Redshift) and let
116them pick.
117 
118For OAuth-based sources (Hubspot, Salesforce, Google Ads), the wizard entry hints at an OAuth flow. These typically
119need the user to authorize in the PostHog UI rather than pasting credentials — explain this and direct them to the
120source setup page rather than trying to collect tokens in chat. OAuth is about _authentication_, not about how data
121flows; OAuth sources still use polling bulk sync, not webhooks.
122 
123Gather the required credentials from the user. Never ask for more fields than the wizard entry says are required —
124asking for an unnecessary `port` when the source doesn't need one confuses users.
125 
126### Step 2 — Validate credentials and discover tables
127 
128Call `external-data-sources-db-schema` with `source_type` plus all credential fields. This does two things at once:
129 
1301. Validates the credentials against the live source. Returns 400 with a `message` if anything is wrong (bad host,
131   wrong password, permission denied). Show the error verbatim — it's often actionable ("password authentication
132   failed for user 'x'").
1332. If valid, returns an array of table entries. Each entry:
134 
135```text
136{
137  "table": "orders",
138  "should_sync": false,
139  "rows": 1_250_000,
140  "incremental_available": true,   # can do sync_type=incremental
141  "append_available": true,        # can do sync_type=append
142  "cdc_available": true,           # can do sync_type=cdc  (null = not enabled for team)
143  "supports_webhooks": false,      # can do sync_type=webhook for real-time push
144  "incremental_fields": [          # candidates: usually updated_at, created_at, id
145    {"field": "updated_at", "type": "datetime", "label": "updated_at", ...},
146    {"field": "created_at", "type": "datetime", ...},
147    {"field": "id", "type": "integer", ...}
148  ],
149  "detected_primary_keys": ["id"],
150  "available_columns": [{"field": "id", "type": "integer", "nullable": false}, ...],
151  "description": "..."
152}
153```
154 
155Present this to the user. Don't dump the raw JSON — summarize: which tables were found, row counts, and the default
156sync method recommendation per table (see [sync-type decision guide](./references/sync-types.md)).
157 
158### Step 3 — Confirm per-table sync configuration
159 
160For each table the user wants to sync, pick a sync_type. See the
161[sync-type decision guide](./references/sync-types.md) for detailed rules, but the short version is:
162 
163- **Small / dimension tables (<50k rows, no natural ordering column):** `full_refresh` — simple and always correct.
164- **Large tables with an `updated_at` / `modified_at`:** `incremental` — much cheaper per sync.
165- **Append-only immutable tables (logs, events):** `append` if available — preserves history.
166- **Postgres with CDC enabled and you need near-real-time:** `cdc` — requires primary keys and Postgres prerequisites.
167- **Sources that support webhooks (currently Stripe):** for near-real-time ingestion set `sync_type: "webhook"` on
168  the tables where `supports_webhooks: true`, then register the webhook as a post-create step (see step 6 below).
169  Tables that don't support webhooks on the same source still need a bulk sync_type.
170 
171For each schema that will use `incremental`/`append`/`cdc`, you also need:
172 
173- `incremental_field` — which column to track for high-water-mark ordering. Pick from the `incremental_fields` list
174  returned by db-schema. Prefer `updated_at` over `created_at` (updated_at catches late-arriving updates;
175  created_at misses them). For integer-only tables, use the monotonically increasing primary key.
176- `incremental_field_type` — must match the chosen field's type (`datetime`, `timestamp`, `date`, `integer`,
177  `numeric`, `objectid`).
178- `primary_key_columns` — required for CDC. Use `detected_primary_keys` from db-schema.
179 
180### Step 4 — Pick a good prefix
181 
182The source's `prefix` is prepended to table names in HogQL. Tables end up as `{prefix}_{table_name}`.
183 
184- Default to the source type lowercased if there's only one source of that type: `stripe`, `postgres`.
185- If the user already has a Postgres source, pick something distinguishing: `postgres_prod`, `postgres_analytics`.
186- Use lowercase, underscore-separated. The prefix becomes part of every HogQL query the user writes.
187 
188Confirm the prefix with the user before creating — changing it later is possible but renames every table.
189 
190### Step 5 — Create the source
191 
192Call `external-data-sources-create` with:
193 
194```json
195{
196  "source_type": "Postgres",
197  "prefix": "postgres_prod",
198  "payload": {
199    "host": "...",
200    "port": "5432",
201    "dbname": "...",
202    "user": "...",
203    "password": "...",
204    "schema": "public",
205    "schemas": [
206      {
207        "name": "orders",
208        "should_sync": true,
209        "sync_type": "incremental",
210        "incremental_field": "updated_at",
211        "incremental_field_type": "datetime",
212        "primary_key_columns": ["id"]
213      },
214      {
215        "name": "users",
216        "should_sync": true,
217        "sync_type": "full_refresh"
218      },
219      {
220        "name": "audit_log",
221        "should_sync": false
222      }
223    ]
224  }
225}
226```
227 
228Rules for the `schemas` array:
229 
230- Every table returned by db-schema should be included, even ones the user doesn't want (set `should_sync: false`).
231  Tables the user didn't mention default to `should_sync: false`.
232- `sync_type` is required only when `should_sync: true`.
233- `incremental_field` / `incremental_field_type` must be present when `sync_type` is `incremental` or `append`.
234- `primary_key_columns` must be present when `sync_type` is `cdc`.
235 
236On success you'll get back a source with a new `id`. The first sync is triggered automatically.
237 
238### Step 6 — Register a webhook (only when any schema is `sync_type: "webhook"`)
239 
240Webhook-type schemas don't start receiving data just by existing — the external service needs to know where to POST
241events, and PostHog needs to know how to verify them. This is a second call after source creation, not part of the
242`external-data-sources-create` payload. Do this **before** telling the user the setup is complete, otherwise they
243hear "syncs are running" while the push channel is still unregistered.
244 
245Only needed when at least one schema on the source has `sync_type: "webhook"` and `should_sync: true`. Currently only
246Stripe implements this flow; for everything else skip this step.
247 
248Before calling create-webhook, check `external-data-sources-webhook-info-retrieve({id})`. If it already returns
249`exists: true`, do NOT call create-webhook again — each successful call registers a new external endpoint and would
250result in duplicate deliveries.
251 
2521. Call `external-data-sources-create-webhook-create({id})`. PostHog:
253   - creates the HogFunction that will receive webhook POSTs,
254   - builds a schema_mapping from external event types to PostHog schema ids,
255   - calls the source's API (e.g. Stripe) to register the webhook URL and subscribe to the relevant events,
256   - on Stripe, auto-captures the `signing_secret` and stores it securely.
257 
258   Returns `{success, webhook_url, error}`. On success report the `webhook_url` to the user for their records — but
259   they don't need to paste it anywhere; registration is already done.
260 
2612. If `success: false` with a permissions error like "API key doesn't have permission to create webhooks":
262   - The HogFunction is still created, just disabled.
263   - Ask the user to create the webhook manually in the source's dashboard using the returned `webhook_url`.
264   - Have them copy the signing secret from the source's webhook settings.
265   - Call `external-data-sources-update-webhook-inputs-create({id}, {inputs: {signing_secret: "whsec_..."}})` to
266     store it. The HogFunction picks it up and verifies incoming payloads.
267 
2683. Verify with `external-data-sources-webhook-info-retrieve({id})`. A healthy webhook has `exists: true`,
269   `external_status.status: "enabled"`, and no `error`.
270 
271Webhooks are supplementary to bulk sync. The first load of a webhook-enabled schema is still done via polling
272(`initial_sync_complete` flips to true when done); after that, the webhook becomes the primary ingestion path. A
273webhook schema will still have a `sync_frequency` that schedules a periodic bulk refresh as a safety net. This is
274expected — not something to "fix".
275 
276### Step 7 — Confirm and explain what happens next
277 
278After creation (and, for webhook schemas, after Step 6):
279 
280- Call `external-data-schemas-list` to show the user the initial state.
281- Explain: every enabled schema enters `Running`, then moves to `Completed` when the first sync finishes. First
282  syncs can take anywhere from seconds to hours depending on row count — a multi-million-row table is fine, just
283  slow.
284- Tell them how to query: `SELECT * FROM {prefix}_{table_name} LIMIT 10` in HogQL.
285- Offer to check back in a few minutes to confirm the initial syncs succeeded.
286 
287## CDC setup for Postgres (optional, when requested)
288 
289If the user wants near-real-time replication from Postgres:
290 
2911. Before calling db-schema, run `external-data-sources-check-cdc-prerequisites-create` with their Postgres creds.
292   It returns `{valid, errors[]}` listing anything missing (wal_level, replication slot, publication, permissions).
2932. If `valid: false`, present the errors and ask the user to fix on the Postgres side. Don't try to create a CDC
294   source that will immediately fail.
2953. Once prerequisites pass, proceed to db-schema and create. Set `sync_type: "cdc"` on the tables that need it, and
296   include `primary_key_columns` for each (CDC requires them).
297 
298## Important notes
299 
300- **Always validate creds with db-schema before create.** The create endpoint will accept invalid creds and then fail
301  asynchronously — the source appears in the list with status `Error` and no tables. Skipping the validation step
302  just pushes the failure into the background.
303- **Present the table list before creating.** Large databases may have hundreds of tables. Don't auto-select them all
304  — row counts and relevance matter for billing. Let the user opt in explicitly.
305- **Don't invent schemas.** Every entry in the `schemas` array must correspond to a real table from the db-schema
306  response. You can't "also add an orders table" unless db-schema found one.
307- **Prefix is load-bearing.** It's part of every HogQL query the user will ever write against these tables. Pick
308  something short, descriptive, and not already taken.
309- **Prefer the secure connect-link for any credentials.** Use `data-warehouse-source-connect-link` so the user
310  authenticates in their browser — the connect page renders the source's full connection form (OAuth and credential
311  options alike) and stores the result without creating the source. Don't collect OAuth tokens or database passwords
312  in chat; pass the `credential_id` reference to setup — source creation always happens through setup, not the UI.
313  (An already-connected OAuth integration can also be passed directly via its id key, e.g.
314  `{"hubspot_integration_id": 123}`.)
315- **Webhooks are a separate step after create.** Setting `sync_type: "webhook"` on a schema doesn't register the
316  webhook — the `create-webhook` call does. Always follow create → create-webhook → webhook-info for webhook-type
317  schemas, and never leave a webhook schema dangling without registration (it just won't receive events).
318- **Webhook support is source-specific and sparse.** Currently only Stripe implements `WebhookSource`. Don't promise
319  webhooks for Hubspot, Salesforce, or Postgres — they'll use polling sync.
320- **Row counts drive billing.** Warehouse syncing is metered by rows synced. A chatty 500M-row events table synced
321  hourly is very different from a 10k-row dimension table synced daily. Flag large tables and offer longer sync
322  frequencies (`sync_frequency: "24hour"`) as the default.
323