Back to all posts

Designing a Schema for a Collaborative Task App

For the backend developer who owns a multi-tenant task app schema and shares it with the team through Git; the tables run on PostgreSQL 17.

On this page

Two engineers add a column to the same table on two branches in the same week. Nobody finds out until one of the migrations fails on staging. Keeping the design in a file that declares the tables and sits in the repository, next to the application code, moves that collision earlier: the two changes meet as a text diff in a pull request instead of as a failed deployment.

Creating the baseline logical design

A collaborative task app loads one schema with tenants that must not see each other's rows, roles per tenant, tasks nested inside tasks, assignment to more than one person, and links that are public to people with no account. Each of those is a table somebody will change while somebody else changes another.

Start above the database. Before a task app has a data type it has boundaries: who owns what, and which rows belong to which tenant. Draw those as entities and the relations between them before you commit to an engine, and mark the relations that are identifying: the parent key is part of the child key, and the child cannot exist without the parent. A workspace membership is exactly that: it has no meaning apart from the workspace and the user it joins.

Converting that to physical tables is where nullability and keys become decisions rather than notes:

CREATE TABLE workspaces (
  workspace_id bigint PRIMARY KEY,
  name         text NOT NULL
);
CREATE TABLE users (
  user_id bigint PRIMARY KEY,
  email   text NOT NULL UNIQUE
);
CREATE TABLE workspace_members (
  workspace_id bigint NOT NULL REFERENCES workspaces,
  user_id      bigint NOT NULL REFERENCES users,
  role         text NOT NULL,
  PRIMARY KEY (workspace_id, user_id)
);
CREATE TABLE projects (
  project_id   bigint PRIMARY KEY,
  workspace_id bigint NOT NULL REFERENCES workspaces,
  name         text NOT NULL,
  is_private   boolean NOT NULL DEFAULT false
);
INSERT INTO workspaces VALUES (1, 'Acme');
INSERT INTO projects VALUES (10, 1, 'Billing', false);

The composite primary key on workspace_members is doing real work: it stops the same person being added to the same workspace twice, and it makes the role a property of the pair rather than of the user. A surrogate key there would need a unique index on the same two columns to say as much. Settling this before data arrives is the point of logical database design, since splitting a table after ingestion costs a data migration as well as a DDL one.

The task tables that carry assignment and subtasks

Three requirements shape the task tables. A task can be dragged into a new position on a board. A task can nest under another task. A task can be assigned to several people. Each of them is a column or a table, and skipping any one of them means a rewrite later:

CREATE TABLE tasks (
  task_id        bigint PRIMARY KEY,
  project_id     bigint NOT NULL REFERENCES projects,
  parent_task_id bigint REFERENCES tasks,
  position       int NOT NULL,
  status         text NOT NULL,
  title          text NOT NULL
);
CREATE TABLE task_assignees (
  task_id     bigint NOT NULL REFERENCES tasks ON DELETE CASCADE,
  user_id     bigint NOT NULL REFERENCES users,
  assigned_at timestamptz NOT NULL,
  PRIMARY KEY (task_id, user_id)
);
CREATE TABLE task_dependencies (
  blocking_task_id  bigint NOT NULL REFERENCES tasks ON DELETE CASCADE,
  dependent_task_id bigint NOT NULL REFERENCES tasks ON DELETE CASCADE,
  PRIMARY KEY (blocking_task_id, dependent_task_id)
);
INSERT INTO tasks VALUES
  (100, 10, NULL, 1, 'open', 'Ship the billing page'),
  (101, 10, 100,  1, 'open', 'Draft the copy'),
  (102, 10, 100,  2, 'open', 'Wire the API'),
  (103, 10, 101,  1, 'done', 'Proofread the copy');

The position column is what lets a client reorder a board by writing one row instead of locking the project's tasks to renumber them. The nullable parent_task_id pointing back at tasks is what makes a subtask a task rather than a second kind of record, so a subtask can be assigned, closed and depended on with the same code. Assignment lives in task_assignees rather than in an array column on the task, which keeps the foreign key to users and is the reason normalization is worth the extra join here. The cascade goes one way on purpose: deleting a task removes its assignment and dependency rows, while deleting a user is refused for as long as that person is assigned to anything.

