pgsql-broker/plan/docs/recommendations.md

PostgreSQL Schema and Function Recommendations

/home/warkanum/.claude/plans/binary-puzzling-mountain.md Review date: 2026-09-14
Scope: pkg/broker/install/sql tables and functions, including their interaction with the Go worker.

Validated against authoritative PostgreSQL documentation on 2026-09-15; see research_validation.md for sources, verification method, and assumptions. Inline notes below marked "(validated: ...)" summarize that document's amendments.

Conclusion

The current schema is a credible internal prototype, but it is not yet a reliable production job queue. It has correctness problems that can strand jobs, lacks a safe RLS/execution-identity design, and needs standard operational queue features such as leases, retries, and migrations.

It is suitable only for a controlled environment with a single trusted operator and simple successful jobs until the priority items below are addressed.

Prioritized recommendations

Priority	Problem	Recommendation
Critical	A failed job is marked failed inside broker_run, then the function returns a nonzero result. The Go worker rolls back its transaction on nonzero results, so the failed update is rolled back and the job remains running forever.	Treat an expected job failure as a committed job outcome, while reserving nonzero function errors for infrastructure failures; alternatively persist the failure in a separate transaction. Add stale-running-job recovery. (validated: converting broker_run to a CREATE PROCEDURE to commit independently only works if the outer EXCEPTION WHEN OTHERS block is removed/restructured — PL/pgSQL cannot run COMMIT/ROLLBACK inside an exception handler.)
Critical	The notification handler passes a newly pending job directly to broker_run, but broker_run requires status running. The notification attempt fails and polling eventually processes the job.	Use NOTIFY only to wake workers. Every work attempt must claim a job through broker_get and FOR UPDATE SKIP LOCKED.
Critical	Arbitrary queued SQL executes with the broker role. This cannot be made safe for multi-tenant/RLS use without an explicit execution model.	Prefer approved job procedures or job types with structured arguments. Add immutable tenant and execution-principal references. Use a least-privileged runtime role that does not own tenant tables and lacks BYPASSRLS.
High	Active-instance registration is race-prone and the Go process can reuse another process's instance ID.	Fail a second startup. Use a session-held PostgreSQL advisory lock for exclusive ownership, with the instance table retained for observability and heartbeats.
High	Jobs have no retry policy, lease expiry, dead-letter state, idempotency key, attempt counter, or backoff.	Add attempt_count, max_attempts, available_at, leased_at, lease_expires_at, lease_token, and terminal/dead-letter handling.
High	Dependencies use mutable, non-unique names and treat running, failed, or cancelled dependencies as satisfied.	Replace depends_on text[] with broker_job_dependency(job_id, depends_on_job_id). Permit execution only when all dependencies completed successfully, and define behaviour for failed dependencies.
High	Schema installation is not idempotent because existing triggers cause reinstallation failures. There is no migration/version tracking.	Use ordered, transactional migrations with a schema-version table. Make trigger creation idempotent or recreate triggers safely. (validated: CREATE OR REPLACE TRIGGER requires PostgreSQL 14+; on earlier versions use DROP TRIGGER IF EXISTS followed by CREATE TRIGGER.)
Medium	Job execution is inside the claim transaction. It is atomic but holds locks through arbitrary job SQL and prevents transaction-controlling work.	Explicitly choose a short lease/ack design with idempotent jobs, or document the atomic transaction limitation and restrict job types accordingly.
Medium	run_as is unused. broker_set permits persistent session changes and its search_path branch is unsafe.	Remove identity controls until fully designed. Use only whitelisted transaction-local settings via SET LOCAL or set_config(..., true).
Medium	Existing indexes do not exactly serve queue claiming by queue, pending status, descending priority, and creation time.	Add and validate a partial claim index: (job_queue, job_priority DESC, created_at, id_broker_jobs) WHERE complete_status = 0.
Medium	Objects are unqualified and depend on search_path, complicating future privilege/RLS work.	Create a dedicated broker schema, schema-qualify all references, revoke PUBLIC access, and grant only the roles that need each operation.

Correctness failure to fix first

The present worker and function behavior can permanently strand a failed job:

broker_get → marks job running
broker_run → job SQL fails → marks job failed, returns error
Go worker → sees error → ROLLBACK
Result → failed update is rolled back; job stays running indefinitely

Related implementation:

