PostgreSQL Kit

Give a stateful Plugin an explicit lifecycle for its own PostgreSQL schema.

lenso-postgres-kit is a lifecycle kit for one Plugin-owned PostgreSQL schema. It is not a shared State Plugin, generic SQL Capability, repository abstraction, or ORM. The owning Plugin still defines its data model, queries, transaction boundaries, backup and retention policy, and dedicated database role.

Define and set up the schema

Keep SQL in the owning Plugin’s migrations/ directory. Rust retains only the explicit version, name, and file order:

orders-module/
├── migrations/
│   ├── 001_create_orders.sql
│   └── 002_add_order_status.sql
└── src/lib.rs

CREATE TABLE orders (
    id bigint PRIMARY KEY,
    total_cents bigint NOT NULL
);

use lenso_postgres_kit::{
    Migration, OwnedPostgres, SchemaOperator, SchemaPlan, sql_migrations,
};

const MIGRATIONS: &[Migration] = sql_migrations![
    (1, "create-orders", "migrations/001_create_orders.sql"),
    (2, "add-order-status", "migrations/002_add_order_status.sql"),
];

async fn install(database_url: &str) -> Result<(), Box<dyn std::error::Error>> {
    let plan = SchemaPlan::new("orders_module", MIGRATIONS)?;
    SchemaOperator::connect(database_url, plan.clone()).await?.setup().await?;
    Ok(())
}

async fn prepare(database_url: &str) -> Result<OwnedPostgres, Box<dyn std::error::Error>> {
    let plan = SchemaPlan::new("orders_module", MIGRATIONS)?;
    // Runtime preparation verifies the exact schema and never migrates it.
    Ok(OwnedPostgres::prepare(database_url, plan).await?)
}

Paths are relative to the owning crate’s Cargo.toml. sql_migrations! uses include_str! to embed SQL at compile time: a missing file is a compile error, and an edit triggers recompilation; no directory is scanned at runtime. Versions and names stay explicit, preserving deterministic order, contiguous-version validation, and checksum drift detection.

Run install as an explicit installation or deployment operation, never from Plugin prepare. After OwnedPostgres::prepare, use postgres.pool() with normal SQLx queries and transactions. The pool selects the owned schema as its search_path.

Upgrade deliberately

Append a new SQL file and declaration without editing an applied file. The new Plugin generation first returns PostgresKitError::UpgradeRequired; stop the owning Plugin, run the operator action, then prepare the new generation:

let plan = SchemaPlan::new("orders_module", MIGRATIONS)?;
let outcome = SchemaOperator::connect(database_url, plan)
    .await?
    .upgrade()
    .await?;
println!("{outcome:?}");

Setup and upgrade use an advisory lock and one atomic migration transaction. The private ledger records version, name, and checksum.

setup is idempotent: it returns Created once and AlreadyCurrent after that. Failures roll back the schema and ledger together.

Enforce isolation in PostgreSQL

search_path is query convenience, not a security boundary. Give each Plugin a dedicated non-superuser role that owns only its schema, and restrict database grants accordingly. Sharing one physical cluster does not authorize direct access to another Plugin’s tables.

Cross-Plugin workflows use Capability calls and application-level coordination, not shared SQL transactions. Resolve a database URL through the Secrets Plugin, but never place the URL itself in the Resolved App Plan.

Verify the kit

cargo fmt --all -- --check
cargo clippy --locked --all-targets --all-features -- -D warnings
cargo test --locked --all-features
LENSO_POSTGRES_TEST_URL=postgres://... \
  cargo test --locked --test postgres_acceptance -- --ignored