Virtual/Computed Fields Guide

Virtual fields (also called computed or generated fields) are database columns whose values are automatically calculated from other columns using SQL expressions. Baasix uses PostgreSQL's GENERATED ALWAYS AS feature to create these fields at the database level.

Overview
Basic Syntax
SQL Expression Patterns
Complete Examples
Querying Virtual Fields
Best Practices
Common Mistakes
Performance Tips

Overview

How It Works

When you define a virtual field with a SQL expression, Baasix:

Creates a PostgreSQL GENERATED ALWAYS AS column
Stores the computed value (STORED mode for better read performance)
Automatically updates the value when source columns change
Makes the field queryable, sortable, and indexable

Benefits

Feature	Description
Database-level computation	Values computed by PostgreSQL, not your application
Automatic updates	Changes to source columns automatically update computed values
Queryable	Filter and sort by computed fields just like regular fields
Indexable	Create database indexes on computed columns
Type-safe	No eval() or code execution risks
Consistent	Always in sync with source data (transactional)

Basic Syntax

Field Definition

Define virtual fields in your schema using the VIRTUAL type and calculated property:

{
  "fieldName": {
    "type": "VIRTUAL",
    "calculated": "SQL_EXPRESSION_HERE",
    "description": "Optional description"
  }
}

Simple Example

{
  "collectionName": "users",
  "schema": {
    "fields": {
      "firstName": { "type": "String", "allowNull": false },
      "lastName": { "type": "String", "allowNull": true },
      "fullName": {
        "type": "VIRTUAL",
        "calculated": "COALESCE(first_name, '') || ' ' || COALESCE(last_name, '')",
        "description": "Full name computed from first and last name"
      }
    }
  }
}

Important: Column Names

Use snake_case column names in SQL expressions (as they exist in the database):

// ✅ CORRECT: Use database column names
"calculated": "first_name || ' ' || last_name"

// ❌ WRONG: Don't use camelCase
"calculated": "firstName || ' ' || lastName"  // Won't work!

SQL Expression Patterns

String Concatenation

{
  "fullName": {
    "type": "VIRTUAL",
    "calculated": "COALESCE(first_name, '') || ' ' || COALESCE(last_name, '')"
  },
  
  "fullAddress": {
    "type": "VIRTUAL",
    "calculated": "street || ', ' || city || ' ' || zip_code"
  },
  
  "displayName": {
    "type": "VIRTUAL",
    "calculated": "'Product: ' || name || ' (Qty: ' || quantity || ')'"
  }
}

Math Operations

{
  "subtotal": {
    "type": "VIRTUAL",
    "calculated": "price * quantity"
  },
  
  "discountedPrice": {
    "type": "VIRTUAL",
    "calculated": "price * (1 - discount / 100.0)"
  },
  
  "total": {
    "type": "VIRTUAL",
    "calculated": "(price * quantity) * (1 - discount / 100.0)"
  },
  
  "taxAmount": {
    "type": "VIRTUAL",
    "calculated": "(price * quantity) * (tax_rate / 100.0)"
  },
  
  "grandTotal": {
    "type": "VIRTUAL",
    "calculated": "(price * quantity) * (1 + tax_rate / 100.0)"
  }
}

Conditional Logic (CASE)

{
  "status": {
    "type": "VIRTUAL",
    "calculated": "CASE WHEN quantity > 10 THEN 'In Stock' WHEN quantity > 0 THEN 'Low Stock' ELSE 'Out of Stock' END"
  },
  
  "ageGroup": {
    "type": "VIRTUAL",
    "calculated": "CASE WHEN age < 25 THEN 'Junior' WHEN age < 40 THEN 'Mid-Level' WHEN age < 60 THEN 'Senior' ELSE 'Veteran' END"
  },
  
  "salaryTier": {
    "type": "VIRTUAL",
    "calculated": "CASE WHEN salary < 30000 THEN 'Entry Level' WHEN salary < 60000 THEN 'Mid Level' WHEN salary < 100000 THEN 'Senior Level' ELSE 'Executive Level' END"
  },
  
  "paymentStatus": {
    "type": "VIRTUAL",
    "calculated": "CASE WHEN paid_amount >= amount THEN 'Paid' WHEN paid_amount > 0 THEN 'Partial' ELSE 'Unpaid' END"
  }
}

Date/Time Calculations

