Build a Classifieds in Replit

TL;DR

Build a Craigslist-style classifieds platform in Replit in 1-2 hours using Express, PostgreSQL, and Drizzle ORM. You'll get category-based listing creation, 30-day auto-expiration, full-text search, contact form with email relay, and a moderation queue for flagged listings.

What you're building

A classifieds platform is a local marketplace for buying, selling, and renting. Unlike a product e-commerce store, listings are user-submitted, expire after a fixed period, and are organized into a hierarchical category tree (e.g., Electronics → Phones → iPhones). Sellers control contact preference — they can choose to show their email, phone, or both.

Replit Agent creates the Express + Drizzle foundation with the category and listing schema. The technically interesting parts are built step by step: the recursive CTE that traverses the category tree so browsing 'Electronics' shows all phones, tablets, and laptops without needing separate queries per subcategory; the full-text search combining PostgreSQL's tsvector with structured filters in one query; and the atomic expiration job that runs on a Replit Scheduled Deployment.

Moderation is built in from the start — listings can be flagged by users and reviewed by admins. This prevents spam and prohibited content from accumulating. The admin queue shows pending flags with approve/remove actions.

Final result

A deployed classifieds platform with hierarchical category browsing, full-text search with price/condition filters, 30-day listing expiration with renewal, user flagging, and an admin moderation queue.

Tech stack

ReplitIDE & Hosting

ExpressBackend Framework

PostgreSQLDatabase

Drizzle ORMDatabase ORM

Replit AuthAuth

ReactFrontend

Prerequisites

A Replit Core account (required for Replit Auth and built-in PostgreSQL)
Decide on your category structure before building — changing the hierarchy after content is posted is disruptive
Optional: a SendGrid or Resend API key for contact form email relay (free tier sufficient)

Build steps

Scaffold the project with Agent

Generate the full Express + Drizzle project including the hierarchical category schema. The parent_id self-reference on categories is key — it enables subcategory nesting at any depth.

prompt.txt

1// Prompt to type into Replit Agent:
2// Build a Node.js Express classifieds platform with Replit Auth and built-in PostgreSQL using Drizzle ORM.
3// Schema in shared/schema.ts:
4// * categories: id serial pk, name text not null unique, slug text not null unique,
5//   parent_id integer references categories(id), icon text, position integer default 0
6// * listings: id serial pk, category_id integer references categories not null,
7//   title text not null, description text not null, price integer (in cents, null=free),
8//   price_type text default 'fixed' (fixed/negotiable/free/contact),
9//   condition text (new/like_new/good/fair/poor),
10//   location text not null, images jsonb, contact_method text default 'email',
11//   contact_email text, contact_phone text, poster_id text not null,
12//   status text default 'active', expires_at timestamp, view_count integer default 0,
13//   created_at timestamp default now()
14// * saved_searches: id serial pk, user_id text not null, query text,
15//   category_id integer, location text, min_price integer, max_price integer,
16//   created_at timestamp default now()
17// * flags: id serial pk, listing_id integer references listings not null,
18//   reporter_id text not null, reason text not null, details text,
19//   status text default 'pending', created_at timestamp default now()
20// Routes: GET /api/listings, GET /api/listings/:id, POST /api/listings,
21// PUT /api/listings/:id, PATCH /api/listings/:id/renew, PATCH /api/listings/:id/sold,
22// DELETE /api/listings/:id, POST /api/listings/:id/flag,
23// GET /api/me/listings, GET /api/categories, GET /api/admin/flags
24// React frontend with category grid, listing cards, filter sidebar, post form, My Listings dashboard

Pro tip: Seed your categories immediately after creating the tables. Open Drizzle Studio and manually insert your top-level categories first (parent_id = null), then subcategories referencing them. Without categories, no listings can be created.

Expected result: Project running with all four tables. The categories table is ready to be seeded.

Seed categories and add the recursive CTE for category browsing

The recursive CTE is the key query that makes 'browse Electronics' show all Phone, Tablet, and Laptop listings automatically. Without it, you'd need a separate query per subcategory level.

