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
|
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");
|
