MEAL: Multi-User Engagement Asynchronous Ledger

{
  "meal_id": "01HQZK8FXZC9YT8KJN6M7P2Q5R",
  "meal_type": "field_discussion",
  "created_at_time": "2025-10-31T20:45:01Z",
  "created_at_location": "38.2492° N, 122.0405° W",
  
  "primary_time_index": "2025-10-31T20:45:01Z",
  "last_updated_time": "2025-11-01T14:10:22Z",
  
  "primary_location_index": {
    "geoid": "a4fd692c2578b270a937ce77869361e3cd22cd0b021c6ad23c995868bd11651e",
    "label": "Field A - North Block",
    "coordinates": [38.5816, -121.4944]
  },
  
  "location_context": [
    {
      "geoid": "field-A",
      "type": "field",
      "label": "Primary Field"
    },
    {
      "geoid": "farm-B",
      "type": "farm",
      "label": "Smith Family Farm"
    },
    {
      "geoid": "county-C",
      "type": "administrative",
      "label": "Yolo County, CA"
    }
  ],
  
  "participant_agents": [
    {
      "agent_id": "user-A45B",
      "agent_type": "human",
      "name": "John Smith (Farm Manager)",
      "joined_at": "2025-10-31T20:45:01Z"
    },
    {
      "agent_id": "user-C992",
      "agent_type": "human",
      "name": "Dr. Sarah Chen (Agronomist)",
      "joined_at": "2025-10-31T21:02:15Z"
    },
    {
      "agent_id": "agent-PAN-007",
      "agent_type": "ai",
      "name": "PANCAKE AI Assistant",
      "joined_at": "2025-10-31T20:45:01Z"
    }
  ],
  
  "packet_sequence": {
    "first_packet_id": "01HQZK8FXZC9YT8KJN6M7P2Q5S",
    "last_packet_id": "01HQZM3DNWR8FV4BH2K9N6P7Q8",
    "packet_count": 42,
    "sip_count": 35,
    "bite_count": 7
  },
  
  "cryptographic_chain": {
    "root_hash": "0x8B7A...F9E2",
    "last_packet_hash": "0x3C2D...A9F1",
    "hash_algorithm": "SHA-256",
    "chain_verifiable": true
  },
  
  "topics": [
    "irrigation_scheduling",
    "pest_management",
    "yield_prediction"
  ],
  
  "related_sirup": [
    {
      "sirup_type": "weather_forecast",
      "geoid": "field-A",
      "time_range": ["2025-10-31", "2025-11-07"]
    },
    {
      "sirup_type": "satellite_imagery",
      "geoid": "field-A",
      "dates": ["2025-10-28", "2025-11-02"]
    }
  ],
  
  "meal_status": "active",
  "archived": false,
  "retention_policy": "indefinite"
}

{
  "packet_id": "01HQZK8FXZC9YT8KJN6M7P2Q5S",
  "packet_type": "sip",
  "meal_id": "01HQZK8FXZC9YT8KJN6M7P2Q5R",
  
  "sequence": {
    "number": 1,
    "previous_packet_id": null,
    "previous_packet_hash": null
  },
  
  "time_index": "2025-10-31T20:45:01Z",
  
  "location_index": {
    "geoid": "office-123",
    "type": "point",
    "coordinates": [38.2492, -122.0405],
    "label": "Farm Office"
  },
  
  "author": {
    "agent_id": "user-A45B",
    "agent_type": "human",
    "name": "John Smith"
  },
  
  "content": {
    "text": "Just checked the weather forecast. Looks like rain coming this weekend. Should we adjust irrigation schedule?",
    "mentions": ["user-C992", "agent-PAN-007"],
    "references": []
  },
  
  "cryptographic": {
    "content_hash": "0x1A2B...3C4D",
    "packet_hash": "0x8B7A...F9E2",
    "signature": "0xABCD...EF01"
  }
}

