Products System

The products system provides comprehensive product catalog management with support for multiple categories, product variants, inventory tracking, and flexible pricing. Products can target multiple demographics and include customizable size/color options.

Overview

The products system supports:

Multi-Category Classification: Products can belong to multiple categories (e.g., apparel + accessories)
Multi-Gender Targeting: Products can target multiple demographics (e.g., male + female + kids)
Product Variants: Size and color options stored as arrays
Inventory Management: Stock status tracking with quantity management
Flexible Pricing: Decimal pricing with 2-digit precision
Image Support: Optional image URL for product display
Configuration-Driven: All options defined in config/products.config.ts
No Ownership Model: Global catalog accessible to all (read-only), managed by admins

📦 Product Catalog

Manage product information, pricing, inventory, and variants. Multi-select categories and genders provide maximum flexibility.

🎨 Product Variants

Support size and color variations for each product. Checkbox-based selection makes variant management intuitive.

💰 Pricing & Inventory

Track pricing with 2-decimal precision, inventory quantity, and stock availability. Optimize stock management with real-time updates.

🔒 Secure by Default

Role-based access ensures only admins can modify products. All authenticated users can browse the catalog.

Quick Start

Creating Products

import { getDatabase } from '#libs/database'
import type { ProductData } from '#libs/database'

const db = getDatabase()

// Minimal product (only required fields)
const minimalProduct: ProductData = {
  product_name: 'Basic T-Shirt',
  price: 19.99,
  genders: ['unisex'], // At least 1 gender required
}

const productId = await db.insertProduct(minimalProduct)
console.log(`Product created: ${productId}`)

// Product with variants
const variantProduct: ProductData = {
  product_name: 'Classic Hoodie',
  product_description: 'Comfortable cotton blend hoodie',
  image_url: 'https://example.com/hoodie.jpg',
  categories: ['apparel'],
  sizes: ['s', 'm', 'l', 'xl', 'xxl'],
  colors: ['light', 'dark'],
  in_stock: true,
  quantity: 50,
  price: 49.99,
  genders: ['male', 'female', 'unisex'],
}

const variantProductId = await db.insertProduct(variantProduct)

// Full product data with all options
const fullProduct: ProductData = {
  product_name: 'Premium Sneakers',
  product_description: 'High-quality athletic footwear with superior comfort',
  image_url: 'https://example.com/sneakers.jpg',
  categories: ['footwear', 'accessories'], // Multiple categories
  sizes: ['m', 'l', 'xl'],
  colors: ['light', 'dark'],
  in_stock: true,
  quantity: 100,
  price: 89.99,
  genders: ['male', 'female', 'kids'], // Multiple genders
}

const fullProductId = await db.insertProduct(fullProduct)

Querying Products

// Get all products (newest first)
const allProducts = await db.getProducts({
  limit: 20,
  order_by: 'created_at',
  order_direction: 'desc',
})

// Filter by category and gender
const mensApparel = await db.getProducts({
  categories: ['apparel'],
  genders: ['male'],
  limit: 10,
})

// Filter by price range
const affordableProducts = await db.getProducts({
  min_price: 10.0,
  max_price: 50.0,
  in_stock: true,
  limit: 20,
})

Product Structure

Required Fields

Field	Type	Description	Example	Validation
product_name	string	Product name	”Classic Hoodie”	Min 1 char, max 255 chars
price	number	Product price	49.99	>= 0, max 2 decimal places
genders	ProductGender[]	Target demographics (array)	[“male”, “female”]	Min 1 element, valid enum values (see below)

Gender Values:

male - Men’s products
female - Women’s products
unisex - Gender-neutral products
kids - Children’s products

Optional Fields

