# PostgreSQL Runtime Architecture

## 1. Decision

Iwf's canonical public boundary is the `postgres-runtime` library plus the
`Iwf.Database` adapter. Application code should depend on those Idris APIs, not
on `psql`, shell commands, Python process details, or libpq calls.

The current Python/libpq helper is a transitional implementation detail. It is
acceptable for the current framework runtime because it gives Iwf one
parameterized execution path for raw SQL, migrations, fixtures, jobs,
transactions, and `PreparedQuery` execution. It is not the long-term production
target.

The long-term runtime should remain hidden behind the same library boundary and
move toward a native Idris/C libpq backend, or another backend with equivalent
semantics, once benchmarks and operational tests show the process helper is the
bottleneck or the main reliability risk.

## 2. Contract

Any PostgreSQL runtime backend must preserve these behavior contracts:

- Parameter values are sent separately from SQL text.
- Parameterized worker queries use cached PostgreSQL prepared statements per
  connection when the backend can support them.
- Checked-out pool slots reuse a long-lived runtime worker where possible.
- Broken idle workers are health-checked, discarded, and replaced.
- Transactions roll back on adapter errors and report the original failure.
- `withConnectionApp` releases checked-out workers and semaphore slots when
  typed Idris user code throws.
- `withTransactionApp` rolls back runtime transactions when typed Idris user
  code throws.
- Nested transactions have one explicit policy instead of implicit savepoint
  behavior.
- Runtime errors preserve structured SQL context without logging parameter
  values.
- Pool metrics expose pool size, checked-out slots, idle workers, failed
  workers, and waiting checkouts.
- Shutdown APIs drain idle workers and close adapter resources explicitly.

## 3. Non-Goals

This decision does not add an IHP compatibility layer, a public Hasql-like API,
or a public `psql` execution surface. It also does not make PostgreSQL
LISTEN/NOTIFY a public realtime feature; AutoRefresh has its own separate
contract.

## 4. Replacement Criteria

Replace the Python/libpq helper when one of these becomes true:

- Database benchmarks show worker process serialization or text encoding is a
  material bottleneck for common Iwf queries.
- Pool load tests show process lifecycle, broken pipes, or shutdown behavior
  cannot meet the production contract reliably.
- A native backend can satisfy the same tests with a smaller deployment
  footprint.

Until then, improvements should harden the library boundary and its tests
instead of exposing lower-level helper details to application code.

## 5. Current Shortcomings

- Plain `withConnection` is an `IO` convenience helper. Use `withConnectionApp`
  when code can throw typed Idris exceptions and needs cleanup enforced by the
  framework.
- The helper is still a Python process using `libpq`, not a native Idris
  PostgreSQL backend.
- Runtime values cross a line-oriented wire protocol as text. Parameter values
  are separated from SQL and prepared statements are cached, but this is not a
  binary protocol.
- The pool is intentionally small: it exposes worker health, metrics, shutdown,
  and reconnect behavior, but not a Hasql-compatible API or advanced connection
  tuning surface.

## Next

Read [Internal Technical Architecture](../technical-architecture.md).