This commit introduces a new file `pagination.py` that implements a flexible pagination system for SQLAlchemy queries. It supports both cursor-based and offset-based pagination, allowing for efficient data retrieval. The `PaginationResult` class is defined to encapsulate the results, including items, total count, next cursor, and items per page. The `paginate_query` function handles the logic for pagination based on the provided parameters.

This update simplifies the pagination implementation by removing unnecessary code related to cursor-based pagination and consolidating the logic for both cursor and offset pagination.

…nation prototype):

- Simplified pagination in the `api_bulletins` function by removing the page parameter and utilizing cursor-based pagination exclusively.
- Enhanced the response structure to directly return pagination results using the new `to_dict` method from `PaginationResult`.
- Updated the frontend to handle pagination more effectively, including a new computed property for item length and improved loading behavior.
- Improved error handling and loading state management during data fetching.

This update streamlines the API and enhances the user experience with more efficient data retrieval.

Unified query conditions into a single list for clarity.
Streamlined parameter handling for consistent filters.
Simplified return structure, ensuring compatibility.
Enhances search functionality and supports future updates.

…y handling

- Enhanced the `api_bulletins` function to streamline query execution and pagination logic, utilizing cursor-based pagination exclusively.
- Updated the response structure to include total count, pagination details, and items in a more efficient format.
- Refactored the `SearchUtils` class to simplify nested query handling, allowing for clearer and more maintainable query construction.
- Improved overall performance and clarity in the bulletin search functionality, setting the stage for future enhancements.

… 2.x support

…rameters and improved clarity in pagination metadata handling.

… logic.

… support

…earch

- Enhanced nested queries handling:  combine conditions from multiple queries, allowing for clearer and more maintainable query construction.
- Streamlined the handling of logical operations (`and`/`or`) when processing nested queries.

…alization, jsonify

This reverts commit 48b400f.

…rformance

(Backward ID index scan)

…model for search-critical foreign keys (assigned_to_id, first_peer_reviewer_id, second_peer_reviewer_id) - Add indexes to Event model for search-critical foreign keys (location_id, eventtype_id) - Create migration script with production-safe CONCURRENTLY indexes - Include junction table indexes for many-to-many search relationships - Based on BulletinSearchBox.js search pattern analysis

…or model for search-critical foreign keys (assigned_to_id, first_peer_reviewer_id, second_peer_reviewer_id, origin_place_id) - Create migration script with production-safe CONCURRENTLY indexes - Include junction table indexes for actor many-to-many search relationships - Based on ActorSearchBox.js search pattern analysis

…Incident model for search-critical foreign keys (assigned_to_id, first_peer_reviewer_id, second_peer_reviewer_id) - Create migration script with production-safe CONCURRENTLY indexes - Include junction table indexes for incident many-to-many search relationships - Based on IncidentSearchBox.js search pattern analysis

…earch-v4

…earch-v4

…t to valid config value

Add permission checks to search endpoints before returning item data.
Users without access now get restricted_json() instead of full details.

Fixes issue where users could see titles, assigned users, and status
of restricted bulletins, actors, and incidents in search results.

Replace NOT ILIKE ALL() with individual indexed searches to resolve
21-42 second timeouts. Uses subquery approach enabling GIN trigram
index usage for better performance.

Use direct NOT ILIKE conditions instead of NOT IN subquery
to avoid correlated subquery performance issues. Generates
WHERE NOT (search ILIKE '%term1%') AND NOT (search ILIKE '%term2%')
instead of WHERE id NOT IN (SELECT id FROM ... OR ...).

…cessary direct NOT conditions and optimized the exclude logic for better performance.

…-v4 features

Replace slow NOT IN subquery pattern with direct NOT ILIKE ALL
array syntax to leverage GIN trigram index efficiently.

Performance improvement: 34 seconds -> ~400ms for complex exclusions

Use fast array containment (@>) for exact tag matches (0.06ms vs 1.8s)
while preserving ILIKE wildcards for partial searches when needed.

Performance improvement: 30,000x faster for exact tag searches

- Exact Match checked: Use fast array containment (@>) for 9533x performance improvement
- Exact Match unchecked: Use ILIKE for partial/wildcard matching behavior
- Preserve Any checkbox functionality for OR vs AND logic

Performance: 572ms -> 0.06ms for exact tag searches