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 app/system/Database/Migrations.
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 |
string |
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. |