Phantom Rows When Bulk Seeds Skip Audit Triggers

Your audit table has 4,000 rows. Your entity table has 4,312. Nobody deleted anything — you just seeded 312 records using COPY or a bulk INSERT that bypassed the AFTER INSERT trigger responsible for writing to audit_log. The rows exist, they pass FK checks, they show up in application queries — but they have no audit trail. Compliance tests go green. Audit-coverage tests don't exist. The gap ships to staging.

This is the phantom row problem in trigger-based audit chains: bulk seed operations that skip row-level triggers produce entities that are structurally valid but operationally invisible to any system that relies on audit records for state reconstruction, event sourcing, or regulatory traceability. It's not a Postgres bug — it's a test data engineering gap.

By the end of this article you'll know exactly where the bypass happens, how to detect missing audit coverage before it leaves your pipeline, and how to write seeds that either fire the trigger correctly or compensate for it explicitly.

Why Bulk Loaders Are Structurally Blind to Row-Level Triggers

Postgres COPY, MySQL LOAD DATA INFILE, and ORM bulk methods like SQLAlchemy's Session.bulk_insert_mappings() or Django's bulk_create() share a deliberate design choice: they trade per-row overhead for throughput. Row-level triggers — FOR EACH ROW — are that overhead. COPY in Postgres fires statement-level triggers but skips row-level ones entirely. bulk_create() in Django issues a single multi-row INSERT and, by default, does not invoke database triggers at all. The result is a class of seed data that looks correct at the schema layer but violates every invariant the trigger chain enforces.

In a modern test architecture, this matters most at the boundary between seed reference data and test-specific data: reference seeds often use bulk loaders for speed, while application logic assumes every row in the entity table has a corresponding audit record. Tests that exercise audit queries, event replay, or GDPR right-to-erasure workflows will silently operate on a corpus that was never audited. The phantom rows don't fail fast — they fail late, in production-like environments where the audit gap is finally observable.

Detecting and Closing the Audit Gap in Your Seed Pipeline

Start with detection. A post-seed invariant query catches the gap immediately and is cheap enough to run in CI:

-- Run after every seed step; zero rows = clean
SELECT e.id, e.created_at
FROM   entities e
LEFT   JOIN audit_log a
       ON  a.entity_id = e.id
       AND a.event_type = 'INSERT'
WHERE  a.id IS NULL;

Wire this into your seed harness as an assertion. In pytest, a session-scoped fixture that runs this query after seed_database() will catch phantom rows before any test touches the data. Fail loudly with the count — silent passes on broken data are worse than slow seeds.

The cleaner fix is to stop bypassing the trigger. For moderate seed volumes (<50k rows), use individual INSERT statements or ORM save() calls — the trigger fires, the audit row lands. For large seeds where throughput matters, write the audit rows explicitly in the same transaction:

import psycopg2, uuid, datetime

def seed_with_audit(conn, rows: list[dict]) -> None:
    with conn.transaction():
        # Bulk insert entities — fast path, triggers bypassed
        with conn.cursor() as cur:
            cur.executemany(
                "INSERT INTO entities (id, name, status) VALUES (%(id)s, %(name)s, %(status)s)",
                rows,
            )
        # Compensating audit writes — same transaction, atomically consistent
        audit_rows = [
            {
                "id": str(uuid.uuid4()),
                "entity_id": r["id"],
                "event_type": "INSERT",
                "actor": "seed_pipeline",
                "occurred_at": datetime.datetime.utcnow(),
            }
            for r in rows
        ]
        with conn.cursor() as cur:
            cur.executemany(
                """INSERT INTO audit_log (id, entity_id, event_type, actor, occurred_at)
                   VALUES (%(id)s, %(entity_id)s, %(event_type)s, %(actor)s, %(occurred_at)s)""",
                audit_rows,
            )

Wrapping both writes in a single transaction is non-negotiable — a partial seed that inserts entities but crashes before the audit rows is worse than a missing audit row, because now you have a race condition between seed retry logic and any test that starts executing. This pattern reduced seed time on a 200k-row corpus from 12 minutes (row-by-row ORM) to 9 seconds (bulk entity insert + compensating audit batch) on Postgres 15 with a standard B-tree index on entity_id.

If you're using dbt for seed orchestration, add a post-hook that calls a stored procedure to backfill audit rows, or use dbt's on-run-end hook to execute the invariant query and fail the run if the count is non-zero. For Kafka-based pipelines where entity creation events drive downstream audit consumers, ensure your seed producer emits the same entity.created events that production does — don't write directly to the entity table and expect the consumer to catch up. The related problem of orphaned audit rows from soft-delete seeds follows the same root cause: seeds that skip the state-transition path the application normally enforces.

Two Mistakes Senior Engineers Make With Trigger-Aware Seeds

Disabling triggers for the whole session, then forgetting to re-enable them. The pattern SET session_replication_role = replica; in Postgres disables triggers (and FK checks) for the session — it's a common speed hack in seed scripts. Engineers add it, ship the speed win, and then the next person inherits a seed script that silently produces phantom rows on every run. The fix is to scope it tightly: disable only for the specific bulk operation, re-enable immediately after, and add the post-seed invariant query as a hard gate. Better yet, document in the script header exactly which triggers are bypassed and why.

Assuming the ORM handles it. SQLAlchemy's bulk_insert_mappings() and Django's bulk_create() are explicit about skipping per-row signals and hooks — it's in the docs. But engineers who come from application code assume "insert = trigger fires" because that's true for single-row ORM saves. The mental model doesn't transfer. Read the bulk method's contract before using it in a seed context, and treat any bulk path as trigger-bypassing by default until you've verified otherwise.

What Most Teams Get Wrong About Trigger Coverage in Test Environments

Myth: triggers are a production concern, not a test concern. Many teams disable triggers in test databases entirely to simplify setup. That's fine for unit tests that mock the DB layer — but for integration tests that exercise audit queries, GDPR workflows, or event-sourced state reconstruction, you need the trigger chain active. If your test DB has triggers disabled, your test suite is not testing the system you're deploying. The same logic applies to partition configurations that silently exclude seeded rows — schema differences between test and prod environments produce false-green suites.

Myth: a row count match between entity and audit tables means the audit chain is healthy. Row counts can match while the audit records are wrong — incorrect event_type, null actor, mismatched occurred_at timestamps, or duplicate audit rows from a retry-without-idempotency seed. Use a structural assertion, not just a count: validate that every entity row has exactly one INSERT audit record with a non-null actor and a timestamp within a sane window of the entity's created_at. Great Expectations makes this a two-line expectation you can attach to your seed pipeline as a checkpoint.

Phantom rows from bulk seeds are a systemic gap, not a one-off oversight — they recur every time someone optimizes seed speed without auditing the trigger contract. Add the LEFT JOIN invariant query as a mandatory post-seed gate in CI, wrap compensating audit writes in the same transaction as the bulk insert, and treat trigger-bypass paths as a first-class architectural decision that gets documented. If you're also generating synthetic entities with LLM tooling, check that generated values don't introduce phantom enum members that pass the bulk insert but break trigger logic downstream.

Note: This article is for informational purposes only and is not a substitute for professional advice. If you need guidance on specific situations described in this article, consider consulting a qualified professional.