How would you structure this schema?

I have the following schema:

export default defineSchema({
  users: defineTable({
    name: v.string(),
    pictureUrl: v.union(v.string(), v.null()),
    tokenIdentifier: v.string(),
  }).index("by_token", ["tokenIdentifier"]),

  canvases: defineTable({
    svgDocument: v.string(),
    members: v.array(
      v.object({
        userId: v.id("users"),
        role: v.union(v.literal("owner"), v.literal("editor")),
      }),
    ),
  }).index("by_authorUserId", ["authorUserId"]), // want to be able to get canvases by authorUserId but I cant do that with this index
});

export default defineSchema({
  users: defineTable({
    name: v.string(),
    pictureUrl: v.union(v.string(), v.null()),
    tokenIdentifier: v.string(),
  }).index("by_token", ["tokenIdentifier"]),

  canvases: defineTable({
    svgDocument: v.string(),
    members: v.array(
      v.object({
        userId: v.id("users"),
        role: v.union(v.literal("owner"), v.literal("editor")),
      }),
    ),
  }).index("by_authorUserId", ["authorUserId"]), // want to be able to get canvases by authorUserId but I cant do that with this index
});

Note I want to be able to list all the canvases that a user is a member of but I cant. Im guessing I have to reverse this structure and have a "canvasMembers" table that records the userId and canvasId and then index and query that first then join to get the canvases? Just checking incase there is a better way.

4 Replies

jamwt•16mo ago

Yep. A junction table is the right call here. https://stack.convex.dev/relationship-structures-let-s-talk-about-schemas

Relationship Structures: Let's Talk About Schemas

In this post we’ll look at some patterns for structuring relationships in the Convex database.

jamwt•16mo ago

Very similar to traditional databases. That way you can create the indexes you want. To quickly produce that list of canvases per member. Many-to-many.

mikeyseeOP•16mo ago

okay.. so just while I have you jamie. It would look something like this:

export const list = query({
  args: {},
  handler: async ({ db, auth }, {}) => {
    const user = await getMe({ auth, db });

    const members = await db
      .query("canvasMembers")
      .withIndex("by_userId", (q) => q.eq("userId", user._id))
      .take(20);

    const canvases = await Promise.all(members.map(m => db.get(m.canvasId)));

    return canvases;
  },
});

export const list = query({
  args: {},
  handler: async ({ db, auth }, {}) => {
    const user = await getMe({ auth, db });

    const members = await db
      .query("canvasMembers")
      .withIndex("by_userId", (q) => q.eq("userId", user._id))
      .take(20);

    const canvases = await Promise.all(members.map(m => db.get(m.canvasId)));

    return canvases;
  },
});

Wouldnt this Promise.all( cause me a high number of db reads from my quota? I guess as you say, if im concerned about that then I should create a many-to-many table to manage that

jamwt•16mo ago

It should be okay. Also, find a lot of useful utility functions here for parallel/high performance reference traversal: https://stack.convex.dev/functional-relationships-helpers

Functional Relationships: Helpers

In this post, we’ll look at some helper functions to help write code to traverse relationships in a readable, predictable, and debuggable way.