Database Schema

All tables use PostgreSQL via Drizzle ORM. Integer monetary amounts are stored in the smallest currency unit (cents). Timestamps use timestamptz. UUIDs are auto-generated with gen_random_uuid() unless noted.

---

Catalog

sellable_entities

Column	Type	Constraints
id	uuid	PK, default random
type	text	NOT NULL
slug	text	NOT NULL, UNIQUE
status	text	NOT NULL, default 'draft'. Enum: draft, active, archived, discontinued
is_visible	boolean	NOT NULL, default false
metadata	jsonb	default {}
created_at	timestamptz	NOT NULL, default now()
updated_at	timestamptz	NOT NULL, default now()
published_at	timestamptz	—

Indexes: type, status, slug.

sellable_attributes

Column	Type	Constraints
id	uuid	PK
entity_id	uuid	NOT NULL, FK sellable_entities.id ON DELETE CASCADE
locale	text	NOT NULL, default 'en'
title	text	NOT NULL
subtitle	text	—
description	text	—
rich_description	jsonb	—
seo_title	text	—
seo_description	text	—

Index: (entity_id, locale).

variants

Column	Type	Constraints
id	uuid	PK
entity_id	uuid	NOT NULL, FK sellable_entities.id ON DELETE CASCADE
sku	text	UNIQUE
barcode	text	—
status	text	NOT NULL, default 'active'. Enum: active, discontinued
sort_order	integer	NOT NULL, default 0
metadata	jsonb	default {}

categories

Column	Type	Constraints
id	uuid	PK
parent_id	uuid	FK categories.id ON DELETE SET NULL
slug	text	NOT NULL, UNIQUE
sort_order	integer	NOT NULL, default 0
metadata	jsonb	default {}

---

Inventory

warehouses

Column	Type	Constraints
id	uuid	PK
name	text	NOT NULL
code	text	NOT NULL, UNIQUE
address	jsonb	—
is_active	boolean	NOT NULL, default true
priority	integer	NOT NULL, default 0

inventory_levels

Column	Type	Constraints
id	uuid	PK
entity_id	uuid	NOT NULL, FK sellable_entities.id ON DELETE CASCADE
variant_id	uuid	FK variants.id ON DELETE CASCADE
warehouse_id	uuid	NOT NULL, FK warehouses.id
quantity_on_hand	integer	NOT NULL, default 0
quantity_reserved	integer	NOT NULL, default 0
quantity_incoming	integer	NOT NULL, default 0
unit_cost	integer	—
reorder_threshold	integer	—
reorder_quantity	integer	—
version	integer	NOT NULL, default 0. Used for optimistic locking.
last_restocked_at	timestamptz	—

Index: (entity_id, variant_id, warehouse_id).

inventory_movements

Column	Type	Constraints
id	uuid	PK
entity_id	uuid	NOT NULL
variant_id	uuid	—
warehouse_id	uuid	NOT NULL
type	text	Enum: receipt, sale, return, adjustment, transfer, reservation, release
quantity	integer	NOT NULL
reference_type	text	—
reference_id	text	—
reason	text	—
performed_by	text	NOT NULL
performed_at	timestamptz	NOT NULL, default now()

---

Cart

carts

Column	Type	Constraints
id	uuid	PK
customer_id	uuid	—
status	text	Enum: active, merged, checked_out, abandoned
currency	text	NOT NULL, default 'USD'
secret	text	—
expires_at	timestamptz	NOT NULL

cart_line_items

Column	Type	Constraints
id	uuid	PK
cart_id	uuid	NOT NULL, FK carts.id ON DELETE CASCADE
entity_id	uuid	NOT NULL, FK sellable_entities.id
variant_id	uuid	FK variants.id
quantity	integer	NOT NULL, default 1
unit_price_snapshot	integer	NOT NULL
currency	text	NOT NULL
notes	text	Free-text notes (POS: “no ice”, “gift wrap”)

---

Orders

orders

Column	Type	Constraints
id	uuid	PK
order_number	text	NOT NULL, UNIQUE
customer_id	uuid	—
status	text	NOT NULL, default 'pending'
currency	text	NOT NULL
subtotal	integer	NOT NULL
tax_total	integer	NOT NULL
shipping_total	integer	NOT NULL
discount_total	integer	NOT NULL, default 0
grand_total	integer	NOT NULL
payment_intent_id	text	Payment processor transaction ID
payment_method_id	text	Payment method used
placed_at	timestamptz	NOT NULL, default now()
fulfilled_at	timestamptz	—
cancelled_at	timestamptz	—

Order state machine: pending → confirmed → processing → [partially_fulfilled | fulfilled] → refunded. Cancel is allowed from pending, confirmed, processing, partially_fulfilled.

order_line_items

Column	Type	Constraints
id	uuid	PK
order_id	uuid	NOT NULL, FK orders.id ON DELETE CASCADE
entity_id	uuid	NOT NULL
entity_type	text	NOT NULL
variant_id	uuid	—
sku	text	—
title	text	NOT NULL
quantity	integer	NOT NULL
unit_price	integer	NOT NULL
total_price	integer	NOT NULL
tax_amount	integer	NOT NULL, default 0
discount_amount	integer	NOT NULL, default 0
fulfillment_status	text	NOT NULL, default 'unfulfilled'

order_status_history

Column	Type	Constraints
id	uuid	PK
order_id	uuid	NOT NULL, FK orders.id ON DELETE CASCADE
from_status	text	NOT NULL
to_status	text	NOT NULL
reason	text	—
changed_by	text	NOT NULL
changed_at	timestamptz	NOT NULL

---

Customers

customers

