Subin Joshua

-- Core entities (simplified)
customers (
  customer_id   UUID PRIMARY KEY,
  email         TEXT UNIQUE,
  source        TEXT,
  member_id     UUID REFERENCES team_members
)
    │
    │ 1:many
    ▼
booking_events (
  event_uuid       UUID PRIMARY KEY,
  customer_id      UUID REFERENCES customers,
  meeting_time     TIMESTAMPTZ,
  touch_type       TEXT,
  meeting_sequence INT  -- computed via trigger
)
    │
    │ 1:many
    ▼
deals (
  deal_id      UUID PRIMARY KEY,
  customer_id  UUID REFERENCES customers,
  pipeline_id  UUID,
  stage_id     UUID,
  status       TEXT
)
    │
    │ 1:many
    ▼
deal_stage_history (
  history_id   UUID PRIMARY KEY,
  deal_id      UUID REFERENCES deals,
  prev_stage   UUID,
  new_stage    UUID,
  changed_at   TIMESTAMPTZ DEFAULT NOW()
)

-- Compute meeting_sequence at INSERT, not query time
CREATE OR REPLACE FUNCTION set_meeting_sequence()
RETURNS TRIGGER AS $$
BEGIN
  NEW.meeting_sequence := (
    SELECT COUNT(*) + 1
    FROM booking_events
    WHERE customer_id = NEW.customer_id
      AND meeting_time < NEW.meeting_time
      AND status = 'active'
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_meeting_sequence
  BEFORE INSERT ON booking_events
  FOR EACH ROW
  EXECUTE FUNCTION set_meeting_sequence();