Field	Type	Description	Example	Validation
product_description	string	Product description	”Comfortable hoodie…”	Max 1000 chars
image_url	string	Product image URL	”https://example.com/img.jpg”	Valid URL format
categories	ProductCategory[]	Product categories (array)	[“apparel”, “accessories”]	Valid enum values (see below)
sizes	string[]	Available sizes (array)	[“s”, “m”, “l”, “xl”]	Valid size IDs from config
colors	string[]	Available colors (array)	[“light”, “dark”]	Valid color IDs from config
in_stock	boolean	Inventory availability	true	Boolean, default true
quantity	number	Available quantity	50	Integer, >= 0, default 0

Category Values:

apparel - Clothing items
accessories - Supplementary items
footwear - Shoes and footwear

Auto-Generated Fields

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

Common Use Cases

Create Product with Variants

Create a product with multiple sizes and colors:

import { getDatabase } from '#libs/database'

async function createProductWithVariants() {
  const db = getDatabase()

  const productData: ProductData = {
    product_name: 'Classic Hoodie',
    product_description: 'Comfortable cotton blend hoodie perfect for layering',
    image_url: 'https://example.com/images/classic-hoodie.jpg',
    categories: ['apparel'],
    sizes: ['s', 'm', 'l', 'xl', 'xxl'], // All standard sizes
    colors: ['light', 'dark'], // Available colors
    in_stock: true,
    quantity: 100,
    price: 49.99,
    genders: ['male', 'female', 'unisex'], // Target all demographics
  }

  try {
    const productId = await db.insertProduct(productData)
    console.log(`Product created with variants: ${productId}`)
    return productId
  } catch (error) {
    console.error('Failed to create product:', error)
    throw error
  }
}

Filter by Category and Gender

Display products filtered by specific criteria:

const apparelProducts = await db.getProducts({
  categories: ['apparel'],
  limit: 20,
})

const mensProducts = await db.getProducts({
  genders: ['male'],
  limit: 20,
})

const mensApparel = await db.getProducts({
  categories: ['apparel'],
  genders: ['male'],
  in_stock: true,
  limit: 20,
})

Price Range Queries

Find products within a specific price range:

async function getAffordableProducts() {
  const db = getDatabase()

  return await db.getProducts({
    min_price: 10.0,
    max_price: 50.0,
    in_stock: true, // Only show available products
    limit: 50,
    order_by: 'price',
    order_direction: 'asc', // Cheapest first
  })
}

// Usage
const budgetFriendly = await getAffordableProducts()
console.log(`Found ${budgetFriendly.length} affordable products`)

Update Inventory

Update stock status and quantity after a sale:

async function updateInventoryAfterSale(productId: string, soldQuantity: number) {
  const db = getDatabase()

  // Get current product
  const product = await db.getProductById(productId)
  if (!product) {
    throw new Error('Product not found')
  }

  // Calculate new inventory
  const newQuantity = product.quantity - soldQuantity
  const inStock = newQuantity > 0

  // Update product
  const success = await db.updateProduct(productId, {
    quantity: newQuantity,
    in_stock: inStock,
  })

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

  console.log(`Inventory updated: ${newQuantity} remaining`)
}

Product List with Pagination

Implement paginated product catalog:

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

  const [products, totalCount] = await Promise.all([
    db.getProducts({
      limit: pageSize,
      offset,
      order_by: 'created_at',
      order_direction: 'desc',
    }),
    db.getProductsCount(),
  ])

  const totalPages = Math.ceil(totalCount / pageSize)

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

// Usage
const result = await getProductsPage(1, 10)
console.log(`Page ${result.pagination.currentPage} of ${result.pagination.totalPages}`)
result.products.forEach(product => {
  console.log(`${product.product_name} - $${product.price}`)
})

Search by Name

Search products by name or description:

async function searchProducts(query: string) {
  const db = getDatabase()

  return await db.getProducts({
    search: query, // Searches product_name and product_description
    limit: 20,
  })
}

// Usage
const hoodies = await searchProducts('hoodie')
console.log(`Found ${hoodies.length} products matching "hoodie"`)

Product Catalog Display

Prepare products for catalog display with images and pricing:

import { formatProductPrice } from '#utils/products'

