Picos Health API Documentation

Complete API reference for all Payload CMS Collections and Globals accessible in the Picos Health system.

Base URL

The API base URL is dynamically based on your organization's subdomain:

https://{org-name}.api.picoshealth.com/v1

Replace {org-name} with your organization's assigned subdomain. For example:

• https://eeda.api.picoshealth.com/v1
• https://healthcare-corp.api.picoshealth.com/v1
• https://my-company.api.picoshealth.com/v1

For development or testing environments, use:

https://dev-api.picoshealth.com/v1

API versions: All existing Collections, Globals, and Order Management endpoints remain available at /v1 indefinitely - no breaking changes, no migration required. The Cart API (added for programmatic checkout) is a new addition and is only available at /v2 (there is no legacy /v1/cart since the feature didn't exist before). Both versions are served from the same base domain, e.g. https://{org-name}.api.picoshealth.com/v2/cart.

Authentication

Most endpoints require JWT authentication via Authorization header:

Authorization: Bearer YOUR_JWT_TOKEN

📋 API Reference Overview

Special Endpoints

In addition to standard CRUD operations on Collections and Globals, the Picos Health API provides specialized endpoints:

• Search APIs - HCPCS code and SKU product fuzzy search with pagination
  • GET /v1/hcpcs/search - Search 75,000+ healthcare procedure codes
  • GET /v1/sku/search - Search 34,000+ products by description, manufacturer, and category
• Product Request API - Request products to be added to formulary
  • POST /v1/products/request - Submit product requests with optional HCPCS code association
• Cart API (new, v2) - Build a cart before checkout, scoped to the authenticated user
  • GET /v2/cart - View cart contents and running total (fee/tax/shipping included)
  • POST /v2/cart - Add a product variant to the cart
  • PATCH /v2/cart/{id} - Update a cart item's quantity
  • DELETE /v2/cart/{id} / DELETE /v2/cart - Remove an item or clear the cart
• Placing an Order Through the API - End-to-end guide: token \u2192 products \u2192 cart \u2192 payment method \u2192 checkout
• Order Management APIs - Complete order lifecycle management
  • POST /v1/orders/create - Check out the current user's cart into a new order (fee/tax/shipping applied automatically)
  • GET /v1/orders/view - List and filter orders
  • PATCH /v1/orders/{id}/approve - Approve pending orders (Tier 1 Admin)
  • PATCH /v1/orders/{id}/deny - Deny orders with reason
  • POST /v1/orders/{id}/reorder - Duplicate previous orders
  • DELETE /v1/orders/{id}/cancel - Cancel orders (1-hour window)

Collections

1. Users

Slug: users
Group: Settings
Description: User accounts with authentication and role-based access control.

Fields

Endpoints

Get All Users

GET /users?limit=10&page=1&sort=-createdAt

Query Parameters:

• limit (number): Results per page (default: 10)
• page (number): Page number (default: 1)
• sort (string): Sort field (prefix with - for descending)
• where (JSON): Filter conditions

Response:

{
  "docs": [
    {
      "id": "550e8400-e29b-41d4-a716-446655440000",
      "email": "user@example.com",
      "firstName": "John",
      "lastName": "Doe",
      "label": "John Doe",
      "role": "admin",
      "location": "New York, NY",
      "createdAt": "2024-01-15T10:30:00Z",
      "updatedAt": "2024-01-15T10:30:00Z"
    }
  ],
  "totalDocs": 1,
  "limit": 10,
  "page": 1,
  "totalPages": 1
}

Get User by ID

GET /users/{id}

Response:

{
  "id": "550e8400-e29b-41d4-a716-446655440000",
  "email": "user@example.com",
  "firstName": "John",
  "lastName": "Doe",
  "role": "admin",
  "createdAt": "2024-01-15T10:30:00Z",
  "updatedAt": "2024-01-15T10:30:00Z"
}

Create User

POST /users
Content-Type: application/json
Authorization: Bearer {token}

{
  "email": "newuser@example.com",
  "password": "securePassword123",
  "firstName": "Jane",
  "lastName": "Smith",
  "role": "patient"
}

Response:

{
  "id": "550e8400-e29b-41d4-a716-446655440001",
  "email": "newuser@example.com",
  "firstName": "Jane",
  "lastName": "Smith",
  "role": "patient",
  "createdAt": "2024-01-15T11:00:00Z"
}

Update User

PATCH /users/{id}
Content-Type: application/json
Authorization: Bearer {token}

{
  "firstName": "Janet",
  "role": "provider"
}

Delete User

DELETE /users/{id}
Authorization: Bearer {token}

2. Products

Slug: products
Group: Settings
Description: Formulary items with pricing, categories, and medical codes.

Fields

Pricing note: Every entry in a product's variants array reflects the live price with the Picos Processing Fee already applied (configured in Site Settings → Taxes & Shipping Defaults, e.g. 15%). This is the price to display to end users and the price used for cart/checkout - do not add the fee again client-side.

Endpoints

Get All Products

GET /products?limit=20&where={"status":{"equals":"published"}}

Response:

{
  "docs": [
    {
      "id": "prod-uuid-1",
      "title": "Compression Stockings",
      "price": 45.99,
      "currency": "USD",
      "description": "Medical-grade compression stockings",
      "sku": "CS-001",
      "hcpcs_code": ["E0663"],
      "manufacturer": "CompressWear Inc",
      "status": "published",
      "createdAt": "2024-01-10T09:15:00Z",
      "variants": [
        {
          "source_table": "products_chah",
          "sku": "CS-001-EA-1",
          "uom": "EA",
          "qty": "1",
          "currency": "USD",
          "price": "52.8885"
        }
      ]
    }
  ],
  "totalDocs": 1,
  "limit": 20,
  "page": 1
}

In this example the raw catalog price is 45.99; with the default 15% processing fee applied, the variants[0].price shown to the end user is 52.8885 (45.99 * 1.15).

Get Product by ID

GET /products/{id}?depth=2

Create Product

POST /products
Authorization: Bearer {token}

{
  "title": "Diabetes Test Strips",
  "price": 29.99,
  "currency": "USD",
  "description": "Reliable glucose test strips",
  "sku": "DTS-100",
  "hcpcs_code": ["A4253"],
  "categories": ["category-id-1"],
  "status": "draft"
}

Update Product

PATCH /products/{id}
Authorization: Bearer {token}

{
  "price": 24.99,
  "status": "published"
}

3. Orders

Slug: orders
Group: Marketplace
Description: Purchase orders with line items, customer info, and status tracking.

Fields

Endpoints

Get All Orders

GET /orders?limit=50&sort=-createdAt&where={"status":{"equals":"pending"}}

Response:

{
  "docs": [
    {
      "id": "order-uuid-1",
      "uuid": "order-uuid-1",
      "customer": "user-id-1",
      "total": 125.47,
      "currency": "USD",
      "status": "pending",
      "line_items": [
        {
          "id": "line-1",
          "product": "prod-id-1",
          "quantity": 2,
          "price": 45.99
        }
      ],
      "createdAt": "2024-01-20T14:30:00Z"
    }
  ],
  "totalDocs": 1
}

Get Order by ID

GET /orders/{id}?depth=2

Create Order

POST /orders
Authorization: Bearer {token}

{
  "customer": "user-id-1",
  "line_items": [
    {
      "product": "prod-id-1",
      "quantity": 2,
      "price": 45.99
    }
  ],
  "total": 125.47,
  "currency": "USD",
  "status": "pending",
  "shipping_address": {
    "street": "123 Main St",
    "city": "New York",
    "state": "NY",
    "zip": "10001"
  }
}

Update Order Status

PATCH /orders/{id}
Authorization: Bearer {token}

{
  "status": "approved"
}

4. Messages

Slug: messages
Group: Default
Description: In-app messaging system for user communication.

Fields

Endpoints

Get All Messages

GET /messages?limit=30&where={"to_user":{"equals":"user-id-1"}}

Create Message

POST /messages
Authorization: Bearer {token}

{
  "subject": "Your Order #12345",
  "body": "<p>Your order has been shipped!</p>",
  "from_user": "admin-id",
  "to_user": "customer-id"
}

Mark as Read

PATCH /messages/{id}
Authorization: Bearer {token}

{
  "read": true,
  "read_at": "2024-01-20T16:00:00Z"
}

5. Groups

Slug: groups
Group: Default
Description: User groups/organizations for hierarchical access control.

Fields

Endpoints

Get All Groups

GET /groups?limit=10

Create Group

POST /groups
Authorization: Bearer {token}

{
  "title": "Healthcare Providers",
  "description": "Group of medical professionals",
  "members": ["user-id-1", "user-id-2"]
}

6. Media

Slug: media
Group: Default
Description: File uploads with S3 storage integration.

Fields

Endpoints

Get All Media

GET /media?limit=25

Upload Media

POST /media
Authorization: Bearer {token}
Content-Type: multipart/form-data

[file data]

Response:

{
  "id": "media-uuid-1",
  "filename": "product-image.png",
  "filesize": 1024000,
  "mimeType": "image/png",
  "url": "https://s3.amazonaws.com/bucket/product-image.png",
  "uploadedBy": "user-id-1",
  "createdAt": "2024-01-20T12:00:00Z"
}

7. Documents

Slug: documents
Group: Default
Description: Document management for files and records.

Fields

Endpoints

Get All Documents

GET /documents?limit=20&where={"owner":{"equals":"user-id-1"}}

Create Document

POST /documents
Authorization: Bearer {token}

{
  "title": "Medical Records",
  "file": "media-id-1",
  "documentType": "medical",
  "owner": "user-id-1"
}

8. Members

Slug: members
Group: Default
Description: Organization members with roles and permissions.

Fields

Endpoints

Get All Members

GET /members?limit=50

Create Member

POST /members
Authorization: Bearer {token}

{
  "user": "user-id-1",
  "role": "provider",
  "status": "pending"
}

9. Patients

Slug: patients
Group: Default
Description: Patient records linked to users.

Fields

Endpoints

Get All Patients

GET /patients?depth=2

Create Patient

POST /patients
Authorization: Bearer {token}

{
  "user": "user-id-1",
  "dateOfBirth": "1985-06-15",
  "medicalConditions": ["diabetes", "hypertension"],
  "emergencyContact": {
    "name": "Jane Doe",
    "phone": "555-0100"
  }
}

10. Transactions

Slug: transactions
Group: Default
Description: Financial transaction records.

Fields

Endpoints

Get All Transactions

GET /transactions?limit=100&sort=-createdAt&where={"user":{"equals":"user-id-1"}}

Response:

{
  "docs": [
    {
      "id": "txn-uuid-1",
      "type": "charge",
      "amount": 125.47,
      "currency": "USD",
      "status": "completed",
      "user": "user-id-1",
      "description": "Order #12345 payment",
      "createdAt": "2024-01-20T14:35:00Z"
    }
  ]
}

11. TransactionLogs

Slug: transaction-logs
Group: Default
Description: Audit trail of all transaction activity.

Fields

Endpoints

Get Transaction Logs

GET /transaction-logs?limit=50&where={"transaction":{"equals":"txn-id-1"}}

12. Orders Activity Feed

Slug: orders-activity-feed
Group: Default
Description: Activity log for order status changes and events.

Fields

Endpoints

Get Order Activity

GET /orders-activity-feed?where={"order":{"equals":"order-id-1"}}

13. CheckoutSessions

Slug: checkout-sessions
Group: Marketplace
Description: Shopping cart/checkout session management.

Fields

Endpoints

Get Active Sessions

GET /checkout-sessions?where={"status":{"equals":"active"}}

Create Checkout Session

POST /checkout-sessions
Authorization: Bearer {token}

{
  "user": "user-id-1",
  "items": [
    {
      "product": "prod-id-1",
      "quantity": 2
    }
  ],
  "status": "active"
}

14. ProductCategories

Slug: product-categories
Group: Settings
Description: Product category taxonomy.

Fields

Endpoints

Get All Categories

GET /product-categories

Create Category

POST /product-categories
Authorization: Bearer {token}

{
  "title": "Mobility Aids",
  "slug": "mobility-aids",
  "description": "Canes, walkers, and wheelchairs"
}

15. ProductTags

Slug: product-tags
Group: Settings
Description: Flexible product tagging system.

Fields

Endpoints

Get All Tags

GET /product-tags

16. ProductFavorites

Slug: product-favorites
Group: Default
Description: User favorite/wishlist items.

Fields

Endpoints

Get User Favorites

GET /product-favorites?where={"user":{"equals":"user-id-1"}}

Add Favorite

POST /product-favorites
Authorization: Bearer {token}

{
  "user": "user-id-1",
  "product": "prod-id-1"
}

17. WalletBalance

Slug: wallet-balance
Group: Default
Description: User account balance tracking.

Fields

Endpoints

Get User Wallet

GET /wallet-balance?where={"user":{"equals":"user-id-1"}}

18. PaymentMethods

Slug: payment-methods
Group: Default
Description: Stored payment methods for users.

Fields

Endpoints

Get User Payment Methods

GET /payment-methods?where={"user":{"equals":"user-id-1"}}

Create Payment Method

POST /payment-methods
Authorization: Bearer {token}

{
  "user": "user-id-1",
  "type": "credit_card",
  "last4": "4242",
  "expiryDate": "12/2025",
  "isDefault": true
}

19. MyCareList

Slug: my-care-list
Group: Default
Description: Personalized care lists with assigned recipients and spending limits.

Fields

Endpoints

Get Care Lists

GET /my-care-list?where={"owner":{"equals":"user-id-1"}}

Create Care List

POST /my-care-list
Authorization: Bearer {token}

{
  "title": "Mom's Care List",
  "assignedTo": ["patient-id-1"],
  "items": [
    {
      "product": "prod-id-1",
      "quantity": 12
    }
  ],
  "spendingLimits": [
    {
      "recipient": "patient-id-1",
      "limit": 500
    }
  ],
  "owner": "user-id-1"
}

20. Invites

Slug: invites
Group: Default
Description: User invitations for registration and access.

Fields

Endpoints

Get Invites

GET /invites?where={"expiresAt":{"greater_than":"2024-01-20T00:00:00Z"}}

Send Invite

POST /invites
Authorization: Bearer {token}

{
  "email": "newuser@example.com",
  "role": "provider",
  "expiresAt": "2024-02-20T00:00:00Z"
}

21. Overseers

Slug: overseers
Group: Default
Description: Overseer/supervisor relationships for patient care management.

Fields

Endpoints

Get Overseers

GET /overseers?where={"patient":{"equals":"patient-id-1"}}

22. Fulfillment

Slug: fulfillment
Group: Marketplace
Description: Order fulfillment and shipping management.

Fields

Endpoints

Get Fulfillment Records

GET /fulfillment?where={"order":{"equals":"order-id-1"}}

Create Fulfillment

POST /fulfillment
Authorization: Bearer {token}

{
  "order": "order-id-1",
  "status": "shipped",
  "trackingNumber": "1Z999AA10123456784",
  "carrier": "UPS",
  "estimatedDelivery": "2024-01-25T00:00:00Z"
}

23. OrderAggregations

Slug: order-aggregations
Group: Default
Description: Aggregated order metrics and analytics data.

Fields

Endpoints

Get Order Aggregations

GET /order-aggregations?limit=30&sort=-createdAt

24. UserPermissionsConfig

Slug: user-permissions-config
Group: Settings
Description: System-wide permission configuration and role definitions.

Fields

Endpoints

Get Permission Config

GET /user-permissions-config?limit=20

Globals

Globals are singleton documents accessible via their slug:

1. SiteSettings

Slug: site-settings
Description: Global site configuration and branding.

Fields

Endpoints

Get Site Settings

GET /globals/site-settings

Response:

{
  "id": "global-uuid",
  "siteName": "Picos Health",
  "siteDescription": "Healthcare delivered.",
  "contactEmail": "support@picoshealth.com",
  "socialLinks": {
    "facebook": "https://facebook.com/picoshealth",
    "twitter": "https://twitter.com/picoshealth"
  },
  "createdAt": "2024-01-01T00:00:00Z",
  "updatedAt": "2024-01-20T12:00:00Z"
}

Update Site Settings

POST /globals/site-settings
Authorization: Bearer {token}

{
  "siteName": "Picos Health v2",
  "contactEmail": "hello@picoshealth.com"
}

2. OrganizationSettings

Slug: organization-settings
Description: Organization-wide configuration and policies.

Fields

Endpoints

Get Organization Settings

GET /globals/organization-settings

Update Organization Settings

POST /globals/organization-settings
Authorization: Bearer {token}

{
  "organizationName": "Healthcare Corp",
  "tier": "premium",
  "supportEmail": "support@healthcare.com"
}

3. MessageSettings

Slug: message-settings
Description: Messaging system configuration.

Fields

Endpoints

Get Message Settings

GET /globals/message-settings

4. Wallet

Slug: wallet
Description: Global wallet/payment system settings.

Fields

Endpoints

Get Wallet Settings

GET /globals/wallet

5. GlobalOrgDashboard

Slug: organization
Description: Organization dashboard configuration and data.

Fields

Endpoints

Get Organization Dashboard

GET /globals/organization

6. GlobalStorefrontHomepage

Slug: storefront-homepage
Description: Public storefront homepage configuration.

Fields

Endpoints

Get Storefront Homepage

GET /globals/storefront-homepage

7. GlobalFormularySettings

Slug: formulary-settings
Description: Product formulary display and filtering configuration.

Fields

Endpoints

Get Formulary Settings

GET /globals/formulary-settings

8. HCPCSFinder

Slug: hcpcs-finder
Description: HCPCS code lookup and management.

Fields

Endpoints

Get HCPCS Finder

GET /globals/hcpcs-finder

9. SKUFinder

Slug: sku-finder
Description: SKU lookup and cross-reference tool.

Fields

Endpoints

Get SKU Finder

GET /globals/sku-finder

Common Query Patterns

Filter Examples

Find orders by status:

GET /orders?where={"status":{"equals":"pending"}}

Find products in price range:

GET /products?where={"price":{"greater_than":10,"less_than":100}}

Find users by role:

GET /users?where={"role":{"in":["admin","provider"]}}

Complex filter (AND conditions):

GET /orders?where={"and":[{"status":{"equals":"pending"}},{"total":{"greater_than":50}}]}

Pagination Examples

Get page 2 with 25 items:

GET /users?limit=25&page=2

Get all results (use carefully):

GET /products?limit=1000&page=1

Sorting Examples

Sort by newest first:

GET /orders?sort=-createdAt

Sort by price ascending:

GET /products?sort=price

Multi-field sort:

GET /orders?sort=-status,createdAt

Error Responses

400 Bad Request

{
  "errors": [
    {
      "message": "Invalid query parameter",
      "field": "limit"
    }
  ]
}

401 Unauthorized

{
  "errors": [
    {
      "message": "Authentication required"
    }
  ]
}

403 Forbidden

{
  "errors": [
    {
      "message": "Insufficient permissions"
    }
  ]
}

404 Not Found

{
  "errors": [
    {
      "message": "Document not found",
      "id": "missing-id"
    }
  ]
}

500 Server Error

{
  "errors": [
    {
      "message": "Internal server error"
    }
  ]
}

Search APIs

Specialized endpoints for searching HCPCS codes and SKU products using fuzzy matching with pagination support.

📖 Comprehensive Guide: For detailed examples, advanced search patterns, and code samples, see the Search & Finder Endpoints Guide.

HCPCS Code Search

Search for Healthcare Common Procedure Coding System (HCPCS) codes using fuzzy matching against code numbers and descriptions.

Endpoint: GET /v1/hcpcs/search

Authentication: Required (Bearer token)

Query Parameters:

Request Example:

# Search for mask products
curl -X GET "https://{org-name}.api.picoshealth.com/v1/hcpcs/search?q=mask&page=1&limit=10" \
  -H "Authorization: Bearer YOUR_JWT_TOKEN"

# Search for oxygen equipment
curl -X GET "https://{org-name}.api.picoshealth.com/v1/hcpcs/search?q=oxygen&limit=25" \
  -H "Authorization: Bearer YOUR_JWT_TOKEN"

Response (1000 results for "mask"):

{
  "results": [
    {
      "HCPC": "A4620",
      "SEQNUM": "0010",
      "RECID": "3",
      "LONG DESCRIPTION": "Variable concentration mask",
      "SHORT DESCRIPTION": "Variable concentration mask",
      "PRICE1": "00",
      "PRICE2": "",
      "BETOS": "D1C",
      "TOS1": "P",
      "COV": "D",
      "MCM1": "3312",
      "ADD DT": "19900101",
      "ACT EFF DT": "20090101"
    },
    {
      "HCPC": "A4928",
      "LONG DESCRIPTION": "Surgical mask, per 20",
      "SHORT DESCRIPTION": "Surgical mask",
      "COV": "D",
      "BETOS": "P9B"
    },
    {
      "HCPC": "A6513",
      "LONG DESCRIPTION": "Compression burn mask, face and/or neck, plastic or equal, custom fabricated",
      "SHORT DESCRIPTION": "Compress burn mask face/neck",
      "COV": "C",
      "BETOS": "D1A"
    }
  ],
  "totalCount": 1000
}

Response Fields:

Searchable Fields:

• HCPC (code number)
• LONG DESCRIPTION
• SHORT DESCRIPTION

Search Algorithm:

• Exact matches (highest priority)
• Prefix matches (starts with search term)
• Substring matches (contains search term)
• Token-based matches (all words appear in field)
• Fuzzy matches (typo tolerance via Levenshtein distance)

Response Codes:

SKU Product Search

Search for products by description, manufacturer, category, or bar codes using fuzzy matching.

Endpoint: GET /v1/sku/search

Authentication: Required (Bearer token)

Query Parameters:

Request Example:

# Search for respiratory products
curl -X GET "https://{org-name}.api.picoshealth.com/v1/sku/search?q=respiratory&limit=10" \
  -H "Authorization: Bearer YOUR_JWT_TOKEN"

# Search for specific brand
curl -X GET "https://{org-name}.api.picoshealth.com/v1/sku/search?q=ResMed&limit=20" \
  -H "Authorization: Bearer YOUR_JWT_TOKEN"

# Search by category
curl -X GET "https://{org-name}.api.picoshealth.com/v1/sku/search?q=wheelchair&page=1&limit=25" \
  -H "Authorization: Bearer YOUR_JWT_TOKEN"

Response (237 results for "respiratory"):

{
  "results": [
    {
      "SKU": "RSP-001",
      "LONG_DESCRIPTION": "Oxygen concentrator, 5L per minute, portable",
      "SHORT_DESCRIPTION": "O2 Concentrator 5L",
      "MANUFACTURER": "ResMed",
      "CATEGORY": "Respiratory Equipment",
      "SUBCATEGORY": "Oxygen Systems",
      "PRICE": "899.99",
      "IN_STOCK": true,
      "HCPCS_CODE": "E1390",
      "UPC": "012345678901"
    },
    {
      "SKU": "RSP-002",
      "LONG_DESCRIPTION": "Portable oxygen tank, 2L capacity with carrying case",
      "MANUFACTURER": "Inogen",
      "CATEGORY": "Respiratory Equipment",
      "PRICE": "1299.99",
      "IN_STOCK": true
    }
  ],
  "totalCount": 237
}

Response Fields:

Searchable Fields:

• Long Description
• Manufacturer Name
• Category
• Subcategory
• Product Code (SKU)
• UPC / EAN / GTIN
• Associated HCPCS code

Pagination:

Both search endpoints support pagination using page and limit parameters:

# Get first 10 results
curl "https://{org-name}.api.picoshealth.com/v1/hcpcs/search?q=cpap&page=1&limit=10" \
  -H "Authorization: Bearer TOKEN"

# Get next 10 results
curl "https://{org-name}.api.picoshealth.com/v1/hcpcs/search?q=cpap&page=2&limit=10" \
  -H "Authorization: Bearer TOKEN"

# Total pages calculation
# totalPages = Math.ceil(totalCount / limit)
# Example: 1000 results ÷ 10 per page = 100 total pages

Performance:

• Initial request: ~100-200ms (first access)
• Cached requests: ~50-100ms (subsequent requests within 1-hour cache window)
• Search operation: Typically <50ms for 1000+ items
• Max results per request: 100 items (configurable via limit)
• Rate limit: 1000 requests per hour per API key

Data Sources:

Multi-Tenant Data Isolation:

Search results are automatically scoped to your organization's data:

# DEMO tenant
curl "https://demo.api.picoshealth.com/v1/sku/search?q=oxygen" \
  -H "Authorization: Bearer demo_token"
# Returns DEMO organization's products

# EEDA tenant
curl "https://eeda.api.picoshealth.com/v1/sku/search?q=oxygen" \
  -H "Authorization: Bearer eeda_token"
# Returns EEDA organization's products

Common Search Examples:

# Oxygen equipment
curl "https://{org-name}.api.picoshealth.com/v1/hcpcs/search?q=oxygen"

# Wound care supplies
curl "https://{org-name}.api.picoshealth.com/v1/sku/search?q=dressing%20bandage"

# Mobility aids
curl "https://{org-name}.api.picoshealth.com/v1/sku/search?q=wheelchair"

# Diabetic supplies
curl "https://{org-name}.api.picoshealth.com/v1/hcpcs/search?q=glucose%20meter"

# By manufacturer
curl "https://{org-name}.api.picoshealth.com/v1/sku/search?q=ResMed"

Error Handling:

# Missing authentication
# Response: 401 Unauthorized
{
  "error": "Missing or invalid Bearer token"
}

# Missing query parameter
# Response: 400 Bad Request
{
  "error": "Query parameter 'q' is required"
}

# No results found
# Response: 200 OK
{
  "results": [],
  "totalCount": 0
}

Product Request API

Create a request for products to be added to the formulary using data from the SKU search.

Endpoint: POST /v1/products/request

Authentication: Required (Bearer token)

Request Headers:

Authorization: Bearer YOUR_JWT_TOKEN
Content-Type: application/json

Request Body:

{
  "productData": {
    "Long Description": "CPAP Heated Tubing Kit Luna II G2",
    "Manufacturer Name": "3B Medical Inc",
    "Manufacturer Number": "3BCL1000",
    "Application": "CPAP Heated Tubing Kit",
    "Primary Category": "Respiratory",
    "Secondary Category": "CPAP / BPAP"
  },
  "productName": "CPAP Heated Tubing Kit",
  "hcpcsCode": "E0601",
  "isTier1": false,
  "userEmail": "user@organization.com",
  "userName": "John Doe",
  "userRole": "Admin",
  "organizationName": "Healthcare Organization"
}

Request Parameters (Body):

Response (Success):

{
  "success": true,
  "requestId": "req-1704758400000-abc123",
  "message": "Product request created successfully. Request ID: req-1704758400000-abc123",
  "data": {
    "product": {
      "requestId": "req-1704758400000-abc123",
      "productName": "CPAP Heated Tubing Kit Luna II G2",
      "manufacturer": "3B Medical Inc",
      "manufacturerNumber": "3BCL1000",
      "application": "CPAP Heated Tubing Kit",
      "categories": ["Respiratory", "CPAP / BPAP"],
      "hcpcsCode": "E0601",
      "isTier1": false,
      "requestedBy": {
        "email": "user@organization.com",
        "name": "John Doe",
        "role": "Admin",
        "organization": "Healthcare Organization"
      },
      "createdAt": "2024-01-20T16:00:00Z"
    },
    "hcpcsCode": "E0601",
    "createdAt": "2024-01-20T16:00:00Z"
  }
}

Response (Error):

{
  "error": "userEmail is required"
}

Example Requests:

# Basic product request
curl -X POST "https://{org-name}.api.picoshealth.com/v1/products/request" \
  -H "Authorization: Bearer YOUR_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "productData": {...},
    "hcpcsCode": "E0601",
    "userEmail": "john@healthcare.com",
    "userName": "John Doe"
  }'

