`scripts/migrate_006_contacts.sql`

18 lines
-- User-imported contacts (address book) — for platform-match + invite/referral.
-- PII: a user's own contacts only; never bulk-messaged by the platform.
CREATE TABLE IF NOT EXISTS contacts (
  id             BIGSERIAL PRIMARY KEY,
  owner_id       BIGINT NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
  display_name   TEXT,
  phone_e164     TEXT,                                   -- normalized
  email          TEXT,
  matched_user_id BIGINT REFERENCES app_users(id) ON DELETE SET NULL,  -- already on platform?
  invited_at     TIMESTAMPTZ,
  invite_channel TEXT,                                   -- whatsapp | sms
  created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (owner_id, phone_e164)
);
CREATE INDEX IF NOT EXISTS idx_contacts_owner   ON contacts(owner_id);
CREATE INDEX IF NOT EXISTS idx_contacts_matched ON contacts(matched_user_id);
CREATE INDEX IF NOT EXISTS idx_contacts_phone   ON contacts(phone_e164);