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

Empty String Handling

For the optional notes field, empty strings are automatically converted to null. You can safely pass "" to clear notes.

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)

Concurrent Safety

The order number generation is designed to handle concurrent order creation. If multiple users create orders simultaneously, the retry logic prevents collisions and ensures unique order numbers.

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:

By User

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

By Status

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

By Client

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

Date Range

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

With Pagination

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,
})

Performance Tip

Selecting specific fields reduces data transfer and improves query performance. Use fields option for large result sets.

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

---
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} />

Edit Mode

---
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

Order Date Format

Order date must be in YYYY-MM-DD format. The system validates this format on the server side.

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

Ownership Model

The orders table has stricter ownership rules than the clients table. This ensures complete data isolation between users and prevents unauthorized access to financial data.

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"
}

Partial Updates

You only need to include fields you want to update. Omitted fields remain unchanged.

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

Related Documentation

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