# ClickHouse

> Read-only enrichment and per-org visitor analytics against graph8's ClickHouse instances

Source: https://docs.graph8.com/developers/clickhouse/

> **Note:**
> These endpoints back the `g8_clickhouse_*` MCP tool family and give direct,
> read-only access to three ClickHouse instances: the global mashup enrichment
> cluster and your per-org visitor analytics instance.
>
> The intent ClickHouse (`intent.keywords` / `intent.crawl_pages`) is **not**
> re-exposed here — use the existing `/intent/*` routes instead.

---

## Bulk Enrichment Lookup

`POST /clickhouse/mashup`

Bulk enrichment lookup against the ClickHouse Cloud `mashup.*` tables:

- `fivexfive_b2b` — 251 million rows
- `fivexfive_b2c` — 424 million rows
- `upid_hem` — 1.95 billion-row HEM to UP_ID resolver
- `default.mashup_new` — denormalized view

Requires an active subscription. The handler refuses to run without a primary-key
filter — a single call cannot trigger a full-cluster scan.

> **Caution:**
> `by_domain` mode is rejected for the `b2c` profile because `b2c` rows have no
> `COMPANY_DOMAIN` column. `by_hem` mode joins through `mashup.upid_hem` first to
> resolve each HEM value to a UP_ID before fetching enrichment data.

### Request Body

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| `mode` | string | **Yes** | Lookup key type. One of: `by_upid`, `by_hem`, `by_domain` |
| `values` | array of strings | **Yes** | Primary key values to look up. 1–100 items. |
| `profile` | string | No | Dataset profile. One of: `b2b` (default), `b2c`, `joined` |
| `limit` | integer | No | Maximum rows to return. 1–1000. Default `1000`. |

### Example

**cURL**

```bash
curl -X POST "https://be.graph8.com/api/v1/clickhouse/mashup" \
  -H "Authorization: Bearer $API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "mode": "by_upid",
    "values": ["UPID1", "UPID2"],
    "profile": "b2b",
    "limit": 1000
  }'
```

### Response

```json
{
  "data": {
    "mode": "by_upid",
    "profile": "b2b",
    "records": [
      {
        "UP_ID": "u1",
        "COMPANY_DOMAIN": "acme.com",
        "...": "..."
      }
    ],
    "matched": 1,
    "requested": 2
  }
}
```

| Field | Type | Description |
|-------|------|-------------|
| `mode` | string | The lookup mode used for this request |
| `profile` | string | The dataset profile used |
| `records` | array | Matched enrichment rows. Column names vary by profile. |
| `matched` | integer | Number of input values that returned at least one record |
| `requested` | integer | Total number of values submitted |

---

## Per-Org Visitor Analytics

`POST /clickhouse/org-analytics`

Per-org visitor and intent-resolved analytics from your org's dedicated
ClickHouse visitor instance. The ClickHouse database name is derived from the
authenticated user's `organization.id` (lower-cased) — the request body never
carries an `org_id`. Cross-tenant queries are physically unreachable. A 90-day
window cap applies to all requests.

### Request Body

| Field | Type | Required | Description |
|-------|------|----------|-------------|
| `dataset` | string | **Yes** | Which visitor table to query. One of: `people_visitors`, `company_visitors`, `5x5_visitors`, `intent_resolved` |
| `since` | string | **Yes** | Start of the time window (inclusive). ISO date or datetime, e.g. `"2026-05-01"` |
| `until` | string | No | End of the time window (exclusive). ISO date or datetime. Defaults to now. |
| `filters` | object | No | Key-value pairs to filter rows. Unknown column keys are silently dropped. Example: `{"contact_id": 12345}` |
| `aggregation` | string | No | How to aggregate results. One of: `raw` (default), `daily_counts`, `top_n` |
| `limit` | integer | No | Maximum rows to return. 1–1000. Default `100`. |

#### Aggregation modes

| Mode | Returns |
|------|---------|
| `raw` | Matching rows ordered by the dataset's timestamp descending |
| `daily_counts` | `[{"day": "2026-05-01", "cnt": 12}, ...]` |
| `top_n` | `[{"value": "<group_key>", "cnt": N}, ...]` grouped by the dataset's primary key (`contact_id` for `people_visitors`, `company_id` for `company_visitors`, `hem` for `5x5_visitors`, `keyword_id` for `intent_resolved`) |

### Example

**cURL**

```bash
# Raw rows — people visitors in May 2026
curl -X POST "https://be.graph8.com/api/v1/clickhouse/org-analytics" \
  -H "Authorization: Bearer $API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "dataset": "people_visitors",
    "since": "2026-05-01",
    "until": "2026-05-30",
    "aggregation": "raw",
    "limit": 100
  }'

# Daily visit counts with a contact filter
curl -X POST "https://be.graph8.com/api/v1/clickhouse/org-analytics" \
  -H "Authorization: Bearer $API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "dataset": "people_visitors",
    "since": "2026-05-01",
    "filters": {"contact_id": 12345},
    "aggregation": "daily_counts"
  }'

# Top companies by visit count
curl -X POST "https://be.graph8.com/api/v1/clickhouse/org-analytics" \
  -H "Authorization: Bearer $API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "dataset": "company_visitors",
    "since": "2026-05-01",
    "aggregation": "top_n",
    "limit": 20
  }'
```

### Response

```json
{
  "data": {
    "dataset": "people_visitors",
    "aggregation": "raw",
    "rows": [
      {
        "contact_id": 1,
        "visited_at": "2026-05-30T10:00:00Z"
      }
    ],
    "total": 1,
    "window": {
      "since": "2026-05-01T00:00:00+00:00",
      "until": "2026-05-30T00:00:00+00:00"
    }
  }
}
```

| Field | Type | Description |
|-------|------|-------------|
| `dataset` | string | The dataset queried |
| `aggregation` | string | The aggregation mode applied |
| `rows` | array | Result rows. Shape depends on `dataset` and `aggregation`. |
| `total` | integer | Number of rows returned |
| `window.since` | string | Resolved start of the query window (UTC ISO datetime) |
| `window.until` | string | Resolved end of the query window (UTC ISO datetime) |