Design Dropbox: System Design & Architecture

Database Schemas

- Users (PostgreSQL)
  user_id UUID PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  storage_quota BIGINT DEFAULT 2147483648, -- 2GB
  storage_used BIGINT DEFAULT 0,
  subscription_plan subscription_enum DEFAULT 'free',
  created_at TIMESTAMP DEFAULT NOW(),
  last_login TIMESTAMP,
  preferences JSONB, -- sync settings, notifications
  
  INDEX idx_user_email ON (email)
  INDEX idx_subscription ON (subscription_plan, created_at)

- Files (PostgreSQL, sharded by user_id)
  file_id UUID PRIMARY KEY,
  user_id UUID NOT NULL,
  parent_folder_id UUID, -- NULL for root
  filename VARCHAR(255) NOT NULL,
  file_size BIGINT NOT NULL,
  content_type VARCHAR(100),
  content_hash VARCHAR(64) NOT NULL, -- SHA-256 of the whole file
  chunk_count INTEGER NOT NULL,
  created_at TIMESTAMP NOT NULL,
  modified_at TIMESTAMP NOT NULL,
  deleted_at TIMESTAMP, -- soft delete (trash)

  INDEX idx_files_listing ON (user_id, parent_folder_id, filename) -- folder listings
  INDEX idx_files_sync ON (user_id, modified_at) -- delta sync

- Folders (PostgreSQL, sharded by user_id)
  folder_id UUID PRIMARY KEY,
  user_id UUID NOT NULL,
  parent_folder_id UUID, -- NULL for root
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  modified_at TIMESTAMP NOT NULL,

  INDEX idx_folders_listing ON (user_id, parent_folder_id, name)
  -- Note: sharing lives only in the Permissions table, not a shared_with[] column here

- File Chunks (PostgreSQL) -- maps a file to its ordered chunk hashes
  file_id UUID NOT NULL,
  chunk_number INTEGER NOT NULL,
  chunk_hash VARCHAR(64) NOT NULL, -- SHA-256 of this chunk
  chunk_size BIGINT NOT NULL,

  PRIMARY KEY (file_id, chunk_number)

- Chunk Store (PostgreSQL/KV) -- one row per UNIQUE chunk, enables block-level dedup
  chunk_hash VARCHAR(64) PRIMARY KEY, -- content-addressed
  storage_key VARCHAR(500) NOT NULL, -- where the bytes live in blob storage
  size BIGINT NOT NULL,
  ref_count BIGINT NOT NULL DEFAULT 0 -- how many files reference this chunk
  -- identical chunks across users/files are stored once; cleaned up when ref_count hits 0

- Change Feed (PostgreSQL, durable append-only; NOT a cache)
  cursor BIGSERIAL PRIMARY KEY, -- monotonic per user
  user_id UUID NOT NULL,
  resource_type resource_type_enum NOT NULL, -- 'file', 'folder'
  resource_id UUID NOT NULL,
  action change_action_enum NOT NULL, -- 'created', 'updated', 'deleted'
  created_at TIMESTAMP NOT NULL,

  INDEX idx_changefeed ON (user_id, cursor) -- "what changed since cursor X"

- Sync Cursor Cache (Redis) -- cache only; source of truth is the Change Feed
  Key: "sync:{user_id}:{device_id}"
  Value: { "last_cursor": 84213 }
  -- if Redis is lost, we re-read the device's cursor from durable storage; no data loss

- Permissions (PostgreSQL)
  permission_id UUID PRIMARY KEY,
  resource_id UUID NOT NULL, -- file_id or folder_id
  resource_type resource_type_enum NOT NULL, -- 'file', 'folder'
  user_id UUID NOT NULL,
  permission_type permission_enum NOT NULL, -- 'read', 'write', 'admin'
  granted_by UUID NOT NULL,
  granted_at TIMESTAMP DEFAULT NOW(),
  expires_at TIMESTAMP,

  INDEX idx_perms_resource ON (resource_id, user_id)
  INDEX idx_perms_user ON (user_id, granted_at)