diff options
Diffstat (limited to 'db/migrations/0164_demonic_spacker_dave.sql')
| -rw-r--r-- | db/migrations/0164_demonic_spacker_dave.sql | 94 |
1 files changed, 94 insertions, 0 deletions
diff --git a/db/migrations/0164_demonic_spacker_dave.sql b/db/migrations/0164_demonic_spacker_dave.sql new file mode 100644 index 00000000..2e746a12 --- /dev/null +++ b/db/migrations/0164_demonic_spacker_dave.sql @@ -0,0 +1,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");
\ No newline at end of file |