### Install Prisma Extension and Dependencies

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Setup

Install the TimescaleDB Prisma extension, Prisma itself, and a driver adapter. This is the initial setup step for integrating TimescaleDB with your Prisma project.

```bash
npm install prisma-extension-timescaledb
npm install -D prisma @prisma/client
npm install @prisma/adapter-pg            # or your preferred driver adapter
```

--------------------------------

### Get Top 10 Buckets by Average Temperature

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/timeBucket-Queries

Retrieves the 10 time buckets with the highest average temperature, with tie-breaking by bucket. This is useful for identifying periods with the highest average values.

```typescript
await prisma.sensorReading.timeBucket({
  bucket: "1 hour", range: { start, end },
  groupBy: ["deviceId"],
  aggregate: { avgTemp: { avg: "temperature" } },
  orderBy: [{ avgTemp: "desc" }, { bucket: "asc" }],   // array = precedence
  limit: 10,
});
```

--------------------------------

### Get Latest 24 Buckets

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/timeBucket-Queries

Retrieves the 24 most recent time buckets, ordered by bucket in descending order. Useful for fetching the latest data points.

```typescript
await prisma.sensorReading.timeBucket({
  bucket: "1 hour", range: { start, end },
  aggregate: { avgTemp: { avg: "temperature" } },
  orderBy: { bucket: "desc" },
  limit: 24,
});
```

--------------------------------

### Prisma Migration and Generation Workflow

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Setup

Execute Prisma commands in the correct order: create the initial migration, generate the Prisma client (which includes TimescaleDB migrations), and then deploy all migrations. This ensures SQL reflects current annotations.

```bash
npx prisma migrate dev --create-only --name init   # 1. Prisma's own CREATE TABLE migration
npx prisma generate                                # 2. our generator writes the timescale migrations + registry
npx prisma migrate deploy                          # 3. apply all migrations
```

--------------------------------

### Initialize Prisma Client with TimescaleDB Extension

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Home

Import necessary modules, initialize PrismaClient with a driver adapter, and extend it with the timescaledb extension using the generated registry.

```typescript
import { PrismaClient } from "./client/client.js";
import { PrismaPg } from "@prisma/adapter-pg";
import { timescaledb } from "prisma-extension-timescaledb";
import { registry } from "./timescale/index.js";

const prisma = new PrismaClient({
  adapter: new PrismaPg({ connectionString: process.env.DATABASE_URL }),
}).$extends(timescaledb(registry));
```

--------------------------------

### Initialize Prisma Client with TimescaleDB Adapter (Manual Config)

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Without-the-Generator

Instantiate the Prisma client with the TimescaleDB adapter and extend it with hypertables configuration. This approach allows for runtime management of policies.

```typescript
const prisma = new PrismaClient({ adapter }).$extends(
  timescaledb({
    hypertables: [{ table: "SensorReading", column: "time", chunkInterval: "1 day" }],
  }),
);
```

--------------------------------

### Querying with Prisma Field Names (Runtime Mapping)

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Without-the-Generator

Demonstrates how to write queries using Prisma's model and field names, even when the underlying database columns are mapped differently. The runtime handles the translation to the actual database names.

```typescript
// You still write Prisma field names; the runtime maps them to ts / device_id:
await prisma.sensorReading.timeBucket({
  bucket: "1 hour",
  range: { start, end },
  where: { deviceId: 1 },
  groupBy: ["deviceId"],
  aggregate: { avgTemp: { avg: "temperature" } },
});
```

--------------------------------

### Environment Variables for Database URLs

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Setup

Define the `DATABASE_URL` and `SHADOW_DATABASE_URL` in your `.env` file. The shadow database URL must point to a TimescaleDB-capable server.

```dotenv
# .env
DATABASE_URL="postgresql://postgres:postgres@localhost:5432/app?schema=public"
SHADOW_DATABASE_URL="postgresql://postgres:postgres@localhost:5432/shadow?schema=public"
```

--------------------------------

### timeBucket Method

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/timeBucket-Queries

Demonstrates the usage of the `timeBucket` method for aggregating sensor readings within specified time buckets, including timezone, origin, and offset configurations.

