FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 07 TaskFlow Data Modeling Lab
TaskFlow Data Modeling Lab Translate product questions into tables, constraints, joins and indexes
Persistence becomes understandable when the database stops being magic.
understand the database before Prisma
Figure - Phase 07 isolates PostgreSQL fundamentals so later ORM code has a clear relational meaning.
FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 07 TaskFlow Data Modeling Lab
TaskFlow practice schema - not the final production schema This lab uses a deliberately small relational model so you can reason about real application questions. Phase 10 will design the final TaskFlow model after Supabase and Prisma are available. Do not prematurely copy this practice schema into production.
Relationships are data rules, not drawing decorations.
projects tasks comments
id PK id PK id PK name project_id FK task_id FK
one project many tasks many comments
tables + constraints + relationships + transactions
Figure - A relational schema stores facts in tables and uses keys/constraints to preserve valid relationships.
CREATE TABLE practice_projects ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL UNIQUE, created_at timestamptz NOT NULL DEFAULT now() );
CREATE TABLE practice_tasks ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, project_id bigint NOT NULL REFERENCES practice_projects(id), title text NOT NULL, completed boolean NOT NULL DEFAULT false, priority smallint NOT NULL DEFAULT 2 CHECK (priority BETWEEN 1 AND 3), due_at timestamptz, created_at timestamptz NOT NULL DEFAULT now() );
Turn product questions into relational questions Product question Database question
Show open tasks for one project Filter practice_tasks by project_id and completed.
Show project name beside each task Join tasks to projects through project_id.
Count open tasks per project Filter, group by project and count.
Delete a project What should happen to its tasks? Choose FK action from product meaning.
Sort upcoming work Order by due_at while deciding how NULL due dates should appear.
Find high-priority incomplete tasks quickly Which query pattern might justify a composite index?
Seed practice rows Practice seed data
FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 07 TaskFlow Data Modeling Lab
INSERT INTO practice_projects (name) VALUES ('Course'), ('Portfolio') RETURNING id, name;
INSERT INTO practice_tasks (project_id, title, priority) VALUES (1, 'Learn relational modeling', 1), (1, 'Practice joins', 2), (2, 'Build portfolio project', 1);
Query 1 - open tasks for a project Project task list
SELECT id, title, priority FROM practice_tasks WHERE project_id = 1 AND completed = false ORDER BY priority, created_at;
Query 2 - project dashboard counts Simple project dashboard
SELECT p.id, p.name, count(t.id) FILTER (WHERE t.completed = false) AS open_tasks, count(t.id) FILTER (WHERE t.completed = true) AS done_tasks FROM practice_projects AS p LEFT JOIN practice_tasks AS t ON t.project_id = p.id GROUP BY p.id, p.name ORDER BY p.name;
Add an index because of a query, not because of a column Index tied to a repeated query pattern
CREATE INDEX practice_tasks_project_completed_idx ON practice_tasks (project_id, completed);
This index is justifiable if TaskFlow repeatedly requests tasks by project and completion state. It is not automatically justified merely because those columns exist.
Think before final schema design ● Should project names be globally unique, or only unique inside a workspace? The answer changes the UNIQUE constraint. ● Should deleting a project delete tasks, prevent deletion, archive the project, or move tasks elsewhere? This changes referential and application behavior. ● Should priority be numeric, an enum-like text value, or its own table? Choose based on domain requirements, not aesthetic preference. ● Should tasks have one assignee or many? That changes relationship cardinality. ● Should completed_at be derived from completed, or stored as historical information? The business meaning matters.
Schema design is product design expressed as persistent rules. Every key, nullability choice, constraint and cascade policy encodes assumptions about the domain.
ORMs do not remove the database. SQL literacy lets you reason about correctness, constraints, query cost, transactions, and the evidence stored in PostgreSQL.
Restate the goal of “TaskFlow Data Modeling Lab” as one observable behavior. Write the success signal before touching code.
Trace the boundary map: application intent → SQL statement → query planner → table/index → rows returned. Mark the one boundary this lesson changes.
Inspect the current repository state and record the exact files, route, table, or runtime involved. Avoid creating an abstraction before you can name the existing owner.
Implement the smallest vertical slice. Add one coherent change, run the narrowest verification command, and read the output before continuing.
Connect the slice to TaskFlow’s phase artifact: A TaskFlow relational sketch plus SQL exercises that create, query, join, update, and constrain representative data.. Verify behavior from the outside as a user or API client would.
Create one controlled failure that is safe to reproduce. Predict the symptom first, capture evidence, locate the earliest broken boundary, then fix only that cause.
Close the reference and rebuild or explain the core change from memory. Record what you would monitor or test before trusting the same change in production.
SELECT t.id, t.title, u.name AS assignee
FROM tasks AS t
LEFT JOIN users AS u ON u.id = t.assignee_id
WHERE t.workspace_id = $1
ORDER BY t.created_at DESC, t.id DESC;Do not copy this as a finished answer. Predict what each boundary does, type the smallest useful slice, run it, then compare the result with your prediction.
Capture EXPLAIN ANALYZE, row counts, filters, sort keys, and indexes before adding an index.
Inspect join cardinality and constraints; do not hide a modeling bug with DISTINCT until you understand it.
Without copying the final source, create a second small scenario that applies “TaskFlow Data Modeling Lab” to a different TaskFlow resource or input. Write the acceptance criteria first, implement it, and provide the evidence that each criterion is satisfied.
Model users, workspaces, membership, boards, lists, and tasks with primary/foreign keys on paper first.
Use the smallest change that demonstrates this lesson. Stop after it works and capture evidence.N+1 query patterns hidden by application loops
Predict the symptom before causing the failure. Then locate the earliest boundary where reality diverges from expectation.Why does this responsibility live here, what input does it trust, what output proves success, and what would go wrong if another layer owned it?
If your explanation is vague, repeat the boundary trace before moving on.Close the reference. Reproduce the core idea from memory or explain the exact request/data flow aloud. Then check your diff or notes for accidental shortcuts that bypass validation, authorization, error handling, or verification earned in earlier phases.