← back to Costa Rica
TK-10346: migration 007 — race-proof no-double-book EXCLUDE constraints (btree_gist, stay + slot modes); backstop to app-level guard
0aaf6cd5c2bc0f814b6a4da295d8139c9511d176 · 2026-08-07 16:15:41 -0700 · Steve
Files touched
A migrate_007_double_book_exclude.sqlA test/booking.test.js
Diff
commit 0aaf6cd5c2bc0f814b6a4da295d8139c9511d176
Author: Steve <steve@designerwallcoverings.com>
Date: Fri Aug 7 16:15:41 2026 -0700
TK-10346: migration 007 — race-proof no-double-book EXCLUDE constraints (btree_gist, stay + slot modes); backstop to app-level guard
---
migrate_007_double_book_exclude.sql | 21 ++++++++
test/booking.test.js | 95 +++++++++++++++++++++++++++++++++++++
2 files changed, 116 insertions(+)
diff --git a/migrate_007_double_book_exclude.sql b/migrate_007_double_book_exclude.sql
new file mode 100644
index 0000000..fce1e56
--- /dev/null
+++ b/migrate_007_double_book_exclude.sql
@@ -0,0 +1,21 @@
+-- TK-10346: race-proof no-double-book at the DB level (backstop to the app-level overlap guard).
+-- Two PARTIAL exclusion constraints so stay-bookings and slot-bookings each guard their own mode.
+BEGIN;
+CREATE EXTENSION IF NOT EXISTS btree_gist;
+
+-- Date-range stays: no two active (confirmed|pending) bookings for the same place with overlapping nights.
+ALTER TABLE bookings DROP CONSTRAINT IF EXISTS bookings_no_overlap_stay;
+ALTER TABLE bookings ADD CONSTRAINT bookings_no_overlap_stay
+ EXCLUDE USING gist (
+ place_id WITH =,
+ daterange(check_in, check_out, '[)') WITH &&
+ ) WHERE (status IN ('confirmed','pending') AND check_in IS NOT NULL AND check_out IS NOT NULL);
+
+-- Time-slot bookings (tours/services): no two active bookings for the same place with overlapping slots.
+ALTER TABLE bookings DROP CONSTRAINT IF EXISTS bookings_no_overlap_slot;
+ALTER TABLE bookings ADD CONSTRAINT bookings_no_overlap_slot
+ EXCLUDE USING gist (
+ place_id WITH =,
+ tstzrange(slot_start, slot_end) WITH &&
+ ) WHERE (status IN ('confirmed','pending') AND slot_start IS NOT NULL AND slot_end IS NOT NULL);
+COMMIT;
diff --git a/test/booking.test.js b/test/booking.test.js
new file mode 100644
index 0000000..b1c317f
--- /dev/null
+++ b/test/booking.test.js
@@ -0,0 +1,95 @@
+'use strict';
+// Money-path tests (node:test) for the booking state machine + double-booking guard.
+// computeSplit conservation is covered in marketplace.test.js; THIS covers the flow:
+// - confirmBooking() is idempotent — a DUPLICATE payment webhook must NOT
+// double-confirm or double-send the WhatsApp confirmation (guarded by
+// `WHERE status='pending' RETURNING *` → no row on the 2nd call).
+// - POST /bookings rejects overlapping dates (the c1 double-booking fix) with 409
+// and never reaches the INSERT.
+// - the happy path persists a split whose parts reconstruct the total.
+// No real DB / network: pool.query is a queue mock, wa.sendText is stubbed. Run: node --test
+const { test, before, after } = require('node:test');
+const assert = require('node:assert');
+const http = require('node:http');
+const express = require('express');
+
+const { signToken } = require('../lib/auth');
+const db = require('../lib/db');
+const wa = require('../lib/whatsapp');
+const { router, confirmBooking } = require('../routes/app');
+
+let responses = []; // queued pool.query results (FIFO)
+let calls = []; // every { sql, args }
+let sentTexts = []; // every wa.sendText invocation
+const origQuery = db.pool.query, origSend = wa.sendText;
+
+let server, base;
+before(async () => {
+ db.pool.query = async (sql, args) => { calls.push({ sql, args }); return responses.length ? responses.shift() : { rows: [], rowCount: 0 }; };
+ wa.sendText = async (...a) => { sentTexts.push(a); return { sandbox: true }; };
+ const app = express();
+ app.use(express.json());
+ app.use('/api/app', router);
+ await new Promise(r => { server = app.listen(0, r); });
+ base = `http://127.0.0.1:${server.address().port}`;
+});
+after(() => { db.pool.query = origQuery; wa.sendText = origSend; server && server.close(); });
+
+function reset(resp) { responses = resp.slice(); calls = []; sentTexts = []; }
+const hasSql = (re) => calls.some(c => re.test(c.sql));
+
+function post(path, body, token) {
+ const data = JSON.stringify(body);
+ return new Promise((resolve, reject) => {
+ const r = http.request(base + path, { method: 'POST', headers: {
+ 'content-type': 'application/json', 'content-length': Buffer.byteLength(data),
+ ...(token ? { authorization: 'Bearer ' + token } : {}) } },
+ res => { let b = ''; res.on('data', c => b += c); res.on('end', () => resolve({ status: res.statusCode, json: JSON.parse(b || '{}') })); });
+ r.on('error', reject); r.end(data);
+ });
+}
+
+test('confirmBooking: first confirmation flips pending→confirmed and sends ONE WhatsApp', async () => {
+ reset([
+ { rows: [{ id: 7, code: 'CR-ABC', currency: 'USD', total: 40000, traveler_id: 3, place_id: 5 }] }, // UPDATE ... RETURNING *
+ { rows: [{ phone_e164: '+50688887777', wa_opt_in: true }] }, // SELECT user
+ { rows: [{ name: 'Casa Vista' }] }, // SELECT place
+ ]);
+ await confirmBooking(7);
+ assert.equal(sentTexts.length, 1, 'exactly one confirmation message');
+ assert.match(sentTexts[0][1], /CR-ABC/); // the booking code is in the message body
+});
+
+test('SECURITY/MONEY: confirmBooking is idempotent — a duplicate payment webhook does NOT re-confirm or re-notify', async () => {
+ reset([{ rows: [] }]); // UPDATE matched no row (already confirmed) → early return
+ await confirmBooking(7);
+ assert.equal(sentTexts.length, 0, 'no second confirmation notification');
+ assert.equal(calls.length, 1, 'stops right after the guarded UPDATE — no user/place lookups, no re-send');
+});
+
+test('MONEY: POST /bookings rejects overlapping dates with 409 and never INSERTs a booking', async () => {
+ const token = signToken({ sub: 3, role: 'guest' });
+ reset([
+ { rows: [{ max_guests: 4, booking_type: 'nightly', min_nights: 1, base_price: 12000, cleaning_fee: 4000, platform_fee_bps: 1000, place_id: 5, host_id: 9, currency: 'USD', name: 'Casa' }] }, // place_booking
+ { rows: [{ '?column?': 1 }] }, // overlap conflict found
+ ]);
+ const r = await post('/api/app/bookings', { place_slug: 'casa', check_in: '2026-09-10', check_out: '2026-09-13', guests: 2 }, token);
+ assert.equal(r.status, 409);
+ assert.equal(hasSql(/INSERT INTO bookings/), false, 'a conflicting booking must never be inserted');
+});
+
+test('MONEY: POST /bookings happy path persists a split whose parts reconstruct the total', async () => {
+ const token = signToken({ sub: 3, role: 'guest' });
+ reset([
+ { rows: [{ max_guests: 4, booking_type: 'nightly', min_nights: 1, base_price: 12000, cleaning_fee: 4000, platform_fee_bps: 1000, place_id: 5, host_id: 9, currency: 'USD', name: 'Casa' }] }, // place_booking
+ { rows: [] }, // no overlap
+ { rows: [{ id: 7, code: 'CR-XYZ', status: 'pending' }] }, // INSERT ... RETURNING *
+ ]);
+ const r = await post('/api/app/bookings', { place_slug: 'casa', check_in: '2026-09-10', check_out: '2026-09-13', guests: 2 }, token);
+ assert.equal(r.status, 200);
+ assert.ok(hasSql(/INSERT INTO bookings/), 'a valid booking should be inserted');
+ const s = r.json.split;
+ assert.equal(s.total, 40000); // 12000*3 nights + 4000 cleaning
+ assert.equal(s.platformFee, 4000); // 10% of 40000
+ assert.equal(s.hostPayout + s.platformFee + (s.processorFee || 0), s.total); // conservation: no money vanishes
+});
← 5e6298b yoloforever: cycle 6 ledger — route-level webhook forgery te
·
back to Costa Rica
·
costa-rica: Cody-gate fix — REAL consent bug: confirmBooking 4e14aa3 →