102 lines
6.0 KiB
TypeScript
102 lines
6.0 KiB
TypeScript
import { MigrateUpArgs, MigrateDownArgs, sql } from '@payloadcms/db-postgres'
|
|
|
|
export async function up({ db, payload, req }: MigrateUpArgs): Promise<void> {
|
|
await db.execute(sql`
|
|
CREATE TYPE "public"."enum_analytics_events_device" AS ENUM('mobile', 'desktop', 'tablet');
|
|
CREATE TYPE "public"."enum_analytics_events_browser" AS ENUM('chrome', 'firefox', 'safari', 'edge', 'other');
|
|
CREATE TABLE "analytics_events" (
|
|
"id" serial PRIMARY KEY NOT NULL,
|
|
"session_id" varchar NOT NULL,
|
|
"visitor_hash" varchar NOT NULL,
|
|
"path" varchar NOT NULL,
|
|
"event_name" varchar DEFAULT 'page_view' NOT NULL,
|
|
"referrer_host" varchar,
|
|
"device" "enum_analytics_events_device",
|
|
"browser" "enum_analytics_events_browser",
|
|
"updated_at" timestamp(3) with time zone DEFAULT now() NOT NULL,
|
|
"created_at" timestamp(3) with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE TABLE "analytics_daily_stats" (
|
|
"id" serial PRIMARY KEY NOT NULL,
|
|
"date" timestamp(3) with time zone NOT NULL,
|
|
"sessions" numeric DEFAULT 0 NOT NULL,
|
|
"page_views" numeric DEFAULT 0 NOT NULL,
|
|
"unique_visitors" numeric DEFAULT 0 NOT NULL,
|
|
"event_counts" jsonb,
|
|
"device_counts" jsonb,
|
|
"browser_counts" jsonb,
|
|
"top_paths" jsonb,
|
|
"entry_paths" jsonb,
|
|
"exit_paths" jsonb,
|
|
"updated_at" timestamp(3) with time zone DEFAULT now() NOT NULL,
|
|
"created_at" timestamp(3) with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE TABLE "cookie_settings" (
|
|
"id" serial PRIMARY KEY NOT NULL,
|
|
"consent_version" varchar DEFAULT '1.0' NOT NULL,
|
|
"necessary_label" varchar DEFAULT 'Notwendig' NOT NULL,
|
|
"necessary_description" varchar DEFAULT 'Für den Betrieb der Website technisch erforderlich (z. B. Login, Sitzung). Kann nicht deaktiviert werden.' NOT NULL,
|
|
"preferences_label" varchar DEFAULT 'Präferenzen' NOT NULL,
|
|
"preferences_description" varchar DEFAULT 'Speichert Einstellungen wie z. B. bevorzugte Anzeigeoptionen.' NOT NULL,
|
|
"preferences_enabled" boolean DEFAULT true,
|
|
"statistics_label" varchar DEFAULT 'Statistik' NOT NULL,
|
|
"statistics_description" varchar DEFAULT 'Anonymisierte, datensparsame Reichweitenmessung (Seitenaufrufe, Ereignisse) ohne personenbezogene Profile.' NOT NULL,
|
|
"statistics_enabled" boolean DEFAULT true,
|
|
"updated_at" timestamp(3) with time zone,
|
|
"created_at" timestamp(3) with time zone
|
|
);
|
|
|
|
CREATE TABLE "analytics_settings" (
|
|
"id" serial PRIMARY KEY NOT NULL,
|
|
"raw_retention_days" numeric DEFAULT 90 NOT NULL,
|
|
"aggregate_retention_months" numeric DEFAULT 12 NOT NULL,
|
|
"updated_at" timestamp(3) with time zone,
|
|
"created_at" timestamp(3) with time zone
|
|
);
|
|
|
|
ALTER TABLE "meeting_settings" ALTER COLUMN "participant_join_window_minutes" SET DEFAULT 10;
|
|
ALTER TABLE "users" ADD COLUMN "can_view_analytics" boolean DEFAULT true;
|
|
ALTER TABLE "payload_locked_documents_rels" ADD COLUMN "analytics_events_id" integer;
|
|
ALTER TABLE "payload_locked_documents_rels" ADD COLUMN "analytics_daily_stats_id" integer;
|
|
CREATE INDEX "analytics_events_session_id_idx" ON "analytics_events" USING btree ("session_id");
|
|
CREATE INDEX "analytics_events_path_idx" ON "analytics_events" USING btree ("path");
|
|
CREATE INDEX "analytics_events_event_name_idx" ON "analytics_events" USING btree ("event_name");
|
|
CREATE INDEX "analytics_events_updated_at_idx" ON "analytics_events" USING btree ("updated_at");
|
|
CREATE INDEX "analytics_events_created_at_idx" ON "analytics_events" USING btree ("created_at");
|
|
CREATE INDEX "visitorHash_createdAt_idx" ON "analytics_events" USING btree ("visitor_hash","created_at");
|
|
CREATE INDEX "createdAt_idx" ON "analytics_events" USING btree ("created_at");
|
|
CREATE UNIQUE INDEX "analytics_daily_stats_date_idx" ON "analytics_daily_stats" USING btree ("date");
|
|
CREATE INDEX "analytics_daily_stats_updated_at_idx" ON "analytics_daily_stats" USING btree ("updated_at");
|
|
CREATE INDEX "analytics_daily_stats_created_at_idx" ON "analytics_daily_stats" USING btree ("created_at");
|
|
ALTER TABLE "payload_locked_documents_rels" ADD CONSTRAINT "payload_locked_documents_rels_analytics_events_fk" FOREIGN KEY ("analytics_events_id") REFERENCES "public"."analytics_events"("id") ON DELETE cascade ON UPDATE no action;
|
|
ALTER TABLE "payload_locked_documents_rels" ADD CONSTRAINT "payload_locked_documents_rels_analytics_daily_stats_fk" FOREIGN KEY ("analytics_daily_stats_id") REFERENCES "public"."analytics_daily_stats"("id") ON DELETE cascade ON UPDATE no action;
|
|
CREATE INDEX "payload_locked_documents_rels_analytics_events_id_idx" ON "payload_locked_documents_rels" USING btree ("analytics_events_id");
|
|
CREATE INDEX "payload_locked_documents_rels_analytics_daily_stats_id_idx" ON "payload_locked_documents_rels" USING btree ("analytics_daily_stats_id");`)
|
|
}
|
|
|
|
export async function down({ db, payload, req }: MigrateDownArgs): Promise<void> {
|
|
await db.execute(sql`
|
|
ALTER TABLE "analytics_events" DISABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE "analytics_daily_stats" DISABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE "cookie_settings" DISABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE "analytics_settings" DISABLE ROW LEVEL SECURITY;
|
|
DROP TABLE "analytics_events" CASCADE;
|
|
DROP TABLE "analytics_daily_stats" CASCADE;
|
|
DROP TABLE "cookie_settings" CASCADE;
|
|
DROP TABLE "analytics_settings" CASCADE;
|
|
ALTER TABLE "payload_locked_documents_rels" DROP CONSTRAINT "payload_locked_documents_rels_analytics_events_fk";
|
|
|
|
ALTER TABLE "payload_locked_documents_rels" DROP CONSTRAINT "payload_locked_documents_rels_analytics_daily_stats_fk";
|
|
|
|
DROP INDEX "payload_locked_documents_rels_analytics_events_id_idx";
|
|
DROP INDEX "payload_locked_documents_rels_analytics_daily_stats_id_idx";
|
|
ALTER TABLE "meeting_settings" ALTER COLUMN "participant_join_window_minutes" SET DEFAULT 30;
|
|
ALTER TABLE "users" DROP COLUMN "can_view_analytics";
|
|
ALTER TABLE "payload_locked_documents_rels" DROP COLUMN "analytics_events_id";
|
|
ALTER TABLE "payload_locked_documents_rels" DROP COLUMN "analytics_daily_stats_id";
|
|
DROP TYPE "public"."enum_analytics_events_device";
|
|
DROP TYPE "public"."enum_analytics_events_browser";`)
|
|
}
|