Order Details System

The order details system manages individual product line items linked to an order. Each record captures a price snapshot at the moment of purchase so that order history remains accurate even if products are later renamed or repriced.

The order details system supports:

• Line Item Tracking: One record per product per order, with quantity and unit price
• Price Snapshots: product_name and unit_price are denormalized at order time for historical accuracy
• Soft Delete: Records are never physically removed — deleted_at is set instead
• Two-Level Ownership: Access is scoped to the authenticated user through the parent orders.user_id FK chain
• Security Fixes: IDOR, soft-delete bypass, and missing userId checks resolved in commit cd3b716
• Dual Provider Support: Identical behavior on Supabase (PostgreSQL) and Turso (LibSQL/SQLite)

import type {
  OrderDetail,
  OrderDetailData,
  OrderDetailQueryOptions,
} from '#libs/database-types'

export interface OrderDetail {
  id: string              // UUID primary key
  order_id: string        // FK → orders.id
  product_id: string      // FK → products.id
  product_name: string    // denormalized — preserved for historical accuracy
  quantity: number        // must be > 0
  unit_price: number      // price snapshot at time of order; defaults to 0
  item_description: string | null
  deleted_at: string | null // null = active; ISO timestamp = soft-deleted
  created_at: string
  updated_at: string
}

export interface OrderDetailData {
  order_id: string
  product_id: string
  product_name: string    // copy from products.name at order time
  quantity: number
  unit_price?: number     // optional; database defaults to 0
  item_description?: string | null
}

export interface OrderDetailQueryOptions {
  order_id?: string       // filter to a specific order
  product_id?: string     // filter to a specific product
  includeDeleted?: boolean // default false — excludes soft-deleted rows
  limit?: number          // default 50
  offset?: number         // default 0
}

All methods are accessed through the database abstraction layer:

import { getDatabase } from '#libs/database'
const db = getDatabase()

Creates a new line item. Verifies caller owns the target order before inserting.

const detailId = await db.insertOrderDetail(
  {
    order_id: 'order-uuid-123',
    product_id: 'product-uuid-456',
    product_name: 'Blue T-Shirt',  // snapshot from product
    quantity: 2,
    unit_price: 19.99,             // snapshot from product
    item_description: 'Size M',
  },
  userId  // must own the order
)
// Returns: UUID string of the created record

Ownership check: The method queries orders WHERE id = order_id AND user_id = userId before inserting. If the order is not found or belongs to another user, an error is thrown.

Retrieves all line items owned by the user. Excludes soft-deleted rows by default.

const items = await db.getOrderDetails(userId, {
  order_id: 'order-uuid-123',
})

const items = await db.getOrderDetails(userId, {
  product_id: 'product-uuid-456',
})

const allItems = await db.getOrderDetails(userId, {
  order_id: 'order-uuid-123',
  includeDeleted: true,
})

const page2 = await db.getOrderDetails(userId, {
  limit: 10,
  offset: 10,
})

Returns: OrderDetail[] — always scoped to the caller’s orders. Returns an empty array if the user has no orders.

Retrieves a single line item by UUID. Returns null for soft-deleted rows or records not owned by the caller.

const detail = await db.getOrderDetailById(detailId, userId)

if (!detail) {
  // not found, soft-deleted, or belongs to another user
  return new Response(JSON.stringify({ error: 'Not found' }), { status: 404 })
}

Ownership check (Supabase): fetches the record, then verifies orders.user_id = userId via a second query.

Ownership check (Turso): single JOIN query — FROM order_details JOIN orders ON orders.id = order_details.order_id WHERE orders.user_id = ?.

Updates writable fields on an existing line item. Only provided fields are changed; omitted fields remain unchanged.

const success = await db.updateOrderDetail(
  detailId,
  userId,
  {
    quantity: 3,
    unit_price: 17.99,
    item_description: 'Size L',
  }
)

if (!success) {
  // not found, soft-deleted, or not owned by caller
}

Updatable fields: product_id, product_name, quantity, unit_price, item_description.

Returns: boolean — true if a row was updated, false if not found or ownership failed.

Soft-deletes a line item by setting deleted_at to the current timestamp. The row is retained in the database for audit purposes.

const deleted = await db.deleteOrderDetail(detailId, userId)

if (!deleted) {
  // not found, already soft-deleted, or not owned by caller
}

