Blob de-duplication breaks for identical content with different mime types

[alxndrsn]

Demonstrated at getodk/central-backend#1073

AKA: blob.contentType

[matthew-white]

We've started filing issues that are potentially user-facing in the central repo, so I just transferred the issue. We're mostly using central-backend and central-frontend just for refactor/testing/development issues.

[matthew-white]

Assuming that we don't want to do #1098, here are a few ideas I can think of:

1. Ignore a conflict on contentType. If a blob already exists, but the user is now specifying a different contentType for it, just ignore the new contentType.
2. Ignore the new contentType unless it's more specific. If the user didn't specify a contentType in the first place, then the existing contentType will be application/octet-stream. We could make it so that the new contentType is ignored unless the existing contentType is application/octet-stream, in which case the new contentType will be preferred and will replace the existing one. The logic is: use the first real information we receive about the contentType (i.e., anything but application/octet-stream), then don't allow any subsequent changes to the contentType.
3. Separate out blobs + content types. Even if two form attachments reference the same file/blob, we could allow them to have different content types. Maybe one is application/octet-stream, and the other is text/csv. The contentType will just match whatever the user told us for each form attachment: we won't try to force form attachments from unrelated forms to be consistent on contentType. This change would involve moving the contentType column from the blobs table to form_attachments and submission_attachments.
   • I'm imagining that this change would allow the contentType to be updated whenever the attachment is replaced. For form attachments, the contentType could be updated up until the form draft is published, after which it's not possible to replace a form attachment without creating a new form version.
4. Prefer the new contentType over the existing contentType. If a blob already exists, and the user is now specifying a different contentType, go ahead and update the contentType of the blob with the new one. This allows the user to make a correction if the original contentType was wrong. See the comment here.

[matthew-white]

One advantage of ideas (3) and (4) over (1) and (2) is that they allow the user to fix mistakes. From getodk/pyodk#115 (comment):

Conceivably the the Content-Type could be set to anything and need to change to anything else. For example if I'm working on a script/app and accidentally set all my files to text/csv but only the first one was supposed to be that and every other file should've been image/jpeg.

In this example, under ideas (1) and (2), the user would have no way to change the image blob from text/csv to image/jpeg.

[matthew-white]

Just to throw out my two cents, I currently lean toward idea (3). It feels slightly wrong to me that two unrelated forms could affect one another when it comes to form attachments. For example, two unrelated forms might use the same .csv file of administrative data (some list of census areas). Right now, the first form to upload the .csv file would get to set its Content-Type, and the second form would have to match it. On the flip side, it feels funny to me to imagine that the second form could change the Content-Type of a file uploaded for the first form, which idea (4) would allow. If we're going to allow Content-Type to be user-provided, I feel like different form attachments should be allowed to have different Content-Types. My thinking is that each form attachment should have its own Content-Type that can be updated up until the form draft is published.

[matthew-white]

I actually suspect that this issue is going to come up more often now that we've fixed getodk/central-backend#1351. We've changed the default contentType of .csv and .geojson files, which means that if the user uploads the same file twice without specifying the Content-Type header either time, that can lead to a conflict:

1. Before upgrading to v2025.1, upload a .geojson form attachment in the browser. If the browser doesn't recognize .geojson files, the resulting form attachment will have a null contentType.
2. Upgrade to v2025.1. This will change the null contentType to application/octet-stream: blobs: prevent NULL contentType in db central-backend#1355
3. Next, upload the same .geojson form attachment in the browser (perhaps for a different form). If the browser doesn't recognize .geojson files, the contentType will now default to application/geo+json in Backend. But that conflicts with the existing contentType of application/octet-stream.