How to Build a Directory Service with Replit

1// Prompt to type into Replit Agent:
2// Build a directory service app with Express and PostgreSQL using Drizzle ORM.
3// Create these tables in shared/schema.ts:
4// - categories: id serial pk, name text unique, slug text unique, icon text, description text, position integer default 0
5// - listings: id serial pk, title text, slug text unique, description text,
6//   category_id integer references categories, website_url text, contact_email text,
7//   location text, tags text[] (PostgreSQL array), image_url text,
8//   status text default 'pending' (pending/approved/rejected/featured),
9//   submitted_by text, approved_by text, approved_at timestamp,
10//   featured boolean default false, view_count integer default 0, created_at timestamp
11// - reviews: id serial pk, listing_id integer references listings,
12//   user_id text, rating integer (1-5), comment text, created_at timestamp,
13//   UNIQUE constraint on (listing_id, user_id)
14// - saved_listings: id serial pk, user_id text, listing_id integer references listings,
15//   created_at timestamp, UNIQUE constraint on (user_id, listing_id)
16// Add a GIN index on to_tsvector('english', listings.title || ' ' || listings.description)
17// Set up Replit Auth middleware. Bind server to 0.0.0.0.

1const { db } = require('../db');
2const { listings, categories, reviews } = require('../../shared/schema');
3const { sql, eq, and, inArray } = require('drizzle-orm');
4
5router.get('/api/listings', async (req, res) => {
6  try {
7    const { q, category, location, page = 1, limit = 20 } = req.query;
8    const offset = (Number(page) - 1) * Number(limit);
9
10    const conditions = [eq(listings.status, 'approved')];
11
12    if (category) {
13      const cat = await db.query.categories.findFirst({ where: eq(categories.slug, category) });
14      if (cat) conditions.push(eq(listings.categoryId, cat.id));
15    }
16
17    if (location) {
18      conditions.push(sql`listings.location ILIKE ${'%' + location + '%'}`);
19    }
20
21    let query;
22    if (q) {
23      // Full-text search with relevance ranking
24      query = await db.execute(
25        sql`SELECT l.*, c.name AS category_name,
26              COALESCE(AVG(r.rating), 0) AS avg_rating,
27              COUNT(r.id) AS review_count,
28              ts_rank(to_tsvector('english', l.title || ' ' || COALESCE(l.description, '')),
29                plainto_tsquery('english', ${q})) AS rank
30            FROM listings l
31            LEFT JOIN categories c ON c.id = l.category_id
32            LEFT JOIN reviews r ON r.listing_id = l.id
33            WHERE ${and(...conditions)}
34              AND to_tsvector('english', l.title || ' ' || COALESCE(l.description, ''))
35                @@ plainto_tsquery('english', ${q})
36            GROUP BY l.id, c.name
37            ORDER BY rank DESC, l.featured DESC
38            LIMIT ${Number(limit)} OFFSET ${offset}`
39      );
40    } else {
41      query = await db.execute(
42        sql`SELECT l.*, c.name AS category_name,
43              COALESCE(AVG(r.rating), 0) AS avg_rating,
44              COUNT(r.id) AS review_count
45            FROM listings l
46            LEFT JOIN categories c ON c.id = l.category_id
47            LEFT JOIN reviews r ON r.listing_id = l.id
48            WHERE ${and(...conditions)}
49            GROUP BY l.id, c.name
50            ORDER BY l.featured DESC, l.created_at DESC
51            LIMIT ${Number(limit)} OFFSET ${offset}`
52      );
53    }
54
55    res.json(query.rows);
56  } catch (err) {
57    res.status(500).json({ error: err.message });
58  }
59});

