101 lines
3.8 KiB
PL/PgSQL
101 lines
3.8 KiB
PL/PgSQL
-- Velocity Comms Schema
|
|
-- Run this migration against your asyncpg pool database.
|
|
|
|
BEGIN;
|
|
|
|
CREATE EXTENSION IF NOT EXISTS pgcrypto;
|
|
|
|
-- Threads (conversations)
|
|
CREATE TABLE IF NOT EXISTS comms_threads (
|
|
thread_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
provider TEXT NOT NULL DEFAULT 'mock',
|
|
external_thread_id TEXT,
|
|
person_id UUID NULL REFERENCES crm_people(person_id) ON DELETE SET NULL,
|
|
phone_e164 TEXT NOT NULL,
|
|
display_name TEXT,
|
|
channel TEXT NOT NULL DEFAULT 'whatsapp',
|
|
status TEXT NOT NULL DEFAULT 'open',
|
|
assigned_user_id UUID NULL,
|
|
last_message_at TIMESTAMPTZ,
|
|
unread_count INT NOT NULL DEFAULT 0,
|
|
metadata_json JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_comms_threads_phone ON comms_threads(phone_e164);
|
|
CREATE INDEX IF NOT EXISTS idx_comms_threads_person ON comms_threads(person_id) WHERE person_id IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_comms_threads_status ON comms_threads(status);
|
|
CREATE INDEX IF NOT EXISTS idx_comms_threads_last_message ON comms_threads(last_message_at DESC NULLS LAST);
|
|
|
|
-- Messages
|
|
CREATE TABLE IF NOT EXISTS comms_messages (
|
|
message_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
thread_id UUID NOT NULL REFERENCES comms_threads(thread_id) ON DELETE CASCADE,
|
|
provider TEXT NOT NULL DEFAULT 'mock',
|
|
external_message_id TEXT,
|
|
direction TEXT NOT NULL CHECK (direction IN ('inbound', 'outbound', 'system')),
|
|
message_type TEXT NOT NULL DEFAULT 'text',
|
|
body TEXT NOT NULL DEFAULT '',
|
|
media_url TEXT,
|
|
media_mime_type TEXT,
|
|
delivery_status TEXT NOT NULL DEFAULT 'pending',
|
|
sent_at TIMESTAMPTZ,
|
|
delivered_at TIMESTAMPTZ,
|
|
read_at TIMESTAMPTZ,
|
|
raw_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_comms_messages_thread ON comms_messages(thread_id, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_comms_messages_external ON comms_messages(external_message_id) WHERE external_message_id IS NOT NULL;
|
|
|
|
-- Call logs
|
|
CREATE TABLE IF NOT EXISTS comms_call_logs (
|
|
call_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
thread_id UUID NULL REFERENCES comms_threads(thread_id) ON DELETE SET NULL,
|
|
person_id UUID NULL REFERENCES crm_people(person_id) ON DELETE SET NULL,
|
|
provider TEXT NOT NULL DEFAULT 'mock',
|
|
external_call_id TEXT,
|
|
phone_e164 TEXT NOT NULL,
|
|
direction TEXT NOT NULL CHECK (direction IN ('inbound', 'outbound')),
|
|
status TEXT NOT NULL DEFAULT 'completed',
|
|
started_at TIMESTAMPTZ NOT NULL,
|
|
ended_at TIMESTAMPTZ,
|
|
duration_seconds INT,
|
|
recording_url TEXT,
|
|
transcript_id UUID,
|
|
transcript_text TEXT,
|
|
raw_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_comms_call_logs_phone ON comms_call_logs(phone_e164);
|
|
CREATE INDEX IF NOT EXISTS idx_comms_call_logs_thread ON comms_call_logs(thread_id) WHERE thread_id IS NOT NULL;
|
|
|
|
-- Settings (key-value JSON)
|
|
CREATE TABLE IF NOT EXISTS comms_settings (
|
|
key TEXT PRIMARY KEY,
|
|
value_json JSONB NOT NULL DEFAULT '{}'::jsonb,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- Insert default settings
|
|
INSERT INTO comms_settings (key, value_json) VALUES ('config', '{
|
|
"provider": "mock",
|
|
"provider_base_url": "",
|
|
"provider_api_key": "",
|
|
"instance_id": "",
|
|
"phone_number_id": "",
|
|
"webhook_callback_url": "",
|
|
"webhook_secret_set": false,
|
|
"default_assignment_user_id": null,
|
|
"auto_link_by_phone": true,
|
|
"create_crm_interaction_on_inbound": true,
|
|
"default_country_code": "91",
|
|
"media_storage_dir": "/opt/dlami/nvme/assets/comms",
|
|
"transcription_provider": "none"
|
|
}'::jsonb) ON CONFLICT (key) DO NOTHING;
|
|
|
|
COMMIT;
|