Interactions, Visits, and Customers

Complete Data Model Documentation

Last Updated: October 2025

Documentation Accuracy: Verified against codebase implementation October 2025

Overview

The Forkast system uses three primary collections to track user engagement:

Collection	Purpose	Created When
interactions	Anonymous engagement tracking	ALWAYS - Every page load is recorded
visits	Form submission records	User submits email/phone data
customers	Returning user tracking	First time email is submitted

⚠️ CRITICAL: Interaction Recording Rules

ALL interactions are recorded, but flagged with trackingSource for data quality:

trackingSource Values:

'path' - ✅ High quality: Path-based URL (e.g., /ig/abc/q/m1)
'query' - ✅ High quality: Query parameter URL (e.g., /ig/abc?p=m1&q)
'untracked' - ⚠️ Low quality: Direct visit without tracking params

Examples:

/ig/abc123/q/m1          → trackingSource: 'path' (QR + placement)
/ig/abc123?p=m1&q        → trackingSource: 'query' (QR + placement)
/gr/xyz789?n             → trackingSource: 'query' (NFC legacy)
/ig/abc123               → trackingSource: 'untracked' (direct visit - no attribution)
/gr/xyz789               → trackingSource: 'untracked' (direct visit - no attribution)

Why Always Record:

✅ Complete data: Never miss an interaction
✅ Quality metrics: Know which interactions are properly tracked
✅ Debugging power: Identify broken QR codes or missing parameters
✅ Analytics: Calculate conversion rates and tracking quality

Rationale: Recording all interactions allows us to see the full picture while maintaining data quality through the trackingSource flag.

Collection Schemas

1. INTERACTIONS Collection

Purpose: Track all QR/NFC scan events before user identification

interface Interaction {
  _id?: ObjectId;
  timestamp: string;                 // ISO timestamp of scan
  type: string;                      // 'qr', 'nfc', 'direct'
  placeShortId: string;              // Place identifier
  fork: string;                      // 'google' or 'instagram'
  templateType?: string;             // Which Place Type template was used
  sessionId?: string;                // Session tracking (empty on server, set client-side)
  userAgent?: string;                // Browser/device info
  deviceType?: string;               // 'mobile', 'tablet', 'desktop' (auto-detected from userAgent)
  ipAddress?: string;                // IP address (extracted from headers)
  referrer?: string;                 // HTTP referrer
  placement?: string;                // JSON stringified placement object (e.g., '{"type":"mesa","id":"1"}')
  customerId?: ObjectId;             // ✅ Customer ObjectId (set when user submits email/phone)
  customerEmail?: string;            // Email (filled when user submits) - DEPRECATED: Use customerId
  emailSubmittedAt?: string;         // When email was added
  trackingSource?: 'path' | 'query' | 'untracked'; // ✅ IMPLEMENTED: URL format quality indicator
}

Example Document (Tracked Interaction):

{
  _id: ObjectId("67234abc123def456789"),
  timestamp: "2024-10-22T10:30:00.234Z",
  type: "qr",
  placeShortId: "abc1234",
  fork: "instagram",
  templateType: "restaurant",
  sessionId: "sess_xyz789abc",
  placement: '{"type":"mesa","id":"1"}',
  trackingSource: "query",                  // Query parameter format (?p=m1&q)
  userAgent: "Mozilla/5.0 (iPhone; CPU iPhone OS 17_0 like Mac OS X)...",
  deviceType: "mobile",
  ipAddress: "192.168.1.100",
  referrer: "https://instagram.com",
  customerEmail: "[email protected]",      // Added after form submission
  emailSubmittedAt: "2024-10-22T10:31:20Z"  // Added after form submission
}

Example Document (Untracked Interaction):

{
  _id: ObjectId("67234def123abc456789"),
  timestamp: "2024-10-22T11:15:00.123Z",
  type: "direct",
  placeShortId: "abc1234",
  fork: "instagram",
  templateType: "restaurant",
  sessionId: "sess_abc123xyz",
  placement: '{"type":"unknown","id":null}',
  trackingSource: "untracked",              // Direct visit without tracking params
  userAgent: "Mozilla/5.0...",
  deviceType: "mobile",
  ipAddress: "192.168.1.101",
  referrer: "https://google.com"
}

Key Fields:

templateType: References the Place Type template (e.g., "restaurant", "retail", "default")
sessionId: Prevents duplicate interaction records from page refreshes
placement: JSON string with physical location metadata
trackingSource: NEW - Data quality indicator:
- 'path' = High quality (path-based URL like /ig/abc/q/m1)
- 'query' = High quality (query param URL like /ig/abc?p=m1&q)
- 'untracked' = Low quality (direct visit with no tracking params)

Created: Server-side on EVERY page load (always recorded):

Instagram Path Route: /src/app/ig/[identifier]/[[...tracking]]/page.tsx (lines 79-111)
Google Path Route: /src/app/gr/[identifier]/[[...tracking]]/page.tsx (lines 95-127)
Database Helper: database.saveInteraction() (lines 785-855)
Normalization Helper: normalizeTracking() (lines 26-83)
Device Detection: Automatic from userAgent (mobile/tablet/desktop)
Duplicate Prevention: sessionId-based checking to prevent double-counting

2. VISITS Collection

