Search & Map Browsing — Frontpics Image Marketplace
Phase 1: PostgreSQL Full-Text Search
How it works
PostgreSQL tsvector + tsquery provides fast full-text search without any external dependencies. This is the MVP approach that works well for hundreds of thousands of images.
A weighted search_vector column on the images table combines title (weight A, highest priority), description (weight B), and tag names (weight C). The vector is maintained by a database trigger that fires on INSERT or UPDATE of the relevant columns.
-- Weighted search vector trigger (see database-schema for full SQL)
NEW.search_vector :=
setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(NEW.description, '')), 'B');
-- Tags are concatenated in a separate step after image_tags updates
A GIN index on search_vector makes full-text queries fast even at 500,000+ rows.
Search Query (Prisma raw)
// apps/api/src/modules/images/images.service.ts
async search(query: ImageQueryDto) {
const {
q, city, tags, isDrone,
minPrice, maxPrice, licenseId,
limit = 20, cursor, sort = 'newest',
} = query;
// Build WHERE clauses
const conditions: Prisma.Sql[] = [
Prisma.sql`i.status = 'active'`,
Prisma.sql`i.deleted_at IS NULL`,
];
if (q) {
conditions.push(
Prisma.sql`i.search_vector @@ plainto_tsquery('english', ${q})`
);
}
if (city) {
conditions.push(Prisma.sql`l.city ILIKE ${`%${city}%`}`);
}
if (isDrone !== undefined) {
conditions.push(Prisma.sql`i.is_drone = ${isDrone}`);
}
if (minPrice !== undefined) {
conditions.push(Prisma.sql`i.base_price_credits >= ${minPrice}`);
}
if (maxPrice !== undefined) {
conditions.push(Prisma.sql`i.base_price_credits <= ${maxPrice}`);
}
if (tags?.length) {
// Images must have ALL specified tags (AND logic)
conditions.push(Prisma.sql`
(SELECT COUNT(*) FROM image_tags it2
JOIN tags t2 ON t2.id = it2.tag_id
WHERE it2.image_id = i.id AND t2.slug = ANY(${tags}::text[])
) = ${tags.length}
`);
}
if (cursor) {
conditions.push(Prisma.sql`i.id < ${cursor}`); // keyset pagination
}
const orderByMap = {
newest: Prisma.sql`i.created_at DESC`,
price_asc: Prisma.sql`i.base_price_credits ASC`,
price_desc: Prisma.sql`i.base_price_credits DESC`,
popular: Prisma.sql`i.download_count DESC`,
};
const where = Prisma.join(conditions, ' AND ');
const orderBy = orderByMap[sort] ?? orderByMap.newest;
const rows = await this.db.$queryRaw<ImageSearchRow[]>`
SELECT
i.id, i.title, i.is_drone, i.base_price_credits,
i.created_at, i.download_count,
f.cdn_url AS thumbnail_url,
l.city, l.country_code,
ts_rank(i.search_vector, plainto_tsquery('english', ${q ?? ''})) AS rank
FROM images i
LEFT JOIN locations l ON l.id = i.location_id
LEFT JOIN image_files f ON f.image_id = i.id AND f.variant = 'thumbnail'
WHERE ${where}
ORDER BY ${orderBy}
LIMIT ${limit + 1}
`;
const hasMore = rows.length > limit;
const data = hasMore ? rows.slice(0, limit) : rows;
const nextCursor = hasMore ? data[data.length - 1].id : null;
return { data, nextCursor };
}
Why keyset pagination instead of OFFSET?
OFFSET 10000 requires PostgreSQL to scan and discard 10,000 rows. Keyset pagination (WHERE id < lastId) uses the index directly — constant time regardless of page depth. At 100k+ images this matters.
Search response caching
Popular queries (no q, browsing by city or tags) are cached in Redis for 60 seconds:
Key: images:search:{hash of query params}
TTL: 60 seconds
This absorbs repeated loads from the browse page without hitting Postgres.
Phase 2: Map Browsing with PostGIS
Overview
PostGIS allows spatial queries like "give me all images where the GPS coordinate falls inside this bounding box." Combined with client-side clustering, this powers an intuitive map browsing interface.
Bounding Box Query
GET /api/v1/images/map?south=30.0&west=-97.9&north=30.5&east=-97.5
async getMapImages(bbox: BboxDto) {
const { south, west, north, east } = bbox;
const rows = await this.db.$queryRaw<MapImageRow[]>`
SELECT
i.id,
i.base_price_credits,
i.is_drone,
f.cdn_url AS thumbnail_url,
ST_X(l.coordinates::geometry) AS longitude,
ST_Y(l.coordinates::geometry) AS latitude
FROM images i
JOIN locations l ON l.id = i.location_id
JOIN image_files f ON f.image_id = i.id AND f.variant = 'thumbnail'
WHERE
i.status = 'active'
AND l.coordinates IS NOT NULL
AND ST_Within(
l.coordinates::geometry,
ST_MakeEnvelope(${west}, ${south}, ${east}, ${north}, 4326)
)
LIMIT 2000
`;
return rows;
}
The API returns up to 2,000 points per bounding box call. The client clusters them.
Frontend: MapLibre GL JS + Supercluster
// apps/web/src/components/images/MapView.tsx - conceptual
'use client';
import Map, { Source, Layer } from 'react-map-gl/maplibre';
import Supercluster from 'supercluster';
export function MapView() {
const [bounds, setBounds] = useState<LngLatBounds>();
const { data: points } = useQuery({
queryKey: ['map-images', bounds],
queryFn: () => fetchMapImages(bounds),
enabled: !!bounds,
});
const { clusters } = useSupercluster({
points,
bounds,
zoom,
options: { radius: 75, maxZoom: 17 },
});
return (
<Map
mapStyle="https://demotiles.maplibre.org/style.json" // OpenStreetMap tiles
onMoveEnd={(e) => setBounds(e.target.getBounds())}
>
{clusters.map((cluster) =>
cluster.properties.cluster ? (
<ClusterMarker key={cluster.id} cluster={cluster} />
) : (
<ImageMarker key={cluster.properties.imageId} point={cluster} />
),
)}
</Map>
);
}
Map tile provider options (all free/open):
- OpenStreetMap via
demotiles.maplibre.org— free, no key required, limited SLA. - Stadia Maps — free tier, MapLibre compatible, good quality.
- Protomaps — self-hosted
.pmtilesbundle, zero external dependency, best for production. - Cloudflare's Workers map — custom tiles if needed later.
For MVP Phase 2, Stadia Maps free tier is recommended.
Search UX Design
Browse Page (/browse)
┌────────────────────────────────────────────────────────────────┐
│ [Search... ] [City ▼] [Tags ▼] [Drone only] [Price ▼] 🗺 Map │
├────────────────────────────────────────────────────────────────┤
│ │
│ ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐ │
│ │ │ │ │ │ │ │ │ │ │ │ │ │
│ │ IMG │ │ IMG │ │ IMG │ │ IMG │ │ IMG │ │ IMG │ │
│ │ │ │ │ │ │ │ │ │ │ │ │ │
│ │🚁 15¢│ │ 8¢ │ │🚁 22¢│ │ 5¢ │ │🚁 30¢│ │ 12¢ │ │
│ └──────┘ └──────┘ └──────┘ └──────┘ └──────┘ └──────┘ │
│ │
│ [Load more] │
└────────────────────────────────────────────────────────────────┘
- Images lazy-load with blur placeholder (Next.js
Imagecomponent withplaceholder="blur"). - Infinite scroll via
IntersectionObserver+ TanStack Query'suseInfiniteQuery. - Filters update query params in URL (shareable, bookmarkable).
- Drone images marked with helicopter icon.
Map View (Phase 2)
- Toggle between grid view and map view at top right.
- Clicking a cluster zooms in.
- Clicking a single marker shows a popover with thumbnail + price + "View image" link.
- Map state (center, zoom) synced with URL params.
Future: OpenSearch (Phase 3)
When PostgreSQL full-text search becomes insufficient (millions of images, complex relevance tuning, faceted aggregations), migrate to OpenSearch:
- Mirror strategy: Prisma event hooks or CDC (Debezium) stream image updates to OpenSearch.
- Indices:
imagesindex with all searchable fields, tags as nested objects, location asgeo_point. - Relevance: BM25 scoring, field boosts, synonym support.
- Aggregations: facet counts for tags, cities, price ranges — enables sidebar filter counters.
- API change: swap the
images.service.tssearch implementation, same API contract.
The PostgreSQL → OpenSearch migration is a zero-downtime swap because the database stays as the source of truth and OpenSearch is a pure read replica.