From 43673ce4a847c8f945ac7fdac973f5d1354bf09e Mon Sep 17 00:00:00 2001 From: stuppie Date: Fri, 21 Aug 2026 11:56:48 -0600 Subject: BusinessPayoutEvent: validations on bp_payouts ext_ref_id matching + tests. Add back update_ext_reference_ids in case we need it. Add an explicit supplier_payout_ext_ref_id UniqueViolation warning with helpful error message --- generalresearch/managers/thl/payout.py | 98 ++++++++++++++++++++-------- generalresearch/models/thl/payout.py | 11 +++- tests/models/thl/test_payout.py | 113 +++++++++++++++++++++++++++++++-- 3 files changed, 190 insertions(+), 32 deletions(-) diff --git a/generalresearch/managers/thl/payout.py b/generalresearch/managers/thl/payout.py index c94065e..fb4b20a 100644 --- a/generalresearch/managers/thl/payout.py +++ b/generalresearch/managers/thl/payout.py @@ -9,6 +9,7 @@ from uuid import uuid4 import numpy as np import pandas as pd +import psycopg from psycopg import sql from pydantic import AwareDatetime, NonNegativeInt, PositiveInt @@ -958,40 +959,46 @@ class BusinessPayoutEventManager(PostgresManagerWithRedis): PayoutStatus.PENDING }, "All BP Payouts must be PENDING" assert bpe.id is None, "Cannot create a BusinessPayoutEvent with an existing ID" + INSERT_SUPPLIER_PAYOUT = """ + INSERT INTO supplier_payout ( + business_id, created, amount, + status, ext_ref_id, payout_type, + request_data, order_data + ) VALUES ( + %(business_id)s, %(created)s, %(amount)s, + %(status)s, %(ext_ref_id)s, %(payout_type)s, + %(request_data)s, %(order_data)s + ) RETURNING id; + """ + INSERT_BP_PAYOUT = """ + INSERT INTO event_payout ( + uuid, debit_account_uuid, created, cashout_method_uuid, + amount, status, ext_ref_id, payout_type, order_data, + request_data, supplier_payout_id + ) VALUES ( + %(uuid)s, %(debit_account_uuid)s, %(created)s, %(cashout_method_uuid)s, + %(amount)s, %(status)s, %(ext_ref_id)s, %(payout_type)s, %(order_data)s, + %(request_data)s, %(supplier_payout_id)s + ); + """ with self.pg_config.make_connection() as conn: with conn.cursor() as c: - # ext_ref_id has a unique constraint, so we don't need to even - # do an existence check first - c.execute( - """ - INSERT INTO supplier_payout ( - business_id, created, amount, - status, ext_ref_id, payout_type, - request_data, order_data - ) VALUES ( - %(business_id)s, %(created)s, %(amount)s, - %(status)s, %(ext_ref_id)s, %(payout_type)s, - %(request_data)s, %(order_data)s - ) RETURNING id; - """, - bpe.model_dump_postgres(), - ) + # ext_ref_id (transaction_id) has a unique constraint + try: + c.execute(INSERT_SUPPLIER_PAYOUT, bpe.model_dump_postgres()) + except psycopg.errors.UniqueViolation as e: + if e.diag.constraint_name == "supplier_payout_ext_ref_id_key": + raise ValueError( + f"Cannot create a BusinessPayoutEvent with an existing " + f"transaction_id. {e.diag.message_detail}" + ) + raise supplier_payout_pk = c.fetchone()["id"] bpe.id = supplier_payout_pk for bp_pe in bpe.bp_payouts: c.execute( - """ - INSERT INTO event_payout ( - uuid, debit_account_uuid, created, cashout_method_uuid, - amount, status, ext_ref_id, payout_type, order_data, - request_data, supplier_payout_id - ) VALUES ( - %(uuid)s, %(debit_account_uuid)s, %(created)s, %(cashout_method_uuid)s, - %(amount)s, %(status)s, %(ext_ref_id)s, %(payout_type)s, %(order_data)s, - %(request_data)s, %(supplier_payout_id)s - ); - """, + INSERT_BP_PAYOUT, bp_pe.model_dump_postgres() | {"supplier_payout_id": supplier_payout_pk}, ) @@ -1187,6 +1194,43 @@ class BusinessPayoutEventManager(PostgresManagerWithRedis): self.update_business_payout_event(pk=bpe.id, status=PayoutStatus.COMPLETE) return bpe + def update_ext_reference_ids( + self, + new_value: str, + current_value: str, + ) -> None: + """ + There are scenarios where an ACH/Wire payout event was saved with + a generic or anonymized reference identifier. We may want to be + able to go back and update all of those transaction IDs. + + """ + assert new_value and current_value + + # Will raise if doesn't exist + self.get_by_ext_ref_id(ext_ref_id=current_value) + + query1 = """ + UPDATE supplier_payout + SET ext_ref_id = %(new_value)s + WHERE ext_ref_id = %(old_value)s + """ + query2 = """ + UPDATE event_payout + SET ext_ref_id = %(new_value)s + WHERE ext_ref_id = %(old_value)s + """ + params = {"new_value": new_value, "old_value": current_value} + with self.pg_config.make_connection() as conn: + with conn.cursor() as c: + c.execute(query1, params) + assert c.rowcount == 1 + c.execute(query2, params) + # As of 2025, no single Business has more than 10,000 Products, + # leave the limit in as an additional safeguard. + assert c.rowcount < 10000 + conn.commit() + # import duckdb # conn = duckdb.connect() diff --git a/generalresearch/models/thl/payout.py b/generalresearch/models/thl/payout.py index 4accd49..835c3b9 100644 --- a/generalresearch/models/thl/payout.py +++ b/generalresearch/models/thl/payout.py @@ -333,7 +333,16 @@ class BusinessPayoutEvent(BaseModel): if invalid_payout_types: raise ValueError( "All BrokerageProductPayoutEvent.payout_type values must equal " - f"BusinessPayoutEvent.payout_type ({self.payout_type=})" + f"BusinessPayoutEvent.payout_type ({self.payout_type})" + ) + + invalid_ext_ids = [ + p.ext_ref_id for p in self.bp_payouts if p.ext_ref_id != self.ext_ref_id + ] + if invalid_ext_ids: + raise ValueError( + "All BrokerageProductPayoutEvent.ext_ref_id values must equal " + f"BusinessPayoutEvent.ext_ref_id ({self.ext_ref_id})" ) return self diff --git a/tests/models/thl/test_payout.py b/tests/models/thl/test_payout.py index 3a51328..7068a41 100644 --- a/tests/models/thl/test_payout.py +++ b/tests/models/thl/test_payout.py @@ -1,10 +1,115 @@ +from uuid import uuid4 + +import pytest +from pydantic import ValidationError + +from generalresearch.currency import USDCent +from generalresearch.models.gr import Team +from generalresearch.models.thl.payout import ( + BusinessPayoutEvent, + BrokerageProductPayoutEvent, +) +from generalresearch.models.thl.wallet import PayoutType + +from generalresearch.models.gr.business import Business, BusinessAddress, BusinessType + + class TestBusinessPayoutEvent: def test_validate(self): - from generalresearch.models.gr.business import Business - instance = Business.model_validate_json( - json_data='{"id":123,"uuid":"947f6ba5250d442b9a66cde9ee33605a","name":"Example » Demo","kind":"c","tax_number":null,"contact":null,"addresses":[],"teams":[{"id":53,"uuid":"8e4197dcaefe4f1f831a02b212e6b44a","name":"Example » Demo","memberships":null,"gr_users":null,"businesses":null,"products":null}],"products":[{"id":"fc23e741b5004581b30e6478363525df","id_int":1234,"name":"Example","enabled":true,"payments_enabled":true,"created":"2025-04-14T13:25:37.279403Z","team_id":"9e4197dcaefe4f1f831a02b212e6b44a","business_id":"857f6ba6160d442b9a66cde9ee33605a","tags":[],"commission_pct":"0.050000","redirect_url":"https://pam-api-us.reppublika.com/v2/public/4970ef00-0ef7-11f0-9962-05cb6323c84c/grl/status","harmonizer_domain":"https://talk.generalresearch.com/","sources_config":{"user_defined":[{"name":"w","active":false,"banned_countries":[],"allow_mobile_ip":true,"supplier_id":null,"allow_pii_only_buyers":false,"allow_unhashed_buyers":false,"withhold_profiling":false,"pass_unconditional_eligible_unknowns":true,"address":null,"allow_vpn":null,"distribute_harmonizer_active":null}]},"session_config":{"max_session_len":600,"max_session_hard_retry":5,"min_payout":"0.14"},"payout_config":{"payout_format":null,"payout_transformation":null},"user_wallet_config":{"enabled":false,"amt":false,"supported_payout_types":["CASH_IN_MAIL","PAYPAL","TANGO"],"min_cashout":null},"user_create_config":{"min_hourly_create_limit":0,"max_hourly_create_limit":null},"offerwall_config":{},"profiling_config":{"enabled":true,"grs_enabled":true,"n_questions":null,"max_questions":10,"avg_question_count":5.0,"task_injection_freq_mult":1.0,"non_us_mult":2.0,"hidden_questions_expiration_hours":168},"user_health_config":{"banned_countries":[],"allow_ban_iphist":true},"yield_man_config":{},"balance":null,"payouts_total_str":null,"payouts_total":null,"payouts":null,"user_wallet":{"enabled":false,"amt":false,"supported_payout_types":["CASH_IN_MAIL","PAYPAL","TANGO"],"min_cashout":null}}],"bank_accounts":[],"balance":{"product_balances":[{"product_id":"fc14e741b5004581b30e6478363414df","last_event":null,"bp_payment_credit":780251,"adjustment_credit":4678,"adjustment_debit":26446,"supplier_credit":0,"supplier_debit":451513,"user_bonus_credit":0,"user_bonus_debit":0,"issued_payment":0,"payout":780251,"payout_usd_str":"$7,802.51","adjustment":-21768,"expense":0,"net":758483,"payment":451513,"payment_usd_str":"$4,515.13","balance":306970,"retainer":76742,"retainer_usd_str":"$767.42","available_balance":230228,"available_balance_usd_str":"$2,302.28","recoup":0,"recoup_usd_str":"$0.00","adjustment_percent":0.027898714644390074}],"payout":780251,"payout_usd_str":"$7,802.51","adjustment":-21768,"expense":0,"net":758483,"net_usd_str":"$7,584.83","payment":451513,"payment_usd_str":"$4,515.13","balance":306970,"balance_usd_str":"$3,069.70","retainer":76742,"retainer_usd_str":"$767.42","available_balance":230228,"available_balance_usd_str":"$2,302.28","adjustment_percent":0.027898714644390074,"recoup":0,"recoup_usd_str":"$0.00"},"payouts_total_str":"$4,515.13","payouts_total":451513,"payouts":[{"bp_payouts":[{"uuid":"40cf2c3c341e4f9d985be4bca43e6116","debit_account_uuid":"3a058056da85493f9b7cdfe375aad0e0","cashout_method_uuid":"602113e330cf43ae85c07d94b5100291","created":"2025-08-02T09:18:20.433329Z","amount":345735,"status":"COMPLETE","ext_ref_id":null,"payout_type":"ACH","request_data":{},"order_data":null,"product_id":"fc14e741b5004581b30e6478363414df","method":"ACH","amount_usd":345735,"amount_usd_str":"$3,457.35"}],"amount":345735,"amount_usd_str":"$3,457.35","created":"2025-08-02T09:18:20.433329Z","line_items":1,"ext_ref_id":null},{"bp_payouts":[{"uuid":"63ce1787087248978919015c8fcd5ab9","debit_account_uuid":"3a058056da85493f9b7cdfe375aad0e0","cashout_method_uuid":"602113e330cf43ae85c07d94b5100291","created":"2025-06-10T22:16:18.765668Z","amount":105778,"status":"COMPLETE","ext_ref_id":"11175997868","payout_type":"ACH","request_data":{},"order_data":null,"product_id":"fc14e741b5004581b30e6478363414df","method":"ACH","amount_usd":105778,"amount_usd_str":"$1,057.78"}],"amount":105778,"amount_usd_str":"$1,057.78","created":"2025-06-10T22:16:18.765668Z","line_items":1,"ext_ref_id":"11175997868"}]}' + # Doesn't validate anymore + # instance = Business.model_validate_json( + # json_data='{"id":123,"uuid":"947f6ba5250d442b9a66cde9ee33605a","name":"Example » Demo","kind":"c","tax_number":null,"contact":null,"addresses":[],"teams":[{"id":53,"uuid":"8e4197dcaefe4f1f831a02b212e6b44a","name":"Example » Demo","memberships":null,"gr_users":null,"businesses":null,"products":null}],"products":[{"id":"fc23e741b5004581b30e6478363525df","id_int":1234,"name":"Example","enabled":true,"payments_enabled":true,"created":"2025-04-14T13:25:37.279403Z","team_id":"9e4197dcaefe4f1f831a02b212e6b44a","business_id":"857f6ba6160d442b9a66cde9ee33605a","tags":[],"commission_pct":"0.050000","redirect_url":"https://pam-api-us.reppublika.com/v2/public/4970ef00-0ef7-11f0-9962-05cb6323c84c/grl/status","harmonizer_domain":"https://talk.generalresearch.com/","sources_config":{"user_defined":[{"name":"w","active":false,"banned_countries":[],"allow_mobile_ip":true,"supplier_id":null,"allow_pii_only_buyers":false,"allow_unhashed_buyers":false,"withhold_profiling":false,"pass_unconditional_eligible_unknowns":true,"address":null,"allow_vpn":null,"distribute_harmonizer_active":null}]},"session_config":{"max_session_len":600,"max_session_hard_retry":5,"min_payout":"0.14"},"payout_config":{"payout_format":null,"payout_transformation":null},"user_wallet_config":{"enabled":false,"amt":false,"supported_payout_types":["CASH_IN_MAIL","PAYPAL","TANGO"],"min_cashout":null},"user_create_config":{"min_hourly_create_limit":0,"max_hourly_create_limit":null},"offerwall_config":{},"profiling_config":{"enabled":true,"grs_enabled":true,"n_questions":null,"max_questions":10,"avg_question_count":5.0,"task_injection_freq_mult":1.0,"non_us_mult":2.0,"hidden_questions_expiration_hours":168},"user_health_config":{"banned_countries":[],"allow_ban_iphist":true},"yield_man_config":{},"balance":null,"payouts_total_str":null,"payouts_total":null,"payouts":null,"user_wallet":{"enabled":false,"amt":false,"supported_payout_types":["CASH_IN_MAIL","PAYPAL","TANGO"],"min_cashout":null}}],"bank_accounts":[],"balance":{"product_balances":[{"product_id":"fc14e741b5004581b30e6478363414df","last_event":null,"bp_payment_credit":780251,"adjustment_credit":4678,"adjustment_debit":26446,"supplier_credit":0,"supplier_debit":451513,"user_bonus_credit":0,"user_bonus_debit":0,"issued_payment":0,"payout":780251,"payout_usd_str":"$7,802.51","adjustment":-21768,"expense":0,"net":758483,"payment":451513,"payment_usd_str":"$4,515.13","balance":306970,"retainer":76742,"retainer_usd_str":"$767.42","available_balance":230228,"available_balance_usd_str":"$2,302.28","recoup":0,"recoup_usd_str":"$0.00","adjustment_percent":0.027898714644390074}],"payout":780251,"payout_usd_str":"$7,802.51","adjustment":-21768,"expense":0,"net":758483,"net_usd_str":"$7,584.83","payment":451513,"payment_usd_str":"$4,515.13","balance":306970,"balance_usd_str":"$3,069.70","retainer":76742,"retainer_usd_str":"$767.42","available_balance":230228,"available_balance_usd_str":"$2,302.28","adjustment_percent":0.027898714644390074,"recoup":0,"recoup_usd_str":"$0.00"},"payouts_total_str":"$4,515.13","payouts_total":451513,"payouts":[{"bp_payouts":[{"uuid":"40cf2c3c341e4f9d985be4bca43e6116","debit_account_uuid":"3a058056da85493f9b7cdfe375aad0e0","cashout_method_uuid":"602113e330cf43ae85c07d94b5100291","created":"2025-08-02T09:18:20.433329Z","amount":345735,"status":"COMPLETE","ext_ref_id":null,"payout_type":"ACH","request_data":{},"order_data":null,"product_id":"fc14e741b5004581b30e6478363414df","method":"ACH","amount_usd":345735,"amount_usd_str":"$3,457.35"}],"amount":345735,"amount_usd_str":"$3,457.35","created":"2025-08-02T09:18:20.433329Z","line_items":1,"ext_ref_id":null},{"bp_payouts":[{"uuid":"63ce1787087248978919015c8fcd5ab9","debit_account_uuid":"3a058056da85493f9b7cdfe375aad0e0","cashout_method_uuid":"602113e330cf43ae85c07d94b5100291","created":"2025-06-10T22:16:18.765668Z","amount":105778,"status":"COMPLETE","ext_ref_id":"11175997868","payout_type":"ACH","request_data":{},"order_data":null,"product_id":"fc14e741b5004581b30e6478363414df","method":"ACH","amount_usd":105778,"amount_usd_str":"$1,057.78"}],"amount":105778,"amount_usd_str":"$1,057.78","created":"2025-06-10T22:16:18.765668Z","line_items":1,"ext_ref_id":"11175997868"}]}' + # ) + # assert isinstance(instance, Business) + + # Make manually + b = Business( + id=123, + uuid=uuid4().hex, + name="Example", + addresses=[ + BusinessAddress( + uuid=uuid4().hex, + city="xxx", + line_1="xxx", + state="fl", + business_id=123, + ) + ], + kind=BusinessType.COMPANY, + teams=[Team(uuid=uuid4().hex, name="Example » Demo")], + products=[], + bank_accounts=[], + ) + ext_ref_id = uuid4().hex + bpe = BusinessPayoutEvent( + business_id=uuid4().hex, + amount=USDCent(100_00), + payout_type=PayoutType.ACH, + ext_ref_id=ext_ref_id, ) + bpe.bp_payouts = [ + BrokerageProductPayoutEvent( + product_id=uuid4().hex, + payout_type=PayoutType.ACH, + amount=USDCent(47_00), + cashout_method_uuid=uuid4().hex, + debit_account_uuid=uuid4().hex, + ext_ref_id=ext_ref_id, + ), + BrokerageProductPayoutEvent( + product_id=uuid4().hex, + payout_type=PayoutType.ACH, + amount=USDCent(53_00), + cashout_method_uuid=uuid4().hex, + debit_account_uuid=uuid4().hex, + ext_ref_id=ext_ref_id, + ), + ] + + # Test validations (amount sum) + with pytest.raises( + ValidationError, + match="BusinessPayoutEvent.amount must equal the sum of bp_payouts amounts", + ): + bpe.bp_payouts = [ + BrokerageProductPayoutEvent( + product_id=uuid4().hex, + payout_type=PayoutType.ACH, + amount=USDCent(47_00), + cashout_method_uuid=uuid4().hex, + debit_account_uuid=uuid4().hex, + ext_ref_id=ext_ref_id, + ) + ] + + with pytest.raises( + ValidationError, + match="All BrokerageProductPayoutEvent.ext_ref_id values must equal", + ): + bpe.bp_payouts = [ + BrokerageProductPayoutEvent( + product_id=uuid4().hex, + payout_type=PayoutType.ACH, + amount=USDCent(100_00), + cashout_method_uuid=uuid4().hex, + debit_account_uuid=uuid4().hex, + ext_ref_id="a different value", + ) + ] - assert isinstance(instance, Business) + with pytest.raises( + ValidationError, match="All BrokerageProductPayoutEvent.payout_type values" + ): + bpe.bp_payouts = [ + BrokerageProductPayoutEvent( + product_id=uuid4().hex, + payout_type=PayoutType.PAYPAL, + amount=USDCent(100_00), + cashout_method_uuid=uuid4().hex, + debit_account_uuid=uuid4().hex, + ext_ref_id=ext_ref_id, + ) + ] -- cgit v1.2.3