Purpose: Record form submissions with complete user data

interface Visit {
  _id?: ObjectId;
  placeId?: ObjectId;                // MongoDB ObjectId of the place
  placeShortId: string;              // String identifier for queries
  placeName?: string;                // DEPRECATED: Display name
  place_slug?: string;               // LEGACY: Old slug field
  place_shortid?: string;            // LEGACY: Old shortId field
  place_id?: ObjectId;               // LEGACY: Old ID field
  method: string;                    // 'qr', 'nfc', 'direct'
  fork?: string;                     // 'google' or 'instagram'
  url: string;                       // Full URL that was hit
  visitedAt: Date;                   // When visit occurred
  timestamp: string;                 // ISO string format
  ipAddress?: string;                // IP address
  ip?: string;                       // LEGACY: Duplicate of ipAddress
  email?: string;                    // Customer email (extracted from completedSteps)
  customerEmail?: string;            // DEPRECATED: Use email instead
  interactionId?: ObjectId;          // Link to interaction record
  customerId?: ObjectId;             // Reference to customer's _id (MongoDB ObjectId)
  referrer?: string;                 // HTTP referrer
  deviceType?: string;               // 'mobile', 'tablet', 'desktop'
  source?: string;                   // Source tracking
  userAgent?: string;                // Browser user agent
  forkUrl?: string;                  // Fork-specific URL
  placement?: {
    type: string;                    // 'cajero', 'mesa', 'unknown'
    id?: number | null;              // 1-999 or null
    location?: string;
    metadata?: Record<string, any>;
  };
  completedSteps?: Array<{           // ✅ CURRENT: Enhanced step tracking
    stepIndex: number;               // Step position in flow (0-indexed)
    stepFormId: string;              // Unique field ID (e.g., "field_0_email")
    stepResponse: any;               // User's actual response (any type)
    completedAt: string;             // ISO timestamp when step was completed
  }>;
  formData?: {                       // DEPRECATED: Migrated to completedSteps
    email?: string;
    phone?: string;
    [key: string]: any;
  };
  userLocation?: {                   // Optional geolocation
    lat: number;
    lng: number;
  };
}

Example Document:

{
  _id: ObjectId("67234def456abc789012"),
  placeShortId: "abc1234",
  placeId: ObjectId("67234aaa111bbb222ccc"),
  placeName: "Restaurant XYZ",
  fork: "instagram",
  method: "qr",
  url: "https://app.forkast.com/ig/abc1234?q=1&placement=mesa1",

  // Customer identification
  email: "[email protected]",
  customerId: ObjectId("67234xyz789abc456def"),  // Customer's _id (ObjectId reference)

  // Enhanced step completion tracking - self-contained step records
  // Each step includes: position, field ID, user's response, and completion timestamp
  completedSteps: [
    {
      stepIndex: 0,
      stepFormId: "field_0_email",
      stepResponse: "[email protected]",
      completedAt: "2024-10-22T10:30:15.234Z"
    },
    {
      stepIndex: 1,
      stepFormId: "field_1_phone",
      stepResponse: "+51999999999",
      completedAt: "2024-10-22T10:30:45.891Z"
    },
    {
      stepIndex: 2,
      stepFormId: "field_2_date",
      stepResponse: { month: 11, day: 15 },
      completedAt: "2024-10-22T10:31:08.445Z"
    },
    {
      stepIndex: 3,
      stepFormId: "field_3_number",
      stepResponse: "4",
      completedAt: "2024-10-22T10:31:20.567Z"
    },
    {
      stepIndex: 4,
      stepFormId: "field_4_text",
      stepResponse: "Window seat please",
      completedAt: "2024-10-22T10:31:35.123Z"
    }
  ],

  // Geolocation (if enabled)
  userLocation: {
    lat: -12.0464,
    lng: -77.0428
  },

  // Links to other collections
  interactionId: ObjectId("67234abc123def456789"),

  // Metadata
  visitedAt: new Date("2024-10-22T10:31:20Z"),
  timestamp: "2024-10-22T10:31:20.567Z",
  ipAddress: "192.168.1.100",
  deviceType: "mobile",
  userAgent: "Mozilla/5.0 (iPhone; CPU iPhone OS 17_0 like Mac OS X)...",
  referrer: "https://instagram.com",
  placement: {
    type: "mesa",
    id: "1",
    metadata: {}
  }
}

Key Fields:

completedSteps: ✅ Self-contained array of step records with:
- stepIndex: Position in the form flow (0-indexed)
- stepFormId: Unique field identifier from form template (e.g., "field_0_email", "field_1_phone")
- stepResponse: User's actual response (any type: string, number, boolean, etc.)
  - inputType: "date" stores { month, day }
  - inputType: "date-full" stores { year, month, day }
- completedAt: ISO timestamp when step was completed
interactionId: Links back to the initial QR/NFC scan interaction
email and phone: Extracted from completedSteps array automatically
DEPRECATED Fields: formData, customerEmail, placeName, place_slug, place_shortid, place_id, ip

Created:

Primary: POST to /src/app/api/pasos-submit/route.ts (lines 312-327)
Simple: POST to /src/app/api/submit-email/route.ts
Helper: database.recordVisit() (lines 749-765)

