data-schema-design

name	data-schema-design
description	PostgreSQL schema design including migration strategy and cross-ORM migration tooling (Prisma, Drizzle, Kysely, Django, golang-migrate), retention policy design, index strategy, keyset pagination, queue-claim patterns, disaster recovery DDL, SECURITY DEFINER safety, RLS policy patterns and RLS performance, naming conventions, and cross-engine notes for MySQL/MariaDB. Use when creating or modifying database tables, writing SQL migrations, planning zero-downtime rollouts, designing RLS policies, planning data retention, adding indexes, reviewing schema security, or troubleshooting query performance. Also use when the user mentions "schema", "DDL", "migration", "RLS", "retention", "index", "table", "enum", "pagination", "MySQL", "MariaDB", or "database design".
owner	Data Engineer (archetype)
version	1.1.0
inspired_by	[{"source":"msitarzewski/agency-agents/engineering/engineering-database-optimizer.md@783f6a72bfd7f3135700ac273c619d92821b419a","license":"MIT","relationship":"pattern_reference","authored_by":"ceo-orchestration framework","authored_at":"2026-05-06T00:00:00.000Z"},{"source":"msitarzewski/agency-agents/engineering/engineering-data-engineer.md@783f6a72bfd7f3135700ac273c619d92821b419a","license":"MIT","relationship":"pattern_reference","authored_by":"ceo-orchestration framework","authored_at":"2026-05-06T00:00:00.000Z"},{"source":"affaan-m/ecc/skills/database-migrations/@81af40761939056ab3dc54732fd4f562a27309d0","license":"MIT","relationship":"pattern_reference","authored_by":"ceo-orchestration framework","authored_at":"2026-07-07T00:00:00.000Z"},{"source":"affaan-m/ecc/skills/postgres-patterns/@81af40761939056ab3dc54732fd4f562a27309d0","license":"MIT","relationship":"pattern_reference","authored_by":"ceo-orchestration framework","authored_at":"2026-07-07T00:00:00.000Z"},{"source":"affaan-m/ecc/skills/mysql-patterns/@81af40761939056ab3dc54732fd4f562a27309d0","license":"MIT","relationship":"pattern_reference","authored_by":"ceo-orchestration framework","authored_at":"2026-07-07T00:00:00.000Z"}]
domain	core
priority	4
risk_class	medium
stack	["postgres","sql"]
context_budget_tokens	1000
inactive_but_retained	false
repo_profile_binding	{"frontend":{"active":true,"priority":6},"engine":{"active":true,"priority":3},"fintech":{"active":true,"priority":3},"trading-readonly":{"active":true,"priority":6},"generic":{"active":true,"priority":4}}
activation_triggers	[{"event":"file-edit","glob":"**/migrations/**"},{"event":"help-me-invoked","regex":"(?i)schema|migration|ddl|mysql|mariadb|pagination"}]
source	affaan-m/ecc@81af4076 skills/database-migrations/ + skills/postgres-patterns/ + skills/mysql-patterns/
license	MIT

Data Schema Design

