dejanandClaude Opus 4.7
4f33509a0f
Internes Project-Management-Tool für ToolCTRL — ergänzend zu Plane (Tickets) und Outline (Doku). Umfasst Puls-Übersicht, Tagesrhythmus (Lock-in + Reflexion), Bereichs-Detail-Views (Entwicklung/Vertrieb/Marketing), Pflicht-Minimum-Check und Blocker-Lifecycle gemäß Projektmanagement-Leitfaden v3.0. Stack: pnpm-Monorepo · Vite + React 18 + Tailwind · Fastify + tRPC v11 + Drizzle ORM · Postgres 16 · Authentik OIDC (Phase 2, Dev-Auto-Login aktiv). Planungs-Artefakte unter _bmad-output/planning-artifacts/. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
54 lines
3.2 KiB
SQL
54 lines
3.2 KiB
SQL
CREATE TABLE "campaign" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"area_id" uuid NOT NULL,
|
|
"name" text NOT NULL,
|
|
"status" text DEFAULT 'active' NOT NULL,
|
|
"reach" integer DEFAULT 0 NOT NULL,
|
|
"owner_user_id" uuid,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "deal" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"area_id" uuid NOT NULL,
|
|
"name" text NOT NULL,
|
|
"owner_user_id" uuid,
|
|
"value_eur" integer DEFAULT 0 NOT NULL,
|
|
"probability" integer DEFAULT 50 NOT NULL,
|
|
"stage" text NOT NULL,
|
|
"entered_stage_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"won_at" timestamp with time zone,
|
|
"won_value_eur" integer,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "gtm_step" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"product" text NOT NULL,
|
|
"title" text NOT NULL,
|
|
"owner_user_id" uuid,
|
|
"status" text DEFAULT 'open' NOT NULL,
|
|
"order" integer DEFAULT 0 NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "lead_handover" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"campaign_id" uuid,
|
|
"deal_id" uuid,
|
|
"uebergeben_am" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"uebergeben_von" uuid
|
|
);
|
|
--> statement-breakpoint
|
|
ALTER TABLE "campaign" ADD CONSTRAINT "campaign_area_id_area_id_fk" FOREIGN KEY ("area_id") REFERENCES "public"."area"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "campaign" ADD CONSTRAINT "campaign_owner_user_id_user_id_fk" FOREIGN KEY ("owner_user_id") REFERENCES "public"."user"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "deal" ADD CONSTRAINT "deal_area_id_area_id_fk" FOREIGN KEY ("area_id") REFERENCES "public"."area"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "deal" ADD CONSTRAINT "deal_owner_user_id_user_id_fk" FOREIGN KEY ("owner_user_id") REFERENCES "public"."user"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "gtm_step" ADD CONSTRAINT "gtm_step_owner_user_id_user_id_fk" FOREIGN KEY ("owner_user_id") REFERENCES "public"."user"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "lead_handover" ADD CONSTRAINT "lead_handover_campaign_id_campaign_id_fk" FOREIGN KEY ("campaign_id") REFERENCES "public"."campaign"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "lead_handover" ADD CONSTRAINT "lead_handover_deal_id_deal_id_fk" FOREIGN KEY ("deal_id") REFERENCES "public"."deal"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "lead_handover" ADD CONSTRAINT "lead_handover_uebergeben_von_user_id_fk" FOREIGN KEY ("uebergeben_von") REFERENCES "public"."user"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
CREATE INDEX "idx_campaign_status" ON "campaign" USING btree ("status");--> statement-breakpoint
|
|
CREATE INDEX "idx_deal_stage" ON "deal" USING btree ("stage");--> statement-breakpoint
|
|
CREATE INDEX "idx_deal_owner" ON "deal" USING btree ("owner_user_id");--> statement-breakpoint
|
|
CREATE INDEX "idx_gtm_product" ON "gtm_step" USING btree ("product"); |