Schema Reference

Source: packages/database/src/schema.ts

Enums

user_role

tenant | landlord

property_type

apartment | condo | house | townhouse | studio | loft | basement | duplex | triplex

listing_status

draft | active | rented | archived

lease_type

fixed | month_to_month | sublet

inquiry_status

new | contacted | resolved | spam

claim_status

pending | approved | rejected

report_reason

already_rented | wrong_info | spam | duplicate | other

report_status

pending | reviewed | dismissed

content_status

draft | published | archived

Tables

users

Column	Type	Notes
id	uuid	PK, auto-generated
email	text	unique, not null
password_hash	text	bcrypt hash
name	text
phone	text
role	user_role	default: tenant
avatar_url	text
created_at	timestamptz
updated_at	timestamptz

neighborhoods

Column	Type	Notes
id	uuid	PK
name	text	unique, e.g. "Mile End"
slug	text	unique, e.g. "mile-end"
borough	text	e.g. "Le Plateau-Mont-Royal"
latitude	numeric(10,7)	center point
longitude	numeric(10,7)	center point
description_en	text	nullable, markdown
description_fr	text	nullable, markdown
highlights_en	text	nullable, markdown
highlights_fr	text	nullable
seo_title_en	text	nullable
seo_title_fr	text	nullable
seo_description_en	text	nullable
seo_description_fr	text	nullable
cover_image_url	text	nullable
walkability_score	integer	nullable, 0-100
transit_score	integer	nullable, 0-100
created_at	timestamptz

Seeded with ~85 Montreal neighborhoods organized by borough.

neighborhood_stats_snapshots

Historical daily stats snapshots for price trends per neighborhood.

Column	Type	Notes
id	uuid	PK
neighborhood_id	uuid	FK → neighborhoods, cascade delete
date	timestamptz	snapshot date
bedrooms	integer	nullable: null=overall, 0=studio, 1-3=specific, 4=4+
avg_price	integer	average price in cents
listing_count	integer	not null, default: 0
min_price	integer
max_price	integer
created_at	timestamptz

Unique index on (neighborhood_id, date, bedrooms). Populated daily at 2:00 AM by the scraper stats-snapshot job.

listings

Column	Type	Notes
id	uuid	PK
landlord_id	uuid	FK → users, cascade delete
neighborhood_id	uuid	FK → neighborhoods, set null
title	text	not null
description	text	not null
property_type	property_type	not null
status	listing_status	default: draft
price_per_month	integer	in cents (150000 = $1,500)
lease_type	lease_type	default: fixed
bedrooms	integer	0 = studio
bathrooms	integer	default: 1
area_sq_ft	integer	optional
address_street	text	not null
address_unit	text	optional
address_city	text	default: Montreal
address_province	text	default: QC
address_postal_code	text	not null
latitude	numeric(10,7)	for map
longitude	numeric(10,7)	for map
amenities	jsonb	string array
available_date	timestamptz
contact_email	text
contact_phone	text
is_pet_friendly	boolean	default: false
is_smoking_allowed	boolean	default: false
has_parking	boolean	default: false
has_laundry	boolean	default: false
views	integer	default: 0, incremented on detail page view
published_at	timestamptz
claimed_by	uuid	FK → users, set null. Claimed listings are never overwritten by scraper updates
claimed_at	timestamptz	set when claim approved
archived_at	timestamptz	nullable, when archived. Cleared if listing is re-scraped (reactivated)
archive_reason	text	nullable, e.g. "stale", "bulk-admin"
rental_price	integer	nullable, actual rent agreed in cents
tenant_name	text	nullable, who rented it
lease_start_date	timestamptz	nullable, lease start
lease_end_date	timestamptz	nullable, lease end
created_at	timestamptz
updated_at	timestamptz

listing_images

Column	Type	Notes
id	uuid	PK
listing_id	uuid	FK → listings, cascade delete
url	text	not null
alt_text	text
sort_order	integer	default: 0
is_primary	boolean	default: false
created_at	timestamptz