# Tier 1 request with volume and pricing
curl -X POST "https://{org-name}.api.picoshealth.com/v1/products/request" \
  -H "Authorization: Bearer YOUR_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "productData": {...},
    "hcpcsCode": "E0601",
    "isTier1": true,
    "anticipatedVolume": 500,
    "volumeRate": "monthly",
    "priceAmount": "45.99",
    "priceCurrency": "USD",
    "userEmail": "admin@healthcare.com",
    "userName": "Jane Smith",
    "userRole": "Tier 1 Admin",
    "organizationName": "Premier Healthcare"
  }'

Response Codes:

Behavior:

• Accepts product requests with associated HCPCS codes
• Supports standard user requests and Tier 1 admin requests with volume/pricing
• Tier 1 requests require anticipatedVolume, volumeRate, and priceAmount
• All requests require user email and name for identification
• Creates a unique request ID for tracking
• Logs request details for administrative review

Integration:

This endpoint is designed to work seamlessly with the frontend RequestProductForFormulary component:

• Accepts the same SKU product data from Master-List.xlsx
• Supports HCPCS code selection
• Handles Tier 1-specific volume and pricing fields
• Returns request confirmation with unique ID for tracking

Cart API

New in v2 - base path /v2/cart (there is no legacy /v1/cart).

