# Flowmaster Database

> FlowMaster database schema and relationships

- Skill: `eron1703/flowmaster-database` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add eron1703/flowmaster-database`
- Raw SKILL.md: https://api.skillmd.com/api/skills/eron1703/flowmaster-database/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: eron1703 (https://skillmd.com/u/eron1703)
- Updated: 2026-09-10
- Page: https://skillmd.com/skills/eron1703/flowmaster-database

---


# FlowMaster Database Schema Reference

## Overview

FlowMaster v1.5 uses ArangoDB as its backend database, providing a flexible document-oriented schema for managing complex business processes, AI agents, and workflow execution. The database supports both document collections and edge collections for maintaining rich relationship graphs between entities. This database is shared between FlowMaster core services AND SDX (Semantic Data eXchange) infrastructure.

**Database Name:** flowmaster
**Version:** 1.5
**Created:** 2026-02-02T13:18:53Z
**Database Type:** ArangoDB (Document + Graph)

## Core Database Architecture

### Key Characteristics

- **Document-based:** All data stored as JSON documents with flexible schemas
- **Graph-enabled:** Edge collections support complex relationship mapping
- **Multi-tenant:** Supports multiple tenants and organizations
- **Revision-tracked:** All documents maintain revision history for audit trails
- **Distributed:** Single shard configuration with replication support

## Key Tables & Their Purposes

### Process Definition Layer

| Collection | Purpose | Type |
|------------|---------|------|
| `process_def` | Defines workflow processes with metadata, owner, version | Document |
| `node_def` | Defines individual nodes (steps) within processes | Document |
| `data_definition` | Defines data schemas used by processes and nodes | Document |
| `proc_def_defines_node` | Links process definitions to their constituent nodes | Edge |
| `proc_def_node_flow` | Defines the flow/sequencing between nodes (conditional and sequential) | Edge |
| `proc_def_uses_data_def` | Links processes to their data definitions | Edge |
| `node_def_produces_data_def` | Maps data output from nodes | Edge |
| `node_def_uses_data_def` | Maps data input to nodes | Edge |

#### Process Definition Document Structure

```
process_def:
- _id: Unique identifier (e.g., "process_def/309386")
- _key: Numeric key for internal use
- _rev: Revision identifier for audit trails
- name: Human-readable process name
- organization_id: UUID of owning organization
- tenant_id: UUID of owning tenant
- owner: UUID of process owner user
- version: Semantic version string
- createdAt: ISO timestamp of creation
- updatedAt: ISO timestamp of last modification (optional)
- isTopLevel: Boolean flag for top-level processes
- metadata: Optional JSON object for extended attributes
```

#### Node Definition Document Structure

```
node_def:
- _id: Unique identifier (e.g., "node_def/a21b685f")
- _key: Unique key for cross-reference
- _rev: Revision identifier
- node_id: Custom node identifier
- type: Node type (start, end, action, ai_agent, subprocess, etc.)
- title: Human-readable node title
- description: Node purpose description
- action_type: Type of action (employee_task, script, ai_agent, etc.)
- isEntryNode: Boolean indicating if this is a process entry point
- isExitNode: Boolean indicating if this is a process exit point
- config: Node-specific configuration JSON
- data: Node data JSON
- metadata: Source information and confidence metrics
- position: Canvas coordinates {x, y}
- requiredInputs: Array of required input field names
- outputs: Array of output field names
```

### Execution Layer

| Collection | Purpose | Type |
|------------|---------|------|
| `execution_sessions` | Top-level execution sessions for process instances | Document |
| `execution_instances` | Individual execution instances within a session | Document |
| `execution_history` | Historical records of execution events | Document |
| `execution_state` | Current state snapshots of running executions | Document |
| `execution_checkpoints` | Savepoints in execution for recovery | Document |
| `execution_patterns` | Recurring execution patterns for optimization | Document |

#### Execution Sessions Document Structure

```
execution_sessions:
- _id: Unique session identifier
- _key: Numeric key
- _rev: Revision identifier
- active_agent_ids: Array of active agent IDs in this session
- context: Nested context object containing:
  - execution_id: UUID of the execution
  - process_context: Process metadata and state
  - node: Current node information
  - node_variables: Node input/output data
  - process: Process metadata
  - process_nodes: Array of all process nodes
  - data_definitions: Array of data schemas