This skill assumes PostgreSQL (including Supabase's PostgreSQL). For other databases, adapt the specifics but the principles transfer.

Fail-Fast Rule

If a migration could cause data loss, stop and require explicit confirmation. Never drop columns or tables without a backup plan. Never alter enum types in a way that invalidates existing rows. Never run destructive DDL in production without testing on a branch first.

Cardinal Rule

Every table in production must have a DDL file in the sql/ directory. If a table exists in the database but has no DDL, it is undocumented technical debt. Schema-as-code is the only way to recover from disaster. Audit periodically to catch drift between production and the sql/ directory.

Audit Baseline: Current State

Existing DDL Files (sql/ directory)

File	Tables Covered
core_tables.sql	Foundational domain tables for the project
billing_tables.sql	Billing / subscription / webhook audit tables
external_data_tables.sql	Tables for ingested third-party data
retention_policies.sql	run_retention_cleanup() function
retention_summary_tables.sql	Daily summary / rollup tables
security_fixes.sql	RLS/function fixes (no new tables)
rls_fixes.sql	RLS policy fixes (no new tables)

Tables WITHOUT DDL (missing — identified in audit)

Run the audit query (see Disaster Recovery) to list tables in production that have no corresponding sql/ file. Every such table is a recovery risk and should have a DDL file created.

Priority: Create DDL files for every table discovered by the audit. Without them, a database disaster (unlikely but possible) means rebuilding from memory.

Numeric Precision Convention

Rule

For financial or high-precision values, prefer TEXT (or NUMERIC with a well-defined scale) and handle arithmetic in your application layer using a decimal arithmetic library.

// Example client configuration
const client = createClient(url, key, {
  db: { schema: 'public' },
  // Return numerics as strings to prevent JavaScript floating-point corruption
});

Why

JavaScript Number has 64-bit IEEE 754 precision. A value like 12345678901234567890.123456789 loses precision when parsed as a JS number. Many JSON parsers coerce numeric columns to JS numbers, corrupting them before your code even sees them.

Supabase-specific: if you're on Supabase, some PostgREST/Supabase configs return numerics as strings — handle this in your DTO layer. Route all arithmetic through your decimal arithmetic library rather than native JS math.

Column Type Guidelines

Data Type	PostgreSQL Type	Example
Prices, rates, amounts	TEXT	price TEXT, amount TEXT
Percentages	TEXT	rate_pct TEXT, avg_rate_pct TEXT
Counts (small, exact)	INTEGER	item_count INTEGER, attempts INTEGER
Counts (large)	BIGINT	sequence_id BIGINT
Timestamps (epoch ms)	BIGINT	timestamp BIGINT, event_time BIGINT
Timestamps (human)	TIMESTAMPTZ	created_at TIMESTAMPTZ
Booleans	BOOLEAN	is_active BOOLEAN
Enums/status	TEXT	status TEXT, tier TEXT
Structured data	JSONB	payload JSONB, metadata JSONB
Arrays	TEXT[]	tags TEXT[]

Anti-Pattern

-- RISKY: NUMERIC/DECIMAL columns may return with trailing zeros or varying
-- string representation depending on client config.
CREATE TABLE prices (
  price NUMERIC(20, 8),  -- e.g. "123.45000000" (trailing zeros)
  amount DECIMAL(30, 8), -- inconsistent string representation
);

-- SAFER: TEXT for all high-precision values
CREATE TABLE prices (
  price TEXT,   -- "123.45" exactly as written
  amount TEXT,  -- "1000000.5" exactly as written
);

Exception: NUMERIC is acceptable for statistical values (e.g. z-scores) used for comparison, not for values where exact representation matters.

RLS Policy Patterns

Supabase-specific: if you're on Supabase, RLS is enforced on the PostgREST path and auth.uid() is available. On plain Postgres, you implement these patterns at the application layer or via your own auth context — the SQL shape still applies.

Pattern 1: Public Read-Only Data

Used for: publicly-visible tables where anyone can read but only the backend writes.

ALTER TABLE public.<table> ENABLE ROW LEVEL SECURITY;

-- Anyone (anon, authenticated, service_role) can read
CREATE POLICY "Anon read <table>"
  ON public.<table>
  FOR SELECT
  USING (true);

-- Only service_role can write (bypasses RLS anyway, but explicit is better)
CREATE POLICY "Service write <table>"
  ON public.<table>
  FOR ALL
  TO service_role
  USING (true)
  WITH CHECK (true);

Why not skip the service_role policy? service_role bypasses RLS, so the write policy is technically redundant. However, it documents the intent: "only the backend writes here." If RLS behavior changes or someone uses a non-service role, the policy protects the table.

Pattern 2: User-Scoped Data (user sees only their own)

Used for: user profiles, user-owned resources, per-user audit trails.

ALTER TABLE public.<table> ENABLE ROW LEVEL SECURITY;

-- Users can read only their own rows
CREATE POLICY "Users read own <table>"
  ON public.<table>
  FOR SELECT
  USING (auth.uid() = user_id);

-- Users can insert their own rows
CREATE POLICY "Users insert own <table>"
  ON public.<table>
  FOR INSERT
  WITH CHECK (auth.uid() = user_id);

-- Service role manages all (backend webhook handlers, jobs)
CREATE POLICY "Service manage <table>"
  ON public.<table>
  FOR ALL
  TO service_role
  USING (true)
  WITH CHECK (true);

Pattern 3: Tier-Gated Data (free users get limited access)

Used for: historical analytics, deep data access, premium features.

-- Users with paid tiers get full history
-- Free users get last 24 hours only
CREATE POLICY "Tier-gated read analytics"
  ON public.events
  FOR SELECT
  USING (
    public.get_user_tier(auth.uid()) IN ('pro', 'enterprise')
    OR event_time > (EXTRACT(EPOCH FROM NOW() - INTERVAL '24 hours') * 1000)::BIGINT
  );

Pattern 4: Service-Only Sensitive Data

Used for: webhook event logs, internal audit logs, system internals.

ALTER TABLE public.<table> ENABLE ROW LEVEL SECURITY;

-- No public read access at all
-- Only service_role can read and write
-- (service_role bypasses RLS, so no policy needed, but explicit deny is safer)
CREATE POLICY "Deny all public access"
  ON public.<table>
  FOR ALL
  USING (false);

Common RLS Mistakes (Fixed)

Table	Issue	Fix Applied
public.events	FOR ALL without TO service_role	Dropped, service_role bypasses
public.audit_log	FOR ALL without TO service_role	Dropped
public.signals	FOR ALL without TO service_role	Dropped
public.metrics	FOR ALL without TO service_role	Replaced with TO service_role
public.snapshots	FOR ALL without TO service_role	Replaced with TO service_role
public.indicators	Unscoped INSERT/UPDATE	Replaced with single FOR ALL TO service_role

Migration Strategy

Principles

1. Idempotent: Every migration uses CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS, DROP POLICY IF EXISTS before CREATE POLICY.
2. Additive only: Never drop columns in a migration. Add new columns, backfill, then deprecate old ones in a future migration.
3. No breaking changes: Old code must work with new schema, new code must work with old schema (during rolling deploy).
4. Tested on branch: Database branching (e.g. Supabase branching) allows testing migrations before applying to production.

ALTER TYPE for Enums

Prefer TEXT columns over PostgreSQL ENUM types for flexibility. If an ENUM type is ever needed:

-- SAFE: Add a value to existing enum
ALTER TYPE billing_tier ADD VALUE IF NOT EXISTS 'enterprise';

-- UNSAFE: Remove or rename enum values (requires migration)
-- 1. Create new enum type
CREATE TYPE billing_tier_v2 AS ENUM ('free', 'pro', 'enterprise');
-- 2. Alter column to use new type
ALTER TABLE users ALTER COLUMN tier TYPE billing_tier_v2 USING tier::text::billing_tier_v2;
-- 3. Drop old type
DROP TYPE billing_tier;
-- 4. Rename new type
ALTER TYPE billing_tier_v2 RENAME TO billing_tier;

Convention: Use TEXT for all status/tier/type columns. Validate in application code, not database constraints. This avoids enum migration pain.

Tier Consistency (Audit Finding)

When a project defines multiple tiers, verify all tables that reference tiers use consistent values. Check:

• users.tier
• subscriptions.tier
• api_keys.tier (if exists)
• Application code TIER_LIMITS object

A mismatch between schema values and application constants is a common source of silent bugs.

Migration File Convention

sql/
  core_tables.sql           -- foundational domain tables
  billing_tables.sql        -- user billing columns, subscriptions, webhook events
  external_data_tables.sql  -- ingested third-party data
  retention_policies.sql    -- cleanup function
  retention_summary_tables.sql -- daily summary tables
  security_fixes.sql        -- RLS/function security patches
  rls_fixes.sql             -- additional RLS fixes

Running Migrations

-- In your SQL editor (e.g. Supabase SQL Editor, psql, or CI migration tool):
-- Paste the contents of the .sql file and run.
-- All statements are idempotent — safe to re-run.

Retention Policy Design

Current Coverage

A run_retention_cleanup() function covers the project's retention-eligible tables and is scheduled via pg_cron (typically daily at 04:00 UTC).

Tables Covered by Retention

Table	Retention	Time Column	Type
public.events	90 days	fetched_at (TIMESTAMPTZ)	DELETE
public.metrics	90 days	calculated_at (TIMESTAMPTZ)	DELETE
public.quotes	90 days	fetched_at (TIMESTAMPTZ)	DELETE
public.news	90 days	fetched_at (TIMESTAMPTZ)	DELETE
public.reports	90 days	fetched_at (TIMESTAMPTZ)	DELETE
public.snapshots	90 days	snapshot_time (BIGINT ms)	DELETE

Tables MISSING Retention (need cleanup)

Table	Growth Rate	Suggested Retention	Strategy
public.trades	High	7 days raw, summarize daily	Aggregate then delete
public.opportunities	High	30 days raw, summarize daily	Aggregate then delete
public.history	Medium	30 days raw, summarize daily	Aggregate then delete
public.events	Low-medium	90 days	Direct delete
public.alerts	Low	180 days	Direct delete
public.telemetry	Medium	7 days (PII)	Direct delete
public.snapshots	High	7 days raw, summarize daily	Aggregate then delete
public.reference	Low	Keep indefinitely (small)	No cleanup needed
public.cache	Bounded (upsert)	Self-cleaning via upsert	No cleanup needed
public.logs_1m	High	90 days	Direct delete
public.webhook_events	Low	365 days (audit)	Direct delete
public.order_executions	Medium	5 years (regulatory)	Archive to cold storage
public.audit_log	Medium	5 years (regulatory)	Archive to cold storage

Retention with Summarization Pattern

For high-volume tables, summarize before deleting:

-- Step 1: Summarize opportunities into daily_summary
INSERT INTO public.daily_summary (day, type, key, total_count,
  positive_count, avg_value, max_value, min_value, avg_gross_value)
SELECT
  DATE(TO_TIMESTAMP(timestamp / 1000)) AS day,
  type,
  key,
  COUNT(*) AS total_count,
  COUNT(*) FILTER (WHERE net_value::NUMERIC > 0) AS positive_count,
  AVG(net_value::NUMERIC)::TEXT AS avg_value,
  MAX(net_value::NUMERIC)::TEXT AS max_value,
  MIN(net_value::NUMERIC)::TEXT AS min_value,
  AVG(gross_value::NUMERIC)::TEXT AS avg_gross_value
FROM public.opportunities
WHERE timestamp < (EXTRACT(EPOCH FROM NOW() - INTERVAL '30 days') * 1000)::BIGINT
GROUP BY day, type, key
ON CONFLICT (day, type, key) DO NOTHING;

-- Step 2: Delete raw data older than 30 days
DELETE FROM public.opportunities
WHERE timestamp < (EXTRACT(EPOCH FROM NOW() - INTERVAL '30 days') * 1000)::BIGINT;

Batch Delete with Backpressure

For large tables, delete in batches to avoid locking:

-- Delete in batches of 10,000 to avoid long locks
CREATE OR REPLACE FUNCTION public.batch_delete_old_rows(
  retention_days INTEGER DEFAULT 7,
  batch_size INTEGER DEFAULT 10000
)
RETURNS BIGINT AS $$
DECLARE
  _deleted BIGINT := 0;
  _batch_deleted BIGINT;
  _cutoff BIGINT;
BEGIN
  _cutoff := (EXTRACT(EPOCH FROM NOW() - (retention_days || ' days')::INTERVAL) * 1000)::BIGINT;

  LOOP
    DELETE FROM public.events
    WHERE id IN (
      SELECT id FROM public.events
      WHERE timestamp < _cutoff
      LIMIT batch_size
    );
    GET DIAGNOSTICS _batch_deleted = ROW_COUNT;
    _deleted := _deleted + _batch_deleted;

    -- Exit when no more rows to delete
    EXIT WHEN _batch_deleted = 0;

    -- Brief pause to release locks and let other queries run
    PERFORM pg_sleep(0.1);
  END LOOP;

  RETURN _deleted;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

Index Strategy

Composite Indexes for Query-Hot Paths

-- Cache table: queried by (source, key) and (key, updated_at)
CREATE INDEX IF NOT EXISTS idx_cache_key ON public.cache(key);
CREATE INDEX IF NOT EXISTS idx_cache_source ON public.cache(source);
CREATE INDEX IF NOT EXISTS idx_cache_updated_at ON public.cache(updated_at);
CREATE INDEX IF NOT EXISTS idx_cache_status ON public.cache(status);

-- Time-series tables: always (key, timestamp DESC) for "latest N" queries
CREATE INDEX IF NOT EXISTS idx_logs_1m_key_time ON public.logs_1m(key, open_time DESC);
CREATE INDEX IF NOT EXISTS idx_events_key_ts ON public.events(key, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_opportunities_key_ts ON public.opportunities(key, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_history_key_time ON public.history(key, bucket_time DESC);
CREATE INDEX IF NOT EXISTS idx_snapshots_key_ts ON public.snapshots(key, timestamp DESC);

Partial Indexes for Filtered Queries

-- Only index active rows (most queries filter by is_active)
CREATE INDEX IF NOT EXISTS idx_reference_active
  ON public.reference(is_active)
  WHERE is_active = TRUE;

-- Only index recent events (queries rarely go back > 7 days)
CREATE INDEX IF NOT EXISTS idx_events_recent
  ON public.events(timestamp DESC)
  WHERE timestamp > (EXTRACT(EPOCH FROM NOW() - INTERVAL '7 days') * 1000)::BIGINT;

-- Only index non-free tier users (tier checks in RLS)
CREATE INDEX IF NOT EXISTS idx_users_paid_tier
  ON public.users(tier)
  WHERE tier != 'free';

Index Guidelines

1. Every foreign key gets an index (PostgreSQL does not auto-index FKs).
2. Every WHERE clause column in a frequent query gets an index.
3. Composite indexes: leftmost column = most selective filter. (key, timestamp DESC) not (timestamp DESC, key).
4. Avoid over-indexing: Each index costs write performance. Write-heavy workloads pay extra per index. Measure before adding.
5. Use CONCURRENTLY: CREATE INDEX CONCURRENTLY for production tables to avoid locking.

SECURITY DEFINER Function Safety

The Problem

SECURITY DEFINER functions run with the privileges of the function owner (typically the postgres superuser). If the function's search_path is mutable, an attacker could create a function in a different schema that shadows a built-in function.

The Fix

Always set search_path explicitly:

-- CORRECT: search_path pinned
CREATE OR REPLACE FUNCTION public.get_user_tier(user_id uuid)
RETURNS text
LANGUAGE sql STABLE SECURITY DEFINER
SET search_path = public
AS $$
  SELECT COALESCE(
    (SELECT tier FROM public.users WHERE id = user_id),
    'free'
  );
$$;

-- WRONG: search_path not set (linter warning: function_search_path_mutable)
CREATE OR REPLACE FUNCTION public.get_user_tier(user_id uuid)
RETURNS text
LANGUAGE sql STABLE SECURITY DEFINER
AS $$
  SELECT COALESCE(
    (SELECT tier FROM users WHERE id = user_id),
    'free'
  );
$$;

Functions That Need search_path

Function	Status
run_retention_cleanup()	Has SECURITY DEFINER, needs SET search_path = public
update_<table>_updated_at() triggers	Fix in security_fixes.sql
get_user_tier()	Needs SET search_path = public
batch_delete_old_rows()	Template above includes it

Naming Conventions

Tables

• Snake_case, plural: order_executions, audit_log
• Prefix for external/ingested data with its source: e.g. vendor_
• Suffix _cache for frequently-refreshed data: cache, data_cache
• Suffix _summary for aggregated data: daily_summary, weekly_summary
• Suffix _1m for time-bucketed data: logs_1m, metrics_1m

Columns

• Snake_case: created_at, rate_pct, user_id
• Primary key: id BIGSERIAL PRIMARY KEY (not UUID for high-volume tables)
• Foreign key: user_id uuid REFERENCES auth.users(id)
• Timestamps: created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW()
• Epoch timestamps: timestamp BIGINT (milliseconds), event_time BIGINT
• Status fields: TEXT not ENUM

Indexes

• Format: idx_<table>_<column(s)>
• Examples: idx_cache_key, idx_logs_1m_key_time

RLS Policies

• Format: "<Action> <scope> <table>"
• Examples: "Anon read cache", "Users read own api_keys", "Service write logs_1m"

DDL Template for New Tables

-- ============================================================
-- <Table Name> — <Brief description>
-- Writer: <which service writes to this table>
-- Schedule: <how often, e.g., "30s batch upsert", "on demand">
-- ============================================================

CREATE TABLE IF NOT EXISTS public.<table_name> (
  id              BIGSERIAL PRIMARY KEY,
  -- Business columns
  source          TEXT        NOT NULL,
  key             TEXT        NOT NULL,
  -- High-precision values as TEXT
  value           TEXT,
  amount          TEXT,
  -- Metadata
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW(),
  -- Uniqueness constraint (if upsert)
  UNIQUE(source, key)
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_<table>_<column> ON public.<table_name>(<column>);

-- RLS
ALTER TABLE public.<table_name> ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Anon read <table_name>"
  ON public.<table_name>
  FOR SELECT
  USING (true);
CREATE POLICY "Service write <table_name>"
  ON public.<table_name>
  FOR ALL
  TO service_role
  USING (true)
  WITH CHECK (true);

Disaster Recovery

Recovery Procedure

1. Schema recovery: Run all sql/*.sql files in order:

   • core_tables.sql first (foundational)
   • Then all other table files
   • Then retention_policies.sql (depends on tables existing)
   • Then security_fixes.sql and rls_fixes.sql (patches)

2. Data recovery: Many managed Postgres providers (including Supabase Pro) offer point-in-time recovery (PITR). Append-heavy data with upserts can often be regenerated from upstream sources.

3. Non-recoverable data: User profiles, credentials, order history, and anything user-authored. These MUST be backed up or have PITR enabled.

DDL Audit Checklist

Run periodically to verify schema-as-code matches production:

-- List all tables in public schema
SELECT tablename FROM pg_tables WHERE schemaname = 'public' ORDER BY tablename;

-- Compare with tables documented in sql/ files
-- Any table in production but not in sql/ = missing DDL (add it)
-- Any table in sql/ but not in production = stale DDL (remove or deploy)

Anti-Patterns to Reject

Anti-Pattern	Why It's Wrong	Correct Approach
NUMERIC/DECIMAL for high-precision values	String representation varies between clients, risks silent precision loss	TEXT for all high-precision values, arithmetic via decimal library
PostgreSQL ENUM types	Painful to alter, migration-heavy	TEXT columns with app-level validation
FOR ALL USING (true) without TO service_role	Grants write to anon/authenticated	Always scope write policies to service_role
Table without DDL file	Cannot recover from disaster	Every table has a sql/ file
Single retention period for all tables	Different data has different value	Per-table retention based on usage + regulation
Missing SET search_path on SECURITY DEFINER	Schema injection vulnerability	Always set search_path = public
DROP TABLE in migration	Data loss, no recovery	Use ALTER TABLE, additive changes only
UUID primary keys on high-volume tables	Slower inserts, larger indexes	BIGSERIAL for time-series, UUID for user-facing
Missing indexes on foreign keys	Slow joins, slow cascading deletes	Index every FK column
Unbounded DELETE in retention	Locks table for duration	Batch delete with LIMIT + pg_sleep

Query Optimization Discipline

Slow queries are a schema problem as much as a query problem. Run EXPLAIN ANALYZE before deploying any query that touches a table with more than 10,000 rows or runs on a hot path (invoked per request, per message, per event).

EXPLAIN ANALYZE Workflow

-- Step 1: Run with EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) to see real execution
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.id, u.tier, COUNT(o.id) AS order_count
FROM public.users u
JOIN public.order_executions o ON o.user_id = u.id
WHERE u.tier != 'free'
  AND o.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.tier;

-- Read the output: look for these signals
-- BAD:  Seq Scan on a large table (no index hit)
-- BAD:  rows estimated=10 actual=94823 (planner was 9000x wrong — stale stats)
-- BAD:  Hash Join with very high actual rows (may spill to disk)
-- OK:   Bitmap Heap Scan (partial index used — acceptable for medium selectivity)
-- GOOD: Index Scan or Index Only Scan

NEVER ship a query that produces a Seq Scan on a table over 50,000 rows without an explicit decision recorded in a comment: -- Seq Scan accepted: table is 200 rows, index cost > benefit.

Plan Regression Detection

Capture the query plan hash for every query on a hot path. Alert when the plan changes between deployments — a plan change is often a sign that an index was dropped, statistics drifted, or a new data distribution broke the planner's assumptions.

-- Capture plan hash for regression tracking via EXPLAIN.
-- pg_stat_statements does NOT expose query plans (only normalized SQL +
-- timing); to track plan stability you must run EXPLAIN in CI and hash
-- the structured output yourself.
EXPLAIN (FORMAT JSON)
  SELECT *
  FROM order_executions
  WHERE status = 'open'
    AND created_at > now() - interval '7 days'
  ORDER BY created_at DESC
  LIMIT 100;

Capture the JSON output and split it into TWO independent CI artifacts:

1. Structural plan hash — normalize cost ranges + node IDs (they fluctuate with stats) and hash the remaining structure: join order, scan types, index choices, filter conditions. Store as plan_structure_hash. A change here MUST trigger deliberate review before merge.
2. Row-estimate snapshot — DO NOT normalize estimates. Store per-node Plan Rows as a separate plan_rows_snapshot artifact. A 10× drift on any node signals stats staleness or a data-distribution shift — alert independently of the structure hash.

Hashing structure AND estimates together collapses the very stats-drift signal you most want to catch; keep them separate.

Index Design Rules for Hot-Path Queries

Query Pattern	Index Type	Example
Equality + range on same column	B-tree composite	(status, created_at DESC)
Queries filtering a known subset	Partial index	WHERE status = 'active'
Expression in WHERE clause	Expression index	lower(email)
Full-text search on TEXT column	GIN index	gin_trgm_ops on description
Covering query (no heap fetch)	Include columns	(user_id) INCLUDE (tier, email)

Covering index rule: if a query reads only two or three columns after filtering, add those columns to INCLUDE (...) so the planner can answer the query from the index leaf page without a heap fetch. This eliminates the most expensive part of an Index Scan on wide tables.

-- CORRECT: covering index for tier-gated user lookup
CREATE INDEX IF NOT EXISTS idx_users_tier_covering
  ON public.users (tier)
  INCLUDE (email, created_at)
  WHERE tier != 'free';

-- WRONG: forces a heap fetch for each matching row
CREATE INDEX IF NOT EXISTS idx_users_tier ON public.users (tier);

Eliminating N+1 Queries

An N+1 query is a loop that issues one query per row from a parent result. It is the single most common cause of application-layer slow queries that look fine in isolation.

-- WRONG: application loop issues one query per user
-- SELECT * FROM users LIMIT 20;
-- then for each user: SELECT * FROM orders WHERE user_id = $1;
-- = 1 + 20 = 21 round trips, scales with result size

-- CORRECT: single JOIN with aggregation
SELECT
  u.id,
  u.email,
  u.tier,
  COALESCE(
    json_agg(
      json_build_object('id', o.id, 'amount', o.amount, 'created_at', o.created_at)
      ORDER BY o.created_at DESC
    ) FILTER (WHERE o.id IS NOT NULL),
    '[]'
  ) AS recent_orders
FROM public.users u
LEFT JOIN public.order_executions o
  ON o.user_id = u.id
  AND o.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.email, u.tier
LIMIT 20;

When a JOIN result set is too large to aggregate safely, use a lateral subquery to cap the per-parent rows explicitly:

-- Lateral: fetch at most 5 recent orders per user without a full JOIN
SELECT u.id, u.email, recent.orders
FROM public.users u
CROSS JOIN LATERAL (
  SELECT json_agg(o ORDER BY o.created_at DESC) AS orders
  FROM (
    SELECT id, amount, created_at
    FROM public.order_executions
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 5
  ) o
) recent
WHERE u.tier != 'free'
LIMIT 20;

Materialized View Refresh Strategy

Use materialized views for aggregations that are expensive to recompute per request but do not require sub-minute freshness.

-- Create the materialized view
CREATE MATERIALIZED VIEW public.mv_daily_revenue AS
SELECT
  DATE(created_at) AS day,
  tier,
  COUNT(*) AS order_count,
  SUM(amount::NUMERIC)::TEXT AS total_revenue
FROM public.order_executions
WHERE status = 'completed'
GROUP BY DATE(created_at), tier
WITH DATA;

-- Index it like any other table
CREATE UNIQUE INDEX ON public.mv_daily_revenue (day, tier);

-- Refresh on a schedule via pg_cron (not CONCURRENTLY if small; CONCURRENTLY if large)
SELECT cron.schedule(
  'refresh-daily-revenue',
  '0 * * * *',   -- every hour
  'REFRESH MATERIALIZED VIEW CONCURRENTLY public.mv_daily_revenue'
);

NEVER use REFRESH MATERIALIZED VIEW without CONCURRENTLY on a production table that serves live reads. The non-concurrent form takes an exclusive lock for the entire refresh duration.

ETL/ELT and Pipeline Schema Discipline

Tables that serve as pipeline landing zones or intermediate stages carry additional constraints beyond regular application tables. Violating them causes silent data corruption that only surfaces hours or days later.

Batch vs. Streaming Choice Rubric

Choose the pipeline model before writing any DDL. The model determines the table structure, index strategy, and schema evolution contract.

Dimension	Choose Batch	Choose Streaming
Acceptable latency	Minutes to hours	Seconds
Source delivery	Files, database exports	Kafka, CDC, webhooks
Processing cost sensitivity	Low (burst OK)	High (sustained cost)
Replay safety / effectively-once	Idempotent upserts (replay-safe; not true exactly-once distributed-systems semantics)	Offset tracking + transactional sink (still effectively-once at consumer)
Schema change frequency	High (dbt handles it)	Low (hard to hot-swap)

If latency tolerance is above 5 minutes, batch is almost always the right choice. Streaming carries real operational cost (broker management, consumer group lag monitoring, offset tracking + transactional sink + replay-safety machinery for effectively-once delivery) that batch avoids entirely.

Idempotency and Replay Safety

Every pipeline landing table MUST support idempotent writes. A replay (re-running the pipeline for the same time window) MUST produce the same final state, not duplicate rows or overwritten aggregates.

-- CORRECT: upsert with explicit conflict target
INSERT INTO public.external_prices (source, key, price, fetched_at)
VALUES ($1, $2, $3, $4)
ON CONFLICT (source, key) DO UPDATE
  SET price = EXCLUDED.price,
      fetched_at = EXCLUDED.fetched_at
WHERE EXCLUDED.fetched_at > external_prices.fetched_at;  -- only advance, never regress

-- WRONG: INSERT without conflict handling — duplicates on replay
INSERT INTO public.external_prices (source, key, price, fetched_at)
VALUES ($1, $2, $3, $4);

For append-only tables (event logs, audit trails), replay safety requires a deduplication key:

-- Deduplication key on the append-only table
CREATE TABLE IF NOT EXISTS public.raw_events (
  id            BIGSERIAL PRIMARY KEY,
  event_id      TEXT        NOT NULL,  -- upstream idempotency key
  source        TEXT        NOT NULL,
  payload       JSONB       NOT NULL,
  ingested_at   TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(source, event_id)             -- replay safe: duplicate = conflict = skip
);

Schema Evolution Contract for Downstream Consumers

When a pipeline table's schema changes, downstream consumers (dbt models, materialized views, application queries) break silently unless a migration discipline is enforced.

Hard rules for pipeline schema changes:

1. Additive only in the same major version. New nullable columns are safe to add. Dropping columns, renaming columns, or changing column types requires a versioned table or a coordinated cut-over.

2. Announce breaking changes with lead time. Add the new column, publish the change, give consumers one full pipeline cycle to adapt, then remove the old column. Never drop on the same day you add the replacement.

3. Dual-write during cut-over. When renaming a column, write both the old and new name for one full release cycle:

   -- Migration: dual-write period (both columns populated)
   ALTER TABLE public.external_data ADD COLUMN symbol TEXT;
   UPDATE public.external_data SET symbol = ticker WHERE symbol IS NULL;
   
   -- Application: write both until all consumers migrated
   -- INSERT INTO external_data (ticker, symbol, ...) VALUES ($1, $1, ...)
   
   -- After all consumers updated: drop old column in a separate migration
   ALTER TABLE public.external_data DROP COLUMN ticker;
   

4. Document the schema contract at the table level. Use a DDL comment that lists the consumer list and the freshness SLA:

   COMMENT ON TABLE public.external_data IS
     'Pipeline landing table for external market data.
      Consumers: mv_daily_summary, api/v1/prices, dbt model silver_prices.
      SLA: refreshed every 30 seconds. Schema changes require 24h notice to consumers.';
   

Pipeline Observability Columns

Every pipeline table MUST include audit columns so failures and replays can be traced without external logging:

CREATE TABLE IF NOT EXISTS public.pipeline_landing (
  id              BIGSERIAL PRIMARY KEY,
  -- Business payload
  source          TEXT        NOT NULL,
  key             TEXT        NOT NULL,
  payload         JSONB       NOT NULL,
  -- Pipeline audit columns (NEVER omit these)
  ingested_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  pipeline_run_id TEXT        NOT NULL,  -- identifies which run wrote this row
  source_file     TEXT,                  -- path or URL of the source file/batch
  row_checksum    TEXT,                  -- md5 or sha256 of payload for drift detection
  UNIQUE(source, key, pipeline_run_id)
);

pipeline_run_id lets you answer: "which pipeline run wrote these rows?" without querying an external log system. row_checksum lets you detect silent data mutation between pipeline stages.

Migration Tooling and Rollout Safety

The Migration Strategy section above covers the idempotent, Postgres-first DDL shape this project uses. This section covers the discipline that holds regardless of which migration runner a target repo already uses, and the zero-downtime rollout pattern for changes that would otherwise break a rolling deploy.

Migration Discipline (tool-independent)

1. Every change is a migration file. Never hand-run DDL against a production database. A manual change leaves no audit trail and cannot be replayed onto a fresh environment, so environments silently diverge.
2. Migrations are immutable once they have run in production. Editing a landed migration makes the version that ran in prod no longer match the one in the repo — the definition of drift. To change course, add a new forward migration; never rewrite history.
3. Roll forward, not back. In production a "rollback" is a new migration that undoes the change, not an in-place reversal. Keep a documented DOWN for local/dev iteration, but treat production recovery as forward-only.
4. Separate schema (DDL) from data (DML). A backfill that transforms millions of rows has a different risk and retry profile from an ALTER TABLE. Mixing them in one migration makes both harder to reason about, and a failed backfill can strand the schema change mid-flight. (This is the operational twin of the Additive only rule.)
5. Rehearse against production-sized data. A migration that returns instantly on 100 rows can hold a lock for minutes on 10M. Test on a branch or a restored snapshot before applying.

Migration Safety Checklist

Before applying any migration:

• Reversible, or explicitly marked irreversible with a recovery note.
• No full-table lock on a large table (concurrent DDL, batched DML).
• New columns are nullable or carry a default. Never add NOT NULL without a default to a populated table — that rewrites every row under an exclusive lock. (On Postgres 11+, ADD COLUMN ... NOT NULL DEFAULT <const> is a metadata-only change and is safe; the hazard is the no-default case.)
• Indexes built CONCURRENTLY, outside a transaction block.
• Data backfill is its own migration, batched.
• Rehearsed on production-sized data; recovery path written down.

Cross-Tool Quick Reference

Our default is raw Postgres DDL in sql/. When a target repo already drives migrations through an ORM's runner, these are the commands you'll meet — the safety rules above are tool-independent and still apply.

Runner	Generate	Apply (prod)	Custom SQL / concurrent index
Prisma	prisma migrate dev --name x	prisma migrate deploy	prisma migrate dev --create-only, then hand-edit — Prisma does not emit CONCURRENTLY
Drizzle	drizzle-kit generate	drizzle-kit migrate	Edit the generated SQL by hand; drizzle-kit push is dev-only (no migration file)
Kysely	kysely migrate:make x	kysely migrate:latest	Write up/down in TS; type the DB as Kysely<any> so a frozen migration never depends on current schema types
Django	makemigrations	migrate	makemigrations --empty for a RunPython backfill; SeparateDatabaseAndState to drop a field from model state without touching the DB yet
golang-migrate	migrate create -ext sql -seq x	migrate ... up	Paired .up.sql/.down.sql; migrate ... force VERSION clears a dirty state

One caveat that bites every runner: CREATE INDEX CONCURRENTLY cannot run inside a transaction, and most runners wrap each migration in one. Isolate a concurrent index in its own migration, or use the runner's explicit "no transaction" escape hatch.

Expand–Contract (Zero-Downtime) Rollout

Any change that a rolling deploy would break — renaming a column, tightening a type, splitting a table — goes through three phases so that old and new code run side by side without error. This is the same principle as the Dual-write during cut-over rule in the pipeline section, generalized to all schema change.

Phase 1 — EXPAND
  Add the new column/table (nullable or defaulted).
  Deploy code that writes BOTH old and new.
  Backfill existing rows in batches.

Phase 2 — MIGRATE
  Deploy code that READS new, still WRITES both.
  Verify old and new agree.

Phase 3 — CONTRACT
  Deploy code that uses only new.
  Drop the old column/table in a SEPARATE, later migration.

Column rename is the canonical case: never ALTER TABLE ... RENAME COLUMN in place on a live table. Add the new name, dual-write, cut reads over, then drop the old name once no deployed code references it. A representative day-scale timeline (compress or stretch to your deploy cadence):

Day 1  Migration adds new_status (nullable); deploy v2 writes status + new_status
Day 2  Backfill migration fills new_status for existing rows
Day 3  Deploy v3 reads new_status only (still writes both)
Day 7  Migration drops the old status column

Batched Backfill with SKIP LOCKED

The Batch Delete with Backpressure function covers deletes; backfills need the same backpressure. FOR UPDATE SKIP LOCKED lets several workers backfill disjoint row sets without blocking each other, and the batch loop keeps any single transaction short:

-- Backfill in bounded batches, releasing locks between batches.
CREATE OR REPLACE PROCEDURE public.backfill_normalized_email(batch_size INT DEFAULT 10000)
LANGUAGE plpgsql AS $$
DECLARE _n INT;
BEGIN
  LOOP
    UPDATE public.users
      SET normalized_email = lower(email)
    WHERE id IN (
      SELECT id FROM public.users
      WHERE normalized_email IS NULL
      LIMIT batch_size
      FOR UPDATE SKIP LOCKED
    );
    GET DIAGNOSTICS _n = ROW_COUNT;
    EXIT WHEN _n = 0;
    COMMIT;   -- release row locks + WAL pressure between batches
  END LOOP;
END $$;

CALL public.backfill_normalized_email();

Note the PROCEDURE/CALL shape: transaction-control statements (COMMIT) are never legal inside an ordinary function, and inside a DO $$ ... $$ block only on PG11+ when invoked at top level (outside an explicit transaction). The stored PROCEDURE + CALL shape works on every PG >= 11 and keeps the transaction boundaries explicit; an application-side loop is the portable fallback. If your migration runner wraps each migration in a single transaction, drive the loop from application code instead so each batch commits independently.

Access Patterns: Pagination, Queues, and RLS Performance

These are read/write shapes that keep hot paths fast as tables grow. They complement the Index Strategy and RLS Policy Patterns sections above.

Keyset (Cursor) Pagination

OFFSET n makes the database scan and discard n rows before returning the page — O(n), and it gets slower the deeper a user pages. Keyset pagination carries the last row's sort key as a cursor and is effectively O(1) per page:

-- First page
SELECT id, created_at
FROM public.events
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: pass the last row's (created_at, id) back as the cursor
SELECT id, created_at
FROM public.events
WHERE (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Back it with an index whose column order matches the ORDER BY: (created_at DESC, id DESC). Use the row-value comparison (created_at, id) < (...), not a bare created_at < — comparing only the timestamp duplicates or skips rows at the page boundary whenever two rows share a timestamp. The trailing unique key (id) breaks the tie deterministically.

Queue Claim with SKIP LOCKED

To hand work to N concurrent workers without two of them grabbing the same job, claim the next row under FOR UPDATE SKIP LOCKED:

UPDATE public.jobs
  SET status = 'processing', started_at = NOW()
WHERE id = (
  SELECT id FROM public.jobs
  WHERE status = 'pending'
  ORDER BY created_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING *;

SKIP LOCKED is correct only for queue-style work where skipping a row another worker already holds is the desired behavior. Never use it for balance reads, accounting, or any integrity-sensitive query — it deliberately returns an inconsistent snapshot (locked rows simply vanish from the result).

RLS Predicate Performance

A policy predicate is evaluated per row. If it calls a stable function such as auth.uid() directly, the planner may re-evaluate that call for every row. Wrap the call in a scalar subquery so the planner hoists it to an init-plan evaluated once per statement:

-- Slower: auth.uid() may run per row
CREATE POLICY "Users read own t" ON public.t
  FOR SELECT USING (auth.uid() = user_id);

-- Faster: evaluated once, then compared per row — identical meaning
CREATE POLICY "Users read own t" ON public.t
  FOR SELECT USING ((SELECT auth.uid()) = user_id);

Apply the same wrap to any STABLE helper used in a policy, e.g. (SELECT public.get_user_tier((SELECT auth.uid()))). This changes nothing about what the policy permits — only how many times the function runs — and is a measurable win on large tables.

BRIN for Append-Only Time-Series

The Index Strategy section uses B-tree composite and partial indexes. For very large append-only tables whose rows land in roughly time order (event logs, snapshots keyed by timestamp, ingest tables), a BRIN index on the time column is a tiny fraction of the size of a B-tree and is enough to prune block ranges for wide range scans:

CREATE INDEX IF NOT EXISTS brin_events_ts ON public.events USING brin (timestamp);

BRIN only helps when physical heap order correlates with the indexed column — true for append-only ingest, false once heavy updates/deletes shuffle the heap. Keep the B-tree (key, timestamp DESC) for "latest N per key" lookups; add BRIN alongside it for historical range scans over the whole table.

Drift-Detection Queries

Run these periodically; they complement the DDL Audit Checklist:

-- Foreign keys with no backing index (slow joins + slow cascading deletes)
SELECT conrelid::regclass AS tbl, a.attname AS col
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND a.attnum = ANY (i.indkey)
  );

-- Tables accumulating dead tuples (autovacuum falling behind)
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Slowest normalized statements (requires the pg_stat_statements extension)
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC
LIMIT 20;

Server-Level Guards

Two settings stop a single runaway query or a forgotten open transaction from taking the database down. Set them at the database or role level, not just per session:

ALTER DATABASE app SET statement_timeout = '30s';
ALTER DATABASE app SET idle_in_transaction_session_timeout = '30s';

And revoke the default public schema grant so newly created objects are not world-usable by accident, then grant explicitly:

REVOKE ALL ON SCHEMA public FROM PUBLIC;

This is defense-in-depth alongside RLS, not a replacement for it.

Cross-Engine Notes: MySQL and MariaDB

This skill assumes Postgres, and the principles transfer directly: schema-as-code, additive migrations, indexed foreign keys, exact-decimal money, batched deletes, and least privilege all apply unchanged. When a target repo is on MySQL or MariaDB, the items below are the concrete syntax and behavioral differences that actually bite. The two engines have diverged, so identify which one you are on before applying a version-specific pattern.

Identify the Engine and Version First

SELECT VERSION();
SHOW VARIABLES LIKE 'version_comment';

Schema Defaults

CREATE TABLE orders (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    account_id  BIGINT UNSIGNED NOT NULL,
    status      VARCHAR(32)     NOT NULL,
    total       DECIMAL(15, 2)  NOT NULL,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at  DATETIME        NULL,
    PRIMARY KEY (id),
    KEY idx_orders_account_status_created (account_id, status, created_at),
    KEY idx_orders_active (account_id, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Concern	Prefer	Avoid
Surrogate primary key	BIGINT UNSIGNED AUTO_INCREMENT	INT on anything that can pass ~2B rows
UUID lookup key	BINARY(16) with app-side conversion	VARCHAR(36) primary key on a hot table
Money / exact quantity	DECIMAL(p, s)	FLOAT / DOUBLE (same lesson as our Postgres money rule — exact type, never binary float)
User-facing text	utf8mb4 tables and indexes	the legacy utf8 / utf8mb3 alias (3-byte; drops emoji and some CJK)
Application timestamps	DATETIME, UTC managed by the app	assuming any type stores a time zone
Soft delete	deleted_at DATETIME NULL + an index that scopes it	filtering soft-deletes with no supporting index
Changing status set	lookup table or constrained VARCHAR	ENUM when values churn (mirrors our "TEXT not ENUM" rule)

Upsert — Engine Divergence

This is the sharpest edge. MySQL now documents a row-alias form and deprecates VALUES(col); MariaDB still documents VALUES(col). For a mixed or unknown fleet, VALUES(col) is the portable choice:

-- Portable (MySQL + MariaDB)
INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE
    setting_value = VALUES(setting_value),
    updated_at    = CURRENT_TIMESTAMP;

-- MySQL-only row alias (use only after confirming the engine is MySQL)
INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (?, ?, ?) AS new
ON DUPLICATE KEY UPDATE
    setting_value = new.setting_value,
    updated_at    = CURRENT_TIMESTAMP;

Keyset Pagination

Same shape as the Postgres pattern above, MySQL syntax:

SELECT id, name, created_at
FROM products
WHERE (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 50;
-- back it with:  CREATE INDEX idx_products_created_id ON products (created_at, id);

Deep OFFSET is O(n) here too — avoid it on large tables.

JSON Columns

Store extension data in a JSON column, but keep ownership, tenancy, and lifecycle fields relational. To index a hot JSON path, expose a generated column and index that column, not the JSON directly:

CREATE TABLE events (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payload    JSON NOT NULL,
    event_type VARCHAR(64)
        GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.type'))) STORED,
    KEY idx_events_type (event_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Reading EXPLAIN

Field	Investigate when
type	ALL (full scan) on a large table
key	NULL despite a selective predicate existing
rows	very high estimate on an interactive path
Extra	Using temporary, Using filesort, or a broad Using where

Use EXPLAIN ANALYZE only where it is safe to actually run the statement — it executes the query and can be expensive on production-sized data.

Transactions and Deadlocks

Keep transactions short and lock rows in a deterministic order across every code path — two paths locking the same rows in opposite order is the number-one deadlock cause.

START TRANSACTION;
SELECT id, balance FROM accounts
  WHERE id IN (?, ?) ORDER BY id FOR UPDATE;   -- deterministic lock order
UPDATE accounts SET balance = balance - ? WHERE id = ?;
UPDATE accounts SET balance = balance + ? WHERE id = ?;
COMMIT;

Deadlock / lock-wait checklist:

• Do external/API calls before opening the transaction, never inside it.
• Index every predicate used in UPDATE, DELETE, and locking reads.
• On deadlock, roll back and retry the whole transaction with a bounded budget.
• Capture SHOW ENGINE INNODB STATUS\G immediately — it is overwritten by the next event.
• For queue-style workers, claim with FOR UPDATE SKIP LOCKED (queue work only, same caveat as the Postgres queue pattern).

Connection Pool Sizing

Keep the pool's recycle age below the server wait_timeout, or the server drops idle connections the pool still believes are live. If wait_timeout = 300, a pool_recycle around 240s plus a checkout-time pre-ping (SQLAlchemy pool_pre_ping) is coherent and recovers cleanly from failover. Note for mysql2: enableKeepAlive is TCP-level keepalive only — it does NOT validate a connection at pool checkout; pair the recycle age with a connection.ping()-on-checkout or retry-on-stale wrapper instead.

Replication Read-After-Write

Read replicas lag. Never route read-your-own-write paths — checkout, permission checks, idempotency-key reads — to a replica immediately after a write; pin those to the primary. Monitor SQL-thread health, IO-thread health, and lag, not just TCP liveness. The status command name differs by version — SHOW REPLICA STATUS\G on newer builds, SHOW SLAVE STATUS\G on older fleets — so check before standardizing on one.

Least Privilege and TLS

Same posture as our Postgres SECURITY DEFINER / RLS discipline:

• The runtime application user is not the migration/admin user and never holds ALL PRIVILEGES or *.*.
• Require TLS for users whose traffic crosses hosts (ALTER USER ... REQUIRE SSL).
• Manage grants through CREATE USER / ALTER USER / DROP USER, never by DML against mysql.user (grant-table corruption risk).
• Remove anonymous users (DROP USER IF EXISTS ''@'localhost').
• Store credentials in a secret manager, never in migration files or examples.

Configuration Is a Prompt for Review, Not a Preset

Durability and sizing knobs (innodb_buffer_pool_size, max_connections, innodb_flush_log_at_trx_commit, sync_binlog, wait_timeout, slow-log settings, binlog retention) must be sized from the workload, hardware, backup policy, and recovery objectives — not copied from a template. Treat any example my.cnf as a starting point to review, not a universal default.

MySQL/MariaDB Anti-Patterns

Anti-Pattern	Risk	Better Pattern
SELECT * on hot paths	Over-fetching, brittle clients	Select explicit columns
Deep OFFSET pagination	Linear scans, slow pages	Keyset pagination
No index on FK joins	Slow joins, lock-heavy deletes	Index FK columns intentionally
Long transactions	Lock waits, large undo history	Commit small units of work
Direct DML against mysql.user	Grant-table corruption	CREATE/ALTER/DROP USER
App user with admin grants	Large blast radius	Least-privilege runtime user
pool_recycle above wait_timeout	Stale pooled connections	Recycle below timeout + pre-ping
Replica read after write	Stale user-facing state	Pin read-after-write flows to primary

When Reviewing a MySQL/MariaDB Change

State the engine/version assumption up front, call out the highest-risk correctness, lock, security, and migration issues, give the exact safe SQL, and attach a validation plan: EXPLAIN, a migration dry run on production-sized data, a lock/deadlock check, and rollback criteria. Flag any MySQL-vs-MariaDB syntax difference that changes the recommendation.

Changelog

• 1.1.0 (2026-07-07, PLAN-153 Wave G, SP-027): clean-room ADAPT merge of three upstream skills into new sections — Migration Tooling and Rollout Safety (tool-independent migration discipline, safety checklist, cross-ORM runner reference, expand–contract rollout, SKIP-LOCKED batched backfill), Access Patterns: Pagination, Queues, and RLS Performance (keyset pagination, queue claim, RLS init-plan wrap, BRIN, drift-detection queries, server-level guards), and Cross-Engine Notes: MySQL and MariaDB. Also introduced the version: frontmatter and this changelog, extended description + activation_triggers, and recorded provenance in inspired_by. No existing section was altered; all additions are net-new. Skill-Import-Attestation: reviewed-by=AE9B236FDAF0462874060C6BCFCFACF00335DC74; sha256=702045c2a1b730fe8184e11fa458fc726891b36379c513e9e55cac643187a2c6