> ## 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.

# Form Field Lookups — Integration

> Feature ID: FW-15 Status: ✅ Phase 1 Complete; \U0001F4CB Phase 2/3 Specified -form-field-lookups.md Expansion: FW-15-PHASE-2-3-EXPANSION.md Last Updated: 20…

**Feature ID:** FW-15\
**Status:** ✅ Phase 1 Complete; 📋 Phase 2/3 Specified\
**Spec:** [FW-15-form-field-lookups.md](../../../specs/fw/archive/FW-15-form-field-lookups.md)\
**Expansion:** [FW-15-PHASE-2-3-EXPANSION.md](../../../specs/fw/archive/FW-15-PHASE-2-3-EXPANSION.md)\
**Last Updated:** 2026-03-19

***

## Overview

FW-15 enables form fields to reference data from other database tables (departments, employees, sites) via a Platform Integration Layer. The feature consumes PF Platform Data Lookup and Platform Workforce; it does not expose events or APIs.

**Phase 1** (complete) delivers basic table lookups with org scoping and the data-lookup integration layer. **Phase 2** adds advanced filtering, client-side search, query caching, and submit-time validation (client + edge function). **Phase 3** deepens platform integration: workforce employee lookups, site lookup documentation, and additional whitelisted tables (`hr_positions`, `pf_picklist_items`).

***

## Integration Points (from Spec)

| Dependency                                     | Pattern             | Purpose                                     | Phase |
| ---------------------------------------------- | ------------------- | ------------------------------------------- | ----- |
| PF-01 (Organizations & Sites)                  | Platform dependency | Organization-scoped lookups; site selection | 1, 3  |
| PF-02 (RBAC)                                   | Platform dependency | Access control on lookup source tables      | 1     |
| PF-08 (Platform Forms Integration)             | Platform Layer      | Form builder and renderer; field settings   | 1     |
| PF-15 / Data Lookup (`@/platform/data-lookup`) | Platform Layer      | Generic table lookups via `useTableLookup`  | 1, 2  |
| Platform Workforce (`@/platform/workforce`)    | Platform Layer      | Employee lookups via `useEmployeeLookup`    | 3     |
| `validate-form-submission` edge function       | Edge Function       | Server-side validation of lookup values     | 2     |

***

## Platform Layer Usage

* **Consumes:** `@/platform/data-lookup` (`useTableLookup`), `@/platform/workforce` (employee lookups via `useEmployeeLookup`).
* **Provides:** Lookup-enabled form fields through FW-01 Form Builder and form renderer; no direct API.
* **Data flow:** Form field `settings` JSONB stores `dataSource: 'lookup'`, `lookupTable`, `labelColumn`, `valueColumn`, `lookupFilters`. Renderer calls platform hooks; RLS on source tables enforces tenant isolation.

### Phase 2 Additions

* **Filtering:** `lookupFilters` in `field.settings` applied as `.eq(column, value)` conditions in addition to `organization_id`.
* **Search:** Client-side filtering on label column (case-insensitive) for 20+ options until FW-18 (server-side search).
* **Caching:** React Query `staleTime: 5min`, `gcTime: 10min` per platform query patterns.
* **Validation:** Client validates selected values still exist; `validate-form-submission` edge function validates server-side within tenant boundary. Errors use `sanitizeErrorMessage` — no table/column leakage.

### Phase 3 Additions

* **Workforce:** Employee lookup fields route through `useEmployeeLookup` from `@/platform/workforce` (not direct HR imports). Supports site, department, position, employment\_status filters.
* **Sites:** `pf_sites` already whitelisted in `LOOKUP_TABLES` with `labelColumn: 'name'`, `valueColumn: 'id'`, `availableFilters: ['is_active']`. Phase 3.2 documents this in the platform README.
* **Additional tables:** `hr_positions` and `pf_picklist_items` (already whitelisted). Credential types deferred until HR credential management spec is complete.

***

## Security and Tenant Isolation

* All lookups filter by `organization_id` when the source table has that column.
* RLS on source tables (e.g. `hr_departments`, `pf_sites`) is respected; no cross-tenant data.
* Table whitelist is code-defined; only approved tables are available for lookups.
* Server-side validation (Phase 2) checks within tenant boundary only; sanitized error messages prevent schema/data leakage.

***

## Related Docs

* [CROSS\_CORE\_INTEGRATIONS.md](./CROSS_CORE_INTEGRATIONS.md)
* [PLATFORM\_INTEGRATION\_LAYERS.md](./PLATFORM_INTEGRATION_LAYERS.md) (PF-15 Data Lookup)
* [FW-15-form-field-lookups.md](../../../specs/fw/archive/FW-15-form-field-lookups.md)
* [FW-15-PHASE-2-3-EXPANSION.md](../../../specs/fw/archive/FW-15-PHASE-2-3-EXPANSION.md)