Docyrus Platform

Docyrus Platform

Docyrus is an AI-native Backend Platform as a Service (BPaaS) that enables businesses to build B2B web apps, mobile apps, client portals, internal tools, AI agents, chatbots, and integrations — without building backend infrastructure from scratch.

Core Building Blocks

The platform is composed of six core building blocks that work together:

• Apps — Top-level containers that group data sources, automations, and custom queries into a deployable unit.
• Data Sources — Structured collections of records with defined schemas. Support simple, advanced, external (connected databases/APIs), and system (pre-built) types. Every data source — internal or external — is exposed through a single, unified CRUD endpoint.
• Fields — 45+ field types that define the schema of each data source, covering text, numbers, dates, selections, relations, files, formulas, nested data, and more.
• Enumerations — Reusable option sets for selection fields, with color, icon, and ordering support.
• Custom Queries — SQL-based analytics templates with dynamic variable interpolation, runtime filters, and multi-database targeting.
• Automations — Event-driven workflows with triggers (record changes, time-based, webhooks, buttons) and action chains (email, notifications, HTTP requests, AI prompts, record operations).

For detailed specifications of each building block, see references/core-building-blocks.md.

Key Feature Areas

Querying & Data Operations

Unified query engine with column selection, 50+ filter operators, aggregations, formulas, pivots, child queries, and full-text search. Full CRUD with bulk operations, record comments, and file attachments. Also a read-only logical SQL endpoint (DSQL) for PostgreSQL-compatible SELECT queries over appSlug.dataSourceSlug tables.

See references/querying-and-data-operations.md.

For complete query payload reference with examples, see references/data-source-query-guide.md.

For logical SQL (DSQL) capabilities, limits, tenant.* pseudo-functions, and the error catalog, see references/dsql-reference.md.

For block formula design (inline expressions and subqueries), see references/formula-design-guide-llm.md.

AI Capabilities

AI agent builder with tool binding, knowledge bases, MCP servers, 18+ model providers, agent chaining, task scheduling, persistent memory, chat integrations, and evaluation metrics.

See references/ai-capabilities.md.

Automation & Workflows

Event-driven automation engine with six trigger types and eleven action types. Supports conditional flows, action chains, and archiving.

See references/automation-and-workflows.md.

Authentication & Multi-Tenancy

OAuth2 flows (PKCE, Client Credentials, Device Code), scope-based permissions, tenant isolation, role-based and record-level ACL, React auth-provider authorization helpers, and client portal system.

See references/auth-and-multi-tenancy.md.

Integrations & Events

Connector framework for HTTP and SQL providers, webhook management, collaborative document editing, in-app messaging, notifications, and email.

See references/integrations-and-events.md.

Platform Services

App templates, data import/export, webforms, reporting & analytics, deployment, localization, audit logging, and billing.

See references/platform-services.md.

Developer Tools

Auto-generated OpenAPI specs, MCP server, full-featured CLI (data + schema + automation CRUD, custom AI agent management, app agent context and AI tools, connectors, messaging, plus the pi agent runtime and repo dev tooling), REST API client libraries, React auth provider with current-user and authorization helpers, and auto-generated collection hooks.

See references/developer-tools.md.

For full CLI command reference (all commands, options, and flags), see references/docyrus-cli-usage.md.

AI Capabilities

AI Agent Builder

Create and manage AI agents with:

• Tool binding — Attach external functions and APIs as agent tools
• Data source access — Grant agents read/write access to specific data sources
• Knowledge base — Upload documents for RAG-powered context
• MCP servers — Connect Model Context Protocol servers for extended tool discovery
• Multi-model support — Choose from 18+ AI providers: OpenAI, Anthropic, Google (AI Studio & Vertex), AWS Bedrock, Groq, Replicate, Together, Mistral, Cohere, Azure, Fireworks, xAI, Portkey, Alibaba, DeepSeek, HuggingFace, OpenRouter, Nebius, Perplexity, Cerebras
• Agent connections — Chain agents as sub-agents or forward requests between agents
• Workflow steps — Define multi-step agent workflows with conditional logic

AI Task Scheduling

• Immediate, scheduled, and recurring (cron-based) agent task execution
• Task status tracking and result persistence

AI Memory

Persistent memory system with scoping levels: user, thread, agent, workspace, and global. Vector embeddings for semantic retrieval.

AI Chat Integrations

• Microsoft Teams and Google Chat installation and message mapping
• Thread-level conversation tracking across platforms

Generative Features

• Content enhancement and completion
• Design generation (logos, banners)
• Audio transcription (speech-to-text)
• Semantic search across records and documents
• AI-powered data import and field recommendations

AI Evaluation

17 evaluation metrics including toxicity, hallucination, and faithfulness tracking for agent quality monitoring.

AI Usage Tracking

Per-model token usage and cost tracking for billing and optimization.

Authentication & Multi-Tenancy

Authentication Methods

• OAuth2 Authorization Code (PKCE) — Browser-based apps
• OAuth2 Client Credentials — Server-to-server
• OAuth2 Device Code — CLI and headless environments
• Automatic token refresh — Transparent renewal with refresh tokens
• React auth provider (`@docyrus/signin`) — Supports standalone PKCE and iframe postMessage auth modes, auto-fetches the current user from /v1/users/me, and can refresh user state after role or permission changes

Access Control

• Scope-based permissions — ReadWrite.All, User.Read, Architect.ReadWrite.All, AI.ReadWrite.All, MCP.Connect, and more
• Tenant isolation — Enforced data separation between tenants on every query
• Role-based access — Roles with granular permission sets; React apps can check them with hasRole() from useDocyrusAuth() or @docyrus/signin/core
• Permission helpers — React apps can evaluate ACL permission checks with hasPermission(operation, dataSourceId) using the merged aclRules returned for the current user
• Record-level ACL — Per-record ownership and sharing rules (private, read-only, read/update, full access)
• Field-level ACL — Restrict visibility or editability of individual fields per role

React App Authorization Helpers

• useDocyrusAuth() exposes user, hasRole, hasPermission, and refreshUser
• user is auto-fetched from /v1/users/me after authentication completes
• Pure framework-agnostic helpers are also available from @docyrus/signin/core

User Management

• User signup with email verification, profile management, avatar, preferences
• Password change/reset, email change workflows
• Multi-device support with device registration for push notifications
• Per-user AI feature access control

Multi-Tenancy

• Fully isolated data per tenant
• Multiple apps per tenant, each with scoped data sources
• Tenant-specific configurations, branding, and translations
• Organizational hierarchy with teams, roles, and user scoping

Portal System

• Client portal configuration with dedicated portal users, organizations, roles, and sessions
• Separate authentication and access control for external users

Automation & Workflows

Automation Engine

Define event-driven workflows with triggers and action chains. Each automation belongs to a tenant app and combines one or more triggers with a graph of typed action nodes.

Trigger Types

The runtime supports ten trigger types. The dev API exposes each as a typed endpoint, and the CLI exposes the same set under docyrus automation create-trigger --type <kebab-case>.

• record-created — fires when a record is inserted into the source data source
• record-modified — fires when specific columns change (with all / any match mode)
• record-deleted — fires when a record is removed from the source data source
• recurrence — fires on a cron-like schedule (hour / day / week / month / year, with run-at time and weekday/month-day options)
• app-event — fires from a connector or core data-provider event (with optional webhook binding)
• webhook — fires when an external HTTP webhook is received
• emailhook — fires when a tenant-bound inbound email is received
• webform — fires when a public webform is submitted
• button-activation — fires when a user activates an in-UI button on a record
• manual-activation — fires when a record is manually activated via the UI

The platform also tracks max_run_per_record for record-* and recurrence triggers to prevent re-firing on the same record.

Action Node Types

Action nodes are the building blocks of an automation's execution graph. The dev API exposes each as a typed endpoint, and the CLI exposes the same set under docyrus automation create-node --type <kebab-case>.

• external-action — invokes a registered core_action against a connector (requires action_type_id; backend validates payload against core_action.input_json_schema and provisions the tenant_action row)
• send-email — sends an email through a tenant email account or template
• send-notification — pushes an in-app or device notification
• create-record — inserts a record into a target data source
• update-records — bulk-updates records in a target data source (optionally pivoted on a target field)
• request-approval — opens an approval cycle against an input data source
• request-input — collects ad-hoc input from a user via the input data source
• http-request — fires an HTTP request (supports batch mode, transformers, and connection auth)
• data-source-query — runs a data source query and emits the result into the chain
• custom-query — runs a saved custom SQL query
• generate-document — renders an HTML/PDF/DOCX template against a record
• ai-prompt — runs a stored prompt against an AI provider
• ai-agent — invokes a Docyrus AI agent
• execute-script — runs a sandboxed JavaScript snippet
• wait-for — bridge action that delays the next step. Does no work itself; forwards input data unchanged and queues the next step(s) with tenant_job_queue.process_after = clock_timestamp() + delaySeconds so the worker defers execution. Configure via data.delaySeconds (integer, ≤ 30 days) or the data.delayValue + data.delayUnit (seconds/minutes/hours/days) pair.

Composition Features

• Conditional branches via per-node condition payloads
• Action chains with parent/child wiring (parent node id)
• Field mappings (field_mapping, dynamic_field_mapping) for record-shaped nodes
• Request lifecycle hooks (pre_action_request, post_action_request) on external-action nodes
• Input/output transformers (input_template, input_transformer, output_transformer, batch_transformer, error_transformer) on http-request nodes
• Custom headers (custom_headers) for http-request nodes
• Target data source conditions (target_data_source_condition) on update-records and external-action nodes
• Soft-delete (archiving) for both automations and individual nodes

