How should I structure my database entities with invites and recipients for e-signing web app?

Question

I am interested in implementing the following requirements for my e-signature web application.

1. A user can create a new signing contract. That contract can include multiple users to sign. The contract creator needs to provide emails of the recipients. Every recipient will have additional data assigned, like signing details, instructions and etc.
2. However, the invited user can still be not present in the system. This is the trickest part.

Right now my following implementation is the following:

1. I create a contract, then check if a user is present in the system by making a filter by email. If the user exists, I create a many-to-many entity ContractRecipientEvent using intermediate table with additional data, which is assigned to the contract. I create it many-to-many because the same user can be assigned to multiple contracts.
2. If the user is not present I create the Invitation model, set all recipient's specific data, and send an email. Then the user is registered, I run the query of all Invitations records with that email and create ContractRecipientEvent, by copying data from the Invitation model.

What I don't like with my approach are the following things:

1. Many-to-many field. I would like to just use plain Foreign keys for my contract recipients, but I am unsure how should I assign multiple users to the same contract? Perhaps I should create a new model ContractRecipient with the user and contract as foreign keys, but that is a many-to-many field also?
2. I don't like that I need to copy data from the Invitation model to the ContractRecipientEvent and only create ContractRecipientEvent after the user is registered, because I need a user entity to create a ContractRecipientEvent, which has a foreign key to the user.
3. The permission structure is difficult to manage. I need to check all the users, who are included in the contract database record, and check if they are assigned to the contract id, they are using for the signing POST request.

I am attaching my final JSON code of the contract list. It works, but I would like to have a correct models structure:

{
  "results": [
    {
      "id": 178,
      "is_author": true,
      "title": "ahhzhzh",
      "message_to_all_recipients": null,
      "contract_signing_status": "WAITING_FOR_ME",
      "contract_signing_type": "SIMPLE",
      "contract_signing_date": {
        "start_date": "2010-09-04T14:15:22Z",
        "end_date": "2010-09-04T14:15:22Z"
      },
      "recipients": [
        {
          "message": null,
          "recipient_signing_status": "NOT_SIGNED",
          "recipient_review_status": "NOT_REQUIRED",
          "recipient_action": "SIGN",
          "role": "ADMIN",
          "email": "[email protected]"
        },
        {
          "message": null,
          "recipient_signing_status": "NOT_SIGNED",
          "recipient_review_status": "NOT_REQUIRED",
          "recipient_action": "SIGN",
          "role": "BASE",
          "email": "[email protected]"
        }
      ]
    },
    {
      "id": 179,
      "is_author": true,
      "title": "dhhdhd",
      "message_to_all_recipients": null,
      "contract_signing_status": "WAITING_FOR_ME",
      "contract_signing_type": "SIMPLE",
      "contract_signing_date": {
        "start_date": "2010-09-04T14:15:22Z",
        "end_date": "2010-09-04T14:15:22Z"
      },
      "recipients": [
        {
          "message": null,
          "recipient_signing_status": "NOT_SIGNED",
          "recipient_review_status": "NOT_REQUIRED",
          "recipient_action": "SIGN",
          "role": "ADMIN",
          "email": "[email protected]"
        },
        {
          "message": null,
          "recipient_signing_status": "NOT_SIGNED",
          "recipient_review_status": "NOT_REQUIRED",
          "recipient_action": "SIGN",
          "role": "BASE",
          "email": "[email protected]"
        }
      ]
    },
 
  ]
}

Answer 1

First of all, do not start optimizing your database schema or imaging you can cut corners. Try to come up with more or less normalized form, which corresponds best with your business domain. Mismodelling will backfire later, it's best to try to make the model right from the start. Even such a small detail like email as the "natural primary key" can lead to headaches later, when you will start thinking of adding phone numbers or some hardware tokens to the picture or you will need to merge users into one.

Second, naming matter. E.g. ContractRecipientEvent - given this name, I thing it's some kind of log entry on what happens with the particular Contract - Recipient relation. Also, is Recipient really good name for it? Maybe, Signee is a better one?

1. Many-to-many field. I would like to just use plain Foreign keys for my contract recipients, but I am unsure how should I assign multiple users to the same contract? Perhaps I should create a new model ContractRecipient with the user and contract as foreign keys, but that is a many-to-many field also?

