feat(api): file upload status

[gphorvath]

User Story

As the LeapfrogAI UI
I want to get a status update for the uploading of my files
So that I can display the status in realtime to my users

Acceptance Criteria

Given I subscribe to the 'fileStatus' todos table
When a file is being processed, has been successfully uploaded, or failed to upload
Then I can get that status via Supabase Realtime on the frontend

Additional context

The API does not need to worry about pub/sub (realtime) functionality, it only needs to create a 'fileStatus' table and keep it up to date with file ids and status. When a file is sent to the files endpoint, update the status to 'processing'. When complete, change to 'complete', if there is an error, update it to 'error'.

ex.:

{
   "id": "file_123",
   "status": "processing"
}
CREATE TABLE file_status (
    id VARCHAR PRIMARY KEY,
    status VARCHAR CHECK (status IN ('processing', 'complete', 'error'))
);

Row Level Security (RLS) should also be setup on the table so that users can only reference their own file uploads. This may required storing the user_id on the row as well.

We should not modify the file object itself, as this would deviate from the OpenAI spec (at least until they release batch processing for files). If/when that is released, this implementation may need to change.

If we instead elect to modify the fileObject in anticipation of how OpenAI will implement batch processing (by seeing how they do it currently for other endpoints), then this implementation will not apply. The frontend will still need to be able to subscribe to a table, but it will have to be the 'file_objects' table.

FYI, The frontend will subscribe to the appropriate table and handle updates in realtime with something like this (issue #671):
ex.

supabase
  .channel('fileStatus')
  .on('postgres_changes', { event: '*', schema: 'public', table: 'todos',  filter: 'id=in.(1, 2, 3)',}, handleUpdate)
  .subscribe()

[jalling97]

"We will not be putting status on files" - decided in backlog grooming 09/05/24