Core Building Blocks

Data Sources

Data sources are the fundamental building block. Each data source represents a structured collection of records with a defined schema. Every data source — whether backed by an internal database table or a connected external REST API — is exposed through a single, unified CRUD endpoint. This means consumers interact with all data sources the same way regardless of where the data lives.

Types:

• Simple — Schema-flexible records. Quick to set up, ideal for lightweight or rapidly evolving data.
• Advanced — Fully structured data sources with dedicated columns per field. Supports base data sources (shared field inheritance across variants) and high-performance querying.
• External — Connected external REST API resources or databases mapped as data sources. External resources are accessed through the same unified API as internal data sources, abstracting away the underlying connection.
• System — Pre-built templates for common entities: documents, threads, messages, contacts, organizations, activities, tasks, events, calendars, time entries, projects, and sections.

Ownership models: App-scoped, tenant-custom, product-provided, system-built-in, or user-specific.

Fields

Every data source is composed of fields. 45+ field types cover all data modeling needs:

Enumerations

Multi-option fields (select, multiSelect, status, tagSelect) support enumerations with color, icon, and ordering. Enum sets allow sharing option lists across multiple fields.

Apps

Apps are the top-level container that groups data sources, views, forms, pages, navigation, and configurations into a deployable unit.

Supported app types: web, mobile, AI agent, integration, inline (embedded), client portal (web & mobile), Chrome extension, MS Office add-in, MS Outlook add-in, external (white-label), website, and website widget.

App lifecycle: draft → design → development → active → inactive, with archiving and permanent deletion.

Customization layers: Apps support per-tenant overrides for pages, views, menus, fields, and enums — allowing product apps to be tailored without forking.

Custom Queries

SQL-based analytics templates with variable interpolation. Support dynamic runtime filters, pagination, and multiple database targets. Built-in variables include tenant context, user identity, and custom filter expressions.

Data Source Query Guide

Comprehensive reference for querying data sources using the ZodSelectQueryPayload schema. This document covers every parameter, operator, and feature with detailed examples.

---

Table of Contents

1. Overview 2. Query Payload Structure 3. Common Parameters 4. Columns 5. Filters 6. Filter Keyword 7. Order By 8. Pagination (limit / offset) 9. Calculations (Aggregations) 10. Formulas 11. Pivot 12. Child Queries 13. Expand 14. Query Mode 15. Distinct Columns 16. Full Count 17. Cursor-Based Sync 18. Filter Operators Reference 19. Allowed Functions Reference 20. Allowed Aggregates Reference 21. Allowed Cast Types 22. Complete Examples

---

Overview

All data source reads go through a unified select query payload. The payload is validated by ZodSelectQueryPayload (defined in libs/shared/src/database/schemas.ts). It supports:

• Column selection with relation expansion, aliasing, spread, and functions
• Filtering with nested AND/OR groups, dozens of operators, and relation field filtering
• Keyword search via full-text search
• Sorting by one or more fields with direction
• Pagination with limit/offset
• Aggregations (count, sum, avg, min, max, etc.) with grouping
• Formulas — computed virtual columns (block/AST-based)
• Pivot — advanced cross-tab grouping with date range series and matrix CTEs
• Child queries — fetch related child records as nested JSON arrays
• Field expansion — automatically expand relation/user/enum fields

---

Query Payload Structure

The full ZodSelectQueryPayload type:

interface ISelectQueryParams {
  // --- Identity ---
  dataSourceId?: string | null;
  dataSourceFullSlug?: string | null;
  connectionId?: string | null;
  connectionAccountId?: string | null;
  parentRecord?: Record<string, any> | null;

  // --- Filtering ---
  filters?: IQueryFilterGroup | null;
  filterKeyword?: string | null;

  // --- Column Selection ---
  columns?: string | null;
  distinctColumns?: string[] | null;

  // --- Computed Columns ---
  formulas?: Record<string, ISelectQueryFormula> | null;

  // --- Aggregation ---
  calculations?: ISelectQueryCalculationRule[] | null;
  groupSummaries?: boolean;

  // --- Sorting ---
  orderBy?: string | ISelectQueryOrderBy | ISelectQueryOrderBy[];

  // --- Pagination ---
  limit?: number;   // default: 100
  offset?: number;  // default: 0

  // --- Expansion ---
  expandTypes?: ("user" | "enum" | "relation")[] | null;
  expand?: string[] | null;

  // --- Misc ---
  queryMode?: "OLTP" | "OLAP" | "EXPORT";
  fullCount?: boolean;
  cursorDateStart?: string | null;
  cursorDateEnd?: string | null;

  // --- Advanced ---
  childQueries?: ISelectQueryChildQueryParams[] | null;
  pivot?: {
    matrix: ISelectPivotMatrixQuery[];
    hideEmptyRows?: boolean;
    orderBy?: string | ISelectQueryOrderBy | ISelectQueryOrderBy[];
    limit?: number;
  } | null;
}

---

Common Parameters

These parameters identify which data source to query.

Note: You need either dataSourceId or dataSourceFullSlug to identify the target data source.

---

Columns

Parameter: columns — string | null

Use a comma-separated list of field slugs to select specific columns.

Rules

• Use () to select specific columns from a related record (field-relation, field-select, field-userSelect type fields).
• Use ... (spread operator) to flatten related columns into the root object. Always flatten related columns when fetching data for charts to avoid object nesting and parsing overhead.
• Use : to alias a column. The alias goes on the left side (e.g. tn:task_name).
• Use @ to apply a pre-defined function. Always use with an alias (e.g. name:upper@account_name).
• Do not use aggregation functions (count, sum, etc.) via @ syntax — use calculations instead.

Basic Selection

"columns": "task_name, created_on, record_owner"

Aliasing with :

Use : to give a column an alias (shorter name in the result).

"columns": "ra:related_account"

Result:

[
  {
    "ra": {
      "id": "uuid",
      "name": "account name"
    }
  }
]

Relation Expansion with ()

Use parentheses to select specific columns from a related record. Works with field-relation, field-select, and field-userSelect type fields.

"columns": "task_name, related_account(name:account_name, phone:account_phone)"

Result:

[
  {
    "task_name": "Task Name",
    "related_account": {
      "name": "Account Name",
      "phone": "05556668899"
    }
  }
]

Spread Operator ...

Use the spread operator to flatten selected columns from a related record into the root object (no nesting).

"columns": "task_name, ...related_account(account_name, phone:account_phone)"

Result:

[
  {
    "task_name": "Task Name",
    "account_name": "Account Name",
    "phone": "05556668899"
  }
]

Functions with @

Use @ to apply a pre-defined function to a column, specified as <function>@<field>.

"columns": "task_name, ...related_account(an:upper@account_name, ap:account_phone)"

Result:

[
  {
    "task_name": "Task Name",
    "an": "ACCOUNT NAME",
    "ap": "05556668899"
  }
]

Special Date/DateTime Formulas for Aggregations

Use the @ symbol with special date formulas to format date intervals. These are typically used with a date interval filter to group data for a specific period. Values outside the current period are grouped as "OLDER" and "UPCOMING".

Format: <formula>@<date_or_datetime_field>

Column Syntax with to_char Function

Use to_char with brackets [] for date formatting:

"columns": "day:to_char[DD/MM/YYYY]@created_on"

This formats the created_on field as DD/MM/YYYY and aliases it as day.

---

Filters

Parameter: filters — IQueryFilterGroup | null

Filters use a recursive group structure with combinators (and / or) and rules.

Tip: If you are asked to find records that contain a specific string, prefer using filterKeyword instead of filters for that filter. filterKeyword performs full-text search across all searchable fields.

Filter Group Structure

interface IQueryFilterGroup {
  rules: (IQueryFilterRule | IQueryFilterGroup)[];
  combinator?: "and" | "or";  // default: "and"
  not?: boolean;               // negate the entire group
}

interface IQueryFilterRule {
  field?: string;
  operator: IFilterOperator;
  value?: any;
  filterType?: QueryFilterType | null;
}

Filter Types (for value casting)

Example: Basic AND Filter

{
  "filters": {
    "combinator": "and",
    "rules": [
      {
        "field": "task_status",
        "operator": "=",
        "value": 1
      },
      {
        "field": "priority",
        "operator": ">=",
        "value": 3
      }
    ]
  }
}

Example: Nested AND + OR

Filter records created between two dates, AND where either email is empty OR phone is not empty:

{
  "filters": {
    "combinator": "and",
    "rules": [
      {
        "field": "created_on",
        "operator": "between",
        "value": ["2025-10-01", "2025-11-01"]
      },
      {
        "combinator": "or",
        "rules": [
          {
            "field": "email",
            "operator": "empty"
          },
          {
            "field": "phone",
            "operator": "not empty"
          }
        ]
      }
    ]
  }
}

Example: Filtering by Related Record's Field (String Match)

Use filterKeyword when searching for a specific substring across all searchable fields:

{
  "filterKeyword": "John",
  "columns": "id, name, email"
}

Alternatively, use rel_{{relation_field_slug}}/{{field_slug}} with like operator to filter by a specific related field:

{
  "filters": {
    "combinator": "and",
    "rules": [
      {
        "field": "rel_client/name",
        "operator": "like",
        "value": "John"
      }
    ]
  }
}