favorites

Column	Type	Notes
id	uuid	PK
user_id	uuid	FK → users, cascade delete
listing_id	uuid	FK → listings, cascade delete
created_at	timestamptz

Unique index on (user_id, listing_id).

inquiries

Column	Type	Notes
id	uuid	PK
listing_id	uuid	FK → listings, cascade delete
user_id	uuid	FK → users, nullable, set null
name	text	not null
email	text	not null
phone	text
message	text	not null
status	inquiry_status	default: new
admin_notes	text	internal admin notes
metadata	jsonb	submitter info: ip, userAgent, deviceType, browser, os, locale, referer
created_at	timestamptz
updated_at	timestamptz

listing_claims

Column	Type	Notes
id	uuid	PK
listing_id	uuid	FK → listings, cascade delete
user_id	uuid	FK → users, cascade delete
status	claim_status	default: pending
justification	text	not null
admin_notes	text
reviewed_at	timestamptz
created_at	timestamptz
updated_at	timestamptz

Unique index on (listing_id, user_id).

listing_reports

User-submitted reports/flags on listings (spam, outdated, duplicate, etc).

Column	Type	Notes
id	uuid	PK
listing_id	uuid	FK → listings, cascade delete
user_id	uuid	FK → users, set null on delete (nullable)
reason	report_reason	not null
comment	text	optional details
reporter_ip	text	IP for rate limiting
status	report_status	default: pending
admin_notes	text	internal admin notes
metadata	jsonb	userAgent, deviceType, browser, os, locale
created_at	timestamptz
resolved_at	timestamptz	set when reviewed/dismissed

listing_notes

Column	Type	Notes
id	uuid	PK
listing_id	uuid	FK → listings, cascade delete
user_id	uuid	FK → users, cascade delete
content	text	not null
created_at	timestamptz
updated_at	timestamptz

articles

Blog posts and wiki/guide pages with bilingual content.

Column	Type	Notes
id	uuid	PK
type	text	blog or wiki
slug	text	unique, URL-safe
category	text	nullable, e.g. "tenant-rights"
title_en	text	not null
title_fr	text	not null
content_en	text	not null, markdown
content_fr	text	not null, markdown
excerpt_en	text	nullable, short summary
excerpt_fr	text	nullable
seo_title_en	text	nullable
seo_title_fr	text	nullable
seo_description_en	text	nullable
seo_description_fr	text	nullable
cover_image_url	text	nullable
status	content_status	default: draft
sort_order	integer	default: 0, for wiki ordering
published_at	timestamptz	nullable
created_at	timestamptz
updated_at	timestamptz

postal_areas

Forward Sortation Areas (FSA — 3-character postal code prefixes) for Greater Montreal with boundary polygons.

Column	Type	Notes
id	uuid	PK
fsa	text	unique, not null (e.g., H2T)
name	text	not null, area name
province	text	not null (QC)
latitude	numeric(10,7)	centroid latitude
longitude	numeric(10,7)	centroid longitude
boundary	jsonb	GeoJSON polygon from Statistics Canada
neighborhood_id	uuid	FK → neighborhoods (nearest match)
median_household_income	integer	nullable, from 2021 Census
average_household_income	integer	nullable, from 2021 Census
created_at	timestamptz

193 FSAs (123 H-prefix Montreal/Laval + 70 J-prefix South Shore/suburbs). 187 have boundary polygons.

postal_codes

Full 6-digit postal codes for Greater Montreal, linked to parent FSAs.

Column	Type	Notes
id	uuid	PK
code	text	unique, not null, normalized no space (e.g., H2T2Y2)
fsa	text	not null, parent FSA (e.g., H2T)
ldu	text	not null, Local Delivery Unit (e.g., 2Y2)
name	text	city/area name from GeoNames
latitude	numeric(10,7)	centroid latitude
longitude	numeric(10,7)	centroid longitude
boundary	jsonb	Reserved for future LDU boundaries
postal_area_id	uuid	FK → postal_areas
created_at	timestamptz

