db-foundations

name	db-foundations
description	Relational database conventions for schema design, query writing, indexing, optimization, and administration. Load when working on SQL files, migrations, or database-related code.
argument-hint	Working on SQL files, migrations, schema design, queries, or database-related application code

Database Foundations

Relational database conventions for schema design, query optimization, indexing, migrations, and lightweight administration. These patterns apply across RDBMS engines — the skill adapts syntax to the configured engine.

Baseline: Follow standard SQL as the foundation. Everything below extends or specializes where noted.

Adaptive Behavior

Read stack.md for the db_engine field to determine which RDBMS dialect to use:

• postgres — PostgreSQL syntax. Use PostgreSQL-specific features: UUID, JSONB, array types, partial indexes, CONCURRENTLY, CTEs with MATERIALIZED/NOT MATERIALIZED.
• mysql — MySQL syntax. Respect MySQL constraints: no partial indexes, different full-text approach (FULLTEXT index), CHAR(36) for UUIDs, use InnoDB engine.
• sqlserver — T-SQL syntax. Use SQL Server conventions: UNIQUEIDENTIFIER, IDENTITY(1,1), CLUSTERED/NONCLUSTERED, WITH (ONLINE=ON) for index operations.
• sqlite — SQLite syntax. Respect SQLite constraints: limited ALTER TABLE (no drop/rename column before 3.35), no stored procedures, TEXT for UUIDs, affinity-based typing.
• oracle — Oracle PL/SQL syntax. Use Oracle conventions: sequences for IDs, RAW(16) for UUIDs, tablespace awareness, ONLINE index rebuilds.

If db_engine is not set in stack.md, default to postgres.

All SQL examples in this skill use the syntax of the configured engine. When generating SQL, always target the configured engine — never produce generic SQL that requires manual adaptation.

Naming Conventions

All database objects use snake_case. This is prescriptive, not suggestive.

Tables

• Plural nouns: users, order_items, payment_methods
• Join tables: both entity names in alphabetical order: project_users, role_permissions
• Never prefix with tbl_ or suffix with _table

Columns

• Singular, descriptive: first_name, email, created_at
• Primary key: always id
• Foreign keys: <referenced_table_singular>_id — e.g., user_id, order_id
• Booleans: prefix with is_, has_, or can_ — e.g., is_active, has_verified_email
• Timestamps: suffix with _at — e.g., created_at, updated_at, deleted_at
• Never use reserved words as column names (user, order, group, table). If unavoidable, rename: user_name, sort_order, group_name.

Indexes

• Single column: idx_<table>_<column> — e.g., idx_users_email
• Composite: idx_<table>_<col1>_<col2> — e.g., idx_orders_user_id_created_at
• Unique: uq_<table>_<column> — e.g., uq_users_email

Constraints

• Primary key: pk_<table> — e.g., pk_users
• Foreign key: fk_<table>_<referenced_table> — e.g., fk_orders_users
• Check: ck_<table>_<description> — e.g., ck_orders_positive_amount
• Unique: uq_<table>_<columns> — e.g., uq_users_email

Schema Design

Domain-First Workflow

Start from domain models in application code, then derive the relational schema:

1. Identify entities — each domain model with an identity becomes a table
2. Map properties to columns — choose engine-appropriate types (see Data Types below)
3. Normalize to 3NF — eliminate redundancy, ensure every non-key column depends on the whole key and nothing but the key
4. Define relationships — foreign keys for associations, join tables for many-to-many
5. Add constraints — NOT NULL on required fields, CHECK for business rules, UNIQUE for natural keys
6. Plan indexes — based on query patterns, not speculatively

Normalization

Target: 3NF by default. Normalize to Third Normal Form for all OLTP schemas. Denormalize only with measured proof of a bottleneck — add a comment explaining the trade-off when you do.

BCNF: Normalize to BCNF when a table has overlapping candidate keys. Don't go looking for it in every table.

Data Types

