Clever Tagline•2w ago

Request: Is a document is referenced anywhere else?

I'm building an app for work, and in the front-end there are lots of things that can be added and deleted by the user. In a few cases, I'd like to prevent a given item from being deleted if it's referenced by any other document in any other table. For example, I'm going to be tracking regions where our company has offices, so I made a regions table. There are many things that will reference a specific region—staff, contacts, deals, etc.—and I'd like to prevent a region from being removed if it's referenced by any of those things. What I'd love to have is something like a documentHasReferences helper function: * Its only argument would be the ID of a document ("Target Document") * First the function would determine the table containing that document ("Target Table") * Next it would scour all tables in the project, checking their schemas to find fields that store IDs for the Target Table * If any such fields are found on a table, it would query the table to find any reference to the Target Document in those fields * If a single reference is found on any table, it would return true otherwise it would return false I can obviously create a query that does something similar, but as I continue building the app I'll need to ensure that this query is updated as other tables are added or removed that interact with this region table. Other cases might also come up where something similar would be useful, which would require additional custom queries. The helper function I'm dreaming about wouldn't need any such updates, as it would operate using internal access to a project's tables, their schemas, etc. One function could be used in any number of use cases with any document ID. Is such a function even doable on the Convex end? If not a function, would this be possible as a component?

14 Replies

Convex Bot•2w 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!

deen•2w ago

convex-ents lets you set a table to use soft deletion as part of the schema. When you delete something, it actually just adds a deletionTime field to the document, and that's it. It does this using a few mechanisms, but all of them are just functions in your convex app. With custom functions you can make any dream a reality. The key is to ONLY use your custom functions, all the time, no matter what you're doing. Create them in functions.ts, and import from there. You can just export them as query and mutation, importing the base convex functions with an alias. If you really need to, you can create an eslint rule that prevents you from using the vanilla functions. But once you really get into them, you'll never want to use anything else. Start from this example https://stack.convex.dev/custom-functions#modifying-the-ctx-argument-to-a-server-function-for-user-auth, but we're focused on the mutation function in your case. It's wrapping the db object in an RLS function - you actually want something similar, but only for one table. You can do whatever you like to the ctx object that gets passed in to your functions. Such as: replacing the delete function with your own; intercepting the arguments, checking for the table string 'regions', and throwing an error if you find it. Otherwise just pass through to the regular delete function. That's the first step. if you can do that, implementing your custom relations check on top of it is pretty simple.

Customizing serverless functions without middleware

Re-use code and centralize request handler definitions with discoverability and type safety and without the indirection of middleware or nesting of wr...

deen•2w ago

Or you could just mirror the regions data into a second "REGIONS_BACKUP_DO_NOT_DELETE" table and call it a day. The other option is cooler though 😆

Clever TaglineOP•7d ago

I appreciate the suggestion about using Convex ents, but I'm too far into development to pivot to that setup. Just to be clear, the main purpose of my original post was to submit a feature request. Preventing a document from being deleted is a secondary issue. While I appreciate the tips in that direction, I'm still curious to hear from the dev team if that function concept I mentioned is something that could be added as a built-in Convex feature. My main goal is to find an easier way to check for any reference to a given document across all of the tables in a project without needing to build a function that queries each table one by one.

ballingt•7d ago

As a feature request this sounds like foreign key constraints. This is not something we'd plan on doing at a low level. If we did implement this, is would be as a library like deen suggests above. Maybe it's a schema table constructor that builds implicit tables for you, similar to ents. Or as a component, so that table could be hidden from you. Re ents, it seems clear from that library that there's plenty of room for a higher level interface to tables. Similar to ents, you want to work with a higher level concept, and you want that maintained for you. We don't want everyone to have to pay the cost of maintaining these constraints. Foreign key constraints don't scale well so we don't want them baked in at a deep level to Convex. But we recognize the desire for a higher-level programming interface that just tables, and the reason we keep brining up Ents is that evidence that we can build good abstractions over the Convex DB interface. Right now developers are doing this themselves, but we can offer some of these useful things (e.g. foreign key constraints, cascading deletes, UNIQUE constraints) that we know are problematic at scale at the user-space level, i.e. in TypeScript instead of in Rust. Reading your final question, yes it's doable as a component! But you'd also need intercept calls somehow today, hence the custom functions. Or a custom db object you use instead of ctx.db, even if it's not injected into every function. Lately we're feeling custom functions might also be too obfuscating in some cases. Instead of custom functions, you can always write code like

const foo = mutation(ctx => {
  const myOrm = MyOrm(ctx);
  await myOrm.insertWhileMaintainingForeignKeyConstraints("messages", {body: 'hello'});
});

const foo = mutation(ctx => {
  const myOrm = MyOrm(ctx);
  await myOrm.insertWhileMaintainingForeignKeyConstraints("messages", {body: 'hello'});
});

Custom functions are great for making sure you always use the other interface, but you can also use a linter for that like deen mentions.

