Data Synchronization

The MyNATCA Platform includes comprehensive data synchronization tools that sync member data, positions, teams, facilities, and regions from the legacy MySQL database to Supabase PostgreSQL.

Overview

Data synchronization ensures that the modern Supabase database stays current with the authoritative MySQL member database. The sync system supports both development and production environments with schema isolation.

Key Features

Multi-table sync: Members, positions, teams, facilities, regions, and more
Environment-aware: Separate dev and production schemas
Dependency management: Intelligent sync ordering with skip option
Production-ready: Support for production schema syncing
Batch processing: Efficient handling of large datasets
Error handling: Comprehensive error reporting and recovery

Sync Architecture

Schema Organization

The sync system uses schema-based isolation to separate development and production data:

Supabase Database:
├── public schema (production data)
│   ├── members
│   ├── positions
│   ├── teams
│   ├── facilities
│   └── regions
│
└── dev schema (development data)
    ├── members
    ├── positions
    ├── teams
    ├── facilities
    └── regions

Sync Process Flow

Available Sync Commands

Sync All Tables

Syncs all data tables in the correct dependency order:

# Development (default - syncs to dev schema)
node sync/sync-all.js
 
# Production (syncs to public schema)
node sync/sync-all.js --env=prod

Sync Order:

Regions (base dependency)
Facilities (depends on regions)
Members (depends on facilities)
Positions (depends on members)
Teams (committees and councils)

Sync Individual Tables

Sync specific tables when you only need to update certain data:

Members Sync

# Development
node sync/sync-all.js members
 
# Production
node sync/sync-all.js members --env=prod

Positions Sync

# Development
node sync/sync-all.js positions
 
# Production
node sync/sync-all.js positions --env=prod

Important Fix (October 2025): The positions sync was recently fixed to properly respect the --env=prod flag. Previously, it always synced to the dev schema regardless of the environment flag.

What Changed:

Added environment detection: const env = process.argv.includes('--env=prod') ? 'prod' : 'dev'
Set target schema based on environment: const targetSchema = env === 'prod' ? 'public' : 'dev'
Updated all Supabase queries to use .schema(this.schema) instead of hardcoded .schema('dev')
Added logging to show target schema: "🎯 Syncing to public schema" or "🎯 Syncing to dev schema"

Verification: When running a production positions sync, you should see:

node sync/sync-all.js positions --env=prod
🎯 Syncing to public schema
Syncing positions...
✓ Synced 1,247 position records

Teams Sync (Committees and Councils)

# Development
node sync/sync-all.js teams
 
# Production
node sync/sync-all.js teams --env=prod

Syncs both committees and councils data from the MyNATCA API.

Facilities Sync

# Development
node sync/sync-all.js facilities
 
# Production
node sync/sync-all.js facilities --env=prod

Regions Sync

# Development
node sync/sync-all.js regions
 
# Production
node sync/sync-all.js regions --env=prod

Skip Dependencies Flag (Added October 2025)

The --skip-deps flag allows you to sync individual tables without automatically syncing their dependencies first. This is useful for re-syncing after a full sync has already been completed.

# Sync only positions without syncing members first
node sync/sync-all.js positions --skip-deps --env=prod
 
# Sync only members without syncing facilities/regions first
node sync/sync-all.js members --skip-deps --env=prod
 
# Combine with environment flag for production quick updates
node sync/sync-all.js positions --skip-deps --env=prod

How It Works:

Without --skip-deps: Orchestrator syncs all dependencies first (e.g., regions → facilities → members → positions)
With --skip-deps: Only syncs the specified table, assumes dependencies are already up-to-date

When to use --skip-deps:

Re-syncing after full sync - When you've already run a complete sync and only need to update one table
Testing sync scripts - During development to test a single sync script quickly
Quick updates - When you know dependencies haven't changed (e.g., positions updated but members unchanged)
Time optimization - Reducing sync time for large datasets when only one table needs updating

When NOT to use --skip-deps:

First-time setup - Initial sync of a new environment (always run full sync first)
After migrations - Schema migrations that affect multiple tables require dependency sync
Foreign key changes - When relationships between tables have been modified
Data integrity concerns - If you're unsure whether dependencies are current

Examples:

Scenario 1: Daily position updates

# Morning: Full sync to ensure everything is current
node sync/sync-all.js --env=prod
 
