Skip to main content

Visitor Sessions

Every visit with traffic params (UTM, partner) creates a visitor_session row in PostgreSQL. This is the source of truth for traffic attribution.

Table Schema​

visitor_session (
    id              BIGSERIAL PRIMARY KEY,
    bev             VARCHAR(100) NOT NULL,      -- cookie value
    guest_id        VARCHAR(100),               -- linked after login
    utm_source, utm_medium, utm_campaign, utm_term, utm_content,
    traffic_source  VARCHAR(50),                -- ORGANIC, PAID, PARTNER, etc.
    referrer_url    TEXT,
    landing_page    TEXT,
    partner_id      VARCHAR(100),
    device_type     VARCHAR(20),
    user_agent      TEXT,
    ip_address      VARCHAR(45),
    geo_country     VARCHAR(10),
    loyalty_tier_level INTEGER,
    created_at      TIMESTAMPTZ
)

When Sessions Are Created​

A session is persisted only when:

  • UTM params are present (utm_source, utm_medium, utm_campaign), or
  • A partner ID is present (partner_id param or X-Partner-Id header)

Regular visits without traffic params do not create sessions (reduces write volume).

Session Lookup​

The VisitorContextResolver resolves context from stored sessions:

resolveFromBev("1712345_abcdef")
  → L1 Caffeine (5 min) → cache hit? return
  → L2 Redis OM (7 day TTL) → cache hit? backfill L1, return
  → DB: SELECT * FROM visitor_session
         WHERE bev = ? AND (partner_id IS NOT NULL OR utm_source IS NOT NULL)
         AND created_at > NOW() - INTERVAL '7 days'
         ORDER BY created_at DESC LIMIT 1
  → found? cache in L1+L2, return
  → not found? return empty (default experience)

Guest Linking​

When an anonymous visitor logs in, their bev sessions are linked to their guest ID:

UPDATE visitor_session SET guest_id = 'guest_123'
WHERE bev = '1712345_abcdef' AND guest_id IS NULL