3. CUSTOMERS Collection

Purpose: Track returning users across multiple visits and places

interface Customer {
  _id?: ObjectId;
  customerId: string;                // Format: 'cust_{7-char-shortId}' (e.g., 'cust_aB3xYz9')
  email: string;                     // Unique customer email (lowercase, trimmed)
  phone?: string | null;             // Customer phone (E.164 format)
  createdAt: Date;                   // First contact date
  lastVisitAt: Date;                 // Last visit date (updated on each visit)
  totalVisits: number;               // Total visits across all places (incremented with $inc)
  places: Array<{                    // Array of visited places
    _id: ObjectId;                   // Place ObjectId
    shortId: string;                 // Place shortId
    name: string;                    // Place name
    visitCount: number;              // Visits to this specific place
    firstVisitAt: Date;              // First visit to this place
    lastVisitAt: Date;               // Last visit to this place (updated on each visit)
    visitIds: ObjectId[];            // Array of all visit _ids ($addToSet prevents duplicates)
  }>;
  metadata?: Record<string, any>;    // Custom data (source, userAgent, etc.)
}

Example Document (New Customer):

{
  _id: ObjectId("67234xyz789abc456def"),
  customerId: "cust_abc7890",
  email: "[email protected]",
  phone: "+51999999999",
  createdAt: new Date("2024-10-22T10:31:20Z"),
  lastVisitAt: new Date("2024-10-22T10:31:20Z"),
  totalVisits: 1,
  places: [
    {
      _id: ObjectId("67234aaa111bbb222ccc"),
      shortId: "abc1234",
      name: "Restaurant XYZ",
      visitCount: 1,
      firstVisitAt: new Date("2024-10-22T10:31:20Z"),
      lastVisitAt: new Date("2024-10-22T10:31:20Z"),
      visitIds: [ObjectId("67234def456abc789012")]
    }
  ],
  metadata: {
    source: "qr",
    userAgent: "Mozilla/5.0 (iPhone; CPU iPhone OS 17_0 like Mac OS X)...",
    ipAddress: "192.168.1.100"
  }
}

Example Document (Returning Customer):

{
  _id: ObjectId("67234xyz789abc456def"),
  customerId: "cust_abc7890",
  email: "[email protected]",
  phone: "+51999999999",
  createdAt: new Date("2024-10-15T14:20:00Z"),
  lastVisitAt: new Date("2024-10-25T15:30:00Z"),  // Updated on each visit
  totalVisits: 5,                                   // Incremented on each visit
  places: [
    {
      _id: ObjectId("67234aaa111bbb222ccc"),
      shortId: "abc1234",
      name: "Restaurant XYZ",
      visitCount: 3,                               // Visited this place 3 times
      firstVisitAt: new Date("2024-10-15T14:20:00Z"),
      lastVisitAt: new Date("2024-10-22T10:31:20Z"),
      visitIds: [
        ObjectId("67234def456abc789012"),
        ObjectId("67234ghi789jkl012mno"),
        ObjectId("67234pqr345stu678vwx")
      ]
    },
    {
      _id: ObjectId("67234bbb222ccc333ddd"),
      shortId: "def5678",
      name: "Cafe ABC",
      visitCount: 2,                               // Visited different place 2 times
      firstVisitAt: new Date("2024-10-18T11:15:00Z"),
      lastVisitAt: new Date("2024-10-25T15:30:00Z"),
      visitIds: [
        ObjectId("67234yza123bcd456efg"),
        ObjectId("67234hij789klm012nop")
      ]
    }
  ],
  metadata: {
    source: "qr",
    userAgent: "Mozilla/5.0 (iPhone; CPU iPhone OS 17_0 like Mac OS X)...",
    ipAddress: "192.168.1.100"
  }
}

Key Fields:

customerId: Unique identifier format cust_{7-char-shortId} generated using ShortIdGenerator
email: Unique index ensures no duplicates (lowercase, trimmed)
places[]: Array tracking every place this customer has visited
places[].visitIds[]: Complete history using $addToSet to prevent duplicate visit IDs
totalVisits: Incremented safely with $inc operator
phone: Optional, used for deduplication and merging

Created/Updated:

Location: /src/app/api/pasos-submit/route.ts (lines 334-502)
Strategy: Three-step customer merge logic:
1. Try email-based upsert (unique index)
2. Fallback to phone-only lookup if no email
3. Detect and merge duplicates if both email+phone provided
Helper: database.createCustomer() (lines 727-746)
Lookup: database.getCustomerByEmail() (lines 701-712)

Collection Relationships

