Building a CRUD Application

This guide walks you through building a complete CRUD (Create, Read, Update, Delete) application in Stack9. We'll build a Product Management System with categories, inventory tracking, and pricing.

What You'll Build

By the end of this guide, you'll have:

• ✅ Product entity with fields for name, SKU, price, category
• ✅ Category entity for organizing products
• ✅ List view showing all products with filtering
• ✅ Detail view for viewing/editing individual products
• ✅ Auto-generated CRUD APIs
• ✅ Validation rules and business logic

Time Required: 45 minutes

Prerequisites

• Stack9 instance initialized
• Basic understanding of JSON
• Text editor or IDE

Step 1: Create the Category Entity

Categories organize products into logical groups.

File: src/entities/custom/category.json

{
  "head": {
    "name": "Category",
    "key": "category",
    "pluralisedName": "categories",
    "icon": "FolderOutlined",
    "allowComments": false,
    "allowTasks": false,
    "isActive": true
  },
  "fields": [
    {
      "label": "Name",
      "key": "name",
      "type": "TextField",
      "placeholder": "Enter category name",
      "description": "Category name (e.g., Electronics, Clothing)",
      "validateRules": {
        "required": true,
        "maxLength": 100,
        "minLength": 2
      },
      "index": true
    },
    {
      "label": "Description",
      "key": "description",
      "type": "TextField",
      "placeholder": "Enter category description",
      "validateRules": {
        "maxLength": 500
      }
    },
    {
      "label": "Is Active",
      "key": "is_active",
      "type": "Checkbox",
      "defaultValue": true,
      "description": "Whether this category is active"
    }
  ],
  "hooks": []
}

What This Does:

• Defines a Category entity with 3 fields
• name is required, indexed for fast searching
• is_active allows soft-disabling categories
• Stack9 auto-creates: database table, REST APIs, TypeScript types

Step 2: Create the Product Entity

File: src/entities/custom/product.json

{
  "head": {
    "name": "Product",
    "key": "product",
    "pluralisedName": "products",
    "icon": "ShoppingOutlined",
    "allowComments": true,
    "allowTasks": true,
    "allowAttachments": true,
    "isActive": true,
    "maxAttachmentSizeAllowed": 10000000,
    "acceptedAttachmentFileTypes": [
      "image/jpg",
      "image/jpeg",
      "image/png"
    ]
  },
  "fields": [
    {
      "label": "SKU",
      "key": "sku",
      "type": "TextField",
      "placeholder": "e.g., PROD-001",
      "description": "Stock Keeping Unit - unique product identifier",
      "validateRules": {
        "required": true,
        "maxLength": 50,
        "pattern": "^[A-Z0-9-]+$"
      },
      "behaviourOptions": {
        "readOnly": false
      },
      "index": true
    },
    {
      "label": "Product Name",
      "key": "name",
      "type": "TextField",
      "placeholder": "Enter product name",
      "validateRules": {
        "required": true,
        "maxLength": 200,
        "minLength": 3
      },
      "index": true
    },
    {
      "label": "Description",
      "key": "description",
      "type": "RichTextEditor",
      "description": "Detailed product description"
    },
    {
      "label": "Category",
      "key": "category_id",
      "type": "SingleDropDown",
      "relationshipOptions": {
        "ref": "category"
      },
      "typeOptions": {
        "label": "name"
      },
      "validateRules": {
        "required": true
      }
    },
    {
      "label": "Price",
      "key": "price",
      "type": "NumericField",
      "placeholder": "0.00",
      "description": "Product price in dollars",
      "validateRules": {
        "required": true,
        "min": 0
      },
      "typeOptions": {
        "decimals": 2,
        "allowNegative": false
      }
    },
    {
      "label": "Cost",
      "key": "cost",
      "type": "NumericField",
      "placeholder": "0.00",
      "description": "Product cost (for margin calculation)",
      "validateRules": {
        "required": false,
        "min": 0
      },
      "typeOptions": {
        "decimals": 2,
        "allowNegative": false
      }
    },
    {
      "label": "Stock Quantity",
      "key": "stock_quantity",
      "type": "NumericField",
      "placeholder": "0",
      "description": "Current stock level",
      "defaultValue": 0,
      "validateRules": {
        "required": true,
        "min": 0
      },
      "typeOptions": {
        "decimals": 0,
        "allowNegative": false
      }
    },
    {
      "label": "Reorder Level",
      "key": "reorder_level",
      "type": "NumericField",
      "placeholder": "10",
      "description": "Minimum stock level before reordering",
      "defaultValue": 10,
      "typeOptions": {
        "decimals": 0,
        "allowNegative": false
      }
    },
    {
      "label": "Status",
      "key": "status",
      "type": "OptionSet",
      "typeOptions": {
        "values": ["Active", "Discontinued", "Out of Stock"]
      },
      "defaultValue": "Active",
      "validateRules": {
        "required": true
      }
    },
    {
      "label": "Is Featured",
      "key": "is_featured",
      "type": "Checkbox",
      "defaultValue": false,
      "description": "Show this product prominently"
    }
  ],
  "hooks": []
}