async function getProductCatalog() {
  const db = getDatabase()

  const products = await db.getProducts({
    fields: ['id', 'product_name', 'image_url', 'price', 'in_stock'],
    in_stock: true, // Only show available products
    limit: 50,
  })

  return products.map(product => ({
    id: product.id,
    name: product.product_name,
    imageUrl: product.image_url || '/images/placeholder.jpg',
    priceLabel: formatProductPrice(product.price), // "$49.99"
    available: product.in_stock,
  }))
}

Query Options

Filtering

Filter products by various criteria:

const apparelProducts = await db.getProducts({
  categories: ['apparel', 'accessories'], // Multiple categories
})

const kidsProducts = await db.getProducts({
  genders: ['kids'],
})

const midRangeProducts = await db.getProducts({
  min_price: 25.0,
  max_price: 75.0,
})

const inStockProducts = await db.getProducts({
  in_stock: true,
})

Sorting

Control result order:

// Newest products first (default)
const newest = await db.getProducts({
  order_by: 'created_at',
  order_direction: 'desc',
})

// Alphabetical by name
const alphabetical = await db.getProducts({
  order_by: 'product_name',
  order_direction: 'asc',
})

// By price (low to high)
const byPrice = await db.getProducts({
  order_by: 'price',
  order_direction: 'asc',
})

// By inventory quantity
const byQuantity = await db.getProducts({
  order_by: 'quantity',
  order_direction: 'desc',
})

Field Selection

Select only needed fields for better performance:

// Minimal data for list views
const productList = await db.getProducts({
  fields: ['id', 'product_name', 'price', 'image_url'],
  limit: 100,
})

// With categories and genders
const withMetadata = await db.getProducts({
  fields: ['id', 'product_name', 'price', 'categories', 'genders', 'in_stock'],
  limit: 100,
})

Product Configuration

Categories, Genders, Sizes, and Colors

All product options are defined in config/products.config.ts:

Available Categories:

apparel - Clothing items (shirts, pants, jackets)
accessories - Supplementary items (bags, hats, jewelry)
footwear - Shoes and footwear items

Available Genders:

male - Men’s products
female - Women’s products
unisex - Gender-neutral products
kids - Children’s products

Size Definitions:

// From config/products.config.ts
export const PRODUCT_SIZES = [
  { id: 's', name: 'S', displayOrder: 1, active: true },
  { id: 'm', name: 'M', displayOrder: 2, active: true },
  { id: 'l', name: 'L', displayOrder: 3, active: true },
  { id: 'xl', name: 'XL', displayOrder: 4, active: true },
  { id: 'xxl', name: 'XXL', displayOrder: 5, active: true },
]

Color Definitions:

// From config/products.config.ts
export const PRODUCT_COLORS = [
  { id: 'light', name: 'Light', displayOrder: 1, active: true },
  { id: 'dark', name: 'Dark', displayOrder: 2, active: true },
]

Helper Functions

import { getActiveSizes, getActiveColors } from '#config/products.config'

// Get all active sizes sorted by display order
const sizes = getActiveSizes()
// Returns: [{ id: 's', name: 'S', ... }, { id: 'm', name: 'M', ... }, ...]

// Get all active colors sorted by display order
const colors = getActiveColors()
// Returns: [{ id: 'light', name: 'Light', ... }, { id: 'dark', name: 'Dark', ... }]

Validation Rules

Complete validation reference:

Field	Required	Min/Max	Format	Special Rules
product_name	Yes	1-255 chars	Text	Trimmed
product_description	No	0-1000 chars	Text	Trimmed
image_url	No	N/A	URL	Valid URL format if provided
sizes	No	N/A	Array	Valid size IDs from PRODUCT_SIZES
colors	No	N/A	Array	Valid color IDs from PRODUCT_COLORS
in_stock	No	N/A	Boolean	Default: true
quantity	Yes	>= 0	Integer	Non-negative, default: 0
price	Yes	>= 0	Decimal	Max 2 decimal places
categories	No	N/A	Array	Valid values from PRODUCT_CATEGORIES
genders	Yes	Min 1 element	Array	Valid values from PRODUCT_GENDERS

