> ## 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 Database Tables

> The FW module provides a comprehensive form builder and workflow automation engine, including form design, submissions, approval workflows, automation rules, a…

**Prefix:** `fw_`\
**Table Count:** 54\
**Last Updated:** 2026-01-10

***

## Overview

The FW module provides a comprehensive form builder and workflow automation engine, including form design, submissions, approval workflows, automation rules, and analytics.

***

## Table Categories

### 1. Form Builder (6 tables)

| Table                      | Columns | Description              |
| -------------------------- | ------- | ------------------------ |
| `fw_forms`                 | 26      | Form definitions         |
| `fw_form_fields`           | 24      | Field definitions        |
| `fw_form_versions`         | 14      | Form version history     |
| `fw_form_templates`        | 18      | Shareable form templates |
| `fw_form_template_ratings` | 10      | Template ratings         |
| `fw_form_permissions`      | 10      | Form access control      |

**Form Status:**

* `draft` → `published` → `archived`

**Field Types:**

```typescript theme={null}
type FieldType = 
  | 'text' | 'textarea' | 'number' | 'email' | 'phone'
  | 'date' | 'datetime' | 'time'
  | 'select' | 'multiselect' | 'radio' | 'checkbox'
  | 'file' | 'image' | 'signature'
  | 'address' | 'lookup' | 'calculated'
  | 'section' | 'repeater' | 'table';
```

**Form Schema Example:**

```typescript theme={null}
interface FormField {
  id: string;
  name: string;
  label: string;
  type: FieldType;
  required: boolean;
  validation?: ValidationRule[];
  options?: SelectOption[];      // For select/radio/checkbox
  lookup_config?: LookupConfig;  // For lookup fields
  calculated_formula?: string;   // For calculated fields
  conditional_logic?: ConditionalRule[];
}
```

### 2. Form Submissions (5 tables)

| Table                           | Columns | Description               |
| ------------------------------- | ------- | ------------------------- |
| `fw_form_submissions`           | 22      | Submission records        |
| `fw_submission_attachments`     | 12      | File attachments          |
| `fw_submission_notes`           | 10      | Internal notes            |
| `fw_form_submission_signatures` | 14      | E-signature records       |
| `fw_portal_submissions`         | 16      | Public portal submissions |

**Submission Data Storage:**

```typescript theme={null}
interface Submission {
  id: string;
  form_id: string;
  form_version_id: string;
  data: Record<string, any>;  // JSONB field data
  status: 'draft' | 'submitted' | 'processing' | 'completed';
  submitted_at?: string;
  submitted_by?: string;
}
```

### 3. Workflow Engine (8 tables)

| Table                             | Columns | Description          |
| --------------------------------- | ------- | -------------------- |
| `fw_workflow_definitions`         | 24      | Workflow definitions |
| `fw_workflow_versions`            | 16      | Version history      |
| `fw_workflow_templates`           | 18      | Shareable templates  |
| `fw_workflow_template_versions`   | 14      | Template versions    |
| `fw_workflow_template_ratings`    | 10      | Template ratings     |
| `fw_workflow_template_usage`      | 8       | Usage tracking       |
| `fw_workflow_version_comparisons` | 12      | Version diffs        |
| `fw_subflows`                     | 16      | Reusable subflows    |

**Workflow Definition Schema:**

```typescript theme={null}
interface WorkflowDefinition {
  id: string;
  name: string;
  trigger_type: 'form_submission' | 'schedule' | 'event' | 'manual';
  trigger_config: TriggerConfig;
  steps: WorkflowStep[];
  variables: WorkflowVariable[];
}

interface WorkflowStep {
  id: string;
  type: 'action' | 'condition' | 'approval' | 'delay' | 'subflow';
  config: StepConfig;
  next_steps: string[];  // Step IDs
  on_error?: string;     // Error handler step
}
```

### 4. Workflow Execution (4 tables)

| Table                    | Columns | Description               |
| ------------------------ | ------- | ------------------------- |
| `fw_workflow_executions` | 22      | Active workflow instances |
| `fw_execution_logs`      | 14      | Step execution logs       |
| `fw_domain_events`       | 16      | Event bus records         |
| `fw_workflow_events`     | 12      | Workflow-specific events  |

**Execution Status:**

* `pending` → `running` → `completed`
* `failed`, `cancelled`, `paused`

### 5. Approvals (7 tables)

| Table                     | Columns | Description                 |
| ------------------------- | ------- | --------------------------- |
| `fw_approval_chains`      | 16      | Approval chain definitions  |
| `fw_approval_steps`       | 14      | Steps within chains         |
| `fw_approval_requests`    | 20      | Active approval requests    |
| `fw_approval_assignments` | 12      | Approver assignments        |
| `fw_approval_delegations` | 14      | Delegation rules            |
| `fw_approval_history`     | 14      | Approval decision log       |
| `fw_workflow_approvals`   | 12      | Workflow-specific approvals |

**Approval Step Types:**

* `single` - One approver required
* `all` - All approvers must approve
* `any` - Any approver can approve
* `percentage` - X% must approve

**Approval Decision Flow:**

```
fw_approval_requests (pending)
        ↓
fw_approval_assignments (assigned to approvers)
        ↓
fw_approval_history (decision recorded)
        ↓
fw_approval_requests (approved/rejected)
```

### 6. Automation Rules (4 tables)