server/routes/listings.js

1// Seed categories in Drizzle Studio or via an admin route:
2// Top-level: Electronics (id:1), Vehicles (id:2), Housing (id:3), Services (id:4), Free (id:5)
3// Under Electronics: Phones (id:6, parent:1), Computers (id:7, parent:1), Gaming (id:8, parent:1)
4// Under Phones: iPhones (id:9, parent:6), Android Phones (id:10, parent:6)
5
6// The recursive CTE query for GET /api/listings?category=electronics:
7import { db } from '../db.js';
8import { sql } from 'drizzle-orm';
9
10export async function getListingsByCategory(categoryId) {
11  // Find all descendant category IDs including the root
12  const categoryResult = await db.execute(sql`
13    WITH RECURSIVE cat_tree AS (
14      SELECT id FROM categories WHERE id = ${categoryId}
15      UNION ALL
16      SELECT c.id FROM categories c
17      JOIN cat_tree ct ON c.parent_id = ct.id
18    )
19    SELECT id FROM cat_tree
20  `);
21
22  const categoryIds = categoryResult.rows.map(r => r.id);
23  if (categoryIds.length === 0) return [];
24
25  // Fetch all active listings in any of these categories
26  const listings = await db.execute(sql`
27    SELECT l.*, c.name as category_name
28    FROM listings l
29    JOIN categories c ON l.category_id = c.id
30    WHERE l.category_id = ANY(${categoryIds}::int[])
31    AND l.status = 'active'
32    AND (l.expires_at IS NULL OR l.expires_at > now())
33    ORDER BY l.created_at DESC
34    LIMIT 50
35  `);
36
37  return listings.rows;
38}

Pro tip: Index categories.parent_id to make the recursive CTE fast: CREATE INDEX idx_categories_parent ON categories(parent_id). Without this, the recursion does a full table scan on every level.

Expected result: GET /api/listings?category=1 (Electronics) returns listings from all subcategories — Phones, Computers, Gaming, iPhones, Android Phones — without separate requests per subcategory.

Add full-text search with combined price and condition filters

The search endpoint combines PostgreSQL's tsvector for text search with standard WHERE clauses for structured filters. A GIN index makes this fast even with thousands of listings.

server/routes/search.js

1import { db } from '../db.js';
2import { sql } from 'drizzle-orm';
3
4// First, add the GIN index (run once in Drizzle Studio or via a migration):
5// CREATE INDEX listings_search ON listings USING GIN (
6//   to_tsvector('english', title || ' ' || description)
7// );
8
9export async function searchListings(req, res) {
10  const { q, category, minPrice, maxPrice, condition, location, page = 1, limit = 20 } = req.query;
11  const offset = (parseInt(page) - 1) * parseInt(limit);
12
13  const params = [];
14  const conditions = ["l.status = 'active'", '(l.expires_at IS NULL OR l.expires_at > now())'];
15
16  if (q) {
17    params.push(q);
18    conditions.push(`to_tsvector('english', l.title || ' ' || l.description) @@ plainto_tsquery('english', $${params.length})`);
19  }
20  if (category) {
21    params.push(parseInt(category));
22    conditions.push(`l.category_id = $${params.length}`);
23  }
24  if (minPrice) {
25    params.push(parseInt(minPrice) * 100); // convert dollars to cents
26    conditions.push(`l.price >= $${params.length}`);
27  }
28  if (maxPrice) {
29    params.push(parseInt(maxPrice) * 100);
30    conditions.push(`(l.price <= $${params.length} OR l.price IS NULL)`);
31  }
32  if (condition) {
33    params.push(condition);
34    conditions.push(`l.condition = $${params.length}`);
35  }
36  if (location) {
37    params.push(`%${location}%`);
38    conditions.push(`l.location ILIKE $${params.length}`);
39  }
40
41  const whereClause = conditions.join(' AND ');
42  params.push(parseInt(limit), offset);
43  const client = await db.$client.connect();
44  const { rows } = await client.query(
45    `SELECT l.*, c.name as category_name FROM listings l JOIN categories c ON l.category_id = c.id WHERE ${whereClause} ORDER BY l.created_at DESC LIMIT $${params.length - 1} OFFSET $${params.length}`,
46    params
47  );
48  client.release();
49  res.json({ data: rows, page: parseInt(page) });
50}

