Data model

Both Workers bind the same D1 database (orderloop, binding DB) and R2 bucket (orderloop-docs, binding DOCS). The Drizzle schema is packages/db/src/schema.ts; the SQL that creates it is packages/db/migrations/*.sql.

Caution

schema.ts must match the migrations column for column and index for index. To change the schema, add a new numbered migration and update schema.ts to match; never edit a migration that has been applied.

D1 holds indexes and the back office. An order’s live run state is not in D1: it lives in its OrderAgent, and its durable progress in its Workflow instance.

Migrations

File	Adds
0001_init.sql	Flows, versions, the orders index, approvals and the mock back office
0002_seed.sql	Demo customers, products and contract prices (flows are seeded by the engine from the templates)
0003_webhook_subscriptions.sql	webhook_subscriptions, webhook_deliveries
0004_order_idempotency.sql	orders.idempotency_key and its partial unique index
0005_idempotency_fingerprint.sql	orders.idempotency_fingerprint: a replayed key must carry the same request
0006_stock_movements.sql	stock_movements, the inventory ledger
0007_inbound_emails.sql	inbound_emails, the email intake log
0008_node_runs.sql	node_runs, per-node timing for the dashboard
0009_price_chain.sql	price_sources and price_rules, the pricing chain, with demo entries
0010_fx_rates.sql	fx_rates with demo rates, and orders.fx_rate
0011_users.sql	users, sessions and api_tokens, for roles and sign-in
0012_auth_failures.sql	auth_failures, counts for throttling failed sign-ins
0013_audit_events.sql	audit_events, the security audit log
0014_token_expiry.sql	api_tokens.expires_at

Apply them with pnpm db:migrate:local, or pnpm --filter @orderloop/web db:migrate:remote before deploying code that needs them. The engine’s tests apply the same directory to their D1 on start.

Flows

Table	Purpose	Key columns
flows	One row per flow; graph_json is the editable draft	id, name, order_type, published_version
flow_versions	Immutable published snapshots; primary key (flow_id, version), cascades with the flow	flow_json, published_at

publishFlow (packages/db/src/flows.ts) saves the draft and inserts the next version. getFlowVersionJson loads a given version or the latest. Orders pin (flow_id, flow_version) at start, so editing or republishing never changes a running order. The built-in templates are published as version 1 the first time they are needed (ensureTemplates in the engine, ensureSeeded in the web app).

Orders and approvals

Table	Purpose	Key columns
orders	The orders index for lists, filters and the dashboard. Written by startOrder and then by the agent’s syncIndex	id, type, status, flow_id, flow_version, instance_id, generation, customer_id, currency, total, waiting_on, idempotency_key, idempotency_fingerprint, fx_rate (the order’s rate, set when it is priced)
approvals	One row per approval wait, for the approvals inbox	order_id, node_id, event_type, title, assignee, amount, status, decided_by, note

Indexes: orders_status (status, updated_at), orders_type (type, updated_at), and orders_idempotency, unique on idempotency_key where it is not null. startOrder inserts the row before creating anything else, so when two requests race with the same Idempotency-Key the unique index decides which one creates the order and the other returns it as replayed. idempotency_fingerprint is the SHA-256 of the request; a replay with a different one is refused. Rows from before 0005 have none and replay without the check.

approvals.status is pending, then the outcome (approved, rejected, escalated) or cancelled. Approvals are indexed by (status, requested_at), and approvals_wait makes (order_id, event_type) unique, so a retried :open step cannot insert a second row.

Back office

The tables the connectors read and write. Every id a step writes embeds its step key.

Table	Purpose
customers	tier, credit_limit, balance (open receivables, raised by invoicing), payment terms
products	Primary key sku; price, cost, stock, reserved, reorder_point, reorder_qty, supplier
contract_prices	Negotiated prices, primary key (customer_id, sku): the pricing chain’s contract source
price_sources	The pricing chain: id (price_book, contract, volume), label, priority (lowest runs first), stop_processing, active. See Pricing chain
fx_rates	Units of each currency per one USD, with updated_at. See Currency
price_rules	Price-book and volume entries: source_id, name, optional scope (sku, customer_id, customer_tier, min_qty, starts_at, ends_at), and a fixed price or discount_pct off list
reservations	Stock holds, id res_${orderId}_${stepKey}_${line}_${sku}; status is held, released or consumed (by shipping)
stock_movements	The inventory ledger: one row per step, line and kind (hold or restock), with qty and applied. A movement is recorded and applied to products in one D1 batch, and only while applied is false, so a retried step changes stock once
payments	Authorizations (kind authorization:<method>), captures, charges and refunds, with status and ref
shipments	Labels and return labels; status is label_created, return_label or cancelled
documents	Index of R2 objects per order: kind, r2_key, content_type
messages	The mock outbox for commerce.notify: recipient, subject, body

reservations, stock_movements, payments, shipments, documents and messages are each indexed by order_id (<table>_order), which is how compensation and the order page find an order’s rows.

Metrics

Table	Purpose
node_runs	One row per finished node run, primary key (order_id, generation, node_id, run): node_type, order_type, status (succeeded or failed), started_at, ended_at, duration_ms. Written by the OrderAgent (see Node timing) and indexed by (node_type, ended_at)

The dashboard’s Last 30 days section (apps/web/app/lib/metrics.server.ts) aggregates in D1 at request time: completion rate by order type from orders (completed over finished, by created_at), p50 and p95 duration per node type from node_runs (nearest rank, with window functions), revenue per day from completed orders, and refund rate from payments (succeeded refund over succeeded capture and charge). Money is converted to USD at each order’s fx_rate.

People and tokens

Table	Purpose
users	email (unique, lowercased), name, role (viewer, approver, operator), password_hash (PBKDF2, pbkdf2:<iterations>:<salt>:<hash>), external_sub (unique when set; reserved for an outside identity provider’s subject), last_login_at, disabled_at
sessions	One per signed-in browser: token_hash (SHA-256 of the __Host-ol_session cookie), user_id, expires_at (30 days)
api_tokens	name, prefix (the first 12 characters, for lists), token_hash (SHA-256 of the ol_live_ token, unique), role, created_by, last_used_at (updated at most every 5 minutes), revoked_at, expires_at (null never expires). For an expired token, last_used_at limits its api.token_expired audit events to one an hour
auth_failures	Failed attempts per key (email-ip:, ip:, email: for sign-in; api-ip: for shared-password bearers) with count and window_start; a 15-minute fixed window, and rows a day old are dropped. See Failed sign-ins
audit_events	The security audit log (apps/web/app/lib/audit.server.ts): at, action (see Audit log), actor, actor_id, actor_kind, target, detail (JSON), ip. Indexed by at and (action, at); rows older than 180 days are deleted as new ones are written

No secret is stored in the clear. See Team and roles.

Webhooks

Table	Purpose
webhook_subscriptions	url, secret (returned once, at creation), events (a JSON array; "*" matches all), active
webhook_deliveries	The outbox: one row per event per subscription, id whd_${orderId}_${stepKey}_${subscriptionId}. status is pending, delivered or failed; with attempts, response_status, last_error, delivered_at

Deliveries are indexed by order_id and by (subscription_id, created_at). See Outbound webhooks.

Email intake

Table	Purpose
inbound_emails	One row per message the email() entrypoint saw: message_id (unique), mailbox, sender, subject, status (accepted, duplicate, rejected), reason, order_id, r2_key, attachments, received_at

See Email intake.

R2 documents

storeDocument writes to orders/<orderId>/<kind>/<name> and records the same key in documents:

Kind	Name	Written by
inbound	po.txt	startOrder, when the input has documentText and no documentKey
email	message.eml (under inbound/)	receiveEmail, the raw message an order started from
invoice	<nodeId>-<run>.html	commerce.invoice
po	<nodeId>-<run>.html	commerce.generate_po

The web app serves them at /docs/<key> to signed-in users only.