pgsql-broker/README.md

# PostgreSQL Broker

A robust, event-driven job processing system for PostgreSQL that uses LISTEN/NOTIFY for real-time job execution. It supports multiple queues, priority-based scheduling, multi-tenant row-level security, and can be used both as a standalone service or as a Go library.

## Status
[![Integration Tests](https://git.warky.dev/wdevs/pgsql-broker/actions/workflows/integration.yml/badge.svg?branch=main)](https://git.warky.dev/wdevs/pgsql-broker/actions?workflow=integration.yml)

[![Release](https://git.warky.dev/wdevs/pgsql-broker/actions/workflows/release.yml/badge.svg?branch=main)](https://git.warky.dev/wdevs/pgsql-broker/actions?workflow=release.yml)

[![Build & Release Docker Image](https://git.warky.dev/wdevs/pgsql-broker/actions/workflows/docker-release.yml/badge.svg?branch=main)](https://git.warky.dev/wdevs/pgsql-broker/actions?workflow=docker-release.yml)


## Features

- **Multi-Database Support**: Single broker process can manage multiple database connections
- **Event-Driven**: Uses PostgreSQL LISTEN/NOTIFY for instant job notifications
- **Multiple Queues**: Support for concurrent job processing across multiple queues per database
- **Priority Scheduling**: Jobs can be prioritized for execution order
- **Job Dependencies**: Jobs can depend on other jobs completing first (cycle-checked, gated in `broker_get`)
- **Multi-Tenant Row-Level Security**: Jobs are isolated per `tenant_id` via FORCE RLS policies (`broker.broker_set_tenant`)
- **Lease-Based Claiming**: Jobs are claimed with an expiring lease and recovered automatically if a worker dies mid-job
- **Least-Privilege Roles**: Optional `broker_admin` / `broker_runtime` / `broker_enqueue` role separation
- **Versioned Migrations**: Embedded, ordered SQL migrations with an applied-versions tracking table
- **Adapter Pattern**: Clean interfaces for database and logging (easy to extend)
- **Standalone or Library**: Use as a CLI tool or integrate into your Go application
- **Configuration Management**: Viper-based config with support for YAML, JSON, and environment variables
- **Graceful Shutdown**: Proper cleanup and job completion on shutdown
- **Single Instance Per Database**: Enforces one broker instance per database to prevent conflicts
- **Docker Support**: Production `Dockerfile`/`docker-compose.yml` and a test-runner `Dockerfile.test`/`docker-compose.test.yml`

## Architecture

The broker supports multi-database architecture where a single broker process can manage multiple database connections. Each database has its own instance with dedicated queues, but only ONE broker instance is allowed per database.

```text
┌─────────────────────────────────────────────────────────────────┐
│                      Broker Process                              │
├─────────────────────────────────────────────────────────────────┤
│                                                                   │
│  ┌────────────────────────────┐  ┌───────────────────────────┐ │
│  │  Database Instance (DB1)   │  │  Database Instance (DB2)  │ │
│  ├────────────────────────────┤  ├───────────────────────────┤ │
│  │ ┌────┐ ┌────┐ ┌────┐      │  │ ┌────┐ ┌────┐            │ │
│  │ │ Q1 │ │ Q2 │ │ QN │      │  │ │ Q1 │ │ Q2 │            │ │
│  │ └────┘ └────┘ └────┘      │  │ └────┘ └────┘            │ │
│  │                            │  │                           │ │
│  │ ┌────────────────────────┐│  │ ┌────────────────────────┐│ │
│  │ │  PostgreSQL Adapter    ││  │ │  PostgreSQL Adapter    ││ │
│  │ │  - Connection Pool     ││  │ │  - Connection Pool     ││ │
│  │ │  - LISTEN/NOTIFY       ││  │ │  - LISTEN/NOTIFY       ││ │
│  │ └────────────────────────┘│  │ └────────────────────────┘│ │
│  └────────────┬───────────────┘  └──────────┬────────────────┘ │
│               │                              │                   │
└───────────────┼──────────────────────────────┼───────────────────┘
                │                              │
                ▼                              ▼
    ┌──────────────────────┐      ┌──────────────────────┐
    │  PostgreSQL (DB1)    │      │  PostgreSQL (DB2)    │
    │  - broker_jobs       │      │  - broker_jobs       │
    │  - broker_job_dependency│   │  - broker_job_dependency│
    │  - broker_queueinstance│    │  - broker_queueinstance│
    │  - broker_schedule   │      │  - broker_schedule   │
    └──────────────────────┘      └──────────────────────┘
```

**Key Points**:

- One broker process can manage multiple databases
- Each database has exactly ONE active broker instance
- Each database instance has its own queues and workers
- Validation prevents multiple broker processes from connecting to the same database
- Different databases can have different queue counts
- Jobs are isolated by `tenant_id` via FORCE RLS; `broker_get` only claims jobs with no incomplete dependency

## Installation

### From Source

```bash
git clone git.warky.dev/wdevs/pgsql-broker
cd pgsql-broker
make build
```

The binary will be available in `bin/pgsql-broker`.

### As a Library

```bash
go get git.warky.dev/wdevs/pgsql-broker
```

### With Docker

See [Docker](#docker) below for the production `Dockerfile`/`docker-compose.yml` and the `Dockerfile.test`/`docker-compose.test.yml` test runner.

## Quick Start

### 1. Setup Database

Migrations are embedded in the binary and applied in order, tracked in `broker.broker_schema_migrations`.

```bash
# Apply all pending migrations, then verify
./bin/pgsql-broker install --config broker.yaml

# Verify only, no changes
./bin/pgsql-broker install --verify-only --config broker.yaml

# Or with make
make sql-install
```

#### Optional: least-privilege roles

`install --with-roles` additionally creates/rotates three roles (idempotent — safe to re-run):

| Role | Purpose |
| ---- | ------- |
| `broker_admin` | Schema owner, `BYPASSRLS`, runs migrations |
| `broker_runtime` | The role the running broker connects as; table/sequence/function privileges only, no `BYPASSRLS` |
| `broker_enqueue` | Narrow role for enqueuing jobs only (`broker_add_job`, `broker_set_tenant`, insert on `broker_jobs`/`broker_job_dependency`) |

```bash
./bin/pgsql-broker install --with-roles \
  --admin-user postgres \
  --broker-admin-password ... \
  --broker-runtime-password ... \
  --broker-enqueue-password ...
```

Credential resolution order for each value: CLI flag → environment variable → interactive masked prompt (TTY only) → error.

| Value | Flag | Env fallback(s) |
| ----- | ---- | ---------------- |
| Admin user | `--admin-user` | `PGUSER`, `PG_USER` |
| Admin password | `--admin-password` | `PGPASSWORD`, `PG_PASS` |
| `broker_admin` password | `--broker-admin-password` | `BROKER_ADMIN_PASSWORD` |
| `broker_runtime` password | `--broker-runtime-password` | `BROKER_RUNTIME_PASSWORD` |
| `broker_enqueue` password | `--broker-enqueue-password` | `BROKER_ENQUEUE_PASSWORD` |

`--with-roles` always connects as the admin login (not the config file's own user) since `broker_admin` must own the schema; it cannot be combined with `--verify-only`.

### 2. Configure

Create a configuration file `broker.yaml`:

```yaml
databases:
  - name: db1
    host: localhost
    port: 5432
    database: broker_db1
    user: postgres
    password: your_password
    sslmode: disable
    queue_count: 4
    tenant_id: default          # optional; sets broker.tenant_id for RLS

  # Optional: add more databases
  - name: db2
    host: localhost
    port: 5432
    database: broker_db2
    user: postgres
    password: your_password
    sslmode: disable
    queue_count: 2

broker:
  name: pgsql-broker
  enable_debug: false
  lease_seconds: 60            # job lease duration before it's reclaimable
  stale_job_recovery_sec: 30   # how often expired leases are recovered

logging:
  level: info
  format: json
```

**Note**: Each database requires a unique `name` identifier and can have its own `queue_count` configuration. See [broker.example.yaml](./broker.example.yaml) for the full field set.

### 3. Run the Broker

```bash
# Using the binary
./bin/pgsql-broker start --config broker.yaml

# Or with make
make run

# Or with custom log level
./bin/pgsql-broker start --log-level debug
```

### 4. Add a Job

```sql
SELECT * FROM broker.broker_add_job(
    'My Job',                    -- p_job_name
    'SELECT do_something()',     -- p_execute_str
    1,                           -- p_job_queue (default: 1)
    0,                           -- p_job_priority (default: 0)
    'sql',                       -- p_job_language (default: 'sql')
    NULL,                        -- p_run_as
    NULL,                        -- p_schedule_id
    ARRAY[123]::BIGINT[],        -- p_depends_on_job_ids (NULL = no deps)
    NULL,                        -- p_idempotency_key
    1,                           -- p_max_attempts (default: 1)
    NULL,                        -- p_job_group (default: p_job_name)
    ARRAY['extract']             -- p_depends_on_groups (NULL = no deps)
);
```

Or, for the common case (name, execute string, priority, dependencies), use the shortcut:

```sql
SELECT * FROM broker.broker_add_job_simple('transform', 'SELECT do_something()', 5, ARRAY['extract']);
```

#### Dependencies: job groups vs. job ids

Every job belongs to a **job group**, defaulting to its own `job_name` when `p_job_group` isn't given. A dependency can target either a whole group (`p_depends_on_groups`) or a single job id (`p_depends_on_job_ids`):

- **Group dependency** (fan-in): the dependent job is only claimable once **every** job currently tagged with that group has `complete_status = 2`. Multiple jobs can share a group (e.g. several parallel `extract` jobs feeding one `transform`); already-completed group members simply drop out of the check.
- **Id dependency**: waits on one specific job row, as before.

Both `broker_add_job` and `broker_add_job_simple` reject a job depending on its own group/id, and a one-hop cycle (A depends on B, B already depends on A).

When calling as `broker_enqueue` (or any RLS-scoped role), set the tenant first and wrap in a single transaction so `SET LOCAL` persists across statements:

```sql
BEGIN;
SET ROLE broker_enqueue;
SELECT broker.broker_set_tenant('my_tenant');
SELECT * FROM broker.broker_add_job_simple('extract', 'SELECT 1');
COMMIT;
```

## Usage as a Library

```go
package main

import (
    "git.warky.dev/wdevs/pgsql-broker/pkg/broker"
    "git.warky.dev/wdevs/pgsql-broker/pkg/broker/adapter"
    "git.warky.dev/wdevs/pgsql-broker/pkg/broker/config"
)

func main() {
    // Load config
    cfg, _ := config.LoadConfig("broker.yaml")

    // Create logger
    logger := adapter.NewSlogLogger(slog.LevelInfo)

    // Create database adapter
    dbAdapter := adapter.NewPostgresAdapter(cfg.Database.ToPostgresConfig(), logger)

    // Create and start broker
    instance, _ := broker.New(cfg, dbAdapter, logger, "1.0.0")
    instance.Start()

    // ... wait for shutdown signal ...

    instance.Stop()
}
```

See the [examples](./examples/) directory for complete examples.

## Docker

### Prebuilt image

Published to `git.warky.dev/wdevs/pgsql-broker` on every `v*.*.*` tag (`.github/workflows/docker-release.yml`), tagged with the version and `latest`.

```bash
docker pull git.warky.dev/wdevs/pgsql-broker:latest
docker run --rm -v $(pwd)/broker.yaml:/etc/pgsql-broker/broker.yaml:ro git.warky.dev/wdevs/pgsql-broker:latest
```

Config must be mounted at `/etc/pgsql-broker/broker.yaml` — no config is baked into the image.

### Prebuilt image: `docker-compose.prebuilt.yml`

Same stack as `docker-compose.yml` below, but pulls `git.warky.dev/wdevs/pgsql-broker:latest` instead of building from source — no local Go toolchain or Dockerfile needed.

```bash
cp broker.docker.example.yaml broker.docker.yaml   # set broker_runtime password
cp .env.example .env                                # set POSTGRES_PASSWORD + 3 BROKER_*_PASSWORD
docker-compose -f docker-compose.prebuilt.yml up -d
```

### Building the image locally

```bash
make docker-build   # builds pgsql-broker:<VERSION> and pgsql-broker:latest via Dockerfile
```

### Production: `Dockerfile` + `docker-compose.yml`

Multi-stage build (`golang:1.26-alpine` → `alpine:3.22`, static binary, non-root user). The compose stack runs Postgres, a one-shot `migrate` service (`install --with-roles`), then the `broker` service connecting as `broker_runtime`.

```bash
cp broker.docker.example.yaml broker.docker.yaml   # set broker_runtime password
cp .env.example .env                                # set POSTGRES_PASSWORD + 3 BROKER_*_PASSWORD
docker-compose up --build -d
```

`broker.docker.yaml` and `.env` are gitignored — never commit real credentials. `BROKER_RUNTIME_PASSWORD` in `.env` must match the `password:` field in `broker.docker.yaml`.

### Test runner: `Dockerfile.test` + `docker-compose.test.yml`

Builds the module and runs the full Go integration suite (`tests/integration/...`) against a disposable Postgres container:

```bash
docker-compose -f docker-compose.test.yml up --build --abort-on-container-exit --exit-code-from tests
docker-compose -f docker-compose.test.yml down -v
```

## Database Schema

Schema objects live under `broker.*` and are applied via ordered, embedded migrations (`pkg/broker/install/sql/migrations/`), tracked in `broker.broker_schema_migrations`.

### Tables

- **broker_queueinstance**: Tracks active broker queue instances (one per database)
- **broker_jobs**: Job queue with status, lease, attempt, tenant, and `job_group` tracking
- **broker_job_dependency**: Dependency edges, each targeting either a `depends_on_job_id` or a `depends_on_group`, enforced at claim time
- **broker_schedule**: Scheduled jobs (cron-like functionality)

### Stored Procedures

- **broker_get**: Fetch and lease the next claimable job from a queue (skips jobs with an incomplete id- or group-dependency)
- **broker_run**: Execute a job
- **broker_set**: Set runtime options (user, application_name, etc.)
- **broker_set_tenant**: Set the `broker.tenant_id` GUC used by RLS policies
- **broker_add_job**: Add a new job to the queue, with an optional `job_group` and dependencies by id and/or group
- **broker_add_job_simple**: Shortcut for `broker_add_job` — name, execute string, priority, and dependencies by group name
- **broker_register_instance / broker_ping_instance / broker_shutdown_instance**: Instance lifecycle tracking
- **broker_recover_stale_jobs**: Reclaim jobs whose lease has expired

### Roles (optional, via `install --with-roles`)

See `pkg/broker/install/sql/roles/0001_roles.sql` — `broker_admin`, `broker_runtime`, `broker_enqueue` (see table above).

## Configuration Reference

See [broker.example.yaml](./broker.example.yaml) for a complete configuration example, or [broker.docker.example.yaml](./broker.docker.example.yaml) for the Docker Compose variant.

### Database Settings

The `databases` array can contain multiple database configurations. Each entry supports:

| Setting | Description | Default |
| ------- | ----------- | ------- |
| `name` | Unique identifier for this database | **Required** |
| `host` | PostgreSQL host | `localhost` |
| `port` | PostgreSQL port | `5432` |
| `database` | Database name | **Required** |
| `user` | Database user | **Required** |
| `password` | Database password | - |
| `sslmode` | SSL mode | `disable` |
| `max_open_conns` | Max open connections | `25` |
| `max_idle_conns` | Max idle connections | `5` |
| `conn_max_lifetime` | Connection max lifetime | `5m` |
| `conn_max_idle_time` | Connection max idle time | `10m` |
| `queue_count` | Number of queues for this database | `4` |
| `tenant_id` | Tenant id set for this connection (RLS) | `default` |

### Broker Settings

Global settings applied to all database instances:

| Setting | Description | Default |
| ------- | ----------- | ------- |
| `name` | Broker instance name | `pgsql-broker` |
| `fetch_query_que_size` | Jobs per fetch cycle | `100` |
| `queue_timer_sec` | Seconds between polls | `10` |
| `queue_buffer_size` | Job buffer size | `50` |
| `worker_idle_timeout_sec` | Worker idle timeout | `10` |
| `notify_retry_seconds` | NOTIFY retry interval | `30s` |
| `enable_debug` | Enable debug logging | `false` |
| `lease_seconds` | Job lease duration before reclaimable | `60` |
| `stale_job_recovery_sec` | Interval for reclaiming expired leases | `30` |
| `metrics_enabled` | Enable Prometheus and the embedded dashboard | `true` |
| `metrics_host` | Metrics HTTP bind host | `0.0.0.0` |
| `metrics_port` | Metrics HTTP bind port | `9469` |
| `queue_depth_poll_sec` | Queue depth collection interval | `15` |

## Development

### Building

```bash
make build       # Build the binary
make clean       # Clean build artifacts
make deps        # Download dependencies
make fmt         # Format code
make vet         # Run go vet
make test        # Run tests
```

### Testing

```bash
go test ./...                          # unit tests
go test -v ./tests/integration/...     # integration tests (needs local Postgres, see below)
docker-compose -f docker-compose.test.yml up --build --abort-on-container-exit --exit-code-from tests
```

Integration tests expect Postgres reachable at `127.0.0.1:5433` (see `tests/integration/`), including `rls_test.go` (multi-tenant isolation) and `stage5_test.go`. `127.0.0.1` is used instead of `localhost` because some CI Docker hosts publish container ports on IPv4 only, and `localhost` can resolve to `::1` first and fail.

### Project Structure

```text
pgsql-broker/
├── cmd/broker/          # CLI application
├── pkg/broker/          # Core broker package
│   ├── adapter/         # Database & logger interfaces
│   ├── config/          # Configuration management
│   ├── models/          # Data models
│   ├── queue/           # Queue management
│   ├── worker/          # Worker implementation
│   └── install/         # Database installer with embedded SQL
│       └── sql/
│           ├── migrations/ # Ordered, versioned schema migrations (embedded in binary)
│           └── roles/      # Optional least-privilege role DDL (--with-roles)
├── tests/integration/   # Go integration test suite
├── examples/            # Usage examples
├── Dockerfile           # Production broker image
├── Dockerfile.test      # Integration test runner image
├── docker-compose.yml   # Postgres + migrate + broker
├── docker-compose.test.yml # Postgres + test runner
└── Makefile             # Build automation
```

## Contributing

Contributions are welcome! Please ensure:

- Code is formatted with `go fmt`
- Tests pass with `go test ./...`
- Documentation is updated

## License

See [LICENSE](./LICENSE) file for details.