Clever TaglineOP•7d ago

Appreciate the feedback, Tom. I'm not familiar with foreign key constraints, but after looking it up, I'm not sure that it matches my desire for this function that I'm envisioning. The reference I found says that foreign key constraints are "used to prevent actions that would destroy links between tables." In the situation I described, I don't want to prevent any action on the Convex side. I guess what I'm looking for is a project-wide search feature: search all tables for any reference to a given document. In fact, I don't even want the actual references found. I just want a boolean that tells me whether or not any foreign keys exist. Whatever calls this function would be responsible for deciding what to do with the result.

ballingt•7d ago

Ah yeah I'm skipping a step here, I mean these sounds similar because they'd both require keeping track of foreign keys. some kind if index that reference-counts foreign keys which is the main cost of both a foreign key constraint and the API you'd like different in that for you case you don't care where they are, so a little less storage, but either way every time we write or delete a foreign key we need to record that

Clever TaglineOP•7d ago

So in short, it's doable, but would add a lot of overhead even to track documents in a single field. I can accept that. I'll just work with what I've got and try to be careful about remembering to add the relevant table searches.

deen•6d ago

I was using ents as an example of an implementation of delete "guard" functionality (and anticipating the general vibe of Tom's answer) - it's a lot simpler than it sounds. If this check would only happen occasionally, and the regionId fields were indexed (helpfully with a common name), you could enumerate your schema for any tables that have that field, and query for a matching id. I did something similar to look for orphaned _storageIds in an automated fashion - though in my case it was to delete them, but you could just as easily report their existence to the function caller.

Clever TaglineOP•6d ago

If this check would only happen occasionally, and the regionId fields were indexed (helpfully with a common name), you could enumerate your schema for any tables that have that field, and query for a matching id.

How would that be accomplished? I've not delved into traversing the schema programmatically.

ballingt•6d ago

Yeah, and with the addition that if we're going to do something expensive to provide a feature we'd generally prefer it happen in userspace ie typescript

deen•6d ago

There isn't a straightforward way of getting the schema definitions back once you've defined them - visible to the naked eye at least. But you already have this information, since you most likely wrote it by hand - you just need to adjust the way you define it, so you can use for whatever you like.

const tableDefinitions = {
  regions: defineTable({
    name: v.string(),
    size: v.number(),
  }),
  offices: defineTable({
    name: v.string(),
    address: v.string(),
    regionId: v.id('region')
  }),
}

const schema = defineSchema(
  tableDefinitions,
)

export default schema

export const tableNames = Object.keys(tableDefinitions)

const tableDefinitions = {
  regions: defineTable({
    name: v.string(),
    size: v.number(),
  }),
  offices: defineTable({
    name: v.string(),
    address: v.string(),
    regionId: v.id('region')
  }),
}

const schema = defineSchema(
  tableDefinitions,
)

export default schema

export const tableNames = Object.keys(tableDefinitions)

This gives you all of your table names, which you could work with, but you can do better if you pop the fields object out of each table into a variable - then you have the table names with field names. A clever definition pattern could allow you to get every table and field name with a single Object.keys or Object.entries read, exporting the data for your use - and if set up cleanly, you won't even notice it's there. Then you could nominate regionId as a field of interest in your helper function, loop through the table names which have it, query their index, etc. - however works best for your use case. You wouldn't want to run this search as part of just any basic query for regions data in your app, but if it's for a special occasion, like trying to delete a region - then I think that's fine. Broadly, this a similar strategy to the custom delete function I suggested - shimming a function of your own into the process, so that you can observe or redirect the flow. Once you get hang of it - it's usually just a few functions to write between you and your goal (like anything else in convex), and then it's not hard to do it in such a way that you barely even notice it's there.

deen•6d ago

After starting a new project with "vanilla" convex, I've spent much of my time experimenting with different patterns for working with my data (instead of actually making the app) - I fully expected to make heavy use of custom ctxes, but found myself instead naturally moving towards this approach. There could a middle ground - I think the current process of defining custom functions is kinda confusing and feels a bit too "magic". Maybe something more structured and formal could ease the cognitive burden - some fancy classes with descriptive names, custom types the user defines that you can see in your ctx after, formalized custom function chaining etc. Maybe even something your convex folders starts out with, in a basic form. It wasn't until after tackling and eventually taming this (needing a handful of attempts, over the course of a few months), that it clicked for me: https://labs.convex.dev/convex-ents/schema/rules#apply-rules It's actually fairly straightforward, but it just looks ugly and weird as hell 😁

Rules - Convex Ents

Relations, default values, unique fields and more for Convex

ballingt•6d ago

totally, the code for your ctx being templated into your project so you can modify would be cool, that's one possible way components could work in the future. Would make the frustration of "why can't I change how ctx.auth.getUserIdentity" much smaller because it's more obvious that you can stick whatever you want on your ctx.