Data model

One PostgreSQL database, lucille_order_center, schema app, provisioned from the Chrono postgres-db service template (infra/{dev,prod}/lucille-postgres.yaml). The schema is owned by Prisma Migrate; migration files are committed under apps/api/prisma/migrations/ and applied at deploy time by a one-off Kubernetes Job (infra/{dev,prod}/lucille-migrate.yaml) that runs from the API image with MIGRATE_MODE=true, which selects the prisma migrate deploy branch of the image's entrypoint dispatcher. Ordering that Job ahead of the API rollout is not guaranteed by the manifests — the requested ArgoCD PreSync hook is not expressible in the two-layer Chrono model, so the migration is run from CI before the API tag is promoted. See infra/README.md and Deployment.

Conventions you must know before writing SQL by hand

UUID primary keys are generated by Prisma, not by PostgreSQL. Every surrogate key is declared @id @default(uuid()) @db.Uuid, which Prisma resolves in the client — the emitted DDL is a bare "id" UUID NOT NULL, with no DEFAULT gen_random_uuid(). Any raw INSERT (a $executeRaw, a psql session, a fixture loader) must therefore supply id itself or the statement fails on the not-null constraint. Two tables have no surrogate key at all: edi_control_numbers is keyed by supplier_id, and idempotency_keys is keyed by the client-supplied TEXT key.

Timestamps are TIMESTAMPTZ(6) with DEFAULT CURRENT_TIMESTAMP in the DDL. updated_at carries Prisma's @updatedAt, which is also client-side: there is no BEFORE UPDATE trigger, so a raw SQL update must set updated_at explicitly or the row will silently keep its old value.

Statuses are real PostgreSQL enum types, not CHECK constraints. The plan specified TEXT ... CHECK (x IN (...)); the schema creates six enum types in app instead — user_role, supplier_type, order_status, order_line_item_status, supplier_dispatch_status and vendor_document_type — with every member @map'd to the exact lowercase snake_case value the wire contract uses. The same value sets are declared once as Zod enums in packages/types/src/enums.ts, so application code and the database cannot disagree. Adding a value is ALTER TYPE ... ADD VALUE plus an edit to that file.

Entity relationships

  restaurants ──┬──< user_restaurants >──┬── users
                │                        │
                │                        └──< orders.placed_by
                └──< orders
                       │
                       ├──< order_line_items >── catalog_items ──1:1── catalog_supplier_mappings
                       │           │                                       │        │
                       │           │                           primary ────┘        └──── alternate
                       │           │                                  ▼                     ▼
                       │           └──< dispatch_line_items >──┐    suppliers ──────────────┘
                       │                                       │        │
                       ├──< supplier_dispatches >──────────────┘        │
                       │           │   └─ reroute_of_dispatch_id → supplier_dispatches (self)
                       │           └──< vendor_documents >──────────────┘
                       │
                       └──< audit_events (append-only)

  edi_control_numbers  ── one row per supplier; PK is supplier_id, no surrogate id
  idempotency_keys     ── standalone; PK is the client-supplied X-Idempotency-Key

Every foreign key is ON UPDATE CASCADE. Deletes are ON DELETE RESTRICT everywhere except the two pure join tables, user_restaurants and dispatch_line_items, which cascade. That asymmetry is deliberate: unassigning a chef or deleting a dispatch should clean up its join rows, but nothing should be able to delete an order, a line item or a dispatch out from under the audit trail.

restaurants

A Lucille location that orders are placed against.

Column	Type	Notes
id	UUID PK	Client-generated (see conventions above)
name	TEXT NOT NULL	Displayed in the restaurant picker
location	TEXT	Free text address or city
active	BOOLEAN NOT NULL DEFAULT true	Deactivating stops new orders, keeps history
created_at	TIMESTAMPTZ(6) NOT NULL	DEFAULT CURRENT_TIMESTAMP
updated_at	TIMESTAMPTZ(6) NOT NULL	DEFAULT CURRENT_TIMESTAMP, then Prisma @updatedAt

users

Mirrored from Zitadel, which remains the source of truth for identity and roles. The row is upserted from the JWT claims on a user's first authenticated request.

Column	Type	Notes
id	UUID PK	The Zitadel subject (sub) claim — no default, ever
email	TEXT NOT NULL	Unique index users_email_key
display_name	TEXT	From the token
role	app.user_role NOT NULL	chef or admin; no default
created_at	TIMESTAMPTZ(6) NOT NULL	There is no updated_at on this table

