Skip to content

Open-source platform for 3D AI agents. Turn text or a photo into a rigged, animated GLB avatar, give it an LLM brain, memory and a wallet, and embed it anywhere with one web component. Remote MCP server, x402 payments, Three.js, browser-native.

18.8 KB
Raw Blame
// @ts-check
// Back-an-Agent Vaults — the DB access layer.
//
// Every read/write against agent_vaults / vault_backers / vault_positions /
// vault_events goes through here, so the routes stay thin and the SQL stays in
// one auditable place. All money columns are numeric(40,0) USDC atomics and come
// back as strings — callers convert with vault-accounting.toBig() before doing
// any arithmetic. Nothing here moves funds or signs; that's vault-trade.js /
// vault-transfer.js. This module is purely the system of record.

import { sql } from './db.js';
import { runLocked } from './locked-sql.js';

// Columns safe to expose publicly (NEVER the encrypted_secret). Spread into a
// SELECT to guarantee the vault keypair ciphertext can't leak through a read.
const PUBLIC_COLS = sql`
	id, agent_id, owner_user_id, network, vault_address, status,
	performance_fee_bps, per_backer_cap_atomics, max_drawdown_bps,
	max_per_trade_atomics, daily_budget_atomics,
	total_shares, peak_share_price_e6, accrued_fee_atomics,
	halt_reason, paused_at, created_at, updated_at
`;

/** Create a vault row. The caller has already generated + encrypted the wallet. */
export async function createVault({
	agentId, ownerUserId, network, vaultAddress, encryptedSecret,
	performanceFeeBps, perBackerCapAtomics, maxDrawdownBps,
	maxPerTradeAtomics, dailyBudgetAtomics,
}) {
	const [row] = await sql`
		INSERT INTO agent_vaults
			(agent_id, owner_user_id, network, vault_address, encrypted_secret,
			 performance_fee_bps, per_backer_cap_atomics, max_drawdown_bps,
			 max_per_trade_atomics, daily_budget_atomics)
		VALUES (
			${agentId}, ${ownerUserId}, ${network}, ${vaultAddress}, ${encryptedSecret},
			${performanceFeeBps}, ${perBackerCapAtomics == null ? null : String(perBackerCapAtomics)},
			${maxDrawdownBps}, ${String(maxPerTradeAtomics)}, ${String(dailyBudgetAtomics)}
		)
		RETURNING ${PUBLIC_COLS}
	`;
	return row;
}

/** Public vault row by id (no secret). */
export async function getVault(id) {
	const [row] = await sql`SELECT ${PUBLIC_COLS} FROM agent_vaults WHERE id = ${id} LIMIT 1`;
	return row || null;
}

/** The active (non-closed) vault for an agent, if any. */
export async function getVaultByAgent(agentId) {
	const [row] = await sql`
		SELECT ${PUBLIC_COLS} FROM agent_vaults
		WHERE agent_id = ${agentId} AND status <> 'closed'
		ORDER BY created_at DESC LIMIT 1
	`;
	return row || null;
}

/**
 * Internal: the vault row WITH the encrypted secret. Only vault-trade.js /
 * vault-transfer.js call this, right before recovering the keypair to sign. Never
 * return this shape to an HTTP response.
 */
export async function getVaultWithSecret(id) {
	const [row] = await sql`
		SELECT ${PUBLIC_COLS}, encrypted_secret FROM agent_vaults WHERE id = ${id} LIMIT 1
	`;
	return row || null;
}

/**
 * Discovery feed: open/paused vaults with their owning agent's identity, ranked.
 * The ranking is computed by the caller against verified reputation; here we just
 * return the raw rows + agent facts and a deposited-backer count.
 */
export async function listVaults({ status = 'open', limit = 60 } = {}) {
	const lim = Math.max(1, Math.min(120, Number(limit) || 60));
	const rows = await sql`
		SELECT
			v.id, v.agent_id, v.network, v.vault_address, v.status,
			v.performance_fee_bps, v.per_backer_cap_atomics, v.max_drawdown_bps,
			v.max_per_trade_atomics, v.daily_budget_atomics,
			v.total_shares, v.peak_share_price_e6, v.created_at, v.updated_at,
			a.name AS agent_name,
			COALESCE(a.profile_image_url, a.avatar_url) AS agent_image,
			a.description AS agent_description,
			(SELECT COUNT(*) FROM vault_backers b WHERE b.vault_id = v.id AND b.shares > 0)::int AS backer_count,
			(SELECT COALESCE(SUM(b.deposited_atomics), 0) FROM vault_backers b WHERE b.vault_id = v.id) AS lifetime_deposited,
			(SELECT e.nav_atomics FROM vault_events e WHERE e.vault_id = v.id AND e.nav_atomics IS NOT NULL ORDER BY e.id DESC LIMIT 1) AS last_nav_atomics
		FROM agent_vaults v
		JOIN agent_identities a ON a.id = v.agent_id AND a.deleted_at IS NULL
		WHERE (${status === 'all'} OR v.status = ${status})
		  AND v.status <> 'closed'
		ORDER BY v.updated_at DESC
		LIMIT ${lim}
	`;
	return rows;
}

