Environment Syncs

How production data is copied, anonymized, and restored into the non-production and per-customer environments.

Where the code lives: these syncs are not in this repo. They live in the aws_control repository as Dockerized bash scripts, run as one-off ECS Fargate tasks and triggered manually from GitHub Actions. The anonymization SQL (anonymize_db.sql) is sourced from this repo (backend/scripts/) and baked into the image at build time.

---

1. Overview

Several environments are periodically refreshed from a production database snapshot so they contain realistic — but anonymized — data:

Sync	Target environment	Region	ECS cluster	Trigger
prod → dev	Shared dev	us-east-1	app-cluster-dev	GitHub Actions (manual)
prod → AMI	AMI research instance	us-east-2	app-cluster-prod-ami	GitHub Actions (manual)
prod → QF487 (cvx-qf487-resync)	CVX/QF487 test instance	us-east-2	—	Script / manual
prod → prod-testing	Prod-testing instance	—	—	Script / manual

They all share the same backbone: pull the latest prod restic snapshot, restore it to a throwaway staging database, anonymize it there, then swap it in for the target database — while preserving the environment's own users (and, for AMI, its companies, workflows, and matching configuration).

2. The shared pipeline

Every sync runs roughly these steps in order (AMI adds a few — see its page):

1. Backup current target DB — pg_dump | gzip | restic backup to the environment's own restic repo, then apply a retention policy (restic forget --prune). (Currently commented out in the AMI script.)
2. Download latest prod snapshot — restic restore latest from the prod backup repo (aerscape-db-backups, us-west-1) using the prod restic credentials. Produces prod_postgres.gz.
3. Restore into staging — create prod_staging_anonymized if missing, drop and recreate its public schema, and pg_restore / zcat | psql the dump into it.
4. Anonymize staging — run anonymize_db.sql against the staging DB. This replaces PII with deterministic fake values and deletes rows that reference customer files (data uploads / downloadable packages). Anonymized users always end up with emails ending in example.com.
5. Scale services to 0 — stop the environment's ECS services so nothing writes during the swap.
6. Extract users to preserve — before wiping the target DB, dump the rows that must survive the sync (see user preservation).
7. Replace target DB — drop/recreate public in the target DB and load the anonymized staging data into it (pg_dump -Fc | pg_restore).
8. Re-insert preserved rows — replay the extracted users (and, for AMI, the full seed) with fresh sequence-generated IDs to avoid collisions.
9. Run migrations — launch the onetime ECS task to apply Django migrations.
10. Scale services back to 1.
11. Sync S3 media — aws s3 sync --delete from the prod media bucket into the environment bucket (see S3 media sync).

Slack notifications are posted at each step unless SKIP_SLACK=true.

COPY_ONLY mode

Both scripts accept COPY_ONLY=true, which skips the entire database pipeline and runs only the S3 media sync. Useful for re-copying media without touching the database.

3. User preservation

The prod snapshot has no knowledge of an environment's own logins, so those are captured before the swap and replayed after it. Identity is decided purely by email pattern (anonymized prod users always end in example.com):

Sync	Preserved as "environment users"
prod → dev	emails ending @aerscape.com, excluding +-tagged addresses
prod → AMI	any email not ending in example.com

All preserved rows are re-inserted without explicit IDs — sequences generate fresh ones — so they never collide with IDs carried in from prod. AMI preserves much more than users; see the prod → AMI page.

4. S3 media sync

After the DB swap, media files are copied from the prod bucket to the environment bucket:

aws s3 sync --delete s3://<prod-bucket> s3://<env-bucket> \
  --exclude 'data_import/*' --exclude 'data_uploads/*' --exclude 'data_downloads/*'

Files live under three top-level prefixes in the bucket, and all three are excluded:

Prefix	Contents	App	Why excluded
data_import/	Provider import staging (CSV + images)	data_import	Re-imported per environment
data_uploads/	Customer-uploaded data packages	data_uploads	DB rows deleted during anonymization
data_downloads/	Generated downloadable data packages	data_downloads	DB rows deleted during anonymization

anonymize_db.sql deletes the data_uploads_* and data_downloads_* package rows, so copying those files would leave orphaned customer data with no DB pointer in the non-prod bucket. Excluding them keeps the environments clean. This matches the cvx-qf487-resync script, which excludes all three:

S3_SYNC_EXCLUDE=(data_import/* data_downloads/* data_uploads/*)

Retaining a specific file: if one object must survive (e.g. a report), add an ordered --include after the exclude — sync matching is prefix- and order-sensitive. See the prod → AMI page.

5. Triggering a sync

Each sync has a GitHub Actions workflow in aws_control/.github/workflows/ (prod_to_dev_sync.yml, prod_to_ami_sync.yml). They are manually dispatched (workflow_dispatch) and each run:

1. builds the sync image and pushes it to ECR (onetime:latest),
2. registers the ECS task definition revision,
3. runs the task on Fargate and waits up to 120 minutes for it to finish.

The AMI scheduled cron is currently disabled — trigger it by hand.