Add TimescaleDB persistence

flowchart LR
    input["Your service"] --> scope["Session / transaction"]
    scope --> repo["Owner-scoped repository"]
    repo --> table[("Notes table")]

You have notes living in memory, and you want them to still be there after the process restarts. This guide takes the shortest real path to that: one connection, one entity, one owner-scoped repository, one migration. It uses the same crates as the starter in examples/htmx-starter, so every API below is exercised by source you can open.

The examples persist a note as id, owner, and title. That is a new persisted shape, not a drop-in replacement for whatever your in-memory note type already is; rename the field (title → body, and so on) to match your domain and adjust the column and Validate bounds to suit.

One design fact shapes the rest. Repository<E, F> gives you mechanics — resource identity checks, typed filters, bounded pagination, row mapping, unique-violation translation, soft-delete filtering. It does not add a WHERE owner = … for you. Owner isolation is application policy you write in the repository, with the schema as a backstop. Remember that and you avoid the one bug that really hurts.

Snippet convention

Snippets are fragments, not complete crates. // fragment: lines say what is assumed; names like NoteService are yours. The entity, repository, and connect shapes were compiled in a throwaway crate against this checkout.

Prerequisites

• PostgreSQL with the TimescaleDB extension already provisioned. The framework never runs CREATE EXTENSION; connect fails if extversion is absent. Required gate: stable TimescaleDB 2.x, minor ≥ 2.13 (pre-release versions are rejected).
• Crates: iet-core, iet-db-timescale, iet-db-macros, and uuid with the v7 feature. Add sqlx (postgres, a runtime, migrate, macros) only if you use the embedded migrator. Add iet-config + iet-config-env if you load settings through the typed loader.
• Toolchain: the workspace declares rust-version = "1.85". That is a declared bound; the repository has since been checked with rustc 1.98.1, not 1.85.

The minimal path

1. Set the connection URL and connect

One setting is required: DATABASE_URL. Everything else has a default defined in crates/features/iet-db-timescale/src/config.rs (pool size, acquire/idle/lifetime, statement and lock timeouts); open that file for the current values and bounds.

// fragment: reads APP_DATABASE_URL; the derive prefix is DATABASE, so
// EnvSource::snapshot("APP") sees pre-stripped keys.
use iet_db_timescale::config::DatabaseConfig;

let config: DatabaseConfig =
    iet_config::ConfigLoader::new(iet_config_env::EnvSource::snapshot("APP")?).load()?;
let database = iet_db_timescale::TimescaleDatabase::connect(&config).await?;

connect is connect_with_health_check(config, true), so SQLx pings each pooled checkout by default. connect_named(&config, "app_primary", true) additionally sets a bounded telemetry identity if you run more than one handle. Statement and lock timeouts are set per connection. If a connection cannot be validated, the pool is closed before the error is returned.

2. Declare the entity

Persistent emits schema metadata; TimescaleEntity emits the field enum and the row mapping the generic repository consumes.

// fragment: inside a private `entity` module; `NoteField` is generated by the
// `TimescaleEntity` derive and lives in the same module.
#[derive(iet_db_macros::Persistent, iet_db_macros::TimescaleEntity)]
#[persistent(table = "app_notes", hard_delete)]
#[entity(fields = NoteField)]
pub(crate) struct NoteEntity {
    pub id: i64,
    #[field(operators = "Equal")]
    pub owner: uuid::Uuid,
    #[field(operators = "Equal,Contains")]
    pub title: String,
}

• #[field(operators = "…")] is an allowlist. owner permits equality (all owner scoping needs); title permits equality and a LIKE-backed substring search. Contains is only legal on String.
• id and deleted_at are readable but not writable through generated entity operations, so id: 0 is a fine placeholder in an insert.
• hard_delete means no deleted_at. Without it, Persistent adds a nullable deleted_at to the metadata and active reads filter deleted_at IS NULL; your hand-written DDL must then include that column.
• New entities should default to a non-null uuid::Uuid id (UUID v7). Persistent infers it as the primary key and generated inserts call Uuid::now_v7(). The integer id above is for existing/external schemas, matching the starter.

3. Read and write it with owner isolation

Every repository method takes &mut CallScope<'_, TimescaleSession>. The scope carries the trusted CallContext and the exclusive session borrow; there is no ambient connection to reach for.

// fragment: `AppError`, `AppResult`, `ErrorKind`, `CallScope`, `ResourceId` from
// iet-core; `Repository`, `Filter`, `Operator`, `Value` from iet-db-timescale;
// `TimescaleSession`; `NoteEntity`, `NoteField` from the entity module.
use iet_core::{AppError, AppResult, CallScope, ErrorKind, ResourceId};
use iet_db_timescale::{
    TimescaleSession,
    filters::{Filter, Operator, Value},
    persistence::Repository,
};

#[derive(Clone)]
pub struct TimescaleNoteRepository {
    resource: ResourceId,
}

impl TimescaleNoteRepository {
    pub fn new(resource: ResourceId) -> Self {
        Self { resource }
    }

