Read-Only Order API Schema Documentation

Overview

Complete read-only API endpoints for the CPS Order Management System. All endpoints return JSON data and support order tracking, status management, shipping status tracking, customer feedback collection, and envelope generation.

Base URL

https://cps-orders.replit.app/api

Data Models

Order Schema

interface Order {
  id: string;                           // Unique order identifier
  orderNumber: string;                  // Order number (e.g., "CPS-0001")
  customerName: string;                 // Customer full name
  customerEmail: string;                // Customer email address
  customerPhone: string;                // Customer phone number
  fulfillmentType: 'ship' | 'pickup';  // Fulfillment method
  shippingAddress?: string;             // Shipping street address
  items: string;                        // Comma-separated order items
  subtotal: string;                     // Subtotal amount
  shippingFee: string;                  // Shipping fee
  total: string;                        // Total amount
  status: 'pending' | 'paid' | 'void' | 'cancelled';  // Order status
  createdAt: string;                    // ISO timestamp
  city?: string;                        // Shipping city
  state?: string;                        // Shipping state
  zipCode?: string;                     // Shipping ZIP code
  shippingStatus?: string;              // Column P: 'shipped' or 'ship' (default)
  feedback?: string;                    // Column Q: Customer feedback text
}

Sales Summary Schema

interface SalesSummary {
  totalOrders: number;                  // Total order count
  pendingCount: number;                 // Pending orders
  completedCount: number;               // Completed (paid) orders
  cancelledCount: number;               // Cancelled orders
  totalRevenue: number;                 // Total revenue (paid orders)
  pendingRevenue: number;               // Revenue from pending orders
  averageOrderValue: number;            // Average order value
}

Order Endpoints

GET /api/orders/pending

Get all pending orders from Google Sheets.

Response Example:

[
  {
    "id": "2",
    "orderNumber": "CPS-0001",
    "customerName": "Paul Smith",
    "customerEmail": "photos@example.com",
    "customerPhone": "3862445990",
    "fulfillmentType": "ship",
    "shippingAddress": "300 East Street",
    "items": "Photo #2314 (8x10) x4 = $80.00; Photo #4112 (4x6) x3 = $24.00",
    "subtotal": "$183.00",
    "shippingFee": "$0.00",
    "total": "$183.00",
    "status": "pending",
    "createdAt": "2025-10-27T03:53:43.976Z",
    "city": "Saint Augustine",
    "state": "FL",
    "zipCode": "32080",
    "shippingStatus": "ship"
  }
]

GET /api/orders/completed

Get all paid orders.

Response: Array of Order objects with status “paid”

GET /api/orders/cancelled

Get all cancelled orders.

Response: Array of Order objects with status “cancelled”

GET /api/orders/summary

Get sales summary statistics.

Response Example:

{
  "totalOrders": 10,
  "pendingCount": 3,
  "completedCount": 6,
  "cancelledCount": 1,
  "totalRevenue": 1250.00,
  "pendingRevenue": 350.00,
  "averageOrderValue": 208.33
}

POST /api/orders

Create a new order in Google Sheets.

Request Body:

{
  "customerName": "John Doe",
  "customerEmail": "john@example.com",
  "customerPhone": "5551234567",
  "fulfillmentType": "ship",
  "shippingAddress": "123 Main St",
  "city": "Springfield",
  "state": "IL",
  "zipCode": "62701",
  "items": "Photo #100 (4x6) x2 = $16.00",
  "subtotal": "$16.00",
  "shippingFee": "$5.00",
  "total": "$21.00"
}

Response: Created Order object with generated order number

PUT /api/orders/:id

Update order status.

Parameters:

id (string): Order ID

Request Body:

{
  "status": "paid"
}

Response: Updated Order object

POST /api/orders/:id/update-shipping

Update shipping status toggle (Column P).

Parameters:

id (string): Order ID

Request Body:

{
  "shipped": true
}

Response:

{
  "success": true,
  "message": "Shipping status updated successfully"
}

POST /api/orders/:orderNumber/feedback

Submit customer feedback (Column Q).

Parameters:

orderNumber (string): Order number (e.g., “UDCGENERAL25-0001”)

Request Body:

{
  "feedback": "Great service! Photos look amazing."
}

Response:

{
  "success": true,
  "message": "Feedback submitted successfully"
}

Envelope Generation Endpoints

GET /api/envelope/png/:orderId

Generate PNG shipping label for order.

Parameters:

orderId (string): Order ID

Response: PNG image file with shipping label

Label Format:

Top line: Order summary (e.g., “CPS-0001 Photo#100-4x6 (2)”)
Return address: Convention Photography Services address
Recipient box: Name, address, city/state/ZIP

GET /api/envelope/docx/:orderId

Generate DOCX shipping label for order.

Parameters:

orderId (string): Order ID

Response: DOCX file with editable shipping label

System Endpoints

GET /api/event-prefix

Get event prefix for order number generation.

Response Example:

{
  "eventPrefix": "CPS"
}

Response Headers

All API responses include:

Content-Type: application/json
Cache-Control: no-cache (for data endpoints)
Access-Control-Allow-Origin: *

Error Responses

Standard error format:

{
  "error": "Description of the error",
  "code": "ERROR_CODE",
  "details": "Additional error details"
}

Common HTTP Status Codes:

200 - Success
404 - Order not found
500 - Internal server error

Integration Notes

Google Sheets Backend

All order data stored in Google Sheets with:

Real-time sync: Orders update in real-time from spreadsheet
30-second cache: Order data cached for performance
Automatic refresh: Cache refreshed after updates
Column P: Shipping status toggle (shipped/ship)
Column Q: Customer feedback collection

Email Automation

SendGrid delivery: Professional receipt emails
SMTP fallback: Alternative delivery method
Envelope attachments: Both PNG and DOCX formats included

This API provides complete read-only access to the order management system with real-time Google Sheets integration, automated email delivery, shipping status tracking, and customer feedback collection.