Forum MetaData Deep Dive

When FinalFantasyFanatic.com's forum was backed up on November 20th 2002, the copy included far more than the posts. Ikonboard 3.0.2a kept about two dozen side tables recording who was online, what got deleted, who moderated what, what people searched for, and how the board was configured. None of it was meant to be read. All of it survived.

This is what came out of those tables.

THREE PEOPLE WERE ONLINE WHEN THE LIGHTS WENT OUT

The who's-online table is a live scratch file, overwritten constantly. What survives is a single frozen moment: whoever happened to be logged in at the instant the backup ran.

Sage Winard   Moderator       04:36:55 UTC   Windows XP, IE6   YeD's Forum of Secret Stuff, topic 25
Lord Arkmam   Moderator       06:17:13 UTC   Windows Me, IE6   YeD's Forum of Secret Stuff, topic 25
FieRcE YeD    Administrator   06:24:10 UTC   Windows XP, IE6   (just logged in)

Two moderators reading the same thread in the admin-only forum, and the administrator arriving seconds before the snapshot with no page loaded yet. One of them was still on Windows Me.

The session tokens are not hashed. Ikonboard 3.0.2a builds them by packing the values and printing them as hex, with no digest applied, so the first eight characters are the login time in plain sight:

3ddb0d6f 5c4d eae3   ->  time 1037765999  PID 23629  rand 60131  =  11/20/2002 04:19:59 UTC
3ddb2a8a b0a5 da91   ->  time 1037773450  PID 45221  rand 55953  =  11/20/2002 06:24:10 UTC
3ddb2472 5a7a d9d3   ->  time 1037771890  PID 23162  rand 55763  =  11/20/2002 05:58:10 UTC

Two match the table's own login field exactly. The third is one second earlier, the gap between minting the token and writing the row. The unpredictable part is a 16-bit process ID and a 16-bit random, and the random is drawn from a range of six hundred billion before being truncated to those 16 bits.

A COMPLETE LEDGER OF EVERYTHING DELETED

The board kept a deletion log: 716 entries between June 16th and November 18th 2002, each naming the table, the forum, the record and the timestamp.

message_data (private messages)   467
forum_posts                        94
search_log                         90
forum_topics                       54
forum_poll_voters                   6
forum_polls                         2
forum_moderators                    2
forum_info                          1

All 54 logged topic deletions are absent from the recovered data, with no false positives. The gaps in surviving topic ID sequences line up almost exactly:

Role Playing          6 gaps    6 logged deletions
Role Play Battling    3 gaps    3 logged
Enlightening Minds    1 gap     1 logged
Final Fantasy Chat    1 gap     1 logged
General Discussion    7 gaps    5 logged

General Discussion's two extra gaps predate June 16th, where the log begins. The 467 private-message deletions are members clearing their own inboxes, not moderation.

WHY THE PROMOTE IT FORUM IS EMPTY

The recovery notes had always recorded Promote It as unrecoverable: no posts, no subfolder on disk. The log explains it.

11/17/2002 22:05:44   36 topics deleted from forum 10
11/17/2002 22:05:45   forum_info FORUM_ID=10 deleted

One second apart. The forum was deleted outright three days before the backup. Its topic IDs ran 1 to 37 with no gaps, so it was intact right up to the moment it was wiped. Deleting a forum from the admin panel cascades to its topics without writing moderator-log entries, which is why 35 of the 54 deleted topics have no moderator attached to them.

Not a recovery failure. A deliberate deletion, missed by four days.

NINETEEN DELETED THREADS, RECOVERED BY NAME

The moderator log records the title of every thread acted on. Cross-referencing it against the deletion ledger puts names back on nineteen threads whose contents are gone.

07/21/2002  Promote It           Funny Pictures
08/01/2002  General Discussion   Gone
08/03/2002  Enlightening Minds   New Editorials
08/11/2002  General Discussion   gone!
08/16/2002  General Discussion   Results for FFF Awards: August Edition.
08/26/2002  Role Play Battling   The Spiriter's Challenge
08/26/2002  Role Play Battling   World Tournament
09/01/2002  Role Playing         RPing Rules
09/01/2002  Role Playing         Rage Against the Machine
09/01/2002  Role Playing         The Fate
09/10/2002  Role Playing         The Godhand
09/10/2002  Role Playing         The Soulless
09/20/2002  Role Playing         The Revenge
09/22/2002  Final Fantasy Chat   FF XI
10/07/2002  General Discussion   FFFOC "Beta Test"
10/21/2002  Role Play Battling   Just want to set a little bit of rules....
10/24/2002  General Discussion   new icons and question
10/24/2002  Secret Stuff         CHAT 2PM CST OCTOBER 13
11/17/2002  Promote It           heh

One member's September run through Role Playing reads as a deliberate cleanup: The Fate, The Godhand, The Soulless, The Revenge, all inside three weeks.

The content itself is not recoverable. Six orphaned posts exist across the entire archive, in one thread, none matching a logged deletion. Ikonboard removed posts along with their topics.

WHO ACTUALLY MODERATED

One hundred logged moderator actions, and who took them.

Closed a topic     37      FieRcE YeD               30
Deleted a post     31      Romi Seneca              23
Deleted a topic    18      RTV King                 15
Moved a topic       6      Sage Winard              11
Edited a topic      5      Lord Arkmam               9
Opened a topic      2      Cloud-FFF                 4
Rebuilt a topic     1      Hobbes Lionheart          4
                           Razz                      3
                           Heroman-X-Zero-Protoman   1

SIX MONTHS OF A BOARD, IN FIVE NUMBERS

June begins on the 14th and November ends on the 20th, so both are partial months.

Month     Posts  Topics   PMs  Searches  Deletions
2002-06     890      51    48       857          9
2002-07     429      33    37       473         15
2002-08     609      33    61       652         69
2002-09     830      59    85     1,262        264
2002-10     994      40   227     1,383        293
2002-11     382      23    89       520         66
Total     4,134     239   547     5,147        716

October is the peak on every measure except topics started, and July is the only real trough. The standout is private messages: posts rose 20% from September to October, while private messages rose 167%. Public posting grew. Private conversation nearly tripled.

5,147 SEARCHES, AND NOBODY EVER SEARCHED FOR A PERSON

The search log kept every query: 5,147 of them, but only 305 distinct terms.

The first search ever run on the rebuilt board was the administrator looking for last time for ikonboard, at 20:33 on June 14th 2002, twenty-four minutes after he registered the new board, looking for the thread about abandoning the old one.

Classifying all 305 terms against thread titles and member names:

Exact thread title      231 terms    4,364 searches  (85%)
Partial thread title      5 terms       58 searches
Member name               0 terms        0 searches
Everything else          69 terms      725 searches

Nobody ever searched for a person. Not once. Search was used exclusively to reach threads: the board surfaced no bookmarks, so the search box was the jump list. Every top query is a thread title.

advanced person above/below you thread    269
game abc                                  236
spam thread part ii                       168
quostest of the mostest                   141
ff6 trivia                                126
the what are you wearing today thread     125
the power of five                         109

Thirty-nine thread titles survive only here. Of the 69 non-matching terms, 27 are threads in the private forums and 3 are already named in the moderator log. The other 39 match no recovered thread anywhere, but 37 of them were searched by more than one member in tight clusters lasting days or weeks, which is what a live thread looks like in a log rather than a content search.

Query                    Searches  Searchers  Active
fffrpg                         67         10  10/06 - 10/13/2002
the love kitten!               24          8  08/31 - 09/27/2002
help                           16          7  07/21 - 08/05/2002
proof of evil                  14          5  10/26 - 11/03/2002
ff 1 parody                    12          7  06/30 - 07/19/2002
ffx-2 propaganda               12          5  10/20 - 10/24/2002
final fantasy quiz             11          7  10/13 - 10/23/2002
the nerd test!                 10          8  09/24 - 10/03/2002
in case you forget...           7          7  09/26 - 09/29/2002

A dozen are unmistakably Promote It: my web site, really cool site, check this out, here is my crummy site, free promotion for yed, all your base inofficial site, got your music needs right here..., flash movies, x-men vs street fighter online, red alert 2. That forum's 37 topics were wiped and only two were ever named. The search log names roughly a dozen more.

The rest group by character: editorials (proof of evil, tolken-agent of satan?, the bile, hate literature?), the quiz and test fad of September and October (the nerd test!, natural wonders test, the lotr test!), and the fan-game project. fffrpg drew 67 searches from 10 different members inside a single week, making it the most-searched thing in the entire archive that no longer exists.

These are titles inferred from search behavior, not recovered records. A term could be a content search that reads like a title, or a thread later renamed. None of the 39 can be tied to a topic ID.

WHO THIS COMMUNITY WAS

The table labeled "calendar" is not a calendar. Its columns are day, month and year, and it holds birthdays. Twenty-two of the 48 members supplied a real one.

Birth years            1983 - 1989
Age in 2002            youngest 13, median 16, oldest 19

Age in 2002    13  14  15  16  17  18  19
Members         3   3   4   6   2   1   3

POLLS, AND VOTES THAT OUTLIVED THEM