```APIDOC
## timeBucket

### Description
Aggregates data into time buckets, allowing for timezone-aware alignment and boundary shifting.

### Method
`prisma.sensorReading.timeBucket(options)`

### Parameters
#### Options Object
- **`bucket`** (string) - Required - The size of the time bucket (e.g., "1 day", "1 week").
- **`range`** (object) - Required - An object with `start` and `end` Date properties defining the time range.
  - **`start`** (Date) - Required - The start of the range.
  - **`end`** (Date) - Required - The end of the range.
- **`timezone`** (string) - Optional - The timezone to use for aligning calendar buckets (e.g., "Europe/Stockholm"). Requires a `timestamptz` column. If not provided, buckets align to UTC.
- **`origin`** (Date) - Optional - An instant to align buckets to (e.g., `new Date("2026-01-01T00:00:00Z")`). Requires `timezone`.
- **`offset`** (string) - Optional - An interval to shift bucket boundaries (e.g., "6 hours"). Requires `timezone`.
- **`aggregate`** (object) - Required - An object defining the aggregation to perform.
  - **`[aggregationName]`** (object) - Required - The name of the aggregation (e.g., `avgTemp`).
    - **`[aggregationFunction]`** (string) - Required - The aggregation function (e.g., `avg`).
      - **`[columnName]`** (string) - Required - The column to aggregate (e.g., `temperature`).

### Notes
- The `timezone` parameter requires a `timestamptz` time column. Calendar bucketing is undefined for tz-naive columns.
- `origin` and `offset` can be used together and require a `timezone`.
- `timezone`, `origin`, and `offset` do not currently combine with `gapfill`.

### Request Example
```ts
const rows = await prisma.sensorReading.timeBucket({
  bucket: "1 day",
  range: { start, end },
  timezone: "Europe/Stockholm",
  aggregate: { avgTemp: { avg: "temperature" } },
});
```
```

--------------------------------

### Prisma Migration Commands for TimescaleDB

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Home

Execute these commands to generate and apply your database migrations, including TimescaleDB-specific configurations. The 'prisma generate' step is crucial for emitting the TimescaleDB registry.

```bash
npx prisma migrate dev --create-only --name init   # your normal CREATE TABLE
npx prisma generate                                # emits the timescale migrations + registry
npx prisma migrate deploy                          # applies everything, in the right order
```

--------------------------------

### Prisma Model with Data Compression Policy

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Retention-and-Compression

Configure data compression using the `@timescale.compression` annotation. This converts older chunks to a columnstore format for improved storage efficiency.

```prisma
/// @timescale.hypertable(column: "time", chunkInterval: "1 day")
/// @timescale.compression(after: "7 days", segmentBy: "deviceId", orderBy: "time DESC")
model SensorReading {
  time        DateTime
  deviceId    Int
  temperature Float

  @@id([deviceId, time])
}
```

--------------------------------

### SQL for Enabling Columnstore and Adding Compression Policy

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Retention-and-Compression

The SQL statements executed by the Prisma generator to enable TimescaleDB's columnstore and add a compression policy to a table.

```sql
ALTER TABLE "SensorReading" SET (
  timescaledb.enable_columnstore = true,
  timescaledb.segmentby = '"deviceId"',
  timescaledb.orderby = '"time" DESC'
);
CALL add_columnstore_policy('"SensorReading"', after => INTERVAL '7 days', if_not_exists => TRUE);
```

--------------------------------

### Prisma Client for Data Compression Management

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Retention-and-Compression

Manage data compression policies at runtime using the Prisma client's `$timescale` extension. Add or remove compression policies programmatically.

```typescript
await prisma.$timescale.addCompressionPolicy("SensorReading", {
  after: "7 days",
  segmentBy: "deviceId",            // or ["deviceId", "siteId"]
  orderBy: "time DESC",             // or [{ column: "time", direction: "desc" }]
});
await prisma.$timescale.removeCompressionPolicy("SensorReading");
```

--------------------------------

### Prisma 7 Connection Settings

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Setup

Configure database connection settings in `prisma.config.ts` for Prisma 7. Ensure the `datasource` and `shadowDatabaseUrl` point to TimescaleDB-capable databases.

```typescript
import "dotenv/config";
import { defineConfig } from "prisma/config";

export default defineConfig({
  schema: "schema.prisma",
  migrations: { path: "migrations" },
  datasource: {
    url: process.env["DATABASE_URL"],
    // Must point at a TimescaleDB-capable database — see "Shadow database".
    shadowDatabaseUrl: process.env["SHADOW_DATABASE_URL"],
  },
});
```

--------------------------------

### Gap-Filling Time Buckets with LOCF and Interpolation

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/timeBucket-Queries

Demonstrates gap-filling for time buckets using 'last observed value forward' (locf) and linear interpolation. This is useful for ensuring a complete time series, even when data is missing.

```typescript
const rows = await prisma.sensorReading.timeBucket({
  bucket: "1 hour",
  range: { start, end },
  gapfill: true,
  aggregate: {
    avgTemp: { avg: "temperature", fill: "locf" },        // carry the last value forward
    smooth:  { avg: "temperature", fill: "interpolate" }, // linear interpolation
    raw:     { avg: "temperature" },                      // null in empty buckets
  },
});
// rows: Array<{ bucket: Date; avgTemp: number | null; smooth: number | null; raw: number | null }>
```

