← back to Costa Rica

scripts/migrate_007_messages.sql

20 lines

-- In-app messenger: a traveler ↔ listing/host thread (our own channel, so
-- contact never has to leave the app). Complements WhatsApp/phone/email.
CREATE TABLE IF NOT EXISTS threads (
  id          BIGSERIAL PRIMARY KEY,
  place_id    INTEGER REFERENCES places(id) ON DELETE CASCADE,
  traveler_id BIGINT REFERENCES app_users(id) ON DELETE CASCADE,
  host_id     BIGINT REFERENCES hosts(id) ON DELETE SET NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (place_id, traveler_id)
);
CREATE TABLE IF NOT EXISTS messages (
  id          BIGSERIAL PRIMARY KEY,
  thread_id   BIGINT NOT NULL REFERENCES threads(id) ON DELETE CASCADE,
  sender_id   BIGINT REFERENCES app_users(id) ON DELETE SET NULL,
  sender_role TEXT NOT NULL CHECK (sender_role IN ('traveler','host','system')),
  body        TEXT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_messages_thread ON messages(thread_id, created_at);