74 lines
5.0 KiB
SQL
74 lines
5.0 KiB
SQL
ALTER TABLE "SmsSubmitRecord" ADD COLUMN "firstWireSubmitAt" TIMESTAMP(3), ADD COLUMN "wireTimeSource" TEXT;
|
|
ALTER TABLE "SmsMessageSegmentAudit" ADD COLUMN "firstWireSubmitAt" TIMESTAMP(3), ADD COLUMN "wireTimeSource" TEXT;
|
|
ALTER TABLE "UpstreamReceiptInbox" ADD COLUMN "gatewayReceivedAt" TIMESTAMP(3);
|
|
ALTER TABLE "SmsSubmitRecord" ADD COLUMN "receiptRequested" BOOLEAN;
|
|
ALTER TABLE "SmsMessageSegmentAudit" ADD COLUMN "receiptRequested" BOOLEAN;
|
|
CREATE INDEX "SmsSubmitRecord_monitor_updated_idx" ON "SmsSubmitRecord" ("updatedAt",id);
|
|
CREATE INDEX "SmsSubmitRecord_monitor_created_idx" ON "SmsSubmitRecord" ("createdAt",id);
|
|
CREATE INDEX "UpstreamReceiptInbox_monitor_updated_idx" ON "UpstreamReceiptInbox" ("updatedAt",id);
|
|
CREATE INDEX "UpstreamReceiptInbox_monitor_message_idx" ON "UpstreamReceiptInbox" ("matchedMessageRecordId","gatewayMessageId");
|
|
|
|
CREATE TABLE "SendingMonitorTarget" (
|
|
"channelId" TEXT PRIMARY KEY, enabled BOOLEAN NOT NULL, version INTEGER NOT NULL,
|
|
"effectiveFrom" TIMESTAMP(3) NOT NULL, "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
|
|
"updatedBy" TEXT NOT NULL
|
|
);
|
|
CREATE TABLE "SendingMonitorTargetVersion" (
|
|
"channelId" TEXT NOT NULL, version INTEGER NOT NULL, enabled BOOLEAN NOT NULL,
|
|
"effectiveFrom" TIMESTAMP(3) NOT NULL, "updatedBy" TEXT NOT NULL,
|
|
PRIMARY KEY("channelId",version)
|
|
);
|
|
CREATE TABLE "SendingMonitorRule" (
|
|
id TEXT PRIMARY KEY, type TEXT NOT NULL, "scopeKey" TEXT NOT NULL, scope JSONB NOT NULL,
|
|
config JSONB NOT NULL, version INTEGER NOT NULL, "effectiveAt" TIMESTAMP(3) NOT NULL,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'), "updatedBy" TEXT NOT NULL,
|
|
UNIQUE(type,"scopeKey")
|
|
);
|
|
CREATE TABLE "SendingMonitorRuleVersion" (
|
|
"ruleId" TEXT NOT NULL, version INTEGER NOT NULL, type TEXT NOT NULL, scope JSONB NOT NULL,
|
|
config JSONB NOT NULL, "effectiveAt" TIMESTAMP(3) NOT NULL, "createdAt" TIMESTAMP(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
|
|
"createdBy" TEXT NOT NULL, PRIMARY KEY("ruleId",version)
|
|
);
|
|
CREATE INDEX "SendingMonitorRuleVersion_effective_idx" ON "SendingMonitorRuleVersion" (type,"effectiveAt");
|
|
CREATE TABLE "SendingMonitorFact" (
|
|
id TEXT PRIMARY KEY, kind TEXT NOT NULL, "sourceId" TEXT NOT NULL, "dimensionKey" TEXT NOT NULL,
|
|
"messageRecordId" TEXT REFERENCES "SmsMessageRecord"(id) ON DELETE SET NULL ON UPDATE CASCADE,
|
|
dimensions JSONB NOT NULL, "submittedAt" TIMESTAMP(3) NOT NULL, "successAt" TIMESTAMP(3),
|
|
verification BOOLEAN NOT NULL, reason TEXT, "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
|
|
UNIQUE(kind,"sourceId")
|
|
);
|
|
CREATE INDEX "SendingMonitorFact_window_idx" ON "SendingMonitorFact" (kind,"submittedAt","dimensionKey");
|
|
CREATE INDEX "SendingMonitorFact_message_idx" ON "SendingMonitorFact" ("messageRecordId");
|
|
CREATE TABLE "SendingMonitorMinute" (
|
|
kind TEXT NOT NULL, "dimensionKey" TEXT NOT NULL, minute TIMESTAMP(3) NOT NULL,
|
|
verification BOOLEAN NOT NULL, dimensions JSONB NOT NULL, metrics JSONB NOT NULL,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
|
|
PRIMARY KEY(kind,"dimensionKey",minute,verification)
|
|
);
|
|
CREATE INDEX "SendingMonitorMinute_window_idx" ON "SendingMonitorMinute" (kind,minute);
|
|
CREATE TABLE "SendingMonitorSnapshot" (
|
|
id TEXT PRIMARY KEY, type TEXT NOT NULL, "dimensionKey" TEXT NOT NULL, dimensions JSONB NOT NULL,
|
|
"evaluationAt" TIMESTAMP(3) NOT NULL, "windowFrom" TIMESTAMP(3) NOT NULL,
|
|
"observedUntil" TIMESTAMP(3) NOT NULL, stage TEXT NOT NULL, revision INTEGER NOT NULL DEFAULT 1,
|
|
metrics JSONB NOT NULL, rule JSONB, status TEXT NOT NULL, completeness JSONB NOT NULL,
|
|
"computedAt" TIMESTAMP(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
|
|
UNIQUE(type,"dimensionKey","evaluationAt")
|
|
);
|
|
CREATE INDEX "SendingMonitorSnapshot_latest_idx" ON "SendingMonitorSnapshot" (type,"evaluationAt" DESC,status);
|
|
CREATE INDEX "SendingMonitorSnapshot_history_idx" ON "SendingMonitorSnapshot" (type,"dimensionKey","evaluationAt");
|
|
CREATE TABLE "SendingMonitorAlert" (
|
|
id TEXT PRIMARY KEY, type TEXT NOT NULL, "dimensionKey" TEXT NOT NULL, dimensions JSONB NOT NULL,
|
|
state TEXT NOT NULL, "openedAt" TIMESTAMP(3) NOT NULL, "lastEvaluatedAt" TIMESTAMP(3) NOT NULL,
|
|
"closedAt" TIMESTAMP(3), "closeReason" TEXT, "ruleKey" TEXT NOT NULL,
|
|
latest JSONB NOT NULL, worst JSONB NOT NULL, "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC')
|
|
);
|
|
CREATE UNIQUE INDEX "SendingMonitorAlert_one_active_idx" ON "SendingMonitorAlert" (type,"dimensionKey") WHERE state='active';
|
|
CREATE INDEX "SendingMonitorAlert_state_idx" ON "SendingMonitorAlert" (state,"openedAt" DESC);
|
|
CREATE TABLE "SendingMonitorAlertRead" (
|
|
"alertId" TEXT NOT NULL, "userId" TEXT NOT NULL, "readAt" TIMESTAMP(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
|
|
PRIMARY KEY("alertId","userId")
|
|
);
|
|
CREATE TABLE "SendingMonitorCheckpoint" (
|
|
id TEXT PRIMARY KEY, data JSONB NOT NULL, "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC')
|
|
);
|