supabase-schema.sql

-- RemitX database schema — Supabase / Postgres
-- Run this in the Supabase SQL Editor: your project → SQL Editor → paste
-- this whole file → Run. Safe to re-run (uses IF NOT EXISTS / OR REPLACE
-- throughout), so re-running it after a change won't error on what already
-- exists.
--
-- IDs are UUIDs generated by Postgres itself (gen_random_uuid()) — there's
-- no ORM generating them app-side anymore, the database owns it.
-- "updatedAt" columns are kept current by a trigger, since nothing in the
-- app sets them manually.

-- ── Extension needed for gen_random_uuid() ─────────────────────────────────
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- ── Enums ────────────────────────────────────────────────────────────────
DO $$ BEGIN
  CREATE TYPE "KycStatus" AS ENUM ('pending', 'verified', 'rejected');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE TYPE "TransactionStatus" AS ENUM ('pending', 'validating', 'confirmed', 'failed');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

DO $$ BEGIN
  CREATE TYPE "EscrowStatus" AS ENUM ('locked', 'released', 'refunded', 'expired');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- ── updatedAt trigger helper ────────────────────────────────────────────────
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW."updatedAt" = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ── users ────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "users" (
  "id"               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "email"            TEXT NOT NULL UNIQUE,
  "firstName"        TEXT NOT NULL,
  "lastName"         TEXT NOT NULL,
  "passwordHash"     TEXT NOT NULL,
  "stellarPublicKey"    TEXT UNIQUE,
  "kycStatus"           "KycStatus" NOT NULL DEFAULT 'pending',
  "sessionVersion"      INTEGER NOT NULL DEFAULT 1,
  "failedLoginAttempts" INTEGER NOT NULL DEFAULT 0,
  "lockedUntil"         TIMESTAMPTZ,
  "createdAt"           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  "updatedAt"        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

DROP TRIGGER IF EXISTS users_set_updated_at ON "users";
CREATE TRIGGER users_set_updated_at
  BEFORE UPDATE ON "users"
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- ── transactions ─────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "transactions" (
  "id"               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "userId"           UUID NOT NULL REFERENCES "users"("id"),
  "fromAsset"        TEXT NOT NULL,
  "toAsset"          TEXT NOT NULL,
  "fromAmount"       TEXT NOT NULL,     -- stored as string: avoids float precision loss
  "toAmount"         TEXT,
  "recipientAddress" TEXT NOT NULL,
  "stellarTxHash"    TEXT,
  "escrowId"         UUID,              -- informational; the real relation is
                                         -- escrows.transactionId -> transactions.id
  "status"           "TransactionStatus" NOT NULL DEFAULT 'pending',
  "createdAt"        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  "confirmedAt"      TIMESTAMPTZ,
  "updatedAt"        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS "transactions_userId_idx" ON "transactions"("userId");
CREATE INDEX IF NOT EXISTS "transactions_status_idx" ON "transactions"("status");

DROP TRIGGER IF EXISTS transactions_set_updated_at ON "transactions";
CREATE TRIGGER transactions_set_updated_at
  BEFORE UPDATE ON "transactions"
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- ── rates ────────────────────────────────────────────────────────────────
-- Not currently written to by the app (lib/rates.ts caches in memory), but
-- kept in the schema for a future move to persistent/shared rate caching.
CREATE TABLE IF NOT EXISTS "rates" (
  "id"        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "fromAsset" TEXT NOT NULL,
  "toAsset"   TEXT NOT NULL,
  "rate"      TEXT NOT NULL,
  "fetchedAt" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE ("fromAsset", "toAsset")
);

-- ── escrows ──────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "escrows" (
  "id"               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "userId"           UUID NOT NULL REFERENCES "users"("id"),
  "transactionId"    UUID NOT NULL UNIQUE REFERENCES "transactions"("id"), -- one escrow per transaction
  "contractAddress"  TEXT NOT NULL,          -- Soroban contract address
  "senderAddress"    TEXT NOT NULL,          -- Stellar public key
  "recipientAddress" TEXT NOT NULL,          -- Stellar public key
  "amount"           TEXT NOT NULL,
  "asset"            TEXT NOT NULL,          -- e.g. "USDC", "XLM"
  "status"           "EscrowStatus" NOT NULL DEFAULT 'locked',
  "depositTxHash"    TEXT,
  "releaseTxHash"    TEXT,
  "createdAt"        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  "expiresAt"        TIMESTAMPTZ NOT NULL,
  "updatedAt"        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS "escrows_userId_idx" ON "escrows"("userId");
CREATE INDEX IF NOT EXISTS "escrows_senderAddress_idx" ON "escrows"("senderAddress");
CREATE INDEX IF NOT EXISTS "escrows_recipientAddress_idx" ON "escrows"("recipientAddress");
CREATE INDEX IF NOT EXISTS "escrows_status_idx" ON "escrows"("status");

DROP TRIGGER IF EXISTS escrows_set_updated_at ON "escrows";
CREATE TRIGGER escrows_set_updated_at
  BEFORE UPDATE ON "escrows"
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- ── Row Level Security ───────────────────────────────────────────────────
-- The app talks to Supabase using the SERVICE ROLE key from Next.js API
-- routes only (see src/lib/supabase.ts) — every route already checks the
-- session cookie itself before touching the database, the same way the
-- prior Prisma setup worked. The service role key bypasses RLS entirely,
-- so RLS is left off by default here; nothing in the browser talks to
-- Supabase directly. If you ever add client-side Supabase calls (using the
-- anon key), enable RLS and add real policies before doing that — an
-- open table with the anon key and no policies is publicly readable.

-- ── Security hardening: RLS enabled with restrictive policies ────────────
ALTER TABLE "users" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "transactions" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "escrows" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "rates" ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "users_select_own" ON "users";
CREATE POLICY "users_select_own" ON "users"
  FOR SELECT USING (auth.uid() = id);

DROP POLICY IF EXISTS "users_update_own" ON "users";
CREATE POLICY "users_update_own" ON "users"
  FOR UPDATE USING (auth.uid() = id)
  WITH CHECK (auth.uid() = id);

DROP POLICY IF EXISTS "transactions_select_own" ON "transactions";
CREATE POLICY "transactions_select_own" ON "transactions"
  FOR SELECT USING (auth.uid() = "userId");

DROP POLICY IF EXISTS "escrows_select_own" ON "escrows";
CREATE POLICY "escrows_select_own" ON "escrows"
  FOR SELECT USING (auth.uid() = "userId");

DROP POLICY IF EXISTS "rates_select_public" ON "rates";
CREATE POLICY "rates_select_public" ON "rates"
  FOR SELECT USING (true);

-- ── Database permission restrictions ─────────────────────────────────────
REVOKE ALL ON "users" FROM anon;
REVOKE ALL ON "transactions" FROM anon;
REVOKE ALL ON "escrows" FROM anon;
REVOKE ALL ON "rates" FROM anon;

GRANT SELECT ON "users" TO authenticated;
GRANT SELECT ON "transactions" TO authenticated;
GRANT SELECT ON "escrows" TO authenticated;
GRANT SELECT ON "rates" TO authenticated;