# Afternoon: Quick position re-sync after member position changes
node sync/sync-all.js positions --skip-deps --env=prod
# ✅ Fast: Only syncs positions table
# ✅ Safe: Members, facilities, regions already synced this morning

Scenario 2: New environment setup

# ❌ WRONG: Skip deps on first sync
node sync/sync-all.js positions --skip-deps --env=prod
# Error: Foreign key violation - members table doesn't exist yet
 
# ✅ CORRECT: Full sync first time
node sync/sync-all.js --env=prod
# Syncs: regions → facilities → members → positions → teams

Performance Impact:

Full sync with dependencies: ~5-10 minutes for all tables
Individual table with --skip-deps: ~30 seconds - 2 minutes per table
Use --skip-deps to reduce re-sync time by 80-90%

View Help

node sync/sync-all.js --help

Displays all available options, table names, and usage examples.

Environment Variables

Required environment variables for sync operations:

# MySQL Source Database
MYSQL_HOST=your_mysql_host
MYSQL_USER=your_mysql_username
MYSQL_PASS=your_mysql_password
MYSQL_DB=your_mysql_database
 
# Supabase Target Database
SUPABASE_URL=https://your-project.supabase.co
SUPABASE_SERVICE_ROLE_KEY=your_service_key
 
# MyNATCA API (for teams sync)
MYNATCA_API_URL=https://api.mynatca.org
MYNATCA_API_KEY=your_api_key

Production Schema Requirements

Before running production syncs, ensure the production schema has all required columns and constraints.

Positions Table Requirements

The public.positions table requires these recent additions:

-- Add enddate column if not exists
ALTER TABLE public.positions
ADD COLUMN IF NOT EXISTS enddate DATE;
 
-- Add unique constraint to prevent duplicate position records
ALTER TABLE public.positions
ADD CONSTRAINT positions_member_position_unique
UNIQUE (membernumber, positiontype);

Migration files available in: platform/migrations/

Running Production Migrations

# Connect to Supabase production database
psql "postgresql://postgres:[password]@db.[project-ref].supabase.co:5432/postgres"
 
# Run migration files
\i migrations/add_positions_enddate.sql
\i migrations/add_positions_unique_constraint.sql

Or use the Supabase SQL Editor:

Navigate to Supabase Dashboard > SQL Editor
Paste migration SQL
Run against production database

Sync Script Details

sync-all.js (Orchestrator)

The main sync orchestrator that handles:

Command-line argument parsing
Environment detection (dev vs production)
Dependency resolution
Sequential sync execution
Error handling and reporting

Key Features:

Validates table names before sync
Logs target schema for transparency
Supports individual or all-table sync
Handles --skip-deps flag for optimization

sync-members.js

Syncs member data from MySQL to Supabase:

Member demographics and contact information
Facility and region assignments
Member type and status
Discord account linkages

sync-positions.js

Syncs member positions (leadership roles):

Position type (facrep, neb, staff, etc.)
Position titles and descriptions
Start and end dates
Member number associations

Recent Fix (October 2025): Now properly respects --env=prod flag and syncs to public schema when specified.

Implementation Details:

// Environment detection added October 2025
const env = process.argv.includes('--env=prod') ? 'prod' : 'dev';
const targetSchema = env === 'prod' ? 'public' : 'dev';
 
console.log(`🎯 Syncing to ${targetSchema} schema`);
 
// Constructor accepts schema option
constructor(options = {}) {
  this.schema = options.schema || 'dev';
  // ... other initialization
}
 
// All queries use dynamic schema
const { data, error } = await this.supabase
  .from('positions')
  .schema(this.schema)  // Uses this.schema instead of hardcoded 'dev'
  .upsert(positionsData);

Migration from Hardcoded to Dynamic Schema:

Before: All queries used .schema('dev') hardcoded
After: Queries use .schema(this.schema) based on environment
Result: Production sync now correctly writes to public.positions table

sync-teams.js

Syncs committees and councils from MyNATCA API:

Committee memberships
Council assignments
Leadership positions within teams
Active/inactive status

Implementation:

// Sync to dev or public schema based on environment
const targetSchema = env === 'prod' ? 'public' : 'dev';
console.log(`🎯 Syncing to ${targetSchema} schema`);

sync-facilities.js

Syncs facility data:

Facility codes and names
Region assignments
Facility types
Location information

