Docyrus Api Dev

Docyrus API Developer

Integrate with the Docyrus API using @docyrus/api-client (REST client) and @docyrus/signin (React auth provider). Authenticate via OAuth2 PKCE, query data sources with powerful filtering/aggregation, and consume REST endpoints.

Authentication Quick Start

React Apps — Use @docyrus/signin

import { DocyrusAuthProvider, useDocyrusAuth, useDocyrusClient, SignInButton } from '@docyrus/signin'

// 1. Wrap root
<DocyrusAuthProvider
  apiUrl={import.meta.env.VITE_API_BASE_URL}
  clientId={import.meta.env.VITE_OAUTH2_CLIENT_ID}
  redirectUri={import.meta.env.VITE_OAUTH2_REDIRECT_URI}
  scopes={['offline_access', 'Read.All', 'DS.ReadWrite.All', 'Users.Read']}
  callbackPath="/auth/callback"
>
  <App />
</DocyrusAuthProvider>

// 2. Use hooks
function App() {
  const { status, signOut } = useDocyrusAuth()
  const client = useDocyrusClient()  // RestApiClient | null

  if (status === 'loading') return <Spinner />
  if (status === 'unauthenticated') return <SignInButton />

  // client is ready — make API calls
  const user = await client!.get('/v1/users/me')
}

Non-React / Server — Use OAuth2Client Directly

import { RestApiClient, OAuth2Client, OAuth2TokenManagerAdapter, BrowserOAuth2TokenStorage } from '@docyrus/api-client'

const tokenStorage = new BrowserOAuth2TokenStorage(localStorage)
const oauth2 = new OAuth2Client({
  baseURL: 'https://api.docyrus.com',
  clientId: 'your-client-id',
  redirectUri: 'http://localhost:3000/callback',
  usePKCE: true,
  tokenStorage,
})

// Auth Code flow
const { url } = await oauth2.getAuthorizationUrl({ scope: 'openid offline_access Users.Read' })
window.location.href = url
// After redirect:
const tokens = await oauth2.handleCallback(window.location.href)

// Create API client with auto-refresh
const client = new RestApiClient({
  baseURL: 'https://api.docyrus.com',
  tokenManager: new OAuth2TokenManagerAdapter(tokenStorage, async () => {
    return (await oauth2.refreshAccessToken()).accessToken
  }),
})

API Endpoints

Data Source Items (Dynamic per tenant)

GET    /v1/apps/{appSlug}/data-sources/{slug}/items          — List with query payload
GET    /v1/apps/{appSlug}/data-sources/{slug}/items/{id}     — Get one
POST   /v1/apps/{appSlug}/data-sources/{slug}/items          — Create
PATCH  /v1/apps/{appSlug}/data-sources/{slug}/items/{id}     — Update
DELETE /v1/apps/{appSlug}/data-sources/{slug}/items/{id}     — Delete one
DELETE /v1/apps/{appSlug}/data-sources/{slug}/items          — Delete many (body: { recordIds })

Endpoints exist only if the data source is defined in the tenant. Check the tenant's OpenAPI spec at GET /v1/api/openapi.json.

System Endpoints (Always Available)

GET    /v1/users          — List users
POST   /v1/users          — Create user
GET    /v1/users/me       — Current user profile
PATCH  /v1/users/me       — Update current user

Connector Discovery & External Request Endpoints

GET    /v1/connectors?q=&limit=&offset=                                    — List connectors with keyword search
GET    /v1/connectors/{dataProviderSlug}                                    — Get connector detail (dataSources + actions)
GET    /v1/connectors/{dataProviderSlug}/actions/{actionKey}                — Get action detail (input/output schemas, API endpoint)
GET    /v1/connectors/{dataProviderSlug}/connections                        — Get tenant connections + user connection status
PUT    /v1/connectors/{dataProviderSlug}                                    — Send HTTP request through connector provider auth

Scopes: Read.All, ReadWrite.All, or Connectors.Read.All. The PUT endpoint requires ReadWrite.All.

PUT request body for sending requests through a connector:

{
  "endpoint": "relative/path/or/absolute-url",
  "requestMethod": "GET",
  "data": { "fields": "id,name", "limit": 20 },
  "contentType": "application/json",
  "headers": { "Authorization": "Bearer <override-token>" },
  "connectionId": "optional-tenant-connection-uuid",
  "connectionAccountId": "optional-connection-account-uuid"
}

The connector resolves auth credentials (OAuth tokens, base URL) from the provider configuration and stored connections. Custom headers.Authorization overrides the stored token.

Action Run Endpoints

GET    /v1/apps/base/actions                                               — List base actions
GET    /v1/apps/{appSlug}/actions/{actionSlug}                             — Get action metadata
POST   /v1/apps/{appSlug}/actions/{actionSlug}/run                         — Run action directly

Action run accepts arbitrary JSON body as input. Optional headers: x-connection-id, x-connection-account-id.

Studio (Dev) Schema Endpoints

The studio surface manages dev-app schema objects. Most routes are gated by the Architect.Read.All / Architect.ReadWrite.All scopes, and the app is identified by its tenant app_id UUID.

# Apps (mutations only — list uses /v1/apps)
DELETE /v1/dev/apps/{appId}                                                 — Archive app
POST   /v1/dev/apps/{appId}/restore                                         — Restore archived app
DELETE /v1/dev/apps/{appId}/permanent                                       — Permanently delete app

# Data sources
GET    /v1/dev/apps/{appId}/data-sources                                    — List data sources (?expand=fields,...)
GET    /v1/dev/apps/{appId}/data-sources/{dataSourceId}                     — Get data source
POST   /v1/dev/apps/{appId}/data-sources                                    — Create data source
PATCH  /v1/dev/apps/{appId}/data-sources/{dataSourceId}                     — Update data source
DELETE /v1/dev/apps/{appId}/data-sources/{dataSourceId}                     — Archive data source
POST   /v1/dev/apps/{appId}/data-sources/{dataSourceId}/restore             — Restore archived data source
DELETE /v1/dev/apps/{appId}/data-sources/{dataSourceId}/permanent           — Permanently delete data source
POST   /v1/dev/apps/{appId}/data-sources/bulk                               — Bulk create (body: { dataSources })

# Fields
GET    /v1/dev/apps/{appId}/data-sources/{dataSourceId}/fields                       — List fields
GET    /v1/dev/apps/{appId}/data-sources/{dataSourceId}/fields/{fieldId}             — Get field
POST   /v1/dev/apps/{appId}/data-sources/{dataSourceId}/fields                       — Create field
PATCH  /v1/dev/apps/{appId}/data-sources/{dataSourceId}/fields/{fieldId}             — Update field
DELETE /v1/dev/apps/{appId}/data-sources/{dataSourceId}/fields/{fieldId}             — Delete field
POST   /v1/dev/apps/{appId}/data-sources/{dataSourceId}/fields/batch                 — Bulk create (body: { fields })
PATCH  /v1/dev/apps/{appId}/data-sources/{dataSourceId}/fields/batch                 — Bulk update (body: { fields[].fieldId })
DELETE /v1/dev/apps/{appId}/data-sources/{dataSourceId}/fields/batch                 — Bulk delete (body: { fieldIds })

