Question on implementing offline first file upload with Brick

[jhb-dev]

For the app I am building with Brick and Supabase, I have to implement file uploads in the upcoming weeks.

I already created a Brick model named FileMetadata which has the following properties:

• id (String)
• fileName (String)
• downloadURL (String, nullable)
• localPath (String, nullable)

The files should be uploaded to Supabase Storage.

When the client is online, this is no problem and I do it as follows:

1. upload the file to the Supabase storage via the Supabase SDK
2. get the download URL to the file
3. Construct the FileMetadata object using the download URL
4. upsert the FileMetadata with Brick

But when the client is offline, it becomes more tricky. What I have done so far is saving the file to the local app documents directory and construct the FileMetadata object, this time with the path to the local file instead of the download URL. Then I upsert the FileMetadata, which gets added to the offline request queue.

What is missing is uploading the File and adding its download URL to the FileMetadata object when the client comes back online and the request queue is processed. Before the FileMetadata gets sent to the remote database, I would have to upload the locally stored file and get its download URL to add it to the FileMetadata object.

In the issues and the docs, I could not find any hints or solutions on implementing offline first file upload with Brick. What I thought of is something like an beforeRemoteUpsert hook, which would get triggered, before the FileMetadata object gets sent to the remote database, in which I could upload the local file first, and set the download URL for the FileMetadata object.

I guess the easiest way would be to add the bytes of the file to the FileMetadata, but I would like to avoid storing files inside Databases.

@tshedor Do you have experience with using brick for file uploads or any ideas how to implement this? Thanks a lot as always!

[tshedor]

@devj3ns So I've never had to do file upload with Brick. This is new territory. But, if I did, I would take an approach similar to what you've done by using the application documents directory.

Does your FileMetadata object have a table counterpart in your Supabase project or are you using the storage schema? When the client comes back online it could reconcile the difference between all files created locally in the last 7 days. The FileMetadata that exists locally but not in the remote would then be uploaded.

There's trigger from Brick that says "I'm back online, reprocess this." In the past, I've had a long running timer that checks for the queue length exceeding 0 for over 10 or so seconds. If it's over that 10 seconds, it would run a job like the reconciliation and reupload.

The other option you could do that feels slightly more hacky is overriding your repository's upsert method:

@override
Future<TModel extends BrickOfflineFirstWithRestModel> upsert<TModel>(instance, {query, repository}) async {
  if (TModel == FileMetadata) {
    // upload file logic
  }
  return super.upsert<TModel>(instance, query: query, repository: repository);
}

[SubutaDan]

FWIW, my Brick app uploads files using:

a persistent queue I hacked together with Sembast for pending upload records;

local storage to hold the file until it is uploaded;

a background task that checks the queue, uploads the next file and removes the queued record.

Nothing special, but happy to share any of the pieces if that is helpful.

[jhb-dev]

Thanks for your input on this!

@tshedor, yes, there is a database table in the Supabase Postgres public schema for the FileMetadata objects. In the Flutter app, this model uses the ConnectOfflineFirstWithRest annotation. So the offline capability and sync of the FileMetdata is already solved by Brick. The only missing piece is how to link the upload task to the FileMetadata object, which is stored in the offline queue when the client picks and adds files to the app while being offline.

Regarding the trigger: So you would check if the client is online (again) and upload all local files that have not been uploaded yet? With this approach, how would you handle the connection to the FileMetadata objects?

Regarding the upsert hack: To my understanding, the upsert method is only used when the client is online. When the client is offline, the upsert is transformed into an API Request containing the object to store in the remote database and saved inside the SQLite offline queue table. When going with this approach, I guess I had to also override the send method of the RestOfflineQueueClient to add the upload task before sending the FileMetada to the API while the offline request queue is processing. Am I getting this right?

@SubutaDan, thanks for sharing your solution. So you basically created a separate queue similar to Bricks offline request queue but for the file upload tasks. How do you handle the metadata of the files, do you have a database model that stores these? (With metadata I mean data like the user to which the file belongs to or a description).

[SubutaDan]

