Monitor Order Fulfillment with PostgreSQL and VeloDB

A paid order can still sit unshipped for hours. Operations teams need a simple way to find these orders before they become customer complaints.

In this tutorial, PostgreSQL continues to process order updates while VeloDB maintains an analytical copy of the relevant tables. A single query finds paid orders that have not shipped within one hour and adds the merchant information needed to investigate them.

Order updates in PostgreSQL
        ↓
Full and incremental sync into VeloDB
        ↓
Join orders with merchant information
        ↓
Find paid orders that are more than one hour late

All names, IDs, regions, timestamps, and amounts in this tutorial are synthetic.

Prerequisites

You need:

• A PostgreSQL database that VeloDB Cloud can reach.
• A VeloDB Cloud warehouse. Follow the VeloDB Cloud Quick Start if you need to create one.
• A PostgreSQL user that can read the selected tables and stream their changes.
• Logical write-ahead logging enabled on the PostgreSQL source.

This tutorial used a hosted PostgreSQL instance from Aiven as a reproducible source. Aiven is not required. You can use another reachable PostgreSQL deployment.

1. Create the sample order data

Connect to the PostgreSQL database and schema that you want to use. This tutorial uses a provider-created database and its public schema.

Run the following PostgreSQL SQL. Orders 10001 and 10003 are overdue, while order 10002 has already shipped.

