121 lines
6.9 KiB
SQL
121 lines
6.9 KiB
SQL
CREATE TYPE "public"."card_assignment_status" AS ENUM('ASSIGNED', 'RETURNED');--> statement-breakpoint
|
|
CREATE TYPE "public"."device_type" AS ENUM('RFID_SCANNER', 'LOCK_SYSTEM');--> statement-breakpoint
|
|
CREATE TYPE "public"."management_user_role" AS ENUM('ADMIN');--> statement-breakpoint
|
|
CREATE TYPE "public"."rfid_card_status" AS ENUM('NEW', 'ENGRAVED', 'LOST', 'DISPOSED');--> statement-breakpoint
|
|
CREATE TYPE "public"."system_log_type" AS ENUM('ACTION', 'INFO', 'WARNING', 'ERROR');--> statement-breakpoint
|
|
CREATE TABLE "access_logs" (
|
|
"member_id" uuid NOT NULL,
|
|
"device_id" uuid NOT NULL,
|
|
"accessed_at" timestamp NOT NULL,
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "devices" (
|
|
"name" varchar(200) NOT NULL,
|
|
"api_key" varchar(255) NOT NULL,
|
|
"type" "device_type" DEFAULT 'RFID_SCANNER' NOT NULL,
|
|
"last_seen_at" timestamp,
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL,
|
|
CONSTRAINT "devices_api_key_unique" UNIQUE("api_key")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "management_users" (
|
|
"first_name" varchar(100) NOT NULL,
|
|
"last_name" varchar(100) NOT NULL,
|
|
"username" varchar(50) NOT NULL,
|
|
"password_hash" varchar(255) NOT NULL,
|
|
"role" "management_user_role" DEFAULT 'ADMIN' NOT NULL,
|
|
"enabled" boolean DEFAULT true NOT NULL,
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL,
|
|
CONSTRAINT "management_users_username_unique" UNIQUE("username")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "member_rfid_cards" (
|
|
"member_id" uuid NOT NULL,
|
|
"card_id" uuid NOT NULL,
|
|
"status" "card_assignment_status" DEFAULT 'ASSIGNED' NOT NULL,
|
|
"issued_at" timestamp,
|
|
"returned_at" timestamp,
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "members" (
|
|
"first_name" varchar(100) NOT NULL,
|
|
"last_name" varchar(100) NOT NULL,
|
|
"title" varchar(20),
|
|
"birth_date" timestamp,
|
|
"membership_number" varchar(50),
|
|
"occupation" varchar(200),
|
|
"street" varchar(200),
|
|
"house_number" varchar(10),
|
|
"postal_code" varchar(10),
|
|
"city" varchar(100),
|
|
"phone_home" varchar(20),
|
|
"phone_work" varchar(20),
|
|
"phone_mobile" varchar(20),
|
|
"email" varchar(255),
|
|
"guest_account" boolean DEFAULT false NOT NULL,
|
|
"joined_at" timestamp,
|
|
"free_text_function" text,
|
|
"free_text_comment" text,
|
|
"password_hash" varchar(255),
|
|
"profile_image" varchar(500),
|
|
"allow_self_service" boolean DEFAULT false NOT NULL,
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL,
|
|
CONSTRAINT "members_membership_number_unique" UNIQUE("membership_number")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "rfid_cards" (
|
|
"rfid_id" varchar(50) NOT NULL,
|
|
"status" "rfid_card_status" DEFAULT 'NEW' NOT NULL,
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL,
|
|
CONSTRAINT "rfid_cards_rfid_id_unique" UNIQUE("rfid_id")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "system_logs" (
|
|
"type" "system_log_type" NOT NULL,
|
|
"name" varchar(255),
|
|
"user_id" uuid,
|
|
"payload" text,
|
|
"created_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
ALTER TABLE "access_logs" ADD CONSTRAINT "access_logs_member_id_members_id_fk" FOREIGN KEY ("member_id") REFERENCES "public"."members"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "access_logs" ADD CONSTRAINT "access_logs_device_id_devices_id_fk" FOREIGN KEY ("device_id") REFERENCES "public"."devices"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "member_rfid_cards" ADD CONSTRAINT "member_rfid_cards_member_id_members_id_fk" FOREIGN KEY ("member_id") REFERENCES "public"."members"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "member_rfid_cards" ADD CONSTRAINT "member_rfid_cards_card_id_rfid_cards_id_fk" FOREIGN KEY ("card_id") REFERENCES "public"."rfid_cards"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "system_logs" ADD CONSTRAINT "system_logs_user_id_management_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."management_users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
CREATE INDEX "access_logs_member_id_idx" ON "access_logs" USING btree ("member_id");--> statement-breakpoint
|
|
CREATE INDEX "access_logs_device_id_idx" ON "access_logs" USING btree ("device_id");--> statement-breakpoint
|
|
CREATE INDEX "access_logs_accessed_at_idx" ON "access_logs" USING btree ("accessed_at");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "devices_api_key_idx" ON "devices" USING btree ("api_key");--> statement-breakpoint
|
|
CREATE INDEX "devices_type_idx" ON "devices" USING btree ("type");--> statement-breakpoint
|
|
CREATE INDEX "devices_name_idx" ON "devices" USING btree ("name");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "management_users_username_idx" ON "management_users" USING btree ("username");--> statement-breakpoint
|
|
CREATE INDEX "management_users_enabled_idx" ON "management_users" USING btree ("enabled");--> statement-breakpoint
|
|
CREATE INDEX "member_rfid_cards_member_id_idx" ON "member_rfid_cards" USING btree ("member_id");--> statement-breakpoint
|
|
CREATE INDEX "member_rfid_cards_card_id_idx" ON "member_rfid_cards" USING btree ("card_id");--> statement-breakpoint
|
|
CREATE INDEX "member_rfid_cards_status_idx" ON "member_rfid_cards" USING btree ("status");--> statement-breakpoint
|
|
CREATE INDEX "member_rfid_cards_member_card_status_idx" ON "member_rfid_cards" USING btree ("member_id","card_id","status");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "members_membership_number_idx" ON "members" USING btree ("membership_number");--> statement-breakpoint
|
|
CREATE INDEX "members_email_idx" ON "members" USING btree ("email");--> statement-breakpoint
|
|
CREATE INDEX "members_guest_account_idx" ON "members" USING btree ("guest_account");--> statement-breakpoint
|
|
CREATE INDEX "members_name_idx" ON "members" USING btree ("first_name","last_name");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "rfid_cards_rfid_id_idx" ON "rfid_cards" USING btree ("rfid_id");--> statement-breakpoint
|
|
CREATE INDEX "rfid_cards_status_idx" ON "rfid_cards" USING btree ("status");--> statement-breakpoint
|
|
CREATE INDEX "system_logs_type_idx" ON "system_logs" USING btree ("type");--> statement-breakpoint
|
|
CREATE INDEX "system_logs_user_id_idx" ON "system_logs" USING btree ("user_id");--> statement-breakpoint
|
|
CREATE INDEX "system_logs_created_at_idx" ON "system_logs" USING btree ("created_at");--> statement-breakpoint
|
|
CREATE INDEX "system_logs_name_idx" ON "system_logs" USING btree ("name"); |