~101K postal codes seeded from GeoNames (CC BY 4.0): 56K H-prefix + 45K J-prefix within ~50km of downtown Montreal.

census_tracts

Census tract boundaries and household income data from the 2021 Canadian Census (Statistics Canada). Used for the income choropleth map.

Column	Type	Notes
id	uuid	PK
ctuid	text	unique, not null. Census tract unique ID (e.g., "4620001.00")
cma_uid	text	CMA code (462 = Montreal)
name	text	short tract name (e.g., "0001.00")
boundary	jsonb	GeoJSON polygon from StatCan shapefile
median_household_income	integer	nullable, 2021 Census
average_household_income	integer	nullable, 2021 Census
median_after_tax_income	integer	nullable, 2021 Census
average_after_tax_income	integer	nullable, 2021 Census
household_count	integer	nullable, number of households in tract
latitude	numeric(10,7)	centroid latitude
longitude	numeric(10,7)	centroid longitude
created_at	timestamptz

1004 census tracts for Montreal CMA, 986 with income data. Seeded from StatCan boundary shapefile + Table 98-10-0058-01 income CSV.

income_snapshots

Historical income data across all sources and geographies. Supports multi-year comparisons and trend analysis.

Column	Type	Notes
id	uuid	PK
geo_type	text	not null. ct, fsa, cma, mrc
geo_code	text	not null. CT UID, FSA code, CMA code, etc.
year	integer	not null. Data year
source	text	not null. census, cra, isq
median_income	integer	nullable. Household median (census only)
median_income_after_tax	integer	nullable. Census only
average_income	integer	nullable. Household avg (census) or individual avg (CRA)
average_income_after_tax	integer	nullable. Census only
total_households	integer	nullable. Census only
total_filers	integer	nullable. CRA only
total_income_thousands	integer	nullable. CRA total income in $1000s
created_at	timestamptz

Unique index on (geo_type, geo_code, year, source).

Records: ~2,882 total — 986 CT×2021 (census), 951 CT×2016 (census), 1,945 FSA×7 years (CRA 2015-2021).

income_distributions

Income bracket distribution data from StatCan Table 98-10-0055. Shows number of households in each income bracket per geography/year.

Column	Type	Notes
id	uuid	PK
geo_type	text	not null. cma
geo_code	text	not null. 462 for Montreal CMA
year	integer	not null. 2016, 2021
source	text	not null. census
brackets	jsonb	[{label, min, max, count, pct}, ...] (19 brackets)
total_households	integer	nullable
created_at	timestamptz

Unique index on (geo_type, geo_code, year, source). 2 records (Montreal CMA × 2 census years).

rent_snapshots

CMHC Rental Market Survey average rent data by zone/subdivision.

Column	Type	Notes
id	uuid	PK
geo_type	text	not null. zone or csd
geo_code	text	not null. Zone/CSD identifier
geo_name	text	not null. Human-readable name
year	integer	not null. 1998–2025
source	text	not null. cmhc
avg_rent_studio	integer	nullable, in cents
avg_rent_1br	integer	nullable, in cents
avg_rent_2br	integer	nullable, in cents
avg_rent_3br	integer	nullable, in cents
avg_rent_total	integer	nullable, in cents
vacancy_rate	numeric(4,1)	nullable, percentage
universe	integer	nullable, total units surveyed
created_at	timestamptz

Unique index on (geo_type, geo_code, year, source). Requires manual CMHC portal download.

points_of_interest

Points of interest (transit stops, schools) pre-seeded from open data sources for nearby queries on listing detail pages.

Column	Type	Notes
id	uuid	PK
source_id	text	unique, not null. Format: stm:{stop_id} or meq:{code}
source	text	stm_gtfs or meq
category	text	metro, bus, train, school
subcategory	text	green_line, orange_line, blue_line, yellow_line, primary, secondary, cegep, university
name	text	POI name
name_fr	text	French name
latitude	numeric(10,7)
longitude	numeric(10,7)
metadata	jsonb	Bus: { routes: ["67", "185"] }. Schools: { schoolBoard, language }
is_active	boolean	default: true. Set false on re-sync when POI no longer exists
synced_at	timestamptz	last sync timestamp
created_at	timestamptz