--------------------------------

### Prisma Client for Data Retention Management

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Retention-and-Compression

Manage data retention policies at runtime using the Prisma client's `$timescale` extension. Add or remove policies programmatically.

```typescript
await prisma.$timescale.addRetentionPolicy("SensorReading", { dropAfter: "30 days" });
await prisma.$timescale.removeRetentionPolicy("SensorReading");
```

--------------------------------

### Define a Basic Hypertable

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Hypertables

Annotate a model with `@timescale.hypertable` to convert it into a TimescaleDB hypertable. Specify the required `column` for time partitioning and optionally the `chunkInterval`.

```prisma
/// @timescale.hypertable(column: "time", chunkInterval: "1 day")
model SensorReading {
  time        DateTime
  deviceId    Int
  temperature Float

  @@id([deviceId, time])
  @@index([deviceId, time])
}
```

--------------------------------

### Enable Chunk Skipping for Compressed Hypertables

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Hypertables

Use `@timescale.hypertable` with `chunkSkipping` to enable range statistics for a secondary column on compressed hypertables. This allows the planner to skip entire chunks that do not match filters on that column. Requires `timescaledb.enable_chunk_skipping` GUC to be set.

```prisma
/// @timescale.hypertable(column: "time", chunkInterval: "1 day", chunkSkipping: "eventId")
/// @timescale.compression(after: "7 days", segmentBy: "deviceId")
model SensorReading {
  time        DateTime
  deviceId    Int
  eventId     BigInt
  temperature Float

  @@id([deviceId, time])
}
```

--------------------------------

### Define a Hypertable with Time and Space Partitioning

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Annotation-Reference

Use the @timescale.hypertable annotation on a model to define it as a hypertable. Specify the time column, chunk interval, and optionally configure space partitioning using partitionColumn and partitions for hash-based dimensioning.

```prisma
/// @timescale.hypertable(column: "time", chunkInterval: "1 day", partitionColumn: "deviceId", partitions: 4)
model SensorReading {
  time     DateTime
  deviceId Int

  @@id([deviceId, time]) // a partitioning column must be part of the table's PK / unique key
}
```

--------------------------------

### Enable Real-time Aggregation

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Continuous-Aggregates

Configures a continuous aggregate view to use real-time aggregation by setting `materializedOnly: false`. This combines materialized data with recent, not-yet-materialized source rows.

```prisma
/// @timescale.continuousAggregate(source: "SensorReading", bucket: "1 hour", timeColumn: "time", materializedOnly: false)
view SensorHourly { … }
```

--------------------------------

### Basic timeBucket Query with Aggregations

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/timeBucket-Queries

Use this snippet to group sensor readings by a specified time interval and compute aggregate statistics. The result row structure is inferred from the `groupBy` and `aggregate` clauses.

```typescript
const rows = await prisma.sensorReading.timeBucket({
  bucket: "1 hour",
  range: { start, end },          // both bounds required — typing forces you to bound the scan
  where: {
    deviceId: { in: [1, 2] },
    temperature: { gte: 20, lt: 35, not: { equals: 25 } }, // nested `not` is supported
  },
  groupBy: ["deviceId"],
  aggregate: {
    avgTemp: { avg: "temperature" },
    maxTemp: { max: "temperature" },
    samples: { count: "temperature" },
  },
});
// rows: Array<{ bucket: Date; deviceId: number; avgTemp: number; maxTemp: number; samples: number }>
```

--------------------------------

### Manual TimescaleDB Configuration with Model Mapping

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Without-the-Generator

Configure the `timescaledb` extension manually when not using the generator, providing explicit mappings between Prisma model/field names and their corresponding database table/column names.

```typescript
timescaledb({
  hypertables: [{
    model: "SensorReading",                 // Prisma model name (drives prisma.sensorReading.*)
    table: "sensor_readings",               // @@map table
    column: "ts",                           // @map time column
    columns: { deviceId: "device_id" },     // Prisma field -> DB column (where/groupBy/aggregate)
  }],
});
```

--------------------------------

### Configure Shadow Database URL in Prisma

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/blob/main/README.md

Set the `shadowDatabaseUrl` in `prisma.config.ts` to a TimescaleDB-capable database. This is required for `prisma migrate dev` and `migrate reset` to validate migrations against a temporary shadow database.

```typescript
export default defineConfig({
  datasource: {
    url: process.env["DATABASE_URL"],
    shadowDatabaseUrl: process.env["SHADOW_DATABASE_URL"], // a TimescaleDB-capable DB
  },
});
```