Example: Filtering by Related Record's Field

Use the rel_{{relation_field_slug}}/{{field_slug}} syntax to filter by a parent/related table's field:

{
  "filters": {
    "combinator": "and",
    "rules": [
      {
        "field": "task_status",
        "operator": "in",
        "value": [1, 2, 3]
      },
      {
        "field": "rel_client/account_status",
        "operator": "=",
        "value": 2
      }
    ]
  }
}

Example: Negated Filter Group

{
  "filters": {
    "combinator": "and",
    "not": true,
    "rules": [
      {
        "field": "status",
        "operator": "=",
        "value": "archived"
      }
    ]
  }
}

Example: Date Shortcut Operators

{
  "filters": {
    "rules": [
      {
        "field": "created_on",
        "operator": "this_month"
      }
    ]
  }
}

Example: User-Related Operators

{
  "filters": {
    "rules": [
      {
        "field": "record_owner",
        "operator": "active_user"
      }
    ]
  }
}

Example: X Days Operators

{
  "filters": {
    "rules": [
      {
        "field": "due_date",
        "operator": "in_next_x_days",
        "value": 7
      }
    ]
  }
}

---

Filter Keyword

Parameter: filterKeyword — string | null

Performs a full-text search across all searchable fields.

{
  "filterKeyword": "John Doe",
  "columns": "id, name, email"
}

---

Order By

Parameter: orderBy — string | ISelectQueryOrderBy | ISelectQueryOrderBy[]

Use comma-separated field and direction pairs to sort data.

String Format

{
  "orderBy": "created_on DESC"
}

Multiple fields:

{
  "orderBy": "firstname ASC, lastname DESC"
}

Object Format

{
  "orderBy": {
    "field": "created_on",
    "direction": "desc"
  }
}

Array Format

{
  "orderBy": [
    { "field": "firstname", "direction": "asc" },
    { "field": "lastname", "direction": "desc" }
  ]
}

Sorting by Related Field

Use parentheses to sort by a field of a related table:

{
  "orderBy": "relation_field_slug(field_name DESC), id ASC"
}

---

Pagination

limit

Type: number (positive integer) Default: 100

Maximum number of records to return.

offset

Type: number (non-negative integer) Default: 0

Number of records to skip for pagination.

Example

{
  "columns": "id, name",
  "limit": 25,
  "offset": 50,
  "orderBy": "created_on DESC"
}

This fetches records 51–75 (page 3 with 25 per page).

---

Calculations

Parameter: calculations — ISelectQueryCalculationRule[] | null

Use calculations to group and aggregate data.

Calculation Rule Structure

interface ISelectQueryCalculationRule {
  func: string;        // "count" | "sum" | "avg" | "min" | "max" | "jsonb_agg" | "json_agg" | "array_agg"
  field: string;       // field/column to aggregate
  name?: string;       // alias for the result column
  isDistinct?: boolean; // aggregate unique values only (default: false)
  minValue?: number;   // aggregate values greater than this
  maxValue?: number;   // aggregate values less than this
  numberType?: "bigint" | "int" | "decimal"; // result number type
}

Rules

• Always use the id field for counting records.
• Use the aggregated field's slug for other functions (sum, avg, etc.).
• Skip numberType unless it is specifically required.
• Use name to alias the calculation result column (keep it short).
• Do not use distinctColumns together with calculations. Prefer calculations to aggregate data.

Example: Count per Group

Count open tasks per user:

{
  "columns": "record_owner(name)",
  "calculations": [
    {
      "field": "id",
      "func": "count",
      "name": "count_of_open_tasks"
    }
  ],
  "filters": {
    "combinator": "and",
    "rules": [
      {
        "field": "task_status",
        "operator": "=",
        "value": 1
      }
    ]
  }
}

Result:

[
  {
    "record_owner": {
      "name": "User Name"
    },
    "count_of_open_tasks": 10
  }
]

Example: Distinct Count

Count unique emails:

{
  "calculations": [
    {
      "field": "email",
      "func": "count",
      "name": "unique_emails",
      "isDistinct": true
    }
  ]
}

Result:

[
  {
    "unique_emails": 10
  }
]

Example: Multiple Aggregations

{
  "columns": "category",
  "calculations": [
    {
      "field": "id",
      "func": "count",
      "name": "total"
    },
    {
      "field": "amount",
      "func": "sum",
      "name": "totalAmount"
    },
    {
      "field": "amount",
      "func": "avg",
      "name": "avgAmount"
    },
    {
      "field": "amount",
      "func": "min",
      "name": "minAmount"
    },
    {
      "field": "amount",
      "func": "max",
      "name": "maxAmount"
    }
  ]
}

groupSummaries

Type: boolean Default: false

When true and aggregation is used, includes group summary rows in the output.

---

Formulas

Parameter: formulas — Record<string, ISelectQueryFormula> | null

Formulas are virtual computed columns injected into SELECT queries at build time. Keys are the formula names (used as column aliases), values are formula definitions.

There are two block formula formats:

1. Block Inline Formula

AST-based formula that compiles to an inline SQL expression. Uses a block tree with kind discriminator.

interface IQueryBlockInlineFormulaSchema {
  alias?: string;
  inputs: IQueryFormulaBlock[];  // exactly 1 root block
}

Example: Simple Division

{
  "columns": "id, name, basic_formula",
  "formulas": {
    "basic_formula": {
      "inputs": [{
        "kind": "math",
        "op": "/",
        "inputs": [
          { "kind": "column", "name": "balance" },
          { "kind": "literal", "literal": 100 }
        ]
      }]
    }
  }
}

SQL: ("t0"."balance" / $1) as "basic_formula"

2. Block Subquery Formula

Compiles to a correlated subquery against a child data source.

interface IQueryBlockSubqueryFormulaSchema {
  alias?: string;
  inputs: IQueryFormulaBlock[];
  from: string;                           // child table full slug
  with: string | Record<string, string>;  // join condition(s)
  filters?: IQueryFilterGroup;
}

Example: Count Child Records

{
  "columns": "id, name, children_count",
  "formulas": {
    "children_count": {
      "from": "app_child_table",
      "with": "parent_field",
      "inputs": [{
        "kind": "aggregate",
        "name": "count",
        "inputs": []
      }]
    }
  }
}

SQL: (SELECT count(*) FROM "schema"."child_table" AS "t0_child" WHERE "t0_child"."parent_field" = "t0"."id") AS "children_count"

Example: Subquery with Filters

{
  "formulas": {
    "active_children": {
      "from": "app_child_table",
      "with": "parent_id",
      "filters": {
        "rules": [
          { "field": "status", "operator": "=", "value": "active" }
        ]
      },
      "inputs": [{
        "kind": "aggregate",
        "name": "count",
        "inputs": []
      }]
    }
  }
}

Example: Multi-Field Subquery Join

{
  "formulas": {
    "related_sum": {
      "from": "app_child",
      "with": {
        "child_field1": "parent_field1",
        "child_field2": "parent_field2"
      },
      "inputs": [{
        "kind": "aggregate",
        "name": "sum",
        "inputs": [{ "kind": "column", "name": "amount" }]
      }]
    }
  }
}

Example: Compatibility Wrapper

Block subquery formulas can also be wrapped under an expression key:

{
  "formulas": {
    "children_count": {
      "from": "app_child_table",
      "with": "parent_field",
      "inputs": [{
        "kind": "aggregate",
        "name": "count",
        "distinct": true,
        "inputs": [{ "kind": "column", "name": "id" }]
      }]
    }
  }
}

Block Formula Kinds Reference

Every block has a kind discriminator and optional tz (timezone) and cast (type cast) properties.

literal — Static Values

{ "kind": "literal", "literal": "Hello World" }
{ "kind": "literal", "literal": 42 }
{ "kind": "literal", "literal": true }
{ "kind": "literal", "literal": null }
{ "kind": "literal", "literal": ["active", "pending", "approved"] }

column — Table Column Reference

{ "kind": "column", "name": "fullname" }
{ "kind": "column", "name": ["col1", "col2"] }

builtin — SQL Constants

{ "kind": "builtin", "name": "current_date" }
{ "kind": "builtin", "name": "now" }

Allowed values: current_date, current_time, current_timestamp, now

function — SQL Function Calls

{
  "kind": "function",
  "name": "concat",
  "inputs": [
    { "kind": "literal", "literal": "Hello " },
    { "kind": "column", "name": "fullname" }
  ]
}

Only whitelisted functions are allowed (see Allowed Functions Reference).

extract — Date Part Extraction

{
  "kind": "extract",
  "part": "month",
  "inputs": [{ "kind": "column", "name": "created_on" }]
}

SQL: extract(month from "t0"."created_on")

Parts: year, month, day, hour, minute, second

aggregate — Aggregate Functions

{ "kind": "aggregate", "name": "count", "inputs": [] }

SQL: count(*)

{
  "kind": "aggregate",
  "name": "count",
  "distinct": true,
  "inputs": [{ "kind": "column", "name": "product_code" }]
}

SQL: count(distinct "t0"."product_code")

Allowed aggregates: count, sum, avg, min, max, jsonb_agg, json_agg, array_agg

math — Arithmetic Operations

{
  "kind": "math",
  "op": "*",
  "inputs": [
    { "kind": "column", "name": "quantity" },
    { "kind": "column", "name": "unit_price" }
  ]
}

SQL: ("t0"."quantity" * "t0"."unit_price")

