023 - Device tokens + notification category/data

• Tanggal: 2026-08-15
• DB: mpcdb
• Ringkas: Tabel device_tokens untuk FCM; kolom category + data pada notifications untuk deep link / audit channel.

ALTER (jalankan di DB server)

-- Device tokens (FCM / Web Push)
CREATE TABLE IF NOT EXISTS device_tokens (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  audience text NOT NULL,
  audience_id uuid NOT NULL,
  platform text NOT NULL,
  token text NOT NULL,
  app text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz,
  CONSTRAINT device_tokens_audience_check
    CHECK (audience IN ('customer', 'admin', 'owner', 'pos')),
  CONSTRAINT device_tokens_platform_check
    CHECK (platform IN ('android', 'ios', 'web'))
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_device_tokens_token
  ON device_tokens (token)
  WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_device_tokens_audience
  ON device_tokens (audience, audience_id)
  WHERE deleted_at IS NULL;

-- Enrich in-app notifications
ALTER TABLE notifications
  ADD COLUMN IF NOT EXISTS category text,
  ADD COLUMN IF NOT EXISTS data jsonb;

CREATE INDEX IF NOT EXISTS idx_notifications_category
  ON notifications (category)
  WHERE deleted_at IS NULL;

Rollback (opsional)

DROP INDEX IF EXISTS idx_notifications_category;
ALTER TABLE notifications DROP COLUMN IF EXISTS data;
ALTER TABLE notifications DROP COLUMN IF EXISTS category;
DROP INDEX IF EXISTS idx_device_tokens_audience;
DROP INDEX IF EXISTS uq_device_tokens_token;
DROP TABLE IF EXISTS device_tokens;

Catatan

• Dampak: mpc-be (FCM + register token), member Android/iOS, POS, admin/owner web push.
• iOS client push ditunda sampai APNs (lihat mpc-dev/docs/tasks.md).