How I Use JSONB to Design Flexible Workflows in PostgreSQL

When you’re building systems that need dynamic logic—like workflows, status transitions, or custom forms—hardcoding every state and rule just doesn’t scale.

That’s why I started using PostgreSQL’s JSONB to store workflow definitions. It’s flexible, fast, and easy to work with from Python. More importantly, it lets non-devs configure logic without touching code.

Here’s how I architect workflow systems using JSONB in PostgreSQL—from schema to querying to real-world editing.

Why JSONB?

I needed something that:

• Could store nested, schema-less structures (like arrays of statuses, transitions, etc.)
• Was easy to read/write in Python
• Allowed me to query and update specific keys without loading the whole object in app memory
• Didn't require a full NoSQL system or multiple relational tables for edge cases

PostgreSQL’s JSONB gave me all that, plus:

• Indexing support
• Powerful built-in query operators
• Native support in most ORMs

Real Example: Job Workflow Definition

Here’s a sample of what a job_workflows table entry might look like:

{
  "statuses": [
    { "id": "pending", "name": "Pending", "color": "#e0e0e0", "next": ["review"] },
    { "id": "review", "name": "Under Review", "color": "#2196f3", "next": ["approved", "rejected"] },
    { "id": "approved", "name": "Approved", "color": "#4caf50", "next": [] },
    { "id": "rejected", "name": "Rejected", "color": "#f44336", "next": [] }
  ],
  "start": "pending"
}

This lives inside a single data JSONB field in the job_workflows table.

Benefits of This Approach

• Schema-flexible: Different workflows can have different numbers of statuses and transitions.
• UI-friendly: I use this structure directly in PyQt and Vue components.
• Easy to diff/validate: I can compare JSON versions and even apply constraints (like detecting unreachable statuses).
• Editable via admin UI: I built form editors that let users manage this without writing JSON directly.

How I Query JSONB in Python (with psycopg2 or SQLAlchemy)

Let’s say I want to get all workflows where a status with id review exists.

SELECT * FROM job_workflows
WHERE data @> '{"statuses": [{"id": "review"}]}';

In Python (with raw SQL or SQLAlchemy), it’s straightforward:

query = text("""
    SELECT * FROM job_workflows
    WHERE data @> :pattern
""")
conn.execute(query, {'pattern': json.dumps({"statuses": [{"id": "review"}]})})

Or, to get all workflows that start with pending:

SELECT * FROM job_workflows
WHERE data ->> 'start' = 'pending';

Updating Nested JSONB Structures

Suppose I want to add a new status or modify the next transitions. I usually load, edit, then write back:

workflow = conn.execute(
    "SELECT data FROM job_workflows WHERE id = %s", (workflow_id,)
).fetchone()[0]

workflow['statuses'].append({
    "id": "archived",
    "name": "Archived",
    "color": "#9e9e9e",
    "next": []
})

conn.execute(
    "UPDATE job_workflows SET data = %s WHERE id = %s",
    (json.dumps(workflow), workflow_id)
)

This is safe and clean for small JSON blobs. For bigger use cases, you might want to update specific paths using PostgreSQL’s jsonb_set().

Indexing for Performance

I usually create a GIN index like this:

CREATE INDEX idx_job_workflows_data ON job_workflows USING GIN (data);

This allows fast lookup for @> queries, even on deeply nested structures.

Admin UI for Editing

Once the structure is stable, I build admin tools to manage the JSON visually. For example, in my PyQt app:

• Statuses appear in a table with editable fields (id, name, color)
• The next field becomes a multi-select dropdown
• I validate things like duplicate IDs or cycles before saving

I’ve also done this in Nuxt with a JSON schema-based form generator, making it frontend-agnostic.

Gotchas and Tradeoffs

While JSONB is powerful, there are trade-offs:

• You lose some referential integrity—e.g., no foreign key constraints inside JSON
• Querying deep nested data can get verbose
• You need strong validation logic on the app side

But for config-style data that changes often or varies per customer, JSONB is ideal.

Summary

Using JSONB to define workflows gave me:

• Dynamic flexibility with structure
• Queryable and indexable data in SQL
• Direct mapping to UI components
• Clean decoupling from rigid relational schemas

It’s not a silver bullet—but for workflows, permissions, settings, or anything schema-light with high variability, it’s a perfect fit.

If you're also working with JSONB or have creative patterns for managing dynamic logic, let’s swap notes. Always happy to learn how others model real-world systems with PostgreSQL.