--------------------------------

### Manage TimescaleDB Chunks with Prisma

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Management

Use these methods to list, compress, decompress, and drop chunks for a given hypertable. Ensure columnstore is enabled for compression operations. Chunk operations can be bounded by time intervals.

```typescript
const chunks = await prisma.$timescale.showChunks("SensorReading");                 // string[]
const old    = await prisma.$timescale.showChunks("SensorReading", { olderThan: "30 days" });

// Convert a single chunk to/from the columnstore on demand (needs the columnstore enabled).
await prisma.$timescale.compressChunk(chunks[0]);
await prisma.$timescale.decompressChunk(chunks[0]);

// On-demand chunk drop (manual retention) — returns the dropped chunk names.
const dropped = await prisma.$timescale.dropChunks("SensorReading", { olderThan: "30 days" });

// Size, row-count & compression introspection — sizes/counts come back as exact `bigint`.
const bytes  = await prisma.$timescale.hypertableSize("SensorReading");         // bigint
const detail = await prisma.$timescale.hypertableDetailedSize("SensorReading"); // { tableBytes, indexBytes, toastBytes, totalBytes }
const rowest = await prisma.$timescale.approximateRowCount("SensorReading");    // bigint (planner estimate; fast)
const stats  = await prisma.$timescale.compressionStats("SensorReading");       // { totalChunks, compressedChunks, beforeTotalBytes, afterTotalBytes }
```

--------------------------------

### Read Data from a Continuous Aggregate

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Continuous-Aggregates

Demonstrates how to query data from a continuous aggregate view using Prisma's `findMany` method. This is a standard Prisma query on a view.

```typescript
const hourly = await prisma.sensorHourly.findMany({
  where: { bucket: { gte: start }, deviceId: 1 },
  orderBy: { bucket: "desc" },
});
```

--------------------------------

### Toolkit Hyperfunctions for Aggregates

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Aggregates-and-Hyperfunctions

Utilize Toolkit hyperfunctions for advanced aggregations like percentile, rate, delta, and timeWeightedAverage. These require the 'timescaledb_toolkit' extension and handle monotonic counters with reset awareness.

```typescript
aggregate: {
  p95:     { percentile: "latency", p: 0.95 },     // approximate p95 (number)
  reqRate: { rate: "requestsTotal" },              // per-second rate of a reset-aware counter
  reqDelta:{ delta: "requestsTotal" },             // total change over the bucket
  twAvg:   { timeWeightedAverage: "gauge" },       // time-weighted average (LOCF; or method: "linear")
}
```

--------------------------------

### Perform a Time Bucket Query with Aggregation

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Home

Use the timeBucket query method provided by the extension to aggregate data within specified time ranges and group by specified fields. The result type is an array of objects containing the bucket, group-by fields, and aggregated values.

```typescript
const rows = await prisma.sensorReading.timeBucket({
  bucket: "1 hour",
  range: { start, end },
  groupBy: ["deviceId"],
  aggregate: { avgTemp: { avg: "temperature" } },
});
// rows: Array<{ bucket: Date; deviceId: number; avgTemp: number }>
```

--------------------------------

### Enable Chunk Skipping via Prisma Client

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Hypertables

Programmatically enable or disable chunk skipping for a hypertable and column using the Prisma client's TimescaleDB extension methods.

```typescript
await prisma.$timescale.enableChunkSkipping("SensorReading", "eventId");
```

```typescript
await prisma.$timescale.disableChunkSkipping("SensorReading", "eventId");
```

--------------------------------

### Continuous Aggregate Management

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Management

Provides methods to refresh continuous aggregates and manage their associated policies. The methods are typo-safe, checking against registered hypertables and continuous aggregates.

```APIDOC
## Continuous Aggregate Management

### Description
Methods for managing continuous aggregates, including full refreshes, windowed refreshes, and policy management.

### Methods

#### `refreshContinuousAggregate(name: string, options?: { start?: Date, end?: Date })`
Refreshes a continuous aggregate. Supports full refresh or a windowed refresh.

#### `addContinuousAggregatePolicy(name: string, options: { startOffset: string, endOffset: string, scheduleInterval: string })`
Adds a scheduled refresh policy to a continuous aggregate.

#### `removeContinuousAggregatePolicy(name: string)`
Removes the scheduled refresh policy from a continuous aggregate.

### Examples
```ts
// Full refresh
await prisma.$timescale.refreshContinuousAggregate("SensorHourly");

// Windowed refresh
await prisma.$timescale.refreshContinuousAggregate("SensorHourly", { start: new Date(), end: new Date() });

