Link property challenges

[akhayyat]

I'm running into a few challenges related to link properties in many-to-many relationships. Here is a minimal schema that demonstrate these challenges:

module default {
  scalar type UserRole extending enum<Owner, Admin, Manager, Viewer>;

  abstract type Timestamped {
    required created_at: datetime {
      readonly := true;
      default := datetime_current();
    }
    required updated_at: datetime {
      rewrite insert, update using (datetime_current());
    }
  }

  abstract link TimestampedLink {
    # TODO: required (1)
    created_at: datetime {
      readonly := true;
      default := datetime_current();
    }
    # TODO: required (1)
    updated_at: datetime {
      # rewrite insert, update using (datetime_current()); (2)
    }
  }

  type User extending Timestamped {
    required name: str {
        constraint exclusive;
    }
    required is_admin: bool {
      default := false;
    }
  }

  type Business extending Timestamped {
    required name: str {
      constraint exclusive;
    }
    required multi users: User {
      extending TimestampedLink;
      # TODO: required (1)
      user_role: UserRole;
    }
    # Triggers
    # Each business must have exactly one owner (3) (4)
    trigger ensure_single_owner after insert, update for each do (
      assert(
        ((select count(__new__.users) filter __new__.users@user_role = UserRole.Owner) = 1),
        message := "A business must have exactly one owner."
      )
    )
  }
}

The challenges are annotated in the code above with their numbers, e.g. (1), (2), etc.

1. Required link properties. See the lines with (1) above.
   I'm aware of Reconsider allowing link properties to be required #5571. In the meantime, are there any workarounds that can have the effect of making a link property required?

2. Rewrite mutation on a link property. See the line with (2) above. Uncommenting this line produces the following error:

   Schema migration error: InternalServerError: AssertionError:
   

   As a result, I'm able to get an updated_at link property, but I'm unable to have it set automatically on insertions and updates. Is there a way to make this work?

3. Validation trigger to enforce filtered link cardinality produces warnings in migrations and in the REPL. See the trigger defined on the lines following (3) in the schema code above. This trigger produces the following warning:

   warning: QueryError (3)
       possibly more than one element returned by an expression in a FILTER clause
   

   I could not find any reference to this message in discussions or issues. How can I fix the trigger to prevent this warning/error?

4. The same validation trigger of (3) is partially effective: it does prevent more than one user with the Owner role for the same business, but it does not prevent 0 owners. Before using assert(select count(...) = 1), I tried assert_single(), which had the same problem. However, the documentation of assert_single() is consistent with this behavior: "Checks that the input set contains no more than one element.".

   Now, for assert(select count(...) = 1), I can see that select count(...) does not return anything in case there are no owners (as opposed to returning 0), which kind-of explains the observed behavior. But the question remains: how do I catch the case of 0 owners?

• Gel Version: 6.3+2c3fb2b
• Gel CLI Version: Gel CLI 7.1.1+45ade47
• OS Version: Debian testing (trixie/sid)

[akhayyat]

Figured out (3) and (4), by changing the trigger from:

    # Each business must have exactly one owner (3) (4)
    trigger ensure_single_owner after insert, update for each do (
      assert(
        ((select count(__new__.users) filter __new__.users@user_role = UserRole.Owner) = 1),
        message := "A business must have exactly one owner."
      )
    )

to:

    trigger ensure_single_owner after insert, update for each do (
      assert((
        with b := (select Business {
          owners := (select .users filter @user_role=UserRole.Owner)
        } filter .id = __new__.id)
        select count(b.owners)) = 1,
        message := "A business must have exactly one owner."
      )
    );

I'd appreciate some help with (1) and (2).

[akhayyat]

About (2) - rewrite mutations on link properties, I see that #6466 (merged) should show a more helpful message, yet I still see the original error:

Schema migration error: InternalServerError: AssertionError: 

If rewrite mutations are not supported on link properties, are there any alternatives? I'd think the updated_at link property on many-to-many relationships is quite common. How does one make it work?

[scotttrinh]

However, this does not seem to enforce requiring a business user upon inserting a business. I can now create a business without any users. The original schema did not allow that. A user had to be set as an owner while inserting a business. That is the desired behavior.

Yeah, that's a good point! Doing an assert on the computed, as you've seen, only gets run when actually querying this type, so it wouldn't trigger in a normal insert. Maybe a better design for this, since there is a clear requirement in one direction, is reverse the direction of the link between Business and BusinessUser. Basically add an required multi business_users: BusinessUser; forward link, and a required single business := .<business_users[is Business] to the BusinessUser side. I think you could even flip both sides of the link if you also wanted to require that a user is a user of at least one Business, but that might have some difficult ergonomics when inserting data. I'll see if some of my colleagues might be able to add some advice here as well.

