> ## Documentation Index
> Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Forms & Workflow Entity Relationship Diagram

> Entity relationship diagram for the Forms & Workflow module

> **Version:** 1.0.0\
> **Last Updated:** 2026-01-11

> **Module:** Forms & Workflow\
> **Prefix:** `fw_`\
> **Tables:** 54

## Changelog

* **1.0.0** (2026-01-11): Initial ERD documentation

## Overview

The Forms & Workflow module provides a flexible form builder, submission management, workflow automation, and approval routing capabilities used across all domain modules.

## Forms & Submissions ERD

```mermaid theme={null}
erDiagram
    fw_forms ||--o{ fw_form_versions : "has versions"
    fw_forms ||--o{ fw_form_submissions : receives
    fw_forms ||--o{ fw_form_fields : defines
    fw_form_submissions ||--o{ fw_submission_values : contains
    fw_form_submissions }o--|| fw_form_versions : "uses version"
    
    fw_forms {
        uuid id PK
        uuid organization_id FK
        string name
        string slug
        text description
        enum status
        string owning_core
        boolean is_template
        jsonb settings
        jsonb custom_fields
    }
    
    fw_form_versions {
        uuid id PK
        uuid form_id FK
        integer version_number
        jsonb schema
        jsonb ui_schema
        boolean is_published
        timestamp published_at
    }
    
    fw_form_fields {
        uuid id PK
        uuid form_id FK
        string field_key
        string field_type
        string label
        boolean is_required
        integer display_order
        jsonb validation_rules
        jsonb options
    }
    
    fw_form_submissions {
        uuid id PK
        uuid form_id FK
        uuid form_version_id FK
        uuid organization_id FK
        uuid submitted_by FK
        jsonb data
        enum status
        timestamp submitted_at
        jsonb custom_fields
    }
```

## Workflow Engine ERD

```mermaid theme={null}
erDiagram
    fw_workflows ||--o{ fw_workflow_versions : "has versions"
    fw_workflows ||--o{ fw_workflow_instances : runs
    fw_workflows ||--o{ fw_workflow_steps : defines
    fw_workflow_instances ||--o{ fw_workflow_tasks : creates
    fw_workflow_steps ||--o{ fw_workflow_tasks : "task for step"
    
    fw_workflows {
        uuid id PK
        uuid organization_id FK
        string name
        text description
        enum trigger_type
        string owning_core
        boolean is_active
        jsonb settings
    }
    
    fw_workflow_versions {
        uuid id PK
        uuid workflow_id FK
        integer version_number
        jsonb definition
        boolean is_active
        timestamp activated_at
    }
    
    fw_workflow_steps {
        uuid id PK
        uuid workflow_id FK
        string step_key
        string step_type
        string name
        integer step_order
        jsonb config
        jsonb conditions
    }
    
    fw_workflow_instances {
        uuid id PK
        uuid workflow_id FK
        uuid workflow_version_id FK
        uuid organization_id FK
        string current_step
        enum status
        jsonb context
        timestamp started_at
        timestamp completed_at
    }
    
    fw_workflow_tasks {
        uuid id PK
        uuid workflow_instance_id FK
        uuid workflow_step_id FK
        uuid assigned_to FK
        enum status
        timestamp due_at
        timestamp completed_at
        jsonb outcome
    }
```

## Approvals ERD

```mermaid theme={null}
erDiagram
    fw_approval_requests ||--o{ fw_approval_steps : "has steps"
    fw_approval_requests ||--o{ fw_approval_history : logs
    fw_approval_steps ||--o{ fw_approval_actions : receives
    
    fw_approval_requests {
        uuid id PK
        uuid organization_id FK
        string request_type
        uuid entity_id FK
        string entity_type
        uuid requested_by FK
        enum status
        timestamp requested_at
        jsonb metadata
    }
    
    fw_approval_steps {
        uuid id PK
        uuid approval_request_id FK
        integer step_order
        uuid approver_id FK
        enum approver_type
        enum status
        timestamp due_at
        boolean is_current
    }
    
    fw_approval_actions {
        uuid id PK
        uuid approval_step_id FK
        uuid actor_id FK
        enum action
        text comments
        timestamp acted_at
        jsonb custom_fields
    }
    
    fw_approval_history {
        uuid id PK
        uuid approval_request_id FK
        uuid actor_id FK
        enum action
        string step_name
        text comments
        timestamp created_at
    }
```

## Automation ERD