React Components

ProductFormRHF

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

File: src/components/react/ProductFormRHF.tsx

Props:

type Props = {
  csrfToken: string // CSRF token for form submission
  product?: Product | null // For edit mode (optional)
  apiEndpoint?: string // Custom API endpoint (optional)
}

Usage in Astro:

Create Mode
Edit Mode

---
import ProductFormRHF from '#components/react/ProductFormRHF.tsx'
import { generateCSRFToken } from '#utils/csrf'

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

<ProductFormRHF client:load csrfToken={csrfToken} />

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

const productId = Astro.params.id
const db = getDatabase()
const product = await db.getProductById(productId)
const csrfToken = await generateCSRFToken(Astro.locals.userId)
---

<ProductFormRHF
  client:load
  product={product}
  csrfToken={csrfToken}
  apiEndpoint={`/api/products/${productId}`}
/>

Features:

One Card per section (Basic Information, Product Variants, Categorization, Inventory & Pricing)
Checkbox chips for multi-select categories, sizes, colors, and genders
Real-time validation with error messages
Error summary with anchor links to fields
Submission error display
CSRF token integration
@fpkit/acss components for accessibility
Unsaved-changes guard (useUnsavedChangesGuard): while the form is dirty, leaving the page raises the browser’s confirmation first. This matters most beside the recent-products rail, whose edit links are ordinary navigations. A successful save releases the guard before it redirects, so saving never prompts.

Implementation Highlights:

The form demonstrates checkbox-based multi-select pattern for array fields:

// Categories checkboxes
{
  getActiveCategories().map(category => (
    <Controller
      key={category}
      name="categories"
      control={control}
      render={({ field }) => (
        <Checkbox
          id={`category-${category}`}
          label={formatCategory(category)}
          checked={field.value?.includes(category) ?? false}
          onChange={checked => {
            const newValue = checked
              ? [...(field.value ?? []), category]
              : (field.value ?? []).filter(c => c !== category)
            field.onChange(newValue)
          }}
        />
      )}
    />
  ))
}

This pattern ensures proper state management for multi-select checkboxes.

ProductsListView

Presentation component for displaying product lists:

File: src/components/react/ProductsListView.tsx

Props:

type ProductsListViewProps = {
  products: Product[]
  isLoading?: boolean
  showActions?: boolean
  emptyMessage?: string
  currentPage?: number
  totalPages?: number
  onPageChange?: (page: number) => void
}

Usage:

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

const db = getDatabase()
const products = await db.getProducts({
  limit: 10,
  order_by: 'created_at',
  order_direction: 'desc',
})
---

<ProductsListView products={products} showActions={true} />

Features:

Card-based grid layout using @fpkit/acss
Displays name, price, categories, genders, stock status, quantity
Product image with fallback
Loading state with accessible alert
Empty state with custom message
Pagination controls (Previous/Next buttons)
Edit action buttons for each product

ProductFormWrapper

Server-rendered wrapper component that handles data fetching, authorization, and error handling for product forms:

File: src/components/astro/forms/ProductFormWrapper.astro

Props:

type Props = {
  // Mode detection
  productId?: string         // Product ID for edit mode (auto-detects edit mode if provided)
  mode?: 'create' | 'edit'  // Explicitly set mode (auto-detected from productId if not provided)

  // Data overrides
  product?: Product | null   // Pre-fetched product data (will fetch in edit mode if not provided)
  csrfToken?: string         // CSRF token (uses Astro.locals.csrfToken if not provided)

  // Configuration
  apiEndpoint?: string       // API endpoint override (auto-generated if not provided)
  showErrors?: boolean       // Show error alerts (default: true)
  hydration?: 'load' | 'visible' | 'idle'  // Hydration strategy (default: 'load')
  className?: string         // Additional CSS class name

  // Loading & Error
  showLoadingSkeleton?: boolean  // Show loading skeleton during fetch (default: true)
  loadingMessage?: string        // Loading message (default: "Loading product form...")
  errorMessages?: {
    notFound?: string
    unauthorized?: string
    fetchFailed?: string
  }

  // Authorization
  requiredRoles?: string[]    // Roles that can edit without ownership (default: ['super_admin'])
  skipOwnershipCheck?: boolean  // Skip ownership check (default: false)
}

