Orders System

The orders system provides comprehensive order management capabilities including creation, querying, updates, and deletion with built-in client linking, automatic order number generation, and row-level security.

Overview

The orders system supports:

Automatic Order Numbers: Daily-scoped sequence (ORD-YYYYMMDD-NNN) with race condition prevention
Client Linking: Foreign key relationship to clients table
Order Status Workflow: 5-state tracking (open → pending → ready → complete/cancelled)
Financial Tracking: Total amount with 2 decimal precision
User Attribution: Track who created each order
Security: Row-level security with strict ownership-based access control
Performance: Optimized indexes for filtering and sorting

📋 Order Records

Manage orders with automatic order number generation, client linking, and status tracking. Links to existing client records.

🔒 Secure by Default

Row-level security ensures users can only access their own orders. Stricter isolation than clients table.

⚡ Fast Queries

Optimized indexes provide fast queries for status filtering, date ranges, and client order history.

🔢 Smart Order Numbers

Automatic daily-scoped order numbers (ORD-YYYYMMDD-NNN) with retry logic to prevent collisions during concurrent creation.

Quick Start

Creating Orders

import { getDatabase } from '#libs/database'
import { generateOrderNumber } from '#utils/order-number'
import { logger } from '#utils/logger'
import type { OrderData } from '#libs/database'

const db = getDatabase()
const correlationId = logger.createCorrelationId()

// Step 1: Generate unique order number
const orderNumberResult = await generateOrderNumber(db, correlationId)

if (!orderNumberResult.success) {
  throw new Error(orderNumberResult.error)
}

// Step 2: Create order with client linking
const orderData: OrderData = {
  order_number: orderNumberResult.orderNumber, // "ORD-20260119-001"
  user_id: userClerkId, // From Clerk authentication
  client_id: 'client-uuid-456',
  client_name: 'John Doe', // Denormalized for fast display
  status: 'open',
  order_date: '2026-01-19', // YYYY-MM-DD format
  total_amount: 150.00,
  notes: 'Rush order for wedding',
}

const orderId = await db.insertOrder(orderData)
console.log(`Order created: ${orderId}`)

Querying Orders

// Get all orders for user (most recent first)
const myOrders = await db.getOrders({
  user_id: userClerkId,
  limit: 10,
  order_by: 'created_at',
  order_direction: 'desc',
})

// Get orders by status
const openOrders = await db.getOrders({
  user_id: userClerkId,
  status: 'open',
  limit: 20,
})

// Get orders for specific client
const clientOrders = await db.getOrders({
  user_id: userClerkId,
  client_id: 'client-uuid-456',
  limit: 50,
})

// Date range query
const januaryOrders = await db.getOrders({
  user_id: userClerkId,
  start_date: '2026-01-01',
  end_date: '2026-01-31',
  order_by: 'order_date',
})

// Optimized list view (selective fields)
const orderList = await db.getOrders({
  user_id: userClerkId,
  fields: ['id', 'order_number', 'client_name', 'status', 'total_amount'],
  limit: 100,
})

Order Structure

Required Fields

Field	Type	Description	Example	Validation
order_number	string	Unique order identifier	”ORD-20260119-001”	Auto-generated
user_id	string	Clerk user ID (owner)	“user-clerk-123”	Auto-set from auth
client_id	string	Reference to client record	”client-uuid-456”	Valid UUID, FK
client_name	string	Client display name	”John Doe”	Denormalized
status	OrderStatus	Order workflow state	”open”	Enum (see below)
order_date	string	Order placement date	”2026-01-19”	YYYY-MM-DD format
total_amount	number	Order total	150.00	>= 0, 2 decimals

Order Status Values:

open - Order created, not yet processed
pending - Order processing in progress
ready - Order ready for pickup/delivery
complete - Order fulfilled and closed
cancelled - Order cancelled

Optional Fields