/** Vaults the user has backed (their portfolio). */
export async function listBackedVaults(userId, { limit = 60 } = {}) {
	const lim = Math.max(1, Math.min(120, Number(limit) || 60));
	const rows = await sql`
		SELECT
			b.shares, b.cost_basis_atomics, b.deposited_atomics, b.redeemed_atomics,
			b.realized_gain_atomics, b.fees_paid_atomics, b.backer_agent_id,
			v.id, v.agent_id, v.network, v.status, v.performance_fee_bps,
			v.max_drawdown_bps, v.total_shares, v.peak_share_price_e6,
			a.name AS agent_name, COALESCE(a.profile_image_url, a.avatar_url) AS agent_image
		FROM vault_backers b
		JOIN agent_vaults v ON v.id = b.vault_id
		JOIN agent_identities a ON a.id = v.agent_id
		WHERE b.user_id = ${userId} AND b.shares > 0
		ORDER BY b.updated_at DESC
		LIMIT ${lim}
	`;
	return rows;
}

/** A single backer's position in a vault (null if they hold none). */
export async function getBacker(vaultId, userId) {
	const [row] = await sql`
		SELECT * FROM vault_backers WHERE vault_id = ${vaultId} AND user_id = ${userId} LIMIT 1
	`;
	return row || null;
}

/** The list of backers in a vault (for the vault page roster). */
export async function listBackers(vaultId, { limit = 50 } = {}) {
	const lim = Math.max(1, Math.min(200, Number(limit) || 50));
	const rows = await sql`
		SELECT user_id, backer_agent_id, shares, cost_basis_atomics, deposited_atomics, updated_at
		FROM vault_backers
		WHERE vault_id = ${vaultId} AND shares > 0
		ORDER BY shares DESC
		LIMIT ${lim}
	`;
	return rows;
}

/** Open positions held by the vault (marked to market by the caller). */
export async function getOpenPositions(vaultId) {
	return sql`
		SELECT id, mint, token_decimals, amount_raw, cost_atomics, last_mark_atomics, opened_at
		FROM vault_positions WHERE vault_id = ${vaultId} AND status = 'open'
		ORDER BY opened_at ASC
	`;
}

/** Append an event to the immutable ledger. Returns the row id (or null on idem clash). */
export async function recordVaultEvent(e) {
	const rows = await sql`
		INSERT INTO vault_events
			(vault_id, type, user_id, backer_agent_id, shares_delta, atomics_delta,
			 nav_atomics, share_price_e6, signature, status, reason, idempotency_key, meta)
		VALUES (
			${e.vaultId}, ${e.type}, ${e.userId ?? null}, ${e.backerAgentId ?? null},
			${e.sharesDelta == null ? null : String(e.sharesDelta)},
			${e.atomicsDelta == null ? null : String(e.atomicsDelta)},
			${e.navAtomics == null ? null : String(e.navAtomics)},
			${e.sharePriceE6 == null ? null : String(e.sharePriceE6)},
			${e.signature ?? null}, ${e.status ?? 'ok'}, ${e.reason ?? null},
			${e.idempotencyKey ?? null}, ${JSON.stringify(e.meta ?? {})}::jsonb
		)
		ON CONFLICT (vault_id, idempotency_key) WHERE idempotency_key IS NOT NULL DO NOTHING
		RETURNING id
	`;
	return rows.length ? Number(rows[0].id) : null;
}

/** Finalize a previously-recorded (pending) event: status/signature/nav/meta. */
export async function updateVaultEvent(id, patch) {
	await sql`
		UPDATE vault_events SET
			status = COALESCE(${patch.status ?? null}, status),
			signature = COALESCE(${patch.signature ?? null}, signature),
			nav_atomics = ${patch.navAtomics == null ? sql`nav_atomics` : String(patch.navAtomics)},
			share_price_e6 = ${patch.sharePriceE6 == null ? sql`share_price_e6` : String(patch.sharePriceE6)},
			atomics_delta = ${patch.atomicsDelta == null ? sql`atomics_delta` : String(patch.atomicsDelta)},
			meta = meta || ${JSON.stringify(patch.meta ?? {})}::jsonb
		WHERE id = ${id}
	`.catch(() => {});
}

