272 lines
9.2 KiB
SQL
272 lines
9.2 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "StatusPengaduan" AS ENUM ('diterima', 'antrian', 'dikerjakan', 'ditolak', 'selesai');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Role" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"permissions" JSONB,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Role_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "User" (
|
|
"id" TEXT NOT NULL,
|
|
"roleId" TEXT,
|
|
"name" TEXT,
|
|
"email" TEXT,
|
|
"password" TEXT,
|
|
"phone" TEXT,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "User_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ApiKey" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"key" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"expiredAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "ApiKey_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Credential" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT,
|
|
"value" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Credential_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "CategoryPengaduan" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "CategoryPengaduan_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Pengaduan" (
|
|
"id" TEXT NOT NULL,
|
|
"idCategory" TEXT NOT NULL,
|
|
"idWarga" TEXT NOT NULL,
|
|
"noPengaduan" TEXT NOT NULL,
|
|
"title" TEXT,
|
|
"detail" TEXT,
|
|
"location" TEXT,
|
|
"image" TEXT,
|
|
"keterangan" TEXT,
|
|
"status" "StatusPengaduan" NOT NULL DEFAULT 'antrian',
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Pengaduan_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "HistoryPengaduan" (
|
|
"id" TEXT NOT NULL,
|
|
"idPengaduan" TEXT NOT NULL,
|
|
"idUser" TEXT,
|
|
"deskripsi" TEXT,
|
|
"status" "StatusPengaduan" NOT NULL DEFAULT 'antrian',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "HistoryPengaduan_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Warga" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT,
|
|
"phone" TEXT,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Warga_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "CategoryPelayanan" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"syaratDokumen" JSONB[],
|
|
"dataText" TEXT[] DEFAULT ARRAY[]::TEXT[],
|
|
"dataPelengkap" JSONB[] DEFAULT ARRAY[]::JSONB[],
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "CategoryPelayanan_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "PelayananAjuan" (
|
|
"id" TEXT NOT NULL,
|
|
"idWarga" TEXT NOT NULL,
|
|
"idCategory" TEXT NOT NULL,
|
|
"noPengajuan" TEXT NOT NULL,
|
|
"status" "StatusPengaduan" NOT NULL DEFAULT 'antrian',
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "PelayananAjuan_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "HistoryPelayanan" (
|
|
"id" TEXT NOT NULL,
|
|
"idPengajuanLayanan" TEXT NOT NULL,
|
|
"idUser" TEXT,
|
|
"deskripsi" TEXT,
|
|
"keteranganAlasan" TEXT,
|
|
"status" "StatusPengaduan" NOT NULL DEFAULT 'antrian',
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "HistoryPelayanan_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "SyaratDokumenPelayanan" (
|
|
"id" TEXT NOT NULL,
|
|
"idPengajuanLayanan" TEXT NOT NULL,
|
|
"idCategory" TEXT NOT NULL,
|
|
"jenis" TEXT NOT NULL,
|
|
"value" TEXT NOT NULL,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "SyaratDokumenPelayanan_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "DataTextPelayanan" (
|
|
"id" TEXT NOT NULL,
|
|
"idPengajuanLayanan" TEXT NOT NULL,
|
|
"idCategory" TEXT NOT NULL,
|
|
"jenis" TEXT NOT NULL,
|
|
"value" TEXT NOT NULL,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "DataTextPelayanan_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "SuratPelayanan" (
|
|
"id" TEXT NOT NULL,
|
|
"idPengajuanLayanan" TEXT NOT NULL,
|
|
"idCategory" TEXT NOT NULL,
|
|
"idWarga" TEXT NOT NULL,
|
|
"noSurat" TEXT NOT NULL,
|
|
"file" TEXT,
|
|
"dateExpired" DATE,
|
|
"status" INTEGER NOT NULL DEFAULT 0,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "SuratPelayanan_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Configuration" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"value" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Configuration_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_phone_key" ON "User"("phone");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "ApiKey_key_key" ON "ApiKey"("key");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Warga_phone_key" ON "Warga"("phone");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "User" ADD CONSTRAINT "User_roleId_fkey" FOREIGN KEY ("roleId") REFERENCES "Role"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ApiKey" ADD CONSTRAINT "ApiKey_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Pengaduan" ADD CONSTRAINT "Pengaduan_idCategory_fkey" FOREIGN KEY ("idCategory") REFERENCES "CategoryPengaduan"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Pengaduan" ADD CONSTRAINT "Pengaduan_idWarga_fkey" FOREIGN KEY ("idWarga") REFERENCES "Warga"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "HistoryPengaduan" ADD CONSTRAINT "HistoryPengaduan_idPengaduan_fkey" FOREIGN KEY ("idPengaduan") REFERENCES "Pengaduan"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "HistoryPengaduan" ADD CONSTRAINT "HistoryPengaduan_idUser_fkey" FOREIGN KEY ("idUser") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "PelayananAjuan" ADD CONSTRAINT "PelayananAjuan_idWarga_fkey" FOREIGN KEY ("idWarga") REFERENCES "Warga"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "PelayananAjuan" ADD CONSTRAINT "PelayananAjuan_idCategory_fkey" FOREIGN KEY ("idCategory") REFERENCES "CategoryPelayanan"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "HistoryPelayanan" ADD CONSTRAINT "HistoryPelayanan_idPengajuanLayanan_fkey" FOREIGN KEY ("idPengajuanLayanan") REFERENCES "PelayananAjuan"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "HistoryPelayanan" ADD CONSTRAINT "HistoryPelayanan_idUser_fkey" FOREIGN KEY ("idUser") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "SyaratDokumenPelayanan" ADD CONSTRAINT "SyaratDokumenPelayanan_idPengajuanLayanan_fkey" FOREIGN KEY ("idPengajuanLayanan") REFERENCES "PelayananAjuan"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "SyaratDokumenPelayanan" ADD CONSTRAINT "SyaratDokumenPelayanan_idCategory_fkey" FOREIGN KEY ("idCategory") REFERENCES "CategoryPelayanan"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "DataTextPelayanan" ADD CONSTRAINT "DataTextPelayanan_idPengajuanLayanan_fkey" FOREIGN KEY ("idPengajuanLayanan") REFERENCES "PelayananAjuan"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "DataTextPelayanan" ADD CONSTRAINT "DataTextPelayanan_idCategory_fkey" FOREIGN KEY ("idCategory") REFERENCES "CategoryPelayanan"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "SuratPelayanan" ADD CONSTRAINT "SuratPelayanan_idPengajuanLayanan_fkey" FOREIGN KEY ("idPengajuanLayanan") REFERENCES "PelayananAjuan"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "SuratPelayanan" ADD CONSTRAINT "SuratPelayanan_idCategory_fkey" FOREIGN KEY ("idCategory") REFERENCES "CategoryPelayanan"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "SuratPelayanan" ADD CONSTRAINT "SuratPelayanan_idWarga_fkey" FOREIGN KEY ("idWarga") REFERENCES "Warga"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|