What This Does:

• Creates Product entity with 11 fields
• Foreign key to Category (category_id)
• Price and cost tracking for margins
• Stock management fields
• Status field with predefined values
• Allows image attachments (product photos)

Step 3: Run Stack9 to Generate Resources

# In your Stack9 instance directory
npm run dev

# Stack9 automatically:
# 1. Creates database tables
# 2. Generates TypeScript models
# 3. Creates REST API endpoints
# 4. Registers entities in the system

Generated APIs (automatic):

# Category APIs
GET    /api/category/:id          # Get category
POST   /api/category              # Create category
PUT    /api/category/:id          # Update category
DELETE /api/category/:id          # Delete category
POST   /api/category/search       # Search categories

# Product APIs
GET    /api/product/:id           # Get product
POST   /api/product               # Create product
PUT    /api/product/:id           # Update product
DELETE /api/product/:id           # Delete product
POST   /api/product/search        # Search products

Step 4: Create Category List Screen

File: src/screens/category_list.json

{
  "head": {
    "title": "Categories",
    "key": "category_list",
    "route": "category-list",
    "app": "products",
    "icon": "FolderOutlined"
  },
  "screenType": "listView",
  "listQuery": "getcategorylist",
  "entityKey": "category",
  "columnsConfiguration": [
    {
      "field": "name",
      "label": "Category Name",
      "value": "{{name}}",
      "renderAs": "Text",
      "options": {
        "linkProp": "/products/category-detail/{{id}}"
      }
    },
    {
      "field": "description",
      "label": "Description",
      "value": "{{description}}",
      "renderAs": "Text"
    },
    {
      "field": "is_active",
      "label": "Status",
      "value": "{{is_active}}",
      "renderAs": "Boolean",
      "options": {
        "trueValue": "Active",
        "falseValue": "Inactive",
        "trueColor": "green",
        "falseColor": "red"
      }
    },
    {
      "field": "_created_at",
      "label": "Created",
      "value": "{{_created_at}}",
      "renderAs": "Date",
      "options": {
        "format": "DD MMM YYYY"
      }
    }
  ],
  "actions": [
    {
      "key": "create",
      "label": "Create Category",
      "type": "drawer",
      "drawerKey": "category_create"
    }
  ]
}

Step 5: Create Product List Screen

File: src/screens/product_list.json

{
  "head": {
    "title": "Products",
    "key": "product_list",
    "route": "product-list",
    "app": "products",
    "icon": "ShoppingOutlined"
  },
  "screenType": "listView",
  "listQuery": "getproductlist",
  "entityKey": "product",
  "columnsConfiguration": [
    {
      "field": "sku",
      "label": "SKU",
      "value": "{{sku}}",
      "renderAs": "Text",
      "options": {
        "linkProp": "/products/product-detail/{{id}}"
      }
    },
    {
      "field": "name",
      "label": "Product Name",
      "value": "{{name}}",
      "renderAs": "Text",
      "options": {
        "linkProp": "/products/product-detail/{{id}}"
      }
    },
    {
      "field": "category",
      "label": "Category",
      "value": "{{category.name}}",
      "renderAs": "Text"
    },
    {
      "field": "price",
      "label": "Price",
      "value": "{{price}}",
      "renderAs": "Currency",
      "options": {
        "currency": "USD"
      }
    },
    {
      "field": "stock_quantity",
      "label": "Stock",
      "value": "{{stock_quantity}}",
      "renderAs": "Number"
    },
    {
      "field": "status",
      "label": "Status",
      "value": "{{status}}",
      "renderAs": "EnumTags",
      "options": {
        "colorMapping": {
          "Active": "green",
          "Discontinued": "red",
          "Out of Stock": "orange"
        }
      }
    },
    {
      "field": "is_featured",
      "label": "Featured",
      "value": "{{is_featured}}",
      "renderAs": "Boolean",
      "options": {
        "trueValue": "Yes",
        "falseValue": "No"
      }
    }
  ],
  "actions": [
    {
      "key": "create",
      "label": "Create Product",
      "type": "drawer",
      "drawerKey": "product_create"
    },
    {
      "key": "export",
      "label": "Export to CSV",
      "type": "export"
    }
  ],
  "filters": [
    {
      "field": "category_id",
      "label": "Category",
      "type": "dropdown",
      "entityKey": "category"
    },
    {
      "field": "status",
      "label": "Status",
      "type": "dropdown",
      "options": ["Active", "Discontinued", "Out of Stock"]
    }
  ]
}

