06. Denormalization: todos assigned to me

Page 01 flagged this and deliberately didn't fix it: Todo is partitioned by ownerId, so "show me todos assigned to me" — access pattern 2, a different user than the owner — has no cheap way to be answered by that table. This page is the fix, and it's the reason this tutorial exists in the first place: not a workaround, the normal way to solve this on Cassandra.

The relational instinct, stated plainly

-- What a relational schema would do
SELECT * FROM todos WHERE assignee_id = ?

One table, one extra WHERE clause. This is the reflex, and it's worth actually trying against Todo's current shape to see precisely where it breaks, rather than just being told it doesn't work.

// Won't compile / won't build a valid query
todoRepo.findAll { TodoTable.assigneeId eq assigneeId }

assigneeId is neither Todo's partition key nor its clustering key, and it has no @SecondaryIndex. QueryExecutor.resolveRows throws KandraQueryException the moment this query is built — not at some later point under load, at the call site, every time. The message points at @SecondaryIndex as the escape hatch, and reaching for it here would be the wrong instinct too: @SecondaryIndex is a scatter-gather across every node in the cluster, logged with a WARN on every single query, meant for low-cardinality fields on non-hot-path queries — "todos assigned to a given user" is exactly the kind of hot, high-cardinality lookup that column type warns you away from. See /foundations for why Kandra draws this line at all.

The actual reason it doesn't translate: Cassandra placed every Todo row by hashing its ownerId. A query for assigneeId = X has no way to know which node(s) hold rows where some other column happens to equal X — that information was never encoded into where the data lives. Nothing short of asking every node is going to answer it from this table, ever, no matter how the query is phrased.

Why not @LookupIndex

User.email from page 03 looks like the same shape of problem — "find rows by a column that isn't the partition key" — and it's tempting to reach for the same tool. It's the wrong one here, and the reason is structural, not a matter of taste:

TodoByAssignee — a second, hand-rolled, denormalized table

The actual fix is the same move /foundations already told you Cassandra wants: store the data shaped for the query you need, even though it duplicates data already in Todo. Not @LookupIndex — a full second entity, with its own partition key (assigneeId, so "todos assigned to me" is a partition-scoped read) and its own clustering key (createdAt DESC, same reasoning as Todo itself — newest assignment first, for free). This is exactly the pattern Kandra's own capstone example uses for posts_by_tag: a hand-rolled table for a "many rows per lookup value" access pattern, maintained by application code, not generated by an annotation.

package com.example.todos.model
 
import io.kandra.core.annotations.*
import kotlinx.serialization.Serializable
import java.time.Instant
import java.util.UUID
 
@Serializable
@ScyllaTable("todos_by_assignee")
data class TodoByAssignee(
    @Serializable(with = UUIDSerializer::class)
    @PartitionKey val assigneeId: UUID,
    @Serializable(with = InstantSerializer::class)
    @ClusteringKey(order = ClusteringOrder.DESC) val createdAt: Instant,
    @Serializable(with = UUIDSerializer::class) val todoId: UUID,
    @Serializable(with = UUIDSerializer::class) val ownerId: UUID,
    val title: String,
    val done: Boolean = false,
)

createdAt here carries the same value as the corresponding Todo.createdAt — copied at write time, not derived — for the same reason page 03 kept @CreatedAt off Todo: the clustering key value has to be something the application controls exactly, not something stamped independently in two different places that could drift apart by a few milliseconds. ownerId and todoId are carried along so a client reading this table has enough to act on a row (recall from page 05: finding a specific todo needs ownerId and createdAt, both present right here).

Register it alongside the others:

register(User::class, Todo::class, TodoByAssignee::class)

The write has to be atomic — this is where KandraBatchScope earns its keep

Creating an assigned todo now means two writes: the primary Todo row, and (only when assigneeId is set) a TodoByAssignee row. Two independent save() calls would look transactional — two sequential lines in the same handler — while actually executing as two unrelated writes, either of which can fail without rolling back the other. A crash between them would leave a todo that exists but is invisible to its assignee, or (worse) a TodoByAssignee row pointing at a todo that was never actually created. /why covers why this matters generally; here's the concrete fix.

import io.kandra.core.ExperimentalKandraApi
 
@OptIn(ExperimentalKandraApi::class)
fun Route.todoRoutes() {
    val todoRepo = application.kandra.suspendRepository<Todo>()
    val todoByAssigneeRepo = application.kandra.suspendRepository<TodoByAssignee>()
 
    post("/todos") {
        val ownerId = call.ownerIdOrRespondBadRequest() ?: return@post
        val req = call.receive<CreateTodoRequest>()
        val todo = Todo(
            ownerId = ownerId, createdAt = Instant.now(), todoId = UUID.randomUUID(),
            title = req.title, assigneeId = req.assigneeId,
        )
 
        if (req.assigneeId != null) {
            val forAssignee = TodoByAssignee(
                assigneeId = req.assigneeId, createdAt = todo.createdAt, todoId = todo.todoId,
                ownerId = ownerId, title = todo.title,
            )
            application.kandra.batch {
                todoRepo.saveInBatch(todo)
                todoByAssigneeRepo.saveInBatch(forAssignee)
            }
        } else {
            todoRepo.save(todo)
        }
        call.respond(HttpStatusCode.Created, todo)
    }
 
    // ...GET /todos/mine, PATCH .../done, DELETE ... unchanged from page 05
 
    get("/todos/assigned-to-me") {
        val assigneeId = call.ownerIdOrRespondBadRequest() ?: return@get
        val pageSize = call.request.queryParameters["size"]?.toIntOrNull() ?: 20
        val token = call.request.queryParameters["token"]
        val page = todoByAssigneeRepo.findPage(pageSize, token) { TodoByAssigneeTable.assigneeId eq assigneeId }
        call.respond(TodoByAssigneePage(page.items, page.nextPageToken, page.hasMore))
    }
}

Both writes land in one LOGGED BATCH, or neither does. EVENTUAL-consistency lookups (not used by either entity here — both of Todo and TodoByAssignee's writes above are plain saveInBatch calls, no @LookupIndex involved) would fire outside this atomicity boundary if either entity had one; nothing here does.

GET /todos/assigned-to-me

todoByAssigneeRepo.findPage(pageSize, token) { TodoByAssigneeTable.assigneeId eq assigneeId }

Same shape as GET /todos/mine from page 05 — a partition-scoped range read, now against a table that was shaped for exactly this query from the start, instead of the one shaped for "my todos."

Next: page 07 — two people editing the same todo at once, and why Todo (but not User) needs @Version to handle it correctly.