# Skywatch Accessing Osprey > Understanding the Osprey moderation infrastructure — system architecture, ClickHouse data access, schema reference, and relationship to Ozone labelling. Use when investigating AT Protocol accounts or reviewing rule execution data. - Skill: `skywatch-bsky/skywatch-accessing-osprey` (Agent Skill, multi-file: 2 files) - Install (CLI): `npx skillmds@latest add skywatch-bsky/skywatch-accessing-osprey` - Raw SKILL.md: https://api.skillmd.com/api/skills/skywatch-bsky/skywatch-accessing-osprey/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Coding & Dev Tools - Author: skywatch-bsky (https://skillmd.com/u/skywatch-bsky) - Updated: 2026-09-21 - Page: https://skillmd.com/skills/skywatch-bsky/skywatch-accessing-osprey --- # Accessing Osprey This skill provides foundational knowledge for accessing, understanding, and querying Osprey rule execution data in ClickHouse. Use this when you need to investigate rule matches, account behavior, or rule performance. ## What Is Osprey Osprey is a moderation rule engine for the AT Protocol (Bluesky). It runs continuously, evaluates user-defined rules against the global firehose of posts and other events, and records the results in a ClickHouse table called `osprey_execution_results`. Rules in Osprey are written in SML (a Python-like domain-specific language). Each rule is a predicate that returns true/false or a numeric score indicating whether a piece of content or an account matches the rule criteria. Key point: Osprey **detects**. It does not label or enforce policy directly. Its output informs human moderators and automated systems (like Ozone) about which accounts or posts match which rules. ## System Topology ``` AT Protocol Firehose ↓ Osprey Rule Engine (runs continuously) ├─ Reads: All posts, profiles, follows, other events ├─ Applies: User-defined rules (SML) ├─ Records: Execution results in ClickHouse └─ Outputs: osprey_execution_results table ↓ ┌───────────────────────────────────┐ │ Statistical Sidecars │ │ (read osprey_execution_results, │ │ write to their own tables) │ ├─ account_entropy → account_entropy_results ├─ url_overdispersion → url_overdispersion_results ├─ url_cosharing → url_cosharing_pairs (investigation only), _clusters, _membership, _runs ├─ quote_overdispersion → quote_overdispersion_results ├─ quote_cosharing → quote_cosharing_pairs, _clusters, _membership └─ signup_anomaly → pds_signup_anomalies ↓ Investigation / Analysis / Labelling Decisions ``` ## Statistical Sidecars Six sidecar services run alongside Osprey, reading from `osprey_execution_results` and producing scored output in their own ClickHouse tables. They flag — they don't label or take action. Their output feeds into investigations as starting points. ### Account Entropy Sidecar **Table:** `account_entropy_results` **Purpose:** Detect automated/bot-like posting patterns using normalized temporal distribution analysis. Scoring uses Miller–Madow bias-corrected entropy normalized by `log2(min(N, bins))`, putting all accounts on a 0–1 scale regardless of activity volume (raw entropy penalized low-activity accounts — fewer posts → lower max possible entropy). Two signals: - **Normalized hourly entropy** — how uniformly an account posts across 24 hours. High normalized value (≥ 0.85) = posts around the clock = bot signature. - **Normalized interval entropy** — how regular the gaps between posts are. Low normalized value (≤ 0.53) = mechanical spacing = bot signature. - **Interval CV** (coefficient of variation of inter-post intervals) — a complementary regularity signal (≥ 0.5 threshold). The `is_bot_like` flag fires when `hourly AND (interval OR cv)` — the account must show around-the-clock posting **and** at least one mechanical-spacing signal. This reduces false positives from shift workers (hourly only) and live-tweeters (interval only). Key columns: `user_id` (DID), `hourly_entropy_norm`, `interval_entropy_norm`, `interval_cv`, `is_bot_like`, `hourly_flag`, `interval_flag`, `cv_flag`, `mean_interval_seconds`, `stddev_interval_seconds`, `sample_rkeys`. Runs every hour, analyses 7-day windows, requires ≥ 10 posts. Threshold env vars: `ACCOUNT_ENTROPY_HOURLY_NORM_THRESHOLD` (0.85), `ACCOUNT_ENTROPY_INTERVAL_NORM_THRESHOLD` (0.53), `ACCOUNT_ENTROPY_CV_THRESHOLD` (0.5). ### URL Overdispersion Sidecar **Table:** `url_overdispersion_results` **Purpose:** Detect coordinated domain sharing campaigns using statistical anomaly detection. Computes two independent signals per domain per time bucket: - **Volume anomaly** (dispersion-aware negative binomial, method-of-moments, falling back to Poisson when data isn't overdispersed) — is the observed share count statistically unlikely given the domain's baseline rate? Baselines are dense, zero-filled rolling **medians** with hour-of-day matching — a bucket with no events counts as 0, not a gap. - **Sharer density anomaly** (one-sided beta-binomial, binomial fallback) — is the ratio of unique sharers to total shares unusually high? (Many accounts each sharing once = coordination.) Either signal alone can flag a domain as anomalous (`is_anomaly = 1`). **Benjamini–Hochberg FDR control** is applied per cycle/granularity/signal, so anomaly gating uses **q-values**, not raw p-values — the tables carry both for transparency, but always read q-values when interpreting results. Uses entity baselines (domain's own history over 14 days) when available, falling back to population median for new/rare domains. Produces results at both hourly and daily granularity. Key columns: `domain`, `granularity`, `total_shares`, `unique_sharers`, `sharer_density`, `volume_p_value`, `volume_q_value`, `density_p_value`, `density_q_value`, `is_anomaly`, `rolling_volume_median`, `rolling_density_mean`, `baseline_source`, `baseline_days_available`, `sample_dids`, `sample_urls`, `on_watchlist`. Runs every 15 minutes, requires ≥ 3 unique sharers. ### URL Co-Sharing Sidecar **Tables:** `url_cosharing_pairs` (investigation tooling only), `url_cosharing_clusters`, `url_cosharing_membership`, `url_cosharing_runs` **Purpose:** Detect coordinated inauthentic URL co-sharing using density-based dismantling (Cinus et al., WWW '25). The sidecar builds a TF-IDF cosine-similarity network over per-account URL-sharing vectors, isolates a high-precision coordinated core via grid-searched dismantling with knee detection, then decomposes the core with Leiden CPM. Detection is **precision-first** (targets precision > 0.9 at recall ≈ 0.1): `knee_found = false` days with zero clusters are correct behaviour, not failures — investigate empty results only alongside rising account eligibility. **Reads** `osprey_execution_results` directly (app.bsky.feed.post creates with non-empty FacetLinkList, over a rolling 7-day window ending yesterday). The `url_cosharing_pairs` materialized view is **no longer consumed by the sidecar** — it remains only for investigation tooling. **Writes** three tables: - **Runs** (`url_cosharing_runs`) — run metadata. `accounts_raw` is the pre-filter rolling-window population; `accounts_eligible` is the post-filter count (filter attrition = the gap). Re-runs are idempotent (today's rows deleted before insert). - **Clusters** (`url_cosharing_clusters`) — per-cluster metrics incl. `mean_edge_similarity`, `subgraph_density`, evolution type / predecessors / Jaccard. No TTL. - **Membership** (`url_cosharing_membership`) — daily membership snapshots. TTL 7 days. - **Pairs** (`url_cosharing_pairs`) — daily account pairs with co-shared URLs. **Investigation tooling only**; not consumed by the sidecar. TTL 7 days. Eligibility filtering is SQL-only (activity floor, df floor, df ceiling — all in `fetch_url_shares_query`). There is **no Python re-filtering** — do not recompute filters over filtered rows; that erases valid detections. Env var is `URL_COSHARING_MAX_URL_DF_FRACTION` (default 0.90, sklearn `max_df` semantics — a fraction of accounts). The old `URL_COSHARING_MAX_URL_DF_PCTL` name raises a ValueError at startup. Key columns: `cluster_id` (stable ID), `member_count`, `mean_edge_similarity`, `subgraph_density`, `evolution_type` (birth/death/continuation/merge/split), `temporal_spread_hours` (full rolling window, not yesterday only), `mean_posting_interval_seconds`, `sample_dids`, `sample_urls`. Runs hourly as docker compose service `url-cosharing`. Calibration: `uv run python -m url_cosharing.calibrate`. **Dedicated MCP tools:** Use `mcp__skywatch-mcp__cosharing_clusters`, `mcp__skywatch-mcp__cosharing_pairs`, `mcp__skywatch-mcp__cosharing_evolution` for structured access (these support JOINs across the three tables internally). For ad-hoc queries, use direct ClickHouse access via SSH (see below). ### Quote Co-Sharing Sidecar **Tables:** `quote_cosharing_pairs`, `quote_cosharing_clusters`, `quote_cosharing_membership` **Purpose:** Detect coordinated quote-post amplification by finding clusters of accounts that repeatedly quote the same posts on the same day. Uses a pairs-based graph (nodes = accounts, edges = co-quoted AT-URIs) with **Newman weighting** (Σ 1/(k−1) over shared items, where k is the item's sharer count), so one viral URI shared by thousands can't manufacture a cluster by itself. `build_graph` aggregates duplicate pairs before filtering; Leiden clusters on `newman_weight` while `min_edge_weight` filters on raw co-share weight. **Note:** This is the pairs-based Newman-weighted method — it remains current for quote co-sharing. URL co-sharing was reworked to a TF-IDF density-dismantling method (see above); do not assume the two share the same architecture. - **Pairs** (`quote_cosharing_pairs`) — daily account pairs with co-quoted posts. Uses `shared_uris` (AT-URIs). TTL 7 days. - **Clusters** (`quote_cosharing_clusters`) — cluster-level metrics. Uses `unique_uris` and `sample_uris` (AT-URIs). No TTL. - **Membership** (`quote_cosharing_membership`) — daily membership snapshots. TTL 7 days. Detects pile-ons, brigading, and astroturfing via coordinated quoting. Cross-reference with `url_cosharing_*` to find accounts coordinating across both sharing and quoting. ### Quote Overdispersion Sidecar **Table:** `quote_overdispersion_results` **Purpose:** Detect posts being quoted at statistically anomalous rates — potential targets of coordinated quote-post campaigns. Same statistical approach as URL overdispersion (NB volume + beta-binomial density + BH FDR) but applied to quote-posts, with phi-gated variance reconstruction. Tracks by `quoted_uri` (AT-URI) and `quoted_author_did`. Produces both hourly and daily results. Key columns: `quoted_uri`, `quoted_author_did`, `granularity`, `total_shares`, `unique_sharers`, `sharer_density`, `volume_p_value`, `volume_q_value`, `density_p_value`, `density_q_value`, `is_anomaly`, `baseline_source`, `baseline_days_available`, `sample_dids`. ### PDS Signup Anomaly Sidecar **Table:** `pds_signup_anomalies` **Purpose:** Detect unusual PDS signup patterns by host using dispersion-aware negative binomial models. Monitors signup rates per PDS host at daily and hourly granularity. Flags when observed signup count is statistically unlikely given the baseline rate (NB with Poisson fallback). **Benjamini–Hochberg FDR control** is applied per cycle/granularity — anomaly gating uses **q-values**, not raw p-values. Excludes known high-volume hosts (bsky.network, bridgy-fed, mostr.pub). Key columns: `pds_host`, `granularity`, `observed_count`, `distinct_accounts`, `expected_lambda`, `p_value`, `q_value`, `is_anomaly`, `baseline_source`, `baseline_days_available`, `dispersion_index`, `rolling_mean`, `rolling_variance`, `sample_dids`. ### How It Works 1. **Event Stream**: The AT Protocol firehose emits events (new posts, profile updates, follows, etc.) 2. **Rule Evaluation**: Osprey evaluates all rules and models against each event 3. **Result Recording**: Osprey writes a row to `osprey_execution_results` with system columns (`__action_id`, `__timestamp`, etc.) and populates each rule/model's column with its result (1/0 for rules, scores for models, strings for extractors). Columns for rules that didn't evaluate remain NULL. 4. **Data Availability**: Investigators and moderators query ClickHouse to understand which accounts are triggering which rules ## ClickHouse Data Access ### Connection ClickHouse is accessed via SSH + Docker. The agent SSHes into the remote server, then uses `sudo docker exec` to run `clickhouse-client` inside the ClickHouse container. Connection details are stored in `.envrc` in the project root and loaded with `direnv exec .`; do not print secret values. To execute a query: ```bash ssh "$CLICKHOUSE_SSH_USER@$CLICKHOUSE_SSH_HOST" \ "sudo docker exec $CLICKHOUSE_DOCKER_CONTAINER \ clickhouse-client --host=$CLICKHOUSE_HOST --port=$CLICKHOUSE_PORT \ --user=$CLICKHOUSE_USER --password='$CLICKHOUSE_PASSWORD' \ --database=$CLICKHOUSE_DATABASE \ --format=JSON --query=\"SELECT ... LIMIT 100\"" ``` To inspect schema, use `DESCRIBE TABLE`: ```bash ssh "$CLICKHOUSE_SSH_USER@$CLICKHOUSE_SSH_HOST" \ "sudo docker exec $CLICKHOUSE_DOCKER_CONTAINER \ clickhouse-client --host=$CLICKHOUSE_HOST --port=$CLICKHOUSE_PORT \ --user=$CLICKHOUSE_USER --password='$CLICKHOUSE_PASSWORD' \ --database=$CLICKHOUSE_DATABASE \ --format=JSON --query=\"DESCRIBE TABLE default.osprey_execution_results\"" ``` See the `skywatch-querying-clickhouse` skill for full query patterns, SSH usage, and the complete environment variable reference. ### Queryable Tables | Table | Source | Purpose | |-------|--------|---------| | `default.osprey_execution_results` | Osprey rule engine | Rule execution history | | `default.pds_signup_anomalies` | Signup anomaly sidecar | PDS signup rate anomalies | | `default.url_overdispersion_results` | URL overdispersion sidecar | Coordinated domain sharing anomalies | | `default.account_entropy_results` | Account entropy sidecar | Bot-like posting pattern detection | | `default.url_cosharing_pairs` | URL co-sharing sidecar | Daily URL co-sharing pairs — investigation tooling only, not consumed by sidecar (TTL 7 days) | | `default.url_cosharing_clusters` | URL co-sharing sidecar | URL cluster metrics and evolution (no TTL) | | `default.url_cosharing_membership` | URL co-sharing sidecar | Daily URL cluster membership (TTL 7 days) | | `default.url_cosharing_runs` | URL co-sharing sidecar | Run metadata: accounts_raw, accounts_eligible, edge counts | | `default.quote_cosharing_pairs` | Quote co-sharing sidecar | Daily quote co-sharing pairs (TTL 7 days) | | `default.quote_cosharing_clusters` | Quote co-sharing sidecar | Quote cluster metrics and evolution (no TTL) | | `default.quote_cosharing_membership` | Quote co-sharing sidecar | Daily quote cluster membership (TTL 7 days) | | `default.quote_overdispersion_results` | Quote overdispersion sidecar | Coordinated quote-post anomalies | All tables are read-only. The agent must self-enforce these constraints: - **SELECT/WITH only** — No INSERT, UPDATE, DELETE, DDL - **LIMIT required** — All queries must have a LIMIT clause - **No semicolons** — Do not chain multiple statements - **No INTO** — Do not use INTO OUTFILE or similar export clauses - **Timeout** — Queries should complete within 60 seconds; filter aggressively JOINs, UNIONs, CTEs, subqueries, and any table are allowed. ## Relationship to Ozone Ozone is the labelling system for the AT Protocol. It allows moderators to apply labels to accounts, posts, and other objects. **Osprey → Ozone flow:** 1. Osprey rule detects problematic content (rule matches) 2. Investigator reviews Osprey data via ClickHouse queries 3. Investigator uses the `mcp__skywatch-mcp__ozone_label` MCP tool to apply a label 4. Ozone records the label, which may affect visibility/filtering of that content Osprey data informs labelling decisions, but the two are separate systems: - Osprey is **automated** detection - Ozone is **manual** labelling (though it can be automated via orchestration) ## Relationship to osprey-rules Plugin The `osprey-rules` plugin is for **writing** rules (authoring SML). This skill is for **accessing** and **querying** rule execution data (understanding results). If you need to: - Write a new rule → Use `osprey-rules` plugin and `skywatch-authoring-osprey-rules` skill - Review/debug a rule → Use `osprey-rules` plugin and `skywatch-reviewing-osprey-rules` skill - Query rule results → Use this skill and `skywatch-querying-clickhouse` skill ## Schema Reference For the complete column listing, column types, and semantic descriptions, see: **`references/osprey-schema.md`** — Full schema documentation System columns (present on every row): - `__action_id` — Unique evaluation event identifier (Int64) - `__timestamp` — When the evaluation occurred, UTC (DateTime64(3)) - `__error_count` — Errors during evaluation (Nullable(Int32)) - `__atproto_label` — Labels applied (Array(String)) - `__entity_label_mutations` — Label changes applied (Array(String)) - `__verdicts` — Verdict strings emitted by rules (Array(String)) Common dynamic columns (PascalCase, all Nullable): - `ActionName` — AT Protocol action type (verified values: `operation#create`, `operation#update`, `operation#delete`, `account` — literal strings like `Create` match nothing) - `AccountAgeSeconds` — Account age in seconds at evaluation time - `DisplayName` — Account display name - `PostHasExternal` — Whether post contains an external link - Rule columns (e.g., `AltGovHandleRule`) — UInt8, 1 = matched - Score columns (e.g., `ToxicityScoreUnwrapped`) — Float64 **`__atproto_label` encoding** (Array(String)): each element is pipe-encoded `subject|val|description|expiration` (e.g. `did:plc:...|spam|Affiliate spam: ...|None` or `at://...|contains-slur|...|None`). Match a label VALUE with `arrayExists(x -> splitByChar('|', x)[2] = 'label-name', __atproto_label)` — NOT `has(__atproto_label, 'label-name')`, which exact-matches the full encoded string and silently returns 0. The `__atproto_label` column is the authoritative source for applied labels: `__verdicts` is rarely populated even when thousands of events are labelled — never conclude "rules aren't working" from an empty `__verdicts`. **SSH/AT-URI redaction hazard:** the ClickHouse SSH connection string (`user:password@host:port`) can collide with post rkey strings in command output — port digits adjacent to rkey characters trigger secret-redaction patterns that garble the rkey. Never print AT-URIs inline in SSH/ClickHouse command lines; pass URIs via files (stdin SQL) and re-verify any URI that looks mangled before citing it. ## Common Investigation Patterns Investigation queries usually follow these patterns: **Find accounts matching a specific rule:** ```sql SELECT __action_id, __timestamp, DisplayName, AccountAgeSeconds FROM default.osprey_execution_results WHERE AltGovHandleRule = 1 AND __timestamp > now() - INTERVAL 7 DAY LIMIT 100 ``` **Count rule hits over time:** ```sql SELECT toDate(__timestamp) AS day, countIf(AltGovHandleRule = 1) AS alt_gov_hits, countIf(DigestRepostBotRule = 1) AS digest_bot_hits FROM default.osprey_execution_results WHERE __timestamp > now() - INTERVAL 7 DAY GROUP BY day ORDER BY day LIMIT 30 ``` **Check multiple rules for recent activity:** ```sql SELECT __timestamp, AltGovHandleRule, SuicidalContentRule, DigestRepostBotRule FROM default.osprey_execution_results WHERE ActionName = 'operation#create' AND __timestamp > now() - INTERVAL 1 DAY LIMIT 100 ``` For proven query patterns, see the `skywatch-querying-clickhouse` skill. ## Performance Tips 1. **Always filter on `__timestamp`** — this is the partitioning key and critical for performance 2. **Use LIMIT** — results can be large; always limit rows 3. **Select specific columns** — with 600+ columns, `SELECT *` is extremely expensive. ClickHouse is column-oriented, so fewer columns = faster queries 4. **Dynamic columns are sparse** — most rule columns are NULL for any given row; only select the rules you care about 5. **No `rule_name` column** — each rule is its own column. Query specific rules by column name, not by filtering a generic field ## Next Steps - Read `references/osprey-schema.md` to understand all available columns - Load the `skywatch-querying-clickhouse` skill to learn 15+ proven query patterns - Use direct ClickHouse queries via SSH to execute exploratory queries (see `skywatch-querying-clickhouse` skill for patterns)