Name: Querying Clickhouse
Availability: InStock
Author: skywatch-bsky

Install

$ agentstack add skill-skywatch-bsky-skywatch-agent-skills-querying-clickhouse

Open-source listing — not yet scanned by AgentStack. Follow the source repository for install instructions.

Security review

⚠ Flagged

1 finding(s); flagged for manual review. · v0.1.0 How review works →

• Prompt-injection patterns
• Secret / credential exfiltration
• Dangerous shell & filesystem operations
• Untrusted network calls
• Known-malicious package signatures

high Destructive filesystem operation.

What it can access

✓ Network access No
✓ Filesystem access No
✓ Shell / process execution No
✓ Environment & secrets No
✓ Dynamic code execution No

From automated source analysis of v0.1.0. “Used” means the capability is present in the source — more access means more to trust, not that it’s unsafe.

About

Querying ClickHouse

This skill provides essential knowledge for writing safe, efficient queries against Osprey rule execution data. Use this when you need to investigate account behavior, analyze rule performance, or detect patterns.

Safety Rules

The ClickHouse MCP server enforces strict safety constraints on all queries:

SELECT Only

All queries must be SELECT statements. No INSERT, UPDATE, DELETE, or DDL operations are permitted.

LIMIT Required

Every query must include a LIMIT clause. This prevents accidental runaway queries and caps result set sizes.

-- Good
SELECT ... FROM default.osprey_execution_results WHERE ... LIMIT 100

-- Bad (will be rejected)
SELECT ... FROM default.osprey_execution_results WHERE ...

Read-Only Enforcement

Queries must start with SELECT or WITH (for CTEs). JOINs, UNIONs, subqueries, and any table are allowed. Semicolons and INTO are blocked to prevent multi-statement execution and data export.

Key investigation tables:

default.osprey_execution_results — Osprey rule execution history
default.pds_signup_anomalies — PDS signup rate anomalies
default.url_overdispersion_results — Coordinated domain/URL sharing anomalies
default.account_entropy_results — Bot-like posting pattern detection
default.url_cosharing_pairs — Daily account URL co-sharing pairs (TTL 7 days)
default.url_cosharing_clusters — URL co-sharing cluster metrics and evolution (no TTL)
default.url_cosharing_membership — Daily URL co-sharing cluster membership (TTL 7 days)
default.quote_cosharing_pairs — Daily account quote co-sharing pairs (TTL 7 days)
default.quote_cosharing_clusters — Quote co-sharing cluster metrics and evolution (no TTL)
default.quote_cosharing_membership — Daily quote co-sharing cluster membership (TTL 7 days)
default.quote_overdispersion_results — Coordinated quote-post anomalies

-- All valid
SELECT * FROM default.osprey_execution_results WHERE ... LIMIT 100
SELECT a.Handle, a.AltGovHandleRule, b.cluster_id
FROM default.osprey_execution_results a
  JOIN default.url_cosharing_membership b ON a.FollowSubjectDid = b.did
LIMIT 50
WITH flagged AS (SELECT user_id FROM default.account_entropy_results WHERE is_bot_like = 1)
  SELECT Handle, AccountAgeSeconds FROM default.osprey_execution_results
  WHERE FollowSubjectDid IN (SELECT user_id FROM flagged) LIMIT 100

-- Rejected
INSERT INTO ...
SELECT * FROM ... INTO OUTFILE ...
SELECT * FROM ... ; DROP TABLE ...

60-Second Timeout

Queries running longer than 60 seconds are automatically cancelled. This encourages efficient query design and prevents resource exhaustion.

Constraint Enforcement

These constraints are enforced at the MCP layer before queries reach ClickHouse, so policy violations are caught early.

Schema Shape: Wide Event Table, Not Flat Columns

osprey_execution_results is a wide event table, not a flat rule_name/did/handle/content/matched/score table. Each row is one evaluated AT Protocol event (post, follow, profile update, etc.). There is no generic did, handle, content, created_at, rule_name, matched, or score column — those concepts are encoded as ~600+ individual dynamic columns, one per rule or model, added automatically as new rules deploy.

Only 6 system columns are present on every row (all prefixed __):

