Database structure

This page is automatically generated and can be manually generated with database scripts.

Introduction

This documentation is automatically generated by information from MySQL and the PHP code.

The documentation inform you as developer about the database structure and column meaning. Additional for the development of new API or backend parts, the module table usage (extracted from PHP codebase) below provides helpful information. The provided information may help to find already existing queries, gateways, and/or the best location for a new query/function.

• List of tables
• Structure of tables
• Usage of table in PHP Modules

List of tables

• fs_abholer
• fs_abholzeiten
• fs_achievement
• fs_answer
• fs_apitoken
• fs_application_has_wallpost
• fs_basket
• fs_basket_anfrage
• fs_basket_has_art
• fs_basket_has_types
• fs_basket_has_wallpost_abandoned
• fs_bell
• fs_betrieb
• fs_betrieb_has_lebensmittel
• fs_betrieb_kategorie
• fs_betrieb_notiz
• fs_betrieb_team
• fs_bezirk
• fs_bezirk_closure
• fs_bezirk_has_theme
• fs_bezirk_has_wallpost
• fs_blog_entry
• fs_botschafter
• fs_buddy
• fs_chain
• fs_contact
• fs_content
• fs_conversation
• fs_email_blacklist
• fs_email_bounces
• fs_email_status
• fs_event
• fs_event_has_wallpost
• fs_fairteiler
• fs_fairteiler_follower
• fs_fairteiler_has_wallpost
• fs_feature_toggles
• fs_fetchdate
• fs_fetchweight
• fs_foodsaver
• fs_foodsaver_archive
• fs_foodsaver_change_history
• fs_foodsaver_has_achievement
• fs_foodsaver_has_bell
• fs_foodsaver_has_bezirk
• fs_foodsaver_has_contact
• fs_foodsaver_has_conversation
• fs_foodsaver_has_event
• fs_foodsaver_has_options
• fs_foodsaver_has_poll
• fs_foodsaver_has_wallpost
• fs_ipblock
• fs_key_account_manager
• fs_lebensmittel
• fs_location
• fs_mailbox
• fs_mailbox_member
• fs_mailbox_message
• fs_mailchange
• fs_msg
• fs_pass_gen
• fs_pass_request
• fs_poll
• fs_poll_has_options
• fs_poll_option_has_value
• fs_post_reaction
• fs_push_notification_subscription
• fs_question
• fs_question_has_quiz
• fs_question_has_wallpost
• fs_quiz
• fs_quiz_session
• fs_rating
• fs_region_function
• fs_region_options
• fs_region_pin
• fs_report
• fs_report_has_wallpost
• fs_send_email
• fs_stat_abholmengen
• fs_store_has_wallpost
• fs_store_log
• fs_theme
• fs_theme_follower
• fs_theme_post
• fs_usernotes_has_wallpost
• fs_verify_history
• fs_wallpost
• phinxlog
• uploads

Structure of tables

Table fs_abholer

Description - fs_abholer

Stores filled pickup slots, describes by who fetches when, where and if confirmed, needed to generate statistics (count, but not weight).

Open todos from old documentation - fs_abholer

• Have deleted entries from fs_foodsaver and fs_betrieb reappear.
• Hide deleted fs_betrieb on existing map
• Don't care about that in existing code. Used to show pickup history, but broken old entries seem fine.
• Document: Add to database as comment (1: Confirmed, 0: Not confirmed)
• Clarify: Why is this an integer and not a boolean?

Table columns - fs_abholer

Column	Description	Type	properties
foodsaver_id		unsigned int(10)	Weak-foreign key (fs_foodsaver:id)
betrieb_id		unsigned int(10)	Weak-foreign key (fs_betrieb:id)
date		datetime
confirmed	1: Confirmed, 0: Not confirmed	unsigned int(4)
id		unsigned int(10)	Primary Key, Auto-Increment

Table fs_abholzeiten

Description - fs_abholzeiten

Contains information about regurlary reoccuring pickup slots.

Open todos from old documentation - fs_abholzeiten

• Document: Add to database as column (Dow) comment: Day of week (1=Monday, 0=Sunday)
• Document: Add to database as column (Time) comment: When on the day the pickup is
• Document: Add to database as column (Fetcher) comment: Number of slots (>= 0, >= 1 enforced by frontend)
• Clarify: Where are additional single pickup slots are stored?
• Remove all non-existent references to fs_betrieb, add ON DELETE CASCADE.
• Add foreign key relationship to fs_betrieb Reasoning: The data only affects future pickups that will not occur in case a store is removed.

Table columns - fs_abholzeiten

Column	Description	Type	properties
betrieb_id		unsigned int(10)	Primary Key, Weak-foreign key (fs_betrieb:id)
dow	Day of week (1=Monday, 0=Sunday)	unsigned int(4)	Primary Key
time	Time when on the day the pickup is	time=00:00:00	Primary Key
fetcher	Number of slots (> 0, limited by frontend currently 8)	unsigned int(4)=4
description	optional description for this pickup time	varchar(100)	Nullable

Table fs_achievement

Description - fs_achievement

Table columns - fs_achievement

Column	Description	Type	properties
id		int(11)	Primary Key, Auto-Increment
name		varchar(255)
description		varchar(255)
validity_in_days_after_assignment		int(11)	Nullable
is_requestable_by_foodsaver		int(1)
created_at		timestamp=current_timestamp()
updated_at		timestamp	Nullable

Table fs_answer

Description - fs_answer

Stores answers to quiz.

Open todos from old documentation - fs_answer

• Add foreign key relationship to fs_question as well as ON DELETE CASCADE. Reasoning: Answers to removed questions are not needed, existing quiz sessions get a copy in fs_quiz_session.quiz_result.

Table columns - fs_answer

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
question_id		unsigned int(10)	foreign key (fs_question:id)
text		text(16777215)	Nullable
explanation		text(16777215)
right		unsigned int(4)	Nullable

Table fs_apitoken

Description - fs_apitoken

User tokens for ICS/ICAL/WebCal access to calendar of future events/pickups.

Open todos from old documentation - fs_apitoken

• Remove tokens for not existing users
• add foreign key relationship to fs_foodsaver as well as ON DELETE CASCADE.

Table columns - fs_apitoken

Column	Description	Type	properties
foodsaver_id	Owner of the token and identifier for the calendar	unsigned int(10)	foreign key (fs_foodsaver:id)
token	Access token, hex-number from openssl.	varchar(255)

Table fs_application_has_wallpost

Description - fs_application_has_wallpost

Link table between applications (for groups) and the wallposts. Design broken: Links foodsaver ids to the wallposts, e.g. all applications a foodsaver does to somewhere

Open todos from old documentation - fs_application_has_wallpost

• Remove entries for non-existing users
• add ON DELETE CASCADE to fs_foodsaver.
• Remove entries for non-existing wallposts
• add ON DELETE CASCADE to fs_wallpost
• Reasoning: Applications texts for deleted users are not useful anymore

Table columns - fs_application_has_wallpost

Column	Description	Type	properties
application_id		unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
wallpost_id		unsigned int(10)	Primary Key, foreign key (fs_wallpost:id)

Table fs_basket

Description - fs_basket

Lists all foodbaskets.

Open todos from old documentation - fs_basket

• Check code to never display foodbaskets from removed users except to admins.
• Clarify: Meaning of status == Basket::Status
• Clarify: Meaning of fs_id
• Clarify: Meaning of appost
• Clarify: Meaning of contact_type
• Clarify: Meaning of location_type
• Clarify: Meaning of weight unit
• Clarify: Meaning of picture
• Clarify: Meaning of time
• Clarify: Meaning of update
• Clarify: Meaning of until
• Clarify: Meaning of fetchtime
• Clarify: Owner of tel
• Clarify: Owner of handy

Table columns - fs_basket

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
foodsaver_id		unsigned int(10)	Weak-foreign key (fs_foodsaver:id)
status	@Basket::Status	unsigned int(4)	Nullable
time	Creation datetime	datetime	Nullable
update		datetime	Nullable
until	Basket is present until datetime expires	datetime
fetchtime		datetime	Nullable
description		text(16777215)	Nullable
picture		varchar(150)	Nullable
tel		varchar(50)
handy		varchar(50)
contact_type		varchar(20)=1
location_type	Fix set to 0	unsigned int(4)	Nullable
weight	Smaller <0kg = 3kg	float	Nullable
lat	Basket location latitude	float
lon	Basket location longitude	float
bezirk_id		unsigned int(10)	Weak-foreign key (fs_bezirk:id)
fs_id		int(10)
appost		int(4)

