|
1
|
--- |
|
2
|
name: query-integration-data |
|
3
|
description: Query and modify data in any connected integration (Linear, GitHub, HubSpot, Slack, Google services, etc.) or connected data warehouse (Databricks, Snowflake, BigQuery). Use listConnections() in the code_execution sandbox to get credentials, then call APIs directly. Supports read operations (queries, counts, exports) and write operations (create, update, delete). |
|
4
|
--- |
|
5
|
|
|
6
|
# Query Integration Data Skill |
|
7
|
|
|
8
|
Connect to any Replit-supported integration to read or write data — query issues, create tickets, send messages, update contacts, manage files, etc. This also includes querying supported data warehouse integrations like Databricks, Snowflake, and BigQuery. |
|
9
|
|
|
10
|
All code runs inline in the `code_execution` sandbox — no script files needed. |
|
11
|
|
|
12
|
## When to Use |
|
13
|
|
|
14
|
Use this skill when the user asks you a **question in chat** that requires data from external services to answer, or when they need to perform data operations without building a visual interface. |
|
15
|
|
|
16
|
- **Answer questions**: Query data to respond to user questions in the conversation (e.g., "How many issues were created this week?") |
|
17
|
- **Fetch and export data**: Export data to CSV/JSON for later use or analysis |
|
18
|
- **Write operations**: Create, update, delete, or modify data in a service |
|
19
|
- **Ad-hoc queries**: One-time data lookups or investigations |
|
20
|
- **Automate tasks**: Perform multi-step operations across the API |
|
21
|
|
|
22
|
**Key point:** Use this skill when the output is an answer or data file, NOT when building a dashboard or visualization interface. |
|
23
|
|
|
24
|
## When NOT to Use |
|
25
|
|
|
26
|
- **The user wants to create a dashboard, visualization, or analytics interface** - use the `data-visualization` skill (it handles data fetching internally) |
|
27
|
- **The user asks to "build", "create", or "make" a dashboard/app with data** - use the `data-visualization` skill |
|
28
|
- The user needs to add an integration to their app (use the `integrations` skill) |
|
29
|
- Production database operations (use the database pane directly) |
|
30
|
- Asks to check deployment or server logs (use the `deployment` skill) |
|
31
|
|
|
32
|
## File Structure |
|
33
|
|
|
34
|
```text |
|
35
|
.agents/ |
|
36
|
└── outputs/ # Generated artifacts (CSV, JSON, etc.) |
|
37
|
``` |
|
38
|
|
|
39
|
Code runs inline in the `code_execution` sandbox — no script files are needed. |
|
40
|
|
|
41
|
## Workflow |
|
42
|
|
|
43
|
```text |
|
44
|
1. CHECK → listConnections(connectorName) to get existing credentials |
|
45
|
├─ connections exist → EXECUTE → OUTPUT |
|
46
|
└─ empty array → SEARCH → LEARN → CLARIFY → setup via `integrations` skill → EXECUTE → OUTPUT |
|
47
|
``` |
|
48
|
|
|
49
|
- **CHECK**: Call `listConnections(connectorName)` in the `code_execution` sandbox. If it returns connections, you already have credentials — skip straight to EXECUTE. |
|
50
|
- **SEARCH → CONNECT** (only when no connections exist): Use `searchIntegrations`, `proposeIntegration`, and `addIntegration` to set up a new connection. See the `integrations` skill for the full lifecycle details. |
|
51
|
- **EXECUTE**: Write and run code in the `code_execution` sandbox. |
|
52
|
- **OUTPUT**: Return the answer or confirmation to the user. |
|
53
|
|
|
54
|
## Getting Connection Credentials |
|
55
|
|
|
56
|
### Primary: `listConnections(connectorName)` |
|
57
|
|
|
58
|
This is the main way to get credentials. It's a pre-registered function in the `code_execution` sandbox. |
|
59
|
|
|
60
|
```javascript |
|
61
|
const conns = await listConnections('linear'); |
|
62
|
console.log(conns.map(c => ({ id: c.id, displayName: c.displayName, status: c.status }))); |
|
63
|
``` |
|
64
|
|
|
65
|
Each connection object has: |
|
66
|
|
|
67
|
- `id`, `connectorConfigId`, `status`, `displayName`, `metadata`, `environment` |
|
68
|
- `settings` — credentials dict (access tokens, API keys, etc.) |
|
69
|
- `getClient()` — returns the `settings` object for constructing SDK clients |
|
70
|
|
|
71
|
Returns an empty array when no connections are configured. |
|
72
|
|
|
73
|
```javascript |
|
74
|
const conns = await listConnections('linear'); |
|
75
|
if (conns.length > 0) { |
|
76
|
const token = conns[0].settings.access_token; |
|
77
|
const { LinearClient } = await import('@linear/sdk'); |
|
78
|
const client = new LinearClient({ accessToken: token }); |
|
79
|
// Ready to query |
|
80
|
} |
|
81
|
``` |
|
82
|
|
|
83
|
### Fallback: Setting Up a New Connection |
|
84
|
|
|
85
|
If `listConnections` returns an empty array, the user hasn't connected the service yet. Use `searchIntegrations` to find the connector, then follow the `integrations` skill to walk the user through setup (`addIntegration` and `proposeIntegration` — order depends on integration type). After the connection is established, `listConnections` will return it. |
|
86
|
|
|
87
|
### Browse the Documentation |
|
88
|
|
|
89
|
**Always browse `public_documentation_link`** before writing code, especially for write operations. This helps you understand: |
|
90
|
|
|
91
|
- Required vs optional fields for creating resources |
|
92
|
- Valid values for enums (status, priority, type, etc.) |
|
93
|
- Relationships between resources (e.g., issues belong to projects) |
|
94
|
- Rate limits and best practices |
|
95
|
|
|
96
|
## Clarifying Questions |
|
97
|
|
|
98
|
**Before write operations, gather required information.** Many APIs require IDs or specific values that the user may not know. |
|
99
|
|
|
100
|
### When to Ask |
|
101
|
|
|
102
|
Ask clarifying questions when the user's request requires: |
|
103
|
|
|
104
|
- **Entity selection**: "Which project should this issue be created in?" |
|
105
|
- **User assignment**: "Who should be assigned? Let me list the team members..." |
|
106
|
- **Required fields**: "What priority - urgent, high, medium, or low?" |
|
107
|
- **Ambiguous references**: "I found 3 projects matching 'backend'. Which one?" |
|
108
|
|
|
109
|
### Pattern: Fetch Options First |
|
110
|
|
|
111
|
For write operations, often run a read query first to get valid options: |
|
112
|
|
|
113
|
```javascript |
|
114
|
// User says: "Create a Linear ticket assigned to John" |
|
115
|
// Problem: Need John's user ID, not just name |
|
116
|
|
|
117
|
const conns = await listConnections('linear'); |
|
118
|
const { LinearClient } = await import('@linear/sdk'); |
|
119
|
const client = new LinearClient({ accessToken: conns[0].settings.access_token }); |
|
120
|
|
|
121
|
// Step 1: List users to find John's ID |
|
122
|
const users = await client.users(); |
|
123
|
const john = users.nodes.find(u => u.name.includes('John')); |
|
124
|
|
|
125
|
// Step 2: If ambiguous, ASK the user |
|
126
|
// "I found John Smith and John Doe. Which one?" |
|
127
|
|
|
128
|
// Step 3: Create with correct ID |
|
129
|
await client.createIssue({ assigneeId: john.id, ... }); |
|
130
|
``` |
|
131
|
|
|
132
|
### Common Multi-Step Patterns |
|
133
|
|
|
134
|
| Action | First fetch... | |
|
135
|
| -------------------------- | ------------------------- | |
|
136
|
| Create issue with assignee | List team members | |
|
137
|
| Create issue in project | List projects | |
|
138
|
| Set status/priority | Get valid workflow states | |
|
139
|
| Add to channel | List channels | |
|
140
|
| Assign to team | List teams | |
|
141
|
|
|
142
|
## Running Code in the Sandbox |
|
143
|
|
|
144
|
All code runs in the `code_execution` sandbox. State persists across calls (notebook-style), so variables from one call are available in subsequent calls. |
|
145
|
|
|
146
|
### Read Operations |
|
147
|
|
|
148
|
Query data and return results: |
|
149
|
|
|
150
|
```javascript |
|
151
|
const conns = await listConnections('linear'); |
|
152
|
const { LinearClient } = await import('@linear/sdk'); |
|
153
|
const client = new LinearClient({ accessToken: conns[0].settings.access_token }); |
|
154
|
|
|
155
|
const issues = await client.issues({ first: 10 }); |
|
156
|
console.log(`Found ${issues.nodes.length} issues`); |
|
157
|
for (const issue of issues.nodes) { |
|
158
|
console.log(`${issue.identifier}: ${issue.title} [${issue.state?.name}]`); |
|
159
|
} |
|
160
|
``` |
|
161
|
|
|
162
|
### Write Operations |
|
163
|
|
|
164
|
Create, update, or delete data: |
|
165
|
|
|
166
|
```javascript |
|
167
|
const conns = await listConnections('linear'); |
|
168
|
const { LinearClient } = await import('@linear/sdk'); |
|
169
|
const client = new LinearClient({ accessToken: conns[0].settings.access_token }); |
|
170
|
|
|
171
|
// Create |
|
172
|
const created = await client.createIssue({ teamId: team.id, title: "Fix login bug" }); |
|
173
|
console.log(`Created: ${created.issue?.identifier}`); |
|
174
|
|
|
175
|
// Update |
|
176
|
await client.updateIssue(issueId, { stateId: doneState.id }); |
|
177
|
console.log(`Updated: ${issueId}`); |
|
178
|
|
|
179
|
// Delete |
|
180
|
await client.deleteIssue(issueId); |
|
181
|
console.log(`Deleted: ${issueId}`); |
|
182
|
``` |
|
183
|
|
|
184
|
### Multi-Step Operations |
|
185
|
|
|
186
|
Variables persist across `code_execution` calls, enabling multi-step workflows: |
|
187
|
|
|
188
|
```javascript |
|
189
|
// Call 1: Get credentials and list teams |
|
190
|
const conns = await listConnections('linear'); |
|
191
|
const { LinearClient } = await import('@linear/sdk'); |
|
192
|
const client = new LinearClient({ accessToken: conns[0].settings.access_token }); |
|
193
|
|
|
194
|
const teams = await client.teams(); |
|
195
|
const team = teams.nodes[0]; |
|
196
|
console.log(`Using team: ${team.name}`); |
|
197
|
|
|
198
|
const users = await client.users(); |
|
199
|
const assignee = users.nodes.find(u => u.name === 'John'); |
|
200
|
console.log(`Found assignee: ${assignee?.name}`); |
|
201
|
``` |
|
202
|
|
|
203
|
```javascript |
|
204
|
// Call 2: Variables from Call 1 are still available |
|
205
|
const issue = await client.createIssue({ |
|
206
|
teamId: team.id, |
|
207
|
assigneeId: assignee?.id, |
|
208
|
title: 'New feature request', |
|
209
|
description: 'Details here...', |
|
210
|
}); |
|
211
|
console.log(`Created ${issue.issue?.identifier}: New feature request`); |
|
212
|
``` |
|
213
|
|
|
214
|
## Databricks |
|
215
|
|
|
216
|
When the user wants to connect to Databricks, use the `databricks-m2m` connector (not the plain `databricks` connector). |
|
217
|
|
|
218
|
## Warehouse Data Exploration |
|
219
|
|
|
220
|
When querying data warehouses (BigQuery, Snowflake, Databricks), large schemas can make serial exploration slow (7-10s per query round-trip). Use the parallel subagent pattern to explore schemas faster. |
|
221
|
|
|
222
|
### CRITICAL: Warehouse queries are billed per byte scanned |
|
223
|
|
|
224
|
Warehouse targets (`bigquery`, `databricks`, `snowflake`) cost real money per query — a |
|
225
|
careless `SELECT *` over a fact table can cost tens of dollars and a dashboard that |
|
226
|
re-runs queries every few seconds can burn thousands of dollars per day. Before writing |
|
227
|
any warehouse query (via `executeSql` or `executeSql({target: "bigquery"})`) follow |
|
228
|
these rules: |
|
229
|
|
|
230
|
- **Project exact columns** — never `SELECT *` on wide tables. |
|
231
|
- **Always `LIMIT`** when exploring; `LIMIT 5`–`LIMIT 100` is plenty for a sample. |
|
232
|
- **Scope by partition / cluster** — add `WHERE event_date >= ...` (or the table's |
|
233
|
clustering column) so the warehouse prunes data and you are billed for a tiny slice. |
|
234
|
- **Prefer pre-aggregated tables** (e.g. `_daily`, `_summary`) over raw event tables. |
|
235
|
- **Diff-only reads** for anything incremental — `WHERE updated_at > :last_seen` and |
|
236
|
persist `last_seen` so subsequent queries only scan the delta. |
|
237
|
- **Cache repeated results locally** — if you run the same exploration query twice in |
|
238
|
the same session, store the first result in a variable or `.agents/outputs/*.csv` |
|
239
|
and reuse it instead of re-running the query. |
|
240
|
- **Don't power a data app from this skill** — if the user is building a dashboard, |
|
241
|
report, or explorer, hand off to the `data-visualization` skill so the app's API |
|
242
|
server can cache results (15-min TTL). The `query-integration-data` skill is for |
|
243
|
answering questions in chat, not for powering a live UI. |
|
244
|
|
|
245
|
### When to Use Parallel Exploration |
|
246
|
|
|
247
|
Use this pattern when ALL of the following are true: |
|
248
|
|
|
249
|
- The target is a **warehouse** connection (`bigquery`, `snowflake`, or `databricks`) |
|
250
|
- The initial INFORMATION_SCHEMA query returns **15+ tables** |
|
251
|
- The user's question is **not about a specific known table** (e.g., they're asking a broad question like "what's our revenue trend?" or "show me customer data") |
|
252
|
|
|
253
|
If the schema has fewer than 15 tables, serial exploration is fast enough — just query tables one-by-one. |
|
254
|
|
|
255
|
### 4-Step Parallel Workflow |
|
256
|
|
|
257
|
**Step 1: Schema Discovery** — Run a single `executeSql` call to get the full table list. |
|
258
|
|
|
259
|
```javascript |
|
260
|
// BigQuery |
|
261
|
const tables = await executeSql({ sqlQuery: `SELECT table_schema, table_name, row_count FROM \`project.region-us\`.INFORMATION_SCHEMA.TABLES WHERE table_schema NOT IN ('INFORMATION_SCHEMA') ORDER BY table_schema, table_name`, target: "bigquery" }); |
|
262
|
|
|
263
|
// Snowflake |
|
264
|
const tables = await executeSql({ sqlQuery: `SELECT TABLE_SCHEMA, TABLE_NAME, ROW_COUNT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA') ORDER BY TABLE_SCHEMA, TABLE_NAME`, target: "snowflake" }); |
|
265
|
|
|
266
|
// Databricks |
|
267
|
const tables = await executeSql({ sqlQuery: `SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema NOT IN ('information_schema') ORDER BY table_schema, table_name`, target: "databricks" }); |
|
268
|
``` |
|
269
|
|
|
270
|
`INFORMATION_SCHEMA.TABLES` is a metadata read and is essentially free. Sampling rows |
|
271
|
from the tables it returns is **not** free — see the next step. |
|
272
|
|
|
273
|
**Step 2: Group Tables** — Partition the table list into 2-4 clusters: |
|
274
|
|
|
275
|
- By schema/dataset name (e.g., `analytics.*`, `sales.*`, `marketing.*`) |
|
276
|
- By name prefix (e.g., `dim_*`, `fact_*`, `stg_*`) |
|
277
|
- By estimated relevance to the user's question (most-likely-relevant tables first) |
|
278
|
|
|
279
|
**Step 3: Launch Parallel Subagents** — Start one `SMALL_TASK` subagent per group: |
|
280
|
|
|
281
|
```javascript |
|
282
|
const group1 = await startAsyncSubagent({ |
|
283
|
task: `Explore these warehouse tables to answer: "${userQuestion}" |
|
284
|
|
|
285
|
Connection: Use executeSql({ sqlQuery: "...", target: "bigquery" }) — always pass target. |
|
286
|
Dialect: BigQuery (use backtick quoting for project.dataset.table) |
|
287
|
|
|
288
|
Tables to explore: |
|
289
|
- analytics.events |
|
290
|
- analytics.sessions |
|
291
|
- analytics.conversions |
|
292
|
|
|
293
|
For each table: |
|
294
|
1. Run: SELECT column_name, data_type, is_partitioning_column FROM \`project.dataset\`.INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'TABLE_NAME' |
|
295
|
2. Identify the partition / cluster column from step 1 (e.g. event_date, _PARTITIONTIME). |
|
296
|
3. Sample rows — project exact columns, scope by partition, and LIMIT. |
|
297
|
NEVER \`SELECT *\` on a fact table on a billed warehouse — \`LIMIT 5\` does not |
|
298
|
undo the bytes scanned by selecting every column. |
|
299
|
Pattern: SELECT <subset of columns from step 1> |
|
300
|
FROM \`project.dataset.TABLE_NAME\` |
|
301
|
WHERE <partition_col> >= CURRENT_DATE() - 7 |
|
302
|
LIMIT 5 |
|
303
|
|
|
304
|
Return your findings in this exact format: |
|
305
|
|
|
306
|
## Table Relevance |
|
307
|
| Table | Relevant? | Why | |
|
308
|
|-------|-----------|-----| |
|
309
|
| ... | Yes/No | Brief reason | |
|
310
|
|
|
311
|
## Column Details (relevant tables only) |
|
312
|
| Table | Column | Type | Notes | |
|
313
|
|-------|--------|------|-------| |
|
314
|
| ... | ... | ... | Key field, foreign key, metric, etc. | |
|
315
|
|
|
316
|
## Suggested Join Conditions |
|
317
|
- table_a.id = table_b.a_id (describe relationship) |
|
318
|
|
|
319
|
## Key Findings |
|
320
|
- Bullet points about data patterns, date ranges, notable values`, |
|
321
|
specialization: 'SMALL_TASK', |
|
322
|
relevantFiles: ['.local/skills/database/SKILL.md'] |
|
323
|
}); |
|
324
|
|
|
325
|
// Launch additional subagents for other groups (2-4 total) |
|
326
|
const group2 = await startAsyncSubagent({ /* same pattern, different tables */ }); |
|
327
|
``` |
|
328
|
|
|
329
|
**Step 4: Synthesize and Query** — Use the `wait_for_background_tasks` tool to wait for the schema-discovery subagents. Once they complete, read their outputs, combine the relevant tables/columns, and write the final SQL query. |
|
330
|
|
|
331
|
### Dialect-Specific Notes |
|
332
|
|
|
333
|
| Dialect | Table Quoting | INFORMATION_SCHEMA Path | Sample Query (project columns + partition + LIMIT) | |
|
334
|
|---------|--------------|------------------------|------------------------------------------------------| |
|
335
|
| BigQuery | `` `project.dataset.table` `` | `` `project.dataset`.INFORMATION_SCHEMA.COLUMNS `` | `` SELECT col_a, col_b FROM `p.d.t` WHERE event_date >= CURRENT_DATE() - 7 LIMIT 5 `` | |
|
336
|
| Snowflake | `"DATABASE"."SCHEMA"."TABLE"` | `DATABASE.INFORMATION_SCHEMA.COLUMNS` | `SELECT col_a, col_b FROM "DB"."SCH"."TBL" WHERE event_date >= DATEADD(day, -7, CURRENT_DATE) LIMIT 5` | |
|
337
|
| Databricks | `` `catalog.schema.table` `` | `catalog.information_schema.columns` | `` SELECT col_a, col_b FROM `c.s.t` WHERE event_date >= current_date() - INTERVAL 7 DAY LIMIT 5 `` | |
|
338
|
|
|
339
|
**Do NOT** use `SELECT *` in the sample query column above. `LIMIT 5` does not undo |
|
340
|
the cost of reading every column for the matched rows on a billed warehouse. |
|
341
|
|
|
342
|
### Tips |
|
343
|
|
|
344
|
- Each subagent should run 3-6 SQL queries (column metadata + sample data per table) |
|
345
|
- Keep subagent count to 2-4 — more than 4 has diminishing returns |
|
346
|
- The structured markdown output format ensures consistent, scannable results |
|
347
|
- After synthesis, write a single well-commented SQL query that answers the user's question |
|
348
|
|
|
349
|
## Output Guidelines |
|
350
|
|
|
351
|
- **Simple answers** (counts, scalar values, short lists of < 20 records): Print directly with `console.log()` |
|
352
|
- **Complex results** (tabular results): Write to `.agents/outputs/<filename>.csv` and summarize |
|
353
|
- **Write confirmations**: Print what was created/updated/deleted with IDs |
|
354
|
- **Errors**: Print clear error messages |
|
355
|
|
|
356
|
```javascript |
|
357
|
const fs = await import('fs'); |
|
358
|
|
|
359
|
// Simple |
|
360
|
console.log(`Answer: 42 issues created this week`); |
|
361
|
|
|
362
|
// Tabular results - write CSV to .agents/outputs/ |
|
363
|
fs.mkdirSync('.agents/outputs', { recursive: true }); |
|
364
|
fs.writeFileSync('.agents/outputs/results.csv', csvContent); |
|
365
|
console.log(`Exported 500 records to .agents/outputs/results.csv`); |
|
366
|
|
|
367
|
// Write |
|
368
|
console.log(`Created issue ENG-123: "Fix login bug"`); |
|
369
|
console.log(`Updated 5 contacts`); |
|
370
|
console.log(`Deleted message ID abc123`); |
|
371
|
``` |
|
372
|
|
|
373
|
## Key Points |
|
374
|
|
|
375
|
- **Use `listConnections(connectorName)`** as the primary way to get credentials |
|
376
|
- **Fall back to search → propose → add** when no connections exist (see `integrations` skill) |
|
377
|
- **All code runs in the `code_execution` sandbox** — no script files needed |
|
378
|
- **Use `console.log()`** to see output — functions execute silently without it (but never log credentials) |
|
379
|
- **Use `await import(...)`** for packages (dynamic imports only) |
|
380
|
- **State persists** across `code_execution` calls — reuse `conns`, clients, and extracted credentials instead of re-fetching (unless expired). |
|
381
|
- **Browse `public_documentation_link`** to understand the API before coding |
|
382
|
- **Ask clarifying questions** before write operations that need specific IDs or values |
|
383
|
- **Fetch options first** when the user references something by name (users, projects, etc.) |
|
384
|
- **Don't cache clients** — access tokens expire; re-create clients from `listConnections` when needed |
|
385
|
- **Write large outputs to `.agents/outputs/`** as CSV or JSON |