/** Cursor-paginated event feed for the audit trail. */
export async function listVaultEvents(vaultId, { limit = 50, beforeId = null, type = null } = {}) {
	const lim = Math.max(1, Math.min(200, Number(limit) || 50));
	return sql`
		SELECT id, type, user_id, backer_agent_id, shares_delta, atomics_delta,
		       nav_atomics, share_price_e6, signature, status, reason, meta, created_at
		FROM vault_events
		WHERE vault_id = ${vaultId}
		  AND (${beforeId == null}::bool OR id < ${beforeId == null ? 0 : beforeId})
		  AND (${type == null}::bool OR type = ${type})
		ORDER BY id DESC
		LIMIT ${lim}
	`;
}

/**
 * Rolling 24h buy spend (USDC atomics): the daily-budget denominator.
 *
 * Counts settled AND in-flight buys. A buy that has been claimed but not yet
 * confirmed on-chain has already committed the vault's capital, so leaving it out
 * (as this did until the 2026-08-06 review, L5) let two concurrent buys read the
 * same pre-spend total and both pass a budget only one of them fit in. The
 * authoritative gate is claimVaultBuyBudget below, which reserves under a lock;
 * this read is for display and for the pre-flight message.
 */
export async function getDailyTradeSpend(vaultId) {
	const [r] = await sql`
		SELECT COALESCE(SUM((meta->>'usdc_in')::numeric), 0) AS spent
		FROM vault_events
		WHERE vault_id = ${vaultId} AND type = 'trade' AND status IN ('ok', 'pending')
		  AND (meta->>'side') = 'buy'
		  AND created_at > now() - interval '24 hours'
	`;
	return r?.spent ?? '0';
}

/**
 * Atomically claim rolling-24h budget headroom for a buy AND reserve its pending
 * ledger row, under a per-vault advisory lock.
 *
 * Check-then-insert was two statements, so two owner buys racing on one vault
 * could both read the same pre-spend total, both clear the budget, and both
 * settle: a $X/day ceiling became $X + one trade. Here the sum and the insert are
 * one statement behind `pg_advisory_xact_lock`, mirroring reserveSpendUsd in
 * agent-trade-guards.js, so the second buy sees the first one's pending row.
 *
 * @returns {Promise<{ ok: true, claimId: number, spentBefore: string }
 *                  | { ok: false, reason: 'daily_budget'|'in_flight', spentBefore?: string }>}
 */
export async function claimVaultBuyBudget({
	vaultId, userId, idempotencyKey, usdcInAtomics, dailyBudgetAtomics, reason, meta,
}) {
	const amount = String(usdcInAtomics);
	// A non-positive budget means "no ceiling configured" (same reading as
	// tradeExceedsDailyBudget), so the claim only has to clear idempotency.
	const budget = String(dailyBudgetAtomics ?? 0);
	const rows = await runLocked([vaultId], sql`
		WITH spent AS (
			SELECT COALESCE(SUM((meta->>'usdc_in')::numeric), 0) AS s
			FROM vault_events
			WHERE vault_id = ${vaultId} AND type = 'trade' AND status IN ('ok', 'pending')
			  AND (meta->>'side') = 'buy'
			  AND created_at > now() - interval '24 hours'
		)
		INSERT INTO vault_events
			(vault_id, type, user_id, atomics_delta, status, reason, idempotency_key, meta)
		SELECT ${vaultId}, 'trade', ${userId ?? null}, ${`-${amount}`}, 'pending', ${reason ?? null},
		       ${idempotencyKey ?? null}, ${JSON.stringify(meta ?? {})}::jsonb
		FROM spent
		WHERE ${budget}::numeric <= 0
		   OR spent.s + ${amount}::numeric <= ${budget}::numeric
		ON CONFLICT (vault_id, idempotency_key) WHERE idempotency_key IS NOT NULL DO NOTHING
		RETURNING id, (SELECT s FROM spent)::text AS spent_before
	`);
	if (rows.length) {
		return { ok: true, claimId: Number(rows[0].id), spentBefore: rows[0].spent_before ?? '0' };
	}
	// No row: either the budget rejected it or this idempotency key already claimed
	// one. Distinguish the two so the caller reports the real reason.
	if (idempotencyKey) {
		const [dupe] = await sql`
			SELECT id FROM vault_events
			WHERE vault_id = ${vaultId} AND idempotency_key = ${idempotencyKey} LIMIT 1
		`;
		if (dupe) return { ok: false, reason: 'in_flight' };
	}
	return { ok: false, reason: 'daily_budget', spentBefore: await getDailyTradeSpend(vaultId) };
}

