Database Workflows (PLV8)

For creating workflows, the visual Design mode, triggers, and security configuration, see Database Workflows in the User Guide.

Database Workflows written in AI/Code mode run as PLV8 functions - synchronous JavaScript executing inside PostgreSQL. They share a transaction with the calling context, have direct access to platform data via the api object, and complete in milliseconds. They cannot make HTTP calls, use AI APIs, or do any I/O outside the database. For that, use a Server Function instead.

Module structure

Every PLV8 function follows a four-section layout: METADATA, CONSTANTS, PRIVATE_FUNCTIONS, and HANDLERS. When a new AI/Code workflow is created in the platform, the editor is pre-populated with a working template following this structure.

Command dispatch

When the caller includes a cmd field, the platform invokes the handler with that name.
When cmd is omitted, the run handler is called.
Each handler receives input - the request payload with cmd removed.
Handler names use snake_case.
A single-function shorthand is supported: returning input => { ... } directly is treated as { run: fn }.

The `__meta` object

The __meta block tells the platform about this function’s identity and dependencies:

Field	Description
`thing_id`	Stable UUID for this function - required
`can_delete`	Whether records created by this function can be deleted
`function_targets`	Thing IDs of other functions this function calls via `api.execute_function`
`view_targets`	Thing IDs of views this function reads via `api.get_data`
`entity_targets`	Thing IDs of entities this function reads or writes
`component_targets`	Thing IDs of components this function reads or writes

Registering targets is not strictly required for the function to run, but it allows the platform to track dependencies and helps keep the system stable over time. The AI assistant maintains this block automatically as you develop - you can also ask it explicitly: “update the meta object to reflect all targets in this code.”

Using AI to write PLV8 code

The AI/Code tab opens in chat mode by default. Describe what the function should do and the AI generates PLV8 code following the structure above. Click Apply to push a suggestion into the editor - suggestions are never applied automatically.

Before prompting, add the entities, views, and functions the AI should work with via the Context picker. The AI does not auto-discover your data model - without context it cannot reference the correct fields, view parameters, or function signatures. The more relevant context you add, the more accurate the generated code will be.

Use See Code to switch to the editor and review or modify the code directly. Switch back to Use AI at any time to continue iterating.

The AI is aware of PLV8 constraints: it will not generate fetch, setTimeout, or any other Node.js/browser API. If a task genuinely requires those, it will suggest using a Server Function instead.

`api` overview

api is injected automatically - no import needed. Call methods directly, without await (PLV8 is synchronous):

const rows = api.get_data({
  target: view_thing_id,
  params: { status: api.constants.statuses.live }
});

Raw SQL is not allowed in PLV8 functions. Do not use plv8.execute, SELECT, INSERT, UPDATE, or DELETE directly - all data access goes through api.

Data access

`api.get_data`

Load rows from a view or dataset.

Field	Required	Description
`target`	yes	View or dataset thing ID
`params`	no	Filter/input values for the view (default `{}`)
`options.page_info`	no	`{ page_size, page, skip, order_by }` - defaults: page_size 50, page 1

Returns an array of rows. Throws on failure.

const rows = api.get_data({
  target: constants.open_orders_view,
  params: { customer_id: input.customer_id },
  options: { page_info: { page_size: 100 } }
});

`api.query_data`

Query an entity or component table directly with SQL fragments.

Field	Required	Default	Description
`target`	yes		Entity or component thing ID
`select_columns`	no	all	Comma-separated column list
`filter`	no	none	SQL `WHERE` fragment (no `WHERE` keyword)
`order_by`	no	none	SQL `ORDER BY` fragment
`offset`	no	none	Skip rows
`limit`	no	no limit	Max rows
`all_versions`	no	`false`	Include all versions

Always use api.quote_literal for dynamic values in filter:

const lines = api.query_data({
  target: constants.invoice_lines_component,
  filter: `parent_thing_id = ` + api.quote_literal(input.thing_id),
  order_by: `ord asc`
});

`api.save_data`

Save one or more records as draft (no submit).

api.save_data({
  target: constants.invoice_entity,
  item: { thing_id: input.thing_id, version_id: input.version_id, total: new_total }
});

api.save_data({
  target: constants.invoice_lines_component,
  items: line_rows,
  options: { run_under_transaction: true }
});

`api.save_and_submit`

Save and submit in one step. Same arguments as api.save_data.

api.save_and_submit({
  target: constants.invoice_entity,
  item: input
});

`api.submit`

Submit an existing record version without saving field changes.

api.submit({
  target: constants.invoice_entity,
  item: { thing_id: input.thing_id, version_id: input.version_id }
});

`api.delete_data`

Delete one or more records. Provide item or items, not both.

api.delete_data({
  target: constants.invoice_lines_component,
  items: stale_lines,
  options: { run_under_transaction: true }
});

`api.clone_data`

Clone an entity record into a new instance. Uses source instead of target.

const cloned = api.clone_data({
  source: { thing_id: input.thing_id, version_id: input.version_id },
  item: template_record
});

`api.clone_version`

Create a new draft version from an existing instance. Uses source only (no item).