Step 6: Create Query Library Entries

File: src/query-library/getcategorylist.json

{
  "key": "getcategorylist",
  "name": "getCategoryList",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/category/search",
    "bodyParams": "{\n  \"$select\": [\"id\", \"name\", \"description\", \"is_active\", \"_created_at\"],\n  \"$where\": {\n    \"_is_deleted\": false\n  },\n  \"$orderBy\": [\n    {\"column\": \"name\", \"order\": \"asc\"}\n  ]\n}"
  }
}

File: src/query-library/getproductlist.json

{
  "key": "getproductlist",
  "name": "getProductList",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/product/search",
    "bodyParams": "{\n  \"$select\": [\n    \"id\",\n    \"sku\",\n    \"name\",\n    \"category_id\",\n    \"category.name\",\n    \"price\",\n    \"stock_quantity\",\n    \"status\",\n    \"is_featured\"\n  ],\n  \"$where\": {\n    \"_is_deleted\": false\n  },\n  \"$withRelated\": [\"category(notDeleted)\"],\n  \"$orderBy\": [\n    {\"column\": \"name\", \"order\": \"asc\"}\n  ]\n}"
  }
}

Step 7: Create Detail Screens

File: src/screens/product_detail.json

{
  "head": {
    "title": "Product Details",
    "key": "product_detail",
    "route": "product-detail/:id",
    "app": "products"
  },
  "screenType": "detailView",
  "detailQuery": "getproduct",
  "entityKey": "product",
  "sections": [
    {
      "title": "Basic Information",
      "fields": ["sku", "name", "description", "category_id", "status"]
    },
    {
      "title": "Pricing",
      "fields": ["price", "cost"]
    },
    {
      "title": "Inventory",
      "fields": ["stock_quantity", "reorder_level"]
    },
    {
      "title": "Settings",
      "fields": ["is_featured"]
    }
  ],
  "actions": [
    {
      "key": "edit",
      "label": "Edit",
      "type": "drawer",
      "drawerKey": "product_edit"
    },
    {
      "key": "delete",
      "label": "Delete",
      "type": "delete",
      "confirmMessage": "Are you sure you want to delete this product?"
    }
  ]
}

File: src/query-library/getproduct.json

{
  "key": "getproduct",
  "name": "getProduct",
  "connector": "stack9_api",
  "queryTemplate": {
    "method": "post",
    "path": "/product/search",
    "bodyParams": "{\n  \"$select\": [\"*\", \"category.name\"],\n  \"$where\": {\n    \"id\": {{id}}\n  },\n  \"$withRelated\": [\"category(notDeleted)\"]\n}",
    "queryParams": {}
  },
  "userParams": {
    "id": ""
  }
}

Step 8: Create App Navigation

File: src/apps/products.json

{
  "name": "Products",
  "key": "products",
  "description": "Product and category management",
  "nodeType": "appNode",
  "icon": "ShoppingOutlined",
  "theme": "default",
  "visibility": "PUBLIC",
  "order": 20,
  "children": [
    {
      "key": "product_list",
      "name": "Products",
      "nodeType": "link",
      "link": "/products/product-list"
    },
    {
      "key": "category_list",
      "name": "Categories",
      "nodeType": "link",
      "link": "/products/category-list"
    }
  ]
}

Step 9: Add Validation Logic (Optional)

