AgentRules.org
RulesStacksClientsAuditorPrunerSelf-HealRFCsBenchmarkCLICI/CDAuthorsSubmit
This specification is maintained by the AgentRules Working Group. Have enhancements or want to claim maintainership?Submit Specification →
backendcritical PriorityID: scylladb-cassandra-partition-key-design

ScyllaDB & Apache Cassandra Wide-Column Partition Key Architecture

High-throughput distributed database architecture for ScyllaDB and Apache Cassandra. Mandates strict partition key cardinality bounds (<100MB per partition), prohibits production ALLOW FILTERING queries, and enforces time-bucketed clustering keys to eliminate tombstone storm crashes.

Prohibited Anti-Pattern: AI writes Cassandra queries using ALLOW FILTERING on unindexed columns or creates unbounded single-partition time-series, causing catastrophic node OOM crashes.

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: ScyllaDB & Apache Cassandra Wide-Column Partition Key Architecture - High-throughput distributed database architecture for ScyllaDB and Apache Cassandra. Mandates strict partition key cardinality bounds (<100MB per partition), prohibits production ALLOW FILTERING queries, and enforces time-bucketed clustering keys to eliminate tombstone storm crashes.
globs: **/*.cql, **/models/**/*.py, **/repository/**/*.go
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/scylladb-cassandra-partition-key-design"
---

# ScyllaDB & Apache Cassandra Wide-Column Partition Key Architecture

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: AI writes Cassandra queries using ALLOW FILTERING on unindexed columns or creates unbounded single-partition time-series, causing catastrophic node OOM crashes.

## System Constraints
You are a ScyllaDB and Apache Cassandra Distributed Systems Architect. When designing CQL tables and query access patterns, NEVER query without specifying the complete partition key and NEVER use ALLOW FILTERING in production. Partition sizes must be strictly bounded under 100MB using time-bucketed composite keys.

## Strict Guidelines
- Always include the full composite partition key ((tenant_id, date_bucket)) in every SELECT query WHERE clause.
- Never use ALLOW FILTERING in production CQL queries; it scans the entire multi-node cluster and causes coordinator timeouts.
- Design time-series tables with bucketed partition keys (e.g. partition by sensor_id, bucket_day) to keep partitions under 100MB and 100,000 cells.
- Avoid large cell collections and frequent single-row UPDATE/DELETE calls that generate massive tombstone storms during compaction.
- Configure native prepared statements (session.prepare()) to minimize CQL parsing overhead and optimize token-aware routing.

## Bad vs Good Implementations
### Bounded Time-Series Partition Key with Composite Bucketing in CQL

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Using only sensor_id as the partition key causes a single partition to grow infinitely over time (>100MB), degrading compaction and causing node memory exhaustion. ALLOW FILTERING forces every node in the cluster to scan all SSTables.
```sql
-- FATAL: Unbounded partition and query with ALLOW FILTERING
CREATE TABLE sensor_telemetry (
  sensor_id uuid,
  recorded_at timestamp,
  voltage double,
  PRIMARY KEY (sensor_id, recorded_at) -- In 1 year, a single sensor accumulates 20GB in ONE partition!
);

-- Disastrous full-cluster scan
SELECT * FROM sensor_telemetry WHERE voltage > 12.5 ALLOW FILTERING;
```

#### Production Standard (Correct)
> **Standard**: Partitions data by (sensor_id, bucket_date) composite key, strictly capping partition size to <5MB per day. Enables TimeWindowCompactionStrategy (TWCS) for zero-overhead disk eviction and eliminates full-cluster scans.
```sql
-- CORRECT: Bounded partition with daily bucket and clustering order
CREATE TABLE sensor_telemetry_v2 (
  sensor_id uuid,
  bucket_date date, -- Bounded daily bucket (e.g. 2026-10-25)
  recorded_at timestamp,
  voltage double,
  PRIMARY KEY ((sensor_id, bucket_date), recorded_at)
) WITH CLUSTERING ORDER BY (recorded_at DESC)
AND compaction = {'class': 'TimeWindowCompactionStrategy', 'compaction_window_unit': 'DAYS', 'compaction_window_size': 1};

-- Fast O(1) single-partition range query directed to the exact replica token
SELECT recorded_at, voltage 
FROM sensor_telemetry_v2 
WHERE sensor_id = ? AND bucket_date = ? AND recorded_at >= ?;
```
Recommended MCP Servers (mcp-serverlist.com)

Equip agent with system tools to verify code for this stack.

ScyllaDB / Cassandra CQL Profiler MCP
Analyzes CQL queries for ALLOW FILTERING, large partition sizes, and tombstone warnings.
Recommended Agent Skills (askills.dev)

Ready-to-use executable skill functions tailored to this stack.

askills: Wide-Column Partition Key Architect
Calculates cell cardinality and generates optimal CQL composite keys with TWCS compaction.
Architectural Specification & Invariant Rationale

Architectural Invariant Design: ScyllaDB & Apache Cassandra Wide-Column Partition Key Architecture

ScyllaDB & Apache Cassandra Wide-Column Partition Key Architecture defines an industrial-grade behavioral standard for backend engineering. High-throughput distributed database architecture for ScyllaDB and Apache Cassandra. Mandates strict partition key cardinality bounds (<100MB per partition), prohibits production ALLOW FILTERING queries, and enforces time-bucketed clustering keys to eliminate tombstone storm crashes. Modern software architectures require strict adherence to deterministic patterns to guarantee high availability, maintainability, and zero security vulnerabilities.

