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

user_9163128 0
Viewed 262

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 through 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"
        }
      ]
    },
 
  ]
}
django-rest-framework relationship database-design django-models django
1 Answers

Well the only difference between a M2M and two ForeignKeys is the through table but let's see if I understand. What if we started with the following models:

class User(models.Model):
  email = models.CharField(...)
  ...

class Contract(models.Model):
  user = models.ForeignKey('User', ..., related_name='contracts')
  ...

class Signature(models.Model):
  user = models.ForeignKey('User', ..., related_name='signatures')
  contract = models.ForeignKey('User', ..., related_name='signatures')
  is_signed = models.BooleanField(default=False)
  ...

class Event(models.Model):
  user = models.ForeignKey('User', ..., related_name='events')
  contract = models.ForeignKey('Contract', ..., related_name='events')
  signature = models.ForeignKey('Signature', ..., related_name='events') 
  message = models.CharField(...)
  ...

Now we can do things like:

# get a specific user:
user = User.objects.get(email=<email>)

# get all of the contracts they own:
users_contracts = user.contracts.all() # OR
users_contracts = Contract.objects.filter(user=user)

# get a specific contract:
contract = Contract.objects.get(id=<contract-id>)

# get all the signatures on a contract:
signatures_on_contract = contract.signatures.all() # OR
signatures_on_contract = Signature.objects.filter(contract=contract)

# get all the signatures for a user:
users_signatures = user.signatures.all()

# get all the contracts that the user signed:
users_signed_contracts = Contracts.objects.filter(
  signatures__in = users_signatures,
  signatures__is_signed = True
)

# get all the events on the contract:
events = contract.events.order_by('id')

Now our contract json can look something like:

// i.e.: contract with id 7:
{
  'id' : 7,
  'user' : {
    'id' : 2,
    'email' : 'some@email.com'
  },
  'signatures' : [
    {
      'id' : 3,
      'user' : {
        'id' : 2,
        'email' : 'some@email.com'
      },
      'is_signed' : true
    },
    {
      'user' : {
        'id' : 4,
        'email' : 'other@email.com'
      },
      'is_signed' : false
    }
  ],
  'events' : [
    {
      'id' : 6,
      'user' : {
        'id' : 2,
        'email' : 'some@email.com'
      },
      'contract' : {
        'id' : 7
      },
      'signature' : {
        'id' : 3
      },
      'message' : 'signed contract 7'
    }
  ]
}

The Event model here is perhaps overqualified and doesn't need all the ForeignKey relations it has, but this way you can flexibly create your json.

EDIT

Handling users who need to sign a contract:

# a list of emails:
emails = ['email@1.com', 'email@2.com', ...]

for email in emails:

    # get or create a user:
    user, created = User.objects.get_or_create(email=email)

    # new user logic:
    if created:
        # set temp password
        # redirect users to change password page before signing a doc (can be done elsewhere)
        ...

    # existing user logic:
    else:
        ...

    # create signatures for each user, and add them to the contract:
    signature = Signature.objects.create(user=user, contract=contract)
    ...

EDIT 2

Here is one example of limiting requests to the Signature table based on objects using DRF:

views.py

class SignatureViewSet(viewsets.ModelViewSet):

    # override this method to limit access:
    def get_queryset(self):

        # superusers can access all items:
        if self.request.user.is_superuser:
            return self.queryset

        # otherwise, users can only access their own signatures:
        else:
            return self.queryset.filter(user=self.request.user)
user_128216750
Related