duroxide-pg

# Duroxide-PG Copilot Instructions

## Project Overview
This is a **PostgreSQL provider** for [Duroxide](https://github.com/microsoft/duroxide), a durable task orchestration framework for Rust. It implements the `Provider` and `ProviderAdmin` traits, storing orchestration state, history, and work queues in PostgreSQL using stored procedures.

## Architecture

### Core Components
- **[src/provider.rs](../src/provider.rs)** - Main `PostgresProvider` implementing duroxide traits (`Provider`, `ProviderAdmin`)
- **[src/migrations.rs](../src/migrations.rs)** - Embedded SQL migration runner using `include_dir!`
- **[migrations/](../migrations/)** - Sequential SQL migrations (schema + stored procedures)
- **[pg-stress/](../pg-stress/)** - Stress testing binary for performance validation

### Key Design Decisions
1. **Stored Procedures over inline SQL** - All database operations use schema-qualified stored procedures in `0002_create_stored_procedures.sql` for atomic transactions and deadlock prevention
2. **Rust-provided timestamps** - All procedures receive `p_now_ms` from Rust (not database `NOW()`) to avoid clock skew (see migration `0006`)
3. **Schema isolation** - Multi-tenant support via custom PostgreSQL schemas (`new_with_schema()`)
4. **Two-phase locking in fetch operations** - Prevents B-tree index deadlocks (peek query → advisory lock → FOR UPDATE verification)

### Data Flow
```
Duroxide Runtime → PostgresProvider → Stored Procedures → PostgreSQL Tables
                                                         (instances, executions, history,
                                                          orchestrator_queue, worker_queue)
```

## Development Workflow

### Prerequisites
```bash
# PostgreSQL running locally (or use Docker)
docker run -d --name duroxide-pg -p 5432:5432 -e POSTGRES_PASSWORD=postgres postgres:15

# Create .env file
echo "DATABASE_URL=postgres://postgres:postgres@localhost:5432/duroxide_test" > .env
```

### Running Tests

**Prefer `cargo-nextest`** if installed (faster, better output). Fall back to `cargo test` otherwise.

```bash
# Check if nextest is available
command -v cargo-nextest >/dev/null 2>&1 && echo "nextest available" || echo "use cargo test"

# All tests with nextest (preferred)
cargo nextest run

# All tests with cargo test (fallback)
cargo test

# Specific test with output
cargo nextest run test_provider_creation --nocapture
# or: cargo test test_provider_creation -- --nocapture

# Provider validation tests only (99 tests from duroxide)
cargo nextest run --test postgres_provider_test
# or: cargo test --test postgres_provider_test

# Stress tests (long-running, marked #[ignore])
cargo nextest run --test stress_tests --run-ignored only
# or: cargo test --test stress_tests -- --ignored
```

### Connection Exhaustion Under High Parallelism

Each test runtime creates a connection pool (default 10 max connections). At high parallelism (e.g., 14 cores), peak PostgreSQL connections can reach **~104**. If PostgreSQL `max_connections` is set to the default 100, tests will fail with timeouts due to connection exhaustion.

**Fix:** Increase PostgreSQL `max_connections` to at least 300:
```bash
docker exec <container> psql -U postgres -c "ALTER SYSTEM SET max_connections = 500;"
docker restart <container>
```

### Stress Testing
```bash
# Quick stress test (10 seconds)
cargo run --release --package duroxide-pg-stress --bin pg-stress -- --duration 10

# Custom configuration
cargo run --release --package duroxide-pg-stress --bin pg-stress -- \
  --duration 30 --orch-concurrency 4 --worker-concurrency 4
```

## Conventions

### Error Handling
Provider errors are classified as **retryable** or **permanent** via `ProviderError`:
```rust
// Retryable: deadlocks (40P01), pool timeouts, I/O errors
ProviderError::retryable(operation, message)

// Permanent: constraint violations (23505, 23503), invalid tokens
ProviderError::permanent(operation, message)
```

### Schema Naming for Tests
Tests create unique schemas with GUID suffix to allow parallel execution:
```rust
fn next_schema_name() -> String {
    let guid = uuid::Uuid::new_v4().to_string();
    format!("test_{}", &guid[guid.len()-8..])
}
```

### Adding Migrations
1. Create `NNNN_description.sql` in `migrations/`
2. Use unqualified table names (search_path is set by runner)
3. Make idempotent with `IF NOT EXISTS` / `IF EXISTS`
4. Update stored procedures with new parameters if needed
5. **REQUIRED: Generate a diff markdown file** (see below)

### Migration Diff Files (Required)
Every migration that modifies schema or stored procedures **must** have a companion `NNNN_diff.md` file. This is required because git diffs for SQL migrations only show the new code, not the delta from the previous version.

#### Option A: Auto-generate with PostgreSQL (preferred)
```bash
./scripts/generate_migration_diff.sh <migration_number>
# Example: ./scripts/generate_migration_diff.sh 9
# Creates: migrations/0009_diff.md
```

The script creates temp schemas, applies migrations before/after, extracts DDL, and diffs them.

#### Option B: Manual extraction (when no PostgreSQL available)
When a live database isn't available, generate diffs by extracting stored procedure bodies from the SQL migration files:

1. **Identify baselines**: For each SP modified in migration N, find the most recent migration before N that contains `CREATE OR REPLACE FUNCTION ... <sp_name>`. Use `grep -n "CREATE OR REPLACE FUNCTION.*<sp_name>" migrations/*.sql` to find them.
2. **Extract SP bodies**: Extract the `CREATE OR REPLACE FUNCTION ... LANGUAGE plpgsql;` block from both the baseline and new migration files.
3. **Normalize before diffing**: Migration files use different SQL quoting mechanisms across versions:
   - Replace schema placeholders (`%I.` or `@SCHEMA@.`) with `SCHEMA.`
   - Replace escaped single quotes (`''`) with `'` (old migrations inside `format('...')` need `''`; newer ones inside `format($fmt$...$fmt$)` don't)
   - Replace escaped percent signs (`%%`) with `%` (same reason: `format()` uses `%` for placeholders)
   - Expand tabs to spaces and strip trailing whitespace
4. **Diff**: Run `diff -u <baseline_normalized> <new_normalized>` to get unified diff output.
5. **Assemble**: Strip the `---`/`+++` header lines and include the hunks in ` ```diff ` code blocks.

#### Diff format requirements
Each changed function must be shown with `+`/`-` diff markers on changed lines inside a ` ```diff ` code block. The diff file should contain:
1. **Table Changes** — New tables (full DDL in ` ```sql ` block), modified tables (mark new columns with `+`)
2. **New Indexes** — Any indexes added by the migration (full DDL in ` ```sql ` block)
3. **Function Changes** — For each changed function: unified diff in a ` ```diff ` block with the baseline migration number noted in the heading (e.g., `### \`func_name\` — body modified (baseline: 0016)`)

Example output: See [migrations/0017_diff.md](../migrations/0017_diff.md)

### Updating duroxide Dependency
Follow the detailed guide in [prompts/update-duroxide-dependency.md](../prompts/update-duroxide-dependency.md). Key steps:
1. **Review changes**: Read duroxide CHANGELOG, README, and provider guides at the duroxide repo
2. **Update Cargo.toml**: Change version, run `cargo check`, fix compilation errors
3. **Implement API changes**: Update `src/provider.rs`, add migrations if needed
4. **Add validation tests**: New tests go in `tests/postgres_provider_test.rs` using the `provider_validation_test!` macro
5. **Test thoroughly**: `cargo test`, run flaky tests 10x
6. **Document as unreleased**: Add dependency and compatibility notes under `CHANGELOG.md`'s existing `[Unreleased]` section

Dependency update and feature PRs must keep the current `duroxide-pg` package version and published README release sections unchanged. Release preparation and publishing are governed by [RELEASE_POLICY.md](../RELEASE_POLICY.md).

> ⚠️ **Never publish directly to crates.io.**

Use [.agents/skills/release-preparation/SKILL.md](../.agents/skills/release-preparation/SKILL.md) to prepare release metadata and create the release PR. After the PR is merged into `main`, the skill may create and push the version tag only with fresh explicit user approval.

## Key Files Reference
| File | Purpose |
|------|---------|
| [provider.rs](../src/provider.rs) | Provider trait implementations |
| [0002_create_stored_procedures.sql](../migrations/0002_create_stored_procedures.sql) | All stored procedures |
| [tests/common/mod.rs](../tests/common/mod.rs) | Test utilities (`create_postgres_store`, `test_create_execution`) |
| [postgres_provider_test.rs](../tests/postgres_provider_test.rs) | Provider validation test harness |

## Debugging Tips
- Enable tracing: `RUST_LOG=duroxide::providers::postgres=debug cargo test`
- Check schema cleanup: Run `scripts/cleanup_test_schemas.sh` after failed tests
- Deadlock issues: Review two-phase locking in `fetch_orchestration_item` stored procedure