const draft = api.clone_version({
  source: { thing_id: input.thing_id, version_id: input.version_id }
});
// draft.thing_id, draft.version_id are the new version's IDs

`api.execute_function`

Call another published function or workflow.

Field	Required	Description
`target`	yes	Function thing ID
`input`	yes	Input object (use `{}` if empty)
`cmd`	no	Handler name - defaults to `run`

const result = api.execute_function({
  target: constants.recalculate_totals_fn,
  cmd: `recalculate`,
  input: { thing_id: input.thing_id, version_id: input.version_id }
});

`api.describe_structure`

Return field-level read/write access for an entity.

const structure = api.describe_structure({ target: constants.invoice_entity });
const can_edit = structure.property_access.find(p => p.property_id === `total`).can_write;

`api.recalculate_data`

Recalculate derived fields on a record and return the updated version.

const updated = api.recalculate_data({
  target: constants.invoice_entity,
  item: invoice_record
});
Object.assign(invoice_record, updated);

Request context

`api.current`

Member	Kind	Description
`application`	property	Current application object
`user()`	function	Current user, or `{ anonymous: true }`
`device_id()`	function	Device ID for the session
`client_environment()`	function	Client environment (features, settings)

const user = api.current.user();
if (user.anonymous) {
  throw new Error(`Authentication required`);
}

`api.is_feature_enabled`

if (api.is_feature_enabled(`my_feature`)) {
  // ...
}

Helpers

`api.constants`

Read-only reference values. Do not duplicate these locally.

// statuses: draft (1), submitted (2), live (3), history (4), deleted (5)
filter: `status = ` + api.constants.statuses.live

// entity_types: versioned, simple, system
if (entity.entity_type__thing_id === api.constants.entity_types.versioned) { ... }

`api.quote_literal`

Safely embed a dynamic value in a query_data filter or order fragment.

filter: `thing_id = ` + api.quote_literal(input.thing_id)
       + ` AND status = ` + api.quote_literal(api.constants.statuses.live)

Never concatenate raw input directly into filter strings.

`api.log` / log shortcuts

Write a function log row.

Method	Level
`api.log({ log_level?, message?, data? })`	configurable
`api.log_debug(message?, data?)`	debug
`api.log_info(message?, data?)`	info
`api.log_warn(message?, data?)`	warn
`api.log_error(message?, data?)`	error

api.log_info(`Invoice submitted`, { thing_id: input.thing_id });

try {
  api.save_data({ target: constants.invoice_entity, item: record });
} catch (err) {
  api.log_error(`Save failed`, { message: err.message });
  throw err;
}

`api.new_uuid`

Generate a random UUID string.

const id = api.new_uuid();

`api.subtransaction`

Run a callback in a nested transaction. Rolls back only that block on throw, without affecting the outer transaction unless you rethrow.

api.subtransaction(function () {
  api.save_data({ target: constants.invoice_entity, item: header });
  api.save_data({ target: constants.invoice_lines_component, items: lines });
});

`api.run_with_mutex`

Serialize execution for a shared resource using a session lock.

const result = api.run_with_mutex(`stock_` + warehouse_id, function () {
  const stock = api.get_data({ target: constants.stock_view, params: { warehouse_id } });
  return api.save_data({
    target: constants.stock_entity,
    item: { ...stock[0], quantity: stock[0].quantity - input.quantity }
  });
});

`api.guard_transaction`

Acquire a transaction-scoped lock. Held until the transaction commits or rolls back.

api.guard_transaction(`order_` + input.order_id, 15);
// proceed with saves - no other transaction can enter with the same key

	`run_with_mutex`	`guard_transaction`
Lock scope	One handler call	Entire current transaction
Typical use	Short critical section	Multi-step save across a transaction

`api.md5` / `api.md5_uuid`

const hex = api.md5(`some_text`);
const deterministic_id = api.md5_uuid(`prefix_` + counter);

`api.clone`

Deep-copy an object before mutating it.

const copy = api.clone(original_record);
copy.total = new_total;
api.save_data({ target: constants.invoice_entity, item: copy });

`api.laptime`

Elapsed time from the current transaction start, useful for profiling.

const start = api.laptime();
// ... work ...
const elapsed = api.laptime();

Naming conventions

Kind	Example
Handler	`submit_invoice`, `recalculate_totals`
Entity constant	`invoice_entity`
Component constant	`invoice_lines_component`
Function constant	`submit_invoice_fn`
View constant	`invoice_status_view`
Record variable	`invoice_record`
Record array	`invoice_records`

General practices

Keep handlers imperative - avoid classes, repositories, or service layers.
Validate input early; throw clear errors.
Always include a run handler as the default entry point.
All data access goes through api - no raw SQL.
Use api.quote_literal for every dynamic value in query_data filters.
Keep logic short and transactional. If the work may take time or needs external I/O, use an Execute Server Function step instead.

Database Workflows (PLV8)

Module structure

Command dispatch

The __meta object

Using AI to write PLV8 code

api overview

Data access

api.get_data

api.query_data

api.save_data

api.save_and_submit

api.submit

api.delete_data

api.clone_data

api.clone_version

api.execute_function

api.describe_structure

api.recalculate_data