// Add policy
await prisma.$timescale.addContinuousAggregatePolicy("SensorHourly", {
  startOffset: "1 month",
  endOffset: "1 hour",
  scheduleInterval: "1 hour",
});

// Remove policy
await prisma.$timescale.removeContinuousAggregatePolicy("SensorHourly");
```
```

--------------------------------

### Prisma Schema with Table and Column Mapping

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Without-the-Generator

Define a Prisma model with `@@map` for table names and `@map` for column names, along with a TimescaleDB hypertable annotation. The generator uses these mappings to interact with the database.

```prisma
/// @timescale.hypertable(column: "time", chunkInterval: "1 day")
model SensorReading {
  time        DateTime @map("ts")
  deviceId    Int      @map("device_id")
  temperature Float

  @@id([deviceId, time])
  @@map("sensor_readings")
}
```

--------------------------------

### SQL for Enabling Chunk Skipping

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Hypertables

This SQL block is generated to enable chunk skipping for a specific hypertable and column. It sets the `timescaledb.enable_chunk_skipping` GUC locally and calls the `enable_chunk_skipping` function.

```sql
DO $$
BEGIN
  SET LOCAL timescaledb.enable_chunk_skipping = on;
  PERFORM enable_chunk_skipping('"SensorReading"', 'eventId', if_not_exists => TRUE);
END $$;
```

--------------------------------

### Background Jobs Management

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Management

Inspect and control TimescaleDB background jobs, including retention, compression, and continuous aggregate refresh policies.

```APIDOC
## Background Jobs & Policies

### Description
Manages TimescaleDB background jobs for policies like retention, compression, and continuous aggregate refresh. These helpers allow inspection and control over job execution.

### Methods

#### `listJobs(tableName?: string)`
- **Description**: Inspect background jobs. Filter by model (hypertable or cagg) or omit for all jobs.
- **Parameters**:
  - **tableName** (string) - Optional - The name of the hypertable or cagg to filter jobs by.
- **Returns**: An array of job objects, each containing `jobId`, `procName`, `scheduleInterval`, `scheduled`, `config`, `nextStart`, etc.

#### `jobStats(tableName?: string)`
- **Description**: Get statistics for background jobs.
- **Parameters**:
  - **tableName** (string) - Optional - The name of the hypertable or cagg to filter job stats by.
- **Returns**: Job statistics including last/next run, status, and total runs/successes/failures.

#### `jobErrors(tableName?: string)`
- **Description**: Retrieve recent failures for background jobs.
- **Parameters**:
  - **tableName** (string) - Optional - The name of the hypertable or cagg to filter job errors by.
- **Returns**: An array of recent job failures, including SQLSTATE and message, newest first.

#### `alterJob(jobId: number, options: { scheduled?: boolean, scheduleInterval?: string, nextStart?: string, maxRetries?: number, retryPeriod?: string, maxRuntime?: string, config?: object })`
- **Description**: Control a background job by its numeric ID. Allows pausing, resuming, rescheduling, and modifying other job parameters.
- **Parameters**:
  - **jobId** (number) - Required - The ID of the job to alter.
  - **options** (object) - Required - An object containing the fields to update (e.g., `scheduled`, `scheduleInterval`).

#### `runJob(jobId: number)`
- **Description**: Trigger a background job to run immediately.
- **Parameters**:
  - **jobId** (number) - Required - The ID of the job to run.

#### `deleteJob(jobId: number)`
- **Description**: Remove a custom background job.
- **Parameters**:
  - **jobId** (number) - Required - The ID of the job to delete.

### Request Examples
```ts
// Inspect jobs for a specific table
const jobs = await prisma.$timescale.listJobs("SensorReading");

// Get job statistics
const jstats = await prisma.$timescale.jobStats("SensorReading");

// Get recent job errors
const errs = await prisma.$timescale.jobErrors("SensorReading");

// Pause a job
const { jobId } = jobs[0];
await prisma.$timescale.alterJob(jobId, { scheduled: false });

// Resume and reschedule a job
await prisma.$timescale.alterJob(jobId, { scheduled: true, scheduleInterval: "2 days" });

// Run a job now
await prisma.$timescale.runJob(jobId);

// Delete a custom job
await prisma.$timescale.deleteJob(jobId);
```
```

--------------------------------

### Manage Compression Policies

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Management

Add or remove compression policies for hypertables. Compression policies define the criteria for compressing older data segments.

```typescript
await prisma.$timescale.addCompressionPolicy("SensorReading", { after: "7 days", segmentBy: "deviceId", orderBy: "time DESC" });
await prisma.$timescale.removeCompressionPolicy("SensorReading");
```

