Turn Requirements into ER Diagrams and Invariants

Data modeling begins by discovering facts, ownership, identity, cardinality, lifecycle, and forbidden states—not by drawing boxes or choosing column types. An ER diagram communicates structure, while written invariants explain what must remain true under every application, import, job, and AI tool. Together they become the contract for schema and tests.

What will you be able to do?

• Extract entities, attributes, events, and relationships from prose.
• State minimum/maximum cardinality and optionality.
• Separate current state from immutable history.
• Turn invariants into constraint and transaction candidates.

Prerequisites and mental model

Interview the domain with six questions: what fact, whose fact, identified how, related to what, valid when, retained how? A diagram is a map; invariants are traffic laws.

How do you model a new requirement?

Requirement: “Agents can belong to several organizations. A ticket may be assigned to one active member of its organization. Reassignment history must be auditable.”

Discover:

• user identity is independent of organization;
• membership is the many-to-many relationship and carries role/status;
• current assignment is optional and tenant-bound;
• assignment changes are events, not overwritten history;
• “active at assignment time” is a cross-row/time rule needing transactional logic.

Candidate invariants:

1. one membership per organization/user;
2. ticket and assignee membership share organization;
3. assignment event is append-only and names actor, old/new assignee, and time;
4. suspended memberships cannot receive new assignments;
5. history remains understandable after display names change.

How do you verify a model before DDL?

Walk real scenarios: invite, accept, suspend, rejoin, assign, transfer, merge customer, delete tenant, legal hold. For each, state rows created/changed, transaction boundary, retained history, and failure response. Mark rules enforceable by NOT NULL, UNIQUE, CHECK, foreign keys, exclusion constraints, triggers, or application transactions.

Why does this matter in AI-native systems?

AI tools amplify ambiguous requirements. “Assign to the best agent” hides tenant membership, working hours, workload source, approval, and audit requirements. Model tool inputs around stable IDs and explicit allowed transitions; store the model recommendation separately from the authorized business event.

Prove it worked

Produce an ERD plus an invariant catalog with columns: ID, statement, owner, enforcement, positive test, negative test, migration impact. Another person must model two scenarios without asking what a relationship means.

Production judgment

• Diagrams omit critical behavior unless temporal and deletion rules are written.
• Surrogate keys do not replace business uniqueness.
• “Optional” can mean unknown, not-yet, or not-applicable; model the reason if it matters.
• Audit history must preserve identifiers and context without retaining prohibited data forever.

Common mistakes

• Boxes mirror UI screens: workflows mistaken for facts → model durable predicates.
• Many-to-many stored as arrays: relationship attributes disappear → use an associative table.
• History overwritten: only current state modeled → add immutable events where required.
• AI action lacks actor: recommendation and authorization conflated → store both.

AI Pair-Programmer Prompt

Hands-on exercise

Model ticket tags, including organization-owned tag definitions and many-to-many assignments. Decide whether tag names can be renamed and reused after archival.

Acceptance criterion: your model prevents cross-tenant assignments and duplicates while preserving the stated rename/archive behavior.

Knowledge check

1. What does an ERD fail to express by itself?
2. Why is membership an entity rather than a direct user column?
3. Why separate recommendation from assignment event?

---

Answers

1. Many lifecycle, temporal, security, transaction, and retention rules.
2. It represents a many-to-many relationship with its own role and state.
3. A model suggestion is not the authorized business action and needs separate provenance.

Completion checklist

• Relationships have minimum/maximum cardinality.
• Invariants have positive and negative tests.
• State and history are separated deliberately.
• AI recommendations cannot impersonate authorized events.

Primary references

• PostgreSQL table basics
• Constraints
• Foreign keys