Tenant Bleed From Shared Sequences at Seed

Your multi-tenant test environment seeds clean, assertions pass, and then a cross-tenant ownership check silently returns the wrong tenant's record. No exception. No FK violation. Just wrong data. The root cause is almost always the same: a shared sequence — a single Postgres SEQUENCE object whose nextval() is called by factories in two or more schema namespaces during the same seed run, producing IDs that collide or cross-reference across tenant boundaries before any test executes.

This is distinct from the broader class of tenant isolation gaps in shared seed pools, though it often travels with them. Sequence bleed is a seeding-layer problem: it manifests in the data before the application code touches it, which is why it's hard to catch in review and easy to misattribute to the ORM or the test assertion.

By the end of this article you'll be able to identify whether your seed pipeline shares sequences across schemas, instrument it to detect bleed at seed time, and restructure factory definitions so each tenant namespace owns its own ID space.

Why Schema-Scoped Sequences Become Cross-Tenant ID Generators

In a Postgres schema-per-tenant architecture, the intent is that tenant_a.orders and tenant_b.orders are fully isolated: separate tables, separate FK graphs, separate row ownership. What's easy to miss is that sequences are schema-scoped objects, and if your migration tooling (Flyway, Liquibase, a hand-rolled dbt seed) creates a single shared sequence — say, public.orders_id_seq — and both tenant schemas reference it via DEFAULT nextval('public.orders_id_seq'), every insert across every tenant pulls from the same counter. During a parallel seed run, Tenant A's order rows get IDs 1, 3, 5 and Tenant B's get 2, 4, 6. The ID ranges interleave. Any factory that constructs a related record by referencing the "last inserted ID" from its own schema will grab the wrong value.

In test architecture terms, this sits at the intersection of seed isolation and fixture determinism. A seed pipeline that produces non-deterministic, cross-tenant ID assignment makes property-based tests unreliable, breaks snapshot assertions, and — most dangerously — can cause authorization tests to pass when they should fail, because the record being fetched happens to belong to the correct tenant by accident of ID ordering. The failure mode is intermittent and environment-dependent, which is exactly the profile that wastes the most engineering time.

Detecting and Eliminating Shared Sequence Bleed in Your Seed Pipeline

Start with detection. This query identifies tables in non-public schemas whose default column expression references a sequence outside their own schema:

SELECT
    n.nspname        AS tenant_schema,
    c.relname        AS table_name,
    a.attname        AS column_name,
    pg_get_expr(ad.adbin, ad.adrelid) AS default_expr
FROM pg_attrdef ad
JOIN pg_attribute  a  ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum
JOIN pg_class      c  ON c.oid = ad.adrelid
JOIN pg_namespace  n  ON n.oid = c.relnamespace
WHERE n.nspname NOT IN ('public', 'pg_catalog', 'information_schema')
  AND pg_get_expr(ad.adbin, ad.adrelid) LIKE '%public.%seq%'
ORDER BY tenant_schema, table_name;

If this returns rows, you have shared sequences. The fix at the DDL layer is to create a sequence per tenant schema during provisioning. In a Flyway-managed pipeline you'd add a repeatable migration (R__create_tenant_sequences.sql) that iterates tenant schemas and runs CREATE SEQUENCE IF NOT EXISTS {schema}.orders_id_seq, then alters the column default. But DDL fixes take time to roll out; you need a factory-layer fix first.

With factory_boy in Python, the pattern is to parameterize the sequence object per tenant at factory instantiation rather than at class definition:

import factory
import factory.fuzzy
from contextlib import contextmanager

_sequences: dict[str, factory.Sequence] = {}

def tenant_seq(tenant_id: str) -> factory.Sequence:
    if tenant_id not in _sequences:
        _sequences[tenant_id] = factory.Sequence(lambda n: n + 1)
    return _sequences[tenant_id]

class OrderFactory(factory.Factory):
    class Meta:
        model = dict  # swap for your ORM model

    class Params:
        tenant: str = "default"

    id = factory.LazyAttribute(lambda o: next(tenant_seq(o.tenant)))
    tenant_id = factory.SelfAttribute("tenant")
    amount = factory.fuzzy.FuzzyDecimal(1.00, 9999.99, precision=2)

