Default Tables
When you install UserFrosting with the Bakery CLI, a number of tables will automatically added to your database. These tables are required for UserFrosting's built-in features, such as user accounts, request throttling, persistent sessions, and access control.
The migrations for most tables can be found in the src/Database/Migrations directory of the Sprinkle that depends on it. The exceptions are the system tables, which are located in the Core sprinkle.
System tables
migrations
The migrations table is responsible for maintaining a history of the migrations run by the installer. In general, you shouldn't need to interact with this table in your own code.
| Column | Type | Description |
|---|---|---|
id |
autoincrement int |
The unique identifier for the record. |
sprinkle |
string(255) |
The name of the Sprinkle to which this migration belongs. |
migration |
string(255) |
The fully qualified namespace+class name of the migration. |
batch |
int |
A counter that groups migrations that were run together in a single instance of the migrate command. Each run of migrate increments this counter. |
created_at |
timestamp |
The time when this record was created. |
updated_at |
timestamp |
The time when this record was last updated. |
Core tables
The core Sprinkle depends on the following tables:
sessions
This is an optional table, and is only used if you are using the database session driver.
| Column | Type | Description |
|---|---|---|
id |
string |
The unique identifier for the record. |
user_id |
unsigned int |
For compatibility with Laravel session drivers only. |
ip_address |
string(45) |
For compatibility with Laravel session drivers only. |
user_agent |
text |
For compatibility with Laravel session drivers only. |
payload |
text |
The base-64 encoded contents of the session. |
last_activity |
integer |
The time when the session was last written. |
throttles
This table stores a history of requests to throttled endpoints.
| Column | Type | Description |
|---|---|---|
id |
string |
The unique identifier for the record. |
type |
string(255) |
The throttled event type. |
ip |
string(255) |
The IP address of the requester. |
request_data |
text |
Additional request data to compare when using the data throttling method. |
created_at |
timestamp |
The time when this record was created. |
updated_at |
timestamp |
The time when this record was last updated. |
Account tables
The account Sprinkle depends on the following tables:
users
This table contains records for each user.
| Column | Type | Description |
|---|---|---|
id |
int (auto-increment) |
The unique identifier for the user. |
user_name |
string(50) |
A unique text identifier for the user. Only [a-zA-Z0-9_-] characters are allowed. |
email |
string(255) |
The email address of the user. Must be unique. |
first_name |
string(20) |
The user's first name. Optional. |
last_name |
string(30) |
The user's last name. Optional. |
locale |
string(10) |
The language and locale to use for this user. |
group_id |
unsigned int |
The id of the user's group. Can be null. |
flag_verified |
bool |
Set to 1 if the user has verified their account via email, 0 otherwise. |
flag_enabled |
bool |
Set to 1 if the user account is currently enabled, 0 otherwise. Disabled accounts cannot be logged in to, but they retain all of their data and settings. |
password |
string(255) |
The hashed password (including the salt and cost function identifier) of the user. |
password_last_set |
timestamp |
The time when the user's password was last set or changed. |
deleted_at |
timestamp |
The time when this record was deleted (when using soft deletes). |
created_at |
timestamp |
The time when this record was created. |
updated_at |
timestamp |
The time when this record was last updated. |
roles
This table contains user roles.
| Column | Type | Description |
|---|---|---|
id |
string |
The unique identifier for the record. |
slug |
string(255) |
A unique text identifier for the role, to be used in URLs and other programmatic contexts. Only [a-zA-Z0-9_-] characters are allowed. |
name |
string(255) |
Name of the role. Any characters can be used. |
description |
text |
A brief description of the role and its purpose. |
created_at |
timestamp |
The time when this record was created. |
updated_at |
timestamp |
The time when this record was last updated. |
permissions
This table contains user permissions.
| Column | Type | Description |
|---|---|---|
id |
string |
The unique identifier for the record. |
slug |
string(255) |
The referencing identifier for the permission. Does not need to be unique. Only [a-zA-Z0-9_] characters are allowed. |
name |
string(255) |
Name of the permission. Any characters can be used. |
conditions |
text |
The conditions on which this permission should be evaluated. |
description |
text |
A brief description of the permission and its purpose. |
created_at |
timestamp |
The time when this record was created. |
updated_at |
timestamp |
The time when this record was last updated. |
activities
This table serves as the default storage method for user activity logs.
| Column | Type | Description |
|---|---|---|
id |
string |
The unique identifier for the record. |
user_id |
unsigned int |
The user_id of the user who completed this activity. |
ip_address |
string(45) |
The IP address of the user when they completed this activity. |
type |
string(255) |
An identifier used to track the type of activity. |
occurred_at |
timestamp |
The time when the activity was completed. |
description |
text |
A description of the activity. |
groups
This table contains records for each user group.
| Column | Type | Description |
|---|---|---|
id |
string |
The unique identifier for the record. |
slug |
string(255) |
A unique text identifier for the group, to be used in URLs and other programmatic contexts. Only [a-zA-Z0-9_-] characters are allowed. |
name |
string(255) |
Name of the group. Any characters can be used. |
description |
text |
A brief description of the group and its purpose. |
icon |
string(100) |
CSS classes identifying the icon to represent users in this group. For example, fa fa-user. |
created_at |
timestamp |
The time when this record was created. |
updated_at |
timestamp |
The time when this record was last updated. |
user_verifications
This table manages all user verification requests via one-time passwords (OTPs), including email verification, password resets, and other verification workflows.
| Column | Type | Description |
|---|---|---|
id |
string |
The unique identifier for the record. |
user_id |
unsigned int |
The user_id of the user associated with this verification. |
code |
string |
The verification code (one-time password) that the user must present to complete verification. |
expires_at |
timestamp |
The time when this verification will expire, after which the user will need a new verification. |
completed_at |
timestamp |
The time when this verification was completed (null if not yet completed). |
created_at |
timestamp |
The time when this record was created. |
updated_at |
timestamp |
The time when this record was last updated. |
Note
As of UserFrosting 6.0, the separate password_resets and verifications tables have been consolidated into the unified user_verifications table to provide a more flexible verification system.
persistences
This table stores records for recreating expired user sessions from a "Remember me" token.
| Column | Type | Description |
|---|---|---|
id |
string |
The unique identifier for the record. |
user_id |
unsigned int |
The user_id of the user to be persistently authenticated. |
token |
char(64) |
The token that the user must present to recreate their session. |
persistent_token |
char(64) |
The series identifier for the user's persistent session. |
expires_at |
timestamp |
The time when the persistent session will expire, and the user will have to reauthenticate. |
created_at |
timestamp |
The time when this record was created. |
updated_at |
timestamp |
The time when this record was last updated. |
role_users
This table maps users to roles.
| Column | Type | Description |
|---|---|---|
user_id |
unsigned int |
The user_id of the user to be associated with the role. |
role_id |
unsigned int |
The role_id of the role to be associated with the user. |
created_at |
timestamp |
The time when this record was created. |
updated_at |
timestamp |
The time when this record was last updated. |
permission_roles
This table maps roles to permissions.
| Column | Type | Description |
|---|---|---|
role_id |
unsigned int |
The role_id of the role to be associated with the permission. |
permission_id |
unsigned int |
The permission_id of the permission to be associated with the role. |
created_at |
timestamp |
The time when this record was created. |
updated_at |
timestamp |
The time when this record was last updated. |