Reading a subtask tree back is a recursive query. In PostgreSQL 17 the RECURSIVE modifier lets a WITH query refer to its own output, with a non-recursive term, UNION or UNION ALL, and a recursive term that references the query[1]:

WITH RECURSIVE subtree AS (
  SELECT task_id, parent_task_id, title, 1 AS depth
  FROM tasks
  WHERE parent_task_id IS NULL AND project_id = 10
  UNION ALL
  SELECT t.task_id, t.parent_task_id, t.title, s.depth + 1
  FROM tasks t
  JOIN subtree s ON t.parent_task_id = s.task_id
)
SELECT title, depth
FROM subtree
ORDER BY depth, title;
titledepth
Ship the billing page1
Draft the copy2
Wire the API2
Proofread the copy3

The same page carries the warning that decides whether this query is safe to expose: the recursive term has to stop returning rows, or the query loops indefinitely. A parent_task_id chain that ever points back up at an ancestor is a cycle, and the query above will spin on it, so either the application refuses to set a parent that is already a descendant, or the query uses the CYCLE clause PostgreSQL 17 provides to detect one.

What to check afterwards

A multi-tenant schema fails in production in ways a development database never shows, because the development copy has one workspace in it and production has thousands. Four checks are worth running once these tables reach a shared database:

  1. Confirm that a composite index exists on the tenant columns the queries filter on, (workspace_id, project_id) rather than workspace_id alone, so a project board does not scan every project in the workspace.
  2. Run EXPLAIN ANALYZE on the recursive subtask query for the deepest tree in the data, and read whether the recursive term uses an index on parent_task_id. PostgreSQL creates one for a primary key or a unique constraint, never for a foreign key.
  3. Delete a task in a transaction and roll it back, checking that the rows in task_assignees and task_dependencies went with it.
  4. Insert a subtask whose parent sits in a different project, and watch the row go in.

The fourth check fails, which is the point of running it. parent_task_id references tasks on its own, so nothing above keeps a subtask inside its parent's project. Two statements move that rule into the database:

ALTER TABLE tasks ADD UNIQUE (project_id, task_id);
ALTER TABLE tasks ADD FOREIGN KEY (project_id, parent_task_id)
  REFERENCES tasks (project_id, task_id);

A root task still inserts cleanly. Under the default MATCH SIMPLE, any foreign key column may be null, and a row with a null in one of them is not required to match anything in the referenced table[2], which is the case a task with no parent is in.

Where the schema lives while the team works on it

DbSchema keeps the whole design in one file with the extension .dbs: tables, columns, foreign keys, diagram positions, and the queries saved with them. The file is plain XML, so Git records an added column as an added element and shows it in an ordinary diff. Each developer branches, edits the model, and opens a pull request that a colleague reads as text or opens in DbSchema as a diagram, which is what makes team collaboration on schema design reviewable at all.

In DbSchema, the baseline above can start as a logical design, drawn without an engine. Choose Design from Scratch on the welcome screen, then Logical Design, and right-click the diagram and choose Create Entity for the first entity. A relation is drawn by dragging the attribute of the child entity onto the key attribute of the parent, and DbSchema lets you mark it Identifying, as the workspace membership is.

Editing the model while offline changes only the file. Nothing is sent to a database until you synchronize the model with the database, review the generated statements and run them, or until you make a change with a connection open, in which case DbSchema applies it to the database immediately.

Splitting the model into focused diagrams

One canvas holding every table stops being readable once the model outgrows the screen, and a task app gets there as soon as billing and audit arrive. DbSchema holds many diagrams in one model, each with its own layout and its own set of visible tables, while the schema definition underneath stays single. Add one with Diagram → New Diagram.

A second focused diagram of the task subject area beside the full model diagram in DbSchema

