From 02ae938c238c9d18448d17a8ec92c0edd8c17463 Mon Sep 17 00:00:00 2001 From: Bertrand Yuan Date: Tue, 16 Dec 2025 00:12:49 +0800 Subject: feat(back-end): introduce payload Payload is the next.js Headless CMS and App Framework, I would like to pick it up and modify it as it is MIT licensed. Many features in Payload is not applicable for our project. So, I modify it so that it is light and clear. --- src/migrations/20251215_093857.ts | 171 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 171 insertions(+) create mode 100644 src/migrations/20251215_093857.ts (limited to 'src/migrations/20251215_093857.ts') diff --git a/src/migrations/20251215_093857.ts b/src/migrations/20251215_093857.ts new file mode 100644 index 0000000..1a53568 --- /dev/null +++ b/src/migrations/20251215_093857.ts @@ -0,0 +1,171 @@ +import type { MigrateUpArgs, MigrateDownArgs } from '@payloadcms/db-postgres' +import { sql } from 'drizzle-orm' + +export async function up({ db }: MigrateUpArgs): Promise { + await db.execute(sql` + CREATE TYPE "payload"."enum_posts_status" AS ENUM('draft', 'published'); + CREATE TABLE "payload"."users_sessions" ( + "_order" integer NOT NULL, + "_parent_id" integer NOT NULL, + "id" varchar PRIMARY KEY NOT NULL, + "created_at" timestamp(3) with time zone, + "expires_at" timestamp(3) with time zone NOT NULL + ); + + CREATE TABLE "payload"."users" ( + "id" serial PRIMARY KEY NOT NULL, + "name" varchar, + "updated_at" timestamp(3) with time zone DEFAULT now() NOT NULL, + "created_at" timestamp(3) with time zone DEFAULT now() NOT NULL, + "email" varchar NOT NULL, + "reset_password_token" varchar, + "reset_password_expiration" timestamp(3) with time zone, + "salt" varchar, + "hash" varchar, + "login_attempts" numeric DEFAULT 0, + "lock_until" timestamp(3) with time zone + ); + + CREATE TABLE "payload"."posts_tags" ( + "_order" integer NOT NULL, + "_parent_id" integer NOT NULL, + "id" varchar PRIMARY KEY NOT NULL, + "tag" varchar + ); + + CREATE TABLE "payload"."posts" ( + "id" serial PRIMARY KEY NOT NULL, + "title" varchar NOT NULL, + "slug" varchar NOT NULL, + "description" varchar, + "content" jsonb NOT NULL, + "featured_image_id" integer, + "author" varchar DEFAULT 'Admin', + "status" "payload"."enum_posts_status" DEFAULT 'draft', + "published_at" timestamp(3) with time zone, + "updated_at" timestamp(3) with time zone DEFAULT now() NOT NULL, + "created_at" timestamp(3) with time zone DEFAULT now() NOT NULL + ); + + CREATE TABLE "payload"."media" ( + "id" serial PRIMARY KEY NOT NULL, + "alt" varchar, + "updated_at" timestamp(3) with time zone DEFAULT now() NOT NULL, + "created_at" timestamp(3) with time zone DEFAULT now() NOT NULL, + "url" varchar, + "thumbnail_u_r_l" varchar, + "filename" varchar, + "mime_type" varchar, + "filesize" numeric, + "width" numeric, + "height" numeric, + "focal_x" numeric, + "focal_y" numeric + ); + + CREATE TABLE "payload"."payload_kv" ( + "id" serial PRIMARY KEY NOT NULL, + "key" varchar NOT NULL, + "data" jsonb NOT NULL + ); + + CREATE TABLE "payload"."payload_locked_documents" ( + "id" serial PRIMARY KEY NOT NULL, + "global_slug" varchar, + "updated_at" timestamp(3) with time zone DEFAULT now() NOT NULL, + "created_at" timestamp(3) with time zone DEFAULT now() NOT NULL + ); + + CREATE TABLE "payload"."payload_locked_documents_rels" ( + "id" serial PRIMARY KEY NOT NULL, + "order" integer, + "parent_id" integer NOT NULL, + "path" varchar NOT NULL, + "users_id" integer, + "posts_id" integer, + "media_id" integer + ); + + CREATE TABLE "payload"."payload_preferences" ( + "id" serial PRIMARY KEY NOT NULL, + "key" varchar, + "value" 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 "payload"."payload_preferences_rels" ( + "id" serial PRIMARY KEY NOT NULL, + "order" integer, + "parent_id" integer NOT NULL, + "path" varchar NOT NULL, + "users_id" integer + ); + + CREATE TABLE "payload"."payload_migrations" ( + "id" serial PRIMARY KEY NOT NULL, + "name" varchar, + "batch" numeric, + "updated_at" timestamp(3) with time zone DEFAULT now() NOT NULL, + "created_at" timestamp(3) with time zone DEFAULT now() NOT NULL + ); + + ALTER TABLE "payload"."users_sessions" ADD CONSTRAINT "users_sessions_parent_id_fk" FOREIGN KEY ("_parent_id") REFERENCES "payload"."users"("id") ON DELETE cascade ON UPDATE no action; + ALTER TABLE "payload"."posts_tags" ADD CONSTRAINT "posts_tags_parent_id_fk" FOREIGN KEY ("_parent_id") REFERENCES "payload"."posts"("id") ON DELETE cascade ON UPDATE no action; + ALTER TABLE "payload"."posts" ADD CONSTRAINT "posts_featured_image_id_media_id_fk" FOREIGN KEY ("featured_image_id") REFERENCES "payload"."media"("id") ON DELETE set null ON UPDATE no action; + ALTER TABLE "payload"."payload_locked_documents_rels" ADD CONSTRAINT "payload_locked_documents_rels_parent_fk" FOREIGN KEY ("parent_id") REFERENCES "payload"."payload_locked_documents"("id") ON DELETE cascade ON UPDATE no action; + ALTER TABLE "payload"."payload_locked_documents_rels" ADD CONSTRAINT "payload_locked_documents_rels_users_fk" FOREIGN KEY ("users_id") REFERENCES "payload"."users"("id") ON DELETE cascade ON UPDATE no action; + ALTER TABLE "payload"."payload_locked_documents_rels" ADD CONSTRAINT "payload_locked_documents_rels_posts_fk" FOREIGN KEY ("posts_id") REFERENCES "payload"."posts"("id") ON DELETE cascade ON UPDATE no action; + ALTER TABLE "payload"."payload_locked_documents_rels" ADD CONSTRAINT "payload_locked_documents_rels_media_fk" FOREIGN KEY ("media_id") REFERENCES "payload"."media"("id") ON DELETE cascade ON UPDATE no action; + ALTER TABLE "payload"."payload_preferences_rels" ADD CONSTRAINT "payload_preferences_rels_parent_fk" FOREIGN KEY ("parent_id") REFERENCES "payload"."payload_preferences"("id") ON DELETE cascade ON UPDATE no action; + ALTER TABLE "payload"."payload_preferences_rels" ADD CONSTRAINT "payload_preferences_rels_users_fk" FOREIGN KEY ("users_id") REFERENCES "payload"."users"("id") ON DELETE cascade ON UPDATE no action; + CREATE INDEX "users_sessions_order_idx" ON "payload"."users_sessions" USING btree ("_order"); + CREATE INDEX "users_sessions_parent_id_idx" ON "payload"."users_sessions" USING btree ("_parent_id"); + CREATE INDEX "users_updated_at_idx" ON "payload"."users" USING btree ("updated_at"); + CREATE INDEX "users_created_at_idx" ON "payload"."users" USING btree ("created_at"); + CREATE UNIQUE INDEX "users_email_idx" ON "payload"."users" USING btree ("email"); + CREATE INDEX "posts_tags_order_idx" ON "payload"."posts_tags" USING btree ("_order"); + CREATE INDEX "posts_tags_parent_id_idx" ON "payload"."posts_tags" USING btree ("_parent_id"); + CREATE UNIQUE INDEX "posts_slug_idx" ON "payload"."posts" USING btree ("slug"); + CREATE INDEX "posts_featured_image_idx" ON "payload"."posts" USING btree ("featured_image_id"); + CREATE INDEX "posts_updated_at_idx" ON "payload"."posts" USING btree ("updated_at"); + CREATE INDEX "posts_created_at_idx" ON "payload"."posts" USING btree ("created_at"); + CREATE INDEX "media_updated_at_idx" ON "payload"."media" USING btree ("updated_at"); + CREATE INDEX "media_created_at_idx" ON "payload"."media" USING btree ("created_at"); + CREATE UNIQUE INDEX "media_filename_idx" ON "payload"."media" USING btree ("filename"); + CREATE UNIQUE INDEX "payload_kv_key_idx" ON "payload"."payload_kv" USING btree ("key"); + CREATE INDEX "payload_locked_documents_global_slug_idx" ON "payload"."payload_locked_documents" USING btree ("global_slug"); + CREATE INDEX "payload_locked_documents_updated_at_idx" ON "payload"."payload_locked_documents" USING btree ("updated_at"); + CREATE INDEX "payload_locked_documents_created_at_idx" ON "payload"."payload_locked_documents" USING btree ("created_at"); + CREATE INDEX "payload_locked_documents_rels_order_idx" ON "payload"."payload_locked_documents_rels" USING btree ("order"); + CREATE INDEX "payload_locked_documents_rels_parent_idx" ON "payload"."payload_locked_documents_rels" USING btree ("parent_id"); + CREATE INDEX "payload_locked_documents_rels_path_idx" ON "payload"."payload_locked_documents_rels" USING btree ("path"); + CREATE INDEX "payload_locked_documents_rels_users_id_idx" ON "payload"."payload_locked_documents_rels" USING btree ("users_id"); + CREATE INDEX "payload_locked_documents_rels_posts_id_idx" ON "payload"."payload_locked_documents_rels" USING btree ("posts_id"); + CREATE INDEX "payload_locked_documents_rels_media_id_idx" ON "payload"."payload_locked_documents_rels" USING btree ("media_id"); + CREATE INDEX "payload_preferences_key_idx" ON "payload"."payload_preferences" USING btree ("key"); + CREATE INDEX "payload_preferences_updated_at_idx" ON "payload"."payload_preferences" USING btree ("updated_at"); + CREATE INDEX "payload_preferences_created_at_idx" ON "payload"."payload_preferences" USING btree ("created_at"); + CREATE INDEX "payload_preferences_rels_order_idx" ON "payload"."payload_preferences_rels" USING btree ("order"); + CREATE INDEX "payload_preferences_rels_parent_idx" ON "payload"."payload_preferences_rels" USING btree ("parent_id"); + CREATE INDEX "payload_preferences_rels_path_idx" ON "payload"."payload_preferences_rels" USING btree ("path"); + CREATE INDEX "payload_preferences_rels_users_id_idx" ON "payload"."payload_preferences_rels" USING btree ("users_id"); + CREATE INDEX "payload_migrations_updated_at_idx" ON "payload"."payload_migrations" USING btree ("updated_at"); + CREATE INDEX "payload_migrations_created_at_idx" ON "payload"."payload_migrations" USING btree ("created_at");`) +} + +export async function down({ db }: MigrateDownArgs): Promise { + await db.execute(sql` + DROP TABLE "payload"."users_sessions" CASCADE; + DROP TABLE "payload"."users" CASCADE; + DROP TABLE "payload"."posts_tags" CASCADE; + DROP TABLE "payload"."posts" CASCADE; + DROP TABLE "payload"."media" CASCADE; + DROP TABLE "payload"."payload_kv" CASCADE; + DROP TABLE "payload"."payload_locked_documents" CASCADE; + DROP TABLE "payload"."payload_locked_documents_rels" CASCADE; + DROP TABLE "payload"."payload_preferences" CASCADE; + DROP TABLE "payload"."payload_preferences_rels" CASCADE; + DROP TABLE "payload"."payload_migrations" CASCADE; + DROP TYPE "payload"."enum_posts_status";`) +} -- cgit v1.2.3