Use cases

Production-shaped Postgres test databases in CI

Shared staging is a bottleneck. One developer's in-progress migration breaks everyone else's test run. Per-PR seed scripts solve isolation but not data quality: a hundred hand-rolled rows can't replicate the edge cases sitting in hundreds of millions of production rows. Tests pass in CI against clean synthetic records; the same code fails in production against real ones.

This guide covers a third option — a postgres ephemeral test database ci: each pull request spins up an ephemeral Postgres database masked from a recent production slice. It carries the realistic distributions your suite needs, tears down when the job finishes, and enforces explicit masking rules for every column so the developer who adds a new PII field declares what to do with it. For a shared long-lived staging refresh, see the staging guide; for one-off tenant debugging from DR standby, see support repro.

The schema you're testing against

A typical payments platform has enough FK depth that seed scripts become a maintenance problem within months. Realistic status distributions — settled, declined, reversed, disputed — are where edge-case bugs hide, and exactly what a seed script can't credibly replicate at scale. 

CREATE TABLE merchants (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        text NOT NULL,
  mcc         char(4) NOT NULL,
  country     char(2) NOT NULL DEFAULT 'US',
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE cardholders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   text NOT NULL,
  email       text NOT NULL,
  ssn_last4   char(4) NOT NULL,
  address     text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE cards (
  id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  cardholder_id  bigint NOT NULL REFERENCES cardholders(id),
  pan_last4      char(4) NOT NULL,
  network        text NOT NULL,
  expiry         date NOT NULL,
  status         text NOT NULL DEFAULT 'active'
);

CREATE TABLE transactions (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  card_id       bigint NOT NULL REFERENCES cards(id),
  merchant_id   bigint NOT NULL REFERENCES merchants(id),
  amount_cents  bigint NOT NULL,
  currency      char(3) NOT NULL DEFAULT 'USD',
  status        text NOT NULL,
  created_at    timestamptz NOT NULL DEFAULT now(),
  metadata      jsonb NOT NULL DEFAULT '{}'
);

CREATE TABLE transaction_events (
  id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  transaction_id  bigint NOT NULL REFERENCES transactions(id),
  event_type      text NOT NULL,
  occurred_at     timestamptz NOT NULL DEFAULT now(),
  payload         jsonb NOT NULL DEFAULT '{}'
);

CREATE TABLE disputes (
  id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  transaction_id  bigint NOT NULL REFERENCES transactions(id),
  reason_code     text NOT NULL,
  status          text NOT NULL DEFAULT 'open',
  evidence        jsonb,
  filed_at        timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE refunds (
  id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  transaction_id  bigint NOT NULL REFERENCES transactions(id),
  amount_cents    bigint NOT NULL,
  status          text NOT NULL DEFAULT 'pending',
  created_at      timestamptz NOT NULL DEFAULT now()
);

Why seed scripts miss the bugs CI should catch

A seed script is a bet that you can enumerate the edge cases in production data. You can't. Production has zero-amount transactions, non-ASCII cardholder names, MCC codes your parser doesn't handle, timestamptz values near timezone boundaries, and disputed transactions in every combination of status and reason_code your state machine supports.

A seed script has twenty clean records and tests that pass on them. The bug ships because the seed data doesn't include the reversed transaction with an open dispute that your refund logic mishandles. Masked production data has that record — because a real customer found it.

Source: read replica, not DR standby

The support repro workflow reads from the DR warm standby because it needs the freshest possible data for a specific ticket. CI doesn't need that. What CI needs is a stable, reachable source that isn't the primary.

The natural fit is a production read replica with inbound access from your CI runner network. Most teams already have one for analytics; extending that access to PrivaCI's SOURCE_DB_URL is a firewall rule, not new infrastructure.

If runners can't reach any live production database — common with GitHub-hosted runners and a private VPC — use the nightly masked snapshot from your full-database staging mask as the source instead. That workflow produces a pg_dump artifact; restore it into the CI service container before the test run and skip the PrivaCI step in CI entirely. The trade-off is one day of data lag, which is acceptable for CI.

Subsetting for CI speed

Running CI against a full production clone isn't practical. The goal is a slice large enough to carry realistic status distributions, edge cases, and FK relationships — and small enough to finish in CI time.

Rooting the subset on transactions with a rolling time window pulls the full FK closure automatically: cards and cardholders referenced by those transactions, merchants, and all child rows (events, disputes, refunds). 

# commercial-extensions.yaml
version: "1.0"
subset:
  - table: public.transactions
    predicate: "created_at >= now() - interval '30 days'"

Thirty days is a reasonable starting point for a single-tenant platform or one where all merchants and cardholders are in scope for CI. On a multi-tenant platform, add an explicit tenant predicate — a time window alone pulls every merchant and cardholder active in that period, which may be more than CI needs: 

# commercial-extensions.yaml (multi-tenant)
version: "1.0"
subset:
  - table: public.transactions
    predicate: "created_at >= now() - interval '30 days' AND merchant_id IN (101, 204)"

Predicates are operator-authored SQL literals, not bind parameters. Replace 101, 204 with the merchant IDs your CI suite covers. If transaction volume is very high, narrow to seven days or oversample terminal states ( status IN ('reversed', 'disputed')) so rarer paths stay represented. The closure handles the FK graph; you only specify the root.

Masking PII and JSONB payloads

Start with privaci generate-ci to scaffold the workflow and a starter mask-rules.yaml from the source schema. Fill in explicit rules for each PII column and set strict_autodetect: true (see the policy gate section below). 

# mask-rules.yaml (excerpt)
version: "1.0"
strict_autodetect: true
global_salt: env://ANONYMIZATION_SALT
tables:
  public.cardholders:
    strategy: transform
    columns:
      full_name: { action: fake, provider: full_name }
      email:     { action: fake, provider: email }
      ssn_last4: { action: static, value: "0000" }
      address:   { action: fake, provider: address }
  public.cards:
    strategy: transform
    columns:
      pan_last4: { action: static, value: "9999" }

ssn_last4 and pan_last4 are display-only char(4) fields with no business logic depending on their specific value — static is cleaner than a fake that implies false precision. merchants carries no PII; no entry needed. Sensitive content in transactions lives in JSONB columns. 

# commercial-extensions.yaml (json_mask section)
version: "1.0"
json_mask:
  - column: public.transactions.metadata
    paths:
      - path: $.risk.device_fingerprint
        action: hash
      - path: $.risk.ip_address
        action: hash
      - path: $.raw_pan
        action: remove
  - column: public.transaction_events.payload
    paths:
      - path: $.cardholder_name
        action: fake
        provider: full_name
      - path: $.ip_address
        action: hash
      - path: $.raw_card_data
        action: remove
  - column: public.disputes.evidence
    paths:
      - path: $.cardholder_statement
        action: remove
      - path: $.merchant_contact_email
        action: hash

$.raw_pan and $.raw_card_data are removed, not hashed — hashing a value guessable from a 16-digit space doesn't provide meaningful protection. Use remove for anything that shouldn't exist in a non-production environment; use hash where a stable opaque value helps test assertions.

Wiring into GitHub Actions

The target is a Postgres service container — it lives for the job, tears down with it, and needs no explicit TTL policy. The engine clones schema from the source into the empty container before writing rows; no migration step belongs before privaci run. If a PR contains schema changes not yet in the source, those columns won't exist in the CI database — the suite runs against the source-shaped schema. To test new columns against masked data, run migrations after PrivaCI completes against the same $DATABASE_URL. 

# .github/workflows/ci.yml
name: ci
on:
  pull_request:
    branches: [main]

jobs:
  test:
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_PASSWORD: ci
          POSTGRES_DB: test
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    env:
      SOURCE_DB_URL: ${{ secrets.PROD_READ_REPLICA_URL }}
      TARGET_DB_URL: postgresql://postgres:ci@localhost:5432/test
      ANONYMIZATION_SALT: ${{ secrets.ANONYMIZATION_SALT }}
      PRIVACI_MARKETPLACE_PRODUCT_CODE: ${{ secrets.PRIVACI_MARKETPLACE_PRODUCT_CODE }}

    steps:
      - uses: actions/checkout@v4

      - name: Mask production slice into CI database
        run: |
          docker run --rm --network host \
            -e SOURCE_DB_URL -e TARGET_DB_URL -e ANONYMIZATION_SALT \
            -e PRIVACI_MARKETPLACE_PRODUCT_CODE \
            -v "$PWD/commercial-extensions.yaml:/config/commercial-extensions.yaml:ro" \
            -v "$PWD/mask-rules.yaml:/config/mask-rules.yaml:ro" \
            ghcr.io/boundarylogic/privaci-commercial:1.0.1 \
            privaci run \
              --config /config/mask-rules.yaml \
              --commercial-extensions /config/commercial-extensions.yaml

      - name: Run test suite
        env:
          DATABASE_URL: ${{ env.TARGET_DB_URL }}
        run: your-test-command

--network host lets the PrivaCI container reach the service container's localhost:5432. On macOS runners ( macos-latest), --network host doesn't work — expose the service port explicitly and set TARGET_DB_URL to postgresql://postgres:ci@127.0.0.1:5432/test. Pin the image tag from your Marketplace subscription — workflows in this guide use the current stable Commercial release.

Enforcing explicit masking rules in the PR that adds a column

strict_autodetect: true in mask-rules.yaml changes preflight when a column matches the PII pattern library but has no explicit YAML rule. Without it, preflight passes and the engine masks obvious PII via auto-detect. With it, preflight exits 3 until someone adds an explicit rule.

auto_detect: true (the default) already prevents obvious PII from landing in the CI database — cardholders.email gets masked whether or not it has a YAML entry. What strict_autodetect: true enforces is that every masking decision is a documented, reviewable config entry:

Add a separate job to enforce this in CI. With Commercial v1.0.1+, privaci preview writes the policy-diff JSON before enforcing strict mode, then exits non-zero — so if: always() on the artifact upload works without a separate jq workaround: 

# .github/workflows/ci.yml (masking policy job)
  check-masking-policy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Fail on uncovered columns
        run: |
          mkdir -p policy-diff
          docker run --rm \
            -e SOURCE_DB_URL=${{ secrets.PROD_READ_REPLICA_URL }} \
            -e TARGET_DB_URL=${{ secrets.PRIVACI_EMPTY_TARGET_URL }} \
            -v "$PWD/mask-rules.yaml:/config/mask-rules.yaml:ro" \
            -v "$PWD/policy-diff:/output" \
            ghcr.io/boundarylogic/privaci-commercial:1.0.1 \
            privaci preview \
              --config /config/mask-rules.yaml \
              --policy-diff /output/diff.json

      - name: Upload policy diff as artifact
        if: always()
        uses: actions/upload-artifact@v4
        with:
          name: policy-diff
          path: policy-diff/diff.json

With strict_autodetect: true, privaci preview exits non-zero when uncovered_strict is non-empty. The diff artifact uploads regardless so reviewers see exactly which columns need rules without re-running the job. The developer who adds the column sees the failure when they have the most context about what it contains.

What the test suite sees

After privaci run completes, the service container holds the masked source schema and the production-shaped slice. The test suite connects to DATABASE_URL and sees a normal Postgres database. 

-- Confirm realistic status distribution in the slice
SELECT status, COUNT(*) AS n
FROM   transactions
GROUP  BY status
ORDER  BY n DESC;

-- Spot-check PII masking ran
SELECT full_name, email, ssn_last4
FROM   cardholders
LIMIT  5;

-- FK integrity: closure should have pulled all referenced cards
SELECT COUNT(*) AS orphan_transactions
FROM   transactions t
LEFT   JOIN cards c ON c.id = t.card_id
WHERE  c.id IS NULL;

The status distribution query tells you whether the time window captured a representative slice. If you only see settled, the window may have landed in a quiet period — widen it or add an explicit terminal-state filter. The orphan check catches incomplete closure before a confusing test failure.

Reruns with the same salt and the same source snapshot produce the same masked data. Assertions on specific fake values won't flake between pushes on the same PR as long as the source data hasn't changed.

How long does it actually take?

The Docker pull and cold start add 20–30 seconds. Against a read replica with a 30-day transaction slice from a mid-volume platform — order of magnitude: hundreds of thousands of transactions with events and disputes — the masking run takes roughly 5–15 minutes. That's longer than a seed script and shorter than waiting for shared staging. That's the correct comparison.

If CI time is too long, narrow the subset predicate. The engine streams and doesn't buffer, so memory stays flat; wall time scales with closure size.

What PrivaCI doesn't handle here

Schema on the target. The engine clones schema from the source and writes rows into it. There is no separate migration step before the run. Run migrations after privaci run if you need columns that don't exist in the source yet.

Test fixtures that don't exist in production. If a test requires a specific edge case not present in the last 30 days, insert the fixture programmatically after PrivaCI runs against the same service container. Masked production as the base, targeted inserts for gaps — layers, not alternatives.

High-parallelism source load. CI jobs running in parallel all read from the same replica. At high concurrency, consider a daily masked snapshot from your staging refresh and a pg_restore step per PR rather than live reads per push.

What you need

Commercial plans · Engine quickstart · Configuration reference · Full-database staging mask