Table fs_basket_anfrage

Description - fs_basket_anfrage

Lists foodbasket requests.

Open todos from old documentation - fs_basket_anfrage

• Remove entries for non-existing users
• add ON DELETE CASCADE to fs_foodsaver.
• Remove entries for non-existing baskets
• add ON DELETE CASCADE to fs_basket.
• Reasoning: Requests for non existant users do not need to be kept, basket FK is purely defensive.

Table columns - fs_basket_anfrage

Column	Description	Type	properties
foodsaver_id		unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
basket_id		unsigned int(10)	Primary Key, foreign key (fs_basket:id)
status		unsigned int(4)	Nullable
time		datetime
appost		int(4)

Table fs_basket_has_art

Description - fs_basket_has_art

Combines foodbaskets with different types of food. Unused: Has to be entered but is never evaluated.

Open todos from old documentation - fs_basket_has_art

• remove in code (in future), leave database as is (for now)

Table columns - fs_basket_has_art

Column	Description	Type	properties
basket_id		unsigned int(10)	Primary Key
art_id		unsigned int(10)	Primary Key

Table fs_basket_has_types

Description - fs_basket_has_types

See fs_basket_has_art

Table columns - fs_basket_has_types

Column	Description	Type	properties
basket_id		unsigned int(10)	Primary Key
types_id		unsigned int(10)	Primary Key

Table fs_basket_has_wallpost_abandoned

Description - fs_basket_has_wallpost_abandoned

Table columns - fs_basket_has_wallpost_abandoned

Column	Description	Type	properties
basket_id		unsigned int(10)	Primary Key, foreign key (fs_basket:id)
wallpost_id		unsigned int(10)	Primary Key, foreign key (fs_wallpost:id)

Table fs_bell

Description - fs_bell

Stores arbitrary notifications

Table columns - fs_bell

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
name		varchar(50)	Nullable
body		varchar(50)	Nullable
vars		text(16777215)	Nullable
attr		varchar(500)	Nullable
icon		varchar(150)	Nullable
identifier		varchar(40)	Nullable
time		datetime
closeable		unsigned int(4)=1
expiration		date	Nullable

Table fs_betrieb

Description - fs_betrieb

Stores stores.

Open todos from old documentation - fs_betrieb

