Data Layer

Database Schema

PostgreSQL database with Drizzle ORM powering the entire platform. 14 tables organized into logical domains supporting property, vehicle, and notification data.

Location Hierarchy

Normalized geographic data for precise location filtering

provinces

South African provinces

idname
cities

Cities with province reference

idnameprovince_id
neighbourhoods

Neighbourhoods with city reference

idnamecity_id

Core Data

Primary business entities for listings, sellers, and scraping

sellers

Seller registry with verification badges & activity metrics

idnamephonetotal_adsactive_ads+7 more
gumtree_property_listings

Property listings with extended attributes (JSONB images)

idgumtree_idtitlepricedescription+15 more
gumtree_vehicle_listings

Vehicle listings with make/model/year attributes

idgumtree_idtitlepricedescription+13 more
scrape_queue

Queue for resume support & scrape tracking

idurlcategorystatusattempts+2 more
listing_media

Multiple images per listing (DO Spaces storage)

idlisting_idtable_nameurlstorage_key+1 more

WhatsApp Recipients

User notification preferences and subscription management

whatsapp_recipients

Registered notification recipients

idnamephone_numberis_activeis_paused
recipient_quiet_hours

Do-not-disturb schedules per user

idrecipient_idstart_time_utcend_time_utcdays_of_week
recipient_table_subscriptions

Category subscriptions with quality thresholds

idrecipient_idtable_namemin_lead_qualityfilter_settings
recipient_location_filters

Location-based notification filters

idsubscription_idprovince_idcity_idneighbourhood_id

Notification Tracking

Audit log for all sent notifications

notification_logs

Complete notification history with Twilio references

idrecipient_idlisting_idtable_namesent_at+2 more

Entity Relationships

provinces
1:N
cities
cities
1:N
neighbourhoods
cities
1:N
listings
neighbourhoods
1:N
listings
sellers
1:N
property_listings
sellers
1:N
vehicle_listings
listings
1:N
listing_media
whatsapp_recipients
1:N
quiet_hours
whatsapp_recipients
1:N
subscriptions
subscriptions
1:N
location_filters
whatsapp_recipients
1:N
notification_logs

Drizzle Schema Definition

schema.ts
// Core Listings Tables
export const gumtreePropertyListings = pgTable('gumtree_property_listings', {
  id: serial('id').primaryKey(),
  gumtreeId: varchar('gumtree_id', { length: 255 }).unique().notNull(),
  title: text('title').notNull(),
  price: varchar('price', { length: 100 }),
  images: jsonb('images').$type<string[]>().default([]),  // JSONB array
  bedrooms: integer('bedrooms'),
  bathrooms: integer('bathrooms'),
  parking: integer('parking'),
  petFriendly: boolean('pet_friendly'),
  sizeSqm: integer('size_sqm'),
  leadQuality: varchar('lead_quality', { length: 20 }).default('bad'),
});

export const gumtreeVehicleListings = pgTable('gumtree_vehicle_listings', {
  id: serial('id').primaryKey(),
  gumtreeId: varchar('gumtree_id', { length: 255 }).unique().notNull(),
  make: varchar('make', { length: 100 }),
  model: varchar('model', { length: 100 }),
  year: integer('year'),
  km: integer('km'),
  fuelType: varchar('fuel_type', { length: 50 }),
  transmission: varchar('transmission', { length: 50 }),
});

// Sellers with Verification Badges
export const sellers = pgTable('sellers', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 255 }).notNull(),
  phone: varchar('phone', { length: 50 }),
  idVerified: boolean('id_verified').default(false),
  mobileVerified: boolean('mobile_verified').default(false),
  emailVerified: boolean('email_verified').default(false),
  totalViews: integer('total_views').default(0),
  sellingFor: varchar('selling_for', { length: 100 }),
});