Actionable rules for designing, evolving, and operating relational-database and API schemas (SQL, GraphQL, REST) with GitOps-driven workflows.
Your schema isn't just a data structure—it's the foundation that determines whether your application scales gracefully or crumbles under production load. Every table, every API endpoint, and every migration decision compounds into either technical debt or a robust system that evolves with your business.
We've all inherited that database where a single column rename requires coordinating six microservices, three mobile apps, and a prayer to the deployment gods. Or worked with APIs where adding a field breaks half your clients because someone thought nullable arrays of nullable objects were a good idea.
The real problem isn't complexity—it's designing schemas that can't evolve. When your primary keys are composite business identifiers, your migrations aren't version-controlled, and your API contracts change without deprecation windows, you're not building a system. You're building technical debt with a database attached.
These Cursor Rules transform schema design from reactive patching to proactive architecture. Instead of fighting your database design, you'll have patterns that handle real-world complexity: distributed systems that need referential integrity, APIs that must stay backward-compatible, and schemas that evolve without breaking production.
Bulletproof Schema Foundation
API Contracts That Don't Break
Operations That Actually Work
Instead of cowboy migrations that might work:
-- Before: Fragile business key dependencies
CREATE TABLE users (
email VARCHAR(255) PRIMARY KEY, -- What happens when email changes?
name VARCHAR(100)
);
-- After: Evolution-ready design
CREATE TABLE users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);
Your migrations become atomic, testable operations with clear rollback paths:
-- V2024_01_15__add_user_preferences.sql
BEGIN;
ALTER TABLE users ADD COLUMN preferences JSONB DEFAULT '{}';
CREATE INDEX CONCURRENTLY ix_users__preferences_gin ON users USING gin(preferences);
COMMIT;
Your schema boundaries stay clean as your system grows:
# Instead of monolithic schemas that become unmaintainable
type User @key(fields: "id") {
id: ID!
email: String!
orders: [Order!]! # Cross-service join - avoid this
preferences: UserPrefs! # Local to this service - good
}
# Separate concerns properly
type Order @key(fields: "id") {
id: ID!
userId: ID! @external
user: User @provides(fields: "id")
items: [OrderItem!]!
}
Your REST endpoints evolve without breaking existing clients:
# openapi/v1.yaml - Original contract
paths:
/v1/users/{userId}:
get:
responses:
'200':
content:
application/json:
schema:
properties:
name: { type: string } # Original field
# Later: Add new field while maintaining compatibility
fullName: { type: string } # New field
name:
type: string
deprecated: true
description: "Use fullName instead. Will be removed in v2."
Your clients get deprecation headers that actually help them migrate:
Deprecation: true
Sunset: "2024-06-01T00:00:00Z"
Link: </docs/migration-guide>; rel="deprecation"
/schema
├── migrations/
│ ├── V2024_01_01__initial_schema.sql
│ └── V2024_01_15__add_user_preferences.sql
├── graphql/
│ ├── user-service.graphql
│ └── order-service.graphql
├── openapi/
│ └── v1.yaml
└── testing/
├── fixtures/
└── contracts/
# .github/workflows/schema-migration.yml
- name: Validate Migration
run: |
# Dry run on staging clone
flyway migrate -dryRunOutput=migration.sql
- name: Schema Drift Check
run: |
pg_dump --schema-only staging > current.sql
diff expected-schema.sql current.sql
// Catch breaking changes before they hit production
describe('API Contract Tests', () => {
it('maintains backward compatibility', async () => {
const oldSchema = await loadSchema('v1.0.0');
const newSchema = await loadSchema('current');
const changes = compareSchemas(oldSchema, newSchema);
expect(changes.breaking).toHaveLength(0);
});
});
Time Savings You'll Measure
Problems You'll Stop Having
Scale You'll Handle
Your schema becomes an enabler of feature development instead of its biggest bottleneck. When your data layer is designed for evolution, your entire engineering organization moves faster.
The difference between good and great backend teams isn't the complexity they can handle—it's building systems simple enough that complexity doesn't matter. Start with schemas that make the hard problems tractable, and everything else follows.
You are an expert in PostgreSQL ≥13, MySQL 8, SQLite 3, Snowflake, GraphQL (Apollo Federation 2), REST (OpenAPI 3.1), Liquibase/Flyway migrations, dbt, GitOps (Argo CD), Docker, and Kubernetes.
Key Principles
- Prefer surrogate primary keys (BIGINT identity) to keep business data change-agnostic.
- Target 3NF for integrity; denormalize only when quantified performance gains outweigh consistency costs.
- Enforce referential integrity with FOREIGN KEYs unless the latency of cross-region patterns justifies application-level validation.
- Treat every schema change as code: place it under version control, review via PR, and release through automated migrations.
- Use consistent snake_case identifiers and ISO-8601 timestamps with time zones.
- Design for evolution: never drop or rename a column/field without a deprecation window and backward-compatibility layer.
SQL (PostgreSQL/MySQL)
- Use GENERATED ALWAYS AS IDENTITY (PostgreSQL) or AUTO_INCREMENT (MySQL) for surrogate PKs; avoid composite natural keys.
- Define NOT NULL + CHECK constraints early (e.g., price > 0). Example:
```sql
CREATE TABLE order_item (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(id),
sku TEXT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(12,2) NOT NULL CHECK (unit_price > 0),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
```
- Index strategy:
• Create one covering index per critical query path.
• Prefix indexes with ix_⟨table⟩__⟨col_list⟩ (e.g., ix_order_item__order_id_sku).
• Re-evaluate indexes quarterly; remove unused ones discovered via pg_stat_user_indexes.
- Names: tables plural nouns (order_items), columns snake_case, FKs end with _id.
- Never disable FK checks for bulk loads—use COPY in a transaction instead.
- Favor VIEWs for API-facing read models; views must select explicit columns (no `*`).
GraphQL SDL
- Types PascalCase, fields camelCase, enums UPPER_SNAKE_CASE.
- Federation: include `@key(fields:"id")` on root entities; keep entity boundary small.
- Pagination: Relay-style cursor connection for collections >50 rows.
- Deprecation: annotate `@deprecated(reason:"use fullName instead")`, keep for ≥2 minor versions.
- Avoid nullable lists of nullable objects—prefer `[Item!]!` for predictable clients.
OpenAPI (REST)
- URI versioning: `/v{n}/resources` (major only). Minor/patch via headers.
- Paths use plural nouns; singular for specific id: `/v1/books/{bookId}`.
- Use HTTP semantics strictly: 201 on create, 204 on delete, 409 on conflict.
- Include JSON Schema components with `$ref` reuse; keep schemas in /components/schemas.
Error Handling & Validation
- Guard conditions first; exit early.
```sql
CREATE OR REPLACE FUNCTION add_stock(p_sku TEXT, p_qty INT) RETURNS VOID AS $$
BEGIN
IF p_qty <= 0 THEN
RAISE EXCEPTION 'Quantity must be > 0';
END IF;
UPDATE inventory SET qty = qty + p_qty WHERE sku = p_sku;
END;
$$ LANGUAGE plpgsql;
```
- All DDL in idempotent, transactional migration files (Liquibase XML/YAML or Flyway SQL).
- Rollback plan required: every forward migration must pair with a verified down script.
- CI pipeline: run `pg_dump --schema-only` after migrations and diff against repo to catch drift.
Framework-Specific Rules
Apollo Server
- Gateway uses distributed tracing; propagate `traceparent` header.
- Set `schemaReporting: true` and push to Apollo Studio on every merge.
- Break federated services by domain (Inventory, Catalog); avoid cross-service joins >2 hops.
Liquibase/Flyway
- One migration per Jira ticket.
- Naming: `V⟨timestamp⟩__⟨brief_description⟩.sql`.
- Never modify an applied migration; create a follow-up fix.
GitOps (Argo CD)
- Store rendered k8s manifests and migration images in the same commit.
- Block promotion if migration fails in staging; use `argocd app wait` gates.
Additional Sections
Testing
- Seed deterministic fixtures via `pg_restore --data-only` for unit tests.
- Use db-specific container per test suite (`testcontainers` or Docker Compose) to avoid shared state.
- Contract tests for GraphQL and REST endpoints generated from JSON Schema or SDL introspection.
Performance
- Partition fact tables by time (e.g., RANGE partition on created_at by month) for datasets >30M rows.
- Use read replicas for SELECT-heavy workloads; configure application routing.
- Periodically `VACUUM (ANALYZE)`; schedule auto-analyze after ≥20% row changes.
Security
- Use row-level security (RLS) where multi-tenant.
- Never expose internal identifiers to public APIs—use ULID/UUID v7 in external contract while retaining bigint PKs internally.
- Encrypt PII at rest with pgcrypto or column-level encryption (MySQL: AES-256-GCM); rotate keys annually.
Documentation
- Generate ERDs nightly with dbSchema CLI and publish to Confluence.
- Publish GraphQL schema diff reports (`graphql-inspector`) on every PR.
Versioning & Deprecation Lifecycle
1. Introduce new column/field nullable ↦ backfill ↦ switch writes ↦ make NOT NULL ↦ deprecate old.
2. Keep old API contract active ≥1 major release; emit deprecation warnings in response headers (`Sunset`, `Deprecation`).
File/Directory Conventions
```
/schema
├─ migrations
│ ├─ V2023_12_01__create_orders.sql
│ └─ V2023_12_15__add_total_to_orders.sql
├─ erd/
│ └─ orders.png
├─ openapi
│ └─ v1.yaml
└─ graphql
├─ inventory.graphql
└─ catalog.graphql
```