024 - MPC CRM dedicated database (crmdb)

• Tanggal: 2026-08-15
• DB: crmdb (terpisah dari mpcdb)
• Ringkas: Database portable untuk mpc-crm: users, channel vault, contacts, tokens, templates, audiences, campaigns, delivery logs.

Provision DB

CREATE DATABASE crmdb;

ALTER (jalankan di crmdb)

Aplikasi memakai Prisma (npx prisma db push / prisma migrate). DDL setara:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS crm_users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  email text NOT NULL UNIQUE,
  password_hash text NOT NULL,
  role text NOT NULL DEFAULT 'editor',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);

CREATE TABLE IF NOT EXISTS crm_channel_profiles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  environment text NOT NULL,
  channel text NOT NULL,
  credentials_enc text,
  meta jsonb NOT NULL DEFAULT '{}',
  is_configured boolean NOT NULL DEFAULT false,
  updated_by uuid,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (environment, channel)
);

CREATE TABLE IF NOT EXISTS crm_settings (
  key text PRIMARY KEY,
  value text NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS crm_contacts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  external_id text UNIQUE,
  name text,
  email text,
  phone text,
  membership_tier text,
  total_spent numeric(15,2) NOT NULL DEFAULT 0,
  last_booking_at timestamptz,
  attrs jsonb NOT NULL DEFAULT '{}',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz,
  source_created_at timestamptz
);
CREATE INDEX IF NOT EXISTS idx_crm_contacts_email ON crm_contacts (email);
CREATE INDEX IF NOT EXISTS idx_crm_contacts_deleted ON crm_contacts (deleted_at);

CREATE TABLE IF NOT EXISTS crm_device_tokens (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  contact_id uuid NOT NULL REFERENCES crm_contacts(id) ON DELETE CASCADE,
  platform text NOT NULL,
  token text NOT NULL UNIQUE,
  app text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);
CREATE INDEX IF NOT EXISTS idx_crm_device_tokens_contact ON crm_device_tokens (contact_id);

CREATE TABLE IF NOT EXISTS crm_templates (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  channel text NOT NULL,
  category text NOT NULL DEFAULT 'marketing',
  subject text,
  title text,
  body text NOT NULL,
  variables text[] NOT NULL DEFAULT '{}',
  status text NOT NULL DEFAULT 'active',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);

CREATE TABLE IF NOT EXISTS crm_audiences (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  description text,
  rules jsonb NOT NULL DEFAULT '{}',
  approx_count int,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);

CREATE TABLE IF NOT EXISTS crm_campaigns (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  channels text[] NOT NULL,
  template_id uuid REFERENCES crm_templates(id),
  audience_id uuid REFERENCES crm_audiences(id),
  environment_snapshot text NOT NULL,
  status text NOT NULL DEFAULT 'draft',
  schedule_at timestamptz,
  started_at timestamptz,
  finished_at timestamptz,
  created_by uuid REFERENCES crm_users(id),
  inline_title text,
  inline_subject text,
  inline_body text,
  sent_count int NOT NULL DEFAULT 0,
  failed_count int NOT NULL DEFAULT 0,
  skipped_count int NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_crm_campaigns_schedule ON crm_campaigns (status, schedule_at);

CREATE TABLE IF NOT EXISTS crm_delivery_logs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  campaign_id uuid NOT NULL REFERENCES crm_campaigns(id) ON DELETE CASCADE,
  contact_id uuid REFERENCES crm_contacts(id),
  channel text NOT NULL,
  status text NOT NULL DEFAULT 'queued',
  provider_id text,
  error text,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_crm_delivery_logs_campaign ON crm_delivery_logs (campaign_id);
CREATE INDEX IF NOT EXISTS idx_crm_delivery_logs_status ON crm_delivery_logs (status);

Rollback

-- Hanya di crmdb
DROP TABLE IF EXISTS crm_delivery_logs CASCADE;
DROP TABLE IF EXISTS crm_campaigns CASCADE;
DROP TABLE IF EXISTS crm_audiences CASCADE;
DROP TABLE IF EXISTS crm_templates CASCADE;
DROP TABLE IF EXISTS crm_device_tokens CASCADE;
DROP TABLE IF EXISTS crm_contacts CASCADE;
DROP TABLE IF EXISTS crm_settings CASCADE;
DROP TABLE IF EXISTS crm_channel_profiles CASCADE;
DROP TABLE IF EXISTS crm_users CASCADE;
-- DROP DATABASE crmdb; -- opsional

Catatan

• mpcdb tidak diubah oleh CRM.
• Sync opsional kontak/token dari mpcdb: npm run db:sync-mpc (butuh MPC_DATABASE_URL).
• mpc-admin / mpc-be / mpc-be-pos: TIDAK PERLU CRUD untuk tabel ini.