schema-w3-production.sql

-- Production Web3 terminal schema (InsForge)
-- From repo root: npx @insforge/cli db import web3-terminal/schema-w3-production.sql

CREATE TABLE IF NOT EXISTS w3_users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  handle TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS w3_wallets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES w3_users(id) ON DELETE CASCADE,
  address TEXT NOT NULL UNIQUE,
  balance NUMERIC(24, 8) NOT NULL DEFAULT 0 CHECK (balance >= 0),
  source TEXT NOT NULL CHECK (source IN ('internal', 'external')),
  label TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_active_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_w3_wallets_user ON w3_wallets(user_id);
CREATE INDEX IF NOT EXISTS idx_w3_wallets_address_lower ON w3_wallets(lower(address));

CREATE TABLE IF NOT EXISTS w3_transactions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  from_wallet_id UUID NOT NULL REFERENCES w3_wallets(id),
  to_wallet_id UUID NOT NULL REFERENCES w3_wallets(id),
  amount NUMERIC(24, 8) NOT NULL CHECK (amount > 0),
  status TEXT NOT NULL DEFAULT 'completed' CHECK (status IN ('completed', 'failed')),
  failure_reason TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_w3_txn_from ON w3_transactions(from_wallet_id);
CREATE INDEX IF NOT EXISTS idx_w3_txn_to ON w3_transactions(to_wallet_id);
CREATE INDEX IF NOT EXISTS idx_w3_txn_created ON w3_transactions(created_at DESC);

CREATE TABLE IF NOT EXISTS w3_sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  client_session_id TEXT NOT NULL UNIQUE,
  user_id UUID REFERENCES w3_users(id) ON DELETE SET NULL,
  wallet_id UUID REFERENCES w3_wallets(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_active_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_w3_sessions_active ON w3_sessions(last_active_at DESC);

-- Atomic transfer: row locks, balance checks, single COMMIT scope (function runs in one txn)
CREATE OR REPLACE FUNCTION w3_transfer_atomic(
  p_from_wallet_id UUID,
  p_to_address TEXT,
  p_amount NUMERIC
) RETURNS JSONB AS $$
DECLARE
  b_from NUMERIC;
  v_to_id UUID;
  v_from_addr TEXT;
  v_to_addr TEXT;
BEGIN
  IF p_amount IS NULL OR p_amount <= 0 THEN
    RETURN jsonb_build_object('ok', false, 'error', 'Amount must be positive');
  END IF;

  SELECT balance, address INTO b_from, v_from_addr
  FROM w3_wallets WHERE id = p_from_wallet_id FOR UPDATE;
  IF NOT FOUND THEN
    RETURN jsonb_build_object('ok', false, 'error', 'Sender wallet not found');
  END IF;

  IF b_from < p_amount THEN
    RETURN jsonb_build_object('ok', false, 'error', 'Insufficient balance');
  END IF;

  SELECT id, address INTO v_to_id, v_to_addr
  FROM w3_wallets WHERE lower(address) = lower(trim(p_to_address)) FOR UPDATE;
  IF NOT FOUND THEN
    RETURN jsonb_build_object('ok', false, 'error', 'Recipient address not registered');
  END IF;

  IF v_to_id = p_from_wallet_id THEN
    RETURN jsonb_build_object('ok', false, 'error', 'Cannot transfer to the same wallet');
  END IF;

  UPDATE w3_wallets SET balance = balance - p_amount, last_active_at = NOW() WHERE id = p_from_wallet_id;
  UPDATE w3_wallets SET balance = balance + p_amount, last_active_at = NOW() WHERE id = v_to_id;

  INSERT INTO w3_transactions (from_wallet_id, to_wallet_id, amount, status)
  VALUES (p_from_wallet_id, v_to_id, p_amount, 'completed');

  RETURN jsonb_build_object(
    'ok', true,
    'to_address', v_to_addr,
    'from_address', v_from_addr
  );
END;
$$ LANGUAGE plpgsql;