IchWillHeim/workflow_engine_design.md

Workflow-Based Android Application -- Concept & Database Design

Overview

This document describes the core idea and database structure for a workflow-based Android application.

A workflow consists of multiple steps. Each step: - Executes a function - Receives input data - Produces a result - Determines the next step based on the result

Workflows can be triggered via UI elements such as buttons.

---

Core Concept

Workflow Execution Flow

1. A workflow is started (e.g., by pressing a button)
2. The first step is executed
3. The step calls a function
4. The function returns a result
5. Based on the result, the next step is determined
6. The process continues until no next step is defined

---

Context Handling

A shared context object is used to pass data between steps.

Example: - Step 1 stores selected contact → SelectedContact - Step 4 reads SelectedContact

---

Database Schema

1. workflows

Stores workflow definitions.

CREATE TABLE workflows (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    start_step_id INTEGER,
    is_active INTEGER NOT NULL DEFAULT 1
);

---

2. workflow_steps

Defines steps within a workflow.

CREATE TABLE workflow_steps (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    workflow_id INTEGER NOT NULL,
    step_key TEXT NOT NULL,
    name TEXT NOT NULL,
    function_key TEXT NOT NULL,
    input_mapping_json TEXT,
    parameter_json TEXT,
    output_key TEXT,
    sort_order INTEGER NOT NULL DEFAULT 0,
    is_active INTEGER NOT NULL DEFAULT 1,

    FOREIGN KEY (workflow_id) REFERENCES workflows(id)
);

---

3. workflow_transitions

Defines transitions between steps.

CREATE TABLE workflow_transitions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    workflow_id INTEGER NOT NULL,
    from_step_id INTEGER NOT NULL,
    result_code TEXT NOT NULL,
    to_step_id INTEGER,
    is_default INTEGER NOT NULL DEFAULT 0,

    FOREIGN KEY (workflow_id) REFERENCES workflows(id),
    FOREIGN KEY (from_step_id) REFERENCES workflow_steps(id),
    FOREIGN KEY (to_step_id) REFERENCES workflow_steps(id)
);

---

4. workflow_functions

Describes available executable functions.

CREATE TABLE workflow_functions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    function_key TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    description TEXT,
    input_schema_json TEXT,
    parameter_schema_json TEXT,
    output_schema_json TEXT,
    is_active INTEGER NOT NULL DEFAULT 1
);

---

5. workflow_buttons

Defines UI triggers for workflows.

CREATE TABLE workflow_buttons (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    caption TEXT NOT NULL,
    icon TEXT,
    workflow_id INTEGER NOT NULL,
    group_name TEXT,
    sort_order INTEGER NOT NULL DEFAULT 0,
    is_active INTEGER NOT NULL DEFAULT 1,

    FOREIGN KEY (workflow_id) REFERENCES workflows(id)
);

---

6. workflow_runs

Tracks workflow executions.

CREATE TABLE workflow_runs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    workflow_id INTEGER NOT NULL,
    started_at TEXT NOT NULL,
    finished_at TEXT,
    status TEXT NOT NULL,
    context_json TEXT,

    FOREIGN KEY (workflow_id) REFERENCES workflows(id)
);

---

7. workflow_run_steps

Tracks execution of each step.

CREATE TABLE workflow_run_steps (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    workflow_run_id INTEGER NOT NULL,
    step_id INTEGER NOT NULL,
    started_at TEXT NOT NULL,
    finished_at TEXT,
    status TEXT NOT NULL,
    input_json TEXT,
    output_json TEXT,
    result_code TEXT,
    error_message TEXT,

    FOREIGN KEY (workflow_run_id) REFERENCES workflow_runs(id),
    FOREIGN KEY (step_id) REFERENCES workflow_steps(id)
);

---

Example: Call Workflow

Steps

1. Select contact
2. If contact is "Peter" → ask confirmation
3. If confirmed → call
4. Otherwise → end

Transitions

From Step Result Code To Step

---

Select Contact PETER Ask Select Contact NOT_PETER Call Ask YES Call Ask NO End

---

Summary

Key components: - workflows - workflow_steps - workflow_transitions - workflow_functions - workflow_buttons

Optional but recommended: - workflow_runs - workflow_run_steps

This structure enables: - Dynamic workflows - Flexible branching logic - Reusable functions - UI-driven execution