51 lines
1.7 KiB
SQL
51 lines
1.7 KiB
SQL
 
|
-- Drop legacy JSON snapshot on tenants
|
||
|
|
ALTER TABLE "tenants" DROP COLUMN IF EXISTS "setting";
|
||
|
|
|
||
|
|
-- Template defaults (copied onto new tenants)
|
||
|
|
CREATE TABLE "setting_templates" (
|
||
|
|
"id" TEXT NOT NULL,
|
||
|
|
"key" VARCHAR(50) NOT NULL,
|
||
|
|
"title_fa" VARCHAR(255),
|
||
|
|
"can_edit" SMALLINT DEFAULT 0,
|
||
|
|
"value" TEXT,
|
||
|
|
"description" TEXT,
|
||
|
|
"type" VARCHAR(50),
|
||
|
|
"type_data" VARCHAR(255),
|
||
|
|
"is_exist" BOOLEAN NOT NULL DEFAULT true,
|
||
|
|
"is_active" SMALLINT DEFAULT 1,
|
||
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
||
|
|
"deleted_at" TIMESTAMP(3),
|
||
|
|
|
||
|
|
CONSTRAINT "setting_templates_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE UNIQUE INDEX "setting_templates_key_key" ON "setting_templates"("key");
|
||
|
|
|
||
|
|
-- Per-tenant settings (cascade on tenant delete)
|
||
|
|
CREATE TABLE "settings" (
|
||
|
|
"id" TEXT NOT NULL,
|
||
|
|
"tenant_id" TEXT NOT NULL,
|
||
|
|
"key" VARCHAR(50) NOT NULL,
|
||
|
|
"title_fa" VARCHAR(255),
|
||
|
|
"can_edit" SMALLINT DEFAULT 0,
|
||
|
|
"value" TEXT,
|
||
|
|
"description" TEXT,
|
||
|
|
"type" VARCHAR(50),
|
||
|
|
"type_data" VARCHAR(255),
|
||
|
|
"is_exist" BOOLEAN NOT NULL DEFAULT true,
|
||
|
|
"is_active" SMALLINT DEFAULT 1,
|
||
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
"updated_at" TIMESTAMP(3) NOT NULL,
|
||
|
|
"deleted_at" TIMESTAMP(3),
|
||
|
|
|
||
|
|
CONSTRAINT "settings_pkey" PRIMARY KEY ("id")
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE UNIQUE INDEX "settings_tenant_id_key_key" ON "settings"("tenant_id", "key");
|
||
|
|
CREATE INDEX "settings_key_idx" ON "settings"("key");
|
||
|
|
CREATE INDEX "settings_tenant_id_idx" ON "settings"("tenant_id");
|
||
|
|
|
||
|
|
ALTER TABLE "settings" ADD CONSTRAINT "settings_tenant_id_fkey"
|
||
|
|
FOREIGN KEY ("tenant_id") REFERENCES "tenants"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|