PostHog skills collection

1---
2name: signals
3description: >
4  How to query the document_embeddings table for raw signal data using HogQL. Use when you need to
5  perform semantic search over signals, fetch every signal that contributed to a specific report,
6  or list signal types. For browsing the curated report layer (the Inbox) — listing reports,
7  filtering by status/source, drilling into a single report by ID — use the `inbox-exploration`
8  skill first; drop into this skill afterwards if the user wants the underlying observations.
9---
10 
11# Querying Signals
12 
13## What Are Signals?
14 
15Signals are automated observations that PostHog generates by monitoring a customer's product data across multiple sources — error tracking, web analytics, experiments, session replay, and more. Each signal is a short natural-language description of something noteworthy (e.g. "Error rate spiked 3× on /checkout").
16 
17Signals are grouped into **Signal Reports**. When a report accumulates enough weight it gets summarized and assessed for actionability. A signal report represents a cluster of related observations that together describe a meaningful issue or trend.
18 
19Signals and their embeddings are stored in the `document_embeddings` ClickHouse table, queryable via HogQL through the `posthog:execute-sql` MCP tool. They may provide a useful way to semantically query for recent things that happened in the user's product.
20 
21## When to use this skill vs. `inbox-exploration`
22 
23The two skills cover different layers of the same product:
24 
25- **`inbox-exploration`** — curated report layer via dedicated MCP tools (`inbox-reports-list`, `inbox-reports-retrieve`, `inbox-source-configs-list`, `inbox-source-configs-retrieve`). Use for "what's in my inbox?", "what's actionable?", filtering reports by status / source / suggested reviewer, looking up a specific report by ID or URL.
26- **This skill (`signals`)** — raw signal layer via HogQL on `document_embeddings`. Use when the curated report layer is not enough: semantic search over signal text, fetching every signal that contributed to a specific report, listing what kinds of signals exist, or any ad-hoc analytics that the report tools don't expose.
27 
28The typical pattern is to start with `inbox-exploration`, get a `report_id` or a sense of the area the user cares about, then drop into this skill when the user wants to see the raw observations.
29 
30## Table and Column Reference
31 
32The HogQL table alias is `document_embeddings`. HogQL automatically constrains queries to the current team — you never need to filter on `team_id`. Key columns for signals:
33 
34| Column          | Type           | Description                                                                  |
35| --------------- | -------------- | ---------------------------------------------------------------------------- |
36| `product`       | String         | Product bucket — always `'signals'` for signals                              |
37| `document_type` | String         | Document type — always `'signal'` for signals                                |
38| `model_name`    | String         | Embedding model — always `'text-embedding-3-small-1536'`                     |
39| `document_id`   | String         | Unique signal ID (UUID)                                                      |
40| `timestamp`     | DateTime64(3)  | When the signal was created                                                  |
41| `inserted_at`   | DateTime64(3)  | When this row version was inserted (used for deduplication and soft deletes) |
42| `content`       | String         | The signal description text                                                  |
43| `metadata`      | String         | JSON string with report_id, source info, weight, deleted flag, etc           |
44| `embedding`     | Array(Float64) | 1536-dimensional embedding vector                                            |
45 
46## Mandatory Filters
47 
48Every signals query MUST include all four of these filters. Missing any of them can cause the query to fail with an invalid model error, return wrong data, or trigger unnecessarily expensive scans:
49 
50```sql
51WHERE model_name = 'text-embedding-3-small-1536'
52  AND product = 'signals'
53  AND document_type = 'signal'
54  AND timestamp >= now() - INTERVAL 30 DAY
55```
56 
57The `model_name` filter is especially critical — the HogQL engine uses it to route to the correct underlying ClickHouse table. If the `WHERE model_name = ...` equality filter is missing or uses an unknown model, the query will fail with an "Invalid model name" error (you cannot use `IN` or other expressions here).
58 
59The `product` and `document_type` filters are equally important — the same model contains data from multiple products (e.g. error tracking, AI memory). Without these filters you will get unrelated data mixed in.
60 
61The `timestamp` filter is required for performance — the table is partitioned by week and has a 3-month TTL. Always include a time bound using `now() - INTERVAL N DAY` (or `WEEK`, `MONTH`, etc.). Default to 30 days unless you have a reason to look further back. Generally, more recent data is more likely to be relevant, unless investigating a long-standing issue.
62 
63## Deduplication Pattern
64 
65The underlying table can contain multiple versions of the same signal (e.g. after a soft-delete re-emission). You MUST always deduplicate by wrapping reads in a subquery using `argMax(..., inserted_at)` grouped by `document_id`.
66 
67**Note:** HogQL supports `metadata.field_name` dot access on the raw `metadata` JSON column, but this type information is lost when the column passes through aggregate functions like `argMax()`. You MUST extract individual metadata fields inside the inner dedup subquery — do NOT pass the whole `metadata` blob through `argMax` and dot into it in the outer query, as this will fail with a type error.
68 
69HogQL's JSON dot access always extracts values as `Nullable(String)`, regardless of the underlying JSON type. This means `metadata.deleted` is the string `'true'`/`'false'`/`null`, not a Bool. Use `deleted != 'true'` — do NOT use `NOT deleted`.
70 
71```sql
72SELECT ... FROM (
73    SELECT
74        document_id,
75        argMax(content, inserted_at) as content,
76        argMax(metadata.report_id, inserted_at) as report_id,
77        argMax(metadata.source_product, inserted_at) as source_product,
78        argMax(metadata.source_type, inserted_at) as source_type,
79        argMax(metadata.deleted, inserted_at) as deleted,
80        argMax(embedding, inserted_at) as embedding,
81        argMax(timestamp, inserted_at) as signal_ts
82    FROM document_embeddings
83    WHERE model_name = 'text-embedding-3-small-1536'
84      AND product = 'signals'
85      AND document_type = 'signal'
86      AND timestamp >= now() - INTERVAL 1 MONTH
87    GROUP BY document_id
88)
89WHERE deleted != 'true'
90```
91 
92Only select the `embedding` column in the inner subquery when you actually need it for similarity searches — it's a 1536-element float array and expensive to materialize otherwise.
93 
94## The `embedText()` Function
95 
96`embedText()` is a HogQL function that converts a text string into an embedding vector at query compile time. It calls the embedding API and inlines the resulting vector as a constant before executing the query. This means you can do semantic search in a single query without any external embedding step.
97 
98**Signature:** `embedText(text, model_name)`
99 
100- `text` — the string to embed. **Must be a string literal**, not a column reference.
101- `model_name` — the embedding model to use. **For signals, always use `'text-embedding-3-small-1536'`.**
102 
103Both arguments must be literal strings. You cannot pass column values or expressions — the function resolves at compile time, not per row.
104 
105## `cosineDistance()` for Similarity Search
106 
107Use `cosineDistance(embedding, ...)` to rank signals by semantic similarity. Lower values = more similar. Always `ORDER BY distance ASC` and add a `LIMIT`.
108 
109```sql
110cosineDistance(embedding, embedText('your search text', 'text-embedding-3-small-1536')) as distance
111```
112 
113The embedding model (`text-embedding-3-small-1536`) uses matryoshka representation learning, so the embedding dimensions are ordered by importance. This means similarity search works well even at high dimensionality — the curse of dimensionality is not a significant concern here.
114 
115## Metadata JSON Fields
116 
117The `metadata` column is a JSON string. HogQL supports `metadata.field_name` dot access **only on the raw table column**. After aggregation (e.g. `argMax`), the JSON type is lost and dot access will fail. Always extract the fields you need inside the dedup subquery.
118 
119| Field            | Inner-query access        | Description                                                         |
120| ---------------- | ------------------------- | ------------------------------------------------------------------- |
121| `report_id`      | `metadata.report_id`      | UUID of the parent Signal Report (empty if unassigned)              |
122| `source_product` | `metadata.source_product` | Originating product (use Example 3 to discover available values)    |
123| `source_type`    | `metadata.source_type`    | Signal type (use Example 3 to discover available values)            |
124| `source_id`      | `metadata.source_id`      | ID of the source entity                                             |
125| `weight`         | `metadata.weight`         | Signal weight (contributes to report promotion threshold)           |
126| `deleted`        | `metadata.deleted`        | Soft-deletion flag (extracted as String — compare with `!= 'true'`) |
127| `extra`          | `metadata.extra`          | Arbitrary JSON blob from the source product                         |
128| `match_metadata` | `metadata.match_metadata` | LLM match reasoning stored during grouping                          |
129 
130---
131 
132## Example 1: Semantic Search for Signals
133 
134Find signals most similar to a natural-language query. This is the most useful query for understanding what's happening in a customer's product:
135 
136```sql
137SELECT
138    document_id,
139    content,
140    report_id,
141    source_product,
142    source_type,
143    cosineDistance(embedding, embedText('users seeing errors on checkout page', 'text-embedding-3-small-1536')) as distance
144FROM (
145    SELECT
146        document_id,
147        argMax(content, inserted_at) as content,
148        argMax(metadata.report_id, inserted_at) as report_id,
149        argMax(metadata.source_product, inserted_at) as source_product,
150        argMax(metadata.source_type, inserted_at) as source_type,
151        argMax(metadata.deleted, inserted_at) as deleted,
152        argMax(embedding, inserted_at) as embedding,
153        argMax(timestamp, inserted_at) as signal_ts
154    FROM document_embeddings
155    WHERE model_name = 'text-embedding-3-small-1536'
156      AND product = 'signals'
157      AND document_type = 'signal'
158      AND timestamp >= now() - INTERVAL 1 MONTH
159    GROUP BY document_id
160)
161WHERE deleted != 'true'
162ORDER BY distance ASC
163LIMIT 10
164```
165 
166Adjust the `embedText` first argument to whatever you're looking for. Write it as a natural-language description of the kind of issue or observation you want to find.
167 
168To restrict to signals that have already been grouped into a report, add `AND report_id != ''` to the outer WHERE.
169 
170## Example 2: Fetch All Signals for a Specific Report
171 
172Once you have a `report_id` (from a semantic search or from the Signal Reports API), fetch all signals belonging to that report:
173 
174```sql
175SELECT
176    document_id,
177    content,
178    report_id,
179    source_product,
180    source_type,
181    signal_ts
182FROM (
183    SELECT
184        document_id,
185        argMax(content, inserted_at) as content,
186        argMax(metadata.report_id, inserted_at) as report_id,
187        argMax(metadata.source_product, inserted_at) as source_product,
188        argMax(metadata.source_type, inserted_at) as source_type,
189        argMax(metadata.deleted, inserted_at) as deleted,
190        argMax(timestamp, inserted_at) as signal_ts
191    FROM document_embeddings
192    WHERE model_name = 'text-embedding-3-small-1536'
193      AND product = 'signals'
194      AND document_type = 'signal'
195      AND timestamp >= now() - INTERVAL 3 MONTH
196    GROUP BY document_id
197)
198WHERE report_id = '<report-uuid-here>'
199  AND deleted != 'true'
200ORDER BY signal_ts ASC
201LIMIT 100
202```
203 
204## Example 3: List Signal Types
205 
206See what kinds of signals exist for this customer — returns one example per unique `(source_product, source_type)` pair from the last month:
207 
208```sql
209SELECT
210    source_product,
211    source_type,
212    count() as cnt,
213    max(signal_ts) as latest_timestamp
214FROM (
215    SELECT
216        document_id,
217        argMax(metadata.source_product, inserted_at) as source_product,
218        argMax(metadata.source_product, inserted_at) as source_product,
219        argMax(metadata.source_type, inserted_at) as source_type,
220        argMax(metadata.deleted, inserted_at) as deleted,
221        argMax(timestamp, inserted_at) as signal_ts
222    FROM document_embeddings
223    WHERE model_name = 'text-embedding-3-small-1536'
224      AND product = 'signals'
225      AND document_type = 'signal'
226      AND timestamp >= now() - INTERVAL 1 MONTH
227    GROUP BY document_id
228)
229WHERE deleted != 'true'
230GROUP BY source_product, source_type
231ORDER BY latest_timestamp DESC
232LIMIT 100
233```
234 
235## Example 4: Recent Signals from a Specific Source
236 
237Find the latest signals from a particular product source (e.g. all error tracking signals):
238 
239```sql
240SELECT
241    document_id,
242    content,
243    source_type,
244    report_id,
245    signal_ts
246FROM (
247    SELECT
248        document_id,
249        argMax(content, inserted_at) as content,
250        argMax(metadata.source_product, inserted_at) as source_product,
251        argMax(metadata.source_type, inserted_at) as source_type,
252        argMax(metadata.report_id, inserted_at) as report_id,
253        argMax(metadata.deleted, inserted_at) as deleted,
254        argMax(timestamp, inserted_at) as signal_ts
255    FROM document_embeddings
256    WHERE model_name = 'text-embedding-3-small-1536'
257      AND product = 'signals'
258      AND document_type = 'signal'
259      AND timestamp >= now() - INTERVAL 1 WEEK
260    GROUP BY document_id
261)
262WHERE source_product = 'error_tracking'
263  AND deleted != 'true'
264ORDER BY signal_ts DESC
265LIMIT 100
266```
267 
268Replace `'error_tracking'` with any source product: `'web_analytics'`, `'experiments'`, `'session_replay'`, etc. Use Example 3 to discover what source products and types exist.
269 
270## Example 5: Full-Text Search for Signals
271 
272When you know a specific keyword or phrase to search for (e.g. a product name, error message, or URL), full-text search with `ILIKE` is faster and more precise than semantic search:
273 
274```sql
275SELECT
276    document_id,
277    content,
278    source_product,
279    source_type,
280    signal_ts
281FROM (
282    SELECT
283        document_id,
284        argMax(content, inserted_at) as content,
285        argMax(metadata.source_product, inserted_at) as source_product,
286        argMax(metadata.source_type, inserted_at) as source_type,
287        argMax(metadata.deleted, inserted_at) as deleted,
288        argMax(timestamp, inserted_at) as signal_ts
289    FROM document_embeddings
290    WHERE model_name = 'text-embedding-3-small-1536'
291      AND product = 'signals'
292      AND document_type = 'signal'
293      AND timestamp >= now() - INTERVAL 1 MONTH
294    GROUP BY document_id
295)
296WHERE deleted != 'true'
297  AND content ILIKE '%feature flag%'
298ORDER BY signal_ts DESC
299LIMIT 10
300```
301 
302Replace `'%feature flag%'` with whatever term you're looking for. Use `ILIKE` for case-insensitive substring matching. For exact token matching, use `hasTokenCaseInsensitive(content, 'token')` instead.
303 
304## Gotchas
305 
3061. **Always use `text-embedding-3-small-1536` as the model name.** This is the only model used for signals.
3072. **`embedText()` arguments must be string literals.** You cannot pass column references or expressions — the function resolves at compile time, not per row.
3083. **Always time-bound your queries.** The table has a 3-month TTL, but unbounded scans are expensive. Use `timestamp >= now() - INTERVAL 1 MONTH` or tighter. Place the time filter in the inner subquery's `WHERE` clause (on the raw `timestamp` column) for best performance.
3094. **Always deduplicate.** Without the `argMax(..., inserted_at) GROUP BY document_id` subquery, you will see stale and duplicate rows.
3105. **Only select `embedding` when you need it.** It's a 1536-element float array — omit it from the inner subquery when you're not doing similarity search.
3116. **Queries should not end with a semicolon.** HogQL does not use them.
3127. **Add a `LIMIT` to every query.** Maximum allowed is 500 rows. In general, you should only select 10 or so signals, using semantic or full text search to rank them.
3138. **Extract metadata fields inside the dedup subquery.** HogQL's `metadata.field` dot access only works on the raw table column. After `argMax()` aggregation, the JSON type is lost and dot access will fail with a type error. Always use `argMax(metadata.field_name, inserted_at) as field_name` in the inner query.
3149. **All JSON dot-access values are `Nullable(String)`.** HogQL extracts every JSON field as a String, even booleans and numbers. For `metadata.deleted`, use `deleted != 'true'` — do NOT use `NOT deleted`.
31510. **Don't alias `argMax(timestamp, inserted_at)` as `timestamp` if the same inner query also filters on the raw `timestamp` column.** HogQL resolves the alias name first, causing an "aggregate in WHERE" error. Either use a distinct alias like `signal_ts`, or move the time filter to the outer query.
316