Pro tip: Use plainto_tsquery for user-facing search (it handles natural input like 'apple iphone 14') rather than to_tsquery (which requires syntax like 'apple & iphone & 14'). plainto_tsquery never throws a syntax error on unusual input.

Expected result: GET /api/listings?q=iphone&condition=like_new&minPrice=200&maxPrice=800 returns listings matching 'iphone' in like_new condition priced between $200 and $800.

Add listing expiration, renewal, and the moderation queue

Listings expire 30 days after posting. Sellers can renew for another 30 days. A Scheduled Deployment runs daily to mark expired listings. The moderation queue shows flagged listings for admin review.

server/routes/listings.js

1import { db } from '../db.js';
2import { listings, flags } from '../../shared/schema.js';
3import { eq, lt, and, sql } from 'drizzle-orm';
4
5// POST /api/listings — set expires_at to 30 days from now
6export async function createListing(req, res) {
7  const posterId = req.get('X-Replit-User-Id');
8  if (!posterId) return res.status(401).json({ error: 'Not authenticated' });
9
10  const expiresAt = new Date();
11  expiresAt.setDate(expiresAt.getDate() + 30);
12
13  const [listing] = await db.insert(listings).values({
14    ...req.body,
15    posterId,
16    expiresAt,
17    status: 'active',
18    viewCount: 0,
19  }).returning();
20
21  res.status(201).json(listing);
22}
23
24// PATCH /api/listings/:id/renew — extend by 30 days
25export async function renewListing(req, res) {
26  const posterId = req.get('X-Replit-User-Id');
27  const { id } = req.params;
28
29  const [listing] = await db.select().from(listings).where(eq(listings.id, parseInt(id)));
30  if (!listing || listing.posterId !== posterId) return res.status(403).json({ error: 'Not authorized' });
31
32  const newExpiry = new Date();
33  newExpiry.setDate(newExpiry.getDate() + 30);
34
35  const [updated] = await db.update(listings)
36    .set({ expiresAt: newExpiry, status: 'active' })
37    .where(eq(listings.id, parseInt(id)))
38    .returning();
39
40  res.json(updated);
41}
42
43// Scheduled expiration job (run as a Replit Scheduled Deployment daily)
44export async function expireListings() {
45  const result = await db.update(listings)
46    .set({ status: 'expired' })
47    .where(and(eq(listings.status, 'active'), lt(listings.expiresAt, new Date())));
48  console.log(`Expired listings updated: ${result.rowCount}`);
49}
50
51// GET /api/admin/flags — moderation queue
52export async function getPendingFlags(req, res) {
53  const adminId = req.get('X-Replit-User-Id');
54  // TODO: check admin role in users table
55  const pendingFlags = await db.execute(sql`
56    SELECT f.*, l.title, l.description, l.poster_id
57    FROM flags f JOIN listings l ON f.listing_id = l.id
58    WHERE f.status = 'pending'
59    ORDER BY f.created_at ASC
60  `);
61  res.json(pendingFlags.rows);
62}

Pro tip: Create a separate Scheduled Deployment in Replit pointing to a dedicated expiration route (GET /api/admin/expire) rather than using setInterval in the main server. Scheduled Deployments are more reliable and cost less than keeping a Reserved VM running just for daily cleanup.

Expected result: New listings have expires_at set 30 days in the future. After calling /renew, expires_at moves 30 days forward. The moderation queue returns all flags with status 'pending' and the associated listing details.

Complete code

server/routes/listings.js