Field	Type	Description	Example	Validation
notes	string	Internal notes	”Rush order”	Max 1000 chars

Auto-Generated Fields

Field	Type	Description
id	string	Unique UUID
created_at	string	ISO 8601 timestamp
updated_at	string	ISO 8601 timestamp

Order Number Format

Pattern

Format: ORD-YYYYMMDD-NNN

Components:

Prefix: ORD- (constant identifier)
Date: YYYYMMDD (ISO 8601 basic date format)
Sequence: NNN (3-digit zero-padded daily sequence)

Examples:

ORD-20260119-001 - First order on January 19, 2026
ORD-20260119-042 - 42nd order on the same day
ORD-20260120-001 - First order on next day (sequence resets)

Automatic Generation

The system automatically generates unique order numbers with retry logic:

import { generateOrderNumber } from '#utils/order-number'
import { logger } from '#utils/logger'

const correlationId = logger.createCorrelationId()
const result = await generateOrderNumber(db, correlationId)

if (result.success) {
  console.log(result.orderNumber) // "ORD-20260119-001"
} else {
  console.error(result.error) // "Failed to generate order number after 3 attempts"
}

Race Condition Prevention:

Retry Logic: Up to 3 attempts with exponential backoff (100ms, 200ms, 400ms)
Collision Detection: Detects UNIQUE constraint violations
Insert Retries: Additional 2 retry attempts during insertion (5 total attempts)

Common Use Cases

Create Order for Existing Client

Link a new order to an existing client:

import { getDatabase } from '#libs/database'
import { generateOrderNumber } from '#utils/order-number'
import { logger } from '#utils/logger'

async function createOrder(userClerkId: string, clientId: string, clientName: string, amount: number) {
  const db = getDatabase()
  const correlationId = logger.createCorrelationId()

  // Generate order number
  const orderNumberResult = await generateOrderNumber(db, correlationId)

  if (!orderNumberResult.success) {
    throw new Error(orderNumberResult.error)
  }

  // Create order
  const orderData: OrderData = {
    order_number: orderNumberResult.orderNumber,
    user_id: userClerkId,
    client_id: clientId,
    client_name: clientName,
    status: 'open',
    order_date: new Date().toISOString().split('T')[0],
    total_amount: amount,
  }

  const orderId = await db.insertOrder(orderData)
  return orderId
}

List Orders with Pagination

Display paginated order list:

import { getDatabase } from '#libs/database'

async function getOrdersPage(userClerkId: string, page: number, pageSize: number = 10) {
  const db = getDatabase()
  const offset = (page - 1) * pageSize

  const [orders, totalCount] = await Promise.all([
    db.getOrders({
      user_id: userClerkId,
      limit: pageSize,
      offset,
      order_by: 'created_at',
      order_direction: 'desc',
    }),
    db.getOrdersCount({ user_id: userClerkId }),
  ])

  const totalPages = Math.ceil(totalCount / pageSize)

  return {
    orders,
    pagination: {
      currentPage: page,
      pageSize,
      totalCount,
      totalPages,
      hasNextPage: page < totalPages,
      hasPrevPage: page > 1,
    },
  }
}

Update Order Status

Move order through workflow stages:

async function updateOrderStatus(orderId: string, newStatus: OrderStatus) {
  const db = getDatabase()

  const success = await db.updateOrder(orderId, {
    status: newStatus,
  })

  if (!success) {
    throw new Error('Failed to update order status')
  }
}

// Usage
await updateOrderStatus('order-uuid-123', 'pending')
await updateOrderStatus('order-uuid-123', 'ready')
await updateOrderStatus('order-uuid-123', 'complete')

Client Order History

Show all orders for a specific client:

async function getClientOrderHistory(userClerkId: string, clientId: string) {
  const db = getDatabase()

  const orders = await db.getOrders({
    user_id: userClerkId,
    client_id: clientId,
    order_by: 'order_date',
    order_direction: 'desc',
    limit: 100,
  })

  const totalRevenue = orders.reduce((sum, order) => sum + order.total_amount, 0)
  const completedOrders = orders.filter(o => o.status === 'complete')

  return {
    orders,
    stats: {
      totalOrders: orders.length,
      completedOrders: completedOrders.length,
      totalRevenue,
    },
  }
}

Status-Based Dashboard

Show orders grouped by status:

async function getOrdersDashboard(userClerkId: string) {
  const db = getDatabase()

  const statuses: OrderStatus[] = ['open', 'pending', 'ready', 'complete', 'cancelled']

  const ordersByStatus = await Promise.all(
    statuses.map(async status => ({
      status,
      orders: await db.getOrders({
        user_id: userClerkId,
        status,
        limit: 50,
      }),
      count: await db.getOrdersCount({
        user_id: userClerkId,
        status,
      }),
    }))
  )

  return ordersByStatus
}

Delete Order

Remove an order (only owner can delete):

async function deleteOrder(orderId: string) {
  const db = getDatabase()

  const success = await db.deleteOrder(orderId)

  if (!success) {
    throw new Error('Order not found or no permission')
  }
}

Query Options

Filtering

Filter orders by various criteria:

const myOrders = await db.getOrders({
  user_id: userClerkId,
})

const openOrders = await db.getOrders({
  user_id: userClerkId,
  status: 'open',
})

const clientOrders = await db.getOrders({
  user_id: userClerkId,
  client_id: 'client-uuid-456',
})

const januaryOrders = await db.getOrders({
  user_id: userClerkId,
  start_date: '2026-01-01',
  end_date: '2026-01-31',
})

const page2Orders = await db.getOrders({
  user_id: userClerkId,
  limit: 10,
  offset: 10, // Skip first 10 results
})

Sorting

Control result order:

// Most recent first (default)
const recentOrders = await db.getOrders({
  user_id: userClerkId,
  order_by: 'created_at',
  order_direction: 'desc',
})

// By order date (chronological)
const byDate = await db.getOrders({
  user_id: userClerkId,
  order_by: 'order_date',
  order_direction: 'asc',
})

// By order number
const byOrderNumber = await db.getOrders({
  user_id: userClerkId,
  order_by: 'order_number',
  order_direction: 'asc',
})

Field Selection

Select only needed fields for better performance:

// Minimal data for list views
const orderList = await db.getOrders({
  user_id: userClerkId,
  fields: ['id', 'order_number', 'client_name', 'status', 'total_amount'],
  limit: 100,
})

// With dates
const withDates = await db.getOrders({
  user_id: userClerkId,
  fields: ['id', 'order_number', 'client_name', 'status', 'order_date'],
  limit: 100,
})

Pagination

Implement paginated order lists:

async function getOrdersPage(userClerkId: string, page: number, pageSize: number = 10) {
  const db = getDatabase()
  const offset = (page - 1) * pageSize

  const [orders, totalCount] = await Promise.all([
    db.getOrders({
      user_id: userClerkId,
      limit: pageSize,
      offset,
      order_by: 'created_at',
      order_direction: 'desc',
    }),
    db.getOrdersCount({ user_id: userClerkId }),
  ])

  const totalPages = Math.ceil(totalCount / pageSize)

  return {
    orders,
    pagination: {
      currentPage: page,
      pageSize,
      totalCount,
      totalPages,
      hasNextPage: page < totalPages,
      hasPrevPage: page > 1,
    },
  }
}

Example Usage:

const result = await getOrdersPage(userClerkId, 1, 10)
console.log(`Page ${result.pagination.currentPage} of ${result.pagination.totalPages}`)
console.log(`Total orders: ${result.pagination.totalCount}`)
result.orders.forEach(order => {
  console.log(`${order.order_number}: ${order.client_name} - $${order.total_amount}`)
})

React Components

OrderFormRHF

