cocottetech/@platform/infrastructure/sql/migrations/0003_mailboxes_and_channels.sql

-- 0003_mailboxes_and_channels.sql
--
-- Adds the channel/mailbox dimension distinct from surface_kind, per brief P
-- (unified inbox) + brief O §N4 channels carve-out. Engagement events that
-- arrive via a messaging channel (iMessage, SMS, email, Signal, etc.) need a
-- channel_kind separate from surface_kind because:
--
--   surface_kind == "external platform where content lives or a listing is
--                    posted" (e.g. tryst, onlyfans).
--   channel_kind == "messaging transport an inbound arrived through"
--                    (e.g. iMessage, SMS, email).
--
-- The same prospect can hit Quinn via iMessage + OF DM. Without a separate
-- axis we conflate channel-of-arrival with surface-of-origin, which breaks
-- brief K §K3h (channel-vs-surface separation invariant) and brief P's
-- "source-labeled, not source-grouped" UX.
--
-- mailboxes table holds one row per ingest source (Quinn has ≥4 Proton
-- inboxes + Gmail + iMessage + SMS + per-surface DM bridges). The unified
-- inbox (brief P) renders one row per engagement_event labeled with the
-- mailbox + channel_kind.

BEGIN;

-- ---------------------------------------------------------------------------
-- channel_kind ENUM — N4 channels roster per O-surfaces-roster.brief.md
-- ---------------------------------------------------------------------------
CREATE TYPE channel_kind AS ENUM (
  'imessage',   -- mac-sync:3100 via plum; anchor channel
  'sms',        -- carrier protocol; distinct from iMessage at protocol level
  'signal',     -- P3+ future MCP
  'telegram',   -- P3+ bot API
  'discord',    -- P3+ bot API, light surface
  'email',      -- Proton (per mail-sync:4444) + Gmail + iCloud + any IMAP
  'sniffies'    -- web-session light surface, mentioned in coworker-agent scan
);

-- ---------------------------------------------------------------------------
-- mailbox_kind ENUM — sub-classification when channel_kind = 'email'.
-- Drives display in brief P's source-label chip and ingest routing.
-- ---------------------------------------------------------------------------
CREATE TYPE mailbox_kind AS ENUM (
  'proton_personal',       -- canonical anchor inbox (e.g. quinn@cocotte.maison)
  'proton_per_platform',   -- per-directory verification inbox (e.g. myprivatedelights@protonmail.com)
  'proton_per_brand',      -- per-brand-site contact (e.g. bookings@tqftw.com)
  'gmail',                 -- read-only legacy triage
  'icloud',                -- read-only legacy triage
  'other'
);

-- ---------------------------------------------------------------------------
-- triage_policy ENUM — per-mailbox / per-channel default behavior.
-- Surfaced in brief P §P3 per-source triage posture editor.
-- ---------------------------------------------------------------------------
CREATE TYPE triage_policy AS ENUM (
  'auto_reply',     -- triage drafts AND auto-sends eligible warm inbounds
  'draft_only',     -- triage drafts, Quinn approves before send
  'auto_archive',   -- transactional inbox; archive without surfacing (e.g. verification emails)
  'hands_off',      -- ingest + show but never draft (e.g. directory inboxes initially)
  'system'          -- platform-managed (verification flows); not Quinn-facing
);