The split that works follows the teams rather than the alphabet: identity, holding users, workspace_members and the invitation tables; execution, holding projects, tasks, task_assignees and task_dependencies; sharing, holding the public link and guest permission tables; audit, holding the event and webhook tables. The same table appears in as many of them as it belongs to, so tasks can sit at the center of the execution diagram and also appear in a billing diagram next to the tables that count hours against it.

Editing a column on any of those diagrams changes the one definition in the model, so the others show the change the moment you look at them. The split is therefore a set of views over one schema rather than four schemas to keep in agreement.

A feature branch that adds two tables

Shipping a list-sharing feature means two new tables that must not reach the shared database until the code that uses them is reviewed. The branch does that work, and DbSchema stays offline for all of it.

The DbSchema Git dialog with a working-tree entry, a diff link, commit message fields and the model repository commit graph
  1. Create a branch, either with git branch feature/list-sharing or with More → New branch in the DbSchema Git dialog, which File → Git Collaboration opens.
  2. Open the .dbs model file in DbSchema and choose Database → Offline (connect later), so every edit goes to the file and nothing reaches a database.
  3. Add the two tables and their foreign keys on the diagram, in offline design mode.
  4. In the Git dialog, tick the model file, type a Commit message, press Commit, then press Push to send the branch for review.

When the branch merges, the database still knows nothing about it: synchronizing the model with the database compares the merged model against the live schema and generates the migration, which you review and run.

The common failure and its fix

The failure that actually happens is two branches editing the same table. One developer adds estimated_hours to tasks; another adds priority_level to tasks; both add an index on their own column. Git reports a conflict, because both edits changed lines inside the same table element in the XML.

What conflictsBranch ABranch BResolution
Tabletaskstaskstasks
New columnestimated_hours numeric(5,2)priority_level varchar(20)Keep both
New indexon estimated_hourson priority_levelKeep both
Foreign keysNone addedNone addedUnchanged

Both edits are additions, so keeping both elements resolves nearly every conflict of this shape, and the merge is a text merge like any other. What earns a careful read is the pair that is not additive: two columns with the same name and different types, or two indexes on the same column with different names.

Open the merged file in DbSchema before you push it, because an XML file that parses is not the same as a model that makes sense, then compare it against the staging database and read the generated script. That comparison is what catches a resolution that dropped an element, since the script will simply not contain the statement that adds the column somebody lost.

Saving the model to a file, Git Collaboration and schema synchronization are Pro edition features, and logical design is in the Architect edition; connecting, reverse-engineering and the interactive diagrams are free in the Community edition. Get DbSchema from https://dbschema.com/download.html, reverse-engineer the database your task app already runs on, and commit the .dbs file into the repository the application lives in. The next schema change then arrives as a pull request.

Frequently asked questions

How do teams collaborate on database schema design?

The team commits the DbSchema model file to the same Git repository as the application code and branches for each schema change. The reviewer reads the XML diff in the pull request or opens the model in DbSchema to see the diagram, and the approved change reaches the database when DbSchema synchronizes the model with it.

What is the best way to handle schema merge conflicts?

Because the DbSchema model file is XML, a conflict appears in the ordinary Git conflict markers, inside the element of the table both branches touched. Resolve it in a text editor, open the merged file in DbSchema to confirm the model still parses and still makes sense, then compare it against the staging database before generating the migration.

How many tables does a typical task management app require?

More than a first sketch suggests. The DbSchema demo model of a task app, the one behind the diagrams on this page, holds 14 tables, 2 views, 25 indexes and 23 foreign keys, and that is before multi-tenant billing, custom fields and audit history are added.

Can a single database table appear in multiple ER diagrams?

Yes, DbSchema lets one table appear on as many diagrams of the same model as you like, each diagram keeping its own layout and its own set of visible tables. Editing a column on any of them changes the single definition in the model, so the diagrams cannot drift apart.

Sources

  1. postgresql.org
  2. postgresql.org

Open the model against your own database

DbSchema reverse-engineers your database into an ER diagram and lets one table sit in several focused diagrams of the same model. The Community Edition is free; saving the model to a file for Git and schema synchronization are Pro features.