Three-layer architecture

pgflow separates concerns across three distinct layers. Each layer operates at a different conceptual level and solves different classes of problems.

Overview

The diagram shows two key distinctions:

Build-time - Write flows in TypeScript, compile to SQL migrations

Run-time - Postgres orchestrates (SQL Core), workers execute your functions (Worker)

Layer responsibilities

Each layer has a distinct responsibility:

Layer	Thinks about	Lives where
DSL	”Process array items in parallel with independent retries per item”	Your repo
SQL Core	”This step is ready, spawn N tasks, aggregate results”	Postgres functions
Worker	”Execute this handler with input, return output or error”	Edge Function (or any runtime)

The key insight: each layer solves problems at its own abstraction level without understanding the others’ concerns.

DSL layer - User intent

Problem domain: How do users express flows naturally?

The TypeScript DSL provides:

Type-safe method chaining (.step(), .array(), .map())
Pattern recognition for complex workflows
Compilation from high-level concepts to DAG primitives
Full type inference across step dependencies

What it doesn’t think about: How tasks execute, database state management, or queue mechanics

Example:

new Flow<Input>({ slug: "process_users" })
  .step({ slug: "fetch_users" }, fetchUsers)
  .array({ slug: "users_array" }, (input) => input.fetch_users)
  .map({ slug: "send_email" }, sendEmail)

The DSL knows this is a map pattern and generates the appropriate step definitions. It doesn’t know or care how the SQL Core will spawn tasks or how workers will execute them.

SQL Core layer - Workflow orchestration

Problem domain: How do flows execute reliably?

The SQL Core handles:

Dependency resolution (which steps are ready)
Step type behaviors (single vs map execution patterns)
Task spawning and result aggregation
Transactional state management
Completion detection

What it doesn’t think about: Why steps exist, what DSL syntax created them, or user intent

Example: The SQL Core sees step definitions with clear semantics:

step_type='single' means spawn 1 task
step_type='map' means spawn N tasks from dependency array
Dependencies met = ready to spawn tasks

It executes these primitives reliably without needing to understand that the DSL created them from a .map() method.

Worker layer - Task execution

Problem domain: How do tasks run safely?

The Worker handles:

Handler function invocation
Input/output transformation
Error handling and reporting
Task-level retry logic

What it doesn’t think about: Where tasks come from, what depends on them, or flow context

Example: The worker receives a task with:

{
  handler: sendEmail,
  input: { user: { email: "[email protected]" } }
}

It executes the handler, returns the result or error. It doesn’t know this task is part of a map step, or that other tasks exist, or what step this belongs to.

The queue processing pattern

If you’ve written queue processing code, pgflow’s execution model will feel natural:

msg = pgmq.read()       // 📥 Get work
result = process(msg)   // ⚙️ Do work
pgmq.send(next_msg)     // 📤 Queue next

👆 This is the core loop. pgflow extends this pattern across multi-step workflows - each completed step automatically triggers its dependents.

The SQL Core manages which tasks to enqueue, the Worker executes them, and Postgres tracks all state transitions.

Layer interactions

Each layer provides reliable primitives for the layer above:

DSL → SQL Core

DSL compiles to step definitions with clear semantics
SQL Core doesn’t parse TypeScript or understand user intent
Contract: step definitions with step_type, dependencies, options

SQL Core → Worker

SQL Core spawns tasks when dependencies are met
Worker doesn’t track dependencies or flow state
Contract: task with handler function, input data, retry config

Worker → SQL Core

Worker executes handler, reports success/failure
SQL Core updates state, checks for newly ready steps
Contract: task completion with output or error

Learn More

How pgflow Works Practical introduction with state machine and examples

Data Model SQL schema, tables, and relationships