Clever Tagline•2d ago

Advice re: schema design for user-specific role-based properties

I'd love some help brainstorming a solution to an app problem. I'm building a staff dashboard app for work. Users of this app will each have one or more assigned roles. I've got both a users table and a roles table, with the relationship between them already established. No issues there. For some (but not all) roles, there will be custom settings that are exclusive to the role. For example, with the Salesperson role there's a default commission percentage for all salespeople, but an individual salesperson might have a commission that overrides that default. While I could create something like a commission field directly on the users table to store this value, I really don't like to have fields that are only needed in limited use cases. The commission override is just one example of a role-specific setting, and there could be a lot more over time, so I'm trying to think of a more efficient way to save such settings without creating tons of limited-use fields. The idea that I'm leaning toward is to have a multi-purpose roleSettings field that would store an object kinda like this:

{
  "roleIdA": {
    "roleA_setting1": "value",
    "roleA_setting2": "value"
  },
  "roleIdB": {
    "roleB_setting1": "value"
  }
}

{
  "roleIdA": {
    "roleA_setting1": "value",
    "roleA_setting2": "value"
  },
  "roleIdB": {
    "roleB_setting1": "value"
  }
}

The keys in the main object would be document IDs from the roles table, with the value being an appropriate object containing the available settings for that role. While this feels like the most appropriate way to minimize fields and maximize flexibility, I'm struggling to figure out how to represent that in a schema definition, with an arbitrary number of ID keys, and the value associated with each key being an object with a format specific to that key. Is that even possible with the validator options?

5 Replies

Convex Bot•2d ago

Thanks for posting in <#1088161997662724167>. Reminder: If you have a Convex Pro account, use the Convex Dashboard to file support tickets. - Provide context: What are you trying to achieve, what is the end-user interaction, what are you seeing? (full error message, command output, etc.) - Use search.convex.dev to search Docs, Stack, and Discord all at once. - Additionally, you can post your questions in the Convex Community's <#1228095053885476985> channel to receive a response from AI. - Avoid tagging staff unless specifically instructed. Thank you!

ballingt•2d ago

One thought is that you can nest these, so you could put them right in your roles table.

{
  role: "salesperson",
  user: "12lk3j12kl3j",
  roleSettings: {
    commision: .05,
    rank: "K"
  }
},
{
  role: "admin",
  user: "12lk3j12kl3j",
  roleSettings: {
    permissions: "superuser",
    favoriteNumbers: [1, 2, 3],
    pets: [
      { name: 'fido', type: 'dog' }
    ]
  }
}

{
  role: "salesperson",
  user: "12lk3j12kl3j",
  roleSettings: {
    commision: .05,
    rank: "K"
  }
},
{
  role: "admin",
  user: "12lk3j12kl3j",
  roleSettings: {
    permissions: "superuser",
    favoriteNumbers: [1, 2, 3],
    pets: [
      { name: 'fido', type: 'dog' }
    ]
  }
}

Or if you wnat a separate table you can do that too. Validators allow unions (so you can type the role settings for each role differently), arrays so you can list many; optional fields so an doesn't have to contain every possible field

Clever TaglineOP•2d ago

The roles table is meant for global data about roles, though—e.g. only one "Salesperson" role record exists—and the users table has a "roles" field defined as v.optional(v.array(v.id("roles"))). Going back to my brainstorm above, my thought was that if I find the ID for the "Salesperson" record in this "roles" field, I should expect to find a key in this theoretical object that's the ID of that role, with the value being a known settings object matching a certain format. Your suggestion (minus the "user" field) would work for creating defaults for those settings, but I feel the user-specific values should live on the user's own record. I just can't think of a way to do that so that I could collect the data and have the types known in advance; i.e. using your example, know that "commission" is a number, "rank" is a string, etc. Or are you saying that by defining the settings object in the original record in "roles," I could use that to infer the type elsewhere? I keep thinking that v.record could be used somehow, but I don't fully understand it. Would something like this work as a validator for a field?

v.record(v.id("roles"), v.union(
  // Salesperson settings
  v.object({
    commission: v.number(),
    rank: v.string()
  }),

  // Another role
  v.object({
    canDoStuff: v.boolean()
  }),
  ...
))

v.record(v.id("roles"), v.union(
  // Salesperson settings
  v.object({
    commission: v.number(),
    rank: v.string()
  }),

  // Another role
  v.object({
    canDoStuff: v.boolean()
  }),
  ...
))

ballingt•2d ago

Ah ok so you need three tables if roles is dynamic; users, roles, and a join table although I'd think about how dynamic you really need it to be, if there's no form where someone adds new roles types maybe you don't need a table for it

but I feel the user-specific values should live on the user's own record. I just can't think of a way to do that so that I could collect the data and have the types known in advance; i.e. using your example, know that "commission" is a number, "rank" is a string, etc.

OK you can do this!

v.object({
  salepersonSettings: v.optional(v.object({ rank: v.number() })),
  adminSettings: v.optional(v.object({ pets: v.array(v.objec({ ... })) }))
})

v.object({
  salepersonSettings: v.optional(v.object({ rank: v.number() })),
  adminSettings: v.optional(v.object({ pets: v.array(v.objec({ ... })) }))
})

Or yeha the record thing, although then you can't type each on separately

Clever TaglineOP•this hour

Funny you should mention a form for new roles... (yes, I've already made one) For now, this is being designed for our own company, and our current roles are pretty well-defined. However, the owner has grand plans for taking what I build and eventually marketing it as a product that others can purchase and use for other types of service businesses. That's been my big struggle with anything I'm doing: make it work for us and our current needs, but also make it somewhat flexible so that it can work for his Grand and Glorious Plan™️ And I'm the lone dev The farther I go into this, the more I feel like I need to back away from the "make it work for every use case" approach, as it's just adding complexity and time that we can't afford right now. This definitely looks promising...

Advice re: schema design for user-specific role-based properties

Did you find this page helpful?