Custom Functions

Overview

Custom functions enable metadata-driven field initialization and transformation without writing application code. They are defined in TB_VAR metadata and executed automatically by CoreWrite during INSERT and UPDATE operations.

Available Functions:

uuid - Generate UUID v4
md5 - Generate MD5 hash
counter - Sequential formatted identifiers
slugify - URL-safe slugs from text
sequence - Auto-increment values
timestamp - Current timestamp
concat - Concatenate field values
default - Default value if empty

Benefits:

✅ No application code required
✅ Consistent behavior across all clients
✅ Metadata-driven configuration
✅ Automatic execution by CoreWrite
✅ Business logic centralization

Function Configuration

TB_VAR Metadata Structure

SELECT
  VAR_ID,           -- Variable ID
  DIM_ID,           -- Dimension ID
  VAR,              -- Field name (e.g., XPRD03)
  VAR_FUNCTION,     -- Function name (e.g., 'uuid', 'counter')
  VAR_PARAMS        -- Function parameters (JSON)
FROM TB_VAR
WHERE DIM_ID = 5  -- PRD dimension
AND VAR_FUNCTION IS NOT NULL;

Example configuration:

VAR_ID	DIM_ID	VAR	VAR_FUNCTION	VAR_PARAMS
101	5	PRD_ID	uuid	null
102	5	XPRD03	counter	`{"prefix": "PRD", "length": 8, "start": 1000}`
103	5	XPRD04	slugify	`{"source": "XPRD01"}`
104	5	XPRD07	md5	`{"source": "XPRD03"}`

Pre-Insert Functions

UUID Function

Generate unique identifiers:

{
  "VAR": "PRD_ID",
  "VAR_FUNCTION": "uuid",
  "VAR_PARAMS": null
}

Example:

# Create product without PRD_ID
curl -X POST https://api.q01.io/api/v4/core/products \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "XPRD01": "Widget Pro",
    "XPRD02": 29.99
  }'

# Response - PRD_ID auto-generated
{
  "PRD_ID": "550e8400-e29b-41d4-a716-446655440000",
  "XPRD01": "Widget Pro",
  "XPRD02": 29.99
}

Implementation:

class PreInsertFunctions {
    public function uuid(): string {
        // Generate UUID v4
        return sprintf(
            '%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
            mt_rand(0, 0xffff),
            mt_rand(0, 0xffff),
            mt_rand(0, 0xffff),
            mt_rand(0, 0x0fff) | 0x4000,
            mt_rand(0, 0x3fff) | 0x8000,
            mt_rand(0, 0xffff),
            mt_rand(0, 0xffff),
            mt_rand(0, 0xffff)
        );
    }
}

Counter Function

Generate sequential formatted identifiers:

{
  "VAR": "XPRD03",
  "VAR_FUNCTION": "counter",
  "VAR_PARAMS": {
    "prefix": "PRD",
    "suffix": "",
    "length": 8,
    "start": 1000,
    "step": 1,
    "pad_char": "0"
  }
}

Example output:

PRD00001000
PRD00001001
PRD00001002

TB_COUNTER table:

CREATE TABLE TB_COUNTER (
  COUNTER_ID INT PRIMARY KEY AUTO_INCREMENT,
  COUNTER_NAME VARCHAR(100) NOT NULL UNIQUE,
  CURRENT_VALUE INT NOT NULL DEFAULT 0,
  PREFIX VARCHAR(20),
  SUFFIX VARCHAR(20),
  LENGTH INT DEFAULT 8,
  PAD_CHAR CHAR(1) DEFAULT '0',
  UPDATED_AT TIMESTAMP DEFAULT NOW() ON UPDATE NOW(),
  INDEX idx_counter_name (COUNTER_NAME)
);

Usage:

# Create product - XPRD03 auto-generated
curl -X POST https://api.q01.io/api/v4/core/products \
  -H "Authorization: Bearer $TOKEN" \
  -d '{
    "XPRD01": "Widget",
    "XPRD02": 19.99
  }'

