efrilmandClaude Opus 5.5
19afa50b9c
A customer's phone number (customers.phone_number, the one they log in with) has one stored form, 62 followed by the number without its 0: 6281234561234. util.NormalizePhoneNumber rewrites 0812…, +62 812…, 62812…, 812… and +62 0812…, with spaces, dashes, dots or parentheses, to it, and refuses anything that is not an Indonesian mobile number (628 and 7 to 11 more digits). It is applied wherever a customer types their number: check-phone, register, login and resend-OTP (the validator rewrites the request), and the transfer recipient. Before, the number was matched as typed and a leading 0 was refused, so the same customer written another way was not found. The masked recipient stays 08**-****-1234 as customers write numbers. Migration 000116 rewrites the numbers already stored in customers and otp_sessions. When several become the same number, the customer that already has it keeps it, else a registered one, else the oldest; the others, and numbers that are not Indonesian mobile numbers, are left as they are and cannot log in until fixed by hand (the query to find them is in the migration). Down does nothing. The migration was run, twice, against Postgres with a reduced customers and otp_sessions schema and sample numbers; not against the real schema. The Postgres tests were not run. customers.phone (the POS contact number) is not touched. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
63 lines
2.4 KiB
SQL
63 lines
2.4 KiB
SQL
-- Customer phone numbers are stored in one form, 62… without + (util.NormalizePhoneNumber),
|
|
-- and every number a customer types is rewritten to it before it is looked up. This
|
|
-- rewrites the numbers stored before, so 0812…, +62 812… and 812… become 62812….
|
|
--
|
|
-- Only one customer may have a number. When several stored numbers become the same one,
|
|
-- the customer that already has it keeps it, else a registered one (with a password),
|
|
-- else the oldest; the others are left as they are. A number that is not an Indonesian
|
|
-- mobile number is left as it is too. Neither can log in until it is fixed by hand:
|
|
--
|
|
-- SELECT id, name, phone_number FROM customers
|
|
-- WHERE phone_number IS NOT NULL AND phone_number !~ '^628[0-9]{7,11}$';
|
|
|
|
WITH stripped AS (
|
|
SELECT id, phone_number, password_hash, created_at,
|
|
regexp_replace(regexp_replace(phone_number, '[[:space:]().-]', '', 'g'), '^\+', '') AS p
|
|
FROM customers
|
|
WHERE phone_number IS NOT NULL
|
|
),
|
|
normalized AS (
|
|
SELECT id, phone_number, password_hash, created_at,
|
|
CASE
|
|
WHEN p LIKE '620%' THEN '62' || substr(p, 4)
|
|
WHEN p LIKE '0%' THEN '62' || substr(p, 2)
|
|
WHEN p LIKE '8%' THEN '62' || p
|
|
ELSE p
|
|
END AS new_phone
|
|
FROM stripped
|
|
),
|
|
ranked AS (
|
|
SELECT id, new_phone,
|
|
row_number() OVER (
|
|
PARTITION BY new_phone
|
|
ORDER BY phone_number = new_phone DESC, password_hash IS NOT NULL DESC, created_at, id
|
|
) AS rank
|
|
FROM normalized
|
|
WHERE new_phone ~ '^628[0-9]{7,11}$'
|
|
)
|
|
UPDATE customers c
|
|
SET phone_number = r.new_phone, updated_at = NOW()
|
|
FROM ranked r
|
|
WHERE c.id = r.id AND r.rank = 1 AND c.phone_number <> r.new_phone;
|
|
|
|
-- A registration started before this migration finishes with the number in its OTP
|
|
-- session, and a resent OTP is found by it.
|
|
UPDATE otp_sessions
|
|
SET phone_number = n.new_phone, updated_at = NOW()
|
|
FROM (
|
|
SELECT id,
|
|
CASE
|
|
WHEN p LIKE '620%' THEN '62' || substr(p, 4)
|
|
WHEN p LIKE '0%' THEN '62' || substr(p, 2)
|
|
WHEN p LIKE '8%' THEN '62' || p
|
|
ELSE p
|
|
END AS new_phone
|
|
FROM (
|
|
SELECT id, regexp_replace(regexp_replace(phone_number, '[[:space:]().-]', '', 'g'), '^\+', '') AS p
|
|
FROM otp_sessions
|
|
) stripped
|
|
) n
|
|
WHERE otp_sessions.id = n.id
|
|
AND n.new_phone ~ '^628[0-9]{7,11}$'
|
|
AND otp_sessions.phone_number <> n.new_phone;
|