--------------------------------

### Manage Continuous Aggregate Policies

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Management

Add or remove scheduled refresh policies for continuous aggregates. Policies define the refresh window and schedule interval.

```typescript
await prisma.$timescale.addContinuousAggregatePolicy("SensorHourly", {
  startOffset: "1 month", endOffset: "1 hour", scheduleInterval: "1 hour",
});
await prisma.$timescale.removeContinuousAggregatePolicy("SensorHourly");
```

--------------------------------

### Prisma Model with Data Retention Policy

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Retention-and-Compression

Define a data retention policy using the `@timescale.retention` annotation on a Prisma model. This automatically drops old chunks based on the `dropAfter` interval.

```prisma
/// @timescale.hypertable(column: "time", chunkInterval: "1 day")
/// @timescale.retention(dropAfter: "30 days")
model SensorReading {
  time        DateTime
  deviceId    Int
  temperature Float

  @@id([deviceId, time])
}
```

--------------------------------

### Refresh a Continuous Aggregate On Demand

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Continuous-Aggregates

Shows how to manually trigger a refresh for a continuous aggregate. This can be used to materialize new rows immediately after data changes or in testing scenarios.

```typescript
await prisma.$timescale.refreshContinuousAggregate("SensorHourly");              // full refresh
await prisma.$timescale.refreshContinuousAggregate("SensorHourly", { start, end }); // window
```

--------------------------------

### Stats Aggregation (1-D Summary)

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Aggregates-and-Hyperfunctions

Compute a 1-D statistical summary using the 'stats' hyperfunction, returning an object with average, sum, variance, skewness, and kurtosis. Note that stddev and variance are population statistics.

```typescript
const [row] = await prisma.sensorReading.timeBucket({
  bucket: "1 hour",
  range: { start, end },
  groupBy: ["deviceId"],
  aggregate: { temp: { stats: "temperature" } },
});
// row.temp -> { average, sum, numVals, stddev, variance, skewness, kurtosis }
```

--------------------------------

### Nested Relation Filters in TimeBucket Queries

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/timeBucket-Queries

Demonstrates deep nesting of relation filters to query data based on indirect relationships. This allows filtering based on fields several relations away.

```typescript
// readings whose device also has a reading over 30° (Reading → device → readings)
where: { device: { is: { readings: { some: { temperature: { gt: 30 } } } } } }
```

--------------------------------

### Retention and Compression Policies

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Management

Manages data retention and compression policies for hypertables. These policies help optimize storage and query performance.

```APIDOC
## Retention and Compression Policies

### Description
Methods for managing data retention and compression policies on hypertables.

### Methods

#### `addRetentionPolicy(tableName: string, options: { dropAfter: string })`
Adds a retention policy to a table, specifying how long data should be retained.

#### `removeRetentionPolicy(tableName: string)`
Removes the retention policy from a table.

#### `addCompressionPolicy(tableName: string, options: { after: string, segmentBy?: string, orderBy?: string })`
Adds a compression policy to a table, defining conditions for data compression.

#### `removeCompressionPolicy(tableName: string)`
Removes the compression policy from a table.

### Examples
```ts
// Add retention policy
await prisma.$timescale.addRetentionPolicy("SensorReading", { dropAfter: "30 days" });

// Remove retention policy
await prisma.$timescale.removeRetentionPolicy("SensorReading");

// Add compression policy
await prisma.$timescale.addCompressionPolicy("SensorReading", {
  after: "7 days",
  segmentBy: "deviceId",
  orderBy: "time DESC",
});

// Remove compression policy
await prisma.$timescale.removeCompressionPolicy("SensorReading");
```
```

--------------------------------

### Exact Aggregate Types

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Aggregates-and-Hyperfunctions

Configure aggregates to return exact types like bigint or string to avoid precision loss. Use 'bigint' for exact integers and 'string' for exact decimals. Disallowed combinations result in compile errors.

```typescript
aggregate: {
  total:   { sum: "deviceId", as: "bigint" },    // → bigint  (exact integer)
  samples: { count: "deviceId", as: "bigint" },  // → bigint  (no overflow past ~2.1B rows)
  exact:   { avg: "temperature", as: "string" }, // → string  (exact decimal)
  fast:    { sum: "temperature" },               // → number  (default)
}
```

--------------------------------

### Define Hierarchical Continuous Aggregates

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Continuous-Aggregates

Illustrates creating hierarchical continuous aggregates where one cagg sources data from another. The `SensorDaily` view rolls up data from the `SensorHourly` view.