1import { db } from '../db.js';
2import { listings } from '../../shared/schema.js';
3import { eq, and, lt } from 'drizzle-orm';
4
5const LISTING_EXPIRY_DAYS = 30;
6
7export async function createListing(req, res) {
8  const posterId = req.get('X-Replit-User-Id');
9  if (!posterId) return res.status(401).json({ error: 'Not authenticated' });
10
11  const { categoryId, title, description, price, priceType, condition, location, images, contactMethod, contactEmail, contactPhone } = req.body;
12  if (!categoryId || !title || !description || !location) {
13    return res.status(400).json({ error: 'categoryId, title, description, and location are required' });
14  }
15
16  const expiresAt = new Date();
17  expiresAt.setDate(expiresAt.getDate() + LISTING_EXPIRY_DAYS);
18
19  const [listing] = await db.insert(listings).values({
20    categoryId: parseInt(categoryId),
21    title, description,
22    price: price ? parseInt(price) : null,
23    priceType: priceType || 'fixed',
24    condition: condition || null,
25    location, images: images || [],
26    contactMethod: contactMethod || 'email',
27    contactEmail: contactEmail || null,
28    contactPhone: contactPhone || null,
29    posterId,
30    status: 'active',
31    expiresAt,
32    viewCount: 0,
33  }).returning();
34
35  res.status(201).json(listing);
36}
37
38export async function getListing(req, res) {
39  const { id } = req.params;
40  await db.execute(`UPDATE listings SET view_count = view_count + 1 WHERE id = ${parseInt(id)}`);
41  const [listing] = await db.select().from(listings).where(eq(listings.id, parseInt(id)));
42  if (!listing || listing.status === 'removed') return res.status(404).json({ error: 'Listing not found' });
43  // Mask contact info based on contact_method
44  if (listing.contactMethod === 'email') delete listing.contactPhone;
45  if (listing.contactMethod === 'phone') delete listing.contactEmail;
46  res.json(listing);
47}

Customization ideas

Featured listings (paid placement)

Add a featured boolean and featured_until timestamp to listings. Charge sellers via Stripe Checkout to feature their listing for 7 or 30 days. Featured listings appear at the top of category and search results.

Image upload with Replit Object Storage

Add an image upload endpoint that accepts multipart form data, stores images in Replit Object Storage, and returns public URLs. Store URLs in the listing's images JSONB array. Limit to 6 images per listing.

Email notifications for saved searches

Add a daily Scheduled Deployment that checks saved_searches and finds new listings (created_at > last_notified) matching each saved search's criteria. Email matching users a digest of new listings using Resend.

Common pitfalls

Pitfall: Not filtering expired listings from search results

How to avoid: Add AND (l.expires_at IS NULL OR l.expires_at > now()) to all public listing queries. Also add AND l.status = 'active' to exclude sold and removed listings.

Pitfall: Querying only the selected category without its children

How to avoid: Use the recursive CTE shown in Step 2 to collect all descendant category IDs, then filter listings by category_id IN (all descendant IDs).

Pitfall: Exposing all contact details regardless of contact_method setting

How to avoid: In the GET /api/listings/:id route, delete listing.contactPhone if contactMethod is 'email', and delete listing.contactEmail if contactMethod is 'phone'. Only return the chosen contact method.

Best practices

Use the recursive CTE for category traversal — it's cleaner and more performant than multiple round-trip queries.
Add a GIN index on listings for full-text search: CREATE INDEX listings_search ON listings USING GIN (to_tsvector('english', title || ' ' || description)).
Store prices in cents (integer) and convert to dollars only for display.
Use a Replit Scheduled Deployment for listing expiration — more reliable than setInterval in the main server.
Mask contact info in API responses based on each listing's contact_method setting.
Deploy the main app on Autoscale and the expiration job as a separate Scheduled Deployment.
Implement idempotent listing renewal: if expires_at is still in the future, extend from the current expires_at, not from today.

AI prompts to try

Copy these prompts to build this project faster.

ChatGPT Prompt