┌─────────────────┐
│  INTERACTIONS   │
│  (Anonymous)    │
├─────────────────┤
│ _id             │──────┐
│ templateType    │      │
│ placeShortId    │      │
│ fork            │      │
│ sessionId       │◄─────┼────────────┐ (All interactions with same sessionId
│ placement       │      │            │  get customerId via updateMany)
│ customerId*     │──────┼────────────┼───┐
└─────────────────┘      │            │   │
                         │            │   │
                         │ interactionId  │ sessionId grouping
                         │            │   │
                         ▼            │   │
                 ┌─────────────────┐ │   │
                 │     VISITS       │ │   │
                 │  (Identified)    │ │   │
                 ├─────────────────┤ │   │
                 │ _id             │─┼───┼──┐
                 │ interactionId   │ │   │  │
                 │ customerId*     │─┼───┼──┼──┐
                 │ sessionId       │─┘   │  │  │
                 │ completedSteps  │     │  │  │ visitIds[]
                 │ placeShortId    │     │  │  │
                 └─────────────────┘     │  │  │
                                         │  │  │
                    customerId (ObjectId)│  │  │
                                         │  │  │
                                         ▼  ▼  ▼
                              ┌─────────────────┐
                              │    CUSTOMERS    │
                              │   (Loyalty)     │
                              ├─────────────────┤
                              │ _id* (ObjectId) │◄─── Referenced by customerId
                              │ shortId         │     (e.g., "cust_abc7890")
                              │ email (unique)  │
                              │ totalVisits     │
                              │ places[]        │
                              │   - visitIds[]  │
                              │   - visitCount  │
                              └─────────────────┘

* customerId fields store Customer._id (MongoDB ObjectId), NOT shortId string
* When Visit created: ALL interactions with same sessionId get customerId set

SessionId Attribution Flow

CRITICAL CONCEPT: All interactions in the same session get attributed to the customer retroactively.

How It Works

Anonymous Interactions Created
- User scans QR code → Interaction #1 created with sessionId: "sess_abc123"
- User clicks button → Interaction #2 created with same sessionId: "sess_abc123"
- User views content → Interaction #3 created with same sessionId: "sess_abc123"
- All interactions have sessionId but NO customerId yet
User Submits Identifier (Email/Phone)
- Visit is created
- Customer is found/created with _id: ObjectId("67234xyz...")
- Visit gets customerId: customer._id

ALL Interactions Get Attributed

// This happens in pasos-submit/route.ts lines 617-622
await interactionsCollection.updateMany(
  { sessionId: sessionId },                    // Find ALL interactions with this sessionId
  { $set: { customerId: customer._id } }       // Set customerId on ALL of them
);

Result
- Interaction #1: Now has customerId: ObjectId("67234xyz...")
- Interaction #2: Now has customerId: ObjectId("67234xyz...")
- Interaction #3: Now has customerId: ObjectId("67234xyz...")
- The entire customer journey is now attributed!

Key Points

✅ Every interaction MUST have a sessionId (no exceptions)
✅ Interactions start anonymous (no customerId)
✅ One Visit is created when user provides email/phone
✅ ALL interactions in that session get the customer's ObjectId added
✅ This links the entire journey from first scan to form submission

Code Reference

Attribution Logic: /src/app/api/pasos-submit/route.ts (lines 615-629)
Database Operation: interactionsCollection.updateMany({ sessionId }, { $set: { customerId } })

Example

// BEFORE form submission:
// interactions collection
{ _id: 1, sessionId: "sess_abc", type: "qr", customerId: null }
{ _id: 2, sessionId: "sess_abc", type: "direct", customerId: null }
{ _id: 3, sessionId: "sess_abc", type: "direct", customerId: null }

// User submits form → Customer created with _id: ObjectId("999")

// AFTER form submission:
// interactions collection
{ _id: 1, sessionId: "sess_abc", type: "qr", customerId: ObjectId("999") }
{ _id: 2, sessionId: "sess_abc", type: "direct", customerId: ObjectId("999") }
{ _id: 3, sessionId: "sess_abc", type: "direct", customerId: ObjectId("999") }

// visits collection
{ _id: 100, customerId: ObjectId("999"), interactionId: ObjectId(1) }

// customers collection
{ _id: ObjectId("999"), shortId: "cust_abc7890", email: "[email protected]" }

Delete Customer Implications

When deleting a customer, you must delete by ObjectId:

// ✅ CORRECT: Delete all interactions for this customer
await interactionsCollection.deleteMany({
  customerId: customer._id  // Use ObjectId reference
});

// ❌ WRONG: Don't use email/phone string matching
await interactionsCollection.deleteMany({
  customerEmail: customer.email  // Unreliable and slow
});

Complete Data Flow

Step-by-Step Flow

┌──────────────────────────────────────────────────────────────┐
│ STEP 1: User scans QR code                                   │
│ URL: /ig/abc1234?q=1&placement=mesa1                        │
└──────────────────────────────────────────────────────────────┘
                          │
                          ▼
         ┌────────────────────────────────────┐
         │  INTERACTION CREATED               │
         │  (Server-side on page load)        │
         ├────────────────────────────────────┤
         │  Collection: interactions          │
         │  ─────────────────────────────     │
         │  _id: ObjectId("673...")           │
         │  type: "qr"                        │
         │  fork: "instagram"                 │
         │  templateType: "restaurant"        │
         │  placeShortId: "abc1234"           │
         │  placement: '{"type":"mesa"...}'   │
         │  sessionId: "sess_xyz..."          │
         │  timestamp: "2024-10-22T10:30:00Z" │
         └────────────────────────────────────┘
                          │
                          ▼
┌──────────────────────────────────────────────────────────────┐
│ STEP 2: User fills multi-step form                          │
│ - Step 0: Email input (10:30:15)                            │
│ - Step 1: Phone input (10:30:45)                            │
│ - Step 2: Date selection (10:31:08)                         │
│ - Step 3: Party size (10:31:20)                             │
└──────────────────────────────────────────────────────────────┘
                          │
                          ▼
         ┌────────────────────────────────────┐
         │  POST /api/pasos-submit            │
         │  Request body:                     │
         │  {                                 │
         │    place: "abc1234",               │
         │    fork: "instagram",              │
         │    formData: {...},                │
         │    completedSteps: [...],          │
         │    interactionId: "673..."         │
         │  }                                 │
         └────────────────────────────────────┘
                          │
                          ▼
         ┌────────────────────────────────────┐
         │  VISIT CREATED                     │
         │  Collection: visits                │
         ├────────────────────────────────────┤
         │  _id: ObjectId("674...")           │
         │  placeShortId: "abc1234"           │
         │  fork: "instagram"                 │
         │  method: "qr"                      │
         │  customerEmail: "customer@..."     │
         │  formData: {                       │
         │    email: "customer@...",          │
         │    phone: "+51999...",             │
         │    reservation_date: "2024-11-15", │
         │    party_size: "4"                 │
         │  }                                 │
         │  completedSteps: [                 │
         │    {stepId:"email", stepIndex:0,   │
         │     completedAt:"10:30:15"},       │
         │    {stepId:"phone", stepIndex:1,   │
         │     completedAt:"10:30:45"},       │
         │    ...                             │
         │  ]                                 │
         │  interactionId: ObjectId("673...")│
         │  visitedAt: "2024-10-22T10:31:20Z" │
         └────────────────────────────────────┘
                          │
                          ▼
         ┌────────────────────────────────────┐
         │  Check if customer exists          │
         │  Query: { email: "customer@..." }  │
         └────────────────────────────────────┘
                          │
                ┌─────────┴─────────┐
                │                   │
         New Customer        Existing Customer
                │                   │
                ▼                   ▼
    ┌───────────────────┐   ┌──────────────────┐
    │  CREATE CUSTOMER  │   │  UPDATE CUSTOMER │
    │  Collection:      │   │  Collection:     │
    │  customers        │   │  customers       │
    ├───────────────────┤   ├──────────────────┤
    │  customerId:      │   │  $inc:           │
    │  "cust_abc..."    │   │   totalVisits: 1 │
    │  email: "..."     │   │                  │
    │  totalVisits: 1   │   │  $set:           │
    │  places: [{       │   │   lastVisitAt    │
    │    shortId,       │   │                  │
    │    visitCount: 1, │   │  $push or $inc:  │
    │    visitIds: [...] │   │   places[].      │
    │  }]               │   │   visitIds       │
    └───────────────────┘   └──────────────────┘
                │                   │
                └─────────┬─────────┘
                          ▼
         ┌────────────────────────────────────┐
         │  UPDATE VISIT WITH CUSTOMER ID     │
         │  Collection: visits                │
         ├────────────────────────────────────┤
         │  $set: {                           │
         │    customerId: customer._id        │
         │  }                                 │
         └────────────────────────────────────┘
                          │
                          ▼
         ┌────────────────────────────────────┐
         │  UPDATE ALL INTERACTIONS BY        │
         │  SESSIONID (Retroactive)           │
         │  Collection: interactions          │
         ├────────────────────────────────────┤
         │  updateMany(                       │
         │    { sessionId: "sess_xyz..." },   │
         │    { $set: {                       │
         │        customerId: customer._id    │
         │      }                             │
         │    }                               │
         │  )                                 │
         │                                    │
         │  ✅ ALL interactions in this       │
         │     session now attributed to      │
         │     customer!                      │
         └────────────────────────────────────┘
                          │
                          ▼
         ┌────────────────────────────────────┐
         │  INCREMENT PLACE COUNTS            │
         │  Collection: places                │
         ├────────────────────────────────────┤
         │  Update place.forks.instagram:     │
         │    count_emails++                  │
         │    count_visits++                  │
         └────────────────────────────────────┘
                          │
                          ▼
                   ✅ COMPLETE

When Each Collection is Created/Updated

INTERACTIONS

Created:

Server-side on EVERY page load
Instagram: /src/app/ig/[identifier]/[[...tracking]]/page.tsx (lines 79-111)
Google: /src/app/gr/[identifier]/[[...tracking]]/page.tsx (lines 95-127)
Database: database.saveInteraction() (lines 785-855)
Condition: Always recorded (even direct visits without tracking params)
Features:
- Automatic device detection from userAgent
- Duplicate prevention via sessionId checking
- trackingSource determination (path/query/untracked)
- Placement extraction from URL
Skip: Development environment only (when NODE_ENV !== 'production')

Updated:

When user submits email/phone in form (creates Visit)
Database: /src/app/api/pasos-submit/route.ts (lines 615-629)
Method: interactionsCollection.updateMany({ sessionId }, { $set: { customerId: customer._id } })
Fields added: customerId (ObjectId reference to customer._id)
CRITICAL: ALL interactions with the same sessionId get updated (not just one)
Note: This retroactively attributes the entire customer journey

VISITS

Created (3 paths):

Multi-step form submission (Primary)
- File: /src/app/api/pasos-submit/route.ts (lines 312-327)
- Method: visitsCollection.insertOne()
- Data: completedSteps (with stepFormId, stepResponse), interactionId, placeId
- Validation: Validates each step against form template (lines 86-135)
- Email/Phone: Automatically extracted from completedSteps
Simple email submission
- File: /src/app/api/submit-email/route.ts
- Method: database.recordVisit()
- Data: email, fork, method
- Legacy: Older submission method
Customer visit helper
- File: /src/lib/utils/customerHelpers.ts (lines 82-100+)
- Method: database.recordVisit()
- Database: connection.ts (lines 749-765)
- Called: After customer authentication flows

Updated:

Progressive form submission (user completes additional steps)
File: /src/app/api/pasos-submit/route.ts
Fields updated: completedSteps (appended), email/phone (extracted), userLocation
Note: formData field is DEPRECATED, use completedSteps instead

CUSTOMERS

Created:

First time an email/phone is submitted via pasos form
File: /src/app/api/pasos-submit/route.ts (lines 334-502)
Method: customersCollection.findOneAndUpdate() with upsert: true
Data: customerId (generated via ShortIdGenerator), email (lowercase), phone, places array
CustomerId Format: cust_{7-char-shortId} (line 342)
Unique Index: Email field prevents duplicates

Three-Step Customer Merge Strategy (lines 353-431):

Email-Based Upsert (lines 353-371)

// Try to find/create by email (has unique index)
findOneAndUpdate({ email }, { ... }, { upsert: true })

Phone-Only Fallback (lines 372-390)

// If no email, try phone-only lookup
if (!email && phone) {
  findOne({ phone, email: { $exists: false } })
}

Duplicate Detection & Merge (lines 392-431)

// If customer provided both email+phone, detect duplicates
// Example: Visit 1 (phone only) → customer A
//          Visit 2 (same phone + email) → customer B
// System merges B into A, deletes B

Updated (every subsequent visit):

New place visit (lines 485-501):

$push: {
  places: {
    _id, shortId, name,
    visitCount: 1,
    firstVisitAt, lastVisitAt,
    visitIds: [visitId]
  }
}
$inc: { totalVisits: 1 }
$set: { lastVisitAt: new Date() }

Return visit to same place (lines 457-478):

$inc: { totalVisits: 1, "places.$.visitCount": 1 }
$set: { lastVisitAt: new Date(), "places.$.lastVisitAt": new Date() }
$addToSet: { "places.$.visitIds": visitId }  // Prevents duplicate visit IDs

Query Examples

Get Visit with Interaction and Customer Data

const visit = await db.collection('visits').aggregate([
  { $match: { _id: ObjectId("visitId") } },

  // Lookup interaction
  {
    $lookup: {
      from: 'interactions',
      localField: 'interactionId',
      foreignField: '_id',
      as: 'interaction'
    }
  },
  { $unwind: { path: '$interaction', preserveNullAndEmptyArrays: true } },

  // Lookup customer
  {
    $lookup: {
      from: 'customers',
      localField: 'customerId',
      foreignField: 'customerId',
      as: 'customer'
    }
  },
  { $unwind: { path: '$customer', preserveNullAndEmptyArrays: true } },

  // Add template type from interaction
  {
    $addFields: {
      templateType: '$interaction.templateType'
    }
  }
]).toArray();

Get All Visits for a Customer

const customer = await db.collection('customers').findOne({
  email: '[email protected]'
});

const visits = await db.collection('visits').find({
  customerId: customer.customerId
}).sort({ visitedAt: -1 }).toArray();

Get Customer Visit History with Details

const customerHistory = await db.collection('customers').aggregate([
  { $match: { email: '[email protected]' } },
  { $unwind: '$places' },

  // Lookup all visits for this place
  {
    $lookup: {
      from: 'visits',
      let: { visitIds: '$places.visitIds' },
      pipeline: [
        { $match: { $expr: { $in: ['$_id', '$$visitIds'] } } },
        { $sort: { visitedAt: -1 } }
      ],
      as: 'placeVisits'
    }
  },

  // Group back
  {
    $group: {
      _id: '$_id',
      customerId: { $first: '$customerId' },
      email: { $first: '$email' },
      totalVisits: { $first: '$totalVisits' },
      places: {
        $push: {
          shortId: '$places.shortId',
          name: '$places.name',
          visitCount: '$places.visitCount',
          visits: '$placeVisits'
        }
      }
    }
  }
]).toArray();

Get Interaction Conversion Rate

const stats = await db.collection('interactions').aggregate([
  {
    $facet: {
      total: [{ $count: 'count' }],
      converted: [
        { $match: { customerEmail: { $exists: true } } },
        { $count: 'count' }
      ]
    }
  },
  {
    $project: {
      totalInteractions: { $arrayElemAt: ['$total.count', 0] },
      convertedInteractions: { $arrayElemAt: ['$converted.count', 0] },
      conversionRate: {
        $multiply: [
          { $divide: [
            { $arrayElemAt: ['$converted.count', 0] },
            { $arrayElemAt: ['$total.count', 0] }
          ]},
          100
        ]
      }
    }
  }
]).toArray();

// Result: { totalInteractions: 1500, convertedInteractions: 890, conversionRate: 59.33 }

Analyze Step Completion Times

