Backflow

Appearance

E-Commerce Use Case ​

Build a complete e-commerce backend with Backflow.

Overview ​

This guide demonstrates building an e-commerce API with:

  • Product catalog management
  • Shopping cart functionality
  • Order processing with Stripe
  • Inventory tracking
  • Customer notifications

Route Aliasing ​

Use route aliases for domain-specific endpoints.

json
{
  "routeAliases": {
    "products": "/api/v1/products",
    "cart": "/api/v1/cart",
    "orders": "/api/v1/orders",
    "checkout": "/api/v1/checkout",
    "reviews": "/api/v1/reviews"
  }
}

Product Catalog ​

List Products ​

json
{
  "path": "/products",
  "method": "get",
  "supabaseQueries": [{
    "table": "products",
    "operation": "select",
    "select": "*, categories(name), variants(*)",
    "filters": [
      { "column": "is_active", "operator": "eq", "value": true }
    ],
    "order": { "column": "created_at", "ascending": false },
    "limit": "{{query.limit || 20}}",
    "offset": "{{query.offset || 0}}"
  }],
  "cache": {
    "enabled": true,
    "ttl": 300
  }
}

Search Products ​

json
{
  "path": "/products/search",
  "method": "get",
  "supabaseQueries": [{
    "table": "products",
    "operation": "select",
    "select": "*",
    "filters": [
      { "column": "name", "operator": "ilike", "value": "%{{query.q}}%" },
      { "column": "is_active", "operator": "eq", "value": true }
    ]
  }]
}

Get Product by ID ​

json
{
  "path": "/products/:id",
  "method": "get",
  "supabaseQueries": [{
    "table": "products",
    "operation": "select",
    "select": "*, categories(name), variants(*), reviews(*, users(name))",
    "filters": [
      { "column": "id", "operator": "eq", "value": "{{params.id}}" }
    ],
    "single": true
  }],
  "cache": {
    "enabled": true,
    "ttl": 600,
    "key": "product:{{params.id}}"
  }
}

Product by Category ​

json
{
  "path": "/categories/:slug/products",
  "method": "get",
  "supabaseQueries": [
    {
      "table": "categories",
      "operation": "select",
      "filters": [
        { "column": "slug", "operator": "eq", "value": "{{params.slug}}" }
      ],
      "single": true,
      "alias": "category"
    },
    {
      "table": "products",
      "operation": "select",
      "filters": [
        { "column": "category_id", "operator": "eq", "value": "{{results.category.id}}" },
        { "column": "is_active", "operator": "eq", "value": true }
      ]
    }
  ]
}

Shopping Cart ​

Get Cart ​

json
{
  "path": "/cart",
  "method": "get",
  "requireAuth": true,
  "supabaseQueries": [{
    "table": "cart_items",
    "operation": "select",
    "select": "*, products(id, name, price, image_url)",
    "filters": [
      { "column": "user_id", "operator": "eq", "value": "{{auth.sub}}" }
    ]
  }]
}

Add to Cart ​

json
{
  "path": "/cart",
  "method": "post",
  "requireAuth": true,
  "validation": {
    "body": {
      "product_id": { "type": "string", "required": true },
      "quantity": { "type": "number", "min": 1, "required": true },
      "variant_id": { "type": "string" }
    }
  },
  "supabaseQueries": [{
    "table": "cart_items",
    "operation": "upsert",
    "data": {
      "user_id": "{{auth.sub}}",
      "product_id": "{{body.product_id}}",
      "variant_id": "{{body.variant_id}}",
      "quantity": "{{body.quantity}}"
    },
    "onConflict": ["user_id", "product_id", "variant_id"],
    "select": "*, products(name, price)"
  }]
}

Update Cart Item ​

json
{
  "path": "/cart/:itemId",
  "method": "put",
  "requireAuth": true,
  "supabaseQueries": [{
    "table": "cart_items",
    "operation": "update",
    "data": {
      "quantity": "{{body.quantity}}"
    },
    "filters": [
      { "column": "id", "operator": "eq", "value": "{{params.itemId}}" },
      { "column": "user_id", "operator": "eq", "value": "{{auth.sub}}" }
    ],
    "select": "*"
  }]
}