# Field enums
GET    /v1/dev/apps/{appId}/data-sources/{dataSourceId}/fields/{fieldId}/enums       — List enum options
POST   /v1/dev/apps/{appId}/data-sources/{dataSourceId}/fields/{fieldId}/enums       — Create enums (body: { enums })
PATCH  /v1/dev/apps/{appId}/data-sources/{dataSourceId}/fields/{fieldId}/enums       — Update enums (body: { enums[].enumId })
DELETE /v1/dev/apps/{appId}/data-sources/{dataSourceId}/fields/{fieldId}/enums       — Delete enums (body: { enumIds })

# Data views (saved views) — slug-scoped
GET    /v1/apps/{appSlug}/data-sources/{dataSourceSlug}/views                        — List views
GET    /v1/apps/{appSlug}/data-sources/{dataSourceSlug}/views/{viewId}               — Get view
POST   /v1/apps/{appSlug}/data-sources/{dataSourceSlug}/views                        — Create view
PUT    /v1/apps/{appSlug}/data-sources/{dataSourceSlug}/views/{viewId}               — Update view
DELETE /v1/apps/{appSlug}/data-sources/{dataSourceSlug}/views/{viewId}               — Delete view

# Forms (record-entry layouts) — slug-scoped
GET    /v1/apps/{appSlug}/data-sources/{dataSourceSlug}/forms                        — List forms
GET    /v1/apps/{appSlug}/data-sources/{dataSourceSlug}/forms/{formId}               — Get form
POST   /v1/apps/{appSlug}/data-sources/{dataSourceSlug}/forms                        — Create form
PUT    /v1/apps/{appSlug}/data-sources/{dataSourceSlug}/forms/{formId}               — Update form
DELETE /v1/apps/{appSlug}/data-sources/{dataSourceSlug}/forms/{formId}               — Delete form

# Webforms (public-facing forms)
GET    /v1/dev/webforms                                                              — List webforms (?dataSourceId)
GET    /v1/dev/webforms/{webformId}                                                  — Get webform
POST   /v1/dev/webforms                                                              — Create webform
PATCH  /v1/dev/webforms/{webformId}                                                  — Update webform
DELETE /v1/dev/webforms/{webformId}                                                  — Delete webform

# HTML / PDF / DOCX export templates
GET    /v1/dev/html-templates                                                        — List (?dataSourceId,&isDefault,&limit,&offset)
GET    /v1/dev/html-templates/{templateId}                                           — Get template
POST   /v1/dev/html-templates                                                        — Create template
PUT    /v1/dev/html-templates/{templateId}                                           — Update template
DELETE /v1/dev/html-templates/{templateId}                                           — Delete template

# Email templates
GET    /v1/dev/email-templates                                                       — List (?dataSourceId,&limit,&offset)
GET    /v1/dev/email-templates/{templateId}                                          — Get template
POST   /v1/dev/email-templates                                                       — Create template
PUT    /v1/dev/email-templates/{templateId}                                          — Update template
DELETE /v1/dev/email-templates/{templateId}                                          — Delete template

Notes:

• The bulk update DTOs do not mirror the list/get response shapes. Send fields[].fieldId and enums[].enumId (not id).
• A webform created without dataSourceId posts submissions into the tenant-schema webform_record table instead of a data source.
• Archived data sources cannot be reliably resolved by slug; use the ID for restore and permanent routes.

Automation Endpoints

Tenant-app automation CRUD plus typed trigger and action node mutations. Gated by Architect.Read.All / Architect.ReadWrite.All.

# Automations
GET    /v1/dev/apps/{appId}/automations                                              — List automations
GET    /v1/dev/apps/{appId}/automations/{id}                                         — Get automation (includes triggers)
POST   /v1/dev/apps/{appId}/automations                                              — Create automation + first trigger
PATCH  /v1/dev/apps/{appId}/automations/{id}                                         — Update automation (name, status, source_data_source_id)
DELETE /v1/dev/apps/{appId}/automations/{id}                                         — Delete automation (204)

# Triggers — typed create/update, type-independent delete
POST   /v1/dev/apps/{appId}/automations/{automationId}/triggers/{type}               — Create trigger
PATCH  /v1/dev/apps/{appId}/automations/{automationId}/triggers/{type}/{triggerId}   — Update trigger
DELETE /v1/dev/apps/{appId}/automations/{automationId}/triggers/{triggerId}          — Delete trigger (204)

# Action nodes — typed create/update, type-independent delete
GET    /v1/dev/apps/{appId}/automations/{automationId}/nodes                         — List nodes
GET    /v1/dev/apps/{appId}/automations/{automationId}/nodes/{nodeId}                — Get node
POST   /v1/dev/apps/{appId}/automations/{automationId}/nodes/{type}                  — Create node
PATCH  /v1/dev/apps/{appId}/automations/{automationId}/nodes/{type}/{nodeId}         — Update node
DELETE /v1/dev/apps/{appId}/automations/{automationId}/nodes/{nodeId}                — Delete node (204)

Trigger {type} values (kebab-case URL segments): record-created, record-modified, record-deleted, recurrence, app-event, webhook, emailhook, webform, button-activation, manual-activation.

Action node {type} values: 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.

POST /v1/dev/apps/{appId}/automations accepts trigger_type in camelCase (e.g. recordCreated, recordModified, recordDeleted, recurrence, appEvent, webhook, emailhook, webform, buttonActivation, manualActivation) on CreateAutomationDto. The typed trigger CRUD endpoints use the kebab-case form in the URL.

Request bodies use snake_case keys (e.g. source_data_source_id, max_run_per_record, modified_columns, recurrence_frequency, core_data_provider_id, webhook_id, tenant_webform_id, action_type_id, field_mapping, dynamic_field_mapping, condition, input_template, input_transformer, custom_headers, pre_action_request, post_action_request, target_data_source_condition).

Important: creating a node with type=external-action requires action_type_id (maps to core_action.id). The backend validates the supplied data against core_action.input_json_schema and inserts the linked tenant_action row in the same transaction.

Action / Approval RPC (Production)

These are separate from the dev-app automation CRUD above. They drive the runtime engine.

PUT  /v1/automation/processAction                                                    — Execute action payload (IActionPayload)
POST /v1/automation/exchange-rates    (alias /v1/automation/syncExchangeRates)       — Fetch/save FX rates (admin or api)
PUT  /v1/automation/sendApprovalRequests                                             — { approvalStatusFieldId, recordId }
PUT  /v1/automation/sendApprovalResponse                                             — Approve response
PUT  /v1/automation/sendApprovalRevisionRequest                                      — Approval revision request
PUT  /v1/automation/sendPushNotification/{notificationId}                            — Push notification (admin or api)

Messaging Endpoints

Tenant email accounts and transactional send. All routes require the Messaging.Email.Send OAuth2 scope.

GET  /v1/messaging/email/accounts                                                    — List active tenant email accounts (no credentials)
POST /v1/messaging/email/accounts/{accountId}/send                                   — Send email through an account

POST /v1/messaging/email/accounts/{accountId}/send body (SendEmailDto):

{
  "to": ["user@example.com"],
  "cc": ["manager@example.com"],
  "bcc": ["audit@example.com"],
  "replyTo": ["support@example.com"],
  "subject": "Daily summary",
  "body": "<p>Hello</p>",
  "sendAsUser": false,
  "attachments": [
    { "filePath": "records/abc/attachments/foo.pdf", "fileName": "foo.pdf", "mimeType": "application/pdf" }
  ]
}

Limits: to/cc/bcc/replyTo accept up to 50 RFC-5322 addresses each, subject is capped at 998 characters, body at 1 000 000 characters, attachments at 10 items, and filePath at 2048 characters. sendAsUser only takes effect when the account allows it (see allowOverrideName / allowOverrideEmail from the accounts list).

EmailAccountDto (returned by list) exposes: id, name, provider, senderEmail, senderName, isUserAccessible, allowOverrideName, allowOverrideEmail, createdOn. Credentials, tokens, and provider secrets are never returned.

SendEmailResponseDto: { messageId, provider, accepted, rejected }.

ACL / Role Management Endpoints

GET    /v1/users/acl?dataSourceId={uuid}&recordId={uuid}   — Read record ACL rows
POST   /v1/users/acl/share                                 — Upsert record shares
DELETE /v1/users/acl/share                                 — Revoke record shares
PUT    /v1/users/acl/owner                                 — Transfer record ownership

GET    /v1/users/acl/roles                                 — List roles
GET    /v1/users/acl/roles/{roleId}                        — Get one role
POST   /v1/users/acl/roles                                 — Create role
PATCH  /v1/users/acl/roles/{roleId}                        — Update role
DELETE /v1/users/acl/roles/{roleId}                        — Delete role

GET    /v1/users/acl/user-roles                            — List user-role assignments
GET    /v1/users/acl/users/{userId}/roles                  — List one user's roles
POST   /v1/users/acl/users/{userId}/roles                  — Add roles to a user
PUT    /v1/users/acl/users/{userId}/roles                  — Replace a user's full role set
DELETE /v1/users/acl/users/{userId}/roles/{roleId}         — Remove one role assignment

GET    /v1/users/acl/role-queries                          — List role queries
GET    /v1/users/acl/role-queries/{roleQueryId}            — Get one role query
POST   /v1/users/acl/role-queries                          — Create role query
PATCH  /v1/users/acl/role-queries/{roleQueryId}            — Update role query
DELETE /v1/users/acl/role-queries/{roleQueryId}            — Delete role query

ACL routes require the normal authenticated API session, but they may not appear in generated Swagger/OpenAPI output because the backend currently excludes them from public docs. Integrate them with direct RestApiClient calls when you need record sharing, role CRUD, user-role assignment management, or role-query management.

For all ACL role operations, prefer using role uid values returned by the API. Nested role objects expose both id and uid, and both map to the role UID value.

Making API Calls

// List items with query payload
const items = await client.get('/v1/apps/base/data-sources/project/items', {
  columns: 'name, status, record_owner(firstname,lastname)',
  filters: { rules: [{ field: 'status', operator: '!=', value: 'archived' }] },
  orderBy: 'created_on DESC',
  limit: 50,
})

// Get single item
const item = await client.get('/v1/apps/base/data-sources/project/items/uuid-here', {
  columns: 'name, description, status',
})

// Create
const newItem = await client.post('/v1/apps/base/data-sources/project/items', {
  name: 'New Project',
  status: 'status-enum-id',
})

// Update
await client.patch('/v1/apps/base/data-sources/project/items/uuid-here', {
  name: 'Updated Name',
})

// Delete
await client.delete('/v1/apps/base/data-sources/project/items/uuid-here')

Query Payload Summary

The GET items endpoint accepts a powerful query payload:

For full query and formula references, read:

• references/data-source-query-guide.md
• references/formula-design-guide-llm.md

Critical Rules

1. Always send `columns` in list/get calls. Without it, only id is returned. 2. Data source endpoints are dynamic — they exist only for data sources defined in the tenant. 3. Use `id` field for count calculations. Use the actual field slug for sum, avg, min, max. 4. Child query keys must appear in `columns` — if childQuery key is orders, include orders in columns. 5. Formula keys must appear in `columns` — if formula key is total, include total in columns. 6. Filter by related field using rel_{{relation_field}}/{{field}} syntax. 7. ACL routes may be hidden from generated OpenAPI — call them directly via RestApiClient instead of expecting generated collection support. 8. Prefer role `uid` values for ACL role writes, user-role roleIds, and role-query roleIds. 9. Treat `PUT /v1/users/acl/users/:userId/roles` as full replacement and POST /v1/users/acl/users/:userId/roles as additive. 10. Send role-query `query` as raw JSON and let backend derive tenantAppId from dataSourceId when applicable. 11. After deleting a role, refresh dependent ACL state — role lists, user-role lists, role-query lists, and any UI showing primary-role labels. 12. Studio bulk update DTOs use scoped IDs — send fields[].fieldId and enums[].enumId (not id) for the PATCH .../fields/batch and PATCH .../enums routes; the list/get response shapes do not match the bulk update DTOs. 13. Automation request bodies use `snake_case` keys (e.g. source_data_source_id, field_mapping). Trigger and node create/update URLs are typed (/triggers/<type>, /nodes/<type>), but delete URLs are type-independent. POST /automations accepts trigger_type in camelCase (recordCreated, etc.) while typed trigger routes use kebab-case (record-created, etc.). 14. `external-action` automation nodes require `action_type_id` — the backend validates the supplied data against core_action.input_json_schema and creates the matching tenant_action row in the same transaction. 15. Messaging endpoints require the `Messaging.Email.Send` scope and never return credentials. sendAsUser only takes effect when the listed account allows the override.

References

Read these files when you need detailed information:

• `references/api-client.md` — Full RestApiClient API, OAuth2Client (all flows: PKCE, client credentials, device code), token managers, interceptors, error classes, SSE/streaming, file upload/download, HTML to PDF, retry logic
• `references/authentication.md` — @docyrus/signin React provider, useDocyrusAuth/useDocyrusClient hooks, hasRole/hasPermission authorization helpers, SignInButton, standalone vs iframe auth modes, env vars, API client access pattern
• `references/data-source-query-guide.md` — Up-to-date query payload guide: columns, filters, orderBy, pagination, calculations, formulas, child queries, pivots, and operator reference
• `references/formula-design-guide-llm.md` — Up-to-date formula design guide for building and validating formulas payloads
• `references/acl-endpoints-frontend.md` — Hidden ACL endpoint reference covering record sharing, roles, user-role assignment flows, role queries, identifier rules, and expected frontend integration behavior

ACL Endpoints for Frontend Developers

Base path: /api/v1/users/acl

All ACL endpoints require the normal authenticated API session.

These endpoints may be hidden from generated Swagger/OpenAPI output because the backend currently marks them with @ApiExcludeEndpoint(). Treat this document as the frontend integration source of truth and call these routes directly with RestApiClient or useDocyrusClient().

Important identifier rules

• Role assignments and ACL role relations are stored using tenant_role.uid.
• Returned nested role objects expose both id and uid, and both values map to the role UID.
• For role operations, backend can resolve incoming roleId values against both tenant_role.uid and tenant_role.id, but frontend apps should prefer role uid values from API responses.
• For user-role writes and role-query roleIds, send role UUIDs and prefer UID values.
• tenant_role_query.query is a JSON object matching the app's filter-query structure. Send raw JSON, not stringified JSON.

