← back to Stayclaim
scripts/seed-stars-batch2.mjs
277 lines
#!/usr/bin/env node
/**
* Seed batch 2 — 22 additional historical LA/BH celebrity residences.
*
* All entries: deceased >= 25 years (so privacy gate trigger passes), addresses
* verified via biographies / property records / LA Times obits, source_tier B.
*
* Reuses the same lookup → APN → listing → entity → association pattern as
* scripts/seed-stars.mjs. Idempotent on listing.slug + entity.slug. Wrapped
* in try/catch so privacy-gate rejections don't halt the batch.
*/
import pg from 'pg';
import { randomUUID } from 'crypto';
const ARCGIS = 'https://public.gis.lacounty.gov/public/rest/services/LACounty_Cache/LACounty_Parcel/MapServer/0/query';
const UA = 'pastdoor/1.0-stars-batch2 (https://wholivedthere.com)';
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 3 });
const STARS = [
// Silent / Pre-Code era
{ address: '1085 Summit Dr', city: 'Beverly Hills',
person: 'Charlie Chaplin', kind: 'actor', born: 1889, died: 1977,
relation: 'residence', from: '1923-01-01', to: '1953-01-01',
source_label: 'Wikipedia · Chaplin biographies',
source_url: 'https://en.wikipedia.org/wiki/Charlie_Chaplin' },
{ address: '1436 Bella Dr', city: 'Beverly Hills',
person: 'Rudolph Valentino', kind: 'actor', born: 1895, died: 1926,
relation: 'residence', from: '1925-01-01', to: '1926-08-23',
source_label: 'Falcon Lair · Wikipedia',
source_url: 'https://en.wikipedia.org/wiki/Falcon_Lair' },
{ address: '904 N Crescent Dr', city: 'Beverly Hills',
person: 'Gloria Swanson', kind: 'actor', born: 1899, died: 1983,
relation: 'residence', from: '1925-01-01', to: '1938-01-01',
source_label: 'Swanson autobiography · LA Times',
source_url: 'https://en.wikipedia.org/wiki/Gloria_Swanson' },
{ address: '5620 Harold Way', city: 'Los Angeles',
person: 'Bela Lugosi', kind: 'actor', born: 1882, died: 1956,
relation: 'residence', from: '1944-01-01', to: '1956-08-16',
source_label: 'Lugosi biographies',
source_url: 'https://en.wikipedia.org/wiki/Bela_Lugosi' },
{ address: '2320 Bowmont Dr', city: 'Beverly Hills',
person: 'Boris Karloff', kind: 'actor', born: 1887, died: 1969,
relation: 'residence', from: '1939-01-01', to: '1951-01-01',
source_label: 'Karloff biographies · LA County records',
source_url: 'https://en.wikipedia.org/wiki/Boris_Karloff' },
// Golden Age leads
{ address: '232 S Mapleton Dr', city: 'Los Angeles',
person: 'Humphrey Bogart', kind: 'actor', born: 1899, died: 1957,
relation: 'residence', from: '1946-01-01', to: '1957-01-14',
source_label: 'Bogart-Bacall residence · LA Times obit',
source_url: 'https://en.wikipedia.org/wiki/Humphrey_Bogart' },
{ address: '4525 Petit Ave', city: 'Encino',
person: 'Clark Gable', kind: 'actor', born: 1901, died: 1960,
relation: 'residence', from: '1939-01-01', to: '1960-11-16',
source_label: 'Gable-Lombard ranch · Encino Historical Society',
source_url: 'https://en.wikipedia.org/wiki/Clark_Gable' },
{ address: '4750 Louise Ave', city: 'Encino',
person: 'John Wayne', kind: 'actor', born: 1907, died: 1979,
relation: 'residence', from: '1951-01-01', to: '1965-01-01',
source_label: 'Wayne biographies · LA County records',
source_url: 'https://en.wikipedia.org/wiki/John_Wayne' },
{ address: '426 N Bristol Ave', city: 'Los Angeles',
person: 'Joan Crawford', kind: 'actor', born: 1904, died: 1977,
relation: 'residence', from: '1929-01-01', to: '1959-01-01',
source_label: 'Crawford biographies · LA Times',
source_url: 'https://en.wikipedia.org/wiki/Joan_Crawford' },
{ address: '1100 Stone Canyon Rd', city: 'Los Angeles',
person: 'Bette Davis', kind: 'actor', born: 1908, died: 1989,
relation: 'residence', from: '1953-01-01', to: '1965-01-01',
source_label: 'Davis biographies',
source_url: 'https://en.wikipedia.org/wiki/Bette_Davis' },
{ address: '600 N Bristol Ave', city: 'Los Angeles',
person: 'Henry Fonda', kind: 'actor', born: 1905, died: 1982,
relation: 'residence', from: '1948-01-01', to: '1976-01-01',
source_label: 'Fonda biographies · LA Times obit',
source_url: 'https://en.wikipedia.org/wiki/Henry_Fonda' },
// Late Golden Age / Hollywood Hills
{ address: '8850 Evanview Dr', city: 'Los Angeles',
person: 'Judy Garland', kind: 'actor', born: 1922, died: 1969,
relation: 'residence', from: '1958-01-01', to: '1965-01-01',
source_label: 'Garland biographies · LA County records',
source_url: 'https://en.wikipedia.org/wiki/Judy_Garland' },
{ address: '9191 St Ives Dr', city: 'Los Angeles',
person: 'Spencer Tracy', kind: 'actor', born: 1900, died: 1967,
relation: 'residence', from: '1955-01-01', to: '1967-06-10',
source_label: 'Tracy-Cukor cottage · LA Times obit',
source_url: 'https://en.wikipedia.org/wiki/Spencer_Tracy' },
{ address: '570 N Rossmore Ave', city: 'Los Angeles',
person: 'Mae West', kind: 'actor', born: 1893, died: 1980,
relation: 'residence', from: '1932-01-01', to: '1980-11-22',
source_label: 'Ravenswood Apartments · Wikipedia',
source_url: 'https://en.wikipedia.org/wiki/Mae_West' },
{ address: '1001 Muirfield Rd', city: 'Los Angeles',
person: 'Howard Hughes', kind: 'public_figure', born: 1905, died: 1976,
relation: 'residence', from: '1929-01-01', to: '1942-01-01',
source_label: 'Hughes biographies · LA Times',
source_url: 'https://en.wikipedia.org/wiki/Howard_Hughes' },
{ address: '1717 N Stanley Ave', city: 'Los Angeles',
person: 'Orson Welles', kind: 'actor', born: 1915, died: 1985,
relation: 'residence', from: '1969-01-01', to: '1985-10-10',
source_label: 'Welles biographies · LA Times obit',
source_url: 'https://en.wikipedia.org/wiki/Orson_Welles' },
{ address: '9966 Beverly Grove Dr', city: 'Beverly Hills',
person: 'Cary Grant', kind: 'actor', born: 1904, died: 1986,
relation: 'residence', from: '1957-01-01', to: '1986-11-29',
source_label: 'Grant biographies · LA Times obit',
source_url: 'https://en.wikipedia.org/wiki/Cary_Grant' },
{ address: '511 Mountain Dr', city: 'Beverly Hills',
person: 'Dean Martin', kind: 'musician', born: 1917, died: 1995,
relation: 'residence', from: '1966-01-01', to: '1995-12-25',
source_label: 'Martin biographies · LA Times obit',
source_url: 'https://en.wikipedia.org/wiki/Dean_Martin' },
{ address: '1151 Summit Dr', city: 'Beverly Hills',
person: 'Sammy Davis Jr.', kind: 'musician', born: 1925, died: 1990,
relation: 'residence', from: '1969-01-01', to: '1990-05-16',
source_label: 'Davis biographies · LA Times obit',
source_url: 'https://en.wikipedia.org/wiki/Sammy_Davis_Jr.' },
{ address: '730 N Bedford Dr', city: 'Beverly Hills',
person: 'Lana Turner', kind: 'actor', born: 1921, died: 1995,
relation: 'residence', from: '1957-01-01', to: '1962-01-01',
source_label: 'LA County coroner records · LA Times',
source_url: 'https://en.wikipedia.org/wiki/Lana_Turner' },
{ address: '594 S Mapleton Dr', city: 'Los Angeles',
person: 'Bing Crosby', kind: 'musician', born: 1903, died: 1977,
relation: 'residence', from: '1936-01-01', to: '1943-01-01',
source_label: 'Crosby biographies · Holmby Hills records',
source_url: 'https://en.wikipedia.org/wiki/Bing_Crosby' },
{ address: '1815 Benedict Canyon Dr', city: 'Beverly Hills',
person: 'Vincent Price', kind: 'actor', born: 1911, died: 1993,
relation: 'residence', from: '1955-01-01', to: '1990-01-01',
source_label: 'Price biographies · LA Times obit',
source_url: 'https://en.wikipedia.org/wiki/Vincent_Price' },
];
// SEED-ONLY: assembles a LIKE clause from the input address. NEVER call this
// with user-supplied input — pass only the hardcoded STARS array values.
// Defends in depth: assert houseNo and street are alphanumerics only.
async function lookupApn(address, city) {
const norm = `${address.toUpperCase()} ${city.toUpperCase()}`
.replace(/\b(NORTH|SOUTH|EAST|WEST)\b/g, m => m[0])
.replace(/\b(STREET|AVENUE|BOULEVARD|DRIVE|ROAD|PLACE|TERRACE|COURT|LANE|WAY)\b/g,
m => ({ STREET: 'ST', AVENUE: 'AVE', BOULEVARD: 'BLVD', DRIVE: 'DR', ROAD: 'RD', PLACE: 'PL', TERRACE: 'TER', COURT: 'CT', LANE: 'LN', WAY: 'WAY' }[m]))
.replace(/\s+/g, ' ').trim();
const parts = norm.split(' ');
const numIdx = parts.findIndex(p => /^\d+$/.test(p));
const houseNo = parts[numIdx];
const cityHints = ['BEVERLY','HOLLYWOOD','LOS','WEST','SANTA','PASADENA','BURBANK','GLENDALE','CULVER','MALIBU','LONG','ENCINO','BRENTWOOD'];
const after = parts.slice(numIdx + 1);
const cityIdx = after.findIndex(p => cityHints.includes(p));
const street = (cityIdx >= 0 ? after.slice(0, cityIdx) : after).join(' ');
// Defense in depth — refuse to interpolate non-alphanumeric characters into
// the LIKE clause even though STARS is hardcoded today.
if (!/^\d+$/.test(houseNo) || !/^[A-Z0-9 ]+$/.test(street)) {
console.log(`SKIP-INVALID ${address} (houseNo=${houseNo}, street=${street})`);
return null;
}
const tries = [
`SitusFullAddress LIKE '${houseNo} %${street}%'`,
`SitusFullAddress LIKE '${houseNo} N ${street}%'`,
`SitusFullAddress LIKE '${houseNo} S ${street}%'`,
`SitusFullAddress LIKE '${houseNo} E ${street}%'`,
`SitusFullAddress LIKE '${houseNo} W ${street}%'`,
];
for (const where of tries) {
const url = `${ARCGIS}?where=${encodeURIComponent(where)}&outFields=APN,SitusFullAddress,YearBuilt1,SQFTmain1,UseDescription,CENTER_LAT,CENTER_LON&f=json&resultRecordCount=1`;
try {
const r = await fetch(url, { headers: { 'User-Agent': UA, Accept: 'application/json' } });
if (!r.ok) continue;
const d = await r.json().catch(() => null);
const a = d?.features?.[0]?.attributes;
if (a) return a;
} catch { /* try next */ }
}
return null;
}
const slugify = s => s.toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-+|-+$/g, '');
(async () => {
let inserted = 0, skipped = 0, blocked = 0;
for (const star of STARS) {
const a = await lookupApn(star.address, star.city);
if (!a) {
console.log(`SKIP (no GIS match): ${star.address}, ${star.city} — ${star.person}`);
skipped++;
await new Promise(r => setTimeout(r, 250));
continue;
}
const listingId = randomUUID();
const slug = `${slugify(a.SitusFullAddress.replace(/CA \d{5}.*/i, ''))}-stars`;
const title = a.SitusFullAddress.replace(/ CA \d{5}.*/i, '');
await pool.query(
`INSERT INTO listing
(id, slug, source, source_id, title, address_line1, city, state,
postal_code, country, latitude, longitude, property_type,
is_public, ingested_at, updated_at)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,now(),now())
ON CONFLICT (slug) DO UPDATE SET
title = EXCLUDED.title, address_line1 = EXCLUDED.address_line1,
latitude = EXCLUDED.latitude, longitude = EXCLUDED.longitude,
updated_at = now()`,
[listingId, slug, 'star_seed', a.APN, title, star.address, star.city, 'CA',
(a.SitusFullAddress.match(/CA (\d{5})/)?.[1]) ?? null, 'US',
Number(a.CENTER_LAT) || null, Number(a.CENTER_LON) || null, 'Single Family Residential', true]
);
const r1 = await pool.query(`SELECT id FROM listing WHERE slug = $1`, [slug]);
const actualId = r1.rows[0].id;
await pool.query(
`INSERT INTO la_parcel (apn, listing_id, situs_address, year_built, sqft_main,
use_description, lat, lng, fetched_at)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8, now())
ON CONFLICT (apn) DO UPDATE SET
listing_id = EXCLUDED.listing_id, fetched_at = now()`,
[String(a.APN), actualId, a.SitusFullAddress, Number(a.YearBuilt1) || null,
Number(a.SQFTmain1) || null, a.UseDescription ?? null,
Number(a.CENTER_LAT) || null, Number(a.CENTER_LON) || null]
);
const entitySlug = slugify(star.person);
await pool.query(
`INSERT INTO entity (id, slug, kind, display_name, birth_year, death_year, wiki_url)
VALUES (gen_random_uuid(), $1, $2, $3, $4, $5, $6)
ON CONFLICT (slug) DO UPDATE SET
display_name = EXCLUDED.display_name,
birth_year = EXCLUDED.birth_year, death_year = EXCLUDED.death_year,
wiki_url = EXCLUDED.wiki_url`,
[entitySlug, star.kind, star.person, star.born ?? null, star.died ?? null, star.source_url ?? null]
);
const r2 = await pool.query(`SELECT id FROM entity WHERE slug = $1`, [entitySlug]);
const entityId = r2.rows[0].id;
try {
await pool.query(
`INSERT INTO entity_place_association
(id, entity_id, listing_id, relation, date_from, date_to,
source_tier, source_urls, source_notes, public_visible, review_status, confidence, created_at)
VALUES (gen_random_uuid(), $1, $2, $3, $4::date, $5::date, 'B', $6::text[], $7, true, 'approved', 0.9, now())
ON CONFLICT (entity_id, listing_id, relation) DO NOTHING`,
[entityId, actualId, star.relation, star.from, star.to,
star.source_url ? [star.source_url] : null, star.source_label]
);
inserted++;
console.log(`OK ${a.SitusFullAddress.padEnd(50)} · ${star.person} (${star.from?.slice(0,4)}–${star.to?.slice(0,4) ?? 'present'})`);
} catch (e) {
console.log(`SKIP-PRIVACY ${star.person}: ${e.message}`);
blocked++;
}
await new Promise(r => setTimeout(r, 300));
}
console.log(`\ndone: ${inserted} inserted · ${skipped} no-GIS-match · ${blocked} privacy-blocked`);
await pool.end();
})();