/** Upsert a backer's position after a deposit (mint shares) or redemption (burn). */
export async function applyBackerDelta({ vaultId, userId, backerAgentId, sharesDelta, basisDelta, depositedDelta = 0n, redeemedDelta = 0n, realizedGainDelta = 0n, feesPaidDelta = 0n }) {
	const [row] = await sql`
		INSERT INTO vault_backers
			(vault_id, user_id, backer_agent_id, shares, cost_basis_atomics,
			 deposited_atomics, redeemed_atomics, realized_gain_atomics, fees_paid_atomics)
		VALUES (
			${vaultId}, ${userId}, ${backerAgentId},
			${String(sharesDelta)}, ${String(basisDelta < 0n ? 0n : basisDelta)},
			${String(depositedDelta)}, ${String(redeemedDelta)},
			${String(realizedGainDelta)}, ${String(feesPaidDelta)}
		)
		ON CONFLICT (vault_id, user_id) DO UPDATE SET
			shares = GREATEST(vault_backers.shares + ${String(sharesDelta)}, 0),
			cost_basis_atomics = GREATEST(vault_backers.cost_basis_atomics + ${String(basisDelta)}, 0),
			deposited_atomics = vault_backers.deposited_atomics + ${String(depositedDelta)},
			redeemed_atomics = vault_backers.redeemed_atomics + ${String(redeemedDelta)},
			realized_gain_atomics = vault_backers.realized_gain_atomics + ${String(realizedGainDelta)},
			fees_paid_atomics = vault_backers.fees_paid_atomics + ${String(feesPaidDelta)},
			backer_agent_id = ${backerAgentId},
			updated_at = now()
		RETURNING *
	`;
	return row;
}

/**
 * Burn `shares` from a backer's position atomically, only if the position still
 * holds them. Returns the updated row, or null when another redemption already
 * took them. Redemption burns BEFORE it pays: a read-then-pay let N concurrent
 * redeems each pay the full position out of other backers' USDC.
 */
export async function reserveBackerShares(vaultId, userId, shares) {
	const [row] = await sql`
		UPDATE vault_backers SET shares = shares - ${String(shares)}, updated_at = now()
		WHERE vault_id = ${vaultId} AND user_id = ${userId} AND shares >= ${String(shares)}
		RETURNING shares
	`;
	return row || null;
}

/** Give back shares a reservation burned when its payout did not happen. */
export async function releaseBackerShares(vaultId, userId, shares) {
	await sql`
		UPDATE vault_backers SET shares = shares + ${String(shares)}, updated_at = now()
		WHERE vault_id = ${vaultId} AND user_id = ${userId}
	`;
}

/**
 * Take `atomics` out of the vault's accrued fee atomically, only if that much is
 * still accrued. Returns the remaining accrual, or null when a concurrent claim
 * already took it. The fee claim reserves BEFORE it pays for the same reason
 * reserveBackerShares exists.
 */
export async function reserveAccruedFee(vaultId, atomics) {
	const [row] = await sql`
		UPDATE agent_vaults SET accrued_fee_atomics = accrued_fee_atomics - ${String(atomics)}, updated_at = now()
		WHERE id = ${vaultId} AND accrued_fee_atomics >= ${String(atomics)}
		RETURNING accrued_fee_atomics
	`;
	return row ? row.accrued_fee_atomics : null;
}

/** Move total_shares by a signed delta and ratchet the high-water share-price peak. */
export async function applyVaultShareDelta(vaultId, sharesDelta, peakSharePriceE6 = null) {
	const [row] = await sql`
		UPDATE agent_vaults SET
			total_shares = GREATEST(total_shares + ${String(sharesDelta)}, 0),
			peak_share_price_e6 = ${peakSharePriceE6 == null ? sql`peak_share_price_e6` : sql`GREATEST(peak_share_price_e6, ${String(peakSharePriceE6)})`},
			updated_at = now()
		WHERE id = ${vaultId}
		RETURNING ${PUBLIC_COLS}
	`;
	return row || null;
}

/** Accrue (delta>0) or claim (delta<0) owner performance fees. */
export async function applyAccruedFee(vaultId, deltaAtomics) {
	const [row] = await sql`
		UPDATE agent_vaults SET
			accrued_fee_atomics = GREATEST(accrued_fee_atomics + ${String(deltaAtomics)}, 0),
			updated_at = now()
		WHERE id = ${vaultId}
		RETURNING accrued_fee_atomics
	`;
	return row?.accrued_fee_atomics ?? '0';
}

