sh-notion/notion_data_team_no_files/Payment Validation Set data problems 2382b2ecb24243449caac4687f044391.md

Payment Validation Set data problems

Summary

• We can’t tell what paid services or what prices were offered in any past Guest Journey.
• We can only know what services and prices are set right now (you can find some explainers and queries below on how to achieve this).
• This won’t change until . Even in that case, past history is gone for good and we can’t rebuild it.

Research

While working on Joan’s request 19459, where we were trying to obtain guests that selected FeewithDeposit from certain hosts that have the option available in their listings, we discovered that we couldn’t make the join between live.dbo.PaymentValidationSet and live.dbo.VerificationRequest.

Taking a closer look to these tables we encountered many NULL values insidelive.dbo.VerificationRequest for PaymentValidationSetId, in the following query result it shows that around 99% of the values are missing.

select PaymentValidationSetId, count(*)

from VerificationRequest vr

group by PaymentValidationSetId

Here is also the query used to obtain the PaymentValidationSetId with FeewithDeposit active

select *

from PaymentValidationSet pvs

left join PaymentValidationSetToCurrency pvstc on pvstc.PaymentValidationSetId = pvs.Id

where pvstc.DisabledValidationOptions & 4 <= 0

and pvs.IsActive = 1

and pvstc.CurrencyIso = 'GBP'

How it works and how to get the data

This PaymentValidationSetId inside live.dbo.VerificationRequest was added by someone that didn’t backfill the data and they are not sure how correct it is https://guardhog.visualstudio.com/Superhog/_workitems/edit/13564.

Currently the way we can obtain the data of options available that the hosts is setting for each accommodation is looking into PaymentValidationSetId inside live.dbo.Accommodation, which unfortunately will only give the current PaymentValidationSetId so we won’t have any history. In case this value is NULL then is using the default PaymentValidationSetId (that it could be modified), otherwise it uses a custom version payment.

In case is using the default PaymentValidationSetId , we have to check for all the default versions on the live.dbo.PaymentValidationSetId table which can be found by the SuperhogUserId = Booking.CreatedByUserId or SuperhogUserId is NULL and use the version that would have been active for this booking when created (Booking.CreatedDate )

Using the query given by Lawrence we can obtain the options available for each of these PaymentValidationSetId , though this is still for confirmation on if it is working correctly.

Example:

1- First for a random BookingId=738395

**select** *

**from** Booking *b*

**where** BookingId =738395

--2024-06-01 CreatedDate

--760891 VerificatioRequestId

--75feb1f7-848d-4394-aa08-c8d82f34f80e CreatedByUserId

--85757 AccommodationId

We get the AccommodationId, VerificationRequestId, CreatedByUsedId (Host) and CreatedDate

2- We check the PaymentValidationId inside Accommodation

**select** * **from** Accommodation a

**where** AccommodationId = 85757

PaymentValidationSetId is set to NULL so it uses the default version of PaymentValidationSet

3- We obtain the data from PaymentValidationSet

**select** *

**from** PaymentValidationSet pvs

**where** SuperhogUserId = **'75feb1f7-848d-4394-aa08-c8d82f34f80e'** **or** SuperhogUserId **is** **null**

Here we can see that it has multiple versions for the default payment being the most recent one Id=2186 and it was also created before the booking.CreatedDate, but it is not active. So in this case the PaymentValidationSet is Id=1

FYI This is also a weird case because booking.CreatedDate is before the PaymentValidationSet.UpdatedDate of Id=2186, so it makes sense that it uses that one but we cannot confirm it

4- We use Lawrence’s query to obtain Payment options for the previously obtained PaymentValidationSet

SELECT
PaymentValidationSet ,
CASE WHEN DisabledValidationOptions & 1 > 0 THEN 0 ELSE 1 END AS "Fee(1)",
CASE WHEN DisabledValidationOptions & 2 > 0 THEN 0 ELSE 1 END AS "Membership(2)",
CASE WHEN DisabledValidationOptions & 4 > 0 THEN 0 ELSE 1 END AS "FeeWithDeposit(4)",
CASE WHEN DisabledValidationOptions & 8 > 0 THEN 0 ELSE 1 END AS "Waiver(8)",
CASE WHEN DisabledValidationOptions & 16 > 0 THEN 0 ELSE 1 END AS "NoCover(16)"
FROM
PaymentValidationSetToCurrency pvstc
WHERE
CurrencyIso = 'GBP'
and PaymentValidationSetId = 1

