Category & Location Index Queries Deep Dive

Comprehensive reference for all location index and item audit query functions, including geo-spatial lookups, bounding box queries, batch indexing, audit logging, and item history tracking.

Overview

The category/location query layer manages two related indexing systems:

• location-index.queries.ts -- Geo-spatial index for fast location-based item queries (city, country, bounding box, remote items), with batch upsert and metadata tracking
• item-audit.queries.ts -- Item audit trail for tracking all changes, status transitions, and admin actions on items

The location index table serves as a secondary index for fast geo queries. The source of truth for item location data remains in YAML files in the Git-based CMS.

Source Files

lib/db/queries/location-index.queries.ts
lib/db/queries/item-audit.queries.ts

---

Function Reference: location-index.queries.ts

Types

interface UpsertLocationIndexParams {
  itemSlug: string;
  latitude: number;
  longitude: number;
  address?: string | null;
  city?: string | null;
  state?: string | null;
  country?: string | null;
  postalCode?: string | null;
  serviceArea?: string | null;
  isRemote?: boolean;
}

interface LocationQueryParams {
  minLat?: number;
  maxLat?: number;
  minLng?: number;
  maxLng?: number;
  city?: string;
  country?: string;
  isRemote?: boolean;
  limit?: number;
  offset?: number;
}

interface LocationIndexStats {
  totalIndexed: number;
  lastIndexedAt: Date | null;
  lastRebuildAt?: Date | null;
  citiesCount: number;
  countriesCount: number;
  remoteCount: number;
}

Write Operations

upsertLocationIndex

Creates or updates a location index entry. Uses ON CONFLICT DO UPDATE on itemSlug for idempotent upserts.

async function upsertLocationIndex(
  params: UpsertLocationIndexParams
): Promise<ItemLocationIndex>

SQL Pattern:

INSERT INTO item_location_index (item_slug, latitude, longitude, ...)
VALUES (?, ?, ?, ...)
ON CONFLICT (item_slug) DO UPDATE SET
  latitude = ?, longitude = ?, ...
RETURNING *;

Normalization: City and country values are stored in both original and normalized (lowercased, trimmed) forms. The normalized columns are indexed for fast case-insensitive queries.

---

batchUpsertLocationIndex

Batch upserts multiple location entries in chunks of 100 to avoid query size limits.

async function batchUpsertLocationIndex(
  entries: UpsertLocationIndexParams[]
): Promise<number>

Parameters:

Parameter	Type	Required	Description
entries	UpsertLocationIndexParams[]	Yes	Array of location data

Returns: number -- Count of entries processed

SQL Pattern: Uses excluded.* syntax for bulk upsert:

INSERT INTO item_location_index (...)
VALUES (...), (...), (...)
ON CONFLICT (item_slug) DO UPDATE SET
  latitude = excluded.latitude,
  longitude = excluded.longitude, ...;

Performance Notes:

• Processes in batches of 100 to stay within PostgreSQL query size limits
• Uses excluded.* references for efficient bulk conflict resolution
• Sets indexedAt to current timestamp for each entry

---

removeFromLocationIndex

Removes a single item from the location index.

async function removeFromLocationIndex(itemSlug: string): Promise<boolean>

---

batchRemoveFromLocationIndex

Removes multiple items from the location index.

async function batchRemoveFromLocationIndex(
  itemSlugs: string[]
): Promise<number>

---

clearLocationIndex

Clears the entire location index. Used during full rebuilds.

async function clearLocationIndex(): Promise<number>

Returns: Number of entries deleted.

---

Read Operations

getLocationBySlug

Gets a location entry by item slug.

async function getLocationBySlug(
  itemSlug: string
): Promise<ItemLocationIndex | null>

---

getLocationsBySlugs

Gets location entries for multiple items.

async function getLocationsBySlugs(
  itemSlugs: string[]
): Promise<ItemLocationIndex[]>

---

queryByBoundingBox

Queries items within a geographic bounding box. Useful for initial radius filtering before precise distance calculation.

async function queryByBoundingBox(
  params: LocationQueryParams
): Promise<ItemLocationIndex[]>

Parameters:

Parameter	Type	Required	Description
minLat	number	No	Minimum latitude
maxLat	number	No	Maximum latitude
minLng	number	No	Minimum longitude
maxLng	number	No	Maximum longitude
limit	number	No	Max results
offset	number	No	Pagination offset

SQL Pattern:

SELECT * FROM item_location_index
WHERE latitude >= ? AND latitude <= ?
  AND longitude >= ? AND longitude <= ?
LIMIT ? OFFSET ?;

---

queryByCity

Queries items by city name using the indexed normalized column.

async function queryByCity(
  city: string,
  includeRemote: boolean = false
): Promise<string[]>

Returns: Array of item slugs matching the city

SQL Pattern:

SELECT item_slug FROM item_location_index
WHERE city_normalized = ?
  AND is_remote = false;

Performance Notes: Uses equality on the city_normalized indexed column (not ILIKE) for optimal query performance.

---

queryByCountry

Queries items by country name using the indexed normalized column.

async function queryByCountry(
  country: string,
  includeRemote: boolean = false
): Promise<string[]>

---

queryRemoteItems

Queries all remote-only items.

async function queryRemoteItems(): Promise<string[]>

---

getAllIndexedSlugs

Gets all indexed item slugs.

async function getAllIndexedSlugs(): Promise<string[]>

---

getAllLocationEntries

Gets all location entries (for distance calculations on the application layer).

async function getAllLocationEntries(): Promise<ItemLocationIndex[]>

---

getRemoteLocationEntries

Gets remote-only location entries with minimal fields (slug, city, country). Optimized for radius queries.

