Selegic CRM Docs

Metadata Schemas — CRM

This reference documents the metadata models used by the CRM, the data they store, how they relate to each other, and how they are used at runtime.

Audience: backend engineers, integrators, UI form builders, and platform maintainers.

At-a-glance

  • Entities: collections of Fields that represent business objects (e.g., Account, Contact).
  • Fields: typed attributes of an Entity. Fields can be standard data, twins, or computed (formula) fields.
  • Relationships: metadata describing associations between Entity records.
  • ValidationRule: reusable, structured rules attached to entities.
  • Formula / FormulaFieldReference: expression + explicit dependencies used to compute field values.
  • TS-first authoring: both ValidationRules and Formulas support TypeScript sources for type-safe logic.

Entity

  • What it stores: identifier, internal name, display name, physical tableName, activity flags, timestamps.
  • Relevance: primary container for field metadata and relationship definitions. Systems that generate DB tables, UIs, or APIs use Entity.tableName and the fields relation to build structure.
  • How it is used at runtime: when generating queries, building forms, and enumerating available columns.

Field

  • What it stores:
    • identifiers: id, entityId
    • naming: name, displayName
    • type: type (enum)
    • behavioral flags: isRequired, isUnique, isActive
    • UI hints: options (JSON), order
    • defaultValue, timestamps
  • Key relations:
    • entity — which Entity owns the field (cascade delete)
    • syncedField / syncedBy — optional 1:1 synced field on the same entity; syncedFieldId is unique.
    • formula — optional Formula record when type = FORMULA.
    • referencedByFormulas — back reference listing formulas that depend on this field.
  • Relevance & behavior:
    • Drives form rendering (labels, input type, options, required/unique hints).
    • syncedField is used where a paired field needs to be kept in sync or used for integration/backup purposes.
    • Entity-level validationRules supply server-side and client-side validation logic.
    • formula fields are computed; their value is derived not persisted by manual entry.

FieldType (enum)

  • Values: TEXT, NUMBER, EMAIL, PHONE, DATE, DATETIME, BOOLEAN, SELECT, MULTI_SELECT, TEXTAREA, URL, JSON, FORMULA.
  • Relevance: determines UI control, validation expectations, and storage/compute behavior. FORMULA indicates computed field whose behavior is described in Formula.

ValidationRule

  • What it stores:
    • entityId, type (ValidationRuleType), optional message, optional config JSON for rule parameters (e.g., min/max/regex, cross-field references), order, isActive, timestamps.
    • tsSource? (string): TypeScript code implementing the rule.
    • tsExport (string, default "validate"): export name to invoke.
    • references (relation): explicit list of fields this rule depends on.
  • Types: REQUIRED, UNIQUE, LENGTH, RANGE, REGEX, INCLUSION, EXCLUSION, CUSTOM.
  • How it is used:
    • Validation flows load the entity's validationRules, evaluate them in order, and produce user-facing errors using message.
    • Rules can be field-level (e.g., regex on name) or cross-field (e.g., startDate <= endDate) based on config.
    • UNIQUE and other DB-backed rules require DB checks; others are enforced in application logic.
    • If tsSource is provided, the platform compiles and invokes the exported function specified by tsExport for type-safe validation.

Formula

  • What it stores: fieldId (the destination field), expression (string), returnType (FieldType), isActive, timestamps.
    • tsSource? (string): TypeScript code implementing the formula; if present, this is the source of truth.
    • tsExport (string, default "evaluate"): export name to invoke.
  • Relevance: defines computation for fields of type = FORMULA. The expression encodes how to compute the value from one or more source fields.
  • How it is used at runtime:
    • The expression is parsed and compiled into an evaluation plan.
    • During parsing, referenced fields are identified and stored as FormulaFieldReference rows so dependency graphs are explicit.
    • When source fields change, the system locates dependent formulas via FormulaFieldReference and schedules recomputation for affected records.
    • returnType determines how to coerce and validate the computed value before storing or returning it to clients.
    • If tsSource is present, the platform compiles and invokes the exported function specified by tsExport for type-safe computation. expression can be treated as documentation/preview or omitted.

FormulaFieldReference

  • What it stores: formulaId, fieldId, optional path (for nested references), and timestamp.
  • Relevance: makes formula dependencies explicit and queryable.
  • How it is used:
    • Build dependency graphs to decide recompute order and incremental updates.
    • Determine which formulas to invalidate when a source field is updated.
    • Support traversal when formulas reference fields across relationships (use path to describe relation chains).

ValidationRuleFieldReference

  • What it stores: ruleId, fieldId, optional path (for nested/cross-entity references), and timestamp.
  • Relevance: makes validation dependencies explicit and queryable for cross-field checks.
  • How it is used:
    • During rule creation/update, persist references to all fields a rule reads.
    • Drive incremental validation by quickly identifying rules affected by changes to specific fields.

Relationship

  • What it stores: entityId (source), targetEntityId (target), name, type (ONE_TO_ONE, ONE_TO_MANY, MANY_TO_MANY), onDelete action.
  • Relevance: allows formulas to reference fields across entities and enables UI to present connected data.
  • How it is used: formula path resolution, join generation for queries, and referential behaviors in data operations.

