← back to Costa Rica
costa-rica: BEST WAY TO CONTACT — contact-card endpoint (whatsapp/tel/mailto/website) + in-app messenger (migration 007 threads/messages, message/threads endpoints); E2E verified — TK-10346
c05bcd280d0e25ea460cf95719cb846bfb3d5422 · 2026-08-07 11:44:24 -0700 · Steve
Files touched
M routes/app.jsA scripts/migrate_007_messages.sql
Diff
commit c05bcd280d0e25ea460cf95719cb846bfb3d5422
Author: Steve <steve@designerwallcoverings.com>
Date: Fri Aug 7 11:44:24 2026 -0700
costa-rica: BEST WAY TO CONTACT — contact-card endpoint (whatsapp/tel/mailto/website) + in-app messenger (migration 007 threads/messages, message/threads endpoints); E2E verified — TK-10346
---
routes/app.js | 55 ++++++++++++++++++++++++++++++++++++++++
scripts/migrate_007_messages.sql | 19 ++++++++++++++
2 files changed, 74 insertions(+)
diff --git a/routes/app.js b/routes/app.js
index cd573c3..5426009 100644
--- a/routes/app.js
+++ b/routes/app.js
@@ -315,6 +315,61 @@ router.post('/contacts/:id/invite', authRequired, async (req, res) => {
ok(res, { invite_link: link });
});
+// ---------------------------------------------------------------- contact + in-app messenger
+// Contact card for a listing: the real channels for the big buttons.
+router.get('/listings/:slug/contact', async (req, res) => {
+ const { rows: [p] } = await pool.query(
+ `SELECT p.id, p.name, p.phone, p.email, p.website FROM places p WHERE p.slug=$1`, [req.params.slug]);
+ if (!p) return bad(res, 404, 'not found');
+ const wa = p.phone ? normalizePhone(p.phone) : null;
+ ok(res, { contact: {
+ name: p.name,
+ whatsapp: wa ? `https://wa.me/${wa.replace(/^\+/, '')}` : null,
+ phone: p.phone || null,
+ tel: p.phone ? `tel:${normalizePhone(p.phone)}` : null,
+ email: p.email || null,
+ mailto: p.email ? `mailto:${p.email}` : null,
+ website: p.website || null,
+ in_app: true, // our own messenger always available
+ }});
+});
+
+// Open (or reuse) an in-app thread with a listing and post the first message.
+router.post('/listings/:slug/message', authRequired, async (req, res) => {
+ const body = (req.body?.body || '').trim();
+ if (!body) return bad(res, 400, 'message body required');
+ const { rows: [p] } = await pool.query(
+ `SELECT p.id, pb.host_id FROM places p LEFT JOIN place_booking pb ON pb.place_id=p.id WHERE p.slug=$1`, [req.params.slug]);
+ if (!p) return bad(res, 404, 'listing not found');
+ const { rows: [t] } = await pool.query(
+ `INSERT INTO threads (place_id, traveler_id, host_id) VALUES ($1,$2,$3)
+ ON CONFLICT (place_id, traveler_id) DO UPDATE SET place_id=EXCLUDED.place_id RETURNING id`,
+ [p.id, req.user.sub, p.host_id || null]);
+ await pool.query(
+ `INSERT INTO messages (thread_id, sender_id, sender_role, body) VALUES ($1,$2,'traveler',$3)`,
+ [t.id, req.user.sub, body]);
+ ok(res, { thread_id: t.id, sent: true });
+});
+
+// My message threads.
+router.get('/threads', authRequired, async (req, res) => {
+ const { rows } = await pool.query(
+ `SELECT t.id, p.name AS place_name, p.slug AS place_slug,
+ (SELECT body FROM messages m WHERE m.thread_id=t.id ORDER BY created_at DESC LIMIT 1) last_message,
+ (SELECT created_at FROM messages m WHERE m.thread_id=t.id ORDER BY created_at DESC LIMIT 1) last_at
+ FROM threads t JOIN places p ON p.id=t.place_id
+ WHERE t.traveler_id=$1 ORDER BY last_at DESC NULLS LAST`, [req.user.sub]);
+ ok(res, { threads: rows });
+});
+
+// Messages in a thread.
+router.get('/threads/:id', authRequired, async (req, res) => {
+ const { rows: [t] } = await pool.query(`SELECT * FROM threads WHERE id=$1 AND traveler_id=$2`, [req.params.id, req.user.sub]);
+ if (!t) return bad(res, 404, 'not found');
+ const { rows } = await pool.query(`SELECT sender_role, body, created_at FROM messages WHERE thread_id=$1 ORDER BY created_at`, [t.id]);
+ ok(res, { messages: rows });
+});
+
// ---------------------------------------------------------------- host onboarding
router.post('/host/apply', authRequired, async (req, res) => {
const { legal_name, cedula, country = 'CR' } = req.body || {};
diff --git a/scripts/migrate_007_messages.sql b/scripts/migrate_007_messages.sql
new file mode 100644
index 0000000..3780cf7
--- /dev/null
+++ b/scripts/migrate_007_messages.sql
@@ -0,0 +1,19 @@
+-- In-app messenger: a traveler ↔ listing/host thread (our own channel, so
+-- contact never has to leave the app). Complements WhatsApp/phone/email.
+CREATE TABLE IF NOT EXISTS threads (
+ id BIGSERIAL PRIMARY KEY,
+ place_id INTEGER REFERENCES places(id) ON DELETE CASCADE,
+ traveler_id BIGINT REFERENCES app_users(id) ON DELETE CASCADE,
+ host_id BIGINT REFERENCES hosts(id) ON DELETE SET NULL,
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ UNIQUE (place_id, traveler_id)
+);
+CREATE TABLE IF NOT EXISTS messages (
+ id BIGSERIAL PRIMARY KEY,
+ thread_id BIGINT NOT NULL REFERENCES threads(id) ON DELETE CASCADE,
+ sender_id BIGINT REFERENCES app_users(id) ON DELETE SET NULL,
+ sender_role TEXT NOT NULL CHECK (sender_role IN ('traveler','host','system')),
+ body TEXT NOT NULL,
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+CREATE INDEX IF NOT EXISTS idx_messages_thread ON messages(thread_id, created_at);
← 9a92040 costa-rica: money-math coverage — processor-fee host-payout
·
back to Costa Rica
·
costa-rica: Cody-gate fixes on sort+density — harmonize vert 98a3029 →