Database and Sync Tools

This page documents tools available for database management, environment synchronization, and data import operations for the Scottsdale Mint WordPress site.

---

WP-CLI

The site includes a wp-cli.yml configuration file at the project root that sets environment defaults for WP-CLI commands.

wp-cli.yml

path: web/wp
url: https://scottsdalemint.com
server: development

Common WP-CLI Commands

# Check WordPress is installed and accessible
wp core version

# List installed plugins and their status
wp plugin list

# Flush Redis object cache
wp cache flush

# Regenerate Algolia search index
wp algolia re-index

# Run WooCommerce database update scripts
wp wc update

# Manage cron events
wp cron event list
wp cron event run suma_daily_analytics_report

# Create/reset admin user
wp user create admin [email protected] --role=administrator

# Export database
wp db export backup.sql

# Import database
wp db import backup.sql

# Search and replace URLs (after DB copy from production)
wp search-replace 'https://scottsdalemint.com' 'https://scottsdalemint.test' --all-tables

---

DB Sync Tool (tools/db-sync/)

The db-sync tool synchronizes the database from production to a local development environment.

Files

tools/db-sync/
├── db-sync.sh              ← Main sync script (Linux/Mac)
├── db-sync.conf.example    ← Configuration template
├── db-sync.conf            ← Actual config (not in git)
├── setup-db-sync.sh        ← One-time setup script
├── close-tunnels.ps1       ← Windows: close SSH tunnels
├── README.md               ← Usage documentation
└── logs/                   ← Log files from sync runs

Setup

# Copy and configure
cp tools/db-sync/db-sync.conf.example tools/db-sync/db-sync.conf
# Edit db-sync.conf with SSH bastion host, database credentials, etc.

# Run one-time setup
bash tools/db-sync/setup-db-sync.sh

Configuration (db-sync.conf)

# Remote database connection
REMOTE_HOST=scottsdale-aurora-prod.cluster-xxxx.us-east-1.rds.amazonaws.com
REMOTE_DB=scottsdalemint
REMOTE_USER=wp_user
REMOTE_PASS=...

# SSH bastion
SSH_HOST=bastion.scottsdalemint.com
SSH_USER=ec2-user
SSH_KEY=~/.ssh/scottsdalemint-bastion.pem

# Local database
LOCAL_DB=scottsdalemint
LOCAL_USER=root

Usage

# Full database sync (replaces local DB with production copy)
bash tools/db-sync/db-sync.sh

# After sync, always run URL replacement:
wp search-replace 'https://scottsdalemint.com' 'https://scottsdalemint.test' \
  --all-tables --report-changed-only

# And flush cache
wp cache flush

warning

The db-sync script replaces your entire local database with the production database. All local changes will be lost. Always backup your local DB first if needed:

wp db export local-backup.sql

---

NFusion Import Tools (tools/nfusion-import/)

retail-import.php

Converts a WooCommerce product export CSV to NFusion's retail product import format.

php tools/nfusion-import/retail-import.php \
  --input path/to/wc-export.csv \
  --output path/to/nfusion-retail-import.csv

retail-tiers-import.php

Converts WooCommerce volume pricing to NFusion price tiers format.

php tools/nfusion-import/retail-tiers-import.php \
  --input path/to/wc-export.csv \
  --output path/to/nfusion-tiers-import.csv

hosts.csv / hosts.php

Tools for managing host/dealer data imports.

---

S3 Sync Tools (tools/s3-sync/)

Tools for syncing media files between S3 buckets (e.g., production to staging):

# Sync production media library to staging
aws s3 sync s3://scottsdale-prod-uploads s3://scottsdale-staging-uploads

---

Aurora Database Access

Direct Access (local development via tunnel)

The Aurora MySQL database is in a private VPC — direct external access is not possible. Access requires:

1. SSH tunnel through the bastion host
2. MySQL client connecting to the tunneled port

# Open SSH tunnel (terminal 1)
ssh -L 3306:scottsdale-aurora-prod.cluster-xxxx.us-east-1.rds.amazonaws.com:3306 \
    -i ~/.ssh/scottsdalemint-bastion.pem \
    [email protected]

# Connect MySQL client (terminal 2)
mysql -h 127.0.0.1 -P 3306 -u wp_user -p scottsdalemint

Via WP-CLI

WP-CLI database commands (e.g., wp db export, wp db query) work when WordPress is running — they use the DATABASE_URL from the environment.

---

Database Schema Notes

Table	Notes
wp_options	Contains Algolia keys, WooCommerce settings, payment gateway configs
wp_posts	WordPress posts, WC products, dealer locator CPT records
wp_postmeta	Product pricing meta, order payment meta, tracking numbers
wp_users / wp_usermeta	Customer accounts, Plaid tokens stored here (encrypted)
wp_woocommerce_order_items	Line items for WooCommerce orders

Plaid Token Storage

Customer Plaid access_token values are stored in wp_usermeta. These tokens have access to customer's bank account. Ensure database backups are encrypted and access is restricted.