← back to Costa Rica
costa-rica: marketplace schema migration 004 (bookings, payments, payouts SINPE+Plaid, WhatsApp, webhooks) — TK-10346
e36298c529e62be17002b000d8cfc13275c8bdbb · 2026-08-07 09:33:07 -0700 · Steve
Files touched
A scripts/migrate_004_marketplace.sql
Diff
commit e36298c529e62be17002b000d8cfc13275c8bdbb
Author: Steve <steve@designerwallcoverings.com>
Date: Fri Aug 7 09:33:07 2026 -0700
costa-rica: marketplace schema migration 004 (bookings, payments, payouts SINPE+Plaid, WhatsApp, webhooks) — TK-10346
---
scripts/migrate_004_marketplace.sql | 253 ++++++++++++++++++++++++++++++++++++
1 file changed, 253 insertions(+)
diff --git a/scripts/migrate_004_marketplace.sql b/scripts/migrate_004_marketplace.sql
new file mode 100644
index 0000000..b7ffa61
--- /dev/null
+++ b/scripts/migrate_004_marketplace.sql
@@ -0,0 +1,253 @@
+-- ============================================================================
+-- Costa Rica marketplace layer — booking + payment + payouts + WhatsApp
+-- Migration 004 (additive; existing directory tables untouched)
+--
+-- Decisions locked (TK-10346, 2026-08-07):
+-- * Native Expo/EAS app; this DB is the API backend.
+-- * CR-native processor only (Tilopay primary, ONVO swappable) — handles
+-- USD international cards AND CRC / SINPE Movil. No Stripe.
+-- * Payouts: SINPE Movil (Tico hosts) + Plaid (foreign hosts).
+-- * WhatsApp: Meta Cloud API direct.
+--
+-- Money is stored in MINOR UNITS (integer cents / centimos) + a currency code,
+-- never floats — the cardinal rule for payment ledgers.
+-- ============================================================================
+
+BEGIN;
+
+-- ---------------------------------------------------------------------------
+-- 1. App users (travelers AND hosts share one identity table)
+-- ---------------------------------------------------------------------------
+CREATE TABLE IF NOT EXISTS app_users (
+ id BIGSERIAL PRIMARY KEY,
+ email TEXT UNIQUE,
+ phone_e164 TEXT UNIQUE, -- +506... ; also the WhatsApp id
+ full_name TEXT,
+ password_hash TEXT, -- null when phone/OTP-only
+ locale TEXT NOT NULL DEFAULT 'es-CR',
+ role TEXT NOT NULL DEFAULT 'traveler' -- traveler | host | admin
+ CHECK (role IN ('traveler','host','admin')),
+ is_host BOOLEAN NOT NULL DEFAULT FALSE, -- can also host while a traveler
+ wa_opt_in BOOLEAN NOT NULL DEFAULT FALSE, -- consented to WhatsApp msgs
+ status TEXT NOT NULL DEFAULT 'active',
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+CREATE INDEX IF NOT EXISTS idx_app_users_phone ON app_users(phone_e164);
+
+-- ---------------------------------------------------------------------------
+-- 2. Hosts — a user who owns/claims one or more directory places
+-- ---------------------------------------------------------------------------
+CREATE TABLE IF NOT EXISTS hosts (
+ id BIGSERIAL PRIMARY KEY,
+ user_id BIGINT NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
+ legal_name TEXT,
+ cedula TEXT, -- cedula fisica/juridica
+ country TEXT NOT NULL DEFAULT 'CR', -- CR (SINPE) vs foreign (Plaid)
+ kyc_status TEXT NOT NULL DEFAULT 'unverified' -- unverified|pending|verified|rejected
+ CHECK (kyc_status IN ('unverified','pending','verified','rejected')),
+ default_payout_method_id BIGINT, -- FK added after payout_methods
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ UNIQUE (user_id)
+);
+
+-- Claim linking a host to an existing directory place (many places per host)
+CREATE TABLE IF NOT EXISTS place_hosts (
+ place_id INTEGER NOT NULL REFERENCES places(id) ON DELETE CASCADE,
+ host_id BIGINT NOT NULL REFERENCES hosts(id) ON DELETE CASCADE,
+ claim_status TEXT NOT NULL DEFAULT 'pending' -- pending|approved|rejected
+ CHECK (claim_status IN ('pending','approved','rejected')),
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ PRIMARY KEY (place_id, host_id)
+);
+
+-- ---------------------------------------------------------------------------
+-- 3. Bookability config for a place (only bookable places get a row)
+-- ---------------------------------------------------------------------------
+CREATE TABLE IF NOT EXISTS place_booking (
+ place_id INTEGER PRIMARY KEY REFERENCES places(id) ON DELETE CASCADE,
+ host_id BIGINT REFERENCES hosts(id) ON DELETE SET NULL,
+ booking_type TEXT NOT NULL DEFAULT 'nightly' -- nightly | slot | ticket | quote
+ CHECK (booking_type IN ('nightly','slot','ticket','quote')),
+ currency TEXT NOT NULL DEFAULT 'USD' -- USD or CRC; charged as-is
+ CHECK (currency IN ('USD','CRC')),
+ base_price INTEGER NOT NULL DEFAULT 0, -- minor units, per night/slot/ticket
+ cleaning_fee INTEGER NOT NULL DEFAULT 0,
+ max_guests INTEGER NOT NULL DEFAULT 1,
+ min_nights INTEGER NOT NULL DEFAULT 1,
+ platform_fee_bps INTEGER NOT NULL DEFAULT 1000, -- 10.00% marketplace take (basis pts)
+ instant_book BOOLEAN NOT NULL DEFAULT TRUE,
+ cancellation TEXT NOT NULL DEFAULT 'flexible',
+ is_active BOOLEAN NOT NULL DEFAULT TRUE,
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+-- Availability / blocked dates / per-date price override
+CREATE TABLE IF NOT EXISTS availability (
+ id BIGSERIAL PRIMARY KEY,
+ place_id INTEGER NOT NULL REFERENCES places(id) ON DELETE CASCADE,
+ day DATE NOT NULL, -- for nightly/ticket
+ slot_start TIMESTAMPTZ, -- for slot bookings (tours)
+ slot_end TIMESTAMPTZ,
+ capacity INTEGER NOT NULL DEFAULT 1,
+ price_override INTEGER, -- minor units, optional
+ is_blocked BOOLEAN NOT NULL DEFAULT FALSE,
+ UNIQUE (place_id, day, slot_start)
+);
+CREATE INDEX IF NOT EXISTS idx_availability_place_day ON availability(place_id, day);
+
+-- ---------------------------------------------------------------------------
+-- 4. Bookings
+-- ---------------------------------------------------------------------------
+CREATE TABLE IF NOT EXISTS bookings (
+ id BIGSERIAL PRIMARY KEY,
+ code TEXT UNIQUE NOT NULL, -- human ref e.g. CR-8F3K2Q
+ place_id INTEGER NOT NULL REFERENCES places(id),
+ host_id BIGINT REFERENCES hosts(id),
+ traveler_id BIGINT NOT NULL REFERENCES app_users(id),
+ check_in DATE,
+ check_out DATE,
+ slot_start TIMESTAMPTZ,
+ slot_end TIMESTAMPTZ,
+ guests INTEGER NOT NULL DEFAULT 1,
+ currency TEXT NOT NULL CHECK (currency IN ('USD','CRC')),
+ subtotal INTEGER NOT NULL, -- minor units
+ fees INTEGER NOT NULL DEFAULT 0, -- cleaning + platform fee
+ platform_fee INTEGER NOT NULL DEFAULT 0, -- our cut (subset of fees)
+ total INTEGER NOT NULL, -- charged to traveler
+ host_payout INTEGER NOT NULL DEFAULT 0, -- owed to host (total - platform_fee - processor)
+ status TEXT NOT NULL DEFAULT 'pending' -- pending|confirmed|cancelled|completed|refunded
+ CHECK (status IN ('pending','confirmed','cancelled','completed','refunded')),
+ notes TEXT,
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+CREATE INDEX IF NOT EXISTS idx_bookings_traveler ON bookings(traveler_id);
+CREATE INDEX IF NOT EXISTS idx_bookings_place ON bookings(place_id);
+CREATE INDEX IF NOT EXISTS idx_bookings_status ON bookings(status);
+
+-- ---------------------------------------------------------------------------
+-- 5. Payments (charges taken from the traveler via the CR-native processor)
+-- ---------------------------------------------------------------------------
+CREATE TABLE IF NOT EXISTS payments (
+ id BIGSERIAL PRIMARY KEY,
+ booking_id BIGINT REFERENCES bookings(id) ON DELETE SET NULL,
+ provider TEXT NOT NULL DEFAULT 'tilopay' -- tilopay | onvo
+ CHECK (provider IN ('tilopay','onvo')),
+ provider_ref TEXT, -- processor charge/order id
+ method TEXT, -- card | sinpe | link
+ currency TEXT NOT NULL CHECK (currency IN ('USD','CRC')),
+ amount INTEGER NOT NULL, -- minor units
+ processor_fee INTEGER NOT NULL DEFAULT 0,
+ status TEXT NOT NULL DEFAULT 'requires_payment'
+ CHECK (status IN ('requires_payment','processing','succeeded','failed','refunded','partially_refunded')),
+ three_ds BOOLEAN NOT NULL DEFAULT FALSE,
+ live_mode BOOLEAN NOT NULL DEFAULT FALSE, -- FALSE = sandbox/test
+ raw JSONB, -- last provider payload
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ UNIQUE (provider, provider_ref)
+);
+CREATE INDEX IF NOT EXISTS idx_payments_booking ON payments(booking_id);
+CREATE INDEX IF NOT EXISTS idx_payments_status ON payments(status);
+
+-- ---------------------------------------------------------------------------
+-- 6. Payout methods — SINPE Movil (Tico) and Plaid (foreign) both live here
+-- ---------------------------------------------------------------------------
+CREATE TABLE IF NOT EXISTS payout_methods (
+ id BIGSERIAL PRIMARY KEY,
+ host_id BIGINT NOT NULL REFERENCES hosts(id) ON DELETE CASCADE,
+ kind TEXT NOT NULL -- sinpe_movil | cr_iban | plaid_ach
+ CHECK (kind IN ('sinpe_movil','cr_iban','plaid_ach')),
+ label TEXT,
+ -- SINPE Movil / CR bank
+ sinpe_phone TEXT, -- +506 number registered to SINPE
+ cr_iban TEXT, -- CR#### IBAN (22 chars)
+ bank_name TEXT,
+ -- Plaid (foreign / US host bank verification + ACH)
+ plaid_item_id TEXT,
+ plaid_access_token TEXT, -- store encrypted at rest (see NOTES)
+ plaid_account_id TEXT,
+ account_last4 TEXT,
+ currency TEXT NOT NULL DEFAULT 'CRC',
+ verified BOOLEAN NOT NULL DEFAULT FALSE,
+ is_default BOOLEAN NOT NULL DEFAULT FALSE,
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+CREATE INDEX IF NOT EXISTS idx_payout_methods_host ON payout_methods(host_id);
+
+-- Now that payout_methods exists, wire the host default FK
+ALTER TABLE hosts
+ DROP CONSTRAINT IF EXISTS hosts_default_payout_fk,
+ ADD CONSTRAINT hosts_default_payout_fk
+ FOREIGN KEY (default_payout_method_id)
+ REFERENCES payout_methods(id) ON DELETE SET NULL;
+
+-- ---------------------------------------------------------------------------
+-- 7. Payouts — money we settle to the host after a booking completes
+-- ---------------------------------------------------------------------------
+CREATE TABLE IF NOT EXISTS payouts (
+ id BIGSERIAL PRIMARY KEY,
+ host_id BIGINT NOT NULL REFERENCES hosts(id),
+ booking_id BIGINT REFERENCES bookings(id),
+ payout_method_id BIGINT REFERENCES payout_methods(id),
+ rail TEXT NOT NULL -- sinpe | plaid_ach | manual
+ CHECK (rail IN ('sinpe','plaid_ach','manual')),
+ currency TEXT NOT NULL CHECK (currency IN ('USD','CRC')),
+ amount INTEGER NOT NULL, -- minor units
+ provider_ref TEXT,
+ status TEXT NOT NULL DEFAULT 'scheduled'
+ CHECK (status IN ('scheduled','processing','paid','failed','cancelled')),
+ live_mode BOOLEAN NOT NULL DEFAULT FALSE,
+ raw JSONB,
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ paid_at TIMESTAMPTZ
+);
+CREATE INDEX IF NOT EXISTS idx_payouts_host ON payouts(host_id);
+
+-- ---------------------------------------------------------------------------
+-- 8. WhatsApp — contacts + full message log (in/out, all message types)
+-- ---------------------------------------------------------------------------
+CREATE TABLE IF NOT EXISTS whatsapp_contacts (
+ id BIGSERIAL PRIMARY KEY,
+ wa_id TEXT UNIQUE NOT NULL, -- E.164 without '+', Meta's wa_id
+ user_id BIGINT REFERENCES app_users(id) ON DELETE SET NULL,
+ profile_name TEXT,
+ opt_in BOOLEAN NOT NULL DEFAULT FALSE,
+ last_inbound_at TIMESTAMPTZ, -- drives the 24h session window
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+CREATE TABLE IF NOT EXISTS whatsapp_messages (
+ id BIGSERIAL PRIMARY KEY,
+ wa_message_id TEXT UNIQUE, -- Meta id (idempotency)
+ contact_id BIGINT REFERENCES whatsapp_contacts(id) ON DELETE CASCADE,
+ booking_id BIGINT REFERENCES bookings(id) ON DELETE SET NULL,
+ direction TEXT NOT NULL CHECK (direction IN ('in','out')),
+ msg_type TEXT NOT NULL, -- text|template|interactive|image|document|location|audio|video|contacts|reaction
+ body TEXT,
+ payload JSONB, -- full Meta payload
+ status TEXT, -- sent|delivered|read|failed (out)
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+CREATE INDEX IF NOT EXISTS idx_wa_messages_contact ON whatsapp_messages(contact_id);
+CREATE INDEX IF NOT EXISTS idx_wa_messages_booking ON whatsapp_messages(booking_id);
+
+-- ---------------------------------------------------------------------------
+-- 9. Webhook events — idempotency ledger for ALL inbound webhooks
+-- (payments + whatsapp). Never process the same event twice.
+-- ---------------------------------------------------------------------------
+CREATE TABLE IF NOT EXISTS webhook_events (
+ id BIGSERIAL PRIMARY KEY,
+ source TEXT NOT NULL, -- tilopay|onvo|whatsapp|plaid
+ external_id TEXT NOT NULL, -- provider event id
+ event_type TEXT,
+ processed BOOLEAN NOT NULL DEFAULT FALSE,
+ payload JSONB,
+ received_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ UNIQUE (source, external_id)
+);
+
+COMMIT;
← 8c8ebde costa-rica: escape p.description in card innerHTML (Cody ARG
·
back to Costa Rica
·
costa-rica: resumable Hacienda enrichment drain (25k cedulas e747338 →