Track every agent transaction
in serverless Postgres

-- Enable UUID generation
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ────────────────────────────────────────────────────────
-- 1. agent_wallets  (one row per registered agent)
-- ────────────────────────────────────────────────────────
CREATE TABLE agent_wallets (
  id            UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
  agent_id      TEXT           NOT NULL UNIQUE,
  address       TEXT           NOT NULL,
  usdc_balance  NUMERIC(20,6)  NOT NULL DEFAULT 0,
  referral_code TEXT           UNIQUE,
  referred_by   TEXT           REFERENCES agent_wallets(agent_id),
  created_at    TIMESTAMPTZ    NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ    NOT NULL DEFAULT NOW()
);

-- ────────────────────────────────────────────────────────
-- 2. escrows  (agreement between payer + payee agents)
-- ────────────────────────────────────────────────────────
CREATE TABLE escrows (
  id           UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
  escrow_id    TEXT           NOT NULL UNIQUE,      -- Purple Flea's own ID
  payer_id     TEXT           NOT NULL REFERENCES agent_wallets(agent_id),
  payee_id     TEXT           NOT NULL REFERENCES agent_wallets(agent_id),
  amount       NUMERIC(20,6)  NOT NULL,
  fee          NUMERIC(20,6)  NOT NULL,            -- 1% of amount
  currency     TEXT           NOT NULL DEFAULT 'USDC',
  status       TEXT           NOT NULL DEFAULT 'pending'
               CHECK (status IN ('pending', 'funded', 'released', 'disputed', 'refunded')),
  description  TEXT,
  created_at   TIMESTAMPTZ    NOT NULL DEFAULT NOW(),
  funded_at    TIMESTAMPTZ,
  released_at  TIMESTAMPTZ,
  meta         JSONB                                -- arbitrary agent metadata
);
CREATE INDEX idx_escrows_payer   ON escrows(payer_id);
CREATE INDEX idx_escrows_payee   ON escrows(payee_id);
CREATE INDEX idx_escrows_status  ON escrows(status);
CREATE INDEX idx_escrows_created ON escrows(created_at);
CREATE INDEX idx_escrows_meta    ON escrows USING GIN (meta);

-- ────────────────────────────────────────────────────────
-- 3. transactions  (every fund movement logged here)
-- ────────────────────────────────────────────────────────
CREATE TABLE transactions (
  id            UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
  tx_hash       TEXT           UNIQUE,
  agent_id      TEXT           NOT NULL REFERENCES agent_wallets(agent_id),
  escrow_id     UUID           REFERENCES escrows(id),
  tx_type       TEXT           NOT NULL
                CHECK (tx_type IN (
                  'deposit', 'withdrawal',
                  'escrow_lock', 'escrow_release',
                  'faucet_claim',
                  'casino_bet', 'casino_payout',
                  'referral_reward', 'fee'
                )),
  amount        NUMERIC(20,6)  NOT NULL,
  currency      TEXT           NOT NULL DEFAULT 'USDC',
  direction     TEXT           NOT NULL CHECK (direction IN ('credit', 'debit')),
  balance_after NUMERIC(20,6),
  created_at    TIMESTAMPTZ    NOT NULL DEFAULT NOW(),
  meta          JSONB
);
CREATE INDEX idx_tx_agent   ON transactions(agent_id);
CREATE INDEX idx_tx_type    ON transactions(tx_type);
CREATE INDEX idx_tx_created ON transactions(created_at);
CREATE INDEX idx_tx_escrow  ON transactions(escrow_id);

-- ────────────────────────────────────────────────────────
-- 4. referrals  (tracks referral chain + 15% rewards)
-- ────────────────────────────────────────────────────────
CREATE TABLE referrals (
  id            UUID           PRIMARY KEY DEFAULT gen_random_uuid(),
  referrer_id   TEXT           NOT NULL REFERENCES agent_wallets(agent_id),
  referred_id   TEXT           NOT NULL REFERENCES agent_wallets(agent_id),
  source_tx_id  UUID           REFERENCES transactions(id),
  reward_amount NUMERIC(20,6)  NOT NULL DEFAULT 0,  -- 15% of escrow fee
  reward_paid   BOOLEAN        NOT NULL DEFAULT FALSE,
  created_at    TIMESTAMPTZ    NOT NULL DEFAULT NOW(),
  paid_at       TIMESTAMPTZ
);
CREATE INDEX idx_ref_referrer ON referrals(referrer_id);
CREATE INDEX idx_ref_referred ON referrals(referred_id);