```

### Node Instance Layer

| Collection | Purpose | Type |
|------------|---------|------|
| `node_inst` | Instantiated nodes during process execution | Document |
| `node_inst_of_node_def` | Links node instances to their definitions | Edge |
| `node_inst_produces_data` | Data produced by node instances | Edge |
| `node_inst_consumes_data` | Data consumed by node instances | Edge |
| `node_variables` | Variable snapshots for specific node executions | Document |
| `node_states` | State information for executing nodes | Document |

### Process Instance Layer

| Collection | Purpose | Type |
|------------|---------|------|
| `proc_inst` | Process instances (workflow executions) | Document |
| `proc_inst_of_proc_def` | Links process instances to definitions | Edge |
| `proc_inst_has_node_inst` | Links process instances to their node instances | Edge |
| `proc_inst_has_node` | Links process instances to node definitions | Edge |

### Human Task Management

| Collection | Purpose | Type |
|------------|---------|------|
| `human_tasks` | Human-assigned tasks requiring manual action | Document |
| `pending_human_tasks` | Queue of pending human tasks | Document |
| `task_comments` | Comments and notes on human tasks | Document |
| `task_has_human_task` | Relationship between system tasks and human tasks | Edge |

#### Human Task Document Structure

```
human_tasks:
- _id: Unique task identifier
- _key: Numeric key
- title: Task title for assignee
- description: Task description
- status: current status (pending, in_progress, completed, cancelled)
- assignee: UUID of assigned user
- process_id: UUID of parent process execution
- execution_id: UUID of execution context
- node_id: UUID of originating node
- form_fields: Array of form field definitions
- context_data: Nested context (execution_variables, form_data)
- result: Completion result JSON
- priority: Task priority (low, medium, high, urgent)
- created_at: ISO creation timestamp
- completed_at: ISO completion timestamp
- timeout_at: ISO timeout deadline
- metadata: Extended metadata (escalation_config, reminder_config, etc.)
```

### AI Agent & Execution

| Collection | Purpose | Type |
|------------|---------|------|
| `agent_profiles` | AI agent configuration and capabilities | Document |
| `agent_instances` | Running instances of AI agents | Document |
| `agent_tasks` | Tasks assigned to AI agents | Document |
| `agent_executes_task` | Edge linking agents to executed tasks | Edge |
| `agent_spawns_agent` | Hierarchical agent spawning relationships | Edge |
| `agent_memories` | Memory/context storage for agents | Document |
| `ai_agent_checkpoints` | Execution checkpoints for AI agents | Document |
| `ai_audit_logs` | Audit trail for AI agent activities | Document |

### Data Instance Layer

| Collection | Purpose | Type |
|------------|---------|------|
| `data_inst` | Instances of data definitions during execution | Document |
| `document_chunks` | Segmented documents for processing | Document |
| `context_items` | Context items referenced in executions | Document |

### Event & Audit Layer

| Collection | Purpose | Type |
|------------|---------|------|
| `event_audit_log` | Comprehensive audit log of all events | Document |
| `ai_audit_logs` | Specific audit log for AI operations | Document |
| `event_schemas` | Event type definitions | Document |
| `event_delivered_to` | Delivery tracking for events | Edge |
| `execution_history` | Historical execution records | Document |
| `error_patterns` | Detected error patterns for analysis | Document |

### Schedule & Trigger Management

| Collection | Purpose | Type |
|------------|---------|------|
| `schedules` | Scheduled process triggers | Document |
| `schedule_triggers_exec` | Triggers linked to executions | Edge |
| `schedule_executions` | Records of scheduled executions | Document |
| `schedule_locks` | Distributed locks for scheduled tasks | Document |

### System State & Observability

| Collection | Purpose | Type |
|------------|---------|------|
| `execution_locks` | Distributed execution locks | Document |
| `circuit_breaker_state` | Circuit breaker state for fault tolerance | Document |
| `rate_limit_state` | Rate limiting state tracking | Document |
| `dead_letter_queue` | Failed messages for reprocessing | Document |
| `compensation_actions` | Actions for undoing failed operations | Document |
| `replays_requests` | Replay request history | Document |

### Configuration & Metadata

| Collection | Purpose | Type |
|------------|---------|------|
| `llm_models` | Available LLM model configurations | Document |
| `llm_providers` | LLM provider definitions | Document |
| `llm_usage_logs` | Usage tracking for LLM calls | Document |
| `mcp_servers` | MCP (Model Context Protocol) server configs | Document |
| `local_mcp_servers` | Local MCP server instances | Document |
| `cloud_mcp_servers` | Cloud-based MCP server instances | Document |
| `templates` | Reusable process templates | Document |
| `tenant_configurations` | Tenant-specific configurations | Document |
| `notification_preferences` | User notification settings | Document |

### SDX Data Layer

| Collection | Purpose | Type |
|------------|---------|------|
| `sdx_datasources` | External data source definitions | Document |
| `sdx_schemas` | Database schema metadata | Document |
| `sdx_tables` | Table definitions | Document |
| `sdx_columns` | Column definitions with annotations | Document |
| `sdx_connections` | Database connection credentials (encrypted) | Document |
| `sdx_llm_configs` | LLM provider configurations | Document |
| `sdx_secrets` | Encrypted credential storage | Document |
| `sdx_annotations` | Semantic data annotations | Document |
| `sdx_jobs` | Async job tracking | Document |
| `sdx_webhooks` | Webhook subscriptions | Document |
| `sdx_audit` | SDX audit logs | Document |

## Important Relationships

### Process Definition Hierarchy

```
process_def (1) --defines--> (N) node_def
    |
    +--uses--> (N) data_definition

