my-project-public

repository

loading code, commits, and activity

repositories

loading repo index

#1	// ═══════════════════════════════════════════════════════════════
#2	// DATABASE SERVICE - PostgreSQL (Neon) connection & helpers
#3	// Provides a consistent interface for all database operations,
#4	// compatible with both Cloudflare D1 (SQLite) and Neon PostgreSQL.
#5	// ═══════════════════════════════════════════════════════════════
#6
#7	// ─────────────────────────────────────────────────
#8	// TYPES
#9	// ─────────────────────────────────────────────────
#10
#11	export interface Agent {
#12	id: string;
#13	name: string;
#14	description: string \| null;
#15	api_key_hash: string;
#16	api_key_prefix: string;
#17	wallet_address: string \| null;
#18	chain: 'solana' \| 'solana-devnet';
#19	status: 'active' \| 'suspended' \| 'pending';
#20	permissions: unknown;
#21	requests_per_minute: number;
#22	requests_per_day: number;
#23	metadata: unknown;
#24	created_at: string;
#25	updated_at: string;
#26	last_active_at: string;
#27	}
#28
#29	export interface Session {
#30	id: string;
#31	agent_id: string;
#32	token_hash: string;
#33	ip_address: string \| null;
#34	user_agent: string \| null;
#35	created_at: string;
#36	expires_at: string;
#37	last_used_at: string;
#38	}
#39
#40	export interface AgentActivity {
#41	id: number;
#42	agent_id: string;
#43	action: string;
#44	details: unknown;
#45	ip_address: string \| null;
#46	status: 'success' \| 'failed' \| 'pending';
#47	error_message: string \| null;
#48	created_at: string;
#49	}
#50
#51	export interface Transaction {
#52	id: string;
#53	agent_id: string;
#54	type: 'transfer' \| 'swap' \| 'fund';
#55	from_address: string \| null;
#56	to_address: string \| null;
#57	token: string \| null;
#58	amount: string \| null;
#59	chain: string;
#60	tx_hash: string \| null;
#61	status: 'pending' \| 'success' \| 'failed';
#62	error_message: string \| null;
#63	created_at: string;
#64	completed_at: string \| null;
#65	}
#66
#67	export interface SmartWallet {
#68	id: string;
#69	agent_id: string;
#70	wallet_type: 'smart' \| 'mpc' \| 'custodial';
#71	address: string;
#72	chain: string;
#73	admin_signer_address: string \| null;
#74	delegated_signer_id: string \| null;
#75	delegated_signer_status: 'pending' \| 'active' \| 'rejected' \| null;
#76	locator: string \| null;
#77	linked_user: string \| null;
#78	alias: string \| null;
#79	is_primary: boolean;
#80	status: 'active' \| 'suspended' \| 'pending';
#81	metadata: unknown;
#82	created_at: string;
#83	updated_at: string;
#84	last_used_at: string \| null;
#85	}
#86
#87	export interface AgentDeployment {
#88	id: string;
#89	agent_id: string;
#90	name: string;
#91	description: string \| null;
#92	status: 'pending' \| 'deploying' \| 'running' \| 'stopped' \| 'error';
#93	wallet_address: string \| null;
#94	chain: string;
#95	public_key: string \| null;
#96	delegated_signer_id: string \| null;
#97	delegated_signer_status: 'pending' \| 'active' \| 'rejected' \| null;
#98	configuration: unknown;
#99	capabilities: unknown;
#100	created_at: string;
#101	deployed_at: string \| null;
#102	last_active_at: string \| null;
#103	metadata: unknown;
#104	}
#105
#106	// ─────────────────────────────────────────────────
#107	// DATABASE CLASS - Abstracts PostgreSQL connection
#108	// ─────────────────────────────────────────────────
#109
#110	export class Database {
#111	private db: D1Database;
#112
#113	constructor(db: D1Database) {
#114	this.db = db;
#115	}
#116
#117	/**
#118	* Execute a query and return all results as array
#119	*/
#120	async all<T = Record<string, unknown>>(
#121	sql: string,
#122	...bindings: unknown[]
#123	): Promise<T[]> {
#124	const result = await this.db.prepare(sql).bind(...bindings).all<T>();
#125	return result.results;
#126	}
#127
#128	/**
#129	* Execute a query and return the first result
#130	*/
#131	async first<T = Record<string, unknown>>(
#132	sql: string,
#133	...bindings: unknown[]
#134	): Promise<T \| null> {
#135	const result = await this.db.prepare(sql).bind(...bindings).first<T>();
#136	return result \|\| null;
#137	}
#138
#139	/**
#140	* Execute a write query (INSERT, UPDATE, DELETE)
#141	*/
#142	async execute(
#143	sql: string,
#144	...bindings: unknown[]
#145	): Promise<{ success: boolean; meta?: Record<string, unknown> }> {
#146	const result = await this.db.prepare(sql).bind(...bindings).run();
#147	return { success: true, meta: result.meta };
#148	}
#149
#150	/**
#151	* Execute multiple statements in a batch
#152	*/
#153	async batch(
#154	statements: Array<{ sql: string; bindings: unknown[] }>
#155	): Promise<Array<{ success: boolean }>> {
#156	const results: Array<{ success: boolean }> = [];
#157	for (const stmt of statements) {
#158	try {
#159	await this.db.prepare(stmt.sql).bind(...stmt.bindings).run();
#160	results.push({ success: true });
#161	} catch {
#162	results.push({ success: false });
#163	}
#164	}
#165	return results;
#166	}
#167
#168	// ═══════════════════════════════════════════════════
#169	// AGENT OPERATIONS
#170	// ═══════════════════════════════════════════════════
#171
#172	async createAgent(agent: {
#173	id: string;
#174	name: string;
#175	description?: string;
#176	api_key_hash: string;
#177	api_key_prefix: string;
#178	chain?: string;
#179	permissions: string;
#180	metadata?: string;
#181	}): Promise<void> {
#182	await this.execute(
#183	`INSERT INTO agents (id, name, description, api_key_hash, api_key_prefix, chain, permissions, metadata)
#184	VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
#185	agent.id,
#186	agent.name,
#187	agent.description \|\| null,
#188	agent.api_key_hash,
#189	agent.api_key_prefix,
#190	agent.chain \|\| 'solana-devnet',
#191	agent.permissions,
#192	agent.metadata \|\| null
#193	);
#194	}
#195
#196	async getAgentById(id: string): Promise<Agent \| null> {
#197	return this.first<Agent>(
#198	'SELECT * FROM agents WHERE id = ?',
#199	id
#200	);
#201	}
#202
#203	async getAgentByApiKeyHash(apiKeyHash: string): Promise<Agent \| null> {
#204	return this.first<Agent>(
#205	"SELECT * FROM agents WHERE api_key_hash = ? AND status = 'active'",
#206	apiKeyHash
#207	);
#208	}
#209
#210	async updateAgentWallet(agentId: string, walletAddress: string): Promise<void> {
#211	await this.execute(
#212	'UPDATE agents SET wallet_address = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
#213	walletAddress,
#214	agentId
#215	);
#216	}
#217
#218	async updateAgentApiKey(
#219	agentId: string,
#220	apiKeyHash: string,
#221	apiKeyPrefix: string
#222	): Promise<void> {
#223	await this.execute(
#224	'UPDATE agents SET api_key_hash = ?, api_key_prefix = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
#225	apiKeyHash,
#226	apiKeyPrefix,
#227	agentId
#228	);
#229	}
#230
#231	async updateAgentLastActive(agentId: string): Promise<void> {
#232	await this.execute(
#233	'UPDATE agents SET last_active_at = CURRENT_TIMESTAMP WHERE id = ?',
#234	agentId
#235	);
#236	}
#237
#238	// ═══════════════════════════════════════════════════
#239	// SESSION OPERATIONS
#240	// ═══════════════════════════════════════════════════
#241
#242	async createSession(session: {
#243	id: string;
#244	agent_id: string;
#245	token_hash: string;
#246	ip_address?: string;
#247	user_agent?: string;
#248	expires_at: string;
#249	}): Promise<void> {
#250	await this.execute(
#251	`INSERT INTO sessions (id, agent_id, token_hash, ip_address, user_agent, expires_at)
#252	VALUES (?, ?, ?, ?, ?, ?)`,
#253	session.id,
#254	session.agent_id,
#255	session.token_hash,
#256	session.ip_address \|\| null,
#257	session.user_agent \|\| null,
#258	session.expires_at
#259	);
#260	}
#261
#262	async getSessionByTokenHash(tokenHash: string): Promise<Session \| null> {
#263	return this.first<Session>(
#264	"SELECT * FROM sessions WHERE token_hash = ? AND expires_at > CURRENT_TIMESTAMP",
#265	tokenHash
#266	);
#267	}
#268
#269	async deleteSessionByTokenHash(tokenHash: string): Promise<void> {
#270	await this.execute(
#271	'DELETE FROM sessions WHERE token_hash = ?',
#272	tokenHash
#273	);
#274	}
#275
#276	async deleteSessionsByAgentId(agentId: string): Promise<void> {
#277	await this.execute(
#278	'DELETE FROM sessions WHERE agent_id = ?',
#279	agentId
#280	);
#281	}
#282
#283	async updateSessionLastUsed(tokenHash: string): Promise<void> {
#284	await this.execute(
#285	'UPDATE sessions SET last_used_at = CURRENT_TIMESTAMP WHERE token_hash = ?',
#286	tokenHash
#287	);
#288	}
#289
#290	// ═══════════════════════════════════════════════════
#291	// ACTIVITY LOGGING
#292	// ═══════════════════════════════════════════════════
#293
#294	async logActivity(activity: {
#295	agent_id: string;
#296	action: string;
#297	details?: string;
#298	ip_address?: string;
#299	status?: string;
#300	error_message?: string;
#301	}): Promise<void> {
#302	await this.execute(
#303	`INSERT INTO agent_activity (agent_id, action, details, ip_address, status, error_message)
#304	VALUES (?, ?, ?, ?, ?, ?)`,
#305	activity.agent_id,
#306	activity.action,
#307	activity.details \|\| null,
#308	activity.ip_address \|\| null,
#309	activity.status \|\| 'success',
#310	activity.error_message \|\| null
#311	);
#312	}
#313
#314	async getAgentActivity(
#315	agentId: string,
#316	limit: number = 50
#317	): Promise<AgentActivity[]> {
#318	return this.all<AgentActivity>(
#319	'SELECT * FROM agent_activity WHERE agent_id = ? ORDER BY created_at DESC LIMIT ?',
#320	agentId,
#321	limit
#322	);
#323	}
#324
#325	// ═══════════════════════════════════════════════════
#326	// TRANSACTION OPERATIONS
#327	// ═══════════════════════════════════════════════════
#328
#329	async createTransaction(tx: {
#330	id: string;
#331	agent_id: string;
#332	type: string;
#333	from_address?: string;
#334	to_address?: string;
#335	token?: string;
#336	amount?: string;
#337	chain?: string;
#338	tx_hash?: string;
#339	status?: string;
#340	}): Promise<void> {
#341	await this.execute(
#342	`INSERT INTO transactions (id, agent_id, type, from_address, to_address, token, amount, chain, tx_hash, status)
#343	VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
#344	tx.id,
#345	tx.agent_id,
#346	tx.type,
#347	tx.from_address \|\| null,
#348	tx.to_address \|\| null,
#349	tx.token \|\| null,
#350	tx.amount \|\| null,
#351	tx.chain \|\| 'solana-devnet',
#352	tx.tx_hash \|\| null,
#353	tx.status \|\| 'pending'
#354	);
#355	}
#356
#357	async updateTransactionStatus(
#358	txId: string,
#359	status: string,
#360	errorMessage?: string,
#361	txHash?: string
#362	): Promise<void> {
#363	const sets = ['status = ?'];
#364	const bindings: unknown[] = [status];
#365
#366	if (status === 'success' \|\| status === 'failed') {
#367	sets.push('completed_at = CURRENT_TIMESTAMP');
#368	}
#369	if (errorMessage) {
#370	sets.push('error_message = ?');
#371	bindings.push(errorMessage);
#372	}
#373	if (txHash) {
#374	sets.push('tx_hash = ?');
#375	bindings.push(txHash);
#376	}
#377
#378	bindings.push(txId);
#379	await this.execute(
#380	`UPDATE transactions SET ${sets.join(', ')} WHERE id = ?`,
#381	...bindings
#382	);
#383	}
#384
#385	async getAgentTransactions(
#386	agentId: string,
#387	limit: number = 20
#388	): Promise<Transaction[]> {
#389	return this.all<Transaction>(
#390	'SELECT * FROM transactions WHERE agent_id = ? ORDER BY created_at DESC LIMIT ?',
#391	agentId,
#392	limit
#393	);
#394	}
#395
#396	// ═══════════════════════════════════════════════════
#397	// API KEY HISTORY
#398	// ═══════════════════════════════════════════════════
#399
#400	async addApiKeyHistory(entry: {
#401	agent_id: string;
#402	api_key_prefix: string;
#403	}): Promise<void> {
#404	await this.execute(
#405	`INSERT INTO api_key_history (agent_id, api_key_prefix) VALUES (?, ?)`,
#406	entry.agent_id,
#407	entry.api_key_prefix
#408	);
#409	}
#410
#411	async revokePreviousApiKeys(
#412	agentId: string,
#413	reason: string = 'regenerated'
#414	): Promise<void> {
#415	await this.execute(
#416	`UPDATE api_key_history
#417	SET revoked_at = CURRENT_TIMESTAMP, revoke_reason = ?
#418	WHERE agent_id = ? AND revoked_at IS NULL`,
#419	reason,
#420	agentId
#421	);
#422	}
#423
#424	// ═══════════════════════════════════════════════════
#425	// SMART WALLET OPERATIONS
#426	// ═══════════════════════════════════════════════════
#427
#428	async createSmartWallet(wallet: {
#429	id: string;
#430	agent_id: string;
#431	wallet_type: string;
#432	address: string;
#433	chain?: string;
#434	admin_signer_address?: string;
#435	locator?: string;
#436	linked_user?: string;
#437	alias?: string;
#438	is_primary?: boolean;
#439	metadata?: string;
#440	}): Promise<void> {
#441	await this.execute(
#442	`INSERT INTO smart_wallets
#443	(id, agent_id, wallet_type, address, chain, admin_signer_address, locator, linked_user, alias, is_primary, metadata)
#444	VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
#445	wallet.id,
#446	wallet.agent_id,
#447	wallet.wallet_type,
#448	wallet.address,
#449	wallet.chain \|\| 'solana-devnet',
#450	wallet.admin_signer_address \|\| null,
#451	wallet.locator \|\| null,
#452	wallet.linked_user \|\| null,
#453	wallet.alias \|\| null,
#454	wallet.is_primary ? 1 : 0,
#455	wallet.metadata \|\| null
#456	);
#457	}
#458
#459	async getSmartWalletByAddress(address: string): Promise<SmartWallet \| null> {
#460	return this.first<SmartWallet>(
#461	'SELECT * FROM smart_wallets WHERE address = ?',
#462	address
#463	);
#464	}
#465
#466	async getAgentSmartWallets(agentId: string): Promise<SmartWallet[]> {
#467	return this.all<SmartWallet>(
#468	'SELECT * FROM smart_wallets WHERE agent_id = ? ORDER BY created_at DESC',
#469	agentId
#470	);
#471	}
#472
#473	// ═══════════════════════════════════════════════════
#474	// DEPLOYMENT OPERATIONS
#475	// ═══════════════════════════════════════════════════
#476
#477	async createDeployment(deployment: {
#478	id: string;
#479	agent_id: string;
#480	name: string;
#481	description?: string;
#482	status?: string;
#483	wallet_address?: string;
#484	chain?: string;
#485	configuration?: string;
#486	capabilities?: string;
#487	metadata?: string;
#488	}): Promise<void> {
#489	await this.execute(
#490	`INSERT INTO agent_deployments
#491	(id, agent_id, name, description, status, wallet_address, chain, configuration, capabilities, metadata)
#492	VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
#493	deployment.id,
#494	deployment.agent_id,
#495	deployment.name,
#496	deployment.description \|\| null,
#497	deployment.status \|\| 'pending',
#498	deployment.wallet_address \|\| null,
#499	deployment.chain \|\| 'solana-devnet',
#500	deployment.configuration \|\| '{}',
#501	deployment.capabilities \|\| '[]',
#502	deployment.metadata \|\| null
#503	);
#504	}
#505
#506	async getDeploymentsByAgentId(agentId: string): Promise<AgentDeployment[]> {
#507	return this.all<AgentDeployment>(
#508	'SELECT * FROM agent_deployments WHERE agent_id = ? ORDER BY created_at DESC',
#509	agentId
#510	);
#511	}
#512
#513	async getDeploymentById(deploymentId: string): Promise<AgentDeployment \| null> {
#514	return this.first<AgentDeployment>(
#515	'SELECT * FROM agent_deployments WHERE id = ?',
#516	deploymentId
#517	);
#518	}
#519
#520	async updateDeploymentStatus(
#521	deploymentId: string,
#522	status: string
#523	): Promise<void> {
#524	const sets = ['status = ?'];
#525	const bindings: unknown[] = [status];
#526
#527	if (status === 'running') {
#528	sets.push('deployed_at = CURRENT_TIMESTAMP');
#529	}
#530	bindings.push(deploymentId);
#531
#532	await this.execute(
#533	`UPDATE agent_deployments SET ${sets.join(', ')} WHERE id = ?`,
#534	...bindings
#535	);
#536	}
#537
#538	async updateDeploymentDelegatedSigner(
#539	deploymentId: string,
#540	signerId: string,
#541	signerStatus: string
#542	): Promise<void> {
#543	await this.execute(
#544	'UPDATE agent_deployments SET delegated_signer_id = ?, delegated_signer_status = ? WHERE id = ?',
#545	signerId,
#546	signerStatus,
#547	deploymentId
#548	);
#549	}
#550
#551	// ═══════════════════════════════════════════════════
#552	// WALLET BALANCES CACHE
#553	// ═══════════════════════════════════════════════════
#554
#555	async upsertWalletBalance(walletBalance: {
#556	wallet_address: string;
#557	chain: string;
#558	token: string;
#559	symbol?: string;
#560	amount: string;
#561	decimals?: number;
#562	usd_value?: string;
#563	}): Promise<void> {
#564	await this.execute(
#565	`INSERT INTO wallet_balances_cache (wallet_address, chain, token, symbol, amount, decimals, usd_value, updated_at)
#566	VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
#567	ON CONFLICT (wallet_address, chain, token)
#568	DO UPDATE SET amount = ?, usd_value = ?, updated_at = CURRENT_TIMESTAMP`,
#569	// INSERT values
#570	walletBalance.wallet_address,
#571	walletBalance.chain,
#572	walletBalance.token,
#573	walletBalance.symbol \|\| null,
#574	walletBalance.amount,
#575	walletBalance.decimals \|\| 9,
#576	walletBalance.usd_value \|\| null,
#577	// UPDATE values
#578	walletBalance.amount,
#579	walletBalance.usd_value \|\| null
#580	);
#581	}
#582
#583	async getCachedBalance(
#584	walletAddress: string,
#585	chain: string,
#586	token: string
#587	): Promise<{
#588	wallet_address: string;
#589	amount: string;
#590	usd_value: string \| null;
#591	updated_at: string;
#592	} \| null> {
#593	return this.first(
#594	'SELECT wallet_address, amount, usd_value, updated_at FROM wallet_balances_cache WHERE wallet_address = ? AND chain = ? AND token = ?',
#595	walletAddress,
#596	chain,
#597	token
#598	);
#599	}
#600
#601	// ═══════════════════════════════════════════════════
#602	// CLEANUP & UTILITY
#603	// ═══════════════════════════════════════════════════
#604
#605	async cleanupExpiredSessions(): Promise<number> {
#606	const result = await this.execute(
#607	'DELETE FROM sessions WHERE expires_at < CURRENT_TIMESTAMP'
#608	);
#609	return (result.meta?.changes as number) \|\| 0;
#610	}
#611
#612	async getAgentCount(): Promise<number> {
#613	const result = await this.first<{ count: number }>(
#614	'SELECT COUNT(*) as count FROM agents'
#615	);
#616	return result?.count \|\| 0;
#617	}
#618
#619	async getActiveSessionCount(): Promise<number> {
#620	const result = await this.first<{ count: number }>(
#621	"SELECT COUNT(*) as count FROM sessions WHERE expires_at > CURRENT_TIMESTAMP"
#622	);
#623	return result?.count \|\| 0;
#624	}
#625	}
#626

z6Mkq5mY3JWtxoxUobWcfNHm7AkRubgSWEZTkBVqZXJviFZ5/my-project-public