Order Processing Pipeline with Audit Trail

Problem Description

A logistics company runs an e-commerce fulfillment operation. They need a Postgres database to manage customer orders, order lines, and a background job queue that processes shipments. The operations team also requires a complete audit trail so they can reconstruct exactly what changed in any order record, and who changed it, at any point in the past.

The order system has a hierarchy: a customer places orders, each order has multiple order lines, and each order line can have multiple shipment records. Engineers have previously used surrogate keys (BIGSERIAL) for every table, but this caused painful join chains — the team lead wants the new design to encode the parent-child relationship directly in the primary keys.

The fulfillment pipeline is built around a worker pool: multiple workers run concurrently and each claims the next available pending shipment job without interference from other workers. Jobs can be in states: pending, in_progress, completed, failed. Each job attempt is tracked separately. The claim operation must be atomic and must not block workers that are claiming different jobs.

To handle peak traffic, a separate stored procedure is used to create new order lines. It must validate that the parent order exists and is not already closed, then insert the line atomically. The procedure should raise structured, machine-readable errors that client applications can catch and handle by error code rather than by parsing message strings.

Output Specification

Produce the following files:

• schema.sql — DDL creating all tables, domains, constraints, triggers, functions, and the job queue infrastructure. The script must be executable against a clean Postgres database.
• procedures.sql — The stored procedure(s) for creating order lines. Include the complete PL/pgSQL bodies.
• audit.sql — The audit trigger infrastructure for order line changes: the audit table, trigger function, and trigger attachment.

Each file should be self-contained (or clearly note any dependency order if they must be run in sequence). Do not leave large intermediate files on disk.