# TimescaleDB

> Navree's default database: why it is the default, running it locally and in production, which tables to make hypertables (Navree's own included), keys and ids, chunks, compression, retention, continuous aggregates and time-bucket queries.

TimescaleDB is Navree's default database: PostgreSQL with an extension for time-series data. Its central idea is the **hypertable**, a table PostgreSQL stores as many chunks, each holding one span of time. Queries over a recent window read only the chunks they need, old chunks can be compressed to a fraction of their size or dropped whole, and continuous aggregates keep rollups (events per day, revenue per hour) up to date incrementally. To your queries a hypertable is an ordinary table.

You decide in your migrations which tables become hypertables, with the [schema builder](https://navree.com/docs/database/schema-builder.md). The same migration runs on plain PostgreSQL, MySQL, MariaDB and SQLite, which get a regular table with the right indexes and a notice.

## Why TimescaleDB is the default

- **It is PostgreSQL.** Everything Navree does on PostgreSQL works the same: the [unit of work](https://navree.com/docs/database/unit-of-work.md) and its `navree.actor` setting for triggers, row-level security for [workspaces](https://navree.com/docs/workspaces/isolation.md), JSONB, `FOR UPDATE` and `SKIP LOCKED` for the queue, full-text search. Your business records are regular tables, as they would be on any PostgreSQL server.
- **Every app keeps time series.** The audit trail, webhook deliveries, job history and monitoring events grow with every request, are read by time range, and expire. As regular tables they slow down as they grow, and deleting old rows leaves the table bloated. As hypertables, recent data stays fast, old data compresses, and expired data is dropped a chunk at a time. Navree's own time-series tables [are hypertables](#navrees-own-tables) from the start.
- **One database.** Time series live next to the records they describe, in the same transactions and joins, so there is no second store to run, back up and keep in step.
- **Nothing is locked in.** A table is regular until a migration declares it a hypertable, and the migration also runs where TimescaleDB isn't installed. Your tests run the production migrations on SQLite.

## Running it

**A new app** is set up for TimescaleDB. `navree new` writes a PostgreSQL URL into `config/app.toml`, turns on the `postgres` feature of the `navree` dependency, and adds a `dock.toml` whose `postgres` service runs TimescaleDB:

```sh
navree new my-shop
cd my-shop
navree dock up               # TimescaleDB on 127.0.0.1:5432
navree dev
```

`navree new my-shop --database postgres` sets up plain PostgreSQL instead, `--database mysql` MySQL or MariaDB, and `--database sqlite` a SQLite file with nothing to install.

**An existing app** needs the `postgres` feature of the facade and a server. [Dock](https://navree.com/docs/tools/dock.md#postgresql-and-timescaledb) runs one for development:

```sh
navree dock:add postgres     # TimescaleDB 2.31 on PostgreSQL 17
navree dock up
```

**In production**, point `APP_DATABASE__URL` at a TimescaleDB server: a managed TimescaleDB service, or PostgreSQL 16 to 18 with the `timescaledb` package installed and `shared_preload_libraries = 'timescaledb'`. The first migration that declares a hypertable runs `CREATE EXTENSION IF NOT EXISTS timescaledb` when the database user may, or ask your administrator to run it once. Then make the extension a requirement, so a server without it stops the migration instead of falling back to regular tables:

```sh
export APP_DATABASE__URL=postgres://shop:secret@db.internal:5432/shop
export APP_SCHEMA__TIMESCALEDB=require
```

`navree migrate` prints a notice for every table it couldn't make a hypertable. See [the extension](#the-extension) for the three modes.

## Which tables to convert

Make a table a hypertable when it is:

- **append-mostly**: rows are inserted and rarely updated or deleted one by one;
- **keyed by time**: every row has a timestamp, and queries ask for a time range (the last hour, this month);
- **large, or growing without end**: millions of rows, most of them old.

Good candidates: request and application logs, events and analytics, the audit trail, metrics and readings, notification and webhook delivery history, job runs. Keep as regular tables the records your app updates in place and points foreign keys at: users, workspaces, companies, deals, orders, settings. A hypertable is a poor fit for a table referenced by other tables' foreign keys, or one whose rows change state for months.

## Navree's own tables

Navree's modules make their time-series tables hypertables in their own migrations, so an app on TimescaleDB gets them without writing anything:

| Module | Table | Time column | Chunks of | Kept for (setting) |
|---|---|---|---|---|
| Audit | `audit_logs` | `created_at` | 7 days | 365 days (`audit.retention_days`) |
| Webhooks | `webhook_deliveries`, `webhook_attempts` | `created_at` | 1 day | 30 days (`webhooks.retain_days`) |
| Fleet | `fleet_jobs` | `finished_at`, Unix milliseconds | 1 hour | 60 minutes (`fleet.trim.recent_minutes`) |
| Fleet | `fleet_metrics` | `at`, Unix milliseconds | 1 day | 7 days (`fleet.trim.metrics_days`) |
| Scheduling | `schedule_runs` | `tick` | 1 day | 7 days |
| Lighthouse | `lighthouse_samples`, `lighthouse_occurrences` | `at` | 1 day | 14 days (`lighthouse.retention_days`) |
| Lighthouse | `lighthouse_aggregates` | `bucket` | 1 day | 14 days (`lighthouse.retention_days`) |

On TimescaleDB their primary keys become `(id, <time column>)` (the scheduler's `(task, tick)` already has its time), and rows already there move into chunks when the migration runs, so an existing app converts on upgrade. That locks each table while its rows move: on a large audit log, run the upgrade in a quiet window. On every other database the tables stay as they were, with an index on their time column, and the migrations report a notice.

Their prune commands (`audit:prune`, `webhooks:prune`, `lighthouse:prune`, Fleet's trimming and the scheduler's hourly cleanup) drop the chunks that lie entirely before the cutoff and delete only the rows of the chunk that straddles it ([pruning in code](#pruning-in-code) shows the helper they use). The retention stays in your configuration, so changing `audit.retention_days` needs no migration.

Webhook attempts show the [key rule](#keys-and-ids) at work: a hypertable's key includes its time column, so no foreign key can point at a delivery's `id` alone. Attempts reference their endpoint instead (deleting an endpoint still deletes its deliveries and attempts), and `webhooks:prune` deletes a delivery's attempts with it.

Every other table Navree creates (users, workspaces, the queue's `jobs` and `failed_jobs`, Lighthouse's issues, webhook endpoints) stays a regular table: their rows are updated in place, picked one at a time, or referenced by foreign keys.

To size the chunks for your traffic, or add policies, change Navree's tables in a migration of your own:

```rust title="src/modules/ops/migrations/m20261101_090000_tune_time_series.rs"
use navree::schema::prelude::*;

pub struct Migration;

impl navree::schema::Migration for Migration {
    async fn up(&self, schema: &Schema<'_>) -> SchemaResult {
        // A busy app: a day per audit chunk, from the next chunk on.
        schema.set_chunk_interval("audit_logs", "1 day").await?;
        // Compress Lighthouse's samples after two days.
        let mut samples = Compression::new();
        samples
            .segment_by(["kind", "name"])
            .order_by("at DESC")
            .after("2 days");
        schema.compress("lighthouse_samples", &samples).await
    }

    async fn down(&self, schema: &Schema<'_>) -> SchemaResult {
        schema.remove_compression_policy("lighthouse_samples").await
    }
}
```

On a database without TimescaleDB both calls report a notice and change nothing. A retention policy you add runs on its own schedule, next to the prune commands: keep its interval no shorter than the module's setting.

With `lighthouse.database_url`, Lighthouse keeps its tables in a database of its own. It makes the same three tables hypertables there when it connects at boot, if that database runs TimescaleDB.

## Creating hypertables

`create_hypertable` creates a table partitioned by a time column:

```rust title="src/modules/analytics/migrations/m20261027_090000_create_events_table.rs"
use navree::schema::prelude::*;

pub struct Migration;

impl navree::schema::Migration for Migration {
    async fn up(&self, schema: &Schema<'_>) -> SchemaResult {
        schema
            .create_hypertable("events", "occurred_at", |t| {
                t.uuid_primary();
                t.foreign_uuid("workspace_id").index();
                t.string("kind");
                t.jsonb("payload");
                t.timestamp_tz("occurred_at");

                let events = t.hypertable("occurred_at");
                events.chunk_interval("1 day").retain_for("90 days");
                events
                    .compress()
                    .segment_by(["workspace_id"])
                    .order_by("occurred_at DESC")
                    .after("7 days");
            })
            .await
    }

    async fn down(&self, schema: &Schema<'_>) -> SchemaResult {
        schema.drop_if_exists("events").await
    }
}
```

Inside the closure, `t.hypertable("occurred_at")` returns the hypertable to configure. You can also declare it inside an ordinary `create`; `schema.create(..., |t| { ...; t.hypertable("occurred_at"); })` does the same as `create_hypertable`. On TimescaleDB this runs:

```sql
CREATE TABLE "events" (... , PRIMARY KEY ("id", "occurred_at"));
SELECT create_hypertable('events', by_range('occurred_at', INTERVAL '1 day'));
CREATE INDEX "events_workspace_id_index" ON "events" ("workspace_id");
ALTER TABLE "events" SET (timescaledb.compress, timescaledb.compress_segmentby = 'workspace_id', timescaledb.compress_orderby = 'occurred_at DESC');
SELECT add_compression_policy('events', INTERVAL '7 days');
SELECT add_retention_policy('events', INTERVAL '90 days');
```

The time column may be a `timestamp_tz` (the usual choice), a `timestamp`, a `date`, or an integer (milliseconds since the epoch, a sequence). An integer time column takes integer chunk intervals (`chunk_interval("86400000")`); a temporal one takes intervals such as `"1 day"`, `"12 hours"` or `"2 weeks"`. A mismatch, a missing time column or an interval that doesn't parse fails before any SQL runs.

### Converting an existing table

An existing table becomes a hypertable with `convert_to_hypertable`:

```rust
let mut audit = Hypertable::on("created_at");
audit
    .chunk_interval("1 week")
    .migrate_data()
    .retain_for("365 days");
audit.compress().segment_by(["workspace_id"]).after("30 days");
schema.convert_to_hypertable("visits", &audit).await?;
```

A short description can be passed as it is built: `schema.convert_to_hypertable("visits", Hypertable::on("created_at").migrate_data())`. A table that has rows needs `.migrate_data()`, which moves them into chunks and locks the table while it runs; without it, the conversion stops and says so. `schema.is_hypertable("visits")` tells whether a table already is one, which keeps a conversion you might run twice from doing anything the second time.

## Keys and ids

TimescaleDB enforces uniqueness inside each chunk, so **every primary key and unique index of a hypertable must include its time column** (and its partition column, [below](#space-partitioning)).

- A primary key that lacks them is widened: `uuid_primary()` on `events` becomes `PRIMARY KEY (id, occurred_at)`, and the migration reports it. The key is widened on every database, so a migration gives the same keys on SQLite in your tests as on TimescaleDB in production. (SQLite keeps an `id()` auto-increment key alone, as it must, and says so.) With `.keys(KeyStrategy::Reject)` on the hypertable, or `schema.hypertable_keys = "reject"` for every hypertable, the migration fails instead and tells you the key to declare.
- A unique index that lacks them can't be widened without changing what it means, so it is an error on every database: add the time column to it, or drop it.
- Converting an existing table rebuilds its primary key the same way, unless other tables' foreign keys point at it; then the conversion stops and names them. Point those foreign keys at a regular table the rows belong to (an endpoint, a device, a workspace), as [webhook attempts](#navrees-own-tables) do, and delete the dependent rows when you prune.

Use **time-ordered ids** for time-series tables: UUID v7 (what Navree generates for `Uuid` keys) or ULID (`ulid_primary()`). Their leading bits are a timestamp, so new rows land at the end of the index, ids sort by creation time, and a lookup by `id` alone still uses the `(id, occurred_at)` key. Two ways to key a time-series table:

| | Declares | Fits |
|---|---|---|
| A time-ordered id | `t.uuid_primary()` (widened to `(id, occurred_at)`) | rows you fetch or link by id: events, audit entries, deliveries |
| A natural key | `t.primary(["device_id", "taken_at"])` | readings with one value per source and instant |

Your code keeps finding rows by `id`: `find_by_id`, updates and deletes filter on `id` and work the same on a hypertable.

## Chunks and partitions

TimescaleDB's default chunk spans 7 days. Size chunks so the indexes of the most recent chunk fit in about a quarter of the server's memory: a table receiving millions of rows a day wants `chunk_interval("1 day")` or less, a quiet one can keep the default. `schema.set_chunk_interval("events", "6 hours")` changes it for chunks created from then on.

`schema.time_dimension("events")` reads a hypertable's time column, its type and its chunk interval from TimescaleDB's catalog, and gives `None` for a regular table:

```rust
if let Some(time) = Schema::new(db.conn()).time_dimension("events").await? {
    println!("{} ({}), chunks of {}", time.column, time.column_type, time.chunk_interval);
}
```

### Space partitioning

`partition_by("workspace_id", 4)` also splits each time span into 4 partitions by hashing a column. It helps when the data is spread over several disks, and rarely on a single server. A partitioned hypertable's keys must include the partition column too, and the builder widens them to `(id, occurred_at, workspace_id)`.

## Compression

```rust
events
    .compress()
    .segment_by(["workspace_id"])
    .order_by("occurred_at DESC")
    .after("7 days");
```

Compression (TimescaleDB's columnstore) stores old chunks by column, often at a tenth of their size. `segment_by` lists the columns your queries filter on: rows are grouped by them, so a query for one workspace reads one segment. Pick columns with many rows per value (a workspace, a device), not unique ones. `order_by` orders rows inside a segment, usually the time column descending. `after` adds a policy that compresses chunks once they are that old; leave the recent chunks, which still receive writes, uncompressed. On an existing hypertable use `schema.compress("events", &compression)`, `add_compression_policy` and `remove_compression_policy`.

## Retention

`retain_for("90 days")` adds a policy that drops chunks older than 90 days, whole, without the cost of a `DELETE`. On an existing hypertable use `schema.add_retention_policy("events", "90 days")` and `remove_retention_policy`.

### Pruning in code

When the retention period is a setting, or other rows go with the old ones, prune from code: a command or a [scheduled task](https://navree.com/docs/digging-deeper/scheduling.md). `navree::db::prune_time_series` deletes the rows older than a cutoff and returns how many. On a hypertable partitioned by that column it drops the chunks that lie entirely before the cutoff and deletes only the rows of the chunk that straddles it; on any other table it is one `DELETE`:

```rust title="src/modules/analytics/prune.rs"
use chrono::{TimeDelta, Utc};
use navree::db::{Db, prune_time_series};

/// Deletes the events older than `days`; returns how many.
pub async fn prune_events(db: &Db, days: i64) -> Result<u64, sea_orm::DbErr> {
    let cutoff = Utc::now() - TimeDelta::days(days);
    db.transaction(|uow| async move {
        let tx = uow.tx().await?;
        prune_time_series(&*tx, "events", "occurred_at", cutoff).await
    })
    .await
}
```

The cutoff has the time column's type: a `DateTime<Utc>` for timestamps, an `i64` for integer time columns (`TimePoint::Integer`). Navree's own prune commands use this helper.

## Reorder policies

`reorder_by("events_workspace_id_occurred_at_index")` rewrites each chunk in the order of that index once it stops receiving writes, so range queries on that index read neighbouring rows. Declare the index in the same migration. On an existing hypertable: `add_reorder_policy` and `remove_reorder_policy`.

## Continuous aggregates

A continuous aggregate is a materialized view over a hypertable that TimescaleDB keeps up to date by refreshing only the time ranges that changed:

```rust
schema
    .create_continuous_aggregate(
        ContinuousAggregate::over("events_daily", "events")
            .time_bucket("1 day", "occurred_at", "day")
            .group_by("workspace_id")
            .aggregate("count(*)", "total")
            .aggregate("count(DISTINCT kind)", "kinds")
            .where_raw("kind <> 'heartbeat'")
            .refresh(
                RefreshPolicy::every("1 hour")
                    .start_offset("3 days")
                    .end_offset("1 hour"),
            ),
    )
    .await?;
```

The refresh policy refreshes the window from `start_offset` ago to `end_offset` ago every `every`; leave the most recent span (`end_offset`) to be computed when read. `.materialized_only(false)` makes queries add the newest raw rows to the materialized ones. You can also give the query as SQL, `ContinuousAggregate::new("events_daily", "SELECT time_bucket(...) ...")`; SQL that calls `time_bucket` only runs where TimescaleDB is installed, and elsewhere the migration stops and suggests the described form.

`drop_continuous_aggregate`, `add_continuous_aggregate_policy`, `remove_continuous_aggregate_policy` and `refresh_continuous_aggregate` manage them later. TimescaleDB refuses to refresh inside a transaction, so a migration that refreshes returns `false` from `within_transaction`.

A continuous aggregate is a view, so you read it like a table: `table("events_daily")` in the [query builder](#time-bucket-queries).

## Time-bucket queries

The query builder groups rows into time buckets with TimescaleDB's `time_bucket`: the start of the bucket each row's time falls in. A chart of the last day, one point per 15 minutes:

```rust
use chrono::{TimeDelta, Utc};
use navree::orm::{Expr, col, table};

let since = Utc::now() - TimeDelta::days(1);
let points = table("readings")
    .select_time_bucket("15 minutes", "taken_at", "bucket")
    .select_expr(Expr::avg(col("celsius")), "average")
    .select_expr(Expr::count_all(), "samples")
    .where_(col("taken_at").gte(since))
    .group_by(["bucket"])
    .order_by("bucket")
    .get(&db)
    .await?;
for point in &points {
    let bucket: chrono::DateTime<Utc> = point.get("bucket")?;
    let average: f64 = point.get("average")?;
    println!("{bucket}: {average:.1} °C");
}
```

Buckets start at Monday, 2000-01-03, plus whole widths, as TimescaleDB's do; `Expr::time_bucket_from("1 day", col("taken_at"), origin)` aligns them to another origin, such as midnight in a time zone. Widths are intervals: `"30 seconds"`, `"5 minutes"`, `"1 hour"`, `"1 day"`, `"1 week"`, `"1 month"`.

`first` and `last` are TimescaleDB's aggregates for the value at the earliest and latest time of each group, such as a day's opening and closing reading:

```rust
let days = table("readings")
    .select_time_bucket("1 day", "taken_at", "day")
    .select_first("celsius", "taken_at", "opening")
    .select_last("celsius", "taken_at", "closing")
    .group_by(["day"])
    .order_by("day")
    .get(&db)
    .await?;
```

On SQLite and MySQL, where your tests run, the builder computes fixed-width buckets (from microseconds to weeks) itself, with the same alignment, so the same query returns the same buckets. Month and year buckets, `first` and `last` need TimescaleDB, and elsewhere the query fails with an error naming what is missing.

## One migration, every database

A migration that declares a hypertable runs everywhere:

| Database | Gets |
|---|---|
| TimescaleDB | A hypertable with its dimensions and policies; continuous aggregates. |
| PostgreSQL without TimescaleDB | A regular table with an index on `(occurred_at DESC)` (and `(partition, occurred_at DESC)`), unless one already starts with them; continuous aggregates as plain views bucketing with `date_bin`. |
| MySQL and MariaDB | The same indexes; views bucketing with epoch arithmetic. |
| SQLite | The same indexes; views bucketing with `strftime`. |

Each fallback reports a notice naming what it skipped:

```text
notice: SQLite does not support TimescaleDB: `events` is a regular table with an index on `occurred_at`; its compression and retention (90 days) policies are skipped
```

So your tests run the production migrations on SQLite, and a plain PostgreSQL server works too, without the time-series features. A view bucket is computed when read; the buckets line up with TimescaleDB's (`time_bucket` counts from Monday 2000-01-03), so the same query gives the same buckets everywhere.

### The extension

| `schema.timescaledb` | Behaviour |
|---|---|
| `"auto"` (default) | Uses TimescaleDB when the database has it. When the extension is available but not installed (the server preloads it and the database user is a superuser), the first hypertable runs `CREATE EXTENSION IF NOT EXISTS timescaledb`, unless `schema.create_extensions = false`. Otherwise it falls back. |
| `"require"` | Fails a hypertable migration on a PostgreSQL database without TimescaleDB, so production can't silently run without it. Other databases still fall back. |
| `"off"` | Never uses TimescaleDB; hypertables are regular tables everywhere. |

Set `require` in production's environment (`APP_SCHEMA__TIMESCALEDB=require`) and keep `auto` for development and tests. A new app's `config/app.toml` sets `timescaledb = "auto"` in its `[schema]` section.

## Dumps

`navree schema:dump` writes hypertables, their dimensions, compression settings, policies and continuous aggregates into the [schema dump](https://navree.com/docs/database/squashing-migrations.md) through TimescaleDB's functions, so a database loaded from the dump is a TimescaleDB database with the same hypertables.

- [Migrations](https://navree.com/docs/database/migrations.md): Writing, ordering, running and dumping migrations.
- [Schema builder](https://navree.com/docs/database/schema-builder.md): Every column type, modifier, index and foreign key.
- [Dock](https://navree.com/docs/tools/dock.md): TimescaleDB and the other services, on your machine.
- [Database: getting started](https://navree.com/docs/database/getting-started.md): TimescaleDB, PostgreSQL, MySQL and SQLite: connecting and configuring.