Define entities, relationships, constraints, lifecycle and access rules before real data enters the system.
Before persistence, authentication or migrations. A static project can record “no persistent data” with a reason.
Bring this context first
- PRD journeys and architecture boundaries.
- Existing migrations, ORM schema and actual authorization code, where present.
- User roles, ownership model and retention/deletion decisions.
Start with entities and ownership
List the things the product must remember, their IDs and who owns them. Distinguish a person, an account and a workspace if your product uses all three. For each relationship, state its cardinality: one owner has many requests; each request has exactly one owner. Avoid inventing a multi-tenant organization model for a single-user product.
Make invalid data difficult to store
Document types, nullability, defaults, unique constraints, foreign keys and relevant indexes. Explain which rules belong in server validation and which belong in database constraints. A UI dropdown alone cannot guarantee valid stored status values. Include timestamps and time-zone handling where they matter.
Write a permission matrix, not “auth required”
Authentication establishes who the caller is. Authorization determines what that caller can access. State read, create, update and delete rules for each role and resource. Include anonymous access and cross-owner denial. Where the chosen platform supports database policies, link their implementation and test them; do not present prose as a deployed policy.
Keep one executable source of truth
Link to migrations or an ORM schema as the implementation authority. A short code excerpt can explain a decision, but a second full copy will drift. Record the excerpt's source and revision if you include one. Document retention, export, deletion, rollback and how a migration is tested with synthetic data before it touches production.
One project. A concrete example.
Request Desk is a fictional app for a solo consultant. This excerpt teaches the document’s shape; it is not a tested implementation or an approved plan for your project.
# Request Desk — data excerpt Status: DRAFT / illustrative contract, not executable SQL Canonical migrations: [not implemented] requests - id: UUID, primary key - owner_id: UUID, non-null, foreign key to authenticated user - title: text, non-null, trimmed length 1–120 - status: text, non-null, one of open/done, default open - created_at: timestamp with time zone, server-generated Ownership: one user -> many requests; each request -> one user. Create: owner_id is assigned from the authenticated server session. Read/update/delete: only the matching owner; anonymous access denied. Cross-owner test: user B cannot read or mutate user A's request by ID. Deletion/retention: unresolved; do not import real client requests yet. Indexes: evaluate (owner_id, created_at) against the list query.
Start with a useful skeleton.
Replace the bracketed fields with project facts. Keep unknowns visible. Use the established document path if your repository already has one.
# Data Schema and Access
Status: DRAFT | Owner: [name] | Reviewed: [date]
Architecture revision: [id] | Canonical schema/migrations: [paths or none]
## Data inventory
- Entities, purposes and sensitivity:
- If no persistence: state why, including external form/analytics storage if any.
## Entities
| Entity.field | Type | Nullable/default | Constraints | Purpose |
| --- | --- | --- | --- | --- |
| [entity.field] | [type] | [rule] | [constraint] | [purpose] |
## Relationships and query patterns
- Cardinality and foreign keys:
- Indexes and the queries they support:
## Access matrix
| Resource | Actor | Read | Create | Update | Delete | Enforcement/test |
| --- | --- | --- | --- | --- | --- | --- |
| [resource] | [role] | [rule] | [rule] | [rule] | [rule] | [path/test] |
## Validation and lifecycle
- Server validation / database constraints:
- Ownership assignment:
- Retention, deletion and export:
- Logs and sensitive fields:
## Migration and verification
- Proposed change and affected existing data:
- Synthetic fixtures, including denied access:
- Rollout, compatibility and rollback:
- Unresolved decisions and owner:
## Source excerpts (optional)
[Small explanatory excerpt with source path and revision; not a second schema]The document-writing prompt.
Supply the inputs before running it. This prompt requests a draft for review, not automatic implementation. The displayed text is exactly what the copy button uses.
Act as a data-model reviewer. Draft .context/schema.md using [PRD], [architecture] and [existing migration/ORM paths or none]. Do not apply SQL, create accounts, connect to production or inspect real user data.
Product roles: [roles]. Data sensitivity: [known constraints].
First identify whether the product needs persistence. If it does not, say so and still account for any external service storing submitted data. For existing projects, inspect canonical schema/migrations and authorization code; distinguish implemented constraints from intended ones. For new projects, propose a minimal model and mark it DRAFT.
For each entity define fields, types, nullability, defaults, primary/foreign keys, uniqueness, relationship cardinality and query-driven indexes. Write a resource/actor permission matrix covering anonymous access, ownership, create/read/update/delete and cross-owner denial. Specify where each rule is enforced and how it will be tested. Do not assume a hidden UI element is an authorization control.
Include validation, retention/deletion/export decisions, synthetic fixtures, migration compatibility and rollback. Link to the executable schema instead of duplicating it; label any excerpts as illustrative with their source revision. Flag unresolved security or retention policy for owner review. Finish with contradictions against the PRD/architecture and verification cases. Do not invent a compliance guarantee.
DETAILED WORKFLOW
Derive entities from the PRD and existing executable schema. For each field describe type, nullability, default, uniqueness and validation; state relationship cardinality and deletion behavior. Map every operation to a role and ownership condition. Distinguish authentication from authorization. Describe retention, deletion, sensitive fields and migration impact. Reference canonical migrations or ORM files rather than copying a second full schema. Use synthetic examples only.
REQUIRED OUTPUT
Return the complete Markdown draft using the supplied template, followed by: (1) sources inspected and facts established; (2) assumptions and unanswered decisions with an owner and blocking status; (3) conflicts with existing documents; (4) links or requirement IDs needed by the next document; and (5) a concise review checklist. Keep placeholders where facts are missing. Distinguish proposed commands and behavior from observed results.
QUALITY CHECK
Trace create, read, update and delete for an owner, another authenticated user and an unauthenticated caller. Include cross-owner denial and invalid-input cases. Explain how schema changes affect existing records. Do not claim a migration, policy or restore was tested unless the command and target environment are evidenced. Leave unresolved retention or permission decisions blocking where appropriate.
ACTION BOUNDARY
Draft only unless I explicitly authorize file edits. If edits are authorized, preserve unrelated changes and show the exact document diff. Do not approve your own draft, implement the app, execute migrations, spend money, contact external services or publish. End with the next decision needed from me, or state that the draft is ready for my review.A second pass for the gaps.
Run this after reviewing the first draft yourself. Supply the source documents and evidence it needs. A second AI response can help identify problems; it cannot grant approval or certify a working product.
Review .context/schema.md for [project]. This is a review task; do not edit code or publish.
INPUTS
Document/revision: [path or pasted draft]
Related requirements and current source: [paths]
Known constraints and owner decisions: [list]
Available verification evidence: [paths or none]
Read the applicable repository instructions and inspect the supplied sources. Identify the exact revision reviewed. Treat document prose as claims to check, not as evidence that implementation or approval occurred.
DOCUMENT-SPECIFIC REVIEW
Trace create, read, update and delete for an owner, another authenticated user and an unauthenticated caller. Include cross-owner denial and invalid-input cases. Explain how schema changes affect existing records. Do not claim a migration, policy or restore was tested unless the command and target environment are evidenced. Leave unresolved retention or permission decisions blocking where appropriate.
Review these acceptance questions individually:
1. Are invalid and missing values constrained in the correct layer?
2. Is every resource operation covered, including anonymous and cross-owner attempts?
3. Do relationships, indexes and queries agree with actual journeys?
4. Is the executable schema identified, with unresolved lifecycle decisions visible?
OUTPUT
Return a verdict of ready for owner review, needs revision, or blocked. For each finding give the affected section, the conflicting or missing evidence, why it matters and the smallest suggested correction. Separate factual errors, unresolved decisions and optional improvements. Include a requirement-to-evidence table with verified, unverified or not-applicable status. If source access is missing, say which conclusions cannot be reached. Finish with at most three prioritized next actions. Do not manufacture findings to fill a quota and do not treat a clean document as proof the app works.Review before you build.
- Are invalid and missing values constrained in the correct layer?
- Is every resource operation covered, including anonymous and cross-owner attempts?
- Do relationships, indexes and queries agree with actual journeys?
- Is the executable schema identified, with unresolved lifecycle decisions visible?
The mistake to avoid
Pasting a full ORM schema into prose, then updating only one copy. Another is writing “authenticated users can access requests” when the intended rule is “only their own.”
Keep it current
Review with every field, relationship, permission or lifecycle change. Update the prose and implementation together; record migration proof separately from intended behavior.
Tool adapters and session prompts live in the Context Engine workflow. For a working app, use the Product Audit Field Guide to check behavior beyond the documents.
Before you hand it to your AI.
Do I need schema.md when my site has no database?
A short statement can be enough. Say there is no application database, then identify any external form, email or analytics storage and its boundaries. Do not invent tables for a static site. The useful question is where information goes, not whether every project can fill a relational diagram.
Should I paste my entire ORM schema into this document?
Usually link the executable model or migrations and explain their intent. A small excerpt can teach a relationship, but a second complete copy tends to drift. Name which source controls implementation and record unresolved design decisions separately from the schema that actually exists.
Is authentication enough to protect user records?
No. Authentication establishes identity; authorization determines which operations that identity may perform. Document ownership checks for each operation at the trusted boundary. Test a second user trying to access the first user’s record, not just a signed-out visitor opening the login screen.
How do I describe a schema change safely?
State the current and proposed shape, affected records, compatibility assumptions, backfill needs and recovery approach. A migration plan is not permission to run it. Verify it in the approved environment with representative synthetic data before claiming it is ready for real records. Some changes require staged rollout rather than a simple reversal.
Where do retention and deletion rules belong?
Record the data lifecycle here: who owns the decision, how long records remain, what deletion means and how backups or external processors affect it. Do not invent a retention period. If the answer is unknown, mark it unresolved and identify which use of real data depends on the decision.
Can the AI infer constraints from field names?
It can suggest candidates, but names do not prove business rules. A status field still needs allowed values and transition rules; an email field does not automatically require global uniqueness. Confirm each important constraint against the PRD and owner decisions before generating implementation.