Creating an Entity

Every business object in Apostol CRM is an entity -- a set of SQL files that define its table, views, CRUD functions, REST API, event handlers, and workflow. This guide walks through creating a complete entity from scratch.

The 8-File Convention

Every entity lives in its own directory under entity/object/document/ (for business objects) or entity/object/reference/ (for lookup data). Each directory contains these files:

The key distinction: table.sql and init.sql only run during installation (--install). All other files are safe to re-run during --update.

Choose a Parent Class

Every entity extends either:

• Document -- for business objects with lifecycle, area-based access, priority, and rich metadata. Examples: client, invoice, payment, device, order.
• Reference -- for lookup/catalog data with code, name, and description (localized). Examples: region, currency, country, category.

This tutorial creates a sensor entity as a Document.

Step 1: Create the Table

The table extends the parent via a foreign key. The BEFORE INSERT trigger copies the parent's ID to the entity's ID, so they share the same UUID.

CREATE TABLE db.sensor (
    id              uuid PRIMARY KEY,
    document        uuid NOT NULL REFERENCES db.document(id) ON DELETE CASCADE,
    code            text NOT NULL,
    value           numeric,
    unit            text,
    metadata        jsonb
);

COMMENT ON TABLE db.sensor IS 'Sensor.';

CREATE UNIQUE INDEX ON db.sensor (code);
CREATE INDEX ON db.sensor (document);

CREATE OR REPLACE FUNCTION db.ft_sensor_insert()
RETURNS trigger AS $$
BEGIN
  IF NEW.id IS NULL THEN
    SELECT NEW.document INTO NEW.id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql
   SECURITY DEFINER
   SET search_path = kernel, pg_temp;

CREATE TRIGGER t_sensor_insert
  BEFORE INSERT ON db.sensor
  FOR EACH ROW
  EXECUTE PROCEDURE db.ft_sensor_insert();

Key rules:

• The id column is always the PRIMARY KEY and equals the parent's ID
• Foreign key to db.document(id) uses ON DELETE CASCADE
• The BEFORE INSERT trigger copies document to id
• Add indexes for lookup columns

Step 2: Create the Views

Every entity needs exactly 3 views in the kernel schema:

1. Sensor -- basic data view, joins the entity with its parent
2. AccessSensor -- access-filtered list of IDs (which objects the current user can read)
3. ObjectSensor -- full object view with all metadata (entity, class, type, state, owner, area, scope)

The ObjectSensor view is what api.sensor is based on. It joins everything: entity data, object metadata, state info, owner/operator, area, and scope.

Step 3: Write CRUD Functions

Every entity needs at minimum: Create*, Edit*, Get*.

CREATE OR REPLACE FUNCTION CreateSensor (
  pParent       uuid,
  pType         uuid,
  pCode         text,
  pLabel        text default null,
  pDescription  text default null,
  pValue        numeric default null,
  pUnit         text default null,
  pMetadata     jsonb default null
) RETURNS       uuid
AS $$
DECLARE
  uDocument     uuid;
  uClass        uuid;
  uMethod       uuid;
BEGIN
  SELECT class INTO uClass FROM db.type WHERE id = pType;

  IF GetEntityCode(uClass) <> 'sensor' THEN
    PERFORM IncorrectClassType();
  END IF;

  uDocument := CreateDocument(pParent, pType, pLabel, pDescription);

  INSERT INTO db.sensor (id, document, code, value, unit, metadata)
  VALUES (uDocument, uDocument, pCode, pValue, pUnit, pMetadata);

  uMethod := GetMethod(uClass, GetAction('create'));
  PERFORM ExecuteMethod(uDocument, uMethod);

  RETURN uDocument;
END;
$$ LANGUAGE plpgsql
   SECURITY DEFINER
   SET search_path = kernel, pg_temp;

The pattern in every Create* function:

1. Validate the type belongs to the correct entity
2. Call the parent's Create function: CreateDocument(...) or CreateReference(...)
3. Insert the specialized row into db.sensor
4. Execute the create method to trigger the workflow

The Edit* function follows a similar pattern: call the parent's Edit, then update the specialized columns using coalesce() to preserve existing values.

Step 4: Write the API Layer

The API layer provides 6 public-facing functions:

Plus one view: api.sensor (based on ObjectSensor).

CREATE OR REPLACE VIEW api.sensor
AS
  SELECT * FROM ObjectSensor;