Indexes on (category) and (latitude, longitude) where is_active = true.

rent_benchmarks

Precomputed rent percentiles per neighborhood + bedroom count. Recomputed nightly at 3am from 12 months of active+archived listings. Used for AutoTrader-style price rating badges.

Column	Type	Notes
id	uuid	PK
neighborhood_id	uuid	FK neighborhoods, not null
bedrooms	integer	not null. 0=studio, 1-3, 4=4+
median	integer	50th percentile rent in cents
p20	integer	20th percentile
p40	integer	40th percentile
p60	integer	60th percentile
p80	integer	80th percentile
sample_count	integer	not null, default 0
computed_at	timestamptz	not null

Unique index on (neighborhood_id, bedrooms).

listing_price_history

Tracks price changes detected by the scraper during re-scraping. Used for "Price Drop" badges and the priceDrops filter.

Column	Type	Notes
id	uuid	PK
listing_id	uuid	FK listings, not null
old_price	integer	not null, in cents
new_price	integer	not null, in cents
changed_at	timestamptz	not null, default now

Index on (listing_id, changed_at).

---

Scraper Schema (scraper.*)

Source: services/scraper/src/db/schema.ts

All scraper tables live in the scraper PostgreSQL schema, isolated from the public schema.

scraper.scrape_runs

One row per scraper execution cycle.

Column	Type	Notes
id	uuid	PK
platform	platform	kijiji, kangalou, etc.
started_at	timestamptz
completed_at	timestamptz	nullable
status	text	running, completed, failed
listings_found	integer	total listings seen
listings_new	integer	first-time listings
listings_updated	integer	re-scraped with changes
error_message	text	nullable
config	jsonb	run configuration

scraper.raw_listings

Core scraped data with full pipeline status tracking.

Column	Type	Notes
id	uuid	PK
platform	platform	source platform
platform_id	text	ID on source site
platform_url	text	URL on source site
scrape_run_id	uuid	FK → scrape_runs
status	scrape_status	raw → normalized → imported
imported_listing_id	uuid	FK → public.listings
content_hash	text	SHA hash of raw data; used to detect changes on re-scrape
raw_*	text/jsonb	raw scraped fields
norm_*	text/int/jsonb	normalized fields
first_scraped_at	timestamptz	first seen
last_scraped_at	timestamptz	most recent scrape

Unique index on (platform, platform_id).

scraper.raw_images

Column	Type	Notes
id	uuid	PK
raw_listing_id	uuid	FK → raw_listings, cascade
source_url	text	original URL
local_path	text	downloaded file path
download_status	text	pending, downloaded, failed
file_size	integer	bytes

scraper.pipeline_events

Per-message activity log. One row per pipeline stage processed. Auto-pruned after 7 days.

Column	Type	Notes
id	uuid	PK
stage	pipeline_stage	scraped, normalized, imported, images, published, error
raw_listing_id	uuid	FK → raw_listings, nullable
platform	platform	source platform
duration_ms	integer	processing time
result	jsonb	stage-specific details (see monitoring docs for schema per stage)
error	text	error message if failed
created_at	timestamptz	indexed for querying

scraper.stats_snapshots

Hourly aggregate throughput counters. Persisted from the in-memory StatsCollector. Auto-pruned after 90 days.

Column	Type	Notes
id	uuid	PK
period_start	timestamptz	start of hourly period
period_end	timestamptz	end of hourly period
counters	jsonb	{scraped, scrapedNew, normalized, imported, ...}
created_at	timestamptz

scraper.scraper_state

Persistent key-value store for cursor state (e.g., kijiji:nextDeepPage).

Column	Type	Notes
key	text	PK
value	jsonb
updated_at	timestamptz