Inventory

operations

Track products, stock levels, and movements across locations.

Collections

Products

Product catalog

Schema Fields

name:stringsku:stringcategory:stringunit_cost:numberunit_price:numbermin_stock:numberactive:boolean

Stock Movements

Inbound and outbound stock changes

Schema Fields

type:stringquantity:numberreason:stringreference:stringtimestamp:string

Locations

Warehouses and storage locations

Schema Fields

name:stringaddress:stringtype:stringcapacity:number

Relationships

stock_movements

for_product

productsMovement for product

stock_movements

at_location

locationsMovement at location

products

stored_at

locationsProduct stored at location

Example Queries

Query this template's data using the SQL endpoint.

Current stock levels

Calculate net stock per product by summing inbound and outbound movements.

WITH
  movements AS (
    SELECT
      target_id as product_id,
      source_id as movement_id
    FROM relationships FINAL
    WHERE workspace_id = {workspace_id:String}
      AND rel_type = 'for_product'
      AND deleted = false
  ),
  quantities AS (
    SELECT
      m.product_id,
      sum(CASE
        WHEN r.data.type.:String IN ('inbound', 'adjustment') THEN CAST(r.data.quantity, 'Float64')
        WHEN r.data.type.:String = 'outbound' THEN -CAST(r.data.quantity, 'Float64')
        ELSE 0
      END) as net_stock
    FROM movements m
    JOIN records r FINAL ON r.id = m.movement_id
      AND r.workspace_id = {workspace_id:String}
      AND r.deleted = false
    GROUP BY m.product_id
  )
SELECT
  p.data.name.:String as product,
  p.data.sku.:String as sku,
  coalesce(q.net_stock, 0) as stock,
  CAST(p.data.min_stock, 'Float64') as min_stock
FROM records p FINAL
LEFT JOIN quantities q ON q.product_id = p.id
WHERE p.workspace_id = {workspace_id:String}
  AND p.collection = 'products'
  AND p.deleted = false
ORDER BY stock ASC

Movement history

List recent stock movements with type and quantity.

SELECT
  data.type.:String as movement_type,
  CAST(data.quantity, 'Float64') as quantity,
  data.reason.:String as reason,
  data.reference.:String as reference,
  data.timestamp.:String as timestamp
FROM records FINAL
WHERE workspace_id = {workspace_id:String}
  AND collection = 'stock_movements'
  AND deleted = false
ORDER BY data.timestamp.:String DESC

Inventory value by category

Total inventory value grouped by product category using unit cost.

SELECT
  data.category.:String as category,
  count() as product_count,
  sum(CAST(data.unit_cost, 'Float64')) as total_cost,
  sum(CAST(data.unit_price, 'Float64')) as total_price
FROM records FINAL
WHERE workspace_id = {workspace_id:String}
  AND collection = 'products'
  AND deleted = false
  AND data.active.:Bool = true
GROUP BY category
ORDER BY total_price DESC

Quick Start

Create the collections using the Rekor CLI:

rekor collections upsert products --workspace my-inventory --schema @products.json

Full Schema (JSON)

Copy the complete template definition:

{
  "collections": [
    {
      "id": "products",
      "name": "Products",
      "description": "Product catalog",
      "icon": "box",
      "color": "#8b5cf6",
      "json_schema": {
        "type": "object",
        "required": [
          "name",
          "sku"
        ],
        "properties": {
          "name": {
            "type": "string"
          },
          "sku": {
            "type": "string"
          },
          "category": {
            "type": "string"
          },
          "unit_cost": {
            "type": "number",
            "minimum": 0
          },
          "unit_price": {
            "type": "number",
            "minimum": 0
          },
          "min_stock": {
            "type": "number",
            "minimum": 0
          },
          "active": {
            "type": "boolean",
            "default": true
          }
        }
      }
    },
    {
      "id": "stock_movements",
      "name": "Stock Movements",
      "description": "Inbound and outbound stock changes",
      "icon": "arrow-right-left",
      "color": "#22c55e",
      "json_schema": {
        "type": "object",
        "required": [
          "type",
          "quantity"
        ],
        "properties": {
          "type": {
            "type": "string",
            "enum": [
              "inbound",
              "outbound",
              "adjustment",
              "transfer"
            ]
          },
          "quantity": {
            "type": "number"
          },
          "reason": {
            "type": "string"
          },
          "reference": {
            "type": "string"
          },
          "timestamp": {
            "type": "string",
            "format": "date-time"
          }
        }
      }
    },
    {
      "id": "locations",
      "name": "Locations",
      "description": "Warehouses and storage locations",
      "icon": "map-pin",
      "color": "#f59e0b",
      "json_schema": {
        "type": "object",
        "required": [
          "name"
        ],
        "properties": {
          "name": {
            "type": "string"
          },
          "address": {
            "type": "string"
          },
          "type": {
            "type": "string",
            "enum": [
              "warehouse",
              "store",
              "dropship"
            ]
          },
          "capacity": {
            "type": "number"
          }
        }
      }
    }
  ],
  "relationships": [
    {
      "type": "for_product",
      "source": "stock_movements",
      "target": "products",
      "description": "Movement for product"
    },
    {
      "type": "at_location",
      "source": "stock_movements",
      "target": "locations",
      "description": "Movement at location"
    },
    {
      "type": "stored_at",
      "source": "products",
      "target": "locations",
      "description": "Product stored at location"
    }
  ]
}