I guess, many-to-many is pretty natural here. Depending on your other use cases, I'd even moved all contract signing metadata to a table on it's own, keeping association table as simple as possible. There still can be some some integer status field, which can hint at which stage the process goes, but everything else can be elsewhere, related to this table.

It may also be, that Contract - Signee can be 1-to-Many, but then you can have Signee - User a separate Many-to-1. Namely, one contract can have 2 (?) or more signees, each user can be a signee 0 to more times (with different contracts).

So ContractSignee will have contract id and user id as foreign keys, and some id as well (in case you want to assign some metadata to each signee in a separate table). It seems like ContractSignee is more than just M-to-M association table as it can have some payload (at least I understood it so from your question).

In short, ContractRecipient or better ContractSignee is good idea.

I don't like that I need to copy data from the Invitation model to the ContractRecipientEvent and only create ContractRecipientEvent after the user is registered, because I need a user entity to create a ContractRecipientEvent, which has a foreign key to the user.

Perhaps, users may have some integer status assigned to them for the life-cycle? For example, "pending_verification", "verified", ..., "removed". Here also it is good to keep entity simple and move whatnot to an auxiliary table. This will help you to have a "tomb" of the user in case you will need need to have some analytics later. I also guess, that in e-signing app audit trails are must have, and maybe not even as simple as a log in the db, but you know better. So each entity and even relation may have a log table of events associated.

As for ContractRecipientEvent - I am not sure why you need this if you will have ContractSignee.

The permission structure is difficult to manage. I need to check all the users, who are included in the contract database record, and check if they are assigned to the contract id, they are using for the signing POST request.

The ContractSignee should probably be the only authoritative source for who has been assigned as a signee to a specific contract. Having "pending" / "draft" users is probably less evil than duplicating data in different tables. Also, I guess the same user can be participating in multiple contracts, so ContractSignee is quite independent entity from user. It's ok to have user associated with ContractSignee a way to provide access to the Contract. All this can be done in one SQL query very efficiently.

There is another aspect here as well. In the future, there may be that contract should be signed in some specific order by sides. This can affect having access to the contract, so should be part of the structure. ContractSignee can also have some kind of status, from which it will be easy to decide whether the Signee already has access to the contract or not.

General advice is to always keep "backbone" of the database schema compact, moving non-key metadata to auxiliary tables.

Now regarding Invitation. I have not quite understood it's necessity from your description and other comments, so I see two cases:

1. Invitation is always per contract. Users do not have any in-system identities and always authenticate via one-time email link or some strong authentication;
2. Invitation is always per user, coming from your system, not specific contract owners. Your system provides users with means to authenticate.

In the second case workflow for the first-time user may be different and involve some sign on stage in it, either separately or in-passing to the contract-signing page.

Answer 2

1. Many-to-many field. I would like to just use plain Foreign keys for my contract recipients, but I am unsure how should I assign multiple users to the same contract? Perhaps I should create a new model ContractRecipient with the user and contract as foreign keys, but that is a many-to-many field also?

This many to many relationship is something that actually exists as such in your domain (each contract can have signatures from multiple users and each user can sign multiple documents). You can try to hide it in your model, but most likely you will either constrain your use-cases too much or you will fail in hiding the many-to-many nature of the relationship.

2. I don't like that I need to copy data from the Invitation model to the ContractRecipientEvent and only create ContractRecipientEvent after the user is registered, because I need a user entity to create a ContractRecipientEvent, which has a foreign key to the user.

You can avoid that copying by having two different states for your User model: Invited and Registered. Then you can create a User instance in the Invited state when you find a non-existing user being added to a document as a signer. This Invited User will most likely not be able to login yet or do any actions that require them to login themselves, but it should be possible for others to refer to them (e.g. add them to additional documents as signer).

Once a user follows the link they received in the invitation email, the User state can be changed to Registered and the details that are provided at registration can be filled in.

This means that the Invitation model only needs to contain details about the invitation itself (which user is being invited, when was the email sent, which link should they follow, etc.)

3. The permission structure is difficult to manage. I need to check all the users, who are included in the contract database record, and check if they are assigned to the contract id, they are using for the signing POST request.

This is also unavoidable, unless you give each contract/user combination a unique link to add their signature. Then the complexity shifts to the link generation.