Files table and management system

[davenquinn]

This system should be based around a files or objects table and should allow:

• Linking to files stored in S3 buckets (either under our control or managed externally)
• Tracking of basic bucket/file metadata (e.g., hashes, access dates, content types) so we don't have to grab it from AWS each time
• Support eventual development of a basic suite of tools for file hygiene: checking/removing/verifying access etc.

This will be critical for all manner of ingestion and file management tasks.

Prior art:

• This storage system schema I implemented based on Supabase's design.
• Supabase storage system.

While Supabase's API isn't exactly what we want, I think (it assumes storage in a single S3 bucket and is generally quite tailored to their use case) maintaining alignment and potentially compatibility would be useful.

[davenquinn]

Starting to work on this in #13 !

[davenquinn]

@brianaydemir @CannonLock , it sounds like this is being worked on — is there a stable pull request that can be separated from the rest of the additions for your user interface? Or is the schema somewhere else?

[davenquinn]

Update on this – we have a working prototype, but it could be nice to use Supabase's implementation directly. It's open-source and we could run it ourselves: https://github.com/supabase/storage

Although Supabase's implementation is tied to a single S3 bucket under the hood, we'd gain several features:

• A well-established client design
• Application-managed "buckets" (well, pseudo-buckets)
• S3 compatibility for "buckets" (including with authenticated access)
• Use RLS policies to establish object ownership
• Great documentation: https://supabase.com/docs/guides/storage/cdn/fundamentals

This would allow us to manage different collections of objects (e.g., website assets, section images, map ingestion files, whatever else) directly from Postgres, rather than having to juggle different S3 credentials for each data type.

[davenquinn]

I guess one downside is that Supabase Storage would not manage links to external files, but that could be handled separately, presumably.

[amyfromandi]

Creating the file management api v3 here: #248

[amyfromandi]

S3 file management update

#248 has been merged into main. There are still a few outstanding enhancements needed for the s3 file management system.

TODO's

• add bucket_name parameter for user to specify when uploading, patching, listing, or deleting files.
• add a track endpoint to attach an existing S3 object into storage.objects table.
• add a forget endpoint to detach an object from storage.objects table, without deleting it from S3.
• add a url endpoint to return a S3 url for a given slug parameter.