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

# Data Migration Tool — Admin Guide

> The Data Migration Tool enables Encore OS administrators to connect to external PostgreSQL databases, browse their schemas and data, configure field-level mapp…

> **Cross-References:**
>
> * Developer README — Technical reference
> * [AGENTS.md](../../../AGENTS.md) — Platform conventions

***

## Overview

The Data Migration Tool enables Encore OS administrators to connect to external PostgreSQL databases, browse their schemas and data, configure field-level mappings, and run batch migrations into Encore OS tables. It supports resumable, batched ingestion with transform rules and error tracking.

### Supported Source Systems

| Source System    | Schema Prefix             | Target Core               |
| ---------------- | ------------------------- | ------------------------- |
| Rippling         | `source_rippling`         | HR (Workforce)            |
| Business Central | `source_business_central` | FA (Finance)              |
| Sage Intacct     | `source_sage_intacct`     | FA (Finance)              |
| RingCentral      | `source_ringcentral`      | CE (Community Engagement) |

***

## Prerequisites

1. **Secret configured:** `MIGRATION_SOURCE_DB_URL` must be set in Lovable Cloud Secrets (Settings → Cloud → Secrets). This is the PostgreSQL connection string for the external source database.
2. **Permission:** The logged-in user must have `pf.data_migration.manage` permission (typically Org Admin).
3. **Source database:** The external database must contain schemas prefixed with `source_*` (e.g., `source_rippling`, `source_business_central`).

***

## Getting Started

1. Navigate to **Settings → Data Migration** (route: `/settings/data-migration`).
2. The page displays three tabs: **Explore Source**, **Mappings**, and **Migration Runs**.

***

## Explore Source Tab

Use this tab to browse the external database before configuring mappings.

### Browse Schemas

Click **"Connect & Browse"** to list all schemas matching the `source_*` pattern. The tool connects read-only to the external database.

### Browse Tables

Select a schema to see its tables with estimated row counts. Row counts are derived from PostgreSQL's `pg_class.reltuples` and may be approximate.

### View Columns

Select a table to see its columns with:

* Column name
* Data type (with badge)
* Nullable indicator
* Default value

### Preview Data

Click **"Preview"** on any table to see up to **25 rows** of sample data. This helps verify the source data structure before mapping.

***

## Mappings Tab

Mappings define how source columns translate to Encore OS target columns.

### Loading Default Mappings

Click **"Load Defaults"** and select a source type:

* **Rippling (HR)** — Maps `employees` → `hr_employees`, `departments` → `hr_departments`
* **Business Central (FA)** — Maps `gl_accounts`, `customers`, `vendors` → FA tables
* **Sage Intacct (FA)** — Maps `gl_accounts`, `customers`, `vendors` → FA tables
* **RingCentral (CE)** — Maps `call_log` → `ce_calls`

Defaults are pre-configured with field mappings based on the source system's schema.

### Creating Custom Mappings

Click **"Create Mapping"** to define a new mapping manually. Specify:

* **Source schema** and **source table**
* **Target table** (Encore OS table name, e.g., `hr_employees`)
* **Field mappings** (source column → target column pairs)
* **Execution order** (lower runs first; useful for parent-before-child dependencies)

### Editing Mappings

Click the **edit icon** on any mapping row to open the Mapping Editor. You can:

* Add, remove, or modify field mapping pairs
* Change the target table
* Adjust execution order
* Toggle the mapping active/inactive

### Transform Rules

Field mappings support transform rules applied during migration:

| Rule           | Description                                         | Example                                   |
| -------------- | --------------------------------------------------- | ----------------------------------------- |
| `rename`       | Rename a source column to a different target column | `first_name` → `given_name`               |
| `default`      | Set a default value when source is null             | `status` defaults to `'active'`           |
| `map_values`   | Map discrete values to new values                   | `'FT'` → `'full_time'`                    |
| `concat`       | Concatenate multiple source columns                 | `first_name` + `last_name` → `full_name`  |
| `skip_if_null` | Skip the entire row if this column is null          | Skip rows without `email`                 |
| `inject`       | Inject a constant value regardless of source        | Always set `source_system` = `'rippling'` |

***

## Migration Runs Tab

