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
Section titled “Catalog”sellable_entities
Section titled “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 |
| tax_class | text | — Maps to tax_classes.name at checkout. Null = the org’s default class |
| 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
Section titled “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
Section titled “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 |
| tax_class | text | — Overrides the entity’s tax_class when set |
| metadata | jsonb | default {} |
categories
Section titled “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
Section titled “Inventory”warehouses
Section titled “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
Section titled “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
Section titled “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() |
| 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
Section titled “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
Section titled “Orders”orders
Section titled “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
Section titled “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. Per-line tax written by checkout (tax classes) |
| discount_amount | integer | NOT NULL, default 0 |
| refunded_quantity | integer | NOT NULL, default 0. Units already refunded on this line |
| fulfillment_status | text | NOT NULL, default 'unfulfilled' |
order_status_history
Section titled “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 |
order_refunds
Section titled “order_refunds”Line-level refund ledger. See Refunds & Exchanges.
| Column | Type | Constraints |
|--------|------|-------------|
| id | uuid | PK |
| organization_id | text | NOT NULL, FK organization.id ON DELETE CASCADE |
| order_id | uuid | NOT NULL, FK orders.id ON DELETE CASCADE |
| amount | integer | NOT NULL. Refund total in minor units |
| reason | text | — |
| lines | jsonb | NOT NULL. [{ lineItemId, quantity, amount }] |
| performed_by | text | NOT NULL. Operator who issued the refund |
| status | text | NOT NULL, default 'completed'. Enum: completed, undone |
| undone_at | timestamptz | — |
| undone_by | text | — |
| created_at | timestamptz | NOT NULL, default now() |
Indexes: (order_id), (organization_id, performed_by, created_at) — the second backs the per-operator daily cap.
order_notes
Section titled “order_notes”Free-text notes on an order; surfaced in the order timeline. See order notes and timeline.
| Column | Type | Constraints |
|--------|------|-------------|
| id | uuid | PK |
| organization_id | text | NOT NULL, FK organization.id ON DELETE CASCADE |
| order_id | uuid | NOT NULL, FK orders.id ON DELETE CASCADE |
| author | text | NOT NULL. Set server-side to the acting user |
| body | text | NOT NULL |
| pinned | boolean | NOT NULL, default false |
| created_at | timestamptz | NOT NULL, default now() |
Index: (order_id). Listing is pinned-first, then newest-first.
Customers
Section titled “Customers”customers
Section titled “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
Section titled “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
Section titled “Pricing”prices
Section titled “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
Section titled “Promotions”promotions
Section titled “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 | — |
tax_classes
Section titled “tax_classes”Named product tax rates. See Tax Classes.
| Column | Type | Constraints |
|--------|------|-------------|
| id | uuid | PK |
| organization_id | text | NOT NULL, FK organization.id ON DELETE CASCADE |
| name | text | NOT NULL. Lowercase slug; UNIQUE per org |
| rate_bps | integer | NOT NULL. Basis points (1800 = 18%) |
| is_default | boolean | NOT NULL, default false. Applied to lines with no class |
| is_active | boolean | NOT NULL, default true |
| created_at | timestamptz | NOT NULL, default now() |
| updated_at | timestamptz | NOT NULL, default now() |
Unique: (organization_id, name).
Settings & Documents
Section titled “Settings & Documents”store_settings
Section titled “store_settings”Org-scoped typed settings groups. See Store Settings.
| Column | Type | Constraints |
|--------|------|-------------|
| id | uuid | PK |
| organization_id | text | NOT NULL, FK organization.id ON DELETE CASCADE |
| group | text | NOT NULL. e.g. general, branding, policies |
| value | jsonb | NOT NULL, default {} |
| created_at | timestamptz | NOT NULL, default now() |
| updated_at | timestamptz | NOT NULL, default now() |
Unique: (organization_id, group) — one row per group.
invoice_sequences
Section titled “invoice_sequences”Per-org fiscal counter for invoice numbers. See Receipts & Invoices.
| Column | Type | Constraints |
|--------|------|-------------|
| id | uuid | PK |
| organization_id | text | NOT NULL, FK organization.id ON DELETE CASCADE |
| series | text | NOT NULL, default 'default' |
| next_value | integer | NOT NULL, default 1. Next number to allocate |
| updated_at | timestamptz | NOT NULL, default now() |
Unique: (organization_id, series). Allocation is a single atomic upsert-returning.
order_documents
Section titled “order_documents”One issued document per (org, order, type) — the idempotency key for fiscal numbers.
| Column | Type | Constraints |
|--------|------|-------------|
| id | uuid | PK |
| organization_id | text | NOT NULL, FK organization.id ON DELETE CASCADE |
| order_id | uuid | NOT NULL, FK orders.id ON DELETE CASCADE |
| type | text | NOT NULL. invoice | receipt |
| document_number | text | NOT NULL |
| created_at | timestamptz | NOT NULL, default now() |
Unique: (organization_id, order_id, type).
commerce_audit_log
Section titled “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
Section titled “Webhooks”webhook_endpoints
Section titled “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
Section titled “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 | — |
commerce_jobs
Section titled “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
Section titled “Fulfillment”fulfillment_records
Section titled “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
Section titled “POS Plugin”Added by @porulle/plugin-pos.
pos_terminals
Section titled “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
Section titled “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
Section titled “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
Section titled “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 |
pos_operator_pins
Section titled “pos_operator_pins”Cashier PINs for terminal login. See PIN authentication.
| Column | Type | Constraints |
|--------|------|-------------|
| id | uuid | PK |
| organization_id | text | NOT NULL |
| operator_id | text | NOT NULL. UNIQUE per org |
| pin_hash | text | NOT NULL. PBKDF2-SHA256 — never plaintext |
| can_override | boolean | NOT NULL, default false |
| created_at | timestamptz | NOT NULL, default now() |
| updated_at | timestamptz | NOT NULL, default now() |
Unique: (organization_id, operator_id).
Layaway Plugin
Section titled “Layaway Plugin”From @porulle/plugin-layaway. See Layaway.
layaways
Section titled “layaways”| Column | Type | Constraints |
|--------|------|-------------|
| id | uuid | PK |
| organization_id | text | NOT NULL |
| customer_id | uuid | — |
| status | text | NOT NULL, default 'active'. Enum: active, completed, forfeited, cancelled |
| currency | text | NOT NULL |
| items | jsonb | NOT NULL. [{ entityId, variantId?, sku?, title, quantity, unitPrice }] |
| total | integer | NOT NULL |
| deposit_amount | integer | NOT NULL, default 0 |
| paid_total | integer | NOT NULL, default 0 |
| order_id | uuid | — Core order created at completion |
| expires_at | timestamptz | — |
| forfeited_at | timestamptz | — |
| forfeit_reason | text | — |
| created_by | text | NOT NULL |
| metadata | jsonb | default {} |
| created_at | timestamptz | NOT NULL, default now() |
| updated_at | timestamptz | NOT NULL, default now() |
Indexes: (organization_id), (status), (customer_id).
layaway_payments
Section titled “layaway_payments”| Column | Type | Constraints |
|--------|------|-------------|
| id | uuid | PK |
| layaway_id | uuid | NOT NULL, FK layaways.id ON DELETE CASCADE |
| amount | integer | NOT NULL |
| method | text | NOT NULL |
| reference | text | — |
| performed_by | text | NOT NULL |
| created_at | timestamptz | NOT NULL, default now() |
Index: (layaway_id).