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

1 interface Order {
2   id: string;                           // Unique order identifier
3   orderNumber: string;                  // Order number (e.g., "CPS-0001")
4   customerName: string;                 // Customer full name
5   customerEmail: string;                // Customer email address
6   customerPhone: string;                // Customer phone number
7   fulfillmentType: 'ship' | 'pickup';  // Fulfillment method
8   shippingAddress?: string;             // Shipping street address
9   items: string;                        // Comma-separated order items
10   subtotal: string;                     // Subtotal amount
11   shippingFee: string;                  // Shipping fee
12   total: string;                        // Total amount
13   status: 'pending' | 'paid' | 'void' | 'cancelled';  // Order status
14   createdAt: string;                    // ISO timestamp
15   city?: string;                        // Shipping city
16   state?: string;                        // Shipping state
17   zipCode?: string;                     // Shipping ZIP code
18   shippingStatus?: string;              // Column P: 'shipped' or 'ship' (default)
19   feedback?: string;                    // Column Q: Customer feedback text
20 }

Sales Summary Schema

1 interface SalesSummary {
2   totalOrders: number;                  // Total order count
3   pendingCount: number;                 // Pending orders
4   completedCount: number;               // Completed (paid) orders
5   cancelledCount: number;               // Cancelled orders
6   totalRevenue: number;                 // Total revenue (paid orders)
7   pendingRevenue: number;               // Revenue from pending orders
8   averageOrderValue: number;            // Average order value
9 }

Order Endpoints

GET /api/orders/pending

Get all pending orders from Google Sheets.

Response Example:

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

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:

1 {
2   "totalOrders": 10,
3   "pendingCount": 3,
4   "completedCount": 6,
5   "cancelledCount": 1,
6   "totalRevenue": 1250.00,
7   "pendingRevenue": 350.00,
8   "averageOrderValue": 208.33
9 }

POST /api/orders

Create a new order in Google Sheets.

Request Body:

1 {
2   "customerName": "John Doe",
3   "customerEmail": "john@example.com",
4   "customerPhone": "5551234567",
5   "fulfillmentType": "ship",
6   "shippingAddress": "123 Main St",
7   "city": "Springfield",
8   "state": "IL",
9   "zipCode": "62701",
10   "items": "Photo #100 (4x6) x2 = $16.00",
11   "subtotal": "$16.00",
12   "shippingFee": "$5.00",
13   "total": "$21.00"
14 }

Response: Created Order object with generated order number

PUT /api/orders/:id

Update order status.

Parameters:

id (string): Order ID

Request Body:

1 {
2   "status": "paid"
3 }

Response: Updated Order object

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

Update shipping status toggle (Column P).

Parameters:

id (string): Order ID

Request Body:

1 {
2   "shipped": true
3 }

Response:

1 {
2   "success": true,
3   "message": "Shipping status updated successfully"
4 }

POST /api/orders/:orderNumber/feedback

Submit customer feedback (Column Q).

Parameters:

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

Request Body:

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

Response:

1 {
2   "success": true,
3   "message": "Feedback submitted successfully"
4 }

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:

1 {
2   "eventPrefix": "CPS"
3 }

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:

1 {
2   "error": "Description of the error",
3   "code": "ERROR_CODE",
4   "details": "Additional error details"
5 }

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.