System Email Is Not Downcased On Creation Or Update

Bug Description

When creating or updating a system with an email address containing uppercase characters, the email is not downcased as intended. This results in the by_email endpoint being unable to find the system in question, requiring manual intervention to update the system email field with the downcased email address.

Affected Environment

This issue is specific to PlaceOS versions 2.2504.2 and below.

Reproduction Steps

To reproduce this issue, follow these steps:

1. Create a System with Uppercase Email: Create a new system with an email address containing uppercase characters.
2. Check Email Field in Database: Query the sys table in the database to verify that the email address is not downcased.

Expected Behavior

The expected behavior is that the email address should be downcased automatically when creating or updating a system.

Screenshots or Output

placeos=# select email from sys;
       email
--------------------
 TESTER@TESTER.TEST
 TEST@TEST.cOM
(2 rows)

Additional Context

This issue causes problems for clients who update bookable system emails, as the by_email endpoint will not be able to find the system in question.

Impact on PlaceOS

The inability to downcase email addresses on creation or update has significant implications for PlaceOS functionality. Specifically, it affects the by_email endpoint, which relies on downcased email addresses to find systems. This issue requires manual intervention to resolve, which can lead to delays and errors in system management.

Workaround

To work around this issue, clients can manually update the system email field with the downcased email address. However, this is not a sustainable solution and requires immediate attention to resolve the underlying issue.

Resolution

To resolve this issue, PlaceOS developers should update the system to downcase email addresses on creation or update. This can be achieved by modifying the relevant code to perform a case-insensitive comparison of email addresses.

Code Changes

To downcase email addresses on creation or update, the following code changes can be made:

-- Update sys table to downcase email addresses
ALTER TABLE sys
ALTER COLUMN email TYPE citext;

-- Update email addresses to downcase
UPDATE sys
SET email = LOWER(email);

Testing

To ensure that the issue is resolved, thorough testing should be performed to verify that email addresses are downcased correctly on creation or update. This includes testing the by_email endpoint to ensure that it can find systems with downcased email addresses.

Conclusion

Q: What is the issue with system email not being downcased on creation or update?

A: The issue is that when creating or updating a system with an email address containing uppercase characters, the email is not downcased as intended. This results in the by_email endpoint being unable to find the system in question.

Q: What are the affected environments?

A: This issue is specific to PlaceOS versions 2.2504.2 and below.

Q: How can I reproduce this issue?

A: To reproduce this issue, follow these steps:

1. Create a System with Uppercase Email: Create a new system with an email address containing uppercase characters.
2. Check Email Field in Database: Query the sys table in the database to verify that the email address is not downcased.

Q: What is the expected behavior?

A: The expected behavior is that the email address should be downcased automatically when creating or updating a system.

Q: What are the implications of this issue?

A: This issue causes problems for clients who update bookable system emails, as the by_email endpoint will not be able to find the system in question. It also requires manual intervention to resolve, which can lead to delays and errors in system management.

Q: What is the workaround for this issue?

A: To work around this issue, clients can manually update the system email field with the downcased email address. However, this is not a sustainable solution and requires immediate attention to resolve the underlying issue.

Q: How can I resolve this issue?

A: To resolve this issue, PlaceOS developers should update the system to downcase email addresses on creation or update. This can be achieved by modifying the relevant code to perform a case-insensitive comparison of email addresses.

Q: What code changes are required to resolve this issue?

A: To downcase email addresses on creation or update, the following code changes can be made:

-- Update sys table to downcase email addresses
ALTER TABLE sys
ALTER COLUMN email TYPE citext;

-- Update email addresses to downcase
UPDATE sys
SET email = LOWER(email);

Q: What testing should be performed to ensure that the issue is resolved?

A: To ensure that the issue is resolved, thorough testing should be performed to verify that email addresses are downcased correctly on creation or update. This includes testing the by_email endpoint to ensure that it can find systems with downcased email addresses.

Q: What is the conclusion of this issue?

A: The inability to downcase email addresses on creation or update is a critical issue that affects PlaceOS functionality. To resolve this issue, PlaceOS developers should update the system to downcase email addresses on creation or update. This requires code changes to perform a case-insensitive comparison of email addresses and thorough testing to ensure that the issue is resolved.

Q: What are the next steps to resolve this issue?

A: The next steps to resolve this issue are to:

1. Update the code: Update the relevant code to perform a case-insensitive comparison of email addresses.
2. Test the issue: Perform thorough testing to verify that email addresses are downcased correctly on creation or update.
3. Deploy the changes: Deploy the updated code to production to resolve the issue.

Q: What is the estimated time to resolve this issue?

A: The estimated time to resolve this issue is dependent on the complexity of the code changes and the testing required. However, it is expected to be a short-term issue that can be resolved within a few days or weeks.