Metadata Tables

Overview

Q01 Core APIs are metadata-driven through the MAP (Metadata Application Platform) framework. Five core metadata tables define dimensions, fields, visibility, permissions, and relationships at runtime—no code changes required for schema evolution.

MAP Framework Tables:

TB_DIM - Dimension registry (entity types)
TB_VAR - Field definitions (variables)
TB_COST - Field configuration (visibility, validation, functions)
TB_MENU - Permission hierarchy (grants)
TB_OBJECT - Dimension relationships (foreign keys)

Benefits:

✅ No code changes for schema modifications
✅ Runtime field visibility control
✅ Centralized permission management
✅ Automatic cascade configuration
✅ Multi-tenant metadata isolation

TB_DIM - Dimension Registry

Purpose

Registers all business entity types (dimensions) in the system. Each dimension corresponds to a data table TB_ANAG_{COD_DIM}00.

Schema

Field	Type	Description
COD_DIM	VARCHAR(50) PK	Dimension code (e.g., 'PRD', 'ORD', 'CUST')
DESC_DIM	VARCHAR(255)	Human-readable dimension name
PREFIX	VARCHAR(10)	Field prefix (e.g., 'PRD_', 'ORD_')
TABLE_NAME	VARCHAR(100)	Physical table name (usually `TB_ANAG_{COD_DIM}00`)
ID_FIELD	VARCHAR(50)	Primary key field name (e.g., 'PRD_ID')
DEFAULT_PESO	INT	Default peso level for new records
DEFAULT_COD_ON_OFF	VARCHAR(10)	Default field visibility (LDRNM)
CASCADE_INSERT	TEXT	Comma-separated list of dimensions to cascade on insert
CASCADE_UPDATE	TEXT	Comma-separated list of dimensions to cascade on update
CASCADE_DELETE	TEXT	Comma-separated list of dimensions to cascade on delete
SEQUENCE_NAME	VARCHAR(100)	Database sequence for auto-increment IDs
COUNTER_FORMAT	VARCHAR(100)	Format for TB_COUNTER-based IDs
ACTIVE	BOOLEAN	Dimension enabled (true) or disabled (false)
SOURCE	VARCHAR(50)	Tenant identifier
CREATED_AT	VARCHAR(14)	Creation timestamp
UPDATED_AT	VARCHAR(14)	Last update timestamp

Example Data

INSERT INTO TB_DIM (
  COD_DIM, DESC_DIM, PREFIX, TABLE_NAME, ID_FIELD,
  DEFAULT_PESO, DEFAULT_COD_ON_OFF,
  CASCADE_DELETE, ACTIVE, SOURCE
) VALUES
(
  'PRD',                          -- Dimension code
  'Products',                     -- Description
  'PRD_',                         -- Field prefix
  'TB_ANAG_PRD00',               -- Table name
  'PRD_ID',                       -- Primary key
  3,                              -- Default peso
  'LDRNM',                        -- Default visibility (all flags)
  'PRDIMG,PRDCAT',               -- Cascade delete to images and categories
  TRUE,                           -- Active
  'ecommerce'                     -- Tenant
);

Querying TB_DIM

// Get all registered dimensions for tenant
const dimensions = await coreAPI.get('/api/v4/core/DIM', {
  params: {
    filter: { SOURCE: 'ecommerce', ACTIVE: true }
  }
});

// Get specific dimension configuration
const productDim = await coreAPI.get('/api/v4/core/DIM/PRD');
console.log(productDim.data.CASCADE_DELETE); // "PRDIMG,PRDCAT"

TB_VAR - Field Definitions

Purpose

Defines individual fields (variables) for each dimension. Each entry in TB_VAR represents a column in TB_ANAG_{COD_DIM}00 or a computed/virtual field.

Schema

