PostgreSQL order-book contract

The curated PostgreSQL order book profile is version 1.1.0. It connects Ennea's managed worker to a separate source database. Use this reference to provision that reviewed contract; adapting another ERP schema requires a supported mapping with the source owner.

Files and prerequisites

• Fresh-database schema: tables, constraints, creation-event trigger and completion function, without lab seed data.
• Optional field descriptor: current permitted details and routing fields.
• One fictional source order: insert only after connection publication.

Use PostgreSQL with PL/pgSQL, gen_random_uuid(), and CREATE OR REPLACE TRIGGER support. The source administrator creates a separate database and scoped worker identity. The managed worker must reach the configured host/port. Select the intended Ennea site/environment and review external-action approval in Test. The scripts contain no credentials and do not create database users.

For a new empty training database, the source owner can run:

psql "$ORDER_BOOK_DATABASE_URL" -v ON_ERROR_STOP=1 -f order-book-schema.sql

Use your database's normal private authentication mechanism. Do not paste connection strings with passwords into shared terminals, screenshots or support requests. For an existing source database, review additive migrations with its owner instead of rerunning fresh table creation.

Source tables and semantics

Object	Contract
orders	order_number text primary key; required customer_name; status is open or completed; positive integer ordered_quantity; completed_quantity between zero and ordered quantity; updated_at timestamp.
additional_fields	JSON object on orders; optional keys described below. Unknown fields are not automatically exposed.
routing	JSON array on orders, up to 200 sequential operations.
business_events	Source-owned outbox with sequence, stable unique event UUID, observation type, order number, JSON data and timestamps.
order_completions	Successful completion receipt keyed by invocation key and bound to payload digest, order and quantity.
order_completion_rejections	Durable invalid-completion receipt under the same identity; no order update or event.
complete_order	Locks the invocation and order; accepts full completion of an open order; commits update, receipt and event together.

The base lab schema uses no partial-completion capability. A duplicate invocation with identical payload observes the same result. Reusing its key with changed order/quantity/digest raises an idempotency conflict. Only the worker invokes completion through its reviewed capability; callers cannot submit SQL through an App or workflow.

The database administrator must grant only the operations required by the reviewed worker queries, outbox delivery and function. The shipped function uses invoker privileges. Provisioning must therefore account for the function's underlying table writes as well as EXECUTE; a blanket read-only user cannot complete orders. Customer database policy and network restrictions remain part of commissioning.

Optional production details

JSON key	Type and source limit
description	String, at most 4,000 characters.
materialGrade	String, at most 120 characters.
thickness	Number greater than zero, at most 100,000; agree its unit in the mapping.
deliveryDate	YYYY-MM-DD string, at most 10 characters.
parentOrderNumber	String, at most 100 characters; reconcile parent identity explicitly.
routing	Ordered array with required string operationNumber, name, workCenterCode, and numeric nonnegative setupMinutes / runMinutesPerUnit.

Routing operation numbers must be unique and source work-center codes must resolve to eligible destination work centers. Import mapping has its own bounded validation; do not use the source descriptor's upper limits to infer that every destination workflow accepts the same range. A source observation is not a published Ennea Material/BoM/Routing definition.

Publish, then create the event

1. Configure host, port, database, user and private password in the curated connector. Test it: the connection test reads schema and does not create source orders.
2. Review the object/action/event mappings and publish. Check required non-Production binding approval when applicable.
3. Confirm DOCS-ERP-001 is absent in the training source. Run order-book-demo-order.sql once. Its DOCS-ASSEMBLY code is the work center from Your first production order.
4. Verify one order.created event in the source outbox and the corresponding accepted integration event/record in Ennea. Existing source orders are not backfilled automatically; the connection baselines existing events at configuration.
5. Inspect the received source record and its exact quantity, identity, optional fields and routing. Use customer-order intake and an explicit production action if manufacturing is intended. Receiving an event alone creates no claim of finished production.

A source event has sourceSystem, sourceEventId, observationType, occurredAt, subject and typed data. Delivery is at least once with durable ingress deduplication. An accepted ingress receipt means durable admission, while downstream processing has its own outcome.

Read and completion results

The reviewed read action takes orderNumber. The projected order contains orderNumber, customerName, status, orderedQuantity, completedQuantity, optional approved details and an observation timestamp. For the sample, expect DOCS-ERP-001, ordered quantity 10, completed quantity 0, status open.

The completion action takes that order number and completedQuantity: 10. Dispatch only after the authorized manufacturing result is established. Preserve the original invocation identity. Successful worker receipts use status: "applied" with the current order. Invalid full completion uses status: "rejected" and reasonCode: "ORDER_COMPLETION_INVALID". Unproven transport outcomes use status: "unknown".

Result	Recovery
Schema missing	Have the source owner compare the required tables, function and trigger before publishing.
Work-center code unmapped	Fix the destination mapping and re-review production preparation.
Rejected completion	Inspect source status/ordered quantity. Correct the business request; retain the rejected receipt.
Unknown completion	Read the original receipt and authoritative source order. Reuse the original identity for reconciliation; do not choose a replacement key.
No creation event	Check publication timing, trigger, subscription and worker delivery. Historical rows are not automatic imports.

This contract is tied to profile 1.1.0 and the downloadable source schema. Review schema/catalog changes together when upgrading. See ERP and API integrations and integration overview.