lunedì 25 maggio 2026

Published maggio 25, 2026 by Django Faiola with 0 comment

iOS Foursquare Swarm - Digging Deeper: New Artifacts Unearthed

Indice dei contenuti

Introduzione

Nel precedente articolo è stata presentata una prima analisi di Swarm per iOS; chi non avesse ancora letto il contenuto può consultare iOS Foursquare Swarm - Check-in App. In occasione dell'adeguamento del codice al nuovo framework iLEAPP è emersa l'opportunità di approfondire l'analisi degli artefatti di Swarm, migliorando i dati esistenti e identificando nuovi artefatti.

Quelli di interesse risiedono principalmente nel database /Library/Caches/foursquare.sqlite situato all'interno dell'App Data Container (ADC):
/private/var/mobile/Containers/Data/Application/<UUID>/.

PINRemoteImage

PINRemoteImage (disponibile su GitHub) è una libreria open source che utilizza PINDiskCache per garantire una persistenza su disco ottimizzata, finalizzata al recupero rapido, al caricamento asincrono e alla gestione efficiente della memoria durante il rendering di immagini scaricate da fonti remote.

All'interno del percorso:
<ADC>/Library/Caches/com.pinterest.PINDiskCache.PINRemoteImageManagerCache/
l’analisi ha evidenziato che la libreria non utilizza un database SQL per l’indicizzazione, ma organizza i dati tramite un file system a oggetti. I file salvati presentano nomi corrispondenti alla URL encoding degli indirizzi sorgente originali.

L’ispezione della directory mostra una grande varietà di contenuti: immagini dei check‑in, sticker, foto profilo degli utenti e altre risorse grafiche. La presenza di formati differenti (come .jpg e .png), spesso replicati in più risoluzioni pre‑calcolate, conferma che l’applicazione sfrutta questa cache per mantenere un set visivo completo e consultabile offline.

Nello specifico, la struttura dei percorsi codificati rivela le seguenti convenzioni:

  • %2Foriginal%2F: rappresenta l’immagine sorgente nel formato originale: /original/.
  • %2FwidthW%2F: contiene la versione dell’immagine con larghezza specifica e aspect ratio invariato: es. /width576/;
  • %2FWH%2F: versione ritagliata e centrata con larghezza e altezza identiche: es. /150/;
  • %2FWxH%2F: come il precedente, ma con larghezza e altezza esplicitamente definite: es. /146x152/.

Esempio di file presente nella cache:
https%3A%2F%2Ffastly%2E4sqi%2Enet%2Fimg%2Fsticker%2F150%2Fpizza_61927f%2Epng
Decodifica:
https://fastly.4sqi.net/img/sticker/150/pizza_61927f.png

📌Nota: Nelle query illustrate a seguire, la ricostruzione dei percorsi delle immagini all'interno della cache viene effettuata solo con il valore original. Tuttavia, all'interno del modulo iLEAPP sono implementate le funzioni _build_photo_map() e _check_in_media_with_suffix(). Queste routine hanno lo scopo di mappare capillarmente tutti i file multimediali presenti e selezionare dinamicamente la risorsa disponibile alla miglior risoluzione (qualità massima).

Account

Per la ricostruzione dell’account, come indicato nel poster, è sufficiente il database SQLite <ADC>/Library/Caches/foursquare.sqlite, e in particolare la tabella ZFSUSER. La query è stata leggermente migliorata includendo anche la biografia dell’utente, il numero di amici e il contatore delle mayorship.

I campi ZPHOTOPREFIX e ZPHOTOSUFFIX  come descritto nella sezione dedicata a PINRemoteImage, consentono inoltre la ricostruzione dell’immagine del profilo se presente nella cache.

SELECT
  U.Z_PK AS "U_PK",
  FU.Z_PK AS "FU_PK",
  datetime(U.ZSWARMCREATEDAT + 978307200, 'unixepoch') AS "created",
  datetime(U.ZJOINEDAT + 978307200, 'unixepoch') AS "joined_at",
  U.ZFIRSTNAME,
  U.ZLASTNAME,
  U.ZBIO,
  U.ZGENDER,
  date(U.ZBIRTHDAY + 978307200, 'unixepoch') AS "birthday",
  U.ZHOMECITY,
  U.ZPHONE,
  U.ZEMAIL,
  FU.ZMONGOID AS "facebook",
  U.ZTWITTER,
  U.ZCANONICALURL,
  IIF(U.ZPHOTOPREFIX IS NOT NULL AND U.ZPHOTOSUFFIX IS NOT NULL, 
    U.ZPHOTOPREFIX || 'original' || U.ZPHOTOSUFFIX, '') AS "orig_photo",
  U.ZCHECKINPINGS,
  coalesce(U.ZFRIENDSCOUNT, 0) AS "friends",
  coalesce(U.ZCHECKINSCOUNT, 0) AS "checkins",
  coalesce(U.ZMAYORSHIPSCOUNT, 0) AS "mayorship",
  U.ZMONGOID AS "uid"
FROM ZFSUSER AS "U"
LEFT JOIN ZFSFACEBOOKUSER AS "FU" ON (U.ZFACEBOOKUSER = FU.Z_PK)
WHERE U.ZRELATIONSHIP = 'self'

Esempio di record (U_PK=3) con i nuovi campi:

  • ZBIO: Consulente Informatico Forense - Digital Forensics Consultant (biografia).
  • ZCHECKINPINGS: off (condivisione del vicinato: nearby=attivo, off=disattivato).
  • ZFRIENDSCOUNT: 1 (numero di amici).
  • ZMAYORSHIPSCOUNT: 0 (numero di mayorship attualmente possedute).

Contatti

I contatti sono presenti nella tabella ZFSUSER del database SQLite <ADC>/Library/Caches/foursquare.sqlite. La query è stata aggiornata per rifinire la classificazione delle relazioni utente e per integrare i contatori di attività e i flag di stato, garantendo una visione più accurata e pulita della cerchia sociale rispetto alle versione precedente.

SELECT
  U.Z_PK AS "U_PK",
  datetime(U.ZLASTMENTIONED + 978307200, 'unixepoch') AS "last_mentioned",
  CASE
    WHEN U.ZUSERTYPE IS NULL THEN 'N/A'
    WHEN U.ZUSERTYPE = '' THEN 'N/D'
    WHEN U.ZUSERTYPE = 'brand' Then 'Brand'
    WHEN U.ZUSERTYPE = 'celebrity' Then 'Celebrity'
    WHEN U.ZUSERTYPE = 'venuePage' Then 'Venue Page'
    WHEN U.ZUSERTYPE = 'page' Then 'Page'
    WHEN U.ZUSERTYPE = 'chain' Then 'Chain'
    ELSE UPPER(SUBSTR(U.ZUSERTYPE, 1, 1)) || SUBSTR(U.ZUSERTYPE, 2)
  END AS "user_type",
  CASE 
    WHEN U.ZRELATIONSHIP IS NULL THEN 'N/A'
    ELSE 
      CASE LOWER(U.ZRELATIONSHIP)
        WHEN 'self' THEN 'Self'
        WHEN 'friend' THEN 'Friend'
        WHEN 'friendship' THEN 'Friend'
        WHEN 'pendingme' THEN 'Incoming Request'
        WHEN 'pendingthem' THEN 'Outgoing Request'
        WHEN 'requested' THEN 'Outgoing Request'
        WHEN 'following' THEN 'Following'
        WHEN 'followingthem' THEN 'Following'
        WHEN 'followedby' THEN 'Follower'
        WHEN 'blocked' THEN 'Blocked'
        WHEN 'blockedby' THEN 'Blocked By User'
        WHEN 'ignored' THEN 'Ignored'
        WHEN 'archived' THEN 'Archived'
        WHEN 'muted' THEN 'Muted'
        WHEN 'contact' THEN 'Address Book Contact'
        WHEN 'stranger' THEN 'Stranger'
        WHEN 'suggested' THEN 'Suggested'
        WHEN 'recommended' THEN 'Recommended'
        WHEN 'nearby' THEN 'Nearby'
        WHEN 'none' THEN 'None'
        WHEN 'null' THEN 'Unknown'
        WHEN '' THEN 'N/D'
        ELSE UPPER(SUBSTR(U.ZRELATIONSHIP, 1, 1)) || SUBSTR(U.ZRELATIONSHIP, 2)
      END
  END AS "relationship",
  U.ZFIRSTNAME,
  U.ZLASTNAME,
  U.ZGENDER,
  U.ZBIRTHDAY,
  U.ZPHONE,
  U.ZEMAIL,
  U.ZCANONICALURL,
  IIF(U.ZPHOTOPREFIX IS NOT NULL AND U.ZPHOTOSUFFIX IS NOT NULL, 
    U.ZPHOTOPREFIX || 'original' || U.ZPHOTOSUFFIX, '') AS "orig_photo",
  IIF(U.ZBLOCKEDSTATUS = 1 OR U.ZRELATIONSHIP = 'blocked', 'Yes', 
    IIF(U.ZBLOCKEDSTATUS = 0 AND (U.ZRELATIONSHIP != 'blocked' OR U.ZRELATIONSHIP IS NULL), 'No', NULL)
  ) AS "blocked",
  IIF(U.ZMUTED = 1 OR U.ZRELATIONSHIP = 'muted', 'Yes', 
    IIF(U.ZMUTED = 0 AND (U.ZRELATIONSHIP != 'muted' OR U.ZRELATIONSHIP IS NULL), 'No', NULL)
  ) AS "muted",
  IIF(U.ZFRIENDDISABLED = 1, 'Yes', IIF(U.ZFRIENDDISABLED = 0, 'No', NULL)) AS "friend_disabled",
  U.ZCHECKINPINGS,
  coalesce(U.ZFRIENDSCOUNT, 0) AS "friends",
  coalesce(U.ZCHECKINSCOUNT, 0) AS "checkins",
  coalesce(U.ZMAYORSHIPSCOUNT, 0) AS "mayors",
  U.ZMONGOID AS "uid"
FROM ZFSUSER AS "U"
WHERE (U.ZRELATIONSHIP != 'self') OR (U.ZRELATIONSHIP IS NULL)
ORDER BY U.ZLASTNAME ASC, U.ZFIRSTNAME ASC

Esempio di record (U_PK=59) con i nuovi campi:

  • ZLASTMENTIONED: NULL (data e ora dell'ultima menzione in MAC Absolute Time).
  • ZRELATIONSHIP: friend (tipo di relazione con l'utente).
  • ZBLOCKEDSTATUS: NULL (indica se il contatto è stato bloccato; 1=bloccato).
  • ZMUTED: NULL (indica se le notifiche dell'utente sono state "silenziate"; 1=silenziato).
  • ZFRIENDDISABLED: NULL (segnala se l'amicizia è stata disabilitata o revocata; 1=disabilitata).
  • ZCHECKINPINGS: nearby (condivisione del vicinato: nearby=attivo, off=disattivato).
  • ZFRIENDSCOUNT: 1 (numero di amici).
  • ZCHECKINSCOUNT: 1 (numero totale di check-in effettuati dall'utente).
  • ZMAYORSHIPSCOUNT: 0 (numero di mayorship attualmente possedute).

📌Nota: La decodifica e classificazione di ZRELATIONSHIP sono delegate alla funzione interna _normalize_relationship(). Questa routine si occupa di sanificare la stringa grezza estratta dal database e di restituire un testo standardizzato, pulito e leggibile, pronto per essere presentato all'interno del report finale. Nelle query illustrate a seguire, invece, verrà riportato il valore grezzo (raw data) senza alcuna normalizzazione. 

Rubrica

La tabella ZFSADDRESSBOOKUSER viene popolata quando l’utente concede all’applicazione l’autorizzazione ad accedere ai contatti del dispositivo, consentendo così a Swarm di individuare eventuali amici già iscritti alla piattaforma.

In questa analisi viene introdotto un nuovo artefatto, finora non documentato, che permette di delineare con maggiore precisione la "cerchia sociale" dell’utente all’interno dell’ecosistema Foursquare, ampliando il quadro informativo ricavabile dai soli dati dell’account. 

SELECT
  AB.Z_PK AS "U_PK",
  datetime(AB.ZLASTMENTIONED + 978307200, 'unixepoch') AS "last_mentioned",
  AB.ZFIRSTNAME,
  AB.ZLASTNAME,
  AB.ZEMAILADDRESS,
  AB.ZFACEBOOKID,
  AB.ZMONGOID AS "uid",
  AB.ZPHONENUMBERS,
  AB.ZPHONENUMBERLABELS,
  AB.ZIMAGE
FROM ZFSADDRESSBOOKUSER AS "AB"
ORDER BY AB.ZLASTNAME ASC, AB.ZFIRSTNAME ASC

Esempio di record (U_PK=3):

  • Z_PK: 3 (chiave primaria).
  • ZLASTMENTIONED: NULL (data e ora dell’ultima menzione in MAC Absolute Time).
  • ZFIRSTNAME: Django (nome del contatto).
  • ZLASTNAME: Faiola (cognome del contatto).
  • ZEMAILADDRESS: dj***@***.com (indirizzo email associato al contatto).
  • ZFACEBOOKID: NULL (identificativo dell’eventuale account Facebook collegato).
  • ZMONGOID: 388E07CD-9345-4B0D-A03D-2D5874D5669C (identificatore univoco).
  • ZPHONENUMBERS: Plist serializzato (o JSON nelle versioni più recenti) contenente l’elenco dei numeri telefonici.
    • [0]: +39 349 4955***
  • ZPHONENUMBERLABELS: Plist o JSON con le etichette associate ai numeri.
    • [0]: _$!<Mobile>!$_ (la sintassi _$!< >!$_ è la convenzione standard di Apple per distinguere le etichette di sistema da quelle inserite manualmente dall'utente).
  • ZIMAGE: <BLOB> (dati binari dell’immagine del contatto così come presente nella rubrica locale).

Check-in

Questa nuova versione non rappresenta un semplice aggiornamento della precedente, ma una ricostruzione molto più completa, contestuale e forense dell’azione di check‑in.

L’estrazione integra nuovi indicatori di stato, dettagli sul ciclo di vita dell’evento, metadati sociali (come la lista degli amici presenti), metadati geografici avanzati, informazioni sulle venue vicine, dati sull’origine dell’azione, indicatori di interazione e, soprattutto, il supporto ai check‑in passivi.

Swarm integra funzionalità di monitoraggio in background (Neighborhood Sharing). Questi eventi vengono registrati nella tabella ZFSCHECKIN: a differenza dei check‑in manuali, quelli passivi non richiedono un’interazione diretta dell’utente, ma risultano fondamentali per ricostruire spostamenti silenti, contesti di presenza e pattern comportamentali non esplicitamente dichiarati. 

SELECT
  CI.Z_PK AS "CI_PK",
  V.Z_PK AS "V_PK",
  U.Z_PK AS "U_PK",
  G.Z_PK AS "G_PK",
  datetime(CI.ZCREATEDAT + 978307200, 'unixepoch') AS "created",
  IIF(CI.ZEDITED = 1, 'Yes', IIF(CI.ZEDITED = 0, 'No', NULL)) AS "is_edited",
  CASE
    WHEN CI.ZISAUTOMATIC IS NULL AND CI.ZPASSIVESTOPID IS NULL AND CI.ZCHECKINTYPE IS NULL THEN 'N/A'
    WHEN CI.ZISAUTOMATIC = '' AND CI.ZPASSIVESTOPID = '' AND CI.ZCHECKINTYPE = '' THEN 'N/D'
    WHEN CI.ZISAUTOMATIC = 1 THEN 'Automatic'
    WHEN CI.ZPASSIVESTOPID IS NOT NULL THEN 'Passive'
    WHEN LOWER(CI.ZCHECKINTYPE) = 'passive' THEN 'Passive'
    ELSE 'Manual'
  END AS "checkin_type",
  CASE 
    WHEN CI.ZCHECKINSTATUS IS NULL THEN 'N/A'
    WHEN CI.ZCHECKINSTATUS = '' THEN 'N/D'
    WHEN CI.ZCHECKINSTATUS = 0 THEN 'Draft/Failed'
    WHEN CI.ZCHECKINSTATUS = 1 THEN 'Sent/Synchronizing'
    WHEN CI.ZCHECKINSTATUS = 2 THEN 'Confirmed/Published'
    WHEN CI.ZCHECKINSTATUS = 3 THEN 'Deleted (Local)'
    ELSE 'Unknown (' || CI.ZCHECKINSTATUS || ')'
  END AS "checkin_status",
  CI.ZCHECKINRETRIES,
  IIF(EXISTS (
    SELECT 1 FROM ZFSSIMPLEFEEDITEM
    WHERE ZCHECKIN = CI.Z_PK
  ), 'Yes', 'No') AS "in_activity_feed",
  (
    SELECT GROUP_CONCAT(
      TRIM(IFNULL(UW.ZFIRSTNAME,'') || ' ' || IFNULL(UW.ZLASTNAME,'')), '|'
    )
    FROM Z_6WITHFRIENDS AS "WF"
    JOIN ZFSUSER AS "UW" ON (WF.Z_28WITHFRIENDS = UW.Z_PK)
    WHERE WF.Z_6WITHFRIENDSINVERSE = CI.Z_PK
  ) AS "with_friends",
  CI.ZTIMEZONEOFFSET AS "tzoffset_min",
  U.ZRELATIONSHIP,
  TRIM(IFNULL(U.ZFIRSTNAME, '') || ' ' || IFNULL(U.ZLASTNAME, '')) AS "full_name",
  COALESCE(CI.ZVISIBILITY, IIF(CI.ZPRIVATE = 1, 'private', 'public')) AS "visibility",
  V.ZNAME AS "venue_name",
  V.ZDISTANCE,
  V.ZGEOLAT AS "venue_lat",
  V.ZGEOLONG AS "venue_lon",
  V.ZADDRESS,
  V.ZNEIGHBORHOOD,
  V.ZCOUNTRY,
  V.ZSTATE,
  V.ZCITY,
  V.ZCROSSSTREET,
  V.ZPOSTALCODE,
  V.ZPHONE,
  V.ZFACEBOOKID,
  V.ZTWITTER,
  V.ZINSTAGRAM,
  V.ZCANONICALURL AS "venue_url",
  CI.ZCHECKINSHORTURL AS "checkin_url",
  V.ZURL AS "website",
  IIF(CI.ZLIKE = 1, 'Yes', IIF(CI.ZLIKE = 0, 'No', NULL)) AS "liked",
  IIF(CI.ZISMAYOR = 1, 'Yes', IIF(CI.ZISMAYOR = 0, 'No', NULL)) AS "is_mayor",
  CI.ZSHOUT,
  CI.ZENTITIES,
  CI.ZVENUELESSLAT AS "passive_lat",
  CI.ZVENUELESSLNG AS "passive_lng",
  CI.ZVENUELESSLOCATIONNAME AS "passive_location_name",
  CI.ZCONTEXTUALLOCATIONNAME AS "contextual_location",
  CI.ZEXACTCONTEXTLINE AS "exact_context",
  G.ZDISPLAYNAME AS "display_geo",
  CI.ZPASSIVESTOPID AS "passive_stop_id",
  (
    SELECT GROUP_CONCAT(V2.ZNAME, '|')
    FROM Z_6NEARBYVENUES AS "NV"
    JOIN ZFSVENUE AS "V2" ON (NV.Z_29NEARBYVENUES = V2.Z_PK)
    WHERE NV.Z_6NEARBYCHECKIN = CI.Z_PK
  ) AS "nearby_venues",
  CI.ZSOURCENAME AS "source_app",
  CI.ZMONGOID AS "uid"
FROM ZFSCHECKIN AS "CI"
LEFT JOIN ZFSVENUE AS "V" ON (CI.ZVENUE = V.Z_PK)
LEFT JOIN ZFSUSER AS "U" ON (CI.ZUSER = U.Z_PK)
LEFT JOIN ZFSROBINDISPLAYGEO AS "G" ON (CI.ZDISPLAYGEO = G.Z_PK)
LEFT JOIN ZFSSIMPLEFEEDITEM AS "FI" ON (CI.Z_PK = FI.ZCHECKIN)
ORDER BY CI.ZCREATEDAT DESC

Esempio di record (CI_PK=115):

  • Z_PK: 115 (chiave primaria).
  • ZCREATEDAT:  800866875 (data e ora di creazione in MAC Absolute Time: 19 maggio 2026 07:01:15).
  • ZEDITED: NULL (indica se il check‑in è stato modificato; 1=Yes).
  • ZISAUTOMATIC: NULL (1=check-in automatico).
  • ZCHECKINTYPE: checkin (tipo di check-in: checkin=check-in e  passive=tracciamento passivo).
  • checkin_type: Manual (campo derivato per indicare il tipo di check-in : Automatic/Passive/Manual).
  • ZCHECKINSTATUS: 2 (stato logico del check-in: 0=Draft/Failed, 1=Sent/Synchronizing, 2=Confirmed/Published, 3=Deleted (Local)).
  • ZCHECKINRETRIES: 0 (numero di tentativi di invio).
  • in_activity_feed: Yes (campo derivato per indicare la presenza del check‑in nel feed).
  • ZTIMEZONEOFFSET: 120 (offset del fuso orario in minuti, +2 ore).
  • ZVISIBILITY: private (visibilità del check‑in che sostituisce la vecchia logica basata su ZPRIVATE).
  • ZPRIVATE: 1 (flag legacy della privacy: 1=private, 0=public).
  • ZCHECKINSHORTURL: https://swarmapp.com/user/1409236566/checkin/6a0c0abb6692a918f0ae173d?s=Fzn90BkdyHrgQNcDBMyHYFg4l4s (URL pubblico del check‑in).
  • ZLIKE: 0 (indica se l’utente ha messo "Mi piace" alla venue; 1=Liked).
  • ZISMAYOR: 0 (flag che indica se l'utente è il mayor della venue; 1=Mayor).
  • ZSHOUT: SPADE 2026 (testo del check‑in).
  • ZENTITIES: <BLOB> (metadati strutturati: tag, link, oggetti incorporati in formato Plist o JSON; decodificato in text: SPADE 2026|typeString: rawShout).
  • ZVENUELESSLAT: 0.0 (latitudine del check-in passivo).
  • ZVENUELESSLNG: 0.0 (longitudine del check-in passivo).
  • ZVENUELESSLOCATIONNAME: NULL (nome della location stimata quando il check‑in non è associato a una venue specifica).
  • ZCONTEXTUALLOCATIONNAME: Milano, Lombardia (contesto geografico aggiuntivo).
  • ZEXACTCONTEXTLINE: Zona 9, Milano, Lombardia (descrizione testuale precisa del contesto).
  • ZPASSIVESTOPID: NULL (identificatore dello stop passivo).
  • ZSOURCENAME: Swarm for iOS (nome della sorgente: Swarm for Android, Swarm for iOS, Foursquare for iOS, ecc.).
  • ZMONGOID: 6a0c0abb6692a918f0ae173d (identificatore univoco del check-in).
  • ZVENUE: 114 (foreign key della venue → ZFSVENUE.Z_PK).
  • ZUSER: 3 (foreign key del contatto → ZFSUSER.Z_PK).
  • ZDISPLAYGEO: 659 (foreign key della posizione geografica → ZFSROBINDISPLAYGEO.Z_PK).
La tabella Z_6WITHFRIENDS implementa la relazione many‑to‑many tra ZFSCHECKIN (check-in) e ZFSUSER (amico). È una tabella di join generata automaticamente da Core Data per modellare l’associazione tra un check‑in e tutti gli amici che risultano avervi partecipato.

Esempio di record della tabella Z_6WITHFRIENDS:

  • Z_6WITHFRIENDSINVERSE: (foreign key del check-in → ZFSCHECKIN.Z_PK).
  • Z_28WITHFRIENDS: (foreign key dell'amico → ZFSUSER.Z_PK).

Il campo derivato with_friends, presente nella query, genera un elenco degli amici associati al check‑in, formattato come stringa unica con i nomi separati dal carattere |: es.  Mario Rossi|Luca Bianchi|Giulia Verdi.

La tabella Z_6NEARBYVENUES implementa la relazione many‑to‑many tra ZFSCHECKIN (check-in) e ZFSVENUE (venue vicine). Anche questa è una tabella di join generata automaticamente da Core Data per modellare l’associazione tra un check-in e tutte le venue rilevate nelle vicinanze al momento dell’evento.

    Esempio di record della tabella Z_6NEARBYVENUES:

    • Z_6NEARBYCHECKIN: (foreign key del check-in → ZFSCHECKIN.Z_PK).
    • Z_29NEARBYVENUES: (foreign key della venue vicina → ZFSVENUE.Z_PK).

    Il campo derivato nearby_venues, presente nella query, genera un elenco delle venue nelle vicinanze associate alla posizione del check‑in, formattato come stringa unica con i nomi separati dal carattere |: es. IBM Studios|IBM Garage|Passerella Ciclopedonale Melchiorre Gioia.

    La tabella ZFSROBINDISPLAYGEO contiene la rappresentazione semantica e leggibile della posizione geografica associata a un check‑in. Swarm la utilizza per descrivere aree, quartieri, zone urbane o località in modo più naturale rispetto alle sole coordinate GPS, fornendo un contesto geografico interpretativo utile soprattutto nei check‑in passivi.

    Esempio di record (G_PK=659):

    • Z_PK: 659 (chiave primaria).
    • ZDISPLAYNAME: Milano, Lombardia (nome leggibile dell’area).
    • ZMONGOID: 72057594041101371 (identificatore univoco).

    La tabella ZFSVENUE contiene tutte le informazioni strutturate relative alle venue presenti nell’ecosistema Foursquare/Swarm: nome, coordinate, indirizzo, contatti, categorie, URL canonico e metadati sociali.

    Esempio di record (V_PK=114):

    • Z_PK: 114 (chiave primaria);
    • ZNAME: IBM Studios (nome della venue);
    • ZDISTANCE: 571869.0 (distanza stimata in metri dalla posizione dell’utente);
    • ZGEOLAT: 45.483143 (latitudine della venue);
    • ZGEOLONG: 9.191763 (longitudine della venue);
    • ZADDRESS: NULL (indirizzo completo);
    • ZNEIGHBORHOOD: Zona 9 (quartiere o zona urbana);
    • ZCOUNTRY: NULL (paese);
    • ZSTATE: NULL (stato/regione);
    • ZCITY: NULL (città);
    • ZCROSSSTREET: NULL (incrocio o riferimento stradale);
    • ZPOSTALCODE: NULL (codice postale);
    • ZPHONE: 0282848301 (numero di telefono della venue);
    • ZFACEBOOKID: NULL (identificativo dell'account Facebook);
    • ZTWITTER: NULL (identificativo dell'account Twitter);
    • ZINSTAGRAM: NULL (identificativo dell'account Instagram);
    • ZCANONICALURL: https://app.foursquare.com/share/venue/5d271337c9890e002f59e8fe?lang=it (URL canonico della venue);
    • ZURL: NULL (sito web ufficiale della venue).

    📌Nota: Il valore di ZDISTANCE di circa 571 km, è un po' fuori scala😖. Molto probabilmente, a causa del movimento ridotto mentre passeggiavo lentamente nei pressi della sede IBM, lo smartphone ha attivato la modalità di risparmio energetico, switchando dal GPS ad alta precisione alla localizzazione tramite celle telefoniche. Swarm ha quindi calcolato la distanza partendo da un vecchio punto di rete rimasto memorizzato nella cache (legato alla cena di gala della sera precedente), generando questo scostamento esagerato nel database.

    Consigli (Tips)

    I Tips rappresentano contributi multimediali condivisi dagli utenti. Pur avendo una base testuale, includono spesso una componente visiva rilevante: grazie alla relazione tra le entità ZFSTIP e ZFSPHOTO è infatti possibile recuperare le immagini originali caricate dall’utente, trasformando una semplice recensione in un’evidenza fotografica geolocalizzata.

    Questa nuova versione dei Tips introduce il tipo di autore del contributo, ridefinisce correttamente le relazioni con gli utenti e integra diversi indicatori statistici di popolarità, mantenendo al tempo stesso il collegamento diretto con le foto associate. 

    SELECT
  T.Z_PK AS "T_PK",
  V.Z_PK AS "V_PK",
  U.Z_PK AS "U_PK",
  P.Z_PK AS "P_PK",
  datetime(T.ZCREATEDAT + 978307200, 'unixepoch') AS "created",
  datetime(T.ZCACHEMODIFIEDAT, 'unixepoch') AS "modified",
  CASE
    WHEN T.ZTYPE IS NULL THEN 'N/A'
    WHEN T.ZTYPE = '' THEN 'N/D'
    WHEN T.ZTYPE = 'user' THEN 'User'
    WHEN T.ZTYPE = 'mayor' THEN 'Mayor'
    WHEN T.ZTYPE = 'foursquare' THEN 'Foursquare'
    WHEN T.ZTYPE = 'brand' THEN 'Brand'
    WHEN T.ZTYPE = 'expert' THEN 'Expert'
    ELSE UPPER(SUBSTR(T.ZTYPE, 1, 1)) || SUBSTR(T.ZTYPE, 2)
  END AS "author_type",
  U.ZRELATIONSHIP,
  TRIM(IFNULL(U.ZFIRSTNAME, '') || ' ' || IFNULL(U.ZLASTNAME, '')) AS "full_name",
  T.ZTEXT AS "tip",
  T.ZTITLE AS "title",
  T.ZAGREECOUNT,
  T.ZDISAGREECOUNT,
  T.ZLIKESCOUNT,
  T.ZSAVERSCOUNT,
  T.ZCOMMENTSCOUNT,
  T.ZVIEWCOUNT,
  T.ZDONESCOUNT,
  V.ZNAME AS "venue_name",
  V.ZDISTANCE,
  V.ZGEOLAT,
  V.ZGEOLONG,
  T.ZCANONICALURL AS "tip_url",
  T.ZSHORTURL AS "short_url",
  IIF(P.ZPREFIX IS NOT NULL AND P.ZSUFFIX IS NOT NULL,
    P.ZPREFIX || 'original' || P.ZSUFFIX, '') AS "photo_url",
  T.ZMONGOID AS "uid" 
FROM ZFSTIP AS "T"
LEFT JOIN ZFSUSER AS "U" ON (T.ZUSER = U.Z_PK)
LEFT JOIN ZFSVENUE AS "V" ON (T.ZVENUE = V.Z_PK)
LEFT JOIN ZFSPHOTO AS "P" ON (T.ZPHOTO = P.Z_PK)
ORDER BY T.ZCREATEDAT DESC

    Esempio di record (T_PK=16):

    • Z_PK: 16 (chiave primaria).
    • ZCREATEDAT: 584225282 (data e ora di creazione in MAC Absolute Time: 07 luglio 2019 20:48:02).
    • ZCACHEMODIFIEDAT: 1778172038 (data e ora dell’ultima modifica in Unix Epoch: 13 aprile 2026 15:34:10).
    • ZTYPE: user (tipologia di Tip: user=User, mayor=Mayor, foursquare=Foursquare, brand=Brand, expert=Expert, ecc.).
    • ZRELATIONSHIP: Unknown (tipo di relazione tra autore e utente corrente).
    • ZTEXT: Caccio e peppe delicioso e atendimento incrível. Vinho da casa Chardonnay uma delícia. (corpo testuale del contributo).
    • ZTITLE: NULL (titolo opzionale, utilizzato soprattutto per contenuti editoriali o di partner).
    • ZAGREECOUNT: 0 (numero di utenti che hanno espresso accordo con il suggerimento).
    • ZDISAGREECOUNT: 0 (numero di utenti che hanno espresso disaccordo).
    • ZLIKESCOUNT: 0 (totale dei "Mi piace" ricevuti).
    • ZSAVERSCOUNT: 0 (numero di utenti che hanno salvato il Tip tra i preferiti).
    • ZCOMMENTSCOUNT: 0 (numero di commenti presenti sotto il post).
    • ZVIEWCOUNT: 1049 (contatore delle visualizzazioni ricevute).
    • ZDONESCOUNT: 0 (numero di utenti che hanno indicato di aver seguito il consiglio "done").
    • ZCANONICALURL: https://app.foursquare.com/share/tip/5d225a82a7edf80023e1e798?lang=it (link diretto alla versione web del contributo).
    • ZSHORTURL: NULL (versione abbreviata del link).
    • ZMONGOID: 5d225a82a7edf80023e1e798 (identificatore univoco del tip).
    • ZUSER: 36 (foreign key del contatto → ZFSUSER.Z_PK).
    • ZVENUE: 51 (foreign key della venue → ZFSVENUE.Z_PK).
    • ZPHOTO: 36 (foreign key della foto → ZFSPHOTO.Z_PK).

    Foto associata al Tip:
    https://fastly.4sqi.net/img/general/original/180830_8bA1MbA6Wpuvz4Nkug2ivDC0luZ1kuFvpDM-j7Ul7nc.jpg

    Adesivi (Stickers)

    Gli Stickers sono elementi grafici collezionabili che gli utenti sbloccano effettuando check‑in in determinate categorie di luoghi o durante eventi specifici. Rappresentano una componente ludica della piattaforma, ma possono anche offrire indicazioni indirette sulle abitudini di frequentazione dell’utente.

    In questa analisi, la query è stata potenziata introducendo il collegamento tra la tabella ZFSSTICKER e la tabella ZFSSTICKERBONUS. L’integrazione di quest’ultima consente di andare oltre la semplice identificazione dello sticker, aggiungendo un livello interpretativo legato alla progressione, alle meccaniche di sblocco e agli obiettivi perseguiti dall’utente.

    Questo permette di ricostruire non solo quali adesivi siano stati collezionati, ma anche come e perché siano stati ottenuti, evidenziando pattern di comportamento, preferenze di luogo e modalità di interazione con la piattaforma. 

    SELECT
  S.Z_PK AS "S_PK",
  SB.Z_PK AS "SB_PK",
  S.ZNAME,
  S.ZCATEGORYNAME,
  IIF(S.ZUNLOCKED = 1, 'Yes', IIF(S.ZUNLOCKED = 0, 'No', NULL)) AS "unlocked",
  SB.ZBONUSTYPE,
  SB.ZSTATUS,
  SB.ZVALUE AS "multiplier",
  SB.ZPROGRESSCHECKINSEARNED AS "earned",
  SB.ZPROGRESSCHECKINSREQUIRED AS "required",
  SB.ZPROGRESSPERCENTCOMPLETE AS "percent",
  IIF(S.ZIMAGEPREFIX IS NOT NULL AND S.ZIMAGENAME IS NOT NULL,
    S.ZIMAGEPREFIX || 'original' || S.ZIMAGENAME, '') AS "sticker_url",
  COALESCE(SB.ZMONGOID, S.ZMONGOID) AS "uid"
FROM ZFSSTICKER AS "S"
LEFT JOIN ZFSSTICKERBONUS AS "SB" ON (S.Z_PK = SB.ZBONUSESINVERSE)

    Esempio di record (S_PK=54):

    • Z_PK: 54 (chiave primaria).
    • ZNAME: Famous Ray (nome dello sticker).
    • ZCATEGORYNAME: Pizzeria (categoria tematica di appartenenza).
    • ZUNLOCKED: 1 (indica se lo sticker è stato sbloccato dall’utente; 1=Yes).
    • ZIMAGEPREFIX: https://fastly.4sqi.net/img/sticker/ (prefisso URL dell'immagine).
    • ZIMAGENAME: /pizza_61927f.png (suffisso URL dell'immagine).
    • ZMONGOID: 52a659800000000000000011 (identificatore univoco dello sticker).
    Esempio di record (SB_PK=5):
    • Z_PK: 5 (chiave primaria).
    • ZBONUSTYPE: multiplier (tipologia di bonus: multiplier=moltiplicatore, points=punti, sticker=sticker e message=messaggio).
    • ZSTATUS: locked (indica lo stato del bonus: locked=bloccato, unlocked=sbloccato, available=disponibile, completed=completato).
    • ZVALUE: 2 (valore del bonus; in questo caso un moltiplicatore x2).
    • ZPROGRESSCHECKINSEARNED: 6 (check‑in già effettuati dall’utente).
    • ZPROGRESSCHECKINSREQUIRED: 10 (check‑in necessari per lo sblocco del bonus).
    • ZPROGRESSPERCENTCOMPLETE: 60.0 (percentuale di completamento della sfida).
    • ZMONGOID: 52a659800000000000000011-multiplier-2 (identificatore univoco del bonus associato).

    In questo caso nella cache è presente l'immagine nel formato %2FWH%2F:
    https%3A%2F%2Ffastly%2E4sqi%2Enet%2Fimg%2Fsticker%2F300%2Fpizza_61927f%2Epng


    Cronologia delle venue

    La tabella ZFSVENUE raccoglie tutti i luoghi visitati dall’utente, mentre la tabella ZFSCATEGORY contiene le categorie associate a ciascuna venue (ad es. Airport, Medical Center, Men’s Store, ecc.).

    In questa analisi si passa da una semplice rappresentazione anagrafica dei luoghi a una visione arricchita da metadati temporali e indicatori di presenza.

    La nuova query SQL trasforma questo artefatto in una vera e propria cronologia comportamentale, permettendo di distinguere tra i luoghi soltanto visualizzati e quelli che fanno parte della routine quotidiana dell’utente.

    In questo modo emergono pattern di frequentazione, abitudini ricorrenti e dinamiche di mobilità che contribuiscono a delineare un profilo geografico più completo e forense. 

    SELECT
  V.Z_PK AS "V_PK",
  C.Z_PK AS "C_PK",
  U.Z_PK AS "U_PK",
  datetime(V.ZLASTVISITEDAT + 978307200, 'unixepoch') AS "last_visited",
  C.ZNAME AS "category_name",
  V.ZNAME AS "venue_name",
  V.ZDISTANCE,
  V.ZGEOLAT,
  V.ZGEOLONG,
  V.ZADDRESS,
  V.ZNEIGHBORHOOD,
  V.ZCOUNTRY,
  V.ZSTATE,
  V.ZCITY,
  V.ZCROSSSTREET,
  V.ZPOSTALCODE,
  V.ZPHONE,
  V.ZFACEBOOKID,
  V.ZTWITTER,
  V.ZINSTAGRAM,
  V.ZCANONICALURL AS "foursquare_url",
  V.ZURL AS "website",	
  V.ZDESCRIPTIONTEXT,
  IIF(V.ZLIKE = 1, 'Yes', IIF(V.ZLIKE = 0, 'No', NULL)) AS "liked",
  IIF(V.ZDISLIKE = 1, 'Yes', IIF(V.ZDISLIKE = 0, 'No', NULL)) AS "disliked",
  IIF(V.ZCLOSED = 1, 'Yes', IIF(V.ZCLOSED = 0, 'No', NULL)) AS "closed",
  IIF(V.ZSAVED = 1, 'Yes', IIF(V.ZSAVED = 0, 'No', NULL)) AS "saved",
  V.ZBEENHERECOUNT AS "been_here_count",
  V.ZRATING AS "rating",
  U.ZMONGOID AS "mayor_uid",
  V.ZMAYORCOUNT AS "mayor_count",
  TRIM(IFNULL(U.ZFIRSTNAME, '') || ' ' || 
    IFNULL(U.ZLASTNAME, '')) AS "full_name",
  V.ZMAYORSUMMARY,
  V.ZFRIENDVISITSSUMMARY,
  V.ZEVENTSSUMMARY,
  V.ZREASONSUMMARY,
  V.ZLIKESCOUNT,
  V.ZUSERSCOUNT AS "visitors_count",
  V.ZCHECKINSCOUNT,
  V.ZPHOTOSCOUNT,
  V.ZTIPSCOUNT,
  V.ZEVENTSCOUNT
FROM ZFSVENUE AS "V"
LEFT JOIN ZFSCATEGORY AS "C" ON (V.ZPRIMARYCATEGORY = C.Z_PK)
LEFT JOIN ZFSUSER AS "U" ON (V.ZMAYOR = U.Z_PK)
ORDER BY V.ZLASTVISITEDAT DESC

    Esempio di record (V_PK=53):

    • Z_PK: 53 (chiave primaria).
    • ZLASTVISITEDAT: 797714176 (data e ora dell'ultimo check-in dell'account in questa venue in MAC Absolute Time: 12 aprile 2026 19:16:16).
    • ZLIKE: NULL (segnala se l'account ha espresso un gradimento positivo per la venue; 1="Mi Piace").
    • ZDISLIKE: 1 (segnala se l'account ha espresso un giudizio negativo; 1="Non mi piace").
    • ZCLOSED: NULL (indica se la venue è stata segnalata come chiuso definitivamente; 1=chiusa).
    • ZSAVED: NULL (indica se la venue è stata salvata dall'account tramite il pulsante 'Salva' o aggiunta a una lista).
    • ZBEENHERECOUNT: 1 (indica quante volte l'account ha effettuato un check-in in questa venue).
    • ZRATING: NULL (un punteggio medio aggregato calcolato da Foursquare su tutti gli utenti della piattaforma, su scala 0.0-10.0).

    Foto

    Questa nuova versione dell'analisi dedicata alle Foto introduce un cambiamento significativo: la tabella ZFSPHOTO viene ora interrogata in modalità globale.

    Mentre in precedenza l’estrazione era limitata ai soli scatti associati alle venue, l’attuale query recupera tutti i contenuti multimediali presenti nel database, includendo anche quelli collegati ai Tips e ad altre interazioni dell’utente.

    Questo approccio consente di ottenere una visione completa dell’attività fotografica, trasformando l’artefatto in un elemento trasversale che collega venue, suggerimenti e dinamiche d’uso della piattaforma.

    SELECT
  P.Z_PK AS "P_PK",
  CI.Z_PK AS "CI_PK",
  T.Z_PK AS "T_PK",
  V.Z_PK AS "V_PK",
  U.Z_PK AS "U_PK",
  datetime(P.ZCREATEDAT + 978307200, 'unixepoch') AS "created",
  P.ZPHOTOTYPE,
  U.ZMONGOID AS "user_id",
  TRIM(IFNULL(U.ZFIRSTNAME, '') || ' ' || IFNULL(U.ZLASTNAME, '')) AS "full_name",
  CASE
    WHEN P.ZVENUE IS NOT NULL AND P.ZVENUE != '' THEN 'Direct'
    WHEN P.ZPREVIEWVENUE IS NOT NULL AND P.ZPREVIEWVENUE != '' THEN 'Preview'
    WHEN CI.ZVENUE IS NOT NULL AND CI.ZVENUE != '' THEN 'Checkin'
    WHEN T.ZVENUE IS NOT NULL AND T.ZVENUE != '' THEN 'Tip'
    WHEN P.ZVENUE IS NULL AND P.ZPREVIEWVENUE IS NULL
      AND CI.ZVENUE IS NULL AND T.ZVENUE IS NULL THEN 'N/A'
    ELSE 'N/D'
  END AS "venue_source",
  V.ZNAME AS "venue_name",
  V.ZDISTANCE,
  V.ZGEOLAT,
  V.ZGEOLONG,
  V.ZADDRESS,
  V.ZNEIGHBORHOOD,
  V.ZCOUNTRY,
  V.ZSTATE,
  V.ZCITY,
  V.ZCROSSSTREET,
  V.ZPOSTALCODE,
  V.ZPHONE,
  V.ZFACEBOOKID,
  V.ZTWITTER,
  V.ZINSTAGRAM,
  V.ZCANONICALURL AS "foursquare_url",
  V.ZURL AS "website",
  V.ZDESCRIPTIONTEXT,
  IIF(P.ZPENDING = 1, 'Yes', IIF(P.ZPENDING = 0, 'No', NULL)) AS "pending",
  IIF(P.ZPUBLIC = 1, 'Yes', IIF(P.ZPUBLIC = 0, 'No', NULL)) AS "public",
  IIF(P.ZHASSTICKER = 1, 'Yes', IIF(P.ZHASSTICKER = 0, 'No', NULL)) AS "has_sticker",
  IIF(P.ZPREFIX IS NOT NULL AND P.ZSUFFIX IS NOT NULL,
    P.ZPREFIX || 'original' || P.ZSUFFIX, '') AS "photo_url",
  (P.ZWIDTH || 'x' || P.ZHEIGHT) AS "photo_size",
  P.ZSWARMPRIVACYSETTING,
  P.ZSOURCENAME,
  P.ZMONGOID
FROM ZFSPHOTO AS "P"
LEFT JOIN ZFSCHECKIN AS "CI" ON (P.ZCHECKIN = CI.Z_PK)
LEFT JOIN ZFSTIP AS "T" ON (P.ZTIP = T.Z_PK)
LEFT JOIN ZFSVENUE AS "V" ON (V.Z_PK = COALESCE(P.ZVENUE, P.ZPREVIEWVENUE, CI.ZVENUE, T.ZVENUE))
LEFT JOIN ZFSUSER AS "U" ON (P.ZUSER = U.Z_PK)
ORDER BY P.ZCREATEDAT DESC

    Esempio di record (P_PK=38):

    • Z_PK: 38 (chiave primaria).
    • ZPHOTOTYPE: checkin (tipo di foto: venue, checkin, tip, e list).
    • venue_source: checkin (questo campo derivato indica la provenienza della foto, specificando il tipo di evento o interazione che ha generato l’associazione con la venue:
      • Direct: la foto è collegata direttamente a una venue tramite ZVENUE.
      • Preview: la foto è associata a una venue visualizzata in anteprima (ZPREVIEWVENUE).
      • Checkin: la foto deriva da un check‑in (ZCHECKIN).
      • Tip: la foto è stata caricata insieme a un Tip (ZTIP).
    • ZPENDING: NULL (indica che il caricamento sul server della foto non è stato completato; 1=in attesa).
    • ZPUBLIC: NULL (indica se la foto è visibile pubblicamente sulla pagina della venue; 1=pubblico).
    • ZHASSTICKER: NULL (indica che l'utente ha applicato uno sticker o un'adesivo digitale sulla foto; 1=applicato).
    • ZPREFIX: https://fastly.4sqi.net/img/general/ (prefisso URL della foto).
    • ZSUFFIX: /1409236566_BELh_opYYJNaMoor4f5OAj57SOOAZzKNe1XKOlxc5xY.jpg (suffisso URL della foto).
    • ZWIDTH: 1440 (larghezza della foto originale).
    • ZHEIGHT: 1920 (altezza della foto originale).
    • ZSWARMPRIVACYSETTING: private (privacy: private, public, friends, ecc.).
    • ZSOURCENAME: NULL (sorgente/provenienza della foto: Instagram, Swarm for Android, Swarm for iOS, Foursquare for iOS, ecc.).

    Nella cache è presente la foto nel formato originale %2Foriginal%2F:
    https%3A%2F%2Ffastly%2E4sqi%2Enet%2Fimg%2Fgeneral%2Foriginal%2F1409236566_BELh_opYYJNaMoor4f5OAj57SOOAZzKNe1XKOlxc5xY%2Ejpg

    Commenti

    La tabella ZFSCOMMENT  raccoglie tutti i commenti generati all’interno della piattaforma, indipendentemente dal contesto: check‑in, piani (plans), suggerimenti (tips), adesivi (stickers) e altre interazioni sociali.

    La nuova versione della query non si limita a estrarre il testo del messaggio, ma ricostruisce l’intero contesto sociale e geografico in cui ogni commento è stato prodotto.

    Questo approccio consente di collegare ogni commento alla venue di riferimento, all’utente che lo ha pubblicato, all’evento che lo ha generato e agli eventuali contenuti multimediali associati, trasformando l’artefatto in un elemento chiave per l’analisi comportamentale e relazionale. 

    SELECT
  C.Z_PK AS "C_PK",
  U.Z_PK AS "U_PK",
  CI.Z_PK AS "CI_PK",
  PL.Z_PK AS "PL_PK",
  T.Z_PK AS "T_PK",
  L.Z_PK AS "L_PK",
  S.Z_PK AS "S_PK",
  G.Z_PK AS "G_PK",
  V.Z_PK AS "V_PK",
  datetime(C.ZCREATEDAT + 978307200, 'unixepoch') AS "created",
  CASE
    WHEN C.ZCHECKIN IS NOT NULL AND C.ZCHECKIN != '' THEN 'Check-in'
    WHEN C.ZPLAN IS NOT NULL AND C.ZPLAN != '' THEN 'Plan'
    WHEN C.ZTIP IS NOT NULL AND C.ZTIP != '' THEN 'Tip'
    WHEN C.ZLIST IS NOT NULL AND C.ZLIST != '' THEN 'List'
    WHEN C.ZSTICKER IS NOT NULL AND C.ZSTICKER != '' THEN 'Sticker'
    WHEN C.ZCHECKIN IS NULL AND C.ZPLAN IS NULL
      AND C.ZTIP IS NULL AND C.ZLIST IS NULL
      AND C.ZSTICKER IS NULL THEN 'N/A'
    ELSE 'N/D'
  END AS "source_type",
  CASE
    WHEN C.ZCHECKIN IS NOT NULL THEN IFNULL(CI.ZSHOUT, '')
    WHEN C.ZPLAN IS NOT NULL THEN IFNULL(PL.ZTEXT, '')
    WHEN C.ZTIP IS NOT NULL THEN IFNULL(T.ZTEXT, '')
    WHEN C.ZLIST IS NOT NULL THEN IFNULL(L.ZNAME, '')
    WHEN C.ZSTICKER IS NOT NULL THEN IFNULL(S.ZNAME, '')
    ELSE ''
  END AS "source_text",
  U.ZRELATIONSHIP,
  TRIM(IFNULL(U.ZFIRSTNAME, '') || ' ' || IFNULL(U.ZLASTNAME, '')) AS "full_name",
  C.ZTEXT,
  IIF(C.ZPENDING = 1, 'Yes', IIF(C.ZPENDING = 0, 'No', NULL)) AS "pending",
  CASE
    WHEN C.ZSTATUS IS NULL THEN 'N/A'
    WHEN CAST(C.ZSTATUS AS TEXT) = '' THEN 'N/D'
    WHEN C.ZSTATUS = 0 THEN 'Draft/Failed'
    WHEN C.ZSTATUS = 1 THEN 'Sent/Synchronizing'
    WHEN C.ZSTATUS = 2 THEN 'Confirmed/Published'
    WHEN C.ZSTATUS = 3 THEN 'Deleted (Local Flag)'
    ELSE 'Unknown (' || CAST(C.ZSTATUS AS TEXT) || ')'
  END AS "status",
  CASE
    WHEN C.ZPLANCOMMENTTYPE IS NULL THEN 'N/A'
    WHEN C.ZPLANCOMMENTTYPE = '' THEN 'N/D'
    WHEN LOWER(C.ZPLANCOMMENTTYPE) = 'default' or C.ZPLANCOMMENTTYPE = '0' THEN 'Default'
    WHEN LOWER(C.ZPLANCOMMENTTYPE) = 'system' or C.ZPLANCOMMENTTYPE = '1' THEN 'System'
    WHEN LOWER(C.ZPLANCOMMENTTYPE) = 'shout' or C.ZPLANCOMMENTTYPE = '2' THEN 'Shout'
    ELSE 'Unknown (' || C.ZPLANCOMMENTTYPE || ')'
  END AS "comment_type",
  C.ZMENTIONRANGESSTRING,
  C.ZENTITIES,
  C.ZLAT,
  C.ZLNG,
  G.ZDISPLAYNAME AS "display_geo",
  V.ZNAME AS "venue_name",
  V.ZGEOLAT AS "venue_lat",
  V.ZGEOLONG AS "venue_lng",
  C.ZMONGOID AS "uid"
FROM ZFSCOMMENT AS "C"
LEFT JOIN ZFSUSER AS "U" ON (C.ZUSER = U.Z_PK)
LEFT JOIN ZFSCHECKIN AS "CI" ON (C.ZCHECKIN = CI.Z_PK)
LEFT JOIN ZFSPLAN AS "PL" ON (C.ZPLAN = PL.Z_PK)
LEFT JOIN ZFSTIP AS "T" ON (C.ZTIP = T.Z_PK)
LEFT JOIN ZFSLIST AS "L" ON (C.ZLIST = L.Z_PK)
LEFT JOIN ZFSSTICKER AS "S" ON (C.ZSTICKER = S.Z_PK)
LEFT JOIN ZFSROBINDISPLAYGEO AS "G" ON (C.ZDISPLAYGEO = G.Z_PK)
LEFT JOIN ZFSVENUE AS "V" ON (V.Z_PK = COALESCE(CI.ZVENUE, T.ZVENUE))
ORDER BY C.ZCREATEDAT DESC

    Esempio di record (C_PK=1):

    • Z_PK: 1 (chiave primaria).
    • ZCREATEDAT: 797108925 (data e ora di creazione del commento in MAC Absolute Time: 05 aprile 2026 19:08:45).
    • source_type: Check-in (campo derivato che identifica l'entità sorgente o la macro-sezione dell'applicazione a cui il commento è agganciato: Check-in, Plan, Tip, List o Sticker).
    • source_text: Marinara e  vino Sant’Andrea Riflessi CIRCEO rosso. (testo di riferimento dell'evento padre).
    • ZTEXT: Eccellente pizza 🍕 (il corpo del messaggio).
    • ZPENDING: NULL (indica che il commento non è stato ancora sincronizzato con i server; 1=in attesa).
    • ZSTATUS: 2 (stato del commento: 0=Draft/Failed, 1=Sent/Synchronizing, 2=Confirmed/Published, 3=Deleted (Local)).
    • ZPLANCOMMENTTYPE: default (identifica la natura strutturale del record nel flusso dei dati: default=testo inserito dall'utente, system=generato automaticamente dall'app per le notifiche di sistema e shout=record generato dall'app per incorporare contenuti multimediali e posizioni).
    • ZMENTIONRANGESSTRING: NULL (contiene gli indici e i riferimenti agli utenti menzionati/taggati nel testo del commento).
    • ZENTITIES: <BLOB> (contiene i metadati aggiuntivi o oggetti incorporati nel commento, come link o riferimenti multimediali).
    • ZLAT: NULL (latitude del dispositivo).
    • ZLNG: NULL (longitudine del dispositivo).
    • ZMONGOID: 69d2b33d0d6d9f1fe0848f90 (identificatore univoco del commento).

    Richieste di amicizia

    La tabella ZFSFRIENDREQUEST funge da registro temporaneo per le richieste di amicizia in stato di attesa. Nella maggior parte dei dataset questa tabella risulta vuota: una volta che la richiesta viene accettata, rifiutata o ignorata, il sistema aggiorna il campo ZRELATIONSHIP nella tabella ZFSUSER e procede alla cancellazione fisica del record da ZFSFRIENDREQUEST.

    La presenza di record in questa tabella è quindi di particolare interesse investigativo, poiché consente di stabilire con precisione il momento esatto (ZDATEREQUESTED) in cui l’interazione sociale è stata avviata.

    Si tratta di un artefatto effimero, ma estremamente informativo: permette di ricostruire iniziative di contatto, tentativi di connessione e dinamiche relazionali che non sempre emergono dai soli dati persistenti dell’utente.

    SELECT
  FR.Z_PK AS "FR_PK",
  UR.Z_PK AS "UR_PK",
  UE.Z_PK AS "UE_PK",
  datetime(FR.ZDATEREQUESTED + 978307200, 'unixepoch') AS "date_requested",
  CASE
    WHEN UE.ZRELATIONSHIP = 'self' THEN 'Inbound'
    WHEN UR.ZRELATIONSHIP = 'self' THEN 'Outbound'
    ELSE ''
  END AS "direction",
  CASE
    WHEN UE.ZRELATIONSHIP IS NULL AND UR.ZRELATIONSHIP IS NULL THEN 'N/A'
    WHEN CAST(UE.ZRELATIONSHIP AS TEXT) = '' AND CAST(UR.ZRELATIONSHIP AS TEXT) = '' THEN 'N/D'
    WHEN LOWER(UE.ZRELATIONSHIP) = 'self' THEN
      CASE LOWER(IFNULL(UR.ZRELATIONSHIP, ''))
        WHEN 'friend' THEN 'Accepted'
        WHEN 'friendship' THEN 'Accepted'
        WHEN 'pendingthem' THEN 'Pending'
        WHEN 'requested' THEN 'Pending'
        WHEN 'ignored' THEN 'Ignored'
        WHEN '' THEN 'N/D'
        ELSE 'Unknown (' || CAST(UR.ZRELATIONSHIP AS TEXT) || ')'
      END
    WHEN LOWER(UR.ZRELATIONSHIP) = 'self' THEN
      CASE LOWER(IFNULL(UE.ZRELATIONSHIP, ''))
        WHEN 'friend' THEN 'Accepted'
        WHEN 'friendship' THEN 'Accepted'
        WHEN 'pendingme' THEN 'Pending'
        WHEN 'ignored' THEN 'Ignored'
        WHEN '' THEN 'N/D'
        ELSE 'Unknown (' || CAST(UE.ZRELATIONSHIP AS TEXT) || ')'
      END
    ELSE
      'Non-Self Relation'
  END AS "derived_status",
  TRIM(IFNULL(UR.ZFIRSTNAME, '') || ' ' || IFNULL(UR.ZLASTNAME, '')) AS "requester_name",
  UR.ZRELATIONSHIP AS "requester_relationship",
  UR.ZCANONICALURL AS "requester_url",
  UR.ZMONGOID AS "requester_uid",
  TRIM(IFNULL(UE.ZFIRSTNAME, '') || ' ' || IFNULL(UE.ZLASTNAME, '')) AS "requestee_name",
  UE.ZRELATIONSHIP AS "requestee_relationship",
  UE.ZCANONICALURL AS "requestee_url",
  UE.ZMONGOID AS "requestee_uid",
  FR.ZMONGOID AS "uid"
FROM ZFSFRIENDREQUEST AS "FR"
LEFT JOIN ZFSUSER AS "UR" ON (FR.ZREQUESTER = UR.Z_PK)
LEFT JOIN ZFSUSER AS "UE" ON (FR.ZREQUESTEE = UE.Z_PK)
ORDER BY FR.ZDATEREQUESTED DESC

    Esempio di record (FR_PK=NULL):

    • Z_PK: NULL (chiave primaria).
    • ZDATEREQUESTED: NULL (data e ora di creazione del commento in MAC Absolute Time).
    • ZREQUESTER: NULL (foreign key dell’utente che ha inviato la richiesta → ZFSUSER.Z_PK).
    • ZREQUESTEE: NULL (foreign key dell’utente destinatario della richiesta → ZFSUSER.Z_PK).
    • ZMONGOID: NULL (identificatore univoco della richiesta).
    Esempio di record (UR_PK,UE_PK=NULL):
    • direction: NULL (campo derivato che indica la direzione della richiesta rispetto all’utente del profilo: inviata=Outbound o ricevuta=Inbound).
    • derived_status: NULL (campo derivato che segnala lo stato logico della richiesta calcolato tramite la relazioni tra i due utenti: Pending, Accepted o Ignored).
    • requester_name: NULL (nome completo dell’utente che ha inviato la richiesta).
    • requester_relationship: NULL (indica il legame dell'utente richiedente rispetto al proprietario del profilo: self, friend o pendingMe).
    • requester_url: NULL (URL del profilo Foursquare del richiedente).
    • requester_uid: NULL (identificatore univoco del richiedente).
    • requestee_name: NULL (nome completo dell’utente destinatario della richiesta).
    • requestee_relationship: NULL (indica il legame dell'utente ricevente rispetto al proprietario del profilo).
    • requestee_url: NULL (URL del profilo Foursquare del destinatario).
    • requestee_uid: NULL (identificatore univoco del destinatario).

    📌Nota: L’artefatto è stato ricostruito solo dallo schema Core Data. Le tabelle non contengono dati e le mappature di alcuni campi derivano da valori già discussi in altri artefatti. Il parser è funzionale ma non testato su dataset reali.

    Piani (Plans)

    La tabella ZFSPLAN raccoglie le proposte di attività future condivise dall’utente. Questo artefatto è particolarmente rilevante in ambito forense poiché rivela le intenzioni del soggetto, permettendo di tracciare eventi pianificati, confermati o annullati, indipendentemente dal fatto che si siano poi tradotti in un check‑in fisico.

    La query aggiornata non si limita a estrarre il testo del piano, ma ricostruisce l’intero ciclo di vita dell’evento, includendo modifiche, stati, interazioni, notifiche, ricevute di lettura e indicatori di interesse da parte di altri utenti. 

    SELECT
  P.Z_PK AS "P_PK",
  U.Z_PK AS "U_PK",
  datetime(P.ZCREATEDAT + 978307200, 'unixepoch') AS "created",
  datetime(P.ZLASTMODIFIEDTIME + 978307200, 'unixepoch') AS "last_modified",
  U.ZRELATIONSHIP,
  TRIM(IFNULL(U.ZFIRSTNAME, '') || ' ' || IFNULL(U.ZLASTNAME, '')) AS "full_name",
  CASE
    WHEN P.ZISGROUP IS NULL THEN 'N/A'
    WHEN CAST(P.ZISGROUP AS TEXT) = '' THEN 'N/D'
    WHEN P.ZISGROUP = 1 THEN 'Group'
    WHEN P.ZISGROUP = 0 THEN 'Individual'
    ELSE 'Unknown (' || CAST(P.ZISGROUP AS TEXT) || ')'
  END AS "plan_type",
  (
    SELECT GROUP_CONCAT(
      TRIM(IFNULL(UP.ZFIRSTNAME,'') || ' ' || IFNULL(UP.ZLASTNAME,'')), '|'
    )
    FROM Z_16PARTICIPANTS AS "PP"
    JOIN ZFSUSER AS "UP" ON (PP.Z_28PARTICIPANTS = UP.Z_PK)
    WHERE PP.Z_16PARTICIPANTPLANS = P.Z_PK
  ) AS "participants",
  P.ZTEXT,
  P.ZDRAFTCOMMENT,
  CASE
    WHEN P.ZSTATUS IS NULL THEN 'N/A'
    WHEN CAST(P.ZSTATUS AS TEXT) = '' THEN 'N/D'
    WHEN P.ZSTATUS = 0 THEN 'Open'
    WHEN P.ZSTATUS = 1 THEN 'Confirmed'
    WHEN P.ZSTATUS = 2 THEN 'Cancelled'
    WHEN P.ZSTATUS = 3 THEN 'Expired'
    WHEN P.ZSTATUS = 4 THEN 'Deleted'
    WHEN P.ZSTATUS = 5 THEN 'Archived'
    WHEN P.ZSTATUS = 6 THEN 'System Deleted'
    ELSE 'Unknown (' || CAST(P.ZSTATUS AS TEXT) || ')'
  END AS "status",
  IIF(P.ZHIDDENBYUSER = 1, 'Yes', IIF(P.ZHIDDENBYUSER = 0, 'No', NULL)) AS "is_hidden",
  IIF(P.ZINFEED = 1, 'Yes', IIF(P.ZINFEED = 0, 'No', NULL)) AS "in_feed",
  IIF(P.ZISUNREAD = 1, 'Yes', IIF(P.ZISUNREAD = 0, 'No', NULL)) AS "is_unread",
  CASE
    WHEN P.ZMUTESTATE IS NULL THEN 'N/A'
    WHEN P.ZMUTESTATE = '' THEN 'N/D'
    WHEN P.ZMUTESTATE = '0' THEN 'Not Muted'
    WHEN P.ZMUTESTATE = '1' THEN 'Muted Temporarily'
    WHEN P.ZMUTESTATE = '2' THEN 'Muted Permanently'
    ELSE 'Unknown (' || P.ZMUTESTATE || ')'
  END AS "mute_state",
  CASE
    WHEN P.ZREADMARKER IS NULL THEN 'N/A'
    WHEN P.ZREADMARKER = '' THEN 'N/D'
    WHEN P.ZREADMARKER = '0' THEN 'Initialized (No Read Activity)'
    WHEN CAST(P.ZREADMARKER AS DOUBLE) > 0.0 THEN 'Last Read at: ' ||
      datetime(CAST(P.ZREADMARKER AS DOUBLE) + 978307200, 'unixepoch')
    WHEN P.ZREADMARKER = 'false' THEN 'Never Read'
    ELSE 'Marker: ' || P.ZREADMARKER
  END AS "read_marker",
  CASE  
    WHEN P.ZREADRECEIPT IS NULL THEN 'N/A'
    WHEN P.ZREADRECEIPT = '' THEN 'N/D'
    WHEN P.ZREADRECEIPT = '0' THEN 'Unread/New'
    WHEN P.ZREADRECEIPT = '1' THEN 'Confirmed (Read)'
    WHEN P.ZREADRECEIPT = '2' THEN 'Read (Group)'
    ELSE 'Status: ' || P.ZREADRECEIPT
  END AS "read_receipt",
  P.ZINTERESTEDCOUNT,
  P.ZINTERESTEDSUMMARY,
  P.ZCOMMENTSCOUNT,
  P.ZENTITIES,
  U.ZMONGOID AS "owner_uid",
  U.ZCANONICALURL AS "owner_url"
FROM ZFSPLAN AS "P"
LEFT JOIN ZFSUSER AS "U" ON (P.ZUSER = U.Z_PK)
ORDER BY P.ZCREATEDAT DESC

    Esempio di record (P_UK=NULL):

    • Z_PK: NULL (chiave primaria).
    • ZCREATEDAT: NULL (data e ora di creazione della proposta in MAC Absolute Time).
    • ZLASTMODIFIEDTIME: NULL (data e ora dell'ultima modifica apportata al piano o dell'ultimo cambio di stato in MAC Absolute Time).
    • ZISGROUP: NULL (1=indica che il piano è rivolto a un gruppo specifico di persone, 0=è una proposta individuale aperta agli amici).
    • ZTEXT: NULL (testo descrittivo del piano inserito dall'utente).
    • ZDRAFTCOMMENT: NULL (testo di una bozza non ancora pubblicata).
    • ZSTATUS: NULL (stato del ciclo di vita del piano: 0=Open, 1=Confirmed, 2=Cancelled, 3=Expired, 4=Deleted, 5=Archived, 6=System Deleted).
    • is_draft: NULL (è un campo derivato che verifica ZDRAFTCOMMENT non sia NULL o che contenga un testo e ritorna Yes come valore).
    • ZHIDDENBYUSER: NULL (indica che il piano è stato nascosto dall'utente per rimuoverlo dalla timeline; 1=Yes).
    • ZINFEED: NULL (indica se il piano deve essere visualizzato nel feed principale delle attività; 1=Yes).
    • ZISUNREAD: NULL (indica se sono presenti aggiornamenti o interazioni nel piano che l'utente non ha ancora visualizzato; 1=Yes).
    • ZMUTESTATE: NULL (configurazione delle notifiche per l'evento, distinguendo tra notifiche attive, silenziate temporaneamente o permanentemente).
    • ZREADMARKER: NULL (indica il punto fino al quale l'utente ha letto i commenti o gli aggiornamenti del piano: contatori, timestamp, identificatori, ecc.).
    • ZREADRECEIPT: NULL (conferma di lettura; indica se l'utente ha preso visione delle ultime modifiche apportate dagli altri partecipanti: 0=non letto, 1=confermata, 2=read (group) oppure un timestamp che indica il momento esatto in cui la ricevuta di lettura è stata generata e inviata ai server di Foursquare).
    • ZINTERESTEDCOUNT: NULL (numero totale di utenti che hanno manifestato interesse per il piano).
    • ZINTERESTEDSUMMARY: NULL (stringa o blob contenente un'anteprima dei nomi o degli ID degli utenti interessati).
    • ZCOMMENTSCOUNT: NULL (conteggio totale dei commenti postati dagli utenti all'interno del thread del piano).
    • ZENTITIES: NULL (metadati strutturati che contengono tag, menzioni o oggetti speciali incorporati nel testo del piano).
    • ZMONGOID: NULL (identificatore univoco del piano).
    • ZUSER: NULL (foreign key del creatore del piano → ZFSUSER.Z_PK).

    La tabella Z_16PARTICIPANTS implementa la relazione many‑to‑many tra ZFSPLAN (piano) e ZFSUSER (partecipante). Si tratta di una tabella di join generata automaticamente da Core Data per modellare l’associazione tra un piano e tutti gli utenti coinvolti.

    Esempio di record della tabella Z_16PARTICIPANTS:

    • Z_16PARTICIPANTPLANS: (foreign key del piano → ZFSPLAN.Z_PK).
    • Z_28PARTICIPANTS: (foreign key dell’utente partecipante → ZFSUSER.Z_PK).

    Il campo derivato participants, presente nella query, genera un elenco dei partecipanti formattato come stringa unica, con i nomi separati da |.

    📌Nota: L’artefatto è stato ricostruito solo dallo schema Core Data. Le tabelle non contengono dati e non esistono mappature ufficiali pubbliche: il parser è funzionale ma non testato su dataset reali.

    Eventi (Events)

    La tabella ZFSEVENT contiene gli eventi associati alle venue, come proiezioni cinematografiche, spettacoli, attività programmate o iniziative promozionali. Questa sezione dell’analisi consente di ricostruire non solo la natura dell’evento, ma anche la sua collocazione temporale, la categoria, la venue di riferimento, i metadati descrittivi e gli eventuali indicatori di partecipazione (Here Now).

    Questa query permette quindi di trasformare questo artefatto in una timeline strutturata degli eventi rilevanti per l’utente, arricchita da informazioni contestuali e descrittive. 

    SELECT
  E.Z_PK AS "E_PK",
  V.Z_PK AS "V_PK",
  C.Z_PK AS "C_PK",
  datetime(E.ZSTARTAT + 978307200, 'unixepoch') AS "start_at",
  datetime(E.ZENDAT + 978307200, 'unixepoch') AS "end_at",
  datetime(E.ZDATE + 978307200, 'unixepoch') AS "date",
  IIF(E.ZALLDAY = 1, 'Yes', IIF(E.ZALLDAY = 0, 'No', NULL)) AS "all_day",
  E.ZNAME AS "event_name",
  IFNULL(E.ZEVENTTYPE, 'standard') AS "event_type",
  E.ZSUMMARY,
  E.ZHERENOWCOUNT AS "here_now_count",
  (
    SELECT GROUP_CONCAT(
      TRIM(IFNULL(UA.ZFIRSTNAME,'') || ' ' || IFNULL(UA.ZLASTNAME,'')), '|'
    )
    FROM Z_8FRENDSATTENDED AS "FA"
    JOIN ZFSUSER AS "UA" ON (FA.Z_28FRENDSATTENDED = UA.Z_PK)
    WHERE FA.Z_8EVENTSFRIENDSATTENDED = E.Z_PK
  ) AS "friends_attended",
  V.ZNAME AS "venue_name",
  C.ZNAME AS "category_name",
  E.ZCAST,
  E.ZDIRECTORS,
  E.ZGENRES,
  E.ZRATING,
  E.ZRUNNINGTIMESECONDS,
  E.ZPROVIDERNAME AS "provider",
  E.ZEVENTURL AS "event_url",
  E.ZMONGOID AS "uid"
FROM ZFSEVENT AS "E"
LEFT JOIN ZFSVENUE AS "V" ON (E.ZVENUE = V.Z_PK)
LEFT JOIN ZFSCATEGORY AS "C" ON (E.ZPRIMARYCATEGORY = C.Z_PK)
ORDER BY E.ZSTARTAT DESC

    Esempio di record (E_PK=NULL):

    • Z_PK: NULL (chiave primaria).
    • ZSTARTAT: NULL (data e ora di inizio evento in MAC Absolute Time).
    • ZENDAT: NULL (data e ora di fine evento in MAC Absolute Time).
    • ZDATE: NULL (data dell'evento per eventi "Tutto il giorno"  in MAC Absolute Time; NULL se ZALLDAY=0).
    • ZALLDAY: NULL (flag booleano: 1=evento a giornata intera, 0=evento con orario definito).
    • ZNAME: NULL (nome/titolo dell'evento).
    • ZEVENTTYPE: NULL (tipo di evento: film, concert, standard e NULL per eventi generici).
    • ZSUMMARY: NULL (descrizione testuale dell'evento).
    • ZHERENOWCOUNT: NULL (numero di utenti Swarm presenti all'evento al momento dell'ultimo aggiornamento della cache).
    • ZCAST: NULL (cast artistico - principalmente per eventi cinematografici e spettacoli).
    • ZDIRECTORS: NULL (regia - principalmente eventi cinematografici).
    • ZGENRES: NULL (genere/i dell'evento).
    • ZRATING: NULL (valutazione numerica dell'evento).
    • ZRUNNINGTIMESECONDS: NULL (durata in secondi - diviso 60 per i minuti; 5400 = 90 minuti).
    • ZPROVIDERNAME: NULL (fonte del dato evento: Fandango, Eventbrite, Foursquare).
    • ZEVENTURL: NULL (URL della pagina evento presso il provider).
    • ZMONGOID: NULL (identificatore univoco dell'evento).
    La tabella Z_8FRENDSATTENDED implementa la relazione many‑to‑many tra ZFSEVENT (evento) e ZFSUSER (amico partecipante). È una tabella di join generata automaticamente da Core Data per modellare l’associazione tra un evento e tutti gli amici che risultano avervi partecipato.

    Esempio di record della tabella Z_8FRENDSATTENDED:

    • Z_8EVENTSFRIENDSATTENDED: (foreign key del piano → ZFSEVENT.Z_PK).
    • Z_28FRENDSATTENDED: (foreign key dell’utente partecipante → ZFSUSER.Z_PK).

    Il campo derivato friends_attended, presente nella query, genera un elenco degli utenti partecipanti formattato come stringa unica, con i nomi separati da |.

    📌Nota: L’artefatto è stato ricostruito solo dallo schema Core Data. Le tabelle non contengono dati e non esistono mappature ufficiali pubbliche: il parser è funzionale ma non testato su dataset reali.

    Bollettini (feed)

    La tabella ZFSACTIVITYSTREAMBULLETIN contiene i contenuti strutturati dei bollettini mostrati nel feed dell’applicazione. Ogni record rappresenta un elemento del flusso attività, come notifiche, interazioni sociali, suggerimenti, milestone o aggiornamenti generati dal sistema.

    In combinazione con ZFSSIMPLEFEEDITEM, questa struttura consente di ricostruire in modo dettagliato: il tipo di evento generato, l’utente che lo ha prodotto (actor), l’utente eventualmente coinvolto (target), la venue associata, il check‑in nativo (se presente), eventuali contenuti multimediali e metadati aggiuntivi (entities, target, mongoid). 

    SELECT
  FI.Z_PK AS "FI_PK",
  B.Z_PK AS "B_PK",
  UA.Z_PK AS "UA_PK",
  UT.Z_PK AS "UT_PK",
  CI.Z_PK AS "CI_PK",
  V.Z_PK AS "V_PK",
  datetime(FI.ZCREATEDAT + 978307200, 'unixepoch') AS "created",
  FI.ZFEEDTYPE,
  CASE
    WHEN FI.ZFEEDCONTENTTYPE IS NULL THEN 'N/A'
    WHEN CAST(FI.ZFEEDCONTENTTYPE AS TEXT) = '' THEN 'N/D'
    WHEN FI.ZFEEDCONTENTTYPE = 1 THEN 'Friend Check-in'
    WHEN FI.ZFEEDCONTENTTYPE = 2 THEN 'Social Interaction (Like/Comment)'
    WHEN FI.ZFEEDCONTENTTYPE = 3 THEN 'Friend Request/Recommendation'
    WHEN FI.ZFEEDCONTENTTYPE = 4 THEN 'System Notification/Milestone'
    ELSE 'Unknown (' || CAST(FI.ZFEEDCONTENTTYPE AS TEXT) || ')'
  END AS "content_type",
  B.ZBULLETINTYPE,
  B.ZTITLETEXT,
  B.ZTEXT,
  B.ZACTIONTEXT,
  TRIM(IFNULL(UA.ZFIRSTNAME, '') || ' ' || IFNULL(UA.ZLASTNAME, '')) AS "actor_name",
  TRIM(IFNULL(UT.ZFIRSTNAME, '') || ' ' || IFNULL(UT.ZLASTNAME, '')) AS "target_name",
  IIF(B.ZIMAGEPREFIX IS NOT NULL AND B.ZIMAGESUFFIX IS NOT NULL,
    B.ZIMAGEPREFIX || 'original' || B.ZIMAGESUFFIX, '') AS "image_url",
  IFNULL(CI.ZSHOUT, '') AS "native_checkin_shout",
  COALESCE(V.ZNAME, B.ZVENUEID) AS "resolved_venue_name",
  V.ZGEOLAT AS "venue_latitude",
  V.ZGEOLONG AS "venue_longitude",
  B.ZENTITIES AS "entities",
  B.ZTARGET AS "target",
  FI.ZMONGOID
FROM ZFSSIMPLEFEEDITEM AS "FI"
LEFT JOIN ZFSACTIVITYSTREAMBULLETIN AS "B" ON (FI.ZACTIVITYSTREAMBULLETIN = B.Z_PK)
LEFT JOIN ZFSUSER AS "UA" ON (FI.ZUSER = UA.Z_PK)
LEFT JOIN ZFSUSER AS "UT" ON (B.ZUSER = UT.Z_PK)
LEFT JOIN ZFSCHECKIN AS "CI" ON (FI.ZCHECKIN = CI.Z_PK)
LEFT JOIN ZFSVENUE AS "V" ON (CI.ZVENUE = V.Z_PK)
ORDER BY FI.ZCREATEDAT DESC

    Esempio di record (FI_PK=97):

    • Z_PK: 97 (chiave primaria).
    • ZCREATEDAT: 800866875 (data e ora di creazione del commento in MAC Absolute Time: 19 maggio 2026 07:01:15).
    • ZFEEDTYPE: activitiesRecent (categoria generale del feed).
    • ZFEEDCONTENTTYPE: 1 (tipo di contenuto: 1=Friend Check‑in, 2=Social Interaction (Like/Comment), 3=Friend Request/Recommendation, 4=System Notification/Milestone).
    • ZMONGOID: checkin6a0c0abb6692a918f0ae173d (Identificatore univoco dell’evento).
    • ZUSER: NULL (foreign key dell'actor → ZFSUSER.Z_PK).
    • ZCHECKIN: 115 (foreign key al check‑in nativo → ZFSCHECKIN.Z_PK).
    • ZACTIVITYSTREAMBULLETIN: NULL (foreign key al contenuto del bollettino → ZFSACTIVITYSTREAMBULLETIN.Z_PK).

    Esempio di record (B_PK=NULL):

    • Z_PK: NULL (chiave primaria).
    • ZBULLETINTYPE: NULL (tipo di bollettino).
    • ZTITLETEXT: NULL (titolo del bollettino mostrato nel feed).
    • ZTEXT: NULL (testo descrittivo aggiuntivo).
    • ZACTIONTEXT: NULL (testo dell’azione associata).
    • ZIMAGEPREFIX: NULL (prefisso dell’URL dell’immagine associata al bollettino).
    • ZIMAGESUFFIX: NULL (suffisso dell’URL dell’immagine).
    • ZENTITIES: <BLOB> (struttura Plist/JSON contenente metadati aggiuntivi: riferimenti a utenti, venue, check‑in).
    • ZTARGET: NULL (riferimento all’oggetto correlato: check‑in, venue, utente, ecc.).
    • ZUSER: NULL (foreign key dell’utente target → ZFSUSER.Z_PK).

    Liste Salvate

    La tabella ZFSLIST contiene le informazioni principali sulle liste create, seguite o generate automaticamente dall’applicazione, mentre la tabella ZFSLISTITEM memorizza i singoli elementi che compongono ciascuna lista.

    Ogni lista può includere venue, note personali, link esterni o elementi provenienti da suggerimenti dell’app. 

    SELECT
  L.Z_PK AS "L_PK",
  LI.Z_PK AS "LI_PK",
  V.Z_PK AS "V_PK",
  U.Z_PK AS "U_PK",
  datetime(L.ZCREATEDAT + 978307200, 'unixepoch') AS "list_created",
  datetime(L.ZLISTUPDATEDAT  + 978307200, 'unixepoch') AS "list_updated",
  datetime(L.ZUPDATEDAT  + 978307200, 'unixepoch') AS "list_metadata_updated",
  TRIM(IFNULL(U.ZFIRSTNAME, '') || ' ' || IFNULL(U.ZLASTNAME, '')) AS "owner_name",
  L.ZNAME AS "list_name",
  L.ZLISTTYPE,
  L.ZLISTDESCRIPTION,
  IIF(L.ZCOLLABORATIVE = 1, 'Yes', IIF(L.ZCOLLABORATIVE = 0, 'No', NULL)) AS "collaborative",
  CASE
    WHEN L.ZEDITABLE IS NULL AND L.ZCOLLABORATIVE IS NULL THEN 'N/A'
    WHEN CAST(L.ZEDITABLE AS TEXT) = '' AND CAST(L.ZCOLLABORATIVE AS TEXT) = '' THEN 'N/D'
    WHEN L.ZEDITABLE = 1 AND L.ZCOLLABORATIVE = 1 THEN 'friends (derived)'
    WHEN L.ZEDITABLE = 1 AND L.ZCOLLABORATIVE = 0 THEN 'private (derived)'
    WHEN L.ZEDITABLE = 0 THEN 'public (derived)'
    ELSE 'unknown (derived)'
  END AS "access_level",
  L.ZLISTITEMSCOUNT,
  L.ZFOLLOWERSCOUNT,
  IIF(L.ZLIKE = 1, 'Yes', IIF(L.ZLIKE = 0, 'No', NULL)) AS "list_liked",
  L.ZLIKESCOUNT,
  L.ZCANONICALURL AS "list_url",
  datetime(LI.ZCREATEDAT + 978307200, 'unixepoch') AS "item_added",
  datetime(LI.ZSHAREDAT + 978307200, 'unixepoch') AS "item_shared",
  V.ZNAME AS "venue_name",
  V.ZADDRESS AS "venue_address",
  V.ZCITY AS "venue_city",
  V.ZGEOLAT AS "latitude",
  V.ZGEOLONG AS "longitude",
  IIF(LI.ZVISITED = 1, 'Yes', IIF(LI.ZVISITED = 0, 'No', NULL)) AS "visited",
  CASE
    WHEN LI.ZSTATE IS NULL THEN 'N/A'
    WHEN LI.ZSTATE = '' THEN 'N/D'
    WHEN LI.ZSTATE = '0' THEN 'Active/Saved'
    WHEN LI.ZSTATE = '1' THEN 'Archived'
    WHEN LI.ZSTATE = '2' THEN 'Removed'
    ELSE 'Unknown (' || LI.ZSTATE || ')'
  END AS "item_status",
  LI.ZTEXT AS "item_note",
  LI.ZSOURCEWEBTITLE AS "source_title",
  LI.ZSOURCEWEBURL AS "source_url",
  LI.ZLISTITEMID AS "item_server_id",
  L.ZMONGOID AS "list_uid"
FROM ZFSLIST AS "L"
LEFT JOIN ZFSUSER AS "U" ON (L.ZUSER = U.Z_PK)
LEFT JOIN ZFSLISTITEM AS "LI" ON (LI.ZLIST = L.Z_PK)
LEFT JOIN ZFSVENUE AS "V" ON (LI.ZVENUE = V.Z_PK)
WHERE L.ZNAME IS NOT NULL
ORDER BY L.ZLISTUPDATEDAT DESC, LI.ZCREATEDAT DESC 

    Esempio di record (L_PK=57):

    • Z_PK: 57 (chiave primaria).
    • ZCREATEDAT: 797523808 (data e ora di creazione della lista in MAC Absolute Time: 10 aprile 2026 14:23:28).
    • ZLISTUPDATEDAT: 798994566 (data e ora dell'ultima modifica degli elementi nella lista in MAC Absolute Time: 27 aprile 2026 14:56:06).
    • ZUPDATEDAT: NULL (data e ora dell'ultima modifica dei metadati della lista, es. cambio nome o descrizione, in MAC Absolute Time).
    • ZCLIENTUPDATEDAT: NULL (data e ora locale dell'ultima modifica in MAC Absolute Time; versioni 7.x).
    • ZLASTVIEWEDAT: NULL (data e ora dell'ultima apertura/visualizzazione della lista in MAC Absolute Time; versioni 7.x).
    • ZNAME: Pizzerie 🍕 (nome della  lista).
    • ZLISTTYPE: created (tipo di lista: created=creata dall'utente, others=categoria generica).
    • ZLISTDESCRIPTION: Degustazione pizza 🍕 (descrizione della lista).
    • ZCOLLABORATIVE: 0 (flag che indica se la lista è collaborativa; 1=Yes).
    • ZEDITABLE: 1 (flag che indica se la lista è modificabile; 1=Yes).
    • ZFOLLOWING: NULL (flag che indica se la lista è seguita; 1=Yes).
    • ZACCESS: NULL (sostituisce la vecchia gestisce della privacy basata su ZEDITABLE, ZCOLLABORATIVE e ZFOLLOWING: private=privata, friends=solo amici e public=pubblica; versioni 7.x).
    • ZLISTITEMSCOUNT: 3 (numero di elementi presenti nella lista).
    • ZFOLLOWERSCOUNT: 0 (numero di follower).
    • ZLIKE: NULL (flag che indica se l'utente ha messo "Mi Piace" sull'intera lista; 1=YES).
    • ZLIKESCOUNT: 0 (numero di "Mi Piace" ricevuti).
    • ZCANONICALURL: https://app.foursquare.com/share/list/69d907e024c57c33e73b38fb?lang=it (URL canonico della lista).
    • ZMONGOID: 69d907e024c57c33e73b38fb (identificatore univoco della lista).
    • ZUSER: 3 (foreign key dell’utente proprietario → ZFSUSER.Z_PK).
    • ZCREATOR: NULL (foreign key dell'utente creatore della lista →  ZFSUSER.Z_PK; versioni 7.x).

    Esempio di record (LI_PK=2):

    • Z_PK: 2 (chiave primaria).
    • ZCREATEDAT: 797523871 (data e ora di aggiunta dell’elemento in MAC Absolute Time: 10 aprile 2026 14:24:31).
    • ZSHAREDAT: NULL (data e ora di condivisione dell’elemento in MAC Absolute Time).
    • ZVISITED: NULL (flag che indica se la venue è stata visitata; 1=Yes).
    • ZUNREAD: NULL (flag che indica se un amico ha aggiunto un locale a una lista condivisa e l'utente sul dispositivo non ha ancora visualizzato l'aggiornamento; 1=Yes).
    • ZSTATE: NULL (stato dell’elemento: '0'=Active/Saved, '1'=Archived, '2'=Removed).
    • ZTEXT: NULL (nota associata all’elemento).
    • ZSOURCEWEBTITLE: NULL (titolo della pagina web da cui è stato salvato l’elemento).
    • ZSOURCEWEBURL: NULL (URL della pagina web di origine).
    • ZLISTITEMID: v50294a29e4b05b9e02e2ae37 (identificatore univoco lato server dell’elemento; ZSERVERID/ZID versioni 7.x).
    • ZSOURCE: NULL (indica la sorgente che ha originato l'aggiunta del luogo alla lista: search, recommendation, nearby, map_click, ecc.; versioni 7.x).
    • ZLIST: 57 (foreign key della lista di appartenenza → ZFSLIST.Z_PK).
    • ZVENUE: 1 (foreign key della venue associata → ZFSVENUE.Z_PK).

    Cronologia delle posizioni passive

    La tabella ZFSPLOCATION registra le posizioni passive rilevate dall’applicazione tramite i servizi di localizzazione del dispositivo. Si tratta di coordinate acquisite in background, senza un’azione diretta dell’utente, utilizzate da Swarm per aggiornare la posizione corrente, supportare la funzione di Neighborhood Sharing e  ottimizzare la precisione dei check‑in automatici.

    È un artefatto essenziale per ricostruire gli spostamenti dell'utente e verificare la coerenza di check‑in e attività anche in assenza di interazioni esplicite.

    Nella pratica, tuttavia, la tabella risulta quasi sempre vuota o contiene pochissimi record, poiché iOS limita fortemente le acquisizioni di localizzazione in background; quando presente, ogni valore rappresenta quindi un punto di elevato valore forense. 

    SELECT
  PL.Z_PK AS "PL_PK",
  datetime(PL.ZTIMESTAMP + 978307200, 'unixepoch') AS "timestamp",
  PL.ZLATITUDE,
  PL.ZLONGITUDE,
  PL.ZHORIZONTALACCURACY,
  CASE
    WHEN PL.ZHORIZONTALACCURACY IS NULL THEN 'N/A'
    WHEN CAST(PL.ZHORIZONTALACCURACY AS TEXT) = '' THEN 'N/D'
    WHEN PL.ZHORIZONTALACCURACY < 0.0 THEN 'Invalid (No Fix/Negative)'
    WHEN PL.ZHORIZONTALACCURACY = 0.0 THEN 'Invalid (Zero Accuracy)'
    WHEN PL.ZHORIZONTALACCURACY <= 30.0 THEN 'High (GPS/Precise Wi-Fi)'
    WHEN PL.ZHORIZONTALACCURACY <= 200.0 THEN 'Medium (Wi-Fi Network)'
    WHEN PL.ZHORIZONTALACCURACY <= 1500.0 THEN 'Low (Cell Tower/Triangulation)'
    ELSE 'Very Low (' || CAST(PL.ZHORIZONTALACCURACY AS TEXT) || 'm)'
  END AS "accuracy_context",
  CASE
    WHEN PL.ZSPEEDSTATE IS NULL THEN 'N/A'
    WHEN CAST(PL.ZSPEEDSTATE AS TEXT) = '' THEN 'N/D'
    WHEN PL.ZSPEEDSTATE = 0 THEN 'Stationary'
    WHEN PL.ZSPEEDSTATE = 1 THEN 'Walking'
    WHEN PL.ZSPEEDSTATE = 2 THEN 'Automotive'
    WHEN PL.ZSPEEDSTATE = 3 THEN 'Cycling'
    WHEN PL.ZSPEEDSTATE = 4 THEN 'Running'
    ELSE 'Unknown (' || CAST(PL.ZSPEEDSTATE AS TEXT) || ')'
  END AS "speed_state",
  IIF(PL.ZSENT = 1, 'Synchronized', IIF(PL.ZSENT = 0, 'Local Only', NULL)) AS "sync_status"
FROM ZFSPLOCATION AS "PL"
WHERE PL.ZLATITUDE IS NOT NULL AND PL.ZLONGITUDE IS NOT NULL
ORDER BY PL.ZTIMESTAMP DESC

    Esempio di record (PL_PK=NULL):

    • Z_PK: NULL (chiave primaria).
    • ZTIMESTAMP: NULL (data e ora della rilevazione in MAC Absolute Time).
    • ZLATITUDE: NULL (latitudine del punto rilevato).
    • ZLONGITUDE: NULL (longitudine del punto rilevato).
    • ZHORIZONTALACCURACY: NULL (accuratezza stimata in metri).
    • accuracy_context: NULL (campo derivato che classifica l’accuratezza orizzontale della posizione in una categoria interpretativa: High, Medium, Low, Very Low, Invalid).
    • ZSPEEDSTATE: NULL (stato di movimento dedotto dal Core Motion: 0=Stationary, 1=Walking, 2=Automotive, 3=Cycling, 4=Running).
    • ZSENT: NULL (stato di sincronizzazione: 1=Synchronized (inviato al server), 0=Local Only (ancora sul dispositivo)).

    📌Nota: La struttura dell’artefatto è ricostruita correttamente dallo schema Core Data, ma non è stato possibile testarne il comportamento su dataset reali a causa della naturale scarsità dei dati.

    Pilgrim logs

    La tabella ZFSPLOG contiene i log interni generati dal motore di localizzazione Pilgrim, utilizzato da Swarm per determinare ingressi/uscite da luoghi, transizioni di stato, qualità del fix e condizioni operative dell’app. Ogni record rappresenta un evento diagnostico con timestamp, livello di log, stato dell’app, coordinate (se presenti) e informazioni aggiuntive utili per interpretare il comportamento del sistema di geolocalizzazione. 

    SELECT
  PL.Z_PK AS "Z_PK",
  datetime(PL.ZTIMESTAMP + 978307200, 'unixepoch') AS "timestamp",
  CASE
    WHEN PL.ZLOGLEVEL IS NULL THEN 'N/A'
    WHEN CAST(PL.ZLOGLEVEL AS TEXT) = '' THEN 'N/D'
    WHEN PL.ZLOGLEVEL = 1 THEN 'Error'
    WHEN PL.ZLOGLEVEL = 2 THEN 'Warning'
    WHEN PL.ZLOGLEVEL = 3 THEN 'Info'
    WHEN PL.ZLOGLEVEL = 4 THEN 'Debug'
    ELSE 'Unknown (' || CAST(PL.ZLOGLEVEL AS TEXT) || ')'
  END AS "log_level",
  CASE
    WHEN PL.ZAPPSTATE IS NULL THEN 'N/A'
    WHEN CAST(PL.ZAPPSTATE AS TEXT) = '' THEN 'N/D'
    WHEN PL.ZAPPSTATE = 0 THEN 'Background'
    WHEN PL.ZAPPSTATE = 1 THEN 'Foreground'
    ELSE 'Unknown (' || CAST(PL.ZAPPSTATE AS TEXT) || ')'
  END AS "app_state",
  PL.ZLOGTITLE,
  PL.ZINFO,
  PL.ZEVENTTYPE,
  PL.ZLATITUDE ,
  PL.ZLONGITUDE,
  PL.ZHORIZONTALACCURACY,
  CASE
    WHEN PL.ZHORIZONTALACCURACY IS NULL THEN 'N/A'
    WHEN CAST(PL.ZHORIZONTALACCURACY AS TEXT) = '' THEN 'N/D'
    WHEN PL.ZHORIZONTALACCURACY < 0.0 THEN 'Invalid (No Fix/Negative)'
    WHEN PL.ZHORIZONTALACCURACY = 0.0 THEN 'Invalid (Zero Accuracy)'
    WHEN PL.ZHORIZONTALACCURACY <= 10.0 THEN 'High (GPS)'
    WHEN PL.ZHORIZONTALACCURACY <= 100.0 THEN 'Medium (Wi-Fi/Cell)'
    WHEN PL.ZHORIZONTALACCURACY <= 1000.0 THEN 'Low (Cell Tower)'
    ELSE 'Very Low (' || CAST(PL.ZHORIZONTALACCURACY AS TEXT) || 'm)'
  END AS "accuracy_context",
  PL.ZRADIUS
FROM ZFSPLOG AS "PL"
WHERE PL.ZTIMESTAMP IS NOT NULL
ORDER BY PL.ZTIMESTAMP DESC

    Esempio di record (PL_PK=NULL):

    • Z_PK: NULL (chiave primaria).
    • ZTIMESTAMP: NULL (data e ora della rilevazione in MAC Absolute Time).
    • ZLOGLEVEL: NULL (livello del log: 1=Error, 2=Warning, 3=Info, 4=Debug).
    • ZAPPSTATE: NULL (stato dell’app: 0=Background, 1=Foreground).
    • ZLOGTITLE: NULL (titolo del log).
    • ZINFO: NULL (informazioni aggiuntive).
    • ZEVENTTYPE: NULL (tipo di evento Pilgrim).
    • ZLATITUDE: NULL (latitudine).
    • ZLONGITUDE: NULL (longitudine).
    • ZHORIZONTALACCURACY: NULL (accuratezza in metri).
    • accuracy_context: NULL (campo derivato che classifica l’accuratezza orizzontale della posizione in una categoria interpretativa: High, Medium, Low, Very Low, Invalid).
    • ZRADIUS: NULL (raggio in metri associato all’evento).

    📌Nota: La struttura dell’artefatto è ricostruita correttamente dallo schema Core Data, ma non è stato possibile testarne il comportamento su dataset reali a causa della naturale scarsità dei dati.

    iLEAPP/LAVA 💖

    Come già anticipato, a supporto del progetto iLEAPP (iOS Logs, Events, And Plists Parser) di Alexis Brignoni, ho aggiornato il plugin foursquareSwarm.py. 

    Colgo l’occasione della recente pubblicazione di LAVA (LEAPP Artifact Viewer App) per mostrare alcuni screenshot dei risultati ottenuti dal modulo.


    Read More

    , , , , ,

    Iscriviti a: Post (Atom)