Skip to main content
One idea at a time

Relationships belong in the database

Your frontend often receives nested JSON. PostgreSQL stores related facts in tables, and a query assembles those facts for the response.

Maya is a user. Acme is a workspace. Maya's membership connects the two and records her role in Acme. A second membership can give Maya a different role in another workspace.

These are different facts with different lifecycles. Deleting one membership should not delete Maya's global account or her membership in another workspace.

A row has one subject​

A users row describes an identity. A workspaces row describes a tenant. A memberships row describes one user's participation in one workspace. A unique membership key prevents the same user from holding duplicate memberships in that workspace.

Projects belong to workspaces. Tasks belong to projects and carry the workspace identifier needed for explicit tenant queries. Comments belong to tasks and also carry their workspace identifier.

Repeating the workspace identifier requires consistency checks. The database must prevent a task from naming Acme while referring to a project owned by another workspace. Later constraints lessons explain composite foreign keys.

Data types encode meaning​

Taskboard uses UUID identifiers rather than JavaScript array positions. A primary key identifies a row across requests and process restarts. A UUID is still only an identifier, not an access credential.

A role has a small defined set of values. Drizzle's text({ enum: roleValues }) narrows the TypeScript type. Taskboard also adds a database CHECK constraint, which prevents a handwritten SQL statement from inserting an unsupported role. The TypeScript enum option alone would not supply that database guarantee.

An unassigned task has no assignee. SQL expresses that absence with NULL. It is different from an empty string and from a fake user identifier. Comparisons involving NULL follow SQL's three-valued logic, so a query tests absence with IS NULL.

Timestamps represent moments in time. The schema uses timezone-aware timestamps, and API output should use an unambiguous format such as an ISO string with an offset. JavaScript dates have millisecond precision. Do not assume that a timestamp can always serve as a unique version or ordering key.

A common design mistake is storing workspace roles on users. That makes Maya's role global, which cannot represent her being an owner in Acme and a member elsewhere.

Why does the membership need its own table?

Users and workspaces have a many-to-many relationship. The membership stores the relationship itself and facts about it, such as the workspace-specific role.

Choose tables around facts and lifecycles. A response's JSON shape does not have to match the storage layout.

Read api/src/schema.ts