Remove from Cart ​

json
{
  "path": "/cart/:itemId",
  "method": "delete",
  "requireAuth": true,
  "supabaseQueries": [{
    "table": "cart_items",
    "operation": "delete",
    "filters": [
      { "column": "id", "operator": "eq", "value": "{{params.itemId}}" },
      { "column": "user_id", "operator": "eq", "value": "{{auth.sub}}" }
    ]
  }]
}

Checkout & Orders ​

Create Checkout Session ​

json
{
  "path": "/checkout",
  "method": "post",
  "requireAuth": true,
  "workflow": {
    "id": "checkout-flow",
    "steps": [
      {
        "id": "get-cart",
        "type": "database",
        "params": {
          "table": "cart_items",
          "operation": "select",
          "select": "*, products(id, name, price, stock)",
          "filters": [
            { "column": "user_id", "operator": "eq", "value": "{{auth.sub}}" }
          ]
        }
      },
      {
        "id": "validate-stock",
        "type": "transform",
        "input": "{{steps.get-cart.result}}",
        "operations": [{
          "type": "filter",
          "condition": "item.quantity <= item.products.stock"
        }]
      },
      {
        "id": "create-stripe-session",
        "type": "integration",
        "integration": "stripe",
        "action": "createCheckoutSession",
        "params": {
          "line_items": "&#123;&#123;steps.validate-stock.result&#125;&#125;",
          "success_url": "&#123;&#123;body.success_url&#125;&#125;",
          "cancel_url": "&#123;&#123;body.cancel_url&#125;&#125;",
          "customer_email": "&#123;&#123;auth.email&#125;&#125;"
        }
      }
    ]
  }
}

Create Order (Webhook) ​

json
{
  "webhooks": [{
    "name": "stripe-checkout",
    "path": "/webhooks/stripe",
    "provider": "stripe",
    "secret": "&#123;&#123;env.STRIPE_WEBHOOK_SECRET&#125;&#125;",
    "actions": [{
      "condition": "&#123;&#123;payload.type&#125;&#125; === 'checkout.session.completed'",
      "workflow": {
        "steps": [
          {
            "id": "create-order",
            "type": "database",
            "params": {
              "table": "orders",
              "operation": "insert",
              "data": {
                "user_id": "&#123;&#123;payload.data.object.client_reference_id&#125;&#125;",
                "stripe_session_id": "&#123;&#123;payload.data.object.id&#125;&#125;",
                "amount_total": "&#123;&#123;payload.data.object.amount_total&#125;&#125;",
                "status": "paid"
              }
            }
          },
          {
            "id": "get-cart-items",
            "type": "database",
            "params": {
              "table": "cart_items",
              "operation": "select",
              "select": "*, products(*)",
              "filters": [
                { "column": "user_id", "operator": "eq", "value": "&#123;&#123;payload.data.object.client_reference_id&#125;&#125;" }
              ]
            }
          },
          {
            "id": "create-order-items",
            "type": "forEach",
            "items": "&#123;&#123;steps.get-cart-items.result&#125;&#125;",
            "step": {
              "type": "database",
              "params": {
                "table": "order_items",
                "operation": "insert",
                "data": {
                  "order_id": "&#123;&#123;steps.create-order.result.id&#125;&#125;",
                  "product_id": "&#123;&#123;item.product_id&#125;&#125;",
                  "quantity": "&#123;&#123;item.quantity&#125;&#125;",
                  "price": "&#123;&#123;item.products.price&#125;&#125;"
                }
              }
            }
          },
          {
            "id": "update-inventory",
            "type": "forEach",
            "items": "&#123;&#123;steps.get-cart-items.result&#125;&#125;",
            "step": {
              "type": "database",
              "params": {
                "table": "products",
                "operation": "update",
                "data": {
                  "stock": "products.stock - &#123;&#123;item.quantity&#125;&#125;"
                },
                "filters": [
                  { "column": "id", "operator": "eq", "value": "&#123;&#123;item.product_id&#125;&#125;" }
                ]
              }
            }
          },
          {
            "id": "clear-cart",
            "type": "database",
            "params": {
              "table": "cart_items",
              "operation": "delete",
              "filters": [
                { "column": "user_id", "operator": "eq", "value": "&#123;&#123;payload.data.object.client_reference_id&#125;&#125;" }
              ]
            }
          },
          {
            "id": "send-confirmation",
            "type": "integration",
            "integration": "sendgrid",
            "action": "sendTemplate",
            "params": {
              "to": "&#123;&#123;payload.data.object.customer_details.email&#125;&#125;",
              "templateId": "order-confirmation",
              "dynamicTemplateData": {
                "orderId": "&#123;&#123;steps.create-order.result.id&#125;&#125;",
                "total": "&#123;&#123;payload.data.object.amount_total&#125;&#125;"
              }
            }
          }
        ]
      }
    }]
  }]
}

