Table Schema (FFS) Here is an annotated list of tables in our current schema. There are some holes and inconsistencies in this data due to the rapid changes we had to make as we scaled our application. In particular, some tables may only be available for certain date ranges, and in other cases, data from one table is continued in another. In general, no Facebook demographics / user data is stored as per the terms of service. The interesting data here includes FFS user activity over time, and the complete history of pets being bought and sold. The less interesting data is probably the viral/referral data, as that is very incomplete as we have had to shut down metrics and we are still working on scaling that out. categories - This is a small table describing categories for our Gifts (i.e. Foods, Toys, etc) | name | The name of the gift category | description | The description of the gift category claimed_prizes - Periodically, we issue fixed rewards in our emails. This is a table of all claimed prizes | user_id | The id of the user who claimed the prize | prize_id | The id of the prize being claimed | user_id_prize_id | Composite key of the two for efficiency comments - This contains on comments left on user profile walls. | commentable_id | The id of the object being commented upon. This is just the user id | commentable_type | The type of object being commented upon. This is always 'User' | user_id | The id of the user who made the comment | comment | The text of the comment gifts - This is a small table containing the Gifts available in our application. | name | The name of the gift | category_id | The category this gift belongs to | path | The path to the gift's image | value | The cost of this gift | sponsored | Is this a sponsored gift? | featured | Show this gift in the featured section of the gift shop? | sponsored_by | Who sponsored this gift, if any | sponsored_url | The click thru link to a sponsor | hidden | Whether to hide this gift from the gift shop giftships - This contains every instance of a gift ever sent out. | gift_id | The id of the sent gift | giver_id | The user id of the gift giver | receiver_id | The user id of the gift receiver invitations - This used to contains records for invitations sent out via purchases, gifts, and pokes. (Up until two weeks ago, invitations were sent out to uninstalled users when they were bought. Now notifications are sent out instead). | copy | The full text of the invitation. We no longer store this, I think. | version | An id of the version of this invitation, used for a/b testing | type | The type of invitation. PurchaseInvitation, PokeInvitation, GiftInvitation. invites - This contains a record of mass invites sent via the mass invitation screen. | count | The number of friends this invite was sent to listings - This contains on instances of a pet being put on sale in the on sale section. | seller_id | The user id of the user selling the pet | pet_id | The user id of the pet being put on sale | value | The value of the pet when they were put on sale. logins - This contains a record of every login by a user. A login is defined as any interaction with the application on a four hour interval. | user_id | The user logging in | source | Where the user came from to login (not currently being tracked due to performance issues) mini_feed_items - This contains a record of every Facebook minifeed (not FFS minifeed) news stories we publish on behalf of a user. | copy | The full text of the minifeed item | version | The version of the minifeed story, for a/b testing purposes network_memberships - This contains user to Facebook Network relationships. | user_id | The id of a user | network_id | The id of the user's network networks - This contains a list of all networks in Facebook, by network id. | facebook_nid | id of the network from Facebook | name | English name of the network news_feed_item - This contains a record of every Facebook newsfeed (not FFS newsfeed) news stories we publish on behalf of a user. | copy | The full text of the newsfeed item | version | The version of the newsfeed story, for a/b testing purposes news_stories - This contains all the items in the FFS news feed and mini feeds ( not Facebook newsfeed and mini feeds ). | actor_id | The id of the object that initiated the event | actor_type | The type of object that initiated the event (usually a User) | event_id | The id of the actual event | event_type | The type of event (e.g. Login, Purchase, Poke, Reward, etc) | actee_id | The id of the object being acted upon | actee_type | The type of object being acted up on (e.g. another User | extra_id | The id of any 'extra' or mediating object | extra_type | The type of the mediating object. | news_feed_only | Only show this event on the FFS newsfeed (and not the minifeed)? | user_id | The id of the user this news story belongs to notifications - This contains a record for all Facebook notifications we publish on behalf of a user. | copy | The full text of the notification | version | The version of the the notification for a/b testing | notifiable_type | The type of event this notification refers to (Purchase, Poke, Comment, etc) | notifiable_id | The id of the event the notification refers to | user_id | The id of the user this notification was sent to offers - This contains all rewards we give to users in exchange for filling out Cost-Per-Action offers. | network_id | The id of the network we are using | offer_id | The id of the offer | user_id | The id of the user | status | The status of the offer. Was it completed | value | The value of the offer old_news_storieS - This is an archived version of the news_stories table. We archive the table when it gets too big. See news_stories for the schema. one_news_stories - This is the most recent archive of the news_stories table. See news_stories for the schema. ownerships - This is a record of all instances of 'ownerships', i.e. whenever a user has ever owned another user. | user_id | The id of the owning user | pet_id | The id of the pet | nickname | The nickname for the pet at that time | current | Is this ownership the current ownership or a historical one? | previous_ownership_id | This points to the last ownership | released | Was the pet set free during this ownership? pokes - This records the instances of all pokes ever done. | poker_id | The id of the user initiating the poke | pokee_id | The id of the user being poked | verb_id | The type of poke being done | mediator_id | The pet being used to poke, if any prizes - This is a small table describing the fixed rewards we periodically make available. | value | The value of the prize purchases - This is a table describing all instances of 'purchases'. A 'purchase' may be a gift, or a person | tax | The tax on this purchase | value | The value of this purchase | premium | The premium over the existing value the user had to pay for this purchase | buyer_id | The user id of the buyer | receiver_id | The user id of the receiver, in case of a gift | purchase_item_id | The id of the item that was purchased | purchase_item_type | The type of item being purchased (User, Gift) referrals - This is a general table which keep tracks of all outgoing potential referral events and data about them, including conversions. Unfortunately, we turned off conversion tracking about a month after launch. | referrer_id | id of the user doing the referring | referree_id | id of the user this referral was sent to | clicked_at | time the referree clicked on the referral | converted_at | time the referree converted into a user | viral_event_type | type of event that generated this referral | viral_event_id | id of the event that generated this referral | tracking_id | a UUID for this referral event rewards - This is a table describing all instances where we inject money in to the system. | user_id | id of the user being rewarded | value | Value of the reward | rewardable_type | the type of the event which resulted in this reward (Login, Offer, Prize, etc) | rewardable_id | the id of the event which resulted in this reward users | - The master users table | active_at | Last time this user used the application | last_refreshed_at | Last time the user refreshed their profile FBML | sex | Sex in plain text | birthday | Birthdate as a time | cash | Cash on hand | value | Value | ownerships_count | Number of pets | description | Description in their user profile | last_logged_in_at | Last time the user logged in to the application (4 hour interval) | show_help | Hide / Show the help box | installed_at | Time of last install | uninstalled_at | Time of last uninstall | installed | Installed? True / false | nickname | Nickname | owner_facebook_uid | facebook uid of this user's owner, if any | owner_user_id | user id of this user's owner, if any | network_id_0 | 1st network | network_id_1 | 2nd network | network_id_2 | etc | network_id_3 | etc | network_id_4 | etc verbs | - A small table listing out all the poke types. | objective | Objective tense of the poke | past_tense | Past tense of the poke | future_tense | Future tense of the poke | text | Unused | image_path | Unused | value | Unused wishes | - This is a user-to-user list of all wishes made by a users to their wishlist. | wisher_id | Wisher user id | wishee_id | Wishee user id