| name | schema-spec |
| description | Write a data schema specification document. Use when the user says "schema spec", "document this schema", "schema design doc", "data model spec", "table spec", "field definitions", "schema contract", "data dictionary", "define these fields", or needs to formally document a database schema, event schema, or API payload schema - even if they don't explicitly say "schema spec".
|
Overview
Based on Fundamentals of Data Engineering (Reis & Housley) and Designing Data-Intensive Applications (Kleppmann). A schema spec is the data contract between producers and consumers. Kleppmann's rule: schema changes are the most dangerous category of change in a data system - the spec exists to make schema evolution explicit and backward-compatible.
The test: can a consumer engineer write a query or build an integration using only this document, without reading source code or asking the producer team?
Workflow
Step 1: Write the schema header
Schema: [table name / event name / object name]
Domain: [which product or service owns this data]
Owner: [team]
Type: [relational table / event / API payload / Avro / Parquet / JSON]
Storage: [database name, topic name, or S3 path]
Created: [date]
Last modified: [date]
Version: [v1 / v2 / etc.]
Step 2: Document each field
Use a table for readability:
| Field | Type | Nullable | Default | Description |
|---|
| id | UUID | No | — | Unique record identifier, generated at insert |
| user_id | BIGINT | No | — | Foreign key to users.id |
| status | ENUM | No | 'pending' | Current state: pending, active, cancelled, completed |
| amount_cents | INTEGER | No | — | Transaction amount in cents. Never fractional. |
| created_at | TIMESTAMPTZ | No | NOW() | UTC timestamp of record creation |
| metadata | JSONB | Yes | NULL | Unstructured key-value pairs. Schema not enforced. |
For each field, ensure:
- Type is precise (INTEGER not "number", TIMESTAMPTZ not "timestamp")
- Nullable is explicit (yes or no, not ambiguous)
- Description explains business meaning, not just the field name
- Enums list all valid values
- Units are explicit (cents not dollars, seconds not "time", bytes not "size")
Step 3: Document primary and foreign keys
Primary key: id (UUID)
Unique constraints: (user_id, order_id) - one record per user per order
Foreign keys:
- user_id → users.id (CASCADE DELETE)
- order_id → orders.id (SET NULL on delete)
Indexes:
- (user_id, created_at DESC) — supports user activity queries
- (status) WHERE status != 'completed' — partial index for active records
Step 4: Document business rules
Rules enforced at the application or database level that don't appear in column definitions:
Business rules:
- amount_cents must be > 0 (enforced by CHECK constraint)
- status transitions are one-way: pending → active → completed or cancelled
- cancelled records are never deleted, only soft-deleted via status
- created_at is immutable after insert
- metadata keys must match the approved key registry (not enforced by DB - application responsibility)
Step 5: Document partitioning and retention
Partitioning: by created_at (monthly)
Retention: 7 years (regulatory requirement - SOX compliance)
Archival policy: records older than 1 year moved to cold storage tier
PII fields: [list any fields containing personal data and their handling policy]
Step 6: Document schema evolution policy
This section prevents breaking changes:
Evolution rules:
- Adding nullable columns: backward-compatible, no coordination required
- Adding NOT NULL columns: requires default value or migration, coordinate with consumers
- Renaming columns: never rename in place - add new column, backfill, deprecate old
- Removing columns: 90-day deprecation window, announce to consumer teams
- Type widening (INT → BIGINT): coordinate with consumers before applying
- Type narrowing: prohibited without explicit consumer sign-off
Current deprecations: [list any fields marked for removal with target date]
Anti-Patterns
1. Ambiguous types and units
Bad: amount FLOAT with no description
Good: amount_cents INTEGER NOT NULL + "Amount in USD cents. Divide by 100 for display. Never stored as float to avoid rounding errors."
2. Missing enum values
Bad: status VARCHAR with description "the status"
Good: status ENUM('pending','active','cancelled','completed') NOT NULL + each value explained
3. No evolution policy
Bad: Schema doc with no guidance on how to make changes
Good: Explicit rules for each change category (add, rename, remove, retype) with consumer notification requirements
4. PII undocumented
Bad: Fields containing email, name, or location with no annotation
Good: Each PII field flagged, data classification noted (e.g., PII-direct, PII-quasi), and handling policy cited
Quality Checklist