Form component for creating/editing orders using React Hook Form + Zod validation:

File: src/components/react/OrderFormRHF.tsx

Props:

type Props = {
  csrfToken: string // CSRF token for form submission
  order?: Order | null // For edit mode (optional)
  apiEndpoint?: string // Custom API endpoint (optional)
  clients: Array<{ id: string; name: string }> // Client dropdown options
}

Usage in Astro:

Create Mode
Edit Mode

---
import OrderFormRHF from '#components/react/OrderFormRHF.tsx'
import { generateCSRFToken } from '#utils/csrf'
import { getDatabase } from '#libs/database'

const csrfToken = await generateCSRFToken(Astro.locals.userId)

// Fetch clients for dropdown
const db = getDatabase()
const clientsData = await db.getClients({
  created_by: Astro.locals.userId,
  fields: ['id', 'first_name', 'last_name'],
  limit: 100,
})

const clients = clientsData.map(c => ({
  id: c.id,
  name: `${c.first_name} ${c.last_name}`,
}))
---

<OrderFormRHF client:load csrfToken={csrfToken} clients={clients} />

---
import OrderFormRHF from '#components/react/OrderFormRHF.tsx'
import { generateCSRFToken } from '#utils/csrf'
import { getDatabase } from '#libs/database'

const orderId = Astro.params.id
const db = getDatabase()

const order = await db.getOrderById(orderId)
const clientsData = await db.getClients({
  created_by: Astro.locals.userId,
  fields: ['id', 'first_name', 'last_name'],
  limit: 100,
})

const clients = clientsData.map(c => ({
  id: c.id,
  name: `${c.first_name} ${c.last_name}`,
}))

const csrfToken = await generateCSRFToken(Astro.locals.userId)
---

<OrderFormRHF
  client:load
  order={order}
  csrfToken={csrfToken}
  clients={clients}
  apiEndpoint={`/api/orders/edit/${orderId}`}
/>

Features:

Client selection dropdown with search
Order status radio buttons
Date picker for order_date
Amount input with validation (>= 0)
Notes textarea (max 1000 chars)
Real-time validation with error messages
Error summary with anchor links
CSRF token integration
@fpkit/acss components for accessibility

OrdersTable

The /dashboard/orders roster, sharing the products and events tables’ markup and prd-* styles:

File: src/components/react/orders/OrdersTable.tsx

Usage:

---
import { OrdersTable } from '#components/react/orders'
---

<OrdersTable
  orders={orders}
  currentPage={currentPage}
  totalPages={totalPages}
  totalCount={totalCount}
  pageSize={10}
  basePath="/dashboard/orders"
/>

Features:

One row per order: date, client (initials, name, order number), services, total, status, edit link
Status pill in the service-day desk’s wording (pending reads “In progress”); cancelled rows are dimmed
Reads order_date as a calendar date, so a date never shifts with the viewer’s timezone
URL-based pager and “Showing x–y of n orders” summary
No row actions beyond Edit, so the page renders it without a client:* directive

Deleting an order happens on /dashboard/orders/edit/[id]: the sidebar mounts DeleteOrderButton (src/components/react/orders/DeleteOrderButton.tsx) for orders canDeleteOrder allows (open, in progress, cancelled). It confirms, sends DELETE /api/orders/delete/[id] with the CSRF token from Astro.locals.csrfToken, and returns to the orders list.

OrdersListView

Presentation component for displaying order lists. Used by the service-day order desk (/service-day/order-desk); the dashboard uses OrdersTable above.

File: src/components/react/OrdersListView.tsx

Props:

type OrdersListViewProps = {
  orders: Order[]
  isLoading?: boolean
  showActions?: boolean
  emptyMessage?: string
  currentPage?: number
  totalPages?: number
  onPageChange?: (page: number) => void
}

Usage:

---
import OrdersListView from '#components/react/OrdersListView.tsx'
import { getDatabase } from '#libs/database'

