fd55afd773
Extracts forms + form_submissions schema from TS migrations into idempotent SQL so Dockerfile.migrator can apply it on production. Also updates generated payload-types after form-builder plugin. Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
358 lines
16 KiB
SQL
358 lines
16 KiB
SQL
-- Migration 0004: form-builder plugin tables + form_id on globals
|
|
-- Extracted from 20260518_104929.ts and 20260518_115657.ts — fully idempotent
|
|
|
|
-- Enum types
|
|
DO $$ BEGIN
|
|
CREATE TYPE "public"."enum_forms_confirmation_type" AS ENUM('message', 'redirect');
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE "public"."enum_forms_redirect_type" AS ENUM('reference', 'custom');
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
-- Block tables
|
|
CREATE TABLE IF NOT EXISTS "forms_blocks_checkbox" (
|
|
"_order" integer NOT NULL,
|
|
"_parent_id" integer NOT NULL,
|
|
"_path" text NOT NULL,
|
|
"id" varchar PRIMARY KEY NOT NULL,
|
|
"name" varchar NOT NULL,
|
|
"label" varchar,
|
|
"width" numeric,
|
|
"required" boolean,
|
|
"default_value" boolean,
|
|
"block_name" varchar
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "forms_blocks_email" (
|
|
"_order" integer NOT NULL,
|
|
"_parent_id" integer NOT NULL,
|
|
"_path" text NOT NULL,
|
|
"id" varchar PRIMARY KEY NOT NULL,
|
|
"name" varchar NOT NULL,
|
|
"label" varchar,
|
|
"width" numeric,
|
|
"required" boolean,
|
|
"block_name" varchar
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "forms_blocks_message" (
|
|
"_order" integer NOT NULL,
|
|
"_parent_id" integer NOT NULL,
|
|
"_path" text NOT NULL,
|
|
"id" varchar PRIMARY KEY NOT NULL,
|
|
"message" jsonb,
|
|
"block_name" varchar
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "forms_blocks_number" (
|
|
"_order" integer NOT NULL,
|
|
"_parent_id" integer NOT NULL,
|
|
"_path" text NOT NULL,
|
|
"id" varchar PRIMARY KEY NOT NULL,
|
|
"name" varchar NOT NULL,
|
|
"label" varchar,
|
|
"width" numeric,
|
|
"default_value" numeric,
|
|
"required" boolean,
|
|
"block_name" varchar
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "forms_blocks_select_options" (
|
|
"_order" integer NOT NULL,
|
|
"_parent_id" varchar NOT NULL,
|
|
"id" varchar PRIMARY KEY NOT NULL,
|
|
"label" varchar NOT NULL,
|
|
"value" varchar NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "forms_blocks_select" (
|
|
"_order" integer NOT NULL,
|
|
"_parent_id" integer NOT NULL,
|
|
"_path" text NOT NULL,
|
|
"id" varchar PRIMARY KEY NOT NULL,
|
|
"name" varchar NOT NULL,
|
|
"label" varchar,
|
|
"width" numeric,
|
|
"default_value" varchar,
|
|
"placeholder" varchar,
|
|
"required" boolean,
|
|
"block_name" varchar
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "forms_blocks_text" (
|
|
"_order" integer NOT NULL,
|
|
"_parent_id" integer NOT NULL,
|
|
"_path" text NOT NULL,
|
|
"id" varchar PRIMARY KEY NOT NULL,
|
|
"name" varchar NOT NULL,
|
|
"label" varchar,
|
|
"width" numeric,
|
|
"default_value" varchar,
|
|
"required" boolean,
|
|
"block_name" varchar
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "forms_blocks_textarea" (
|
|
"_order" integer NOT NULL,
|
|
"_parent_id" integer NOT NULL,
|
|
"_path" text NOT NULL,
|
|
"id" varchar PRIMARY KEY NOT NULL,
|
|
"name" varchar NOT NULL,
|
|
"label" varchar,
|
|
"width" numeric,
|
|
"default_value" varchar,
|
|
"required" boolean,
|
|
"block_name" varchar
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "forms_blocks_radio_options" (
|
|
"_order" integer NOT NULL,
|
|
"_parent_id" varchar NOT NULL,
|
|
"id" varchar PRIMARY KEY NOT NULL,
|
|
"label" varchar NOT NULL,
|
|
"value" varchar NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "forms_blocks_radio" (
|
|
"_order" integer NOT NULL,
|
|
"_parent_id" integer NOT NULL,
|
|
"_path" text NOT NULL,
|
|
"id" varchar PRIMARY KEY NOT NULL,
|
|
"name" varchar NOT NULL,
|
|
"label" varchar,
|
|
"width" numeric,
|
|
"default_value" varchar,
|
|
"required" boolean,
|
|
"block_name" varchar
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "forms_blocks_date" (
|
|
"_order" integer NOT NULL,
|
|
"_parent_id" integer NOT NULL,
|
|
"_path" text NOT NULL,
|
|
"id" varchar PRIMARY KEY NOT NULL,
|
|
"name" varchar NOT NULL,
|
|
"label" varchar,
|
|
"width" numeric,
|
|
"required" boolean,
|
|
"default_value" timestamp(3) with time zone,
|
|
"block_name" varchar
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "forms_emails" (
|
|
"_order" integer NOT NULL,
|
|
"_parent_id" integer NOT NULL,
|
|
"id" varchar PRIMARY KEY NOT NULL,
|
|
"email_to" varchar,
|
|
"cc" varchar,
|
|
"bcc" varchar,
|
|
"reply_to" varchar,
|
|
"email_from" varchar,
|
|
"subject" varchar DEFAULT 'You''ve received a new message.' NOT NULL,
|
|
"message" jsonb
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "forms" (
|
|
"id" serial PRIMARY KEY NOT NULL,
|
|
"title" varchar NOT NULL,
|
|
"submit_button_label" varchar,
|
|
"confirmation_type" "enum_forms_confirmation_type" DEFAULT 'message',
|
|
"confirmation_message" jsonb,
|
|
"redirect_type" "enum_forms_redirect_type" DEFAULT 'reference',
|
|
"redirect_url" 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 IF NOT EXISTS "forms_rels" (
|
|
"id" serial PRIMARY KEY NOT NULL,
|
|
"order" integer,
|
|
"parent_id" integer NOT NULL,
|
|
"path" varchar NOT NULL,
|
|
"pages_id" integer
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "form_submissions_submission_data" (
|
|
"_order" integer NOT NULL,
|
|
"_parent_id" integer NOT NULL,
|
|
"id" varchar PRIMARY KEY NOT NULL,
|
|
"field" varchar NOT NULL,
|
|
"value" varchar NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "form_submissions" (
|
|
"id" serial PRIMARY KEY NOT NULL,
|
|
"form_id" integer NOT NULL,
|
|
"updated_at" timestamp(3) with time zone DEFAULT now() NOT NULL,
|
|
"created_at" timestamp(3) with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
-- FK constraints on block tables
|
|
DO $$ BEGIN
|
|
ALTER TABLE "forms_blocks_checkbox" ADD CONSTRAINT "forms_blocks_checkbox_parent_id_fk"
|
|
FOREIGN KEY ("_parent_id") REFERENCES "public"."forms"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "forms_blocks_email" ADD CONSTRAINT "forms_blocks_email_parent_id_fk"
|
|
FOREIGN KEY ("_parent_id") REFERENCES "public"."forms"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "forms_blocks_message" ADD CONSTRAINT "forms_blocks_message_parent_id_fk"
|
|
FOREIGN KEY ("_parent_id") REFERENCES "public"."forms"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "forms_blocks_number" ADD CONSTRAINT "forms_blocks_number_parent_id_fk"
|
|
FOREIGN KEY ("_parent_id") REFERENCES "public"."forms"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "forms_blocks_select_options" ADD CONSTRAINT "forms_blocks_select_options_parent_id_fk"
|
|
FOREIGN KEY ("_parent_id") REFERENCES "public"."forms_blocks_select"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "forms_blocks_select" ADD CONSTRAINT "forms_blocks_select_parent_id_fk"
|
|
FOREIGN KEY ("_parent_id") REFERENCES "public"."forms"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "forms_blocks_text" ADD CONSTRAINT "forms_blocks_text_parent_id_fk"
|
|
FOREIGN KEY ("_parent_id") REFERENCES "public"."forms"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "forms_blocks_textarea" ADD CONSTRAINT "forms_blocks_textarea_parent_id_fk"
|
|
FOREIGN KEY ("_parent_id") REFERENCES "public"."forms"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "forms_blocks_radio_options" ADD CONSTRAINT "forms_blocks_radio_options_parent_id_fk"
|
|
FOREIGN KEY ("_parent_id") REFERENCES "public"."forms_blocks_radio"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "forms_blocks_radio" ADD CONSTRAINT "forms_blocks_radio_parent_id_fk"
|
|
FOREIGN KEY ("_parent_id") REFERENCES "public"."forms"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "forms_blocks_date" ADD CONSTRAINT "forms_blocks_date_parent_id_fk"
|
|
FOREIGN KEY ("_parent_id") REFERENCES "public"."forms"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "forms_emails" ADD CONSTRAINT "forms_emails_parent_id_fk"
|
|
FOREIGN KEY ("_parent_id") REFERENCES "public"."forms"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "forms_rels" ADD CONSTRAINT "forms_rels_parent_fk"
|
|
FOREIGN KEY ("parent_id") REFERENCES "public"."forms"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "forms_rels" ADD CONSTRAINT "forms_rels_pages_fk"
|
|
FOREIGN KEY ("pages_id") REFERENCES "public"."pages"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "form_submissions_submission_data" ADD CONSTRAINT "form_submissions_submission_data_parent_id_fk"
|
|
FOREIGN KEY ("_parent_id") REFERENCES "public"."form_submissions"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "form_submissions" ADD CONSTRAINT "form_submissions_form_id_forms_id_fk"
|
|
FOREIGN KEY ("form_id") REFERENCES "public"."forms"("id") ON DELETE set null ON UPDATE no action;
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
-- payload_locked_documents_rels columns
|
|
ALTER TABLE "payload_locked_documents_rels" ADD COLUMN IF NOT EXISTS "forms_id" integer;
|
|
ALTER TABLE "payload_locked_documents_rels" ADD COLUMN IF NOT EXISTS "form_submissions_id" integer;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "payload_locked_documents_rels" ADD CONSTRAINT "payload_locked_documents_rels_forms_fk"
|
|
FOREIGN KEY ("forms_id") REFERENCES "public"."forms"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "payload_locked_documents_rels" ADD CONSTRAINT "payload_locked_documents_rels_form_submissions_fk"
|
|
FOREIGN KEY ("form_submissions_id") REFERENCES "public"."form_submissions"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
-- Indexes
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_checkbox_order_idx" ON "forms_blocks_checkbox" USING btree ("_order");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_checkbox_parent_id_idx" ON "forms_blocks_checkbox" USING btree ("_parent_id");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_checkbox_path_idx" ON "forms_blocks_checkbox" USING btree ("_path");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_email_order_idx" ON "forms_blocks_email" USING btree ("_order");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_email_parent_id_idx" ON "forms_blocks_email" USING btree ("_parent_id");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_email_path_idx" ON "forms_blocks_email" USING btree ("_path");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_message_order_idx" ON "forms_blocks_message" USING btree ("_order");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_message_parent_id_idx" ON "forms_blocks_message" USING btree ("_parent_id");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_message_path_idx" ON "forms_blocks_message" USING btree ("_path");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_number_order_idx" ON "forms_blocks_number" USING btree ("_order");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_number_parent_id_idx" ON "forms_blocks_number" USING btree ("_parent_id");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_number_path_idx" ON "forms_blocks_number" USING btree ("_path");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_select_options_order_idx" ON "forms_blocks_select_options" USING btree ("_order");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_select_options_parent_id_idx" ON "forms_blocks_select_options" USING btree ("_parent_id");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_select_order_idx" ON "forms_blocks_select" USING btree ("_order");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_select_parent_id_idx" ON "forms_blocks_select" USING btree ("_parent_id");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_select_path_idx" ON "forms_blocks_select" USING btree ("_path");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_text_order_idx" ON "forms_blocks_text" USING btree ("_order");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_text_parent_id_idx" ON "forms_blocks_text" USING btree ("_parent_id");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_text_path_idx" ON "forms_blocks_text" USING btree ("_path");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_textarea_order_idx" ON "forms_blocks_textarea" USING btree ("_order");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_textarea_parent_id_idx" ON "forms_blocks_textarea" USING btree ("_parent_id");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_textarea_path_idx" ON "forms_blocks_textarea" USING btree ("_path");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_radio_options_order_idx" ON "forms_blocks_radio_options" USING btree ("_order");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_radio_options_parent_id_idx" ON "forms_blocks_radio_options" USING btree ("_parent_id");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_radio_order_idx" ON "forms_blocks_radio" USING btree ("_order");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_radio_parent_id_idx" ON "forms_blocks_radio" USING btree ("_parent_id");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_radio_path_idx" ON "forms_blocks_radio" USING btree ("_path");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_date_order_idx" ON "forms_blocks_date" USING btree ("_order");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_date_parent_id_idx" ON "forms_blocks_date" USING btree ("_parent_id");
|
|
CREATE INDEX IF NOT EXISTS "forms_blocks_date_path_idx" ON "forms_blocks_date" USING btree ("_path");
|
|
CREATE INDEX IF NOT EXISTS "forms_emails_order_idx" ON "forms_emails" USING btree ("_order");
|
|
CREATE INDEX IF NOT EXISTS "forms_emails_parent_id_idx" ON "forms_emails" USING btree ("_parent_id");
|
|
CREATE INDEX IF NOT EXISTS "forms_updated_at_idx" ON "forms" USING btree ("updated_at");
|
|
CREATE INDEX IF NOT EXISTS "forms_created_at_idx" ON "forms" USING btree ("created_at");
|
|
CREATE INDEX IF NOT EXISTS "forms_rels_order_idx" ON "forms_rels" USING btree ("order");
|
|
CREATE INDEX IF NOT EXISTS "forms_rels_parent_idx" ON "forms_rels" USING btree ("parent_id");
|
|
CREATE INDEX IF NOT EXISTS "forms_rels_path_idx" ON "forms_rels" USING btree ("path");
|
|
CREATE INDEX IF NOT EXISTS "forms_rels_pages_id_idx" ON "forms_rels" USING btree ("pages_id");
|
|
CREATE INDEX IF NOT EXISTS "form_submissions_submission_data_order_idx" ON "form_submissions_submission_data" USING btree ("_order");
|
|
CREATE INDEX IF NOT EXISTS "form_submissions_submission_data_parent_id_idx" ON "form_submissions_submission_data" USING btree ("_parent_id");
|
|
CREATE INDEX IF NOT EXISTS "form_submissions_form_idx" ON "form_submissions" USING btree ("form_id");
|
|
CREATE INDEX IF NOT EXISTS "form_submissions_updated_at_idx" ON "form_submissions" USING btree ("updated_at");
|
|
CREATE INDEX IF NOT EXISTS "form_submissions_created_at_idx" ON "form_submissions" USING btree ("created_at");
|
|
CREATE INDEX IF NOT EXISTS "payload_locked_documents_rels_forms_id_idx" ON "payload_locked_documents_rels" USING btree ("forms_id");
|
|
CREATE INDEX IF NOT EXISTS "payload_locked_documents_rels_form_submissions_id_idx" ON "payload_locked_documents_rels" USING btree ("form_submissions_id");
|
|
|
|
-- form_id columns on globals (requires forms table to exist first)
|
|
ALTER TABLE "group_visits_page" ADD COLUMN IF NOT EXISTS "form_id" integer;
|
|
ALTER TABLE "_group_visits_page_v" ADD COLUMN IF NOT EXISTS "version_form_id" integer;
|
|
ALTER TABLE "birthday_page" ADD COLUMN IF NOT EXISTS "form_id" integer;
|
|
ALTER TABLE "_birthday_page_v" ADD COLUMN IF NOT EXISTS "version_form_id" integer;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "group_visits_page" ADD CONSTRAINT "group_visits_page_form_id_forms_id_fk"
|
|
FOREIGN KEY ("form_id") REFERENCES "forms"("id") ON DELETE SET NULL;
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "_group_visits_page_v" ADD CONSTRAINT "_group_visits_page_v_form_id_forms_id_fk"
|
|
FOREIGN KEY ("version_form_id") REFERENCES "forms"("id") ON DELETE SET NULL;
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "birthday_page" ADD CONSTRAINT "birthday_page_form_id_forms_id_fk"
|
|
FOREIGN KEY ("form_id") REFERENCES "forms"("id") ON DELETE SET NULL;
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE "_birthday_page_v" ADD CONSTRAINT "_birthday_page_v_form_id_forms_id_fk"
|
|
FOREIGN KEY ("version_form_id") REFERENCES "forms"("id") ON DELETE SET NULL;
|
|
EXCEPTION WHEN duplicate_object THEN null; END $$;
|