apptor flow uses PostgreSQL 16 with Liquibase for schema migrations. All tables include org_id for multi-tenant data isolation.
Core Execution Tables
process
The workflow definition registry. One row per workflow, regardless of how many versions it has.
| Column | Type | Description |
|---|
process_id | VARCHAR(64) PK | Unique workflow ID |
process_name | VARCHAR(255) | Display name |
process_description | VARCHAR(255) | Optional description |
process_type_cd | INT | Process type code |
active_version | VARCHAR(10) | Currently deployed version number |
created_ts | TIMESTAMP TZ | Creation timestamp |
created_by | VARCHAR(255) | User who created |
updated_ts | TIMESTAMP TZ | Last update timestamp |
updated_by | VARCHAR(255) | Last updater |
org_id | VARCHAR(64) | Organization (tenant) ID |
process_version
Every saved version of a workflow definition.
| Column | Type | Description |
|---|
process_version_id | BIGINT PK | Auto-generated |
process_id | VARCHAR(64) FK → process | Parent workflow |
version_number | VARCHAR(10) | e.g. "1", "2", "1.0.3" |
state_cd | INT | 0=draft, 1=published, 2=archived |
process_text | TEXT | Full workflow JSON definition |
dialect | VARCHAR(10) | JSON format dialect |
environment_id | BIGINT FK → environments | Target environment |
updated_ts | TIMESTAMP TZ | |
updated_by | VARCHAR(255) | |
created_ts | TIMESTAMP TZ | |
created_by | VARCHAR(255) | |
org_id | VARCHAR(64) | |
process_instance
Each workflow execution (a run).
| Column | Type | Description |
|---|
process_instance_id | VARCHAR(64) PK | Unique execution ID |
process_id | VARCHAR(64) | Workflow that was run |
process_name | VARCHAR(100) | Workflow name at time of execution |
process_description | VARCHAR(255) | |
process_version | VARCHAR(10) | Version that ran |
process_instance_version | INT | Internal version counter |
data | TEXT | Full execution state JSON |
state_cd | INT | 0=running, 1=completed, 2=failed, 3=cancelled |
start_time | TIMESTAMP TZ | When execution started |
end_time | TIMESTAMP TZ | When execution ended |
correlation_key | VARCHAR(255) | Key for event correlation |
parent_instance_id | VARCHAR(64) | Parent execution (for subprocesses) |
org_id | VARCHAR(64) | |
node_instance
A record per node execution within a process instance.
| Column | Type | Description |
|---|
node_instance_id | VARCHAR(64) PK | |
process_instance_id | VARCHAR(64) FK | Parent execution |
node_id | VARCHAR(255) | Node ID from workflow definition |
node_name | VARCHAR(255) | Display name |
node_type | VARCHAR(64) | Node type string |
status_cd | INT | 0=pending, 1=running, 2=completed, 3=failed, 4=cancelled, 5=timed_out |
state | TEXT | Node-specific state JSON |
variables | TEXT | Variable snapshot at this node |
attached_node_id | VARCHAR(64) | For boundary events |
attached_node_instance_id | VARCHAR(64) | |
group_assigned | VARCHAR(64) | Assigned user group (user tasks) |
owner_user_id | VARCHAR(64) | Assigned user (user tasks) |
delegated_user_id | VARCHAR(64) | Delegated user |
start_time | TIMESTAMP TZ | |
end_time | TIMESTAMP TZ | |
parent_node_id | VARCHAR(255) | Parent node (for subprocesses) |
parent_node_instance_id | VARCHAR(255) | |
iteration | INT | Loop iteration counter |
org_id | VARCHAR(64) | |
event_subscription
Suspends a workflow waiting for an external event (Intermediate Catch Event).
| Column | Type | Description |
|---|
event_subscription_id | BIGINT PK | Auto-generated |
process_instance_id | VARCHAR(64) FK | Suspended execution |
process_id | VARCHAR(64) | |
process_version | VARCHAR(10) | |
sub_process_name | VARCHAR(64) | If inside a subprocess |
node_id | VARCHAR(64) | The catch event node waiting |
node_instance_id | VARCHAR(64) | |
event_type | VARCHAR(100) | Type of event expected |
message_or_signal_ref | VARCHAR(64) | Event name/reference |
correlation_key | VARCHAR(255) | Matches the inbound event key |
cancel_activity | BOOLEAN | Whether to cancel on boundary event |
due_time | TIMESTAMP TZ | Optional deadline |
state_cd | INT | 0=waiting, 1=triggered, 2=cancelled |
created_ts | TIMESTAMP TZ | |
org_id | VARCHAR(64) | |
queue_item
Persistent message queue — ensures messages survive server restarts.
| Column | Type | Description |
|---|
item_id | BIGINT PK | |
queue_name | VARCHAR(50) | Actor queue name |
message_id | VARCHAR(64) | Deduplication ID |
message | TEXT | Serialized message payload |
created_ts | TIMESTAMP TZ | |
org_id | VARCHAR(64) | |
AI Memory Tables
ai_memory_conversation
Conversation context for AI Task nodes with memory enabled.
| Column | Type | Description |
|---|
conversation_id | VARCHAR(128) PK | Unique conversation ID |
conversation_type | VARCHAR(32) | 'conversation' (default) |
max_messages | INT | Maximum messages to retain (default: 10) |
enabled | BOOLEAN | Whether memory is active |
configuration | TEXT | JSON config |
created_time | TIMESTAMP TZ | |
last_accessed | TIMESTAMP TZ | |
org_id | VARCHAR(64) | |
ai_memory_message
Individual messages stored in conversation memory.
| Column | Type | Description |
|---|
message_id | VARCHAR(128) PK | |
conversation_id | VARCHAR(128) FK | Parent conversation |
sequence_number | BIGINT | Message order |
role | VARCHAR(32) | 'user', 'assistant', 'system' |
content | TEXT | Message content |
metadata | TEXT | Additional JSON metadata |
created_time | TIMESTAMP TZ | |
RBAC Tables
roles
| Column | Type | Description |
|---|
role_id | UUID PK | |
name | VARCHAR | Role name |
description | VARCHAR | |
scope | VARCHAR | 'organization' or 'system' |
org_id | VARCHAR(64) | null for system-wide roles |
permissions
| Column | Type | Description |
|---|
permission_id | UUID PK | |
permission_key | VARCHAR UNIQUE | e.g. 'WORKFLOW_EXECUTE' |
name | VARCHAR | Display name |
description | VARCHAR | |
group_id | UUID | Permission group |
role_permissions
| Column | Type | Description |
|---|
role_id | UUID FK | |
permission_id | UUID FK | |
api_key_roles
| Column | Type | Description |
|---|
api_key_id | UUID FK | |
role_id | UUID FK | |
Authentication Tables
api_keys
| Column | Type | Description |
|---|
id | UUID PK | |
key_id | VARCHAR UNIQUE | Public key identifier |
name | VARCHAR | Human-readable label |
key_hash | VARCHAR | bcrypt hash of the secret |
key_prefix | VARCHAR | First chars shown in UI |
rate_limit_per_minute | INT | Request rate limit |
rate_limit_per_hour | INT | Hourly rate limit |
expires_at | TIMESTAMP TZ | Expiry (null = never) |
last_used_at | TIMESTAMP TZ | |
status | VARCHAR | ACTIVE, SUSPENDED, REVOKED |
created_at | TIMESTAMP TZ | |
organization_id | VARCHAR(64) | |
organization_auth_configs
OIDC/SAML configuration per organization.
idp_connections
Identity provider connection details (client ID, discovery URL, etc.).
federated_users
Mapping between OIDC subject IDs and internal user IDs.
Other Key Tables
| Table | Purpose |
|---|
environments | DEVELOPMENT / PRODUCTION environments per org |
integrations | Configured external service connections |
connectors | Connector definitions for integration types |
secrets | Encrypted org secrets (name, encrypted_value) |
published_workflows | Public workflow URL configurations |
public_workflow_executions | Executions of published workflows |
voice_call_state | Active voice call state for Voice Task nodes |
user_groups | User groups for task assignment |
user_group_members | Group membership |
workflow_assignments | Workflow-to-user/group access assignments |
openapi_specs | Uploaded OpenAPI specifications for REST integrations |
Database Access (Local Dev)
PGPASSWORD="postgres" "/c/Program Files/PostgreSQL/16/bin/psql.exe" \
-h localhost -p 5432 -U postgres -d apptor_flow_db -w \
-c "SELECT table_name FROM information_schema.tables WHERE table_schema='public' ORDER BY table_name"
Connection details (local):
- Host:
localhost
- Port:
5432
- User:
postgres
- Password:
postgres
- Database:
apptor_flow_db