const db = getDatabase()
const orders = await db.getOrders({
  user_id: Astro.locals.userId,
  limit: 10,
  order_by: 'created_at',
  order_direction: 'desc',
})
---

<OrdersListView orders={orders} showActions={true} />

Features:

Card-based grid layout using @fpkit/acss
Displays order number, client name, status, date, amount
Color-coded status badges
Currency formatting for amounts
Date formatting (e.g., “Jan 19, 2026”)
Loading state with accessible alert
Empty state with custom message
Pagination controls (Previous/Next buttons)
Edit action buttons for each order

Validation Rules

Complete validation reference:

Field	Required	Min/Max	Format	Special Rules
client_id	Yes	N/A	UUID	Must be valid client ID (foreign key)
status	Yes	N/A	Enum	open, pending, ready, complete, cancelled
order_date	Yes	N/A	YYYY-MM-DD	Valid date format
total_amount	Yes	>= 0	Number	Non-negative, 2 decimal places
notes	No	0-1000 chars	Text	Trimmed
order_number	Yes	N/A	Text	Auto-generated (ORD-YYYYMMDD-NNN)
user_id	Yes	N/A	Text	Auto-set from Clerk authentication
client_name	Yes	N/A	Text	Fetched from client record during order creation

Security Model

Authentication

All order operations require:

Valid Clerk JWT token
User record in database (synced from Clerk)

Authorization

User-Scoped Access:

Users can only view their own orders
Users can only create orders attributed to themselves
Users can only update/delete their own orders
No organization-wide visibility (stricter than clients table)

Privileged Access (team_manager and above):

team_manager, team_admin, admin, and super_admin roles can bypass ownership enforcement
The bypass is controlled exclusively by resolveOrderPrivilege(locals) from #utils/orders
API routes must call resolveOrderPrivilege and branch on the result — never skip the gate
See Orders Utilities — Privilege Gate for the full pattern

Immutable Ownership:

user_id cannot be changed after creation
Orders cannot be transferred between users
Maintains strict audit trail

Client Ownership Verification

Before creating an order, the API endpoint verifies client ownership:

// Verify client exists and user has access
const client = await db.getClientById(clientId)

if (!client || client.created_by !== internalUserId) {
  return new Response(
    JSON.stringify({ error: 'Client not found or access denied' }),
    { status: 403 }
  )
}

Purpose:

Prevents IDOR (Insecure Direct Object Reference) attacks
Ensures client-order relationship integrity
Additional security layer beyond RLS

API Endpoints

POST /api/orders/create

Create a new order record.

Security: Auth + Rate Limit + CSRF + Validation + Client Ownership Check

Request (JSON):

{
  "client_id": "client-uuid-456",
  "status": "open",
  "order_date": "2026-01-19",
  "total_amount": 150.00,
  "notes": "Rush order",
  "csrfToken": "token-here"
}

Response (Success):

{
  "success": true,
  "orderId": "uuid-string",
  "orderNumber": "ORD-20260119-001"
}

GET /api/orders/edit/[id]

Retrieve a single order by ID.

Security: Auth

Response (Success):

{
  "success": true,
  "order": {
    "id": "uuid",
    "order_number": "ORD-20260119-001",
    "user_id": "user-clerk-123",
    "client_id": "client-uuid-456",
    "client_name": "John Doe",
    "status": "open",
    "order_date": "2026-01-19",
    "total_amount": 150.00,
    "notes": "Rush order",
    "created_at": "2026-01-19T10:30:00Z",
    "updated_at": "2026-01-19T10:30:00Z"
  }
}

PUT/POST /api/orders/edit/[id]

Update an existing order record.

Security: Auth + Rate Limit + CSRF + Validation + Ownership Check

Request (JSON):

{
  "status": "complete",
  "notes": "Delivered on time",
  "csrfToken": "token-here"
}

Response (Success):