Already-deleted guard: The Supabase provider includes .is('deleted_at', null) in the WHERE clause of the ownership check to prevent double-deleting. The Turso provider adds AND deleted_at IS NULL to the UPDATE WHERE clause for the same reason.

order_details has no user_id column. Ownership is derived through the FK chain:

order_details.order_id → orders.id (where orders.user_id = <caller>)

Every method that reads or writes data first verifies this chain at the application layer. This matches the pattern used in all other order operations.

The Supabase provider uses the service role client, which bypasses Postgres RLS. Ownership is therefore enforced in code rather than relying on RLS alone:

Even though Postgres RLS policies exist on the table (using auth.uid()), the service role bypasses them. The application-level checks are the authoritative enforcement layer.

The Turso provider enforces ownership in a single SQL JOIN rather than multiple round-trips:

SELECT od.*
FROM order_details od
JOIN orders o ON o.id = od.order_id
WHERE od.id = ?
  AND o.user_id = ?
  AND od.deleted_at IS NULL

This is more efficient and avoids TOCTOU (time-of-check / time-of-use) race conditions on reads.

Three issues were resolved when this system was finalized:

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

  return db.getOrderDetails(userId, {
    order_id: orderId,
    limit: pageSize,
    offset,
  })
}

AddOrderDetailButton is a self-contained React component that adds a product line item to an order in a single click. It handles the full request lifecycle — loading state, success confirmation, and inline error display — without navigating away from the page.

File: src/components/react/AddOrderDetailButton.tsx

In an Astro component, generate the CSRF token server-side and pass it as a prop:

---
import { AddOrderDetailButton } from '#components/react/AddOrderDetailButton'

const csrfToken = Astro.locals.csrfToken ?? ''
---

<AddOrderDetailButton
  client:load
  orderId="00000000-0000-0000-0000-000000000001"
  productId="00000000-0000-0000-0000-000000000002"
  productName="Blue T-Shirt"
  quantity={2}
  unitPrice={19.99}
  itemDescription="Size M"
  csrfToken={csrfToken}
/>

The button label changes to reflect the current request state:

Use onSuccess and onError to coordinate parent component state — for example, refreshing an order summary list after a successful add:

<AddOrderDetailButton
  orderId={orderId}
  productId={product.id}
  productName={product.name}
  quantity={1}
  unitPrice={product.price}
  csrfToken={csrfToken}
  onSuccess={(detailId) => {
    // detailId is the UUID of the newly created order_details row
    console.log('Added detail:', detailId)
    refreshOrderSummary()
  }}
  onError={(message) => {
    // message comes from result.details ?? result.error from the API
    reportError(message)
  }}
/>

The component displays errors inline using the Alert component from @fpkit/acss. The error clears automatically when a new submission starts. Errors come from two sources:

• API errors (non-2xx response): result.details ?? result.error ?? 'Failed to add item to order'
• Network errors (fetch throws): 'An unexpected error occurred'

No page navigation or reload occurs on either success or failure.

The component POSTs to POST /api/orders/[orderId]/details/create.

POST /api/orders/:orderId/details/create
Content-Type: application/json

{
  "product_id": "product-uuid-456",
  "product_name": "Blue T-Shirt",
  "quantity": 2,
  "unit_price": 19.99,
  "item_description": "Size M",
  "csrf_token": "<server-generated-token>"
}

The endpoint enforces a layered security pipeline before any database write:

1. Authentication — locals.userId must be present (401 if missing)
2. Rate limiting — sliding window via eventCreationRateLimiter (429 if exceeded)
3. Content-Type — must be application/json (400 if not)
4. CSRF validation — submitted token validated against the server-side cookie (403 if invalid)
5. Zod validation — body validated against orderDetailSchema (400 if invalid)
6. Order ownership — getOrderById(orderId, userDbId) verifies the caller owns the order (404 if not)
7. Insert — db.insertOrderDetail(data, userDbId) via the database abstraction layer

{ "success": true, "detailId": "new-detail-uuid-9999" }

{ "error": "Validation failed", "details": "Quantity must be a positive integer" }

{ "error": "Unauthorized" }

{ "error": "Security validation failed", "details": "CSRF token not found" }

{ "error": "Order not found", "details": "The specified order does not exist or you do not have access to it" }

System Guides:

• Orders System — Parent order management; order details extend this system
• Products System — Source of product_name and unit_price snapshots

Technical Reference:

• Order Details Table Feature — Schema, RLS policies, migration history, and Supabase vs Turso differences
• Database Architecture — How the abstraction layer works