Track project · 09

Design the TaskFlow PostgreSQL Schema

Build and query the relational model directly in PostgreSQL before introducing the ORM.

Build plan

Work through it one observable step at a time

01
Create tables with deliberate data types and primary keys.
02
Add foreign keys plus NOT NULL, UNIQUE, and CHECK constraints where they express real invariants.
03
Insert realistic sample rows.
04
Write SELECT/WHERE/ORDER BY queries used by TaskFlow.
05
Write a JOIN showing task owner or workspace information.
06
Write an aggregate query for task counts by status.
07
Wrap a multi-row business change in a transaction and test rollback.
08
Create a candidate index for a real list query, then compare `EXPLAIN ANALYZE` before and after.
09
Explain which rules belong in PostgreSQL versus application code.

Evidence

Do not rely on “it seems to work”

Evidence to collect

A clean Git diff or file-tree changeCommands used and their exit/resultBrowser, HTTP, database, test, container, or CI evidence that proves the behaviorOne deliberate failure, the evidence you used, and the root-cause fixA short explanation of why the final responsibility lives in the chosen layer

Project exit ticket

Close the project only when these are true

Completion

I can demonstrate every deliverable.I can reproduce one key part without the walkthrough.I can explain one trade-off I deliberately chose.I can show one failure that my tests, constraints, or runbook now protects against.