Database and Postgres Skills for AI Coding Agents

Best for: Ordinary Postgres projects that need a broad default

This portable Supabase guide applies to Postgres anywhere. Its rule set spans schema design, migrations, row-level security, indexes, EXPLAIN, locking, connection management, and advanced Postgres features. The material is organized by priority, which gives an agent a useful order when a task touches several parts of a database at once.

The examples make the guidance operational. Rules pair incorrect SQL with a corrected version, and many include EXPLAIN output so the agent can connect a query shape to its cost. The coverage includes missing, partial, expression, and covering indexes, connection pooling, lock behavior, and RLS policies. The RLS material includes policy patterns that avoid repeated per-row work, while the locking material gives an agent database-level context before it changes a live schema. Use this as the broad starting rulebook for a Postgres project, then follow the conditional platform link when the task depends on a vendor product or workflow.

• Portable guidance for Postgres on any host
• Schema and migration patterns
• RLS, indexes, and EXPLAIN coverage
• Locking guidance for safer database changes

Best for: Slow Postgres queries that need evidence before a fix

This SQL optimization reference starts with EXPLAIN so an agent can inspect scans, joins, and plan shape before proposing a change. It explains sequential scans on large tables, index-only scans, and the situations that produce nested-loop, hash, or merge joins. That sequence keeps an index recommendation tied to observed planner behavior.

The index section covers partial indexes for filtered subsets, expression indexes such as LOWER(email), covering indexes with INCLUDE, GIN for JSONB and full text, and BRIN for very large append-only tables. It also names common index blockers, including functions in WHERE clauses and leading-wildcard LIKE patterns. pg_stat_statements and pg_stat_user_tables queries help find expensive, missing, or unused indexes while keeping write overhead in view. The result is a repeatable path from an observed query, through a plan and measured workload, to an index change that can be checked again.

• EXPLAIN-first query investigation
• Index selection grounded in query plans
• pg_stat_statements for slow-query evidence
• Performance fixes weighed against write cost

Best for: Schema changes that need rollback and restore planning

This migration guide uses expand-contract for online schema changes. The agent adds a compatible shape, backfills it, moves application callers across, and removes the old shape after the transition. That sequence gives deployed versions time to coexist during a rename or representation change. The examples cover Sequelize, TypeORM, and Prisma syntax, with a working down migration treated as part of the change.

Recovery is part of the workflow. Transaction-wrapped migrations can roll back when an operation fails, while a checkpoint pattern takes a table snapshot before a risky transformation, verifies the result, and restores the checkpoint when verification fails. A multi-step rename can therefore carry its compatibility period, down path, transaction behavior, and restore checkpoint as one reviewable plan. Reach for this card when the job changes schema state and the recovery path needs to be written alongside the forward migration.

• Expand-contract workflow for schema changes
• Down migrations as part of the change
• Transaction rollback when a migration fails
• Checkpoint restore for risky transformations

Best for: Auditing Postgres code before it reaches production

This review guide focuses on Postgres-specific decisions that general code review often misses: data types, JSONB and arrays, indexes, functions and extensions, row-level security, and privileges. Those areas affect storage shape, query behavior, execution rights, and the boundary between application code and database policy. A useful pass asks whether the chosen type expresses the data, whether JSONB or arrays have an access pattern the indexes support, and whether database code runs with the intended authority.

Use the guide as a focused audit pass over a schema change, query, function, or access-policy change. Review each choice in its Postgres context, record the risk and the reason for the recommendation, and send slow-query investigation to SQL optimization patterns or state changes to database-migration. Its scope makes it a review companion for the other three defaults, especially when a migration also changes indexes, functions, RLS, or privileges.

• Postgres data types, JSONB, and arrays
• Indexes, functions, and extensions
• RLS and privilege review
• Focused audit for Postgres code changes

Best for: Designing entities, relationships, constraints, and access paths before implementation

This workflow starts with the domain model rather than a premature table layout. It helps identify entities, relationships, cardinality, ownership, and lifecycle rules, then choose data types and nullability that express those rules. Constraints such as primary keys, foreign keys, uniqueness, and checks make the intended invariants enforceable instead of leaving them to application code.

The design is tied to how the data will be used. It considers read and write access patterns, query boundaries, indexing needs, and the trade-offs between normalization and deliberate denormalization. The result is a durable model that can be translated into an orderly, migration-ready schema and reviewed before implementation changes deployed state.

• Domain entities and relationship modelling
• Types, nullability, and enforceable constraints
• Access patterns connected to schema decisions
• Migration-ready design before implementation

Best for: Building maintainable dbt transformations with tests and documentation

This skill guides the dbt analytics-engineering workflow: declare sources, build transformations in understandable model layers, and shape trusted outputs for downstream analysis. It keeps staging, intermediate, and final reporting models distinct so joins, business logic, and aggregations have a clear place to live and can be changed without hiding semantics in one large query.

It also treats a dbt project as a convention-driven codebase. Follow the repository's project and model naming patterns, configure models in the expected locations, add tests and documentation alongside the models, and use incremental strategies when the workload requires them. The workflow is for turning warehouse data into maintainable analytics models with reviewable, documented behavior.

• Sources and layered transformation models
• Project conventions for maintainable dbt code
• Tests and documentation alongside models
• Incremental strategies for larger workloads

Best for: Designing or reviewing a Postgres schema before it ships

postgresql-table-design is a Postgres-specific schema workflow for choosing column types, keys, constraints, indexes, partitions, and semi-structured storage. It starts with normalized tables and real query access paths, then covers common Postgres choices such as identity values, TIMESTAMPTZ, JSONB, foreign-key indexes, and NULL-aware uniqueness.

Use it when designing a new Postgres schema, reviewing one before release, or planning a live schema change. Its rules cover workload patterns, indexing, partitioning, and Postgres storage and MVCC behavior, giving a database implementation decision more depth than a dbt model unit test.

• Postgres types, primary keys, constraints, and access-path indexes
• Foreign-key indexes, JSONB, and NULL-aware uniqueness
• Partitioning and workload-specific schema choices
• Safe Postgres schema design and review before release

Best for: Operating a Postgres service beyond a single query or migration

This card fills the operational gap between application-focused Postgres advice and running a database service. Its generic Postgres references cover connection and process architecture, memory, MVCC transactions, WAL and checkpoints, replication, storage layout, monitoring, and backup and recovery.

The backup material distinguishes logical dumps, physical backups, and point-in-time recovery, and insists on a tested recovery rather than assuming a backup is usable. Reach for this workflow when the work concerns recovery objectives, WAL archiving, replication, vacuum behaviour, pool sizing, or the database's operational health.

• WAL, checkpoints, MVCC, and vacuum operations
• Replication, storage, monitoring, and connection pooling
• Logical, physical, and point-in-time recovery paths
• Recovery testing treated as a required operation

Best for: Testing a risky change against realistic or schema-only data

This is a narrow branching workflow, not a generic Neon product card. It first decides between a normal branch for realistic migration and performance testing and a schema-only branch when production rows cannot be copied. It then verifies the CLI or MCP path, creates the branch, retrieves its connection string, and assigns an expiration so temporary environments do not linger.

The source also covers reset-from-parent, per-PR and per-test-run lifecycles, and preserving a reset state for rollback. Use it only when Neon branching is the available isolation mechanism. It complements the generic migration card by supplying a concrete, disposable environment for validating the plan.

• Normal versus schema-only branch decision
• CLI-first creation and connection-string retrieval
• Per-PR and per-test-run database lifecycles
• Expiration, reset, and rollback-aware cleanup