Using Cassandra with AI Coding Assistants

I am using Apache Cassandra 6, not a relational database.
CQL is not SQL. There are no joins, subqueries, or foreign keys.

Design the Cassandra tables for these specific query patterns.
Use query-driven data modeling: one table per query pattern.
Do not normalize across multiple tables that require joins.

Write a CQL query that includes the full partition key in the WHERE clause.
Do not use ALLOW FILTERING.

Design a database schema for a messaging application.

I am building a messaging application using Apache Cassandra 6.
Design Cassandra tables using query-driven data modeling for these exact access patterns:

1. Fetch the most recent 50 messages in a conversation, ordered by time descending.
2. List all conversations for a user, ordered by most recently active.
3. Look up a user's profile by user_id.

Each table should have a defined partition key and clustering columns.
Do not use joins, foreign keys, or ALLOW FILTERING.
Use UUID or TIMEUUID for identifiers.

# Database: Apache Cassandra 6

This project uses Apache Cassandra 6 (not a relational database).

## Modeling Rules
- Use query-driven data modeling: one table per access pattern
- Never use ALLOW FILTERING in production queries
- Always include the full partition key in WHERE clauses
- Use UUID or TIMEUUID for primary keys, not integer sequences
- Use prepared statements for all application queries

## Default Settings
- Consistency level: LOCAL_QUORUM
- Use SAI (StorageAttachedIndex) for secondary access patterns instead of ALLOW FILTERING

## Schema Conventions
- Partition keys must be bounded (use time buckets for time-series data)
- Clustering columns define sort order within a partition
- SAI indexes replace the need for ALLOW FILTERING on non-key columns

# Cassandra Rules

- Database: Apache Cassandra 6 (not relational — no joins, no foreign keys)
- All schemas follow query-driven design: tables are defined by query patterns
- Never suggest ALLOW FILTERING
- Always include partition key in WHERE clauses
- Default IDs: UUID or TIMEUUID (not AUTO_INCREMENT)
- Default consistency: LOCAL_QUORUM
- Use CREATE CUSTOM INDEX ... USING 'StorageAttachedIndex' for secondary filters
- Prepared statements required for all parameterized queries

## Database Context

This project uses Apache Cassandra 6.
CQL is not SQL. Do not generate JOINs, subqueries, or foreign key references.

Data modeling uses query-driven design.
Tables are designed for specific access patterns, not normalized entity relationships.

Query rules:
- Always include the full partition key in WHERE clauses
- Never use ALLOW FILTERING
- Use LOCAL_QUORUM as the default consistency level
- Identifiers use UUID or TIMEUUID
- Secondary access patterns use SAI (StorageAttachedIndex)

Access patterns:
1. Fetch messages for a conversation (filter: conversation_id, sort: sent_at DESC, limit: 50)
2. List conversations for a user (filter: user_id, sort: last_message_at DESC, limit: 20)
3. Get user profile (filter: user_id)

Design Cassandra 6 tables for the access patterns above.
For each table: provide the partition key, clustering columns, column types, and the DDL.
Explain which access pattern each table serves.

Write the complete CQL DDL for these tables.
Use CREATE TABLE with explicit column types.
Include CREATE CUSTOM INDEX statements for any SAI indexes you recommended.

1. Fetch the 50 most recent messages in a conversation, newest first
2. List a user's conversations, ordered by most recently active
3. Look up a user profile by user_id

-- Table 1: messages by conversation (access pattern 1)
CREATE TABLE messages_by_conversation (
  conversation_id uuid,
  sent_at         timeuuid,
  sender_id       uuid,
  body            text,
  PRIMARY KEY (conversation_id, sent_at)
) WITH CLUSTERING ORDER BY (sent_at DESC);

-- Table 2: conversations by user (access pattern 2)
CREATE TABLE conversations_by_user (
  user_id          uuid,
  last_message_at  timeuuid,
  conversation_id  uuid,
  preview          text,
  PRIMARY KEY (user_id, last_message_at)
) WITH CLUSTERING ORDER BY (last_message_at DESC);

-- Table 3: users by id (access pattern 3)
CREATE TABLE users (
  user_id   uuid PRIMARY KEY,
  username  text,
  email     text
);