1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
|
CREATE TABLE "login_attempts" (
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "login_attempts_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
"email" varchar(255) NOT NULL,
"user_id" integer,
"success" boolean NOT NULL,
"ip_address" varchar(45) NOT NULL,
"user_agent" text,
"failure_reason" varchar(100),
"attempted_at" timestamp with time zone DEFAULT now() NOT NULL,
"country" varchar(2),
"city" varchar(100)
);
--> statement-breakpoint
CREATE TABLE "mfa_tokens" (
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "mfa_tokens_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
"user_id" integer NOT NULL,
"token" varchar(10) NOT NULL,
"type" varchar(20) NOT NULL,
"expires_at" timestamp with time zone NOT NULL,
"used_at" timestamp with time zone,
"is_active" boolean DEFAULT true NOT NULL,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"phone_number" varchar(20),
"attempts" integer DEFAULT 0 NOT NULL
);
--> statement-breakpoint
CREATE TABLE "password_history" (
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "password_history_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
"user_id" integer NOT NULL,
"password_hash" varchar(255) NOT NULL,
"salt" varchar(255) NOT NULL,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"replaced_at" timestamp with time zone
);
--> statement-breakpoint
CREATE TABLE "passwords" (
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "passwords_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
"user_id" integer NOT NULL,
"password_hash" varchar(255) NOT NULL,
"salt" varchar(255) NOT NULL,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"expires_at" timestamp with time zone,
"is_active" boolean DEFAULT true NOT NULL,
"strength" integer NOT NULL,
"has_uppercase" boolean NOT NULL,
"has_lowercase" boolean NOT NULL,
"has_numbers" boolean NOT NULL,
"has_symbols" boolean NOT NULL,
"length" integer NOT NULL
);
--> statement-breakpoint
CREATE TABLE "security_settings" (
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "security_settings_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
"min_password_length" integer DEFAULT 8 NOT NULL,
"require_uppercase" boolean DEFAULT true NOT NULL,
"require_lowercase" boolean DEFAULT true NOT NULL,
"require_numbers" boolean DEFAULT true NOT NULL,
"require_symbols" boolean DEFAULT true NOT NULL,
"password_expiry_days" integer DEFAULT 90,
"password_history_count" integer DEFAULT 5 NOT NULL,
"max_failed_attempts" integer DEFAULT 5 NOT NULL,
"lockout_duration_minutes" integer DEFAULT 30 NOT NULL,
"require_mfa_for_partners" boolean DEFAULT true NOT NULL,
"sms_token_expiry_minutes" integer DEFAULT 5 NOT NULL,
"max_sms_attempts_per_day" integer DEFAULT 10 NOT NULL,
"session_timeout_minutes" integer DEFAULT 480 NOT NULL,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "phone" varchar(20);--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "mfa_enabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "mfa_secret" varchar(32);--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "is_locked" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "lockout_until" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "failed_login_attempts" integer DEFAULT 0 NOT NULL;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "last_login_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "password_change_required" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "login_attempts" ADD CONSTRAINT "login_attempts_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "mfa_tokens" ADD CONSTRAINT "mfa_tokens_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "password_history" ADD CONSTRAINT "password_history_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "passwords" ADD CONSTRAINT "passwords_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "login_attempts_email_idx" ON "login_attempts" USING btree ("email");--> statement-breakpoint
CREATE INDEX "login_attempts_attempted_at_idx" ON "login_attempts" USING btree ("attempted_at");--> statement-breakpoint
CREATE INDEX "login_attempts_ip_address_idx" ON "login_attempts" USING btree ("ip_address");--> statement-breakpoint
CREATE INDEX "mfa_tokens_user_id_idx" ON "mfa_tokens" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "mfa_tokens_token_idx" ON "mfa_tokens" USING btree ("token");--> statement-breakpoint
CREATE INDEX "mfa_tokens_expires_at_idx" ON "mfa_tokens" USING btree ("expires_at");--> statement-breakpoint
CREATE INDEX "password_history_user_id_idx" ON "password_history" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "password_history_created_at_idx" ON "password_history" USING btree ("created_at");--> statement-breakpoint
CREATE INDEX "passwords_user_id_idx" ON "passwords" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "passwords_active_idx" ON "passwords" USING btree ("is_active");--> statement-breakpoint
CREATE UNIQUE INDEX "users_email_idx" ON "users" USING btree ("email");--> statement-breakpoint
CREATE INDEX "users_phone_idx" ON "users" USING btree ("phone");
|