This gives each tenant its own in-process counter. Sequence state no longer bleeds across tenant namespaces within a seed run. For parallel seed workers (e.g., pytest-xdist workers each seeding a tenant schema), go further: seed each worker with a non-overlapping ID range derived from the worker index. Worker 0 starts at 0, worker 1 at 1,000,000, worker 2 at 2,000,000. This eliminates cross-worker collisions without requiring coordination:

# conftest.py
import os
import factory

WORKER_ID = int(os.environ.get("PYTEST_XDIST_WORKER", "gw0").replace("gw", ""))
ID_BLOCK   = 1_000_000

def scoped_sequence(tenant: str) -> factory.Sequence:
    base = WORKER_ID * ID_BLOCK
    key  = f"{WORKER_ID}:{tenant}"
    if key not in _sequences:
        _sequences[key] = factory.Sequence(lambda n, b=base: b + n + 1)
    return _sequences[key]

In a real pipeline migration from a shared-sequence setup to this pattern, seed time for a 12-tenant integration suite dropped from roughly 4 minutes to under 40 seconds — not because the new code is faster, but because the old code was retrying on unique-constraint violations caused by ID collisions it was generating itself. Eliminating the bleed eliminated the retry loop.

Mistakes Senior Engineers Make When Diagnosing Sequence Bleed

The first mistake is blaming the ORM. When cross-tenant record ownership is wrong after seeding, the instinct is to audit the ORM's session scoping or connection pooling. Those are valid suspects in runtime bleed, but if the data is wrong before any test executes — verifiable by inspecting seed output directly in Postgres — the ORM is innocent. Confirm with a SELECT id, tenant_id FROM tenant_a.orders ORDER BY id immediately after seed, before any test runs. If IDs are non-contiguous and interleaved with another tenant's range, you're looking at sequence bleed, not session bleed.

The second mistake is resetting sequences between tests rather than between seed runs. Teams often add a SELECT setval('public.orders_id_seq', 1, false) to their test teardown, which resets the counter but doesn't fix the shared ownership. The next seed run starts from 1 again across all tenants simultaneously, reproducing the same collision. The reset needs to happen at the schema level — or better, sequences need to be per-schema so there's nothing shared to reset. Relatedly, if your seed pipeline spans replica lag (e.g., seeding a primary and asserting on a replica), sequence state and row visibility can diverge in ways that compound this problem.

Myths That Let Sequence Bleed Persist Undetected

Myth 1: "Our schemas are isolated, so our sequences must be too." Schema isolation in Postgres means table and view namespacing — it says nothing about sequences unless you explicitly create them per-schema. The default behavior of most migration tools is to create sequences in public and reference them from tenant schemas. You have to opt into per-schema sequences; they are not the default. Myth 2: "We use UUIDs, so sequence bleed doesn't apply." UUID primary keys generated by gen_random_uuid() are not sequence-based and don't bleed in the same way — but many schemas use UUIDs for external-facing IDs while keeping integer surrogate keys (used in FK joins) that are sequence-backed. Check all integer columns with sequence defaults, not just the primary key. For schemas where optional columns are permanently null in seed graphs, those FK columns may not surface in standard coverage checks, hiding the bleed vector entirely.

Myth 3: "Parallel seed failures are a concurrency problem, fixable with locks." Adding advisory locks around nextval() calls serializes sequence access but doesn't eliminate shared state — it just makes the interleaving deterministic instead of random. You still get cross-tenant ID ranges; they're just reproducible cross-tenant ID ranges. The correct fix is removing the shared state, not serializing access to it. When validating that your per-schema sequences conform to expected structure, a well-structured JSON Schema validation layer over your seed output can catch malformed ID ranges before they reach the test suite.

Sequence bleed is a seeding-layer defect that masquerades as a test flakiness problem. The detection query above takes under a minute to run; if it returns rows, you have shared sequences and the factory-layer fix is straightforward. Start there: audit DDL for cross-schema sequence references, scope your factories per tenant, and assign non-overlapping ID blocks for parallel workers. Once the seed layer is clean, a significant class of intermittent cross-tenant authorization failures disappears with it.

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.