Choose the most specific type that fits the domain. Avoid TEXT or VARCHAR(MAX) when a bounded type works.

UUIDs (default primary key strategy):

Engine	Type	Default
PostgreSQL	UUID	DEFAULT gen_random_uuid()
MySQL	CHAR(36)	Application-generated
SQL Server	UNIQUEIDENTIFIER	DEFAULT NEWSEQUENTIALID()
SQLite	TEXT	Application-generated
Oracle	RAW(16)	SYS_GUID()

Common type mappings:

Domain concept	PostgreSQL	MySQL	SQL Server	SQLite	Oracle
String (bounded)	VARCHAR(n)	VARCHAR(n)	NVARCHAR(n)	TEXT	VARCHAR2(n)
String (unbounded)	TEXT	TEXT	NVARCHAR(MAX)	TEXT	CLOB
Integer	INTEGER	INT	INT	INTEGER	NUMBER(10)
Big integer	BIGINT	BIGINT	BIGINT	INTEGER	NUMBER(19)
Decimal/money	NUMERIC(p,s)	DECIMAL(p,s)	DECIMAL(p,s)	REAL	NUMBER(p,s)
Boolean	BOOLEAN	TINYINT(1)	BIT	INTEGER	NUMBER(1)
Timestamp (UTC)	TIMESTAMPTZ	DATETIME	DATETIMEOFFSET	TEXT (ISO 8601)	TIMESTAMP WITH TIME ZONE
JSON (dynamic)	JSONB	JSON	NVARCHAR(MAX)	TEXT	CLOB

Rules:

• Always store timestamps in UTC. Convert to local time at the presentation layer, never in the database.
• Use NUMERIC/DECIMAL for money. Never floating point.
• Prefer TIMESTAMPTZ (or equivalent) over TIMESTAMP. Time zone awareness prevents bugs.
• VARCHAR(n) over TEXT when a reasonable bound exists — it documents intent and catches bad data.

Constraints

Every table MUST have:

• Primary key — UUID id column by default
• NOT NULL on every column that the domain requires. Default to NOT NULL; add NULL only when the domain genuinely allows absence.
• Foreign keys for every relationship. Never rely on application code alone to enforce referential integrity.

Add where appropriate:

• UNIQUE constraints on natural keys (email, slug, external_id)
• CHECK constraints for business rules (CHECK (amount > 0), CHECK (status IN ('active', 'inactive', 'suspended')))
• DEFAULT values for columns with sensible defaults (created_at DEFAULT NOW(), is_active DEFAULT TRUE)

Canonical Schema Example

Domain: a User entity with an email, name, and role association.

-- PostgreSQL syntax (adapts to db_engine)
CREATE TABLE roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_roles_name UNIQUE (name)
);

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    role_id UUID NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_users_email UNIQUE (email),
    CONSTRAINT fk_users_roles FOREIGN KEY (role_id) REFERENCES roles (id)
);

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_role_id ON users (role_id);

What this demonstrates: snake_case everywhere, UUID primary keys, explicit constraint names, NOT NULL by default, foreign key with named constraint, indexes on lookup columns.

Indexing Strategy

When to Index

Index columns that appear in:

• WHERE clauses — filter conditions
• JOIN conditions — foreign keys (always index foreign keys)
• ORDER BY — sort columns, especially with LIMIT
• Frequently queried unique lookups — email, slug, external ID

Do NOT index:

• Low-cardinality columns alone (boolean is_active with 50/50 split — rarely useful as a standalone index)
• Columns that are rarely queried in WHERE clauses
• Every column "just in case" — each index slows writes and consumes storage

Index Types

B-tree (default): Covers =, <, >, <=, >=, BETWEEN, LIKE 'prefix%'. This is the right choice 90% of the time.

Engine-specific indexes:

Type	PostgreSQL	MySQL	SQL Server	Use case
B-tree	Default	Default	NONCLUSTERED (default)	Equality, range, sorting
Hash	USING HASH	Adaptive Hash (automatic)	N/A	Equality only (rare — B-tree is usually better)
Full-text	GIN with tsvector	FULLTEXT	Full-text index	Text search
GIN	USING GIN	N/A	N/A	JSONB, arrays, full-text
GiST	USING GiST	N/A	Spatial index	Geometric, range types

Composite Index Rules

Column order matters. The index is useful for queries that filter on a left prefix of the indexed columns.

-- Index: idx_orders_user_id_created_at ON orders (user_id, created_at)
-- ✅ Useful: WHERE user_id = ? AND created_at > ?
-- ✅ Useful: WHERE user_id = ?
-- ❌ NOT useful: WHERE created_at > ?  (skips leading column)

Order by selectivity: put the most selective (highest cardinality) column first, unless the query pattern dictates otherwise.

Covering Indexes

Include additional columns to satisfy a query entirely from the index, avoiding table lookups:

-- PostgreSQL / SQL Server
CREATE INDEX idx_users_email_covering ON users (email) INCLUDE (first_name, last_name);
-- Query: SELECT first_name, last_name FROM users WHERE email = ?
-- Satisfied entirely from the index — no table access needed

MySQL achieves this naturally when the index covers all selected columns (InnoDB secondary indexes include the primary key).

Partial Indexes (PostgreSQL, SQLite)

Index only rows that match a condition — smaller index, faster writes:

-- PostgreSQL
CREATE INDEX idx_users_active_email ON users (email) WHERE is_active = TRUE;
-- Only indexes active users — smaller, faster for the common query pattern

MySQL and SQL Server do not support partial indexes. Use filtered indexes in SQL Server (WHERE clause on CREATE INDEX).

Query Optimization

EXPLAIN First

Run EXPLAIN ANALYZE (or engine equivalent below) on any query touching >10k rows or joining 3+ tables. Never tune without a plan.

Engine	Command	Key output
PostgreSQL	EXPLAIN ANALYZE	Actual time, rows, loops, Seq Scan vs Index Scan
MySQL	EXPLAIN or EXPLAIN ANALYZE (8.0.18+)	type column (ALL=full scan, ref=index, eq_ref=unique index), rows estimate
SQL Server	SET STATISTICS IO ON; SET STATISTICS TIME ON; or execution plan in SSMS	Logical reads, scan count, actual vs estimated rows
SQLite	EXPLAIN QUERY PLAN	SCAN TABLE vs SEARCH TABLE USING INDEX
Oracle	EXPLAIN PLAN FOR + SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)	Full/Index scan, cost, cardinality

Red flags in execution plans:

• Full table scan on a large table with a filter condition — missing index
• Estimated rows far from actual rows — stale statistics (ANALYZE the table)
• Nested loop on large sets — consider hash join (may need more work_mem in PostgreSQL)
• Sort operation on large result — add an index that provides the ordering

Join Strategies

• Always use explicit JOIN ... ON — never comma-separated implicit joins.
• EXISTS over IN for subquery filters on large datasets — EXISTS short-circuits.
• Never use LEFT JOIN when INNER JOIN is correct — it masks missing data.

-- ✅ Explicit join
SELECT u.email, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE o.created_at > '2026-01-01';

-- ❌ Never: implicit join
SELECT u.email, o.total
FROM users u, orders o
WHERE o.user_id = u.id AND o.created_at > '2026-01-01';

CTEs vs Subqueries

• CTEs for readability when a subquery is referenced multiple times or the query has complex multi-step logic
• Subqueries for simple one-off filters — no need to extract into a CTE
• PostgreSQL: CTEs are optimization fences before v12 (materialized by default). In v12+, use NOT MATERIALIZED to let the planner inline. After v12, this is generally automatic.
• Avoid correlated subqueries in SELECT lists — they execute per row. Rewrite as JOINs.

Pagination

Keyset pagination (cursor-based) over OFFSET for large datasets:

-- ❌ OFFSET pagination: slow on page 1000 (scans and discards 999 pages)
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 19980;

-- ✅ Keyset pagination: constant performance regardless of page depth
SELECT * FROM orders
WHERE created_at < '2026-03-15T10:30:00Z'
ORDER BY created_at DESC
LIMIT 20;

OFFSET is fine for small datasets or when the user rarely goes beyond page 5. For anything else, use keyset pagination.

Batch Operations

• Bulk INSERT: use multi-row VALUES syntax, not one INSERT per row
• Bulk UPDATE: use UPDATE ... FROM (PostgreSQL), UPDATE ... JOIN (MySQL), or merge patterns
• Bulk DELETE: delete in batches with a LIMIT to avoid long-running transactions and lock escalation

-- ✅ Multi-row insert
INSERT INTO users (id, email, first_name)
VALUES
    (gen_random_uuid(), '[e-mail]', 'Alice'),
    (gen_random_uuid(), '[e-mail]', 'Bob'),
    (gen_random_uuid(), '[e-mail]', 'Carol');

-- ✅ Batch delete (prevent lock escalation)
DELETE FROM audit_logs
WHERE created_at < '2025-01-01'
LIMIT 1000;  -- repeat until 0 rows affected

N+1 Prevention

The N+1 problem: fetching a list of N entities, then issuing 1 query per entity for related data.

At the SQL level:

• JOIN the related data in the original query
• Use IN clause for batch loading: WHERE user_id IN (?, ?, ?, ...)
• Never loop queries — if you're generating SQL in a loop, restructure as a single query with a join or IN clause

Migration Patterns

Forward-Only Migrations

Migrations move the schema forward. Every migration file has a unique sequential identifier and a descriptive name.

migrations/
  001_create_users.sql
  002_create_orders.sql
  003_add_users_phone_number.sql

Rules:

• Never edit a migration that has been applied to any environment. Create a new migration instead.
• One concern per migration — don't mix table creation with data backfill.
• Idempotent when possible — use IF NOT EXISTS / IF EXISTS guards.

Safe vs Dangerous Changes

Safe (additive):

• ADD COLUMN with a default or as nullable
• CREATE TABLE
• CREATE INDEX CONCURRENTLY (PostgreSQL) or WITH (ONLINE=ON) (SQL Server)
• ADD CONSTRAINT (check constraints, foreign keys — may need validation)

Dangerous (require care):

• DROP COLUMN — ensure no code references it. Deploy code changes first, then migrate.
• RENAME COLUMN — breaks existing code. Prefer: add new column, migrate data, update code, drop old column.
• ALTER COLUMN TYPE — may require table rewrite. Check engine-specific behavior.
• DROP TABLE — irreversible. Require explicit confirmation in migration tooling.
• ADD NOT NULL to existing column — fails if NULLs exist. Backfill first, then add constraint.

Zero-Downtime Pattern

For changes that can't be applied atomically:

1. Deploy new code that handles both old and new schema
2. Run the migration (add column, create table, etc.)
3. Backfill data if needed (as a separate migration or script)
4. Deploy code that uses the new schema only
5. Clean up (drop old column, remove compatibility code)

This multi-step approach prevents downtime and avoids breaking running application instances during deployment.

ORM Interaction Principles

When to Use Raw SQL