# Response
{
  "PRD_ID": 123,
  "XPRD01": "Widget",
  "XPRD02": 19.99,
  "XPRD03": "PRD00001000"  # Auto-generated
}

Implementation:

class PreInsertFunctions {
    public function counter(array $params, array $record): string {
        $counterName = $params['counter_name'] ?? $params['prefix'];

        // Atomic increment
        $this->db->beginTransaction();

        $counter = $this->db->query(
            "SELECT * FROM TB_COUNTER WHERE COUNTER_NAME = ? FOR UPDATE",
            [$counterName]
        )->fetch();

        if (!$counter) {
            // Create counter
            $this->db->query(
                "INSERT INTO TB_COUNTER (COUNTER_NAME, CURRENT_VALUE, PREFIX, LENGTH, PAD_CHAR)
                 VALUES (?, ?, ?, ?, ?)",
                [$counterName, $params['start'], $params['prefix'], $params['length'], $params['pad_char']]
            );
            $value = $params['start'];
        } else {
            // Increment counter
            $value = $counter['CURRENT_VALUE'] + ($params['step'] ?? 1);
            $this->db->query(
                "UPDATE TB_COUNTER SET CURRENT_VALUE = ? WHERE COUNTER_NAME = ?",
                [$value, $counterName]
            );
        }

        $this->db->commit();

        // Format value
        $formatted = str_pad(
            $value,
            $params['length'],
            $params['pad_char'] ?? '0',
            STR_PAD_LEFT
        );

        return ($params['prefix'] ?? '') . $formatted . ($params['suffix'] ?? '');
    }
}

Slugify Function

Generate URL-safe slugs:

{
  "VAR": "XPRD04",
  "VAR_FUNCTION": "slugify",
  "VAR_PARAMS": {
    "source": "XPRD01",
    "separator": "-",
    "lowercase": true
  }
}

Example:

# Input
{
  "XPRD01": "Widget Pro 2024!"
}

# Output
{
  "XPRD01": "Widget Pro 2024!",
  "XPRD04": "widget-pro-2024"  # Auto-generated slug
}

Implementation:

class PreInsertFunctions {
    public function slugify(array $params, array $record): string {
        $source = $record[$params['source']] ?? '';
        $separator = $params['separator'] ?? '-';

        // Convert to ASCII
        $slug = iconv('UTF-8', 'ASCII//TRANSLIT', $source);

        // Remove special characters
        $slug = preg_replace('/[^a-zA-Z0-9\s-]/', '', $slug);

        // Replace spaces with separator
        $slug = preg_replace('/\s+/', $separator, $slug);

        // Remove duplicate separators
        $slug = preg_replace('/' . preg_quote($separator) . '+/', $separator, $slug);

        // Trim separators from ends
        $slug = trim($slug, $separator);

        // Lowercase if specified
        if ($params['lowercase'] ?? true) {
            $slug = strtolower($slug);
        }

        return $slug;
    }
}

MD5 Function

Generate MD5 hashes:

{
  "VAR": "XPRD07",
  "VAR_FUNCTION": "md5",
  "VAR_PARAMS": {
    "source": "XPRD03"
  }
}

Example:

# Input
{
  "XPRD03": "PRD00001000"
}

# Output
{
  "XPRD03": "PRD00001000",
  "XPRD07": "5d41402abc4b2a76b9719d911017c592"  # MD5 hash
}

Implementation:

class PreInsertFunctions {
    public function md5(array $params, array $record): string {
        $source = $record[$params['source']] ?? '';
        return md5($source);
    }
}

Sequence Function

Auto-increment values:

{
  "VAR": "XPRD08",
  "VAR_FUNCTION": "sequence",
  "VAR_PARAMS": {
    "sequence_name": "PRD_SEQ",
    "start": 1,
    "step": 1
  }
}