CREATE TABLE IF NOT EXISTS merchants (
  merchant_id BIGINT PRIMARY KEY,
  merchant_name VARCHAR(100) NOT NULL,
  region VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS orders (
  order_id BIGINT PRIMARY KEY,
  merchant_id BIGINT NOT NULL,
  customer_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,
  paid_at TIMESTAMP,
  shipped_at TIMESTAMP,
  amount NUMERIC(10, 2) NOT NULL,
  updated_at TIMESTAMP NOT NULL
);

INSERT INTO merchants (merchant_id, merchant_name, region)
VALUES
  (101, 'Northstar Home', 'East'),
  (102, 'Greenfield Electronics', 'South')
ON CONFLICT (merchant_id) DO UPDATE
SET merchant_name = EXCLUDED.merchant_name,
    region = EXCLUDED.region;

INSERT INTO orders (
  order_id, merchant_id, customer_id, status,
  paid_at, shipped_at, amount, updated_at
)
VALUES
  (
    10001, 101, 90001, 'paid',
    NOW() - INTERVAL '2 hours', NULL, 299.00, NOW()
  ),
  (
    10002, 102, 90002, 'shipped',
    NOW() - INTERVAL '2 hours',
    NOW() - INTERVAL '80 minutes', 499.00, NOW()
  ),
  (
    10003, 101, 90003, 'paid',
    NOW() - INTERVAL '110 minutes', NULL, 99.00, NOW()
  )
ON CONFLICT (order_id) DO UPDATE
SET merchant_id = EXCLUDED.merchant_id,
    customer_id = EXCLUDED.customer_id,
    status = EXCLUDED.status,
    paid_at = EXCLUDED.paid_at,
    shipped_at = EXCLUDED.shipped_at,
    amount = EXCLUDED.amount,
    updated_at = EXCLUDED.updated_at;

The ON CONFLICT clauses make the sample reusable. Running the script again resets the same fictional rows instead of creating duplicates.

Verify the source data:

SELECT
  order_id,
  status,
  paid_at,
  shipped_at,
  amount
FROM orders
ORDER BY order_id;

2. Confirm logical replication support

Incremental synchronization uses PostgreSQL logical write-ahead logging. Check the current setting:

SHOW wal_level;

The expected result is logical. If the result is different, follow the configuration instructions from your PostgreSQL provider before creating the import job. The exact settings and permissions vary by deployment.

3. Sync PostgreSQL into VeloDB

Open Import Data in the VeloDB Cloud console, click + Add Import Job, and select PostgreSQL. For more information about the wizard, see Import Data.

Configure the source connection:

Setting	Example value
Job Name	postgres_order_demo_to_velodb
Host	<your-postgresql-host>
Port	<your-postgresql-port>
Database	<your-postgresql-database>
User	<your-postgresql-user>
Password	Enter the password in the console. Do not add it to source code.
Enable SSL	Turn it on when the PostgreSQL provider requires TLS.
CA Certificate	Use the provider certificate when certificate verification is required.

Use the actual port shown by your provider instead of assuming the standard PostgreSQL port of 5432.

Then configure the destination and tables:

Setting	Value used in this tutorial
Target Database	Create order_demo_postgres_velodb
Sync Type	Full + Incremental
Source Schema	public
Tables to Migrate	public.orders and public.merchants
Sync Interval	60 seconds

Full + Incremental first copies the existing rows and then continues to capture new inserts and updates. An incremental-only job does not include rows that existed before the job started.

Keep Reverse Private Endpoint disabled for a publicly reachable demo database. A private production database may require private networking.

Do not publish the source hostname, connection URI, username, password, or certificate. The values above are placeholders.

4. Verify the initial sync

When the full sync completes, confirm that VeloDB contains three orders and two merchants:

SELECT COUNT(*) AS order_count
FROM order_demo_postgres_velodb.orders;

SELECT COUNT(*) AS merchant_count
FROM order_demo_postgres_velodb.merchants;

The previous source statements use PostgreSQL syntax. Queries in the VeloDB SQL Editor use VeloDB's MySQL-compatible SQL syntax, which is why the date functions look different in the next step.

5. Find overdue orders

Run the following query in VeloDB:

SELECT
  o.order_id,
  m.merchant_name,
  m.region,
  o.amount,
  o.paid_at,
  TIMESTAMPDIFF(MINUTE, o.paid_at, NOW()) AS minutes_waiting
FROM order_demo_postgres_velodb.orders AS o
JOIN order_demo_postgres_velodb.merchants AS m
  ON o.merchant_id = m.merchant_id
WHERE o.status = 'paid'
  AND o.shipped_at IS NULL
  AND o.paid_at < DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY o.paid_at;

The query combines the latest order status with merchant context and returns only orders that have exceeded the one-hour fulfillment threshold.

Orders 10001 and 10003 both require attention.

6. Mark an order as shipped in PostgreSQL

Update order 10001 in the source PostgreSQL database:

UPDATE orders
SET status = 'shipped',
    shipped_at = NOW(),
    updated_at = NOW()
WHERE order_id = 10001;

This update represents the normal business event that occurs when the warehouse ships an order.

Optional: run the update with Python

The following example reads connection details from environment variables, so no credentials are stored in the script:

python3 -m pip install "psycopg[binary]"

import os
import psycopg

connection_options = dict(
    host=os.environ["PGHOST"],
    port=os.environ["PGPORT"],
    dbname=os.environ["PGDATABASE"],
    user=os.environ["PGUSER"],
    password=os.environ["PGPASSWORD"],
    sslmode=os.environ.get("PGSSLMODE", "require"),
)

if "PGSSLROOTCERT" in os.environ:
    connection_options["sslrootcert"] = os.environ["PGSSLROOTCERT"]

with psycopg.connect(**connection_options) as connection:
    with connection.cursor() as cursor:
        cursor.execute("""
            UPDATE orders
            SET status = 'shipped',
                shipped_at = NOW(),
                updated_at = NOW()
            WHERE order_id = 10001
        """)

Use a secret manager for credentials in production. Never publish the source hostname, connection URI, username, password, or certificate.

7. Confirm the change in VeloDB

Wait for the configured synchronization interval, then run the overdue-order query again.

Order 10001 is no longer returned because it is now marked as shipped. The row was not deleted from VeloDB. It simply no longer matches the exception filter. Order 10003 remains because it is still paid, overdue, and unshipped.

This tutorial used a 60-second sync interval, and the update appeared in a subsequent sync cycle. This observation describes the tutorial configuration, not a general latency guarantee.

Why use VeloDB for this workflow?

PostgreSQL remains responsible for order transactions. VeloDB receives a continuously updated analytical copy and runs the operational query, including the join between order and merchant data.

This separation lets teams build fulfillment dashboards and exception reports without placing the same analytical workload on the transactional database. You can extend the pattern to cancellations, payment failures, warehouse delays, inventory shortages, and regional fulfillment trends.

Production considerations

• Use a dedicated PostgreSQL account with only the permissions required for synchronization.
• Confirm that logical replication and the required permissions are configured for the source deployment.
• Store credentials in a secret manager instead of scripts or screenshots.
• Use TLS certificate verification and private networking when appropriate.
• Choose a synchronization interval that matches the operational requirement, then validate it under the expected workload.
• Monitor synchronization status and lag before using the data for alerts.
• Replace the synthetic sample with approved and properly governed production data.

To build the same workflow from a MySQL source, see Monitor Order Fulfillment with MySQL and VeloDB.