Constraints protect every writer

Frontend validation protects one interaction. Database constraints protect stored data regardless of which route, worker, migration, or SQL client writes it.

Suppose two requests both check whether Maya already belongs to Acme. Both reads say no. Both try to insert a membership. An application check alone cannot stop that race.

A unique constraint on the workspace and user pair lets PostgreSQL reject the second insert. The guarantee happens where concurrent writes meet.

A foreign key checks a relationship

A task's project_id must refer to an existing project. A plain foreign key proves that the project exists. It does not prove that the task and project name the same workspace.

The stronger relationship uses both identifiers:

-- Illustrative form of the tenant relationship.
FOREIGN KEY (workspace_id, project_id)
REFERENCES projects (workspace_id, id)

PostgreSQL requires an appropriate unique key on the referenced pair. The constraint then rejects an Acme task pointing to another workspace's project, even if buggy application code attempts it.

Taskboard applies this pattern to tenant-owned parent relationships. Read the schema to see the actual constraint names and columns.

Different constraints answer different questions

A primary key identifies a row. NOT NULL requires a value. A unique constraint prevents duplicate keys. A check constraint can require a version or count to satisfy a rule. A foreign key checks a relationship between rows.

These guarantees do not replace authorization. An attacker might submit a perfectly valid task update belonging to another tenant. The database can accept a structurally consistent update unless the application also constrains access.

There are details worth learning before treating a constraint as a promise. SQL NULL represents an unknown or absent value. A check expression that evaluates to unknown can pass, so a check and NOT NULL often work together. Unique treatment of nullable columns also needs an explicit design.

A common mistake is assuming Drizzle's inferred TypeScript type protects rows inserted outside the Node application. Only the migration's actual database definition protects those writes.

What does a composite task-to-project foreign key prevent?

It prevents a task from combining one workspace identifier with a project belonging to a different workspace. It does not decide which user may read the task.

Use constraints for facts that must hold across all writers. Keep caller permissions in the authorization path as well.

Read api/src/schema.ts