FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 07 Exercises and Phase Exit
Exercises and Phase Exit SQL practice, relational design drills and the Phase 08 readiness standard
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 Exercises and Phase Exit
Active recall - explain before looking back ● What is the difference between a database, schema, table, row and column? ● Why is NULL not equivalent to an empty string or false? ● What invariant does a primary key protect? ● What invariant does a foreign key protect? ● Why does PostgreSQL not automatically index the referencing side of every foreign key? ● When would LEFT JOIN answer a different product question than INNER JOIN? ● Why can UPDATE or DELETE without WHERE be valid and dangerous at the same time? ● What does a transaction protect that two unrelated SQL statements do not? ● Why can adding an index improve reads but make writes more expensive? ● What problem is normalization trying to reduce? ● Why are migration history and final schema different artifacts? ● Why should application code use parameters instead of concatenating user input into SQL?
Level 1 - Follow 1 Write CREATE TABLE statements for a project and task table with primary key, NOT NULL title, completed default and project foreign key. 2 Write INSERT statements for two projects and three tasks. 3 Write a SELECT for incomplete tasks in one project ordered by priority. 4 Write an UPDATE that completes exactly one task and returns the modified row. 5 Write a DELETE that removes exactly one task and returns its id.
Level 2 - Modify 1 Add an optional due_at timestamptz column and query only tasks with a due date. 2 Add a CHECK constraint that allows priority values 1 through 3. 3 Add a UNIQUE rule appropriate for a practice label table. 4 Change a query so projects with zero tasks still appear in the result. 5 Add a useful index for a repeated query that filters by project_id and completed.
Level 3 - Implement Design a small labels feature without copying a solution. A task can have many labels and a label can belong to many tasks. Produce the tables, keys, constraints, join table, one INSERT flow and one JOIN query that returns task titles with label names.
Level 4 - Engineer TaskFlow needs project archiving. Archived projects should remain queryable for audit/history, ordinary task lists should exclude them, and users must not accidentally create new tasks in archived projects. Decide which rules belong in the database and which belong in NestJS business logic. Document your reasoning; there may be more than one defensible design.
FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 07 Exercises and Phase Exit
Debugging drills Scenario First question
INSERT fails with duplicate key Which UNIQUE/PK rule was violated and is the conflict expected?
Task references project 999 and fails Does project 999 exist? Should orphan tasks ever be legal?
DELETE project fails due to tasks Is RESTRICT/NO ACTION the intended domain behavior?Query shows duplicate project rows Did a one-to-many JOIN multiply the parent row?
Dashboard count is wrong Are you filtering before/after grouping correctly?
Query became slow as data grew What does EXPLAIN show and which access pattern dominates?
Phase 07 exit checklist ● [ ] You can explain relational tables and durable invariants without mentioning Prisma. ● [ ] You can choose practical types for text, booleans, numbers and timestamps. ● [ ] You understand NULL and use IS NULL correctly. ● [ ] You can distinguish PK, FK, UNIQUE, NOT NULL and CHECK responsibilities. ● [ ] You can write basic SELECT, INSERT, UPDATE and DELETE statements safely. ● [ ] You can write an INNER JOIN and explain when a LEFT JOIN is required. ● [ ] You can explain BEGIN, COMMIT and ROLLBACK. ● [ ] You can justify an index from a query pattern and name its write/storage cost. ● [ ] You can identify duplicated durable facts and propose a more normalized model. ● [ ] You understand that schema changes will later be captured as migrations. ● [ ] You can debug database problems independently of Next.js and NestJS.
We now understand the database itself. Phase 08 will provision and explore the real managed PostgreSQL environment in Supabase: project creation, connection modes, credentials, SQL Editor, psql connectivity, environment secrets and the operational boundary between our app and its database.
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 “Exercises and Phase Exit” 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 “Exercises and Phase Exit” to a different TaskFlow resource or input. Write the acceptance criteria first, implement it, and provide the evidence that each criterion is satisfied.
Write joins by hand and predict row counts before executing them.
Use the smallest change that demonstrates this lesson. Stop after it works and capture evidence.Treating IDs as the only integrity mechanism
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.