← back to Costa Rica
routes/app.js
613 lines
'use strict';
// Mobile-app API (JWT-authed). Mounted at /api/app BEFORE the site basic-auth
// gate so public app users and the Expo client can reach it.
const express = require('express');
const crypto = require('crypto');
const { pool } = require('../lib/db');
const { signToken, hashPassword, verifyPassword, authRequired, optionalAuth } = require('../lib/auth');
const { computeSplit, nights } = require('../lib/money');
const { getProvider } = require('../lib/payments');
const plaid = require('../lib/plaid');
const wa = require('../lib/whatsapp');
const apple = require('../lib/apple');
const router = express.Router();
const bookingCode = () => 'CR-' + crypto.randomBytes(3).toString('hex').toUpperCase();
// Normalize a phone to E.164. Costa Rica default (+506) for bare 8-digit numbers.
function normalizePhone(raw) {
if (!raw) return null;
let d = String(raw).replace(/[^\d+]/g, '');
if (d.startsWith('+')) return d;
d = d.replace(/\D/g, '');
if (d.length === 8) return '+506' + d; // CR local
if (d.length === 11 && d.startsWith('506')) return '+' + d;
if (d.length >= 10) return '+' + d;
return null;
}
const ok = (res, data) => res.json({ ok: true, ...data });
const bad = (res, code, msg) => res.status(code).json({ ok: false, error: msg });
// ---------------------------------------------------------------- auth
router.post('/auth/register', async (req, res) => {
const { email, password, full_name, phone } = req.body || {};
if (!email || !password) return bad(res, 400, 'email and password required');
try {
const { rows } = await pool.query(
`INSERT INTO app_users (email, password_hash, full_name, phone_e164)
VALUES ($1,$2,$3,$4) RETURNING id, email, full_name, role, is_host`,
[String(email).toLowerCase(), hashPassword(password), full_name || null, phone || null]);
const u = rows[0];
ok(res, { token: signToken({ sub: u.id, role: u.role }), user: u });
} catch (e) {
if (e.code === '23505') return bad(res, 409, 'email or phone already registered');
console.error('[register]', e.message);
bad(res, 500, 'internal error'); // M2/R4 — never leak DB/driver text to the client
}
});
router.post('/auth/login', async (req, res) => {
const { email, password } = req.body || {};
try {
const { rows } = await pool.query(
`SELECT id, email, full_name, role, is_host, password_hash FROM app_users WHERE email=$1`,
[String(email || '').toLowerCase()]);
const u = rows[0];
if (!u || !verifyPassword(password || '', u.password_hash)) return bad(res, 401, 'invalid credentials');
ok(res, { token: signToken({ sub: u.id, role: u.role, host_id: null }),
user: { id: u.id, email: u.email, full_name: u.full_name, role: u.role, is_host: u.is_host } });
} catch (e) {
console.error('[login]', e.message);
bad(res, 500, 'login failed');
}
});
// Sign in with Apple — app sends Apple's identity_token; we verify + upsert.
router.post('/auth/apple', async (req, res) => {
const { identity_token, full_name } = req.body || {};
let claims;
try { claims = await apple.verifyIdentityToken(identity_token); }
// Log the reason server-side; return a GENERIC 401 (do not hand token/infra
// internals — fetchT timeout text, JSON.parse errors — to the client). Mirrors
// the DB-error catch below (M2/R4). (Cody SIWA audit, cycle 27.)
catch (e) { console.error('[auth/apple] verify failed:', e.message); return bad(res, 401, 'apple verify failed'); }
try {
// Link by apple_sub first, else by verified email, else create.
let { rows } = await pool.query(
`SELECT id, email, full_name, role, is_host, apple_sub FROM app_users WHERE apple_sub=$1`, [claims.sub]);
// Only auto-link to an existing account when Apple says the email is VERIFIED
// (prevents account-takeover via an unverified-email token). Otherwise a new
// Apple-owned account is created below.
if (!rows[0] && claims.email && claims.email_verified) {
({ rows } = await pool.query(
`UPDATE app_users SET apple_sub=$1, auth_provider='apple' WHERE email=$2 RETURNING *`,
[claims.sub, claims.email.toLowerCase()]));
}
if (!rows[0]) {
({ rows } = await pool.query(
`INSERT INTO app_users (apple_sub, email, full_name, auth_provider)
VALUES ($1,$2,$3,'apple') RETURNING *`,
[claims.sub, claims.email ? claims.email.toLowerCase() : null, full_name || null]));
}
const u = rows[0];
ok(res, { token: signToken({ sub: u.id, role: u.role }),
user: { id: u.id, email: u.email, full_name: u.full_name, role: u.role, is_host: u.is_host } });
} catch (e) {
if (e.code === '23505') return bad(res, 409, 'account conflict');
console.error('[auth/apple]', e.message);
bad(res, 500, 'internal error'); // M2/R4 — never leak DB/driver text to the client
}
});
router.get('/me', authRequired, async (req, res) => {
const { rows } = await pool.query(`SELECT id,email,full_name,phone_e164,role,is_host,wa_opt_in FROM app_users WHERE id=$1`, [req.user.sub]);
if (!rows[0]) return bad(res, 404, 'not found');
const { rows: hostRows } = await pool.query(`SELECT id, kyc_status FROM hosts WHERE user_id=$1`, [req.user.sub]);
ok(res, { user: rows[0], host: hostRows[0] || null });
});
// ---------------------------------------------------------------- listings (bookable inventory)
router.get('/listings', optionalAuth, async (req, res) => {
const { region, vertical, q, limit = 40, offset = 0 } = req.query;
const where = ['pb.is_active = TRUE'];
const args = [];
if (region) { args.push(region); where.push(`r.slug = $${args.length}`); }
if (vertical) { args.push(vertical); where.push(`p.vertical = $${args.length}`); }
if (q) { args.push(`%${q}%`); where.push(`p.name ILIKE $${args.length}`); }
// Clamp to safe integers and inline — guaranteed numeric, no injection surface.
const lim = Math.max(1, Math.min(parseInt(limit, 10) || 40, 100));
const off = Math.max(0, parseInt(offset, 10) || 0);
const page = 'LIMIT ' + lim + ' OFFSET ' + off; // clamped integers, no injection surface
const { rows } = await pool.query(
`SELECT p.slug, p.name, p.vertical, p.category, p.description, p.image_url, p.rating,
-- Nothing has ever populated places.image_url (every image ingest targets REGIONS),
-- so every listing renders imageless with no fallback. Expose the region image as a
-- clearly-named SEPARATE field so the client can fall back to it. Purely ADDITIVE:
-- image_url is unchanged, so no existing consumer breaks, and if a region has no
-- image this is simply null — strictly non-worsening. (TK-11518)
r.image_url AS region_image_url,
r.name AS region, r.slug AS region_slug, p.lat, p.lng,
pb.booking_type, pb.currency, pb.base_price, pb.cleaning_fee, pb.max_guests, pb.instant_book
FROM place_booking pb
JOIN places p ON p.id = pb.place_id
LEFT JOIN regions r ON r.id = p.region_id
WHERE ${where.join(' AND ')}
ORDER BY p.rating DESC NULLS LAST, p.name
${page}`, args);
ok(res, { count: rows.length, listings: rows });
});
router.get('/listings/:slug', optionalAuth, async (req, res) => {
const { rows } = await pool.query(
`SELECT p.*, pb.booking_type, pb.currency, pb.base_price, pb.cleaning_fee, pb.max_guests,
pb.min_nights, pb.instant_book, pb.cancellation, r.name AS region
FROM places p
JOIN place_booking pb ON pb.place_id = p.id
LEFT JOIN regions r ON r.id = p.region_id
WHERE p.slug=$1 AND pb.is_active`, [req.params.slug]);
if (!rows[0]) return bad(res, 404, 'listing not found or not bookable');
ok(res, { listing: rows[0] });
});
router.get('/listings/:slug/availability', async (req, res) => {
const { from, to } = req.query;
const { rows: [pl] } = await pool.query(`SELECT id FROM places WHERE slug=$1`, [req.params.slug]);
if (!pl) return bad(res, 404, 'listing not found');
const { rows } = await pool.query(
`SELECT day, slot_start, slot_end, capacity, price_override, is_blocked
FROM availability WHERE place_id=$1
AND ($2::date IS NULL OR day >= $2) AND ($3::date IS NULL OR day <= $3)
ORDER BY day, slot_start`, [pl.id, from || null, to || null]);
ok(res, { availability: rows });
});
// ---------------------------------------------------------------- bookings
router.post('/bookings', authRequired, async (req, res) => {
const { place_slug, check_in, check_out, slot_start, slot_end, guests = 1 } = req.body || {};
const { rows: [pb] } = await pool.query(
`SELECT pb.*, p.id AS place_id, p.name FROM place_booking pb
JOIN places p ON p.id = pb.place_id WHERE p.slug=$1 AND pb.is_active`, [place_slug]);
if (!pb) return bad(res, 404, 'listing not bookable');
// C1 — validate guests BEFORE it feeds the money math (unvalidated `guests`
// reached `base_price * guests` → NaN/negative money + a CHECK-violation 500).
const g = Number.parseInt(guests, 10);
if (!Number.isInteger(g) || g < 1) return bad(res, 400, 'guests must be a positive integer');
if (g > pb.max_guests) return bad(res, 400, `max ${pb.max_guests} guests`);
let subtotal;
if (pb.booking_type === 'nightly') {
// C2 — validate dates at the route so bad input is a clean 400, never NaN
// money or a DB CHECK-violation 500 (bookings_has_a_date / bookings_stay_order).
if (Number.isNaN(Date.parse(check_in)) || Number.isNaN(Date.parse(check_out)))
return bad(res, 400, 'check_in and check_out must be valid ISO dates');
if (new Date(check_out + 'T00:00:00Z') <= new Date(check_in + 'T00:00:00Z'))
return bad(res, 400, 'check_out must be after check_in');
const n = nights(check_in, check_out);
if (n < (pb.min_nights || 1)) return bad(res, 400, `min ${pb.min_nights} nights`);
// Cap the stay length: an unbounded date range makes subtotal = base_price * n
// overflow the INTEGER money columns (a 500), and even below overflow lets a
// client create an absurd multi-decade booking that squats the listing's
// availability (the overlap guard then blocks every real booking for years).
const MAX_NIGHTS = Number.parseInt(process.env.MAX_BOOKING_NIGHTS || '365', 10);
if (n > MAX_NIGHTS) return bad(res, 400, `maximum stay is ${MAX_NIGHTS} nights`);
// Overlap guard (Cody gate, TK-10346 c1). Robust fix = a btree_gist EXCLUDE
// constraint on daterange (queued in the go-live memo); this closes the common case.
const { rows: conflict } = await pool.query(
`SELECT 1 FROM bookings WHERE place_id=$1 AND status IN ('pending','confirmed','completed')
AND check_in < $3 AND check_out > $2 LIMIT 1`, [pb.place_id, check_in, check_out]);
if (conflict.length) return bad(res, 409, 'those dates are not available');
subtotal = pb.base_price * n;
} else {
// C2 (slot mode) — validate slot bounds symmetrically.
if (Number.isNaN(Date.parse(slot_start)) || Number.isNaN(Date.parse(slot_end)))
return bad(res, 400, 'slot_start and slot_end must be valid ISO datetimes');
if (new Date(slot_end) <= new Date(slot_start))
return bad(res, 400, 'slot_end must be after slot_start');
subtotal = pb.base_price * g; // slot/ticket priced per guest (validated integer)
}
const split = computeSplit({ subtotal, cleaningFee: pb.cleaning_fee, currency: pb.currency, platformFeeBps: pb.platform_fee_bps });
// Overflow guard: EVERY stored bookings money column is INTEGER (int4, max
// 2,147,483,647). Guard each one, not just `total` — `fees = cleaningFee +
// platformFee` can reach ~2x total and overflows int4 while total is still under
// it (e.g. a host with a huge cleaning_fee, at the default 10% fee), so a
// total-only check would still let the INSERT 500 on "integer out of range".
// `max_guests`/`cleaning_fee` are host-set and uncapped by any DB CHECK, so the
// inputs can be large enough to matter. (Cody gate, cycle 14.)
const INT4_MAX = 2_147_483_647;
const amounts = [split.subtotal, split.fees, split.platformFee, split.total, split.hostPayout];
if (!amounts.every(v => Number.isSafeInteger(v) && v >= 0 && v <= INT4_MAX)) return bad(res, 400, 'booking total exceeds the maximum');
const code = bookingCode();
const { rows: [bk] } = await pool.query(
`INSERT INTO bookings (code, place_id, host_id, traveler_id, check_in, check_out, slot_start, slot_end,
guests, currency, subtotal, fees, platform_fee, total, host_payout, status)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,'pending') RETURNING *`,
[code, pb.place_id, pb.host_id, req.user.sub, check_in || null, check_out || null, slot_start || null, slot_end || null,
g, pb.currency, split.subtotal, split.fees, split.platformFee, split.total, split.hostPayout]);
ok(res, { booking: bk, split });
});
router.get('/bookings', authRequired, async (req, res) => {
const { rows } = await pool.query(
`SELECT b.*, p.name AS place_name, p.slug AS place_slug
FROM bookings b JOIN places p ON p.id=b.place_id
WHERE b.traveler_id=$1 ORDER BY b.created_at DESC`, [req.user.sub]);
ok(res, { bookings: rows });
});
router.get('/bookings/:code', authRequired, async (req, res) => {
const { rows } = await pool.query(
`SELECT b.*, p.name AS place_name, p.slug AS place_slug FROM bookings b
JOIN places p ON p.id=b.place_id WHERE b.code=$1 AND b.traveler_id=$2`, [req.params.code, req.user.sub]);
if (!rows[0]) return bad(res, 404, 'not found');
const { rows: pays } = await pool.query(`SELECT id,status,method,amount,currency,provider FROM payments WHERE booking_id=$1`, [rows[0].id]);
ok(res, { booking: rows[0], payments: pays });
});
router.post('/bookings/:code/cancel', authRequired, async (req, res) => {
const { rows } = await pool.query(
`UPDATE bookings SET status='cancelled', updated_at=NOW()
WHERE code=$1 AND traveler_id=$2 AND status IN ('pending','confirmed') RETURNING *`,
[req.params.code, req.user.sub]);
if (!rows[0]) return bad(res, 409, 'cannot cancel');
ok(res, { booking: rows[0] });
});
// ---------------------------------------------------------------- payments
router.post('/bookings/:code/pay', authRequired, async (req, res) => {
const { method = 'card' } = req.body || {};
const { rows: [bk] } = await pool.query(
`SELECT id, code, place_id, host_id, traveler_id, check_in, check_out, slot_start, slot_end,
guests, currency, subtotal, fees, platform_fee, total, host_payout, status, created_at
FROM bookings WHERE code=$1 AND traveler_id=$2`, [req.params.code, req.user.sub]);
if (!bk) return bad(res, 404, 'booking not found');
if (bk.status !== 'pending') return bad(res, 409, `booking is ${bk.status}`);
const { rows: [u] } = await pool.query(`SELECT email, full_name, phone_e164 FROM app_users WHERE id=$1`, [req.user.sub]);
const provider = getProvider();
const returnUrl = (process.env.APP_RETURN_URL || 'crmarketplace://pay/return');
// REORDER (PRE-FLIGHT #6): check for an in-flight payment before calling the
// provider, so a timeout/error leaves a reconcilable row + a retry detects an
// existing payment instead of double-charging (closes Cody #1/#2). This SELECT is
// the fast path for the SEQUENTIAL retry; the atomic backstop below handles true
// CONCURRENCY.
const inflight = () => pool.query(
`SELECT id FROM payments WHERE booking_id=$1 AND status IN ('processing','requires_action') LIMIT 1`, [bk.id]);
const { rows: [existing] } = await inflight();
if (existing) return ok(res, { payment_id: existing.id, status: 'requires_action', client_action: null, live_mode: provider.liveMode });
// Pre-charge: write a 'processing' row so a timeout leaves something to reconcile
// against. RACE BACKSTOP (Cody cold audit, cycle 19): the SELECT above and this
// INSERT are not atomic, so two concurrent /pay requests could both pass the SELECT
// and both insert -> both call the processor -> DOUBLE CHARGE. The partial unique
// index payments_one_inflight_per_booking (migrate_011) makes the DB reject the
// second in-flight insert with 23505; the race-loser then REUSES the winner's
// payment instead of firing a second real charge.
let pay;
try {
const ins = await pool.query(
`INSERT INTO payments (booking_id, provider, method, currency, amount, status, live_mode)
VALUES ($1,$2,$3,$4,$5,'processing',$6) RETURNING id`,
[bk.id, provider.name, method, bk.currency, bk.total, provider.liveMode]);
pay = ins.rows[0];
} catch (e) {
// 23505 on payments_one_inflight_per_booking = another concurrent /pay won the
// in-flight slot. Return ITS payment instead of firing a second real charge.
// Fetch the booking's most-recent payment WITHOUT a status filter: the winner may
// already have RESOLVED (a fast sandbox/live succeed flips it off 'processing'
// before we get here), so an in-flight-only lookup could miss it and 500 a
// traveler whose payment actually went through. (Cody gate, cycle 19.)
if (e && e.code === '23505' && e.constraint === 'payments_one_inflight_per_booking') {
const { rows: [raced] } = await pool.query(
`SELECT id, status FROM payments WHERE booking_id=$1 ORDER BY created_at DESC LIMIT 1`, [bk.id]);
if (raced) return ok(res, { payment_id: raced.id, status: raced.status, client_action: null, live_mode: provider.liveMode });
}
throw e;
}
let charge;
try {
charge = await provider.createCharge({
amount: bk.total, currency: bk.currency, method,
booking: { code: bk.code, id: bk.id },
customer: { email: u.email, name: u.full_name, phone: u.phone_e164 },
returnUrl: `${returnUrl}?code=${bk.code}`,
});
} catch (e) {
// Timeout or error: mark the pre-written row as failed (a retry can then create a fresh attempt).
await pool.query(`UPDATE payments SET status='failed', raw=$1, updated_at=NOW() WHERE id=$2`,
[JSON.stringify({ error: String(e.message) }), pay.id]);
return bad(res, 502, `processor error: ${e.message}`);
}
// A provider that returned a FAILED charge (e.g. a live 4xx decline surfaced by the
// createCharge res.ok guard) must record 'failed' + surface an error — it must NOT
// be flattened into 'processing' (a stuck row with a null provider_ref the webhook
// can never resolve, holding the booking 'pending' forever). PRE-FLIGHT §5b #2,
// consumer half — the adapter's fail-closed 'failed' only helps if the route honors it.
if (charge.status === 'failed') {
await pool.query(`UPDATE payments SET provider_ref=$1, status='failed', raw=$2, updated_at=NOW() WHERE id=$3`,
[charge.providerRef || null, JSON.stringify(charge.raw || {}), pay.id]);
return bad(res, 402, 'payment failed');
}
// Post-charge: write the provider_ref and final status. ('requires_action' is
// surfaced to the client but stored as 'processing' — the payments.status CHECK
// has no 'requires_action'; the row is in-flight until the webhook/poll resolves it.)
await pool.query(
`UPDATE payments SET provider_ref=$1, status=$2, raw=$3, updated_at=NOW() WHERE id=$4`,
[charge.providerRef, charge.status === 'succeeded' ? 'succeeded' : 'processing', JSON.stringify(charge.raw || {}), pay.id]);
// If sandbox/instant-succeeded, confirm the booking immediately. R1 — the
// payment already succeeded and was recorded; a confirm failure must NOT 500
// the caller (the webhook / GET /payments poll retries confirmation).
if (charge.status === 'succeeded') {
try { await confirmBooking(bk.id); }
catch (e) { console.error('[pay] confirmBooking failed (payment ok; will retry via webhook/poll)', bk.id, e.message); }
}
ok(res, { payment_id: pay.id, status: charge.status, client_action: charge.clientAction, live_mode: provider.liveMode });
});
router.get('/payments/:id', authRequired, async (req, res) => {
const { rows: [pay] } = await pool.query(
`SELECT pm.* FROM payments pm JOIN bookings b ON b.id=pm.booking_id
WHERE pm.id=$1 AND b.traveler_id=$2`, [req.params.id, req.user.sub]);
if (!pay) return bad(res, 404, 'not found');
// Poll processor for latest status (covers sandbox redirect-return).
if (pay.status === 'processing') {
try {
const provider = getProvider(pay.provider);
const latest = await provider.getCharge(pay.provider_ref);
if (latest.status !== 'processing') {
await pool.query(`UPDATE payments SET status=$1, updated_at=NOW() WHERE id=$2`, [latest.status, pay.id]);
pay.status = latest.status;
}
} catch { /* leave processing */ }
}
// Rescue any 'succeeded' payment whose booking is still pending — covers a
// just-polled success AND a payment a webhook already marked 'succeeded' but
// whose confirmBooking then failed (the webhook releases its idempotency marker
// and 500s for a provider retry, but until that retry lands the booking sits
// pending while payments.status is already succeeded — a state this poll used to
// skip because it only acted on 'processing'). confirmBooking is idempotent.
// (Cody gate, cycle 12, TK-10346.)
if (pay.status === 'succeeded') {
try { await confirmBooking(pay.booking_id); }
catch (e) { console.error('[poll] confirmBooking', pay.booking_id, e.message); }
}
ok(res, { payment: { id: pay.id, status: pay.status, amount: pay.amount, currency: pay.currency } });
});
// Shared: mark booking confirmed + notify via WhatsApp (best-effort).
async function confirmBooking(bookingId) {
const { rows: [b] } = await pool.query(
`UPDATE bookings SET status='confirmed', updated_at=NOW() WHERE id=$1 AND status='pending' RETURNING *`, [bookingId]);
if (!b) return;
try {
const { rows: [u] } = await pool.query(`SELECT phone_e164, wa_opt_in FROM app_users WHERE id=$1`, [b.traveler_id]);
const { rows: [p] } = await pool.query(`SELECT name FROM places WHERE id=$1`, [b.place_id]);
if (u?.phone_e164 && u.wa_opt_in) { // respect the traveler's WhatsApp opt-in (consent), not just presence of a number
const to = u.phone_e164.replace(/^\+/, '');
await wa.sendText(to,
`✅ ¡Reserva confirmada! ${p?.name} · code ${b.code} · ${b.currency} ${(b.total/100).toFixed(2)}. Gracias.`,
{ bookingId: b.id });
}
} catch (e) { console.warn('[confirm] wa notify failed', e.message); }
}
// ---------------------------------------------------------------- contacts (REMOVED)
// The address-book import / contact-list / WhatsApp-invite endpoints were REMOVED
// 2026-09-03 (TK-10387, DTD verdict C, unanimous 8/8).
//
// Why: the iOS client uploaded the user's ENTIRE address book as cleartext
// {name, phone, email} and this route PERSISTED it — durable storage of third
// parties' personal data from people who are not users, were never notified and
// never consented. That also contradicted the app's own NSContactsUsageDescription,
// which told the user contacts stay on-device and are not collected.
//
// Deleting only the client screen was NOT sufficient: this route stayed live and
// authenticated, still willing to accept and store 5000 PII rows from any client.
// So the server path is removed too, which is what makes the 'no contacts are
// collected' claim structurally true rather than merely unused.
//
// Prod was verified EMPTY before removal (select count(*) from contacts -> 0), so
// no third-party data was ever actually collected.
//
// If invite-friends is ever rebuilt, do NOT restore this shape. Either use a
// share-sheet / deep link the user sends themselves (no upload, no permission), or
// real private set intersection. Salted hashing is not sufficient: E.164 is a
// ~10^10 space, so an app-global salt is brute-forceable, while a per-user salt
// makes cross-user matching impossible by construction.
// ---------------------------------------------------------------- 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 id, place_id, traveler_id, host_id 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 || {};
const { rows: [h] } = await pool.query(
`INSERT INTO hosts (user_id, legal_name, cedula, country) VALUES ($1,$2,$3,$4)
ON CONFLICT (user_id) DO UPDATE SET legal_name=EXCLUDED.legal_name, cedula=EXCLUDED.cedula, country=EXCLUDED.country
RETURNING *`, [req.user.sub, legal_name || null, cedula || null, country]);
await pool.query(`UPDATE app_users SET is_host=TRUE WHERE id=$1`, [req.user.sub]);
ok(res, { host: h });
});
async function requireHost(req, res) {
const { rows } = await pool.query(
`SELECT id, user_id, legal_name, cedula, country, kyc_status, default_payout_method_id
FROM hosts WHERE user_id=$1`, [req.user.sub]);
if (!rows[0]) { bad(res, 403, 'not a host — call /host/apply first'); return null; }
return rows[0];
}
router.post('/host/claim', authRequired, async (req, res) => {
const host = await requireHost(req, res); if (!host) return;
const { rows: [pl] } = await pool.query(`SELECT id FROM places WHERE slug=$1`, [req.body?.place_slug]);
if (!pl) return bad(res, 404, 'place not found');
await pool.query(
`INSERT INTO place_hosts (place_id, host_id, claim_status) VALUES ($1,$2,'pending')
ON CONFLICT (place_id, host_id) DO NOTHING`, [pl.id, host.id]);
ok(res, { claimed: req.body.place_slug, status: 'pending' });
});
router.post('/host/listings', authRequired, async (req, res) => {
const host = await requireHost(req, res); if (!host) return;
const b = req.body || {};
const { rows: [pl] } = await pool.query(`SELECT id FROM places WHERE slug=$1`, [b.place_slug]);
if (!pl) return bad(res, 404, 'place not found');
// Reject a min_nights above the booking cap: it would make the listing
// permanently unbookable (a booking needs n >= min_nights AND n <= MAX_BOOKING_NIGHTS,
// impossible when min_nights > MAX). Guard at write time so the bad config can't
// be created. (Cody gate, cycle 14.)
const MAX_NIGHTS = Number.parseInt(process.env.MAX_BOOKING_NIGHTS || '365', 10);
if (b.min_nights != null && (Number.parseInt(b.min_nights, 10) || 0) > MAX_NIGHTS)
return bad(res, 400, `min_nights cannot exceed the ${MAX_NIGHTS}-night booking cap`);
// Ownership guard (Cody gate, TK-10346 c1): a host may only list a place they
// have claimed, and may not overwrite a listing owned by another host (IDOR).
const { rows: [guard] } = await pool.query(
`SELECT (SELECT 1 FROM place_hosts WHERE place_id=$1 AND host_id=$2) AS claimed,
(SELECT host_id FROM place_booking WHERE place_id=$1) AS current_host`, [pl.id, host.id]);
if (!guard.claimed) return bad(res, 403, 'claim this place first via /host/claim');
if (guard.current_host && guard.current_host !== host.id) return bad(res, 409, 'listing owned by another host');
// ATOMICITY (Cody gate, cycle 9): the guard SELECT above and this upsert are
// SEPARATE statements with no lock between them, so two hosts racing to list the
// same never-before-listed place could BOTH read current_host=NULL, both pass the
// guard, and both run the upsert. The old unconditional `DO UPDATE SET host_id=
// EXCLUDED.host_id` let the second writer SILENTLY overwrite the first and still
// 200 — "last commit wins, loser never told". The `WHERE` makes the single
// statement atomic: the update only applies when the caller already owns the row
// (re-list) or it is unowned (host deleted -> NULL). A racing non-owner matches no
// row -> empty RETURNING -> 409, instead of a false success. (Approval enforcement
// — gating on claim_status='approved' — is a separate, customer-facing decision;
// see pending-approval memo. This is the pure-correctness half.)
const { rows: [pb] } = await pool.query(
`INSERT INTO place_booking (place_id, host_id, booking_type, currency, base_price, cleaning_fee, max_guests, min_nights, instant_book, is_active)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,TRUE)
ON CONFLICT (place_id) DO UPDATE SET host_id=EXCLUDED.host_id, booking_type=EXCLUDED.booking_type, currency=EXCLUDED.currency,
base_price=EXCLUDED.base_price, cleaning_fee=EXCLUDED.cleaning_fee, max_guests=EXCLUDED.max_guests,
min_nights=EXCLUDED.min_nights, instant_book=EXCLUDED.instant_book, is_active=TRUE, updated_at=NOW()
WHERE place_booking.host_id = EXCLUDED.host_id OR place_booking.host_id IS NULL
RETURNING *`,
[pl.id, host.id, b.booking_type || 'nightly', b.currency || 'USD', b.base_price | 0,
b.cleaning_fee | 0, b.max_guests || 1, b.min_nights || 1, b.instant_book !== false]);
if (!pb) return bad(res, 409, 'listing owned by another host');
ok(res, { listing: pb });
});
router.post('/host/payout-methods', authRequired, async (req, res) => {
const host = await requireHost(req, res); if (!host) return;
const b = req.body || {};
if (!['sinpe_movil', 'cr_iban', 'plaid_ach'].includes(b.kind)) return bad(res, 400, 'bad kind');
// Field completeness (Cody cold audit, cycle 23): a payout method must carry the
// identifier its kind actually pays to, or the host silently gets $0 at payout time
// (a sinpe_movil with no phone / a cr_iban with no IBAN). Clean 400 here, backed by
// DB CHECKs (migrate_012). (plaid_ach carries its identifier via the /host/plaid/*
// exchange, not this route.)
if (b.kind === 'sinpe_movil' && !b.sinpe_phone) return bad(res, 400, 'sinpe_movil requires a sinpe_phone');
if (b.kind === 'cr_iban' && !b.cr_iban) return bad(res, 400, 'cr_iban requires a cr_iban (IBAN)');
const { rows: [pm] } = await pool.query(
`INSERT INTO payout_methods (host_id, kind, label, sinpe_phone, cr_iban, bank_name, currency, is_default)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8) RETURNING *`,
[host.id, b.kind, b.label || null, b.sinpe_phone || null, b.cr_iban || null, b.bank_name || null,
b.currency || (b.kind === 'plaid_ach' ? 'USD' : 'CRC'), b.is_default !== false]);
if (pm.is_default) await pool.query(`UPDATE hosts SET default_payout_method_id=$1 WHERE id=$2`, [pm.id, host.id]);
ok(res, { payout_method: { id: pm.id, kind: pm.kind, currency: pm.currency, is_default: pm.is_default } });
});
// Plaid (foreign hosts): Link token + exchange
router.post('/host/plaid/link-token', authRequired, async (req, res) => {
const host = await requireHost(req, res); if (!host) return;
// A live Plaid error/timeout throws; catch it so it becomes a clean gateway error,
// NOT an uncaught async rejection (this router has no error middleware, so an
// uncaught throw would hang the request / crash the process on Node's
// unhandledRejection). See YOLO_NOTES: the systemic 23-route gap is a separate cycle.
let t;
try { t = await plaid.createLinkToken(req.user.sub); }
catch (e) { console.error('[plaid] link-token', e.message); return bad(res, e.code === 'PROVIDER_TIMEOUT' ? 504 : 502, 'bank linking is temporarily unavailable'); }
ok(res, { link_token: t.link_token, env: plaid.ENV, live: plaid.liveMode });
});
router.post('/host/plaid/exchange', authRequired, async (req, res) => {
const host = await requireHost(req, res); if (!host) return;
let ex;
try { ex = await plaid.exchangePublicToken(req.body?.public_token); }
catch (e) { console.error('[plaid] exchange', e.message); return bad(res, e.code === 'PROVIDER_TIMEOUT' ? 504 : 502, 'bank linking is temporarily unavailable'); }
// getAuth fetches the account (account_id + last4) — the payout DESTINATION. If it
// fails, do NOT persist a payout method as verified=TRUE with a null account (a
// silent lie that a later ACH payout would have nothing to send to). Record it
// unverified and never make it the host's default. (Cody gate, cycle 10.)
let auth;
try { auth = await plaid.getAuth(ex.access_token); }
catch (e) { console.error('[plaid] getAuth', e.message); auth = { accounts: [] }; }
const acct = auth.accounts?.[0] || {};
const verified = !!acct.account_id;
// Guard the DB writes too: this router has no error middleware, so a bare
// pool.query throw here would crash the process (Node unhandledRejection).
let pm;
try {
const ins = await pool.query(
`INSERT INTO payout_methods (host_id, kind, label, plaid_item_id, plaid_access_token, plaid_account_id, account_last4, currency, verified, is_default)
VALUES ($1,'plaid_ach','Bank (Plaid)',$2,$3,$4,$5,'USD',$6,$6) RETURNING id`,
[host.id, ex.item_id, ex.access_token, acct.account_id || null, acct.mask || null, verified]);
pm = ins.rows[0];
if (verified) await pool.query(`UPDATE hosts SET default_payout_method_id=$1 WHERE id=$2`, [pm.id, host.id]);
} catch (e) { console.error('[plaid] persist payout method', e.message); return bad(res, 502, 'could not save bank details'); }
ok(res, { payout_method_id: pm.id, last4: acct.mask, verified, sandbox: !!ex.sandbox });
});
module.exports = { router, confirmBooking, normalizePhone };