Enum values

Role ownership

• APP
• CUSTOM
• PRODUCT
• SYSTEM
• USER

Role query restriction level

• hidden
• read-only
• not-deletable

Endpoint groups

1) Record ACL endpoints

These manage record-level shares, not role CRUD.

Share payload notes

• principalType must be one of: user, team, role, tenant, public.
• permissions is the backend ACL bitmask value.
• expiresAt is optional and must be a valid ISO date if provided.

Record ACL response shapes

GET /v1/users/acl returns:

{
  "direct": [
    {
      "id": "uuid",
      "principal_type": "user",
      "principal_id": "uuid",
      "permissions": 7,
      "expires_at": null,
      "created_by": "uuid-or-null",
      "created_on": "2026-03-29T20:10:00.000Z"
    }
  ],
  "effective": [
    {
      "id": "uuid",
      "user_id": "uuid",
      "permissions": 7,
      "source_principal_type": "role",
      "source_principal_id": "uuid"
    }
  ]
}

2) Role endpoints

Role create/update rules

• slug is required on create and must be unique within the tenant.
• Create defaults:
• ownership = "CUSTOM"
• privileges = ""
• status = 1
• Role responses include both id and uid, both pointing to the role UID.

Role delete side effects

Deleting a role also cleans up dependent ACL state:

• removes tenant_user_role assignments for that role
• sets tenant_user.primary_role to null where applicable
• removes linked tenant_acl_rule rows
• removes linked tenant_acl_field_rule rows
• removes the role from tenant_role_query role arrays
• hard deletes role-query rows that become empty after role removal

Frontend implication: after role deletion, refresh role lists, user-role lists, role-query lists, and any UI showing primary-role labels.

3) User-role endpoints

User-role behavior

• GET /v1/users/acl/user-roles accepts optional userId and roleId filters.
• If roleId is supplied, backend resolves it to the canonical role UID first.
• POST /users/:userId/roles is additive.
• POST /users/:userId/roles safely ignores duplicate assignments.
• PUT /users/:userId/roles is a full replacement operation.
• Sending roleIds: [] to PUT /users/:userId/roles clears all additional roles for that user.

4) Role-query endpoints

Role queries are role-based filtering rules attached to roles and optionally scoped to a specific data source.

Role-query rules

• roleIds is required on create and must contain at least one UUID.
• Stored role IDs are normalized to role UIDs.
• query is required on create and must be a JSON object.
• filterChildRelations defaults to false.
• restrictionLevel defaults to hidden.
• If dataSourceId is provided, backend derives tenantAppId automatically.
• If dataSourceId changes during update, backend recalculates tenantAppId.

Common request examples

Sync a user's complete role set

{
  "roleIds": ["role-uid-uuid-1", "role-uid-uuid-2"]
}

Create a role query

{
  "name": "Hide archived records",
  "dataSourceId": "data-source-uuid",
  "roleIds": ["role-uid-uuid"],
  "query": {
    "condition": "and",
    "filters": []
  },
  "filterChildRelations": false,
  "restrictionLevel": "hidden"
}

Share a record

{
  "dataSourceId": "uuid",
  "recordId": "uuid",
  "items": [
    {
      "principalType": "user",
      "principalId": "uuid",
      "permissions": 7,
      "expiresAt": "2026-12-31T00:00:00.000Z"
    }
  ]
}

Error expectations

Common backend error patterns:

• 400 Bad Request
• malformed UUID in path, query, or body
• missing required DTO fields
• invalid enum values
• invalid boolean or date format in share payloads
• 404 Not Found
• role not found
• role query not found
• tenant user not found
• tenant data source not found
• one or more submitted role IDs could not be resolved
• 409 Conflict
• role slug already exists in the tenant
• 500 Internal Server Error
• unexpected persistence failure

Frontend integration recommendations

• Use direct RestApiClient calls or useDocyrusClient() for ACL work; these routes may not be present in generated OpenAPI or collection layers.
• Prefer role uid values from API responses for future writes and filters.
• Treat PUT /users/:userId/roles as the canonical full-sync endpoint.
• Treat POST /users/:userId/roles as an additive convenience endpoint.
• Send role-query query values as raw JSON objects.
• Omit tenantAppId when sending a role query scoped by dataSourceId; backend derives it.
• After deleting a role, invalidate and refetch role lists, user-role lists, role-query lists, and any dependent role-label UI.

Suggested TypeScript interfaces

export interface IAclRole {
  activitySummaryReportQueryId: string | null;
  createdBy: string | null;
  createdOn: string | null;
  databaseId: string | null;
  disableLogin: number | null;
  id: string;
  lastModifiedBy: string | null;
  lastModifiedOn: string | null;
  name: string;
  ownership: "APP" | "CUSTOM" | "PRODUCT" | "SYSTEM" | "USER";
  privileges: string;
  slug: string;
  status: number | null;
  tenantAppId: string | null;
  uid: string;
}

export interface IAclUserRoleAssignment {
  createdOn: string | null;
  id: string;
  role: {
    databaseId: string | null;
    id: string;
    name: string;
    slug: string;
    uid: string;
  };
  roleId: string;
  status: number | null;
  userId: string;
}

export interface IAclRoleQuery {
  createdBy: string | null;
  createdOn: string | null;
  dataSourceId: string | null;
  filterChildRelations: boolean;
  id: string;
  lastModifiedBy: string | null;
  lastModifiedOn: string | null;
  name: string | null;
  query: Record<string, unknown> | null;
  restrictionLevel: "hidden" | "read-only" | "not-deletable";
  roleIds: string[];
  tenantAppId: string | null;
}

export interface IAclRecordShare {
  id: string;
  principal_type: "user" | "team" | "role" | "tenant" | "public";
  principal_id: string;
  permissions: number;
  expires_at: string | null;
  created_by: string | null;
  created_on: string | null;
}

export interface IAclEffectiveUserAccess {
  id: string;
  user_id: string;
  permissions: number;
  source_principal_type: "user" | "team" | "role" | "tenant" | "public";
  source_principal_id: string;
}

@docyrus/api-client Reference

Table of Contents

1. RestApiClient 2. HTTP Methods 3. Configuration 4. Token Management 5. OAuth2Client 6. Interceptors 7. Error Handling 8. Streaming 9. File Operations 10. Utilities

---

RestApiClient

import { RestApiClient, MemoryTokenManager } from '@docyrus/api-client'

const client = new RestApiClient({
  baseURL: 'https://api.docyrus.com',
  tokenManager: new MemoryTokenManager(),
  timeout: 5000,
  headers: { 'X-API-Version': '1.0' },
})

---

HTTP Methods

// GET with query params
const users = await client.get<User[]>('/v1/users', { params: { page: 1, limit: 10 } })

// POST with body
const newUser = await client.post<User>('/v1/users', { name: 'John', email: 'john@example.com' })

// PATCH (partial update)
const updated = await client.patch<User>('/v1/users/123', { name: 'Jane' })

// PUT (full replace)
await client.put('/v1/users/123', { name: 'Jane', email: 'jane@example.com' })

// DELETE
await client.delete('/v1/users/123')

// DELETE with body
await client.delete('/v1/items', { recordIds: ['id1', 'id2'] })

Typed Responses