/** Set vault status (pause/resume/halt/close), recording the reason. */
export async function setVaultStatus(vaultId, status, { haltReason = null } = {}) {
	const [row] = await sql`
		UPDATE agent_vaults SET
			status = ${status},
			halt_reason = ${haltReason},
			paused_at = ${status === 'paused' ? sql`now()` : sql`NULL`},
			updated_at = now()
		WHERE id = ${vaultId}
		RETURNING ${PUBLIC_COLS}
	`;
	return row || null;
}

/** Update disclosed terms (owner-only; caller enforces ownership + safety bounds). */
export async function updateVaultTerms(vaultId, patch) {
	const [row] = await sql`
		UPDATE agent_vaults SET
			performance_fee_bps = COALESCE(${patch.performanceFeeBps ?? null}, performance_fee_bps),
			per_backer_cap_atomics = ${patch.perBackerCapAtomics === undefined ? sql`per_backer_cap_atomics` : (patch.perBackerCapAtomics == null ? null : String(patch.perBackerCapAtomics))},
			max_drawdown_bps = COALESCE(${patch.maxDrawdownBps ?? null}, max_drawdown_bps),
			max_per_trade_atomics = COALESCE(${patch.maxPerTradeAtomics == null ? null : String(patch.maxPerTradeAtomics)}, max_per_trade_atomics),
			daily_budget_atomics = COALESCE(${patch.dailyBudgetAtomics == null ? null : String(patch.dailyBudgetAtomics)}, daily_budget_atomics),
			updated_at = now()
		WHERE id = ${vaultId}
		RETURNING ${PUBLIC_COLS}
	`;
	return row || null;
}

/** Add to / open a token position after a buy. */
export async function upsertPosition({ vaultId, mint, tokenDecimals, amountRawDelta, costDelta, markAtomics = null }) {
	const [row] = await sql`
		INSERT INTO vault_positions (vault_id, mint, token_decimals, amount_raw, cost_atomics, last_mark_atomics, status)
		VALUES (${vaultId}, ${mint}, ${tokenDecimals}, ${String(amountRawDelta)}, ${String(costDelta)}, ${markAtomics == null ? null : String(markAtomics)}, 'open')
		ON CONFLICT (vault_id, mint) WHERE status = 'open' DO UPDATE SET
			amount_raw = vault_positions.amount_raw + ${String(amountRawDelta)},
			cost_atomics = vault_positions.cost_atomics + ${String(costDelta)},
			token_decimals = ${tokenDecimals},
			last_mark_atomics = ${markAtomics == null ? sql`vault_positions.last_mark_atomics` : String(markAtomics)},
			updated_at = now()
		RETURNING *
	`;
	return row;
}

/** Reduce a position after a sell; closes it when the balance hits zero. */
export async function reducePosition({ vaultId, mint, amountRawDelta, proceedsAtomics, costRemovedAtomics }) {
	const [pos] = await sql`SELECT * FROM vault_positions WHERE vault_id = ${vaultId} AND mint = ${mint} AND status = 'open' LIMIT 1`;
	if (!pos) return null;
	const remaining = BigInt(pos.amount_raw) - BigInt(amountRawDelta);
	const realized = BigInt(proceedsAtomics) - BigInt(costRemovedAtomics);
	if (remaining <= 0n) {
		const [row] = await sql`
			UPDATE vault_positions SET
				amount_raw = 0, cost_atomics = 0,
				realized_pnl_atomics = realized_pnl_atomics + ${String(realized)},
				status = 'closed', closed_at = now(), updated_at = now()
			WHERE id = ${pos.id} RETURNING *
		`;
		return row;
	}
	const [row] = await sql`
		UPDATE vault_positions SET
			amount_raw = ${String(remaining)},
			cost_atomics = GREATEST(cost_atomics - ${String(costRemovedAtomics)}, 0),
			realized_pnl_atomics = realized_pnl_atomics + ${String(realized)},
			updated_at = now()
		WHERE id = ${pos.id} RETURNING *
	`;
	return row;
}

/** Mark an open position's last NAV contribution (cosmetic cache for the feed). */
export async function markPosition(positionId, markAtomics) {
	await sql`UPDATE vault_positions SET last_mark_atomics = ${String(markAtomics)}, updated_at = now() WHERE id = ${positionId}`.catch(() => {});
}