const stepTimings = await db.collection('visits').aggregate([
  { $match: { 'completedSteps.0': { $exists: true } } },
  { $unwind: { path: '$completedSteps', includeArrayIndex: 'idx' } },
  {
    $group: {
      _id: {
        visitId: '$_id',
        stepIndex: '$completedSteps.stepIndex'
      },
      stepId: { $first: '$completedSteps.stepId' },
      completedAt: { $first: '$completedSteps.completedAt' }
    }
  },
  { $sort: { '_id.visitId': 1, '_id.stepIndex': 1 } },
  {
    $group: {
      _id: '$_id.visitId',
      steps: {
        $push: {
          stepId: '$stepId',
          stepIndex: '$_id.stepIndex',
          completedAt: '$completedAt'
        }
      }
    }
  },
  {
    $project: {
      timeBetweenSteps: {
        $map: {
          input: { $range: [1, { $size: '$steps' }] },
          as: 'i',
          in: {
            fromStep: { $arrayElemAt: ['$steps.stepId', { $subtract: ['$$i', 1] }] },
            toStep: { $arrayElemAt: ['$steps.stepId', '$$i'] },
            durationMs: {
              $subtract: [
                { $toLong: { $toDate: { $arrayElemAt: ['$steps.completedAt', '$$i'] } } },
                { $toLong: { $toDate: { $arrayElemAt: ['$steps.completedAt', { $subtract: ['$$i', 1] }] } } }
              ]
            }
          }
        }
      }
    }
  },
  { $unwind: '$timeBetweenSteps' },
  {
    $group: {
      _id: {
        from: '$timeBetweenSteps.fromStep',
        to: '$timeBetweenSteps.toStep'
      },
      avgDurationMs: { $avg: '$timeBetweenSteps.durationMs' },
      minDurationMs: { $min: '$timeBetweenSteps.durationMs' },
      maxDurationMs: { $max: '$timeBetweenSteps.durationMs' },
      count: { $sum: 1 }
    }
  },
  {
    $project: {
      fromStep: '$_id.from',
      toStep: '$_id.to',
      avgDurationSeconds: { $divide: ['$avgDurationMs', 1000] },
      minDurationSeconds: { $divide: ['$minDurationMs', 1000] },
      maxDurationSeconds: { $divide: ['$maxDurationMs', 1000] },
      count: 1
    }
  },
  { $sort: { avgDurationSeconds: -1 } }
]).toArray();

// Result:
// [
//   { fromStep: "email", toStep: "phone", avgDurationSeconds: 28.5, count: 450 },
//   { fromStep: "phone", toStep: "date", avgDurationSeconds: 22.3, count: 380 },
//   ...
// ]

Data Transformations

Email Normalization

All email fields are converted to lowercase before storage:

email.toLowerCase()
customerEmail.toLowerCase().trim()

IP Address Extraction

From request headers in order of preference:

ipAddress = headers.get('x-forwarded-for') ||
            headers.get('x-real-ip') ||
            'unknown'

Device Type Detection

if (ua.includes('mobile') || ua.includes('iphone') || ua.includes('android')) {
  return 'mobile';
} else if (ua.includes('tablet') || ua.includes('ipad')) {
  return 'tablet';
} else {
  return 'desktop';
}

Customer ID Generation

// Format: 'cust_' + 7-character short ID
// Generated using ShortIdGenerator utility
customerId = 'cust_' + generateShortId(7);
// Example: 'cust_aB3xYz9'

// Implementation: /src/lib/utils/customerHelpers.ts (lines 68-75)
// Characters: Alphanumeric (a-z, A-Z, 0-9)
// Length: Always 7 characters + 'cust_' prefix = 12 total

Key Design Decisions

1. Why Three Separate Collections?

Interactions: Track ALL engagement (even anonymous)
Visits: Track identified users with complete data
Customers: Track loyalty across multiple places

2. Why Store templateType in Interactions?

Template is determined at moment of QR scan (interaction)
Historical accuracy if place template changes later
Visits reference interaction for template information

3. Why Track completedSteps with Timestamps?

Analyze user behavior (how long on each step)
Identify friction points in forms
A/B test different form flows
Segment fast vs. slow completers

4. Why Lazy Customer Creation?

Don't create customer until we have identifying info (email)
Keeps customer collection clean
Reduces database size

5. Why Store visitIds Array in Customer?

Quick lookup of all visits for a customer+place
Enable detailed visit history analysis
Support "your last order was..." features
Uses $addToSet operator to prevent duplicate visit IDs

6. Why Three-Step Customer Merge Logic?

Handles customers who provide phone first, email later
Prevents duplicate customer records
Merges historical data when both identifiers provided
Maintains data integrity with unique email index

Files & Scripts Reference

Page Routes (Interaction Creation)

Instagram Routes:

/src/app/ig/[identifier]/[[...tracking]]/page.tsx (lines 79-111)
- Creates interaction on every page load
- Calls database.saveInteraction()
- Sets fork: 'instagram'

Google Routes:

/src/app/gr/[identifier]/[[...tracking]]/page.tsx (lines 95-127)
- Creates interaction on every page load
- Calls database.saveInteraction()
- Sets fork: 'google'

API Endpoints

Form Submission:

/src/app/api/pasos-submit/route.ts
- Lines 86-135: Form validation against template
- Lines 312-327: Visit creation
- Lines 334-502: Customer creation/update with merge logic
- Creates visits with completedSteps array
- Handles customer deduplication

Simple Email:

/src/app/api/submit-email/route.ts
- Legacy simple email submission
- Calls database.recordVisit()

Database Helpers (/src/lib/database/connection.ts)

Interaction Methods:

saveInteraction() (lines 785-855) - Creates interaction with duplicate prevention
updateInteractionWithEmail() - Updates interaction when email submitted

Visit Methods:

recordVisit() (lines 749-765) - Simple visit insertion
Direct visitsCollection.insertOne() in pasos-submit

Customer Methods:

getCustomerByEmail() (lines 701-712) - Finds customer by email
getCustomerById() (lines 714-725) - Finds customer by customerId
createCustomer() (lines 727-746) - Creates new customer record

Utility Functions

Route Helpers (/src/lib/utils/routeHelpers.ts):

normalizeTracking() (lines 26-83) - Normalizes tracking data from URL
- Determines trackingSource (path/query/untracked)
- Extracts placement from URL
- Returns method type (qr/nfc/direct)
generateTrackingUrls() (lines 123-146) - Generates QR URLs

Customer Helpers (/src/lib/utils/customerHelpers.ts):

generateShortId() (lines 68-75) - Generates 7-character alphanumeric IDs
recordCustomerVisit() (lines 82-100+) - Records visit for customer

Device Detection:

Automatic detection in saveInteraction() from userAgent string
Returns: 'mobile', 'tablet', or 'desktop'

TypeScript Interfaces

Core Types (/src/lib/database/connection.ts):

Interaction (lines 168-184) - Interaction document structure
Visit (lines 87-127) - Visit document with completedSteps
Customer (lines 129-139) - Customer with places array
CustomerPlace (lines 141-149) - Place visit tracking

Request Types:

PasosSubmissionRequest (pasos-submit, lines 20-25) - Form submission request
- Includes completedSteps with stepFormId, stepResponse
- Includes interactionId for linking

Database Collections

forkast database:

interactions - All page visits (anonymous + identified)
- Indexes: timestamp, placeShortId, sessionId, customerEmail
visits - Form submissions with completedSteps
- Indexes: placeId, customerId, visitedAt, interactionId
customers - Customer records with places array
- Unique index: email
- Indexes: customerId, phone, lastVisitAt
places - Place/business information
- Used for lookups during visit creation

Key Implementation Files

Validation:

/src/lib/types/formTemplates.ts - Form validation logic
- validateStepInput() - Validates individual step responses
- Input validators for email, phone, etc.

Headers & IP Extraction:

IP Address: x-forwarded-for → x-real-ip → 'unknown'
User Agent: Direct from request headers
Device Type: Derived from userAgent

Migration Notes

Deprecated Fields (Still Present for Compatibility):

formData → Replaced by completedSteps in visits
customerEmail → Use email instead in visits
placeName → Use placeId lookups instead
place_slug, place_shortid, place_id → Legacy fields
ip → Use ipAddress instead

Current Field Naming:

Use completedSteps with stepFormId and stepResponse
Use customerId format: cust_{7-char-shortId}
Use interactionId to link visits to interactions
Use trackingSource to track URL format quality

Summary

This three-collection architecture provides:

✅ Complete funnel tracking: Anonymous → Identified → Returning ✅ Performance: Optimized queries with proper indexing ✅ Analytics: Rich data for user behavior analysis ✅ Flexibility: completedSteps supports any form template ✅ Scalability: Separate concerns, efficient updates ✅ Data Quality: trackingSource field for attribution analysis ✅ Deduplication: Three-step customer merge prevents duplicates ✅ Session Management: Prevents double-counting via sessionId

The system captures the complete user journey from first scan to loyal repeat customer with full attribution tracking and data quality indicators.

jmsaavedra/INTERACTIONS-VISITS-CUSTOMERS.md

Interactions, Visits, and Customers

Complete Data Model Documentation

Overview

⚠️ CRITICAL: Interaction Recording Rules

Collection Schemas

1. INTERACTIONS Collection

2. VISITS Collection

3. CUSTOMERS Collection

Collection Relationships

SessionId Attribution Flow

How It Works

Key Points

Code Reference

Example

Delete Customer Implications

Complete Data Flow

Step-by-Step Flow

When Each Collection is Created/Updated

INTERACTIONS

VISITS

CUSTOMERS

Query Examples

Get Visit with Interaction and Customer Data

Get All Visits for a Customer

Get Customer Visit History with Details

Get Interaction Conversion Rate

Analyze Step Completion Times

Data Transformations

Email Normalization

IP Address Extraction

Device Type Detection

Customer ID Generation

Key Design Decisions

1. Why Three Separate Collections?

2. Why Store templateType in Interactions?

3. Why Track completedSteps with Timestamps?

4. Why Lazy Customer Creation?

5. Why Store visitIds Array in Customer?

6. Why Three-Step Customer Merge Logic?

Files & Scripts Reference

Page Routes (Interaction Creation)

API Endpoints

Database Helpers (/src/lib/database/connection.ts)

Utility Functions

TypeScript Interfaces

Database Collections

Key Implementation Files

Migration Notes

Summary