PostgreSQL adalah backing store untuk 15 dari 18 modul domain
OmniStream (sisanya message dan webhook_audit di MongoDB;
events bukan store, melainkan wire-format). Seluruh schema dibuat
dari file migrasi SQL yang dijalankan otomatis oleh omni-common
via sqlx::migrate! pada startup setiap service.
Setiap file berurutan dari initial schema (Feb 2026) sampai
pemuatan dukungan Messenger (Apr 2026) dan wa_flows / automation
rollout. Beberapa migrasi penting yang sering dirujuk:
Daftar di bawah adalah tabel utama yang dirujuk oleh route
handler dan models di crates/omni-common/src/models/. Tabel
pendukung (mis. enum lookup, index helpers) dihilangkan untuk
keringkasan. Sumber otoritatif tetap file migrasi.
Agent dan RBAC
Code
agents — user internal (admin/supervisor/agent) id UUID PK email TEXT UNIQUE password_hash TEXT — argon2id PHC string name TEXT role TEXT — 'admin' | 'supervisor' | 'agent' is_active BOOLEAN last_seen_at TIMESTAMPTZ created_at TIMESTAMPTZ updated_at TIMESTAMPTZdivisions — tim / departemen id UUID PK name TEXT description TEXTagent_divisions — keanggotaan M:N agent <-> division agent_id UUID FK -> agents.id division_id UUID FK -> divisions.id PRIMARY KEY (agent_id, division_id)
contacts — pelanggan / end-user id UUID PK phone TEXT — WhatsApp JID / nomor email TEXT name TEXT profile_pic_url TEXT external_id TEXT — ID di platform (mis. IG user id) created_at TIMESTAMPTZconversations — percakapan antara contact dan agent id UUID PK contact_id UUID FK -> contacts.id assigned_agent_id UUID FK -> agents.id NULL division_id UUID FK -> divisions.id NULL channel TEXT — 'whatsapp' | 'instagram' | 'email' | 'messenger' status TEXT — 'open' | 'resolved' (post-migration 39) last_message_at TIMESTAMPTZ last_message_preview TEXT unread_count INT created_at TIMESTAMPTZ resolved_at TIMESTAMPTZ NULLconversation_notes — catatan internal (tidak dilihat contact) id UUID PK conversation_id UUID FK -> conversations.id agent_id UUID FK -> agents.id body TEXT created_at TIMESTAMPTZconversation_transfers — log transfer antar-agent/divisi id UUID PK conversation_id UUID FK -> conversations.id from_agent_id UUID to_agent_id UUID reason TEXT transferred_at TIMESTAMPTZ
Kolom channel di conversations adalah salah satu titik hotspot
refactoring — perubahan enumnya menyentuh tiga query dengan daftar
kolom eksplisit (conversations.rs::list_conversations, ::get_conversation,
contacts.rs::list_contact_conversations), lihat catatan di CLAUDE.md.
Quick replies dan WA templates
Code
quick_replies — template pesan cepat milik org id UUID PK division_id UUID FK -> divisions.id NULL title TEXT body TEXT media_url TEXT created_by UUID FK -> agents.idagent_favorite_replies — pin per-agent agent_id UUID FK -> agents.id quick_reply_id UUID FK -> quick_replies.id PRIMARY KEY (agent_id, quick_reply_id)wa_templates — cache Meta-approved WhatsApp templates id UUID PK name TEXT language TEXT category TEXT status TEXT — 'APPROVED' | 'PENDING' | 'REJECTED' header_type TEXT header_media_url TEXT body_text TEXT footer_text TEXT components_json JSONB
Campaign dan scheduled messages
Code
campaigns — broadcast campaign id UUID PK name TEXT template_id UUID FK -> wa_templates.id NULL status TEXT — 'draft' | 'scheduled' | 'running' | 'completed' | 'failed' scheduled_at TIMESTAMPTZ integration_account_id UUID NULL created_by UUID FK -> agents.id created_at TIMESTAMPTZcampaign_recipients — satu row per tujuan id UUID PK campaign_id UUID FK -> campaigns.id phone TEXT status TEXT — 'pending' | 'sent' | 'delivered' | 'failed' conversation_id UUID NULL — di-fill saat pertama kali dibalas sent_at TIMESTAMPTZ NULL error_message TEXT NULLscheduled_messages — pesan terjadwal non-campaign id UUID PK conversation_id UUID FK -> conversations.id agent_id UUID FK -> agents.id msg_type TEXT content_json JSONB scheduled_at TIMESTAMPTZ status TEXT — 'pending' | 'sent' | 'cancelled' | 'failed'
Integration, SLA, CSAT, webhook, activity log
Code
integrations — konfigurasi channel (WA/IG/Email) id UUID PK channel TEXT name TEXT credentials_json JSONB — enkripsi secret di-handle di app layer is_active BOOLEAN updated_at TIMESTAMPTZintegration_accounts — multi-account per channel (sejak migrasi 30) id UUID PK integration_id UUID FK -> integrations.id external_id TEXT display_name TEXT is_default BOOLEANsla_policies — kebijakan SLA id UUID PK name TEXT channel TEXT NULL division_id UUID NULL first_response_minutes INT resolution_minutes INT priority INT — derived: channel+division(4), division(2), channel(2), default(1)sla_breach_logs — log pelanggaran SLA id UUID PK conversation_id UUID FK -> conversations.id policy_id UUID FK -> sla_policies.id breach_type TEXT — 'first_response' | 'resolution' breached_at TIMESTAMPTZcsat_surveys — survei kepuasan pasca-resolve id UUID PK conversation_id UUID FK -> conversations.id contact_id UUID FK -> contacts.id score INT NULL — 1..5 feedback TEXT NULL survey_sent_at TIMESTAMPTZ responded_at TIMESTAMPTZ NULLoutgoing_webhooks — endpoint webhook milik customer id UUID PK url TEXT secret TEXT — HMAC secret events TEXT[] — daftar event subscribed is_active BOOLEANwebhook_deliveries — antrian delivery + retry id UUID PK webhook_id UUID FK -> outgoing_webhooks.id event_type TEXT payload_json JSONB status TEXT — 'pending' | 'success' | 'failed' attempts INT next_retry_at TIMESTAMPTZ NULL last_error TEXT NULLactivity_logs — audit trail per-agent id UUID PK agent_id UUID FK -> agents.id action TEXT — 'login', 'resolve_conversation', ... target_type TEXT target_id UUID NULL metadata_json JSONB created_at TIMESTAMPTZ
Deferred tables (v1.1)
Dua tabel di schema sudah ada tapi tidak dibahas di dokumentasi v1: