One Rule, One Place: Email Identity in the Database Schema

Migration 0005 on DemandScope ran late in the afternoon, and the two checks that followed turned out to be more interesting than the change itself. First, the Alembic autogenerate that compares ORM metadata against the actual schema came back clean: nothing to rewrite [4]. Second, a probe INSERT with the same email under a different tenant was rejected at the database level [1].

Read from its title alone, the change sounds trivial: drop the composite constraint uq_users_tenant_email that guarded the tenant-email pair, and declare the email column unique=True on the model. Before this, the email identity rule was enforced twice. The global unique index ix_users_email already rejected duplicate emails across tenants, while the composite constraint guarded a weaker variant inside a single tenant.

Two specifications for one rule

My first assumption: dropping the composite constraint was pure cosmetics. The stronger global index already existed, so the composite one could never be violated first. That assumption was right about current behavior and wrong about what was being maintained. Two constraints guarding one rule are two specifications of the same rule, not one specification written twice.

Imagine a refactor six months later: the product changes, and one email is now allowed in two tenants. A developer relaxes the global index and accidentally resurrects the composite constraint that was supposed to be gone. The database that used to reject duplicates now accepts them inside a tenant, because the second, older specification is still written down. Postgres evaluates every constraint independently, and violating any of them raises an error [1]. The rule that should have disappeared comes back through the back door.

The declaration shape carries consequences too. SQLAlchemy offers unique=True for an anonymous single-column constraint and the table-level UniqueConstraint for named or multi-column ones [3]. Under the hood, Postgres automatically creates a unique index for every unique constraint or primary key, and manually adding a second unique index just duplicates the first [2]. One more detail applies to composites: a multicolumn unique index only rejects rows that are equal in all of its columns at once [2], so a composite constraint genuinely has different semantics from a global unique.

Verification that makes it trustworthy

Three steps prove the change is safe, and all of them are repeatable. The 0005 to 0004 migration roundtrip leaves nothing behind, and the downgrade recreates the composite constraint in exactly its old shape. Autogenerate reports no drift between the model and the schema [4]. Finally, the cross-tenant INSERT probe with an identical email is rejected by the database, not by application validation [1].

The algorithmic transparency literature uses a fitting measure: the factors influencing a computation must be retraced, which is what a transparent implementation means [5]. A database schema is exactly that kind of specification. As long as one rule is written in one place, anyone reading the table definition can confirm that email identity is global, without running the system.

The criterion I took home

Since this migration, the question I ask when adding a constraint has changed. Not "what rule needs guarding" but "is this rule already stated somewhere else". A new constraint earns its place when it states a rule that does not exist yet. When it only duplicates a stronger rule, the right move is to drop it, because a duplicate specification is not a safety net. It is a debt that comes due the day the rule changes.

Sources

1. PostgreSQL Documentation: Constraints
2. PostgreSQL Documentation: Unique Indexes
3. SQLAlchemy Documentation: Defining Constraints and Indexes
4. Alembic Documentation: Auto Generating Migrations
5. Boche, Fono, Kutyniok: The Algorithmic Transparency Requirement (arXiv:2401.10310)