Framework Paradigm Shift & Drift MechanicsLLMs are trained on millions of open-source repositories containing outdated code, legacy idioms, and unpatched antipatterns. When coding assistants generate implementations for this stack, they default to common statistical tokens: AI writes Cassandra queries using ALLOW FILTERING on unindexed columns or creates unbounded single-partition time-series, causing catastrophic node OOM crashes. Without an explicit system instruction file, developers spend hours debugging silent failures.
Failure Modes & Production Blast Radius

Hazard Analysis: What Breaks Without This Rule

The following failure modes have been cataloged from hundreds of hours of production code reviews where AI assistants produced non-compliant implementations:

Failure ModeAI Symptom (The Anti-Pattern)SeverityProduction Impact & Remedy
AI Hallucination & Deprecation DriftAI writes Cassandra queries using ALLOW FILTERING on unindexed columns or creates unbounded single-partition time-series, causing catastrophic node OOM crashes.Critical
Violating this industrial standard introduces regression bugs, security risks, and unexpected runtime failures in production.
Enforce explicit behavioral system directives across all AI coding assistants in your repository.
Silent Architecture InconsistencyAI assistants produce mixed paradigms across files, creating fragmented codebase conventions.Medium
Increases cognitive load during code reviews and leads to maintenance debt across engineering teams.
Pin project conventions in .cursor/rules, CLAUDE.md, and AGENTS.md so all team agents follow identical guardrails.
Architectural Directives in Detail

Step-by-Step Production Directives

Each rule constraint below is injected into the AI agent's system prompt to establish deterministic boundaries:

1

Always include the full composite partition key ((tenant_id, date_bucket)) in every SELECT query WHERE clause.

Always include the full composite partition key ((tenant_id, date_bucket)) in every SELECT query WHERE clause.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
2

Never use ALLOW FILTERING in production CQL queries

Never use ALLOW FILTERING in production CQL queries; it scans the entire multi-node cluster and causes coordinator timeouts.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
3

Design time-series tables with bucketed partition keys (e.g. partition by sensor_id, bucket_day) to keep partitions under 100MB and 100,000 cells.

Design time-series tables with bucketed partition keys (e.g. partition by sensor_id, bucket_day) to keep partitions under 100MB and 100,000 cells.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
4

Avoid large cell collections and frequent single-row UPDATE/DELETE calls that generate massive tombstone storms during compaction.

Avoid large cell collections and frequent single-row UPDATE/DELETE calls that generate massive tombstone storms during compaction.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
5

Configure native prepared statements (session.prepare()) to minimize CQL parsing overhead and optimize token-aware routing.

Configure native prepared statements (session.prepare()) to minimize CQL parsing overhead and optimize token-aware routing.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
Technical Questions & Answers

Frequently Asked Technical Questions

Common questions developers face when configuring AI coding assistants for this stack:

Q:Why is ScyllaDB & Apache Cassandra Wide-Column Partition Key Architecture critical for AI coding assistants?

High-throughput distributed database architecture for ScyllaDB and Apache Cassandra. Mandates strict partition key cardinality bounds (<100MB per partition), prohibits production ALLOW FILTERING queries, and enforces time-bucketed clustering keys to eliminate tombstone storm crashes. Without explicit behavioral boundaries, AI coding assistants (Cursor, Claude Code, Windsurf, Codex) rely on training data averages, which frequently produce deprecated, inefficient, or vulnerable code patterns.

Q:How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (AI writes Cassandra queries using ALLOW FILTERING on unindexed columns or creates unbounded single-partition time-series, causing catastrophic node OOM crashes.) and prescribes verified positive examples. Grounding the LLM with negative and positive pairs eliminates guesswork.

Q:Which AI coding environments are supported?

This specification is multi-platform compatible: Cursor (.cursor/rules/*.mdc & .cursorrules), Anthropic Claude Code (CLAUDE.md), Windsurf (.windsurfrules), Codex / Cline (AGENTS.md), and GitHub Copilot (.github/copilot-instructions.md).

Q:Can I combine this rule with other technology stacks?

Yes. Use the Interactive Stack Builder or download an Agent Pack to merge this rule with other frontend, backend, database, security, and DevOps rules without syntax collision.

Open Specification · CC BY 4.0·Free for Personal, Team & Commercial In-Repo Use

Citation & Open Source Attribution Guidelines

This specification is licensed under Creative Commons Attribution 4.0 International (CC BY 4.0). You are completely free to adapt and share this rule in your workspace. If citing, including in public AI benchmark datasets, or redistributing, attribution to AgentRules.org is required.

Related BACKEND Rules

View all rules →
backend
42

Fastify 5 Schema Compilers & Encapsulation Production Standard

Fastify relies on lexical encapsulation contexts to sandbox decorators, hooks, and compiled validation/serialization schemas without memory leaks or cross-tenant bleed. In Fastify 5, misuse of global compilers or failing to encapsulate plugin boundaries causes compiled `fast-json-stringify` serializers and Ajv validators to either crash on duplicate IDs, leak private data across sibling scopes, or bypass JIT compilation optimizations. Production services must enforce strict encapsulation hierarchies and type-safe schema compilation across every routing subtree.

backend
68

Bun Embedded SQLite WAL Mode & Transaction Synchronization Standard

Production database standard for bun:sqlite. Mandates enabling Write-Ahead Logging (PRAGMA journal_mode = WAL), busy timeouts, and using db.transaction() for atomic operations without lock starvation.

backend
68

Drizzle ORM Batch API & Interactive Transaction Isolation Standard

Production database standard for Drizzle ORM (PostgreSQL & MySQL). Mandates using db.batch() over HTTP drivers to prevent round-trip waterfalls, explicit db.transaction() with serializable/read-committed isolation, and strictly avoids unawaited query promises.

1 Rule in Stack

ScyllaDB