interface ApiResponse<T> { data: T; meta: { page: number; total: number } }
const response = await client.get<ApiResponse<User[]>>('/v1/users')
const users: User[] = response.data.data

---

Configuration

interface ApiClientConfig {
  baseURL?: string                      // Base URL for all requests
  tokenManager?: TokenManager           // Token manager instance
  headers?: Record<string, string>      // Default headers
  timeout?: number                      // Request timeout in ms
  fetch?: typeof fetch                  // Custom fetch implementation
  FormData?: typeof FormData            // Custom FormData
  AbortController?: typeof AbortController
  storage?: Storage                     // Browser storage for persistence
}

---

Token Management

MemoryTokenManager (default)

import { MemoryTokenManager } from '@docyrus/api-client'
const tokenManager = new MemoryTokenManager()

StorageTokenManager (persistent)

import { StorageTokenManager } from '@docyrus/api-client'
const tokenManager = new StorageTokenManager(localStorage, 'auth_token')

AsyncTokenManager (custom)

import { AsyncTokenManager } from '@docyrus/api-client'
const tokenManager = new AsyncTokenManager({
  async getToken() { return await secureStorage.get('token') },
  async setToken(token) { await secureStorage.set('token', token) },
  async clearToken() { await secureStorage.remove('token') },
})

Set Token Directly

await client.setAccessToken('your-auth-token')

---

OAuth2Client

Full OAuth2 support with PKCE, Device Code, and Client Credentials flows.

Setup

import { OAuth2Client, BrowserOAuth2TokenStorage } from '@docyrus/api-client'

const oauth2 = new OAuth2Client({
  baseURL: 'https://api.docyrus.com',
  clientId: 'your-client-id',
  clientSecret: 'your-client-secret',    // optional for public clients
  redirectUri: 'http://localhost:3000/callback',
  defaultScopes: ['openid', 'offline_access'],
  usePKCE: true,                          // default: true
  tokenStorage: new BrowserOAuth2TokenStorage(localStorage),
})

Authorization Code Flow (PKCE)

// Step 1: Generate auth URL
const { url, state, codeVerifier } = await oauth2.getAuthorizationUrl({
  scope: 'openid offline_access Users.Read',
})

// Step 2: Redirect user
window.location.href = url

// Step 3: Handle callback
const tokens = await oauth2.handleCallback(window.location.href)
// tokens: { accessToken, refreshToken, ... }

Client Credentials Flow (server-to-server)

const tokens = await oauth2.getClientCredentialsToken({
  scope: 'Read.All',
  delegatedUserId: 'user-id-to-impersonate',
})

Device Code Flow (CLI/headless)

const deviceAuth = await oauth2.startDeviceAuthorization('openid offline_access')
console.log(`Go to: ${deviceAuth.verification_uri}`)
console.log(`Enter code: ${deviceAuth.user_code}`)

const tokens = await oauth2.pollDeviceAuthorization(
  deviceAuth.device_code, deviceAuth.interval, deviceAuth.expires_in,
  { onExpired: () => console.log('Code expired'), signal: abortController.signal },
)

Token Operations

const tokens = await oauth2.getTokens()
const isExpired = await oauth2.isTokenExpired()
const accessToken = await oauth2.getValidAccessToken()  // auto-refreshes
const newTokens = await oauth2.refreshAccessToken()
await oauth2.revokeToken(tokens.refreshToken)
const tokenInfo = await oauth2.introspectToken(tokens.accessToken)
await oauth2.logout()

Integrate OAuth2 with RestApiClient

import { RestApiClient, OAuth2Client, OAuth2TokenManagerAdapter, BrowserOAuth2TokenStorage } from '@docyrus/api-client'

const tokenStorage = new BrowserOAuth2TokenStorage(localStorage)
const oauth2 = new OAuth2Client({ baseURL: 'https://api.docyrus.com', clientId: 'id', tokenStorage })

const tokenManager = new OAuth2TokenManagerAdapter(tokenStorage, async () => {
  const tokens = await oauth2.refreshAccessToken()
  return tokens.accessToken
})

const apiClient = new RestApiClient({ baseURL: 'https://api.docyrus.com', tokenManager })

Rate Limit Check

const rateLimit = await oauth2.checkRateLimit()
// { remaining, limit, reset }

PKCE Utilities

import { generatePKCEChallenge, generateCodeVerifier, generateCodeChallenge, generateState, generateNonce } from '@docyrus/api-client'

const pkce = await generatePKCEChallenge()
// { codeVerifier, codeChallenge, codeChallengeMethod: 'S256' }

---

Interceptors

client.use({
  // Transform outgoing requests
  async request(config) {
    config.headers = { ...config.headers, 'X-Request-Time': new Date().toISOString() }
    return config
  },
  // Transform incoming responses
  async response(response, request) {
    console.log(`${request.url} took ${Date.now() - request.timestamp}ms`)
    return response
  },
  // Handle errors globally
  async error(error, request, response) {
    if (error.status === 401) { await refreshToken() }
    return { error, request, response }
  },
})

Common Interceptor: Unwrap Response Data

client.use({
  response: (response) => {
    if (response.data?.data && typeof response.data === 'object' && !Array.isArray(response.data)) {
      response.data = response.data.data
    }
    return response
  },
})

---

Error Handling

import {
  ApiError, NetworkError, TimeoutError,
  AuthenticationError,   // 401
  AuthorizationError,    // 403
  NotFoundError,         // 404
  RateLimitError,        // 429 — has error.retryAfter
  ValidationError,
  // OAuth2-specific
  OAuth2Error, InvalidGrantError, InvalidClientError,
  AccessDeniedError, ExpiredTokenError, AuthorizationPendingError,
} from '@docyrus/api-client'

try {
  await client.get('/resource')
} catch (error) {
  if (error instanceof AuthenticationError) { /* re-login */ }
  else if (error instanceof AuthorizationError) { /* forbidden */ }
  else if (error instanceof NotFoundError) { /* 404 */ }
  else if (error instanceof RateLimitError) { /* retry after error.retryAfter */ }
  else if (error instanceof NetworkError) { /* offline */ }
  else if (error instanceof TimeoutError) { /* timed out */ }
}

---

Streaming

Server-Sent Events (SSE)

const eventSource = client.sse('/events', {
  onMessage(data) { console.log('Received:', data) },
  onError(error) { console.error(error) },
  onComplete() { console.log('Stream completed') },
})
eventSource.close()

Chunked Streaming

for await (const chunk of client.stream('/stream', {
  method: 'POST',
  body: { query: 'stream data' },
})) {
  console.log('Chunk:', chunk)
}

---

File Operations

Upload

const formData = new FormData()
formData.append('file', fileInput.files[0])
formData.append('description', 'My file')
await client.post('/upload', formData)

Download

const response = await client.get('/download/file.pdf', { responseType: 'blob' })
const url = URL.createObjectURL(response.data)
const link = document.createElement('a')
link.href = url
link.download = 'file.pdf'
link.click()

HTML to PDF

await client.html2pdf({
  html: '<html><body>Content</body></html>',
  // or: url: 'https://example.com',
  options: { format: 'A4', margin: { top: 10, bottom: 10, left: 10, right: 10 }, landscape: false },
})

Custom Query/Report