Twenty-six poll records across 14 distinct polls, and 83 votes from 21 members cast between June 20th and November 11th 2002. Ten of the fourteen polls drew at least one vote.

But four poll IDs in the vote table have no surviving poll record at all. The votes outlived the polls they belonged to, which fits the deletion ledger, where two polls and six vote records were deleted. The best-attended poll drew 15 votes against a 48-member board.

APPOINTED VERSUS ACTIVE

Cross-referencing who held moderator powers against who used them.

Romi Seneca         1 forum     23 actions
RTV King            2 forums    15 actions
Sage Winard         1 forum     11 actions
Lord Arkmam         1 forum      9 actions
Hobbes Lionheart    1 forum      4 actions
Razz                2 forums     3 actions
Highlander          1 forum      0 actions

Highlander never used his moderator powers once, despite being the single most active member on the board by both posts (624) and searches (775).

A smaller detail: the private-message table stores each member's folder layout. Twenty-two used the defaults. Two did not. One renamed their inbox to Halo's Temple and their sent box to Portal to Corneria; another kept folders called keepers and funnys.

TWO SOCIAL GRAPHS THAT DISAGREE

The board recorded who people wrote to, and separately who they chose to list in their address book. The two describe different communities.

547 private messages across 60 distinct pairs, which is sparse for 48 members, and concentrated on the administrators.

FieRcE YeD        <-> RTV King              29
FieRcE YeD        <-> Hobbes Lionheart      27
Romku Tetsulian   <-> Sage Winard           26
Razz              <-> Romku Tetsulian       24
Hobbes Lionheart  <-> Johnny Bravo          22
Highlander        <-> Lord Arkmam           17

Address books tell a different story. Only 12 members kept one at all, listing 19 people between them, but 44 of the 47 entries carry a label the owner wrote by hand.

Lord Arkmam                -> RTV King         "Allmighty Leader"
RTV King                   -> FieRcE YeD       "This is yed"
Highlander                 -> Sage Winard      "My Big Bro'"
Highlander                 -> JCD              "Ondskan sj..."
Heroman-X-Zero-Protoman    -> Cloud-FFF        "Best Friend"
Heroman-X-Zero-Protoman    -> Sage Winard      "Administrator"
Cloud-FFF                  -> Romku Tetsulian  "pal"

Private messages centre on whoever ran the board. Address books centre on friendships. People wrote to the administrators, and listed the people they liked.

A MEETING THAT WAS DELETED, RECONSTRUCTED FROM ITS EDGES

One of the nineteen deleted threads is CHAT 2PM CST OCTOBER 13, removed from the admin-only forum on October 24th 2002. The thread itself is gone: the private forum has topics 1 to 14 and 16 to 26, a clean hole where 15 used to be, with no orphaned posts.

But the threads either side of it survived, and between them they reconstruct the whole event. First, the planning.

10/09  Hobbes Lionheart  We should include Razz in our ideas. He is capable of making sprites.
10/09  FieRcE YeD        already informed him of meeting, going to give him time when we
                         set up the time.
10/11  Hobbes Lionheart  This saturday is not good for me but Sunday should be okay
10/11  RTV King          2pm cst sunday will be the meeting, I will have the fff link to
                         the chat in this thread (oh by the way this is yed on rtvs comp)
                         ps rtv is a loser
10/12  Highlander        2pm Sunday huh? See what time that'll be in Sweden *checks*
                         Edit: 2pm should be around 10pm if I get this right..
                         Where will this meeting be by the way?

October 13th 2002 was a Sunday, and the board's own configuration confirms its clock ran on CST. The deleted thread was created next, to carry the chat link, which is exactly why it was removed eleven days later once it was stale.

The chat ran in a Java applet chat room hosted on the site. That page survives in the backup.

<applet codebase="http://www.finalfantasyfanatic.com/cgi-image/vcclient"
        archive="COM/volano/MyVolanoChat.jar"
        code="COM.volano.MyVolanoChat.class" width="500" height="100">

Then the aftermath, in the thread that followed.

10/13 23:34  Hobbes Lionheart  I am the head story writer with Arkmam and Highlander
                               as my assistant story writers.
10/14 03:18  Sage Winard       I am so deeply sorry for missing this chat, It was 2:50 my
                               time and the chat was to be at one pm. I got on and no one
                               was there -.- Can we get a review of the chat here?
10/14 15:05  Lord Arkmam       I copied the whole talk down to my hard drive, I can mail
                               it to ya.

The meeting assigned the writing roles for a fan game the group was building in RPG Maker 2000, a hero collecting crystal shards across the Final Fantasy universes. The thread that followed runs 41 replies working out the story.

A full transcript of the chat was made and offered around by email. It has not been found.