Operators: +, -, *, /, % Requires at least 2 operands. For 3+: ((a op b) op c).

case — Conditional Expressions

{
  "kind": "case",
  "cases": [{
    "when": {
      "kind": "compare",
      "op": ">",
      "left": { "kind": "column", "name": "price" },
      "right": { "kind": "literal", "literal": 100 }
    },
    "then": { "kind": "literal", "literal": "expensive" }
  }],
  "else": { "kind": "literal", "literal": "cheap" }
}

SQL: case when "t0"."price" > $1 then $2 else $3 end

compare — Comparison Operations

{
  "kind": "compare",
  "op": "in",
  "left": { "kind": "column", "name": "status" },
  "right": { "kind": "literal", "literal": ["active", "pending"] }
}

SQL: "t0"."status" in ($1, $2)

Operators: =, !=, <>, >, <, >=, <=, like, ilike, in, not in, not_in

boolean — Logical Operations

{
  "kind": "boolean",
  "op": "and",
  "inputs": [
    {
      "kind": "compare", "op": ">",
      "left": { "kind": "column", "name": "price" },
      "right": { "kind": "literal", "literal": 100 }
    },
    {
      "kind": "compare", "op": "ilike",
      "left": { "kind": "column", "name": "name" },
      "right": { "kind": "literal", "literal": "%pro%" }
    }
  ]
}

SQL: (("t0"."price" > $1) and ("t0"."name" ilike $2))

Operators: and, or, not

Block Formula: Type Casting

Any block can include a cast property:

{ "kind": "column", "name": "price", "cast": "decimal" }

SQL: ("t0"."price")::decimal

Block Formula: Timezone Handling

Any block can include a tz property:

{ "kind": "function", "name": "now", "tz": "UTC" }

SQL: now() at time zone $1

Advanced Formula Examples

Nested Functions with Aggregates: Round the sum of (quantity × unit_price)

{
  "formulas": {
    "rounded_total": {
      "alias": "rounded_total",
      "inputs": [{
        "kind": "function",
        "name": "round",
        "inputs": [
          {
            "kind": "aggregate",
            "name": "sum",
            "inputs": [{
              "kind": "math",
              "op": "*",
              "inputs": [
                { "kind": "column", "name": "quantity" },
                { "kind": "column", "name": "unit_price" }
              ]
            }]
          },
          { "kind": "literal", "literal": 2 }
        ]
      }]
    }
  }
}

SQL: round(sum(("t0"."quantity" * "t0"."unit_price")), $1) as "rounded_total"

CASE with Boolean Logic: Categorize rows

{
  "formulas": {
    "category": {
      "inputs": [{
        "kind": "case",
        "cases": [{
          "when": {
            "kind": "boolean",
            "op": "and",
            "inputs": [
              {
                "kind": "compare", "op": ">",
                "left": { "kind": "column", "name": "price" },
                "right": { "kind": "literal", "literal": 100 }
              },
              {
                "kind": "compare", "op": "ilike",
                "left": { "kind": "column", "name": "name" },
                "right": { "kind": "literal", "literal": "%pro%" }
              }
            ]
          },
          "then": { "kind": "literal", "literal": "premium" }
        }],
        "else": { "kind": "literal", "literal": "standard" }
      }]
    }
  }
}

Null Handling with COALESCE:

{
  "formulas": {
    "safe_desc": {
      "inputs": [{
        "kind": "function",
        "name": "coalesce",
        "inputs": [
          { "kind": "column", "name": "description" },
          { "kind": "literal", "literal": "No description" }
        ]
      }]
    }
  }
}

Timezone Conversion:

{
  "formulas": {
    "local_time": {
      "inputs": [{
        "kind": "function",
        "name": "to_char",
        "inputs": [
          { "kind": "function", "name": "now", "tz": "UTC" },
          { "kind": "literal", "literal": "YYYY-MM-DD HH24:MI:SS" }
        ]
      }]
    }
  }
}

SQL: to_char(now() at time zone $1, $2)

---

Pivot

Parameter: pivot — { matrix, hideEmptyRows?, orderBy?, limit? } | null

Use pivot to perform advanced cross-tab grouping queries with aggregations. Each matrix object is executed as a CTE query. All CTEs are cross-joined to create a full matrix, then the main data is left-joined. This ensures all combinations appear in results, even when no matching records exist.

Pivot Structure

interface IPivot {
  matrix: ISelectPivotMatrixQuery[];
  hideEmptyRows?: boolean;
  orderBy?: string | ISelectQueryOrderBy | ISelectQueryOrderBy[];
  limit?: number;
}

interface ISelectPivotMatrixQuery {
  using: string;      // field in main query to join the CTE on
  columns: string;    // columns to select (supports alias, spread, functions)
  spread?: boolean;   // spread jsonb columns as separate columns
  filters?: IQueryFilterGroup;
  limit?: number;
  dateRange?: {
    interval: "day" | "week" | "month" | "year" | "hour" | "minute" | "second";
    increment?: number;   // number of intervals to increment (default: 1)
    min: string;          // minimum datetime value (ISO format)
    max: string;          // maximum datetime value (ISO format)
  };
}

How Pivot Works

1. Each matrix entry generates a CTE (Common Table Expression):

• If dateRange is provided, a date range series is generated
• Otherwise, records are fetched from the related data source

2. All CTEs are cross-joined to create the full cartesian product (matrix) 3. The main query data is left-joined to the matrix 4. This ensures all combinations appear, even with zero matching records

Example: Orders per Day, per User, per Status

{
  "columns": "...order_status(orderStatus:name)",
  "pivot": {
    "matrix": [
      {
        "using": "created_on",
        "columns": "day:to_char[DD/MM/YYYY]@created_on",
        "dateRange": {
          "interval": "day",
          "min": "2025-09-01T00:00:00Z",
          "max": "2025-09-02T00:00:00Z"
        },
        "spread": true
      },
      {
        "using": "record_owner",
        "columns": "userName:name",
        "spread": true,
        "filters": {
          "combinator": "and",
          "rules": [
            {
              "field": "primary_role",
              "operator": "=",
              "value": "1cdefd30-9f6d-4c7e-94c9-5b8a7e1c9f31"
            }
          ]
        }
      }
    ]
  },
  "calculations": [
    {
      "field": "id",
      "func": "count",
      "name": "total"
    },
    {
      "field": "amount",
      "func": "sum",
      "name": "totalSold"
    }
  ]
}

What each matrix entry does:

1. First matrix (using: "created_on"): Creates a date range series from 2025-09-01 to 2025-09-02 with day intervals. Even if there are no orders on a given day, that day still appears in results. 2. Second matrix (using: "record_owner"): Fetches users filtered by role. Even if a user has no orders on a day, they still appear in the cross-join.

Result:

[
  {
    "userName": "User 1",
    "orderStatus": 1,
    "day": "01/09/2025",
    "total": 10,
    "totalSold": 3000
  },
  {
    "userName": "User 2",
    "orderStatus": 5,
    "day": "01/09/2025",
    "total": 5,
    "totalSold": 1500
  },
  {
    "userName": "User 1",
    "orderStatus": 1,
    "day": "02/09/2025",
    "total": 10,
    "totalSold": 3000
  },
  {
    "userName": "User 2",
    "orderStatus": 3,
    "day": "02/09/2025",
    "total": 5,
    "totalSold": 1500
  }
]

Date Range Intervals

Pivot Options

---

Child Queries

Parameter: childQueries — ISelectQueryChildQueryParams[] | null

Use childQueries to fetch related records from a child data source as a nested JSON array for each parent record. This is similar to a LEFT JOIN but returns results as an aggregated JSON array in a single column.

Child Query Structure

interface ISelectQueryChildQueryParams {
  alias: string;                    // alias for the child query
  from: string;                    // child data source slug in "appSlug_slug" format
  using: string;                   // field in the child DS that references the parent record
  columns?: string | null;         // comma-separated columns to select from child
  filters?: IQueryFilterGroup;     // optional filters on child records
  calculations?: ISelectQueryCalculationRule[]; // optional aggregations
  orderBy?: string | ISelectQueryOrderBy | ISelectQueryOrderBy[];
  limit?: number;                  // max child records per parent (default: 100)
}

Example: Clients with Their Matters

{
  "columns": "id, name, matters",
  "childQueries": [
    {
      "alias": "matters",
      "from": "attornaid_matter",
      "using": "client",
      "columns": "name",
      "filters": {
        "rules": [
          { "field": "created_on", "operator": "<", "value": "2025-12-01" }
        ]
      }
    }
  ]
}

Result:

[
  {
    "id": "uuid",
    "name": "Client Name",
    "matters": [
      { "name": "Matter 1" },
      { "name": "Matter 2" }
    ]
  }
]

Key Rules

• The child query key (e.g. "matters") must also appear in the parent's columns string.
• from uses appSlug_slug format (e.g. "attornaid_matter").
• using is the field in the child data source that references the parent record's id.
• All parent query parameters (columns, filters, calculations, orderBy, limit) are supported within child queries.

Example: Products with Recent Orders (limited, sorted)

{
  "columns": "id, product_name, recent_orders",
  "childQueries": [
    {
      "alias": "recent_orders",
      "from": "shop_order_item",
      "using": "product",
      "columns": "order_date, quantity, total_price",
      "orderBy": "order_date DESC",
      "limit": 5,
      "filters": {
        "rules": [
          { "field": "order_date", "operator": "last_30_days" }
        ]
      }
    }
  ]
}