const results = await client.runCustomQuery(customQueryId, options)
// PUT reports/runCustomQuery/:customQueryId

---

Utilities

import { buildUrl, isAbortError, parseContentDisposition, createAbortSignal, jsonToQueryString, withRetry } from '@docyrus/api-client'

const url = buildUrl('/api/users', { page: 1, limit: 10 })
// '/api/users?page=1&limit=10'

const signal = createAbortSignal(5000) // 5s timeout

const response = await withRetry(() => client.get('/flaky'), {
  retries: 3, retryDelay: 1000,
  retryCondition: (error) => error.status >= 500,
})

@docyrus/signin — React Authentication Reference

Table of Contents

1. Overview 2. Installation 3. DocyrusAuthProvider 4. Auth Hooks 5. Authorization (Roles & Permissions) 6. SignInButton 7. Auth Modes 8. Environment Variables 9. App Integration Pattern 10. Advanced Usage

---

Overview

@docyrus/signin provides "Sign in with Docyrus" for React apps. Auto-detects environment:

• Standalone: OAuth2 Authorization Code + PKCE via page redirect
• Iframe: Receives tokens via window.postMessage from *.docyrus.app hosts

Peer dependencies: react >= 18, @docyrus/api-client >= 0.0.10

---

Installation

pnpm add @docyrus/signin @docyrus/api-client

---

DocyrusAuthProvider

Wrap application root:

import { DocyrusAuthProvider } from '@docyrus/signin'

<DocyrusAuthProvider
  apiUrl="https://alpha-api.docyrus.com"
  clientId="your-oauth2-client-id"
  redirectUri="http://localhost:3000/auth/callback"
  scopes={['offline_access', 'Read.All', 'DS.ReadWrite.All', 'Users.Read']}
  callbackPath="/auth/callback"
>
  <App />
</DocyrusAuthProvider>

Props

---

Auth Hooks

useDocyrusAuth()

Full authentication context:

import { useDocyrusAuth } from '@docyrus/signin'

const {
  status,        // 'loading' | 'authenticated' | 'unauthenticated'
  mode,          // 'standalone' | 'iframe'
  client,        // RestApiClient | null — configured API client with tokens
  tokens,        // { accessToken, refreshToken, ... } | null
  user,          // DocyrusUser | null — auto-fetched from /v1/users/me
  signIn,        // () => void — redirects to Docyrus login page
  signOut,       // () => void — logout and clear tokens
  hasRole,       // (role: string | string[]) => boolean — check role by slug or uid
  hasPermission, // (operation: string, dataSourceId?: string) => boolean — check ACL permission
  refreshUser,   // () => Promise<void> — re-fetch user from API
  error,         // Error | null
} = useDocyrusAuth()

useDocyrusClient()

Shorthand for just the API client:

import { useDocyrusClient } from '@docyrus/signin'

const client = useDocyrusClient() // RestApiClient | null

if (client) {
  const user = await client.get('/v1/users/me')
  const items = await client.get('/v1/apps/base/data-sources/project/items', queryPayload)
}

---

Authorization (Roles & Permissions)

The provider auto-fetches the current user from /v1/users/me after authentication and exposes hasRole and hasPermission helpers. The user object is null until the fetch completes (shortly after status becomes 'authenticated').

Role Checking

const { hasRole } = useDocyrusAuth()

hasRole(null)                      // true — no role requirement
hasRole('super_admin')             // checks slug or uid match
hasRole(['editor', 'reviewer'])    // true if user has any of these roles

Checks both primaryRole and all additional roles from the user object.

Permission Checking

const { hasPermission } = useDocyrusAuth()

hasPermission('view', dataSourceId)    // can view this data source?
hasPermission('edit', dataSourceId)    // can edit?
hasPermission('delete', dataSourceId)  // can delete?

Permission resolution order: 1. super_admin role → always granted 2. global_editor role → granted for: view, create, edit, delete, create_bulk, export, import, print 3. global_viewer role → granted only for: view 4. Always-permitted system data sources (reports, todos, notes, etc.) 5. User's aclRules array (merged from all roles by the server)

Pure Functions (Framework-Agnostic)

import { hasRole, hasPermission } from '@docyrus/signin/core'
import type { DocyrusUser } from '@docyrus/signin/core'

// Use with any user object (e.g., server-side, tests)
hasRole(user, 'super_admin')
hasPermission(user, 'edit', 'some-ds-id')

Refreshing User Data

const { refreshUser } = useDocyrusAuth()
await refreshUser() // re-fetch after role/permission changes

---

SignInButton

Unstyled button. Automatically hidden when authenticated or in iframe mode.

import { SignInButton } from '@docyrus/signin'

// Basic
<SignInButton />

// Styled
<SignInButton className="btn btn-primary" label="Log in with Docyrus" />

// Render prop for full control
<SignInButton>
  {({ signIn, isLoading }) => (
    <button onClick={signIn} disabled={isLoading}>
      {isLoading ? 'Redirecting...' : 'Sign in with Docyrus'}
    </button>
  )}
</SignInButton>

---

Auth Modes

Standalone (OAuth2 PKCE)

For apps running directly in the browser:

1. User clicks sign-in 2. Page redirects to Docyrus authorization endpoint 3. After login, redirects back with authorization code 4. Provider automatically exchanges code for tokens 5. Tokens stored in localStorage, auto-refreshed before expiry

Iframe (postMessage)

For apps embedded in an iframe on *.docyrus.app:

1. Provider detects iframe environment and validates host origin 2. Host sends { type: 'signin', accessToken, refreshToken } via postMessage 3. Provider creates API client with received tokens 4. When tokens expire, provider sends { type: 'token-refresh-request' } to host 5. Host responds with fresh tokens

---

Environment Variables

# .env
VITE_API_BASE_URL=https://localhost:3366
VITE_OAUTH2_CLIENT_ID=your-client-id
VITE_OAUTH2_REDIRECT_URI=http://localhost:3000/auth/callback
VITE_OAUTH2_SCOPES=openid profile offline_access Users.Read DS.ReadWrite.All

Access in code: import.meta.env.VITE_API_BASE_URL

---

App Integration Pattern

Minimal Setup (main.tsx)

import { StrictMode } from 'react'
import { createRoot } from 'react-dom/client'
import { DocyrusAuthProvider } from '@docyrus/signin'

const scopes = (import.meta.env.VITE_OAUTH2_SCOPES || '').split(' ').filter(Boolean)

createRoot(document.getElementById('root')!).render(
  <StrictMode>
    <DocyrusAuthProvider
      apiUrl={import.meta.env.VITE_API_BASE_URL}
      clientId={import.meta.env.VITE_OAUTH2_CLIENT_ID}
      redirectUri={import.meta.env.VITE_OAUTH2_REDIRECT_URI}
      scopes={scopes}
      callbackPath="/auth/callback"
    >
      <App />
    </DocyrusAuthProvider>
  </StrictMode>,
)

Auth-Gated App (App.tsx)

import { useDocyrusAuth, useDocyrusClient, SignInButton } from '@docyrus/signin'