    fn query(&self) -> Repository<NoteEntity, NoteField> {
        Repository::new(self.resource.clone())
    }
}

/// `owner_id` is the authenticated principal's subject (`actor.subject`),
/// never a value read straight from a request. Invalid UUID text is an auth
/// failure, not "not found".
fn owner(value: &str) -> AppResult<uuid::Uuid> {
    uuid::Uuid::parse_str(value)
        .map_err(|_| AppError::new(ErrorKind::Unauthenticated, "note.owner", "Invalid owner"))
}

fn owner_filter(owner_id: &str) -> AppResult<Filter<NoteField>> {
    Ok(Filter::Compare(
        NoteField::Owner,
        Operator::Equal,
        Value::Uuid(owner(owner_id)?),
    ))
}

impl TimescaleNoteRepository {
    /// One bounded page. `limit`/`offset` come from validated input; `page`
    /// rejects `limit` outside 1..=100 and `offset` above 200000. Do not loop
    /// `page` until the table ends — that is an unbounded scan by another name.
    pub async fn list_page(
        &self,
        owner_id: &str,
        limit: u32,
        offset: u32,
        scope: &mut CallScope<'_, TimescaleSession>,
    ) -> AppResult<Vec<NoteEntity>> {
        // Manual owner isolation: this predicate is ours, not the repository's.
        self.query()
            .page(&owner_filter(owner_id)?, limit, offset, false, scope)
            .await
    }

    pub async fn create(
        &self,
        owner_id: &str,
        title: &str,
        scope: &mut CallScope<'_, TimescaleSession>,
    ) -> AppResult<NoteEntity> {
        self.query()
            .insert_entity(
                &NoteEntity {
                    id: 0, // placeholder; the identity column assigns the real id
                    owner: owner(owner_id)?,
                    title: title.into(),
                },
                scope,
            )
            .await
    }

    pub async fn delete(
        &self,
        owner_id: &str,
        id: i64,
        scope: &mut CallScope<'_, TimescaleSession>,
    ) -> AppResult<()> {
        let deleted = self
            .query()
            .delete_filtered(id, &owner_filter(owner_id)?, scope)
            .await?;
        if !deleted {
            return Err(AppError::new(ErrorKind::NotFound, "note.missing", "Note not found"));
        }
        Ok(())
    }
}

The main operations are page, find_by_id, insert_entity, update_filtered, and delete_filtered, all checking the resource first. page/list enforce 1 <= limit <= 100 and offset <= 200000. In real code NoteEntity stays crate-private and these methods implement your repository port rather than being pub inherent methods.

The trap: find_by_id and delete_by_id use the primary key only. If you read with find_by_id and trust the result, you may have another owner's row. Filter it in Rust (as the starter's task repository does) or use an owner filter:

// fragment: owner-scoped read.
let owner = owner(owner_id)?;
Ok(self.query().find_by_id(id, scope).await?.filter(|row| row.owner == owner))

For mutations, prefer update_filtered / delete_filtered so the owner predicate travels in the same statement. The schema is the second layer: the starter's app_tasks uses a composite foreign key FOREIGN KEY(owner, project_id) REFERENCES app_projects(owner, id), so a row cannot point at another owner's project even if a service bug tried.

4. Create the table with a paired migration

Apply the table before you run the repository from step 3, or its queries will fail. A reviewed pair is the reliable path: Migrations::load requires NNNN_name.up.sql and NNNN_name.down.sql (positive version, lowercase name, both directions present, non-empty). iet-db-cli reads that directory and the _sqlx_migrations ledger.

Point the CLI at a development database first. These are development-only loopback credentials, not a production pattern:

# development-only loopback credentials; keep real credentials out of source control
export APP_DATABASE_URL='postgres://app_user:[email protected]:5432/app_notes'

Scaffold the pair from the workspace root:

cargo run -p iet-db-cli -- --prefix APP --directory migrations new --version 1 --name notes

In this checkout new writes unpadded names: migrations/1_notes.up.sql and migrations/1_notes.down.sql. Replace the template SQL with DDL that matches NoteEntity:

-- migrations/1_notes.up.sql
CREATE TABLE app_notes (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  owner UUID NOT NULL,
  title TEXT NOT NULL CHECK (length(title) BETWEEN 1 AND 120)
);
CREATE INDEX app_notes_owner ON app_notes(owner, id);

-- migrations/1_notes.down.sql
DROP TABLE app_notes;

Then inspect and apply:

cargo run -p iet-db-cli -- --prefix APP --directory migrations status
cargo run -p iet-db-cli -- --prefix APP --directory migrations apply --approved

status prints one applied|pending <version> <description> line per migration, for example pending 1 notes before apply and applied 1 notes after. apply is guarded by --approved, and Migrations::status runs first and rejects checksum drift, unknown versions, failed rows, and a missing earlier version. rollback --target N --approved reverts down to (and excluding) version N; --target 0 reverts everything. The loader parses any positive numeric prefix, so hand-written 0001_notes.up.sql is also valid; new simply does not zero-pad.