Yes, I guess it is similar to the offline-first. queue, but mine is bare bones. The files that have to be uploaded are photos, each of which belongs to one of the items in a collection that is managed using offline first with REST. The name of the photo file is already written on the Brick record, along with the user id, tags, etc. The item that goes onto the queue is a small record that has the local file path and an identifier for the user to whom the file belongs.

On 26 August 2024 20:43:21 GMT+09:00, Jens Becker @.***> wrote:

Thanks for your input on this!

@tshedor, yes, there is a database table in the Supabase Postgres public schema for the FileMetadata objects. In the Flutter app, this model uses the ConnectOfflineFirstWithRest annotation. So the offline capability and sync of the FileMetdata is already solved by Brick. The only missing piece is how to link the upload task to the FileMetadata object, which is stored in the offline queue when the client picks and adds files to the app while being offline.

Regarding the trigger: So you would check if the client is online (again) and upload all local files that have not been uploaded yet? With this approach, how would you handle the connection to the FileMetadata objects?

Regarding the upsert hack: To my understanding, the upsert method is only used when the client is online. When the client is offline, the upsert is transformed into an API Request containing the object to store in the remote database and saved inside the SQLite offline queue table. When going with this approach, I guess I had to also override the send method of the RestOfflineQueueClient to add the upload task before sending the FileMetada to the API while the offline request queue is processing. Am I getting this right?

@SubutaDan, thanks for sharing your solution. So you basically created a separate queue similar to Bricks offline request queue but for the file upload tasks. How do you handle the metadata of the files, do you have a database model that stores these? (With metadata I mean data like the user to which the file belongs to or a description).

-- Reply to this email directly or view it on GitHub: https://github.com/GetDutchie/brick/issues/409#issuecomment-2310004522 You are receiving this because you were mentioned.

Message ID: @.***> -- Japan: +81 70 911 52405 US: +1 704 380 9253 UK: +44 7875 599 430

[tshedor]

Regarding the trigger: So you would check if the client is online (again) and upload all local files that have not been uploaded yet?

Yes

With this approach, how would you handle the connection to the FileMetadata objects?

Is there a way that you can populate FileMetadata on the server instead of on the client? That way, the client would simply pull from the server instead of informing the server of a URL it's already created. I'm thinking you'd accomplish this with a function or with a SQL trigger.

If your FileMetadata was reliant on the server to create everything once you uploaded the file (say to a function endpoint), you could then reinvoke .get<FileMetadata> after a 200 response from the endpoint. Or you could short poll it, or you could use channels (though mind the cost, it escalates quickly).

What do you think of this route?

Regarding the upsert hack: To my understanding, the upsert method is only used when the client is online. When the client is offline, the upsert is transformed into an API Request containing the object to store in the remote database and saved inside the SQLite offline queue table. When going with this approach, I guess I had to also override the send method of the RestOfflineQueueClient to add the upload task before sending the FileMetada to the API while the offline request queue is processing. Am I getting this right?

You're right, I apologize for misleading on my suggestion. You would have to compose another http.Client (which you would do by passing that into RestProvider, the gzip mixin takes this approach). This isn't impossible, but it breaks encapsulation since you'd be invoking Repository within a client that is a dependency of Repository. At runtime it all works out, but it's not great architecture.

Unless your file uploads don't require Repository invocations, in which case you're doing something like what @SubutaDan is suggesting by having an external queue that's processing this stuff outside of Brick.

[jhb-dev]

Good idea @tshedor, yes, I could create an endpoint which handles the file upload to Supabase Storage and the creation of the FileMetadata object in the database. This way, it would be easier to create a separate queuing system for the file uploads, as this separate queue would not depend on the FileMetadata objects, which would otherwise be stored inside Bricks offline queue.

I think I will implement a small prototype for both ways - the custom client and the separate queue - and then evaluate which way works better.

I will post an update if I have something working and sharable which others might profit from. Maybe this will help to add first party support for file uploads to brick in the future.

[tshedor]

@devj3ns do you still plan to return to this?

[jhb-dev]

Hi @tshedor, yes, I’ve implemented a solution that’s been running successfully in production for several weeks and fits my use case well.