{
  "ageInYears": {
    "type": "VIRTUAL",
    "calculated": "EXTRACT(YEAR FROM AGE(birth_date))"
  },
  
  "membershipDays": {
    "type": "VIRTUAL",
    "calculated": "EXTRACT(DAY FROM (CURRENT_DATE - join_date))"
  },
  
  "birthYear": {
    "type": "VIRTUAL",
    "calculated": "EXTRACT(YEAR FROM birth_date)"
  },
  
  "daysOverdue": {
    "type": "VIRTUAL",
    "calculated": "EXTRACT(DAY FROM (CURRENT_DATE - due_date))"
  },
  
  "isOverdue": {
    "type": "VIRTUAL",
    "calculated": "due_date < CURRENT_DATE AND paid_amount < amount"
  },
  
  "formattedDate": {
    "type": "VIRTUAL",
    "calculated": "TO_CHAR(created_at, 'YYYY-MM-DD')"
  },
  
  "monthName": {
    "type": "VIRTUAL",
    "calculated": "TO_CHAR(created_at, 'Month')"
  }
}

String Functions

{
  "emailDomain": {
    "type": "VIRTUAL",
    "calculated": "SUBSTRING(email FROM POSITION('@' IN email) + 1)"
  },
  
  "emailUsername": {
    "type": "VIRTUAL",
    "calculated": "SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1)"
  },
  
  "initials": {
    "type": "VIRTUAL",
    "calculated": "UPPER(SUBSTRING(first_name, 1, 1) || SUBSTRING(last_name, 1, 1))"
  },
  
  "displayNameTitleCase": {
    "type": "VIRTUAL",
    "calculated": "INITCAP(first_name || ' ' || last_name)"
  },
  
  "uppercaseName": {
    "type": "VIRTUAL",
    "calculated": "UPPER(name)"
  },
  
  "lowercaseEmail": {
    "type": "VIRTUAL",
    "calculated": "LOWER(email)"
  }
}

JSON Field Access

{
  "preferredLanguage": {
    "type": "VIRTUAL",
    "calculated": "settings->>'language'"
  },
  
  "emailNotifications": {
    "type": "VIRTUAL",
    "calculated": "(settings->>'emailNotifications')::boolean"
  },
  
  "theme": {
    "type": "VIRTUAL",
    "calculated": "COALESCE(settings->>'theme', 'light')"
  },
  
  "nestedValue": {
    "type": "VIRTUAL",
    "calculated": "data->'user'->>'name'"
  }
}

NULL Handling

{
  "safeFullName": {
    "type": "VIRTUAL",
    "calculated": "COALESCE(first_name, 'Unknown') || ' ' || COALESCE(last_name, '')"
  },
  
  "safeDiscount": {
    "type": "VIRTUAL",
    "calculated": "COALESCE(discount, 0)"
  },
  
  "safeTheme": {
    "type": "VIRTUAL",
    "calculated": "COALESCE(settings->>'theme', 'light')"
  }
}

Numeric Functions

{
  "absoluteBalance": {
    "type": "VIRTUAL",
    "calculated": "ABS(balance)"
  },
  
  "roundedPrice": {
    "type": "VIRTUAL",
    "calculated": "ROUND(price * 1.08, 2)"
  },
  
  "maxPrice": {
    "type": "VIRTUAL",
    "calculated": "GREATEST(price1, price2, price3)"
  },
  
  "minPrice": {
    "type": "VIRTUAL",
    "calculated": "LEAST(price1, price2, price3)"
  }
}

Complete Examples

E-commerce Product Schema

