File System on the Web
This folder documents all the research I've conducted to converge on a solution for designing filesystem APIs on dbdocs/dbdiagram. This is to support the multifile DBML feature (which I have shipped in the compiler quite ago) on the cloud.
The compiler intentionally just needs a file system interface to resolve imports, etc. So the task of designing filesystem is quite isolated with the compiler's internals.
Constraints
Each user has multiple workspaces, each of which has a logical file system, in which files are stored.
Each file can have permission settings.
Almost every files are plain texts, not binary.
Files can be very large, up to 20k LOC (around 1-2 MBs).
A workspace can have tens or hundreds of files.
Almost likely, we won't use specialized cloud-based storage solutions like S3, etc. We usually don't want the hussle of contacting devops and allocate more unique resources. Adding more servers is OK though, but need some justifications.
Shall account for concurrent reads/writes and data consistency.
Use Cases
If file A imports file B and a PRO user is viewing file A, a concurrent change to file B should be reflected to that user.
Articles & References
Storage (File/Metadata/API)
How real services store files, manage metadata, and expose file CRUD APIs.
GitHub
[file, metadata]: How GitHub stores and serves Git repos: full architecture walkthrough[file]: GitHub DGit/Spokes: distributed Git storage[api]: GitHub Contents API: file CRUD over REST, git-backed. Closest to our use case.
Microsoft
[api]: OneDrive/MS Graph API: hybrid path + ID addressing[api]: VS Code FileSystemProvider: cleanest modern FS interface (stat, readFile, writeFile, readDirectory, rename, delete, watch)
General
[file]: PostgreSQL: storing files in DB: acceptable for files <100KB, simplified backups[file]: Database vs blob storage: decision guide[api]: Cloud storage HTTP APIs compared: Dropbox vs Google Drive vs S3 vs Box[api, collaboration]: Optimistic locking with ETags: the right default for web file APIs[file, metadata]: Google Drive/Dropbox system design: metadata DB + blob storage split, chunked uploads, sync via message queues
DB Schema for Filesystems
[metadata]: Supabase Storage schema: Open-source S3-compatible file storage service.PostgreSQL for metadata (
buckets+objectstables), S3 for content.Flat namespace with
/-delimited names,path_tokensarray, JSONB metadata, RLS.
[metadata]: Notion data model: PostgreSQL, block-based (everything is a block with id, type, properties, content, parent).[file]: MongoDB GridFS: two collections (fs.files+fs.chunks), chunked storage for files > 16 MB, streaming/seeking
Realtime & Collaboration
How to handle concurrent multi-file edits and keep a project-wide view (like the dbdiagram renderer) in sync for all users.
Figma
NOTE
Closest model to our use case: concurrent edits to a shared project trigger a full re-render of a visual canvas for all viewers. Similar to how editing file B should re-render the dbdiagram that imports B.
[collaboration]: multiplayer technology[collaboration]: multiplayer infrastructure deep dive[collaboration, file]: S3 checkpointing + DynamoDB WAL for file persistence[collaboration]: database scaling
Zed
NOTE
Open source editor with built-in collaboration via CRDTs. Runs one LSP instance per project server-side, feeds CRDT-resolved content to LSP. This solves "file A imports file B, both edited concurrently, LSP needs consistent view".
[collaboration]: source[collaboration]: CRDT implementation
Replit Web-based, OT for real-time collab. Server-side LSP handles cross-file analysis, triggered by file change events from any collaborator.
[collaboration]: multiplayer architecture
Out-of-Scope
Dropbox: Magic Pocket
Out of scope: uses immutable blob store, not applicable to our text file model.
Supabase Storage (tool-specific)
Out of scope: too tool-specific, may revisit after research. Supabase Storage is S3-compatible, deployable on a dedicated server without cloud dependency.