function App() {
  const { status, signOut } = useDocyrusAuth()
  const client = useDocyrusClient()

  if (status === 'loading') return <div>Loading...</div>
  if (status === 'unauthenticated') return <SignInButton />

  // client is guaranteed non-null when authenticated
  return (
    <div>
      <p>Authenticated!</p>
      <button onClick={() => client!.get('/v1/users/me').then(console.log)}>My Profile</button>
      <button onClick={signOut}>Sign Out</button>
    </div>
  )
}

Accessing the API Client

In React components, use useDocyrusClient() to get the authenticated client. Generated collections are hooks that call useDocyrusClient() internally, so no manual client syncing is needed:

// Generated collections use useDocyrusClient() internally
const { list, get, create } = useBaseProjectCollection()

// For direct API access in React components
const client = useDocyrusClient()
const data = await client!.get('/v1/custom-endpoint')

---

Advanced Usage

Core classes and permission functions exported for advanced scenarios:

import { AuthManager, StandaloneOAuth2Auth, IframeAuth, detectAuthMode } from '@docyrus/signin'
import { hasRole, hasPermission, getAllRoles } from '@docyrus/signin/core'
import type { DocyrusUser, DocyrusRole, DocyrusAclRule, AclOperation, PermissionConfig } from '@docyrus/signin/core'

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
    }
  ]
}

SQL Block Formula Reference

Formula Types

Two block formula formats:

Block Inline — AST expression in SELECT: { alias?: string, inputs: IQueryFormulaBlock[] }. Detected by inputs without from/with.

Block Subquery — correlated subquery on child table: { alias?, inputs, from: string, with: string | Record<string,string>, filters?: IQueryFilterGroup }. Detected by from+with.

Block Schema

Top-level requires exactly 1 element in inputs[]. Optional alias becomes SQL alias.

Every block has optional tz?: string (timezone) and cast?: string (type cast). Processing: compile → tz → cast.

Block Kinds

literal

{ kind: "literal", literal: string|number|boolean|Date|null|Array }

• Scalars → parameterized $N. Arrays → ($1, $2, ...).
• Inside concat/concat_ws parent: auto-casts (::text, ::boolean, ::timestamptz, ::jsonb).

column

{ kind: "column", name: string|string[] }

• Advanced DS: "alias"."slug"
• Simple DS custom fields: "alias".data->>'<field-uuid>' with auto-cast by field type:
• number/money/duration(decimal≠false) → ::decimal, (decimal=false) → ::int
• DB types jsonb/date/time/timestamptz/boolean/int* → ::<type>, uuid[] → ::jsonb
• Simple DS static/system fields (in SIMPLE_STATIC_FIELD_SLUGS): direct reference. Field not found → error.

builtin

{ kind: "builtin", name: "current_date"|"current_time"|"current_timestamp"|"now" }

• Emitted as raw SQL. Other names → error.

function

{ kind: "function", name: string, inputs?: Block[] }

• Validated against allowed functions whitelist. Inputs compiled recursively, joined by commas.
• Gotcha: Literal auto-cast only works inside concat/concat_ws. For jsonb_build_object and other functions, add explicit "cast": "text" to string literal blocks or Postgres will fail to determine parameter types.

extract

{ kind: "extract", part: "year"|"month"|"day"|"hour"|"minute"|"second", inputs: [Block] }

• Exactly 1 input required.
• SQL: extract(<part> from <expr>)

aggregate

{ kind: "aggregate", name: "count"|"sum"|"avg"|"min"|"max"|"jsonb_agg"|"json_agg"|"array_agg", distinct?: boolean, inputs: Block[] }

• count with empty or omitted inputs → count(*). For count specifically, inputs is optional.
• distinct → DISTINCT keyword.

math

{ kind: "math", op: "+"|"-"|"*"|"/"|"%", inputs: Block[] }

• Min 2 operands. Left-associative with parens: ((a op b) op c).

case

{ kind: "case", cases: [{when: Block, then: Block}], else?: Block }

• Min 1 case required. else optional (defaults NULL).

compare

{ kind: "compare", op: "="|"!="|"<>"|">"|"<"|">="|"<="|"like"|"ilike"|"in"|"not in"|"not_in", left: Block, right: Block }

• in/not in: left in right. not_in accepted as alias but prefer "not in" (with space).
• ilike auto-converted to like for MySQL dialect.

boolean

{ kind: "boolean", op: "and"|"or"|"not", inputs: Block[] }

• not: exactly 1 input → not (<expr>). and/or: min 2 → ((<a>) op (<b>)).

Subquery Details

• from: child table full slug (appSlug_tableSlug), matched via dataSource.children.
• with (string): child field joins to parent id. with (object): { childField: parentField }.
• Simple child DS: table rewritten to tenant_record, fields use data->>'uuid' refs.
• Optional filters apply WHERE on child table.
• Child alias: t0_child. Parent alias: t0.

Allowed Functions (Postgres)

String: length, lower, upper, substr, replace, concat, trim, ltrim, rtrim, btrim, split_part, initcap, reverse, strpos, lpad, rpad

Number: abs, ceil, floor, round, sqrt, power, mod, gcd, lcm, exp, ln, log, log10, log1p, pi, sign, width_bucket, trunc, greatest, least

Date/Time: now, age, clock_timestamp, date_part, date_trunc, extract, isfinite, justify_days, justify_hours, make_date, make_time, make_timestamp, make_timestamptz, timeofday, to_timestamp, to_char, to_date, to_time

Utility: coalesce

JSON/JSONB: jsonb_array_length, jsonb_extract_path, jsonb_extract_path_text, jsonb_object_keys, jsonb_build_object, json_build_object, jsonb_agg, json_agg, array_agg, array_to_json, row_to_json

Aggregates: count, sum, avg, min, max, jsonb_agg, json_agg, array_agg

Cast Types

Allowed: int, int2, int4, int8, bigint, real, float, float4, float8, numeric, double, decimal, money, timestamp, timestamptz, date, time, interval, bool, boolean, uuid, text (+ array variants like int[], text[]).

Timezone

tz property: validated /^[a-zA-Z0-9_]+$/. SQL: <expr> at time zone '<tz>'. Column/function blocks omit outer parens.

Validation Errors

SelectQueryBuilder Integration

1. Formulas in ISelectQueryParams.formulas as Record<string, ISelectQueryFormula>. 2. Column alias matching formula key → formula replaces column ref in SELECT. 3. Dispatch: from/expression → buildBlockFormula() (subquery), inputs only → buildBlockFormula() (inline). 4. Calculations with func:"formula" also route through buildFormula(). 5. usedFormulas set prevents duplicate application across SELECT and aggregations. 6. Subquery formulas trigger async resolveChildDatasources() before build.

Examples

Inline math (balance / 100):

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

Formatted date (to_char):

{ "inputs": [{ "kind": "function", "name": "to_char", "inputs": [{ "kind": "column", "name": "created_on" }, { "kind": "literal", "literal": "DD/MM/YYYY" }] }] }

Subquery count:

{ "from": "app_child", "with": "parent_id", "inputs": [{ "kind": "aggregate", "name": "count", "inputs": [] }] }

Subquery count with distinct:

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

Multi-field subquery join:

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

CASE with AND:

{ "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" } }] }

Multi-branch CASE (tier assignment):

