database

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)