### Starting a New Migration

1. Click **"New Migration"**
2. Select **Source Schema** (schemas load automatically from the external DB)
3. Select **Source Table** (tables load with row counts when schema is selected)
4. Enter **Target Table** name (e.g., `hr_employees`)
5. Set **Batch Size** (default: 500)
6. Click **"Start Migration"**

### Monitoring Progress

Once started, the run appears in the list with:

* **Status badge** — Updates every 3 seconds via polling
* **Progress bar** — Shows `rows_processed / total_rows`
* **Timing** — Start time, end time (when complete)

### Run Statuses

| Status                  | Meaning                                   |
| ----------------------- | ----------------------------------------- |
| `pending`               | Run created, worker not yet started       |
| `running`               | Worker is actively processing batches     |
| `completed`             | All rows migrated successfully            |
| `completed_with_errors` | Migration finished but some rows failed   |
| `failed`                | Migration stopped due to a critical error |
| `cancelled`             | Manually cancelled by an admin            |

### Reviewing Error Logs

Click on a run to expand its details. The **error\_log** field contains sanitized error messages for failed rows. Errors are grouped by type to help identify patterns (e.g., missing required fields, data type mismatches).

***

## Resumability

If a migration run is interrupted (e.g., Edge Function timeout), the worker persists `last_offset` after each batch. When the run is retried, it resumes from the last successfully committed offset rather than restarting from the beginning.

This ensures:

* No duplicate rows in the target table
* Large migrations survive Edge Function timeouts (typically \~60s)
* Progress is never lost

***

## Batch Size Tuning

The default batch size is **500 rows**. Adjust based on your data:

| Scenario                            | Recommended Batch Size |
| ----------------------------------- | ---------------------- |
| Wide tables (50+ columns)           | 100–250                |
| Narrow tables (\< 10 columns)       | 1000–2000              |
| Tables with large text/JSON columns | 100–200                |
| Fast, simple tables                 | 1000–5000              |

Smaller batches reduce memory pressure and timeout risk. Larger batches reduce round-trip overhead.

***

## Multi-Organization Setup

### Current Model

The tool uses a **single global secret** (`MIGRATION_SOURCE_DB_URL`) shared across all organizations. This works when all source data resides in one external database with separate schemas per source system.

### Future Roadmap: Per-Org Connections

A planned enhancement will introduce a `pf_data_migration_connections` table to store **per-organization connection strings** encrypted via Supabase Vault:

```
pf_data_migration_connections
  id, organization_id, connection_name, source_type,
  encrypted_connection_string (via Vault),
  is_active, last_tested_at, created_at
```

This will allow each organization to connect to its own external database independently.

***

## Troubleshooting

| Error                | Cause                                           | Fix                                                    |
| -------------------- | ----------------------------------------------- | ------------------------------------------------------ |
| "Connection refused" | External DB unreachable                         | Verify `MIGRATION_SOURCE_DB_URL`, check firewall rules |
| "No schemas found"   | No `source_*` schemas in external DB            | Create schemas with `source_` prefix                   |
| "Missing mapping"    | No mapping configured for the selected table    | Create or load default mappings first                  |
| "Timeout"            | Batch too large or slow network                 | Reduce batch size to 100–250                           |
| "Permission denied"  | DB user lacks read access                       | Grant `SELECT` on source schemas                       |
| "Column not found"   | Source schema changed since mapping was created | Update field mappings to match current schema          |

***

## Security

* **Read-only connections:** The explore function sets `SET default_transaction_read_only = on` to prevent any writes to the external database.
* **Tenant isolation:** Every migrated row has `organization_id` injected automatically. No cross-tenant data leakage is possible.
* **Error sanitization:** Error messages displayed to users are sanitized via `sanitizeErrorMessage()` to prevent leaking internal database details.
* **Connection security:** The `MIGRATION_SOURCE_DB_URL` secret is stored in Lovable Cloud Secrets and never exposed to the client. Edge Functions access it server-side via `Deno.env.get()`.
* **Permission gating:** The route is protected by `pf.data_migration.manage` permission check.

***

## Version History

### 1.0.0 (2026-02-24)

* Initial release with full admin guide

***

**Last Updated:** 2026-02-24