Store User Preferences / Settings in the Database

[elias-ba]

User Story

Create a user preferences system that allows us to store and manage user settings effectively within the existing users table. We will use a JSONB column to simplify the structure and provide a flexible way to handle various user preferences.

Proposed Structure

Insead of creating a new key value table, update the users table to add a preferences column which is a JSONB column to store user preferences in a keyed and namespaced format.

Example JSON structure for the preferences:

{
  "onboarding.seen": true,
  "editor.orientaion": "vertical"
}

Details

• User Preferences Management:

  • Use the JSONB column for storing preferences, allowing us to easily update specific settings without needing multiple rows.
  • For example, to mark the onboarding as seen:

    user
    |> User.changeset(%{preferences: Map.put(user.preferences, "onboarding.seen", true)})
    |> Repo.update()

  • Querying specific preferences can be done efficiently using:

    from(u in User, where: fragment("?->>'onboarding.seen' = 'true'", u.preferences))
    |> Repo.all()

• Benefits:

  • Simplifies the user preferences structure by reducing the complexity of managing multiple rows.
  • Makes it easier to manage and update individual preferences.
  • Allows for better organization of preferences through namespacing, enhancing readability and maintainability.

Implementation Ideas

• Update the existing users table to include the new JSONB column for user preferences.
• Modify the user model and associated changesets to handle the new preferences structure.
• Integrate with the issue #2563 to track the onboarding section state or progress of the Arkade tutorial videos