Marketplace freeze triggers — staging refresh + scrub runbook

ContextCopy Anchor Link

Two PostgreSQL BEFORE UPDATE triggers enforce compliance-critical freeze rules at the DB layer, independent of the AR validators:

Both raise restrict_violation (SQLSTATE 23001).

These triggers run for every write path — ActiveRecord::Base.connection.execute, raw psql, ON CONFLICT clauses, anything that issues an UPDATE. Including the staging-refresh scrub script that runs immediately after restoring a prod dump.

Why this affects staging refreshCopy Anchor Link

The typical refresh flow is:

1. pg_dump from prod → pg_restore into staging
2. Scrub script anonymizes PII: rename vendors, fake emails, possibly shuffle country for jurisdiction-rotation test scenarios, fake commission_bps for fee-tier test setups

pg_restore itself is safe — the COPY phase doesn't fire BEFORE UPDATE triggers (COPY is INSERT-shaped, no UPDATE).

Step 2 is where it breaks. A scrub UPDATE identity.vendors SET country = 'XX' WHERE … against vendors with payment_account_id set will raise PG::RestrictViolation and abort the script.

Escape hatchCopy Anchor Link

Wrap the scrub UPDATEs in a session that sets session_replication_role = replica. PostgreSQL disables user-defined triggers in that mode while keeping FK / NOT NULL / CHECK constraints in force.

BEGIN;
SET LOCAL session_replication_role = replica;

-- Scrub UPDATEs that target the frozen columns
UPDATE identity.vendors
  SET country = 'IN'
  WHERE country != 'IN'
    AND id IN (SELECT id FROM identity.vendors WHERE …);

UPDATE billing.marketplace_order_intents
  SET commission_bps = 500
  WHERE created_at > NOW() - INTERVAL '30 days';

COMMIT;

SET LOCAL scopes the change to the current transaction — when COMMIT runs, session_replication_role reverts. Do not use SET (without LOCAL) — it leaks the disabled-trigger state to subsequent statements on the same session.

Permission requirementCopy Anchor Link

session_replication_role requires superuser or a role with the pg_read_all_settings + pg_database_owner membership (or equivalent on managed PG like RDS, where it's exposed as a parameter group setting). In staging, the scrub script should run under a role with this privilege; in dev, the developer's local PG role typically has it by default.

When NOT to use thisCopy Anchor Link

• In production code paths. Production never needs to bypass these triggers. If a controller / job / service is failing on PG::RestrictViolation, the bug is the caller, not the trigger.
• In Rails migrations targeting prod. Migrations that scrub frozen columns on prod data are a separate (rare, ticketed) decision and should go through review.
• In test/spec setup helpers. Tests use real or test factories that respect the freeze rules — that's correct.

How to verify the scrub workedCopy Anchor Link

-- Confirm the triggers are still installed (defense against accidental DROP)
SELECT tgname FROM pg_trigger WHERE tgname IN (
  'vendor_country_freeze',
  'marketplace_order_commission_bps_freeze'
);
-- Should return both rows.

-- Confirm scrub completed by sampling
SELECT country, COUNT(*) FROM identity.vendors GROUP BY country;
SELECT commission_bps, COUNT(*) FROM billing.marketplace_order_intents GROUP BY commission_bps;

RelatedCopy Anchor Link

• Migration: backend/db/migrate/20260520120000_marketplace_freeze_triggers_and_drop_country_index.rb
• Spec: backend/domains/billing/spec/db_constraints/marketplace_freeze_triggers_spec.rb
• PG docs: https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-SESSION-REPLICATION-ROLE
• Issue: #1374 (item 2)