I'm building a classifieds platform with Express and PostgreSQL. I have a categories table with a parent_id self-reference (for subcategories) and a listings table with a category_id foreign key. Help me write an Express route for GET /api/listings?category=:slug that: (1) looks up the category by slug, (2) uses a PostgreSQL recursive CTE to find all descendant category IDs (children, grandchildren, etc.), and (3) returns all active, non-expired listings whose category_id is in that set — ordered by created_at descending with pagination.

Build Prompt

Add an email contact relay to the classifieds platform. Instead of showing the seller's email directly (which would expose it to scrapers), add a POST /api/listings/:id/contact route. The buyer submits their name, email, and message. The server validates the listing is active and sends an email to the seller via Resend with the buyer's message and a Reply-To set to the buyer's email. Store RESEND_API_KEY in Replit Secrets. This keeps seller emails private while still enabling contact.

Frequently asked questions

How do I stop spam listings from appearing?

Add a requires_approval boolean column to categories. Set it to true for categories prone to spam. When a listing is created in a requires_approval category, set status to 'pending_review' instead of 'active'. Admin sees it in the moderation queue and approves or rejects it.

Can I add a map view to show listings by location?

Yes. Add latitude and longitude columns to listings. Geocode the location text when a listing is created (using OpenCage or Mapbox API with key in Replit Secrets). On the frontend, use react-leaflet to display a map with listing markers. Add a viewport bounding box filter to GET /api/listings for efficient map-based loading.

How do listings expire automatically?

Create a Replit Scheduled Deployment pointing to GET /api/admin/expire-listings. This route runs UPDATE listings SET status = 'expired' WHERE status = 'active' AND expires_at < now(). The Scheduled Deployment runs it daily at midnight. This is more reliable than setInterval because Scheduled Deployments are managed by Replit's infrastructure.

Do I need Replit Core for this build?

Yes. Replit Auth (used for posting listings and admin moderation) requires Replit Core or higher. The built-in PostgreSQL is also included in Core. The free tier doesn't include these features.

How do I handle subcategory nesting deeper than 2 levels?

The recursive CTE works for any depth automatically. Electronics → Phones → iPhones → Refurbished iPhones is 3 levels — the CTE traverses all levels in one query. There's no code change needed to support deeper nesting.

Can RapidDev help me build a custom classifieds platform?

Yes. RapidDev has built 600+ apps including classifieds and marketplace platforms with payment collection, escrow, and geo-based filtering. Contact us for a free consultation.

What's the difference between status 'expired', 'sold', and 'removed'?

'expired' means the listing's 30-day window ended and the seller didn't renew. 'sold' means the seller marked it as sold. 'removed' means an admin removed it for policy violations. All three are hidden from public browsing but visible to the seller in their My Listings dashboard for reference.

Talk to an Expert

Our team has built 600+ apps. Get personalized help with your project.

Book a free consultation

How to Build a Classifieds with Replit

What you'll build

What you'll build

What you're building

Final result

Tech stack

Prerequisites

Build steps

Scaffold the project with Agent

Seed categories and add the recursive CTE for category browsing

Add full-text search with combined price and condition filters

Add listing expiration, renewal, and the moderation queue

Complete code

Customization ideas

Featured listings (paid placement)

Image upload with Replit Object Storage

Email notifications for saved searches

Common pitfalls

Best practices

AI prompts to try

Frequently asked questions

Talk to an Expert

Need help building your app?

We put the rapid in RapidDev

How to Build a Classifieds with Replit

What you'll build

What you're building

Final result

Tech stack

Prerequisites

Build steps

Scaffold the project with Agent

Seed categories and add the recursive CTE for category browsing

Add full-text search with combined price and condition filters

Add listing expiration, renewal, and the moderation queue

Complete code

Customization ideas

Featured listings (paid placement)

Image upload with Replit Object Storage

Email notifications for saved searches

Common pitfalls

Best practices

AI prompts to try

Related builds

How to Build Directory Service with Replit

How to Build Marketplace with Replit

How to Build Shopping Cart with Replit

How to Build Reviews and Ratings with Replit

Explore related guides

Frequently asked questions

Talk to an Expert

Need help building your app?

We put the rapid in RapidDev