{
  "success": true,
  "orderId": "uuid-string"
}

Performance Tips

Use Field Selection

For large result sets, select only needed fields:

// ✅ FAST - Minimal data transfer
const orders = await db.getOrders({
  user_id: userClerkId,
  fields: ['id', 'order_number', 'client_name', 'status', 'total_amount'],
  limit: 100,
})

// ⚠️ SLOWER - All fields returned
const orders = await db.getOrders({
  user_id: userClerkId,
  limit: 100,
})

Limit Result Sets

Always use pagination for lists:

// ✅ GOOD - Limited result set
const orders = await db.getOrders({
  user_id: userClerkId,
  limit: 10,
  offset: 0,
})

// ❌ BAD - Fetches all records
const allOrders = await db.getOrders({
  user_id: userClerkId,
  limit: 999999,
})

Leverage Indexes

Use indexed fields for filtering and sorting:

// ✅ FAST - Uses idx_orders_user_id
const myOrders = await db.getOrders({
  user_id: userClerkId,
})

// ✅ FAST - Uses idx_orders_status
const openOrders = await db.getOrders({
  user_id: userClerkId,
  status: 'open',
})

// ✅ FAST - Uses idx_orders_order_date
const recentOrders = await db.getOrders({
  user_id: userClerkId,
  start_date: '2026-01-01',
  end_date: '2026-01-31',
})

Troubleshooting

Order Number Generation Failed

Symptom: Failed to generate order number after 3 attempts error

Cause: High concurrent order creation causing repeated collisions

Solution:

Check database connectivity and performance
Verify order number uniqueness constraint exists
Consider increasing retry attempts in generateOrderNumber() if needed

Order Not Found After Creation

Symptom: Order created successfully but not visible in queries

Cause: RLS policy requires authentication and user_id match

Solution: Ensure JWT token is present and user_id matches authenticated user

Permission Denied on Update

Symptom: You do not have permission to update this order error

Cause: Attempting to update order owned by another user

Solution: Verify ownership (orders have strict user-scoped access, no admin override)

Client Not Found Error

Symptom: Client not found or access denied during order creation

Cause: Client doesn’t exist or user doesn’t have access

Solution:

Verify client exists with getClientById()
Ensure client is owned by the same user (check client.created_by)

Validation Errors

Symptom: Invalid input error on form submission

Cause: Field doesn’t meet validation requirements

Solution: Check validation rules table above and error details

Technical Reference:

Orders Table Feature - Complete technical documentation

Database Documentation:

Database Architecture - Understanding the abstraction layer
Clients System - Client management (foreign key dependency)
User Management - User attribution and authorization

Migration History:

Migration 017: Orders Table

Order Line Items

Each order can have multiple product line items tracked in the order_details table. Line items capture a price snapshot at the time of order so that order history is preserved accurately if products are later renamed or repriced.

Guide: Order Details System — database operations, security model, and query options
Technical reference: Order Details Table Feature — schema, RLS policies, migration 025

Overview

Quick Start

Creating Orders

Querying Orders

Order Structure

Required Fields

Optional Fields

Auto-Generated Fields

Order Number Format

Pattern

Automatic Generation

Common Use Cases

Create Order for Existing Client

List Orders with Pagination

Update Order Status

Client Order History

Status-Based Dashboard

Delete Order

Query Options

Filtering

Sorting

Field Selection

Pagination

React Components

OrderFormRHF

OrdersTable

OrdersListView

Validation Rules

Security Model

Authentication

Authorization

Client Ownership Verification

API Endpoints

POST /api/orders/create

GET /api/orders/edit/[id]

PUT/POST /api/orders/edit/[id]

Performance Tips

Use Field Selection

Limit Result Sets

Leverage Indexes

Troubleshooting

Order Number Generation Failed

Order Not Found After Creation

Permission Denied on Update

Client Not Found Error

Validation Errors

Related Documentation

Order Line Items