FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 07 Relational Database Mental Model
Relational Database Mental Model Durable state, tables, rows, schemas and invariants
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 Relational Database Mental Model
A database is durable application state Our in-memory Todo array is useful for teaching HTTP, but it is not durable application state. A relational database gives the application a separate system whose job is to store facts, enforce rules around those facts, answer queries and coordinate concurrent changes.
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.
Database, schema, table, row and column tasks table
id title completed project_id
column = attribute/type/rule row = one stored record
Figure - Tables organize typed columns; each row stores one record that must satisfy table constraints.
Database A PostgreSQL database that contains application schemas and objects.
Schema A namespace that groups database objects. PostgreSQL commonly starts with the public schema.
Table A relation used to store records with a defined set of columns and constraints.
Column A named attribute with a data type and possibly rules/defaults.
Row One stored record satisfying the table definition.
Constraint A database-enforced rule that prevents invalid states.
FULL_STACK_DEVELOPER_2026_COMPLETE - Phase 07 Relational Database Mental Model
The database should protect invariants An invariant is a condition that must remain true for valid persistent data. For example, a task title may be required; an email may need to be unique; a task may reference only an existing project. Application code can validate these conditions, but durable database constraints provide a final line of defense when multiple clients, scripts, background jobs or future services write to the same database.
Incoming row candidate data
Type checks is the value compatible?
NOT NULL / CHECK does the value satisfy local rules?
UNIQUE / PRIMARY KEY does identity remain valid?
FOREIGN KEY does the referenced row exist?
Accepted row database invariant preserved
Figure - Database constraints reject invalid states before they become persistent application data.
Rows are not objects A TypeScript object and a database row can describe the same business concept, but they are not the same thing. Objects live in process memory and can contain methods, derived values or temporary state. Database rows are persisted values governed by database types and constraints. Prisma later maps between these worlds; understanding the difference makes ORM behavior less surprising.
Set-oriented thinking SQL is designed to describe sets of rows. Instead of looping through every task in JavaScript to find incomplete items, a query asks the database for the set matching a condition. This becomes increasingly important as data grows beyond what can be loaded into application memory safely. Think in sets
SELECT id, title FROM tasks WHERE completed = false ORDER BY created_at DESC;
In an HRIS, Payroll, Inventory or Procurement system, the same mental model applies: tables persist facts, constraints protect valid relationships, and application services orchestrate workflows around that durable state.
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 “Relational Database Mental Model” 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 “Relational Database Mental Model” 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.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.