then inserting a business without any users produces the following error:

I think you would also get this error if queried for Business.business_users because how assert_exists works is that it triggers a query-time error if the set is empty.

which suggests that the required multi backlink was triggered, apparently by the trigger, as if it would not be triggered otherwise. Now, the trigger seems excessive. Its only purpose is to trigger the assert in the backlink.

Maybe, maybe not! The trigger is actually testing that there is exactly one Owner, which is somewhat separate from what required multi is enforcing, so it's not entirely excessive.

[akhayyat]

Update:
This is the best I was able to do so far:

  type Business extending Timestamped {
    # Backlinks
    required multi users := assert_exists(.<business[is BusinessUser].user);
    required multi business_users := assert_exists(.<business[is BusinessUser]);
    # Triggers (should be redundant, but seems to be required to enforce `required` above
    trigger check_users after insert for each do (
      assert_exists(__new__.business_users)
    )
  }

  type BusinessUser extending Timestamped {
    required user_role: UserRole;
    # Links
    required business: Business {
      on source delete allow;
    };
    required user: User {
      on source delete allow;
    };
    # Constraints
    constraint exclusive on ((.business, .user));
    # Triggers
    trigger ensure_single_owner after insert, update for each do (
      assert(
        count((
          select BusinessUser filter .business = __new__.business and .user_role = UserRole.Owner
        )) = 1,
        message := "A business must have exactly one owner."
      )
    );
    trigger ensure_single_owner_on_delete after delete, update for each do (
      assert(
        count((
          select BusinessUser filter .business = __old__.business and .user_role = UserRole.Owner
        )) = 1,
        message := "A business must have exactly one owner."
      )
    );
  }

A few notes and questions:

1. I simplified the "redundant" Business trigger, and I'm still getting the desired behavior Is there a way to do away with the redundant Business trigger? It does not add additional logic compared to the required multi backlinks, yet the links appear to be insufficient to prevent inserting a Business that has no users. insert Business {} succeeds without the trigger, but fails with the trigger with the message gel error: CardinalityViolationError: assert_exists violation: expression returned an empty set, not with the trigger message, indicating that the required backlink needed to be nudged to do its thing.
2. Is there a way to consolidate the BusinessUser triggers? (insert/update trigger, and delete/update trigger) The only difference is __new__ vs __old__ (for example, can __new__ and __old__ be coalesced?
3. I updated the BusinessUser trigger. Is there any material difference between your version and this version in terms of performance? For example, does your version require joins behind the scenes?

[akhayyat]

Here is another data point. I updated the Business type as follows (added custom messages to all assertions):

...
required multi users := assert_exists(
  .<business[is BusinessUser].user,
  message := "users backlink assertion failed"
);
required multi business_users := assert_exists(
  .<business[is BusinessUser],
  message := "business_users backlink assertion failed"
);
...
trigger check_users after insert for each do (
  assert_exists(__new__.business_users, message := "Business trigger failed")
)

The reason I think the trigger is only nudging the required link instead of enforcing the required owner itself is, with the change above on the schema in my previous comment:

with bu := (
  insert BusinessUser {
    business := (insert Business {name:="B1"}),
    user := (select User filter .id = <uuid>"3367993a-08dd-11f0-8eb7-53fa0c590a87"),
    user_role:=UserRole.Owner
  }
) select bu.business {};

works.

with bu := (
  insert BusinessUser {
    business := (insert Business {name:="B1"}),
    user := (select User filter .id = <uuid>"3367993a-08dd-11f0-8eb7-53fa0c590a87"),
    user_role:=UserRole.Viewer
  }
) select bu.business {};

fails with the error:
gel error: QueryAssertionError: A business must have exactly one owner.

insert Business {name:="B1"};

fails with the error:
gel error: CardinalityViolationError: business_users backlink assertion failed
(not the Business trigger assertion!)

insert Business {name:="B1"};

with the Business trigger removed succeeds (it should not).

That is, whenever there is a BusinessUser involved, the BusinessUser trigger kicks in. Whenever there isn't, however, the required multi backlink's assertion is kicking in only if the Business trigger is present!!!

[akhayyat]

Maybe a better design for this, since there is a clear requirement in one direction, is reverse the direction of the link between Business and BusinessUser. Basically add an required multi business_users: BusinessUser; forward link, and a required single business := .<business_users[is Business] to the BusinessUser side.

Let's try that:

type Business extending Timestamped {
  ...
  required multi business_users: BusinessUser {
    constraint exclusive;
  }
  required multi users := .business_users.user;
  ...
}
type BusinessUser extending Timestamped {
  required single business := assert_exists(.<business_users[is Business]);
  # constraint exclusive on ((.business, .user));
}

The commented out constraint results in the error:
constraints cannot contain paths with more than one hop

Without this constraint, it is possible for the same business to have two BusinessUser links having the same user, isn't it? (multiple links between the same business-user pair.)

[akhayyat]

Any updates on this:

That is, whenever there is a BusinessUser involved, the BusinessUser trigger kicks in. Whenever there isn't, however, the required multi backlink's assertion is kicking in only if the Business trigger is present!!!

Is this a bug, or a misunderstanding on my part?

[vpetrovykh]

I would recommend sticking with the intermediate object BusinessUser that points to both Business and User because then you can keep the constraint exclusive on ((.business, .user)) that ensures that the same User cannot be associated with a Business twice.

That is, whenever there is a BusinessUser involved, the BusinessUser trigger kicks in. Whenever there isn't, however, the required multi backlink's assertion is kicking in only if the Business trigger is present!!!

Correct, triggers only activate when their object type is somehow used directly in a query. So when you run insert Business {name:="B1"};, only the Business object triggers activate.

The misunderstanding here is what required means on the computed link or property. Computed links and properties are only evaluated when explicitly accessed by a query. They don't exist as real data in a table, instead they are dynamically evaluated on an as-needed basis. So a required keyword on a computed link means that if you successfully access the link, it is guaranteed to be non-empty, but there's the possibility (via the assert_exists) that you will get an error instead. Because the computed value is not stored as data there is no associated automatic data integrity check either, only the assert_exists that's part of the computed expression. This is why you need a separate insert trigger to actually perform the data integrity check for Business object.

So I suggest the following schema:

module default {
  scalar type UserRole extending enum<Owner, Admin, Manager, Viewer>;

  abstract type Timestamped {
    required created_at: datetime {
      readonly := true;
      default := datetime_current();
    }
    required updated_at: datetime {
      rewrite insert, update using (datetime_current());
    }
  }

  type User extending Timestamped {
    required name: str {
        constraint exclusive;
    }
    required is_admin: bool {
      default := false;
    }
  }

  type Business extending Timestamped {
    required name: str {
        constraint exclusive;
    }
    # Backlinks
    required multi users := assert_exists(.<business[is BusinessUser].user);
    required multi business_users := assert_exists(.<business[is BusinessUser]);
    # Trigger ensuring that `required` computed links are in fact created during insert
    trigger check_users after insert for each do (
      assert_exists(
        # Do not use the computed link since accessing it will first activate
        # the assertion that's part of the computed expression. Instead check
        # that a BusinessUser has been linked by using the backlink directly,
        # bypassing computed expression.
        __new__.<business[is BusinessUser],
        message := "A business must have exactly one owner."
      )
    )
  }

  type BusinessUser extending Timestamped {
    required user_role: UserRole;
    # Links
    required business: Business {
      on source delete allow;
    };
    required user: User {
      on source delete allow;
    };
    # Constraints
    constraint exclusive on ((.business, .user));
    # Triggers
    trigger ensure_single_owner after insert, update for each do (
      assert(
        count((
          select BusinessUser filter .business = __new__.business and .user_role = UserRole.Owner
        )) = 1,
        message := "A business must have exactly one owner."
      )
    );
    trigger ensure_single_owner_on_delete after delete, update for each do (
      assert(
        count((
          select BusinessUser filter .business = __old__.business and .user_role = UserRole.Owner
        )) = 1,
        message := "A business must have exactly one owner."
      )
    );
  }
}

[akhayyat]

Thank you so very much, @vpetrovykh, for the complete answer and the detailed explanation and examples. Really appreciated.

One follow up point..

The misunderstanding here is what required means on the computed link or property. Computed links and properties are only evaluated when explicitly accessed by a query.

If I remove required and assert_exists() from the Business computed backlinks (while keeping the trigger), I don't think I'd lose anything, do I? I'd still have exactly the same enforcement of the desired integrity requirements (exactly one owner per business). Correct?

This brings me to my next point: what's the value of supporting required computed properties or links?
Since it's declared in a schema, it's reasonable to expect it to be automatically enforced. Since it cannot be enforced on its own, it could be misleading.
Worse, it leads to run-time errors due to the query-time assertion, all without guaranteeing the intended constraint it's introduced to uphold.

The lesson I learned here is: never use required on a computed property or link. I'm wondering if that's something that the language should observe.

[vpetrovykh]

What marking a computed property or link as required gives is static inference guarantees. An interface can rely on the fact that if the data is fetched, then it will never be empty for any of the required fields. It also means that using that field elsewhere is safe without first checking for empty value, which can simplify logic in other parts of the application.

There are plenty of cases where an assert_exists() is not necessary for Gel to statically determine that the expression is not empty, but mostly these are going to be computed properties. E.g. required foo := count(.<some[is Stuff]), or required bar := array_agg(.children.val), or required domain := str_split(.email, '@')[1], etc.

Essentially for computed properties the required qualifier enforces data integrity upon reading the data, which is potentially useful for an application that's consuming the values. It also forces Gel to do static schema analysis and validate that the computed expression is non-empty. Which can be useful for cases like required domain := str_split(.email, '@')[1]. It basically makes sure that if you ever attempt to modify the schema to make email optional, you will not forget to update the domain as well, to either make it optional or to change the expression in some other way that keeps the required promise.

Of course, if your app does not rely on any of the above features, feel free to keep your computeds optional (which they are by default) and free from assert checks. There are many cases where computeds don't need to be complicated and can be just simple expressions used as convenient shortcuts.

[vpetrovykh]

To illustrate why an automatic check is not as straightforward as it sounds imagine the following schema:

type HeartBeat {
  required created_at: datetime {
    readonly := true;
    default := datetime_current();
  }
}

type Foo {
  required multi recent_beats := assert_exists(
    (select HeartBeat filter .created_at > (datetime_current() - <duration>'10m')),
    'HeartBeat lost for more than 10 minutes'
  )
}

There is nothing that can be checked when HeartBeat simply stops being inserted. So no "automatic" data integrity checks will help. Also, potentially there are other recovery mechanisms and processes that can maintain this fictional system and do whatever is needed to "restore HeartBeat". You also don't necessarily want to break all access to Foo while you're fixing the HeartBeat.

Hope this helps to understand why required computeds have two separate mechanisms available for checking their validity:

1. the computed expression itself can use various assert functions to validate the data as it's being accessed
2. triggers on objects may be used to validate the data without explicitly accessing the computed expression during various operations that modify the data

[vpetrovykh]

2. Is there a way to consolidate the BusinessUser triggers? (insert/update trigger, and delete/update trigger) The only difference is __new__ vs __old__ (for example, can __new__ and __old__ be coalesced?

TL;DR
No, these two triggers are about as consolidated as they can reasonably get.

Actually, coalescing them is probably not going to help you "consolidate" triggers in many cases, here's why:
The data integrity that you seem to be after is that "every Business must have exactly one Owner". This invariant should be maintained after any changes in data. The issue is that __old__.business and __new__.business are potentially two different Business objects, each of which must have exactly one Owner. Coalescing may seem like a useful consolidation for insert and delete triggers, because you're guaranteed that only one of them will link to a Business. For update triggers, however, both __old__.business and __new__.business are valid and must have their own data integrity check. So you would still end up with at least 2 versions of the trigger, but they might be harder to understand if you used __old__ ?? __new__ in one of them (insert and delete trigger) and a larger expression for correctly counting owners in the other one (update trigger).

Just for fun, imagine that __old__ and __new__ could be used in all triggers, this is what a single consolidated expression might look like:

    trigger xxx after insert, delete, update for each do (
      assert(
        all((
          with
            # Don't use coalesce, we need both potentially distinct businesses
            B := {__old__, __new__}.business,
            B_w_count := B {
                # for each Business count the owners, don't use computeds to avoid asserts in them
                num_owners := count((
                    select .<business[is BusinessUser]
                    filter .user_role = UserRole.Owner
                )),
            },
          # check whether the count is 1 for each Business
          select B_w_count.num_owners = 1
        )),
        # ^^^ The whole expression of having 1 owner is wrapped in `all` because it must be true for all Businesses.
        # Luckily, the `business` link is `required` and so we can guarantee that `all` will never be applied to an empty set.
        # This is good because `all({}) = True` and we'd need a separate check for emptiness if the links weren't required.
        message := "A business must have exactly one owner."
      )
    );

Arguably, the above consolidated expression is not "intuitive" or "obvious", and thus harder to read and write than two much simpler non-consolidated trigger expressions for __old__ and __new__ respectively.