The Cart API lets an authenticated API caller build up a cart of product variants before checking out via POST /v1/orders/create. The cart is scoped to the user resolved from your bearer token (see Authentication) - there is no way to add items to another user's cart, and you never pass a userId yourself.

Every cart response includes a running total with the processing fee, tax, and shipping already calculated from Site Settings:

{
  "items": [ /* array of cart items, see below */ ],
  "subtotal": 105.78,
  "tax": 8.73,
  "shipping": 12.99,
  "total": 127.50
}

List Cart

Endpoint: GET /v2/cart

Authentication: Bearer token required

curl "https://{org-name}.api.picoshealth.com/v2/cart" \
  -H "Authorization: Bearer YOUR_BEARER_TOKEN"

Add Item to Cart

Endpoint: POST /v2/cart

Authentication: Bearer token required

Request Body:

curl -X POST "https://{org-name}.api.picoshealth.com/v2/cart" \
  -H "Authorization: Bearer YOUR_BEARER_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "productId": "66e7d74f-0da4-4705-9ccb-5e8f03273596",
    "sku": "688527R-EA-1",
    "quantity": 2
  }'

The server looks up the variant's current, fee-adjusted price by sku - it does not trust a client-supplied price, so the cart is always priced correctly even if catalog prices change.

Response (201 Created): the updated cart summary (items, subtotal, tax, shipping, total), plus addedItem for the specific line just added/incremented.