```prisma
/// @timescale.continuousAggregate(source: "SensorReading", bucket: "1 hour", timeColumn: "time")
view SensorHourly {
  bucket   DateTime /// @timescale.bucket
  deviceId Int      /// @timescale.groupBy
  avgTemp  Float    /// @timescale.aggregate(fn: "avg", column: "temperature")
  @@unique([deviceId, bucket])
}

/// Roll the hourly cagg up into a daily one (source is SensorHourly, not the hypertable):
/// @timescale.continuousAggregate(source: "SensorHourly", bucket: "1 day", timeColumn: "bucket")
view SensorDaily {
  day      DateTime /// @timescale.bucket
  deviceId Int      /// @timescale.groupBy
  avgTemp  Float    /// @timescale.aggregate(fn: "avg", column: "avgTemp")
  @@unique([deviceId, day])
}
```

--------------------------------

### Relation Filters for TimeBucket Queries

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/timeBucket-Queries

Filter hypertable data based on fields of related models using Prisma's `some`, `none`, `every`, `is`, and `isNot` operators. These are compiled into SQL `EXISTS` subqueries.

```typescript
where: {
  device: { is: { active: true } },      // to-one: the related device is active
  tags:   { some: { label: "prod" } },   // to-many: has ≥1 matching tag
  alerts: { every: { resolved: true } }, // to-many: all resolved (vacuously true if none)
  owner:  { isNot: null },               // relation exists
}
```

--------------------------------

### Candlestick Aggregation (OHLC)

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Aggregates-and-Hyperfunctions

Generate candlestick (OHLC) data objects per bucket using the 'candlestick' hyperfunction. This requires both price and volume columns and is not combinable with gapfill.

```typescript
// on a Trade model with `time`, `price`, and `volume` columns:
const candles = await prisma.trade.timeBucket({
  bucket: "1 day",
  range: { start, end },
  aggregate: { ohlc: { candlestick: "price", volume: "volume" } },
});
// candles[i].ohlc -> { open, high, low, close, vwap }
```

--------------------------------

### Standard Aggregates in Prisma

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Aggregates-and-Hyperfunctions

Defines common aggregate functions like average, standard deviation, and count with distinct values. Use for basic statistical analysis on numeric columns or counting distinct entries.

```typescript
aggregate: {
  avgTemp: { avg: "temperature" },
  spread:  { stddev: "temperature" },             // stddevPop / variance / varPop too — numeric like avg
  devices: { count: "deviceId", distinct: true }, // count(DISTINCT deviceId)
  dist:    { histogram: "temperature", min: 0, max: 100, buckets: 10 },
}
```

--------------------------------

### Define a Continuous Aggregate View

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Continuous-Aggregates

Defines a continuous aggregate view named SensorHourly that aggregates sensor readings by hour. It specifies the source hypertable, bucket interval, time column, and an optional refresh policy.

```prisma
/// @timescale.continuousAggregate(source: "SensorReading", bucket: "1 hour", timeColumn: "time", refresh: { startOffset: "1 month", endOffset: "1 hour", scheduleInterval: "1 hour" })
view SensorHourly {
  bucket   DateTime /// @timescale.bucket          (exactly one)
  deviceId Int      /// @timescale.groupBy
  avgTemp  Float    /// @timescale.aggregate(fn: "avg", column: "temperature")

  @@unique([deviceId, bucket])   // Prisma 7 disallows @@id on views
}
```

--------------------------------

### Time Bucket Query with Time Zone

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/timeBucket-Queries

Use this snippet to perform time bucket aggregations aligned to a specific time zone. The `timezone` parameter ensures that calendar-based buckets (day, week, month) respect the DST rules of the specified zone. Requires a `timestamptz` time column.

```typescript
const rows = await prisma.sensorReading.timeBucket({
  bucket: "1 day",
  range: { start, end },
  timezone: "Europe/Stockholm", // day/week/month buckets align to this zone's calendar (DST-aware)
  // origin: new Date("2026-01-01T00:00:00Z"), // align buckets to an instant
  // offset: "6 hours",                        // shift bucket boundaries by an interval
  aggregate: { avgTemp: { avg: "temperature" } },
});
```

--------------------------------

### Earliest and Latest Value Aggregates

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Aggregates-and-Hyperfunctions

Retrieves the value of a column at the earliest or latest timestamp within each bucket. Useful for tracking values like 'open' or 'close' prices over time intervals.

```typescript
aggregate: {
  open:  { first: "temperature" }, // value at the earliest time in the bucket
  close: { last: "temperature" },  // value at the latest time
  tag:   { last: "label" },        // any column type — keeps the column's type
}
```

--------------------------------

### Inspect TimescaleDB Background Jobs

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Management

Retrieves information about TimescaleDB background jobs, filtering by model or for all jobs. Provides details like job ID, name, schedule, and status.