Example: Child Query with Aggregations

{
  "columns": "id, name, order_stats",
  "childQueries": [
    {
      "alias": "order_stats",
      "from": "shop_order",
      "using": "customer",
      "calculations": [
        { "field": "id", "func": "count", "name": "total_orders" },
        { "field": "amount", "func": "sum", "name": "total_spent" }
      ]
    }
  ]
}

---

Expand

expandTypes (Deprecated)

Type: ("user" | "enum" | "relation")[] | null

Automatically expand all columns of the specified field types. Replaced by expand.

{
  "expandTypes": ["user", "relation"]
}

expand

Type: string[] | null

List of specific field slugs to expand. Expanded fields return their full object representation instead of just the ID/value.

{
  "expand": ["record_owner", "related_account", "status"]
}

---

Query Mode

Parameter: queryMode — "OLTP" | "OLAP" | "EXPORT" Default: "OLTP"

---

Distinct Columns

Parameter: distinctColumns — string[] | null

List of columns to deduplicate results on. Use only for simple queries when you need exactly one deterministic row per group and the winner is defined by a simple ORDER BY.

Important: Do not use distinctColumns together with calculations. Prefer calculations to aggregate data.

Example: Last Invoice Date per Client

{
  "columns": "...client(client_name:name), invoice_date",
  "distinctColumns": ["client"],
  "orderBy": "invoice_date DESC"
}

Example: Deduplicate by Email

{
  "columns": "email, name",
  "distinctColumns": ["email"]
}

---

Full Count

Parameter: fullCount — boolean

When true, returns the total count of records matching the filters using a window function, alongside the paginated results.

{
  "columns": "id, name",
  "limit": 10,
  "offset": 0,
  "fullCount": true
}

---

Cursor-Based Sync

Used for incremental data synchronization, fetching only records modified within the cursor window.

{
  "cursorDateStart": "2025-10-01T00:00:00Z",
  "cursorDateEnd": "2025-10-02T00:00:00Z"
}

---

Filter Operators Reference

Basic Comparison

Text Search

Collection

Null/Empty Checks

Boolean

User-Related

Record Sharing

Follower-Related

Date Shortcuts

Dynamic Date Operators (require value)

---

Allowed Functions Reference

Postgres Functions

Postgres Literals (used as raw SQL)

current_date, current_time, current_timestamp

---

Allowed Aggregates Reference

Supported aggregate functions:

---

Allowed Cast Types

Valid types for the cast property in block formulas and numberType in calculations:

int, int[], int2, int2[], int4, int4[], int8, int8[], bigint, bigint[], real, real[], float, float[], float4, float4[], float8, float8[], numeric, numeric[], double, double[], decimal, decimal[], money, money[], timestamp, timestamp[], timestamptz, timestamptz[], date, date[], time, time[], interval, interval[], bool, bool[], boolean, boolean[], uuid, uuid[], text, text[]

---

Complete Examples

Example 1: Full-Featured Select Query

Fetch tasks with filters, sorting, pagination, and relation expansion:

{
  "dataSourceFullSlug": "crm_task",
  "columns": "id, task_name, ...record_owner(owner_name:name, owner_email:email), ...related_account(account_name:name)",
  "filters": {
    "combinator": "and",
    "rules": [
      { "field": "task_status", "operator": "in", "value": [1, 2] },
      { "field": "due_date", "operator": "in_next_x_days", "value": 7 },
      { "field": "record_owner", "operator": "in_active_user_team" }
    ]
  },
  "orderBy": "due_date ASC, task_name ASC",
  "limit": 50,
  "offset": 0,
  "fullCount": true
}

Example 2: Aggregation Dashboard

Monthly sales report grouped by category:

{
  "dataSourceFullSlug": "shop_order",
  "columns": "months_of_year@created_on, ...category(cat:name)",
  "calculations": [
    { "field": "id", "func": "count", "name": "order_count" },
    { "field": "total_amount", "func": "sum", "name": "revenue" },
    { "field": "total_amount", "func": "avg", "name": "avg_order" }
  ],
  "filters": {
    "rules": [
      { "field": "created_on", "operator": "this_year" },
      { "field": "order_status", "operator": "!=", "value": "cancelled" }
    ]
  },
  "orderBy": "months_of_year@created_on ASC"
}

Example 3: Pivot — Weekly Sales by Salesperson

{
  "dataSourceFullSlug": "shop_order",
  "columns": "...order_status(status_name:name)",
  "pivot": {
    "matrix": [
      {
        "using": "created_on",
        "columns": "week:to_char[IYYY-IW]@created_on",
        "dateRange": {
          "interval": "week",
          "min": "2025-01-01T00:00:00Z",
          "max": "2025-03-31T23:59:59Z"
        },
        "spread": true
      },
      {
        "using": "salesperson",
        "columns": "sp_name:name",
        "spread": true
      }
    ],
    "orderBy": "week ASC"
  },
  "calculations": [
    { "field": "id", "func": "count", "name": "deals" },
    { "field": "amount", "func": "sum", "name": "revenue" }
  ]
}

Example 4: Child Queries — Customers with Orders and Tickets

{
  "dataSourceFullSlug": "crm_customer",
  "columns": "id, name, email, recent_orders, open_tickets",
  "childQueries": [
    {
      "alias": "recent_orders",
      "from": "shop_order",
      "using": "customer",
      "columns": "id, order_date, total_amount, ...status(status_label:name)",
      "orderBy": "order_date DESC",
      "limit": 10,
      "filters": {
        "rules": [
          { "field": "order_date", "operator": "last_90_days" }
        ]
      }
    },
    {
      "alias": "open_tickets",
      "from": "support_ticket",
      "using": "customer",
      "columns": "id, subject, priority, created_on",
      "orderBy": "created_on DESC",
      "limit": 5,
      "filters": {
        "rules": [
          { "field": "status", "operator": "!=", "value": "closed" }
        ]
      }
    }
  ],
  "filters": {
    "rules": [
      { "field": "status", "operator": "=", "value": "active" }
    ]
  },
  "limit": 25
}

Example 5: Formulas — Computed Columns with Subquery

Fetch accounts with an inline profit margin formula and a subquery counting active deals:

{
  "dataSourceFullSlug": "crm_account",
  "columns": "id, name, profit_margin, active_deals",
  "formulas": {
    "profit_margin": {
      "inputs": [{
        "kind": "math",
        "op": "*",
        "inputs": [
          {
            "kind": "math",
            "op": "/",
            "inputs": [
              {
                "kind": "math",
                "op": "-",
                "inputs": [
                  { "kind": "column", "name": "revenue" },
                  { "kind": "column", "name": "cost" }
                ]
              },
              { "kind": "column", "name": "revenue", "cast": "decimal" }
            ]
          },
          { "kind": "literal", "literal": 100 }
        ]
      }]
    },
    "active_deals": {
      "from": "crm_deal",
      "with": "account",
      "filters": {
        "rules": [
          { "field": "stage", "operator": "!=", "value": "lost" },
          { "field": "stage", "operator": "!=", "value": "won" }
        ]
      },
      "inputs": [{
        "kind": "aggregate",
        "name": "count",
        "inputs": []
      }]
    }
  },
  "orderBy": "profit_margin DESC",
  "limit": 20
}

Example 6: Combined Pivot + Calculations + Filters

Daily hourly breakdown of support tickets per agent for today:

{
  "dataSourceFullSlug": "support_ticket",
  "columns": "...priority(priority_name:name)",
  "pivot": {
    "matrix": [
      {
        "using": "created_on",
        "columns": "hour:hours_of_today@created_on",
        "dateRange": {
          "interval": "hour",
          "min": "2025-10-15T00:00:00Z",
          "max": "2025-10-15T23:59:59Z"
        },
        "spread": true
      },
      {
        "using": "assigned_agent",
        "columns": "agent:name",
        "spread": true,
        "filters": {
          "rules": [
            { "field": "is_active", "operator": "true" }
          ]
        }
      }
    ],
    "hideEmptyRows": false
  },
  "calculations": [
    { "field": "id", "func": "count", "name": "ticket_count" }
  ],
  "filters": {
    "rules": [
      { "field": "created_on", "operator": "today" }
    ]
  }
}

Example 7: Complex Nested Filters

{
  "dataSourceFullSlug": "crm_deal",
  "columns": "id, name, amount, stage, record_owner(name)",
  "filters": {
    "combinator": "and",
    "rules": [
      {
        "field": "amount",
        "operator": ">",
        "value": 10000
      },
      {
        "combinator": "or",
        "rules": [
          {
            "combinator": "and",
            "rules": [
              { "field": "stage", "operator": "=", "value": "negotiation" },
              { "field": "created_on", "operator": "this_month" }
            ]
          },
          {
            "combinator": "and",
            "rules": [
              { "field": "stage", "operator": "=", "value": "proposal" },
              { "field": "record_owner", "operator": "active_user" }
            ]
          }
        ]
      },
      {
        "field": "rel_account/industry",
        "operator": "in",
        "value": ["technology", "finance", "healthcare"]
      }
    ]
  },
  "orderBy": "amount DESC",
  "limit": 100
}

Example 8: CASE Formula with Multiple Conditions

