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.

Overview

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)

Line Items

Each order detail links a product to an order with quantity and a price snapshot captured at purchase time.

Ownership via FK Chain

Access is enforced at the application layer through order_details.order_id → orders.user_id. No direct user_id column is needed.

Soft Delete

Deleting a line item sets deleted_at rather than removing the row. Deleted rows are excluded from all queries by default.

Dual Provider

Supabase uses PostgreSQL RLS + service role; Turso uses JOIN-based ownership in raw SQL. Both expose identical TypeScript method signatures.

Implementation Status

The full stack is implemented: database layer (migration, types, 5 CRUD operations), the POST /api/orders/[orderId]/details/create API endpoint, and the AddOrderDetailButton React component. See the Component Usage section below.

---

Data Model

TypeScript Interfaces

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

OrderDetail — retrieved record

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
}

OrderDetailData — create/update input

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
}

OrderDetailQueryOptions — query filters

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
}

Price Snapshot Pattern

Always copy product_name and unit_price from the product at the moment you create the line item. If the product is later renamed or repriced, the order history will remain accurate.

---

Database Operations

All methods are accessed through the database abstraction layer:

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

insertOrderDetail

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.

---

getOrderDetails

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

All items for an order

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

Filter by product

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

Include soft-deleted (audit)

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

With pagination

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.

---

getOrderDetailById

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 = ?.

---

updateOrderDetail

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.

Turso updated_at

Turso (SQLite) has no trigger support. The Turso provider sets updated_at manually in the UPDATE statement. Supabase uses a database trigger (trg_order_details_updated_at) from migration 001.

---

deleteOrderDetail

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.

---

Security Model

Two-Level Ownership Enforcement

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.

Supabase: Service Role + App-Level Checks

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

Operation	Ownership verification
insertOrderDetail	Pre-flight query: orders WHERE id=? AND user_id=?
getOrderDetails	Pre-fetch all caller’s order IDs, then IN (orderIds)
getOrderDetailById	Fetch record, then verify parent order ownership
updateOrderDetail	Two-query chain: detail → order → user_id check
deleteOrderDetail	Two-query chain + deleted_at IS NULL guard

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.

Turso: JOIN-Based Ownership

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.

Security Fixes (commit cd3b716)

Three issues were resolved when this system was finalized:

Issue	Fix Applied
IDOR on getOrderDetailById	Added ownership verification through parent order
Soft-delete bypass on deleteOrderDetail	Added deleted_at IS NULL guard to the ownership check query
Missing userId param in operations	All methods now require and validate userId

---

Query Options Reference

Option	Type	Default	Description
order_id	string	—	Filter to a specific order’s line items
product_id	string	—	Filter to a specific product across all orders
includeDeleted	boolean	false	When true, returns soft-deleted rows too
limit	number	50	Maximum rows to return
offset	number	0	Rows to skip (for pagination)

Pagination Example

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 Component

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

Quick Start

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

CSRF Token Required

csrfToken must come from Astro.locals.csrfToken (set by the middleware). Never generate or hardcode it client-side — the API endpoint validates it against the server-side cookie and will return 403 if the token is missing or expired.

Props Reference

Prop	Type	Required	Description
orderId	string	Yes	UUID of the target order (used in the API route param)
productId	string	Yes	UUID of the product to add as a line item
productName	string	Yes	Denormalized product name — copied at order time for historical accuracy
quantity	number	Yes	Number of units; must be a positive integer
unitPrice	number	No	Price per unit at time of order. Defaults to 0
itemDescription	string | null	No	Optional line-item notes (max 500 chars)
csrfToken	string	Yes	Server-generated CSRF token from Astro.locals.csrfToken
onSuccess	(detailId: string) => void	No	Callback fired with the new detail UUID after a successful insert
onError	(message: string) => void	No	Callback fired with the error message string on failure

Button States

The button label changes to reflect the current request state:

State	Label	Button	Notes
Idle	Add to Order	Enabled	Ready for interaction
Submitting	Saving...	Disabled	In-flight POST request
Success	Added!	Enabled	Resets to idle after 3 seconds
Error	Add to Order	Enabled	Inline Alert rendered above button

With Callbacks

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

Error Handling

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.

---

API Endpoint

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

Request

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

Security Pipeline

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

Responses

201 Created

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

400 Validation Error

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

401 Unauthenticated

{ "error": "Unauthorized" }

403 CSRF Failure

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

404 Order Not Found

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

429 Rate Limited

Standard rate-limit response with Retry-After header.

---

Related Documentation

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