41 lines
1.3 KiB
SQL
41 lines
1.3 KiB
SQL
|
-- Customer OTP login challenges.
|
||
|
|
--
|
||
|
|
-- Additive only: creates one new table and its indexes. No existing table,
|
||
|
|
-- column or row is touched.
|
||
|
|
--
|
||
|
|
-- Ports henza_server's SMS login (ApiMobileController.php:39-78). The reference
|
||
|
|
-- kept the code in plain text on the customer row with no expiry and no attempt
|
||
|
|
-- limit; this stores a bcrypt hash and adds both, per docs/parity/DECISIONS.md
|
||
|
|
-- D6 / T1.
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS "CustomerOtp" (
|
||
|
|
"id" TEXT NOT NULL,
|
||
|
|
"mobile" TEXT NOT NULL,
|
||
|
|
"customerId" TEXT NOT NULL,
|
||
|
|
"codeHash" TEXT NOT NULL,
|
||
|
|
"expiresAt" TIMESTAMP(3) NOT NULL,
|
||
|
|
"attempts" INTEGER NOT NULL DEFAULT 0,
|
||
|
|
"consumedAt" TIMESTAMP(3),
|
||
|
|
"requestIp" TEXT,
|
||
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
|
||
|
|
CONSTRAINT "CustomerOtp_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS "CustomerOtp_mobile_createdAt_idx"
|
||
|
|
ON "CustomerOtp"("mobile", "createdAt");
|
||
|
|
CREATE INDEX IF NOT EXISTS "CustomerOtp_customerId_idx"
|
||
|
|
ON "CustomerOtp"("customerId");
|
||
|
|
CREATE INDEX IF NOT EXISTS "CustomerOtp_expiresAt_idx"
|
||
|
|
ON "CustomerOtp"("expiresAt");
|
||
|
|
|
||
|
|
DO $$
|
||
|
|
BEGIN
|
||
|
|
ALTER TABLE "CustomerOtp"
|
||
|
|
ADD CONSTRAINT "CustomerOtp_customerId_fkey"
|
||
|
|
FOREIGN KEY ("customerId") REFERENCES "Customer"("id")
|
||
|
|
ON DELETE CASCADE ON UPDATE CASCADE;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN duplicate_object THEN NULL;
|
||
|
|
END $$;
|