Update Item Quantity

Endpoint: PATCH /v2/cart/{id}

{id} is the cart item's own id (from a previous list/add response, not the product id).

curl -X PATCH "https://{org-name}.api.picoshealth.com/v2/cart/CART_ITEM_ID" \
  -H "Authorization: Bearer YOUR_BEARER_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"quantity": 3}'

Setting quantity to 0 or less removes the item.

Remove Item From Cart

Endpoint: DELETE /v2/cart/{id}

curl -X DELETE "https://{org-name}.api.picoshealth.com/v2/cart/CART_ITEM_ID" \
  -H "Authorization: Bearer YOUR_BEARER_TOKEN"

Alternatively, remove by productId + sku, or clear the entire cart, via DELETE /v2/cart with a body:

# Remove by product + sku
curl -X DELETE "https://{org-name}.api.picoshealth.com/v2/cart" \
  -H "Authorization: Bearer YOUR_BEARER_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"productId": "66e7d74f-...", "sku": "688527R-EA-1"}'

# Clear the whole cart
curl -X DELETE "https://{org-name}.api.picoshealth.com/v2/cart" \
  -H "Authorization: Bearer YOUR_BEARER_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"clear": true}'

Placing an Order Through the API: Step-by-Step

This walks through the full flow to go from an API key to a placed order.

Step 1 - Exchange your API key for a bearer token

curl -X POST "https://{org-name}.api.picoshealth.com/v1/auth/token" \
  -H "Content-Type: application/json" \
  -d '{"apiKey": "YOUR_API_KEY"}'

Every subsequent request in this guide uses Authorization: Bearer <token> from this response. The token encodes which user/organization the API key belongs to - the API resolves the current user from this token on every request, so you never need to (and cannot) pass a userId yourself when placing an order.

Step 2 - Find a product and its variant SKU

curl "https://{org-name}.api.picoshealth.com/v1/products?limit=20&search=wheelchair" \
  -H "Authorization: Bearer YOUR_BEARER_TOKEN"

Pick the product id and the sku of the variant (unit of measure/quantity/vendor combination) you want from its variants array. Prices shown already include the Picos Processing Fee - that's the real price the user pays.

Step 3 - Add the variant to your cart

curl -X POST "https://{org-name}.api.picoshealth.com/v2/cart" \
  -H "Authorization: Bearer YOUR_BEARER_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"productId": "PRODUCT_ID", "sku": "PRODUCT_SKU", "quantity": 1}'

Repeat for each item you want in the order. Check GET /v2/cart at any time to see the running subtotal/tax/shipping/total.

Step 4 - Get a payment method

The payment method must already exist on the user's account (created via the main website/dashboard, or POST /payment-methods, in advance - the API does not collect raw card numbers). Look up the user's saved payment methods:

curl "https://{org-name}.api.picoshealth.com/payment-methods?where={\"user\":{\"equals\":\"CURRENT_USER_ID\"}}" \
  -H "Authorization: Bearer YOUR_BEARER_TOKEN"

Take the id of the payment method you want to charge.

The payment method must be chargeable. A payment-methods row only works at checkout if its stripePaymentMethodId is a real Stripe PaymentMethod id, attached via Stripe.js/Elements card tokenization first (raw card numbers are never sent to this API). A payment method created without that step - last4/expiryDate alone - will be found and returned by this query, but POST /v1/orders/create will reject it with a 422 when you try to check out with it. See Placing Orders for the full create flow.

Step 5 - Check out the cart into an order

Endpoint: POST /v1/orders/create

Authentication: Bearer token required

Request Body:

curl -X POST "https://{org-name}.api.picoshealth.com/v1/orders/create" \
  -H "Authorization: Bearer YOUR_BEARER_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "paymentMethodId": "PAYMENT_METHOD_ID",
    "shippingAddress": "123 Main St",
    "shippingCity": "New York",
    "shippingState": "NY",
    "shippingZip": "10001",
    "recipientName": "John Doe"
  }'

What happens server-side:

1. The bearer token is decoded to resolve the current user - this becomes the order's placedBy.
2. If paymentMethodId was provided, it's verified to belong to that user; otherwise their default payment method (isDefault: true) is used. Either way, a 404 is returned if no matching payment method exists.
3. The user's cart (built via POST /v2/cart, not part of this request body) is loaded - an empty cart returns a 400 error. Cart contents are never accepted here, so a client can't override what was actually added to the cart.
4. Tax and shipping are computed from Site Settings against the (already fee-adjusted) cart subtotal:
   • tax = subtotal * taxRate
   • shipping = shippingRate, waived once subtotal >= freeShippingThreshold
5. The order total is charged to Stripe right now, synchronously, before anything is saved. The payment method's stripePaymentMethodId is attached to (or verified against) the user's Stripe customer, and a Stripe PaymentIntent is created for the full total with off_session: true, confirm: true. Test vs. live Stripe keys are selected automatically from your subdomain (dev/demo use Stripe test-mode keys; every other tenant uses live keys) - you never choose this yourself. A declined/invalid card returns 402 immediately and nothing else below happens - no order, no line items, no cart-clear.
6. Once the charge succeeds, the order + its line items are created with status pending, storing the resulting stripe_payment_intent_id and stripe_payment_status: "succeeded".
7. The cart is cleared.

Response (201 Created):

{
  "success": true,
  "orderId": "order-uuid-12345",
  "sourceId": "PICOS-AB12CD",
  "status": "pending",
  "subtotal": 105.78,
  "tax": 8.73,
  "shipping": 12.99,
  "total": 127.50,
  "stripePaymentIntentId": "pi_3U99AWDkvsUeYtWr3LBnzlYZ",
  "createdAt": "2026-08-27T16:00:00Z"
}

HTTP Status Codes:

Once created, use the Order Management API below (GET /v1/orders/view/{id}, approve/deny/reorder/cancel) to manage the order.

Order Management API

Complete API for viewing, approving, and managing orders in the Picos Health system. Orders are created via the cart checkout flow above (POST /v1/orders/create), not by posting raw line items directly.

Endpoint: GET /v1/orders/view

Authentication: Bearer token required

Description: Retrieves paginated list of orders with optional filtering by status, user, or approval status.

Query Parameters:

Response (Success):

{
  "orders": [
    {
      "id": "order-uuid-12345",
      "source_id": "PICOS-AB12CD",
      "placed_by_id": "user-uuid",
      "recipient": "Jane Doe",
      "recipient_type": "patient",
      "facility": "Austin, Texas",
      "status": "pending",
      "subtotal_amount": "105.78",
      "tax_amount": "8.73",
      "shipping_amount": "12.99",
      "amount": "127.50",
      "shipping_address": "123 Main St",
      "shipping_city": "New York",
      "shipping_state": "NY",
      "shipping_zip": "10001",
      "shipping_country": "US",
      "stripe_payment_intent_id": "pi_3U99AWDkvsUeYtWr3LBnzlYZ",
      "created_at": "2026-08-27T16:00:00Z",
      "lineItems": [
        {
          "id": "line-item-uuid",
          "title": "CPAP Heated Tubing Kit",
          "price": 45.99,
          "quantity": 2,
          "unitOfMeasure": "EA"
        }
      ]
    }
  ],
  "pagination": {
    "page": 1,
    "limit": 10,
    "totalCount": 42,
    "totalPages": 5
  }
}

HTTP Status Codes:

Example Request:

# Get pending orders with pagination
curl -X GET "https://63cvrae6wa.execute-api.us-east-1.amazonaws.com/v1/orders/view?page=1&limit=10&status=pending" \
  -H "Authorization: Bearer YOUR_JWT_TOKEN"

# Filter by the user who placed the order
curl -X GET "https://63cvrae6wa.execute-api.us-east-1.amazonaws.com/v1/orders/view?userId=USER_UUID" \
  -H "Authorization: Bearer YOUR_JWT_TOKEN"

View Single Order

Endpoint: GET /v1/orders/view/{id}

Authentication: Bearer token required

Description: Retrieves full details of a single order by ID.

Path Parameters:

Response (Success):

{
  "id": "order-uuid-12345",
  "source_id": "PICOS-AB12CD",
  "placed_by_id": "user-uuid",
  "recipient": "Jane Doe",
  "recipient_type": "patient",
  "facility": "Austin, Texas",
  "coordinator": "Jane Doe",
  "payment_method": "card",
  "payment_method_label": "\u2022\u2022\u2022\u2022 8210",
  "payment_method_type": "mastercard",
  "items_summary": "CPAP Heated Tubing Kit x2",
  "subtotal_amount": "105.78",
  "tax_amount": "8.73",
  "shipping_amount": "12.99",
  "amount": "127.50",
  "shipping_address": "123 Main St",
  "shipping_city": "New York",
  "shipping_state": "NY",
  "shipping_zip": "10001",
  "shipping_country": "US",
  "status": "pending",
  "stripe_payment_intent_id": "pi_3U99AWDkvsUeYtWr3LBnzlYZ",
  "stripe_payment_status": "succeeded",
  "created_at": "2026-08-27T16:00:00Z",
  "updated_at": "2026-08-27T16:00:00Z",
  "lineItems": [
    {
      "id": "line-item-uuid",
      "title": "CPAP Heated Tubing Kit Luna II G2",
      "price": 45.99,
      "quantity": 2,
      "unitOfMeasure": "EA",
      "vendorName": "3B Medical Inc"
    }
  ]
}

HTTP Status Codes:

Example Request:

curl -X GET "https://63cvrae6wa.execute-api.us-east-1.amazonaws.com/v1/orders/view/order-uuid-12345" \
  -H "Authorization: Bearer YOUR_JWT_TOKEN"

Approve Order

Endpoint: PATCH /v1/orders/{id}/approve

Authentication: Bearer token required (Tier 1 Admin only)

Description: Approves a pending order. Only users with Tier 1 Admin role can approve orders.

Path Parameters:

Request Body:

{
  "memberId": "admin-user-uuid"
}

Request Parameters:

Response (Success):

{
  "success": true,
  "orderId": "order-uuid-12345",
  "uuid": "order-uuid-12345",
  "approved": true,
  "approvalDate": "2024-01-20T16:15:00Z",
  "message": "Order approved successfully"
}

Response (Error):

{
  "error": "Order already approved",
  "status": 409
}

HTTP Status Codes:

Example Request:

curl -X PATCH "https://63cvrae6wa.execute-api.us-east-1.amazonaws.com/v1/orders/order-uuid-12345/approve" \
  -H "Authorization: Bearer YOUR_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "memberId": "admin-user-uuid"
  }'

Business Logic:

• Only Tier 1 Admins can approve orders
• Cannot approve already-approved orders (returns 409 Conflict)
• Sets order status to approved and records approval timestamp
• Stores the approving admin's member ID in approved_by field

Deny Order

Endpoint: PATCH /v1/orders/{id}/deny

Authentication: Bearer token required

Description: Denies or cancels an order with optional reason.

Path Parameters:

Request Body:

{
  "reason": "Out of stock",
  "memberId": "admin-user-uuid"
}

Request Parameters:

Response (Success):

{
  "success": true,
  "orderId": "order-uuid-12345",
  "uuid": "order-uuid-12345",
  "status": "cancelled",
  "reason": "Out of stock",
  "message": "Order denied successfully"
}

HTTP Status Codes:

Example Request:

curl -X PATCH "https://63cvrae6wa.execute-api.us-east-1.amazonaws.com/v1/orders/order-uuid-12345/deny" \
  -H "Authorization: Bearer YOUR_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "reason": "Out of stock",
    "memberId": "admin-user-uuid"
  }'

Business Logic:

• Cannot deny already-cancelled or already-approved orders
• Sets order status to cancelled
• Stores denial reason and approving admin ID

Reorder (Duplicate Previous Order)

Endpoint: POST /v1/orders/{id}/reorder

Authentication: Bearer token required

Description: Creates a new order by duplicating all line items and addresses from a previous order.

Path Parameters:

Request Body:

{
  "notes": "Same as before, please expedite",
  "paymentDetails": {
    "method": "card",
    "last4": "4242",
    "brand": "visa"
  }
}

Request Parameters:

Response (Success - 201 Created):

{
  "success": true,
  "orderId": "order-uuid-67890",
  "uuid": "order-uuid-67890",
  "originalOrderId": "order-uuid-12345",
  "status": "repeat_order_placed",
  "message": "Repeat order created successfully",
  "createdAt": "2024-01-21T10:30:00Z"
}

HTTP Status Codes:

Example Request:

curl -X POST "https://63cvrae6wa.execute-api.us-east-1.amazonaws.com/v1/orders/order-uuid-12345/reorder" \
  -H "Authorization: Bearer YOUR_JWT_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "notes": "Same products, please expedite delivery"
  }'

Business Logic:

• Copies all line_items from original order
• Copies shipping_address from original order
• Copies billing_address from original order
• Sets is_repeat flag to true for tracking
• Creates entirely new order with unique UUID
• Marks new order as repeat_order_placed initially

Cancel Order

Endpoint: DELETE /v1/orders/{id}/cancel

Authentication: Bearer token required

Description: Cancels an approved order within 1 hour of approval. Admins can bypass the time window with force=true.

Path Parameters:

Query Parameters:

Response (Success):

{
  "success": true,
  "orderId": "order-uuid-12345",
  "uuid": "order-uuid-12345",
  "status": "cancelled",
  "cancelledAt": "2024-01-20T16:45:00Z",
  "message": "Order cancelled successfully"
}

Response (Error - Outside cancellation window):

{
  "error": "Cannot cancel order more than 1 hour after approval",
  "timeElapsed": "65 minutes",
  "approvalTime": "2024-01-20T16:15:00Z",
  "attemptTime": "2024-01-20T17:20:00Z"
}

HTTP Status Codes:

Example Requests:

# Cancel within 1-hour window
curl -X DELETE "https://63cvrae6wa.execute-api.us-east-1.amazonaws.com/v1/orders/order-uuid-12345/cancel" \
  -H "Authorization: Bearer YOUR_JWT_TOKEN"

# Force cancel outside window (admin only)
curl -X DELETE "https://63cvrae6wa.execute-api.us-east-1.amazonaws.com/v1/orders/order-uuid-12345/cancel?force=true" \
  -H "Authorization: Bearer YOUR_JWT_TOKEN"

Business Logic:

• Enforces 1-hour cancellation window after order approval
• If order is not approved, allows immediate cancellation
• force=true parameter bypasses time window (admin override)
• Sets order status to cancelled
• Records cancellation timestamp in cancelledAt field
• Returns error code 410 if window expired without force flag

Webhook Events

Payload CMS triggers webhooks for key events:

• users.create - New user created
• users.update - User updated
• orders.create - New order created
• orders.update - Order status changed
• products.create - New product added
• products.update - Product updated
• payments.completed - Payment completed
• messages.create - New message sent

Best Practices

1. Always authenticate - Include Authorization header with JWT token
2. Use pagination - Don't fetch all records at once
3. Filter efficiently - Use where conditions server-side
4. Limit depth - Use depth parameter to control relationship expansion
5. Cache responses - Implement caching for read-heavy operations
6. Handle errors - Check response status and error messages
7. Rate limiting - Respect rate limits and implement backoff
8. Validate input - Validate data before sending to API

EEDA Database Migrations

Infrastructure for managing EEDA (Enterprise Edition Data Architecture) database migrations with separate credentials and deployment pipeline.

Overview

EEDA migrations run on a separate PostgreSQL database from the main Payload CMS database, allowing independent scaling and management for enterprise deployments.

Key Features:

• Separate database credentials via .env.eeda
• Idempotent migrations using IF NOT EXISTS patterns
• Transaction-safe execution with rollback support
• Dry-run capability for preview before applying
• Lexicographical ordering (date-based filenames)
• Migration tracking in _migrations table
• CLI support with multiple operation modes

Migration Runner

Location: /db-migrations/run-migrations-eeda.mjs

Usage:

# Apply all pending migrations
npm run migrate:eeda

# Preview migrations without applying
npm run migrate:eeda:dry-run