```mermaid theme={null}
erDiagram
    fw_automations ||--o{ fw_automation_triggers : "triggered by"
    fw_automations ||--o{ fw_automation_actions : executes
    fw_automations ||--o{ fw_automation_runs : logs
    fw_automation_runs ||--o{ fw_automation_logs : details
    
    fw_automations {
        uuid id PK
        uuid organization_id FK
        string name
        text description
        boolean is_active
        string owning_core
        jsonb settings
    }
    
    fw_automation_triggers {
        uuid id PK
        uuid automation_id FK
        enum trigger_type
        string event_name
        jsonb conditions
        integer priority
    }
    
    fw_automation_actions {
        uuid id PK
        uuid automation_id FK
        enum action_type
        integer action_order
        jsonb config
        boolean stop_on_error
    }
    
    fw_automation_runs {
        uuid id PK
        uuid automation_id FK
        uuid trigger_id FK
        enum status
        timestamp started_at
        timestamp completed_at
        jsonb context
        text error_message
    }
    
    fw_automation_logs {
        uuid id PK
        uuid automation_run_id FK
        uuid action_id FK
        enum status
        timestamp executed_at
        integer duration_ms
        jsonb result
        text error
    }
```

## Signatures ERD

```mermaid theme={null}
erDiagram
    fw_form_submissions ||--o{ fw_signature_requests : requires
    fw_signature_requests ||--o{ fw_signatures : collects
    fw_signatures ||--o{ fw_signature_audit_log : audits
    
    fw_signature_requests {
        uuid id PK
        uuid organization_id FK
        uuid submission_id FK
        uuid document_id FK
        string signer_email
        string signer_name
        enum status
        timestamp requested_at
        timestamp expires_at
    }
    
    fw_signatures {
        uuid id PK
        uuid signature_request_id FK
        uuid signer_id FK
        string signature_data
        string ip_address
        string user_agent
        timestamp signed_at
        jsonb metadata
    }
    
    fw_signature_audit_log {
        uuid id PK
        uuid signature_id FK
        enum event_type
        string ip_address
        timestamp created_at
        jsonb details
    }
```

## Portal & Public Forms ERD

```mermaid theme={null}
erDiagram
    fw_portals ||--o{ fw_portal_pages : contains
    fw_portals ||--o{ fw_portal_submissions : receives
    fw_portals }o--|| pf_organizations : "belongs to"
    
    fw_portals {
        uuid id PK
        uuid organization_id FK
        string name
        string slug
        text description
        jsonb branding
        boolean is_active
        jsonb settings
    }
    
    fw_portal_pages {
        uuid id PK
        uuid portal_id FK
        uuid form_id FK
        string title
        string path
        integer display_order
        boolean is_published
        jsonb access_rules
    }
    
    fw_portal_submissions {
        uuid id PK
        uuid portal_id FK
        uuid form_submission_id FK
        string submitter_email
        string submitter_ip
        timestamp submitted_at
        enum status
    }
    
    fw_portal_rate_limits {
        uuid id PK
        string ip_address
        string action_type
        integer request_count
        timestamp window_start
    }
```

## RLS Policy Coverage

| Table                     | SELECT | INSERT | UPDATE | DELETE | WITH CHECK | Status        |
| ------------------------- | ------ | ------ | ------ | ------ | ---------- | ------------- |
| fw\_forms                 | ✅      | ✅      | ✅      | ✅      | ✅          | Complete      |
| fw\_form\_versions        | ✅      | ✅      | -      | -      | -          | Version table |
| fw\_form\_submissions     | ✅      | ✅      | ✅      | ✅      | ✅          | Complete      |
| fw\_workflows             | ✅      | ✅      | ✅      | ✅      | ✅          | Complete      |
| fw\_workflow\_versions    | ✅      | ✅      | -      | -      | -          | Version table |
| fw\_workflow\_instances   | ✅      | ✅      | ✅      | ✅      | ✅          | Complete      |
| fw\_approval\_requests    | ✅      | ✅      | ✅      | ✅      | ✅          | Complete      |
| fw\_approval\_history     | ✅      | ✅      | -      | -      | -          | Audit table   |
| fw\_signature\_audit\_log | ✅      | ✅      | -      | -      | -          | Audit table   |
| fw\_portal\_rate\_limits  | ✅      | -      | -      | -      | -          | Public read   |
| fw\_automation\_logs      | ✅      | ✅      | -      | -      | -          | Audit table   |

## Key Tables

### fw\_forms

Form definitions with version control and module ownership.

### fw\_form\_submissions

Submitted form data with status tracking.

### fw\_workflows

Workflow definitions with step-based execution.

### fw\_approval\_requests

Multi-step approval routing with delegation support.

### fw\_automations

Event-driven automation rules.

## Helper Functions

| Function                               | Purpose                          |
| -------------------------------------- | -------------------------------- |
| `fw_has_org_access(org_id, user_id)`   | Check FW module access           |
| `fw_can_submit_form(form_id, user_id)` | Check form submission permission |
| `fw_get_pending_approvals(user_id)`    | Get pending approval tasks       |
| `fw_advance_workflow(instance_id)`     | Move workflow to next step       |

## Cross-Module Dependencies

* **PF:** Organizations, profiles, notifications
* **HR:** Leave requests, onboarding workflows
* **RH:** Intake forms, discharge workflows
* **GR:** Policy approval workflows
* **FA:** Bill approval workflows