{
  "collectionName": "products",
  "schema": {
    "fields": {
      "id": {
        "type": "UUID",
        "primaryKey": true,
        "defaultValue": { "type": "UUIDV4" }
      },
      "name": {
        "type": "String",
        "allowNull": false
      },
      "price": {
        "type": "Decimal",
        "allowNull": false
      },
      "discount": {
        "type": "Integer",
        "allowNull": true,
        "defaultValue": 0,
        "description": "Discount percentage (0-100)"
      },
      "quantity": {
        "type": "Integer",
        "allowNull": false,
        "defaultValue": 0
      },
      "taxRate": {
        "type": "Decimal",
        "allowNull": false,
        "defaultValue": 0
      },
      
      "discountedPrice": {
        "type": "VIRTUAL",
        "calculated": "price * (1 - discount / 100.0)",
        "description": "Price after discount"
      },
      
      "subtotal": {
        "type": "VIRTUAL",
        "calculated": "quantity * price * (1 - discount / 100.0)",
        "description": "Subtotal (quantity × discounted price)"
      },
      
      "taxAmount": {
        "type": "VIRTUAL",
        "calculated": "(quantity * price * (1 - discount / 100.0)) * (tax_rate / 100.0)",
        "description": "Tax amount"
      },
      
      "total": {
        "type": "VIRTUAL",
        "calculated": "(quantity * price * (1 - discount / 100.0)) * (1 + tax_rate / 100.0)",
        "description": "Total including tax"
      },
      
      "displayName": {
        "type": "VIRTUAL",
        "calculated": "'Product: ' || name || ' (Qty: ' || quantity || ')'",
        "description": "Formatted display name"
      },
      
      "stockStatus": {
        "type": "VIRTUAL",
        "calculated": "CASE WHEN quantity > 10 THEN 'In Stock' WHEN quantity > 0 THEN 'Low Stock' ELSE 'Out of Stock' END",
        "description": "Stock status"
      }
    }
  }
}

Invoice Schema

{
  "collectionName": "invoices",
  "schema": {
    "fields": {
      "id": {
        "type": "UUID",
        "primaryKey": true,
        "defaultValue": { "type": "UUIDV4" }
      },
      "amount": { "type": "Decimal", "allowNull": false },
      "paidAmount": { "type": "Decimal", "defaultValue": 0 },
      "dueDate": { "type": "Date", "allowNull": false },
      
      "balance": {
        "type": "VIRTUAL",
        "calculated": "amount - paid_amount",
        "description": "Remaining balance"
      },
      
      "paymentStatus": {
        "type": "VIRTUAL",
        "calculated": "CASE WHEN paid_amount >= amount THEN 'Paid' WHEN paid_amount > 0 THEN 'Partial' ELSE 'Unpaid' END",
        "description": "Payment status"
      },
      
      "isOverdue": {
        "type": "VIRTUAL",
        "calculated": "due_date < CURRENT_DATE AND paid_amount < amount",
        "description": "Whether invoice is overdue"
      },
      
      "daysOverdue": {
        "type": "VIRTUAL",
        "calculated": "EXTRACT(DAY FROM (CURRENT_DATE - due_date))",
        "description": "Days past due date (negative if not yet due)"
      }
    }
  }
}

Member/User Schema

{
  "collectionName": "members",
  "schema": {
    "fields": {
      "id": {
        "type": "UUID",
        "primaryKey": true,
        "defaultValue": { "type": "UUIDV4" }
      },
      "firstName": { "type": "String", "allowNull": false },
      "lastName": { "type": "String", "allowNull": true },
      "email": { "type": "String", "allowNull": false },
      "birthDate": { "type": "Date", "allowNull": true },
      "joinDate": { "type": "DateTime", "defaultValue": { "type": "NOW" } },
      
      "fullName": {
        "type": "VIRTUAL",
        "calculated": "COALESCE(first_name, '') || ' ' || COALESCE(last_name, '')",
        "description": "Full name"
      },
      
      "initials": {
        "type": "VIRTUAL",
        "calculated": "UPPER(SUBSTRING(first_name, 1, 1) || COALESCE(SUBSTRING(last_name, 1, 1), ''))",
        "description": "User initials"
      },
      
      "emailDomain": {
        "type": "VIRTUAL",
        "calculated": "SUBSTRING(email FROM POSITION('@' IN email) + 1)",
        "description": "Email domain"
      },
      
      "ageInYears": {
        "type": "VIRTUAL",
        "calculated": "EXTRACT(YEAR FROM AGE(birth_date))",
        "description": "Age in years"
      },
      
      "membershipDays": {
        "type": "VIRTUAL",
        "calculated": "EXTRACT(DAY FROM (CURRENT_DATE - join_date::date))",
        "description": "Days since joining"
      },
      
      "ageGroup": {
        "type": "VIRTUAL",
        "calculated": "CASE WHEN EXTRACT(YEAR FROM AGE(birth_date)) < 25 THEN 'Youth' WHEN EXTRACT(YEAR FROM AGE(birth_date)) < 65 THEN 'Adult' ELSE 'Senior' END",
        "description": "Age group classification"
      }
    }
  }
}

Querying Virtual Fields

Select Virtual Fields

# Include virtual fields in response
GET /items/products?fields=name,price,discountedPrice,total,stockStatus

Filter by Virtual Fields

# Filter products where total > 100
GET /items/products?filter={"total":{"gt":100}}