# Verbose output with detailed logging
npm run migrate:eeda:verbose

# Reset and rerun all migrations
npm run migrate:eeda:reset

Environment Setup:

Create .env.eeda file in project root:

EEDA_DATABASE_URL=postgresql://user:password@host:port/eeda_db?sslmode=require

Initialization Migrations

Two SQL files initialize the EEDA schema:

1. Organization Initialization

File: /db-migrations/20260721_initialize_eeda_organization.sql

Creates:

• EEDA organization record with metadata
• Organization settings (tier, branding, features)
• Media directory for asset storage

SQL Pattern:

-- Idempotent organization creation
INSERT INTO organizations (id, name, tier, settings, created_at)
VALUES (uuid_generate_v4(), 'EEDA Org', 'tier_1', '{}', NOW())
ON CONFLICT (name) DO UPDATE SET updated_at = NOW();

2. Product Categories Initialization

File: /db-migrations/20260721_initialize_eeda_product_categories.sql

Creates:

• Healthcare parent category
• 5 subcategories:
  • Assistive Devices
  • Medical Supplies
  • Diagnostic Equipment
  • Mobility Aids
  • Wound Care

Hierarchical Structure:

Healthcare (parent)
├── Assistive Devices
├── Medical Supplies
├── Diagnostic Equipment
├── Mobility Aids
└── Wound Care

SQL Pattern:

-- Create parent category
INSERT INTO product_categories (id, title, slug, parent_id)
VALUES (uuid_generate_v4(), 'Healthcare', 'healthcare', NULL)
ON CONFLICT DO NOTHING;

-- Create subcategories with parent reference
INSERT INTO product_categories (id, title, slug, parent_id)
VALUES (uuid_generate_v4(), 'Assistive Devices', 'assistive-devices', :parent_id)
ON CONFLICT DO NOTHING;

Adding New Migrations

File Naming Convention:

YYYYMMDD_description.sql
Example: 20260725_add_supplier_contacts.sql

Migration Template:

-- Migration: Add supplier contacts (20260725)
-- Description: Adds supplier contact information to organizations

-- Create supplier_contacts table if not exists
CREATE TABLE IF NOT EXISTS supplier_contacts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id),
  contact_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(20),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  CONSTRAINT unique_contact UNIQUE(organization_id, email)
);

-- Create index for lookups
CREATE INDEX IF NOT EXISTS idx_supplier_contacts_org 
  ON supplier_contacts(organization_id);

-- Add comment for documentation
COMMENT ON TABLE supplier_contacts IS 'Supplier contact information for each organization';

Idempotent Patterns:

✅ Use IF NOT EXISTS for creating objects:

CREATE TABLE IF NOT EXISTS table_name (...);
CREATE INDEX IF NOT EXISTS idx_name ON table(...);

✅ Use ON CONFLICT for inserts:

INSERT INTO table (col1, col2) VALUES (val1, val2)
ON CONFLICT (unique_col) DO UPDATE SET col2 = EXCLUDED.col2;

❌ Avoid non-idempotent patterns:

-- DON'T: Will fail if table exists
CREATE TABLE table_name (...);

-- DON'T: Will fail if column exists
ALTER TABLE table ADD COLUMN col_name ...;

Running Migrations

Standard Deployment:

# 1. Test in dry-run mode
npm run migrate:eeda:dry-run

# 2. Apply migrations
npm run migrate:eeda

# 3. Verify in application logs
npm run migrate:eeda:verbose

Force Reset (Development Only):

# WARNING: Deletes all EEDA data and re-runs migrations
npm run migrate:eeda:reset

Migration Status Tracking

The _migrations table tracks applied migrations:

SELECT * FROM _migrations ORDER BY applied_at DESC;

Schema:

CREATE TABLE _migrations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL UNIQUE,
  batch INT NOT NULL,
  applied_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Example Output:

│ batch │ name                                     │ applied_at          │
├───────┼──────────────────────────────────────────┼─────────────────────┤
│ 1     │ 20260721_initialize_eeda_organization    │ 2026-07-21 10:00 UTC│
│ 1     │ 20260721_initialize_eeda_product_... │ 2026-07-21 10:01 UTC│
│ 2     │ 20260725_add_supplier_contacts           │ 2026-07-25 14:30 UTC│

CLI Options

--dry-run:

npm run migrate:eeda:dry-run
# Shows SQL that would execute without applying changes

--verbose:

npm run migrate:eeda:verbose
# Logs detailed execution info, timing, and status

--reset:

npm run migrate:eeda:reset
# WARNING: Deletes _migrations table and re-runs all migrations

--db-url (Override):

npm run migrate:eeda -- --db-url postgresql://user:pass@host/db
# Override connection string from environment

Error Handling

Connection Errors:

Error: connect ECONNREFUSED 127.0.0.1:5432
→ Verify EEDA_DATABASE_URL is correct and database is running

Permission Errors:

Error: permission denied to create schema
→ Ensure database user has CREATE privilege

Already Applied:

Error: duplicate key value violates unique constraint "_migrations_name_key"
→ Migration already applied (idempotent, safe to retry)

Best Practices

1. Test Migrations Locally

   • Run npm run migrate:eeda:dry-run first
   • Verify SQL output matches expectations
   • Test with sample data

2. Backup Before Production

   pg_dump -U user -d eeda_db > backup.sql
   npm run migrate:eeda
   

3. Document Changes

   • Add comments in migration files
   • Update this documentation
   • Include in CHANGELOG

4. Name Migrations Clearly

   • Use date prefix: YYYYMMDD
   • Use descriptive name: add_supplier_contacts
   • Full example: 20260725_add_supplier_contacts.sql

5. Keep Migrations Small

   • One logical change per migration
   • Easier to debug and rollback
   • Clearer git history

Troubleshooting

Migration Not Running:

# Check if migration already applied
psql -U user -d eeda_db -c "SELECT * FROM _migrations WHERE name = '...'"

# If showing, force reset (dev only)
npm run migrate:eeda:reset

# If not showing, check file naming convention (YYYYMMDD format)
ls -la db-migrations/*.sql

Migration Stuck:

# Check active connections
psql -U user -d eeda_db -c "SELECT * FROM pg_stat_activity WHERE query LIKE '%migrations%'"

# Kill if needed (dangerous!)
psql -U user -d eeda_db -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query LIKE '%migrations%'"

Rollback (Manual):

-- Drop objects created by migration (DANGEROUS - use with caution)
DROP TABLE IF EXISTS supplier_contacts CASCADE;

-- Remove migration record
DELETE FROM _migrations WHERE name = '20260725_add_supplier_contacts';

-- Then fix SQL and re-run

Support

For API support and issues:

• Email: support@picoshealth.com
• Documentation: https://picoshealth.com/docs
• Issues: Report via admin dashboard or email