• pkg/broker/install/sql/procedures/02_broker_run.sql
• pkg/broker/worker/worker.go

Recommended target model

broker_job
  id, tenant_id, job_type, arguments jsonb, status,
  priority, available_at, attempt_count, max_attempts,
  lease_token, leased_at, lease_expires_at,
  created_by, completed_at, result, error

broker_job_dependency
  job_id, depends_on_job_id

Use a dedicated broker schema and separate roles:

1. A non-login migration/schema-owner role.
2. A least-privileged broker runtime role, with no BYPASSRLS and no ownership of tenant tables.
3. Narrowly scoped enqueue/application roles.

RLS must use immutable tenant_id and a transaction-local, trusted tenant context. It must not be driven by arbitrary queued SQL or an unvalidated run_as field. If the runtime role ever owns the tenant tables (e.g. because it also ran migrations), it must also run ALTER TABLE ... FORCE ROW LEVEL SECURITY, since table owners otherwise bypass RLS by default (Row Security Policies).

Delivery order

1. Correct rollback/error outcomes and change notifications into worker wake-ups.
2. Establish the role, privilege, RLS, and tenant-context design.
3. Implement leases, retries, stale-job recovery, and idempotency.
4. Replace installation scripts with idempotent versioned migrations.
5. Add integration coverage for failed jobs, duplicate starts, stale leases, RLS isolation, dependency outcomes, and repeat migration runs.

Cleanup and maintenance

The current design only appends history. Completed jobs, error messages, execution results, and historical broker instances will grow indefinitely. This will eventually increase index size, autovacuum work, backup duration, and query latency. Maintenance must be a designed, observable capability rather than an ad hoc deletion script.

Retention policy

Define retention separately for successful jobs, failed/dead-letter jobs, job output/error text, scheduled jobs, and historical instance records. Retention must account for business audit, incident investigation, tenant requirements, and legal obligations before data is deleted.

Recommended implementation:

1. Store a terminal timestamp and an immutable tenant identifier on every job.
2. Add an archival state or archive table when completed job history remains useful beyond the online retention window.
3. Implement a broker-owned maintenance procedure that deletes or archives only terminal records older than a configured retention period, in bounded batches.
4. Enforce tenant-aware RLS and authorization for retained job payloads, results, and error messages; these fields can contain sensitive data.
5. Emit counts and oldest-record timestamps before and after each maintenance run.

Runtime reconciliation

Add scheduled maintenance operations for:

1. Expiring leases held by crashed workers and returning eligible jobs to the queue.
2. Marking stale broker instances inactive after a defined heartbeat timeout, without allowing accidental takeover of a live owner.
3. Retrying transient failures according to backoff and maximum-attempt rules; routing exhausted jobs to a dead-letter state.
4. Identifying jobs that have been running longer than their configured maximum duration.
5. Detecting queue backlog, oldest pending job age, retry/dead-letter count, and worker heartbeat health.

Database health

Frequent status updates produce dead tuples and index churn. Operational documentation should specify:

1. Autovacuum monitoring and table-specific tuning after measuring production load.
2. ANALYZE and index-health monitoring for the job claim query.
3. Bounded deletion batches to avoid long-running transactions, replication lag, and lock pressure.
4. Optional time partitioning for large, retention-oriented job history tables; do not partition prematurely for a small deployment.
5. Backup/restore testing and a migration rollback/recovery procedure.

Guardrails

Never purge pending, leased, running, failed, or dead-letter jobs merely because they are old. Automated cleanup must operate only on explicitly eligible terminal states, use a documented retention configuration, and be dry-run capable before destructive execution.

Scale readiness: millions of reads and writes

The current tables can support modest workloads, but they are not designed for sustained millions of job events. In particular, updating a single wide broker_jobs row through every lifecycle state creates table and index churn; retaining results and errors in that same hot table makes every queue scan increasingly expensive; and the current one-active-broker rule prevents horizontal worker scaling within a database.

Target storage model

Separate the hot queue from append-heavy operational history:

broker_job                    -- current mutable queue state; narrow and hot
  id, tenant_id, queue_id, status, priority, available_at,
  attempt_count, lease_token, lease_expires_at, created_at

broker_job_payload            -- command/type and structured arguments; accessed after claim
  job_id, job_type, arguments jsonb, created_by

