Henry_Du
bd8df1f026
- 新增 /api/admin/songs/batch-reject.post 批量驳回接口（事务清排期/投票/扣接纳数/黑名单去重/异步通知） - requireSongAdmin 三角色校验 + createApiError 统一错误包装 - scheduleDaysBefore/After 四字段排期可见范围限制（开放 API 与公开排期接口三处缓存路径 + DB 条件过滤） - submissionNoteAutoModeration 基于黑名单 KEYWORD/LANGUAGE/GENRE 自动判定留言 publicStatus，命中自动关闭 submissionNotePublic - 更新 songs/request.post 与 songs/[id]/update.put 接入自动审查逻辑
318 lines
22 KiB
SQL
318 lines
22 KiB
SQL
CREATE TYPE "public"."card_code_status" AS ENUM('AVAILABLE', 'LOCKED', 'REDEEMED', 'INVALID');--> statement-breakpoint
|
|
ALTER TYPE "public"."BlacklistType" ADD VALUE 'LANGUAGE';--> statement-breakpoint
|
|
ALTER TYPE "public"."BlacklistType" ADD VALUE 'GENRE';--> statement-breakpoint
|
|
ALTER TYPE "public"."user_status" ADD VALUE 'pending';--> statement-breakpoint
|
|
ALTER TYPE "public"."user_status" ADD VALUE 'rejected';--> statement-breakpoint
|
|
CREATE TABLE "auth_sessions" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"user_id" integer NOT NULL,
|
|
"session_token" varchar(255) NOT NULL,
|
|
"expires_at" timestamp with time zone NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"ip_address" text,
|
|
"user_agent" text,
|
|
"last_active_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"is_revoked" boolean DEFAULT false NOT NULL,
|
|
"browser" text,
|
|
"operating_system" text,
|
|
"device" text,
|
|
"login_method" text DEFAULT 'password' NOT NULL,
|
|
"revoked_at" timestamp with time zone,
|
|
"revoked_reason" text,
|
|
CONSTRAINT "auth_sessions_session_token_unique" UNIQUE("session_token")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "backup_records" (
|
|
"id" serial PRIMARY KEY NOT NULL,
|
|
"filename" text NOT NULL,
|
|
"file_path" text NOT NULL,
|
|
"file_size" bigint NOT NULL,
|
|
"type" text DEFAULT 'MANUAL' NOT NULL,
|
|
"status" text DEFAULT 'COMPLETED' NOT NULL,
|
|
"error_message" text,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"started_at" timestamp with time zone,
|
|
"completed_at" timestamp with time zone,
|
|
"created_by" integer,
|
|
"expires_at" timestamp with time zone
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "card_code_redemptions" (
|
|
"id" serial PRIMARY KEY NOT NULL,
|
|
"card_code_id" integer NOT NULL,
|
|
"user_id" integer NOT NULL,
|
|
"redeemed_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"song_id" integer,
|
|
"purpose" text NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "card_codes" (
|
|
"id" serial PRIMARY KEY NOT NULL,
|
|
"code" varchar(64) NOT NULL,
|
|
"status" "card_code_status" DEFAULT 'AVAILABLE' NOT NULL,
|
|
"type" text NOT NULL,
|
|
"value" integer DEFAULT 1 NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"expires_at" timestamp with time zone,
|
|
"locked_at" timestamp with time zone,
|
|
"locked_by" integer,
|
|
"batch_id" varchar(64),
|
|
"note" text,
|
|
CONSTRAINT "card_codes_code_unique" UNIQUE("code")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "ChangelogEntry" (
|
|
"id" serial PRIMARY KEY NOT NULL,
|
|
"createdAt" timestamp DEFAULT now() NOT NULL,
|
|
"updatedAt" timestamp DEFAULT now() NOT NULL,
|
|
"version" text NOT NULL,
|
|
"title" text NOT NULL,
|
|
"content" text NOT NULL,
|
|
"published" boolean DEFAULT false NOT NULL,
|
|
"publishedAt" timestamp,
|
|
"createdBy" integer
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "ExpectedPlayDate" (
|
|
"id" serial PRIMARY KEY NOT NULL,
|
|
"createdAt" timestamp DEFAULT now() NOT NULL,
|
|
"updatedAt" timestamp DEFAULT now() NOT NULL,
|
|
"date" text NOT NULL,
|
|
"availableSequences" text NOT NULL,
|
|
"enabled" boolean DEFAULT true NOT NULL,
|
|
CONSTRAINT "ExpectedPlayDate_date_unique" UNIQUE("date")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "GradeClass" (
|
|
"id" serial PRIMARY KEY NOT NULL,
|
|
"createdAt" timestamp DEFAULT now() NOT NULL,
|
|
"updatedAt" timestamp DEFAULT now() NOT NULL,
|
|
"grade" text NOT NULL,
|
|
"class" text NOT NULL,
|
|
"enabled" boolean DEFAULT true NOT NULL,
|
|
CONSTRAINT "grade_class_unique" UNIQUE("grade","class")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "oauth_providers" (
|
|
"id" serial PRIMARY KEY NOT NULL,
|
|
"name" text NOT NULL,
|
|
"displayName" text,
|
|
"enabled" boolean DEFAULT true NOT NULL,
|
|
"clientId" text,
|
|
"clientSecret" text,
|
|
"authorizationEndpoint" text,
|
|
"tokenEndpoint" text,
|
|
"userInfoEndpoint" text,
|
|
"scope" text,
|
|
"redirectUri" text,
|
|
"usernameClaim" text DEFAULT 'preferred_username',
|
|
"nameClaim" text DEFAULT 'name',
|
|
"emailClaim" text DEFAULT 'email',
|
|
"avatarClaim" text,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL,
|
|
"sortOrder" integer DEFAULT 0 NOT NULL,
|
|
"icon" text
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "passkey_challenges" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"challenge" text NOT NULL,
|
|
"type" text NOT NULL,
|
|
"user_id" integer,
|
|
"expires_at" timestamp with time zone NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "schedule_song_pool" (
|
|
"id" serial PRIMARY KEY NOT NULL,
|
|
"createdAt" timestamp DEFAULT now() NOT NULL,
|
|
"updatedAt" timestamp DEFAULT now() NOT NULL,
|
|
"song_id" integer NOT NULL,
|
|
"target_play_date" timestamp,
|
|
"priority" integer DEFAULT 0 NOT NULL,
|
|
"added_by" integer,
|
|
"note" text,
|
|
"status" text DEFAULT 'PENDING' NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "SongComment" (
|
|
"id" serial PRIMARY KEY NOT NULL,
|
|
"createdAt" timestamp DEFAULT now() NOT NULL,
|
|
"updatedAt" timestamp DEFAULT now() NOT NULL,
|
|
"songId" integer NOT NULL,
|
|
"userId" integer NOT NULL,
|
|
"content" text NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "submission_restriction_exemptions" (
|
|
"id" serial PRIMARY KEY NOT NULL,
|
|
"user_id" integer NOT NULL,
|
|
"exemption_type" text NOT NULL,
|
|
"reason" text,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"expires_at" timestamp with time zone,
|
|
"created_by" integer,
|
|
CONSTRAINT "submission_restriction_exemptions_user_type_unique" UNIQUE("user_id","exemption_type")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "user_passkeys" (
|
|
"id" text PRIMARY KEY NOT NULL,
|
|
"user_id" integer NOT NULL,
|
|
"name" text,
|
|
"public_key" text NOT NULL,
|
|
"credential_device_type" text NOT NULL,
|
|
"credential_backed_up" boolean NOT NULL,
|
|
"transports" text,
|
|
"counter" integer DEFAULT 0 NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"last_used_at" timestamp with time zone
|
|
);
|
|
--> statement-breakpoint
|
|
ALTER TABLE "User" ALTER COLUMN "forcePasswordChange" SET DEFAULT false;--> statement-breakpoint
|
|
ALTER TABLE "Notification" ADD COLUMN "batch_id" uuid;--> statement-breakpoint
|
|
ALTER TABLE "Notification" ADD COLUMN "source" text DEFAULT 'SYSTEM' NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "Notification" ADD COLUMN "sender_id" integer;--> statement-breakpoint
|
|
ALTER TABLE "Notification" ADD COLUMN "sender_name" text;--> statement-breakpoint
|
|
ALTER TABLE "Notification" ADD COLUMN "sender_username" text;--> statement-breakpoint
|
|
ALTER TABLE "Notification" ADD COLUMN "title" text;--> statement-breakpoint
|
|
ALTER TABLE "Notification" ADD COLUMN "important" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "Notification" ADD COLUMN "user_deleted" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "Schedule" ADD COLUMN "replay_request_id" integer;--> statement-breakpoint
|
|
ALTER TABLE "Song" ADD COLUMN "expectedPlayDate" text;--> statement-breakpoint
|
|
ALTER TABLE "Song" ADD COLUMN "expectedPlaySequence" integer;--> statement-breakpoint
|
|
ALTER TABLE "Song" ADD COLUMN "durationSeconds" integer;--> statement-breakpoint
|
|
ALTER TABLE "Song" ADD COLUMN "submissionNotePublicStatus" text;--> statement-breakpoint
|
|
ALTER TABLE "Song" ADD COLUMN "cardCodeId" integer;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "siteVersion" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "submissionNoteRequiresApproval" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "instanceId" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "telemetryEnabled" boolean DEFAULT true NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "showBeianIcon" boolean DEFAULT true NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "scheduleDaysDisplay" integer DEFAULT 7 NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "scheduleDaysEditable" integer DEFAULT 3 NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "scheduleDaysRequestable" integer DEFAULT 14 NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "scheduleDaysBeforeEnabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "scheduleDaysBefore" integer DEFAULT 1 NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "scheduleDaysAfterEnabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "scheduleDaysAfter" integer DEFAULT 1 NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "forcePasswordChangeOnFirstLogin" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "enableSubmissionRestriction" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "submissionRestrictionScope" text DEFAULT 'all' NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "sameSongRestrictionHours" integer;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "sameArtistRestrictionHours" integer;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "cardCodeEnabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "cardCodeSubmissionRequired" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "cardCodeVoteEnabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "cardCodeReplayEnabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "enableCardCodeRequests" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "requireCardCodeForRequests" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "enableCardCodeLimitBypass" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "submissionRestrictionEnabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "submissionRestrictionGradeEnabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "submissionRestrictionClassEnabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "submissionRestrictionGraduateEnabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "submissionRestrictionWithdrawnEnabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "captchaProvider" text DEFAULT 'TURNSTILE';--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "captchaEnabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "turnstileSiteKey" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "turnstileSecretKey" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "allowRegister" boolean DEFAULT true NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "registerRequireEmail" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "registerRequireEmailVerify" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "registerDefaultRole" text DEFAULT 'USER';--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "registerEnabledGradeClasses" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "registerRequiresApproval" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "oauthRegisterRequiresApproval" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "registerEmailRequired" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "registerRequiresGradeClass" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "oauthEnabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "oauthAllowRegister" boolean DEFAULT true NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "oauthAllowLogin" boolean DEFAULT true NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "oauthAllowBind" boolean DEFAULT true NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "allowOAuthRegistration" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "oauthRedirectUri" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "oauthStateSecret" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "oauthProviders" text DEFAULT '[]';--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "githubOAuthEnabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "githubClientId" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "githubClientSecret" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "casdoorOAuthEnabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "casdoorServerUrl" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "casdoorClientId" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "casdoorClientSecret" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "casdoorOrganizationName" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "googleOAuthEnabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "googleClientId" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "googleClientSecret" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "aggregateOAuthEnabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "aggregateOAuthAppId" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "aggregateOAuthAppKey" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "aggregateOAuthLoginType" text DEFAULT 'qq';--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "aggregateOAuthEndpoint" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "customOAuthEnabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "customOAuthDisplayName" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "customOAuthAuthorizeUrl" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "customOAuthTokenUrl" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "customOAuthUserInfoUrl" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "customOAuthScope" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "customOAuthClientId" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "customOAuthClientSecret" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "customOAuthUserIdField" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "customOAuthUsernameField" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "customOAuthNameField" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "customOAuthEmailField" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "customOAuthAvatarField" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "captchaMaxFailures" integer DEFAULT 3 NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "autoBackupEnabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "autoBackupIntervalHours" integer DEFAULT 24 NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "autoBackupRetentionDays" integer DEFAULT 30 NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "autoBackupConfig" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "statisticsCode" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "statisticsCodeEnabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "defaultTheme" text DEFAULT 'System' NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "enabledThemes" text DEFAULT '["System","ClassicDark","ClassicLight","ModernLight"]' NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "enabledPlatforms" text;--> statement-breakpoint
|
|
ALTER TABLE "SystemSettings" ADD COLUMN "platformOrder" text;--> statement-breakpoint
|
|
ALTER TABLE "User" ADD COLUMN "studentId" text;--> statement-breakpoint
|
|
ALTER TABLE "User" ADD COLUMN "tokenVersion" integer DEFAULT 0 NOT NULL;--> statement-breakpoint
|
|
ALTER TABLE "User" ADD COLUMN "avatarProvider" text;--> statement-breakpoint
|
|
ALTER TABLE "User" ADD COLUMN "avatarProviderUserId" text;--> statement-breakpoint
|
|
ALTER TABLE "User" ADD COLUMN "remark" text;--> statement-breakpoint
|
|
ALTER TABLE "auth_sessions" ADD CONSTRAINT "auth_sessions_user_id_User_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."User"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "card_code_redemptions" ADD CONSTRAINT "card_code_redemptions_card_code_id_card_codes_id_fk" FOREIGN KEY ("card_code_id") REFERENCES "public"."card_codes"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "card_code_redemptions" ADD CONSTRAINT "card_code_redemptions_user_id_User_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."User"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "card_code_redemptions" ADD CONSTRAINT "card_code_redemptions_song_id_Song_id_fk" FOREIGN KEY ("song_id") REFERENCES "public"."Song"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "schedule_song_pool" ADD CONSTRAINT "schedule_song_pool_song_id_Song_id_fk" FOREIGN KEY ("song_id") REFERENCES "public"."Song"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "submission_restriction_exemptions" ADD CONSTRAINT "submission_restriction_exemptions_user_id_User_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."User"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "user_passkeys" ADD CONSTRAINT "user_passkeys_user_id_User_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."User"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
CREATE INDEX "auth_sessions_user_active_idx" ON "auth_sessions" USING btree ("user_id","is_revoked","expires_at");--> statement-breakpoint
|
|
CREATE INDEX "auth_sessions_expires_at_idx" ON "auth_sessions" USING btree ("expires_at");--> statement-breakpoint
|
|
CREATE INDEX "auth_sessions_last_active_idx" ON "auth_sessions" USING btree ("last_active_at");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "auth_sessions_session_token_unique" ON "auth_sessions" USING btree ("session_token");--> statement-breakpoint
|
|
CREATE INDEX "backup_records_created_at_idx" ON "backup_records" USING btree ("created_at");--> statement-breakpoint
|
|
CREATE INDEX "backup_records_status_idx" ON "backup_records" USING btree ("status");--> statement-breakpoint
|
|
CREATE INDEX "card_code_redemptions_user_id_idx" ON "card_code_redemptions" USING btree ("user_id");--> statement-breakpoint
|
|
CREATE INDEX "card_code_redemptions_card_code_id_idx" ON "card_code_redemptions" USING btree ("card_code_id");--> statement-breakpoint
|
|
CREATE INDEX "card_codes_status_idx" ON "card_codes" USING btree ("status");--> statement-breakpoint
|
|
CREATE INDEX "card_codes_batch_id_idx" ON "card_codes" USING btree ("batch_id");--> statement-breakpoint
|
|
CREATE INDEX "card_codes_expires_at_idx" ON "card_codes" USING btree ("expires_at");--> statement-breakpoint
|
|
CREATE INDEX "passkey_challenges_challenge_idx" ON "passkey_challenges" USING btree ("challenge");--> statement-breakpoint
|
|
CREATE INDEX "passkey_challenges_expires_at_idx" ON "passkey_challenges" USING btree ("expires_at");--> statement-breakpoint
|
|
CREATE INDEX "schedule_song_pool_song_id_idx" ON "schedule_song_pool" USING btree ("song_id");--> statement-breakpoint
|
|
CREATE INDEX "schedule_song_pool_target_date_idx" ON "schedule_song_pool" USING btree ("target_play_date");--> statement-breakpoint
|
|
CREATE INDEX "submission_restriction_exemptions_user_id_idx" ON "submission_restriction_exemptions" USING btree ("user_id");--> statement-breakpoint
|
|
CREATE INDEX "user_passkeys_user_id_idx" ON "user_passkeys" USING btree ("user_id");--> statement-breakpoint
|
|
ALTER TABLE "Song" ADD CONSTRAINT "Song_cardCodeId_card_codes_id_fk" FOREIGN KEY ("cardCodeId") REFERENCES "public"."card_codes"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
CREATE INDEX "notification_user_important_read_created_idx" ON "Notification" USING btree ("userId","important","read","createdAt");--> statement-breakpoint
|
|
CREATE INDEX "notification_batch_id_idx" ON "Notification" USING btree ("batch_id");--> statement-breakpoint
|
|
CREATE INDEX "schedule_published_song_idx" ON "Schedule" USING btree ("publishedAt","songId");--> statement-breakpoint
|
|
CREATE INDEX "schedule_published_date_idx" ON "Schedule" USING btree ("publishedAt","playDate");--> statement-breakpoint
|
|
CREATE INDEX "song_card_code_id_idx" ON "Song" USING btree ("cardCodeId");--> statement-breakpoint
|
|
CREATE INDEX "song_semester_created_at_idx" ON "Song" USING btree ("semester","createdAt");--> statement-breakpoint
|
|
CREATE INDEX "song_requester_id_idx" ON "Song" USING btree ("requesterId");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "User_username_unique" ON "User" USING btree ("username");--> statement-breakpoint
|
|
CREATE INDEX "vote_user_song_idx" ON "Vote" USING btree ("userId","songId");--> statement-breakpoint
|
|
ALTER TABLE "Vote" ADD CONSTRAINT "vote_song_user_unique" UNIQUE("songId","userId"); |