# Filter invoices that are overdue
GET /items/invoices?filter={"isOverdue":{"eq":true}}

# Filter by stock status
GET /items/products?filter={"stockStatus":{"eq":"In Stock"}}

# Filter by payment status
GET /items/invoices?filter={"paymentStatus":{"in":["Unpaid","Partial"]}}

Sort by Virtual Fields

# Sort by total descending
GET /items/products?sort=-total

# Sort by age group
GET /items/members?sort=ageGroup

# Multiple sorts
GET /items/products?sort=-stockStatus,total

Aggregate Virtual Fields

# Sum of totals
GET /items/products?aggregate={"sum":"total"}

# Average discounted price
GET /items/products?aggregate={"avg":"discountedPrice"}

# Group by stock status
GET /items/products?aggregate={"count":"*"}&groupBy=stockStatus

Best Practices

1. Use COALESCE for String Concatenation

Always handle NULL values when concatenating strings:

// ✅ Good: Handles NULL
"calculated": "COALESCE(first_name, '') || ' ' || COALESCE(last_name, '')"

// ❌ Bad: Returns NULL if any field is NULL
"calculated": "first_name || ' ' || last_name"

2. Use Decimal Division

Avoid integer division issues:

// ✅ Good: Decimal division
"calculated": "total / count::decimal"
"calculated": "discount / 100.0"

// ❌ Bad: Integer division (5 / 2 = 2)
"calculated": "total / count"
"calculated": "discount / 100"

3. Break Complex Calculations

Split complex expressions into multiple virtual fields:

{
  "subtotal": {
    "type": "VIRTUAL",
    "calculated": "quantity * unit_price"
  },
  "taxAmount": {
    "type": "VIRTUAL",
    "calculated": "(quantity * unit_price) * (tax_rate / 100.0)"
  },
  "total": {
    "type": "VIRTUAL",
    "calculated": "(quantity * unit_price) * (1 + tax_rate / 100.0)"
  }
}

4. Test SQL Expressions First

Test your expressions in PostgreSQL before adding to schema:

SELECT 
  first_name,
  last_name,
  COALESCE(first_name, '') || ' ' || COALESCE(last_name, '') as full_name
FROM users
LIMIT 5;

5. Add Descriptions

Document what each virtual field computes:

{
  "stockStatus": {
    "type": "VIRTUAL",
    "calculated": "CASE WHEN quantity > 10 THEN 'In Stock' WHEN quantity > 0 THEN 'Low Stock' ELSE 'Out of Stock' END",
    "description": "Stock availability status based on quantity thresholds"
  }
}

Common Mistakes

1. Using camelCase Column Names

// ❌ WRONG
"calculated": "firstName || ' ' || lastName"

// ✅ CORRECT  
"calculated": "first_name || ' ' || last_name"

2. Missing NULL Handling

// ❌ WRONG: Returns NULL if discount is NULL
"calculated": "price * (1 - discount / 100.0)"

// ✅ CORRECT: Handles NULL discount
"calculated": "price * (1 - COALESCE(discount, 0) / 100.0)"

3. Integer Division

// ❌ WRONG: Integer division
"calculated": "completed / total * 100"

// ✅ CORRECT: Decimal division
"calculated": "(completed::decimal / total) * 100"

4. Invalid Date Arithmetic

// ❌ WRONG
"calculated": "CURRENT_DATE - 30"

// ✅ CORRECT
"calculated": "CURRENT_DATE - INTERVAL '30 days'"

Performance Tips

Keep expressions simple - Complex expressions can slow down queries
Add indexes - Index virtual fields used in WHERE or ORDER BY clauses
Stored mode - Baasix uses STORED mode by default (faster reads)
Limit virtual fields - Don't create virtual fields you don't need
Test with data - Verify performance with realistic data volumes

Creating Indexes on Virtual Fields

After creating a schema with virtual fields, you can add indexes for frequently queried computed columns:

-- Index for filtering by stock status
CREATE INDEX idx_products_stock_status ON products(stock_status);

-- Index for sorting by total
CREATE INDEX idx_products_total ON products(total);

-- Index for filtering by payment status
CREATE INDEX idx_invoices_payment_status ON invoices(payment_status);

Schema Reference Guide - Complete field type reference
Database Schema Guide - Schema creation patterns
Advanced Query Guide - Filtering and aggregation
Complete Filter Reference - All filter operators

On this page