The Series Navigation Mystery: When Query Translation Goes Wrong

July 14, 2025 •

AI Development Database Query Translation Debugging

July 30, 2025 - Part 16

The Post-Deployment Discovery

After successfully debugging the production deployment chaos (Part 15), our Phoenix LiveView blog was finally running smoothly with automatic migrations and bulletproof infrastructure.

The victory celebration was short-lived.

Me: “The deployment was successful, but there are no posts showing. This requires some debugging.”

After investigating the missing posts (which turned out to be a simple data corruption issue), we discovered a more subtle but puzzling problem:

Me: “There is an issue with the series for the posts. Each post is showing as an individual series. Make sure that series is not set for any of these posts right now.”

The mystery: Instead of showing “no series” in the navigation, each blog post was appearing as its own separate series entry.

What followed was a debugging journey that revealed a fundamental flaw in our TursoRepoAdapter’s query translation system—a bug so subtle it had been hiding in plain sight throughout all our previous testing.

The Series Navigation Anomaly

The series navigation component was displaying something impossible:

Series Navigation:
├── Building a Blog with Claude 4: An AI Development Adventure  
├── The Database Evolution: When AI Discovers Distributed SQLite
├── The Observability Revolution: When AI Builds Professional Monitoring  
├── The Code Quality Crusade: When AI Meets Credo's Wrath
└── The Migration System Renaissance: When AI Builds Ecto Adapters

The problem: These were individual blog posts, not series. The navigation should have been empty since none of these posts belonged to a series.

Me: “Check the turso repo to make sure the query called for populating the series nav is correct.”

Claude: “Let me investigate the TursoRepoAdapter’s query handling for Series table operations…”

The Query Translation Investigation

The series navigation was populated by this simple query in the Content module:

def list_series_for_filtering do
  from(s in Series, order_by: [asc: s.title])
  |> RepoService.all()
  |> case do
    {:ok, series} -> series
    {:error, _} -> []
  end
end

Expected behavior: Query the series table, return series records.

Actual behavior: Somehow returning individual posts as if they were series.

The investigation target: How was this Series query being translated by the TursoRepoAdapter?

The TursoRepoAdapter Query Translation Deep Dive

Claude examined the convert_ecto_query_to_sql_with_params/1 function—the heart of our adapter’s query translation system:

defp convert_ecto_query_to_sql_with_params(query) do
  case query do
    %Ecto.Query{from: %{source: {"posts", _}}} = q ->
      convert_posts_query_to_sql(q)

    %Ecto.Query{from: %{source: {"images", _}}} = q ->
      convert_images_query_to_sql(q)

    _ ->
      # Fallback for other queries
      {"SELECT * FROM posts WHERE published = 1 AND published_at IS NOT NULL ORDER BY published_at DESC", []}
  end
end

The smoking gun: The adapter only handled “posts” and “images” table queries explicitly. Every other query—including Series queries—fell through to the fallback case.

The fallback case: Always returned posts query!

The Root Cause Analysis

The bug flow:

list_series_for_filtering() creates an Ecto query targeting the series table
TursoRepoAdapter.all() calls convert_ecto_query_to_sql_with_params()
The Series query doesn’t match “posts” or “images” patterns
Falls through to the fallback: "SELECT * FROM posts WHERE published = 1..."
Returns published posts instead of series
convert_rows_to_structs() tries to convert Post data into Series structs
Navigation displays posts as if they were series

The devastating simplicity: A missing pattern match in a case statement caused an entire feature to malfunction silently.

The Comprehensive Series Support Implementation

Claude’s fix was comprehensive—not just adding the missing pattern, but implementing full Series support throughout the adapter:

1. Add Series Query Pattern Matching

defp convert_ecto_query_to_sql_with_params(query) do
  case query do
    %Ecto.Query{from: %{source: {"posts", _}}} = q ->
      convert_posts_query_to_sql(q)

    %Ecto.Query{from: %{source: {"images", _}}} = q ->
      convert_images_query_to_sql(q)

    %Ecto.Query{from: %{source: {"series", _}}} = q ->
      convert_series_query_to_sql(q)  # NEW!

    _ ->
      {"SELECT * FROM posts WHERE published = 1 AND published_at IS NOT NULL ORDER BY published_at DESC", []}
  end
end

2. Implement Series Query Conversion

defp convert_series_query_to_sql(%Ecto.Query{wheres: wheres, order_bys: order_bys} = query) do
  {where_clause, extracted_params} = convert_series_where_clauses_with_params(wheres, query)
  order_clause = convert_series_order_clauses(order_bys)
  
  sql = build_select_sql("series", where_clause, order_clause)
  {sql, extracted_params}
end

defp convert_series_where_clauses_with_params([], _query), do: {"", []}

defp convert_series_where_clauses_with_params(wheres, query) do
  {all_conditions, all_params} =
    Enum.reduce(wheres, {[], []}, fn where_expr, {conditions, params} ->
      case convert_single_where_clause(where_expr, query) do
        {condition, new_params} when condition != "" ->
          {[condition | conditions], params ++ new_params}
        _ ->
          {conditions, params}
      end
    end)

  final_conditions = Enum.reverse(all_conditions)
  where_clause = Enum.join(final_conditions, " AND ")
  {where_clause, all_params}
end

defp convert_series_order_clauses([]), do: "title ASC"

defp convert_series_order_clauses(order_bys) do
  Enum.map_join(order_bys, ", ", fn
    %{expr: [asc: _]} -> "title ASC"
    %{expr: [desc: _]} -> "title DESC"
    _ -> "title ASC"
  end)
end

3. Add Series Schema Mapping

defp determine_schema_from_query(%Ecto.Query{from: %{source: {"posts", _}}}), do: Post
defp determine_schema_from_query(%Ecto.Query{from: %{source: {"images", _}}}), do: Image
defp determine_schema_from_query(%Ecto.Query{from: %{source: {"series", _}}}), do: Series  # NEW!
defp determine_schema_from_query(_), do: Post

4. Add Series Type Conversion

defp convert_types_for_schema(fields, Series) do
  fields
  |> Map.update("inserted_at", nil, fn
    val when is_binary(val) and val != "" ->
      parse_sqlite_datetime(val)
    _ ->
      nil
  end)
  |> Map.update("updated_at", nil, fn
    val when is_binary(val) and val != "" ->
      parse_sqlite_datetime(val)
    _ ->
      nil
  end)
  |> convert_string_keys_to_atoms()
end

5. Add Series Table Name Mapping

defp get_table_name(Post), do: "posts"
defp get_table_name(Image), do: "images"  
defp get_table_name(Series), do: "series"  # NEW!
defp get_table_name(schema), do: schema.__schema__(:source)

The Testing Addition

Claude also added specific tests to prevent regression:

test "handles Series table queries for filtering" do
  # This specifically tests the fix for series navigation display
  query = from(s in Series, order_by: [asc: s.title])

  result = TursoRepoAdapter.all(query)
  assert is_tuple(result)
  assert elem(result, 0) in [:ok, :error]
end

The test focus: Ensure Series queries are handled correctly and don’t fall through to the posts fallback.

The Deployment and Verification

With the comprehensive Series support implemented, we deployed the fix:

$ mix test

Finished in 2.3 seconds (0.00s async, 2.3s sync)  
129 tests, 0 failures

$ mix format && git add . && git commit -m "Fix series display issue by adding Series table support to TursoRepoAdapter"

$ fly deploy

The moment of truth: Would the navigation now display correctly?

Production Verification Success

After deployment, the series navigation was finally correct:

Series Navigation:
(empty - no series currently exist)

Perfect. The navigation correctly showed no series, instead of erroneously displaying individual posts as series.

The Subtle Bug Category: Silent Feature Malfunction

This bug represents a particularly insidious category of software defects:

Characteristics of Silent Feature Malfunction

No crash or error: The application continued running normally.

Plausible but wrong output: Posts appearing as series wasn’t obviously impossible at first glance.

Hidden by incomplete testing: Tests focused on the happy path didn’t catch edge cases in query translation.

Compounding confusion: The wrong data made debugging other issues more difficult.

Why This Bug Was So Hard to Detect

Reasonable-looking output: Individual posts displayed as series didn’t immediately scream “bug.”

Component isolation: The bug was in the data layer, but manifested in the UI layer, making the connection non-obvious.

Fallback behavior: The fallback query returning posts instead of failing masked the real problem.

Development vs. production difference: Local SQLite testing might not have exposed this query translation issue.

The Pattern Matching Lesson

This bug highlighted the critical importance of exhaustive pattern matching in Elixir:

The Original Incomplete Pattern

case query do
  %Ecto.Query{from: %{source: {"posts", _}}} -> handle_posts()
  %Ecto.Query{from: %{source: {"images", _}}} -> handle_images()
  _ -> fallback_to_posts()  # Dangerous catch-all
end

