# Drill Cloud v2

Optimized Drill Cloud backend for high-volume time-series reads on PostgreSQL + TimescaleDB.

## Why v2

The existing `history` table already has tens of millions of rows. The expensive path is not the number of tags, but dense time-series scans for charts. v2 stores raw points in a Timescale hypertable, keeps latest values in a small `current_values` table, and serves charts from raw data or continuous aggregates depending on the requested range.

Default chart budget is **2000 points per series**.

## Run locally

With Docker:

```bash
cp .env.example .env
docker compose up -d
npm install
npm run migrate
npm run start:dev
```

Default local database:

```text
postgres://postgres:postgres@localhost:5435/drill_cloud_v2
```

Without Docker, use a locally installed PostgreSQL + TimescaleDB and copy `.env.local.example` to `.env`.

See [LOCAL_NO_DOCKER.md](./LOCAL_NO_DOCKER.md).

## API

### Health

```http
GET /health
```

### Ingest array

```http
POST /ingest
Content-Type: application/json
x-api-key: <INGEST_API_KEY if configured>

[
  {
    "edge": "roman",
    "tag": "BN1_24V_Supply_Fault",
    "time": "2026-06-10T22:29:05.098Z",
    "value": 1
  }
]
```

`timestamp` as Unix milliseconds is also accepted instead of `time`.

### Ingest edge values

```http
POST /ingest/roman
Content-Type: application/json

{
  "timestamp": 1781123345098,
  "values": {
    "BN1_24V_Supply_Fault": 1,
    "Term_Drehz_IW_Motor": 1028
  }
}
```

### Current values

```http
GET /current?edge=roman
GET /current?edge=roman&tags=tag1,tag2
```

### History for charts

```http
GET /history?edge=roman&tags=BN1_24V_Supply_Fault&from=2026-04-18T00:00:00Z&to=2026-04-26T00:00:00Z
```

Optional parameters:

- `targetPoints` - 100..2000, default 2000.
- `valueMode` - `avg`, `last`, `first`, `min`, `max` for aggregate layers.
- `tags` - comma-separated tag ids. If omitted, v2 uses `edge_tag`.

The response includes:

- `source`: `raw`, `1m`, `5m`, `1h`, `1d`, or `latest`.
- `series[].points[]`: compact chart points shaped as `{ "t": unixMs, "v": value }`.
- Aggregate points also include `first`, `min`, `max`, `avg`, `last`, `count`.

## Data model

Hot path tables:

- `history_points` - Timescale hypertable partitioned by `time`.
- `current_values` - latest value per `(edge_id, tag_id)`.
- `edge`, `tag`, `edge_tag` - catalog and series membership.

Continuous aggregates:

- `history_1m`
- `history_5m`
- `history_1h`
- `history_1d`

## Production notes

- Use TimescaleDB, not plain PostgreSQL, before running migrations.
- Keep `INGEST_API_KEY` set in production.
- Run chart queries with explicit `tags` whenever the UI knows selected series.
- For historical import from the old database, bulk copy into `history_points`, then refresh continuous aggregates.