sync-regions.js

Syncs regional data:

Region codes
Region names
Regional boundaries

Best Practices

Development Workflow

# 1. Initial full sync to development
node sync/sync-all.js
 
# 2. Test your changes with dev data
npm run dev
 
# 3. Individual table re-sync as needed
node sync/sync-all.js positions --skip-deps
 
# 4. Verify sync results
npm run verify-sync

Production Deployment

# 1. Run migrations first
psql -f migrations/add_positions_enddate.sql
psql -f migrations/add_positions_unique_constraint.sql
 
# 2. Full production sync
node sync/sync-all.js --env=prod
 
# 3. Verify sync succeeded
node sync/verify-production-sync.js
 
# 4. Individual updates as needed
node sync/sync-all.js positions --skip-deps --env=prod

Sync Monitoring

Monitor sync operations for:

Duration: Track how long each sync takes
Record counts: Verify expected number of records synced
Errors: Watch for constraint violations or data issues
Schema targeting: Confirm syncing to correct schema

Example sync output:

🎯 Syncing to public schema
Syncing positions...
✓ Synced 1,247 position records
✓ Sync completed in 3.2 seconds

Troubleshooting

"Relation does not exist" Errors

Cause: Target table doesn't exist in the specified schema

Solution:

Run schema migrations to create tables
Verify schema name in environment variables
Check Supabase dashboard for table existence

Unique Constraint Violations

Cause: Attempting to insert duplicate records

Solution:

Ensure unique constraints are properly defined
Add ON CONFLICT handling in sync scripts
Clear test data before re-syncing
Check for data quality issues in source MySQL database

Production Sync Syncing to Wrong Schema

Cause: Sync script not respecting --env=prod flag

Solution:

Verify script has environment detection logic:

const env = process.argv.includes('--env=prod') ? 'prod' : 'dev';
const targetSchema = env === 'prod' ? 'public' : 'dev';

Check script logs for "🎯 Syncing to [schema] schema" message
Update script to match pattern from sync-teams.js

Foreign Key Constraint Failures

Cause: Syncing dependent tables before base tables

Solution:

Don't use --skip-deps on first sync
Run full sync to ensure dependency order
Sync base tables (regions, facilities) before dependent tables (members, positions)

Sync Performance Optimization

Batch Processing

For large datasets (10,000+ records):

const BATCH_SIZE = 1000;
 
// Process in batches
for (let i = 0; i < records.length; i += BATCH_SIZE) {
  const batch = records.slice(i, i + BATCH_SIZE);
  await supabase.from(table).upsert(batch);
}

Indexing

Ensure indexes exist on frequently queried columns:

-- Add indexes for better sync performance
CREATE INDEX IF NOT EXISTS idx_members_membernumber ON public.members(membernumber);
CREATE INDEX IF NOT EXISTS idx_positions_membernumber ON public.positions(membernumber);
CREATE INDEX IF NOT EXISTS idx_facilities_facilityid ON public.facilities(facilityid);

Connection Pooling

Use connection pooling for high-volume syncs:

const { createClient } = require('@supabase/supabase-js');
 
const supabase = createClient(
  process.env.SUPABASE_URL,
  process.env.SUPABASE_SERVICE_ROLE_KEY,
  {
    db: { schema: targetSchema },
    auth: { persistSession: false },
    global: {
      headers: { 'x-connection-pooling': 'true' }
    }
  }
);

Data Synchronization

Overview

Key Features

Sync Architecture

Schema Organization

Sync Process Flow

Available Sync Commands

Sync All Tables

Sync Individual Tables

Members Sync

Positions Sync

Teams Sync (Committees and Councils)

Facilities Sync

Regions Sync

Skip Dependencies Flag (Added October 2025)

View Help

Environment Variables

Production Schema Requirements

Positions Table Requirements

Running Production Migrations

Sync Script Details

sync-all.js (Orchestrator)

sync-members.js

sync-positions.js

sync-teams.js

sync-facilities.js

sync-regions.js

Best Practices

Development Workflow

Production Deployment

Sync Monitoring

Troubleshooting

"Relation does not exist" Errors

Unique Constraint Violations

Production Sync Syncing to Wrong Schema

Foreign Key Constraint Failures

Sync Performance Optimization

Batch Processing

Indexing

Connection Pooling

Related Documentation