I’ll put together a brief documentation of the solution tomorrow, and then I'll close this issue.

[tshedor]

@devj3ns Awesome. Thanks very much.

[jhb-dev]

For anyone coming across this issue in the future, here's a brief documentation of the solution I implemented.

Considered Approaches

Like I mentioned earlier, I considered two approaches for implementing file uploads to Supabase storage in a Brick-based app:

1. Brick's Queue for Uploads: Utilize Brick's existing queue to manage file uploads when the associated object is sent.
2. Separate Upload Queue: Create a dedicated queue for file uploads, similar to Brick's offline request queue.

I chose the second option for the following reasons:

• A separate queue can run independently, ensuring data is uploaded first before handling files.
• It is a cleaner, more predictable approach that offers better extensibility.

Implementation Overview

Architecture

FileMetadata Brick Model

• A Brick model stored in SQLite and synced with the Postgres database.
• Created when a file is picked by the user.
• Stores:
  • local_path: Local path of the file (nullable).
  • supabase_storage_path: Path in Supabase storage (nullable, set by a Postgres function after upload).
  • download_url: URL for accessing the file (nullable, set after upload).

StorageRequest Class

• class to track upload/delete requests for Supabase storage.
• stores the following information:
  • file_metadata_id (uuid, reference to the FileMetadata object)
  • type (enum, either "upload" or "delete")
  • storage_bucket (supabase storage bucket)
  • storage_path (path inside the storage bucket)
  • local_path (nullable, only set if type is "upload")

QueuedStorageRequest Class

• Extends StorageRequest to manage queued requests
• Stored only in a dedicated SQLite database
• stores the following information:
  • attempts (int, how often the request has been attempted)
  • locked (boolean, if the request is currently being processed)
  • created_at (datetime, timestamp of when the request was created)
  • updated_at (datetime, timestamp of when the request was last attempted to be processed)

StorageOfflineQueueDatabase Class

• Acts as an adapter for interacting with the SQLite table (queue).
• similar to bricks RestRequestSqliteCacheManager`
• requests are queued and sorted by their created_at timestamp (important to ensure that destructive operations are executed after insertions)

StorageOfflineQueue Class

• Manages the processing of the queued storage requests.
• Uses a timer to process requests every X seconds.
• Integrates with SupabaseStorageAdapter and LocalStorageAdapter to interact with Supabase and the local file system.
• The implementation is a combination of bricks OfflineRequestQueue and RestOfflineQueueClient
• is started automatically, when bricks offline request queue has finished processing (is empty), to ensure that all FileMetadata objects are uploaded

SupabaseStorageAdapter Class

• provides methods to upload and delete files to/from supabase storage
• important: uses the upsert option in order to avoid problems when uploading the same file multiple times
• important: converts the file name to a s3 compatible file name to prevent upload issues

LocalStorageAdapter Class

• provides methods to write and delete files to/from the local file system
• uses the ApplicationDocumentsDirectory to store the files

StorageService

• Combines the above components, similar to a Brick repository.
• Provides methods to upload, delete, and manage the queue.

Order of Events

1. User picks a file (e.g. takes a photo with the camera)
2. A safe file name is created and the file is stored in the local file system
3. A FileMetadata object is created and upserted using Brick.
4. Assuming the client is offline, Brick queues the FileMetadata object.
5. StorageRequest is created and added to the StorageOfflineQueue.
6. When back online, Brick upserts FileMetadata objects from the offline queue.
7. Once Brick's queue is empty, the StorageOfflineQueue begins processing file uploads.
8. The StorageOfflineQueue uploads the file to supabase storage and stores the ID to the FileMetadata object in the metadata field
9. A Postgres function is triggered after successful uploads to mark the FileMetadata as uploaded and add the download_url.

Conclusion

This is the implementation I came up with. I am sure there are improvements and optimizations that can be made to this setup and process. This could potentially be a separate package in the brick ecosystem or as an extension to it, but unfortunately, I do not have the time to work on this.

I am happy to discuss this implementation and if someone is interested in the code, I can share parts of it.