Critical Patterns

Naming Conventions (REQUIRED)

-- ✅ ALWAYS: snake_case for tables and columns CREATE TABLE user_accounts ( user_id UUID PRIMARY KEY, first_name VARCHAR(100), created_at TIMESTAMP DEFAULT NOW() );

-- ❌ NEVER: Mixed case or camelCase CREATE TABLE UserAccounts ( userId UUID, firstName VARCHAR(100) );

Soft Deletes (RECOMMENDED)

-- ✅ ALWAYS: Use soft deletes for audit trail ALTER TABLE orders ADD COLUMN deleted_at TIMESTAMP NULL;

-- Query active records SELECT * FROM orders WHERE deleted_at IS NULL;

Audit Columns (REQUIRED)

-- ✅ ALWAYS: Include audit columns CREATE TABLE products ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name VARCHAR(255) NOT NULL, -- Audit columns created_at TIMESTAMP DEFAULT NOW(), updated_at TIMESTAMP DEFAULT NOW(), created_by UUID REFERENCES users(id), updated_by UUID REFERENCES users(id) );

Decision Tree

Need unique identifier? → Use UUID over serial Need audit trail? → Add created_at, updated_at, *_by columns Need to delete records? → Use soft delete (deleted_at) Need fast lookups? → Add appropriate indexes Need data integrity? → Use foreign keys + constraints Need row-level security? → Implement RLS policies

Code Examples

Index Strategy

-- ✅ Good: Composite index for common query patterns CREATE INDEX idx_orders_user_status ON orders(user_id, status) WHERE deleted_at IS NULL;

-- Use EXPLAIN ANALYZE to verify EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = '...' AND status = 'pending';

Row-Level Security

-- Enable RLS ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Policy: Users see only their documents CREATE POLICY user_documents ON documents FOR ALL USING (owner_id = current_user_id());

Commands

-- Check table size SELECT pg_size_pretty(pg_total_relation_size('table_name'));

-- Analyze query performance EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

-- View index usage SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;

Resources

Specialized database documentation:

• Design Patterns: design-patterns.md

• Logic & Procedures: logic-procedures.md

• Reporting Optimization: reporting-optimization.md

• Security & RBAC: security-rbac.md

• Style Guide: style-guide.md

Database Design Principles

Learn to THINK, not copy SQL patterns.

⚠️ Core Principle

• ASK user for database preferences when unclear

• Choose database/ORM based on CONTEXT

• Don't default to PostgreSQL for everything

Decision Checklist

Before designing schema:

• Asked user about database preference?

• Chosen database for THIS context?

• Considered deployment environment?

• Planned index strategy?

• Defined relationship types?

Anti-Patterns

❌ Default to PostgreSQL for simple apps (SQLite may suffice) ❌ Skip indexing ❌ Use SELECT * in production ❌ Store JSON when structured data is better ❌ Ignore N+1 queries

Specialized Extensions

For specific technologies, use these skills if available:

• Vector DB: lancedb

• Supabase: supabase-postgres-best-practices , supabase-auth

• NoSQL: nosql-expert

• Prisma: backend-dev-guidelines (includes Prisma patterns)