-- ---------------------------------------------------------------------------
-- mailboxes table — one row per ingest source.
--
-- A user has many mailboxes; each mailbox is bound to exactly one channel_kind.
-- `mailbox_kind` is NULL except when channel_kind = 'email' (other channels
-- don't sub-classify).
--
-- `identifier` is the canonical address/handle for this mailbox:
--   - email:    'quinn@cocotte.maison'
--   - imessage: the Apple ID or phone-number this mailbox represents
--   - sms:      the phone number
--   - signal/telegram/discord: account handle
--   - sniffies: account handle
--
-- (user_id, channel_kind, identifier) is unique per active mailbox so Quinn
-- can't accidentally register the same source twice. Deleted/inactive mailboxes
-- don't block re-registration of the same identifier later.
-- ---------------------------------------------------------------------------
CREATE TABLE mailboxes (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id               UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  org_id                UUID NULL REFERENCES orgs(id) ON DELETE CASCADE,
  channel_kind          channel_kind NOT NULL,
  mailbox_kind          mailbox_kind NULL,           -- only set when channel_kind='email'
  display_name          TEXT NOT NULL,                -- "Proton: myprivatedelights@protonmail.com"
  identifier            TEXT NOT NULL,                -- canonical address/handle for this source
  triage_policy         triage_policy NOT NULL DEFAULT 'draft_only',
  poll_interval_seconds INTEGER NOT NULL DEFAULT 300,
  active                BOOLEAN NOT NULL DEFAULT TRUE,
  last_ingest_at        TIMESTAMPTZ NULL,
  config_json           JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at            TIMESTAMPTZ NOT NULL DEFAULT now(),

  -- Hard invariant: mailbox_kind is only meaningful for email channels.
  CONSTRAINT mailboxes_email_kind_invariant CHECK (
    (channel_kind = 'email' AND mailbox_kind IS NOT NULL) OR
    (channel_kind <> 'email' AND mailbox_kind IS NULL)
  ),
  CONSTRAINT mailboxes_poll_interval_positive CHECK (poll_interval_seconds > 0)
);

-- Per-user channel lookup (drives brief P unified-inbox source filter chip).
CREATE INDEX idx_mailboxes_user_channel ON mailboxes(user_id, channel_kind) WHERE active = TRUE;

-- Prevent duplicate active mailboxes for the same identifier on the same channel.
CREATE UNIQUE INDEX uq_mailboxes_user_channel_identifier_active
  ON mailboxes(user_id, channel_kind, identifier)
  WHERE active = TRUE;

-- updated_at trigger using the existing function from 0001.
CREATE TRIGGER trg_touch_mailboxes BEFORE UPDATE ON mailboxes
  FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

-- ---------------------------------------------------------------------------
-- engagement_events extensions
--
-- engagement_events already carries surface_kind (where the event originated
-- from the prospect's perspective). Adding channel_kind + mailbox_id lets the
-- unified inbox label rows correctly when the inbound arrived through a
-- channel rather than a content surface.
--
-- Both new columns are NULL because legacy engagement_events rows ingested
-- before this migration didn't track channel/mailbox. New ingestor code MUST
-- populate channel_kind for inbox-bound events (mailbox_id is required when
-- channel_kind = 'email', optional otherwise).
-- ---------------------------------------------------------------------------
ALTER TABLE engagement_events
  ADD COLUMN channel_kind channel_kind NULL,
  ADD COLUMN mailbox_id   UUID NULL REFERENCES mailboxes(id) ON DELETE SET NULL;

-- Application-layer invariant (Postgres can't enforce this conditionally
-- against legacy rows): when channel_kind = 'email', mailbox_id must be set.
-- Enforced in platform-api's engagement-event service before insert.

CREATE INDEX idx_engagement_events_mailbox  ON engagement_events(mailbox_id)
  WHERE mailbox_id IS NOT NULL;
CREATE INDEX idx_engagement_events_channel  ON engagement_events(user_id, channel_kind, occurred_at DESC)
  WHERE channel_kind IS NOT NULL;

-- ---------------------------------------------------------------------------
-- Row-level security for mailboxes — same pattern as scoped tables in 0001.
-- ---------------------------------------------------------------------------
ALTER TABLE mailboxes ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON mailboxes
  USING (
    user_id = current_user_uuid()
    OR (org_id IS NOT NULL AND org_id IN (
      SELECT org_id FROM org_members WHERE user_id = current_user_uuid()
    ))
  )
  WITH CHECK (
    user_id = current_user_uuid()
    OR (org_id IS NOT NULL AND org_id IN (
      SELECT org_id FROM org_members WHERE user_id = current_user_uuid()
    ))
  );

COMMIT;

-- ---------------------------------------------------------------------------
-- Follow-ups (NOT in this migration; flagged for the brief P engineering pass):
--   - mail-sync extension to support multi-Proton-credential ingestion (#15).
--   - engagement-ingestor worker writes channel_kind + mailbox_id on insert.
--   - platform-api Mailbox entity + CRUD module mirroring this schema.
--   - enums.ts mirror update (lock-step with this file).
-- ---------------------------------------------------------------------------