Get Orders ​

json
{
  "path": "/orders",
  "method": "get",
  "requireAuth": true,
  "supabaseQueries": [{
    "table": "orders",
    "operation": "select",
    "select": "*, order_items(*, products(name, image_url))",
    "filters": [
      { "column": "user_id", "operator": "eq", "value": "&#123;&#123;auth.sub&#125;&#125;" }
    ],
    "order": { "column": "created_at", "ascending": false }
  }]
}

Get Order by ID ​

json
{
  "path": "/orders/:id",
  "method": "get",
  "requireAuth": true,
  "supabaseQueries": [{
    "table": "orders",
    "operation": "select",
    "select": "*, order_items(*, products(*)), shipments(*)",
    "filters": [
      { "column": "id", "operator": "eq", "value": "&#123;&#123;params.id&#125;&#125;" },
      { "column": "user_id", "operator": "eq", "value": "&#123;&#123;auth.sub&#125;&#125;" }
    ],
    "single": true
  }]
}

Reviews ​

Create Review ​

json
{
  "path": "/products/:productId/reviews",
  "method": "post",
  "requireAuth": true,
  "validation": {
    "body": {
      "rating": { "type": "number", "min": 1, "max": 5, "required": true },
      "title": { "type": "string", "maxLength": 100 },
      "content": { "type": "string", "maxLength": 2000 }
    }
  },
  "supabaseQueries": [
    {
      "table": "order_items",
      "operation": "select",
      "select": "orders!inner(user_id)",
      "filters": [
        { "column": "product_id", "operator": "eq", "value": "&#123;&#123;params.productId&#125;&#125;" },
        { "column": "orders.user_id", "operator": "eq", "value": "&#123;&#123;auth.sub&#125;&#125;" }
      ],
      "alias": "purchased"
    },
    {
      "table": "reviews",
      "operation": "insert",
      "condition": "&#123;&#123;results.purchased.length&#125;&#125; > 0",
      "data": {
        "product_id": "&#123;&#123;params.productId&#125;&#125;",
        "user_id": "&#123;&#123;auth.sub&#125;&#125;",
        "rating": "&#123;&#123;body.rating&#125;&#125;",
        "title": "&#123;&#123;body.title&#125;&#125;",
        "content": "&#123;&#123;body.content&#125;&#125;"
      },
      "select": "*"
    }
  ]
}

Get Product Reviews ​

json
{
  "path": "/products/:productId/reviews",
  "method": "get",
  "supabaseQueries": [{
    "table": "reviews",
    "operation": "select",
    "select": "*, users(name, avatar_url)",
    "filters": [
      { "column": "product_id", "operator": "eq", "value": "&#123;&#123;params.productId&#125;&#125;" },
      { "column": "is_approved", "operator": "eq", "value": true }
    ],
    "order": { "column": "created_at", "ascending": false }
  }],
  "cache": {
    "enabled": true,
    "ttl": 300
  }
}

Inventory Management ​

Update Stock ​

