Filtering System with nuqs Integration

Overview

CONA uses a sophisticated filtering system that combines nuqs for URL state management with Prisma database queries. This system provides type-safe, URL-synchronized filtering across all data tables in the application.

Architecture

Core Components

nuqs - Handles URL state management and type-safe query parameter parsing
Filter Mapping - Maps URL parameters to database fields and types
Filter Utils - Processes and applies filters to Prisma queries
Filter Components - UI components for different filter types

Flow Diagram

URL Parameters → nuqs → Search Actions → Filter Utils → Prisma Query → Database
                ↓
         UI Components (synchronized)

nuqs Integration

Search Parameters Configuration

The core nuqs configuration is in apps/webapp/app/lib/search-params-config.ts:

import {
  createSearchParamsCache,
  parseAsString,
  parseAsBoolean,
  parseAsInteger,
} from "nuqs/server";

export const searchParamsCache = createSearchParamsCache(
  {
    // Pagination
    page: parseAsInteger.withDefault(1),
    pageSize: parseAsInteger.withDefault(10),

    // Filtering
    filter: parseAsBoolean.withDefault(false),
    tab: parseAsString.withDefault("published"),

    // Sorting
    sort: parseAsString.withDefault(""),
    asc: parseAsBoolean.withDefault(true),

    // Dynamic filters handled separately in actions
  },
  {
    urlKeys: {
      pageSize: "size", // URL shows 'size' but internally uses 'pageSize'
    },
  }
);

URL Parameter Structure

The filtering system uses a specific URL parameter structure:

/sales-order?filter=true&doc_date=2025-07-07&doc_date_op=equals&status=Draft&page=1&size=10

Parameter Types:

filter=true - Enables filtering
{field}={value} - Filter value
{field}_op={operator} - Filter operator (optional)
page={number} - Pagination page
size={number} - Page size
sort={field} - Sort field
asc={boolean} - Sort direction

Filter Types and Operators

String Filters

Available Operators:

equals - Exact match
contains - Partial match (default)
startsWith - Starts with value
endsWith - Ends with value
not - Not equal to

Examples:

?filter=true&doc_nr=INV-001&doc_nr_op=equals
?filter=true&doc_receiver_name=John&doc_receiver_name_op=contains

Date Filters

Available Operators:

equals - Exact date match (default)
gte - Greater than or equal
lte - Less than or equal
gt - Greater than
lt - Less than
between - Date range

Timezone-Aware Date Handling: Date filters now properly handle timezones using the organization’s default timezone. When users enter dates like 2025-07-07, the system:

Gets the organization’s default timezone from subsidiary settings
Interprets the date in that timezone (e.g., 2025-07-07 in Europe/Berlin)
Converts the full day range to UTC for database queries
Ensures users get results for the complete day in their timezone

Example:

User selects: 2025-07-07
Organization timezone: Europe/Berlin (UTC+1)
System converts: 2025-07-06T23:00:00Z to 2025-07-07T22:59:59Z (UTC)
Result: All documents created on July 7th Berlin time

Examples:

?filter=true&doc_date=2025-07-07                    # Equals (default)
?filter=true&doc_date=2025-07-07&doc_date_op=gte    # From date
?filter=true&doc_date=2025-07-01,2025-07-31         # Date range (auto-detects 'between')

Number Filters

Available Operators:

equals - Exact match (default)
gt - Greater than
gte - Greater than or equal
lt - Less than
lte - Less than or equal
between - Number range

Examples:

?filter=true&doc_total_amount=100.50                   # Equals
?filter=true&doc_total_amount=100&doc_total_amount_op=gte  # Greater than or equal
?filter=true&doc_total_amount=100,500                 # Range (auto-detects 'between')

Array Filters

Available Operators:

in - Contains any of the values (default)

Examples:

?filter=true&doc_tags=Important,Urgent&doc_tags_op=in

Boolean Filters

Available Operators:

equals - Exact match (default)
not - Not equal to

Examples:

?filter=true&is_credit=true
?filter=true&is_credit=false&is_credit_op=not

Filter Mapping

The filter-mapping.ts file defines how URL parameters map to database fields:

export const filterMapping: Record<string, FilterMappingConfig> = {
  doc_date: {
    field: "date",           // Database field
    type: "date",            // Filter type
    filterType: "date",      // UI component type
  },
  doc_nr: {
    field: "nr",
    type: "string",
    filterType: "text",
  },
  status: {
    field: "status.label",   // Nested field
    type: "string",
    filterType: "select",
  },
  // ... more mappings
};

Implementation in Search Actions

Basic Search Action Structure

"use server";

import { searchParamsCache } from "@/app/lib/search-params-config";
import { applyFilter } from "@/app/lib/utils/filter-utils";
import { filterMapping } from "@/app/lib/utils/filter-mapping";