The framework does not validate your schema at application startup. status and verify inspect the migration ledger, not live table shape. If you want a startup check (the starter has one), it is application code you write.

Once the pair is applied, you are ready to exercise step 5 against the real table. This guide has not run a live insert, so treat that as the next test to run rather than a demonstrated result.

Other migration styles (advanced, linked for reference). The starter instead embeds SQLx migrators per implementation crate and assembles them in its migrate command; see examples/htmx-starter/crates/project-impl-timescale/src/lib.rs and examples/htmx-starter/servers/web/src/database.rs. That path uses plain NNNN_name.sql files and app-owned schema validation. Separately, you can derive a pair from Persistent metadata with iet_db_timescale::generation::EntityRegistry (register, then initial or diff); it is a conservative planner (tables, nullable additions, declared indexes) and rejects destructive or hypertable-dimension changes.

5. Run it through a scope and a transaction

// fragment: `actor: &Principal`, `self.database: TimescaleDatabase`.
use iet_core::{CallContext, TransactionScope};

let context = CallContext { principal: Some(actor.clone()), ..Default::default() };

// read (one bounded page: limit 20, offset 0)
let mut session = self.database.session();
let notes = self
    .repository
    .list_page(&actor.subject, 20, 0, &mut CallScope::new(&context, &mut session))
    .await?;

// write
let mut session = self.database.session();
let mut transaction = TransactionScope::enter(&mut session).await?;
let result = self
    .repository
    .create(&actor.subject, title, &mut CallScope::new(&context, transaction.session()))
    .await;
transaction.finish(result).await?;

CallScope::new borrows the session mutably, and every generated operation calls scope.require_resource(&self.resource). Building the repository with resource A and running it against a session for resource B gives a deterministic Internal session.resource_mismatch instead of silently querying the wrong database. TransactionScope::finish marks rollback-only on an inner error, commits only when this call owns the transaction, and surfaces commit/rollback failures.

Expected outcomes and tests

• Missing extension → Unavailable timescale.extension_missing; old or pre-release version → timescale.version.
• insert_entity with id: 0 returns the assigned id (identity column or Uuid::now_v7()), and unique violations become Conflict entity.duplicate.
• page/list outside limit 1..=100 or offset <= 200000 return Validation; a type-mismatched operator, disallowed operator, Contains on non-text, or text over 4096 bytes returns query.filter; filter nesting is capped (depth 8, 100 nodes).
• A wrong ResourceId yields Internal session.resource_mismatch.
• Migrations::status detects checksum drift, unknown versions, failed rows, and a missing earlier version. Down-file checksums are not independently recorded by SQLx, so do not claim down-drift verification.
• The ignored live test crates/features/iet-db-timescale/tests/repository.rs::ordinary_crud_works_without_tenant_context (needs a disposable FW_TEST_TIMESCALE_URL) verifies insert, rollback, duplicate rejection, soft-delete hiding, bounds, and resource mismatch. Never point it at production.

Compile without a database:

cargo check -p <your-impl-crate>

Common mistakes

• Expecting the repository to filter by owner. It does not. Guard reads that use find_by_id, and prefer *_filtered mutations.
• Reaching for database.pool() in a repository. Business repositories use the scoped session; the pool bypasses resource identity and shared transaction semantics and is for infrastructure and migrations.
• Forgetting hard_delete or the deleted_at column. The metadata and your DDL must agree.
• Passing a UUID that isn't v7. Identifier values are validated; non-v7/RFC values are rejected.
• Retrying a mutation after an error. Commit failures leave the session in an unknown state on purpose; surface the error.
• Mixing the pair loader with plain filenames. Migrations::load needs .up.sql + .down.sql; sqlx::migrate! does not.

Advanced choices (after the basic path works)

• Soft delete. Drop hard_delete and add deleted_at: Option<chrono::DateTime<chrono::Utc>>; active reads then exclude deleted rows. Use #[persistent(index(..., unique))] for partial uniqueness.
• Connect tuning and telemetry. connect_with_health_check(&config, false) skips the per-checkout ping (stale connections then surface as normal operation errors); connect_named labels the handle; database.record_pool_metrics() emits fw_db_pool_connections / fw_db_pool_idle when telemetry is enabled.
• Hypertables and richer metadata. Add #[persistent(table = "…", time_column = "observed_at")] (the column must be a non-null timestamptz, and primary keys/unique indexes must include it), and declare foreign_key / index metadata so generation can emit and validate them.

Limitations

• No implicit tenancy. A declared owner column is ordinary data; the repository predicate is the policy and a composite foreign key is the backstop.
• No automatic CREATE EXTENSION and no fallback to plain PostgreSQL.
• SQLx manages per-step transactions and locking, not atomic whole-batch rollback; Timescale DDL must be authored for the engine.
• The derived diff covers tables, nullable additions, and declared indexes only; constraint drift and destructive changes need review.
• These crates do not provide generic multi-tenant predicates or composite-key CRUD beyond page/find_by_id.

Where to go next

• Protect a mutation that writes a note: Build an HTMX page.
• Let the user log in who owns those notes: Compose authentication.