Field	Type	Description
COD_DIM	VARCHAR(50) FK	Dimension code (references TB_DIM)
NUM_VAR	INT	Field number (ordering)
COD_VAR	VARCHAR(50) PK	Field code (e.g., 'XPRD01', 'XPRD02')
DESC_VAR	VARCHAR(255)	Human-readable field name
DATA_TYPE	VARCHAR(50)	Data type (VARCHAR, INT, DECIMAL, DATE, BOOLEAN, TEXT)
LENGTH	INT	Maximum field length (for VARCHAR)
PRECISION	INT	Decimal precision (for DECIMAL)
SCALE	INT	Decimal scale (for DECIMAL)
DEFAULT_VALUE	TEXT	Default value if not provided
NULLABLE	BOOLEAN	Allow NULL values (true/false)
UNIQUE	BOOLEAN	Enforce uniqueness (true/false)
INDEXED	BOOLEAN	Create database index (true/false)
VIRTUAL	BOOLEAN	Computed field not stored in database
FORMULA	TEXT	SQL expression for virtual fields
VALIDATION_REGEX	VARCHAR(500)	Regular expression for validation
SOURCE	VARCHAR(50)	Tenant identifier
CREATED_AT	VARCHAR(14)	Creation timestamp
UPDATED_AT	VARCHAR(14)	Last update timestamp

Example Data

-- Product name field
INSERT INTO TB_VAR (
  COD_DIM, NUM_VAR, COD_VAR, DESC_VAR,
  DATA_TYPE, LENGTH, NULLABLE, INDEXED, SOURCE
) VALUES
(
  'PRD',           -- Dimension
  1,               -- Field order
  'XPRD01',        -- Field code
  'Product Name',  -- Description
  'VARCHAR',       -- Data type
  255,             -- Max length
  FALSE,           -- Not nullable (required)
  TRUE,            -- Indexed for search
  'ecommerce'      -- Tenant
);

-- Product price field
INSERT INTO TB_VAR (
  COD_DIM, NUM_VAR, COD_VAR, DESC_VAR,
  DATA_TYPE, PRECISION, SCALE, NULLABLE, SOURCE
) VALUES
(
  'PRD',           -- Dimension
  2,               -- Field order
  'XPRD02',        -- Field code
  'Price',         -- Description
  'DECIMAL',       -- Data type
  10,              -- Precision (10 total digits)
  2,               -- Scale (2 decimal places)
  FALSE,           -- Not nullable
  'ecommerce'      -- Tenant
);

-- Product SKU (unique identifier)
INSERT INTO TB_VAR (
  COD_DIM, NUM_VAR, COD_VAR, DESC_VAR,
  DATA_TYPE, LENGTH, NULLABLE, UNIQUE, INDEXED,
  VALIDATION_REGEX, SOURCE
) VALUES
(
  'PRD',
  3,
  'XPRD03',
  'SKU',
  'VARCHAR',
  50,
  FALSE,
  TRUE,            -- Must be unique
  TRUE,            -- Indexed for fast lookup
  '^[A-Z]{3}-[0-9]{4}-[0-9]{3}$',  -- Format: ABC-2025-001
  'ecommerce'
);

Querying TB_VAR

// Get all fields for PRD dimension
const fields = await coreAPI.get('/api/v4/core/VAR', {
  params: {
    filter: { COD_DIM: 'PRD' },
    sort: 'NUM_VAR'  // Order by field number
  }
});

// Get specific field definition
const priceField = await coreAPI.get('/api/v4/core/VAR', {
  params: {
    filter: { COD_DIM: 'PRD', COD_VAR: 'XPRD02' }
  }
});
console.log(priceField.data[0].DATA_TYPE);  // "DECIMAL"
console.log(priceField.data[0].PRECISION);  // 10
console.log(priceField.data[0].SCALE);      // 2

TB_COST - Field Configuration

Purpose

Configures field behavior including visibility (COD_ON_OFF), required validation, pre-insert functions, and cascade operations. This is where runtime field control happens.

Schema

