← back to Bubbesblock
scripts/migrate.js
136 lines
'use strict';
// Load every post from data/posts.json into the bubbesblock DB. Idempotent (upsert
// posts, replace each post's comments). Marks rows with their `source` so future
// Nextdoor-export imports can be distinguished from the seed.
const { Client } = require('pg');
const fs = require('fs');
const path = require('path');
require('dotenv').config({ path: path.join(__dirname, '..', '.env') });
const seed = JSON.parse(fs.readFileSync(path.join(__dirname, '..', 'data', 'posts.json'), 'utf8'));
(async () => {
const c = new Client({ connectionString: process.env.DATABASE_URL });
await c.connect();
await c.query(
`INSERT INTO site_config(k,v) VALUES('neighborhood',$1),('me',$2)
ON CONFLICT(k) DO UPDATE SET v=EXCLUDED.v`,
[JSON.stringify(seed.neighborhood), JSON.stringify(seed.me)]
);
for (let i = 0; i < seed.posts.length; i++) {
const p = seed.posts[i];
await c.query(
`INSERT INTO posts(id,position,author,initials,color,address,time_label,edited,
category,cat_color,body,photo,reactions,reacted_by,shares,more_comments,source)
VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17)
ON CONFLICT(id) DO UPDATE SET
position=EXCLUDED.position, author=EXCLUDED.author, initials=EXCLUDED.initials,
color=EXCLUDED.color, address=EXCLUDED.address, time_label=EXCLUDED.time_label,
edited=EXCLUDED.edited, category=EXCLUDED.category, cat_color=EXCLUDED.cat_color,
body=EXCLUDED.body, photo=EXCLUDED.photo, reactions=EXCLUDED.reactions,
reacted_by=EXCLUDED.reacted_by, shares=EXCLUDED.shares,
more_comments=EXCLUDED.more_comments, source=EXCLUDED.source`,
[p.id, i, p.author, p.initials, p.color, p.address, p.time, !!p.edited,
p.category, p.catColor, JSON.stringify(p.body || []),
p.photo ? JSON.stringify(p.photo) : null, JSON.stringify(p.reactions || []),
p.reactedBy, p.shares || 0, p.moreComments || 0, p.source || 'seed']
);
await c.query('DELETE FROM comments WHERE post_id=$1', [p.id]);
const cs = p.comments || [];
for (let j = 0; j < cs.length; j++) {
const m = cs[j];
await c.query(
`INSERT INTO comments(post_id,position,author,initials,color,nb,time_label,thanks,is_reply,body)
VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)`,
[p.id, j, m.author, m.initials, m.color, m.nb, m.time, m.thanks || 0, !!m.reply, m.body]
);
}
}
// ---- opportunity leads (optional file) ----
const oppPath = path.join(__dirname, '..', 'data', 'opportunities.json');
if (fs.existsSync(oppPath)) {
const od = JSON.parse(fs.readFileSync(oppPath, 'utf8'));
await c.query(
`INSERT INTO site_config(k,v) VALUES('service_area',$1),('categories_for_you',$2)
ON CONFLICT(k) DO UPDATE SET v=EXCLUDED.v`,
[JSON.stringify(od.service_area || {}), JSON.stringify(od.categories_for_you || [])]
);
for (let i = 0; i < (od.opportunities || []).length; i++) {
const o = od.opportunities[i];
await c.query(
`INSERT INTO opportunities(id,position,neighbor,initials,color,category,for_you,area,address,time_label,responses,locked,body)
VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13)
ON CONFLICT(id) DO UPDATE SET
position=EXCLUDED.position, neighbor=EXCLUDED.neighbor, initials=EXCLUDED.initials,
color=EXCLUDED.color, category=EXCLUDED.category, for_you=EXCLUDED.for_you,
area=EXCLUDED.area, address=EXCLUDED.address, time_label=EXCLUDED.time_label,
responses=EXCLUDED.responses, locked=EXCLUDED.locked, body=EXCLUDED.body`,
[o.id, i, o.neighbor, o.initials, o.color, o.category, !!o.for_you, o.area, o.address,
o.time, o.responses || 0, !!o.locked, o.body]
);
}
}
// ---- users: me + every author/neighbor seen across content (idempotent) ----
const uid = name => 'u-' + String(name).toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-|-$/g, '');
const users = new Map();
const addUser = (name, initials, color, address, verified) => {
if (!name) return; const id = uid(name);
if (!users.has(id)) users.set(id, { id, name, initials: initials || name.slice(0, 2).toUpperCase(), color: color || 'a1', address: address || null, verified: !!verified });
};
const me = seed.me; addUser(me.name, me.initials, me.color, me.address, me.verified);
for (const p of seed.posts) { addUser(p.author, p.initials, p.color, p.address, true); for (const m of (p.comments || [])) addUser(m.author, m.initials, m.color, null, true); }
if (fs.existsSync(path.join(__dirname, '..', 'data', 'opportunities.json'))) {
const od = JSON.parse(fs.readFileSync(path.join(__dirname, '..', 'data', 'opportunities.json'), 'utf8'));
for (const o of (od.opportunities || [])) addUser(o.neighbor, o.initials, o.color, o.address, true);
}
for (const u of users.values()) {
await c.query(`INSERT INTO users(id,name,initials,color,address,verified) VALUES($1,$2,$3,$4,$5,$6)
ON CONFLICT(id) DO UPDATE SET name=EXCLUDED.name,initials=EXCLUDED.initials,color=EXCLUDED.color,
address=COALESCE(EXCLUDED.address,users.address),verified=users.verified OR EXCLUDED.verified`,
[u.id, u.name, u.initials, u.color, u.address, u.verified]);
}
// ---- groups + events ----
const commPath = path.join(__dirname, '..', 'data', 'community.json');
if (fs.existsSync(commPath)) {
const cm = JSON.parse(fs.readFileSync(commPath, 'utf8'));
for (let i = 0; i < (cm.groups || []).length; i++) { const g = cm.groups[i];
await c.query(`INSERT INTO groups(slug,name,emoji,color,members,blurb,position) VALUES($1,$2,$3,$4,$5,$6,$7)
ON CONFLICT(slug) DO UPDATE SET name=EXCLUDED.name,emoji=EXCLUDED.emoji,color=EXCLUDED.color,members=EXCLUDED.members,blurb=EXCLUDED.blurb,position=EXCLUDED.position`,
[g.slug, g.name, g.emoji, g.color, g.members, g.blurb, i]); }
for (let i = 0; i < (cm.events || []).length; i++) { const e = cm.events[i];
await c.query(`INSERT INTO events(id,title,emoji,when_label,location,going,body,position) VALUES($1,$2,$3,$4,$5,$6,$7,$8)
ON CONFLICT(id) DO UPDATE SET title=EXCLUDED.title,emoji=EXCLUDED.emoji,when_label=EXCLUDED.when_label,location=EXCLUDED.location,going=EXCLUDED.going,body=EXCLUDED.body,position=EXCLUDED.position`,
[e.id, e.title, e.emoji, e.when_label, e.location, e.going, e.body, i]); }
}
// ---- starter notifications + one inbox conversation for the demo user (only if none yet) ----
const meId = uid(me.name);
if ((await c.query('SELECT count(*) FROM notifications WHERE user_id=$1', [meId])).rows[0].count === '0') {
const notifs = [
['react', 'Goldie Fein', 'thanked your comment on “Honey cake on the stoop”', '/p/honey-cake'],
['comment', 'Sol Katz', 'replied to the Block Party post', '/p/block-party'],
['opportunity', 'BubbesBlock', 'New lead matched to Wallcoverings near you', '/opportunities'],
['event', 'Shabbat Dinner Club', 'reminded you: Shabbat dinner Friday 6:30', '/events']
];
for (const [t, a, x, l] of notifs) await c.query('INSERT INTO notifications(user_id,type,actor,text,link) VALUES($1,$2,$3,$4,$5)', [meId, t, a, x, l]);
const conv = await c.query('INSERT INTO conversations(user_id,peer,peer_initials,peer_color) VALUES($1,$2,$3,$4) RETURNING id', [meId, 'Ruth Bernstein', 'RB', 'a3']);
const cid = conv.rows[0].id;
await c.query('INSERT INTO messages(conv_id,from_me,body) VALUES($1,$2,$3),($1,$4,$5)', [cid, false, 'Steve! Can I still borrow that folding table for Sunday? 🙏', true, 'Of course — I’ll drop it by Saturday. Save me a slice of honey cake 😉']);
}
const np = await c.query('SELECT count(*) FROM posts');
const nc = await c.query('SELECT count(*) FROM comments');
const no = await c.query('SELECT count(*) FROM opportunities');
const nu = await c.query('SELECT count(*) FROM users');
const ng = await c.query('SELECT count(*) FROM groups');
const ne = await c.query('SELECT count(*) FROM events');
console.log(`migrated: ${np.rows[0].count} posts, ${nc.rows[0].count} comments, ${no.rows[0].count} opportunities, ${nu.rows[0].count} users, ${ng.rows[0].count} groups, ${ne.rows[0].count} events`);
await c.end();
})().catch(e => { console.error(e); process.exit(1); });