user_restaurants

The chef-to-restaurant assignment. This table — not the token — is the authority on which restaurants a chef may act on.

Column	Type	Notes
user_id	UUID NOT NULL	→ users(id), ON DELETE CASCADE
restaurant_id	UUID NOT NULL	→ restaurants(id), ON DELETE CASCADE

Primary key (user_id, restaurant_id), which doubles as the lookup index for the per-request scoping query. The reverse direction ("who is assigned to this restaurant?") is served by idx_user_restaurants_restaurant on restaurant_id.

suppliers

Column	Type	Notes
id	UUID PK
name	TEXT NOT NULL
type	app.supplier_type NOT NULL	sysco, costco, central_kitchen — selects the handler
config	JSONB NOT NULL DEFAULT '{}'	Type-specific connection config
active	BOOLEAN NOT NULL DEFAULT true
created_at / updated_at	TIMESTAMPTZ(6) NOT NULL

config holds directories, email addresses, and EDI interchange identifiers — and, for credentials, Vault secret names only. Never a password, private key or passphrase. The per-type shapes are validated on write by the Zod schemas in packages/types/src/supplier.ts, which is also where the defaults live (/outbound, /inbound, /archive, ZZ qualifiers, 004010, VN — chosen because Sysco's Trading Partner Implementation Guide was unavailable, see decision D3 in IMPLEMENTATION.md). The seeded Sysco row, verbatim from apps/api/prisma/seed.ts:

{
  "credentialsSecretName": "lucille-sysco-sftp",
  "outboundDir": "/outbound",
  "inboundDir": "/inbound",
  "archiveDir": "/archive",
  "isaSenderQualifier": "ZZ",
  "isaSenderId": "LUCILLE",
  "isaReceiverQualifier": "ZZ",
  "isaReceiverId": "SYSCO",
  "ediVersion": "004010",
  "productIdQualifier": "VN"
}

sftpHostSecretRef is an optional additional key. A costco config is { emailAddress, ccAddresses }; central_kitchen adds an optional facilityName rendered on the PDF header.

catalog_items

The unified, supplier-agnostic catalog.

Column	Type	Notes
id	UUID PK
name	TEXT NOT NULL
category	TEXT	Grouping and filter
unit	TEXT NOT NULL	case, lb, each
unit_size	TEXT	e.g. 6x10oz, display only
description	TEXT
active	BOOLEAN NOT NULL DEFAULT true	Soft delete flag
created_at / updated_at	TIMESTAMPTZ(6) NOT NULL

Nothing supplier-related lives on this table. That is what allows the chef-facing projection to be a straight column subset rather than a filtered join — see decision D14 in IMPLEMENTATION.md, which keeps supplier identity out of chef responses entirely.

catalog_supplier_mappings

The routing engine's configuration.

Column	Type	Notes
id	UUID PK
catalog_item_id	UUID NOT NULL	→ catalog_items(id), unique
primary_supplier_id	UUID NOT NULL	→ suppliers(id)
alt_supplier_id	UUID	→ suppliers(id), nullable — the back-order fallback
supplier_sku	TEXT NOT NULL	The supplier's own product code; goes on the wire
supplier_product_code	TEXT	Additional supplier reference
active	BOOLEAN NOT NULL DEFAULT true
created_at / updated_at	TIMESTAMPTZ(6) NOT NULL

The unique index catalog_supplier_mappings_catalog_item_id_key on catalog_item_id encodes the proof-of-concept constraint: one product maps to exactly one supplier. Lifting it later means dropping the constraint and teaching order placement to choose between candidates — the rest of the pipeline is already per-supplier. Two further indexes, idx_catalog_supplier_mappings_primary and idx_catalog_supplier_mappings_alt, back the "which items does this supplier carry?" admin query and the reroute lookup.

The application additionally rejects alt_supplier_id = primary_supplier_id, since rerouting a back order to the supplier that raised it is a no-op. That rule is in packages/types/src/mapping.ts and re-asserted in apps/api/src/domain/mappings.ts; it is not a database constraint.

orders

Column	Type	Notes
id	UUID PK
order_number	TEXT NOT NULL	Unique index orders_order_number_key
restaurant_id	UUID NOT NULL	→ restaurants(id)
placed_by	UUID NOT NULL	→ users(id)
status	app.order_status NOT NULL DEFAULT 'pending'	pending, dispatching, completed, partial, failed
notes	TEXT	Travels to every supplier on the order
created_at / updated_at	TIMESTAMPTZ(6) NOT NULL

order_number is the human-readable purchase-order reference, in the format LOC-YYYYMMDD-NNNN (three-letter location code, calendar day, per-restaurant daily counter). It exists because a UUID is useless to a human reading a supplier inbox and unusable on the wire: the number is the email subject, the PDF header and X12 BEG03, and it is what an inbound 855 is correlated on when Sysco names its acknowledgment file itself. The plan's prose required it but its DDL omitted it.

Generation lives in apps/api/src/domain/order-number.ts. The counter is derived by counting the restaurant's orders for the day, which is racy under concurrency — so uniqueness is guaranteed by the index, not the derivation: withOrderNumberRetry catches the unique violation, re-derives with an incremented attempt offset and re-runs the whole transaction, up to five times. Gaps in the sequence are expected and acceptable.

order_line_items

Column	Type	Notes
id	UUID PK
order_id	UUID NOT NULL	→ orders(id)
catalog_item_id	UUID NOT NULL	→ catalog_items(id)
quantity	DECIMAL(12,3) NOT NULL	In the item's own unit; three decimal places
unit	TEXT NOT NULL	Copied at placement so history survives a unit change
status	app.order_line_item_status NOT NULL DEFAULT 'pending'	See below
created_at / updated_at	TIMESTAMPTZ(6) NOT NULL

status values: pending, submitted, confirmed, backordered, rerouted, dispatch_failed, backorder_no_alternate.

quantity is DECIMAL(12,3) rather than an unconstrained NUMERIC: fixed precision keeps the value exact through the X12 and PDF serialisers, and three decimals is enough for weight-based units. unit is denormalised deliberately — if the administrator later changes an item from case to each, last month's orders must still read correctly. Indexes: idx_order_line_items_order and idx_order_line_items_catalog_item.

supplier_dispatches

One row per supplier per order, plus one per reroute. This is the worker's unit of work and its idempotency guard.

Column	Type	Notes
id	UUID PK
order_id	UUID NOT NULL	→ orders(id)
supplier_id	UUID NOT NULL	→ suppliers(id)
status	app.supplier_dispatch_status NOT NULL DEFAULT 'pending'	pending, submitted, confirmed, failed
reference	TEXT	EDI file name, or SMTP message ID
pdf_url	TEXT	Full DO Spaces object URL for the Central Kitchen PDF
attempt	INTEGER NOT NULL DEFAULT 0	Delivery attempt counter
failure_reason	TEXT	Last transport- or protocol-level error
is_reroute	BOOLEAN NOT NULL DEFAULT false	True when created by the back-order reroute flow
reroute_of_dispatch_id	UUID	→ supplier_dispatches(id), self-reference
submitted_at	TIMESTAMPTZ(6)
confirmed_at	TIMESTAMPTZ(6)	Set when an X12 855 acknowledges the dispatch
created_at / updated_at	TIMESTAMPTZ(6) NOT NULL

Four of these columns are absent from the plan's DDL and exist for concrete reasons:

• attempt — BullMQ retries a dispatch job, and "failed after 3 attempts" cannot be reconstructed from a status column alone. It is projected onto admin dispatch payloads (packages/types/src/dispatch.ts, apps/api/src/domain/admin-orders.ts).
• failure_reason — so an administrator can see why a dispatch failed without reading pod logs. Written on enqueue failure by apps/api/src/domain/orders.ts and on an X12 997 rejection by apps/api/src/domain/inbound-ack.ts (truncated to 2000 characters).
• is_reroute and reroute_of_dispatch_id — a reroute is a new dispatch to the alternate supplier, not a mutation of the original, so the two must stay linked. The flag is what lets apps/api/src/domain/orders.ts and admin-orders.ts answer "which supplier actually carried this line?" by preferring the newest dispatch while still being able to find the original fan-out. Indexed as idx_supplier_dispatches_reroute_of.

reference is one of three correlation paths for an inbound 855, not the only one. correlate855() in apps/api/src/domain/inbound-ack.ts prefers an explicit dispatchId, then supplier_dispatches.reference matched against the inbound file name, then orders.order_number taken from BAK03 or parsed out of the file name — the last of which is what usually fires, because Sysco names its own acknowledgment files.

Indexes: idx_supplier_dispatches_order, idx_supplier_dispatches_supplier, idx_supplier_dispatches_reroute_of.

dispatch_line_items

The join table between dispatches and line items. Note the physical name: the Prisma model is SupplierDispatchLineItem but its @@map is dispatch_line_items.

Column	Type	Notes
dispatch_id	UUID NOT NULL	→ supplier_dispatches(id), ON DELETE CASCADE
line_item_id	UUID NOT NULL	→ order_line_items(id), ON DELETE CASCADE

Primary key (dispatch_id, line_item_id), plus idx_dispatch_line_items_line_item for the reverse lookup. The table is absent from the plan's DDL, which implicitly assumed a line item belongs to one dispatch. It does not: one order fans out to several suppliers, and a reroute sends only a subset of the lines to a different supplier, so the same line item legitimately appears in two dispatches. Order placement writes these rows in the same transaction as the dispatches (apps/api/src/domain/orders.ts).

audit_events — append-only

Column	Type	Notes
id	UUID PK
order_id	UUID	→ orders(id), nullable
line_item_id	UUID	→ order_line_items(id), nullable
dispatch_id	UUID	→ supplier_dispatches(id), nullable
actor_id	UUID	→ users(id), nullable — null for system actions
event_type	TEXT NOT NULL	Not an enum — see below
payload	JSONB NOT NULL DEFAULT '{}'	Everything needed to reconstruct the event
created_at	TIMESTAMPTZ(6) NOT NULL

Append-only is enforced in the database, not merely by convention: a trigger raises an exception on any UPDATE or DELETE against the table, installed by the hand-written 20260813134500_audit_events_append_only migration. Prisma Migrate does not model functions or triggers, so these objects are invisible to the drift detector and do not put the schema out of sync.

CREATE OR REPLACE FUNCTION "app"."audit_events_append_only"() RETURNS TRIGGER
    LANGUAGE plpgsql
AS $$
BEGIN
    RAISE EXCEPTION 'audit_events is append-only'
        USING ERRCODE = '42501',
              DETAIL  = 'Rejected ' || TG_OP || ' on app.audit_events.',
              HINT    = 'Insert a new compensating audit event instead of modifying or deleting an existing one.';
END;
$$;

CREATE TRIGGER "audit_events_append_only"
    BEFORE UPDATE OR DELETE ON "app"."audit_events"
    FOR EACH ROW
    EXECUTE FUNCTION "app"."audit_events_append_only"();

To correct a mistaken event, insert a compensating one. The technical plan achieved immutability by granting insert-only privileges to a second database role; the trigger was chosen instead — decision D6 in IMPLEMENTATION.md — because it needs one connection pool rather than two, cannot be bypassed by connecting as the wrong user, and is directly testable from the integration suite. The outward foreign keys are ON DELETE RESTRICT, so the rows an audit event points at cannot be deleted either.

event_type is deliberately TEXT, not a PostgreSQL enum: the vocabulary evolves independently of migrations. The authoritative allow-list is auditEventTypeSchema in packages/types/src/enums.ts; recordAuditEvent() in apps/api/src/domain/audit.ts types its input as AuditEventType | string, so the list is enforced at compile time for first-party callers rather than at run time.

Group	Values
Order	ORDER_PLACED, ORDER_STATUS_CHANGED
Dispatch	SUPPLIER_DISPATCH_CREATED, SUPPLIER_SUBMITTED, SUPPLIER_DISPATCH_FAILED, SUPPLIER_CONFIRMED
Back order	LINE_ITEM_BACKORDERED, BACKORDER_REROUTED, BACKORDER_NO_ALTERNATE
Inbound	VENDOR_DOCUMENT_RECEIVED, EDI_REJECTED
Catalog	CATALOG_ITEM_CREATED, CATALOG_ITEM_UPDATED, CATALOG_ITEM_DEACTIVATED
Supplier	SUPPLIER_CREATED, SUPPLIER_UPDATED
Mapping	MAPPING_CREATED, MAPPING_UPDATED
Restaurant	RESTAURANT_CREATED, RESTAURANT_UPDATED, CHEF_ASSIGNED, CHEF_UNASSIGNED

Indexes: idx_audit_events_order and idx_audit_events_created on created_at DESC.

vendor_documents

Inbound supplier documents, stored raw and parsed.

Column	Type	Notes
id	UUID PK
supplier_id	UUID NOT NULL	→ suppliers(id)
dispatch_id	UUID	→ supplier_dispatches(id), null when correlation failed
document_type	app.vendor_document_type NOT NULL	confirmation, invoice, backorder
raw_content	TEXT	The raw EDI interchange or email body
parsed_data	JSONB	Null when parsing failed — the row is still kept
reference	TEXT	Inbound file name or email message ID
parse_error	TEXT	Populated when the document could not be parsed
received_at	TIMESTAMPTZ(6) NOT NULL	DEFAULT CURRENT_TIMESTAMP; no updated_at

reference and parse_error are absent from the plan's DDL. reference is the inbound correlation key and the idempotency guard for SFTP polling: (supplier_id, reference) identifies a document, so re-reading the same file from the remote inbox is a no-op rather than a duplicate application of an 855. It has its own index, idx_vendor_documents_reference. parse_error records the reason a parse failed, so an unparseable interchange is never a silent failure — the row is kept with parsed_data = NULL, dispatch_id = NULL and a populated parse_error, and the failure is reported to Sentry. An inbound file that cannot be understood is exactly the file an engineer will need to read later.

Indexes: idx_vendor_documents_dispatch, idx_vendor_documents_supplier, idx_vendor_documents_reference.

edi_control_numbers

X12 requires the interchange control number in ISA13 to be unique per interchange and monotonically increasing. Reusing one causes Sysco's gateway to discard the interchange as a duplicate, so the counter is a database row rather than anything in memory. The table has no surrogate id: its primary key is supplier_id, one row per trading partner, and it holds three separate counters rather than a counter_type discriminator.

Column	Type	Notes
supplier_id	UUID PK	→ suppliers(id)
isa_control_number	INTEGER NOT NULL DEFAULT 0	ISA13 / IEA02
group_control_number	INTEGER NOT NULL DEFAULT 0	GS06 / GE02
transaction_control_number	INTEGER NOT NULL DEFAULT 0	ST02 / SE02
updated_at	TIMESTAMPTZ(6) NOT NULL	Bumped by the allocation

Allocation is a single statement in apps/worker/src/edi/control-numbers.ts. It upserts, so a newly created supplier needs no seeding step, and it wraps at 999999999 because ISA13 is exactly nine characters wide:

INSERT INTO app.edi_control_numbers AS ecn (
  supplier_id, isa_control_number, group_control_number,
  transaction_control_number, updated_at
)
VALUES ($1::uuid, 1, 1, 1, now())
ON CONFLICT (supplier_id) DO UPDATE
  SET isa_control_number = (ecn.isa_control_number % 999999999) + 1,
      group_control_number = (ecn.group_control_number % 999999999) + 1,
      transaction_control_number = (ecn.transaction_control_number % 999999999) + 1,
      updated_at = now()
RETURNING ecn.isa_control_number, ecn.group_control_number, ecn.transaction_control_number;

The row lock taken by the statement serialises every concurrent allocation in the cluster, so two worker replicas can never be handed the same number. Numbers consumed by a job that later fails are simply skipped — X12 requires uniqueness and monotonicity, not density. peekControlNumbers() reads the counters for diagnostics only and must never be used to derive the next number.

idempotency_keys

The replay cache behind the X-Idempotency-Key header on POST /api/orders. A chef on flaky restaurant wifi retries a submit whose first attempt already reached the API and fanned out to three suppliers; without this table the retry places a second purchase order and every supplier ships the same food twice.

Column	Type	Notes
key	TEXT PK	Client-supplied; 255-char cap is app-side
user_id	UUID NOT NULL	Zitadel subject — intentionally not a FK
request_hash	TEXT NOT NULL	SHA-256 of the canonicalised request body
response_status	INTEGER NOT NULL	Only 2xx outcomes are recorded
response_body	JSONB NOT NULL	Replayed verbatim
created_at	TIMESTAMPTZ(6) NOT NULL	DEFAULT CURRENT_TIMESTAMP; drives the sweep

The plan put this in Redis with a 24-hour TTL. It lives in PostgreSQL instead — decision D11 in IMPLEMENTATION.md — because Redis here is a queue broker with no persistence guarantee we rely on, and a key lost to a failover is exactly the situation in which a client is retrying; and because a stored response is evidence of what the API told the client, which belongs with the order history rather than in an uninspectable cache. purgeExpiredIdempotencyKeys() in apps/api/src/domain/idempotency.ts reproduces the TTL on the same 24-hour horizon and is backed by idx_idempotency_keys_created_at; idx_idempotency_keys_user serves the per-user lookup. user_id is scoped into every lookup so one user's key cannot replay another's response, and a key replayed with a different request_hash is rejected rather than answered from cache.

Indexes

The complete set created by the initial migration, in addition to every primary key:

CREATE UNIQUE INDEX users_email_key                          ON app.users(email);
CREATE UNIQUE INDEX catalog_supplier_mappings_catalog_item_id_key
                                                             ON app.catalog_supplier_mappings(catalog_item_id);
CREATE UNIQUE INDEX orders_order_number_key                  ON app.orders(order_number);
CREATE INDEX idx_user_restaurants_restaurant                 ON app.user_restaurants(restaurant_id);
CREATE INDEX idx_catalog_supplier_mappings_primary           ON app.catalog_supplier_mappings(primary_supplier_id);
CREATE INDEX idx_catalog_supplier_mappings_alt               ON app.catalog_supplier_mappings(alt_supplier_id);
CREATE INDEX idx_orders_restaurant                           ON app.orders(restaurant_id);
CREATE INDEX idx_orders_placed_by                            ON app.orders(placed_by);
CREATE INDEX idx_orders_created_at                           ON app.orders(created_at DESC);
CREATE INDEX idx_order_line_items_order                      ON app.order_line_items(order_id);
CREATE INDEX idx_order_line_items_catalog_item               ON app.order_line_items(catalog_item_id);
CREATE INDEX idx_supplier_dispatches_order                   ON app.supplier_dispatches(order_id);
CREATE INDEX idx_supplier_dispatches_supplier                ON app.supplier_dispatches(supplier_id);
CREATE INDEX idx_supplier_dispatches_reroute_of              ON app.supplier_dispatches(reroute_of_dispatch_id);
CREATE INDEX idx_dispatch_line_items_line_item               ON app.dispatch_line_items(line_item_id);
CREATE INDEX idx_audit_events_order                          ON app.audit_events(order_id);
CREATE INDEX idx_audit_events_created                        ON app.audit_events(created_at DESC);
CREATE INDEX idx_vendor_documents_dispatch                   ON app.vendor_documents(dispatch_id);
CREATE INDEX idx_vendor_documents_supplier                   ON app.vendor_documents(supplier_id);
CREATE INDEX idx_vendor_documents_reference                  ON app.vendor_documents(reference);
CREATE INDEX idx_idempotency_keys_created_at                 ON app.idempotency_keys(created_at);
CREATE INDEX idx_idempotency_keys_user                       ON app.idempotency_keys(user_id);

Each one backs a real query: restaurant-scoped order lists for chefs, the admin's cross-restaurant list ordered by recency, the order detail page's line and audit fetches, dispatch lookup during the worker's idempotency check, inbound-document correlation and de-duplication, reroute chain traversal, and the idempotency sweeper. The three unique indexes are load-bearing correctness constraints rather than optimisations: orders_order_number_key is what actually guarantees order-number uniqueness, and catalog_supplier_mappings_catalog_item_id_key is what enforces one-supplier-per-product.

Verification

This page was verified against the source rather than against the plan. The authorities are apps/api/prisma/schema.prisma, the emitted DDL in apps/api/prisma/migrations/20260813133545_init/migration.sql and apps/api/prisma/migrations/20260813134500_audit_events_append_only/migration.sql, the enum spellings in packages/types/src/enums.ts, and the seed data in apps/api/prisma/seed.ts. Behavioural claims are anchored to apps/api/src/domain/order-number.ts, apps/api/src/domain/idempotency.ts, apps/api/src/domain/orders.ts, apps/api/src/domain/inbound-ack.ts, apps/api/src/domain/audit.ts and apps/worker/src/edi/control-numbers.ts.

Two caveats. The attempt column is read and projected by the API but nothing writes it yet: the BullMQ queue processors that would maintain it are not in apps/worker/src, which currently holds the per-supplier handlers and the EDI/PDF/SFTP layers only. Likewise is_reroute = true rows are read by the API and covered by its tests, but the backorder-reroute job that inserts them is enqueued by rerouteLineItem() and consumed by a processor that is not in the tree yet — the columns, relations and API projections are in place, the writer is pending.

Where to go next

• API reference — how these tables are exposed.
• Order lifecycle — what the status columns mean.
• Sysco EDI — where control numbers are consumed.
• Queues and jobs — the dispatch and reroute jobs that write these rows.