Field	Type	Description
COD_DIM	VARCHAR(50) FK	Dimension code
NUM_COST	INT PK	Configuration number (unique per field)
COD_VAR	VARCHAR(50) FK	Field code (references TB_VAR)
COD_ON_OFF	VARCHAR(10)	Visibility flags (L/D/N/M/R)
REQUIRED	BOOLEAN	Field required (true/false)
VALUE_TEMP	TEXT	Pre-insert function (e.g., '`{uuid}`', '`{counter}`')
INSERT_CASCADE	TEXT	Related dimension to cascade on insert
UPDATE_CASCADE	TEXT	Related dimension to cascade on update
DELETE_CASCADE	TEXT	Related dimension to cascade on delete
RENDER_HINT	VARCHAR(100)	UI rendering hint (text, textarea, select, date, etc.)
GROUP_NAME	VARCHAR(100)	Logical grouping for UI (e.g., 'Basic Info', 'Pricing')
ORDER_IN_GROUP	INT	Display order within group
HELP_TEXT	TEXT	Tooltip or help message for UI
PLACEHOLDER	VARCHAR(255)	Placeholder text for input fields
MIN_VALUE	VARCHAR(50)	Minimum value (for numbers/dates)
MAX_VALUE	VARCHAR(50)	Maximum value (for numbers/dates)
SOURCE	VARCHAR(50)	Tenant identifier
CREATED_AT	VARCHAR(14)	Creation timestamp
UPDATED_AT	VARCHAR(14)	Last update timestamp

COD_ON_OFF Flags

Flag	Meaning	Context
L	List	Visible in list views (GET `/api/v4/core/{dim}`)
D	Detail	Visible in detail views (GET `/api/v4/core/{dim}/{id}`)
N	New	Can be set on creation (POST)
M	Modify	Can be updated (PUT/PATCH)
R	Search	Can be used in filters (?filter[field]=value)

Examples:

"LDRNM" - Full visibility, can create and modify
"LDR" - Visible and searchable, but read-only (can't create/modify)
"N" - Can only be set on creation (immutable after)
"M" - Can only be modified, not set on creation
"" - Hidden field (internal use only)

Example Data

-- Product name: full access
INSERT INTO TB_COST (
  COD_DIM, NUM_COST, COD_VAR, COD_ON_OFF,
  REQUIRED, RENDER_HINT, GROUP_NAME, ORDER_IN_GROUP,
  HELP_TEXT, SOURCE
) VALUES
(
  'PRD',
  1,
  'XPRD01',
  'LDRNM',           -- Visible everywhere, can create/modify
  TRUE,              -- Required field
  'text',            -- Text input
  'Basic Info',      -- UI group
  1,                 -- First field in group
  'Enter product name (max 255 characters)',
  'ecommerce'
);

-- Product SKU: auto-generated, immutable after creation
INSERT INTO TB_COST (
  COD_DIM, NUM_COST, COD_VAR, COD_ON_OFF,
  REQUIRED, VALUE_TEMP, RENDER_HINT, SOURCE
) VALUES
(
  'PRD',
  3,
  'XPRD03',
  'LDR',             -- Visible and searchable, but read-only
  FALSE,             -- Auto-generated, not required from user
  '{counter:PRD-{YYYY}-{NNN}}',  -- Auto-generate: PRD-2025-001
  'text',
  'ecommerce'
);

-- Product price: required, can modify
INSERT INTO TB_COST (
  COD_DIM, NUM_COST, COD_VAR, COD_ON_OFF,
  REQUIRED, RENDER_HINT, MIN_VALUE, MAX_VALUE,
  GROUP_NAME, SOURCE
) VALUES
(
  'PRD',
  2,
  'XPRD02',
  'LDRNM',           -- Full access
  TRUE,              -- Required
  'number',          -- Numeric input
  '0.01',            -- Min price
  '999999.99',       -- Max price
  'Pricing',         -- UI group
  'ecommerce'
);

-- Product category: foreign key with cascade
INSERT INTO TB_COST (
  COD_DIM, NUM_COST, COD_VAR, COD_ON_OFF,
  REQUIRED, RENDER_HINT, DELETE_CASCADE, SOURCE
) VALUES
(
  'PRD',
  5,
  'XPRD05',
  'LDRNM',
  TRUE,
  'select',          -- Dropdown select
  'PRDCAT',          -- When product deleted, cascade to category link
  'ecommerce'
);

Pre-Insert Functions (VALUE_TEMP)

Function	Example	Result
`{uuid}`	`{uuid}`	`550e8400-e29b-41d4-a716-446655440000`
`{md5}`	`{md5}`	MD5 hash of concatenated field values
`{counter}`	`{counter:PRD-{YYYY}-{NNN}}`	`PRD-2025-001` (uses TB_COUNTER)
`{sequence}`	`{sequence:seq_product_id}`	Next value from database sequence
`{slugify}`	`{slugify:XPRD01}`	URL-friendly slug from field
`{timestamp}`	`{timestamp}`	`20251219153045`
`{concat}`	`{concat:XPRD01,-,XPRD03}`	Concatenate fields
`{default}`	`{default:active}`	Default literal value

Querying TB_COST

// Get all field configurations for PRD dimension
const configs = await coreAPI.get('/api/v4/core/COST', {
  params: {
    filter: { COD_DIM: 'PRD' },
    sort: 'NUM_COST'
  }
});

// Get visible fields for list view (COD_ON_OFF contains 'L')
const listFields = configs.data.filter(c =>
  c.COD_ON_OFF.includes('L')
);

// Get required fields
const requiredFields = configs.data.filter(c =>
  c.REQUIRED === true
);

// Get fields that can be set on creation (COD_ON_OFF contains 'N')
const createFields = configs.data.filter(c =>
  c.COD_ON_OFF.includes('N')
);

TB_MENU - Permission Hierarchy

Purpose

Defines hierarchical permissions using the nested set model. Grants are organized in a tree structure with parent-child inheritance.

Schema

Field	Type	Description
MENU_ID	VARCHAR(50) PK	Menu/grant identifier
PARENT_ID	VARCHAR(50) FK	Parent menu ID (NULL for root)
NLEFT	INT	Left boundary (nested set)
NRIGHT	INT	Right boundary (nested set)
LEVEL	INT	Tree depth (0 = root)
LABEL	VARCHAR(255)	Human-readable label
DESCRIPTION	TEXT	Grant description
GRANT_TYPE	VARCHAR(50)	Type (read, write, delete, admin)
RESOURCE	VARCHAR(100)	Protected resource (dimension code or '*')
ACTIVE	BOOLEAN	Grant enabled (true/false)
SOURCE	VARCHAR(50)	Tenant identifier
CREATED_AT	VARCHAR(14)	Creation timestamp
UPDATED_AT	VARCHAR(14)	Last update timestamp

Nested Set Model

Structure:

products (NLEFT=1, NRIGHT=10)
├── products.read (NLEFT=2, NRIGHT=3)
├── products.write (NLEFT=4, NRIGHT=5)
│   ├── products.create (NLEFT=6, NRIGHT=7)
│   └── products.update (NLEFT=8, NRIGHT=9)
└── products.delete (NLEFT=10, NRIGHT=11)

Parent-Child Query:

-- Find all children of 'products'
SELECT child.*
FROM TB_MENU parent
INNER JOIN TB_MENU child
  ON child.NLEFT BETWEEN parent.NLEFT AND parent.NRIGHT
WHERE parent.MENU_ID = 'products';

Grant Inheritance:

User with products grant has all child grants (products.read, products.write, products.delete)
User with products.write grant has products.create and products.update

Example Data

-- Root level - products module
INSERT INTO TB_MENU (
  MENU_ID, PARENT_ID, NLEFT, NRIGHT, LEVEL,
  LABEL, GRANT_TYPE, RESOURCE, ACTIVE, SOURCE
) VALUES
(
  'products',
  NULL,
  1,
  10,
  0,
  'Product Management',
  'module',
  'PRD',
  TRUE,
  'ecommerce'
);

-- Read permission
INSERT INTO TB_MENU (
  MENU_ID, PARENT_ID, NLEFT, NRIGHT, LEVEL,
  LABEL, GRANT_TYPE, RESOURCE, ACTIVE, SOURCE
) VALUES
(
  'products.read',
  'products',
  2,
  3,
  1,
  'View Products',
  'read',
  'PRD',
  TRUE,
  'ecommerce'
);

-- Write permission (with children)
INSERT INTO TB_MENU (
  MENU_ID, PARENT_ID, NLEFT, NRIGHT, LEVEL,
  LABEL, GRANT_TYPE, RESOURCE, ACTIVE, SOURCE
) VALUES
(
  'products.write',
  'products',
  4,
  7,
  1,
  'Edit Products',
  'write',
  'PRD',
  TRUE,
  'ecommerce'
);

-- Create sub-permission
INSERT INTO TB_MENU (
  MENU_ID, PARENT_ID, NLEFT, NRIGHT, LEVEL,
  LABEL, GRANT_TYPE, RESOURCE, ACTIVE, SOURCE
) VALUES
(
  'products.create',
  'products.write',
  5,
  6,
  2,
  'Create Products',
  'write',
  'PRD',
  TRUE,
  'ecommerce'
);

-- Delete permission
INSERT INTO TB_MENU (
  MENU_ID, PARENT_ID, NLEFT, NRIGHT, LEVEL,
  LABEL, GRANT_TYPE, RESOURCE, ACTIVE, SOURCE
) VALUES
(
  'products.delete',
  'products',
  8,
  9,
  1,
  'Delete Products',
  'delete',
  'PRD',
  TRUE,
  'ecommerce'
);

Querying TB_MENU

// Check if user has grant (includes parent grants via nested set)
async function hasGrant(userGrants, requiredGrant) {
  const response = await coreAPI.get('/api/v4/core/MENU', {
    params: {
      filter: {
        MENU_ID: { $in: userGrants.join(',') }
      }
    }
  });

  const userMenus = response.data;
  const requiredMenu = await coreAPI.get('/api/v4/core/MENU', {
    params: { filter: { MENU_ID: requiredGrant } }
  });

  const requiredNLeft = requiredMenu.data[0].NLEFT;
  const requiredNRight = requiredMenu.data[0].NRIGHT;

  // Check if any user grant encompasses the required grant
  return userMenus.some(menu =>
    requiredNLeft >= menu.NLEFT && requiredNRight <= menu.NRIGHT
  );
}

// Usage
const hasPermission = await hasGrant(
  ['products.write'],       // User grants
  'products.create'         // Required grant
);
console.log(hasPermission); // true (write includes create)

TB_OBJECT - Dimension Relationships

Purpose

Defines relationships between dimensions (foreign keys, joins). Used for $expand parameter and cascade operations.

Schema

Field	Type	Description
COD_DIM	VARCHAR(50) FK	Source dimension
NUM_OBJECT	INT PK	Object number
COD_OBJECT	VARCHAR(50)	Relationship name (e.g., 'category', 'customer')
TARGET_DIM	VARCHAR(50) FK	Target dimension code
SOURCE_FIELD	VARCHAR(50)	Foreign key field in source table
TARGET_FIELD	VARCHAR(50)	Referenced field in target table (usually ID)
RELATIONSHIP_TYPE	VARCHAR(20)	one-to-one, one-to-many, many-to-one, many-to-many
CASCADE_DELETE	BOOLEAN	Delete related records when parent deleted
CASCADE_UPDATE	BOOLEAN	Update related records when parent updated
NULLABLE	BOOLEAN	Allow NULL foreign key
DESCRIPTION	TEXT	Relationship description
SOURCE	VARCHAR(50)	Tenant identifier
CREATED_AT	VARCHAR(14)	Creation timestamp
UPDATED_AT	VARCHAR(14)	Last update timestamp

Example Data

-- Product → Category (many-to-one)
INSERT INTO TB_OBJECT (
  COD_DIM, NUM_OBJECT, COD_OBJECT, TARGET_DIM,
  SOURCE_FIELD, TARGET_FIELD, RELATIONSHIP_TYPE,
  NULLABLE, CASCADE_DELETE, SOURCE
) VALUES
(
  'PRD',           -- Source dimension
  1,               -- Object number
  'category',      -- Relationship name
  'CAT',           -- Target dimension
  'XPRD05',        -- FK field in PRD table
  'CAT_ID',        -- PK field in CAT table
  'many-to-one',   -- Many products → one category
  FALSE,           -- Category required
  FALSE,           -- Don't delete product if category deleted
  'ecommerce'
);

-- Order → Customer (many-to-one)
INSERT INTO TB_OBJECT (
  COD_DIM, NUM_OBJECT, COD_OBJECT, TARGET_DIM,
  SOURCE_FIELD, TARGET_FIELD, RELATIONSHIP_TYPE,
  NULLABLE, SOURCE
) VALUES
(
  'ORD',
  1,
  'customer',
  'CUST',
  'XORD_CUSTOMER_ID',
  'CUST_ID',
  'many-to-one',
  FALSE,
  'ecommerce'
);

-- Order → Order Items (one-to-many)
INSERT INTO TB_OBJECT (
  COD_DIM, NUM_OBJECT, COD_OBJECT, TARGET_DIM,
  SOURCE_FIELD, TARGET_FIELD, RELATIONSHIP_TYPE,
  CASCADE_DELETE, SOURCE
) VALUES
(
  'ORD',
  2,
  'items',
  'ORDITEM',
  'ORD_ID',
  'XORDITEM_ORDER_ID',
  'one-to-many',
  TRUE,            -- Delete items when order deleted
  'ecommerce'
);

Querying TB_OBJECT

// Get all relationships for PRD dimension
const relationships = await coreAPI.get('/api/v4/core/OBJECT', {
  params: {
    filter: { COD_DIM: 'PRD' }
  }
});

// Use $expand to include related data
const productsWithCategory = await coreAPI.get('/api/v4/core/PRD', {
  params: {
    $expand: 'category',  // Uses COD_OBJECT from TB_OBJECT
    $num_rows: 10
  }
});

console.log(productsWithCategory.data[0].category);
// { CAT_ID: 'cat_123', XCAT01: 'Electronics', ... }

Summary

✅ TB_DIM registers all dimensions (entity types) with cascade rules
✅ TB_VAR defines individual fields with data types and validation
✅ TB_COST configures field visibility (COD_ON_OFF), required validation, and pre-insert functions
✅ TB_MENU manages hierarchical permissions using nested set model
✅ TB_OBJECT defines dimension relationships for joins and cascades

Key Takeaways:

MAP framework enables metadata-driven schema management
COD_ON_OFF in TB_COST controls field visibility per operation (L/D/N/M/R)
TB_MENU nested set allows hierarchical permission inheritance
TB_OBJECT enables $expand for relational queries
All metadata tables respect multi-tenant SOURCE isolation

Common Queries:

Get dimension config: GET /api/v4/core/DIM/{cod_dim}
Get field definitions: GET /api/v4/core/VAR?filter[COD_DIM]={dim}
Get field configs: GET /api/v4/core/COST?filter[COD_DIM]={dim}
Get grants: GET /api/v4/core/MENU
Get relationships: GET /api/v4/core/OBJECT?filter[COD_DIM]={dim}

Dimensions - Dimension concept explained
Field Visibility - COD_ON_OFF usage
Nested Set Model - TB_MENU structure
COD_ON_OFF Flags - Flag reference
API Endpoints - How to query metadata tables

Metadata Tables

Overview​

TB_DIM - Dimension Registry​

Purpose​

Schema​

Example Data​

Querying TB_DIM​

TB_VAR - Field Definitions​

Purpose​

Schema​

Example Data​

Querying TB_VAR​

TB_COST - Field Configuration​

Purpose​

Schema​

COD_ON_OFF Flags​

Example Data​

Pre-Insert Functions (VALUE_TEMP)​

Querying TB_COST​

TB_MENU - Permission Hierarchy​

Purpose​

Schema​

Nested Set Model​

Example Data​

Querying TB_MENU​

TB_OBJECT - Dimension Relationships​

Purpose​

Schema​

Example Data​

Querying TB_OBJECT​

Summary​

Related Concepts​

Overview

TB_DIM - Dimension Registry

Purpose

Schema

Example Data

Querying TB_DIM

TB_VAR - Field Definitions

Purpose

Schema

Example Data

Querying TB_VAR

TB_COST - Field Configuration

Purpose

Schema

COD_ON_OFF Flags

Example Data

Pre-Insert Functions (VALUE_TEMP)

Querying TB_COST

TB_MENU - Permission Hierarchy

Purpose

Schema

Nested Set Model

Example Data

Querying TB_MENU

TB_OBJECT - Dimension Relationships

Purpose

Schema

Example Data

Querying TB_OBJECT

Summary

Related Concepts