← back to Ventura Corridor
db/migrations/023_marketplace.sql
66 lines
-- 023_marketplace.sql
-- LOCAL marketplace listings for the Ventura Boulevard corridor.
-- LOCAL ONLY — no scraping of third-party platforms. Meta's TOS, BrandTotal/Voyager
-- precedent, and ambiguous image rights on user-uploaded Marketplace photos
-- mean we never mirror Facebook/Craigslist/OfferUp/Nextdoor data. The page
-- deeplinks OUT to those platforms (plain hyperlinks are fine) but every row
-- in this table is a neighbor posting on OUR site.
--
-- CHECK constraint locks source_platform = 'local' so a future scraper can't
-- silently insert mirrored rows without first revising this migration and
-- revisiting the compliance conversation that gated it.
CREATE TABLE IF NOT EXISTS marketplace_listings (
id BIGSERIAL PRIMARY KEY,
category TEXT NOT NULL, -- Furniture, Electronics, Vehicles, Home Goods, Real Estate, Services, Jobs, Clothing, Free, Misc
title TEXT NOT NULL,
description TEXT,
price_cents BIGINT, -- NULL = free or "make offer"
condition TEXT CHECK (condition IS NULL OR condition IN ('new','like-new','good','fair','parts')),
location_label TEXT, -- Sherman Oaks, Studio City, Tarzana, Encino, Woodland Hills
lat DOUBLE PRECISION,
lng DOUBLE PRECISION,
image_url TEXT, -- relative path inside /public, or first-party CDN; never a hot-link to FB/CL/OU
source_url TEXT, -- internal listing URL, or mailto:/tel: handoff to seller
source_platform TEXT NOT NULL DEFAULT 'local'
CHECK (source_platform = 'local'),
seller_name TEXT,
seller_contact TEXT, -- email or phone; NULL if listing relies on source_url
posted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
fetched_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
expires_at TIMESTAMPTZ,
status TEXT NOT NULL DEFAULT 'active'
CHECK (status IN ('active','sold','expired','removed'))
);
CREATE INDEX IF NOT EXISTS marketplace_category_idx ON marketplace_listings (category);
CREATE INDEX IF NOT EXISTS marketplace_status_idx ON marketplace_listings (status);
CREATE INDEX IF NOT EXISTS marketplace_posted_idx ON marketplace_listings (posted_at DESC);
CREATE INDEX IF NOT EXISTS marketplace_location_idx ON marketplace_listings (location_label);
CREATE INDEX IF NOT EXISTS marketplace_price_idx ON marketplace_listings (price_cents);
-- Seed: plausible LOCAL listings written for the corridor. Titles/descriptions
-- composed for this seed — not scraped, not mirrored. source_url either points
-- back to the corridor site, opens an email/phone to the (test) seller, or is
-- NULL so the listing renders as info-only.
INSERT INTO marketplace_listings
(category, title, description, price_cents, condition, location_label, lat, lng, source_url, source_platform, seller_name)
VALUES
('Furniture', 'Mid-century walnut credenza', 'Original 1960s Drexel Declaration credenza. Refinished top, original hardware. Pickup in Sherman Oaks. Photos on request.', 145000, 'good', 'Sherman Oaks', 34.1517, -118.4490, NULL, 'local', 'Sherman Oaks neighbor'),
('Furniture', 'Restoration Hardware Cloud sofa, 9-ft', 'Pearl linen, three-piece. Bought 2023, lightly used, like-new. Pickup Studio City.', 385000, 'like-new', 'Studio City', 34.1444, -118.3947, NULL, 'local', 'Studio City neighbor'),
('Vehicles', '2019 Tesla Model 3 Long Range', 'Single owner, 38k miles, white/black, FSD transferable. Full service history.', 2895000, 'good', 'Encino', 34.1593, -118.5009, NULL, 'local', 'Encino neighbor'),
('Electronics', 'Apple Studio Display 27" 5K', 'Nano-texture glass, tilt + height stand. Original box. Pickup in Tarzana.', 135000, 'like-new', 'Tarzana', 34.1722, -118.5532, NULL, 'local', 'Tarzana neighbor'),
('Electronics', 'Sonos Arc + Sub Gen 3 bundle', 'Both white. Pickup only, no shipping.', 125000, 'good', 'Sherman Oaks', 34.1502, -118.4561, NULL, 'local', 'Sherman Oaks neighbor'),
('Home Goods', 'Vintage Persian Tabriz rug 9x12', 'Late-century, hand-knotted. Some wear at edges, no fading.', 78000, 'good', 'Encino', 34.1601, -118.5023, NULL, 'local', 'Encino neighbor'),
('Home Goods', 'Sub-Zero 36" built-in fridge — for parts','Door seal failure, compressor still good. Free if you haul it from the curb.', 0, 'parts', 'Woodland Hills', 34.1684, -118.6059, NULL, 'local', 'Woodland Hills neighbor'),
('Free', 'Free moving boxes — assorted sizes', '40+ boxes plus packing paper. Curbside in Studio City. First come.', NULL, 'good', 'Studio City', 34.1422, -118.3911, NULL, 'local', 'Studio City neighbor'),
('Services', 'Wallpaper installation — corridor pro', '20+ years installing for Sherman Oaks / Studio City interior designers. Insured.', NULL, NULL, 'Sherman Oaks', 34.1510, -118.4480, 'https://nationalpaperhangers.com', 'local', 'NPH installer'),
('Services', 'Interior design consultation, 1 hr', 'On-site walkthrough plus mood board. Corridor-based studio.', 25000, NULL, 'Studio City', 34.1448, -118.3955, NULL, 'local', 'Corridor studio'),
('Real Estate', '2BR / 2BA Sherman Oaks rental', 'Behind the boulevard. Hardwood, in-unit W/D, secure parking. 1-year lease.', 385000, NULL, 'Sherman Oaks', 34.1496, -118.4504, NULL, 'local', 'Sherman Oaks neighbor'),
('Real Estate', 'Studio City guest house — short term', 'Detached. Furnished. 30-day minimum. No smoking, no pets.', 460000, NULL, 'Studio City', 34.1429, -118.3922, NULL, 'local', 'Studio City neighbor'),
('Jobs', 'Front-of-house — neighborhood restaurant','Weekend dinner shifts. Tarzana. Hospitality experience preferred, will train.', NULL, NULL, 'Tarzana', 34.1715, -118.5524, NULL, 'local', 'Local employer'),
('Jobs', 'Showroom assistant — Sherman Oaks studio','Part-time. Email-and-spreadsheet competent. Design-curious.', NULL, NULL, 'Sherman Oaks', 34.1505, -118.4485, 'mailto:steve@designerwallcoverings.com', 'local', 'Designer Wallcoverings'),
('Clothing', 'Vintage Levi 501 lot — 5 pairs', 'All 32x32, big-E and red-tab. Sold as a set.', 42000, 'good', 'Studio City', 34.1438, -118.3938, NULL, 'local', 'Studio City neighbor'),
('Clothing', 'Pair of Hermes Oran sandals, size 38', 'Gold. Worn twice, original box. No trades.', 52000, 'like-new', 'Encino', 34.1597, -118.5015, NULL, 'local', 'Encino neighbor')
ON CONFLICT DO NOTHING;