[Backend] Implement RPC function to retrieve filtering options

[nhphong]

Let's implement an RPC function on Supabase to retrieve filtering options for the Verdad system and display them on the left sidebar of the homepage.

Business Value:

This feature will enhance the user experience by providing dynamic filtering options for users, making it easier to navigate and find relevant content in the Verdad system.

Acceptance Criteria:

1. Functionality:
   • Implement an RPC function on Supabase that returns filtering options in the specified JSON format.
   • The function should handle pagination for labels with a default label_page of 0 and label_page_size of 5.
   • Ensure the labels are sorted based on their created_at timestamp.

2. Response Format:

   • The RPC function should return a JSON response with the following structure:

     {
      "labels": {
          "items": [
              {"label": "Sample Label 1", "value": "uuid-1"},
              {"label": "Sample Label 2", "value": "uuid-2"}
          ],
          "page_size": 2,
          "total_pages": 13,
          "current_page": 0
      },
      "states": [
          {"label": "State 1", "value": "State 1"},
          {"label": "State 2", "value": "State 2"}
      ],
      "sources": [
          {"label": "Source 1", "value": "Source 1"},
          {"label": "Source 2", "value": "Source 2"}
      ],
      "labeledBy": [
          {"label": "by Me", "value": "by_me"},
          {"label": "by Others", "value": "by_others"}
      ],
      "languages": [
          {"label": "English", "value": "English"},
          {"label": "Spanish", "value": "Spanish"}
      ],
      "starredBy": [
          {"label": "by Me", "value": "by_me"},
          {"label": "by Others", "value": "by_others"}
      ]
     }

3. Localization:
   • Default the language to English.
4. Pagination:
   • Implement a mechanism for pagination of labels, including support for a "Show more" button as per the Figma design.

Technical Notes:

• Utilize Supabase's RPC capabilities to create a performant and scalable solution.
• Consider future enhancements such as displaying the number of snippets next to each label and sorting labels by snippet count.

Tasks:

• [ ] Design and implement the RPC function on Supabase.
• [ ] Ensure the function adheres to the desired JSON response format.
• [ ] Implement sorting of labels by created_at.
• [ ] Set up pagination logic for labels.
• [ ] Write tests to validate the functionality and response format.
• [ ] Document the function and its usage.

Considerations for Future Enhancements:

• Display the number of snippets next to each label.
• Implement sorting of labels based on snippet count.

[linear[bot]]

VER-136 [Backend] Implement RPC function to retrieve filtering options