Why position columns instead of a linked list?

Because reading a list in order happens constantly and a linked list makes that a walk. A position column sorts in one scan; the price is that dropping between two items eventually needs a renumber, which is cheap when done per board and lazily.

Why are labels per board?

A label is a team's vocabulary for one project. Global labels mean one team renaming 'Blocked' changes it on someone else's board, and colours would have to mean the same thing everywhere.

Do I need both workspace_members and board_members?

If every board is open to the whole workspace, no. The moment one board needs to be private, yes — and retrofitting the second table means auditing every existing board's visibility.

Why is card_activities a blob rather than typed columns?

Each action_type carries different detail — a move has two lists, a comment has text, a due date has a date. Columns for all of them would be mostly null; the blob keeps the table narrow and the shape a matter for the application.

Where does the subscription sit?

On the workspace, not the user. The plan governs what the workspace's boards can do, so a person on two workspaces gets different features in each, which is what their employers paid for separately.

Browse Templates/Trello Clone

Trello Clone

Complete database schema for a Trello-like kanban project management platform featuring workspaces, boards with member access control, ordered lists, draggable cards with rich metadata, labels, checklists with progress tracking, file attachments, card comments, activity feeds, and subscription management.

PostgreSQL16 tablesSaaS

kanbanproject-managementtaskstrello

About this schema

Almost every table here carries a position column — lists, cards, labels on a card, checklists, checklist items. That is what a board is: ordered things inside ordered things, where the ordering is the user's and has to survive a drag that moves one item between two others.

Access is granted twice. workspace_members says who belongs to the organisation; board_members says who can open a particular board. A private board inside a workspace is only possible because the second table exists.

card_activities is the board's memory. Every action writes a row with an action_type and a data blob, which is how a card can show what happened to it after the fact — the other tables hold only the current state.

Key relationships

boards → lists → cards: The three levels of a board, each ordered by position. Moving a card between lists changes list_id and position in the same write.
workspace_members / board_members: Two grants. Belonging to the workspace does not open every board, which is what makes a private board possible.
cards ↔ labels via card_labels: Labels are defined per board, so the same colour can mean 'blocked' on one board and 'design' on another without either renaming the other's.
checklists → checklist_items: A card has several checklists and each has its own items, both ordered. The item carries its own due_date, so a sub-task can be dated without being a card.
cards → card_activities: Append-only. It is the only place that answers who moved this card and when, because the card itself only knows where it is now.

Design decisions

Order is a position column, not a linked list

A linked list would make a move two updates and never need renumbering, but it makes reading a list in order a walk. A position column reads in one sorted scan, which is what a board does constantly. The cost is the move: dropping between two items needs a value between theirs, so position should be a float or a sparse integer, and either way it eventually needs a renumber. Doing that renumber per board, lazily, is cheaper than walking a list on every render.

Labels belong to a board

labels.board_id scopes them, so two boards can both have a red label meaning different things. Global labels would be tidier and wrong: a label is a team's vocabulary for one project, and sharing them across boards means one team's rename changes another team's board.

Two levels of membership

A workspace grant alone would make every board visible to everyone in the organisation, and board grants alone would mean inviting people to the company one board at a time. Keeping both is what lets a workspace have open boards and a private one in it, at the cost of two checks on every board open.

Activity is a log, not derived

card_activities stores what happened rather than reconstructing it from the current state, because the current state cannot say where a card used to be. The data blob is deliberately opaque — each action_type shapes it differently, and giving each a column would mean a table where most columns are null.

Tables in this template

usersUsers

9 cols

workspacesWorkspaces

7 cols

workspace_membersWorkspace Members

5 cols

boardsBoards

9 cols

board_membersBoard Members

5 cols

listsLists

7 cols

labelsLabels

5 cols

cardsCards

10 cols

card_membersCard Members

4 cols

card_labelsCard Labels

4 cols

checklistsChecklists

5 cols

checklist_itemsChecklist Items

7 cols

card_attachmentsCard Attachments

8 cols

card_commentsCard Comments

6 cols

card_activitiesCard Activities

6 cols

subscriptionsSubscriptions

8 cols

Frequently asked

Why position columns instead of a linked list?: Because reading a list in order happens constantly and a linked list makes that a walk. A position column sorts in one scan; the price is that dropping between two items eventually needs a renumber, which is cheap when done per board and lazily.
Why are labels per board?: A label is a team's vocabulary for one project. Global labels mean one team renaming 'Blocked' changes it on someone else's board, and colours would have to mean the same thing everywhere.
Do I need both workspace_members and board_members?: If every board is open to the whole workspace, no. The moment one board needs to be private, yes — and retrofitting the second table means auditing every existing board's visibility.
Why is card_activities a blob rather than typed columns?: Each action_type carries different detail — a move has two lists, a comment has text, a due date has a date. Columns for all of them would be mostly null; the blob keeps the table narrow and the shape a matter for the application.
Where does the subscription sit?: On the workspace, not the user. The plan governs what the workspace's boards can do, so a person on two workspaces gets different features in each, which is what their employers paid for separately.

Related Templates

View all templates →

Trello Clone

About this schema

Key relationships

Design decisions

Order is a position column, not a linked list

Labels belong to a board

Two levels of membership

Activity is a log, not derived

Tables in this template

Frequently asked

Related Templates

Stripe

SaaS Multi-Tenant

Analytics Dashboard

Helpdesk Ticketing