We just shipped query_items to ListService — a cross-list search method that lets you query items across all your lists with a single call. It supports 7 filter types, returns results with parent list context, and took 914 lines of new code across implementation and tests.
The TDD audit found 10 bugs across 2 rounds of review. Five were missing test coverage. Five were actual bugs in the service layer. This is the story of what happens when you build query infrastructure that needs to compose filters without leaking data between users.
What Shipped
The query_items method is 85 lines. It queries the list_items table with an inner JOIN to the parent lists table for context, enforces user isolation at the database level, and supports these filters:
• keyword (ILIKE search on content)
• status (PENDING, DONE, CANCELLED)
• due_before / due_after (datetime range)
• theme (filter by list theme)
• list_id (scope to a single list)
• include_archived (default: false)
All filters use AND logic. You pass a ListQueryFilters model with whatever combination you need, and the method chains them together via the Supabase client. Results come back with list_name and list_theme flattened into each item dict.
We wrote 309 lines of tests covering all filter types, multi-list results, user isolation, and archive exclusion. Tests T-28 through T-35 use mocked Supabase clients following the project pattern from earlier item CRUD work.
The Audit: 10 Findings Across 2 Rounds
The first TDD audit round caught 8 issues (F1-F8). Three were missing tests (due_after, list_id, delete_item ownership). Five were actual bugs:
F1: Missing RLS policies. The migration didn't include Row Level Security policies. We caught this before production.
F2: Empty update bug. update_list would send an empty {} payload if all fields were None, causing a false 404 error.
F3: Can't clear optional fields. update_list couldn't set fields like description back to None because of how we checked for None vs not-provided.
F4: Wrong join type. update_items used a left join for ownership checks instead of !inner, risking data leaks.
F8: Empty string validation. ThemeChange.new_theme accepted empty strings when it should have enforced min_length=1.
Round 2 found 2 more issues (R2-F1, R2-F2). Both were variations on the nullable-field clearing problem:
R2-F1: update_items can't clear nullable fields. Same issue as F3 but for items (priority, due_at, follow_up_at). Fixed by using model_fields_set to distinguish "not provided" from "explicitly None".
R2-F2: delete_item join inconsistency. Same fix as F4 — changed from left join to !inner for ownership check.
The Nullable Field Problem
The trickiest bugs were F3 and R2-F1. The issue: how do you tell the difference between a field that wasn't provided in the update payload and a field that was explicitly set to None to clear it?
Pydantic V2 has model_fields_set — a set of field names that were explicitly provided during model construction. We updated both update_list and update_items to check this set when building the update payload. If a field is in model_fields_set and its value is None, we include it in the database update as null. If it's not in the set, we skip it entirely.
This pattern mirrors how we handled the empty-update bug (F2). If no fields are set, we raise a validation error before hitting the database. The audit tests for all three scenarios now live in test_list_service_audit.py and test_list_service_audit_r2.py.
What's Next
The ListService layer is now 906 lines with 1,332 lines of tests across three test modules (test_list_query.py, test_list_service_audit.py, test_list_service_audit_r2.py). We have CRUD for lists, CRUD for items, theme management, reminder cascade, name resolution with fuzzy matching, bulk operations, and now cross-list query.
Next up: integration tests against a live Supabase instance (already shipped for bulk_retheme in PR #291), consolidating the audit test findings into architectural patterns, and wiring this layer into the API endpoints so users can actually use it.
The audit process keeps proving its value. Ten findings across 2 rounds isn't failure — it's the process working. We catch bugs before they leak user data or cause confusing errors in production. That's the whole point.