production-data-sync

name	Production Data Sync
description	Syncing dev database changes to production. Covers incremental delta export/import, bulk pg_restore for empty tables, and the SSH pipeline to reach prod PostgreSQL inside Docker.

Production Data Sync

Overview

Dev is the authoring environment for LRT/LAT data. Production receives promoted data via two mechanisms:

1. Delta export (mix data.export_delta) -- incremental SQL (INSERT ON CONFLICT) for tables that already have data on prod
2. pg_restore -- bulk COPY for tables that are empty or need full replacement on prod

Core Principles

1. Prod PostgreSQL is NOT directly reachable

The shared_postgres container only exposes port 5432 inside Docker. SSH tunnels (-L 5437:postgres:5432) fail because postgres is a Docker-internal hostname.

The working pipeline: pipe SQL/dumps through SSH + docker exec:

cat file.sql | ssh sertantai-hz "docker exec -i shared_postgres psql -U postgres -d sertantai_legal_prod"

2. Choose the right tool for the job

Scenario	Tool	Why
Table has data on prod, need incremental update	mix data.export_delta	Generates idempotent INSERT ON CONFLICT
Table is empty on prod, bulk load needed	pg_restore via SSH pipe	COPY is orders of magnitude faster than INSERTs
Small table (<1K rows)	Either works	Delta is fine for small counts

3. FK ordering matters

Always restore/apply in this order (parents before children):

1. legal_register_uk (parent — was uk_lrt)
2. legal_articles_uk (FK to legal_register — was lat)
3. amendment_annotations (FK to legal_register)
4. scrape_sessions
5. scrape_session_records (FK to scrape_sessions)
6. cascade_affected_laws
7. law_edges (no FK, text name refs)
8. si_code_families (derived/materialized)

Note: After the partition migration (2026-05-18), uk_lrt and lat are views backed by legal_register (partitioned) and legal_articles (partitioned). Delta sync uses the views transparently. Bulk pg_restore/dump must target the partition tables (legal_register_uk, legal_articles_uk) directly.

---

Common Pitfalls & Solutions

Pitfall 1: Single transaction for large imports

Wrapping 150K+ INSERT statements in BEGIN;/COMMIT; holds all changes in memory until commit. For very large imports this is slow and can time out.

Solution: Split by table. Each table gets its own transaction.

Pitfall 2: Triggers fire during pg_restore

The propagate_lat_stats() trigger on lat tries to UPDATE uk_lrt during COPY. Inside pg_restore the search_path may not resolve uk_lrt, causing the restore to fail with 0 rows.

Solution: Disable triggers before restore, re-enable after, then propagate stats manually:

# Disable triggers on the partition (not the view)
ssh sertantai-hz "docker exec shared_postgres psql -U postgres -d sertantai_legal_prod \
  -c 'ALTER TABLE legal_articles_uk DISABLE TRIGGER ALL;'"

# Restore
gzip -c /mnt/nas/sertantai-data/data/snapshots/latest/legal_articles_uk.dump | \
  ssh sertantai-hz "gunzip | docker exec -i shared_postgres pg_restore \
  -U postgres -d sertantai_legal_prod --data-only --no-owner"

# Re-enable
ssh sertantai-hz "docker exec shared_postgres psql -U postgres -d sertantai_legal_prod \
  -c 'ALTER TABLE legal_articles_uk ENABLE TRIGGER ALL;'"

# Propagate stats manually
ssh sertantai-hz "docker exec shared_postgres psql -U postgres -d sertantai_legal_prod -c \"
  UPDATE legal_register
  SET lat_count = COALESCE((SELECT COUNT(*) FROM legal_articles WHERE law_id = legal_register.id AND country = legal_register.country), 0),
      latest_lat_updated_at = (SELECT MAX(updated_at) FROM legal_articles WHERE law_id = legal_register.id AND country = legal_register.country)
  WHERE country = 'uk';
\""

Pitfall 3: Delta sync does NOT propagate deletes

mix data.export_delta generates INSERT ON CONFLICT (upsert) statements. Rows deleted in dev are not deleted from prod. After large cleanup/QA sessions in dev, prod will accumulate orphaned rows.

Solution: After applying a delta, compare counts. If prod has more rows than dev, run an orphan cleanup (see Pattern 5).

Pitfall 4: Derived tables must be rebuilt on prod, not synced

Some tables (e.g., si_code_families) are derived/materialized from canonical data — they're rebuilt by mix tasks like mix law_edges.rebuild, not managed as Ash resources. These should NOT be added to the delta config because:

• They have no Ash resource (the delta exporter relies on Ash resources for column mapping)
• They're downstream artifacts — syncing them creates divergence risk if the rebuild task runs later
• The correct approach is to rebuild them on prod after syncing the upstream canonical tables

# After syncing uk_lrt to prod, rebuild derived tables:
ssh sertantai-hz "docker exec sertantai_legal_app bin/sertantai_legal eval 'Mix.install([]); Mix.Task.run(\"law_edges.rebuild\")'"

# Or for first-time population, bulk copy from dev:
PGPASSWORD=postgres psql -h localhost -p 5436 -U postgres -d sertantai_legal_dev -c "
  COPY (SELECT si_code, family, law_count, pct FROM si_code_families ORDER BY si_code, family) 
  TO STDOUT WITH CSV HEADER
" | ssh sertantai-hz "docker exec -i shared_postgres psql -U postgres -d sertantai_legal_prod -c \"
  TRUNCATE si_code_families;
  COPY si_code_families(si_code, family, law_count, pct) FROM STDIN WITH CSV HEADER;
\""

Pitfall 5: --limit N with FK tables

Using mix data.export_delta --limit 5 exports 5 rows per table, but child table rows may reference parent rows not in that 5-row slice. Use --limit only with --tables on a single table, or omit it for production exports.

---

Working Patterns

Pattern 1: Incremental sync (routine, after initial load)

cd backend

# 1. Check what's changed
mix data.export_delta --dry-run

# 2. Export delta (uses watermarks from last_sync.json)
mix data.export_delta

# 3. Apply to prod
cat ../scripts/sync/delta_TIMESTAMP.sql | \
  ssh sertantai-hz "docker exec -i shared_postgres psql -U postgres \
  -d sertantai_legal_prod -v ON_ERROR_STOP=1"

Pattern 2: Bulk load empty tables (first-time or full replacement)

# For each empty table, use pg_restore from NAS snapshot:
gzip -c /mnt/nas/sertantai-data/data/snapshots/latest/TABLE.dump | \
  ssh sertantai-hz "gunzip | docker exec -i shared_postgres pg_restore \
  -U postgres -d sertantai_legal_prod --data-only --no-owner"

Remember to disable/re-enable triggers for lat and amendment_annotations (see Pitfall 2).

Pattern 3: Split large delta by table

If a delta file is too large for a single transaction, split it:

# Find table section boundaries
grep -n "^-- .* rows)" scripts/sync/delta_TIMESTAMP.sql

# Extract per-table sections (add BEGIN;/COMMIT; wrappers)
# Upload compressed, apply individually
gzip -c split/1_uk_lrt.sql | ssh sertantai-hz "cat > ~/split_1.sql.gz"
ssh sertantai-hz "gunzip -c ~/split_1.sql.gz | docker exec -i shared_postgres \
  psql -U postgres -d sertantai_legal_prod -v ON_ERROR_STOP=1"

Pattern 4: Clean up orphaned rows on prod after delta sync

Delta sync only upserts — it never deletes. After large dev cleanup/QA sessions, prod accumulates rows that no longer exist in dev. Clean up by exporting dev IDs and deleting non-matching rows on prod.

Delete order is reverse FK order (children before parents):

# 1. Export dev PKs for each table to CSV
PGPASSWORD=postgres psql -h localhost -p 5436 -U postgres -d sertantai_legal_dev \
  -c "COPY (SELECT id FROM TABLE_NAME) TO STDOUT" > /tmp/TABLE_dev_ids.csv

# 2. Upload to server, load into temp table, delete orphans
#    Use NOT EXISTS (faster than NOT IN for large sets — avoids NULL-safety overhead)
cat /tmp/TABLE_dev_ids.csv | ssh sertantai-hz "docker exec -i shared_postgres psql -U postgres -d sertantai_legal_prod -c \"
  CREATE TEMP TABLE dev_ids (id UUID);
  COPY dev_ids FROM STDIN;
  CREATE INDEX ON dev_ids(id);
  DELETE FROM TABLE_NAME t WHERE NOT EXISTS (SELECT 1 FROM dev_ids d WHERE d.id = t.id);
  DROP TABLE dev_ids;
\""

Important considerations:

• The PK column varies by table: id (uuid) for most, section_id (text) for lat. Adjust the temp table type accordingly.
• Index the temp table before the DELETE — for 100K+ rows this makes a huge difference.
• Disable triggers on lat before deleting (see Pitfall 2), re-enable + propagate stats after.
• Delete children before parents: lat and amendment_annotations before uk_lrt.
• Always verify counts match dev after cleanup.

Pattern 5: Check prod state before sync

ssh sertantai-hz "docker exec shared_postgres psql -U postgres -d sertantai_legal_prod -c \"
  SELECT 'legal_register' AS t, COUNT(*), MAX(updated_at) FROM legal_register
  UNION ALL SELECT 'legal_articles', COUNT(*), MAX(updated_at) FROM legal_articles
  UNION ALL SELECT 'amendments', COUNT(*), MAX(updated_at) FROM amendment_annotations
  UNION ALL SELECT 'scrape_sessions', COUNT(*), MAX(updated_at) FROM scrape_sessions
  UNION ALL SELECT 'scrape_session_records', COUNT(*), MAX(updated_at) FROM scrape_session_records
  UNION ALL SELECT 'cascade_affected_laws', COUNT(*), MAX(updated_at) FROM cascade_affected_laws
  UNION ALL SELECT 'law_edges', COUNT(*), NULL::timestamp FROM law_edges
  UNION ALL SELECT 'si_code_families', COUNT(*), NULL::timestamp FROM si_code_families;
\""

Use the MAX(updated_at) from prod as the --since value for mix data.export_delta.

---

Troubleshooting

Error: "column X is of type jsonb[] but expression is of type jsonb"

The SQL generator was casting {:array, :map} columns as '[...]'::jsonb instead of ARRAY['...'::jsonb]. Fixed in commit 4be28a9. If you see this on old delta files, re-export.

Error: "invalid input syntax for type uuid"

The UUID formatter had a zero-padding bug for small values in the last segment (e.g., 8ccb727e instead of 00008ccb727e). Fixed in commit 4be28a9. Re-export to fix.

Error: "channel N: open failed: connect failed: Temporary failure in name resolution"

SSH tunnel target postgres can't resolve -- it's a Docker-internal hostname. Don't use SSH tunnels. Use the docker exec -i pipeline instead.

Error: "current transaction is aborted, commands ignored"

One statement in the transaction failed, cascading to all subsequent statements. Find the first error (search for the first ERROR: in output that isn't "current transaction is aborted"). Fix the issue, re-export, re-apply.

COPY failed: trigger error during pg_restore

Disable triggers on the target table before restore. See Pitfall 2 above.

---

Quick Reference

Mix Tasks

Command	Purpose
mix data.export_delta	Export using saved watermarks
mix data.export_delta --since DATE	Export since specific date
mix data.export_delta --tables uk_lrt	Export single table
mix data.export_delta --dry-run	Preview counts only
mix data.apply_delta FILE	Apply with confirmation (for non-Docker targets)
mix data.apply_delta FILE --dry-run	Validate only

SSH Pipeline Commands

# Run SQL on prod
cat file.sql | ssh sertantai-hz "docker exec -i shared_postgres psql -U postgres -d sertantai_legal_prod"

# pg_restore to prod
gzip -c file.dump | ssh sertantai-hz "gunzip | docker exec -i shared_postgres pg_restore -U postgres -d sertantai_legal_prod --data-only --no-owner"

# Query prod
ssh sertantai-hz "docker exec shared_postgres psql -U postgres -d sertantai_legal_prod -c 'SELECT COUNT(*) FROM uk_lrt;'"

# Upload file to server
gzip -c large_file.sql | ssh sertantai-hz "cat > ~/file.sql.gz"

Key Files

File	Purpose
backend/lib/sertantai_legal/sync/delta/	Core modules (Config, ColumnMapper, SqlGenerator, Exporter, Applier)
backend/lib/mix/tasks/data.export_delta.ex	Mix task wrapper
backend/lib/mix/tasks/data.apply_delta.ex	Mix task wrapper
scripts/sync/last_sync.json	Per-table watermarks for incremental export
scripts/sync/delta_*.sql	Generated delta files
.claude/plans/DATA-SYNC.md	Full architecture + promotion SOP

---

Related Skills

• NAS Data Sync: .claude/skills/nas-data-sync/ -- dev database snapshot export/import via office NAS
• Docker Restart: .claude/skills/docker-restart/ -- safe container restart procedures
• Production Deployment: .claude/skills/production-deployment/ -- deploying app + running migrations