From de2ac5a2860bc25180971e7a11f852d9d44675b7 Mon Sep 17 00:00:00 2001 From: dujinkim Date: Wed, 6 Aug 2025 04:23:40 +0000 Subject: (대표님) 정기평가, 법적검토, 정책, 가입관련 처리 및 관련 컴포넌트 추가, 메뉴 변경 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- db/migrations/0260_cute_tomas.sql | 53 +++++++++++++++++++++++++++++++++++++++ 1 file changed, 53 insertions(+) create mode 100644 db/migrations/0260_cute_tomas.sql (limited to 'db/migrations/0260_cute_tomas.sql') diff --git a/db/migrations/0260_cute_tomas.sql b/db/migrations/0260_cute_tomas.sql new file mode 100644 index 00000000..368ce023 --- /dev/null +++ b/db/migrations/0260_cute_tomas.sql @@ -0,0 +1,53 @@ +CREATE TYPE "public"."consent_action" AS ENUM('consent', 'revoke', 'update');--> statement-breakpoint +CREATE TYPE "public"."consent_type" AS ENUM('privacy_policy', 'terms_of_service', 'marketing', 'optional');--> statement-breakpoint +CREATE TYPE "public"."policy_type" AS ENUM('privacy_policy', 'terms_of_service');--> statement-breakpoint +CREATE TABLE "consent_logs" ( + "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "consent_logs_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), + "user_id" integer NOT NULL, + "consent_type" "consent_type" NOT NULL, + "action" "consent_action" NOT NULL, + "old_status" boolean, + "new_status" boolean NOT NULL, + "policy_version" varchar(20) NOT NULL, + "ip_address" varchar(45), + "user_agent" text, + "action_timestamp" timestamp with time zone DEFAULT now() NOT NULL, + "additional_data" json +); +--> statement-breakpoint +CREATE TABLE "policy_versions" ( + "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "policy_versions_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), + "policy_type" "policy_type" NOT NULL, + "version" varchar(20) NOT NULL, + "content" text NOT NULL, + "effective_date" timestamp with time zone NOT NULL, + "is_current" boolean DEFAULT false NOT NULL, + "created_at" timestamp with time zone DEFAULT now() NOT NULL +); +--> statement-breakpoint +CREATE TABLE "user_consents" ( + "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "user_consents_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), + "user_id" integer NOT NULL, + "consent_type" "consent_type" NOT NULL, + "consent_status" boolean DEFAULT false NOT NULL, + "policy_version" varchar(20) NOT NULL, + "consented_at" timestamp with time zone DEFAULT now() NOT NULL, + "ip_address" varchar(45), + "user_agent" text, + "revoked_at" timestamp with time zone, + "revoke_reason" text, + "created_at" timestamp with time zone DEFAULT now() NOT NULL, + "updated_at" timestamp with time zone DEFAULT now() NOT NULL +); +--> statement-breakpoint +ALTER TABLE "consent_logs" ADD CONSTRAINT "consent_logs_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "user_consents" ADD CONSTRAINT "user_consents_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +CREATE INDEX "consent_logs_user_action_timestamp_idx" ON "consent_logs" USING btree ("user_id","action_timestamp");--> statement-breakpoint +CREATE INDEX "consent_logs_consent_type_idx" ON "consent_logs" USING btree ("consent_type");--> statement-breakpoint +CREATE INDEX "consent_logs_action_idx" ON "consent_logs" USING btree ("action");--> statement-breakpoint +CREATE UNIQUE INDEX "policy_versions_type_version_idx" ON "policy_versions" USING btree ("policy_type","version");--> statement-breakpoint +CREATE INDEX "policy_versions_current_idx" ON "policy_versions" USING btree ("is_current");--> statement-breakpoint +CREATE INDEX "policy_versions_effective_date_idx" ON "policy_versions" USING btree ("effective_date");--> statement-breakpoint +CREATE INDEX "user_consents_user_type_idx" ON "user_consents" USING btree ("user_id","consent_type");--> statement-breakpoint +CREATE INDEX "user_consents_consented_at_idx" ON "user_consents" USING btree ("consented_at");--> statement-breakpoint +CREATE INDEX "user_consents_policy_version_idx" ON "user_consents" USING btree ("policy_version"); \ No newline at end of file -- cgit v1.2.3