• Conversation 108242 and 108243 missing (store 1, 2) (fixed, inserted)
• Link bezirk_id to fs_bezirk
• Care about kette_id, betrieb_kategorie_id (fixed with issue #862)

Table columns - fs_betrieb

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
betrieb_status_id	@Status::CooperationStatus	unsigned int(10)
bezirk_id		unsigned int(10)
added		date
plz		varchar(5)
stadt		varchar(50)
lat		varchar(20)	Nullable
lon		varchar(20)	Nullable
kette_id		unsigned int(10)	Weak-foreign key (fs_kette:id), Nullable
betrieb_kategorie_id		int(11)	Weak-foreign key (fs_betrieb_kategorie:id), Nullable
name		varchar(120)	Nullable
str		varchar(120)	Nullable
status_date		date	Nullable
status		unsigned int(4)	Nullable
ansprechpartner		varchar(60)	Nullable
telefon		varchar(50)	Nullable
fax		varchar(50)	Nullable
email		varchar(60)	Nullable
begin		date	Nullable
besonderheiten		text(16777215)	Nullable
public_info		varchar(200)	Nullable
public_time	@Store::PublicTimes	int(4)
ueberzeugungsarbeit	@Store::ConvinceStatus	int(4)
presse	Yes=0; No=1	int(4)
sticker	Yes=0; No=1	int(4)
abholmenge	See @WeightHelper	int(4)
team_status	0 = Team Voll; 1 = Es werden noch Helfer gesucht; 2 = Es werden dringend Helfer gesucht	int(4)=1
prefetchtime	Frequence of expected regular pickups in seconds	unsigned int(10)=1209600
team_conversation_id		unsigned int(10)	Nullable
springer_conversation_id		unsigned int(10)	Nullable
deleted_at		datetime	Nullable
use_region_pickup_rule	[@StoreSettings::USE](https://gitlab.com/foodsharing-dev/foodsharing/-/tree/master/src/Modules/Core/DBConstants/StoreSettings/USE.php)_PICKUP_RULE_YES = Store follows region pickup rule. @StoreSettings::USE_PICKUP_RULE_NO = Store does not follow region pickup rule.	unsigned int(1)

Table fs_betrieb_has_lebensmittel

Description - fs_betrieb_has_lebensmittel

Table columns - fs_betrieb_has_lebensmittel

Column	Description	Type	properties
betrieb_id		unsigned int(10)	Primary Key
lebensmittel_id		unsigned int(10)	Primary Key

Table fs_betrieb_kategorie

Description - fs_betrieb_kategorie

Table columns - fs_betrieb_kategorie

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
name		varchar(50)	Nullable

Table fs_betrieb_notiz

Description - fs_betrieb_notiz

Stores wallposts on stores (independent of wallpost system)

Open todos from old documentation - fs_betrieb_notiz

• Have deleted entries from fs_foodsaver and fs_betrieb reappear.

Table columns - fs_betrieb_notiz

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
foodsaver_id		unsigned int(10)
betrieb_id		unsigned int(10)
milestone		unsigned int(4)
text		text(16777215)	Nullable
zeit		datetime	Nullable
last		int(4)

Table fs_betrieb_team

Description - fs_betrieb_team

Defines foodsaver team embers with pickup store provided food and statistic.

Open todos from old documentation - fs_betrieb_team

• Have deleted entries from fs_foodsaver and fs_betrieb reappear.
• Clarify: Describe the meaning of APPLIED_FOR_TEAM and jumber
• Clarify: Could a managing/verantworktlicher can pick up from a store?
• Clarify: What are Biebs? StoreGateway::getBiebsForStore()
• Clarify: Why does the table contains a id, is the foodsaver_id and betrieb_id not unique enought
• Clarify: Why are stats directly part table?
• Clarify: Is it possible to be black listed for a store or a team?

Table columns - fs_betrieb_team

Column	Description	Type	properties
foodsaver_id		unsigned int(10)	Weak-foreign key (fs_foodsaver:id)
betrieb_id		unsigned int(10)	Weak-foreign key (fs_betrieb:id)
verantwortlich	@StoreTeam::Responsible	unsigned int(4)	Nullable
active	@StoreTeam::MembershipStatus	int(11)
stat_last_update		datetime	Nullable
stat_fetchcount		unsigned int(10)
stat_first_fetch		date	Nullable
stat_last_fetch		date	Nullable
stat_add_date		date	Nullable
id		unsigned int(10)	Primary Key, Auto-Increment

Table fs_bezirk

Description - fs_bezirk

Stores districts as well as workinggroups (differentiated by type column) (referred to as 'group' or 'district' in this document)

Open todos from old documentation - fs_bezirk

• Remove unused columns email_pass, conversation_id
• Clarify: Field description
• Clarify: Extract typs from src\Modules\Core\DBConstants\Region\Type
• Cöarify: Use of moderated? $region['moderated'] || in_array($region['type'], [Type::BIG_CITY, Type::FEDERAL_STATE, Type::COUNTRY]);

Table columns - fs_bezirk

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
parent_id		unsigned int(11)	foreign key (fs_bezirk:id), Nullable
has_children	Is set when ID is set in parent_id of other row	int(4)
type		int(4)=1
teaser		text(16777215)
desc		text(16777215)
photo		varchar(200)
master		unsigned int(10)
mailbox_id	Id of user related mailbox	unsigned int(10)	Weak-foreign key (fs_mailbox:id)
name		varchar(50)	Nullable
email		varchar(120)
email_pass		varchar(50)
email_name		varchar(100)
apply_type	Defines join conditions for Working-Groups (@Region::ApplyType)	int(4)=2
banana_count		int(4)
fetch_count		int(4)
week_num		int(4)
report_num		int(4)
stat_last_update		datetime=current_timestamp()
stat_fetchweight		unsigned decimal
stat_fetchcount		unsigned int(10)
stat_postcount		unsigned int(10)
stat_betriebcount		unsigned int(7)
stat_korpcount		unsigned int(7)
stat_botcount		unsigned int(7)
stat_fscount		unsigned int(7)
stat_fairteilercount		unsigned int(7)
conversation_id	Unused	unsigned int(10)
moderated	Unused? Only select found no insert or update *	int(4)

Table fs_bezirk_closure

Description - fs_bezirk_closure

Table columns - fs_bezirk_closure

Column	Description	Type	properties
bezirk_id		unsigned int(10)	foreign key (fs_bezirk:id)
ancestor_id		unsigned int(10)	foreign key (fs_bezirk:id)
depth		unsigned int(10)

Table fs_bezirk_has_theme

Description - fs_bezirk_has_theme

Forum: Relates threads to groups.

Open todos from old documentation - fs_bezirk_has_theme

• remove broken entries
• add foreign key relationsips on fs_theme and fs_bezirk with ON DELETE CASCADE trigger.

Table columns - fs_bezirk_has_theme

Column	Description	Type	properties
theme_id		unsigned int(10)	Primary Key, foreign key (fs_theme:id)
bezirk_id		unsigned int(10)	Primary Key, foreign key (fs_bezirk:id)
bot_theme		unsigned int(4)

Table fs_bezirk_has_wallpost

Description - fs_bezirk_has_wallpost

Open todos from old documentation - fs_bezirk_has_wallpost

• remove broken entries
• add foreign key relationships on fs_bezirk and fs_wallpost with ON DELETE CASCADE trigger.

Table columns - fs_bezirk_has_wallpost

Column	Description	Type	properties
bezirk_id		unsigned int(10)	Primary Key, foreign key (fs_bezirk:id)
wallpost_id		unsigned int(10)	Primary Key, foreign key (fs_wallpost:id)

Table fs_blog_entry

Description - fs_blog_entry

Stores blog entries (there is a /news URL)

Table columns - fs_blog_entry

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
bezirk_id		unsigned int(10)
foodsaver_id		unsigned int(10)
active		unsigned int(4)
name		varchar(100)	Nullable
teaser		varchar(500)	Nullable
body		text(16777215)	Nullable
time		datetime	Nullable
picture		varchar(150)

Table fs_botschafter

Description - fs_botschafter

Notes the ambassador/admin attribute on a group membership.

Open todos from old documentation - fs_botschafter

• remove broken entries
• add foreign key relationships on fs_bezirk and fs_foodsaver

Table columns - fs_botschafter

Column	Description	Type	properties
foodsaver_id		unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
bezirk_id		unsigned int(10)	Primary Key, foreign key (fs_bezirk:id)

Table fs_buddy

Description - fs_buddy

'I know XY' connection/friendship relation.

Open todos from old documentation - fs_buddy

• investigate, why there are lots of '0' entries remove broken entries
• add foreign key relationships on fs_foodsaver with ON DELETE CASCADE trigger

Table columns - fs_buddy

Column	Description	Type	properties
foodsaver_id		unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
buddy_id		unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
confirmed		unsigned int(4)	Nullable

Table fs_chain

Description - fs_chain

Table columns - fs_chain

Column	Description	Type	properties
id	unique id of the chain	unsigned int(10)	Primary Key, Auto-Increment
name		varchar(120)
headquarters_zip		varchar(5)	Nullable
headquarters_city		varchar(50)	Nullable
status		unsigned int(10)
modification_date		date
allow_press		int(4)
forum_thread	id of the chains forum thread	unsigned int(10)	foreign key (fs_theme:id), Nullable
notes	Only visibe in the chain table	varchar(200)	Nullable
common_store_information	Details displayed on store pages	text(16777215)	Nullable
estimated_store_count		unsigned int(6)
headquarters_country		varchar(50)	Nullable

Table fs_contact

Description - fs_contact

Stores email addresses gathered from incoming/outgoing emails that will be used for autocompletion.

Table columns - fs_contact

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
name		varchar(180)	Nullable
email		varchar(180)	Nullable

Table fs_content

Description - fs_content

'Mini-CMS' content table that is used to generate some pages as well as sections on some pages.

Table columns - fs_content

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
name		varchar(20)	Nullable
title		varchar(120)	Nullable
body		text(16777215)	Nullable
last_mod		datetime	Nullable

Table fs_conversation

Description - fs_conversation

Conversation table

Open todos from old documentation - fs_conversation

• get rid of API module
• switch over to foodsharing-API backend to get rid of denormalized columns.
• By that, also stop htmlentities encoding. On the decoding side, implement time aware message body decoders.
• Denormalized fields usage: listConversations (member, last)
• remove last_foodsaver_id (unused, easy)
• remove heartbeat code (that is not used since we have socket.io)

Table columns - fs_conversation

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
locked		int(1)
name		varchar(40)	Nullable
last		datetime	Nullable
last_foodsaver_id		unsigned int(10)	Nullable
last_message_id		unsigned int(10)	Nullable
last_message		text(16777215)	Nullable
last_message_is_htmlentity_encoded		int(1)=1

Table fs_email_blacklist

Description - fs_email_blacklist

Contains email addresses that are not allowed to signup (needs database admin to maintain)

Table columns - fs_email_blacklist

Column	Description	Type	properties
email		varchar(255)
since		timestamp=current_timestamp()
reason		text(16777215)

Table fs_email_bounces

Description - fs_email_bounces

Table columns - fs_email_bounces

Column	Description	Type	properties
email		varchar(255)
bounced_at		datetime
bounce_category		varchar(255)

Table fs_email_status

Description - fs_email_status

Mass mailer status table (per-recipient status)

Open todos from old documentation - fs_email_status

• remove broken entries
• add foreign key relationships to fs_foodsaver and fs_email with ON DELETE CASCADE

Table columns - fs_email_status

Column	Description	Type	properties
email_id		unsigned int(10)	Primary Key, foreign key (fs_send_email:id)
foodsaver_id		unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
status		unsigned int(4)	Nullable

Table fs_event

Description - fs_event

Contains all events

Open todos from old documentation - fs_event

• Regain missing users
• Check code fetching events to properly handle deleted users
• remove broken entries for fs_bezirk by setting them NULL
• add foreign key relationship to fs_bezirk (on delete set NULL)
• add foreign key relationship to fs_location (nullable)

Table columns - fs_event

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
foodsaver_id		unsigned int(10)
bezirk_id		unsigned int(10)	foreign key (fs_bezirk:id), Nullable
location_id		unsigned int(10)	foreign key (fs_location:id), Nullable
public		int(1)
name		varchar(200)	Nullable
start		datetime
end		datetime
description		text(16777215)	Nullable
bot		unsigned int(4)	Nullable
online		unsigned int(4)	Nullable

Table fs_event_has_wallpost

Description - fs_event_has_wallpost

Links wallposts on event (only communication channel for an event)

Open todos from old documentation - fs_event_has_wallpost

• Remove broken entries
• add foreign key relationships to fs_event and fs_wallpost, ON DELETE CASCADE

Table columns - fs_event_has_wallpost

Column	Description	Type	properties
event_id		unsigned int(10)	Primary Key, foreign key (fs_event:id)
wallpost_id		unsigned int(10)	Primary Key, foreign key (fs_wallpost:id)

Table fs_fairteiler

Description - fs_fairteiler

Lists fair share points

Open todos from old documentation - fs_fairteiler

• Null broken entries (should still be listable in map)
• add foreign key relationship to fs_bezirk

Table columns - fs_fairteiler

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
bezirk_id		unsigned int(10)	foreign key (fs_bezirk:id), Nullable
name		varchar(260)	Nullable
picture		varchar(100)
status		unsigned int(4)	Nullable
desc		text(16777215)	Nullable
anschrift		varchar(260)	Nullable
plz		varchar(5)	Nullable
ort		varchar(100)	Nullable
lat		varchar(100)	Nullable
lon		varchar(100)	Nullable
add_date		date	Nullable
add_foodsaver		unsigned int(10)	Nullable

Table fs_fairteiler_follower

Description - fs_fairteiler_follower

Links follower/responsible to food share points

Open todos from old documentation - fs_fairteiler_follower

• Remove broken entries
• add foreign key relationships to fs_foodsaver and fs_fairteiler with ON DELETE CASCADE

Table columns - fs_fairteiler_follower

Column	Description	Type	properties
fairteiler_id		unsigned int(10)	Primary Key, foreign key (fs_fairteiler:id)
foodsaver_id		unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
type		unsigned int(4)=1
infotype		unsigned int(4)=1

Table fs_fairteiler_has_wallpost

Description - fs_fairteiler_has_wallpost

Links wallposts to fair share points

Open todos from old documentation - fs_fairteiler_has_wallpost

• Remove broken entries
• add foreign key relationship to fs_fairteiler, fs_wallpost with ON DELETE CASCADE
• check code that handles wallpost display to see how NULL foodsavers are handled.
• Reasoning: Keep wallposts from deleted users.

Table columns - fs_fairteiler_has_wallpost

Column	Description	Type	properties
fairteiler_id		unsigned int(10)	Primary Key, foreign key (fs_fairteiler:id)
wallpost_id		unsigned int(10)	Primary Key, foreign key (fs_wallpost:id)

Table fs_feature_toggles

Description - fs_feature_toggles

Table columns - fs_feature_toggles

Column	Description	Type	properties
identifier		varchar(255)	Primary Key
is_active		int(1)
site_environment		varchar(255)	Primary Key
created_at		timestamp=current_timestamp()
updated_at		timestamp	Nullable

Table fs_fetchdate

Description - fs_fetchdate

Stores non-recurring pickup slots (just the slot, no fetcher information)

Open todos from old documentation - fs_fetchdate

• reinsert missing stores

Table columns - fs_fetchdate

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
betrieb_id		unsigned int(10)
time		datetime	Nullable
fetchercount		unsigned int(4)	Nullable
description	optional description for this pickup	varchar(100)	Nullable

Table fs_fetchweight

Description - fs_fetchweight

Table columns - fs_fetchweight

Column	Description	Type	properties
id		int(11)	Primary Key
weight		decimal

Table fs_foodsaver

Description - fs_foodsaver

Describes the user in the foodsharing system. Therefore it contains profile, role and statistics.

Open todos from old documentation - fs_foodsaver

• bezirk_id ('home district') is 0 for a lot of users. Should be NULL. As we don't need this soon in new backend, care later.
• Document: Add to database as column (deleted_at) comment: deleted_at: deletion day of account, if NULL, account is active
• new_bezirk: Could be removed? Currently no usage found. (unset in SettingsControl and initial_migration)
• want_new: Could be removed? Currently no usage found. (initial_migration)
• Clarify: Is it possible that a community member is in the role ogra or site_admin and not part of the orga team?
• Drop unused columns from database and remove rests from codebase

Table columns - fs_foodsaver

Column	Description	Type	properties
id	Unique id of user in foodsharing community	unsigned int(10)	Primary Key, Auto-Increment
bezirk_id	Id of home destrict	unsigned int(10)	Weak-foreign key (fs_bezirk:id)
position	Free text description of position in foodsharing community (public visible)	varchar(255)
verified	User is verified to by part of the foodsharing community (True: Verified, False: Not verified or lost verification)	unsigned int(4)
last_pass	Last generation of passport	datetime	Nullable
new_bezirk	Unused	varchar(120)
want_new	Unused	int(4)
mailbox_id	Id of user related mailbox	unsigned int(10)	Weak-foreign key (fs_mailbox:id), Nullable
rolle	Describes the type of member in the foodsharing community system @Foodsaver::Role	int(4)
type	Unused? I only found one SELECT but no write	int(4)	Nullable
plz	Zip code of user home	varchar(10)	Nullable
stadt	City name of user home	varchar(100)	Nullable
lat	Geo position latitude of user home	varchar(20)	Nullable
lon	Geo position longitude of user home	varchar(20)	Nullable
photo	URL to user image ('/api/uploads' '/images/) Path is different depending on upload way	varchar(50)	Nullable
email	E-Mail address which is used for registration and newsletter	varchar(120)	Nullable
password	User login password hash	varchar(100)	Nullable
name	First name of user	varchar(120)	Nullable
admin	Unused? I think replaced by SITE_ADMIN role. Found it only in SELECT but no write and Foodsharing Test helper createOrga sets two fields with simular values.	unsigned int(4)	Nullable
nachname	Last name of user	varchar(120)	Nullable
anschrift	Address (street and house number) of user home	varchar(120)	Nullable
telefon	phone number of user	varchar(30)	Nullable
homepage	URL to user own homepage	varchar(255)	Nullable
handy	mobile phone number of user	varchar(50)	Nullable
geschlecht	Gender of user (@Foodsaver::Gender)	unsigned int(4)
geb_datum	birthday of user	date	Nullable
anmeldedatum	Date of registaration	datetime	Nullable
privacy_notice_accepted_date	Documentation modification date of privacy notice which the user accetped	datetime	Nullable
privacy_policy_accepted_date	Documentation modification date of privacy policy which the user accetped	datetime	Nullable
orgateam	Unused? Defines which member is part of the orga team (meaning orgateam is unclear)	unsigned int(4)	Nullable
active	E-Mail activation after registration is completed (True: E-Mail is verified, False: verification e-mail feedback is pending)	unsigned int(4)
data	Unused??	text(16777215)
about_me_public	User provided description text for public accessable parts.	text(16777215)
newsletter	User subscribes the newsletter	int(1)
token	Verification code which is send to user during registration	varchar(100)
infomail_message	True if the user have subscription for info mails like chat notifications.	int(1)	Nullable
last_login	Timestamp of the last successful login.	datetime	Nullable
stat_fetchweight	Fetch weight in kg	unsigned decimal
stat_fetchcount	Count of fetches the user have confirmed in total.	unsigned int(10)
stat_ratecount	Unused	unsigned int(10)
stat_rating	Unused	unsigned decimal
stat_postcount	Count of total post fs_theme_post, fs_wallpost and fs_betrieb_notiz the user created	int(11)
stat_buddycount	Statistic count of the confirmed buddies	unsigned int(7)
stat_bananacount	Count of recived bananas the user have.	unsigned int(7)
stat_fetchrate	(1 - 'confirmed but missed fetchs' / 'confirmed fetches') * 100 in procent	decimal=100
sleep_status	Kind of sleeping (@Foodsaver::SleepStatus)	unsigned int(4)
sleep_from	Date of sleep will begin	date	Nullable
sleep_until	Date of expected sleeping ends.	date	Nullable
sleep_msg	Message about the reason of sleeping.	text(16777215)	Nullable
option	key value store (deactivated activity-listings)	text(16777215)
beta	Unused? NO referene in code found.	int(1)
quiz_rolle	@Foodsaver::Role of quiz that the user have already successful done. (Hard coded levels exist: Role::AMBASSADOR, Role::STORE_MANAGER, Role::FOODSAVER)	unsigned int(4)
contact_public	Unused (Hidden feature) User can be contacted public via mail form.	int(4)
deleted_at	Date of accounts was deleted.	datetime	Nullable
about_me_intern	User provided description text for community internal accessable parts.	text(65535)	Nullable
deleted_by	id of the user who deleted this profile	unsigned int(10)	Nullable
deleted_reason	optional explanation why this profile was deleted	varchar(200)	Nullable

Table fs_foodsaver_archive

Description - fs_foodsaver_archive

Archive table to hold removed users for admin purposes

Open todos from old documentation - fs_foodsaver_archive

• Move to fs_foodsaver_archive3
• Recreate with same structure as fs_foodsaver

Table columns - fs_foodsaver_archive

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
bezirk_id		unsigned int(10)
position		varchar(255)
verified		unsigned int(4)
last_pass		datetime	Nullable
new_bezirk		varchar(120)
want_new		int(4)
mailbox_id		unsigned int(10)	Nullable
rolle		int(4)
type		int(4)	Nullable
plz		varchar(10)	Nullable
stadt		varchar(100)	Nullable
lat		varchar(20)	Nullable
lon		varchar(20)	Nullable
photo		varchar(50)	Nullable
email		varchar(120)	Nullable
password		varchar(100)	Nullable
name		varchar(120)	Nullable
admin		unsigned int(4)	Nullable
nachname		varchar(120)	Nullable
anschrift		varchar(120)	Nullable
telefon		varchar(30)	Nullable
homepage		varchar(255)	Nullable
handy		varchar(50)	Nullable
geschlecht		unsigned int(4)
geb_datum		date	Nullable
anmeldedatum		datetime	Nullable
privacy_notice_accepted_date		datetime	Nullable
privacy_policy_accepted_date		datetime	Nullable
orgateam		unsigned int(4)	Nullable
active		unsigned int(4)
data		text(16777215)
about_me_public		text(16777215)
newsletter		int(1)
token		varchar(25)
infomail_message		int(1)	Nullable
last_login		datetime	Nullable
stat_fetchweight		unsigned decimal
stat_fetchcount		unsigned int(10)
stat_ratecount		unsigned int(10)
stat_rating		unsigned decimal
stat_postcount		int(11)
stat_buddycount		unsigned int(7)
stat_bananacount		unsigned int(7)
stat_fetchrate		decimal=100
sleep_status		unsigned int(4)
sleep_from		date	Nullable
sleep_until		date	Nullable
sleep_msg		text(16777215)	Nullable
option		text(16777215)
beta		int(1)
quiz_rolle		unsigned int(4)
contact_public		int(4)
deleted_at		datetime	Nullable
about_me_intern		text(65535)	Nullable
deleted_by	id of the user who deleted this profile	unsigned int(10)	Nullable
deleted_reason	optional explanation why this profile was deleted	varchar(200)	Nullable

Table fs_foodsaver_change_history

Description - fs_foodsaver_change_history

Logs all changes to personal data in foodsaver table

Open todos from old documentation - fs_foodsaver_change_history

• Do not use indices as this should persist deletions (as one reason for it was to be able to detect abuse)

Table columns - fs_foodsaver_change_history

Column	Description	Type	properties
date		timestamp=current_timestamp()
fs_id		int(11)
changer_id		int(11)
object_name		text(16777215)
old_value		text(16777215)	Nullable
new_value		text(16777215)	Nullable

Table fs_foodsaver_has_achievement

Description - fs_foodsaver_has_achievement

Table columns - fs_foodsaver_has_achievement

Column	Description	Type	properties
id		int(11)	Primary Key, Auto-Increment
foodsaver_id		unsigned int(10)	foreign key (fs_foodsaver:id)
achievement_id		int(11)	foreign key (fs_achievement:id)
reviewer_id		unsigned int(10)	foreign key (fs_foodsaver:id), Nullable
notice		text(65535)	Nullable
valid_until		datetime	Nullable
created_at		timestamp=current_timestamp()
updated_at		timestamp	Nullable

Table fs_foodsaver_has_bell

Description - fs_foodsaver_has_bell

Stores bell <-> user relationship

Open todos from old documentation - fs_foodsaver_has_bell

• Remove broken data
• Add foreign key relationships to fs_foodsaver and fs_bell

Table columns - fs_foodsaver_has_bell

Column	Description	Type	properties
foodsaver_id		unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
bell_id		unsigned int(10)	Primary Key, foreign key (fs_bell:id)
seen		unsigned int(4)	Nullable

Table fs_foodsaver_has_bezirk

Description - fs_foodsaver_has_bezirk

Describes which foodsaver is in which group/district/workgroups and regions membership

Open todos from old documentation - fs_foodsaver_has_bezirk

• Remove broken data
• Add foreign key relationships to fs_foodsaver and fs_bezirk

Table columns - fs_foodsaver_has_bezirk

Column	Description	Type	properties
foodsaver_id		unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
bezirk_id		unsigned int(10)	Primary Key, foreign key (fs_bezirk:id)
active	0=beworben,1=aktiv,10=vielleicht	unsigned int(10)	Nullable
added	Date of insert	datetime=current_timestamp()
application	Addition information about the foodsaver (like skills, possible time, ...)	text(16777215)
notify_by_email_about_new_threads	Emails from new forum threads in regions and working groups can be disabled.	unsigned int(1)=1

Table fs_foodsaver_has_contact

Description - fs_foodsaver_has_contact

Relates contacts (email addresses, see above) to users

Open todos from old documentation - fs_foodsaver_has_contact

• Remove broken data
• Add FK to fs_foodsaver, fs_contact

Table columns - fs_foodsaver_has_contact

Column	Description	Type	properties
foodsaver_id		unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
contact_id		unsigned int(10)	Primary Key, foreign key (fs_contact:id)

Table fs_foodsaver_has_conversation

Description - fs_foodsaver_has_conversation

Relates conversations to foodsavers. Care: It is also used to look up conversations by user

Open todos from old documentation - fs_foodsaver_has_conversation

• Reinsert missing foodsaver, add FK to fs_conversation (not to user)
• Fix code to handle deleted users correctly

Table columns - fs_foodsaver_has_conversation

Column	Description	Type	properties
foodsaver_id		unsigned int(10)	foreign key (fs_foodsaver:id)
conversation_id		unsigned int(10)	foreign key (fs_conversation:id)
unread		int(6)
id		unsigned int(10)	Primary Key, Auto-Increment

Table fs_foodsaver_has_event

Description - fs_foodsaver_has_event

Relates users to events.

Open todos from old documentation - fs_foodsaver_has_event

• Remove broken data
• FK on fs_foodsaver, fs_event, ON DELETE CASCADE

Table columns - fs_foodsaver_has_event

Column	Description	Type	properties
foodsaver_id		unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
event_id		unsigned int(10)	Primary Key, foreign key (fs_event:id)
status		unsigned int(4)

Table fs_foodsaver_has_options

Description - fs_foodsaver_has_options

Table columns - fs_foodsaver_has_options

Column	Description	Type	properties
foodsaver_id		unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
option_type	category of the option	unsigned int(10)	Primary Key
option_value	value of the option	varchar(255)

Table fs_foodsaver_has_poll

Description - fs_foodsaver_has_poll

Table columns - fs_foodsaver_has_poll

Column	Description	Type	properties
foodsaver_id	id of the voter	unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
poll_id	id of the poll	unsigned int(10)	Primary Key, foreign key (fs_poll:id)
time	time at which the voter has voted, null if not voted yet	datetime	Nullable

Table fs_foodsaver_has_wallpost

Description - fs_foodsaver_has_wallpost

Wallposts on user profile

Open todos from old documentation - fs_foodsaver_has_wallpost

• Remove broken data
• FK on fs_foodsaver, fs_wallpost, ON DELETE CASCADE

Table columns - fs_foodsaver_has_wallpost

Column	Description	Type	properties
foodsaver_id		unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
wallpost_id		unsigned int(10)	Primary Key, foreign key (fs_wallpost:id)
usercomment		int(4)

Table fs_ipblock

Description - fs_ipblock

Table columns - fs_ipblock

Column	Description	Type	properties
ip		varchar(20)	Primary Key
context		varchar(10)	Primary Key
start		datetime	Nullable
duration		unsigned int(10)	Nullable

Table fs_key_account_manager

Description - fs_key_account_manager

Table columns - fs_key_account_manager

Column	Description	Type	properties
foodsaver_id		unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
chain_id		unsigned int(10)	Primary Key, foreign key (fs_chain:id)

Table fs_lebensmittel

Description - fs_lebensmittel

Store different kinds of food to be linked with individual stores. Only ever implemented as setter, don't care for now.

Table columns - fs_lebensmittel

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
name		varchar(50)	Nullable

Table fs_location

Description - fs_location

Table columns - fs_location

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
name		varchar(200)	Nullable
lat		decimal	Nullable
lon		decimal	Nullable
zip		varchar(10)	Nullable
city		varchar(100)	Nullable
street		varchar(200)	Nullable

Table fs_mailbox

Description - fs_mailbox

Stores mailbox names (for email mailboxes)

Table columns - fs_mailbox

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
name		varchar(50)	Nullable
member		int(4)
last_access		datetime=current_timestamp()

Table fs_mailbox_member

Description - fs_mailbox_member

Maps additional mailbox access for users (e.g. granting custom mailboxes or group ones)

Open todos from old documentation - fs_mailbox_member

• Remove brokendata
• FK on fs_mailbox, fs_mailbox_member, ON DELETE CASCADE

Table columns - fs_mailbox_member

Column	Description	Type	properties
mailbox_id		unsigned int(10)	Primary Key, foreign key (fs_mailbox:id)
foodsaver_id		unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
email_name		varchar(120)

Table fs_mailbox_message

Description - fs_mailbox_message

emails

Open todos from old documentation - fs_mailbox_message

• remove brokendata
• FK on fs_mailbox, ON DELETE CASCADE

Table columns - fs_mailbox_message

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
mailbox_id		unsigned int(10)	foreign key (fs_mailbox:id)
folder		unsigned int(4)=1	Nullable
sender		text(65535)	Nullable
to		text(16777215)
subject		text(65535)	Nullable
body		text(16777215)	Nullable
body_html		text(16777215)
time		datetime	Nullable
attach		text(16777215)	Nullable
read		unsigned int(4)	Nullable
answer		unsigned int(4)	Nullable

Table fs_mailchange

Description - fs_mailchange

Requests to change the emailaddress

Open todos from old documentation - fs_mailchange

• Remove broken / old data
• FK on fs_foodsaver, ON DELETE CASCADE
• There is a recent entry with foodsaver_id = 0. How did that get here?

Table columns - fs_mailchange

Column	Description	Type	properties
foodsaver_id		unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
newmail		varchar(200)	Nullable
time		datetime	Nullable
token		varchar(300)	Nullable

Table fs_msg

Description - fs_msg

Conversation messages

Open todos from old documentation - fs_msg

• Remove broken data (conversations), readd missing users
• FK on fs_conversation, ON DELETE CASCADE

Table columns - fs_msg

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
conversation_id		unsigned int(10)	foreign key (fs_conversation:id)
foodsaver_id		unsigned int(10)
body		text(16777215)	Nullable
time		datetime	Nullable
is_htmlentity_encoded		int(1)=1

Table fs_pass_gen

Description - fs_pass_gen

Logs which ID cards have been generated

Open todos from old documentation - fs_pass_gen

• remove broken data (foodsaver_id), readd missing (bot_id)
• FK for foodsaver_id, ON DELETE CASCADE
• handle emtpy users correctly (bot_id)

Table columns - fs_pass_gen

Column	Description	Type	properties
foodsaver_id		unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
date		datetime	Primary Key
bot_id		unsigned int(10)	foreign key (fs_foodsaver:id), Nullable

Table fs_pass_request

Description - fs_pass_request

Password change/forgot requestsRelates questions <-> quiz

Open todos from old documentation - fs_pass_request

• remove broken/old data
• FK for foodsaver_id, ON DELETE CASCADE

Table columns - fs_pass_request

Column	Description	Type	properties
foodsaver_id		unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
name		varchar(50)	Nullable
time		datetime	Nullable

Table fs_poll

Description - fs_poll

Table columns - fs_poll

Column	Description	Type	properties
id	unique id of the poll	unsigned int(10)	Primary Key, Auto-Increment
region_id	region with which the poll is associated	unsigned int(10)	foreign key (fs_bezirk:id)
name	title of the poll	varchar(200)	Nullable
description	description of the poll	text(16777215)	Nullable
scope	determines who will be invited to vote	unsigned int(2)
type	determines how a vote is cast and which values are possible for each option	unsigned int(2)
start	start timestamp for the poll	datetime
end	end timestamp for the poll	datetime
author	id of the user who created the poll	unsigned int(10)
creation_timestamp		datetime
votes	number of users who have voted	unsigned int(10)
cancelled_by	id of the user who cancelled the poll	unsigned int(10)	Nullable
eligible_votes_count	number of users who are eligible to vote	unsigned int(10)
shuffle_options		int(4)=1

Table fs_poll_has_options

Description - fs_poll_has_options

Table columns - fs_poll_has_options

Column	Description	Type	properties
poll_id	the poll to which this option belongs	unsigned int(10)	Primary Key, foreign key (fs_poll:id)
option	index of the option	unsigned int(2)	Primary Key
option_text	description text of the option	varchar(200)	Nullable

Table fs_poll_option_has_value

Description - fs_poll_option_has_value

Table columns - fs_poll_option_has_value

Column	Description	Type	properties
poll_id	the poll to which the option belongs	unsigned int(10)	Primary Key, foreign key (fs_poll:id)
option	index of the option	unsigned int(2)	Primary Key
value	value for the option	int(2)	Primary Key
votes	number of current votes for the value	unsigned int(10)

Table fs_post_reaction

Description - fs_post_reaction

Table columns - fs_post_reaction

Column	Description	Type	properties
post_id		unsigned int(10)	foreign key (fs_theme_post:id)
time		datetime
foodsaver_id		int(11)
key		varchar(63)	Nullable

Table fs_push_notification_subscription

Description - fs_push_notification_subscription

Table columns - fs_push_notification_subscription

Column	Description	Type	properties
id		int(11)	Primary Key, Auto-Increment
foodsaver_id		int(11)
data		text(65535)	Nullable
type		varchar(24)	Nullable

Table fs_question

Description - fs_question

Questions (for quiz)

Table columns - fs_question

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
text		text(16777215)	Nullable
duration		unsigned int(3)
wikilink		varchar(250)

Table fs_question_has_quiz

Description - fs_question_has_quiz

Relates questions <-> quiz

Open todos from old documentation - fs_question_has_quiz

• remove broken data
• FK to fs_quiz, fs_question, ON DELETE CASCADE

Table columns - fs_question_has_quiz

Column	Description	Type	properties
question_id		unsigned int(10)	Primary Key, foreign key (fs_question:id)
quiz_id		unsigned int(10)	Primary Key, foreign key (fs_quiz:id)
fp		unsigned int(4)	Nullable

Table fs_question_has_wallpost

Description - fs_question_has_wallpost

Relates wallposts to questions

Open todos from old documentation - fs_question_has_wallpost

• remove broken data
• FK to fs_question, fs_wallpost, ON DELETE CASCADE

Table columns - fs_question_has_wallpost

Column	Description	Type	properties
question_id		unsigned int(10)	Primary Key, foreign key (fs_question:id)
wallpost_id		unsigned int(10)	Primary Key, foreign key (fs_wallpost:id)
usercomment		int(4)

Table fs_quiz

Description - fs_quiz

Table columns - fs_quiz

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
name		varchar(200)	Nullable
desc		text(16777215)	Nullable
maxfp		unsigned int(6)
questcount		unsigned int(6)

Table fs_quiz_session

Description - fs_quiz_session

Each try (by users) of a quiz (session)

Open todos from old documentation - fs_quiz_session

• remove broken data

Table columns - fs_quiz_session

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
foodsaver_id		unsigned int(10)	foreign key (fs_foodsaver:id)
quiz_id		unsigned int(10)
status		unsigned int(4)	Nullable
quiz_index		unsigned int(4)	Nullable
quiz_questions		text(16777215)	Nullable
quiz_result		text(16777215)	Nullable
time_start		datetime	Nullable
time_end		datetime	Nullable
fp		decimal	Nullable
maxfp		unsigned int(4)	Nullable
quest_count		unsigned int(4)	Nullable
easymode		int(4)

Table fs_rating

Description - fs_rating

Stores trust bananas

Open todos from old documentation - fs_rating

• remove broken data (as we don't want to have trust bananas from/for deleted users)
• Add FK

Table columns - fs_rating

Column	Description	Type	properties
foodsaver_id		unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
rater_id		unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
msg		text(16777215)
time		datetime

Table fs_region_function

Description - fs_region_function

Table columns - fs_region_function

Column	Description	Type	properties
id		int(11)	Primary Key, Auto-Increment
region_id		unsigned int(11)	foreign key (fs_bezirk:id)
function_id		unsigned int(11)
target_id		int(11)	Nullable

Table fs_region_options

Description - fs_region_options

Table columns - fs_region_options

Column	Description	Type	properties
region_id		unsigned int(10)	Primary Key, foreign key (fs_bezirk:id)
option_type	category of the option	unsigned int(10)	Primary Key
option_value	value of the option	varchar(255)

Table fs_region_pin

Description - fs_region_pin

Table columns - fs_region_pin

Column	Description	Type	properties
region_id	region id	unsigned int(10)	Primary Key, foreign key (fs_bezirk:id)
lat	latitude	varchar(20)
lon	longitude	varchar(20)
desc	description	text(16777215)
status	state of the pin	unsigned int(4)

Table fs_report

Description - fs_report

Table columns - fs_report

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
foodsaver_id		unsigned int(10)
reporter_id		unsigned int(10)	Nullable
reporttype		unsigned int(4)	Nullable
betrieb_id		unsigned int(10)	Nullable
time		datetime	Nullable
committed		unsigned int(4)	Nullable
msg		text(16777215)	Nullable
tvalue		varchar(300)	Nullable

Table fs_report_has_wallpost

Description - fs_report_has_wallpost

Table columns - fs_report_has_wallpost

Column	Description	Type	properties
fsreport_id		unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
wallpost_id		unsigned int(10)	Primary Key, foreign key (fs_wallpost:id)

Table fs_send_email

Description - fs_send_email

Table columns - fs_send_email

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
foodsaver_id		unsigned int(10)
mailbox_id		unsigned int(10)
mode		int(4)=1
complete		int(4)
name		varchar(200)	Nullable
message		text(16777215)	Nullable
zeit		datetime	Nullable
recip		text(16777215)	Nullable
attach		varchar(500)

Table fs_stat_abholmengen

Description - fs_stat_abholmengen

Stores statistics per store (maybe broken implementation?)

Open todos from old documentation - fs_stat_abholmengen

• add FK to store

Table columns - fs_stat_abholmengen

Column	Description	Type	properties
betrieb_id		unsigned int(10)	Primary Key
date		datetime	Primary Key
abholmenge		decimal

Table fs_store_has_wallpost

Description - fs_store_has_wallpost

Table columns - fs_store_has_wallpost

Column	Description	Type	properties
store_id		unsigned int(10)	Primary Key
wallpost_id		unsigned int(10)	Primary Key

Table fs_store_log

Description - fs_store_log

Table columns - fs_store_log

Column	Description	Type	properties
id		int(11)	Primary Key, Auto-Increment
store_id	ID of Store	int(10)
date_activity	when did the action take place	datetime=current_timestamp()
action	action type that was performed	int(4)
fs_id_a	foodsaver_id who is doing the action	int(10)
fs_id_p	to which foodsaver_id is it done to	int(10)	Nullable
date_reference	date referenced (slot or wallpost entry)	datetime	Nullable
content	Text from the store-wall-entry	varchar(255)	Nullable
reason	Why a negativ action was done	text(16777215)	Nullable

Table fs_theme

Description - fs_theme

Threads in the forum.

Open todos from old documentation - fs_theme

• nothing as threads from deleted users should be kept

Table columns - fs_theme

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
foodsaver_id		unsigned int(10)
last_post_id		unsigned int(10)
name		varchar(260)	Nullable
time		datetime	Nullable
active		unsigned int(4)=1
sticky		int(1)
status	status of the thread (open or closed)	unsigned int(10)

Table fs_theme_follower

Description - fs_theme_follower

stores who follows themes

Open todos from old documentation - fs_theme_follower

• remove broken data (as follower information is irrelevant for broken XY)
• Add FK

Table columns - fs_theme_follower

Column	Description	Type	properties
foodsaver_id		unsigned int(10)	Primary Key, foreign key (fs_foodsaver:id)
theme_id		unsigned int(10)	Primary Key, foreign key (fs_theme:id)
infotype		int(1)
bell_notification		int(1)=1

Table fs_theme_post

Description - fs_theme_post

Stores posts in themes

Open todos from old documentation - fs_theme_post

• Recreate users
• Remove broken data (for themes, not users)
• Add FK to themes

Table columns - fs_theme_post

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
theme_id		unsigned int(10)	foreign key (fs_theme:id)
foodsaver_id		unsigned int(10)
reply_post		unsigned int(10)
body		text(16777215)	Nullable
time		datetime	Nullable

Table fs_usernotes_has_wallpost

Description - fs_usernotes_has_wallpost

connects organotes on users with wallposts

Open todos from old documentation - fs_usernotes_has_wallpost

• recreate missing users
• remove broken entries (for wallposts)

Table columns - fs_usernotes_has_wallpost

Column	Description	Type	properties
usernotes_id		unsigned int(10)	Primary Key
wallpost_id		unsigned int(10)	Primary Key, foreign key (fs_wallpost:id)
usercomment		int(4)

Table fs_verify_history

Description - fs_verify_history

Table columns - fs_verify_history

Column	Description	Type	properties
fs_id		unsigned int(10)	Nullable
date		datetime
bot_id		unsigned int(10)	Nullable
change_status		int(1)	Nullable

Table fs_wallpost

Description - fs_wallpost

Table columns - fs_wallpost

Column	Description	Type	properties
id		unsigned int(10)	Primary Key, Auto-Increment
foodsaver_id		unsigned int(10)
body		text(16777215)	Nullable
time		datetime	Nullable
attach		text(16777215)	Nullable

Table phinxlog

Description - phinxlog

Table columns - phinxlog

Column	Description	Type	properties
version		int(20)	Primary Key
migration_name		varchar(100)	Nullable
start_time		timestamp	Nullable
end_time		timestamp	Nullable
breakpoint		int(1)

Table uploads

Description - uploads

Table columns - uploads

Column	Description	Type	properties
uuid		char(36)	Primary Key
user_id		unsigned int(10)
sha256hash		char(64)
mimeType		varchar(255)
uploaded_at		datetime
lastaccess_at		datetime
filesize		unsigned int(10)
used_in	Indicates in which module this uploaded file is being used (profile photo, wall post, ...). A value of null indicates that the file is not being used (yet).	unsigned int(4)	Nullable
usage_id	Id of the entity that uses this uploaded file, e.g. id of the profile or the wall post. A null value indicates that the file is not being used (yet).	char(10)	Nullable

Usage of table in PHP Modules

Activity

• fs_betrieb (SELECT)
• fs_betrieb_notiz (SELECT)
• fs_betrieb_team (SELECT)
• fs_bezirk (SELECT)
• fs_bezirk_has_theme (SELECT)
• fs_event (SELECT)
• fs_event_has_wallpost (SELECT)
• fs_fairteiler (SELECT)
• fs_fairteiler_has_wallpost (SELECT)
• fs_foodsaver (SELECT)
• fs_theme (SELECT)
• fs_theme_post (SELECT)
• fs_wallpost (SELECT)

Application

• fs_bezirk (SELECT)
• fs_foodsaver (SELECT)
• fs_foodsaver_has_bezirk (DELETE, SELECT)

Basket

• fs_basket (SELECT, UPDATE)
• fs_basket_anfrage (INSERT, SELECT)
• fs_basket_has_art (INSERT)
• fs_basket_has_types (INSERT)

Bell

• fs_bell (DELETE, SELECT, UPDATE)
• fs_foodsaver_has_bell (DELETE, INSERT, SELECT, UPDATE)

Blog

• fs_blog_entry (DELETE, SELECT, UPDATE)

Buddy

• fs_buddy (DELETE, INSERT, SELECT, UPDATE)
• fs_foodsaver (SELECT)

BusinessCard

• fs_bezirk (SELECT)
• fs_foodsaver (SELECT)
• fs_mailbox (SELECT)

Command

• fs_foodsaver (SELECT)
• fs_foodsaver_has_options (SELECT)

Commands

• fs_feature_toggles (INSERT)

Content

• fs_content (DELETE, INSERT, SELECT, UPDATE)

DTO

• fs_id ()
• fs_name ()

Dev

• fs_bezirk (, SELECT)
• fs_region_function (SELECT)

Event

• fs_bezirk (SELECT)
• fs_event (INSERT, SELECT, UPDATE)
• fs_foodsaver_has_bezirk (SELECT)
• fs_foodsaver_has_event (DELETE, INSERT, SELECT)
• fs_location (INSERT, SELECT)

FoodSharePoint

• fs_botschafter (SELECT)
• fs_fairteiler (DELETE, INSERT, SELECT, UPDATE)
• fs_fairteiler_follower (DELETE, INSERT, SELECT)
• fs_fairteiler_has_wallpost (SELECT)
• fs_foodsaver (SELECT)
• fs_wallpost (SELECT)

Foodsaver

• fs_apitoken (DELETE)
• fs_application_has_wallpost (DELETE)
• fs_basket_anfrage (DELETE)
• fs_betrieb_team (SELECT)
• fs_bezirk (SELECT)
• fs_bezirk_closure (SELECT)
• fs_botschafter (DELETE, SELECT)
• fs_buddy (DELETE)
• fs_email_blacklist (SELECT)
• fs_email_status (DELETE)
• fs_fairteiler_follower (DELETE)
• fs_foodsaver (SELECT, UPDATE)
• fs_foodsaver_archive (INSERT)
• fs_foodsaver_has_bell (DELETE)
• fs_foodsaver_has_bezirk (DELETE, SELECT)
• fs_foodsaver_has_contact (DELETE)
• fs_foodsaver_has_event (DELETE)
• fs_foodsaver_has_wallpost (DELETE)
• fs_mailbox_member (DELETE)
• fs_mailchange (DELETE)
• fs_pass_gen (DELETE)
• fs_pass_request (DELETE)
• fs_quiz_session (DELETE)
• fs_rating (DELETE)
• fs_region_function (SELECT)
• fs_theme_follower (DELETE)
• fs_verify_history (INSERT, SELECT)

Group

• fs_betrieb (SELECT)
• fs_bezirk (DELETE, SELECT)
• fs_bezirk_closure (DELETE, INSERT, SELECT)
• fs_botschafter (SELECT)
• fs_fairteiler (SELECT)
• fs_foodsaver ()
• fs_region_function (DELETE, INSERT, SELECT)

Info

• fs_theme_follower (SELECT)

Legal

• fs_betrieb_team (UPDATE)
• fs_botschafter (DELETE)
• fs_content (SELECT)
• fs_foodsaver (UPDATE)

Login

• fs_email_blacklist (SELECT)
• fs_foodsaver (SELECT, UPDATE)
• fs_pass_request (DELETE, INSERT, SELECT)

Lookup

• fs_foodsaver (SELECT)

Mailbox

• fs_bezirk (SELECT, UPDATE)
• fs_botschafter (SELECT)
• fs_contact (INSERT, SELECT)
• fs_foodsaver (SELECT, UPDATE)
• fs_foodsaver_has_contact (INSERT)
• fs_mailbox (INSERT, SELECT, UPDATE)
• fs_mailbox_member (DELETE, INSERT, SELECT)
• fs_mailbox_message (DELETE, SELECT, UPDATE)

Mails

• fs_email_bounces (DELETE, INSERT, SELECT)
• fs_mailbox (SELECT)
• fs_mailbox_message (INSERT, SELECT, UPDATE)

Maintenance

• fs_bezirk (SELECT)
• fs_foodsaver (SELECT, UPDATE)
• fs_foodsaver_has_bezirk (INSERT)
• fs_ipblock (DELETE)

Map

• fs_basket (SELECT)
• fs_betrieb (SELECT)
• fs_fairteiler (SELECT)
• fs_foodsaver (SELECT)
• fs_region_pin (SELECT)

Message

• fs_betrieb (SELECT)
• fs_conversation (DELETE, INSERT, SELECT, UPDATE)
• fs_foodsaver (SELECT)
• fs_foodsaver_has_conversation (DELETE, INSERT, SELECT, UPDATE)
• fs_msg (DELETE, INSERT)

PassportGenerator

• fs_foodsaver (SELECT, UPDATE)
• fs_foodsaver_has_bezirk (SELECT)
• fs_pass_gen (INSERT)

Profile

• fs_abholer (SELECT)
• fs_basket (SELECT)
• fs_basket_anfrage (SELECT)
• fs_betrieb (SELECT)
• fs_betrieb_kategorie (SELECT)
• fs_betrieb_team (SELECT)
• fs_bezirk (SELECT)
• fs_event (SELECT)
• fs_foodsaver (SELECT, UPDATE)
• fs_foodsaver_change_history (SELECT)
• fs_foodsaver_has_bezirk (SELECT)
• fs_foodsaver_has_event (SELECT)
• fs_rating (DELETE, INSERT, SELECT)
• fs_report (SELECT)
• fs_store_log (SELECT)

PushNotification

• fs_push_notification_subscription (DELETE, INSERT)

Querys

• fs_feature_toggles (SELECT)

Quiz

• fs_answer (DELETE)
• fs_foodsaver (SELECT)
• fs_question (DELETE, SELECT)
• fs_question_has_quiz (DELETE, SELECT)
• fs_quiz (INSERT, SELECT)
• fs_quiz_session (DELETE, SELECT)

Region

• fs_abholer (SELECT)
• fs_betrieb (SELECT)
• fs_bezirk (INSERT, SELECT, UPDATE)
• fs_bezirk_closure (INSERT, SELECT)
• fs_bezirk_has_theme (INSERT, SELECT)
• fs_botschafter (DELETE, INSERT, SELECT)
• fs_foodsaver (SELECT)
• fs_foodsaver_has_bezirk (INSERT, SELECT)
• fs_mailbox (INSERT, SELECT)
• fs_region_options (INSERT, SELECT)
• fs_region_pin (INSERT, SELECT)
• fs_theme (DELETE, INSERT, SELECT, UPDATE)
• fs_theme_follower (DELETE, INSERT, SELECT)
• fs_theme_post (DELETE, SELECT)

Report

• fs_betrieb (SELECT)
• fs_betrieb_team (SELECT)
• fs_foodsaver (SELECT)
• fs_report (DELETE, SELECT, UPDATE)

Search

• fs_betrieb (SELECT)
• fs_betrieb_team (SELECT)
• fs_bezirk (SELECT)
• fs_bezirk_has_theme (SELECT)
• fs_botschafter (SELECT)
• fs_buddy (SELECT)
• fs_chain (SELECT)
• fs_conversation (SELECT)
• fs_fairteiler (SELECT)
• fs_fairteiler_follower (SELECT)
• fs_foodsaver (SELECT)
• fs_foodsaver_has_bezirk (SELECT)
• fs_foodsaver_has_conversation (SELECT)
• fs_key_account_manager (SELECT)
• fs_mailbox (SELECT)
• fs_theme (SELECT)
• fs_theme_follower (SELECT)
• fs_theme_post (SELECT)

Settings

• fs_apitoken (DELETE, SELECT)
• fs_foodsaver (UPDATE)
• fs_foodsaver_has_options (INSERT, SELECT)

Statistics

• fs_abholer (SELECT)
• fs_basket (SELECT)
• fs_fairteiler (SELECT)
• fs_foodsaver (SELECT)
• fs_foodsaver_has_bezirk (SELECT)

Stats

• fs_abholer (SELECT)
• fs_betrieb (SELECT)
• fs_betrieb_notiz (SELECT)
• fs_betrieb_team (UPDATE)
• fs_bezirk (SELECT, UPDATE)
• fs_bezirk_closure (SELECT)
• fs_bezirk_has_theme (SELECT)
• fs_botschafter (SELECT)
• fs_buddy (SELECT)
• fs_fairteiler (SELECT)
• fs_fetchweight (SELECT)
• fs_foodsaver (UPDATE)
• fs_foodsaver_has_bezirk (SELECT)
• fs_rating (SELECT)
• fs_report (SELECT)
• fs_theme_post (SELECT)
• fs_wallpost (SELECT)

Store

• fs_abholer (DELETE, INSERT, SELECT)
• fs_abholzeiten (DELETE, INSERT, SELECT)
• fs_betrieb (INSERT, SELECT, UPDATE)
• fs_betrieb_has_lebensmittel (DELETE, INSERT, SELECT)
• fs_betrieb_kategorie (SELECT)
• fs_betrieb_notiz (DELETE, INSERT, SELECT)
• fs_betrieb_team (DELETE, INSERT, SELECT, UPDATE)
• fs_bezirk (SELECT)
• fs_bezirk_closure (SELECT)
• fs_chain (SELECT)
• fs_fetchdate (INSERT, SELECT)
• fs_foodsaver (SELECT)
• fs_lebensmittel (SELECT)
• fs_store_log (INSERT)

StoreChain

• fs_betrieb (SELECT)
• fs_chain (INSERT, SELECT)
• fs_foodsaver (SELECT)
• fs_key_account_manager (DELETE, INSERT, SELECT)

Team

• fs_foodsaver (SELECT)
• fs_ipblock (INSERT, SELECT)

Unclassified

• fs_bell (DELETE)
• fs_betrieb (SELECT)

Unit

• fs_bezirk (SELECT)
• fs_botschafter (SELECT)
• fs_foodsaver_has_bezirk (SELECT)

Voting

• fs_bezirk (SELECT)
• fs_bezirk_closure (SELECT)
• fs_botschafter (SELECT)
• fs_foodsaver (SELECT)
• fs_foodsaver_has_bezirk (SELECT)
• fs_foodsaver_has_poll (INSERT, SELECT, UPDATE)
• fs_poll (DELETE, INSERT, SELECT, UPDATE)
• fs_poll_has_options (DELETE, INSERT, SELECT)
• fs_poll_option_has_value (DELETE, INSERT, SELECT, UPDATE)

WallPost

• fs_foodsaver (SELECT)
• fs_wallpost (DELETE, INSERT, SELECT)

WorkGroup

• fs_bezirk (SELECT)
• fs_botschafter (SELECT)
• fs_foodsaver (SELECT)
• fs_foodsaver_has_bezirk (SELECT)
• fs_mailbox (SELECT)
• fs_region_function (SELECT)

Xhr

• fs_bezirk (, UPDATE)
• fs_mailbox (INSERT, UPDATE)