Prod → AMI Sync

Refreshes the AMI research instance from an anonymized production snapshot, preserving AMI users, companies, workflows, and matching configuration, then re-runs emission matching.

• Script: aws_control/prod-to-ami-sync/prod-to-ami-sync.sh
• Target: app-cluster-prod-ami in us-east-2
• Trigger: GitHub Actions → Prod-to-AMI-Sync → Run workflow (manual; scheduled cron is disabled)

See Environment Syncs for the shared pipeline; this page covers what is specific to AMI.

---

What it does

The AMI script is a refactored version of the pipeline (functions + an ERR trap that alerts Slack and leaves services stopped for manual recovery). Beyond the shared steps it also:

• Deactivates anonymized prod users — UPDATE accounts_user SET is_active = false WHERE email LIKE '%example.com' so carried-over prod logins can't be used.
• Runs a much larger seed than dev (see below).
• Re-runs emission matching for every company after startup.

The current backup-of-current-AMI-DB step is commented out in main() — AMI has no restic repo configured yet.

What is preserved (the AMI seed)

prepare_ami_seed.sql runs against the AMI DB before it is wiped and emits a SQL script that re-inserts, after the restore:

• Users — any email not ending in example.com (plus their user_permissions)
• Companies + SSO configs (accounts_companysinglesignon)
• Company memberships (including permissions, filter_shape, and settings)
• SSO email exemptions (custom_auth_ssoemailexemption) — taken wholesale
• Waffle switches and flags
• Workflow tables — conditions, condition groups, expressions, workflow defs, transitions, workflow instances (only if the referenced event still exists), linking rules, and the action registry
• Emission matching configuration — upserts the known-good per-provider MatchingConfiguration + default params, then attaches every company in the AMI DB to every configuration
• A fixed downloadable file record — one DownloadableDataPackage pointing at data_downloads/AMI/Company-Codes-AMI.xlsx (see S3 exclusions)

Companies/users are inserted without explicit IDs; old→new ID maps are built via RETURNING joined on natural keys (company name, user email) and used to re-link dependent rows. The AMI company row is always asserted (idempotent).

Bitwarden-sourced users

Some AMI users don't exist in the AMI DB at all — they are stored as pre-hashed entries in the AMI_USERS Bitwarden secret and created during setup. See prod-to-ami-sync/INITIAL_SETUP.md for the full one-time setup (SSO config, flags/switches, default workflow, AMI users).

Emission matching + the "stuck in CREATED" problem

After the containers come back up, the script's run_company_matching step registers ecs_task_definition_matching.json and launches a single Fargate task.

Why events used to get stuck. Emission matching is asynchronous: each match creates an EmissionRecord and an Event with event_status = CREATED (emission_matching/utils.py), and events leave CREATED only when the workflow manager fires an AUTO transition CREATED → NEW. That manager normally runs on a 5-minute Celery beat (run_workflow_manager, gated on the workflow-automation-enabled switch). If matching finishes without the manager running promptly afterward, freshly matched events pile up in CREATED.

What the task now does (all in one container, no platform code change — the logic lives in the task definition's -c script):

1. For every company, create a tracked EmissionMatchingRun (create_emission_matching_run) and start it (start_emission_matching_run), which enqueues the matching chord with an emission_matching_run_id so completion is observable. Companies that can't start a run (e.g. a duplicate already PENDING/RUNNING) are skipped and logged.
2. Poll those runs every 30s until all reach a terminal status (COMPLETE / FAILED / TIMED_OUT), or a 2-hour timeout elapses.
3. Run the workflow manager synchronously in-process — WorkflowManager().run() — moving matched events out of CREATED. This runs regardless of whether matching finished or the timeout was hit.

The task runs in the background (up to 2h); the sync script launches it and proceeds to the S3 sync without waiting. Progress is visible in the task's CloudWatch logs (enqueued N matching runs → waiting on N matching runs → running workflow manager).

2h-timeout caveat: a company with no matching configuration produces a run that never flips to COMPLETE on its own (the matcher early-returns), so it would keep the poll busy until the timeout. The AMI seed attaches every company to every MatchingConfiguration (see Matching), so this normally can't happen; the timeout is the backstop. If this ever becomes routine, the cleaner fix is a Celery chord callback that triggers the workflow manager — but that is a platform change and is deliberately avoided here.

S3 media sync & exclusions

aws s3 sync --delete \
  s3://aerscape-prod-backup-ohio s3://"$AWS_STORAGE_BUCKET_NAME" \
  --source-region us-east-2 --region us-east-2 \
  --exclude 'data_import/*'

Planned change: also exclude customer files, since anonymize_db.sql already deletes their DB rows:

--exclude 'data_import/*' --exclude 'data_uploads/*' --exclude 'data_downloads/*'

Retained AMI file

One downloadable file is kept available in the AMI app: data_downloads/AMI/Company-Codes-AMI.xlsx.

Rather than carving it out of the sync with an --include, it is handled by leaving the object in the bucket (its data_downloads/ folder is no longer synced, so nothing ever deletes or overwrites it) and recreating its DB row after each restore. The data_downloads table is wiped by the restore, so prepare_ami_seed.sql inserts a single DownloadableDataPackage owned by the AMI company, pointing file at that exact key, with data_download_status = READY:

INSERT INTO data_downloads_downloadabledatapackage
    (id, owner_id, file, description, notes, uploaded_at, modified_at, data_download_status)
SELECT gen_random_uuid(), c.id, 'data_downloads/AMI/Company-Codes-AMI.xlsx',
       'Company Codes AMI', '', now(), now(), 'READY'
FROM accounts_company c
WHERE c.name = 'AMI'
  AND NOT EXISTS ( SELECT 1 FROM data_downloads_downloadabledatapackage d
                   WHERE d.file = 'data_downloads/AMI/Company-Codes-AMI.xlsx' );

The insert is idempotent (guarded on the file path). To retain a different or additional file, drop it in the bucket under data_downloads/AMI/… and add a matching row to that seed block.

Environment variables

AMI (target) side

Variable	Notes
DB_URL	AMI PostgreSQL hostname (required)
PGPASSWORD	AMI DB password (required)
AMI_DB_NAME	Target DB, default postgres
AWS_STORAGE_BUCKET_NAME	AMI media bucket (required)
AWS_DEFAULT_REGION	Default us-east-2 (Fargate does not auto-set it)

Prod (source) side

Variable	Notes
PROD_RESTIC_PASSWORD	Prod restic repo password (required)
PROD_AWS_ACCESS_KEY_ID / PROD_AWS_SECRET_ACCESS_KEY	Read prod backup repo (required)
SOURCE_RESTIC_REPOSITORY	Defaults to the prod backups repo

Optional

Variable	Default	Notes
STAGING_DB	prod_staging_anonymized	Staging DB name
SKIP_SLACK	false	Suppress Slack notifications
COPY_ONLY	false	Run only the S3 media sync

One-time setup

The first sync cannot seed values that don't exist yet. INITIAL_SETUP.md in the sync folder documents the manual steps done once after the initial restore: SSO config, waffle switches/flags, the default AMI workflow (the CREATED → NEW AUTO transition), and loading AMI users from Bitwarden. On subsequent syncs these are carried automatically by prepare_ami_seed.sql.

Known issues

• Sensor API keys — same Fernet plaintext limitation as prod → dev.
• No AMI DB backup — the pre-restore backup step is commented out (no restic repo configured for AMI yet).
• Matching timeout backstop — see the 2h-timeout caveat.
• Waffle switches and flags — preserved by the AMI seed. See Flags and Switches for the full list.