```typescript
// Inspect — filter by model (a hypertable or a cagg), or omit for all jobs.
const jobs  = await prisma.$timescale.listJobs("SensorReading");   // [{ jobId, procName, scheduleInterval, scheduled, config, nextStart, ... }]
const jstats = await prisma.$timescale.jobStats("SensorReading");  // last/next run, status, totalRuns/Successes/Failures (bigint)
const errs  = await prisma.$timescale.jobErrors("SensorReading");  // recent failures (SQLSTATE + message), newest first
```

--------------------------------

### Adding Data to a Hypertable

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Setup

Insert data into a TimescaleDB hypertable using the standard Prisma API. The extension handles routing rows into the correct time chunks automatically.

```typescript
await prisma.sensorReading.create({
  data: { time: new Date(), deviceId: 1, temperature: 21.5 },
});

await prisma.sensorReading.createMany({
  data: [
    { time: new Date("2026-06-15T10:05:00Z"), deviceId: 1, temperature: 20 },
    { time: new Date("2026-06-15T10:25:00Z"), deviceId: 1, temperature: 22 },
  ],
});
```

--------------------------------

### Control TimescaleDB Background Jobs

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Management

Allows pausing, resuming, rescheduling, or running TimescaleDB background jobs by their ID. Changes are applied to specific fields like 'scheduled' or 'scheduleInterval'.

```typescript
// Control — by numeric job id (from listJobs).
const { jobId } = jobs[0];
await prisma.$timescale.alterJob(jobId, { scheduled: false });                            // pause
await prisma.$timescale.alterJob(jobId, { scheduled: true, scheduleInterval: "2 days" }); // resume + reschedule
await prisma.$timescale.runJob(jobId);                                                    // run now, synchronously
await prisma.$timescale.deleteJob(jobId);                                                 // remove a custom job
```

--------------------------------

### Refresh Continuous Aggregates

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Management

Use to perform a full refresh or a windowed refresh of a continuous aggregate. The function automatically retries transient contention errors.

```typescript
await prisma.$timescale.refreshContinuousAggregate("SensorHourly");                 // full refresh
await prisma.$timescale.refreshContinuousAggregate("SensorHourly", { start, end }); // window
```

--------------------------------

### Set Chunk Interval for Hypertables

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Management

Changes the time-dimension chunk interval for a live hypertable. Affects only newly created chunks.

```typescript
await prisma.$timescale.setChunkInterval("SensorReading", "6 hours");
```

--------------------------------

### Prisma Schema Definition for TimescaleDB

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Home

Define your Prisma schema using the TimescaleDB generator and datasource. Use custom directives like @timescale.hypertable and @timescale.continuousAggregate to configure TimescaleDB features.

```prisma
// schema.prisma
generator client {
  provider        = "prisma-client"
  output          = "./client"
  previewFeatures = ["views"]
}

generator timescaledb {
  provider = "prisma-extension-timescaledb"   // emits reset-safe migrations + a typed registry
  output   = "./timescale"
}

datasource db {
  provider = "postgresql"
}

/// @timescale.hypertable(column: "time", chunkInterval: "1 day")
model SensorReading {
  time        DateTime
  deviceId    Int
  temperature Float

  @@id([deviceId, time])
  @@index([deviceId, time])
}

/// @timescale.continuousAggregate(source: "SensorReading", bucket: "1 hour", timeColumn: "time", refresh: { startOffset: "1 month", endOffset: "1 hour", scheduleInterval: "1 hour" })
view SensorHourly {
  bucket   DateTime /// @timescale.bucket
  deviceId Int      /// @timescale.groupBy
  avgTemp  Float    /// @timescale.aggregate(fn: "avg", column: "temperature")

  @@unique([deviceId, bucket])   // Prisma 7 disallows @@id on views
}
```

--------------------------------

### setChunkInterval

Source: https://github.com/krister-johansson/prisma-extension-timescaledb/wiki/Management

Changes the time-dimension chunk interval of a live hypertable. This affects only chunks created after the call, and existing chunks retain their interval.

```APIDOC
## setChunkInterval

### Description
Changes the time-dimension chunk interval of a **live** hypertable. It affects only chunks created **after** the call — existing chunks keep their interval, so it never rewrites data.

### Method
`prisma.$timescale.setChunkInterval(tableName: string, interval: string)`

### Parameters
#### Path Parameters
- **tableName** (string) - Required - The name of the hypertable to modify.
- **interval** (string) - Required - The new chunk interval (e.g., "6 hours").

### Request Example
```ts
await prisma.$timescale.setChunkInterval("SensorReading", "6 hours");
```
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.