Column	Type	Constraints
id	uuid	PK
user_id	text	NOT NULL, UNIQUE
email	text	UNIQUE
phone	text	—
first_name	text	—
last_name	text	—
metadata	jsonb	default {}

customer_addresses

Column	Type	Constraints
id	uuid	PK
customer_id	uuid	NOT NULL, FK customers.id ON DELETE CASCADE
type	text	NOT NULL. Enum: shipping, billing
is_default	boolean	NOT NULL, default false
first_name	text	NOT NULL
last_name	text	NOT NULL
line1	text	NOT NULL
line2	text	—
city	text	NOT NULL
state	text	—
postal_code	text	—
country	text	NOT NULL

---

Pricing

prices

Column	Type	Constraints
id	uuid	PK
entity_id	uuid	NOT NULL, FK sellable_entities.id ON DELETE CASCADE
variant_id	uuid	FK variants.id ON DELETE CASCADE
currency	text	NOT NULL
amount	integer	NOT NULL
customer_group_id	text	—
min_quantity	integer	—
max_quantity	integer	—
valid_from	timestamptz	—
valid_until	timestamptz	—

Indexes: (entity_id, variant_id, currency), (valid_from, valid_until).

---

Promotions

promotions

Column	Type	Constraints
id	uuid	PK
code	text	UNIQUE
name	text	NOT NULL
type	text	Enum: percentage_off_order, fixed_off_order, percentage_off_item, fixed_off_item, free_shipping, buy_x_get_y
value	integer	NOT NULL, default 0
is_automatic	boolean	NOT NULL, default false
is_active	boolean	NOT NULL, default true
priority	integer	NOT NULL, default 100
usage_limit_total	integer	—
usage_limit_per_customer	integer	—
valid_from	timestamptz	—
valid_until	timestamptz	—

---

Audit

commerce_audit_log

Column	Type	Constraints
id	uuid	PK
entity_type	text	NOT NULL
entity_id	text	NOT NULL
event	text	NOT NULL
payload	jsonb	NOT NULL, default '{}'
actor_id	text	—
actor_type	text	—
request_id	text	—
created_at	timestamptz	NOT NULL, default now()

Index: (entity_type, entity_id).

---

Webhooks

webhook_endpoints

Column	Type	Constraints
id	uuid	PK
url	text	NOT NULL
secret	text	NOT NULL
events	jsonb	NOT NULL
is_active	boolean	NOT NULL, default true

webhook_deliveries

Column	Type	Constraints
id	uuid	PK
endpoint_id	uuid	NOT NULL, FK webhook_endpoints.id
event_name	text	NOT NULL
payload	jsonb	NOT NULL
status_code	integer	—
attempt_count	integer	NOT NULL, default 0
next_retry_at	timestamptz	—
delivered_at	timestamptz	—
failed_at	timestamptz	—

---

Jobs

commerce_jobs

Column	Type	Constraints
id	uuid	PK
queue	text	NOT NULL, default 'default'
task_slug	text	NOT NULL
input	jsonb	NOT NULL, default '{}'
output	jsonb	—
status	text	Enum: pending, processing, succeeded, failed
attempts	integer	NOT NULL, default 0
max_attempts	integer	NOT NULL, default 5
error	text	—
wait_until	timestamptz	—
concurrency_key	text	—
processing_started_at	timestamptz	—
completed_at	timestamptz	—

---

Fulfillment

fulfillment_records

Column	Type	Constraints
id	uuid	PK
order_id	uuid	NOT NULL, FK orders.id ON DELETE CASCADE
type	text	NOT NULL
status	text	NOT NULL, default 'pending'
carrier	text	—
tracking_number	text	—
tracking_url	text	—
estimated_delivery	timestamptz	—
shipped_at	timestamptz	—
delivered_at	timestamptz	—
download_url	text	For digital downloads
download_expires_at	timestamptz	—
max_downloads	integer	—
download_count	integer	NOT NULL, default 0

---

POS Plugin

Added by @porulle/plugin-pos.

pos_terminals

Column	Type	Constraints
id	uuid	PK
name	text	NOT NULL
code	text	NOT NULL
type	text	Enum: register, tablet, mobile, kiosk
is_active	boolean	NOT NULL, default true

Unique: (organization_id, code).

pos_shifts

Column	Type	Constraints
id	uuid	PK
terminal_id	uuid	NOT NULL, FK pos_terminals.id ON DELETE CASCADE
operator_id	text	NOT NULL
status	text	Enum: open, closed
opening_float	integer	NOT NULL, default 0
closing_count	integer	—
expected_cash	integer	—
cash_variance	integer	—
sales_count	integer	NOT NULL, default 0
sales_total	integer	NOT NULL, default 0

pos_transactions

Column	Type	Constraints
id	uuid	PK
shift_id	uuid	NOT NULL, FK pos_shifts.id ON DELETE CASCADE
terminal_id	uuid	NOT NULL, FK pos_terminals.id
cart_id	uuid	NOT NULL
order_id	uuid	Set after checkout completes
type	text	Enum: sale, return, exchange
status	text	Enum: open, held, completed, voided
receipt_number	text	Sequential per terminal per day (e.g., MC1-0001)
total	integer	NOT NULL, default 0
void_reason	text	Required when voiding

pos_payments

Column	Type	Constraints
id	uuid	PK
transaction_id	uuid	NOT NULL, FK pos_transactions.id ON DELETE CASCADE
method	text	Enum: cash, card, gift_card, store_credit, other
amount	integer	NOT NULL
change_given	integer	NOT NULL, default 0
reference	text	Card last 4, gift card code, auth code
status	text	Enum: collected, refunded