5- Finally we can verify that the Payment chosen by the guest coincides with the available options for this PaymentValidationSet

**select** Value

**from** Verification v

**where** Name = **'PaymentValidation'**

**and** VerificationRequestId = 760891

Current explanation and work from Development Team

There are 4 levels of settings when it comes the payment validation sets

• Global level
• Account/User level
• Listing Level
• Host&Stay override

Global Level PaymentValidationSet

• these were set in the db when we first implemented payment
  • in the db there is one row with SuperhogUserId == null, this is the Global PaymentValidationSet
• where can we see them? (nowhere - yet)

Rules

• If no override has been set for an account, all guest journeys created via this account use the Global PaymentValidationSet

Account Level PaymentValidationSet

• Set in Wilbur
• When a host account is set up and the user navigates to the Wilbur -> Verification Terms tab, they will see their account set. If no previous account set is saved, they will see the Global set by default
• When they press save (regardless of if any actual changes have been made) this account will now have an account override set.
• Each time they save, the old set is expired and a new set is created so we can track history

Rules

• If an account level PaymentValidationSet has been set for an account (AND NO OTHER SET exists - e.g. listing level or host and stay), all guest journeys created via this account will use this Account Level PaymentValidationSet

Listing Level PaymentValidationSet

• A host can use their dashboard to create one or more PaymentValidationSets and then optionally apply these to one or more listings
• Part 1 - setting up and editing listing level paymentvalidationsets
  • Load host dashboard and go to account -> Deposit/Waiver Price Plans
  • The default override (account or global level, whichever applies) will come first and will not be editable
  • All other sets are listing level sets. You can add new ones and edit existing ones (cant yet delete)
• Part 2 - using the listing level paymentvalidationsets
  • Now that you have some listing level paymentvalidationsets, you can now apply them to individual listings
  • Go to the dashboard and go to Listings
  • you can use the dropdown to set a price plan for each listing.
  • by default, all listings will be assigned the default paymentvalidationset. when you choose another paymentvalidationsets from the dropdown dev then this paymentvalidationsets will be set against that listings
    • Dev Note: if you dont select one, or change it back to default the listing will simply have a NULL PaymentValidationSetId - this is how we apply the default
  • Dev Note (nice to have): should really a save notification when these are applied

Rules

If a booking is created against a listing with a listing paymentvalidationsets, then the payment values that are used are from that listing paymentvalidationsets.  If the listing does not have one, then the default is used (Account level first, global if none of the above)

Host&Stay override

With Host & Stay, lets say

• there were 100 guest journeys already existing
• the Existing Account/User level PaymentValidationSet existed lets say PaymentValidationSetId = 456
• All existing guest journeys need to use 456, end of story - regardless of any subsequent changes
• All guest journeys created after this point need to follow the normal rules

This is the work that Yaseen did as part of sprint 39.  Unfortunately it didnt work very well as this work exposed an existing bug - we were under the mistaken impression that that because no listing level paymentvalidationsets were applied against any Host&Stay listings (but two did actually exist) - this exposed an error in the code whereby we just retrieved all of the paymentvalidationsets for that host and just simple picked the top paymentvalidationset without applying any other filter.  We need to fix this because it's wrong (!)

So, three things need to be done

• fix the bug that host&stay exposed
• remove duplicate code which handles paymentvalidationsets and have a single source of truth
• unit test and end to end test to make sure all rules are followed correctly

Upcoming Changes

In future work there is a plan to add the PaymentValidationSet to VerificationRequest for easier connection between as well as having some historic data for each of them, which is inexistent today. Also a simplification as to how to get the available payment options set by the host in their PaymentValidationSet, so we don’t have to depend on a complex query with virtually no explanation as to how it works