async function getRemoteLocationEntries(): Promise<RemoteLocationEntry[]>

Distinct Value Queries

getDistinctCities

Gets all distinct city names, deduplicated by normalized form.

async function getDistinctCities(): Promise<string[]>

SQL Pattern:

SELECT MIN(city) FROM item_location_index
WHERE city_normalized IS NOT NULL
GROUP BY city_normalized
ORDER BY MIN(city);

Design Note: Groups by normalized column but returns the MIN() of the original (which favors capitalized forms), providing clean display values.

---

getDistinctCountries

Gets all distinct country names, deduplicated by normalized form.

async function getDistinctCountries(): Promise<string[]>

---

Statistics and Metadata

getLocationIndexStats

Gets statistics about the location index.

async function getLocationIndexStats(): Promise<LocationIndexStats>

Returns: { totalIndexed, lastIndexedAt, citiesCount, countriesCount, remoteCount }

---

updateLocationIndexMeta

Updates the singleton metadata row after a rebuild. Uses upsert pattern.

async function updateLocationIndexMeta(
  rebuildAt: Date,
  durationMs: number,
  itemCount: number
): Promise<void>

---

getLocationIndexMeta

Gets the location index metadata (last rebuild time, duration, item count).

async function getLocationIndexMeta(): Promise<LocationIndexMeta | null>

---

Function Reference: item-audit.queries.ts

Types

type ItemAuditLogWithPerformer = ItemAuditLog & {
  performer: { id: string; email: string | null } | null;
};

interface CreateItemAuditLogParams {
  itemId: string;
  itemName: string;
  action: ItemAuditActionValues;
  previousStatus?: string | null;
  newStatus?: string | null;
  changes?: ItemAuditChanges | null;
  performedBy?: string | null;
  performedByName?: string | null;
  notes?: string | null;
  metadata?: Record<string, unknown> | null;
}

interface PaginatedItemHistory {
  logs: ItemAuditLogWithPerformer[];
  total: number;
  page: number;
  limit: number;
  totalPages: number;
}

createItemAuditLog

Creates a new item audit log entry.

async function createItemAuditLog(
  data: CreateItemAuditLogParams
): Promise<ItemAuditLog>

---

getItemHistory

Gets paginated audit history for an item with optional action type filtering.

async function getItemHistory(params: {
  itemId: string;
  page?: number;
  limit?: number;
  actionFilter?: ItemAuditActionValues[];
}): Promise<PaginatedItemHistory>

SQL Pattern:

SELECT item_audit_logs.*, users.id, users.email
FROM item_audit_logs
LEFT JOIN users ON item_audit_logs.performed_by = users.id
WHERE item_audit_logs.item_id = ?
  AND item_audit_logs.action IN (...)
ORDER BY item_audit_logs.created_at DESC
LIMIT ? OFFSET ?;

---

getLatestItemAuditLog

Gets the most recent audit log for an item.

async function getLatestItemAuditLog(
  itemId: string
): Promise<ItemAuditLog | null>

---

getAuditLogsByAction

Gets audit logs filtered by action type.

async function getAuditLogsByAction(
  action: ItemAuditActionValues,
  limit: number = 50
): Promise<ItemAuditLog[]>

---

getAuditLogsByPerformer

Gets audit logs filtered by the user who performed the action.

async function getAuditLogsByPerformer(
  performedBy: string,
  limit: number = 50
): Promise<ItemAuditLog[]>

---

getItemAuditStats

Gets audit log statistics for an item, grouped by action type.

async function getItemAuditStats(
  itemId: string
): Promise<Record<string, number>>

SQL Pattern:

SELECT action, count(*) FROM item_audit_logs
WHERE item_id = ?
GROUP BY action;

---

Performance Notes

1. Normalized columns -- Location queries use pre-normalized city_normalized and country_normalized columns with equality operators instead of ILIKE, providing index-friendly lookups.

2. Batch processing -- batchUpsertLocationIndex processes in chunks of 100 to avoid PostgreSQL query size limits while maintaining throughput.

3. Singleton metadata -- updateLocationIndexMeta uses upsert on a fixed 'singleton' ID, avoiding race conditions during concurrent rebuilds.

4. excluded.* references -- Batch upserts use PostgreSQL's excluded pseudo-table for conflict resolution, avoiding the need to construct separate update values.

5. LEFT JOIN for performers -- Audit log queries use LEFT JOIN on users to handle cases where the performing user may have been deleted.

Usage Examples

Rebuilding the location index

import {
  clearLocationIndex,
  batchUpsertLocationIndex,
  updateLocationIndexMeta,
} from '@/lib/db/queries';

const startTime = Date.now();

await clearLocationIndex();

const entries = items.map(item => ({
  itemSlug: item.slug,
  latitude: item.location.lat,
  longitude: item.location.lng,
  city: item.location.city,
  country: item.location.country,
  isRemote: item.isRemote,
}));

const processed = await batchUpsertLocationIndex(entries);

await updateLocationIndexMeta(new Date(), Date.now() - startTime, processed);

Querying items near a location

import { queryByBoundingBox } from '@/lib/db/queries';

// Find items within a bounding box around New York
const items = await queryByBoundingBox({
  minLat: 40.5,
  maxLat: 41.0,
  minLng: -74.3,
  maxLng: -73.7,
  limit: 50,
});

Logging an item status change

import { createItemAuditLog } from '@/lib/db/queries';

await createItemAuditLog({
  itemId: 'clockify',
  itemName: 'Clockify',
  action: 'status_change',
  previousStatus: 'draft',
  newStatus: 'published',
  performedBy: adminUserId,
  performedByName: 'Admin User',
  notes: 'Approved after review',
});