TimescaleDB Hypertable with Continuous Aggregates Cursor Rules Template

Overview

Cursor rules configuration for TimescaleDB hypertables with continuous aggregates using Cursor AI. It defines how Cursor AI should operate when editing SQL, generating queries, and ensuring safe data engineering practices inside a PostgreSQL-based stack.

Direct answer: Use this Cursor Rules Template to configure Cursor AI for TimescaleDB hypertables and continuous aggregates, guiding SQL generation, security, testing, and project structure.

When to Use These Cursor Rules

• When building analytics on hypertable data with time-based partitioning in PostgreSQL.
• When you want Cursor AI to propose SQL with safety checks (RBAC, parameterization, and auditing).
• When your project uses continuous aggregates for materialized views and need consistent governance.

Copyable .cursorrules Configuration

FrameworkRole: TimescaleDB Database Engineer
FrameworkContext: Cursor AI assists with SQL generation, governance, and safe AI-assisted development for TimescaleDB hypertables and continuous aggregates
CodeStyle: SQL/PLpgSQL, snake_case, 2-space indentation
ArchitectureDirectoryRules: |
  db/
  db/timescaledb/
  db/timescaledb/hypertables/
  db/timescaledb/continuous_aggregates/
AuthenticationSecurity: |
  Use role-based access control; avoid embedding credentials
  Use pgcrypto for encryption at rest if needed
  All queries must be parameterized
DatabasePatterns: |
  hypertable, chunk, time_bucket, continuous_aggregate
TestingLinting: |
  pgTAP for unit tests
  psql-based integration tests
  sqllint or sqlfluff for linting
ProhibitedActions: |
  Do not use dynamic SQL without validation
  Do not bypass RBAC or grant superuser
  Do not modify system catalogs or perform unsafe DDL without approvals

Recommended Project Structure

db/
  timescaledb/
    hypertables/
      01_create_hypertable.sql
      02_alter_hypertable.sql
    continuous_aggregates/
      01_create_aggregate.sql
      02_refresh_policy.sql
  migrations/
  tests/

Core Engineering Principles

• Define clear ownership and boundaries for hypertable design and aggregation logic.
• Enforce RBAC, least privilege, and secure access to the database.
• Favor idempotent migrations and deterministic SQL in Cursor-driven work.
• Guard against AI-generated unsafe SQL with parameterization and validation.
• Document schema changes and data lineage in the project.

Code Construction Rules

• Use time_bucket for windowed aggregations and proper indexing on time columns.
• Keep continuous aggregates definitions stable; refresh schedules are managed via policy scripts.
• Always parameterize queries generated by Cursor AI; avoid string concatenation for user inputs.
• Prefer set-based SQL over row-by-row iterations; avoid cursors for bulk data modifications when possible.
• Store DDL in migration scripts with explicit checks for existing objects.

Security and Production Rules

• Use roles and grant only required permissions to app users.
• Encrypt sensitive data at rest using pgcrypto when needed.
• Enable TLS for client connections and enforce with pg_hba.conf settings.
• Audit SQL changes and maintain an immutable migration history.

Testing Checklist

• Unit tests for SQL functions via pgTAP.
• Integration tests to validate hypertable creation and continuous aggregation refresh.
• Linting: enforce SQL style with sqllint or sqlfluff.
• Smoke tests post-migration in a staging environment.

Common Mistakes to Avoid

• Relying on non-deterministic functions in migrations or triggers.
• Directly modifying system catalogs or bypassing RBAC in scripts.
• Using dynamic SQL without proper sanitization or parameterization.

FAQ

What is this Cursor Rules Template for TimescaleDB hypertables?

A complete, copyable .cursorrules configuration that guides Cursor AI on SQL generation, security, testing, and project structure for TimescaleDB hypertables with continuous aggregates.

How do I apply the .cursorrules block in my project?

Copy the code block into a file named .cursorrules at the repository root. Cursor AI reads this to constrain its SQL tasking in this stack.

Which tests should run with this template?

Unit tests with pgTAP for SQL functions, integration tests via psql scripts, and linting with sqllint or sqlfluff to ensure correctness and style.

What security practices does this template enforce?

It enforces RBAC, parameterized SQL, encryption for sensitive fields, and auditing of schema changes; avoid embedding credentials in code.

What should I watch for when maintaining continuous aggregates?

Monitor refresh policies, ensure consistent aggregation definitions, and test performance against hypertables with realistic data loads.

What are common mistakes to avoid in this stack?

Avoid dynamic SQL without validation, bypassing access controls, and applying unsafe migrations that can lock tables or mutate large datasets unexpectedly.