export async function searchDocuments({
  objectTypeSlug,
  searchParams,
}: SearchDocumentsParams) {
  // 1. Parse nuqs parameters
  await searchParamsCache.parse(searchParams);
  const params = searchParamsCache.all();

  // 2. Build base where clause
  let where: DocumentWhereInput = {
    is_deleted: false,
    object_type: { slug: objectTypeSlug },
  };

  // 3. Apply filters if enabled
  if (params.filter) {
    Object.entries(searchParams).forEach(([key, value]) => {
      if (key in filterMapping && value) {
        const config = filterMapping[key];
        const operator = searchParams[`${key}_op`] || getDefaultOperator(config.type);
        
        where = applyFilter(where, config.field, value, operator);
      }
    });
  }

  // 4. Execute query
  const documents = await prisma.documents.findMany({ where });
  
  return { success: true, data: documents };
}

Default Operator Logic

The system automatically determines operators based on field types:

function getDefaultOperator(type: string, value: any): string {
  switch (type) {
    case "string":
      return "contains";
    case "date":
      return value.includes(",") ? "between" : "equals";
    case "number":
      return value.includes(",") ? "between" : "equals";
    case "array":
      return "in";
    case "boolean":
      return "equals";
    default:
      return "contains";
  }
}

Timezone-Aware Date Processing

The filtering system now properly handles timezone conversion using date-fns-tz:

How It Works

Get Organization Timezone: Retrieves default timezone from subsidiary settings
Parse User Input: User-entered dates are interpreted in the organization’s timezone
Convert to UTC: Uses proper timezone conversion with DST handling
Database Queries: All date comparisons are done in UTC with correct ranges
Consistent Results: Users get results for complete days in their timezone

Implementation

// In search-documents.ts
const subsidiaryResult = await getDefaultSubsidiaryDetails({ organizationId });
const timezone = subsidiaryResult.success ? subsidiaryResult.data?.timezone || "UTC" : "UTC";

// Pass timezone to filter processing
where = applyFilter(where, config.field, value, operator, timezone);

// In filter-utils.ts using @cona/utils
import { convertUserDateToUtcRange, convertUserDateRangeToUtc } from "@cona/utils";

// Convert user date to proper UTC range
const { startOfDay, endOfDay } = convertUserDateToUtcRange(year, month, day, timezone || "UTC");

Date Processing Example

// User enters: 2025-07-07
// Organization timezone: Europe/Berlin (UTC+1)
// System converts to UTC range: 
//   Start: 2025-07-06T23:00:00.000Z
//   End: 2025-07-07T22:59:59.999Z
// Database query: WHERE date >= '2025-07-06T23:00:00.000Z' AND date <= '2025-07-07T22:59:59.999Z'
// Result: All documents created on July 7th Berlin time

Benefits

Timezone Accuracy: Proper handling of user timezones with DST support
User-Friendly: Users see results for complete days in their timezone
Robust: Uses date-fns-tz for reliable timezone conversion
Consistent: Same behavior across different deployments and timezones

Custom Properties Filtering

The system supports filtering by custom properties using a special syntax:

// URL: ?filter=true&custom_invoice_type=recurring
// This searches for documents with custom_properties.invoice_type.value = "recurring"

if (key.startsWith("custom_") && value) {
  const customPropertyKey = key.replace("custom_", "");
  const andConditions = Array.isArray(where.AND) ? where.AND : [];
  
  andConditions.push({
    custom_properties: {
      path: [customPropertyKey, "value"],
      string_contains: typeof value === "string" ? value : String(value),
    },
  });
  
  where.AND = andConditions;
}

UI Components

Filter Input Components

Each filter type has its own component:

TextFilter - For string fields
NumberFilter - For numeric fields
DateFilter - For date fields
SelectFilter - For dropdown selections
CreditDebitFilter - For boolean credit/debit fields

Base Filter Props

All filter components share common props:

interface BaseFilterProps<TData, TValue> {
  column: { columnDef: CustomColumnDef<TData, TValue> };
  value: string | null;
  setValue: (value: string | null) => void;
  operator?: string | null;
  setOperator?: (operator: string | null) => void;
  isChanging: boolean;
  setIsChanging: (isChanging: boolean) => void;
}

Example Filter Component

export function DateFilter<TData, TValue>({
  value,
  setValue,
  operator,
  setOperator,
}: BaseFilterProps<TData, TValue>) {
  return (
    <div className="flex gap-0">
      <Select
        value={operator ?? "equals"}
        onValueChange={setOperator}
      >
        <SelectTrigger>
          <SelectValue>{operatorSymbols[operator]}</SelectValue>
        </SelectTrigger>
        <SelectContent>
          <SelectItem value="equals">Equal to =</SelectItem>
          <SelectItem value="gt">After &gt;</SelectItem>
          <SelectItem value="gte">From ≥</SelectItem>
          <SelectItem value="lt">Before &lt;</SelectItem>
          <SelectItem value="lte">Until ≤</SelectItem>
          <SelectItem value="between">Between ↔</SelectItem>
        </SelectContent>
      </Select>
      
      <DatePicker
        value={value}
        onChange={setValue}
      />
    </div>
  );
}

Data Processing

Filter Utils

The filter-utils.ts file handles the core filtering logic:

export function applyFilter(
  where: WhereInput,
  field: string,
  value: any,
  operator: string = "contains"
): WhereInput {
  // 1. Parse and validate value
  const fieldType = getFieldType(field);
  let processedValue = processValue(value, fieldType);

  // 2. Apply operator
  const operatorValue = applyOperator(processedValue, operator, fieldType);

  // 3. Handle nested fields
  if (field.includes(".")) {
    return buildNestedWhere(where, field, operatorValue);
  }

  // 4. Return updated where clause
  return { ...where, [field]: operatorValue };
}

Value Processing

Values are processed based on their type:

function processValue(value: any, fieldType: string): any {
  switch (fieldType) {
    case "date":
      if (typeof value === "string") {
        const [year, month, day] = value.split("-").map(Number);
        return new Date(Date.UTC(year, month - 1, day));
      }
      return value;
      
    case "number":
      if (typeof value === "string") {
        return parseFloat(value.replace(/[^\d,.,-]/g, "").replace(",", "."));
      }
      return value;
      
    case "boolean":
      return typeof value === "string" ? value.toLowerCase() === "true" : Boolean(value);
      
    default:
      return value;
  }
}

Common Issues and Solutions

Issue 1: Date Filtering Not Working

Problem: Setting doc_date=2025-07-07 doesn’t filter correctly. Solution: Ensure the default operator is equals and timezone is handled properly:

// Fixed in search-documents.ts
operator = "equals";  // Changed from "gte" to "equals"

// Get organization timezone
const subsidiaryResult = await getDefaultSubsidiaryDetails({ organizationId });
const timezone = subsidiaryResult.success ? subsidiaryResult.data?.timezone || "UTC" : "UTC";

// Pass timezone to filter processing
where = applyFilter(where, config.field, value, operator, timezone);

Issue 2: Custom Properties Not Filtering

Problem: Custom property filters don’t work. Solution: Use the custom_ prefix in URL parameters:

?filter=true&custom_invoice_type=recurring

Issue 3: Nested Field Filtering

Problem: Filtering on related entity fields fails. Solution: Use dot notation in filter mapping:

doc_receiver_name: {
  field: "entity.label",  // Nested field
  type: "string",
}

Issue 4: Number Format Issues

Problem: European number formats (1.234,56) not working. Solution: The system handles both formats automatically:

const normalized = value
  .replace(/[^\d,.,-]/g, "")
  .replace(/[.,](?=.*[.,])/g, "");
const numericValue = parseFloat(normalized.replace(",", "."));

Issue 5: Date Filtering and Timezones

Problem: Date filters show incorrect results due to timezone differences. ✅ Solution Implemented: The system now properly handles timezone conversion:

// Implemented timezone-aware filtering:
// 1. Uses date-fns-tz library for accurate timezone conversion
// 2. Gets organization timezone from subsidiary settings
// 3. Converts user dates to proper UTC ranges

// In @cona/utils/src/date.ts
export function convertUserDateToUtcRange(
  year: number,
  month: number,
  day: number,
  timezone: BusinessTimezone = "UTC"
): { startOfDay: Date; endOfDay: Date } {
  // Proper timezone conversion with DST handling
}

// In search actions
const timezone = subsidiaryResult.success ? subsidiaryResult.data?.timezone || "UTC" : "UTC";
where = applyFilter(where, config.field, value, operator, timezone);

Benefits:

Users get complete day results in their timezone
Proper DST handling with date-fns-tz
No more half-day results from neighboring timezones

Best Practices

1. Always Use Filter Mapping

Don’t hardcode field mappings. Use the centralized filterMapping object:

// ✅ Good
const config = filterMapping[key];
where = applyFilter(where, config.field, value, operator);

// ❌ Bad
where = applyFilter(where, "some_field", value, operator);

2. Handle Optional Operators

Always provide fallback operators:

const operator = searchParams[`${key}_op`] || getDefaultOperator(config.type);

3. Validate Filter Values

Check for null/undefined values:

if (key in filterMapping && value) {
  // Apply filter
}

4. Use Consistent URL Keys

Map internal names to user-friendly URL keys:

urlKeys: {
  pageSize: "size",
}

5. Log Filter Operations