Example:

# Multiple inserts
POST /api/v4/core/products  # XPRD08 = 1
POST /api/v4/core/products  # XPRD08 = 2
POST /api/v4/core/products  # XPRD08 = 3

Timestamp Function

Current timestamp:

{
  "VAR": "XPRD09",
  "VAR_FUNCTION": "timestamp",
  "VAR_PARAMS": {
    "format": "Y-m-d H:i:s"
  }
}

Example:

# Output
{
  "XPRD09": "2025-12-19 10:30:00"
}

Concat Function

Concatenate fields:

{
  "VAR": "XPRD10",
  "VAR_FUNCTION": "concat",
  "VAR_PARAMS": {
    "fields": ["XPRD01", "XPRD03"],
    "separator": " - "
  }
}

Example:

# Input
{
  "XPRD01": "Widget Pro",
  "XPRD03": "PRD00001000"
}

# Output
{
  "XPRD10": "Widget Pro - PRD00001000"
}

Default Function

Default value if empty:

{
  "VAR": "XPRD06",
  "VAR_FUNCTION": "default",
  "VAR_PARAMS": {
    "value": "Y"
  }
}

Example:

# Input (XPRD06 not provided)
{
  "XPRD01": "Widget"
}

# Output
{
  "XPRD01": "Widget",
  "XPRD06": "Y"  # Default value
}

Post-Update Hooks

On Update Functions

Execute functions on UPDATE:

{
  "VAR": "UPDATED_AT",
  "VAR_FUNCTION": "timestamp",
  "VAR_PARAMS": {
    "on_event": "UPDATE"
  }
}

Example:

# Update product
PUT /api/v4/core/products/123

# UPDATED_AT automatically set to current timestamp

Conditional Functions

Execute only if condition met:

{
  "VAR": "XPRD11",
  "VAR_FUNCTION": "counter",
  "VAR_PARAMS": {
    "prefix": "REV",
    "length": 4,
    "condition": {
      "field": "TREC",
      "operator": "=",
      "value": "M"
    }
  }
}

Example:

# First update - TREC changes from N to M
PUT /api/v4/core/products/123
# XPRD11 = REV0001 (counter incremented)

# Second update - TREC already M
PUT /api/v4/core/products/123
# XPRD11 unchanged (condition not met)

Function Execution Order

Pre-Insert Phase

Validate request (token, grants, required fields)
Apply context filters (source, peso, ambiente)
Execute pre-insert functions (in TB_VAR order)
Validate COD_ON_OFF flags
Insert into TB_ANAG_{DIM}00
Insert OUTBOX event
Commit transaction

Function Dependencies

// XPRD04 depends on XPRD01
{
  "VAR": "XPRD04",
  "VAR_FUNCTION": "slugify",
  "VAR_PARAMS": {
    "source": "XPRD01"
  },
  "VAR_ORDER": 2  // Execute after XPRD01 functions
}

// XPRD07 depends on XPRD03
{
  "VAR": "XPRD07",
  "VAR_FUNCTION": "md5",
  "VAR_PARAMS": {
    "source": "XPRD03"
  },
  "VAR_ORDER": 3  // Execute after XPRD03 functions
}

Custom Function Development

Create New Function

1. Define function in PreInsertFunctions class:

class PreInsertFunctions {
    public function customFunction(array $params, array $record): mixed {
        // Extract parameters
        $param1 = $params['param1'] ?? 'default';
        $param2 = $params['param2'] ?? null;

        // Business logic
        $result = $this->processLogic($param1, $param2, $record);

        return $result;
    }

    private function processLogic($param1, $param2, $record) {
        // Implementation
        return 'result';
    }
}

2. Register in function registry:

class FunctionRegistry {
    private $functions = [
        'uuid' => 'uuid',
        'md5' => 'md5',
        'counter' => 'counter',
        'slugify' => 'slugify',
        'customFunction' => 'customFunction',  // New function
    ];

