> ## Documentation Index
> Fetch the complete documentation index at: https://docs.specific.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Observability

> Query your environments' logs and metrics with SQL.

Every deployed environment streams logs and metrics into a queryable store. Use `specific query` to run SQL against this data when debugging production incidents, investigating regressions, or doing ad-hoc analytics.

## Running a query

```bash theme={null}
# Inline
specific query "SELECT count() FROM observability.logs"

# Against a specific environment
specific query --environment staging "SELECT * FROM observability.logs LIMIT 5"

# From a file via stdin
cat queries/p99.sql | specific query

# Machine-readable output for agents and scripts
specific query --format jsonl "SELECT Timestamp, Body FROM observability.logs LIMIT 5"
```

Flags:

| Flag                                 | Description                                                                                                                                                                                                                        |
| ------------------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `-e, --environment <name\|id>`       | Target environment by name or ID (defaults to the current one). Run `specific status` to list environments.                                                                                                                        |
| `--db <name>`                        | Run the query against one of the environment's **Postgres databases** (read-only) instead of the observability store. The SQL is standard Postgres, and the schema is your application's tables; see [Postgres](/guides/postgres). |
| `--format <table\|json\|jsonl\|csv>` | Output format. Defaults to `table` on a terminal, `jsonl` when piped.                                                                                                                                                              |

Queries are automatically scoped to the selected environment: you can't see data from other environments and don't need to filter on environment yourself.

Queries are read-only: `INSERT`, `UPDATE`, `DELETE`, and DDL are rejected. Each query is capped at 30 seconds of execution time, returns at most 100,000 rows, and the SQL string is limited to 50,000 characters.

The `table` format truncates wide values for readability. Agents and scripts should use `jsonl`, `json`, or `csv` for complete, untruncated values. Notices are printed to stderr so stdout remains parseable.

## Choosing an environment

`specific query` runs against exactly one environment. Run `specific status` to discover every environment and its ID. The output has two sections:

* **Environments** - long-lived environments such as `production` and `staging`.
* **Preview environments** - ephemeral environments created for pull requests (plus any manually created previews), each with its name, `env_...` ID, PR number and title, deployed URL, and expiry.

Preview environments stream logs and metrics exactly like long-lived ones. To inspect one, pass its name or ID to `--environment`; that's the only way to reach a preview's data, since queries are auto-scoped:

```bash theme={null}
specific query --environment preview-a1b2c3d4 \
  "SELECT Timestamp, ServiceName, Body
   FROM observability.logs
   WHERE SeverityNumber >= 17
   ORDER BY Timestamp DESC
   LIMIT 50"
```

## Schema

Two views are exposed in the `observability` database. Column names follow OpenTelemetry's ClickHouse exporter conventions and are **case-sensitive**, so use `SeverityText`, `ServiceName`, and `ResourceAttributes` exactly as shown. If unsure, run `DESCRIBE observability.logs`.

### observability.logs

Unified log stream from services. Retention: up to 30 days (your plan may restrict how far back queries can read; a cutoff prints a note on stderr).

| Column               | Type                | Notes                                                                                       |
| -------------------- | ------------------- | ------------------------------------------------------------------------------------------- |
| `Timestamp`          | DateTime64(9)       | When the log was emitted; supports time arithmetic (`Timestamp > now() - INTERVAL 1 HOUR`). |
| `ServiceName`        | String              | Service that emitted the log.                                                               |
| `SeverityText`       | String              |                                                                                             |
| `SeverityNumber`     | UInt8               |                                                                                             |
| `Body`               | String              | The log message.                                                                            |
| `LogAttributes`      | Map(String, String) | Per-event attributes.                                                                       |
| `ResourceAttributes` | Map(String, String) | Resource labels (for example, `service.name`, `deployment.environment.name`).               |
| `TraceId`            | String              | Distributed-tracing trace ID, if present.                                                   |
| `SpanId`             | String              | Distributed-tracing span ID, if present.                                                    |

Also exposed but rarely queried directly: `EnvironmentId`, `ProjectId` (used for auto-scoping), `TraceFlags`.

### observability.metrics

Unified metrics from services. Retention: up to 90 days.