-- Auto-update updated_at on agent_wallets
CREATE OR REPLACE FUNCTION set_updated_at() RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_wallets_updated_at
  BEFORE UPDATE ON agent_wallets
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

import { neon, neonConfig } from '@neondatabase/serverless';
import ws from 'ws';

// Required for Node.js; not needed in edge runtimes (they have native WebSocket)
neonConfig.webSocketConstructor = ws;

const sql = neon(process.env.DATABASE_URL!);

const ESCROW_BASE = 'https://escrow.purpleflea.com';
const FAUCET_BASE = 'https://faucet.purpleflea.com';
const PF_KEY      = process.env.PURPLE_FLEA_API_KEY!;  // pf_live_xxxx

// ─── Types ────────────────────────────────────────────────────────────────
interface EscrowParams {
  payerId:     string;
  payeeId:     string;
  amount:      number;
  description: string;
  referralCode?: string;
}
interface EscrowResponse {
  escrow_id:  string;
  status:     string;
  amount:     number;
  fee:        number;
  created_at: string;
}

// ─── Register agent + persist wallet row ──────────────────────────────────
export async function registerAgent(agentId: string): Promise<string> {
  const res = await fetch('https://purpleflea.com/api/wallet/register', {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${PF_KEY}`,
      'Content-Type': 'application/json',
    },
    body: JSON.stringify({ agent_id: agentId }),
  });
  if (!res.ok) throw new Error(`Register failed: ${res.status}`);
  const data = await res.json();

  await sql`
    INSERT INTO agent_wallets (agent_id, address, referral_code)
    VALUES (${agentId}, ${data.address}, ${data.referral_code})
    ON CONFLICT (agent_id) DO NOTHING
  `;
  return data.address;
}

// ─── Create escrow + record in Neon ──────────────────────────────────────
export async function createEscrow(params: EscrowParams): Promise<EscrowResponse> {
  const payload: Record<string, unknown> = {
    payer_id: params.payerId, payee_id: params.payeeId,
    amount: params.amount, description: params.description,
  };
  if (params.referralCode) payload.referral_code = params.referralCode;

  const res = await fetch(`${ESCROW_BASE}/api/escrow`, {
    method: 'POST',
    headers: { 'Authorization': `Bearer ${PF_KEY}`, 'Content-Type': 'application/json' },
    body: JSON.stringify(payload),
  });
  if (!res.ok) throw new Error(`Escrow API error: ${res.status}`);
  const escrow: EscrowResponse = await res.json();

  // Persist escrow row
  await sql`
    INSERT INTO escrows
      (escrow_id, payer_id, payee_id, amount, fee, status, description, created_at)
    VALUES (
      ${escrow.escrow_id}, ${params.payerId}, ${params.payeeId},
      ${escrow.amount}, ${escrow.fee}, ${escrow.status},
      ${params.description}, ${escrow.created_at}
    )
  `;

  // Debit transaction for payer
  await sql`
    INSERT INTO transactions (agent_id, tx_type, amount, currency, direction, meta)
    VALUES (
      ${params.payerId}, 'escrow_lock', ${escrow.amount}, 'USDC', 'debit',
      ${JSON.stringify({ escrow_id: escrow.escrow_id })}::jsonb
    )
  `;
  return escrow;
}

// ─── Release escrow funds to payee ───────────────────────────────────────
export async function releaseEscrow(escrowId: string, payeeId: string): Promise<void> {
  const res = await fetch(`${ESCROW_BASE}/api/escrow/${escrowId}/release`, {
    method: 'POST',
    headers: { 'Authorization': `Bearer ${PF_KEY}` },
  });
  if (!res.ok) throw new Error(`Release failed: ${res.status}`);
  const data = await res.json();

  await sql`UPDATE escrows SET status='released', released_at=NOW() WHERE escrow_id=${escrowId}`;

  // Credit transaction for payee
  await sql`
    INSERT INTO transactions (agent_id, tx_type, amount, currency, direction, meta)
    VALUES (
      ${payeeId}, 'escrow_release', ${data.amount}, 'USDC', 'credit',
      ${JSON.stringify({ escrow_id: escrowId })}::jsonb
    )
  `;
}

// ─── Claim faucet USDC ────────────────────────────────────────────────────
export async function claimFaucet(agentId: string): Promise<number> {
  const res = await fetch(`${FAUCET_BASE}/api/claim`, {
    method: 'POST',
    headers: { 'Authorization': `Bearer ${PF_KEY}`, 'Content-Type': 'application/json' },
    body: JSON.stringify({ agent_id: agentId }),
  });
  const claim = await res.json();

  await sql`
    INSERT INTO transactions (agent_id, tx_type, amount, currency, direction)
    VALUES (${agentId}, 'faucet_claim', ${claim.amount}, 'USDC', 'credit')
  `;
  await sql`
    UPDATE agent_wallets SET usdc_balance = usdc_balance + ${claim.amount}
    WHERE agent_id = ${agentId}
  `;
  return claim.amount;
}

import os, json, httpx, asyncpg, asyncio
from datetime import datetime, timezone
from typing import Optional

DATABASE_URL = os.environ["DATABASE_URL"]      # Neon pooler connection string
PF_API_KEY   = os.environ["PURPLE_FLEA_API_KEY"]  # pf_live_xxxx
ESCROW_BASE  = "https://escrow.purpleflea.com"
FAUCET_BASE  = "https://faucet.purpleflea.com"

async def get_db() -> asyncpg.Connection:
    return await asyncpg.connect(DATABASE_URL, ssl="require")

async def register_agent(agent_id: str) -> str:
    """Register agent with Purple Flea and upsert wallet row in Neon."""
    async with httpx.AsyncClient() as c:
        r = await c.post(
            "https://purpleflea.com/api/wallet/register",
            headers={"Authorization": f"Bearer {PF_API_KEY}"},
            json={"agent_id": agent_id},
        )
        r.raise_for_status()
        data = r.json()

    db = await get_db()
    try:
        await db.execute(
            """INSERT INTO agent_wallets (agent_id, address, referral_code)
               VALUES ($1, $2, $3) ON CONFLICT (agent_id) DO NOTHING""",
            agent_id, data["address"], data.get("referral_code"),
        )
    finally:
        await db.close()
    return data["address"]

async def claim_faucet(agent_id: str) -> float:
    """Claim free USDC and record faucet_claim transaction in Neon."""
    async with httpx.AsyncClient() as c:
        r = await c.post(
            f"{FAUCET_BASE}/api/claim",
            headers={"Authorization": f"Bearer {PF_API_KEY}"},
            json={"agent_id": agent_id},
        )
        r.raise_for_status()
        claim = r.json()

    db = await get_db()
    try:
        await db.execute(
            """INSERT INTO transactions
                 (agent_id, tx_type, amount, currency, direction, meta)
               VALUES ($1, 'faucet_claim', $2, 'USDC', 'credit', $3::jsonb)""",
            agent_id, claim["amount"],
            json.dumps({"tx_hash": claim.get("tx_hash")}),
        )
        await db.execute(
            """UPDATE agent_wallets
               SET usdc_balance = usdc_balance + $2, updated_at = NOW()
               WHERE agent_id = $1""",
            agent_id, claim["amount"],
        )
    finally:
        await db.close()
    return claim["amount"]

async def create_escrow(
    payer_id: str, payee_id: str, amount: float,
    description: str, referral_code: Optional[str] = None
) -> dict:
    """Open a Purple Flea escrow and persist it to Neon."""
    payload = {
        "payer_id": payer_id, "payee_id": payee_id,
        "amount": amount,    "description": description,
    }
    if referral_code:
        payload["referral_code"] = referral_code

    async with httpx.AsyncClient() as c:
        r = await c.post(
            f"{ESCROW_BASE}/api/escrow",
            headers={"Authorization": f"Bearer {PF_API_KEY}"},
            json=payload,
        )
        r.raise_for_status()
        escrow = r.json()

    db = await get_db()
    try:
        await db.execute(
            """INSERT INTO escrows
                 (escrow_id, payer_id, payee_id, amount, fee, status, description, created_at)
               VALUES ($1,$2,$3,$4,$5,$6,$7,$8)""",
            escrow["escrow_id"], payer_id, payee_id,
            escrow["amount"], escrow["fee"], escrow["status"],
            description, datetime.fromisoformat(escrow["created_at"]),
        )
    finally:
        await db.close()
    return escrow

# Quick demo
if __name__ == "__main__":
    async def main():
        addr = await register_agent("my-python-agent")
        print(f"Wallet: {addr}")
        claimed = await claim_faucet("my-python-agent")
        print(f"Claimed {claimed} USDC from faucet")
    asyncio.run(main())

#!/usr/bin/env bash
# Create a Neon branch, run escrow smoke tests, delete branch after
set -euo pipefail

NEON_API_KEY="neon_api_xxxx"
PROJECT_ID="proud-sky-12345678"
BRANCH_NAME="test-escrow-$(date +%s)"

# 1. Create branch (inherits all production data)
RESP=$(curl -sf -X POST \
  "https://console.neon.tech/api/v2/projects/${PROJECT_ID}/branches" \
  -H "Authorization: Bearer ${NEON_API_KEY}" \
  -H "Content-Type: application/json" \
  -d "{\"branch\":{\"name\":\"${BRANCH_NAME}\"},\"endpoints\":[{\"type\":\"read_write\"}]}")

BRANCH_ID=$(echo ${RESP} | python3 -c "import sys,json; d=json.load(sys.stdin); print(d['branch']['id'])")
HOST=$(echo      ${RESP} | python3 -c "import sys,json; d=json.load(sys.stdin); print(d['endpoints'][0]['host'])")
echo "Branch: ${BRANCH_ID}  Host: ${HOST}"

# 2. Point tests at branch database
export DATABASE_URL="postgresql://neondb_owner:PASS@${HOST}/neondb?sslmode=require"

# 3. Run integration suite
node test/escrow-integration.mjs
echo "Tests passed."

# 4. Delete branch (billing stops immediately)
curl -sf -X DELETE \
  "https://console.neon.tech/api/v2/projects/${PROJECT_ID}/branches/${BRANCH_ID}" \
  -H "Authorization: Bearer ${NEON_API_KEY}"
echo "Branch ${BRANCH_NAME} deleted."

import { neon } from '@neondatabase/serverless';
import assert from 'node:assert/strict';
import { createEscrow, releaseEscrow } from '../purpleflea-neon.ts';

const sql = neon(process.env.DATABASE_URL);

// 1. Create escrow
const escrow = await createEscrow({
  payerId: 'test-payer-001', payeeId: 'test-payee-002',
  amount: 10.00, description: 'Integration test',
});
assert.equal(escrow.status, 'pending');
assert.equal(escrow.fee, 0.10, 'Expected 1% fee');

// 2. Verify persisted to Neon branch
const [row] = await sql`
  SELECT * FROM escrows WHERE escrow_id = ${escrow.escrow_id}
`;
assert.equal(row.status, 'pending');
assert.equal(parseFloat(row.amount), 10.00);
assert.equal(parseFloat(row.fee),    0.10);

// 3. Release and verify
await releaseEscrow(escrow.escrow_id, 'test-payee-002');
const [updated] = await sql`
  SELECT status, released_at FROM escrows WHERE escrow_id = ${escrow.escrow_id}
`;
assert.equal(updated.status, 'released');
assert.ok(updated.released_at, 'released_at should be set');

console.log('All escrow integration tests passed on Neon branch.');

export const runtime = 'edge';  // Vercel Edge Function

import { neon } from '@neondatabase/serverless';

const sql    = neon(process.env.DATABASE_URL!);
const PF_KEY = process.env.PURPLE_FLEA_API_KEY!;

export async function POST(req: Request): Promise<Response> {
  const { payer_id, payee_id, amount, description } = await req.json();

  // 1. Create escrow on Purple Flea
  const pfRes = await fetch('https://escrow.purpleflea.com/api/escrow', {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${PF_KEY}`,
      'Content-Type': 'application/json',
    },
    body: JSON.stringify({ payer_id, payee_id, amount, description }),
  });
  if (!pfRes.ok) return Response.json({ error: 'Purple Flea error' }, { status: 502 });
  const escrow = await pfRes.json();

  // 2. Persist to Neon over HTTP (TCP not available at edge)
  await sql`
    INSERT INTO escrows
      (escrow_id, payer_id, payee_id, amount, fee, status, description)
    VALUES (
      ${escrow.escrow_id}, ${payer_id}, ${payee_id},
      ${escrow.amount}, ${escrow.fee}, ${escrow.status}, ${description}
    )
  `;

  return Response.json({
    escrow_id: escrow.escrow_id,
    status:    escrow.status,
    fee:       escrow.fee,
  });
}