1// Prompt to type into Replit Agent:
2// Add these routes to server/routes/listings.js:
3//
4// POST /api/listings — create a new listing, requires auth
5//   Set status = 'pending', submitted_by = req.user.id
6//   Auto-generate slug from title (lowercase, spaces to hyphens, strip special chars)
7//   Return the created listing
8//
9// GET /api/listings/:slug — public, detail page
10//   Join with categories and reviews, include avg_rating and review_count
11//   Atomically increment view_count: UPDATE listings SET view_count = view_count + 1
12//   Return listing + reviews array
13//
14// GET /api/listings/mine — requires auth
15//   Return all listings where submitted_by = req.user.id, include status
16//
17// GET /api/admin/listings?status=pending — requires auth (admin check)
18//   List listings filtered by status, include submitter info
19//
20// PATCH /api/admin/listings/:id/approve — admin only
21//   Set status = 'approved', approved_by = req.user.id, approved_at = now()
22//
23// PATCH /api/admin/listings/:id/reject — admin only
24//   Set status = 'rejected', approved_by = req.user.id
25//
26// PATCH /api/admin/listings/:id/feature — admin only
27//   Toggle featured boolean
28//
29// For admin check: store admin user IDs in process.env.ADMIN_USER_IDS (comma-separated)
30// and check if req.user.id is in that list

1const { db } = require('../db');
2const { reviews, savedListings, listings } = require('../../shared/schema');
3const { eq, and } = require('drizzle-orm');
4
5// POST /api/items/:id/reviews — add or update a review
6router.post('/api/listings/:id/reviews', async (req, res) => {
7  if (!req.user) return res.status(401).json({ error: 'Login required' });
8
9  const { rating, comment } = req.body;
10  if (!rating || rating < 1 || rating > 5) {
11    return res.status(400).json({ error: 'Rating must be 1-5' });
12  }
13
14  try {
15    // Upsert: insert or update if user already reviewed this listing
16    const review = await db.insert(reviews).values({
17      listingId: Number(req.params.id),
18      userId: req.user.id,
19      rating: Number(rating),
20      comment
21    }).onConflictDoUpdate({
22      target: [reviews.listingId, reviews.userId],
23      set: { rating: Number(rating), comment }
24    }).returning();
25
26    res.json(review[0]);
27  } catch (err) {
28    res.status(500).json({ error: err.message });
29  }
30});
31
32// POST /api/listings/:id/save — toggle saved
33router.post('/api/listings/:id/save', async (req, res) => {
34  if (!req.user) return res.status(401).json({ error: 'Login required' });
35
36  const existing = await db.query.savedListings.findFirst({
37    where: and(eq(savedListings.userId, req.user.id), eq(savedListings.listingId, Number(req.params.id)))
38  });
39
40  if (existing) {
41    await db.delete(savedListings).where(eq(savedListings.id, existing.id));
42    return res.json({ saved: false });
43  }
44
45  await db.insert(savedListings).values({ userId: req.user.id, listingId: Number(req.params.id) });
46  res.json({ saved: true });
47});
48
49// GET /api/saved — user's saved listings
50router.get('/api/saved', async (req, res) => {
51  if (!req.user) return res.status(401).json({ error: 'Login required' });
52  const saved = await db.query.savedListings.findMany({
53    where: eq(savedListings.userId, req.user.id),
54    with: { listing: true }
55  });
56  res.json(saved.map(s => s.listing));
57});

1// Prompt to type into Replit Agent:
2// Create a seed script at scripts/seed.js that inserts initial categories:
3// const categories = [
4//   { name: 'Technology', slug: 'technology', icon: '💻', description: 'Tech tools and services' },
5//   { name: 'Food & Drink', slug: 'food-drink', icon: '🍕', description: 'Restaurants and cafes' },
6//   { name: 'Health', slug: 'health', icon: '🏥', description: 'Healthcare and wellness' },
7//   { name: 'Education', slug: 'education', icon: '📚', description: 'Learning resources' },
8//   { name: 'Services', slug: 'services', icon: '🔧', description: 'Professional services' }
9// ]
10// Run: await db.insert(categories).values(categories).onConflictDoNothing()
11// Then add to package.json scripts: "seed": "node scripts/seed.js"
12//
13// Also add the PostgreSQL retry wrapper to server/db.js (Pool with connectionTimeoutMillis: 5000)
14// Ensure server binds to 0.0.0.0
15// Deploy using Autoscale (Deploy button top-right → Autoscale)