Skip to main content

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.

Version: 1.0.0
Last Updated: 2026-02-24
Status: Active
Module: PF (Platform Foundation)
Cross-References:
  • Developer README — Technical reference
  • AGENTS.md — Platform conventions

​
Overview

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

​
Supported Source Systems

Source SystemSchema PrefixTarget Core
Ripplingsource_ripplingHR (Workforce)
Business Centralsource_business_centralFA (Finance)
Sage Intacctsource_sage_intacctFA (Finance)
RingCentralsource_ringcentralCE (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 Health 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 Health 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:
RuleDescriptionExample
renameRename a source column to a different target columnfirst_name → given_name
defaultSet a default value when source is nullstatus defaults to 'active'
map_valuesMap discrete values to new values'FT' → 'full_time'
concatConcatenate multiple source columnsfirst_name + last_name → full_name
skip_if_nullSkip the entire row if this column is nullSkip rows without email
injectInject a constant value regardless of sourceAlways 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

StatusMeaning
pendingRun created, worker not yet started
runningWorker is actively processing batches
completedAll rows migrated successfully
completed_with_errorsMigration finished but some rows failed
failedMigration stopped due to a critical error
cancelledManually 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:
ScenarioRecommended Batch Size
Wide tables (50+ columns)100–250
Narrow tables (< 10 columns)1000–2000
Tables with large text/JSON columns100–200
Fast, simple tables1000–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

ErrorCauseFix
”Connection refused”External DB unreachableVerify MIGRATION_SOURCE_DB_URL, check firewall rules
”No schemas found”No source_* schemas in external DBCreate schemas with source_ prefix
”Missing mapping”No mapping configured for the selected tableCreate or load default mappings first
”Timeout”Batch too large or slow networkReduce batch size to 100–250
”Permission denied”DB user lacks read accessGrant SELECT on source schemas
”Column not found”Source schema changed since mapping was createdUpdate 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