json
{
  "path": "/admin/products/:id/stock",
  "method": "put",
  "requireAuth": true,
  "rbac": {
    "roles": ["admin", "inventory_manager"]
  },
  "supabaseQueries": [{
    "table": "products",
    "operation": "update",
    "data": {
      "stock": "&#123;&#123;body.stock&#125;&#125;",
      "low_stock_threshold": "&#123;&#123;body.low_stock_threshold&#125;&#125;"
    },
    "filters": [
      { "column": "id", "operator": "eq", "value": "&#123;&#123;params.id&#125;&#125;" }
    ],
    "select": "id, name, stock"
  }],
  "integrations": [{
    "condition": "&#123;&#123;body.stock&#125;&#125; <= &#123;&#123;body.low_stock_threshold&#125;&#125;",
    "type": "slack",
    "action": "sendMessage",
    "params": {
      "channel": "#inventory-alerts",
      "text": "Low stock alert: &#123;&#123;params.id&#125;&#125; has &#123;&#123;body.stock&#125;&#125; units"
    }
  }]
}

Low Stock Report ​

json
{
  "path": "/admin/inventory/low-stock",
  "method": "get",
  "requireAuth": true,
  "rbac": {
    "roles": ["admin", "inventory_manager"]
  },
  "supabaseQueries": [{
    "table": "products",
    "operation": "select",
    "select": "id, name, stock, low_stock_threshold",
    "filters": [
      { "column": "stock", "operator": "lte", "raw": "low_stock_threshold" }
    ],
    "order": { "column": "stock", "ascending": true }
  }]
}

Complete Configuration ​

json
{
  "routeAliases": {
    "products": "/api/v1/products",
    "cart": "/api/v1/cart",
    "orders": "/api/v1/orders",
    "checkout": "/api/v1/checkout",
    "reviews": "/api/v1/reviews",
    "admin": "/api/v1/admin"
  },
  "supabase": {
    "url": "&#123;&#123;env.SUPABASE_URL&#125;&#125;",
    "anonKey": "&#123;&#123;env.SUPABASE_ANON_KEY&#125;&#125;"
  },
  "jwt": {
    "secret": "&#123;&#123;env.JWT_SECRET&#125;&#125;",
    "expiresIn": "24h"
  },
  "credentials": {
    "stripe": {
      "secretKey": "&#123;&#123;env.STRIPE_SECRET_KEY&#125;&#125;"
    },
    "sendgrid": {
      "apiKey": "&#123;&#123;env.SENDGRID_API_KEY&#125;&#125;",
      "fromEmail": "orders@yourstore.com"
    },
    "slack": {
      "webhookUrl": "&#123;&#123;env.SLACK_WEBHOOK_URL&#125;&#125;"
    }
  },
  "cache": {
    "provider": "redis",
    "url": "&#123;&#123;env.REDIS_URL&#125;&#125;"
  },
  "routes": [
    { "path": "/products", "method": "get", "...": "..." },
    { "path": "/cart", "method": "get", "...": "..." }
  ],
  "webhooks": [
    { "name": "stripe-checkout", "path": "/webhooks/stripe", "...": "..." }
  ]
}

Database Schema ​

sql
-- Products
CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  stock INTEGER DEFAULT 0,
  low_stock_threshold INTEGER DEFAULT 10,
  category_id UUID REFERENCES categories(id),
  image_url TEXT,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Cart Items
CREATE TABLE cart_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  product_id UUID REFERENCES products(id),
  variant_id UUID,
  quantity INTEGER DEFAULT 1,
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(user_id, product_id, variant_id)
);

-- Orders
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  stripe_session_id TEXT,
  amount_total INTEGER,
  status TEXT DEFAULT 'pending',
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Order Items
CREATE TABLE order_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id UUID REFERENCES orders(id),
  product_id UUID REFERENCES products(id),
  quantity INTEGER,
  price DECIMAL(10,2)
);

-- Reviews
CREATE TABLE reviews (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id UUID REFERENCES products(id),
  user_id UUID NOT NULL,
  rating INTEGER CHECK (rating >= 1 AND rating <= 5),
  title TEXT,
  content TEXT,
  is_approved BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now()
);

Released under the ISC License.

Copyright © 2024 Backflow