    public function execute($functionName, $params, $record) {
        if (!isset($this->functions[$functionName])) {
            throw new \Exception("Unknown function: $functionName");
        }

        $method = $this->functions[$functionName];
        return $this->functions->$method($params, $record);
    }
}

3. Configure in TB_VAR:

INSERT INTO TB_VAR (DIM_ID, VAR, VAR_FUNCTION, VAR_PARAMS)
VALUES (5, 'XPRD12', 'customFunction', '{"param1": "value1", "param2": "value2"}');

Best Practices

✅ DO:

Use appropriate functions:

// ✅ Good - counter for codes
{
  "VAR": "XPRD03",
  "VAR_FUNCTION": "counter",
  "VAR_PARAMS": {"prefix": "PRD", "length": 8}
}

// ✅ Good - slugify for URLs
{
  "VAR": "XPRD04",
  "VAR_FUNCTION": "slugify",
  "VAR_PARAMS": {"source": "XPRD01"}
}

Define execution order:

// ✅ Good - explicit order
{
  "VAR": "XPRD04",
  "VAR_FUNCTION": "slugify",
  "VAR_PARAMS": {"source": "XPRD01"},
  "VAR_ORDER": 2
}

Handle errors gracefully:

// ✅ Good - error handling
public function customFunction($params, $record) {
    try {
        return $this->process($params, $record);
    } catch (\Exception $e) {
        logger->error("Function error: " . $e->getMessage());
        return null;  // or default value
    }
}

❌ DON'T:

Don't depend on external services:

// ❌ Bad - external API call
public function fetchFromAPI($params, $record) {
    return file_get_contents('https://external-api.com/data');
}

Don't execute expensive operations:

// ❌ Bad - complex computation
public function slowFunction($params, $record) {
    sleep(5);  // Blocks entire insert
    return 'result';
}

Don't modify other fields:

// ❌ Bad - side effects
public function badFunction($params, $record) {
    $record['OTHER_FIELD'] = 'value';  // Don't modify other fields
    return 'result';
}

Summary

✅ Custom functions enable metadata-driven field initialization
✅ No application code required for common operations
✅ Available functions: uuid, md5, counter, slugify, sequence, timestamp, concat, default
✅ Functions configured in TB_VAR metadata
✅ Executed automatically by CoreWrite during INSERT/UPDATE
✅ Support for dependencies and execution order
✅ Extensible - create custom functions as needed

Key Takeaways:

Functions centralize business logic in metadata
Counter function generates formatted sequential IDs
Slugify creates URL-safe identifiers
UUID generates unique identifiers
Functions can depend on other field values
Execution order controlled by VAR_ORDER
Functions should be fast and side-effect free
Custom functions extend built-in capabilities

Counter Management - TB_COUNTER implementation
TB_VAR Metadata - Variable configuration
Write Operations - POST/PUT/PATCH
Advanced Topics - Overview

Custom Functions

Overview​

Function Configuration​

TB_VAR Metadata Structure​

Pre-Insert Functions​

UUID Function​

Counter Function​

Slugify Function​

MD5 Function​

Sequence Function​

Timestamp Function​

Concat Function​

Default Function​

Post-Update Hooks​

On Update Functions​

Conditional Functions​

Function Execution Order​

Pre-Insert Phase​

Function Dependencies​

Custom Function Development​

Create New Function​

Best Practices​

✅ DO:​

❌ DON'T:​

Summary​

Related Concepts​

Overview

Function Configuration

TB_VAR Metadata Structure

Pre-Insert Functions

UUID Function

Counter Function

Slugify Function

MD5 Function

Sequence Function

Timestamp Function

Concat Function

Default Function

Post-Update Hooks

On Update Functions

Conditional Functions

Function Execution Order

Pre-Insert Phase

Function Dependencies

Custom Function Development

Create New Function

Best Practices

✅ DO:

❌ DON'T:

Summary

Related Concepts