Messages missing after upgrading to Ventura

[alexdanilowicz]

A user reported that after upgrading to Ventura, the text column on the message table was NULL

[ju-li]

@alexdanilowicz

Love what you're working on (I'm working on something iMessage related too). I solved this issue by using the attributedBody field
That field is a bunch of gibberish (some kind of objective-c object). Anyway, I cast it to text in sqlite. And then in JS extracted the text like this:
attributedBody?.substring(attributedBody?.search('NSString')+14, attributedBody?.search('NSDictionary')-12)

Hacky as fuck but it works 🤷

[alexdanilowicz]

hey @ju-li! Thank you so much for sharing. I'll have to add this, or feel free to open up a PR, but this is super super helpful! Really appreciate you commenting.

Would love to check out your product too. :)

[alexdanilowicz]

@ju-li — following up here, do you happen to have a code snippet? I'd love to finally improve this. Thanks for your original message, Automata looks very cool!

[ju-li]

@alexdanilowicz hi sorry I was busy and forgot to respond

So this is my code for generating a CSV export of the message history:

const exportData = async (filePath: string, startdate: string, enddate: string) => {
 const createCsvWriter = csv.createObjectCsvWriter
 const csvWriter = createCsvWriter({
    path: filePath,
    header: [
      {id: 'guid', title: 'GUID'},
      {id: 'date', title: 'Date'},
      {id: 'time', title: 'Time'},
      {id: 'recipientId', title: 'Recipient'},
      {id: 'roomName', title: 'Room'},
      {id: 'displayName', title: 'Display Name'},
      {id: 'message', title: 'Message'},
      {id: 'service', title: 'Service'},
      {id: 'direction', title: 'Direction'},
      {id: 'status', title: 'Status'},
    ]
  });

  const query = `WITH data AS (
    SELECT
      m.guid,
      CASE WHEN m.date > 10000000000 THEN 
          -- date is the time elapsed in nanoseconds since 1/1/2001.
          -- We then convert it to our localtime
          -- Not sure why this is 1 hour ahead though, even in UTC time
          -- See: https://apple.stackexchange.com/questions/114168/dates-format-in-messages-chat-db
          datetime((m.date/1000000000) + strftime('%s','2001-01-01 00:00:00'), 'unixepoch', 'localtime')
        ELSE 
          datetime(m.date + strftime('%s','2001-01-01 00:00:00'), 'unixepoch', 'localtime')
        END AS datetime,
        c.chat_identifier as recipientId,
        c.room_name as roomName,
        c.display_name as displayName,
        CASE 
          WHEN maj.attachment_id IS NOT NULL THEN '[Attachment]'
          ELSE m."text"
        END as message,
        m.service,
        CASE 
          WHEN m.is_from_me = 1 THEN 'outbound'
          ELSE 'inbound'
        END as direction,
        CASE
          WHEN m.error = 1 THEN 'Error'
          WHEN m.is_read = 1 THEN 'Read'
          WHEN m.is_delivered = 1 THEN 'Delivered'
          ELSE 'Sent'
        END as status,
        CAST(m.attributedBody as TEXT) as attributedBody2
        
    FROM chat as c
    JOIN chat_message_join cmj ON c.ROWID = cmj.chat_id 
    JOIN message m ON cmj.message_id = m.ROWID
    LEFT JOIN message_attachment_join maj ON maj.message_id = m.ROWID )
  SELECT guid, date(datetime) as date, time(datetime) as time, recipientId, roomName, displayName, message, service, direction, status, attributedBody2 FROM data
  WHERE date(datetime) BETWEEN '${startdate.substring(0,10)}' AND '${enddate.substring(0,10)}'
  ORDER BY 2 DESC, 3 DESC
  `

  const data: Data[] = await getSQLData(query)
  const data2 = data.map((d) => {
    const fixed = {
      ...d,
      message: d.message ?? d.attributedBody2?.substring(d.attributedBody2?.search('NSString')+14, d.attributedBody2?.search('NSDictionary')-12)
    }
    delete fixed.attributedBody2
    return fixed
  })
  await csvWriter.writeRecords(data2)
}

If the text field is missing, I'd do a search for NSString and NSDictionary and hack it that way =X

The reason I did that is because the attributedBody field that contains the message data is packed with this thing called NSArchiver, which is specific to Macs: https://stackoverflow.com/questions/75330393/how-can-i-read-the-attributedbody-column-in-macos-imessage-database
I didn't find any easy NSArchive reader available on NPM
But since it's a JSON-like data type (i.e., not compressed/encrypted and delimited somewhat), this hacky method works perfectly. Haven't had any issues in production lol