broker_job_execution          -- append-only attempts, results, errors, timings
  id, job_id, attempt_no, state, started_at, completed_at, error, result

broker_job_event              -- optional append-only audit/event stream
  id, job_id, tenant_id, event_type, occurred_at, metadata jsonb

Keep the claim path narrow: it should not scan or update large payload/result columns. Treat result/error retention as a separate policy and keep sensitive fields protected by tenant-aware RLS.

Claim and indexing strategy

1. Claim jobs atomically with FOR UPDATE SKIP LOCKED and an immediate lease update, ideally in one statement or a tightly scoped function. Do not select a job and later claim it in a separate transaction.
2. Use a partial index for claimable work, such as (queue_id, priority DESC, available_at, id) WHERE status = 'pending'. Validate the exact form with EXPLAIN (ANALYZE, BUFFERS) against representative data.
3. Keep status and queue keys compact (smallint/foreign key where suitable) and avoid indexing every mutable column. Every index on frequently updated rows adds write amplification.
4. Support bounded batch claims only after measuring contention and fairness; never use offset pagination for queue work.
5. Add idempotency keys with an appropriate unique constraint so client retries do not create duplicate jobs.

Partitioning and retention

Partition large append-only execution/event history by time, normally monthly at first, so retention can detach/archive/drop old partitions efficiently. Consider partitioning the mutable job table only after measurement: its unique-key and foreign-key constraints become more complex, and its partition key must be reflected in uniqueness design.

For very large multi-tenant workloads, assess hash partitioning by tenant or queue only when it demonstrably reduces contention; it is not a substitute for correct indexes and bounded retention.

Horizontal operation

Millions of events require multiple broker processes and workers to claim work concurrently. Replace the current global single-instance restriction with one of these deliberate models:

1. Multiple stateless workers sharing the same queue and using row leases plus SKIP LOCKED; or
2. One elected scheduler/maintenance leader with many independent queue workers.

The leader lock must protect only leader duties such as schedule creation and reconciliation. It must not serialize normal job claiming. Worker ownership must be represented by lease tokens and expiry, not a permanent single active instance row.

PostgreSQL operating requirements

1. Use a dedicated, session-persistent connection for LISTEN; transaction-pooling proxies cannot safely carry listener state. Use separate pooled connections for claims and execution.
2. Treat NOTIFY as a low-latency wake-up only. Its payload must be shorter than 8000 bytes by default (NOTIFY) and notification delivery must not be the sole source of truth; periodic/bounded polling remains necessary.
3. Tune connection-pool sizes per database capacity, rather than multiplying workers and connections without a budget.
4. Monitor queue depth, oldest-ready-job age, claim latency, execution latency, lock waits, dead tuples, autovacuum progress, index size, WAL volume, replication lag, and database connection saturation.
5. Load-test realistic payload sizes, tenant distributions, job durations, retry rates, and failure modes before selecting partitions, worker counts, or autovacuum settings.

Capacity acceptance criteria

Before production rollout, establish and automate tests for the intended workload:

1. Sustained and burst enqueue/claim/complete rates at the target million-event volume.
2. Correctness under concurrent workers, duplicate client submissions, broker crashes, worker lease expiry, and notification loss.
3. RLS isolation across concurrent tenants at load.
4. Stable p95/p99 claim latency and bounded table/index growth over a retention cycle.
5. Successful archival/purge, backup, restore, and migration exercises using production-scale representative data.

Additional platform improvements

Delivery semantics and transactional outbox

Define at-least-once delivery as the broker's explicit contract. Exactly-once execution is generally not achievable across process and database failures, so job handlers must be idempotent. Add idempotency keys and enforce them with an appropriate unique constraint.

For work created as part of an application database change, implement a transactional outbox. The application transaction should record both the business change and the intent to enqueue work; a reliable dispatcher then creates or exposes the broker job after commit. This prevents a committed business change from losing its corresponding job due to an application crash between separate writes.

Fairness, quotas, cancellation, and time limits

Add per-tenant and per-queue concurrency caps, rate limits, and quotas so one tenant cannot exhaust workers or storage. Implement priority ageing so a continuous stream of high-priority jobs does not permanently starve lower-priority work.

Add a cancellation state and cooperative cancellation protocol. Jobs should support deadlines, lease-aware cancellation checks, and configured statement_timeout and lock_timeout values. The broker must distinguish a user cancellation from a timeout, execution failure, or expired lease.

