Design a Google Drive-Like Backend: Schema, Sharing Permissions and Folder Logic

Design the backend of a file storage service similar to Google Drive, where users upload files, organise them in nested folders and share them with other users. This round is explicitly not a large-scale system design: the focus is the business logic and the database schema, not distributed infrastructure. Agree on the exact feature set with the interviewer before designing. ### Clarifying Questions - Which features are in scope: upload and download, folders, rename and move, sharing, trash and restore, version history, storage quotas? - Can a file appear in several folders, or does every item have exactly one parent folder? - Which roles can a share grant (for example viewer and editor), and does sharing a folder grant access to everything inside it? - Should the schema target a relational database or a document database? - Must names be unique within a folder, and what happens when an upload or a move creates a conflict? ### Part 1 — Data model and schema Define the entities and the schema for users, files, folders, stored file contents and shares, with keys, constraints and indexes. ```hint Item versus bytes Consider separating what the user sees, a named item in a folder, from the stored bytes, and what that separation makes possible. ``` ```hint Storing the tree Think about how the folder hierarchy is stored so that listing a folder and checking whether one folder lies inside another are both cheap enough. ``` #### What This Part Should Cover - Tables or collections with keys, required fields, uniqueness constraints and indexes - How the folder hierarchy is represented, and what each representation makes cheap or expensive - Where file bytes live and which metadata the database keeps ### Part 2 — Sharing and permission checks Design how items are shared and how the backend decides whether a user may view, edit, share or delete an item. ```hint Inherited access Ask how a share on a folder reaches an item several levels below it, and what happens to that access when the item is moved elsewhere. ``` #### What This Part Should Cover - The share model: who grants which role to whom on which item - The permission check, including inheritance through ancestor folders, and its cost - Rules for the owner, for revoking access and for moving shared items ### Part 3 — Core operations and their edge cases Describe the business logic of upload, rename, move and delete: the checks each operation performs and how each stays consistent under concurrent requests. ```hint Moving a folder Think about which destinations are invalid when a folder is moved, and what two simultaneous moves could do to the tree. ``` #### What This Part Should Cover - Validation and name-conflict handling for upload, rename and move - Preventing cycles when folders are moved, and what changes for the moved folder's contents - Delete semantics (trash, restore, permanent deletion) and cleanup of stored bytes - Transactions and constraints that keep the data consistent under concurrent requests ### What a Strong Answer Covers - A feature set agreed up front, and a schema that enforces its rules through constraints - A clear representation of the hierarchy with its trade-offs - A correct, efficient permission model that handles inheritance - Explicit business rules and edge cases for each operation - An API that maps onto the operations, with attention to concurrency and data integrity ### Follow-up Questions - How would you add version history so that a user can restore an earlier version of a file? - How would you enforce a per-user storage quota when several uploads finish at the same time? - A folder holds a very large number of items. How do you list it with sorting and pagination? - How would you build a "Shared with me" view efficiently?