Structure of database for signatures (was transaction question)

[tttp]

Hi, Looking at the log, it seems that they are two transactions, one to add the contact the other to add signature+contact_signature)

I'm still not convinced of the benefit of having two tables for signature and contact, but that's the topic for another issue ;)

[marcinkoziej]

Are you sure it's 2 transactions? should be one transaction with two statements - I think when the second step fails, the first statement is rolledback

Why is this useful:

1. I can have multiple contact records per signature - if i need to encrypt the contact for 2 partners (each a different key)
2. I can have share record that points to same contacts as signatures signed beforehand

[tttp]

I'm not sure it's two transactions, I probably misread the logs then

1) encryption: I think we shouldn't over-complicate things, if the data should go to more than 1 partner, we don't encrypt (plan B: if/when we need that post MVP, we can store the encrypted data in the consent_org record) 2) the share could point to the "parent" petition signature as the source. Beside, what would be the contact "source" if you have two contacts (encrypted with different keys)?

[marcinkoziej]

Longer answer: To me structure is not always over-engineering. In this case I had few advantages in mind:

Where I can, I want non-null data NULL is "The Billion Dollar Mistake" and I don't want them. Any time I have to deal with a possible null value, i have to check for it. More NULLs->more bugs. Because we started off with a very vague idea of what is in the contact personal data (and now it seems even my assumption that contact.name is mandatory, we are supposed to have first_name and last_name....). So one thing I can do is separate this mess into a box - separate record, and have signature function as record with non-null, well defined fields.

Openess to extension This is MVP, okay, but why not be open to new features? Say we will add mail to target, or any other action, and then what? Copy over all the flat structure of contact data? This is like speakout works and is horrible. On the other end, what if we want to implement open letters, signed by Organisations? Then we can have a new organization record and connect it to signature.

Separation of concerns, organisation and why not? One can always split table into two, in 1-1 relation. Why not? If it serves for better concern divisions, or code organisation purposes, that's okay! We have the API layer to make this invisible to the user.

2. As for the share, that was just an example, we'll see what we need.

[tttp]

Very valid points.

On my side, the other side of the coin: proca doesn't care about the structure of the data, beside a very few that needs special handling (eg email and consent?). From a GDPR point of view, the easiest is to not store any personal data, so we do not have to ghost and all that jazz.

ie in essence what it does is take the data, gather the consent and push that payload to the next step. The extreme is storing that payload already encrypted from the client side and "simply" be a hub that push the data to their final destination (CRM).

as for new feature, why not simply store them as well as big json blob? you can, obviously, store an email to target or generate a postcard or whatever.

Say otherwise, what about seeing that in the lense of a micro service architecture? proca collect the actions from the supporter, and send them to another service based on the type (eg to a twillo fax machine, to a CRM for storing, to a mailer....

Not super sure of either, but to loop back to your point about the name, what's the point of having 3 columns:

• name
• first name
• last name ... and possibly many more (middle name...) and schema migration and all of them being mostly empty anyway?

proca doesn't care about which fields are stored, it is just there to push them to the CRM, no matter their format. That solves the null data too, you take what you receive and push it to the next service

I'm not super convinced either way. I see pros and cons both way, but I think it's easier to split into two tables later when "really" needed, because the other way around isn't going to happen, no matter how complex the schema (cf civicrm ;)

[tttp]

Sleeping on it, I realise that what I'm suggesting is a mostly noSQL "structure".

But still unsure about the structure, I still think we need a "payload" unstructured json where we can easily shove whatever custom field we want into.

and still think we need well structured things, like the consents.