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.
`theme`	`string(100)`	The user theme (not yet fully implemented).
`group_id`	unsigned `int`	The id of the user's group.
`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.
`last_activity_id`	unsigned `int`	The id of the last activity performed by this user.
`password`	`string(255)`	The hashed password (including the salt and cost function identifier) of the user.
`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.

`password_resets`

This table contains records for each password reset request that is issued.

Column	Type	Description
`id`	`string`	The unique identifier for the record.
`user_id`	unsigned `int`	The `user_id` of the user who requested the password reset.
`hash`	`string(255)`	The secret token, emailed to the user, that they must present to complete the reset.
`completed`	`bool`	Flags whether or not the reset was completed successfully.
`expires_at`	`timestamp`	The time when this request will expire, after which the user will need to submit a new request.
`completed_at`	`timestamp`	The time when this request was completed.
`created_at`	`timestamp`	The time when this record was created.
`updated_at`	`timestamp`	The time when this record was last updated.

`verifications`

This table contains records for new account verification tokens.

Column	Type	Description
`id`	`string`	The unique identifier for the record.
`user_id`	unsigned `int`	The `user_id` of the user to be verified.
`hash`	`string(255)`	The secret token, emailed to the user, that they must present to complete the verification.
`completed`	`bool`	Flags whether or not the verification was completed successfully.
`expires_at`	`timestamp`	The time when this request will expire, after which the user will need to resend the verification request.
`completed_at`	`timestamp`	The time when this request was completed.
`created_at`	`timestamp`	The time when this record was created.
`updated_at`	`timestamp`	The time when this record was last updated.

`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`	`string(40)`	The token that the user must present to recreate their session.
`persistent_token`	`bool`	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.