Usage:

---
import ProductFormWrapper from '#components/astro/forms/ProductFormWrapper.astro'
---

<ProductFormWrapper />

---
import ProductFormWrapper from '#components/astro/forms/ProductFormWrapper.astro'

const productId = Astro.params.id
if (!productId) return Astro.redirect('/dashboard/products')
---

<ProductFormWrapper productId={productId} />

---
import Auth from '#layouts/Auth.astro'
import ProductFormWrapper from '#components/astro/forms/ProductFormWrapper.astro'
import { RecentProductsRail } from '#components/react/products'
import { loadRecentProducts } from '#utils/recent-products'

// Defense in depth — middleware already gates /dashboard/*.
if (!Astro.locals.userId) {
  return Astro.redirect('/login')
}

const productId = Astro.params.id

// Never throws: a failed lookup logs and returns [], which the rail
// renders as its empty state.
const recentProducts = await loadRecentProducts({
  correlationId: Astro.locals.correlationId,
  context: 'product edit page',
})
---

<Auth pageTitle="Edit Product">
  <section class="container">
    <div class="pfm-page">
      <div class="pfm-main">
        <ProductFormWrapper productId={productId} />
      </div>
      <aside>
        <RecentProductsRail products={recentProducts} currentProductId={productId} />
      </aside>
    </div>
  </section>
</Auth>

Features:

Auto-detects create vs edit mode from productId prop
Fetches product data server-side before rendering
Loading skeleton during data fetch
Environment-aware error messages (detailed in dev, safe in production)
CSRF token from Astro.locals.csrfToken or explicit prop
Configurable hydration strategy (load, visible, idle)

<!-- Allow team_admin to edit products -->
<ProductFormWrapper
  productId={productId}
  requiredRoles={['super_admin', 'admin', 'team_admin']}
  skipOwnershipCheck={true}
/>

API Endpoints Used:

Create: POST /api/products/create
Edit: PUT /api/products/edit/[id]

See also: Form Wrapper Components Guide for advanced usage patterns.

Security Model

Authentication

All product operations require:

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

Authorization

Public Read:

All authenticated users can view all products
No role restrictions for browsing catalog
Enables organization-wide product search

Admin Write:

Only team_admin, admin, and super_admin roles can create/update/delete products
Role verification at API endpoint level:

const userRole = await db.getUserRole(clerkId)
if (!['team_admin', 'admin', 'super_admin'].includes(userRole)) {
  return new Response(JSON.stringify({ error: 'Insufficient permissions' }), {
    status: 403,
  })
}

No Ownership Model:

Products are not attributed to specific users (no created_by field)
Any admin can edit any product
Simplifies product management across teams
Suitable for shared catalog systems

API Endpoints

POST /api/products/create

Create a new product record.

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

Request (JSON):

{
  "product_name": "Classic Hoodie",
  "product_description": "Comfortable hoodie",
  "image_url": "https://example.com/hoodie.jpg",
  "categories": ["apparel"],
  "sizes": ["s", "m", "l", "xl"],
  "colors": ["light", "dark"],
  "in_stock": true,
  "quantity": 50,
  "price": 49.99,
  "genders": ["male", "female", "unisex"],
  "csrfToken": "token-here"
}

Response (Success):

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

GET /api/products/[id]

Retrieve a single product by ID.

Security: Auth

Response (Success):