Add logging for debugging:

console.log("Processing filter:", { key, value, operator });

6. Handle Timezone for Date Filters

Always retrieve and pass timezone information for accurate date filtering:

// Get organization timezone
const subsidiaryResult = await getDefaultSubsidiaryDetails({ organizationId });
const timezone = subsidiaryResult.success ? subsidiaryResult.data?.timezone || "UTC" : "UTC";

// Pass timezone to filter processing
where = applyFilter(where, config.field, value, operator, timezone);

// User enters: 2025-07-07 in Europe/Berlin
// System processes: 2025-07-06T23:00:00.000Z to 2025-07-07T22:59:59.999Z (UTC)
// Result: Complete day in user's timezone

Testing Filters

Unit Tests

Test the filter utility functions:

describe("applyFilter", () => {
  it("should handle date equals correctly", () => {
    const where = {};
    const result = applyFilter(where, "date", "2025-07-07", "equals");
    
    expect(result.date.gte).toBeDefined();
    expect(result.date.lte).toBeDefined();
  });
});

Integration Tests

Test complete filtering workflows:

describe("Document Filtering", () => {
  it("should filter by date correctly", async () => {
    const result = await searchDocuments({
      objectTypeSlug: "sales-order",
      searchParams: {
        filter: "true",
        doc_date: "2025-07-07",
      },
    });
    
    expect(result.success).toBe(true);
    expect(result.data.documents).toHaveLength(1);
  });
});

Future Enhancements

1. Advanced Operators

Add more sophisticated operators:

regex - Regular expression matching
fuzzy - Fuzzy text matching
geospatial - Location-based filtering

2. Filter Presets

Allow users to save and reuse filter combinations:

const presets = {
  "overdue-invoices": {
    object_type: "sales-invoice",
    doc_due_date_op: "lt",
    doc_due_date: "today",
    status: "Sent",
  },
};

3. Real-time Filtering

Implement debounced real-time filtering:

const debouncedFilter = useDebouncedCallback(
  (filters) => updateFilters(filters),
  500
);

This filtering system provides a powerful, type-safe, and URL-synchronized filtering experience across all CONA data tables. The nuqs integration ensures that filter states are preserved in URLs and can be shared or bookmarked by users.

​Filtering System with nuqs Integration

​Overview

​Architecture

​Core Components

​Flow Diagram

​nuqs Integration

​Search Parameters Configuration

​URL Parameter Structure

​Filter Types and Operators

​String Filters

​Date Filters

​Number Filters

​Array Filters

​Boolean Filters

​Filter Mapping

​Implementation in Search Actions

​Basic Search Action Structure

​Default Operator Logic

​Timezone-Aware Date Processing

​How It Works

​Implementation

​Date Processing Example

​Benefits

​Custom Properties Filtering

​UI Components

​Filter Input Components

​Base Filter Props

​Example Filter Component

​Data Processing

​Filter Utils

​Value Processing

​Common Issues and Solutions

​Issue 1: Date Filtering Not Working

​Issue 2: Custom Properties Not Filtering

​Issue 3: Nested Field Filtering

​Issue 4: Number Format Issues

​Issue 5: Date Filtering and Timezones

​Best Practices

​1. Always Use Filter Mapping

​2. Handle Optional Operators

​3. Validate Filter Values

​4. Use Consistent URL Keys

​5. Log Filter Operations

​6. Handle Timezone for Date Filters

​Testing Filters

​Unit Tests

​Integration Tests

​Future Enhancements

​1. Advanced Operators

​2. Filter Presets

​3. Real-time Filtering

Filtering System with nuqs Integration

Overview

Architecture

Core Components

Flow Diagram

nuqs Integration

Search Parameters Configuration

URL Parameter Structure

Filter Types and Operators

String Filters

Date Filters

Number Filters

Array Filters

Boolean Filters

Filter Mapping

Implementation in Search Actions

Basic Search Action Structure

Default Operator Logic

Timezone-Aware Date Processing

How It Works

Implementation

Date Processing Example

Benefits

Custom Properties Filtering

UI Components

Filter Input Components

Base Filter Props

Example Filter Component

Data Processing

Filter Utils

Value Processing

Common Issues and Solutions

Issue 1: Date Filtering Not Working

Issue 2: Custom Properties Not Filtering

Issue 3: Nested Field Filtering

Issue 4: Number Format Issues

Issue 5: Date Filtering and Timezones

Best Practices

1. Always Use Filter Mapping

2. Handle Optional Operators

3. Validate Filter Values

4. Use Consistent URL Keys

5. Log Filter Operations

6. Handle Timezone for Date Filters

Testing Filters

Unit Tests

Integration Tests

Future Enhancements

1. Advanced Operators

2. Filter Presets

3. Real-time Filtering