| Column | Type | Description | |--------|------|-------------| | __action_id | Int64 | Unique identifier for the evaluation event | | __timestamp | DateTime64(3) | UTC timestamp of evaluation — the primary time column | | __error_count | Nullable(Int32) | Errors during evaluation | | __atproto_label | Array(String) | Labels applied as a result of this evaluation | | __entity_label_mutations | Array(String) | Label changes applied to the entity | | __verdicts | Array(String) | Verdict strings emitted by rules |

Everything else — account identity, post text, rule outcomes, follower counts — lives in dynamic PascalCase columns like Handle, PostTextCleaned, AccountAgeSeconds, AltGovHandleRule. See the accessing-osprey skill's references/osprey-schema.md for the full column reference, naming conventions, and type patterns. Use clickhouse_schema or SELECT name, type FROM system.columns WHERE table = 'osprey_execution_results' to get the live list — it grows continuously.

Common columns used in this doc's query patterns:

| Column | Type | Use Case | |--------|------|----------| | __timestamp | DateTime64(3) | Filter by date range (always include for performance) | | Handle | Nullable(String) | Account handle | | PostTextCleaned | Nullable(String) | Cleaned post text — use with ngramDistance() | | AccountAgeSeconds | Nullable(Int64) | Account age at evaluation time | | ActionName | Nullable(String) | Event type (create_post, create_follow, update_profile, etc.) | | Rule | Nullable(UInt8) | 1 if that specific rule matched, else 0/NULL |

Query Structure Best Practices

Follow this pattern for reliable, performant queries:

1. Filter by `__timestamp` First

ClickHouse tables are time-partitioned on __timestamp. Always include a __timestamp filter to dramatically improve performance.

SELECT __timestamp, Handle, AltGovHandleRule
FROM default.osprey_execution_results
WHERE AltGovHandleRule = 1
  AND __timestamp > now() - interval 7 day
LIMIT 100

Without time filtering, queries may scan the entire table and timeout.

2. Select Specific Columns

Avoid SELECT *. With 600+ dynamic columns, ClickHouse's columnar storage makes selecting only needed columns dramatically faster.

-- Good (fast)
SELECT Handle, AltGovHandleRule, __timestamp
FROM default.osprey_execution_results
WHERE __timestamp > now() - interval 1 day
LIMIT 100

-- Bad (very slow, 600+ columns)
SELECT *
FROM default.osprey_execution_results
WHERE __timestamp > now() - interval 1 day
LIMIT 100

3. Use LIMIT Generously

Start with conservative LIMIT values (10-100) for exploratory queries, and increase only if needed.

-- Safe for exploration
SELECT ...
LIMIT 50

-- For comprehensive analysis, still cap results
SELECT ...
LIMIT 10000

4. Query Specific Rule/Model Columns Directly

There is no rule_name column to filter on — each rule is its own column. Reference the rule or model column by name in the WHERE clause.

SELECT __timestamp, Handle, AccountAgeSeconds
FROM default.osprey_execution_results
WHERE AltGovHandleRule = 1
  AND __timestamp > now() - interval 1 day
LIMIT 100

Performance Tips

Content Search Is Expensive

The ngramDistance() function searches for similar text by n-gram comparison. It's powerful but slow, and only meaningful on PostTextCleaned (not every row has post text — filter out NULL/empty first). Note: ngramDistance() returns 0 for identical content and 1 for completely different content.

Always pair ngramDistance() with other filters:

-- Good: narrow context with time + null-check + ngramDistance
SELECT Handle, PostTextCleaned, __timestamp
FROM default.osprey_execution_results
WHERE __timestamp > now() - interval 1 day
  AND PostTextCleaned IS NOT NULL
  AND length(PostTextCleaned) > 0
  AND ngramDistance(PostTextCleaned, 'target phrase')  now() - interval 7 day
GROUP BY day
LIMIT 50

-- Slower: full row enumeration
SELECT __timestamp, AltGovHandleRule
FROM default.osprey_execution_results
WHERE __timestamp > now() - interval 7 day
LIMIT 1000

Avoid Expensive Operations

String concatenation in WHERE clauses
Function calls on large datasets (e.g., LOWER(PostTextCleaned) for every row)
Subqueries (use JOIN patterns instead if possible, though joins are limited to the same table)
SELECT \* — with 600+ columns this is dramatically more expensive here than on a normal table