Optionality, Keys & Constraints Put invariants in the database instead of only in application code
Phase 10 - Relational Data Modeling Volume 03 - Database Engineering
↓
↓
Phase 10 decides what the durable data should mean before Phase 11 writes application behavior against it.
A valid row is more than a TypeScript object Database constraints remain active regardless of which application code writes the row. They are therefore the final line of defense for structural integrity.
↓
↓
↓
The closer a rule is to a permanent data invariant, the stronger the case for enforcing it in the database too.
Primary keys Every durable entity needs a stable identity. For TaskFlow, UUID-style IDs are appropriate for entities that may cross service/API boundaries. The course will use UUID-compatible Prisma String IDs generated with uuid() for production entities.
Natural vs surrogate keys Key type Example Trade-off
Surrogate generated UUID Stable identity independent of business attributes
Uniqueness Uniqueness should reflect business invariants. Example: a workspace slug may be globally unique; a membership should be unique for the pair (workspaceId, userId); project names may or may not be unique depending on product rules. @@unique([workspaceId, userId])
NULL is a domain decision Field Possible rule Schema implication
Check-like invariants Prisma schema syntax does not express every possible PostgreSQL CHECK constraint directly. When the database must guarantee an invariant that exceeds ORM-level expressiveness, use reviewed SQL migrations rather than pretending the invariant does not exist. Do not encode every business rule as a database constraint. Workflow rules that require external state, permissions, or multiple aggregates usually belong in application services. Use the database for structural invariants it can enforce reliably.
Exercise Classify each rule as primarily database constraint, service rule, or both: unique workspace slug; user must be workspace member before task assignment; task title required; invoice approval requires manager role; membership pair must be unique.
Optionality Keys and Constraints matters because it changes a real boundary in TaskFlow. The goal is to understand the inputs, owner, observable output, and failure modes before adding more abstraction.
Restate the goal of “Optionality Keys and Constraints” as one observable behavior. Write the success signal before touching code.
Trace the boundary map: business noun → invariant → table/relation → constraint → query shape. 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 complete Prisma/ER model for TaskFlow core entities with keys, relation tables, uniqueness, and important indexes.. 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.
Input -> owner -> transformation -> observable output -> failure evidenceDo 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.
Return to the nearest observable boundary: input, network, process log, database, or rendered UI. Compare actual evidence with the predicted path.
Change one variable at a time and reproduce the original failure after the fix; otherwise you have correlation, not diagnosis.
Without copying the final source, create a second small scenario that applies “Optionality Keys and Constraints” to a different TaskFlow resource or input. Write the acceptance criteria first, implement it, and provide the evidence that each criterion is satisfied.
Model workspace membership as an explicit relation carrying role/state rather than an array on User.
Use the smallest change that demonstrates this lesson. Stop after it works and capture evidence.Ambiguous cascade-delete behavior
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.