| Column               | Type                | Notes                                            |
| -------------------- | ------------------- | ------------------------------------------------ |
| `TimeUnix`           | DateTime64(9)       | Measurement timestamp; supports time arithmetic. |
| `MetricName`         | String              | For example, `container.cpu.time` (see below).   |
| `MetricType`         | String              | `'gauge'` or `'sum'`.                            |
| `Value`              | Float64             | Measured value.                                  |
| `ServiceName`        | String              | Service that emitted the metric.                 |
| `Attributes`         | Map(String, String) | Metric dimensions.                               |
| `ResourceAttributes` | Map(String, String) | Resource labels.                                 |

### Service metrics

Emitted for every running service container:

| Metric                            | Type  | Notes                                                                              |
| --------------------------------- | ----- | ---------------------------------------------------------------------------------- |
| `container.cpu.time`              | sum   | Cumulative CPU seconds; rate it for utilization.                                   |
| `container.cpu.usage`             | gauge | Current CPU usage in cores.                                                        |
| `container.cpu.limit_utilization` | gauge | Fraction of CPU limit used (0–1).                                                  |
| `container.memory.working_set`    | gauge | Memory in active use (bytes), the right number for "is this service close to OOM". |
| `container.memory.usage`          | gauge | Total memory usage including page cache (bytes).                                   |
| `container.memory.available`      | gauge | Memory available before the container limit (bytes).                               |
| `k8s.volume.available`            | gauge | Free space on attached volumes (bytes).                                            |
| `k8s.volume.capacity`             | gauge | Total capacity of attached volumes (bytes).                                        |

## Debugging recipes

Recent errors for a service:

```sql theme={null}
SELECT Timestamp, Body
FROM observability.logs
WHERE ServiceName = 'api'
  AND SeverityNumber >= 17
  AND Timestamp >= now() - INTERVAL 1 HOUR
ORDER BY Timestamp DESC
LIMIT 50
```

Search log bodies for a substring:

```sql theme={null}
SELECT Timestamp, ServiceName, Body
FROM observability.logs
WHERE positionCaseInsensitive(Body, 'connection refused') > 0
  AND Timestamp >= now() - INTERVAL 6 HOUR
ORDER BY Timestamp DESC
LIMIT 100
```

Service CPU timeseries in 1-minute buckets:

```sql theme={null}
SELECT
  toStartOfMinute(TimeUnix) AS bucket,
  avg(Value)                AS cpu_cores
FROM observability.metrics
WHERE MetricName = 'container.cpu.usage'
  AND ServiceName = 'api'
  AND TimeUnix >= now() - INTERVAL 1 HOUR
GROUP BY bucket
ORDER BY bucket
```

Correlate all logs belonging to one trace:

```sql theme={null}
SELECT Timestamp, ServiceName, Body
FROM observability.logs
WHERE TraceId = '<trace-id>'
ORDER BY Timestamp
```

## Performance

Both views are partitioned and ordered to make environment-scoped, time-bounded queries fast. To stay within the 30-second limit:

* **Always filter by time** - `Timestamp >=` for logs, `TimeUnix >=` for metrics. Unbounded scans across 30/90 days will time out.
* **Add `ServiceName` and/or `MetricName`** to narrow the partition whenever you can.
* **Select only the columns you need** - `Body`, `LogAttributes`, and `ResourceAttributes` are the heavyweight columns.
* **`LIMIT` exploratory queries** while shaping them.

## Discovering data

When you don't know what's there, ask the database:

```sql theme={null}
SHOW TABLES FROM observability;
DESCRIBE observability.logs;

SELECT DISTINCT ServiceName FROM observability.logs LIMIT 50;
SELECT DISTINCT MetricName FROM observability.metrics LIMIT 100;
```

## ClickHouse dialect

The observability store is ClickHouse, so `specific query` without `--db` uses the ClickHouse SQL dialect (unlike `--db`, which is standard Postgres). Commonly needed functions:

* `now()`, `now64()` - current time
* `toStartOfMinute(t)`, `toStartOfFiveMinutes(t)`, `toStartOfHour(t)` - bucket timestamps
* `positionCaseInsensitive(haystack, needle)` - case-insensitive substring search
* `JSONExtractString(s, 'field')`, `JSONExtractInt`, `JSONExtractFloat` - pull fields out of JSON log bodies
* `LogAttributes['key']`, `Attributes['key']`, `ResourceAttributes['key']` - read map columns