Cross-queue dependency scheduling

The broker must support dependencies between jobs in different queues without allowing a blocked job to stall a queue. Queue membership determines which worker can claim a job; dependency eligibility determines whether that job is currently runnable. They are independent concerns.

Replace the current depends_on text[] column with a normalized dependency table:

broker_job_dependency
  job_id                 bigint not null references broker_job(id)
  depends_on_job_id      bigint not null references broker_job(id)
  created_at             timestamptz not null
  primary key (job_id, depends_on_job_id)
  check (job_id <> depends_on_job_id)

When claiming work for one queue, the claim query must select only jobs for which every dependency has completed successfully. Its eligibility predicate should have this shape:

NOT EXISTS (
  SELECT 1
  FROM broker_job_dependency d
  JOIN broker_job dependency ON dependency.id = d.depends_on_job_id
  WHERE d.job_id = candidate.id
    AND dependency.status <> 'completed'
)

Combined with ORDER BY priority DESC, available_at, id FOR UPDATE SKIP LOCKED, this lets a worker skip blocked work and claim the next runnable job in its own queue. A dependency can therefore be in any other queue without blocking unrelated jobs.

Required behavior and safeguards:

1. A job may run only after every dependency is completed; running, pending, failed, cancelled, and dead_letter are not success.
2. When a dependency reaches a non-success terminal state, mark dependent jobs blocked or cancelled with a recorded reason. Do not leave them pending forever.
3. Reject self-dependencies and detect cycles when dependencies are created. Use a recursive validation query or a transactionally maintained acyclic graph policy.
4. Create jobs and their dependency rows atomically in one transaction; a job must never become visible without its full dependency set.
5. Index both lookup directions: primary key (job_id, depends_on_job_id) for eligibility checks and (depends_on_job_id, job_id) for propagating a dependency completion/failure to dependents.
6. Restrict dependencies to the same tenant by default. Cross-tenant dependencies require an explicit sharing/authorization model compatible with RLS.
7. On dependency completion or terminal failure, notify affected queues as an optimization. Polling/claim scans remain the source of truth, so missed notifications do not affect correctness.

Test this with dependencies across multiple queues, multiple workers, priority ordering, concurrent completion, failed/cancelled prerequisites, cycles, duplicate dependency creation, RLS isolation, and notification loss.

Run groups and named dependency targets

Jobs must support a run_group name, and a dependency declaration must be able to target either a job name or a run-group name. Names are useful at the API boundary, but the broker must resolve them to immutable IDs for reliable runtime scheduling.

Recommended model:

broker_run_group
  id, tenant_id, name, state, completion_policy, sealed_at, created_at
  unique (tenant_id, name)

broker_job
  id, tenant_id, run_group_id, job_name, queue_id, status, ...
  unique (run_group_id, job_name)

broker_job_dependency
  job_id, dependency_kind, depends_on_job_id, depends_on_group_id
  -- dependency_kind is 'job' or 'group'; exactly one target ID is non-null

The enqueue API may accept depends_on: ['daily-import', 'validate-customers'], but each reference must be explicitly typed as a job or group when names could overlap. For example:

[
  { "kind": "group", "name": "daily-import" },
  { "kind": "job", "group": "daily-import", "name": "validate-customers" }
]

Required semantics:

1. A job dependency is satisfied only when its referenced job is completed.
2. A group dependency is satisfied only when the group is sealed and all jobs in that group are completed, unless an explicitly selected alternative completion policy says otherwise.
3. An empty sealed group completes successfully by default; this should be documented and configurable only if a different business rule is required.
4. If any member of a prerequisite group fails, is cancelled, or reaches dead-letter status, propagate a blocked or cancelled terminal outcome to dependent jobs, with the failed member recorded as the reason.
5. A group must be sealed before jobs outside it may rely on its completion. This prevents adding a new job after a dependent has already started and silently changing the meaning of the dependency.
6. Group and job names are tenant-scoped. Job names are unique only within their run group, so an unqualified job-name dependency is invalid unless the API supplies its target group or a separately tenant-unique job key.
7. Create a run group, its jobs, and all resolvable dependency edges in one transaction. Resolve names to IDs during that transaction and reject missing references, ambiguous names, self-dependencies, and cycles.
8. If future references are genuinely required, model them as a separate, explicit deferred-reference state with a deadline and reconciliation process. Do not silently treat a missing job or group name as an already satisfied dependency.

