From 699c27a24b92d134768822009544d8277a938418 Mon Sep 17 00:00:00 2001 From: stuppie Date: Tue, 22 Sep 2026 11:12:20 -0600 Subject: add canonical email column in user metadata table, field in pydantic model, parsing, filtering in manager --- .../thl/user_manager/user_metadata_manager.py | 32 ++++++++++++++-------- generalresearch/models/thl/user_profile.py | 17 +++++++++++- generalresearch/thl_django/common/models.py | 5 ++++ pyproject.toml | 3 +- .../thl/test_user_manager/test_user_metadata.py | 30 +++++++++++++++++++- 5 files changed, 72 insertions(+), 15 deletions(-) diff --git a/generalresearch/managers/thl/user_manager/user_metadata_manager.py b/generalresearch/managers/thl/user_manager/user_metadata_manager.py index 91ac72e..2b18302 100644 --- a/generalresearch/managers/thl/user_manager/user_metadata_manager.py +++ b/generalresearch/managers/thl/user_manager/user_metadata_manager.py @@ -11,8 +11,9 @@ class UserMetadataManager(PostgresManager): self, product_id: str, product_user_ids: Collection[str] ) -> dict[str, UserMetadata]: query = """ - SELECT email_address, email_sha256, email_sha1, email_md5, display_name, - product_user_id, thl_user.id as user_id + SELECT email_address, canonical_email, email_sha256, + email_sha1, email_md5, display_name, + product_user_id, thl_user.id as user_id FROM thl_usermetadata RIGHT OUTER JOIN thl_user on thl_usermetadata.user_id = thl_user.id WHERE product_id = %(product_id)s @@ -32,6 +33,7 @@ class UserMetadataManager(PostgresManager): email_sha256s: Collection[str] | None = None, email_sha1s: Collection[str] | None = None, email_md5s: Collection[str] | None = None, + canonical_emails: Collection[str] | None = None, ) -> list[UserMetadata]: for arg in [ user_ids, @@ -39,6 +41,7 @@ class UserMetadataManager(PostgresManager): email_sha256s, email_sha1s, email_md5s, + canonical_emails, ]: assert arg is None or isinstance(arg, (set, list)), ( "must pass a collection of objects" @@ -47,26 +50,30 @@ class UserMetadataManager(PostgresManager): filters = [] params = {} - if user_ids: + if user_ids is not None: params["user_id"] = list(set(user_ids)) filters.append("user_id = ANY(%(user_id)s)") - if email_addresses: + if email_addresses is not None: params["email_address"] = list(set(email_addresses)) filters.append("email_address = ANY(%(email_address)s)") - if email_sha256s: + if email_sha256s is not None: params["email_sha256"] = list(set(email_sha256s)) filters.append("email_sha256 = ANY(%(email_sha256)s)") - if email_sha1s: + if email_sha1s is not None: params["email_sha1"] = list(set(email_sha1s)) filters.append("email_sha1 = ANY(%(email_sha1)s)") - if email_md5s: + if email_md5s is not None: params["email_md5"] = list(set(email_md5s)) filters.append("email_md5 = ANY(%(email_md5)s)") + if canonical_emails is not None: + params["canonical_emails"] = list(set(canonical_emails)) + filters.append("canonical_email = ANY(%(canonical_emails)s)") filter_str = "WHERE " + " AND ".join(filters) if filters else "" res = self.pg_config.execute_sql_query( f""" - SELECT user_id, email_address, email_sha256, email_sha1, email_md5, display_name + SELECT user_id, email_address, email_sha256, email_sha1, + email_md5, display_name, canonical_email FROM thl_usermetadata {filter_str} """, @@ -146,6 +153,7 @@ class UserMetadataManager(PostgresManager): UPDATE thl_usermetadata SET email_address = %(email_address)s, + canonical_email = %(canonical_email)s, email_sha256 = %(email_sha256)s, email_sha1 = %(email_sha1)s, email_md5 = %(email_md5)s, @@ -162,12 +170,12 @@ class UserMetadataManager(PostgresManager): return self.pg_config.execute_write( query=""" INSERT INTO thl_usermetadata ( - user_id, email_address, email_sha256, - email_sha1, email_md5, display_name + user_id, email_address, canonical_email, + email_sha256, email_sha1, email_md5, display_name ) VALUES ( - %(user_id)s, %(email_address)s, %(email_sha256)s, - %(email_sha1)s, %(email_md5)s, %(display_name)s + %(user_id)s, %(email_address)s, %(canonical_email)s, + %(email_sha256)s, %(email_sha1)s, %(email_md5)s, %(display_name)s ); """, params=user_metadata.to_db(), diff --git a/generalresearch/models/thl/user_profile.py b/generalresearch/models/thl/user_profile.py index 9df7b23..091ab81 100644 --- a/generalresearch/models/thl/user_profile.py +++ b/generalresearch/models/thl/user_profile.py @@ -3,6 +3,7 @@ from __future__ import annotations import hashlib from typing import Annotated, Any, Self +import email_normalize from pydantic import ( BaseModel, ConfigDict, @@ -24,7 +25,9 @@ class UserMetadata(BaseModel): user_id: SkipJsonSchema[PositiveInt] = Field(exclude=True, lt=MAX_INT32) - email_address: EmailStr | None = Field(default=None, examples=["contact@mail.com"]) + email_address: EmailStr | None = Field( + default=None, examples=["Donald.Duck+123@googlemail.com"] + ) display_name: str | None = Field( default=None, @@ -32,6 +35,16 @@ class UserMetadata(BaseModel): description="A public name chosen by the user. Can be used in leaderboards or event stream.", ) + @computed_field( + description="Normalized email address by stripping mailbox-provider-specific behaviors such as plus addressing and period ignoring", + examples=["donaldduck@gmail.com"], + ) + def canonical_email(self) -> EmailStr | None: + if self.email_address is None: + return None + + return email_normalize.normalize(self.email_address).normalized_address + @computed_field def email_md5( self, @@ -98,6 +111,8 @@ class UserMetadata(BaseModel): "display_name": display_name, } ) + if kwargs.get("canonical_email") is not None: + assert obj.canonical_email == kwargs["canonical_email"], "canonical email mismatch" if kwargs.get("email_md5") is not None: assert obj.email_md5 == kwargs["email_md5"], "email_md5 mismatch" diff --git a/generalresearch/thl_django/common/models.py b/generalresearch/thl_django/common/models.py index ffd9662..67c5ca5 100644 --- a/generalresearch/thl_django/common/models.py +++ b/generalresearch/thl_django/common/models.py @@ -369,11 +369,15 @@ class THLUserMetadata(models.Model): to=THLUser, on_delete=models.RESTRICT, null=False, primary_key=True ) + email_address = models.CharField(max_length=320, null=True) email_sha256 = models.CharField(max_length=64, null=True) email_sha1 = models.CharField(max_length=40, null=True) email_md5 = models.CharField(max_length=32, null=True) + # "Donald.Duck+123@googlemail.com" -> "donaldduck@gmail.com" + canonical_email = models.CharField(max_length=320, null=True) + # Not unique within a BP, or anything like that. A user # can set this to whatever they like. No index # as we will not ever look up a user by their name. @@ -386,6 +390,7 @@ class THLUserMetadata(models.Model): models.Index(fields=["email_sha256"]), models.Index(fields=["email_sha1"]), models.Index(fields=["email_md5"]), + models.Index(fields=["canonical_email"]), ] diff --git a/pyproject.toml b/pyproject.toml index 0e6fd21..2b8aee9 100644 --- a/pyproject.toml +++ b/pyproject.toml @@ -4,7 +4,7 @@ build-backend = "setuptools.build_meta" [project] name = "generalresearch" -version = "3.5.4" +version = "3.5.5" description = "Python Utilities for General Research" readme = "README.md" requires-python = ">=3.14" @@ -14,6 +14,7 @@ dependencies = [ "psycopg", "cachetools", "decorator", + "email-normalize", "influxdb", "limits", "more-itertools", diff --git a/tests/managers/thl/test_user_manager/test_user_metadata.py b/tests/managers/thl/test_user_manager/test_user_metadata.py index eb6a272..515c5bc 100644 --- a/tests/managers/thl/test_user_manager/test_user_metadata.py +++ b/tests/managers/thl/test_user_manager/test_user_metadata.py @@ -17,7 +17,6 @@ if TYPE_CHECKING: class TestUserMetadataManager: - def test_get_notset( self, user: User, @@ -113,3 +112,32 @@ class TestUserMetadataManager: with pytest.raises(expected_exception=ValueError) as e: res = user_metadata_manager.get(email_address=email_address) assert "More than 1 result returned!" in str(e.value) + + def test_canonical( + self, + product: Product, + user_factory: Callable[..., User], + user_metadata_manager: UserMetadataManager, + ): + + u: User = user_factory(product=product) + + rand_part = uuid4().hex[:12] + local = f"Example.{rand_part}" + expected_canonical = f"example{rand_part}@gmail.com" + + um = UserMetadata( + user_id=u.user_id, email_address=f"{local}+123@googlemail.com" + ) + assert um.canonical_email == expected_canonical + user_metadata_manager.update(user_metadata=um) + + um.email_address = f"{local}+456@googlemail.com" + user_metadata_manager.update(user_metadata=um) + + um2 = user_metadata_manager.get(email_address=um.email_address) + assert um2.email_address == f"{local}+456@googlemail.com" + assert um2.canonical_email == expected_canonical + + res = user_metadata_manager.filter(canonical_emails=[expected_canonical]) + assert len(res) == 1 -- cgit v1.2.3