{
  "success": true,
  "product": {
    "id": "uuid",
    "product_name": "Classic Hoodie",
    "product_description": "Comfortable hoodie",
    "image_url": "https://example.com/hoodie.jpg",
    "categories": ["apparel"],
    "sizes": ["s", "m", "l", "xl"],
    "colors": ["light", "dark"],
    "in_stock": true,
    "quantity": 50,
    "price": 49.99,
    "genders": ["male", "female", "unisex"],
    "created_at": "2026-01-24T10:30:00Z",
    "updated_at": "2026-01-24T10:30:00Z"
  }
}

PUT /api/products/[id]

Update an existing product record.

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

Request (JSON):

{
  "price": 39.99,
  "quantity": 25,
  "in_stock": true,
  "csrfToken": "token-here"
}

Response (Success):

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

Performance Tips

Use Field Selection

For large result sets, select only needed fields:

// ✅ FAST - Minimal data transfer
const products = await db.getProducts({
  fields: ['id', 'product_name', 'price', 'image_url'],
  limit: 100,
})

// ⚠️ SLOWER - All fields returned
const products = await db.getProducts({
  limit: 100,
})

Limit Result Sets

Always use pagination for lists:

// ✅ GOOD - Limited result set
const products = await db.getProducts({
  limit: 10,
  offset: 0,
})

// ❌ BAD - Fetches all records
const allProducts = await db.getProducts({
  limit: 999999,
})

Leverage Indexes

Use indexed fields for filtering and sorting:

// ✅ FAST - Uses GIN index on categories
const apparelProducts = await db.getProducts({
  categories: ['apparel'],
})

// ✅ FAST - Uses GIN index on genders
const mensProducts = await db.getProducts({
  genders: ['male'],
})

// ✅ FAST - Uses idx_products_in_stock
const inStock = await db.getProducts({
  in_stock: true,
})

Troubleshooting

Product Not Found After Creation

Symptom: Product created successfully but not visible in queries

Cause: Possibly missing authentication or database sync issue

Solution: Ensure JWT token is present in request headers and user exists in database

Permission Denied on Update

Symptom: Insufficient permissions error on create/update/delete

Cause: User does not have required role (team_admin/admin/super_admin)

Solution: Verify user role in database matches required permissions

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

Array Handling Issues

Symptom: Categories or genders not saving correctly

Cause: Provider differences (JSONB vs JSON TEXT)

Solution: Arrays are automatically handled by database abstraction layer - ensure you’re passing JavaScript arrays (not strings)

Example:

// ✅ CORRECT - JavaScript array
categories: ['apparel', 'accessories']

// ❌ INCORRECT - String (will be rejected)
categories: "apparel,accessories"

Price Decimal Issues

Symptom: Price validation fails for values like 19.999

Cause: Price must have at most 2 decimal places

Solution: Round price to 2 decimals before submission:

const price = Math.round(inputPrice * 100) / 100 // Rounds to 2 decimals

Technical Reference:

Products Table Feature - Complete technical documentation

Database Documentation:

Database Architecture - Understanding the abstraction layer

Migration History:

Overview

Quick Start

Creating Products

Querying Products

Product Structure

Required Fields

Optional Fields

Auto-Generated Fields

Common Use Cases

Create Product with Variants

Filter by Category and Gender

Price Range Queries

Update Inventory

Product List with Pagination

Search by Name

Product Catalog Display

Query Options

Filtering

Sorting

Field Selection

Product Configuration

Categories, Genders, Sizes, and Colors

Helper Functions

Validation Rules

React Components

ProductFormRHF

ProductsListView

ProductFormWrapper

Security Model

Authentication

Authorization

API Endpoints

POST /api/products/create

GET /api/products/[id]

PUT /api/products/[id]

Performance Tips

Use Field Selection

Limit Result Sets

Leverage Indexes

Troubleshooting

Product Not Found After Creation

Permission Denied on Update

Validation Errors

Array Handling Issues

Price Decimal Issues

Related Documentation