← back to Costa Rica
scripts/migrate_004_marketplace.sql
260 lines
-- ============================================================================
-- 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);
-- At most ONE default payout method per host. Without this, a host with two
-- is_default=TRUE rows makes createPayoutForBooking's `ORDER BY is_default DESC
-- LIMIT 1` tie and pick an arbitrary rail — silent money-misdirection. The UI
-- must clear the prior default before setting a new one. (Cody gate, TK-10346 C3)
CREATE UNIQUE INDEX IF NOT EXISTS idx_payout_methods_one_default_per_host
ON payout_methods(host_id) WHERE is_default;
-- 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;