| Table                   | Columns | Description               |
| ----------------------- | ------- | ------------------------- |
| `fw_automation_rules`   | 20      | Automation definitions    |
| `fw_automation_actions` | 16      | Action configurations     |
| `fw_automation_logs`    | 14      | Execution logs            |
| `fw_action_templates`   | 14      | Reusable action templates |

**Action Types:**

```typescript theme={null}
type ActionType = 
  | 'send_email' | 'send_notification'
  | 'create_record' | 'update_record' | 'delete_record'
  | 'call_api' | 'call_webhook'
  | 'assign_task' | 'start_workflow'
  | 'calculate' | 'transform';
```

### 7. Alerts & Notifications (5 tables)

| Table                                  | Columns | Description            |
| -------------------------------------- | ------- | ---------------------- |
| `fw_workflow_alert_rules`              | 16      | Alert rule definitions |
| `fw_workflow_alerts`                   | 14      | Active alerts          |
| `fw_workflow_notification_rules`       | 14      | Notification configs   |
| `fw_workflow_notification_preferences` | 12      | User preferences       |
| `fw_template_update_notifications`     | 10      | Template change alerts |

### 8. Analytics (2 tables)

| Table                             | Columns | Description        |
| --------------------------------- | ------- | ------------------ |
| `fw_workflow_path_analytics`      | 14      | Path analysis data |
| `fw_workflow_performance_metrics` | 16      | Performance KPIs   |

**Tracked Metrics:**

* Execution time per step
* Bottleneck identification
* Approval turnaround time
* Error rates by step type

### 9. Testing & Debugging (6 tables)

| Table                   | Columns | Description           |
| ----------------------- | ------- | --------------------- |
| `fw_test_cases`         | 16      | Test case definitions |
| `fw_test_datasets`      | 12      | Test data sets        |
| `fw_test_scenarios`     | 14      | Test scenarios        |
| `fw_test_coverage`      | 12      | Coverage reports      |
| `fw_debug_sessions`     | 14      | Debug session logs    |
| `fw_sandbox_executions` | 16      | Sandbox test runs     |

### 10. Integration (2 tables)

| Table                | Columns | Description            |
| -------------------- | ------- | ---------------------- |
| `fw_api_connections` | 18      | External API configs   |
| `fw_query_whitelist` | 12      | Allowed query patterns |

### 11. Public Portal (3 tables)

| Table                   | Columns | Description         |
| ----------------------- | ------- | ------------------- |
| `fw_form_portal_config` | 16      | Portal settings     |
| `fw_portal_rate_limits` | 10      | Rate limiting       |
| `fw_page_templates`     | 14      | Portal page layouts |

### 12. Security (1 table)

| Table                    | Columns | Description           |
| ------------------------ | ------- | --------------------- |
| `fw_signature_audit_log` | 12      | Signature audit trail |

### 13. Module Settings (1 table)

| Table                | Columns | Description      |
| -------------------- | ------- | ---------------- |
| `fw_module_settings` | 27      | FW configuration |

**Key Settings:**

| Setting                          | Type    | Default |
| -------------------------------- | ------- | ------- |
| `max_form_fields`                | integer | 100     |
| `max_workflow_steps`             | integer | 50      |
| `default_approval_timeout_hours` | integer | 72      |
| `enable_public_portals`          | boolean | false   |
| `max_file_size_mb`               | integer | 25      |

***

## Common Query Patterns

### Get Form with Fields

```typescript theme={null}
const { data } = await supabase
  .from('fw_forms')
  .select(`
    *,
    fields:fw_form_fields(*)
  `)
  .eq('id', formId)
  .single();
```

### Get Pending Approvals

```typescript theme={null}
const { data } = await supabase
  .from('fw_approval_requests')
  .select(`
    *,
    assignments:fw_approval_assignments(
      *,
      approver:pf_profiles(first_name, last_name)
    )
  `)
  .eq('status', 'pending')
  .contains('assignments.approver_id', [userId]);
```

### Get Workflow Execution Status

```typescript theme={null}
const { data } = await supabase
  .from('fw_workflow_executions')
  .select(`
    *,
    logs:fw_execution_logs(*)
  `)
  .eq('id', executionId)
  .single();
```

### Get Form Submissions

```typescript theme={null}
const { data } = await supabase
  .from('fw_form_submissions')
  .select(`
    *,
    form:fw_forms(name),
    submitter:pf_profiles(first_name, last_name)
  `)
  .eq('form_id', formId)
  .order('submitted_at', { ascending: false });
```

***

## RLS Policies

FW uses multi-level RLS:

1. Organization isolation via `organization_id`
2. Form-level permissions via `fw_form_permissions`
3. Submission access based on form settings
4. Approval access for assigned approvers

**Helper Functions:**

* `fw_user_can_view_form()` - Form view permission
* `fw_user_can_submit_form()` - Submission permission
* `fw_user_can_approve()` - Approval assignment check

***

## Platform Integration

Forms are accessed via Platform Integration Layer:

```typescript theme={null}
// ✅ CORRECT - Use platform layer
import { FormEmbed, useFormSubmission } from '@/platform/forms';

// ❌ WRONG - Direct core import
import { FormEmbed } from '@/cores/fw/components';
```

***

## See Also

* [FW Module Specs](https://github.com/Encore-OS/encoreos/tree/development/specs/fw)
* [Platform Forms Integration](/architecture/integrations/PLATFORM_INTEGRATION_LAYERS)
* [Database Schema Overview](/architecture/DATABASE_SCHEMA)