Database Schema

VuFind uses a relational database for a few functions, primarily to track user accounts and user-generated data.

The latest VuFind database definitions can be found here. This page provides some documentation and clarification on the various tables used by the system.

This is used to store authentication hashes used by the email authenticator introduced in VuFind 6.1.

See Tracking Record Changes for details on the purpose of this table, which helps VuFind keep track of the last time each record was indexed or deleted.

This table stores user comments on records.

This table associates external session IDs with internal ones; used for Shibboleth single log-out.

This table can be optionally used to collect user feedback form responses.

This table is used to manage persistent logins.

This table supports VuFind's OAI-PMH Server functionality.

This table stores user ratings of records when the “rating” setting is turned on in the [Social] section of config.ini (introduced in VuFind 9.0).

This table is only used when the optional record cache (introduced in VuFind 3.0) is turned on. In this situation, it stores copies of records to accelerate performance and/or provide a fallback when third-party data sources are offline.

This table contains information about records that have been associated with user-generated data (comments, favorites, etc.)

This is a linking table between resource and tags.

This table is used both for storing temporary (associated with PHP session IDs) search history and long-term saved searches (associated with user accounts).

This table stores session data when VuFind is configured for database session storage. It is unused when the session is stored on disk, in memcache, etc.

When optional database-driven link shortening is turned on (see the url_shortener setting in config.ini), this table is used to store URLs.

This table stores user-generated tags.

This table stores user information (unless “privacy mode” is enabled in config.ini – available in VuFind 3.0 and newer).

One of the most important functions of the user table is the storage of user credentials.

VuFind is designed to work with two sets of credentials: the “primary” credentials, which allow the user to access user-specific functions of VuFind itself, and the “catalog” credentials, which allow VuFind to connect to a third-party ILS system to retrieve additional information and perform user-specific operations.

Depending on VuFind's configuration, different columns of the user table may be used for storing these credentials. The hashed/encrypted fields were not added until VuFind 2, with the “unsafe” fields retained for backward compatibility.

It is strongly encouraged to only use the hashed/encrypted versions of fields whenever possible. If you run VuFind's standard web-based installer and “fix” the security issue, your configuration will be automatically adjusted to use the secure fields. The web-based upgrade script will also help adjust legacy insecure data.

Here are detailed descriptions of the relevant fields:

• username - The “primary” username for accessing VuFind. This is used as a unique identifier for the user's account and will be filled in with some sort of unique value regardless of authentication configuration.
• password - The “primary” password, unencrypted. This will only be populated when VuFind is configured for low security and is using its own database for authentication (as opposed to a third-party system). low security field
• pass_hash - The “primary” password, one-way hashed. This allows users to log in, but does not store their actual password in a way that can be easily retrieved. This will only be populated when VuFind is configured for high security and is using its own database for authentication (as opposed to a third-party system).
• cat_username - The “catalog” username for identifying the user to a third-party ILS (sometimes a barcode number, but varies from ILS to ILS).
• cat_password - The “catalog” password for authenticating the user to a third-party ILS, stored in plain text. low security field
• cat_pass_enc - The “catalog” password for authenticating the user to a third-party ILS, stored in an encrypted format that VuFind can reverse using a key found in config.ini (see “ils_encryption_key” in the “Authentication” section).

Note that, in security mode, VuFind is able to hash its own “primary” password because it only needs a hash to validate incoming credentials and does not need to retrieve the original value. However, it is necessary to store the “catalog” password in a reversible encrypted format because it needs to send that value to the ILS every time the user performs a transaction that communicates with the third-party system.

This table is only used when VuFind's “library card” functionality (introduced in release 2.5) is enabled. This allows storage of multiple ILS account credentials so a single user can access multiple ILS instances.

This table stores information on user-generated favorite lists (the lists themselves, not their content).

This table associates the user, user_list and resource tables in order to make favorite lists function.

These tables are only used when VuFind's statistics collection functionality is enabled and configured to use the database for storage.

VuFind's database schema is sometimes adjusted between releases. If you use a MySQL database backend, these changes are automatically applied when you run VuFind's web-based upgrade tool (see migration notes). If you use PostgreSQL, you will currently have to apply migrations using scripts provided in the migrations directory of the code.

Note that this changelog only goes back as far as release 2.5. Not every release includes database changes.

• Added login_token table.

• Added feedback table.
• Added ratings table.
• Added an additional index to the search table to improve expiration performance.
• Changed the saved column of the search table to use a more appropriate type for Boolean values.
• Changed the public column of the user_list table to use a more appropriate type for Boolean values.
• Removed the unused folder_id column from the search table.

• Changed constraint/default of resource_id column in resource_tags table to support user list tagging.

• Added hash column to shortlinks table.

• Added new auth_hash table.
• The pending_email and user_provided_email fields were added to the user table to support email change functionality.
• The last_language field was added to the user table to track user language preferences.
• The notification_frequency, last_notification_sent and notification_base_url fields were added to the search table to support Email Alerts.
• The id fields of search, session and external_session were switched to the bigint type to allow more growth.

• The shortlinks table was added (used for the optional URL shortening mechanism).
• Added column email_verified to user table.

• The resource table now includes an extra_metadata field that can be populated with a JSON document containing extra record details (currently used for storing Summon bookmarks, to deal with changing IDs).
• The field sizes for storing catalog passwords have been increased to accommodate longer strings.

• The comments table now allows anonymous comments; user_id may now be null, and foreign key behavior has changed.
• The last_login and auth_method columns were added to the user table (to track last login date and method).
• The data column of the session table has been changed to type mediumtext to allow room for larger session data objects.

• Added external_session table.
• Added cat_id field to user table.

• Changed the collation of the tags table to utf8_bin to fix strange case sensitivity behavior (only affects MySQL; see https://vufind.org/jira/browse/VUFIND-1187 for details).

• Increased size of cat_pass_enc/cat_password fields in user table.
• Increased size of record_id, title and author columns in resource table.
• Changed default source value in resource table from 'VuFind' to 'Solr' (see VUFIND-1139).
• Introduced record table.
• Changed type of created field in search table from date to datetime.
• Added checksum field to search table (to aid in faster lookups).

• Introduced user_card table.
• Increased size of username and email columns in user table.