maaz519
21772e7611
Task 8.1: 12 Calendar/Meeting tables (meeting_request, meeting, participant, transcript, segment, summary, generic action_item + meeting link, provider account, calendar_event, availability_window, sync_cursor) + 13 enums; migration calendar-init. Adds calendar.meeting.requested/scheduled/reminder.queued/ summarized events. resetDb truncates the new tables. Kernel already had the MEETING_REQUEST / MEETING_FOLLOWUP / callback.meetingRef stubs. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
295 lines
10 KiB
SQL
295 lines
10 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "IiosMeetingType" AS ENUM ('ZOOM', 'PHONE', 'IN_PERSON', 'CALLBACK', 'INTERNAL');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "IiosMeetingStatus" AS ENUM ('REQUESTED', 'SCHEDULED', 'CONFIRMED', 'CANCELLED', 'COMPLETED', 'NO_SHOW');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "IiosMeetingRequestStatus" AS ENUM ('PENDING', 'PROPOSED', 'ACCEPTED', 'DECLINED', 'SCHEDULED', 'CANCELLED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "IiosParticipantRole" AS ENUM ('ORGANIZER', 'REQUIRED', 'OPTIONAL');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "IiosAttendanceStatus" AS ENUM ('INVITED', 'ACCEPTED', 'DECLINED', 'TENTATIVE', 'ATTENDED', 'NO_SHOW');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "IiosParticipantVisibility" AS ENUM ('FULL', 'LIMITED', 'NONE');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "IiosConsentStatus" AS ENUM ('UNKNOWN', 'GRANTED', 'DENIED', 'REVOKED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "IiosTranscriptStatus" AS ENUM ('PENDING', 'READY', 'BLOCKED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "IiosSummaryStatus" AS ENUM ('PROPOSED', 'APPROVED', 'PUBLISHED', 'REJECTED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "IiosActionItemStatus" AS ENUM ('OPEN', 'IN_PROGRESS', 'DONE', 'CANCELLED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "IiosCalendarProviderType" AS ENUM ('SIMULATED', 'GOOGLE', 'OUTLOOK', 'ICS');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "IiosCalendarSyncStatus" AS ENUM ('IDLE', 'SYNCING', 'ERROR');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "IiosCalendarEventStatus" AS ENUM ('CONFIRMED', 'TENTATIVE', 'CANCELLED');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "IiosMeetingRequest" (
|
|
"id" TEXT NOT NULL,
|
|
"scopeId" TEXT NOT NULL,
|
|
"interactionId" TEXT,
|
|
"requestedByActorId" TEXT NOT NULL,
|
|
"targetActorId" TEXT,
|
|
"requestedWindowJson" JSONB NOT NULL,
|
|
"meetingType" "IiosMeetingType" NOT NULL DEFAULT 'CALLBACK',
|
|
"status" "IiosMeetingRequestStatus" NOT NULL DEFAULT 'PENDING',
|
|
"resultingMeetingId" TEXT,
|
|
"callbackRequestId" TEXT,
|
|
"sourceClaimId" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "IiosMeetingRequest_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "IiosMeeting" (
|
|
"id" TEXT NOT NULL,
|
|
"scopeId" TEXT NOT NULL,
|
|
"interactionId" TEXT,
|
|
"calendarEventId" TEXT,
|
|
"meetingType" "IiosMeetingType" NOT NULL,
|
|
"providerRef" TEXT,
|
|
"joinUrlRef" TEXT,
|
|
"status" "IiosMeetingStatus" NOT NULL DEFAULT 'REQUESTED',
|
|
"organizerActorId" TEXT NOT NULL,
|
|
"threadId" TEXT,
|
|
"title" TEXT NOT NULL,
|
|
"startAt" TIMESTAMP(3),
|
|
"endAt" TIMESTAMP(3),
|
|
"timezone" TEXT NOT NULL DEFAULT 'UTC',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "IiosMeeting_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "IiosMeetingParticipant" (
|
|
"id" TEXT NOT NULL,
|
|
"meetingId" TEXT NOT NULL,
|
|
"actorRefId" TEXT,
|
|
"sourceHandleId" TEXT,
|
|
"role" "IiosParticipantRole" NOT NULL DEFAULT 'REQUIRED',
|
|
"attendanceStatus" "IiosAttendanceStatus" NOT NULL DEFAULT 'INVITED',
|
|
"recordingConsent" "IiosConsentStatus" NOT NULL DEFAULT 'UNKNOWN',
|
|
"visibility" "IiosParticipantVisibility" NOT NULL DEFAULT 'FULL',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "IiosMeetingParticipant_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "IiosMeetingTranscript" (
|
|
"id" TEXT NOT NULL,
|
|
"meetingId" TEXT NOT NULL,
|
|
"sourceType" TEXT NOT NULL DEFAULT 'SIMULATED',
|
|
"transcriptRef" TEXT NOT NULL,
|
|
"consentRefId" TEXT,
|
|
"status" "IiosTranscriptStatus" NOT NULL DEFAULT 'PENDING',
|
|
"language" TEXT NOT NULL DEFAULT 'en',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "IiosMeetingTranscript_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "IiosTranscriptSegment" (
|
|
"id" TEXT NOT NULL,
|
|
"transcriptId" TEXT NOT NULL,
|
|
"segmentNo" INTEGER NOT NULL,
|
|
"speakerActorId" TEXT,
|
|
"startMs" INTEGER NOT NULL,
|
|
"endMs" INTEGER NOT NULL,
|
|
"contentRef" TEXT NOT NULL,
|
|
"confidence" DOUBLE PRECISION,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "IiosTranscriptSegment_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "IiosMeetingSummary" (
|
|
"id" TEXT NOT NULL,
|
|
"meetingId" TEXT NOT NULL,
|
|
"aiArtifactId" TEXT,
|
|
"summaryType" TEXT NOT NULL DEFAULT 'AI',
|
|
"status" "IiosSummaryStatus" NOT NULL DEFAULT 'PROPOSED',
|
|
"approvedByActorId" TEXT,
|
|
"publishedAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "IiosMeetingSummary_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "IiosActionItem" (
|
|
"id" TEXT NOT NULL,
|
|
"scopeId" TEXT NOT NULL,
|
|
"sourceInteractionId" TEXT,
|
|
"sourceMessageId" TEXT,
|
|
"ownerActorId" TEXT,
|
|
"ownerTeamId" TEXT,
|
|
"title" TEXT NOT NULL,
|
|
"status" "IiosActionItemStatus" NOT NULL DEFAULT 'OPEN',
|
|
"dueAt" TIMESTAMP(3),
|
|
"completedAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "IiosActionItem_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "IiosMeetingActionItem" (
|
|
"id" TEXT NOT NULL,
|
|
"meetingId" TEXT NOT NULL,
|
|
"actionItemId" TEXT NOT NULL,
|
|
"ownerActorId" TEXT,
|
|
"sourceSegmentId" TEXT,
|
|
"status" "IiosActionItemStatus" NOT NULL DEFAULT 'OPEN',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "IiosMeetingActionItem_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "IiosCalendarProviderAccount" (
|
|
"id" TEXT NOT NULL,
|
|
"scopeId" TEXT NOT NULL,
|
|
"actorRefId" TEXT,
|
|
"providerType" "IiosCalendarProviderType" NOT NULL DEFAULT 'SIMULATED',
|
|
"accountHandleToken" TEXT NOT NULL,
|
|
"secretRef" TEXT,
|
|
"syncStatus" "IiosCalendarSyncStatus" NOT NULL DEFAULT 'IDLE',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "IiosCalendarProviderAccount_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "IiosCalendarEvent" (
|
|
"id" TEXT NOT NULL,
|
|
"scopeId" TEXT NOT NULL,
|
|
"providerAccountId" TEXT,
|
|
"externalEventId" TEXT,
|
|
"title" TEXT NOT NULL,
|
|
"startAt" TIMESTAMP(3) NOT NULL,
|
|
"endAt" TIMESTAMP(3) NOT NULL,
|
|
"status" "IiosCalendarEventStatus" NOT NULL DEFAULT 'CONFIRMED',
|
|
"visibilityClass" TEXT NOT NULL DEFAULT 'internal',
|
|
"interactionId" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "IiosCalendarEvent_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "IiosAvailabilityWindow" (
|
|
"id" TEXT NOT NULL,
|
|
"scopeId" TEXT NOT NULL,
|
|
"actorRefId" TEXT,
|
|
"teamProjectionId" TEXT,
|
|
"startAt" TIMESTAMP(3) NOT NULL,
|
|
"endAt" TIMESTAMP(3) NOT NULL,
|
|
"source" TEXT NOT NULL DEFAULT 'USER',
|
|
"recurrenceJson" JSONB,
|
|
"status" TEXT NOT NULL DEFAULT 'ACTIVE',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "IiosAvailabilityWindow_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "IiosCalendarSyncCursor" (
|
|
"id" TEXT NOT NULL,
|
|
"providerAccountId" TEXT NOT NULL,
|
|
"externalCursor" TEXT,
|
|
"lastSyncAt" TIMESTAMP(3),
|
|
"status" "IiosCalendarSyncStatus" NOT NULL DEFAULT 'IDLE',
|
|
"errorCode" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "IiosCalendarSyncCursor_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "IiosMeetingRequest_scopeId_status_idx" ON "IiosMeetingRequest"("scopeId", "status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "IiosMeeting_scopeId_status_idx" ON "IiosMeeting"("scopeId", "status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "IiosMeetingParticipant_meetingId_idx" ON "IiosMeetingParticipant"("meetingId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "IiosMeetingParticipant_meetingId_actorRefId_key" ON "IiosMeetingParticipant"("meetingId", "actorRefId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "IiosMeetingTranscript_meetingId_idx" ON "IiosMeetingTranscript"("meetingId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "IiosTranscriptSegment_transcriptId_idx" ON "IiosTranscriptSegment"("transcriptId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "IiosMeetingSummary_meetingId_idx" ON "IiosMeetingSummary"("meetingId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "IiosActionItem_scopeId_status_idx" ON "IiosActionItem"("scopeId", "status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "IiosMeetingActionItem_meetingId_idx" ON "IiosMeetingActionItem"("meetingId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "IiosMeetingActionItem_meetingId_actionItemId_key" ON "IiosMeetingActionItem"("meetingId", "actionItemId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "IiosCalendarProviderAccount_scopeId_idx" ON "IiosCalendarProviderAccount"("scopeId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "IiosCalendarEvent_scopeId_idx" ON "IiosCalendarEvent"("scopeId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "IiosCalendarEvent_providerAccountId_externalEventId_key" ON "IiosCalendarEvent"("providerAccountId", "externalEventId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "IiosAvailabilityWindow_scopeId_actorRefId_idx" ON "IiosAvailabilityWindow"("scopeId", "actorRefId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "IiosCalendarSyncCursor_providerAccountId_key" ON "IiosCalendarSyncCursor"("providerAccountId");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "IiosMeetingParticipant" ADD CONSTRAINT "IiosMeetingParticipant_meetingId_fkey" FOREIGN KEY ("meetingId") REFERENCES "IiosMeeting"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "IiosMeetingTranscript" ADD CONSTRAINT "IiosMeetingTranscript_meetingId_fkey" FOREIGN KEY ("meetingId") REFERENCES "IiosMeeting"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "IiosTranscriptSegment" ADD CONSTRAINT "IiosTranscriptSegment_transcriptId_fkey" FOREIGN KEY ("transcriptId") REFERENCES "IiosMeetingTranscript"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "IiosMeetingSummary" ADD CONSTRAINT "IiosMeetingSummary_meetingId_fkey" FOREIGN KEY ("meetingId") REFERENCES "IiosMeeting"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "IiosMeetingActionItem" ADD CONSTRAINT "IiosMeetingActionItem_meetingId_fkey" FOREIGN KEY ("meetingId") REFERENCES "IiosMeeting"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "IiosMeetingActionItem" ADD CONSTRAINT "IiosMeetingActionItem_actionItemId_fkey" FOREIGN KEY ("actionItemId") REFERENCES "IiosActionItem"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "IiosCalendarEvent" ADD CONSTRAINT "IiosCalendarEvent_providerAccountId_fkey" FOREIGN KEY ("providerAccountId") REFERENCES "IiosCalendarProviderAccount"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "IiosCalendarSyncCursor" ADD CONSTRAINT "IiosCalendarSyncCursor_providerAccountId_fkey" FOREIGN KEY ("providerAccountId") REFERENCES "IiosCalendarProviderAccount"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|