sql-general.yaml

description: General SQL best practices for security, performance, data integrity, and maintainability
globs:
  - "**/*.sql"
  - "**/*repository*"
  - "**/*query*"
  - "**/*dao*"
  - "**/*mapper*"
  - "**/*.py"
  - "**/*.java"
  - "**/*.ts"
  - "**/*.js"
  - "**/*.go"
  - "**/*.cs"
  - "**/*.rb"
rules:
  # Security Rules
  - name: prevent-sql-injection
    description: >
      Never concatenate user input directly into SQL queries. Use parameterized queries,
      prepared statements, or ORM query builders to prevent SQL injection attacks.
      String interpolation in SQL is a critical security vulnerability.
    severity: critical

  - name: use-parameterized-queries
    description: >
      Always use parameterized queries or prepared statements instead of string formatting
      for dynamic values. This prevents SQL injection and improves query plan caching.
      Example: Use `WHERE id = ?` or `WHERE id = $1` instead of string concatenation.
    severity: critical

  - name: apply-least-privilege
    description: >
      Database users and application connections should have only the minimum privileges
      required. Avoid using root/admin accounts for application connections. Create
      specific roles with limited permissions for each application component.
    severity: high

  - name: sanitize-input-for-identifiers
    description: >
      When dynamic table or column names are absolutely necessary, whitelist allowed
      identifiers and use proper escaping. Never allow arbitrary user input as SQL
      identifiers. Consider using enums or predefined mappings.
    severity: critical

  - name: mask-sensitive-data-in-logs
    description: >
      Ensure SQL queries containing sensitive data (passwords, PII, tokens) are not
      logged in plain text. Use query parameter placeholders in logs and mask
      sensitive values before logging.
    severity: high

  # Performance Rules
  - name: use-appropriate-indexes
    description: >
      Create indexes on columns used in WHERE, JOIN, ORDER BY, and GROUP BY clauses.
      Consider composite indexes for multi-column queries. Monitor query execution
      plans to verify index usage.
    severity: high

  - name: avoid-select-star
    description: >
      Avoid SELECT * in production code. Explicitly list required columns to reduce
      data transfer, improve query plan stability, and make schema changes safer.
      SELECT * may fetch unnecessary columns and break when schema changes.
    severity: medium

  - name: prevent-n-plus-one-queries
    description: >
      Avoid N+1 query patterns where a loop executes a query for each item. Use JOINs,
      batch queries, or eager loading to fetch related data in a single query.
      N+1 queries cause severe performance degradation at scale.
    severity: high

  - name: use-covering-indexes
    description: >
      For frequently executed queries, consider covering indexes that include all
      columns needed by the query. This allows the database to satisfy the query
      from the index alone without accessing the table.
    severity: medium

  - name: optimize-joins
    description: >
      Ensure JOIN conditions use indexed columns. Avoid joining on expressions or
      functions. Consider JOIN order and let the query optimizer work efficiently.
      Large Cartesian products indicate missing or incorrect JOIN conditions.
    severity: high

  - name: limit-result-sets
    description: >
      Always use LIMIT/TOP for queries that could return large result sets. Implement
      proper pagination with OFFSET or keyset pagination. Unbounded queries can
      exhaust memory and cause application failures.
    severity: high

  - name: avoid-functions-on-indexed-columns
    description: >
      Avoid applying functions to indexed columns in WHERE clauses (e.g., WHERE YEAR(date_col) = 2024).
      This prevents index usage. Instead, restructure the query to compare against the column directly.
    severity: medium

  # Data Integrity Rules
  - name: use-foreign-keys
    description: >
      Define foreign key constraints to maintain referential integrity between related
      tables. Foreign keys prevent orphaned records and document relationships.
      Consider ON DELETE/UPDATE actions appropriate for your use case.
    severity: high

  - name: define-appropriate-constraints
    description: >
      Use CHECK constraints, NOT NULL, UNIQUE, and DEFAULT constraints to enforce
      data validity at the database level. Don't rely solely on application-level
      validation; the database should be the last line of defense.
    severity: medium

  - name: use-transactions-appropriately
    description: >
      Wrap related database operations in transactions to ensure atomicity. Use
      appropriate isolation levels based on consistency requirements. Keep
      transactions short to minimize lock contention.
    severity: high

  - name: choose-correct-isolation-level
    description: >
      Choose transaction isolation levels based on consistency vs performance needs.
      READ COMMITTED is often sufficient; use SERIALIZABLE only when necessary.
      Understand phantom reads, non-repeatable reads, and dirty reads implications.
    severity: medium

  - name: handle-null-values-explicitly
    description: >
      Handle NULL values explicitly in queries and application code. Use COALESCE,
      NULLIF, or IS NULL/IS NOT NULL as appropriate. Remember that NULL comparisons
      with = or <> don't work as expected.
    severity: medium

  # Best Practices Rules
  - name: use-consistent-naming-conventions
    description: >
      Follow consistent naming conventions for tables, columns, indexes, and constraints.
      Use snake_case or camelCase consistently. Prefix/suffix conventions help identify
      object types (e.g., idx_ for indexes, fk_ for foreign keys).
    severity: low

  - name: normalize-appropriately
    description: >
      Apply appropriate normalization (usually 3NF) to reduce data redundancy and
      improve data integrity. Denormalize strategically only when performance
      requirements justify it, and document the trade-offs.
    severity: medium

  - name: version-schema-migrations
    description: >
      Use version-controlled migration files for all schema changes. Each migration
      should be idempotent or have both up and down scripts. Never apply manual
      schema changes to production databases.
    severity: high

  - name: make-migrations-backwards-compatible
    description: >
      Design migrations to be backwards compatible when possible. Use expand-contract
      pattern for breaking changes. This allows zero-downtime deployments and
      easy rollbacks.
    severity: high

  - name: document-complex-queries
    description: >
      Add comments to complex queries explaining the business logic, performance
      considerations, or non-obvious behavior. Future maintainers should understand
      why the query is structured a particular way.
    severity: low

  - name: use-appropriate-data-types
    description: >
      Choose the most appropriate data types for columns. Use INTEGER vs BIGINT based
      on actual needs. Use DATE/TIMESTAMP for dates, not strings. Use DECIMAL for
      money, not FLOAT. Proper types save space and prevent bugs.
    severity: medium

  - name: implement-soft-deletes-when-appropriate
    description: >
      Consider soft deletes (is_deleted flag or deleted_at timestamp) for data that
      may need recovery or audit trails. Ensure queries exclude soft-deleted records
      by default. Balance with GDPR/data retention requirements.
    severity: low

  - name: use-connection-pooling
    description: >
      Use connection pooling to manage database connections efficiently. Creating
      new connections is expensive. Configure pool sizes based on application
      concurrency and database server capacity.
    severity: high