Encore OS — Documentation

Cross-References:

Developer README — Technical reference

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

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.
Permission: The logged-in user must have pf.data_migration.manage permission (typically Org Admin).
Source database: The external database must contain schemas prefixed with source_* (e.g., source_rippling, source_business_central).

Getting Started

Navigate to Settings → Data Migration (route: /settings/data-migration).
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

Click “New Migration”
Select Source Schema (schemas load automatically from the external DB)
Select Source Table (tables load with row counts when schema is selected)
Enter Target Table name (e.g., hr_employees)
Set Batch Size (default: 500)
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

Getting Started

Data & Migration

Configuration Building Blocks

Templates & Recommendations

Scheduling & Automation

Data Migration Tool — Admin Guide

Overview

Supported Source Systems

Prerequisites

Getting Started

Explore Source Tab

Browse Schemas

Browse Tables

View Columns

Preview Data

Mappings Tab

Loading Default Mappings

Creating Custom Mappings

Editing Mappings

Transform Rules

Migration Runs Tab

Starting a New Migration

Monitoring Progress

Run Statuses

Reviewing Error Logs

Resumability

Batch Size Tuning

Multi-Organization Setup

Current Model

Future Roadmap: Per-Org Connections

Troubleshooting

Security

Version History

1.0.0 (2026-02-24)

​Overview

​Supported Source Systems

​Prerequisites

​Getting Started

​Explore Source Tab

​Browse Schemas

​Browse Tables

​View Columns

​Preview Data

​Mappings Tab

​Loading Default Mappings

​Creating Custom Mappings

​Editing Mappings

​Transform Rules

​Migration Runs Tab

​Starting a New Migration

​Monitoring Progress

​Run Statuses

​Reviewing Error Logs

​Resumability

​Batch Size Tuning

​Multi-Organization Setup

​Current Model

​Future Roadmap: Per-Org Connections

​Troubleshooting

​Security

​Version History

​1.0.0 (2026-02-24)

Overview

Supported Source Systems

Prerequisites

Getting Started

Explore Source Tab

Browse Schemas

Browse Tables

View Columns

Preview Data

Mappings Tab

Loading Default Mappings

Creating Custom Mappings

Editing Mappings

Transform Rules

Migration Runs Tab

Starting a New Migration

Monitoring Progress

Run Statuses

Reviewing Error Logs

Resumability

Batch Size Tuning

Multi-Organization Setup

Current Model

Future Roadmap: Per-Org Connections

Troubleshooting

Security

Version History

1.0.0 (2026-02-24)