Comprehensive database schema for a Figma-like collaborative design platform with organization and team management, design files, page and frame hierarchy, component libraries, version history, comment threads with reactions, file sharing and permissions, and plugin ecosystem.
The containment runs five levels deep: organization, team, project, file, page, and then frames inside the page. Each level exists because something attaches to it — billing to the organization, membership to teams, sharing to files, layout to frames.
comments carry x and y. A comment is pinned to a point on the canvas, not to an element, which is why a comment survives deleting the thing it was about and why it drifts when the design moves underneath it. That is a deliberate choice and its consequence is visible in every design tool that makes it.
frames.parent_id nests frames inside frames with absolute coordinates on each. A group is a frame whose children point at it, and moving the parent does not rewrite the children — the renderer composes positions, the database does not.
comments.x and comments.y anchor a thread to a position rather than to a frame id. Anchoring to the element would keep comments attached when a design moves, and would delete the conversation when the element goes — which is worse, because the discussion usually outlives the rectangle it was about. Coordinates mean a comment can end up pointing at empty canvas, and every tool that makes this choice lives with that.
org_members and team_members both exist because they answer different questions: the organization decides who is billed and who may create teams, the team decides who sees which projects. Collapsing them would mean either everyone in the org sees everything, or every team membership has to be granted individually to people who already belong.
file_versions holds labelled snapshots created by a person, not an entry per edit. Design files change continuously and an entry per change is noise with a storage bill. The named version is the one someone wants to come back to, which is the only kind worth keeping forever.
Each frame stores its own x and y rather than an offset from its parent. Moving a group then updates one row instead of every descendant, and a frame's position can be read without walking up the tree. The cost is that the two numbers mean nothing until you know which page they are on.