render media in facebook static site

[redshiftzero]

@micahflee noted in this comment:

The FB static archive site does not embed media yet. (It successfully downloaded a video I posted to the media folder, but it's video isn't included in assets/archive.json and it's not embedded in the archive site.)

The functionality for rendering the media/video on the static site was included in the webapp in #405, but I think this query when building the archive is the culprit for why media didn't show up

[redshiftzero]

I did some testing of this today, and I'm unable to reproduce this issue.
When I use an export with both videos and images, I see in assets/archive.js the media entries, and the expected data is in the static site:

Can you confirm you see this on latest main @micahflee ?

[micahflee]

I'm reproducing it. I spent some time trying to debug this and haven't yet succeeded, so I thought you might want to give it a shot.

Here's my sqlite database: data.sqlite3.zip

I've added the following log line to archiveBuild:

    async archiveBuild() {
        if (!this.db) {
            this.initDB();
        }

        if (!this.account) {
            return false;
        }

        log.info("FacebookAccountController.archiveBuild: building archive");
        // Posts with optional media
        const postsFromDb = exec(
            this.db,
            `SELECT
                p.*,
                CASE
                    WHEN pm.mediaId IS NOT NULL
                        THEN GROUP_CONCAT(
                            json_object(
                                'mediaId', pm.mediaId,
                                'postId', pm.postId,
                                'type', pm.type,
                                'uri', pm.uri,
                                'description', pm.description,
                                'createdAt', pm.createdAt,
                                'addedToDatabaseAt', pm.addedToDatabaseAt
                            )
                        )
                        ELSE NULL
                    END as media,
                CASE
                    WHEN pu.url IS NOT NULL
                        THEN GROUP_CONCAT(pu.url)
                        ELSE NULL
                    END as urls
                FROM post p
                LEFT JOIN post_media pm ON p.postID = pm.postId
                LEFT JOIN post_url pu ON p.postID = pu.postId
                GROUP BY p.postID
                ORDER BY p.createdAt DESC`,
            [],
            "all"
        );
        // Transform into FacebookPostWithMedia
        const posts: FacebookPostWithMedia[] = (postsFromDb as Array<FacebookPostRow & { media?: string, urls?: string[] }>).map((post) => ({
            ...post,
            media: post.media ? JSON.parse(`[${post.media}]`) : undefined,
        }));
        log.info(`FacebookAccountController.archiveBuild: posts from DB`, JSON.stringify(posts));

Here's the output of that SQL query, exported as JSON, when I run it manually:

[
  {
    "id": 140,
    "postID": "1741055617",
    "createdAt": "2025-03-04T02:33:37.000Z",
    "title": "Chase Westbrook added 3 new photos.",
    "text": "photography is sweet",
    "isReposted": "0",
    "addedToDatabaseAt": "2025-03-25T21:51:49.551Z",
    "media": "{\"mediaId\":\"1741055617_481073816_122113895492759940_3122657736492251322_n_122113895486759940.jpg\",\"postId\":\"1741055617\",\"type\":\"photo\",\"uri\":\"481073816_122113895492759940_3122657736492251322_n_122113895486759940.jpg\",\"description\":null,\"createdAt\":\"2025-03-04T02:33:30.000Z\",\"addedToDatabaseAt\":\"2025-03-25T21:51:49.561Z\"},{\"mediaId\":\"1741055617_481272552_122113895360759940_3857960351418550625_n_122113895354759940.jpg\",\"postId\":\"1741055617\",\"type\":\"photo\",\"uri\":\"481272552_122113895360759940_3857960351418550625_n_122113895354759940.jpg\",\"description\":null,\"createdAt\":\"2025-03-04T02:33:00.000Z\",\"addedToDatabaseAt\":\"2025-03-25T21:51:49.560Z\"},{\"mediaId\":\"1741055617_481459574_122113895456759940_7026353546115949084_n_122113895450759940.jpg\",\"postId\":\"1741055617\",\"type\":\"photo\",\"uri\":\"481459574_122113895456759940_7026353546115949084_n_122113895450759940.jpg\",\"description\":null,\"createdAt\":\"2025-03-04T02:33:30.000Z\",\"addedToDatabaseAt\":\"2025-03-25T21:51:49.562Z\"}",
    "urls": null
  },
  {
    "id": 141,
    "postID": "1741055490",
    "createdAt": "2025-03-04T02:31:30.000Z",
    "title": "Chase Westbrook shared a post.",
    "text": "what a reel",
    "isReposted": "1",
    "addedToDatabaseAt": "2025-03-25T21:51:49.552Z",
    "media": null,
    "urls": null
  },
  {
    "id": 142,
    "postID": "1741055318",
    "createdAt": "2025-03-04T02:28:38.000Z",
    "title": "Chase Westbrook shared a post.",
    "text": "nice",
    "isReposted": "1",
    "addedToDatabaseAt": "2025-03-25T21:51:49.552Z",
    "media": null,
    "urls": null
  },
  {
    "id": 143,
    "postID": "1740775154",
    "createdAt": "2025-02-28T20:39:14.000Z",
    "title": "Chase Westbrook added a new video.",
    "text": "lol",
    "isReposted": "0",
    "addedToDatabaseAt": "2025-03-25T21:51:49.552Z",
    "media": "{\"mediaId\":\"1740775154_AQPbn31jG6zwAsbUDJC1n24seGezJoS1gjJwWtdl2mXc5xfWLJHFJUeCMwlni7i2XfHK427hQvOW1zbz0oe9fmFY_971815314581705.mp4\",\"postId\":\"1740775154\",\"type\":\"video\",\"uri\":\"AQPbn31jG6zwAsbUDJC1n24seGezJoS1gjJwWtdl2mXc5xfWLJHFJUeCMwlni7i2XfHK427hQvOW1zbz0oe9fmFY_971815314581705.mp4\",\"description\":\"lol\",\"createdAt\":\"2025-02-28T20:39:08.000Z\",\"addedToDatabaseAt\":\"2025-03-25T21:51:49.561Z\"}",
    "urls": null
  },
  {
    "id": 144,
    "postID": "1740775067",
    "createdAt": "2025-02-28T20:37:47.000Z",
    "title": "Chase Westbrook shared a link.",
    "text": "One of the best wikis around: https://stardewvalleywiki.com/Stardew_Valley_Wiki\n\nAnd the store to boycott: https://stardewvalleywiki.com/JojaMart",
    "isReposted": "0",
    "addedToDatabaseAt": "2025-03-25T21:51:49.553Z",
    "media": null,
    "urls": "https://stardewvalleywiki.com/JojaMart"
  },
  {
    "id": 145,
    "postID": "1739411411",
    "createdAt": "2025-02-13T01:50:11.000Z",
    "title": "Chase Westbrook shared a link.",
    "text": "These shows look great, especially Newtopia https://www.themarysue.com/new-television-shows-streaming-february-2025/",
    "isReposted": "0",
    "addedToDatabaseAt": "2025-03-25T21:51:49.554Z",
    "media": null,
    "urls": "https://www.themarysue.com/new-television-shows-streaming-february-2025/"
  },
  {
    "id": 146,
    "postID": "1739410166",
    "createdAt": "2025-02-13T01:29:26.000Z",
    "title": "Chase Westbrook updated his status.",
    "text": "Looking forward to the annual Oregon Outdoor Festival this weekend! It�s a great opportunity to connect with fellow outdoor enthusiasts and learn about local conservation efforts. Hope to see some of you there!",
    "isReposted": "0",
    "addedToDatabaseAt": "2025-03-25T21:51:49.555Z",
    "media": null,
    "urls": null
  },
  {
    "id": 147,
    "postID": "1739410141",
    "createdAt": "2025-02-13T01:29:01.000Z",
    "title": "Chase Westbrook updated his status.",
    "text": "Just finished reading 'The Martian' by Andy Weir. What a thrilling ride! �� If you love science fiction with a touch of humor, this one�s a must-read. What�s on your reading list?",
    "isReposted": "0",
    "addedToDatabaseAt": "2025-03-25T21:51:49.555Z",
    "media": null,
    "urls": null
  },
  {
    "id": 148,
    "postID": "1739410065",
    "createdAt": "2025-02-13T01:27:45.000Z",
    "title": "Chase Westbrook updated his status.",
    "text": "Hey this is my new Facebook account. I'm just trying it out. I like scifi and fantasy.",
    "isReposted": "0",
    "addedToDatabaseAt": "2025-03-25T21:51:49.556Z",
    "media": null,
    "urls": null
  },
  {
    "id": 149,
    "postID": "1739409842",
    "createdAt": "2025-02-13T01:24:02.000Z",
    "title": "Chase Westbrook added a life event from February 12, 1983: Born on February 12, 1983.",
    "text": "Born on February 12, 1983 (2/12/1983)",
    "isReposted": "0",
    "addedToDatabaseAt": "2025-03-25T21:51:49.556Z",
    "media": null,
    "urls": null
  }
]

Note that in the "photography is sweet" post (the top one, with postID 1741055617) lists three separate images in the media string.

And now, here is my debug log:

14:51:49.559 › FacebookAccountController.archiveBuild: posts from DB [{"id":140,"postID":"1741055617","createdAt":"2025-03-04T02:33:37.000Z","title":"Chase Westbrook added 3 new photos.","text":"photography is sweet","isReposted":0,"addedToDatabaseAt":"2025-03-25T21:51:49.551Z","urls":null},{"id":141,"postID":"1741055490","createdAt":"2025-03-04T02:31:30.000Z","title":"Chase Westbrook shared a post.","text":"what a reel","isReposted":1,"addedToDatabaseAt":"2025-03-25T21:51:49.552Z","urls":null},{"id":142,"postID":"1741055318","createdAt":"2025-03-04T02:28:38.000Z","title":"Chase Westbrook shared a post.","text":"nice","isReposted":1,"addedToDatabaseAt":"2025-03-25T21:51:49.552Z","urls":null},{"id":143,"postID":"1740775154","createdAt":"2025-02-28T20:39:14.000Z","title":"Chase Westbrook added a new video.","text":"lol","isReposted":0,"addedToDatabaseAt":"2025-03-25T21:51:49.552Z","urls":null},{"id":144,"postID":"1740775067","createdAt":"2025-02-28T20:37:47.000Z","title":"Chase Westbrook shared a link.","text":"One of the best wikis around: https://stardewvalleywiki.com/Stardew_Valley_Wiki\n\nAnd the store to boycott: https://stardewvalleywiki.com/JojaMart","isReposted":0,"addedToDatabaseAt":"2025-03-25T21:51:49.553Z","urls":"https://stardewvalleywiki.com/JojaMart"},{"id":145,"postID":"1739411411","createdAt":"2025-02-13T01:50:11.000Z","title":"Chase Westbrook shared a link.","text":"These shows look great, especially Newtopia https://www.themarysue.com/new-television-shows-streaming-february-2025/","isReposted":0,"addedToDatabaseAt":"2025-03-25T21:51:49.554Z","urls":"https://www.themarysue.com/new-television-shows-streaming-february-2025/"},{"id":146,"postID":"1739410166","createdAt":"2025-02-13T01:29:26.000Z","title":"Chase Westbrook updated his status.","text":"Looking forward to the annual Oregon Outdoor Festival this weekend! Itâ��s a great opportunity to connect with fellow outdoor enthusiasts and learn about local conservation efforts. Hope to see some of you there!","isReposted":0,"addedToDatabaseAt":"2025-03-25T21:51:49.555Z","urls":null},{"id":147,"postID":"1739410141","createdAt":"2025-02-13T01:29:01.000Z","title":"Chase Westbrook updated his status.","text":"Just finished reading 'The Martian' by Andy Weir. What a thrilling ride! ð���ð��� If you love science fiction with a touch of humor, this oneâ��s a must-read. Whatâ��s on your reading list?","isReposted":0,"addedToDatabaseAt":"2025-03-25T21:51:49.555Z","urls":null},{"id":148,"postID":"1739410065","createdAt":"2025-02-13T01:27:45.000Z","title":"Chase Westbrook updated his status.","text":"Hey this is my new Facebook account. I'm just trying it out. I like scifi and fantasy.","isReposted":0,"addedToDatabaseAt":"2025-03-25T21:51:49.556Z","urls":null},{"id":149,"postID":"1739409842","createdAt":"2025-02-13T01:24:02.000Z","title":"Chase Westbrook added a life event from February 12, 1983: Born on February 12, 1983.","text":"Born on February 12, 1983 (2/12/1983)","isReposted":0,"addedToDatabaseAt":"2025-03-25T21:51:49.556Z","urls":null}]

Here's the same JSON but readable:

[
    {
        "id": 140,
        "postID": "1741055617",
        "createdAt": "2025-03-04T02:33:37.000Z",
        "title": "Chase Westbrook added 3 new photos.",
        "text": "photography is sweet",
        "isReposted": 0,
        "addedToDatabaseAt": "2025-03-25T21:51:49.551Z",
        "urls": null
    },
    {
        "id": 141,
        "postID": "1741055490",
        "createdAt": "2025-03-04T02:31:30.000Z",
        "title": "Chase Westbrook shared a post.",
        "text": "what a reel",
        "isReposted": 1,
        "addedToDatabaseAt": "2025-03-25T21:51:49.552Z",
        "urls": null
    },
    {
        "id": 142,
        "postID": "1741055318",
        "createdAt": "2025-03-04T02:28:38.000Z",
        "title": "Chase Westbrook shared a post.",
        "text": "nice",
        "isReposted": 1,
        "addedToDatabaseAt": "2025-03-25T21:51:49.552Z",
        "urls": null
    },
    {
        "id": 143,
        "postID": "1740775154",
        "createdAt": "2025-02-28T20:39:14.000Z",
        "title": "Chase Westbrook added a new video.",
        "text": "lol",
        "isReposted": 0,
        "addedToDatabaseAt": "2025-03-25T21:51:49.552Z",
        "urls": null
    },
    {
        "id": 144,
        "postID": "1740775067",
        "createdAt": "2025-02-28T20:37:47.000Z",
        "title": "Chase Westbrook shared a link.",
        "text": "One of the best wikis around: https://stardewvalleywiki.com/Stardew_Valley_Wiki\n\nAnd the store to boycott: https://stardewvalleywiki.com/JojaMart",
        "isReposted": 0,
        "addedToDatabaseAt": "2025-03-25T21:51:49.553Z",
        "urls": "https://stardewvalleywiki.com/JojaMart"
    },
    {
        "id": 145,
        "postID": "1739411411",
        "createdAt": "2025-02-13T01:50:11.000Z",
        "title": "Chase Westbrook shared a link.",
        "text": "These shows look great, especially Newtopia https://www.themarysue.com/new-television-shows-streaming-february-2025/",
        "isReposted": 0,
        "addedToDatabaseAt": "2025-03-25T21:51:49.554Z",
        "urls": "https://www.themarysue.com/new-television-shows-streaming-february-2025/"
    },
    {
        "id": 146,
        "postID": "1739410166",
        "createdAt": "2025-02-13T01:29:26.000Z",
        "title": "Chase Westbrook updated his status.",
        "text": "Looking forward to the annual Oregon Outdoor Festival this weekend! It�s a great opportunity to connect with fellow outdoor enthusiasts and learn about local conservation efforts. Hope to see some of you there!",
        "isReposted": 0,
        "addedToDatabaseAt": "2025-03-25T21:51:49.555Z",
        "urls": null
    },
    {
        "id": 147,
        "postID": "1739410141",
        "createdAt": "2025-02-13T01:29:01.000Z",
        "title": "Chase Westbrook updated his status.",
        "text": "Just finished reading 'The Martian' by Andy Weir. What a thrilling ride! �� If you love science fiction with a touch of humor, this one�s a must-read. What�s on your reading list?",
        "isReposted": 0,
        "addedToDatabaseAt": "2025-03-25T21:51:49.555Z",
        "urls": null
    },
    {
        "id": 148,
        "postID": "1739410065",
        "createdAt": "2025-02-13T01:27:45.000Z",
        "title": "Chase Westbrook updated his status.",
        "text": "Hey this is my new Facebook account. I'm just trying it out. I like scifi and fantasy.",
        "isReposted": 0,
        "addedToDatabaseAt": "2025-03-25T21:51:49.556Z",
        "urls": null
    },
    {
        "id": 149,
        "postID": "1739409842",
        "createdAt": "2025-02-13T01:24:02.000Z",
        "title": "Chase Westbrook added a life event from February 12, 1983: Born on February 12, 1983.",
        "text": "Born on February 12, 1983 (2/12/1983)",
        "isReposted": 0,
        "addedToDatabaseAt": "2025-03-25T21:51:49.556Z",
        "urls": null
    }
]

As you can see, the media is missing from all posts. The "photography is sweet" should have three images and the "lol" post should have one video.