File: src/entity-hooks/product.vat.ts

import {
  CustomFunction,
  CustomFunctionContext,
  CustomFunctionResponse,
  HookOperation,
} from '@april9/stack9-sdk';
import { DBProduct } from '../models/stack9/Product';

export class ValidateProduct extends CustomFunction {
  constructor(private context: CustomFunctionContext<DBProduct>) {
    super();
  }

  entityName = 'product';

  async exec(): Promise<CustomFunctionResponse> {
    const { entity, operation, db } = this.context;

    // Validate SKU is unique
    if (operation === HookOperation.create || entity.sku) {
      const existing = await db.entity.knex('products')
        .where({ sku: entity.sku })
        .whereNot({ id: entity.id || 0 })
        .where({ _is_deleted: false })
        .first();

      if (existing) {
        return {
          valid: false,
          errors: [{
            field: 'sku',
            message: 'SKU already exists'
          }]
        };
      }
    }

    // Validate price is greater than cost
    if (entity.price && entity.cost && entity.price < entity.cost) {
      return {
        valid: false,
        errors: [{
          field: 'price',
          message: 'Price must be greater than cost'
        }]
      };
    }

    // Auto-update status based on stock
    let status = entity.status;
    if (entity.stock_quantity !== undefined) {
      if (entity.stock_quantity === 0) {
        status = 'Out of Stock';
      } else if (status === 'Out of Stock' && entity.stock_quantity > 0) {
        status = 'Active';
      }
    }

    return {
      valid: true,
      entity: {
        ...entity,
        status
      }
    };
  }
}

Step 10: Test Your Application

# Start Stack9
npm run dev

# Navigate to:
http://localhost:3000/products/category-list
http://localhost:3000/products/product-list

Test CRUD Operations

Create:

1. Click "Create Category"
2. Fill in: Name = "Electronics", Description = "Electronic devices"
3. Save
4. Click "Create Product"
5. Fill in: SKU = "ELEC-001", Name = "Laptop", Category = "Electronics", Price = 999.99
6. Save

Read:

1. View product list - see your laptop
2. Click on SKU to view details
3. See all product information

Update:

1. Click "Edit" on product detail
2. Change price to 899.99
3. Save
4. Verify price updated

Delete:

1. Click "Delete" on product
2. Confirm deletion
3. Verify product removed from list

What You've Built

You now have a complete CRUD application with:

✅ Entities:

• Category entity with 3 fields
• Product entity with 11 fields
• Foreign key relationship (Product → Category)

✅ Screens:

• Category list with create action
• Product list with filters and export
• Product detail with sections
• Auto-generated create/edit forms

✅ APIs:

• 10 REST endpoints (5 per entity)
• Search, filter, and sort capabilities
• Relationship loading

✅ Business Logic:

• SKU uniqueness validation
• Price/cost validation
• Auto-status updates based on stock
• Soft deletes

Next Steps

Enhance your application with:

1. Add Automation - Notify when stock is low

   • See: Building Workflows

2. Add Search - Full-text search across products

   • See: Implementing Search

3. Add Reports - Sales and inventory reports

   • See: Creating Reports

4. Add Images - Product photo uploads

   • Configure attachments in entity head

5. Add Pricing Tiers - Volume discounts

   • Create price_tier entity with Grid relationship

Troubleshooting

Entity Not Appearing

Problem: Created entity but not seeing it in UI

Solution:

1. Restart Stack9: npm run dev
2. Check entity file is in src/entities/custom/
3. Check JSON is valid
4. Check logs for errors

Query Not Working

Problem: List screen shows "No data"

Solution:

1. Check query exists in src/query-library/
2. Verify connector is set to stack9_api
3. Test API directly: POST /api/product/search
4. Check browser console for errors

Relationship Not Loading

Problem: Category name not showing in product list

Solution:

1. Add to $withRelated: ["category(notDeleted)"]
2. Add to $select: ["category.name"]
3. Use dot notation in column: {{category.name}}

Complete Code

All code from this guide is available at:

• GitHub: stack9-examples/product-crud
• Download: product-crud.zip

Summary

In 45 minutes, you've built a production-ready CRUD application with:

• Database schema
• REST APIs
• Admin UI
• Business logic
• Validation

No backend code written! Everything is configuration-driven, type-safe, and maintainable.