A-POST-OLdevel

REST Endpoints

Every entity in Apostol CRM exposes its API through a REST dispatcher function in the rest schema. The C++ server routes incoming HTTP requests to these PL/pgSQL functions, which parse the path and payload and delegate to the api.* layer.

How Routing Works

When the C++ server receives POST /api/v1/sensor/list, it:

  1. Strips the /api/v1/ prefix
  2. Extracts the entity name: sensor
  3. Looks up the registered endpoint: rest.sensor
  4. Calls rest.sensor('/sensor/list', payload)

The rest.sensor function uses a CASE statement to route the path to the correct API function.

Function Signature

Every REST dispatcher follows this signature:

CREATE OR REPLACE FUNCTION rest.sensor (
  pPath       text,
  pPayload    jsonb default null
) RETURNS     SETOF json
AS $$
  • pPath -- the URL path (e.g., '/sensor/list')
  • pPayload -- the JSON request body
  • Returns SETOF json -- each row is one JSON object in the response array

The 6 Standard Routes

Every entity implements these routes:

/entity/type -- Available types

Returns the types defined for this entity (e.g., default.sensor):

WHEN '/sensor/type' THEN
  FOR r IN SELECT * FROM jsonb_to_record(pPayload) AS x(fields jsonb)
  LOOP
    FOR e IN EXECUTE format('SELECT %s FROM api.type($1)',
      JsonbToFields(r.fields, GetColumns('type', 'api')))
      USING GetEntity('sensor')
    LOOP
      RETURN NEXT row_to_json(e);
    END LOOP;
  END LOOP;

/entity/method -- Available methods

Returns the methods available for a specific object based on its current state:

WHEN '/sensor/method' THEN
  IF pPayload IS NULL THEN
    PERFORM JsonIsEmpty();
  END IF;

  arKeys := array_cat(arKeys, ARRAY['id']);
  PERFORM CheckJsonbKeys(pPath, arKeys, pPayload);

  FOR r IN SELECT * FROM jsonb_to_record(pPayload) AS x(id uuid)
  LOOP
    FOR e IN SELECT * FROM api.get_object_methods(r.id) ORDER BY sequence
    LOOP
      RETURN NEXT row_to_json(e);
    END LOOP;
  END LOOP;

/entity/count -- Count objects

Returns the count of objects matching optional search/filter criteria:

WHEN '/sensor/count' THEN
  IF pPayload IS NOT NULL THEN
    arKeys := array_cat(arKeys, ARRAY['search', 'filter']);
    PERFORM CheckJsonbKeys(pPath, arKeys, pPayload);
  ELSE
    pPayload := '{}';
  END IF;

  FOR r IN SELECT * FROM jsonb_to_record(pPayload) AS x(search jsonb, filter jsonb)
  LOOP
    FOR e IN SELECT * FROM api.count_sensor(r.search, r.filter) AS count
    LOOP
      RETURN NEXT row_to_json(e);
    END LOOP;
  END LOOP;

/entity/set -- Create or update (upsert)

Uses GetRoutines() to dynamically discover parameter names from the function signature:

WHEN '/sensor/set' THEN
  IF pPayload IS NULL THEN
    PERFORM JsonIsEmpty();
  END IF;

  arKeys := array_cat(arKeys, GetRoutines('set_sensor', 'api', false));
  PERFORM CheckJsonbKeys(pPath, arKeys, pPayload);

  FOR r IN EXECUTE format(
    'SELECT row_to_json(api.set_sensor(%s)) FROM jsonb_to_record($1) AS x(%s)',
    array_to_string(GetRoutines('set_sensor', 'api', false, 'x'), ', '),
    array_to_string(GetRoutines('set_sensor', 'api', true), ', ')
  ) USING pPayload
  LOOP
    RETURN NEXT r;
  END LOOP;

GetRoutines introspects the function signature at runtime:

  • GetRoutines('set_sensor', 'api', false) -- returns parameter names as text[]
  • GetRoutines('set_sensor', 'api', true) -- returns name type pairs for record casting
  • GetRoutines('set_sensor', 'api', false, 'x') -- returns x.name prefixed references

/entity/get -- Get single object

Supports field projection via the fields parameter:

WHEN '/sensor/get' THEN
  IF pPayload IS NULL THEN
    PERFORM JsonIsEmpty();
  END IF;

  arKeys := array_cat(arKeys, ARRAY['id', 'fields']);
  PERFORM CheckJsonbKeys(pPath, arKeys, pPayload);

  FOR r IN SELECT * FROM jsonb_to_record(pPayload) AS x(id uuid, fields jsonb)
  LOOP
    FOR e IN EXECUTE format('SELECT %s FROM api.get_sensor($1)',
      JsonbToFields(r.fields, GetColumns('sensor', 'api')))
      USING r.id
    LOOP
      RETURN NEXT row_to_json(e);
    END LOOP;
  END LOOP;

/entity/list -- List with pagination

WHEN '/sensor/list' THEN
  IF pPayload IS NOT NULL THEN
    arKeys := array_cat(arKeys, ARRAY['fields', 'search', 'filter',
      'reclimit', 'recoffset', 'orderby']);
    PERFORM CheckJsonbKeys(pPath, arKeys, pPayload);
  ELSE
    pPayload := '{}';
  END IF;

  FOR r IN SELECT * FROM jsonb_to_record(pPayload) AS x(
    fields jsonb, search jsonb, filter jsonb,
    reclimit integer, recoffset integer, orderby jsonb)
  LOOP
    FOR e IN EXECUTE format('SELECT %s FROM api.list_sensor($1, $2, $3, $4, $5)',
      JsonbToFields(r.fields, GetColumns('sensor', 'api')))
      USING r.search, r.filter, r.reclimit, r.recoffset, r.orderby
    LOOP
      RETURN NEXT row_to_json(e);
    END LOOP;
  END LOOP;

Batch Support

Every route supports both single-object and batch operations. When the payload is a JSON array, use jsonb_to_recordset (plural) instead of jsonb_to_record:

IF jsonb_typeof(pPayload) = 'array' THEN
  FOR r IN SELECT * FROM jsonb_to_recordset(pPayload) AS x(id uuid)
  LOOP
    -- process each item
  END LOOP;
ELSE
  FOR r IN SELECT * FROM jsonb_to_record(pPayload) AS x(id uuid)
  LOOP
    -- process single item
  END LOOP;
END IF;

Field Projection

Clients can request only specific columns:

{
  "id": "...",
  "fields": ["id", "code", "label"]
}

The JsonbToFields(r.fields, GetColumns('sensor', 'api')) function validates the requested fields against the actual view columns and returns a SQL column list. If fields is NULL, it returns *.

Dynamic Method Delegation

The ELSE clause at the end handles workflow actions automatically:

ELSE
  RETURN NEXT ExecuteDynamicMethod(pPath, pPayload);

This handles paths like /sensor/enable, /sensor/disable, /sensor/delete, /sensor/restore, and any custom actions registered in the workflow. ExecuteDynamicMethod extracts the action from the path, looks up the method for the object's class and current state, and executes it.

Key Validation

Before processing, validate the payload keys to catch typos:

arKeys := array_cat(arKeys, ARRAY['id', 'fields']);
PERFORM CheckJsonbKeys(pPath, arKeys, pPayload);

CheckJsonbKeys raises an error if the payload contains unknown keys.

Registering the Route

In the entity's init.sql, register the route:

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

This maps the URL prefix /sensor/* to the rest.sensor function.

Adding Custom Routes

You can add entity-specific routes beyond the standard 6:

WHEN '/client/balance' THEN
  IF pPayload IS NULL THEN
    PERFORM JsonIsEmpty();
  END IF;

  arKeys := array_cat(arKeys, ARRAY['id']);
  PERFORM CheckJsonbKeys(pPath, arKeys, pPayload);

  FOR r IN SELECT * FROM jsonb_to_record(pPayload) AS x(id uuid)
  LOOP
    RETURN NEXT api.get_client_balance(r.id);
  END LOOP;

Error Handling

Errors are handled by the Platform's exception system. Functions raise exceptions (via PERFORM SomeError() or RAISE EXCEPTION), and the REST layer returns a standard error response. Common error functions:

  • RouteIsEmpty() -- path is NULL
  • LoginFailed() -- no valid session
  • JsonIsEmpty() -- payload is NULL when required
  • ObjectNotFound(entity, field, value) -- object does not exist
  • AccessDenied() -- insufficient permissions