Removing the UNIQUE constraint on auth_user.email — will it survive py4web/pydal upgrades?

[Mamisoa Andriantafika]

Hi all,

  I maintain an EMR app on py4web where auth_user stores both patients and staff. In this domain a unique email is wrong: families share an address,
  children have no email, nursing homes reuse one address, etc. I want to allow duplicate emails while keeping email mandatory, and keeping username
  strictly unique.

  Versions: py4web 1.20251012.1, pydal 20251012.3.

  The framework defines the field in py4web/utils/auth.py:406-411:

  Field(
      "email",
      requires=(IS_EMAIL(), IS_NOT_IN_DB(db, "auth_user.email")),
      unique=True,
      requires=(IS_EMAIL(), IS_NOT_IN_DB(db, "auth_user.email")),
      unique=True,
      label=self.param.messages["labels"].get("email"),
  ),

  So uniqueness is enforced at two levels:

  1. App-level validator — IS_NOT_IN_DB(...) blocks the form/insert at the validation layer.
  2. Real UNIQUE index in MySQL/MariaDB (SHOW INDEX FROM auth_user → Key_name=email, Non_unique=0), which rejects any insert()/update() with an
  IntegrityError.

  Planned approach:

  1. Override the validator at runtime, right after auth.define_tables() in common.py (app code, never overwritten by an upgrade):

  db.auth_user.email.requires = IS_EMAIL()   # still mandatory, drop IS_NOT_IN_DB
  db.auth_user.email.unique = False
  2. Drop the real DB index once, manually:

  ALTER TABLE auth_user DROP INDEX email;

  2. (username keeps its UNIQUE index — untouched.)

  Questions for the group — I'd like to confirm my mental model before doing this in production:

  1. Migration behavior. My understanding is that pydal never introspects the live database — it diffs the model definition against the stored .table file
  (databases/..._auth_user.table) and only migrates on that diff. So once I manually DROP INDEX email, pydal will not recreate it on restart, because
  unique=True only takes effect at the initial creation of a fresh table and the .table snapshot already reflects the migrated state. Is that correct? Is
  there any code path (fake_migrate, a migrate=True re-run, index reconciliation) where pydal would notice the missing index and re-add it?
  2. The .table file. Does flipping unique=False in the model after the table exists trigger any ALTER TABLE on the next migration, or is the UNIQUE clause
  effectively frozen in the .table snapshot and ignored thereafter? I want to be sure a routine restart won't produce a surprise migration.
  3. Validator override placement. Is overriding db.auth_user.email.requires after auth.define_tables() the idiomatic way to relax an Auth field, or is
  there an official hook (an Auth param, extra_fields, a define_tables override) that's more robust across versions?
  4. Future upgrades. Is there anything in the Auth lifecycle (a future change to how email is defined, a forced re-migration, auth.enable() re-running
  field setup, etc.) that could re-introduce either the IS_NOT_IN_DB validator or the UNIQUE index after a pip/uv upgrade of py4web?
  5. Fresh deploys. On a brand-new DB the framework would recreate the UNIQUE index at first table creation. My plan is to re-run the DROP INDEX script
  post-deploy. Is there a cleaner way to declare "email not unique" at table-definition time that the framework respects, so a fresh deploy never creates
  the index in the first place?

  Context: in production since 2021 (existing, already-migrated DB), MariaDB backend, and no other table references email as a foreign key, so there's no
  referential-integrity concern. My only worry is fighting the framework/pydal on every restart or upgrade.

  Thanks for any confirmation or correction.