24 lines
1.0 KiB
SQL
24 lines
1.0 KiB
SQL
CREATE TABLE "conversation_messages" (
|
|
"id" UUID NOT NULL DEFAULT gen_random_uuid(),
|
|
"contact_id" UUID NOT NULL,
|
|
"direction" TEXT NOT NULL,
|
|
"message_type" TEXT NOT NULL DEFAULT 'text',
|
|
"source" TEXT NOT NULL DEFAULT 'agent',
|
|
"body" TEXT NOT NULL,
|
|
"status" TEXT NOT NULL DEFAULT 'sent',
|
|
"sender_user_id" UUID,
|
|
"sender_name" TEXT,
|
|
"external_message_id" TEXT,
|
|
"webhook_event_id" TEXT,
|
|
"occurred_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"read_at" TIMESTAMP(3),
|
|
"created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "conversation_messages_pkey" PRIMARY KEY ("id"),
|
|
CONSTRAINT "conversation_messages_contact_id_fkey" FOREIGN KEY ("contact_id") REFERENCES "contacts"("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
CREATE INDEX "conversation_messages_contact_id_occurred_at_idx" ON "conversation_messages"("contact_id", "occurred_at");
|
|
CREATE UNIQUE INDEX "conversation_messages_webhook_event_id_key" ON "conversation_messages"("webhook_event_id");
|