.cursorrules (deprecated)

1# SQL & Database Operations — Cursor Rules
2# Comprehensive rules for database design, querying, and operations
3 
4## Project Context
5You are working on a project that uses a relational database (PostgreSQL, MySQL, or
6SQLite). The codebase interacts with the database through an ORM (Prisma, SQLAlchemy,
7Drizzle) or raw SQL. Queries must be efficient, safe from injection, and the schema
8must be well-designed for the application's access patterns.
9 
10## Tech Stack
11- PostgreSQL (preferred) / MySQL / SQLite
12- ORM: Prisma, SQLAlchemy, Drizzle, or TypeORM
13- Migrations: Prisma Migrate, Alembic, or raw SQL migrations
14- Connection pooling: PgBouncer or built-in pool
15- Query monitoring: EXPLAIN ANALYZE, pg_stat_statements
16 
17## Schema Design
18 
19### Naming Conventions
20- Tables: plural snake_case (e.g., `users`, `order_items`, `product_categories`)
21- Columns: snake_case (e.g., `created_at`, `user_id`, `is_active`)
22- Primary keys: `id` (auto-incrementing integer or UUID)
23- Foreign keys: `{referenced_table_singular}_id` (e.g., `user_id`, `order_id`)
24- Indexes: `idx_{table}_{columns}` (e.g., `idx_users_email`, `idx_orders_user_id_status`)
25- Unique constraints: `uq_{table}_{columns}` (e.g., `uq_users_email`)
26- Boolean columns: `is_` or `has_` prefix (e.g., `is_active`, `has_verified_email`)
27- Timestamps: `created_at`, `updated_at`, `deleted_at` (for soft deletes)
28- Enums: singular noun (e.g., `order_status`, `payment_method`)
29 
30### Table Design Rules
31```sql
32CREATE TABLE users (
33    id BIGSERIAL PRIMARY KEY,
34    email VARCHAR(255) NOT NULL,
35    name VARCHAR(100) NOT NULL,
36    password_hash VARCHAR(255) NOT NULL,
37    role VARCHAR(20) NOT NULL DEFAULT 'user',
38    is_active BOOLEAN NOT NULL DEFAULT true,
39    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
40    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
41 
42    CONSTRAINT uq_users_email UNIQUE (email),
43    CONSTRAINT chk_users_role CHECK (role IN ('user', 'admin', 'moderator'))
44);
45 
46CREATE TABLE orders (
47    id BIGSERIAL PRIMARY KEY,
48    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
49    status VARCHAR(20) NOT NULL DEFAULT 'pending',
50    total NUMERIC(10, 2) NOT NULL DEFAULT 0,
51    notes TEXT,
52    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
53    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
54 
55    CONSTRAINT chk_orders_status CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
56    CONSTRAINT chk_orders_total CHECK (total >= 0)
57);
58 
59CREATE INDEX idx_orders_user_id ON orders(user_id);
60CREATE INDEX idx_orders_status ON orders(status);
61CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
62```
63 
64### Design Principles
65- Every table must have a primary key
66- Use appropriate data types — don't store numbers as strings
67- Use `TIMESTAMPTZ` (not `TIMESTAMP`) for time-aware timestamps
68- Use `NUMERIC`/`DECIMAL` for money, never `FLOAT` or `DOUBLE`
69- Add `NOT NULL` constraints unless the column genuinely needs nulls
70- Use `CHECK` constraints for valid value ranges
71- Add foreign key constraints for referential integrity
72- Include `created_at` and `updated_at` on every table
73- Normalize to 3NF, denormalize selectively for read performance
74 
75## Indexing Strategy
76 
77### When to Index
78- Foreign key columns (always)
79- Columns used in `WHERE` clauses frequently
80- Columns used in `ORDER BY` clauses
81- Columns used in `JOIN` conditions
82- Columns used in `UNIQUE` constraints (automatically indexed)
83 
84### Index Types
85```sql
86-- B-tree (default, good for equality and range queries)
87CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
88 
89-- Composite index (for multi-column queries — column order matters)
90CREATE INDEX idx_orders_user_status ON orders(user_id, status);
91 
92-- Partial index (for filtered subsets — smaller and faster)
93CREATE INDEX idx_orders_pending ON orders(created_at) WHERE status = 'pending';
94 
95-- GIN index (for full-text search, JSONB, arrays)
96CREATE INDEX idx_products_tags ON products USING GIN(tags);
97 
98-- Expression index (for computed lookups)
99CREATE INDEX idx_users_email_lower ON users(LOWER(email));
100```
101 
102### Indexing Rules
103- Index foreign keys to prevent slow DELETE cascades
104- Put the most selective column first in composite indexes
105- Use partial indexes for queries that always filter the same way
106- Don't over-index — each index slows down writes
107- Monitor unused indexes with `pg_stat_user_indexes`
108 
109## Query Patterns
110 
111### Efficient Queries
112```sql
113-- Pagination: cursor-based (fast on large tables)
114SELECT * FROM orders
115WHERE user_id = $1 AND id < $2
116ORDER BY id DESC
117LIMIT 20;
118 
119-- Pagination: offset-based (simple but slow on large offsets)
120SELECT * FROM orders WHERE user_id = $1
121ORDER BY created_at DESC
122LIMIT 20 OFFSET 40;
123 
124-- Aggregation with filtering
125SELECT
126    DATE_TRUNC('month', created_at) AS month,
127    COUNT(*) AS order_count,
128    SUM(total) AS revenue
129FROM orders
130WHERE status = 'delivered' AND created_at >= NOW() - INTERVAL '12 months'
131GROUP BY DATE_TRUNC('month', created_at)
132ORDER BY month DESC;
133 
134-- Avoid SELECT * in production queries
135SELECT id, email, name, role FROM users WHERE id = $1;
136 
137-- Use EXISTS instead of COUNT for existence checks
138SELECT EXISTS(SELECT 1 FROM users WHERE email = $1) AS email_taken;
139```
140 
141### Joins
142```sql
143-- Prefer explicit JOIN syntax over WHERE clause joins
144SELECT o.id, o.total, u.name, u.email
145FROM orders o
146INNER JOIN users u ON u.id = o.user_id
147WHERE o.status = 'pending'
148ORDER BY o.created_at DESC;
149 
150-- Use LEFT JOIN only when you need rows without matches
151SELECT u.id, u.name, COUNT(o.id) AS order_count
152FROM users u
153LEFT JOIN orders o ON o.user_id = u.id
154GROUP BY u.id, u.name;
155```
156 
157## Migrations
158 
159### Rules
160- Every schema change goes through a migration — never modify production manually
161- Migrations must be reversible (include both `up` and `down`)
162- Use descriptive migration names: `add_status_to_orders`, `create_product_categories`
163- Never modify an existing migration that has been applied in production
164- Test migrations against a copy of production data when possible
165- Add indexes concurrently in production: `CREATE INDEX CONCURRENTLY`
166 
167### Safe Migration Patterns
168```sql
169-- Adding a column (safe — no lock on reads)
170ALTER TABLE users ADD COLUMN phone VARCHAR(20);
171 
172-- Adding NOT NULL column (safe approach — add nullable, backfill, then constrain)
173ALTER TABLE users ADD COLUMN bio TEXT;
174UPDATE users SET bio = '' WHERE bio IS NULL;
175ALTER TABLE users ALTER COLUMN bio SET NOT NULL;
176 
177-- Adding index without locking writes
178CREATE INDEX CONCURRENTLY idx_users_phone ON users(phone);
179```
180 
181## Security
182- ALWAYS use parameterized queries — never concatenate user input into SQL
183- Use the ORM's query builder or prepared statements
184- Grant minimum database permissions to the application role
185- Never expose database errors directly to the API response
186- Sanitize and validate all user input before querying
187- Use row-level security (RLS) in PostgreSQL for multi-tenant applications
188 
189## Performance
190 
191### Query Optimization
192- Use `EXPLAIN ANALYZE` to understand query execution plans
193- Look for sequential scans on large tables — add indexes
194- Avoid `SELECT *` — fetch only needed columns
195- Use `LIMIT` on all queries that return lists
196- Use `EXISTS` instead of `COUNT(*)` for boolean checks
197- Batch inserts with `INSERT INTO ... VALUES (...), (...), (...)`
198- Use `ON CONFLICT` (upsert) instead of select-then-insert
199 
200### Connection Management
201- Use connection pooling (PgBouncer or ORM built-in pool)
202- Set appropriate pool size (typically 2x CPU cores)
203- Close connections in error handlers
204- Use read replicas for read-heavy workloads
205 
206## Common Pitfalls
207- N+1 queries: fetching related records in a loop instead of a JOIN or IN clause
208- Missing indexes on foreign keys (slow cascading deletes)
209- Using OFFSET pagination on large tables (scans all skipped rows)
210- Storing monetary values as float (precision errors)
211- Not using transactions for multi-step operations
212- Forgetting to add indexes on columns used in WHERE/ORDER BY
213- Locking entire tables with ALTER TABLE on production (use CONCURRENTLY)
214- Not setting statement timeouts (one slow query blocks the pool)
215- Trusting ORM-generated queries without checking EXPLAIN output
216