{
  "dataSourceFullSlug": "crm_deal",
  "columns": "id, name, amount, deal_tier",
  "formulas": {
    "deal_tier": {
      "inputs": [{
        "kind": "case",
        "cases": [
          {
            "when": {
              "kind": "compare", "op": ">=",
              "left": { "kind": "column", "name": "amount" },
              "right": { "kind": "literal", "literal": 100000 }
            },
            "then": { "kind": "literal", "literal": "Enterprise" }
          },
          {
            "when": {
              "kind": "compare", "op": ">=",
              "left": { "kind": "column", "name": "amount" },
              "right": { "kind": "literal", "literal": 25000 }
            },
            "then": { "kind": "literal", "literal": "Mid-Market" }
          },
          {
            "when": {
              "kind": "compare", "op": ">=",
              "left": { "kind": "column", "name": "amount" },
              "right": { "kind": "literal", "literal": 5000 }
            },
            "then": { "kind": "literal", "literal": "SMB" }
          }
        ],
        "else": { "kind": "literal", "literal": "Micro" }
      }]
    }
  }
}

Example 9: Date Formatting with Block Formula

{
  "dataSourceFullSlug": "crm_activity",
  "columns": "id, subject, formatted_date, formatted_time",
  "formulas": {
    "formatted_date": {
      "inputs": [{
        "kind": "function",
        "name": "to_char",
        "inputs": [
          { "kind": "column", "name": "created_on" },
          { "kind": "literal", "literal": "DD Mon YYYY" }
        ]
      }]
    },
    "formatted_time": {
      "inputs": [{
        "kind": "function",
        "name": "to_char",
        "inputs": [
          { "kind": "column", "name": "created_on" },
          { "kind": "literal", "literal": "HH24:MI" }
        ]
      }]
    }
  },
  "orderBy": "created_on DESC",
  "limit": 50
}

Example 10: Distinct Count with Min/Max Bounds

{
  "dataSourceFullSlug": "shop_order",
  "columns": "category",
  "calculations": [
    {
      "field": "id",
      "func": "count",
      "name": "total_orders"
    },
    {
      "field": "amount",
      "func": "sum",
      "name": "valid_revenue",
      "minValue": 0,
      "maxValue": 1000000
    },
    {
      "field": "amount",
      "func": "avg",
      "name": "avg_amount",
      "numberType": "decimal"
    },
    {
      "field": "product_code",
      "func": "count",
      "name": "unique_products",
      "isDistinct": true
    }
  ]
}

Developer Tools

OpenAPI Specification

• Auto-generated, tenant-specific OpenAPI specs reflecting all configured data sources and fields
• Regeneratable on-demand after schema changes
• Powers client SDK generation and API discovery

MCP Server (Model Context Protocol)

• Built-in MCP server exposing platform capabilities as AI-consumable tools
• Discovery, CRUD, querying, enum management, custom query execution, and JSONata evaluation — all accessible via MCP transport

CLI

• Full-featured CLI (@docyrus/docyrus) for terminal and AI agent use
• Data & schema: data operations (ds, full query engine + comments + file attachments), read-only logical SQL with schema discovery (dsql: query + schema app/data-source/data-sources), schema management (studio: data sources, fields, enums, data views, forms, webforms, HTML/PDF/DOCX export templates, email templates, plus tenant-wide field/enum search), automation management (automation: automation, trigger, and action node CRUD)
• App & AI configuration: app management including AI agent context and app-scoped AI tools (apps), custom AI agent builder with full sub-resource CRUD — models, tools, data sources, docs, MCPs, connections, tasks, recurring tasks, workflow steps, deployments, and workflow jobs (agent)
• Messaging, connectors & discovery: tenant email account discovery and transactional send (messaging), connector discovery, provider-auth requests, and action runs (connect), OpenAPI discovery (discover), direct API requests (curl)
• Agent runtime & dev tooling: platform AI chat (docy), pi Cowork/Coding agents (opsy/cody/coder), agent bridge server (server), browser automation (browser), repo knowledge graph (knowledge), project plan graph (project-plan), and release management (release)
• Multi-account, multi-tenant session management; named environments (not API_BASE_URL); OpenAPI discovery with caching and fallback generation; interactive TUI mode

For full CLI command reference, see docyrus-cli-usage.md.

Client Libraries

• REST API client (@docyrus/api-client) with OAuth2 support, interceptors, streaming, and file operations
• React authentication provider (@docyrus/signin) with standalone OAuth2 PKCE and iframe postMessage modes, automatic current-user fetch from /v1/users/me, and hasRole / hasPermission / refreshUser helpers
• Framework-agnostic authorization helpers are also available from @docyrus/signin/core
• Auto-generated collection hooks from OpenAPI specs for data fetching integration

Docyrus CLI Usage

Complete command reference for the Docyrus CLI (@docyrus/docyrus).

Global Flags

• -g, --global — Use global ~/.docyrus settings instead of local project settings
• --format <toon|json|yaml|md|jsonl> — Output format
• --llms — Print the full LLM-readable manifest
• --mcp — Start as an MCP stdio server

Flag forms: --help prints flags in kebab-case (--app-slug, --from-file); the parser also accepts the camelCase schema keys (--appSlug, --fromFile). Both work.

---

env — Environments

The CLI uses saved named environments, not API_BASE_URL.

Built-in: live (prod alias) → https://api.docyrus.com, beta, alpha, dev (local-development alias) → https://localhost:3366.

---

auth — Authentication

docyrus auth login

Authorize CLI using OAuth2 device flow or manual token entry.

Client ID resolution order: explicit --clientId > DOCYRUS_API_CLIENT_ID env var > local config > global config > manual-token fallback.

Default scopes: openid email profile offline_access ReadWrite.All Architect.ReadWrite.All Automations.Run Reports.Run.CustomQuery Messaging.Email.Send Messaging.Sms.Send Messaging.Whatsapp.Send MCP.Connect

docyrus auth set-tokens

Set custom access and refresh tokens for the active environment.

docyrus auth accounts list

List saved user accounts for the current API base URL.

docyrus auth accounts use

Switch active account by user ID.

docyrus auth tenants list

List available tenants for an account.

docyrus auth tenants use <tenantSelector>

Switch active tenant for an account.

Note: Numeric selector = tenant number, non-numeric = UUID.

docyrus auth logout

Revoke and clear all tenant sessions for the active account.

docyrus auth who

Return current authenticated user (/v1/users/me).

docyrus auth tenant

Return the active tenant record (GET /v1/tenant/current, scope Tenant.Read). Read-only passthrough with no flags — returns id, no, name, accountStatus, product/subscription references, seat counts, paymentChannel, trial/subscription dates, and onboardingStatus. Distinct from the auth tenants (plural) account-management group.

Sandbox / CI token helpers

Mostly invoked by the sandbox runtime; all default --appId to DOCYRUS_SANDBOX_APP_ID.

---

docy — AI Agent Chat

docyrus docy "<prompt>"

Send a single prompt to the platform's main AI agent. (Previously docyrus ai.)

Output behavior:

• TTY mode: renders markdown for human readability
• --json, --verbose, or --format: preserves structured output

The pi agent launchers docyrus opsy (Cowork Agent) and docyrus cody / docyrus coder (Coding Agent), plus the docyrus server bridge, are covered under Dev Workflow Tooling.

---

browser — Browser Automation

Browser automation commands (local Chrome on :9222 or a remote Cloudflare session). Commands return JSON with a mode field ("local" or "remote").

---

ds — Data Source Item Operations

docyrus ds get <appSlug> <dataSourceSlug>

Get data source metadata, including its fields.

docyrus ds list <appSlug> <dataSourceSlug>

List data source items with the supported query parameters.

Most frequently used options:

Advanced options:

docyrus ds create <appSlug> <dataSourceSlug>

Create data source item(s).

Notes:

• Array payloads trigger bulk create (max 50 items per batch)
• Supports JSON and CSV input files

docyrus ds update <appSlug> <dataSourceSlug> [recordId]

Update data source item(s).

Notes:

• Batch update requires id in every item
• Cannot provide both recordId and batch payload

docyrus ds delete <appSlug> <dataSourceSlug> <recordId>

Delete a data source item.

docyrus ds comments create <appSlug> <dataSourceSlug> <recordId>

Create a record-scoped comment.

Notes:

• Use either --message or --data / --fromFile
• --data and --fromFile cannot be mixed with field-specific flags

docyrus ds files upload <appSlug> <dataSourceSlug> <recordId>

Upload a record-scoped file attachment.

Notes:

• Uploads use multipart/form-data
• Content type is inferred from the file extension when omitted

---

dsql — Logical SQL & Schema Discovery

Read-only logical SQL over tables named appSlug.dataSourceSlug (e.g. base.contact), plus token-efficient schema discovery. Requires DS.Read.* / DS.ReadWrite.* scopes.

docyrus dsql query [query]

Run a read-only PostgreSQL-compatible SELECT (PUT /v1/dsql/query). The SQL is resolved from the positional argument, then --from-file, then stdin. Throttled to 60 requests/minute. Returns { data, meta: { count } }.

docyrus dsql generate [question]

Generate a DSQL query from a natural-language question using the base DSQL generator agent (POST /v1/ai/agents/:agentId/chat). Returns the query text only (does not run it). The question is resolved from the positional argument, then --from-file, then stdin. Returns { query, prompt }.

docyrus dsql ask [question]

Generate a DSQL query from a natural-language question, run it via PUT /v1/dsql/query, and return the rows (generate + query). Returns { query, prompt, data, meta: { count } }.

docyrus dsql schema app <appSlug>