CREATE OR REPLACE FUNCTION api.add_sensor (
  pParent       uuid,
  pType         uuid,
  pCode         text,
  pLabel        text default null,
  pDescription  text default null,
  pValue        numeric default null,
  pUnit         text default null,
  pMetadata     jsonb default null
) RETURNS       uuid
AS $$
BEGIN
  RETURN CreateSensor(pParent, coalesce(pType, GetType('default.sensor')),
    pCode, pLabel, pDescription, pValue, pUnit, pMetadata);
END;
$$ LANGUAGE plpgsql
   SECURITY DEFINER
   SET search_path = kernel, pg_temp;

Step 5: Write the REST Dispatcher

The REST dispatcher maps URL paths to API functions. See the REST Endpoints guide for details.

Every entity gets 6 standard routes plus dynamic method delegation:

CREATE OR REPLACE FUNCTION rest.sensor (
  pPath       text,
  pPayload    jsonb default null
) RETURNS     SETOF json
AS $$
DECLARE
  r           record;
  e           record;
  arKeys      text[];
BEGIN
  IF pPath IS NULL THEN
    PERFORM RouteIsEmpty();
  END IF;

  IF current_session() IS NULL THEN
    PERFORM LoginFailed();
  END IF;

  CASE pPath
  WHEN '/sensor/type' THEN
    -- return available types for this entity
  WHEN '/sensor/method' THEN
    -- return available methods for an object
  WHEN '/sensor/count' THEN
    -- count objects with search/filter
  WHEN '/sensor/set' THEN
    -- create or update (upsert)
  WHEN '/sensor/get' THEN
    -- get single object by ID
  WHEN '/sensor/list' THEN
    -- list objects with search/filter/pagination
  ELSE
    RETURN NEXT ExecuteDynamicMethod(pPath, pPayload);
  END CASE;

  RETURN;
END;
$$ LANGUAGE plpgsql
   SECURITY DEFINER
   SET search_path = kernel, pg_temp;

The ELSE clause handles workflow actions automatically: /sensor/enable, /sensor/disable, /sensor/delete, /sensor/restore, and any custom actions.

Step 6: Write Event Handlers

Every entity needs 9 event handlers for the standard lifecycle actions, plus a drop handler for cleanup:

CREATE OR REPLACE FUNCTION EventSensorCreate (
  pObject   uuid default context_object()
) RETURNS   void
AS $$
BEGIN
  PERFORM WriteToEventLog('M', 1000, 'create', 'Sensor created.', pObject);
END;
$$ LANGUAGE plpgsql;

The 9 events: Create, Open, Edit, Save, Enable, Disable, Delete, Restore, Drop. Each follows the same pattern -- log the action and perform any custom logic.

The Drop handler is special: it deletes the entity's row from db.sensor before the parent cascade removes everything.

Step 7: Register the Entity

The init.sql file registers the entity in the Platform's class hierarchy:

CREATE OR REPLACE FUNCTION CreateEntitySensor (
  pParent       uuid
)
RETURNS         uuid
AS $$
DECLARE
  uEntity       uuid;
BEGIN
  -- Register entity
  uEntity := AddEntity('sensor', 'Sensor');

  -- Create class with at least one type
  PERFORM CreateClassSensor(pParent, uEntity);

  -- Wire REST endpoint
  PERFORM RegisterRoute('sensor', AddEndpoint('SELECT * FROM rest.sensor($1, $2);'));

  RETURN uEntity;
END
$$ LANGUAGE plpgsql
   SECURITY DEFINER
   SET search_path = kernel, pg_temp;

This involves:

• AddEntity -- registers the entity name (must be unique)
• AddClass -- creates the class in the hierarchy
• AddType -- creates at least one type (format: 'qualifier.entity')
• AddDefaultMethods -- sets up the standard 4-state workflow
• RegisterRoute + AddEndpoint -- wires the REST endpoint

Step 8: Wire into the Parent

Add the entity to the parent's create and update scripts:

In entity/object/document/create.psql:

\ir './sensor/create.psql'

In entity/object/document/update.psql:

\ir './sensor/update.psql'

In the configuration's InitConfigurationEntity() function:

PERFORM CreateEntitySensor(GetClass('document'));

Checklist

After completing all steps, your entity will have:

• A database table with proper inheritance
• Three kernel views for data access
• CRUD functions (Create, Edit, Get)
• Six API functions (add, update, set, get, count, list)
• A REST dispatcher with six standard routes
• Nine event handlers for the full lifecycle
• Workflow integration (states, methods, transitions)
• Automatic REST routing via the Platform

Run ./runme.sh --install for first-time setup, or ./runme.sh --update to update routines and views after changes.