node_def (1) --produces--> (N) data_definition
node_def (1) --consumes--> (N) data_definition
```

### Execution Flow

```
execution_sessions (1) --contains--> (N) execution_instances
    |
    +--runs--> (1) proc_inst

proc_inst (1) --of--> (1) process_def
proc_inst (1) --has--> (N) node_inst
proc_inst (1) --references--> (N) data_inst

node_inst (1) --of--> (1) node_def
node_inst (1) --produces--> (N) data_inst
node_inst (1) --consumes--> (N) data_inst
```

### Node Flow Definition

```
proc_def_node_flow:
- _from: Source node_def ID
- _to: Target node_def ID
- type: "conditional" or "sequence"
- condition: Expression evaluated at runtime (for conditional flows)
- label: Human-readable flow label
```

### AI Agent Execution

```
agent_profiles (1) --spawns--> (N) agent_instances
agent_instances (1) --executes--> (N) agent_tasks
human_tasks (1) --references--> (1) agent_tasks (optional)
```

## Data Types & Constraints

### Common Field Types

| Type | Description | Examples |
|------|-------------|----------|
| UUID | Universally unique identifier | "a0000000-0000-0000-0000-000000000001" |
| ISO Timestamp | ISO 8601 datetime | "2026-02-02T13:18:53Z" |
| Numeric Key | Auto-incrementing integer | 309386, 288354 |
| Revision | ArangoDB revision string | "_l_0mKee---" |
| Status Enum | Predefined status values | "pending", "in_progress", "completed", "cancelled" |
| JSON Object | Flexible nested data | {key: value, ...} |
| Array | Ordered collection | [item1, item2, ...] |

### Common Status Values

**Execution Status:** pending, in_progress, completed, failed, cancelled, paused
**Human Task Status:** pending, in_progress, completed, cancelled, overdue
**Process Status:** draft, active, suspended, completed, archived

### Priority Levels

**Task Priority:** low, medium, high, urgent

### Node Types (Current - being refactored per R11-R16)

**start** - Entry point of process
**end** - Exit point of process
**human_internal** - Internal employee task (R12)
**human_external** - Task for external parties in affiliated orgs (R13)
**machine** - Automated/system task (R14)
**external_affiliated** - Task involving affiliated organizations (R15)
**external_third_party** - Task involving external third parties (R16)
**ai_agent** - AI-powered operation
**subprocess** - Call to another process
**decision** - Branching logic node (gateway)

Note: Current code has: start, end, task, service_task, gateway, event, subprocess, data_object. The above are TARGET types per R11-R16.

### Action Types

**employee_task** - Task for employee completion
**script** - System script execution
**ai_agent** - AI agent execution
**approval** - Approval workflow
**notification** - Send notification
**webhook** - Call external webhook

## Query Patterns

### Get All Nodes in a Process

```
proc_def_defines_node where _from = "process_def/{id}"
```

### Get Process Flow

```
proc_def_node_flow where metadata.process_id = "{id}"
```

### Get Active Executions for a Process

```
execution_sessions where context.process_context.process.id = "{process_id}"
```

### Get Pending Human Tasks

```
human_tasks where status = "pending"
```

### Get Process with All Relationships

```
process_def (1) -> proc_def_defines_node (N) -> node_def
process_def (1) -> proc_def_uses_data_def (N) -> data_definition
```

## When to Use This Skill

Use this skill when you need to:

1. **Understand process structure** - Query process definitions, nodes, and their relationships
2. **Track execution state** - Monitor running processes, executions, and their current state
3. **Manage human tasks** - Query or understand human task assignments and workflows
4. **Build MCP endpoints** - Create tools that interact with the FlowMaster database
5. **Analyze workflows** - Study process flows, conditional branches, and data transformations
6. **Debug executions** - Understand execution history and state snapshots
7. **Query execution results** - Access results from completed executions
8. **Reference data schemas** - Look up data definitions used by processes
9. **Understand AI agent integration** - See how agents are configured and executed
10. **Track audit trails** - Access comprehensive audit logs and event history

## Collection Ownership

**Important**: Each collection has a designated owner service with defined access patterns. See `ARANGODB_ISOLATION_STRATEGY.md` for complete details.

### Core Services

| Collection | Owner Service | Port | Access Type |
|------------|---------------|------|-------------|
| process_def, node_def, data_definition | Process Service | 9001 | Owner (rw) |
| execution_sessions, execution_instances | Execution Service | 9002 | Owner (rw) |
| human_tasks, pending_human_tasks | Human Task Service | 9003 | Owner (rw) |
| agent_profiles, agent_instances | AI Agent Service | 9004 | Owner (rw) |
| schedules, schedule_executions | Scheduler Service | 9008 | Owner (rw) |
| event_audit_log, event_schemas | Event Audit Service | 9010 | Owner (rw) |
| llm_models, tenant_configurations | Configuration Service | 9012 | Owner (rw) |

### SDX Collections (Separate Database)

**Database**: `flowmaster_sdx` (isolated from core)

| Collection | Owner Service | Port | Access Type |
|------------|---------------|------|-------------|
| sdx_datasources, sdx_schemas, sdx_tables | SDX Service | 9015 | Owner (rw) |
| sdx_columns, sdx_connections, sdx_secrets | SDX Service | 9015 | Owner (rw) |
| sdx_annotations, sdx_jobs, sdx_audit | SDX Service | 9015 | Owner (rw) |

**Access Pattern**: SDX collections are isolated in separate database. Cross-service access via SDX Service API only.

### Access Patterns

- **Owner (rw)**: Full read/write access, schema management
- **Reader (ro)**: Read-only via well-defined queries
- **Writer (wo)**: Write-only (e.g., audit logs - append only)
- **API Access**: Cross-service writes via service API (not direct DB access)

**Reference**: See `ARANGODB_ISOLATION_STRATEGY.md` for:
- Complete ownership matrix
- Schema versioning system
- Migration coordination
- Database user permissions
- Access control enforcement

## Development Notes

- All documents use ArangoDB's standard `_id`, `_key`, and `_rev` fields
- Edge collections follow the pattern `_from -> _to` to establish relationships
- Tenant and organization IDs are UUIDs for multi-tenancy support
- Timestamps are in ISO 8601 format with millisecond precision
- Execution context is deeply nested in execution_sessions for complete state capture
- The database uses automatic sharding (1 shard) with potential for horizontal scaling
- Revision tracking enables full audit history and conflict resolution
- **Schema Changes**: All schema modifications must follow migration coordination protocol (see ARANGODB_ISOLATION_STRATEGY.md)
- **Cross-Service Access**: Use service APIs for cross-service data access, not direct database queries
- **SDX Isolation**: SDX uses separate `flowmaster_sdx` database - no direct access from core services