Return the DSQL schema of every queryable data source in the app (GET /v1/dsql/schema/apps/:appSlug).

docyrus dsql schema data-source <appSlug> <dataSourceSlug>

Return the DSQL schema of a single data source (GET /v1/dsql/schema/apps/:appSlug/data-sources/:dataSourceSlug).

docyrus dsql schema data-sources

Return the DSQL schema for the data sources matching the given ids (GET /v1/dsql/schema/data-sources?ids=...).

---

discover — OpenAPI Discovery

docyrus discover api

Download tenant OpenAPI spec for the active tenant. Caches locally for subsequent use.

docyrus discover namespaces

List API namespaces from the active tenant's OpenAPI spec.

docyrus discover path <prefix>

List endpoints matching a path prefix.

Note: Auto-normalizes paths with or without /v1 prefix.

docyrus discover endpoint <selector>

Return full endpoint details for a path and HTTP method.

Examples:

• /v1/users/me — defaults to GET
• [PUT]/v1/users/me/photo — explicit PUT method

docyrus discover entity <name>

Return full entity schema by name.

docyrus discover search <query>

Search endpoint paths and entity names.

---

connect — Connector & Action Commands

docyrus connect list-connectors

List available integration connectors.

docyrus connect get-connector <slug>

Get connector details with data sources and actions.

docyrus connect get-action <slug> <actionKey>

Get connector action details including input/output schemas.

docyrus connect list-connections <slug>

Get tenant and user connections for a connector.

docyrus connect curl <slug> <endpoint>

Send an HTTP request through a connector's provider auth.

docyrus connect run-action <slug> <actionKey>

Run a connector action directly by provider slug + action key via POST /v1/connectors/:slug/actions/:actionKey/run. (To run a persisted app action, use apps actions run.)

---

apps — App Management

docyrus apps list

List apps (/v1/apps). Mutations route through /v1/dev/apps/:appId.

docyrus apps update

PATCH /v1/dev/apps/:appId. Convenience flags (--name, --slug, --description, --icon, --color, --status, --betaUrl, --chromeExtensionPath, --mobileVersionPath, --agentContext, --routePath) merge over --data/--fromFile. Store fields and array/object values must go through --data/--fromFile. --status ∈ {active, design, development, draft, inactive}.

docyrus apps set-agent-context

Set an app's freeform AI agent context (PATCH /v1/dev/apps/:appId agent_context). Provide exactly one of --value (inline), --fromFile (text/markdown), or --clear.

docyrus apps actions

CRUD over standalone tenant_action rows (/v1/dev/apps/:appId/actions), plus the action-type picker and run. All commands take --appId/--appSlug.

Convenience flags cover the common columns (--status, --connectionId, --connectionAccountId, --requestMethod, --customEndpoint, --batch, …). JSON-shaped fields (--options, --conditions, --customHeaders, --inputTransformer, --inputTemplate, --inputJsonSchema, --outputJsonSchema, …) are parsed as JSON; the long tail can go through --data/--fromFile.

docyrus apps ai-tools

CRUD over app-scoped tenant_ai_tool rows (/v1/dev/apps/:appId/ai-tools). All commands take --appId/--appSlug.

Convenience flags cover the common columns (--description, --icon, --type, --clientSideExecution, --needsApproval, …). JSON-shaped fields (--inputJsonSchema, --outputJsonSchema, --customQueryFilters, --dataSourceQueryColumns, …) are parsed as JSON; the long tail can go through --data/--fromFile. Platform-managed fields (group, avatar, restricted, cost, development_status, owner_product_id, core_action_id, core_data_provider_id) are not settable on app-scoped tools.

docyrus apps delete

Archive an app (soft delete).

Note: Exactly one of --appId or --appSlug required.

docyrus apps restore

Restore an archived app.

docyrus apps permanent-delete

Permanently delete an app.

---

agent — Custom AI Agents

CRUD for dev-app custom agents and their sub-resources (/v1/dev/apps/:appId/agents...). The parent agent is --agentId; an individual sub-resource row is --id. This is distinct from the pi-agent launchers (opsy/cody/coder).

Agent resource

create/update accept --data/--fromFile (JSON) plus camelCase convenience flags mapping 1:1 onto Create*Dto/Update*Dto snake_case keys (--name, --description, --instructions, --defaultAiModelId, --temperature, --maxTokens, --supportTools, --supportDataSources, --supportFiles, --supportKnowledgeBase, --supportWebSearch, …). JSON flags (--instructionSchema, --inputFormSchema, --memoryOptions, …) are parsed as JSON; list flags (--standardSuggestions, --supportedFileFormats) are comma-separated. The long tail goes through --data/--fromFile.

Sub-resource groups

Each supports list/get/create/update/delete (unless noted); all take --appId/--appSlug and --agentId, row commands take --id.

createOnly flags appear only on create; updateOnly (e.g. --archived) only on update. delete returns { deleted: true, id }.

---

studio — Schema Management

Manage data source schemas, fields, enumerations, saved views, forms, public webforms, HTML/PDF/DOCX export templates, and email templates via the development API.

Common selector rules:

• App: exactly one of --appId or --appSlug
• Data source: exactly one of --dataSourceId or --dataSourceSlug (where supported)
• Field: exactly one of --fieldId or --fieldSlug (where supported)

Data Source Commands

docyrus studio list-data-sources

docyrus studio get-data-source

Returns the data source metadata together with its fields.

docyrus studio create-data-source

docyrus studio update-data-source

Same options as create-data-source plus data source selector (--dataSourceId / --dataSourceSlug).

docyrus studio delete-data-source

Archive a data source.

docyrus studio restore-data-source

Restore an archived data source. Requires --dataSourceId (slug resolution not available for archived data sources).

docyrus studio permanent-delete-data-source

Permanently delete a data source. Requires --dataSourceId.

docyrus studio bulk-create-data-sources

Field Commands

docyrus studio list-fields

docyrus studio get-field

docyrus studio create-field

docyrus studio update-field

Same options as create-field plus field selector (--fieldId / --fieldSlug).

docyrus studio delete-field

docyrus studio create-fields-batch

docyrus studio update-fields-batch

Same options as create-fields-batch.

Note: The CLI auto-normalizes payloads: id → fieldId, read_only → readOnly, default_value → defaultValue, relation_data_source_id → relationDataSourceId, options → editorOptions.

docyrus studio delete-fields-batch

Same options. Payload key: fieldIds.

Enum Commands

docyrus studio list-enums

docyrus studio create-enums

docyrus studio update-enums

Same options as create-enums (without --enumSetId).

Note: The CLI auto-normalizes id → enumId.

docyrus studio delete-enums

Same options. Payload key: enumIds.

Search Commands

Tenant-wide, paged search across schema objects — useful for discovery and refactors.

Data View Commands

Saved view definitions for a data source. Routes through /v1/apps/:appSlug/data-sources/:dataSourceSlug/views. App and data source can be supplied as id or slug — the CLI resolves whichever side is missing.

docyrus studio list-data-views

docyrus studio get-data-view

Same selectors as list-data-views, plus --viewId (required).

docyrus studio create-data-view

docyrus studio update-data-view

Same options as create-data-view, plus --viewId (required) and --archived (boolean).

docyrus studio delete-data-view

Same selectors as list-data-views, plus --viewId (required).

Form Commands

Data source form definitions used by record-entry UIs. Routes through /v1/apps/:appSlug/data-sources/:dataSourceSlug/forms.

docyrus studio list-forms

docyrus studio get-form

Same selectors as list-forms, plus --formId (required).

docyrus studio create-form

docyrus studio update-form

Same options as create-form, plus --formId (required) and --archived (boolean).

docyrus studio delete-form

Same selectors as list-forms, plus --formId (required).

Webform Commands

Public-facing webforms. Routes through /v1/dev/webforms. CRUD by --webformId. List and create accept either --dataSourceId or --dataSourceSlug (slug requires --appId or --appSlug to resolve).

docyrus studio list-webforms

docyrus studio get-webform

docyrus studio create-webform

When dataSourceId is omitted, submissions land in the tenant-schema webform_record table instead of a data source.

docyrus studio update-webform

docyrus studio delete-webform

HTML Template Commands

HTML/PDF/DOCX export templates. Routes through /v1/dev/html-templates. CRUD by --templateId. Data source binding is required on create; slug requires --appId or --appSlug.

docyrus studio list-html-templates

docyrus studio get-html-template

docyrus studio create-html-template

docyrus studio update-html-template

Same options as create-html-template, plus --templateId (required).

docyrus studio delete-html-template

Email Template Commands

Transactional email templates. Routes through /v1/dev/email-templates. CRUD by --templateId. Data source binding is optional.

docyrus studio list-email-templates

docyrus studio get-email-template

docyrus studio create-email-template

docyrus studio update-email-template

Same options as create-email-template, plus --templateId (required).

docyrus studio delete-email-template

---

automation — Automations, Triggers, and Action Nodes

Manage automations, their triggers, and their action nodes for a tenant app. All commands route through /v1/dev/apps/:appId/automations.

Common selector rules:

• App: exactly one of --appId or --appSlug
• Automation: --automationId (no slug — automations and nodes are ID-only)
• Trigger --type (URL kebab-case): record-created, record-modified, record-deleted, recurrence, app-event, webhook, emailhook, webform, button-activation, manual-activation
• Node --type (URL kebab-case): external-action, send-email, send-notification, create-record, update-records, request-approval, request-input, http-request, data-source-query, custom-query, generate-document, ai-prompt, ai-agent, execute-script, wait-for