{ "inputs": [{ "kind": "case", "cases": [{ "when": { "kind": "compare", "op": ">=", "left": { "kind": "column", "name": "revenue" }, "right": { "kind": "literal", "literal": 100000 } }, "then": { "kind": "literal", "literal": "enterprise" } }, { "when": { "kind": "compare", "op": ">=", "left": { "kind": "column", "name": "revenue" }, "right": { "kind": "literal", "literal": 10000 } }, "then": { "kind": "literal", "literal": "business" } }, { "when": { "kind": "compare", "op": ">=", "left": { "kind": "column", "name": "revenue" }, "right": { "kind": "literal", "literal": 1000 } }, "then": { "kind": "literal", "literal": "starter" } }], "else": { "kind": "literal", "literal": "free" } }] }

Nested aggregate: round(sum(qty * price), 2):

{ "alias": "total", "inputs": [{ "kind": "function", "name": "round", "inputs": [{ "kind": "aggregate", "name": "sum", "inputs": [{ "kind": "math", "op": "*", "inputs": [{ "kind": "column", "name": "qty" }, { "kind": "column", "name": "price" }] }] }, { "kind": "literal", "literal": 2 }] }] }

Timezone: to_char(now() at time zone 'UTC', 'YYYY-MM-DD'):

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

COALESCE (null handling):

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

Subquery with filters (count active children):

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

String concatenation with initcap:

{ "inputs": [{ "kind": "function", "name": "initcap", "inputs": [{ "kind": "function", "name": "concat", "inputs": [{ "kind": "column", "name": "first_name" }, { "kind": "literal", "literal": " " }, { "kind": "column", "name": "last_name" }] }] }] }

Percentage with cast: round(completed/total * 100, 2):

{ "inputs": [{ "kind": "function", "name": "round", "inputs": [{ "kind": "math", "op": "*", "inputs": [{ "kind": "math", "op": "/", "inputs": [{ "kind": "column", "name": "completed_tasks", "cast": "decimal" }, { "kind": "function", "name": "greatest", "inputs": [{ "kind": "column", "name": "total_tasks", "cast": "decimal" }, { "kind": "literal", "literal": 1 }] }] }, { "kind": "literal", "literal": 100 }] }, { "kind": "literal", "literal": 2 }] }] }

Days since created: date_part('day', age(now, created_on))::int:

{ "inputs": [{ "kind": "function", "name": "date_part", "inputs": [{ "kind": "literal", "literal": "day" }, { "kind": "function", "name": "age", "inputs": [{ "kind": "builtin", "name": "now" }, { "kind": "column", "name": "created_on" }] }], "cast": "int" }] }

Boolean NOT with OR (is_active = not archived or deleted):

{ "inputs": [{ "kind": "boolean", "op": "not", "inputs": [{ "kind": "boolean", "op": "or", "inputs": [{ "kind": "compare", "op": "=", "left": { "kind": "column", "name": "is_archived" }, "right": { "kind": "literal", "literal": true } }, { "kind": "compare", "op": "=", "left": { "kind": "column", "name": "is_deleted" }, "right": { "kind": "literal", "literal": true } }] }] }] }

Subquery sum with filters (outstanding invoice amount):

{ "from": "billing_invoice_line", "with": "invoice_id", "filters": { "combinator": "and", "rules": [{ "field": "status", "operator": "!=", "value": "paid", "filterType": "ALPHA" }, { "field": "amount", "operator": ">", "value": 0, "filterType": "NUMERIC" }] }, "inputs": [{ "kind": "function", "name": "coalesce", "inputs": [{ "kind": "aggregate", "name": "sum", "inputs": [{ "kind": "column", "name": "amount" }] }, { "kind": "literal", "literal": 0 }] }] }

Extract year-month (concat year + padded month):

{ "inputs": [{ "kind": "function", "name": "concat", "inputs": [{ "kind": "extract", "part": "year", "inputs": [{ "kind": "column", "name": "created_on" }], "cast": "text" }, { "kind": "literal", "literal": "-" }, { "kind": "function", "name": "lpad", "inputs": [{ "kind": "extract", "part": "month", "inputs": [{ "kind": "column", "name": "created_on" }], "cast": "text" }, { "kind": "literal", "literal": 2 }, { "kind": "literal", "literal": "0" }] }] }] }

JSONB extraction:

{ "inputs": [{ "kind": "function", "name": "jsonb_extract_path_text", "inputs": [{ "kind": "column", "name": "address" }, { "kind": "literal", "literal": "country" }] }] }

Weighted average: sum(score * weight) / greatest(sum(weight), 1):

{ "inputs": [{ "kind": "math", "op": "/", "inputs": [{ "kind": "aggregate", "name": "sum", "inputs": [{ "kind": "math", "op": "*", "inputs": [{ "kind": "column", "name": "score" }, { "kind": "column", "name": "weight" }] }] }, { "kind": "function", "name": "greatest", "inputs": [{ "kind": "aggregate", "name": "sum", "inputs": [{ "kind": "column", "name": "weight" }] }, { "kind": "literal", "literal": 1 }] }], "cast": "decimal" }] }

Date truncation (period grouping by month):

{ "inputs": [{ "kind": "function", "name": "date_trunc", "inputs": [{ "kind": "literal", "literal": "month" }, { "kind": "column", "name": "order_date" }] }] }

Multiple subquery formulas (project with total + open task counts, using compat wrapper):

{
  "columns": "id, name, total_tasks, open_tasks",
  "formulas": [
    {
      "key": "total_tasks",
      "expression": {
        "from": "base_task", "with": "project",
        "inputs": [{ "kind": "aggregate", "name": "count", "inputs": [{ "kind": "column", "name": "id" }] }]
      }
    },
    {
      "key": "open_tasks",
      "expression": {
        "from": "base_task", "with": "project",
        "inputs": [{ "kind": "aggregate", "name": "count", "inputs": [{ "kind": "column", "name": "id" }] }],
        "filters": { "rules": [{ "field": "status", "operator": "not_in", "value": ["<completed_uuid>", "<cancelled_uuid>"] }], "combinator": "and" }
      }
    }
  ]
}

→ Each formula produces a correlated subquery: (SELECT count("t0_child"."id") FROM ... WHERE "t0_child"."project" = "t0"."id" [AND status filter]). No GROUP BY needed.

Combined aggregations via jsonb_build_object (pack total + open counts into one JSON column, one subquery):

{
  "key": "task_stats",
  "expression": {
    "from": "base_task", "with": "project",
    "inputs": [{
      "kind": "function", "name": "jsonb_build_object",
      "inputs": [
        { "kind": "literal", "literal": "total", "cast": "text" },
        { "kind": "aggregate", "name": "count", "inputs": [{ "kind": "column", "name": "id" }] },
        { "kind": "literal", "literal": "open", "cast": "text" },
        { "kind": "aggregate", "name": "count", "inputs": [{ "kind": "case", "cases": [{ "when": { "kind": "compare", "op": "not in", "left": { "kind": "column", "name": "status" }, "right": { "kind": "literal", "literal": ["<completed_uuid>", "<cancelled_uuid>"] } }, "then": { "kind": "column", "name": "id" } }] }] }
      ]
    }]
  }
}

→ Result: { "task_stats": { "total": 6, "open": 2 } }. count(CASE WHEN ... THEN id END) skips NULLs (no else) to count conditionally. "cast": "text" on literal keys is required for jsonb_build_object.