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",
    "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",
    "allowComments": true,
    "allowTasks": 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": {
        "precision": 2
      }
    },
    {
      "label": "Cost",
      "key": "cost",
      "type": "NumericField",
      "placeholder": "0.00",
      "description": "Product cost (for margin calculation)",
      "validateRules": {
        "required": false,
        "min": 0
      },
      "typeOptions": {
        "precision": 2
      }
    },
    {
      "label": "Stock Quantity",
      "key": "stock_quantity",
      "type": "NumericField",
      "placeholder": "0",
      "description": "Current stock level",
      "defaultValue": 0,
      "validateRules": {
        "required": true,
        "min": 0
      },
      "typeOptions": {
        "precision": 0
      }
    },
    {
      "label": "Reorder Level",
      "key": "reorder_level",
      "type": "NumericField",
      "placeholder": "10",
      "description": "Minimum stock level before reordering",
      "defaultValue": 10,
      "typeOptions": {
        "precision": 0
      }
    },
    {
      "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

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": "EnumTags",
      "options": {
        "enumColor": {
          "true": "green",
          "false": "red"
        }
      }
    },
    {
      "field": "_created_at",
      "label": "Created",
      "value": "{{_created_at}}",
      "renderAs": "Date",
      "options": {
        "format": "DD MMM YYYY"
      }
    }
  ],
  "actions": [
    {
      "key": "create",
      "label": "Create Category",
      "type": "openDrawer",
      "drawerKey": "category_create_screen"
    }
  ]
}

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": "Decimal",
      "options": {
        "prefix": "$",
        "afterDecimalPoint": 2
      }
    },
    {
      "field": "stock_quantity",
      "label": "Stock",
      "value": "{{stock_quantity}}",
      "renderAs": "Decimal",
      "options": {
        "afterDecimalPoint": 0
      }
    },
    {
      "field": "status",
      "label": "Status",
      "value": "{{status}}",
      "renderAs": "EnumTags",
      "options": {
        "enumColor": {
          "Active": "green",
          "Discontinued": "red",
          "Out of Stock": "orange"
        }
      }
    },
    {
      "field": "is_featured",
      "label": "Featured",
      "value": "{{is_featured}}",
      "renderAs": "EnumTags",
      "options": {
        "enumColor": {
          "true": "blue",
          "false": "gray"
        }
      }
    }
  ],
  "actions": [
    {
      "key": "create",
      "label": "Create Product",
      "type": "openDrawer",
      "drawerKey": "product_create_screen"
    },
    {
      "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

Query library entries define how data is fetched and filtered. For CRUD applications, you'll typically need queries for listing records with filters and fetching individual records.

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  \"$sort\": {\n    \"name\": \"asc\"\n  }  ]\n}",
    "queryParams": {
      "page": "{{page}}",
      "limit": "{{limit}}"
    }
  },
  "userParams": {
    "page": "0",
    "limit": "10"
  }
}

Product List Query with Filters

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  \"$sort\": {\n    \"name\": \"asc\"\n  }\n}",
    "queryParams": {
      "page": "{{page}}",
      "limit": "{{limit}}"
    }
  },
  "filters": [
    {
      "name": "Category",
      "key": "category",
      "typeQueryFilter": "array",
      "field": "category_id",
      "typeFilter": "MultiDropDown",
      "useSubquery": false,
      "sequence": 1,
      "dataSource": {
        "type": "query",
        "labelProp": "name",
        "valueProp": "id",
        "query": {
          "name": "getCategoryList",
          "queryKey": "getcategorylist"
        }
      }
    },
    {
      "name": "Status",
      "key": "status",
      "typeQueryFilter": "array",
      "field": "status",
      "typeFilter": "MultiDropDown",
      "useSubquery": false,
      "sequence": 2,
      "dataSource": {
        "type": "static",
        "options": [
          { "label": "Active", "value": "Active" },
          { "label": "Discontinued", "value": "Discontinued" },
          { "label": "Out of Stock", "value": "Out of Stock" }
        ]
      }
    },
    {
      "name": "Price Range",
      "key": "price_range",
      "typeQueryFilter": "between",
      "field": "price",
      "typeFilter": "NumericRangeValue",
      "useSubquery": false,
      "sequence": 3
    },
    {
      "name": "Featured Only",
      "key": "featured",
      "typeQueryFilter": "compare",
      "field": "is_featured",
      "typeFilter": "BooleanValue",
      "useSubquery": false,
      "sequence": 4
    }
  ],
  "querySearchFields": ["name", "sku", "description"],
  "userParams": {
    "page": "0",
    "limit": "20"
  }
}