Write payload rules:

• Write commands accept --data '<json>' or --from-file ./payload.json (JSON only)
• Convenience flags are camelCase and are converted to snake_case in the request body
• Nested objects (trigger data; node data, field_mapping, dynamic_field_mapping, condition, input_template, input_transformer, custom_headers, pre_action_request, post_action_request, target_data_source_condition) must be supplied via --data / --from-file — the CLI does not flatten them
• delete, delete-trigger, and delete-node return { deleted: true, id } (API itself returns 204)

Automation CRUD

docyrus automation list

docyrus automation get

docyrus automation create

Creates an automation together with its first trigger. --triggerType uses camelCase (recordCreated, recordModified, recordDeleted, recurrence, appEvent, webhook, emailhook, webform, buttonActivation, manualActivation) to match CreateAutomationDto.trigger_type.

docyrus automation update

docyrus automation delete

Trigger CRUD

docyrus automation list-triggers

Derived from the automation GET response.

docyrus automation get-trigger

Same options as list-triggers, plus --triggerId (required).

docyrus automation create-trigger

Routes through POST .../triggers/:type.

docyrus automation update-trigger

Routes through PATCH .../triggers/:type/:triggerId. Same flags as create-trigger, plus --triggerId (required).

docyrus automation delete-trigger

Routes through DELETE .../triggers/:triggerId (type-independent).

Action Node CRUD

docyrus automation list-nodes

GET .../nodes

docyrus automation get-node

GET .../nodes/:nodeId

docyrus automation create-node

Routes through POST .../nodes/:type. external-action create requires --actionTypeId; the backend validates data against core_action.input_json_schema and provisions the tenant_action row in the same transaction.

wait-for nodes accept no flat convenience flags beyond the common base — supply data.delaySeconds (integer ≤ 30 days) or the data.delayValue + data.delayUnit pair (seconds / minutes / hours / days) via --data / --from-file. The action forwards input through unchanged and queues the next step(s) with a deferred tenant_job_queue.process_after.

docyrus automation create-node \
  --appSlug crm \
  --automationId 9c4f… \
  --type wait-for \
  --name "Wait 2 hours" \
  --parent <previous-node-id> \
  --data '{"data":{"delayValue":2,"delayUnit":"hours"}}'

docyrus automation update-node

Routes through PATCH .../nodes/:type/:nodeId. Same flags as create-node, plus --nodeId (required).

docyrus automation delete-node

Routes through DELETE .../nodes/:nodeId (type-independent).

---

messaging — Tenant Email Accounts and Send

List tenant email accounts and send transactional emails. Routes through /v1/messaging/email/* and requires the Messaging.Email.Send OAuth2 scope. Credentials are never returned.

docyrus messaging accounts

GET /messaging/email/accounts

Returns active tenant email accounts. Each item exposes id, name, provider, senderEmail, senderName, isUserAccessible, allowOverrideName, allowOverrideEmail, createdOn.

docyrus messaging email send

POST /messaging/email/accounts/:accountId/send

Limits: up to 50 addresses per recipient list, up to 10 attachments. Attachments are { filePath, fileName?, mimeType? } with filePath referencing a tenant-scoped storage path.

Response payload: { messageId, provider, accepted, rejected }.

---

curl — Direct API Requests

docyrus curl <path>

Send arbitrary requests to the Docyrus API.

Notes:

• Default method: GET (POST if -d provided)
• Path auto-normalizes /v1 prefix
• JSON payloads auto-detect Content-Type: application/json

---

Dev Workflow Tooling

Beyond data/schema operations, the CLI bundles the pi agent runtime and repo dev tooling. For full flags see docyrus <command> --help or the docyrus-cli-app skill.

pi Agents and Server

Launchers accept --provider, --model, --thinking, --continue, --resume, --session, --apiKey, and --print/--mode.

Repo Knowledge Graph (knowledge)

Manages the repo's docyrus/knowledge graph: init, generate-initial, refresh, search, section, locate, refs, expand, check, doctor, config, list-impacted, audit-staged, pre-commit, hook.

Project Plan (project-plan)

Repo-tracked plan graph (phases → features → tasks): ensure, check, config, show, summary, list-phases, list-features, list-tasks, find-tasks, get-task, upsert-phase, upsert-feature, upsert-task, set-order, set-task-status, create-linked-todo, upsert-from-architect, upsert-from-plan.

Releases (release)

Terminal UI

docyrus tui launches the OpenTUI interface (requires Bun); it reuses the existing CLI command graph.

---

Settings & Persistence

Storage Locations

Default settings root: ./.docyrus/ (local) or ~/.docyrus/ (global with -g).

Environment Variables

Integrations & Events

Connector Framework

• HTTP providers — OAuth2-enabled external API integrations (Stripe, Slack, Microsoft, etc.) with custom transformers for request/response mapping
• SQL providers — Direct database connections to external databases
• Microsoft integrations — SharePoint and MS Graph connectors

Provider Features

• OAuth2 credential management with automatic token refresh
• Webhook subscription management
• Configurable pagination
• Per-tenant and per-user credential isolation

Webhooks & Events

• Create and manage webhooks with signed verification
• Event types: data operations, chat platform messages, collaboration events
• Public webhook keys for external service integration (AI agent, bot, email, portal, webform)
• File upload support via webhooks
• Delivery tracking with retry logic

Content & Document Management

Collaborative Document Editor

• Real-time collaborative editing
• Document versioning and history
• Fragment-based document access
• Batch document import and version management

File Management

• File uploads with type validation and size limits
• Organized folder structures
• File metadata and association with records

Messaging & Notifications

In-App Messaging

• Thread-based conversations
• Message history with user mentions
• File sharing in messages
• Conversation channels embedded in data sources

Notifications

• Notification center with read/unread tracking
• Per-type notification preferences
• Device-based push notifications
• Email notification delivery

Email

• Transactional email templates (signup, password reset, invitations, magic links)
• Custom HTML email templates with dynamic content
• Email configuration management
• Delivery tracking and webhook integration
• Tenant email accounts with per-provider credentials, optional user-level identity override, and send-as-user enforcement
• Send endpoint POST /v1/messaging/email/accounts/{accountId}/send (scope Messaging.Email.Send) with multi-recipient to/cc/bcc/replyTo, HTML or text body, and up to 10 storage-backed attachments
• Account discovery via GET /v1/messaging/email/accounts (credentials are never returned; the response exposes provider, sender identity, and override flags)

Platform Services

Templates & Import/Export

App Templates

• Pre-built, searchable app templates with categories
• One-click app creation from templates

Data Import

• Bulk import via file upload (CSV, JSON)
• Template-based field mapping
• Web page import and scraping
• Migration support from external platforms (Airtable, Monday.com, Notion) with user mapping, field mapping, and batch processing

Webforms (Public Data Collection)

• Public-facing forms (no authentication required)
• Form field mapping to data sources
• Submission webhooks, CAPTCHA support, and response tracking

Reporting & Analytics

• Custom query templates with variable interpolation
• Runtime filter application and result pagination
• Aggregation calculations (count, sum, avg, min, max)
• Pivot tables and cross-tab matrices
• Report definitions with widget-based visualizations

Deployment & Versioning

• Application deployment with streaming logs and status monitoring
• Deployment history tracking
• Worker scripts with custom endpoint configuration
• Git repository integration for version control

Localization & Navigation

• Multi-language translation management per app
• Language-specific field labels and locale-aware formatting
• Custom navigation structures with nested menus, pinning, and favorites

Platform Administration

• Audit logging with row-level change tracking and user activity history
• Resource usage tracking and quota management
• Billing accounts with subscription management
• System configuration and super admin capabilities

Security

• Tenant data isolation
• Encrypted environment variables and secure credential storage
• Signed webhook verification
• OAuth2 with PKCE for all authentication flows
• File validation (type checking, size limits)
• Input validation at all API boundaries

Querying & Data Operations

Record CRUD

• Create, read, update, delete individual records
• Bulk create, bulk update, bulk delete (batched for performance)
• Insert/update/delete with custom return value selection

Record Comments & Files

• Record-scoped comments with create, list, fetch by ID, update, and delete operations
• Comment payloads can include threading (parentId), assignee targeting (assignedTo), attachments metadata, level, status, and done state
• Record-scoped file attachments with upload, list, fetch by ID, insert-without-upload, copy/move, and delete operations
• File uploads support multipart form data, public/private storage selection, and record association

Query Engine

Every list/get call accepts a structured query payload:

• Column selection — Pick specific fields, alias them, spread related data, apply functions
• Filtering — 50+ operators across comparison, text matching, date shortcuts (today, this_week, last_30_days), user-relative filters (active_user, in_active_user_scope), array containment, and null checks. Filter groups support AND/OR/NOT nesting
• Sorting — Multi-field, directional
• Pagination — Limit/offset with optional full count
• Aggregations — count, sum, avg, min, max with grouping
• Formulas — Virtual computed columns via JSONata expressions
• Pivot — Cross-tab matrix generation with date range series support
• Child queries — Fetch related records as nested JSON arrays in a single request
• Expand — Return full objects for relation and enum fields instead of IDs
• Keyword search — Full-text search across multiple fields

Detailed References

• For complete query payload reference with all parameters, operators, and examples, see data-source-query-guide.md.
• For block formula design (inline expressions and correlated subqueries), see formula-design-guide-llm.md.