Skip to content

Add date filtering to /search endpoint #4

@pdugan20

Description

@pdugan20

Problem

The /v1/search endpoint accepts q, domain, limit, and page but has no date filtering. Queries like "find all Pink Floyd listens in 2023" aren't possible without fetching all results and filtering client-side.

Why it's non-trivial

The search_index FTS5 table doesn't store timestamps. Adding date support requires one of:

  1. Join FTS results back to source tables to check timestamps -- works but is slow and complex (different timestamp columns per domain)
  2. Add a date column to search_index -- cleaner, but requires a migration, backfilling existing rows, and updating all sync indexing logic to populate the new column

Recommended approach

Option 2 (add timestamp to search index):

  • Add occurred_at column to search_index table
  • Migration to backfill from source tables (scrobbles, activities, watch_history, collection)
  • Update all sync services that call insertSearchIndex to include the timestamp
  • Add from/to query params to /search route schema and handler
  • Use occurred_at >= ? AND occurred_at <= ? alongside the FTS5 MATCH query

Context

Deferred from the date-filtering project (see docs/projects/date-filtering/). All other endpoints now support date/from/to filtering.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions