diff options
Diffstat (limited to 'db/migrations/0260_cute_tomas.sql')
| -rw-r--r-- | db/migrations/0260_cute_tomas.sql | 53 |
1 files changed, 53 insertions, 0 deletions
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 |