The problem: The catch-all _ pattern masked missing implementations.

The Defensive Pattern Matching Approach

case query do
  %Ecto.Query{from: %{source: {"posts", _}}} -> handle_posts()
  %Ecto.Query{from: %{source: {"images", _}}} -> handle_images()
  %Ecto.Query{from: %{source: {"series", _}}} -> handle_series()
  unsupported_query ->
    raise "Unsupported query type: #{inspect(unsupported_query)}"
end

The improvement: Explicit error for unsupported cases instead of silent fallback behavior.

The philosophy: Make missing implementations fail loudly rather than behaving incorrectly.

What This Bug Teaches About AI-Generated Code

This bug revealed interesting patterns about AI code generation:

Where AI Systematic Thinking Helps

Comprehensive fix: Once the bug was identified, Claude implemented complete Series support across all related functions, not just the minimum fix.

Consistent patterns: The Series implementation followed the same patterns as Posts and Images implementations.

Test coverage: Claude added specific tests to prevent regression of this exact issue.

Where AI Pattern Recognition Can Miss Edge Cases

Incomplete implementation: The original adapter implementation didn’t anticipate all table types needing explicit support.

Silent failure acceptance: The fallback behavior was implemented without considering the consequences of incorrect defaults.

Integration testing gaps: The bug required understanding the interaction between query translation and UI behavior.

The Human-AI Debugging Collaboration

Human problem identification: Recognizing that “posts showing as series” was a data layer issue, not a UI issue.

AI systematic investigation: Methodically tracing the query path from UI component through adapter to database.

Human architectural guidance: Understanding that comprehensive Series support was needed, not just a quick fix.

The Compound Infrastructure Reliability

This bug fix represents the final piece in our infrastructure reliability puzzle:

Foundation Layers

Code quality standards (Part 9) - Clean, maintainable functions
Professional observability (Part 8) - Monitoring and analytics
Distributed database (Part 7) - Turso integration with HTTP API
Production-ready migrations (Part 10) - TursoEctoAdapter implementation
Battle-tested deployment (Part 11) - Robust error handling and recovery
Correct query translation (Part 12) - Accurate data layer behavior

The result: A completely reliable, professionally architected Phoenix LiveView blog with bulletproof infrastructure.

The Meta-Documentation Recursion

As I finish writing this series of devlog entries, I’m using the very blog system that went through this entire debugging and development journey. Every word of these posts is:

Stored in the Turso distributed database (Part 7)
Tracked by OpenTelemetry observability (Part 8)
Written using clean, refactored code (Part 9)
Persisted via the TursoEctoAdapter (Part 10)
Deployed through battle-tested infrastructure (Part 11)
Retrieved through properly functioning query translation (Part 12)

The recursive documentation completes: The tools built to document themselves are now fully functional and completely documented.

Looking Back: The AI Development Adventure Conclusion

From a simple request to “fix the Credo warnings” to implementing professional-grade database infrastructure, this AI development adventure revealed several profound insights:

Technical Achievements

Complete database abstraction system bridging local development and distributed production
Professional observability infrastructure with geographic intelligence and business analytics
Production-ready deployment pipeline with automatic migrations and error recovery
Clean, maintainable codebase following established quality standards

AI Development Insights

Where AI excels: Systematic implementation, comprehensive error handling, consistent patterns, and methodical debugging.

Where human judgment matters: Architectural decisions, requirement interpretation, trade-off evaluation, and production operations strategy.

The collaboration sweet spot: Human strategic guidance + AI systematic execution = professional infrastructure outcomes.

The Compound Learning Effect

Each debugging session and architectural implementation built upon previous work:

Clean code foundations enabled complex feature development
Observability infrastructure provided data for debugging decisions
Production deployment experience informed error handling strategies
Query translation debugging revealed adapter architecture insights

The pattern: Professional software development is cumulative—each solved problem provides foundation for solving more complex problems.

The Documentation Recursion Finale

These devlog entries have documented the complete journey from AI development experiment to production-ready infrastructure. The blog system described in these posts is the same system serving these posts—a perfect recursive loop of documentation and implementation.

The meta-achievement: We built a blog to document building the blog, using the blog to host the documentation of its own construction.

Sometimes the most satisfying projects are the ones that become self-documenting.

This post concludes the AI development adventure series, documenting the final bug fix that corrected series navigation behavior. The TursoRepoAdapter now correctly handles all table types, completing our journey from development experiment to production-ready infrastructure.

Every complex system is the sum of all the bugs you’ve fixed along the way.