← back to Bubbesblock
Add BubbesBlock Postgres DB (posts+comments+config), migration, DB-backed server with JSON fallback; live on bubbesblock.com
0110f825cb542a3954722af08cd1292cbaceb583 · 2026-06-19 15:24:21 -0700 · Steve
Files touched
A db/schema.sqlM package.jsonA scripts/db-setup.shA scripts/migrate.jsM server.js
Diff
commit 0110f825cb542a3954722af08cd1292cbaceb583
Author: Steve <steve@designerwallcoverings.com>
Date: Fri Jun 19 15:24:21 2026 -0700
Add BubbesBlock Postgres DB (posts+comments+config), migration, DB-backed server with JSON fallback; live on bubbesblock.com
---
db/schema.sql | 44 ++++++++++++++++++++++++
package.json | 4 ++-
scripts/db-setup.sh | 31 +++++++++++++++++
scripts/migrate.js | 57 +++++++++++++++++++++++++++++++
server.js | 98 ++++++++++++++++++++++++++++++++++++++++++-----------
5 files changed, 213 insertions(+), 21 deletions(-)
diff --git a/db/schema.sql b/db/schema.sql
new file mode 100644
index 0000000..13994a8
--- /dev/null
+++ b/db/schema.sql
@@ -0,0 +1,44 @@
+-- BubbesBlock schema — block posts + comments + small site config.
+CREATE TABLE IF NOT EXISTS site_config (
+ k text PRIMARY KEY,
+ v jsonb
+);
+
+CREATE TABLE IF NOT EXISTS posts (
+ id text PRIMARY KEY,
+ position int,
+ author text NOT NULL,
+ initials text,
+ color text,
+ address text,
+ time_label text,
+ edited boolean DEFAULT false,
+ category text,
+ cat_color text,
+ body jsonb NOT NULL DEFAULT '[]'::jsonb,
+ photo jsonb,
+ reactions jsonb NOT NULL DEFAULT '[]'::jsonb,
+ reacted_by text,
+ shares int DEFAULT 0,
+ more_comments int DEFAULT 0,
+ source text DEFAULT 'seed',
+ created_at timestamptz DEFAULT now()
+);
+
+CREATE TABLE IF NOT EXISTS comments (
+ id bigserial PRIMARY KEY,
+ post_id text NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
+ position int,
+ author text NOT NULL,
+ initials text,
+ color text,
+ nb text,
+ time_label text,
+ thanks int DEFAULT 0,
+ is_reply boolean DEFAULT false,
+ body text,
+ created_at timestamptz DEFAULT now()
+);
+
+CREATE INDEX IF NOT EXISTS idx_posts_position ON posts(position);
+CREATE INDEX IF NOT EXISTS idx_comments_post ON comments(post_id, position);
diff --git a/package.json b/package.json
index 6b96afd..06d8ffe 100644
--- a/package.json
+++ b/package.json
@@ -8,6 +8,8 @@
"dev": "node server.js"
},
"dependencies": {
- "express": "^4.19.2"
+ "dotenv": "^16.4.5",
+ "express": "^4.19.2",
+ "pg": "^8.12.0"
}
}
diff --git a/scripts/db-setup.sh b/scripts/db-setup.sh
new file mode 100755
index 0000000..4f394bb
--- /dev/null
+++ b/scripts/db-setup.sh
@@ -0,0 +1,31 @@
+#!/usr/bin/env bash
+# Idempotent + self-healing: create the bubbesblock role+db, (re)write .env with a
+# fresh known password, apply schema. Runs ON the Kamatera box (uses sudo -u postgres).
+set -euo pipefail
+PROJ="$(cd "$(dirname "$0")/.." && pwd)"
+PW="$(openssl rand -hex 20)"
+
+# role (create or reset password to the known value) — SQL via stdin, not argv
+sudo -u postgres psql -v ON_ERROR_STOP=1 -q <<SQL
+DO \$do\$ BEGIN
+ IF EXISTS (SELECT FROM pg_roles WHERE rolname='bubbesblock') THEN
+ ALTER ROLE bubbesblock LOGIN PASSWORD '${PW}';
+ ELSE
+ CREATE ROLE bubbesblock LOGIN PASSWORD '${PW}';
+ END IF;
+END \$do\$;
+SQL
+
+# database (owned by the role)
+sudo -u postgres psql -tAc "SELECT 1 FROM pg_database WHERE datname='bubbesblock'" | grep -q 1 \
+ || sudo -u postgres createdb -O bubbesblock bubbesblock
+
+# app env (600), password never echoed to stdout
+umask 077
+printf 'DATABASE_URL=postgres://bubbesblock:%s@127.0.0.1:5432/bubbesblock\nPORT=9825\n' "$PW" > "$PROJ/.env"
+chmod 600 "$PROJ/.env"
+
+# schema
+set -a; . "$PROJ/.env"; set +a
+psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -q -f "$PROJ/db/schema.sql"
+echo "DB ready: $(psql "$DATABASE_URL" -tAc "SELECT current_database()||' / '||current_user")"
diff --git a/scripts/migrate.js b/scripts/migrate.js
new file mode 100644
index 0000000..e602bec
--- /dev/null
+++ b/scripts/migrate.js
@@ -0,0 +1,57 @@
+'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]
+ );
+ }
+ }
+
+ const np = await c.query('SELECT count(*) FROM posts');
+ const nc = await c.query('SELECT count(*) FROM comments');
+ console.log(`migrated: ${np.rows[0].count} posts, ${nc.rows[0].count} comments`);
+ await c.end();
+})().catch(e => { console.error(e); process.exit(1); });
diff --git a/server.js b/server.js
index ee56baa..0020bb3 100644
--- a/server.js
+++ b/server.js
@@ -2,48 +2,106 @@
const express = require('express');
const fs = require('fs');
const path = require('path');
+require('dotenv').config({ path: path.join(__dirname, '.env') });
const app = express();
const PORT = process.env.PORT || 3201;
const DATA = path.join(__dirname, 'data', 'posts.json');
+let pool = null;
+if (process.env.DATABASE_URL) {
+ const { Pool } = require('pg');
+ pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 4 });
+ pool.on('error', e => console.error('pg pool error:', e.message));
+}
+
app.use(express.json());
app.use(express.static(path.join(__dirname, 'public')));
+app.get('/healthz', (_req, res) => res.json({ ok: true, service: 'bubbesblock', db: !!pool }));
-// Health probe for deploy smoke-test + uptime canary.
-app.get('/healthz', (_req, res) => res.json({ ok: true, service: 'bubbesblock' }));
+// ---- JSON fallback (used if the DB is unreachable) ----
+function jsonAll() { return JSON.parse(fs.readFileSync(DATA, 'utf8')); }
-// Read fresh each request so seed edits show up without a restart.
-function load() {
- return JSON.parse(fs.readFileSync(DATA, 'utf8'));
+// ---- DB shapers (map snake_case rows -> the camelCase the front end expects) ----
+function shapePost(p, commentCount) {
+ return {
+ id: p.id, author: p.author, initials: p.initials, color: p.color,
+ address: p.address, time: p.time_label, edited: p.edited,
+ category: p.category, catColor: p.cat_color, body: p.body, photo: p.photo,
+ reactions: p.reactions, reactedBy: p.reacted_by, shares: p.shares,
+ moreComments: p.more_comments, commentCount
+ };
+}
+function shapeComment(m) {
+ return {
+ author: m.author, initials: m.initials, color: m.color, nb: m.nb,
+ time: m.time_label, thanks: m.thanks, reply: m.is_reply, body: m.body
+ };
+}
+async function config(client) {
+ const r = await client.query("SELECT k, v FROM site_config WHERE k IN ('me','neighborhood')");
+ const o = {}; r.rows.forEach(x => (o[x.k] = x.v)); return o;
}
-// Feed: lightweight list (no comment bodies) for the home page.
-app.get('/api/posts', (req, res) => {
+// ---- Feed ----
+app.get('/api/posts', async (_req, res) => {
+ if (pool) {
+ let client;
+ try {
+ client = await pool.connect();
+ const cfg = await config(client);
+ const r = await client.query(
+ `SELECT p.*, (SELECT count(*) FROM comments c WHERE c.post_id = p.id) AS live
+ FROM posts p ORDER BY p.position`);
+ const posts = r.rows.map(p => {
+ const out = shapePost(p, Number(p.live) + (p.more_comments || 0));
+ delete out.moreComments; // feed only needs the total
+ return out;
+ });
+ return res.json({ neighborhood: cfg.neighborhood, me: cfg.me, posts });
+ } catch (e) {
+ console.error('PG feed failed, JSON fallback:', e.message);
+ } finally { if (client) client.release(); }
+ }
+ // fallback
try {
- const d = load();
- const feed = d.posts.map(p => ({
- id: p.id, author: p.author, initials: p.initials, color: p.color,
- address: p.address, time: p.time, edited: !!p.edited,
- category: p.category, catColor: p.catColor, body: p.body,
- photo: p.photo, reactions: p.reactions, reactedBy: p.reactedBy,
- shares: p.shares, commentCount: (p.comments ? p.comments.length : 0) + (p.moreComments || 0)
+ const d = jsonAll();
+ const posts = d.posts.map(p => ({
+ id: p.id, author: p.author, initials: p.initials, color: p.color, address: p.address,
+ time: p.time, edited: !!p.edited, category: p.category, catColor: p.catColor, body: p.body,
+ photo: p.photo, reactions: p.reactions, reactedBy: p.reactedBy, shares: p.shares,
+ commentCount: (p.comments ? p.comments.length : 0) + (p.moreComments || 0)
}));
- res.json({ neighborhood: d.neighborhood, me: d.me, posts: feed });
+ res.json({ neighborhood: d.neighborhood, me: d.me, posts });
} catch (e) { res.status(500).json({ error: String(e) }); }
});
-// Single post with full comment thread for the detail page.
-app.get('/api/posts/:id', (req, res) => {
+// ---- Single post + thread ----
+app.get('/api/posts/:id', async (req, res) => {
+ if (pool) {
+ let client;
+ try {
+ client = await pool.connect();
+ const cfg = await config(client);
+ const pr = await client.query('SELECT * FROM posts WHERE id=$1', [req.params.id]);
+ if (!pr.rows.length) return res.status(404).json({ error: 'not found' });
+ const cr = await client.query(
+ 'SELECT * FROM comments WHERE post_id=$1 ORDER BY position', [req.params.id]);
+ const post = shapePost(pr.rows[0], cr.rows.length + (pr.rows[0].more_comments || 0));
+ post.comments = cr.rows.map(shapeComment);
+ return res.json({ neighborhood: cfg.neighborhood, me: cfg.me, post });
+ } catch (e) {
+ console.error('PG post failed, JSON fallback:', e.message);
+ } finally { if (client) client.release(); }
+ }
try {
- const d = load();
+ const d = jsonAll();
const post = d.posts.find(p => p.id === req.params.id);
if (!post) return res.status(404).json({ error: 'not found' });
res.json({ neighborhood: d.neighborhood, me: d.me, post });
} catch (e) { res.status(500).json({ error: String(e) }); }
});
-// Pretty post URL -> serve the detail page (client reads ?id or the path).
app.get('/p/:id', (_req, res) => res.sendFile(path.join(__dirname, 'public', 'post.html')));
-app.listen(PORT, () => console.log(`BubbesBlock running on http://localhost:${PORT}`));
+app.listen(PORT, () => console.log(`BubbesBlock on http://localhost:${PORT} (db=${!!pool})`));
← 18d989b Add /healthz route + deploy config; live on bubbesblock.com
·
back to Bubbesblock
·
Add Opportunity Alerts surface: 12 similar-but-not-exact hom e1c77a4 →