Photos, PDF and attachments in SQL Pipeline

General Q&A
1

When reviewing Photos/PDF in my SQL Pipelines I noticed that the fields are null in SQL despite having values in the JourneyApps DB and when I pull the record via Backend API I can get URLs to the various files.

Why are these files not included in SQL and can I have the URL for the file put into the appropriate fields in the pipeline?

2

Hey @forumfred

In short, photos, PDFs, signatures and other attachments (all called ‘Attachments’ in our world) are not stored in the same DB as the rest of the “raw data” and as such are not included in the pipeline by default (at least not at time of writing - but there is an open feature request to change this).

That said, we basically have 2 ways for you to access them “from SQL”.

Option 1. Using the Backend API
Included in SQL by default will be the ID of the attachment, this will be stored a field called ${attachment_field}_id, e.g. if you have a field called photo in your Data Model then there will be a photo_id field in SQL that will contain the Attachment ID of the attachment in question, in this case a photo that you stored in that photo field. Please note the Backend API is authenticated

Using the backend API you have two options for retrieving the attachment Data.

  1. You query the entire object (docs) to retrieve URLs to all the attachments for the object, .e.g.
{
	"id": "11111111-1111-1111-1111-3deab88d8b33",
	"type": "supporting_information",
	"updated_at": "2021-09-30T18:19:58Z",
	"photo_id": "11111111-1111-1111-1111-8fe99344c9c8",
	"photo": {
		"state": "uploaded",
		"thumbnail": "https://run-us.journeyapps.com/media/111111111111111111111/test.png?sha=11111111111",
		"fullscreen": "https://run-us.journeyapps.com/media/111111111111111111111/test.png?sha=11111111111",
		"original": "https://run-us.journeyapps.com/media/11111111111111111111111/test.png?sha=11111111111"
	},
....
  1. Querying the Attachment API to get the RAW attachment data back.
    2.1 HTTP GET request to: https://{backend_url}/api/v4/{account_id}/attachments/{attachment_id}
    The attachment_id is the value that is currently in SQL in the attachment_field_id field, e.g. photo_id
    So in the example above, the Endpoint would be: https://run-us.journeyapps.com/api/v4/5e34a0cf07601117d334598c/attachments/11111111-1111-1111-1111-8fe99344c9c8
    2.2 The format of the data that is returned will depend on the mime type of the attachment. In this case it will either be image/png or image/jpeg because it was stored as a type="photo" in the DB. Pictures captured using the camera will be stored as image/jpeg, but users can upload PNG files manually

Option 2. Including the URL manually in the Raw Data
This method basically involves writing the URL of the attachment directly to a dedicated field in the DB which then gets automatically synced to SQL

  1. Update the data model in your app to include a new TEXT field for every attachment you want to ‘sync’ to SQL, calling it something like attachment_field_url , e.g. photo_url
  2. Once the attachment is uploaded to the Cloud, get the URL of the attachment (in CloudCode) and write that to the corresponding text field in the DB
  3. The contents of this text field will then automatically be written to SQL
  4. NB. You will need to update the URL every time the attachment changes (i.e. every time you capture a new photo, regenerate a PDF, etc.)
2 Likes