131 lines
5.0 KiB
SQL
131 lines
5.0 KiB
SQL
-- CreateTable
|
|
CREATE TABLE "SignatureAnalyticsGeneration" (
|
|
"id" TEXT NOT NULL,
|
|
"businessDate" DATE NOT NULL,
|
|
"sourceAsOf" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "SignatureAnalyticsGeneration_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "SignatureAnalyticsDay" (
|
|
"businessDate" DATE NOT NULL,
|
|
"publishedGenerationId" TEXT,
|
|
"generatedAt" TIMESTAMP(3),
|
|
"sourceAsOf" TIMESTAMP(3),
|
|
"refreshFor" DATE,
|
|
"state" TEXT NOT NULL DEFAULT 'missing',
|
|
"error" TEXT,
|
|
"provenance" TEXT NOT NULL DEFAULT 'daily',
|
|
"schemaVersion" INTEGER NOT NULL DEFAULT 1,
|
|
"rowCounts" JSONB,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "SignatureAnalyticsDay_pkey" PRIMARY KEY ("businessDate")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "SignatureAnalyticsRun" (
|
|
"id" TEXT NOT NULL,
|
|
"scope" TEXT NOT NULL,
|
|
"businessDate" DATE NOT NULL,
|
|
"refreshFor" DATE NOT NULL,
|
|
"generationId" TEXT NOT NULL,
|
|
"state" TEXT NOT NULL DEFAULT 'pending',
|
|
"owner" TEXT,
|
|
"fence" INTEGER NOT NULL DEFAULT 0,
|
|
"leaseUntil" TIMESTAMP(3),
|
|
"attempt" INTEGER NOT NULL DEFAULT 0,
|
|
"nextAttemptAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"checkpoint" JSONB,
|
|
"error" TEXT,
|
|
"startedAt" TIMESTAMP(3),
|
|
"finishedAt" TIMESTAMP(3),
|
|
|
|
CONSTRAINT "SignatureAnalyticsRun_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "SignatureQualityDaily" (
|
|
"generationId" TEXT NOT NULL,
|
|
"businessDate" DATE NOT NULL,
|
|
"signatureId" TEXT NOT NULL,
|
|
"signatureName" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"tenantName" TEXT NOT NULL,
|
|
"applicationNames" TEXT NOT NULL,
|
|
"total" INTEGER NOT NULL,
|
|
"payload" JSONB NOT NULL,
|
|
|
|
CONSTRAINT "SignatureQualityDaily_pkey" PRIMARY KEY ("generationId","signatureId")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "SignatureActivityDaily" (
|
|
"generationId" TEXT NOT NULL,
|
|
"businessDate" DATE NOT NULL,
|
|
"dimensionKey" TEXT NOT NULL,
|
|
"dimensionType" TEXT NOT NULL,
|
|
"signatureId" TEXT NOT NULL,
|
|
"channelKey" TEXT NOT NULL,
|
|
"carrier" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"applicationId" TEXT,
|
|
"signatureName" TEXT NOT NULL,
|
|
"tenantName" TEXT NOT NULL,
|
|
"applicationName" TEXT NOT NULL,
|
|
"channelName" TEXT NOT NULL,
|
|
"approvedAt" TIMESTAMP(3),
|
|
"submittedAttempts" INTEGER NOT NULL,
|
|
"acceptedBusinessCount" INTEGER NOT NULL,
|
|
"deliveredBusinessCount" INTEGER NOT NULL,
|
|
"applicability" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "SignatureActivityDaily_pkey" PRIMARY KEY ("generationId","dimensionKey")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "UnreportedSignatureDaily" (
|
|
"generationId" TEXT NOT NULL,
|
|
"businessDate" DATE NOT NULL,
|
|
"dimensionKey" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"applicationId" TEXT NOT NULL,
|
|
"signatureName" TEXT NOT NULL,
|
|
"tenantName" TEXT NOT NULL,
|
|
"applicationName" TEXT NOT NULL,
|
|
"messageCount" INTEGER NOT NULL,
|
|
|
|
CONSTRAINT "UnreportedSignatureDaily_pkey" PRIMARY KEY ("generationId","dimensionKey")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "SignatureAnalyticsGeneration_id_businessDate_key" ON "SignatureAnalyticsGeneration"("id", "businessDate");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "SignatureAnalyticsRun_state_nextAttemptAt_idx" ON "SignatureAnalyticsRun"("state", "nextAttemptAt");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "SignatureAnalyticsRun_scope_businessDate_key" ON "SignatureAnalyticsRun"("scope", "businessDate");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "SignatureQualityDaily_businessDate_generationId_total_idx" ON "SignatureQualityDaily"("businessDate", "generationId", "total");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "SignatureActivityDaily_businessDate_generationId_dimensionT_idx" ON "SignatureActivityDaily"("businessDate", "generationId", "dimensionType", "acceptedBusinessCount");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "UnreportedSignatureDaily_businessDate_generationId_messageC_idx" ON "UnreportedSignatureDaily"("businessDate", "generationId", "messageCount");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "SignatureAnalyticsDay" ADD CONSTRAINT "SignatureAnalyticsDay_publishedGenerationId_businessDate_fkey" FOREIGN KEY ("publishedGenerationId", "businessDate") REFERENCES "SignatureAnalyticsGeneration"("id", "businessDate") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "SignatureQualityDaily" ADD CONSTRAINT "SignatureQualityDaily_generationId_businessDate_fkey" FOREIGN KEY ("generationId", "businessDate") REFERENCES "SignatureAnalyticsGeneration"("id", "businessDate") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "SignatureActivityDaily" ADD CONSTRAINT "SignatureActivityDaily_generationId_businessDate_fkey" FOREIGN KEY ("generationId", "businessDate") REFERENCES "SignatureAnalyticsGeneration"("id", "businessDate") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "UnreportedSignatureDaily" ADD CONSTRAINT "UnreportedSignatureDaily_generationId_businessDate_fkey" FOREIGN KEY ("generationId", "businessDate") REFERENCES "SignatureAnalyticsGeneration"("id", "businessDate") ON DELETE RESTRICT ON UPDATE CASCADE;
|