{
  "packet_id": "01HQZK9GYWC9YT8KJN6M7P2Q5T",
  "packet_type": "bite",
  "meal_id": "01HQZK8FXZC9YT8KJN6M7P2Q5R",
  
  "sequence": {
    "number": 5,
    "previous_packet_id": "01HQZK8MXZC9YT8KJN6M7P2Q5U",
    "previous_packet_hash": "0x2B3C...4D5E"
  },
  
  "time_index": "2025-10-31T22:15:30Z",
  
  "location_index": {
    "geoid": "field-A",
    "type": "polygon",
    "coordinates": [[38.5816, -121.4944], ...],
    "label": "Field A - North Block"
  },
  
  "author": {
    "agent_id": "user-A45B",
    "agent_type": "human",
    "name": "John Smith"
  },
  
  "bite": {
    "Header": {
      "id": "01HQZK9GYWC9YT8KJN6M7P2Q5T",
      "geoid": "field-A",
      "timestamp": "2025-10-31T22:15:30Z",
      "type": "observation",
      "source": {
        "app": "TerraTrac Mobile",
        "user": "user-A45B"
      }
    },
    "Body": {
      "observation_type": "pest_scouting",
      "pest_species": "aphids",
      "severity": "moderate",
      "affected_area_pct": 15,
      "photo_url": "https://storage.pancake.io/photos/abc123.jpg",
      "notes": "Found in northwest corner, about 15% of plants affected"
    },
    "Footer": {
      "hash": "0x9C8D...7E6F",
      "schema_version": "1.0",
      "tags": ["pest", "aphids", "observation", "field-A"]
    }
  },
  
  "cryptographic": {
    "bite_hash": "0x9C8D...7E6F",
    "packet_hash": "0x3D4E...5F60",
    "signature": "0xDEF0...1234"
  },
  
  "context": {
    "in_response_to": "01HQZK8FXZC9YT8KJN6M7P2Q5S",
    "mentions": ["user-C992"],
    "caption": "Photo attached from field. Aphids confirmed in NW corner."
  }
}

-- Find all MEALs for Field A
SELECT * FROM meals
WHERE primary_location_index->>'geoid' = 'field-A';

-- Find recent MEALs
SELECT * FROM meals
WHERE last_updated_time >= NOW() - INTERVAL '7 days';

-- Find MEALs with AI participation
SELECT * FROM meals
WHERE participant_agents @> '[{"agent_type": "ai"}]';

-- Find all packets posted from Field B (even in Field A's MEAL)
SELECT * FROM meal_packets
WHERE location_index->>'geoid' = 'field-B';

-- Track user movement through conversation
SELECT 
    packet_id,
    time_index,
    location_index->>'label' as location,
    content->>'text' as message
FROM meal_packets
WHERE meal_id = 'meal-123'
AND author->>'agent_id' = 'user-A45B'
ORDER BY sequence_number;

SELECT DISTINCT m.meal_id, m.primary_location_index
FROM meals m
JOIN meal_packets mp ON m.meal_id = mp.meal_id
WHERE m.primary_location_index->>'geoid' = 'field-A'  -- MEAL about Field A
AND mp.location_index->>'geoid' = 'field-A'           -- Posted FROM Field A
AND mp.time_index >= NOW() - INTERVAL '30 days';

-- MEAL Root Table
CREATE TABLE meals (
    meal_id VARCHAR(26) PRIMARY KEY,  -- ULID
    meal_type VARCHAR(50),
    
    -- Temporal indexing (MANDATORY)
    created_at_time TIMESTAMP WITH TIME ZONE NOT NULL,
    last_updated_time TIMESTAMP WITH TIME ZONE NOT NULL,
    primary_time_index TIMESTAMP WITH TIME ZONE NOT NULL,
    
    -- Spatial indexing (OPTIONAL but recommended)
    primary_location_index JSONB,  -- {geoid, label, coordinates, type}
    location_context JSONB[],       -- Array of related geoids
    
    -- Participants
    participant_agents JSONB NOT NULL,  -- Array of agent objects
    
    -- Packet tracking
    packet_count INTEGER DEFAULT 0,
    sip_count INTEGER DEFAULT 0,
    bite_count INTEGER DEFAULT 0,
    first_packet_id VARCHAR(26),
    last_packet_id VARCHAR(26),
    
    -- Cryptographic verification
    root_hash VARCHAR(66),
    last_packet_hash VARCHAR(66),
    hash_algorithm VARCHAR(20) DEFAULT 'SHA-256',
    chain_verifiable BOOLEAN DEFAULT true,
    
    -- Metadata
    topics TEXT[],
    related_sirup JSONB[],
    meal_status VARCHAR(20) DEFAULT 'active',
    archived BOOLEAN DEFAULT false,
    retention_policy VARCHAR(50) DEFAULT 'indefinite',
    
    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes for fast retrieval
CREATE INDEX idx_meals_time ON meals(primary_time_index);
CREATE INDEX idx_meals_location ON meals USING GIN(primary_location_index);
CREATE INDEX idx_meals_updated ON meals(last_updated_time);
CREATE INDEX idx_meals_participants ON meals USING GIN(participant_agents);
CREATE INDEX idx_meals_topics ON meals USING GIN(topics);


-- MEAL Packets Table (The String)
CREATE TABLE meal_packets (
    packet_id VARCHAR(26) PRIMARY KEY,  -- ULID
    meal_id VARCHAR(26) NOT NULL REFERENCES meals(meal_id),
    packet_type VARCHAR(10) NOT NULL,  -- 'sip' or 'bite'
    
    -- Sequence (for hash chain)
    sequence_number INTEGER NOT NULL,
    previous_packet_id VARCHAR(26),
    previous_packet_hash VARCHAR(66),
    
    -- Temporal indexing (MANDATORY)
    time_index TIMESTAMP WITH TIME ZONE NOT NULL,
    
    -- Spatial indexing (OPTIONAL, overrides MEAL default)
    location_index JSONB,  -- {geoid, type, coordinates, label}
    
    -- Author
    author JSONB NOT NULL,  -- {agent_id, agent_type, name}
    
    -- Content (either SIP or BITE)
    sip_data JSONB,   -- For SIP packets
    bite_data JSONB,  -- For BITE packets
    
    -- Context
    context JSONB,  -- {in_response_to, mentions, caption, references}
    
    -- Cryptographic
    content_hash VARCHAR(66),
    packet_hash VARCHAR(66),
    signature VARCHAR(132),
    
    -- Link to standalone packet (if exists)
    sip_id VARCHAR(26),  -- References sips(id)
    bite_id VARCHAR(26), -- References bites(id)
    
    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    UNIQUE(meal_id, sequence_number)
);

-- Indexes
CREATE INDEX idx_meal_packets_meal ON meal_packets(meal_id, sequence_number);
CREATE INDEX idx_meal_packets_time ON meal_packets(time_index);
CREATE INDEX idx_meal_packets_location ON meal_packets USING GIN(location_index);
CREATE INDEX idx_meal_packets_author ON meal_packets USING GIN(author);
CREATE INDEX idx_meal_packets_sip ON meal_packets(sip_id) WHERE sip_id IS NOT NULL;
CREATE INDEX idx_meal_packets_bite ON meal_packets(bite_id) WHERE bite_id IS NOT NULL;

def find_meals_by_location_and_time(geoid: str, days_back: int = 7):
    """Find MEALs by location and time"""
    cutoff = datetime.utcnow() - timedelta(days=days_back)
    
    query = """
        SELECT meal_id, primary_location_index, last_updated_time, packet_count
        FROM meals
        WHERE primary_location_index->>'geoid' = %s
        AND last_updated_time >= %s
        ORDER BY last_updated_time DESC
    """
    
    return execute_query(query, (geoid, cutoff))

def correlate_meal_with_sirup(meal_id: str):
    """Correlate MEAL conversation with SIRUP data"""
    
    # Get MEAL context
    meal = get_meal(meal_id)
    geoid = meal['primary_location_index']['geoid']
    start_time = meal['created_at_time']
    end_time = meal['last_updated_time']
    
    # Get MEAL packets
    packets = get_meal_packets(meal_id)
    
    # Get SIRUP data for same location and time
    sirup_data = query_sirup(
        geoid=geoid,
        start_time=start_time,
        end_time=end_time,
        sirup_types=['weather_forecast', 'satellite_imagery', 'soil_moisture']
    )
    
    # Build correlated timeline
    timeline = []
    for packet in packets:
        timeline.append({
            'type': 'chat',
            'time': packet['time_index'],
            'content': packet['content'],
            'author': packet['author']['name']
        })
    
    for sirup in sirup_data:
        timeline.append({
            'type': 'sirup',
            'time': sirup['timestamp'],
            'sirup_type': sirup['sirup_type'],
            'data': sirup['summary']
        })
    
    # Sort by time
    timeline.sort(key=lambda x: x['time'])
    
    return timeline

def filter_meal_by_location_and_type(meal_id: str, location_geoid: str, packet_type: str = 'bite'):
    """Filter MEAL packets by location and type"""
    
    query = """
        SELECT packet_id, time_index, location_index, bite_data
        FROM meal_packets
        WHERE meal_id = %s
        AND packet_type = %s
        AND location_index->>'geoid' = %s
        ORDER BY sequence_number
    """
    
    return execute_query(query, (meal_id, packet_type, location_geoid))

def find_ai_assisted_meals(agent_id: str = 'agent-PAN-007', days_back: int = 30):
    """Find MEALs with AI agent participation"""
    
    cutoff = datetime.utcnow() - timedelta(days=days_back)
    
    query = """
        SELECT DISTINCT m.meal_id, m.primary_location_index, m.created_at_time,
               COUNT(mp.packet_id) as ai_packet_count
        FROM meals m
        JOIN meal_packets mp ON m.meal_id = mp.meal_id
        WHERE m.participant_agents @> %s::jsonb
        AND mp.author->>'agent_id' = %s
        AND m.last_updated_time >= %s
        GROUP BY m.meal_id, m.primary_location_index, m.created_at_time
        ORDER BY m.last_updated_time DESC
    """
    
    agent_filter = json.dumps([{"agent_id": agent_id}])
    return execute_query(query, (agent_filter, agent_id, cutoff))

def search_meals_with_sirup_correlation(search_term: str, sirup_condition: dict):
    """
    Search MEALs with text matching + SIRUP correlation
    
    Example: Find "crop failure" mentions during drought events
    """
    
    # Find MEALs with search term
    text_query = """
        SELECT DISTINCT mp.meal_id, mp.packet_id, mp.time_index, mp.location_index,
               mp.sip_data->>'text' as text
        FROM meal_packets mp
        WHERE mp.packet_type = 'sip'
        AND mp.sip_data->>'text' ILIKE %s
    """
    
    matching_packets = execute_query(text_query, (f'%{search_term}%',))
    
    # For each match, check if SIRUP condition was true at that time/location
    correlated_results = []
    for packet in matching_packets:
        geoid = packet['location_index']['geoid']
        time = packet['time_index']
        
        # Check SIRUP data
        sirup_match = check_sirup_condition(
            geoid=geoid,
            time=time,
            condition=sirup_condition  # e.g., {"type": "weather", "drought": True}
        )
        
        if sirup_match:
            correlated_results.append({
                'meal_id': packet['meal_id'],
                'packet_id': packet['packet_id'],
                'text': packet['text'],
                'time': time,
                'location': geoid,
                'sirup_event': sirup_match
            })
    
    return correlated_results

# Create MEAL for field visit
meal = MEAL.create(
    meal_type="field_visit",
    primary_location={"geoid": "field-A", "label": "North Block"},
    participants=["user-john-smith", "agent-PAN-007"]
)

# Add text note (SIP)
meal.add_packet(
    packet_type="sip",
    author="user-john-smith",
    location={"geoid": "field-A", "coordinates": [38.58, -121.49]},
    content={"text": "Starting field inspection. Weather looks good."}
)

# Add photo observation (BITE)
meal.add_packet(
    packet_type="bite",
    author="user-john-smith",
    location={"geoid": "field-A-section-3"},
    bite=observation_bite  # BITE with photo, pest count, etc.
)

# AI agent responds
meal.add_packet(
    packet_type="sip",
    author="agent-PAN-007",
    content={"text": "Based on your observation, I recommend..."}
)

# Create MEAL
meal = MEAL.create(
    meal_type="pest_management",
    primary_location={"geoid": "field-B"},
    participants=["user-manager", "user-agronomist", "agent-PAN"]
)

# Timeline:
# 10:00 - Manager posts photo of aphids (BITE)
# 10:15 - Agronomist comments (SIP)
# 10:20 - AI pulls weather data (BITE reference to SIRUP)
# 10:25 - AI recommends spray window (SIP)
# 11:00 - Manager confirms spray schedule (SIP)

-- Find MEAL about Field C around Oct 15
SELECT * FROM meals
WHERE primary_location_index->>'geoid' = 'field-C'
AND primary_time_index BETWEEN '2025-10-14' AND '2025-10-16';

-- Get full conversation
SELECT packet_id, time_index, author->>name, content
FROM meal_packets
WHERE meal_id = 'found-meal-id'
ORDER BY sequence_number;

-- Correlate with SIRUP data
-- Shows: Pest observation + Weather window + AI recommendation = Decision

# Find all MEALs where agronomist participated
expert_meals = find_meals_by_participant("user-expert-agronomist")

# Extract decision patterns
for meal in expert_meals:
    # Get context: field data, weather, observations
    context = get_meal_context(meal['meal_id'])
    
    # Get expert's recommendations
    expert_packets = get_packets_by_author(meal['meal_id'], "user-expert-agronomist")
    
    # Build training example
    training_data.append({
        'input': context,
        'output': expert_packets,
        'outcome': get_field_outcome(meal['primary_location_index'], days_after=30)
    })

def compute_packet_hash(packet: dict, previous_hash: str) -> str:
    """Compute packet hash for chain verification"""
    
    # Canonical representation
    canonical = json.dumps({
        'packet_id': packet['packet_id'],
        'meal_id': packet['meal_id'],
        'sequence_number': packet['sequence_number'],
        'time_index': packet['time_index'],
        'author': packet['author'],
        'content_hash': packet['content_hash'],
        'previous_hash': previous_hash
    }, sort_keys=True)
    
    return hashlib.sha256(canonical.encode()).hexdigest()

def verify_meal_chain(meal_id: str) -> bool:
    """Verify integrity of entire MEAL chain"""
    
    packets = get_meal_packets(meal_id, order_by='sequence_number')
    
    previous_hash = None
    for packet in packets:
        expected_hash = compute_packet_hash(packet, previous_hash)
        
        if packet['packet_hash'] != expected_hash:
            return False
        
        previous_hash = expected_hash
    
    return True

# MEAL-level permissions
meal_permissions = {
    'meal_id': 'meal-123',
    'visibility': 'private',  # private, team, organization, public
    'participants': ['user-A', 'user-B', 'agent-PAN'],
    'viewers': ['user-C'],  # Read-only access
    'admins': ['user-A']   # Can archive, manage participants
}