Key Points About Filters:

filters array: Defines the filter UI components that appear in the list view
typeQueryFilter: Specifies how the filter operates (compare, between, array)
typeFilter: Determines the UI component (MultiDropDown, NumericRangeValue, BooleanValue, etc.)
dataSource: Can be static options or fetched from another query
querySearchFields: Enables text search across multiple fields

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": "openDrawer",
      "drawerKey": "product_edit_screen"
    },
    {
      "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": ""
  }
}

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 '@april9au/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:

Click "Create Category"
Fill in: Name = "Electronics", Description = "Electronic devices"
Save
Click "Create Product"
Fill in: SKU = "ELEC-001", Name = "Laptop", Category = "Electronics", Price = 999.99
Save

Read:

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

Update:

Click "Edit" on product detail
Change price to 899.99
Save
Verify price updated

Delete:

Click "Delete" on product
Confirm deletion
Verify product removed from list

Working with Filters

Filters enhance the user experience by allowing data refinement. Here's how filters work in your CRUD application:

Filter Types Available

Your product list now supports:

Category Filter - Multi-select dropdown pulling from categories
Status Filter - Multi-select with static options
Price Range - Min/max numeric inputs
Featured Toggle - Boolean yes/no filter
Text Search - Search across name, SKU, and description

How Filters Appear in the UI

When users access the product list, they'll see:

Search Box at the top (from querySearchFields)
Filter Panel on the left with all defined filters
Applied Filters shown as badges
Result Count updating as filters are applied

Adding More Filters

To add a stock level filter, update getproductlist.json:

{
  "name": "Stock Level",
  "key": "stock_level",
  "typeQueryFilter": "compare",
  "field": "stock_quantity",
  "typeFilter": "SingleDropDown",
  "useSubquery": false,
  "sequence": 5,
  "dataSource": {
    "type": "static",
    "options": [
      { "label": "Out of Stock (0)", "value": "0" },
      { "label": "Low Stock (1-10)", "value": "1-10" },
      { "label": "In Stock (10+)", "value": "10+" }
    ]
  }
}

Filter Best Practices

Group Related Filters - Use filterSections to organize
Set Sensible Defaults - Use userParams for initial values
Index Filter Fields - Add database indexes for performance
Limit Options - Don't load thousands of dropdown options

For comprehensive filter documentation, see Query Filters and Search.

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, search, and export
Product detail with sections
Auto-generated create/edit forms

✅ Query Features:

Advanced filtering with multiple filter types
Text search across fields
Pagination support
Dynamic filter data sources

✅ 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:

Add Automation - Notify when stock is low
- See: Building Workflows
Add Search - Full-text search across products
- See: Implementing Search
Add Reports - Sales and inventory reports
- See: Creating Reports
Add Images - Product photo uploads
- Configure attachments in entity head
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:

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

Query Not Working

Problem: List screen shows "No data"

Solution:

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

Relationship Not Loading

Problem: Category name not showing in product list

Solution:

Add to $withRelated: ["category(notDeleted)"]
Add to $select: ["category.name"]
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.

What You'll Build​

Prerequisites​

Step 1: Create the Category Entity​

Step 2: Create the Product Entity​

Step 3: Run Stack9 to Generate Resources​

Step 4: Create Category List Screen​

Step 5: Create Product List Screen​

Step 6: Create Query Library Entries​

Category List Query​

Product List Query with Filters​

Step 7: Create Detail Screens​

Step 8: Create App Navigation​

Step 9: Add Validation Logic (Optional)​

Step 10: Test Your Application​

Test CRUD Operations​

Working with Filters​

Filter Types Available​

How Filters Appear in the UI​

Adding More Filters​

Filter Best Practices​

What You've Built​

Next Steps​

Troubleshooting​

Entity Not Appearing​

Query Not Working​

Relationship Not Loading​

Complete Code​

Summary​

What You'll Build

Prerequisites

Step 1: Create the Category Entity

Step 2: Create the Product Entity

Step 3: Run Stack9 to Generate Resources

Step 4: Create Category List Screen

Step 5: Create Product List Screen

Step 6: Create Query Library Entries

Category List Query

Product List Query with Filters

Step 7: Create Detail Screens

Step 8: Create App Navigation

Step 9: Add Validation Logic (Optional)

Step 10: Test Your Application

Test CRUD Operations

Working with Filters

Filter Types Available

How Filters Appear in the UI

Adding More Filters

Filter Best Practices

What You've Built

Next Steps

Troubleshooting

Entity Not Appearing

Query Not Working

Relationship Not Loading

Complete Code

Summary