2

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": "test2331@gmail.com"
        },
        {
          "message": null,
          "recipient_signing_status": "NOT_SIGNED",
          "recipient_review_status": "NOT_REQUIRED",
          "recipient_action": "SIGN",
          "role": "BASE",
          "email": "test2333@gmail.com"
        }
      ]
    },
    {
      "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": "test123@gmail.com"
        },
        {
          "message": null,
          "recipient_signing_status": "NOT_SIGNED",
          "recipient_review_status": "NOT_REQUIRED",
          "recipient_action": "SIGN",
          "role": "BASE",
          "email": "test233@gmail.com"
        }
      ]
    },
 
  ]
}
1
  • 1
    The term "many-to-many field" seems to be specific to Django. I'm a little confused about whether you are talking about a database design that is agnostic to a framework or something specific to Django.
    – JimmyJames
    Mar 24 at 18:50
2
+100

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.

2
  • Thanks, I was a little bit busy and only found time to look at the answer right now. The answer is amazing! You even covered the case, where there is some invitation order, which I will add in the future. Just one more last question perhaps. I need to pass additional data to the ContractSignee, for example: an additional comment, an access modifier ( if it is read, or sign). So, should I create an additional one to one auxiliary table, which will be linked to the ContractSignee, instead of keeping all that in the ContractSignee M2M table? Mar 29 at 13:30
  • That depends. access modifier (or mode?) is directly related to ContractSignee - so can be a column there. As for other extras - it may depend. You can start with one in the same table, but if you continue adding those (eg, comment will grow into comment thread) - it will be time to rethink.
    – Roman Susi
    Mar 29 at 14:37
1
  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.

  1. 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.)

  1. 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.

10
  • Regarding the two users' states. This is a very good idea! I thought about it myself but could not formulate it the way you did. As far as I understood, the Invitation model could be an OneToOne relationship with a null option. When the user assigns recipients, I would then create an Invitation model. After the recipient registers, I would create a User model and set that OneToOne field to a particular Invitation model. Regarding permissions. Is it alright to just check if recipient is included in contract and if it is, let him do some of the actions:signing, updating or etc? Mar 22 at 11:40
  • As I see it, when a recipient is assigned who doesn't have a corresponding User model, you create both a User and an Invitation model. After that recipient registers, you can delete the Invitation model (it has served its purpose and is no longer needed). The Invitation model would have a mandatory OneToOne relationship to User, but the other direction would be optional. Mar 22 at 13:58
  • Regarding the permissions, I can't really tell if that will work. It depends on the requirements you have about who can do which actions. Mar 22 at 13:59
  • Ok, one more question. Suppose that two contract owners decide to invite the same user, with the same email? So, by following this approach I should create a user with an invited state, that would include the only email. Create an Invitation model and assign it to the User. However, what should I do if another person invites this same user? I can't create another User model, because the User technically is created, he just did not register. I think about changing OneToOne to Foreign keys, then it will be easier to track. Mar 22 at 14:30
  • The user has been invited already, so no new invitation is needed. You might consider sending an email to all users that are added to a contract to notify them they are supposed to sign something. In your case, what would happen if a user responds to both invitations and tries to register multiple times? Mar 22 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.