25 lines
2.4 KiB
PL/PgSQL
25 lines
2.4 KiB
PL/PgSQL
CREATE TABLE "HomeProjectionState" (id TEXT PRIMARY KEY, version INTEGER NOT NULL DEFAULT 0, "seededDay" TEXT, initialized BOOLEAN NOT NULL DEFAULT false, "lastError" TEXT, "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP);
|
|
CREATE TABLE "HomeProjectionDirty" ("messageRecordId" TEXT PRIMARY KEY,"enqueuedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP);
|
|
CREATE TABLE "HomeMessageFact" ("messageRecordId" TEXT NOT NULL,"fromVersion" INTEGER NOT NULL,"toVersion" INTEGER,"queuedDay" TEXT NOT NULL,payload JSONB NOT NULL,PRIMARY KEY("messageRecordId","fromVersion"));
|
|
CREATE INDEX "HomeMessageFact_queuedDay_toVersion_idx" ON "HomeMessageFact"("queuedDay","toVersion");
|
|
CREATE TABLE "HomeSnapshot" (id TEXT PRIMARY KEY,"userId" TEXT NOT NULL,"businessDate" TEXT NOT NULL,version INTEGER NOT NULL,"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,"expiresAt" TIMESTAMP(3) NOT NULL,summary JSONB NOT NULL);
|
|
CREATE INDEX "HomeSnapshot_expiresAt_idx" ON "HomeSnapshot"("expiresAt");
|
|
INSERT INTO "HomeProjectionState"(id) VALUES ('home');
|
|
-- A durable, transactional invalidation only: no business state or external effects.
|
|
CREATE FUNCTION home_mark_dirty() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
DECLARE mid TEXT;
|
|
BEGIN
|
|
IF TG_TABLE_NAME = 'SmsMessageRecord' THEN mid := COALESCE(NEW.id,OLD.id);
|
|
ELSIF TG_TABLE_NAME = 'UpstreamReceiptInbox' THEN mid := COALESCE(NEW."matchedMessageRecordId",OLD."matchedMessageRecordId");
|
|
ELSE mid := COALESCE(NEW."messageRecordId",OLD."messageRecordId"); END IF;
|
|
IF mid IS NOT NULL THEN
|
|
INSERT INTO "HomeProjectionDirty"("messageRecordId") VALUES(mid) ON CONFLICT ("messageRecordId") DO UPDATE SET "enqueuedAt"=CURRENT_TIMESTAMP;
|
|
END IF;
|
|
RETURN NULL;
|
|
END $$;
|
|
CREATE TRIGGER home_message_dirty AFTER INSERT OR UPDATE OR DELETE ON "SmsMessageRecord" FOR EACH ROW EXECUTE FUNCTION home_mark_dirty();
|
|
CREATE TRIGGER home_submit_dirty AFTER INSERT OR UPDATE OR DELETE ON "SmsSubmitRecord" FOR EACH ROW EXECUTE FUNCTION home_mark_dirty();
|
|
CREATE TRIGGER home_segment_dirty AFTER INSERT OR UPDATE OR DELETE ON "SmsMessageSegmentAudit" FOR EACH ROW EXECUTE FUNCTION home_mark_dirty();
|
|
CREATE TRIGGER home_inbox_dirty AFTER INSERT OR UPDATE OR DELETE ON "UpstreamReceiptInbox" FOR EACH ROW EXECUTE FUNCTION home_mark_dirty();
|
|
CREATE TRIGGER home_receipt_dirty AFTER INSERT OR UPDATE OR DELETE ON "SmsReceiptRecord" FOR EACH ROW EXECUTE FUNCTION home_mark_dirty();
|