Queue claiming remains unchanged: a worker considers only jobs in its own queue, then evaluates the resolved job/group dependencies. An unresolved or incomplete group/job is ineligible, so the worker skips it and moves to the next runnable candidate in that queue.

Index broker_run_group on (tenant_id, name), jobs on (run_group_id, job_name), dependency targets in both directions, and maintain group completion state incrementally or through an indexed aggregate query. Avoid scanning every historical group member during every queue claim at high volume.

Typed payloads and contracts

Replace raw SQL text with versioned job types and validated structured arguments, normally jsonb. Establish payload-size limits and use external object/blob storage when job input or output is too large for a hot operational table.

Define stable enqueue, status, retry, and cancellation API contracts. Document terminal states, error codes, compatibility expectations, and job-type versioning so clients can evolve safely.

Scheduling

Jobs must support cron-style schedules for hourly, daily, weekly, and more complex recurring execution. Implement scheduling fully rather than retaining the current unused schedule schema.

Use an explicit schedule model:

broker_schedule
  id, tenant_id, name, cron_expression, timezone, enabled,
  job_template/type and arguments, queue_id, priority,
  misfire_policy, next_run_at, last_run_at, created_at, updated_at
  unique (tenant_id, name)

broker_schedule_run
  id, schedule_id, scheduled_for, run_group_id, job_id,
  state, created_at
  unique (schedule_id, scheduled_for)

Required behavior:

1. Define and document a single cron dialect, including whether it has five fields (minute through day-of-week) or seconds. Validate expressions at create/update time using the same parser the scheduler uses.
2. Require an IANA timezone, such as Africa/Johannesburg, on every schedule. Do not use a server-local default. Compute next_run_at in the configured timezone and persist it as timestamptz.
3. Materialize every due occurrence using a unique (schedule_id, scheduled_for) key. This makes scheduler retries and leader failover idempotent and prevents duplicate hourly/daily/weekly jobs.
4. Define a per-schedule misfire policy for downtime: skip, run_once, or bounded catch_up. Establish a maximum catch-up count/window so an extended outage cannot flood the queue.
5. Define daylight-saving behavior explicitly: for a local time that occurs twice, run once or twice by policy; for a nonexistent local time, skip or run at the next valid instant by policy. Test both cases.
6. Have only an elected scheduler leader create due runs. Use a database advisory lock or equivalent short-lived leader lease; normal workers must remain horizontally scalable and independent.
7. Each occurrence should create a new immutable job or run group, for example schedule:<schedule-id>:<scheduled-for>. A run must never mutate a previous occurrence's job.
8. For recurring workflows with multiple dependent jobs, create an occurrence-specific run group and resolve dependency edges only within that occurrence. A daily run must not accidentally depend on a job from yesterday's or tomorrow's run.
9. Respect tenant RLS when managing schedules and generated jobs. The scheduler needs a controlled tenant execution context, not unrestricted access to all tenant data.
10. Publish schedule health metrics: next-run lateness, due-run creation latency, misfires, disabled schedules, duplicate-prevention conflicts, and generated-job failures.

The scheduler should use database time when deciding what is due, or otherwise use one explicitly controlled time source. Do not rely on each broker host's local clock.

Observability and operations

Include job ID, tenant ID, request/correlation ID, job type, attempt number, and execution owner in structured logs. Publish metrics and traces for enqueue, claim, execution, retry, failure, cancellation, lease expiry, queue depth, and oldest-ready-job age.

Define dashboards and alerts for backlog growth, SLA breach risk, dead-letter growth, worker health, database saturation, lock waits, autovacuum lag, replication lag, and maintenance failure.

Resilience, testing, and deployment

Test backup and restore, active-worker crashes, duplicate notifications, broker upgrades, PostgreSQL failover, notification loss, and migration recovery. Add property and concurrency tests for claim correctness, permission/RLS tests, schema migration tests, and production-shaped load/soak tests.

Provide health and readiness probes, graceful draining on shutdown, secret-management integration, strict configuration validation, and a safe zero-downtime upgrade process.

Data governance

Define job payload, result, and error-message retention; PII redaction; audit access; tenant data export; and tenant deletion rules. Apply these controls consistently to hot tables, archives, logs, traces, backups, and dead-letter records.