Operational patterns and runtime responsibilities

  • Form rendering and validation

    • The front-end or API loads Entity.fields and the entity's validationRules to render controls and client-side validation.
    • On submit, server-side validation re-evaluates entity validationRules to ensure data integrity.
    • If a rule has tsSource, compile and execute it with a safe, typed runtime environment.

  • Formula lifecycle

    • Parse expression → extract referenced fields and relation paths → persist FormulaFieldReference rows.
    • Validate types of referenced fields against operations in the expression.
    • On source field update: query FormulaFieldReference for dependent formulaIds, compute affected target records, evaluate formulas in dependency order, and persist results.
    • Detect and prevent cycles during formula creation/update by analyzing dependency graph.

  • Twin field behavior

    • syncedField establishes a one-to-one pairing between fields on the same entity.
    • Business logic should decide whether twin updates are symmetric (update both sides) or directional (primary → twin only).
    • Use a service helper to set twin pointers atomically and ensure uniqueness constraints are maintained.

  • Data storage constraints

    • Unique constraints exist for entity names, table names, per-entity field names, twin pointers, and formula ownership.
    • Cascading deletes ensure metadata cleanup when parent objects are removed.

Examples (conceptual)

  • ValidationRule JSON (stored in ValidationRule.config):
{"min": 1, "max": 100}
  • Formula expression (string stored in Formula.expression):
IF({Status} = "Closed", {CloseDate}, null)
  • FormulaFieldReference path value for cross-entity reference:
owner.email

ValidationRule Examples

This sub-section provides one concrete example for each ValidationRuleType. Examples show config stored in ValidationRule.config, a sample message, and notes on evaluation.

  • REQUIRED
{
  "field": "name"
}

Message: "Name is required."

Notes: If the specified field is null/empty the rule fails. This is straightforward to evaluate in the API/form layer.

  • UNIQUE
{
  "field": "email"
}

Message: "Email must be unique."

Notes: Evaluate with a DB query to check for other rows having the same value. Prefer also adding a DB unique constraint when possible.

  • LENGTH
{
  "field": "username",
  "min": 3,
  "max": 50
}

Message: "Username must be between 3 and 50 characters."

Notes: Apply to string fields; enforce using string length checks.

  • RANGE
{
  "field": "amount",
  "min": 0,
  "max": 10000
}

Message: "Amount must be between 0 and 10000."

Notes: Apply to numeric or date fields; coerce types before applying.

  • REGEX
{
  "field": "productCode",
  "pattern": "^[A-Z]{3}-\\d{4}$"
}

Message: "Product code must match pattern AAA-1234."

Notes: Use a safe, anchored regex. Escape backslashes in JSON.

  • INCLUSION
{
  "field": "status",
  "in": ["Open", "Pending", "Closed"]
}

Message: "Status must be one of Open, Pending or Closed."

Notes: Useful for enums or select-type fields; evaluate by membership check.

  • EXCLUSION
{
  "field": "category",
  "notIn": ["Deprecated", "Internal"]
}

Message: "Category cannot be Deprecated or Internal."

Notes: The inverse of INCLUSION.

  • CUSTOM

Example 1 — hook-based custom validator:

{
  "hook": "validateVatNumber",
  "params": { "countryField": "country" }
}

Message: "VAT number invalid for provided country."

Notes: The application invokes a user-registered hook with params and the current record values. Use for complex cross-field or third-party validations.

Example 2 — cross-field expression (stored as CUSTOM to allow arbitrary checks):

{
  "type": "crossField",
  "expression": "startDate <= endDate",
  "fields": ["startDate", "endDate"]
}

Message: "Start date must be before or equal to end date."

Notes: Evaluate by resolving referenced fields from the record and executing the expression securely (not via eval); prefer a small expression engine or compiled AST.

TS Authoring (type-safe)

You can write both validation and formula logic in TypeScript for full type safety. The platform compiles the code and invokes a named export.

  • Validation rule TS signature (default export name validate):
// tsSource for ValidationRule
export type ValidationContext<EntityShape> = {
  entity: { id: string; name: string };
  fields: Record<string, { type: string }>;
  db: { existsUnique: (field: string, value: unknown, excludeId?: string) => Promise<boolean> };
};

export type ValidationResult = { ok: true } | { ok: false; message?: string };

export async function validate<EntityShape>(
  record: EntityShape,
  ctx: ValidationContext<EntityShape>
): Promise<ValidationResult> {
  // Example: unique email
  const email = (record as any).email as string | undefined;
  if (!email) return { ok: true };
  const taken = await ctx.db.existsUnique("email", email);
  return taken ? { ok: false, message: "Email must be unique" } : { ok: true };
}
  • Formula TS signature (default export name evaluate):
// tsSource for Formula
export type FormulaContext<EntityShape> = {
  entity: { id: string; name: string };
  fields: Record<string, { type: string }>;
};

export function evaluate<EntityShape>(record: EntityShape, ctx: FormulaContext<EntityShape>) {
  // Example: fullName = `${firstName} ${lastName}`
  const first = (record as any).firstName ?? "";
  const last = (record as any).lastName ?? "";
  return `${first} ${last}`.trim();
}

Notes:

  • tsExport determines which function the platform invokes.
  • The platform should compile TS in a sandboxed environment and provide typed contexts.
  • Persist field dependencies in ValidationRuleFieldReference and FormulaFieldReference so changes can trigger the right validations and recomputations.

Maintenance notes

  • Keep formula parsing and dependency extraction deterministic to avoid spurious updates.
  • Provide admin tools to list formulas that reference a given field and to backfill formula values when formulas or data schemas change.

If you want implementation help

  • I can add a parser and the service code that: parses expressions, populates FormulaFieldReference, validates referenced fields, and performs safe recompute/backfill flows.

End of reference.

Selegic CRM

Documentation for the Selegic CRM platform (server, web, apps and packages).

Apply Migrations

Run Prisma migrations against the CRM Cloud SQL database using gcloud and Cloud SQL Proxy.

On this page

ValidationRule ExamplesTS Authoring (type-safe)