Use raw SQL (or the ORM's raw query escape hatch) when:

• The query involves complex joins, window functions, CTEs, or recursive queries that the ORM can't express cleanly
• Performance is critical and you need control over the exact query plan
• You're writing database-specific queries (PostgreSQL JSONB operators, full-text search)
• The ORM generates obviously inefficient SQL (multiple queries where one join would suffice)

Use the ORM for standard CRUD, simple filters, and queries that map naturally to the ORM's query builder. Don't fight the ORM when it works — save raw SQL for when it doesn't.

N+1 Prevention

• Eager-load relationships you know you'll need — JOIN or preload, not lazy access in a loop
• Review generated SQL — enable query logging in development and watch for repeated patterns
• Batch-load related entities: collect IDs from the list, fetch in one query
• Never serialize ORM objects directly — JSON serialization triggers lazy loads for every relationship field
• Be explicit about what you load — if a handler needs users with their roles, load both upfront. Don't rely on lazy loading in production code paths.

Migration Tooling

• The ORM's migration tool generates the migration based on model changes — review every generated migration before applying
• Never auto-apply migrations in production — always review, approve, then apply
• The migration file is the source of truth, not the ORM model — if they diverge, the migration wins
• Check for destructive operations in auto-generated migrations — some ORMs generate DROP COLUMN or DROP TABLE when a model field is removed

Lightweight DBA

EXPLAIN Analysis Habit

Apply the EXPLAIN thresholds from the Query Optimization section. Also run EXPLAIN on any query reported as slow or running in a loop. Add indexes based on evidence from the plan, not intuition.

Index Maintenance

• Rebuild bloated indexes periodically (PostgreSQL: REINDEX CONCURRENTLY, SQL Server: ALTER INDEX ... REBUILD WITH (ONLINE=ON))
• Remove unused indexes — they slow writes and consume storage. Query pg_stat_user_indexes (PostgreSQL) or sys.dm_db_index_usage_stats (SQL Server) for index usage.
• Update statistics after large data loads (ANALYZE in PostgreSQL, UPDATE STATISTICS in SQL Server, automatic in MySQL InnoDB)

Connection Pooling

• Always use a connection pool — never open/close connections per query
• Size the pool to match your workload — too small creates contention, too large wastes memory and overwhelms the database
• Rule of thumb: pool_size = (core_count * 2) + disk_count (from PostgreSQL wiki). Adjust based on measurement.
• Set idle timeouts — connections left idle too long consume server resources

Security

• Parameterized queries always. Never concatenate user input into SQL strings. This is non-negotiable.

  -- ✅ Parameterized
  SELECT * FROM users WHERE email = $1;
  
  -- ❌ NEVER: string concatenation
  SELECT * FROM users WHERE email = '" + userInput + "';
  

• Least-privilege roles — the application connects with a role that has only the permissions it needs. Never use the superuser/admin role for application queries.
• No SELECT * in application code. Always list the columns you need. SELECT * couples your code to the table structure and fetches data you don't use.
• Audit sensitive operations — log access to PII columns, permission changes, and data deletions.

Common Anti-Patterns

These are patterns that agents commonly generate. Flag and fix them.

Anti-Pattern	Problem	Fix
SELECT *	Fetches unnecessary data, breaks when schema changes	List specific columns
Missing foreign keys	No referential integrity — orphaned rows accumulate	Always define FK constraints
Unbounded queries	SELECT ... FROM large_table with no LIMIT or WHERE	Always add LIMIT or pagination
Storing computed values	total_price stored alongside quantity * unit_price	Compute at query time or use generated columns
EAV (Entity-Attribute-Value)	Key-value tables instead of proper columns	Use proper columns. If truly dynamic, use JSONB (PostgreSQL) or JSON columns
Implicit type coercion	Comparing VARCHAR to INT silently — engine casts, index can't be used	Match types in comparisons; explicit CAST when needed
Boolean as string	status VARCHAR(5) holding 'true'/'false'	Use engine-native boolean type
Soft delete without index	deleted_at column but no partial index on active records	Add partial index: WHERE deleted_at IS NULL
Missing updated_at	No way to track when a row last changed	Add updated_at with trigger or application-level update
Over-indexing	Index on every column "for performance"	Index based on actual query patterns, remove unused indexes
Nullable foreign keys for required relationships	user_id UUID without NOT NULL	Add NOT NULL to required foreign keys
ORDER BY RAND() / ORDER BY NEWID()	Full table scan and sort for random selection	Use TABLESAMPLE or application-level random selection